MySQL メモ6 OrderBY句狙いのIndex

MySQLでソート処理が実行されるタイミングは、最終的な取得結果をソートする。
そのため、最終的な取得結果件数が多い場合はソート処理もその分遅くなってしまう。

OrderBy句に指定したカラムにIndexが登録されている場合は、そのカラムはIndexによってソートされた状態となる。
上記の場合は、ソート処理が不要のため、Indexありなしではパフォーマンスが大きく異なる。

まずはテストデータを作成。
下記SQLでシンプルなユーザ情報テーブルを作成。

CREATE TABLE user_test(user_id int primary key auto_increment, name varchar(256),sex int, mail varchar(256),role_id int);

下記SQLでテストデータ(100万ちょい)を作成。

CREATE TABLE user_test(user_id int primary key auto_increment, name varchar(256),sex int, mail varchar(256),role_id int);
insert into user_test () values ();
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
insert into user_test (user_id) select 0 from user_test;
update user_test set name = concat('会員', user_id), sex = CEIL(RAND() * 2), mail = CONCAT(SUBSTRING(MD5(RAND()), 1, 10),'@example.com'), role_id = CEIL(RAND() * 10);
select count(*) from user_test;
->1048576

まずはOrderBy句に指定したrole_idカラムにIndexが"ない"状態で実行計画を確認。

explain select * from user_test order by role_id limit 5;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	user_test	​(NULL)​	ALL	​(NULL)​	​(NULL)​	​(NULL)​	​(NULL)​	1047493	100	Using filesort

Extraのカラムで"Using filesort"が実行されていることがわかる。

role_idにIndexを追加する。

ALTER TABLE user_test ADD INDEX index_role_id(role_id);

OrderBy句に指定したrole_idカラムにIndexが"ある"状態で実行計画を確認。

explain select * from user_test order by role_id limit 5;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	user_test	​(NULL)​	index	​(NULL)​	index_role_id	5	​(NULL)​	5	100	​(NULL)​

Extraカラムで"Using filesort"が実行されてないことがわかる。
rowsカラムの件数も大きくことなる。