Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
5ヶ月前投稿

MySQLインデックスのお手入れの基本

Percona Database Performance Blogの翻訳。既に運用を始めたデータベースで、インデックスが正しく使われているか、無駄や不足がないかを確認する方法のまとめ記事。クエリをひとつひとつ確認するのではなく、統計情報を元に判断する分かりやすい方法。
原文 Basic Housekeeping for MySQL Indexes - Percona Database Performance Blog (English)
翻訳者 D98ee74ffe0fafbdc83b23907dda3665 doublemarket


このブログ記事では、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 |
+-------------+-----------------+

追記 : Morgan Tocker氏からのコメント

Hi Daniel,

どのくらい手入れをすべきかというバランスのいい議論ですね。

ひとつだけ言いたいことがあります。重複しているように見えるインデックスを削除する時には注意しましょう。クエリ内にヒントが使われていると、代わりのインデックスが使われるのではなく、エラーが出力されてしまいます。

先週のManyiとのインタビューでも言いましたが、MySQL 8.0ではこういった場合のためにinvisible indexがサポートされる予定です(まず見えなくして、それから削除できます)。これはこの記事の1.にも役に立つはず。

Morgan

次の記事
MySQL 8.0 Lab版: MySQLの (再帰)共通テーブル式(CTE)
前の記事
InfluxDB 1.0 GAリリース : 振り返りとこれから

Feed small 記事フィード