MySQLのパフォーマンスを改善するうえで、「インデックス」は重要な要素の一つです。しかし、MySQLの内部でオプティマイザがどのようにインデックスを選択し、データを処理しているのかを正確に理解しているエンジニアは多くありません。どうすれば、インデックスを適切に使いこなせるのでしょうか。今回は、MySQL運用のスペシャリストとして知られるyoku0825さんに、インデックスの活用方法について解説していただきました。
B-treeインデックスの基本構造
― 今回のインタビューでは、MySQLのインデックスの知見を詳細に伺っていきます。
インデックスを適切に活用するためには、その仕組みを理解することが重要です。そこでまずは、MySQLのInnoDBで最も基本となるB-treeインデックスの構造からお話しします。内部的にどのような構造になっているか、イメージを掴んでいきましょう。
今回は、MySQL公式サイトからダウンロードできるサンプルとして、 worldデータベースのcityテーブルを見ていきます。このテーブルは、都市の国コードを示すCountryCodeカラムにインデックスが張られています。
mysql> SHOW CREATE TABLE city\G
*************************** 1. row ***************************
Table: city
Create Table: CREATE TABLE `city` (
`ID` int NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
B-treeインデックスの実体は「多段の連想配列」のようなものだと考えると、理解しやすくなります。キーがインデックス対象のカラムデータで、値がそれに紐づく行のソート済みのプライマリーキーのリスト、という構造をイメージしてください。
たとえば、このcityテーブルのKEY CountryCode (CountryCode)というインデックスは、内部的にはCountryCodeをキーとして、それに対応する行のプライマリーキー（この場合はID）のリストを保持しています。この構造を、クエリで仮想的に表現すると以下のようになります。
mysql> SELECT countrycode, GROUP_CONCAT(id ORDER BY id) FROM city GROUP BY 1 ORDER BY 1, 2 LIMIT 10;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| countrycode | GROUP_CONCAT(id ORDER BY id) |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ABW | 129 |
| AFG | 1,2,3,4 |
| AGO | 56,57,58,59,60 |
| AIA | 61,62 |
| ALB | 34 |
| AND | 55 |
| ANT | 33 |
| ARE | 64,65,66,67,68 |
| ARG | 69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,125 |
| ARM | 126,127,128 |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
このように、'ARE'というキーに対して[64, 65, 66, 67, 68]というプライマリーキーのリストが紐づいています。WHERE countrycode = 'ARE'というクエリが実行されると、MySQLはまず連想配列を引くように、このインデックス構造から'ARE'のキーに対応する最初のデータ（IDが64）を見つけます。
mysql> SELECT * FROM city WHERE countrycode = 'ARE';
+----+-----------+-------------+-----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-----------+-------------+-----------+------------+
| 64 | Dubai | ARE | Dubai | 669181 | <-- 最初のアクセスでここにたどり着く
| 65 | Abu Dhabi | ARE | Abu Dhabi | 398695 | ->next
| 66 | Sharja | ARE | Sharja | 320095 | ->next
| 67 | al-Ayn | ARE | Abu Dhabi | 225970 | ->next
| 68 | Ajman | ARE | Ajman | 114395 | ->next
+----+-----------+-------------+-----------+------------+
この後、MySQLは内部的にカーソルを一つずつ進めていきます。このカーソルはnext()やprev()のようなメソッドを持つイテレータだと考えてください。最初にID64のデータにたどり着いた後、next()を呼び出して65、さらにnext()で66へと順番にデータを辿ります。そして、'ARE'に属する最後のデータ（68）を読み込んだ後、もう一度next()を呼び出し、次のキー（この場合は'ARG'）に到達した時点で'ARE'の範囲から外れたと判断し、探索を終了します。
この動きは、MySQLのセッションステータスで確認できます。
mysql> FLUSH STATUS;
mysql> SELECT * FROM city WHERE countrycode = 'ARE';
-- (結果は上記と同じ)
mysql> SHOW SESSION STATUS LIKE 'handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
...
| Handler_read_key | 1 |
| Handler_read_next | 5 |
...
+----------------------------+-------+
Handler_read_keyが1回、これが最初の連想配列アクセスです。そしてHandler_read_nextが5回ですね。つまり、4回のnextを呼び出して'ARE'に属するデータにアクセスした後、さらに1回nextを叩いて、'ARE'ではなくなったことを知ってbreakしています。これが、B-treeインデックスの基本的な動きになります。
「カーディナリティの高い順」という定石が当てはまらないケース
― Handler_read_next の回数が、パフォーマンスを考える上で重要になるのですか？
そうです。Handler_read_nextで読み取る行数が増えるほど、パフォーマンスは悪化します。ですから、インデックスを設計する際には「いかにこの回数を減らせるか」が重要になります。
よく言われるのが、「カーディナリティの高いカラムにインデックスを張りましょう」というセオリーです。カーディナリティとは、つまり値の種類の多さですね。カーディナリティが高ければ、一つの値に該当する行数が少なくなる傾向があり、結果としてHandler_read_nextの回数が減るので効率が良くなる、という理屈です。
ただ、この“カーディナリティ”という言葉には少し注意が必要です。単純に数値が高ければ良い、というわけではありません。
たとえば、日本の都道府県について考えてみます。「WHERE 都道府県 = '沖縄県'」のように対象が少ないクエリであれば、インデックスは効率的に機能します。一方で「WHERE 都道府県 = '東京都'」とすると、日本の人口の1割以上がヒットしてしまい、たとえインデックスを使っても大量の行をスキャンするためコストは高くなります。
もう一つ、Webサービスでよくある例を挙げてみます。仮にユーザーが100万人いれば、ユーザーIDのカーディナリティは100万なので非常に高いです。これだけ見れば、ユーザーIDでアイテムを検索するのは効率的に思えますよね。
しかし、ここに特定のヘビーユーザーが一人いて、その人だけが大量のアイテムを持っている場合などが厄介です。そのユーザーのアイテムボックスを表示しようとすると、大量のHandler_read_nextが発生してしまい、EXPLAINではインデックスを正しく使えているように見えるのに実際のクエリはそれほど速くない、という事態に陥ります。ライトユーザーの検索は速いのに、ヘビーユーザーの検索だけが遅い、という現象が起きます。
このように、重要なのはカーディナリティの数値だけではなく、データの分布を把握することです。たとえ特定カラムのカーディナリティが高くても、データに極端な偏りがある場合には、そのカラムにインデックスを張るのが最善ではない場合があります。
ユーザーの動向やそれに基づくデータの偏り、実際にレスポンスタイムが悪くなっているクエリなどを一番よく知っているのはアプリケーション開発者です。ぜひDBAはアプリケーション開発者と積極的に話をして、彼らが持つ現場の知識を引き出してください。
B-treeのソート済み構造を活用して、ORDER BYを高速化
― 先ほど、B-treeインデックスが「ソート済みのデータ構造」であることを解説していただきました。この知識は、どのような場面で活きてくるのでしょうか？
その構造を理解していると、ORDER BY句のパフォーマンス改善に応用できます。ORDER BYは、インデックスがうまく使われないとUsing filesortという追加のソート処理が発生し、パフォーマンスのボトルネックになりやすいです。
先ほどのcityテーブルで、CountryCodeで絞り込んだ結果をPopulation（人口）の順で並べ替える、というケースを見てみます。
mysql> EXPLAIN SELECT * FROM city WHERE countrycode = 'ARE' ORDER BY population;
+...| Extra |
+...| Using index condition; Using filesort |
+...|---------------------------------------+
ExtraカラムにUsing filesortが表示されました。これは、CountryCodeのインデックス（CountryCode順にはソートされている）を使って5件のデータを絞り込んだ後、その結果をメモリ上などで改めてPopulationの順に並べ替えていることを意味します。この処理は、対象行数が増えるほどコストが大きくなります。
― なるほど。LIMIT句で取得件数を絞れば、このソート処理は軽くなりませんか？
それが、よくある落とし穴なんです。
mysql> EXPLAIN SELECT * FROM city WHERE countrycode = 'ARE' ORDER BY population LIMIT 3;
+...| Extra |
+...| Using index condition; Using filesort |
+...|---------------------------------------+
LIMIT 3をつけてもUsing filesortは消えません。なぜならMySQLは、絞り込んだ5行全てを一度ソートし終えなければ、どの3行が人口の少ないトップ3なのかを判断できないからです。この場合のLIMITは、MySQL内部のソートの処理コストを減らせておらず、最終的にクライアントへ返す行数を減らす効果しかありません。
― どうすれば、filesort を回避できるのでしょうか？
ここで、B-treeインデックスが「ソート済みのデータ構造」であることが効いてきます。つまり、最初からクエリが求める順番にソートされたインデックスを用意すると良いです。countrycodeとpopulationを含む複合インデックスを作成してみます。
mysql> ALTER TABLE city ADD KEY idx_test(countrycode, population);
このインデックスは、B-treeの内部でまずcountrycodeでソートされ、同じcountrycodeの中ではpopulationでソートされた状態でデータが格納されます。この状態で再度EXPLAINを実行すると、結果が変わります。
mysql> EXPLAIN SELECT * FROM city WHERE countrycode = 'ARE' ORDER BY population LIMIT 3;
+...| Extra |
+...| Using index condition |
+...|-----------------------+
Using filesortが消えました。MySQLは、この新しいインデックスの中から'ARE'の先頭を探します。そのレコードは、インデックスの構造上、'ARE'の中で最もpopulationが小さいものだと保証されています。そこからnext()を呼び出して2番目、3番目のレコードを順に辿るだけで、ソート済みの結果が3件得られます。これが、B-treeインデックスの構造を理解して設計することで、パフォーマンスを改善できる例です。
そして、この例が示す大事な教訓は「DBAがテーブルの定義だけを見て、良いインデックスを設計するのは不可能」だということです。「特定のアプリで、人口順でデータを並べ替えることが必須」といった要件は、アプリケーション開発者しか分かりません。
だからこそ、先ほどの話にも通じますが、DBAとアプリケーション開発者が普段から密にコミュニケーションを取り、「どのような検索が、どれくらいの頻度で行われるのか」といった情報を把握することが不可欠です。インデックスの設計は、データベースの知識とアプリケーションの知識、その両輪があってこそ最適化できるものだと言えます。
未使用インデックスの特定と安全な管理
― サービスが長く運用されると、使われなくなったインデックスが徐々に溜まっていきます。これらの管理方法についても教えてください。
インデックスは読み込みを高速化する一方で、書き込み（INSERT/UPDATE/DELETE）の際には追加の処理コストが発生します。そのため、使われていないインデックスは定期的に見つけて削除することが重要です。
MySQL 5.7以降では、performance_schemaやsysスキーマを利用して、どのインデックスが使われているかを統計情報から確認できます。
mysql> use sys;
mysql> SELECT * FROM schema_unused_indexes;
+--------------------+--------------------------------------+-------------+
| object_schema | object_name | index_name |
+--------------------+--------------------------------------+-------------+
| employees | dept_manager | dept_no |
...
| world | city | CountryCode |
| world | city | idx_test |
...
+--------------------+--------------------------------------+-------------+
schema_unused_indexesビューは、mysqldの起動以降、一度もアクセスされていないインデックスの一覧を表示してくれます。稼働期間が十分長ければ、ここに表示されるインデックスは不要である可能性が高いと判断できます。
ただし、ここには大きな罠があります。リードレプリカを用いている場合です。たとえば、マスターでは書き込みしか行われず、リードレプリカで参照用のクエリが実行されている場合、マスター側でこのビューを見ると、参照用のインデックスが「未使用」としてリストされてしまいます。これを見てインデックスを削除してしまうと、リードレプリカ側のパフォーマンスが大きく劣化する大事故につながります。私もこれでやらかしたことがあります（笑）。
― 恐ろしいですね……。
こうした、インデックスを削除する際のリスクを低減するために、MySQL 8.0から不可視インデックス（Invisible Index）という機能が導入されました。
mysql> ALTER TABLE city ALTER INDEX idx_test INVISIBLE;
このようにインデックスをINVISIBLEに設定すると、インデックスデータ自体は削除されませんが、オプティマイザの選択肢（possible_keys）から除外されます。つまり、インデックスが存在しないのと同じ状態になります。この状態で一定期間様子を見て、問題が発生しなければ安全にDROP INDEXできます。もし問題が発生した場合は、VISIBLEにするだけですぐに元に戻せます。
インタビュー中、リアルタイムでクエリを書きながら解説するyoku0825さん
インデックスは「あらかじめ追加」か「必要になってから追加」か
― インデックスは参照を高速化する一方で、更新処理のオーバーヘッドにもなります。あらかじめ多めにインデックスを張っておくべきか、それとも必要最低限にして後から足すべきか、どちらが望ましいでしょうか？
これはDBAによっても考えが分かれますが、私は可能な限り先にインデックスを張っておくべきだと考えています。
その最大の理由はロックです。InnoDBでは、更新処理の際に対象行をロックしますが、この時、インデックスを使って対象行を探すプロセスはSELECTと全く同じで、しかも探索するのに使ったread, nextの通った行全てにロックを置いていきます。つまり、インデックスの効きが悪いと、本来ロックする必要のない行までスキャンしてしまい、それらをロックしてしまう可能性があります。
ロックの範囲が意図せず広がると、ロック待ちによるシステムの並列処理性能が低下し、デッドロックによるロールバックの原因にもなります。パフォーマンスだけでなく、システムの安定性を保つためにも、インデックスによる的確な絞り込みは非常に重要です。
確かに更新処理のオーバーヘッドは存在します。しかし、インデックスを一つ追加することで増える処理時間は、過去に計測したベンチマークでは、およそ2%ほどでした。これはあくまで参考値ですが、たとえば1回のINSERTに200ミリ秒かかっていた処理が、204ミリ秒になるといった具合です。このミリ秒単位の差がユーザーの体感に影響するかというと、インデックスが数個増えた程度ではまずわかりません。
だからこそ私は、「インデックスを張ることで増える書き込みコストよりも、得られる利点のほうがずっと大きいので、あらかじめ張っておこう」というスタンスにしています。ただし、これはアプリケーションの特性やテーブルの役割によって変わるので、常に絶対というわけではありません。自分たちのユースケースに合わせて設計方針を立ててください。
― 参考になる知見が盛りだくさんでした。今回はありがとうございました！
撮影：本多香