第110回　Invisible Indexesを使って気軽にチューニングを始めてみる

使用されず役に立たないインデックスを定義するのは、SQLアンチパターンの1つ「インデックスショットガン」として知られています。使用されていないインデックスを定義するのは、ディスク容量を圧迫して、さらに更新コストも掛かるという良いこと無しな状態です。

ただ実際には、あなたが使用されていないインデックスを見つけたとしても、安易にドロップするのは非常に危険です。ドロップするのは時間がかかりませんが、インデックスを再構築するまでには時間がかかります。

もしも万が一そのインデックスが使用されているクエリが存在するとしたら、その時点から障害につながってしまう可能性があります。ドロップはしたくないけど、使わないようにして影響を確認したい……、今回はそんな時に便利なMySQL 8.0の新機能「Invisible Indexes」を使ってインデックスを外した時の影響を調べてみましょう。

検証環境

今回はDockerHub上のMySQLイメージを利用して行います。バージョンは8.0.18を使用しています。

$ docker run --name michibushin -v "/tmp:/tmp"  -e MYSQL_ROOT_PASSWORD=my-secret-pw -d -p 3306:3306  -d mysql:8.0.18 --secure-file-priv=/tmp

上記の操作で起動して、以下のようにMySQLに接続をしていきます。

$ mysql -h0.0.0.0  -uroot -pmy-secret-pw

検証用のテーブルとデータ

検証用のデータとしては、第2回　MySQLにはじめてのデータを入れてみるからKEN_ALL.CSVを利用して作成していきます。

第2回と異なる点としては、テーブルの構造を改変して以下の様にします。zip_codeとold_zipcodeの複合インデックス、そしてzip_code単体でのインデックスを追加で作成しています。

$ cat ken_all.sql
CREATE DATABASE zipcode CHARACTER SET utf8mb4;
CREATE TABLE zipcode.zipcode(
       code varchar(12) NOT NULL,
       old_zipcode varchar(5) NOT NULL,
       zip_code varchar(7) NOT NULL,
       prefecture_kana varchar(255) NOT NULL,
       city_kana varchar(255) NOT NULL,
       town_kana varchar(255) NOT NULL,
       prefecture varchar(128) NOT NULL,
       city varchar(128) NOT NULL,
       town varchar(128) NOT NULL
     ) DEFAULT CHARACTER SET= utf8mb4;
ALTER TABLE zipcode.zipcode ADD INDEX index_zipcode_on_zip_code_and_old_zip_code(zip_code, old_zipcode);
ALTER TABLE zipcode.zipcode ADD INDEX index_zipcode_on_zip_code(zip_code);
$ mysql -h0.0.0.0  -uroot -pmy-secret-pw < ken_all.sql

データベースにアクセスしてみて、以下のクエリを打って同じ結果が得られていれば大丈夫です。

$ mysql -h0.0.0.0  -uroot -pmy-secret-pw zipcode
mysql> show create table zipcode;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| zipcode | CREATE TABLE `zipcode` (
  `code` varchar(12) NOT NULL,
  `old_zipcode` varchar(5) NOT NULL,
  `zip_code` varchar(7) NOT NULL,
  `prefecture_kana` varchar(255) NOT NULL,
  `city_kana` varchar(255) NOT NULL,
  `town_kana` varchar(255) NOT NULL,
  `prefecture` varchar(128) NOT NULL,
  `city` varchar(128) NOT NULL,
  `town` varchar(128) NOT NULL,
  KEY `index_zipcode_on_zip_code_and_old_zip_code` (`zip_code`,`old_zipcode`),
  KEY `index_zipcode_on_zip_code` (`zip_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

無駄なインデックスを特定する

現在使われていないインデックスを特定する必要があります。今回は第9回　pt-query-digestを使って遅いクエリーを発見するでも紹介したPercona Toolkitのpt-duplicate-key-checkerを使う方法と、MySQL5.7から追加されたsys.schema_unused_indexesを利用する方法を紹介したいと思います。

pt-duplicate-key-checker

こちらは以前Percona Toolkitの1つで、これを使うと名前の通り重複したキーやインデックスを発見することができます。Percona Toolkitのインストール方法に関しては解説しません。aptやyum、Homebrewといったソフトウェアで簡単にインストールができるので、OSやディストリビューションに合わせてインストールをしてください。公式のインストールガイドはこちらになります。

というわけで実行をしてみましょう。

$ pt-duplicate-key-checker --host 0.0.0.0 --user root --password my-secret-pw --database zipcode
# ########################################################################
# zipcode.zipcode
# ########################################################################

# index_zipcode_on_zip_code is a left-prefix of index_zipcode_on_zip_code_and_old_zip_code
# Key definitions:
#   KEY `index_zipcode_on_zip_code` (`zip_code`)
#   KEY `index_zipcode_on_zip_code_and_old_zip_code` (`zip_code`,`old_zipcode`),
# Column types:
#     `zip_code` varchar(7) not null
#     `old_zipcode` varchar(5) not null
# To remove this duplicate index, execute:
ALTER TABLE `zipcode`.`zipcode` DROP INDEX `index_zipcode_on_zip_code`;

# ########################################################################
# Summary of indexes
# ########################################################################

# Size Duplicate Indexes   30
# Total Duplicate Indexes  1
# Total Indexes            2

結果の部分が2つあり、どこに重複するキーやインデックスが存在するのかというのが上部に表示され、下部にはサマリーが書かれています。サマリーから、インデックスが2個あって、そのうち1個が重複していることがわかります。上部からは詳細なメッセージと、そのインデックスを除外するためのDDLが出力されています。

また、この結果をファイルとして取っておいてMySQLクライアントで実行することで、重複するキーを一気に除外することも可能です。

sys.schema_unused_indexes

こちらはMySQL 5.7から導入されたviewで、MySQLが起動してから一度も使用されていないindexを表示してくれます。

 mysql> select * from sys.schema_unused_indexes;
+---------------+-------------+--------------------------------------------+
| object_schema | object_name | index_name                                 |
+---------------+-------------+--------------------------------------------+
| zipcode       | zipcode     | index_zipcode_on_zip_code_and_old_zip_code |
| zipcode       | zipcode     | index_zipcode_on_zip_code                  |
+---------------+-------------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

まだクエリを一度も発行していないので、2件とも検知されていることがわかります。そこで、試しに下記のクエリを実行してみましょう。

mysql> select * from zipcode where zip_code like '105%' limit 1;
+-------+-------------+----------+--------------------+--------------+-----------------------------------------------+------------+--------+--------------------------------+
| code  | old_zipcode | zip_code | prefecture_kana    | city_kana    | town_kana                                     | prefecture | city   | town                           |
+-------+-------------+----------+--------------------+--------------+-----------------------------------------------+------------+--------+--------------------------------+
| 13103 | 105         | 1050000  | ﾄｳｷｮｳﾄ             | ﾐﾅﾄｸ         | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ                               | 東京都     | 港区   | 以下に掲載がない場合           |
+-------+-------------+----------+--------------------+--------------+-----------------------------------------------+------------+--------+--------------------------------+
1 row in set (0.00 sec)

実行後再度確認をしてみると、以下のようになるはずです。

mysql> select * from zipcode where zip_code like '105%' limit 1;
+-------+-------------+----------+--------------------+--------------+-----------------------------------------------+------------+--------+--------------------------------+
| code  | old_zipcode | zip_code | prefecture_kana    | city_kana    | town_kana                                     | prefecture | city   | town                           |
+-------+-------------+----------+--------------------+--------------+-----------------------------------------------+------------+--------+--------------------------------+
| 13103 | 105         | 1050000  | ﾄｳｷｮｳﾄ             | ﾐﾅﾄｸ         | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ                               | 東京都     | 港区   | 以下に掲載がない場合           |
+-------+-------------+----------+--------------------+--------------+-----------------------------------------------+------------+--------+--------------------------------+
1 row in set (0.01 sec)

気をつける点としては、後から追加したindexが使用されるようになって不要になっているindexがあっても、上手く検知ができない事に注意が必要です。また、十分な期間を置いて実施しないと、月次のバッチ等で使用しているindexを誤って無効にしてしまう可能性があるので注意しましょう。

Invisible Indexesを試してみる

さてここまでで不要なインデックスに関して簡単に知ることができました。さぁドロップをしてみましょう！…… といきたいところですが、冒頭に書いたとおり、開発環境や検証環境なら良いと思いますが、本番環境でいきなりDROP INDEXするのはかなり恐ろしいです。

というわけで、一旦使用されなくなるInvisible Indexesを使ってみましょう。

mysql> explain select * from zipcode where zip_code like '105%' limit 1;
+----+-------------+---------+------------+-------+----------------------------------------------------------------------+--------------------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                                                        | key                                        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------------------------------------------------------------+--------------------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | zipcode | NULL       | range | index_zipcode_on_zip_code_and_old_zip_code,index_zipcode_on_zip_code | index_zipcode_on_zip_code_and_old_zip_code | 30      | NULL |  369 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------------------------------------------------------+--------------------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

この時点の状態では、index_zipcode_on_zip_code_and_old_zip_codeが選択されていそうという事がわかります。ここで以下のクエリを実行し、invisibleな状態にしてみます。

mysql> ALTER TABLE zipcode alter index index_zipcode_on_zip_code_and_old_zip_code invisible;

この状態で再度explainを取ってみましょう。

mysql> explain select * from zipcode where zip_code like '105%' limit 1;
+----+-------------+---------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys             | key                       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | zipcode | NULL       | range | index_zipcode_on_zip_code | index_zipcode_on_zip_code | 30      | NULL |  369 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

ということでオプティマイザーから選択されない状態になりました。

続いて元に戻してみましょう。

mysql> ALTER TABLE zipcode alter index index_zipcode_on_zip_code_and_old_zip_code visible;
mysql> explain select * from zipcode where zip_code like '105%' limit 1;
+----+-------------+---------+------------+-------+----------------------------------------------------------------------+--------------------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                                                        | key                                        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------------------------------------------------------------+--------------------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | zipcode | NULL       | range | index_zipcode_on_zip_code_and_old_zip_code,index_zipcode_on_zip_code | index_zipcode_on_zip_code_and_old_zip_code | 30      | NULL |  369 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------------------------------------------------------+--------------------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

元の状態に即座に直すことができました。使い方は簡単で、visible, invisibleのトグルになっていることはわかっていただけたと思います。

さて、ちょっと不安になることとして、ヒント句を指定している場合にどういった挙動になるか気になると思います。

mysql> ALTER TABLE zipcode alter index index_zipcode_on_zip_code_and_old_zip_code invisible;

一旦インデックスを再びinvisibleにします。その状態で、わざとinvisibleにしたindex_zipcode_on_zip_code_and_old_zip_codeを指定をしてみましょう。

mysql> explain select * from zipcode FORCE INDEX (index_zipcode_on_zip_code_and_old_zip_code)  where zip_code like '105%' limit 1;
ERROR 1176 (42000): Key 'index_zipcode_on_zip_code_and_old_zip_code' doesn't exist in table 'zipcode'

エラーになってしまいました。というわけで、ヒント句を使用していたとしても実は使われていたという事にはなりません。ちなみに、存在しないindexを指定するとエラーになってしまうので、逆にヒント句を多用している場合には、事前に書き換えておく必要があるので注意をしましょう。

まとめ

今回は重複したキーやインデックスを調査し、一時的に使用しないようにする方法について説明しました。これから年末に向けて大掃除の時期が近づいておりますが、皆様のMySQLのインデックスの掃除にご利用ください。