Where狙いのキー、order by狙いのキー

WHERE狙いのキー、
ORDER BY狙いのキー
2014/08/28
yoku0825
YAPC::Asia Tokyo 2014

みんなEXPLAINしてるー？
目XPLAINでも可

Extra: Using where; Using
filesort

でもなんで？
クエリーが遅い時にこいつらが出るのか、
こいつらが出る時にクエリーが遅くなるのか

という話を
コードで例えて
お話ししたいと思います
Perlがへたっぴなのは
大目にみてくだしあ。。

I'm yoku0825
● とある企業のDBA
● オラクれない
● ポスグれない
● マイエスキューエる
● 家に帰ると
● 嫁の夫
● せがれの父
● 馬鹿だからかわいいわけじゃなくて、かわいい
イルカがたまたまバカだった

はじめに
● サンプルデータは MySQLのサンプルデータ
ベース(worldデータベース)からインデック
スを全て取っ払ったものです
● http://dev.mysql.com/doc/index-other.html
● コードはgithubに上げてあります
● https://github.com/yoku0825/yapc_2014
● すごく…ウンコードです…

はじめに
● 原則、MySQLは1つのテーブルにつき同時に1
つのインデックスしか使いません
● Index mergeとかあるけどアレは例外だし狙って
やっても速くなる訳でもないので除外
● 自己結合ジョインは別
● この資料はBKAやICP, MRRとかには対応して
いません
● リードバッファやジョインバッファも非対応
● 雰囲気だけなんとなく感じてください

As A Codeとか言いながら
最初にトランプの話をします。

100枚のトランプの山があります。
何組かのトランプを混ぜたもので
何が何枚入ってるかはわかりません。

この山の中から
スペードのAを探してください

これがテーブルスキャン
SELECT * FROM cards
WHERE mark= 'spade' AND number= 'A';

面倒なので、チートシートを作ります

マーク上から
club 4,13,25,28,31,34,35,36,37,39,43,46,
47,54,62,66,71,74,77,84,85,86,89,9
6,100
diamond 1,5,15,23,24,26,32,33,41,45,48,49,5
1,57,63,65,67,68,73,76,80,81,82,90,
93,95,98
heart 2,3,7,11,12,17,22,27,29,50,52,53,56
,58,64,69,70,72,78,87,92,94
spade 6,8,9,10,14,16,18,19,20,21,30,38,40
,42,44,55,59,60,61,75,79,83,88,91,9
7,99

これがWHERE句を部分的に
インデックスで解決したパターン
SELECT * FROM cards

マーク数字上から
club A 54,77
club 3 74,84
..
heart K 17,22,69
spade 2 16,20
spade 3 21
spade 4 40
spade 5 88,99
..

これがWHERE句を全て
インデックスで解決したパターン
SELECT * FROM cards

このチートシートを使っていいから、
山の中からハートを
KからAに向かって並べておくれ
SELECT * FROM cards
WHERE mark= 'heart'
ORDER BY number DESC;

マーク数字上から
club 1 54,77
club 3 74,84
..
heart A 29,70
..
heart J 53,58
heart Q 50
heart K 17,22,69
..

インデックスとは
ソート済みのデータの複製
異論はあると思う

じゃあコードに行きます

テーブルデータ
$VAR1 = [
{
'population' => '103000',
'region' => 'Caribbean',
'name' => 'Aruba',
'continent' => 'North America',
'code' => 'ABW',
..
},
{
'population' => '22720000',
'region' => 'Southern and Central Asia',
'name' => 'Afghanistan',
'continent' => 'Asia',
'code' => 'AFG',
..
},
{
'population' => '12878000',
'region' => 'Central Africa',
'name' => 'Angola',
'continent' => 'Africa',
'code' => 'AGO',
..
},
..
hashrefを要素にした
arrayrefで表現
display_table_structure.pl

テーブルデータ
$VAR1 = [
{
'name' => 'Aruba',
'code' => 'ABW',
..
},
{
'population' => '22720000',
'code' => 'AFG',
..
},
{
'population' => '12878000',
'name' => 'Angola',
'code' => 'AGO',
..
},
..
my $row= $table->[0];
my $row= $table->[1];
print $row->{name}; ## Afghanistan
display_table_structure.pl

for (my $rownum= 0; $rownum < scalar(@$country_table); $rownum++)
{
my $row= $country_table->[$rownum];
$evaluted++;
if ($row->{continent} eq "Asia")
{
$sorted++;
push(@{$sort_buffer->{$row->{population}}}, $rownum);
}
}
my $sorted_buffer= filesort_single_column($sort_buffer);
foreach my $rownum (@$sorted_buffer)
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
$row->{population});
if (++$count >= 5)
{last;}
}
scan_where_scan_orderby.pl

{
$evaluted++;
{
$sorted++;
}
}
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last;}
}
1行ずつフェッチして

{
$evaluted++;
{
$sorted++;
}
}
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last;}
}
WHERE句で評価

{
$evaluted++;
{
$sorted++;
}
}
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last;}
}
マッチしたら
ソートバッファに詰める

{
$evaluted++;
{
$sorted++;
}
}
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last;}
}
ソートバッファの中身を
クイックソート

{
$evaluted++;
{
$sorted++;
}
}
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last;}
}
先頭の5件を出力したら
そこでループを抜ける

$ ./scan_where_scan_orderby.pl
Maldives Asia 286000
Brunei Asia 328000
Macao Asia 473000
Qatar Asia 599000
Bahrain Asia 617000
Total rows evaluted are 239, sorted are 51.
5行の出力に対して、
239回のWHERE評価と
51行のファイルソート

そりゃあ遅いよ

インデックス作りましょう

インデックス
$VAR1 = [
{
'Africa' => [
2,
17,
19,
..,
]
},
{
'Antarctica' => [
11,
12,
34,
..,
]
},
{
'Asia' => [
1,
7,
9,
..,
カラムの値をkey,
対応する行のプライマリーキーの
arrayrefをvalueにした
hashrefのarrayref
(ソート済みを表現したかった)
display_single_column_index_structure.pl

インデックス
$VAR1 = [
{
'Africa' => [
2,
17,
19,
..,
]
},
{
'Antarctica' => [
11,
12,
34,
..,
]
},
{
'Asia' => [
1,
7,
9,
..,
Continent = 'Africa'な
行IDを取り出すには
$index->[0]->{Africa}
(arrayrefとして取り出す)

インデックス
$VAR1 = [
{
'Africa' => [
2,
17,
19,
..,
]
},
{
'Antarctica' => [
11,
12,
34,
..,
]
},
{
'Asia' => [
1,
7,
9,
..,
$VAR1 = [
{
'name' => 'Aruba',
'code' => 'ABW',
..
},
{
'population' => '22720000',
'code' => 'AFG',
..
},
{
'population' => '12878000',
'name' => 'Angola',
'code' => 'AGO',
..
},
..
$table->[$index->[2]->{Asia}->[0]]

ちょっとつらい
$index->[$n]->{hoge}
ここがマジックナンバー

インデックス
$VAR1 = {
'map' => {
'Oceania' => 5,
'North America' => 4,
'Europe' => 3,
'South America' => 6,
'Asia' => 2,
'Africa' => 0,
'Antarctica' => 1
},
'index' => [
[
2,
17,
19,
..
],
[
11,
12,
34,
..
],
..
ソート済みの順番を
$index->{map}に押し込めた
こっちはarrayrefのarrayrefに

インデックス
$index_num= $index->{map}->{Africa}
$VAR1 = [
{
'name' => 'Aruba',
'code' => 'ABW',
..
},
{
'population' => '22720000',
'code' => 'AFG',
..
},
{
'population' => '12878000',
'name' => 'Angola',
'code' => 'AGO',
..
},
..
$VAR1 = {
'map' => {
'Oceania' => 5,
'North America' => 4,
'Europe' => 3,
'South America' => 6,
'Asia' => 2,
'Africa' => 0,
'Antarctica' => 1
},
'index' => [
[
2,
17,
19,
..
],
[
11,
12,
34,
..
],
..
$rownum_array=
$index->{index}->[$index_num}
foreach my $rownum(@$rownum_array)
{
my $row= $table->[$rownum];
..
}

うわあ。。
ごめんなさい。。

my $index_num = $country_index->{map}->{Asia};
my $rownum_array= $country_index->{index}->[$index_num];
foreach my $rownum (@$rownum_array)
{
$sorted++;
}
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last;}
}
indexed_where_scan_orderby.pl

{
$sorted++;
}
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last;}
}
WHERE句のレンジを
インデックスから取り出す

{
$sorted++;
}
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last;}
}
取り出した行を全部
ソートバッファに詰めて

{
$sorted++;
}
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last;}
}
クイックソートして

{
$sorted++;
}
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last;}
}
ループを抜ける

$ ./indexed_where_scan_orderby.pl
Brunei Asia 328000
Macao Asia 473000
Qatar Asia 599000
Bahrain Asia 617000
WHEREの評価は1回で済むけど
51行をファイルソート

インデックス
$VAR1 = [
{
'0' => [
11,
12,
34,
93,
100,
187,
221
]
},
{
'50' => [
166
]
},
{
'600' => [
38
]
},
..
既にソート済みなので、
先頭から順番に
取り出すだけで良い
[
$table->[11],
$table->[12],
$table->[34],
$table->[93],
$table->[100],
$table->[187],
$table->[221],
$table->[166],
$table->[38],
..
]

my $cardinality= scalar(keys(%{$index->{map}}));
LOOP: for (my $index_num= 0;
$index_num < $cardinality;
$index_num++)
{
my $rownum_array= $index->{index}->[$index_num];
{
$evaluted++;
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last LOOP;}
}
}
}
scan_where_indexed_orderby.pl

$index_num++)
{
{
$evaluted++;
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last LOOP;}
}
}
}
インデックスに沿って
行を取り出して

$index_num++)
{
{
$evaluted++;
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last LOOP;}
}
}
}
WHERE句にマッチするか
判定して

$index_num++)
{
{
$evaluted++;
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last LOOP;}
}
}
}

$ ./scan_where_indexed_orderby.pl
Brunei Asia 328000
Macao Asia 473000
Qatar Asia 599000
Bahrain Asia 617000
ソートのオーバーヘッドはないものの、
79行をWHERE句評価

WHEREとORDER BYを
両方カバーするキー

複合インデックス
$VAR1 = [
{
'Africa' => [
{
'0' => [
100
]
},
{
'6000' => [
188
]
},
..
]
},
{
'Antarctica' => [
{
'0' => [
11,
12,
34,
93,
187
]
}
]
},
.. display_double_column_index_structure.pl

$VAR1 = [
{
'Africa' => [
{
'0' => [
100
]
},
{
'6000' => [
188
]
},
..
]
},
{
'Antarctica' => [
{
'0' => [
11,
12,
34,
93,
187
]
}
]
},
..
my $african= $index->[0]->{Africa};
my $african_6000_people= $african->[1]->{6000};
foreach my $rownum (@$african_6000_people)
{
my $row= $table->[$rownum];
..
}
display_double_column_index_structure.pl

$VAR1 = {
'map' => {
'Oceania' => {
'83000' => 12,
'3862000' => 22,
'235000' => 19,
'self' => 5,
..
},
'North America' => {
'154000' => 14,
'21000' => 4,
..
},
}
},
'index' => [
[
[
100
],
[
188
],
..

$VAR1 = {
'map' => {
'Oceania' => {
'83000' => 12,
'3862000' => 22,
'235000' => 19,
'self' => 5,
..
},
'154000' => 14,
'21000' => 4,
..
},
}
},
'index' => [
[
[
100
],
[
188
],
..
マジックナンバーよけをしたら
かなりカオス
selfを予約語にしてるのが
ものすごくイケてない

$VAR1 = {
'map' => {
'Oceania' => {
'83000' => 12,
'3862000' => 22,
'235000' => 19,
'self' => 5,
..
},
'154000' => 14,
'21000' => 4,
..
},
}
},
'index' => [
[
[
100
],
[
188
],
..
my $index_num=
$index->{map}->{Oceania}->{83000};
my $rownum_array=
$index->{index}->[$index_num];
foreach ..

$VAR1 = {
'map' => {
'Oceania' => {
'83000' => 12,
'3862000' => 22,
'235000' => 19,
'self' => 5,
..
},
my $index_nums_hash=
$index->{map}->{Oceania}->{self};
'154000' => 14,
'21000' => 4,
..
},
}
},
'index' => [
[
[
100
],
[
188
],
..
foreach my $index_num
(values(%$index_nums_hash))
..
Foreach ..

＼カオス！／
すいませんすいません。。

WHEREとORDER BYを
mysql56> ALTER TABLE Country ADD KEY index_continent_population(continent, population);
-> FROM Country
+----+-------------+---------+------+----------------------------+----------------------------+---------+-------+------
+-------------+
|
+----+-------------+---------+------+----------------------------+----------------------------+---------+-------+------
+-------------+
| 1 | SIMPLE | Country | ref | index_continent_population | index_continent_population | 33 | const | 51 | Using
where |
+----+-------------+---------+------+----------------------------+----------------------------+---------+-------+------
+-------------+
* WHERE句のレンジをインデックスから取り出し
* インデックスに沿って行を取り出し
* 5件データが揃ったらループから抜ける

WHEREとORDER BYを
my $index_num = $country_index->{map}->{Asia}->{self};
my $index_range= $country_index->{index}->[$index_num];
LOOP: foreach my $rownum_array (@$index_range)
{
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last LOOP;}
}
}
indexed_where_indexed_orderby.pl

WHEREとORDER BYを
{
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last LOOP;}
}
}
WHERE句のレンジを
インデックスから取り出す

WHEREとORDER BYを
{
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last LOOP;}
}
}
インデックスに沿って
行を取り出して

WHEREとORDER BYを
{
{
printf("%st%st%dn",
$row->{name},
$row->{continent},
if (++$count >= 5)
{last LOOP;}
}
}

MySQLはJOINが遅い( ｷﾘｯ

スキャンジョイン
mysql56> EXPLAIN SELECT Name, Language, Population, Percentage
-> FROM Country INNER JOIN CountryLanguage ON Country.Code= CountryLanguage.CountryCode
-> WHERE Country.continent = 'Asia'
-> ORDER BY Percentage LIMIT 5;
+----+-------------+-----------------+------+---------------+------+---------+------+------
+----------------------------------------------------+
|
+----+-------------+-----------------+------+---------------+------+---------+------+------
+----------------------------------------------------+
| 1 | SIMPLE | CountryLanguage | ALL | NULL | NULL | NULL | NULL | 984 | Using temporary; Using filesort
|
| 1 | SIMPLE | Country | ALL | NULL | NULL | NULL | NULL | 239 | Using where; Using join buffer
(Block Nested Loop) |
+----+-------------+-----------------+------+---------------+------+---------+------+------
+----------------------------------------------------+
2 rows in set (0.00 sec)
* 外側のテーブルから1行データをフェッチして
* WHEREとONのカラムで内側のテーブルを評価して
ソートバッファに詰め込み
* 外側のテーブルから次の1行をフェッチして…を繰り返し
* クイックソートして先頭から5件取り出す

for (my $language_rownum= 0;
$language_rownum < scalar(@$language_table);
$language_rownum++)
{
my $language_row= $language_table->[$language_rownum];
for (my $country_rownum= 0;
$country_rownum < scalar(@$country_table);
$country_rownum++)
{
my $country_row= $country_table->[$country_rownum];
$evaluted++;
if ($language_row->{countrycode} eq $country_row->{code} &&
$country_row->{continent} eq "Asia")
{
$sorted++;
push(@{$sort_buffer->{$language_row->{percentage}}},
[$language_rownum, $country_rownum]);
}
}
}
ファイルソート以降は略
scan_join_scan_where_scan_orderby.pl

$language_rownum++)
{
$country_rownum++)
{
$evaluted++;
{
$sorted++;
}
}
}
外側のテーブルから
1行ずつフェッチ

$language_rownum++)
{
$country_rownum++)
{
$evaluted++;
{
$sorted++;
}
}
}
内側のテーブルでも
1行ずつフェッチして

$language_rownum++)
{
$country_rownum++)
{
$evaluted++;
{
$sorted++;
}
}
}
評価＆ソートバッファ

$ ./scan_join_scan_where_scan_orderby.pl
United Arab Emirates Hindi 2441000 0.000000
Bahrain English 617000 0.000000
Japan Ainu 126714000 0.000000
Kuwait English 1972000 0.000000
Lebanon French 3282000 0.000000
評価する行の数が倍倍ゲェム

直感の赴くまま
SELECT Name, Language, Population, Percentage
FROM Country INNER JOIN CountryLanguage ON Country.Code= CountryLanguage.CountryCode
WHERE Country.continent = 'Asia'
ORDER BY CountryLanguage.Percentage LIMIT 5;
mysql56> ALTER TABLE Country ADD KEY index_continent(continent);
mysql56> ALTER TABLE CountryLanguage
-> ADD KEY index_countrycode_percentage(countrycode, percentage);
これは残念ながらWHERE狙いのキーになる。
わかりますん。

my $country_index_num = $country_index->{map}->{Asia};
my $country_rownum_array= $country_index->{index}->[$country_index_num];
foreach my $country_rownum (@$country_rownum_array)
{
$evaluted++;
my $language_index_num = $language_index->{map}->{$country_row->{code}}->{self};
my $language_index_range= $language_index->{index}->[$language_index_num];
foreach my $language_rownum_array (@$language_index_range)
{
foreach my $language_rownum (@$language_rownum_array)
{
$sorted++;
[$country_rownum, $language_rownum]);
}
}
}
indexed_join_indexed_where_scan_orderby.pl

{
$evaluted++;
{
{
$sorted++;
}
}
}
外側のテーブルを
WHEREで刈り込んで

JOIN de WHERE狙いのキーその１
{
$evaluted++;
{
{
$sorted++;
}
}
}
刈り込んだ外側を
1行ずつキーにして
内側テーブルを刈り込む

{
$evaluted++;
{
{
$sorted++;
}
}
}
内側まで刈り込んだら
ソートバッファに詰める

$ ./indexed_join_indexed_where_scan_orderby.pl
United Arab Emirates Hindi 2441000 0.000000
Bahrain English 617000 0.000000
Japan Ainu 126714000 0.000000
Kuwait English 1972000 0.000000
Lebanon French 3282000 0.000000
スキャンジョインよりよっぽどいいけど
そんなに効率が良いわけではなさそう

percentageをインデックスに入
れてるのにWHERE狙いのキー？

Let's プリントデバッグ

{
$evaluted++;
{
{
$sorted++;
printf("* %st%dt%s => %st%fn",
$country_row->{name},
$country_row->{population},
$country_row->{code},
$language_row->{language},
$language_row->{percentage});
}
}
}
verbose_indexed_join_indexed_where_scan_orderby.pl

* Afghanistan 22720000 AFG => AFG Balochi 0.900000
* Afghanistan 22720000 AFG => AFG Turkmenian 1.900000
* Afghanistan 22720000 AFG => AFG Uzbek 8.800000
* Afghanistan 22720000 AFG => AFG Dari 32.100000
* Afghanistan 22720000 AFG => AFG Pashto 52.400000
* United Arab Emirates 2441000 ARE => ARE Hindi 0.000000
* United Arab Emirates 2441000 ARE => ARE Arabic 42.000000
* Armenia 3520000 ARM => ARM Azerbaijani 2.600000
* Armenia 3520000 ARM => ARM Armenian 93.400000
* Azerbaijan 7734000 AZE => AZE Armenian 2.000000
* Azerbaijan 7734000 AZE => AZE Lezgian 2.300000
* Azerbaijan 7734000 AZE => AZE Russian 3.000000
* Azerbaijan 7734000 AZE => AZE Azerbaijani 89.000000
* Bangladesh 129155000 BGD => BGD Garo 0.100000
* Bangladesh 129155000 BGD => BGD Khasi 0.100000
* Bangladesh 129155000 BGD => BGD Santhali 0.100000
* Bangladesh 129155000 BGD => BGD Tripuri 0.100000
* Bangladesh 129155000 BGD => BGD Marma 0.200000
* Bangladesh 129155000 BGD => BGD Chakma 0.400000
* Bangladesh 129155000 BGD => BGD Bengali 97.700000
* Bahrain 617000 BHR => BHR English 0.000000
* Bahrain 617000 BHR => BHR Arabic 67.700000
* Brunei 328000 BRN => BRN English 3.100000
* Brunei 328000 BRN => BRN Chinese 9.300000
* Brunei 328000 BRN => BRN Malay-English 28.800000
* Brunei 328000 BRN => BRN Malay 45.500000
..

* Afghanistan 22720000 AFG => AFG Balochi 0.900000
* Afghanistan 22720000 AFG => AFG Turkmenian 1.900000
* Afghanistan 22720000 AFG => AFG Uzbek 8.800000
* Afghanistan 22720000 AFG => AFG Dari 32.100000
* Afghanistan 22720000 AFG => AFG Pashto 52.400000
* United Arab Emirates 2441000 ARE => ARE Hindi 0.000000
* United Arab Emirates 2441000 ARE => ARE Arabic 42.000000
* Armenia 3520000 ARM => ARM Azerbaijani 2.600000
* Armenia 3520000 ARM => ARM Armenian 93.400000
* Azerbaijan 7734000 AZE => AZE Armenian 2.000000
* Azerbaijan 7734000 AZE => AZE Lezgian 2.300000
* Azerbaijan 7734000 AZE => AZE Russian 3.000000
* Azerbaijan 7734000 AZE => AZE Azerbaijani 89.000000
* Bangladesh 129155000 BGD => BGD Garo 0.100000
* Bangladesh 129155000 BGD => BGD Khasi 0.100000
* Bangladesh 129155000 BGD => BGD Santhali 0.100000
* Bangladesh 129155000 BGD => BGD Tripuri 0.100000
* Bangladesh 129155000 BGD => BGD Marma 0.200000
* Bangladesh 129155000 BGD => BGD Chakma 0.400000
* Bangladesh 129155000 BGD => BGD Bengali 97.700000
* Bahrain 617000 BHR => BHR English 0.000000
* Bahrain 617000 BHR => BHR Arabic 67.700000
* Brunei 328000 BRN => BRN English 3.100000
* Brunei 328000 BRN => BRN Chinese 9.300000
* Brunei 328000 BRN => BRN Malay-English 28.800000
* Brunei 328000 BRN => BRN Malay 45.500000
..
こっちがCountry
こっちがCountryLanguage

確かにソートが必要になる

ORDER BY狙いのキーを使うには
どちらが外部表で
どう結合されるかをイメージ

* 0.000000 Hindi ARE => ARE United Arab Emirates 2441000
* 0.000000 English BHR => BHR Bahrain 617000
* 0.000000 Ainu JPN => JPN Japan 126714000
* 0.000000 English KWT => KWT Kuwait 1972000
* 0.000000 French LBN => LBN Lebanon 3282000
* 0.000000 English MDV => MDV Maldives 286000
* 0.000000 Balochi OMN => OMN Oman 2542000
* 0.000000 Urdu QAT => QAT Qatar 599000
* 0.000000 Portuguese TMP => TMP East Timor 885000
* 0.000000 Sunda TMP => TMP East Timor 885000
* 0.000000 Soqutri YEM => YEM Yemen 18112000
* 0.100000 Garo BGD => BGD Bangladesh 129155000
* 0.100000 Khasi BGD => BGD Bangladesh 129155000
* 0.100000 Santhali BGD => BGD Bangladesh 129155000
* 0.100000 Tripuri BGD => BGD Bangladesh 129155000
* 0.100000 English JPN => JPN Japan 126714000
* 0.100000 Philippene Languages JPN => JPN Japan 126714000
* 0.100000 Chinese KOR => KOR South Korea 46844000
* 0.100000 Chinese PRK => PRK North Korea 24039000
* 0.200000 Marma BGD => BGD Bangladesh 129155000
* 0.200000 Dong CHN => CHN China 1277558000
* 0.200000 Puyi CHN => CHN China 1277558000
* 0.200000 Chinese JPN => JPN Japan 126714000
* 0.300000 Paiwan TWN => TWN Taiwan 22256000
* 0.400000 Chakma BGD => BGD Bangladesh 129155000
* 0.400000 Mongolian CHN => CHN China 1277558000
..
こっちがCountry
verbose_indexed_join_indexed_where_indexed_orderby.pl
こっちがCountryLanguage

* 0.000000 Hindi ARE => ARE United Arab Emirates 2441000
* 0.000000 English BHR => BHR Bahrain 617000
* 0.000000 Ainu JPN => JPN Japan 126714000
* 0.000000 English KWT => KWT Kuwait 1972000
* 0.000000 French LBN => LBN Lebanon 3282000
* 0.000000 English MDV => MDV Maldives 286000
* 0.000000 Balochi OMN => OMN Oman 2542000
* 0.000000 Urdu QAT => QAT Qatar 599000
* 0.000000 Portuguese TMP => TMP East Timor 885000
* 0.000000 Sunda TMP => TMP East Timor 885000
* 0.000000 Soqutri YEM => YEM Yemen 18112000
* 0.100000 Garo BGD => BGD Bangladesh 129155000
* 0.100000 Khasi BGD => BGD Bangladesh 129155000
* 0.100000 Santhali BGD => BGD Bangladesh 129155000
* 0.100000 Tripuri BGD => BGD Bangladesh 129155000
* 0.100000 English JPN => JPN Japan 126714000
* 0.100000 Philippene Languages JPN => JPN Japan 126714000
* 0.100000 Chinese KOR => KOR South Korea 46844000
* 0.100000 Chinese PRK => PRK North Korea 24039000
* 0.200000 Marma BGD => BGD Bangladesh 129155000
* 0.200000 ここでソーDong ト済みCHN であ=> っCHN て
China 1277558000
* 0.200000 Puyi CHN => CHN China 1277558000
* 0.200000 Chinese JPN => JPN Japan 126714000
* 0.300000 Paiwan TWN => TWN Taiwan 22256000
* 0.400000 Chakma BGD => BGD Bangladesh 129155000
* 0.400000 Mongolian CHN => CHN China 1277558000
..
verbose_indexed_join_indexed_where_indexed_orderby.pl
ほしいから
こっち向きに
結合してほしい

JOIN de ORDER BY狙いのキー
mysql56> ALTER TABLE Country ADD KEY index_code_continent(code, continent);
mysql56> ALTER TABLE CountryLanguage ADD KEY index_percentage(percentage);
-> FROM CountryLanguage JOIN Country ON Country.Code= CountryLanguage.CountryCode
+----+-------------+-----------------+-------+----------------------+----------------------+---------
+-----------------------------------------+------+-----------------------+
| rows | Extra |
+----+-------------+-----------------+-------+----------------------+----------------------+---------
+-----------------------------------------+------+-----------------------+
| 1 | SIMPLE | CountryLanguage | index | NULL | index_percentage | 4 | NULL
| 5 | NULL |
| 1 | SIMPLE | Country | ref | index_code_continent | index_code_continent | 36 |
world.CountryLanguage.CountryCode,const | 1 | Using index condition |
+----+-------------+-----------------+-------+----------------------+----------------------+---------
+-----------------------------------------+------+-----------------------+
* 外側のテーブルはORDER BY狙いのキー
** ↑はただのJOINなれど、STRAIGHT_JOINした方がいい。
* 1行ごとにWHEREとONのカラムで評価
* 合計5行マッチしたら終了

my $language_cardinality= scalar(keys(%{$language_index->{map}}));
LOOP: for (my $language_index_num= 0;
$language_index_num < $language_cardinality;
$language_index_num++)
{
my $language_rownum_array= $language_index->{index}->[$language_index_num];
{
my $country_index_num_first= $country_index->{map}->{$language_row->{countrycode}}->{self};
my $country_index_num_second= $country_index->{map}->{$language_row->{countrycode}}->{Asia};
if (!(defined($country_index_num_second)))
{next;}
my $country_rownum_array=
$country_index->{index}->[$country_index_num_first]->[$country_index_num_second];
{
printf("%st%st%dt%fn",
if (++$count >= 5)
{last LOOP;}
}
}
}
indexed_join_indexed_where_indexed_orderby.pl

{
{
{next;}
{
if (++$count >= 5)
{last LOOP;}
}
}
}
外側のテーブルには

{
{
{next;}
{
if (++$count >= 5)
{last LOOP;}
}
}
}
内側のテーブルは

● もちろん両方狙えるインデックスを作っていく
のが最良。
● ただし必ずしも一番良いキーでWHEREとORDER BY
を両方狙い打ちできるとは限らない。
● どっちがどういう動作になるかがイメージできれ
ば、打ち分けるのはそう難しくない
● ちゃんと狙ったクエリーとそうでないクエリーは
100倍くらい性能が違うこともザラ。

まとめ
● WHERE句で十分絞り込める場合はWHERE狙い
● たとえばWHERE Continent = 'Moo'(マッチ0件)
だったらWHERE狙い。ORDER BY狙いだと5件揃わ
ないのでテーブルをまるまるスキャンする。
● WHERE句がほとんど機能しないような場合は
ORDER BY狙い
● 同じカラム同じカーディナリティーでも、値によっ
て本当は違う。
● このセッションを聞いている人をWHERE gender =
'male'ならORDER BYを狙った方がいいだろう
し、WHERE gender = 'female'ならWHEREを狙った
方がいい。

愚痴
● 本当はそのあたりの按配をオプティマイザー氏
が上手くやってくれると嬉しい。
● Oracle DBは上手くやってくれるらしい(って聞い
た。ホントかどうかは知らない)
● MySQLのオプティマイザーが残念なのはInnoDBの
統計情報のせいもある。
– InnoDBの統計情報はサンプリング(デフォルトでは1イ
ンデックスあたり8ページ)
● 8ページ= 128kB、テーブルサイズがどれだけあろうとも。
– 俺たちが「MySQLのオプティマイザー氏はバカ」なんて
言ってられるのは、「そのカラムの偏りが(設計上や
サービス上)どうなりそうか」という高精度な予測を
持っているから。

「何とかとMySQLは使いよう」
楽しいMySQLライフを！

Where狙いのキー、order by狙いのキー

More Related Content

Where狙いのキー、order by狙いのキー