内部構造から理解するMySQL」をWeb掲載用に再編集したものです。データの操作がイメージできれば誰でもできる』が2019年8月26日に発売されました。本記事と併せてご活用ください。実行計画はSQL理解の大きな手がかりSQLはほかの手続き型言語と大幅に違います。そのため,ほかの手続き型言語と同じ感覚で使うと違和感が大きくなり,アレルギーのように嫌う人も出てきます。最近では,手続き型言語でORMを使うとSQLを意識しないでRDBMSをストレージのように扱うことが可能なため,SQLを理解する前にORMを使ってしまった人の中には,SQLをまったく理解していない人もいるようです。正しい理解をしないまま使うということは非常に危険ですので,SQLとはどういうものかということを実行計画を通して理解しましょう。実行計画はSQL文の前に“⁠EXPLAIN⁠”を付けて実行するか,Workbenchなどのツールを利用すると視覚的に確認することも可能です。本稿ではWorkbenchでの結果(画像)を用いて解説します。実行計画が変わるSQL実行計画とは何かというと,実際に実行されるプログラムの概要を提示したものです。SQLを直接実行できるCPUは存在していませんから,DBエンジンの中のオプティマイザーという機能が,手続き型のソースに翻訳してからコンパイルして実行するという処理を行っています。worldデータベースでリスト1,リスト2の実行計画を取って確認してみましょう。リスト1の実行計画は図1,リスト2の実行計画は図2になります。図では大きな違いに見えないので,それぞれJavaのようなソースコードに直してみます。図1をソースコードに直すとリスト3になります。インデックスを使わずにテーブル全体(第1回の図4の(5)プライマリーキーのリーフ部分)を読みながらデータをフィルタリングする処理になっています。retRows;一方,図2をソースコードに直したリスト4では,インデックス(第1回の図4の(1⁠)⁠~(⁠4⁠)⁠)を利用してフィルタリングしているというように,アルゴリズムが大幅に変更されていることがわかります。retRows;ここで第1回の図4のデータ構造を思い出し,リスト3とリスト4でヒットする件数が逆になったとき,読み込まなければならないページ数をイメージしてみましょう。ヒット率が高い(全レコード数に対してヒットする件数が多い)とき,第1回の図4の(1⁠)⁠~(⁠5)の順でインデックスを読み取ってから実データを探す,ということを繰り返すことは効率的ではなく,最初からすべてのデータ(第1回の図4の(5)だけ)を読み取って,不要なデータを読み飛ばすほうが効率的です。逆にヒット率が低いときはインデックスを使ったほうが効率的になります。'Z'」の条件では,4,079件のうち57件ヒットします。いずれも,効率が良いと考えられるアルゴリズムが選択され構築されていることがわかります。cityテーブルの件数は,現在は4,079件ですが,1,000万件中5,000件がヒットするというような条件のときはインデックスを使います。ヒットするレコード数の絶対値が多い/少ないではなく,全レコード数に対する比率でアルゴリズムが変わるわけです。パラメータを含むSQLがまったく同じであっても,時間経過とともにデータ数とその分散具合が変われば,最終的に実行されるアルゴリズムが変わる可能性があるということです。逆に考えれば,インデックスがあるカラムに抽出条件を1つ加えたSELECT文を実行する場合,DBエンジンはSQL実行前に「どのぐらいデータがヒットするか」を予想しているということがわかります。DBエンジンはヒット率を予想するために事前に統計情報を作っています。SQLの実行時にその統計情報を用いて次のような複雑で大量の内部処理を行います。DBエンジンがSQL実行前に行う内部処理SQL文のシンタックス(文法)チェックDBオブジェクト(テーブルなど)の存在チェックDBオブジェクトに対する権限チェックカラム,ファンクション,引数などの存在,型チェックパース処理(文法上の整理)テーブル・インデックスの統計情報から実行計画を作成マシン語に翻訳(コンパイル)統計情報の更新MySQLでは統計情報の更新は,次のような基準で自動で行われます。作りなおすタイミングはインデックスが全体の数%書き換わったときランダムにインデックスの数ページを選んで統計情報を作成するMySQLの統計情報の更新方法は,Oracleなどに比べるとかなりアバウトです。Oracleでは,統計情報を作ることがたいへん重い処理になるため夜間バッチなどで行っても問題になることがあるぐらいですが,その分実行計画はかなり精緻なものになります。逆に,MySQLでは統計情報の更新が高速である分,非常にアバウトになるため,同じデータでもインデックスを使うか使わないかの閾値はかなり変化します。気になる方は,次の方法で実験すればどのぐらいアバウトか体感できます。リスト2の条件'Z'を,Mから順にM,N,O,P,Q......と,変化させてインデックスを使うようになるところを探す第1回で紹介した「https://dev.mysql.com/doc/index-other.html」からダウンロードした「world.sql」のSQL文を流して,worldデータベースを作りなおす再度,M~順に変化させてインデックスを使うようになるところを探す本稿を書くために筆者が行った複数回の実験では,O~Vの範囲で変化しました。実行計画が変わらないSQLworldデータベースで,リスト5,リスト6の実行計画を取るとそれぞれ図3,図4になります。「⁠countryからJOINするSQL」の実行計画JOINする順番というのは,SQLとしては大幅にロジックが違うように感じるかもしれませんが,cityからJOINしても,countryからJOINしても,実行計画は同じになっています。*」とするとカラムの順番は変わります⁠)⁠。これまで見てきたとおり,オプティマイザーは単純にSQL文を変換しているのではなく,SQL文を解析して,テーブルの件数,使えるインデックス,カーディナリティ注1などから検討し,どのテーブルからJOINしていくか順番まで決めている。アルゴリズムを最適化(オプティマイズ)しているため,使われているテーブルと結合条件が同じであれば同じ実行計画になるわけです。注1カーディナリティとはデータの分散具合のこと。データが分散していればいるほど「カーディナリティが高い」という。逆に、データがあまり分散していないと「カーディナリティが低い」という。47種類しかないので、どちらもカーディナリティは低い。オプティマイザーはプログラマ,SQLは詳細設計書オプティマイザーはプログラマにあたり,SQLは詳細設計書にあたります。オプティマイザーの性格はRDBMSごとに違い,それぞれのバージョンでもまったく違ってきます。RDBMSごとの性格の違いから,向いているシステム,向いていないシステムがあります。RDBMSを選択するときも,本来はそれぞれのオプティマイザーの性格まで含めて検討するべきでしょう。オプティマイザー(プログラマ)は,当然,人間のプログラマにはおよびませんから,それぞれのオプティマイザーに合わせたSQLを書き,必ずレビュー,つまり,実行計画を確認する必要があります。注意する点は,「⁠データ量が変わると実行計画が変わってしまうため,できる限り本番環境に近い環境で行う」ということです。フリーランスのエンジニアを経て,2003年に株式会社ジーワンシステムを創業する。その後,プレイングマネージャーとして多くのシステム開発に従事し,現在ではデータベースを中心としたコンサルティングを行っている。データの操作がイメージできれば誰でもできる本書は,SQLの文法は学んだもののSQLに苦手意識を持っているITエンジニアのための書籍です。2019」を開催しました。DAY1・DAY2の様子を注目セッションとともにお伝えします。LINEのエンジニア3名がイベントに先駆けて見どころを紹介します。コードの安全性・安定性を高める開発サイクル~テスト管理の効率を上げ,脆弱性診断を自動で行う~本連載では,連載「業務を改善する情報共有の仕掛け」を受けつつ,安全性・安定性を加えた開発サイクルについて考えていきます。若者世代から圧倒的な人気を誇るライブ配信&動画投稿コミュニティアプリ「MixChannel」(ミクチャ)の裏側(レコメンドシステムやサーバサイド開発など)について解説していきます。その他の連載CSS3アニメーションでつくるインターフェイス表現CSS3によるアニメーション表現を紹介していきます。その中でも,幅広い読者に応用してもらえるだろうインターフェイスを主なお題とします。MySQL道普請便り本連載では,MySQLを使ったアプリ開発・運用に関するノウハウをご紹介していきます。RecipeUbuntuの強力なデスクトップ機能を活用するための,いろいろなレシピをお届けします。JavaScriptセキュリティの基礎知識JavaScriptに関するセキュリティ上の問題はどこで発生し,どうすれば防ぐことができるのか?について解説していきます。〜システムをきちんと運用するための基礎知識システムは「作って終わり」ではなく,運用の中でさまざまな問題が発生します。問題の発生に備えて事前にどのような対応をしておくべきなのか,問題発生時に何をしなければならないのか,ポイントを解説していきます。きたみりゅうじの聞かせて珍プレーソフトウェア開発の現場で体験したトホホな失敗,思わずうなる珍プレーをきたみりゅうじ氏が四コママンガで紹介。Linuxのメンテナンスの傍ら,Linuxやオープンソースソフトと日々を過ごす著者が,その魅力とつきあい方を,エッセイ風味でお届けします。PRESS特別編集部員,さわやか笑顔のスーパーハカーはまちちゃんとネット大好き14歳わかばちゃんが,毎号,読者の皆さんから寄せられたおたよりを紹介します。皆さんの日頃の悩みにも答えちゃいますよ。]メールでのお知らせメールで次の案内をお送りしております。メールの配信を希望する方は,利用したい項目をチェックしてメールアドレスを入力し,[登録]ボタンをクリックしてください。UPDATES[ほぼ週間に1回]新刊メールマガジン[ほぼ週間に1回]電脳会議の各号案内[ほぼ1ヵ月半に1回]※変更する場合や停止する場合は,登録情報変更ページをご覧ください。※登録いただいたメールアドレスには,そのお知らせ以外,配信いたしません。Ltd.ページ内容の全部あるいは一部を無断で利用することを禁止します。個別にライセンスが設定されている記事等はそのライセンスに従います。