YAPC::Asia Tokyo 2014でMySQLのWHERE狙いのキーとORDER BY狙いのキーの話をします

YAPC::Asia Tokyo 2014に応募していたトークを採択していただきました :)
WHERE狙いのキー、ORDER BY狙いのキー - YAPC::Asia Tokyo 2014

たくさんの人に応援していただいていて、本当に感謝しております :)
Talks Social Ranking - YAPC::Asia Tokyo 2014


WHERE狙いって何よとかORDER BY狙いってしゃらっと言ってますが、このへんはフィーリングで呼んでいるだけの造語です。MySQLに詳しい方にはなんとなーく伝わるんじゃないかなと期待していますが、どちらかというと「なんだよそれ造語かよ道理で聞いたこともない」って方に聞いていただきたいなぁと思っていたりします。

たとえば、EXPLAIN(目XPLAINでも可)でtype: ALLになるような(=テーブルスキャンの)クエリーってヤバそうじゃないですか。なんでヤバいかって、テーブルに格納されるレコードの件数に比例して(本当は線形じゃなくもっとヤバい）処理量が増えていきそうなのがある程度みんな「あ、やべっ」って感じになるじゃないですか。

mysql56> show create table Country\G
*************************** 1. row ***************************
       Table: Country
Create Table: CREATE TABLE `Country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` float(3,1) DEFAULT NULL,
  `GNP` float(10,2) DEFAULT NULL,
  `GNPOld` float(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql56> EXPLAIN SELECT Code, Name, Population FROM Country WHERE Continent = 'Asia';
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | Country | ALL  | NULL          | NULL | NULL    | NULL |  239 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

MySQLのworldデータベースからインデックスを全て取っ払ったテーブルを用意します。EXPLAINを取るとtype: ALLでExtra: Using whereです。
これを「Perlのコードっぽく書くと」こんな感じですね。

my @country_table= ({Code => "ABW", Name => "Aruba",       Continent => "North America", .., Population => 103000},
                    {Code => "AFG", Name => "Afghanistan", Continent => "Asia",          .., Population => 22720000},
                    {Code => "AGO", Name => "Angola",      Continent => "Africa",        .., Population => 12878000},
                    ..);

foreach my $row (@country_table)
{
  if ($row->{Continent} eq "Asia")
  {
    printf("Code:%s, Name:%s, Population:%d\n", $row->{Code}, $row->{Name}, $row->{Population});
  }
}

ヤバそうですよね。
これにKEY(Continent) を足すとこんな感じになります。

mysql56> EXPLAIN SELECT Code, Name, Population FROM Country WHERE Continent = 'Asia';
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key       | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | Country | ref  | Continent     | Continent | 1       | const |   51 | Using index condition |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

Perlで表現すると、

my @country_table= ({Code => "ABW", Name => "Aruba",       Continent => "North America", .., Population => 103000},
                    {Code => "AFG", Name => "Afghanistan", Continent => "Asia",          .., Population => 22720000},
                    {Code => "AGO", Name => "Angola",      Continent => "Africa",        .., Population => 12878000});

my %continent_index= (Asia   => [1, 9, 19, ..],
                      Europe => [4, 5, 15, ..],
                      ..);

foreach my $row_num (@{$continent_index{Asia}})
{
    printf("Code:%s, Name:%s, Population:%d\n",
           $country_table[$row_num]->{Code},
           $country_table[$row_num]->{Name},
           $country_table[$row_num]->{Population});
}

MySQLの人からもPerlの人からも怒られそうな感じがしてきましたが、こんな感じの話になる予定です。興味を持っていただけたら幸いです :)