MySQLのインデックスは1つのテーブルにつき1つしか使われないとは限らない
タイトルが全てですが、その通り。 MySQLにはインデックスマージというものが存在するということを最近知った。
詳細は以下のドキュメントを参照してほしい。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.4 インデックスマージの最適化
必ずしも使われるわけではないけど、1つのテーブルにつきインデックスは1つしか使われないものと思っていた認識が誤りだった。
create table user( id int, last_name varchar(20), first_name varchar(20), nickname varchar(20) ) ENGINE = InnoDB DEFAULT CHARSET = utf8;
こんな感じのテーブルがあるとしてインデックスを貼ります。
CREATE INDEX idx_first_name ON user(first_name); CREATE INDEX idx_last_name ON user(last_name); CREATE INDEX idx_nickname ON user(nickname);
データを何件か入れて以下のSQLを実行して実行計画を取得する。
explain select * from user where first_name like 'テスト%' or last_name like 'テスト%' or nickname like 'テスト%';
インデックスを貼っていない場合
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 200 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
インデックスを貼っている場合
+------+-------------+-------+-------------+-------------------------------------------+-------------------------------------------+----------+------+------+--------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------------+-------------------------------------------+-------------------------------------------+----------+------+------+--------------------------------------------------------------------------+ | 1 | SIMPLE | user | index_merge | idx_first_name,idx_last_name,idx_nickname | idx_first_name,idx_last_name,idx_nickname | 63,63,63 | NULL | 3 | Using sort_union(idx_first_name,idx_last_name,idx_nickname); Using where | +------+-------------+-------+-------------+-------------------------------------------+-------------------------------------------+----------+------+------+--------------------------------------------------------------------------+
では、複合インデックスだとどうなるか？既存のインデックスを消して以下のインデックスを追加し、確認する
CREATE INDEX idx_multi ON user(first_name, last_name, nickname);
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | user | ALL | idx_multi | NULL | NULL | NULL | 200 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
なるほど。orで検索する場合は、複合インデックスを使っても使われることはなく1つ1つのカラムに対してインデックスを貼ることでインデックスマージが効いてパフォーマンスが上がる可能性が高いということらしい。
今回のケースでは Using sort_union が使われているが公式ページを見ると他にも種類があるようです。
今回の機能はMySQL5以降は有効とのことでMariaDBとMySQL両方で試してみました。こうゆうとき、dockerとか便利ですよね〜と言いたいとこだったけど最新版のMariaDBでエラーが発生して時間かかった。結局バージョンを10.0まで落とすことで解決したけど。
docker-compose.yml
version: '2'
services:
maria:
image: mariadb:10.0
restart: always
volumes:
- ./data/mariadb/:/var/lib/mysql
ports:
- "3307:3306"
expose:
- "3307"
environment:
MYSQL_ROOT_PASSWORD: root
user: "1000:50"
mysql:
image: mysql
restart: always
volumes:
- ./data/mysql:/var/lib/mysql
ports:
- "3308:3306"
expose:
- "3308"
environment:
MYSQL_ROOT_PASSWORD: root
mariadb:10.2 で出ていたエラーは以下の通り。時間があったら調べよう
[Warning] InnoDB: Failed to set O_DIRECT on file./ibdata1;OPEN: Invalid argument, ccontinuing anyway. O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662. [Note] InnoDB: Highest supported file format is Barracuda. [Note] InnoDB: 128 out of 128 rollback segments are active. [Note] InnoDB: Creating shared tablespace for temporary tables [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... [Note] InnoDB: File './ibtmp1' size is now 12 MB. [Note] InnoDB: Waiting for purge to start [Note] InnoDB: 5.7.21 started; log sequence number 1603633 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool [Note] InnoDB: Buffer pool(s) load completed at 180304 4:52:55 [Note] Plugin 'FEEDBACK' is disabled. [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded [Note] Recovering after a crash using tc.log [ERROR] Can't init tc log [ERROR] Aborting
