読者です 読者をやめる 読者になる 読者になる

MySQL の where 狙いと order by 狙い

わたしは参加していないのですが YAPC::Asia Tokyo 2014 で次のような発表があったようです。

このセッションを聞いている人を WHERE gender = 'male' なら ORDER BY を狙った方がいいだろうし、WHERE gender = 'female' なら WHERE を狙った方がいい。

ちょっと試してみます。

次のようなテーブルがあったとします。age は年齢で gender は性別です。

drop table if exists yapcasia;
create table yapcasia (
  id int not null primary key,
  age int not null,
  gender char(6) not null
);

create index idx_yapcasia__age on yapcasia (age);
create index idx_yapcasia__gender on yapcasia (gender);

次のようにデータを入れます。

time seq 10000 |
  mysql test -e "
    truncate table yapcasia;
    load data local infile '/dev/stdin' into table yapcasia (@id)
      set id = @id,
          age = floor(rand() * 40 + 20),
          gender = if(@id % 500, 'male', 'female');
    analyze table yapcasia;
  "

男性が 9980 人に対して、女性が 20 人しかいません。

select count(*) from yapcasia where gender = 'male';
/* 9980 */

select count(*) from yapcasia where gender = 'female';
/* 20 */

性別ごとに年齢の若い順に 5 人列挙します。

select * from yapcasia where gender = 'male' order by age asc limit 5;
+-----+-----+--------+
| id  | age | gender |
+-----+-----+--------+
|  18 |  20 | male   |
|  51 |  20 | male   |
|  75 |  20 | male   |
| 122 |  20 | male   |
| 181 |  20 | male   |
+-----+-----+--------+

select * from yapcasia where gender = 'female' order by age asc limit 5;
+------+-----+--------+
| id   | age | gender |
+------+-----+--------+
| 4000 |  20 | female |
| 4500 |  20 | female |
| 1500 |  22 | female |
| 7500 |  22 | female |
| 3500 |  22 | female |
+------+-----+--------+

実行計画を見てみると、

explain select * from yapcasia where gender = 'male' order by age asc limit 5 \G
/*
           id: 1
  select_type: SIMPLE
        table: yapcasia
         type: index
possible_keys: idx_yapcasia__gender
          key: idx_yapcasia__age
      key_len: 4
          ref: NULL
         rows: 10
        Extra: Using where
*/

explain select * from yapcasia where gender = 'female' order by age asc limit 5 \G
/*
           id: 1
  select_type: SIMPLE
        table: yapcasia
         type: ref
possible_keys: idx_yapcasia__gender
          key: idx_yapcasia__gender
      key_len: 18
          ref: const
         rows: 20
        Extra: Using index condition; Using where; Using filesort
*/

男性を検索するときは ORDER BY 狙いでインデックスが、女性を検索するときは WHERE 狙いでインデックスが使われています。

InnoDB の統計情報はカーディナリティが極端に低い列だと値ごとの統計情報も持っているのでしょうかね?


この例なら yapcasia (gender, age) なインデックスがあれば両狙いになるのは言うまでもありません。

drop index idx_yapcasia__age on yapcasia;
drop index idx_yapcasia__gender on yapcasia;
create index idx_yapcasia__gender_age on yapcasia (gender, age);
analyze table yapcasia;

explain select * from yapcasia where gender = 'male' order by age asc limit 5 \G
/*
           id: 1
  select_type: SIMPLE
        table: yapcasia
         type: ref
possible_keys: idx_yapcasia__gender_age
          key: idx_yapcasia__gender_age
      key_len: 18
          ref: const
         rows: 5078
        Extra: Using where; Using index
*/

explain select * from yapcasia where gender = 'female' order by age asc limit 5 \G
/*
           id: 1
  select_type: SIMPLE
        table: yapcasia
         type: ref
possible_keys: idx_yapcasia__gender_age
          key: idx_yapcasia__gender_age
      key_len: 18
          ref: const
         rows: 20
        Extra: Using where; Using index
*/