MySQL：indexの貼り方には要注意。速度低下の原因はインデックスだった。

MySQLのindexは貼り方に要注意！

EC向けシステムの基礎が組みあがり、利用可能なカートの追加や動作周りやボタンの共通化などの調整を進めている今日この頃。
「速度が遅い」と感じる場所があり、インデックスをしっかり作ろうという流れになりました。

そんな作業を進める中で起こった現象がこれ。

速度アップを狙ってindex作ってるのに、なんてことだ。
という事で、indexの貼り方を間違えると速度低下を招くという注意喚起のお話です。

そもそもindexの役割は？

その名の通り【目次】が役割です。検索のための索引集がindexなので、インデックスの有無で処理速度が大きく変わります。
例えば、5万件の中から4個の項目を対象にした検索を掛けた時にindexなしは2秒かかったとしてもindex付きは0.1秒で終わったりします。

それくらい速度に大きな違いを生むため、システム設計ではインデックスをどう貼るかをしっかり考えなくてはいけません。

インデックスに含めなくてよい項目とは

今回の速度低下の原因はこの原則を守っていないパートが複数あった事でした。

というよりも『同じテーブルでも検索方法が変わる為にインデックスが使われない検索が生じるパートがあった』というのが正しいでしょう。

SQL1 の例はstockのみの検索になる為、インデックスはstockだけで良い。

SQL2 の例はstock とshipper_id の検索になる為、stockとshipper_idに対する複合indexが必要。

さて、ここでSQL2もあるから stockとshipper_idで複合index貼ればいいじゃんと考えてしまうのですが、これが今回の速度低下の落とし穴でした。

正解は、インデックスを2つ作る事。

この2つ共が必要だったわけです。

余計なものがあると探す範囲が広がり速度が落ちる

場合によっては使われない事もあるそうです。

JOINしているSQL文などでは…検索にバカみたいな時間を要する事がありました。

でもねぇ、全てのシーンを考えてインデックス貼っていたらデータ容量が大変なことになります。EC業ってアイテム数が積みあがっていきますから、出品1万アイテムでもDBでは5万あるなんてザラです。というか、それぐらいでなくては商品の入れ替えが遅れて気づいたら2000アイテムまで減ってたなんて事はよく起こります(弊社事例)。そんな膨大な量の商品データにインデックスが複数通り貼られるとなれば、データサイズは怖いですよ。

と言う事で、ここら辺はDBの設計時に考慮しておかなくてはなりません。

単独インデックスと複合インデックスの違い

indexには種類があります。それが、単独インデックスと複合インデックスです。
単独インデックスの利用シーンは上記SQL1のような場合です。複合インデックスの利用シーンは上記SQL2です。

そのまま1項目で検索するか2項目以上を組合わせて検索するかの違いで使い分けます。

複合インデックスを効果的に作ると爆発的に速度が上がります。
その【効果的】に重要な役割を果たすのがインデックスの順番です。

複合インデックスは順番も重要

レコード数が少ない場合はそれほど大きな違いはありませんが、1万を超えるレコードとなると話は変わってきます。検索する順番に従いインデックスの順番を組み換えします。

例えば、先程のSQL2の場合では shipper_id ⇒ stock の順にwhereに入っています。複合インデックスではこの順番通りに記載すると効果絶大です。

phpMyAdminではインデックスの順番を入れ替える事が出来ますが、こういったtuningが目的なのでしょうね。

調整後の結果は

無謀なindexを作ってしまったために発生した速度低下でしたが、上記原則を守り下記メンテナンスを行いました。

まとめ

結果、メンテナンス前には画面表示まで5秒かかっていたパートが1秒に短縮！
まぁそれでも1秒かかるんですけど、それだけECで扱いたいデータの量が半端ないって事なんですよね。

従来からよくある【1アイテム 1テーブル1レコード制】のシステムに反旗を翻して作成した自作システム。
速度ではやはり従来型の方に分がありますよね。検索が楽ですから。
でも、汎用性や利便性はかなりのレベルに仕上がっていると自負しています。

ご興味のある方は是非お問合せください。

さぁ、今日も頑張っていきましょう！