このブログ記事では、MySQLインデックスに手入れする基本的なステップについて見ていこうと思います。
データベースは、インデックス次第でハイパフォーマンスにも、役立たずで遅くて大変にもなりうることはご存知でしょう。インデックスは、時々手入れをする価値がある非常に重要なものです。それでは、何をチェックすればよいのでしょうか？順不同ですが、確認すべき点を挙げてみます。
1. 使われていないインデックス
sysスキーマで、使われていないインデックスをとても簡単に見つけられます。 schema_unused_indexes ビューを使いましょう。
mysql> select * from sys.schema_unused_indexes;
+---------------+-----------------+-------------+
| object_schema | object_name | index_name |
+---------------+-----------------+-------------+
| world | City | CountryCode |
| world | CountryLanguage | CountryCode |
+---------------+-----------------+-------------+
2 rows in set (0.01 sec)
このビューは performance_schema.table_io_waits_summary_by_index_usage テーブルを元にしていて、このテーブルを使うにはパフォーマンススキーマ、 events_waits_current コンシューマー、 wait/io/table/sql/handler インストゥルメントを有効にする必要があります。 PRIMARY インデックス(key)は無視されます。
これらをまだ有効にしていないなら、以下のクエリーを実行しましょう。
update performance_schema.setup_consumers set enabled = 'yes' where name = 'events_waits_current';
update performance_schema.setup_instruments set enabled = 'yes' where name = 'wait/io/table/sql/handler';
ドキュメントにはこう書かれています。
このビューのデータがあなたのワークロードを間違いなく反映するようにしたいなら、ビューを使う前にしかるべき時間、サーバーを動かしておくべきです。
しかるべき時間とは、以下を表しています。
- 週ごとのジョブがある？最低1週間待ちましょう。
- 月次レポートがある？最低1ヶ月待ちましょう。
急がなくていいのです！そして、使われていないインデックスが見つかったら、削除しましょう。
2. 重複したインデックス
ここでは2つのやり方があります。
- pt-duplicate-key-checker
- sysスキーマのschema_redundant_indexesビュー
pt-duplicate-key-checkerは、Percona Toolkitの一部です。基本的な使い方は以下のようにわかりやすいものです。
[root@e51d333b1fbe mysql-sys]# pt-duplicate-key-checker
# ########################################################################
# world.CountryLanguage
# ########################################################################
# CountryCode is a left-prefix of PRIMARY
# Key definitions:
# KEY `CountryCode` (`CountryCode`),
# PRIMARY KEY (`CountryCode`,`Language`),
# Column types:
# `countrycode` char(3) not null default ''
# `language` char(30) not null default ''
# To remove this duplicate index, execute:
ALTER TABLE `world`.`CountryLanguage` DROP INDEX `CountryCode`;
# ########################################################################
# Summary of indexes
# ########################################################################
# Size Duplicate Indexes 2952
# Total Duplicate Indexes 1
# Total Indexes 37
schema_redundant_indexesビューは、sysスキーマをインストールしてしまえば簡単に使えます。違いは、こちらはinformation_schema.statisticsテーブルを元にしているという点です。
mysql> select * from schema_redundant_indexesG
*************************** 1. row ***************************
table_schema: world
table_name: CountryLanguage
redundant_index_name: CountryCode
redundant_index_columns: CountryCode
redundant_index_non_unique: 1
dominant_index_name: PRIMARY
dominant_index_columns: CountryCode,Language
dominant_index_non_unique: 0
subpart_exists: 0
sql_drop_index: ALTER TABLE `world`.`CountryLanguage` DROP INDEX `CountryCode`
1 row in set (0.00 sec)
ここでも冗長なインデックスを見つけたら、削除してしまいましょう。
3. 足りない可能性のあるインデックス
パフォーマンススキーマのstatementsサマリーテーブルには、いくつか面白いフィールドがあります。今回のケースでは、 NO_INDEX_USED (ステートメントがインデックスを使わずにテーブルスキャンをしているということ)と NO_GOOD_INDEX_USED ("1"がステートメントに対するいいインデックスが見つからなかった、 "0"はそれ以外)の2つが比較的重要です。
sysスキーマには、 performance_schema.events_statements_summary_by_digest を元にしたビュー statements_with_full_table_scans があり、テーブルスキャンをしたステートメントを標準化したものが全て入っていて便利です。
例としては以下のような情報です。
mysql> select * from world.CountryLanguage where isOfficial = 'F';
55a208785be7a5beca68b147c58fe634 -
746 rows in set (0.00 sec)
mysql> select * from statements_with_full_table_scansG
*************************** 1. row ***************************
query: SELECT * FROM `world` . `Count ... guage` WHERE `isOfficial` = ?
db: world
exec_count: 1
total_latency: 739.87 us
no_index_used_count: 1
no_good_index_used_count: 0
no_index_used_pct: 100
rows_sent: 746
rows_examined: 984
rows_sent_avg: 746
rows_examined_avg: 984
first_seen: 2016-09-05 19:51:31
last_seen: 2016-09-05 19:51:31
digest: aa637cf0867616c591251fac39e23261
1 row in set (0.01 sec)
このクエリーは、いいインデックスが見つからなかったためインデックスを使っておらず、そのためここにレポートされています。EXPLAINの結果を見てみましょう。
mysql> explain select * from world.CountryLanguage where isOfficial = 'F'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 984
Extra: Using where
"query"の項目には、実際のクエリーではなく概要(フィンガープリントに近い)が表示されている点に注意しましょう。
この場合、 CountryLanguage テーブルには isOfficial 列に対するインデックスが不足しているのが分かります。このインデックスを追加する価値があるかどうかは、あなたが判断する必要があります。
4. マルチカラムインデックスの列の順序
これについては以前Multiple Column index beats Index Mergeという記事でマルチカラムインデックスを使う全てのケースを取り上げ、インデックスヒントを使う場合もあることにも触れました。
しかし、インデックスヒントを使う場合でも、列の順序が影響してくることを忘れないようにしましょう。MySQLは、インデックスの最初の列が最低でもひとつ存在しないと、マルチカラムインデックスを使ってくれません。
例えば以下のようなテーブルを考えてみましょう。
mysql> show create table CountryLanguage\G
*************************** 1. row ***************************
Table: CountryLanguage
Create Table: CREATE TABLE `CountryLanguage` (
`CountryCode` char(3) NOT NULL DEFAULT '',
`Language` char(30) NOT NULL DEFAULT '',
`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
`Percentage` float(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (`CountryCode`,`Language`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Language に対するクエリーにはインデックスは使われません。
mysql> explain select * from CountryLanguage where Language = 'English'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 984
Extra: Using where
単純にこれはプライマリーキーの一番左の列ではないからです。クエリーの条件に CountryCode を追加してやれば、インデックスが使われるようになります。
mysql> explain select * from CountryLanguage where Language = 'English' and CountryCode = 'CAN'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
type: const
possible_keys: PRIMARY,CountryCode
key: PRIMARY
key_len: 33
ref: const,const
rows: 1
Extra: NULL
ここではさらに、関連する列の選択性についても考える必要があります。どのような列の順序が望ましいのでしょうか？
この場合、 CountryCode よりも Language の方が選択性が高くなります。
mysql> select count(distinct CountryCode)/count(*), count(distinct Language)/count(*) from CountryLanguage;
+--------------------------------------+-----------------------------------+
| count(distinct CountryCode)/count(*) | count(distinct Language)/count(*) |
+--------------------------------------+-----------------------------------+
| 0.2368 | 0.4644 |
+--------------------------------------+-----------------------------------+
つまりこの場合マルチカラムインデックスを作るなら、 (Language, CountryCode) の順番に作るのがいいということになります。
ソートやグルーピングのことを考えなくていい、つまり検索を最適化するためだけのインデックスなら、選択性の最も高い列をインデックスの一番最初に持ってくるのはいい考えです。最もよく実行するクエリーで可能な限り選択性が高くなるように列を選ぶ必要があります。
さてではこれで十分でしょうか？そうでもありません。テーブルのデータが均等に分散していない特別な場合はどうでしょうか。ひとつの値が他の値よりも圧倒的に多く現れる時は？その場合、インデックスはうまく使えません。平均的なケースでのパフォーマンスが、特別な場合には必ずしも当てはまらないということに注意しましょう。特別な場合というのは、アプリケーション全体のパフォーマンスをダメにしてしまう可能性があります。
まとめ
結論としては、正しいインデックス次第で大きく変わるということです。たまにはインデックスに愛情と思いやりを与えてあげましょう。そうすれば、データベースはとても恩を感じてくれるはずです。
ちなみに全ての例は以下のMySQLとsysスキーマで確認しました。
mysql> select * from sys.version;
+-------------+-----------------+
| sys_version | mysql_version |
+-------------+-----------------+
| 1.5.1 | 5.6.31-77.0-log |
+-------------+-----------------+
Hi Daniel,
どのくらい手入れをすべきかというバランスのいい議論ですね。
ひとつだけ言いたいことがあります。重複しているように見えるインデックスを削除する時には注意しましょう。クエリ内にヒントが使われていると、代わりのインデックスが使われるのではなく、エラーが出力されてしまいます。
先週のManyiとのインタビューでも言いましたが、MySQL 8.0ではこういった場合のためにinvisible indexがサポートされる予定です(まず見えなくして、それから削除できます)。これはこの記事の1.にも役に立つはず。
Morgan
