ゲームを題材に学ぶ 内部構造から理解するMySQL
第3回 実行計画を見てSQLの構造を理解しよう
本記事は,
本記事のテーマを,
実行計画はSQL理解の大きな手がかり
SQLはほかの手続き型言語と大幅に違います。そのため,
しかし,
実行計画はSQL文の前に
実行計画が変わるSQL
実行計画とは何かというと,
さっそく,
リスト1 CountryCodeが'B'より大きいデータを取得するSQL
SELECT * FROM city WHERE CountryCode > 'B' -- 結果は図1
リスト2 CountryCodeが'Z'より大きいデータを取得するSQL
SELECT * FROM city WHERE CountryCode > 'Z' -- 結果は図2
図では大きな違いに見えないので,
リスト3 図1をJavaのようなソースコードで表現
for(Row row:city){ if(row.CountryCode > "B"){ retRows.add(row); } } return retRows;
一方,図2をソースコードに直したリスト4では,
リスト4 図2をJavaのようなソースコードで表現
rowKeys = city.Indices.CountryCode.getRangeKeys("Z"); for(RowID rowID:rowKeys){ retRows.add(city.getRow(rowID)); } return retRows;
ここで第1回の図4のデータ構造を思い出し,リスト3とリスト4でヒットする件数が逆になったとき,
「CountryCode > 'B'」
cityテーブルの件数は,
つまり,
DBエンジンがSQL実行前に行う内部処理
- SQL文のシンタックス
(文法) チェック - DBオブジェクト
(テーブルなど) の存在チェック - DBオブジェクトに対する権限チェック
- カラム,
ファンクション, 引数などの存在, 型チェック - パース処理
(文法上の整理) - テーブル・
インデックスの統計情報から実行計画を作成 - マシン語に翻訳
(コンパイル)
統計情報の更新
MySQLでは統計情報の更新は,
- 作りなおすタイミングはインデックスが全体の数%書き換わったとき
- ランダムにインデックスの数ページを選んで統計情報を作成する
MySQLの統計情報の更新方法は,
逆に,
- リスト2の条件'Z'を,
Mから順にM, N, O, P, Q……と, 変化させてインデックスを使うようになるところを探す - 第1回で紹介した
「https:// dev. mysql. com/ doc/ index-other. html」 からダウンロードした 「world. sql」 のSQL文を流して, worldデータベースを作りなおす - 再度,
M~順に変化させてインデックスを使うようになるところを探す
本稿を書くために筆者が行った複数回の実験では,
実行計画が変わらないSQL
worldデータベースで,リスト5,リスト6の実行計画を取るとそれぞれ図3,図4になります。
リスト5 cityからJOINするSQL
SELECT * FROM city INNER JOIN country ON city.CountryCode = country.Code;
リスト6 countryからJOINするSQL
SELECT * FROM country INNER JOIN city ON country.Code = city.CountryCode;
JOINする順番というのは,
これまで見てきたとおり,
- 注1
- カーディナリティとはデータの分散具合のこと。データが分散していればいるほど
「カーディナリティが高い」 という。逆に、データがあまり分散していないと 「カーディナリティが低い」 という。たとえば 「性別」 は通常は2種類、 「都道府県」 は47種類しかないので、どちらもカーディナリティは低い。
オプティマイザーはプログラマ, SQLは詳細設計書
オプティマイザーはプログラマにあたり,
また,
記事中で紹介した書籍
-
SQLの苦手を克服する本 データの操作がイメージできれば誰でもできる
本書は,SQLの文法は学んだもののSQLに苦手意識を持っているITエンジニアのための書籍です。 複雑なSQLを読める/書けるようになるには,データベースの表をカタマリで...