この記事は、
適切なインデックスを設計する
インデックスの調整によるクエリの高速化は、RDBMSを使用する際の数あるチューニングテクニックの中でも最もお手軽なものです。テーブルのカラムの定義を変えるわけではないので、クエリの結果に違いが生じず、アプリケーションを変更する必要性がないからです。適切なインデックスを付与するだけでチューニングが済むというのは極めて効率的です。それでは適切なインデックスとはどのようなものでしょうか。本記事では、まずインデックスを設計する際に重要なポイントを解説します。
インデックスとSQL構文
「どのカラムの組み合わせに対してインデックスを作成すべきか」
どれだけRDBMSに熟練した人であっても、テーブル定義だけを見て必要なインデックスを洗い出すことができる人はいません。経験から当てずっぽうで予測することはできますが、確実に当てるのは至難の業です。なぜなら、どのインデックスが必要になるかはクエリを見なければわからないからです。ですので、インデックスを設計する場合にはまずそのテーブルに対してどのようなクエリを実行するかを知る必要があります。
大切なのは、クエリから検索条件にどのようなカラムが含まれているかを判断することです。その情報がない状態で必要なインデックスを設計するのは、的を見ずに矢を射るようなものです。運が良ければ当たるかもしれませんが、そのような行動は無責任だと筆者は感じています。
そして、SQLを見て必要なインデックスを判断するには、実行計画が正しくインデックスを利用できるように、インデックスがどのように使われるか、解決されるかを知識として知っておくことが重要です。ここでは、MySQLのSQL構文がどのようにインデックスを使っているかについて見ていきたいと思います。
単一のテーブルに対するシンプルなSELECT
ここで言う単一のテーブルに対するシンプルなSELECTとは、JOINやUNION、サブクエリ、ORDER BY、GROUP BYといった構文の利用がなく、下記の書式で表されるものを指しています。ビューや関数の利用もないものとします。
SELECT {select_list} FROM {テーブル名} WHERE {where_condition}
このようなSELECTでは、WHERE句の検索条件だけがインデックスによる恩恵を受けます。Software Design2024年6月号の第1特集 第3章でも解説しましたが、こういったケースではマルチカラムインデックスを最大限活用するのが望ましいです。マルチカラムインデックスは左端のカラムから順番に等価比較で指定すれば、それに続くカラムに対して範囲検索をしたときにインデックスが利用できます。これは大事な基本になりますので覚えておいてください。
また、ここで重要なことは、左端のプレフィックスとして検索条件指定するには、ANDで結合しないといけないという点です。たとえば次のクエリは(col1, col2, col3)というインデックスがあっても利用できません。
SELECT * FROM tbl_name WHERE col1=10 OR col2=100;
ORの場合でも、複数の異なるインデックスのカラムに対する検索条件であれば、インデックスマージという最適化手法によってインデックスを用いた検索が可能なケースがあります。たとえば、(col1, col2, col3)というインデックスの他に、(col2)というインデックスがあれば、先ほどのクエリはインデックスマージを適用できるでしょう。col1はマルチカラムインデックスの左端なのでインデックスが利用可能ですし、col2は単体のインデックスが存在します。インデックスマージが適用される場合にもマルチカラムインデックスを利用可能である点に注意してください。そうでないケースと同様に、それぞれのインデックスに対して左端のカラムから順にANDで等価比較を行う必要があります。
ORDER BY
ORDER BYによってソートされたカラムをインデックスで解決するには、インデックスエントリの並んでいる順序とソート結果の順序を等しくする必要があります。また、WHERE句に検索条件がある場合は、マルチカラムインデックスを用いて等価比較を行っているカラムを左端から順に含めるといった対応も必要になります。詳しい内容については特集第3章の
典型的な例として次のようなクエリがあります。(col1, col2, col3)というインデックスの利用を想定しています。
SELECT * FROM t WHERE col1=1 AND col2=10 AND col3>100 ORDER BY col3;
SELECT * FROM t WHERE col1=1 AND col2=10 ORDER BY col3;
左端のプレフィックスから等価比較をANDで結び、その次のカラムで範囲検索とソートを行うというパターンです。範囲検索を省略した場合には、そのカラムがとり得る値の全域が検索対象範囲ということになります。
GROUP BY
GROUP BY、いわゆる集約関数においてもインデックスを利用できます。集約関数はその性質上多数の行を読み取る必要がありますが、その対象をインデックスに絞ることで(col1, col2)というインデックスに対するルースインデックススキャンで解決が可能です。
SELECT col1, MAX(col2) FROM tbl_name GROUP BY col1;
ルースインデックススキャンが利用できない場合は、インデックスをスキャンする必要があります。そのような実行計画はルースインデックススキャンと対比してタイトインデックススキャンと呼びます。タイトインデックススキャンの場合であってもカバリングインデックスにできれば、比較的高速にクエリを実行できるでしょう。たとえば次のクエリは(col1, col2, col3)というインデックスがあればタイトインデックススキャンで実行されます。
SELECT col2, MAX(col3) FROM tbl_name WHERE col1=1000 GROUP BY col2;
GROUP BYを解決するのにインデックスがまったく利用できない場合にはテンポラリテーブルが用いられます。たとえば上のクエリがインデックスで解決できない場合には、MySQLはcol2とcol3を含むテンポラリテーブルを作成し、グループであるcol2で行をソートされた状態で格納してから、テンポラリテーブルを順番に読み取りcol2のグループごとに集計を行うという処理をします。テンポラリテーブルはディスクI/
余談ですが、実はSELECT DISTINCTの構文も一種のGROUP BYと見なすことができます。次の2つのクエリは等価です。オプティマイザはこれらのクエリが等価であることを知っており、GROUP BYに対してとり得る実行計画をSELECT DISTINCTにも適用しますので覚えておきましょう。
SELECT DISTINCT col1, col2, col3 FROM tbl_name;
SELECT col1, col2, col3 FROM tbl_name GROUP BY col1, col2, col3;
結合（JOIN）
JOINでもインデックスの利用は重要です。とくにテーブルが、JOINにおいて最初にアクセスされる駆動表tが内部表になったときに(col1, col2)のインデックスが利用されます。
SELECT * FROM other_table JOIN t ON other_table.c1 = t.col2 WHERE t.col1=10;
SELECT * FROM other_table JOIN t ON other_table.c1 = t.col1 WHERE t.col2=100;
ON句の条件はWHERE句の条件と可換です。どちらに置いても結果は同じで、ON句の条件とWHERE句の条件をANDで結んだときと同じように考えることができます。上の2つのクエリは、いずれもインデックスを使ってtを検索できます。
もしtが駆動表になった場合には、WHERE句のほうにある検索条件だけが検索に利用されます。そのため、(col1, col2)のインデックスを利用可能なのは1つめのクエリだけです。
相関サブクエリ
相関サブクエリ、とくにINサブクエリでは、インデックスの使われ方に注意してください。次のような相関サブクエリがあるとします。
SELECT * FROM t1 WHERE t1.col1 IN (SELECT col2 FROM t2 WHERE t2.col1=10);
このINサブクエリは、多くのRDBMSのオプティマイザによって、次のようなEXISTSサブクエリに変換された上で実行されます。IN句の比較対象になっているt1.をサブクエリに移動し、サブクエリが返すt2.と直接比較する形に変えただけですので、この書き換えの結果は等価になります。
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.col1=10 AND t2.col2=t1.col1);
サブクエリ内でインデックスを利用できるようにするほかに、EXISTSあるいはINサブクエリにはさらに別の最適化手法も適用することが可能です。
通常のINNER JOINは、駆動表から1行取得し、その行とマッチする行を内部表から探し出します。マッチする行が内部表に複数行ある場合、JOINの結果もその複数行が反映されます。しかし、セミジョインの場合は内部表に何件マッチする行があったとしても、JOINの結果は1行だけになります。つまりこれはまさにEXISTSサブクエリをJOINに置き換えて実行するための特殊なJOINということになります。
セミジョインは特殊ではあるもののあくまでもJOINの一種ですので、JOINするテーブルの順序の入れ替えが可能です。この順序の入れ替えの効果は非常に大きく、適用可能な実行計画の探索範囲が大きく広がり、より高速にクエリを実行できる可能性が高くなります。また、JOINの一種であるがゆえに、種々のJOINに対する最適化が可能です。たとえば、適用可能なインデックスがない場合にテンポラリテーブルにサブクエリ部を実体化
なお、セミジョインによる最適化が採用されているかどうかはEXPLAINを見ることでわかります。セミジョインはJOINなので、実行計画のselect_がSIMPLEになります。
インデックス設計のポイント
ここからは、インデックス設計において考慮しておきたいポイントをまとめていきます。
カーディナリティ
インデックスが有効に働くためには、インデックスを作成する対象のカラムの値が適度にバラけている必要があります。値に変化がなく、同じ値が連続して格納されているようなインデックスは検索の効率がよくありません。どれだけ値がバラけているかを示す尺度をカーディナリティと言います。
カーディナリティが最も高くなるのは、すべての値が一意であるケースです。反対に、カーディナリティが最も低くなるのは、すべての値が同じ場合です。すべての値が同じカラムに対してインデックスを作成する意味は皆無です。
よって、インデックスを作成する場合はカーディナリティがどの程度なのかを考慮することが重要です。基本的にはカーディナリティが高いカラムを対象にインデックスを作成する必要があります。と言ってもカーディナリティは数値化された指標ではありません。漠然とカーディナリティが高いか低いかをインデックス設計時に吟味し、インデックス設計の判断材料にします。
たとえば二値しか持たないカラムなどは、カーディナリティが低いため、インデックスに含める意味はほとんどありません。ただし、値のばらつきに圧倒的な偏りがあり、ごく少ないほうの値を持つ行を検索するというような場合には検索の高速化に寄与しますので、一考する価値はあるでしょう。
カバリングインデックス
B＋ツリーインデックスにおいて、同じ階層のノード同士は連結リストでつながっています。そのため、範囲検索やスキャンにより順次行を読み取っていくような操作では、連結リストを順にたどっていくことになります。クラスタードインデックスでは、データがリーフノードに一緒に収められているため、この操作は非常に効率的です。しかし、セカンダリインデックスにおいてはそうではありません。セカンダリインデックスを検索して行データを返すには、クラスタードインデックスを再度検索する必要があります。セカンダリインデックスとクラスタードインデックスの往復により、多くのランダムアクセスが生じることになります。ほかのRDBMS製品のヒープ表の場合にも、テーブル本体へのアクセスが生じることになるのでランダムアクセスが生じます。
単体の行を取得するだけであればこのコストは無視できるケースがほとんどですが、範囲検索やスキャンで多くの行を取得する場合には何度もセカンダリインデックスとクラスタードインデックス
MySQLが採用するInnoDBのセカンダリインデックスには主キーの値が含まれています。そのため、主キーに含まれているカラムは、セカンダリインデックスの定義に含まれていなくても暗黙的にそのセカンダリインデックス上で値を取得できます。カバリングインデックスを検討する際には、InnoDBにおいてはセカンダリインデックスの定義に主キーのカラムは含めなくてよいということを覚えておきましょう。
インデックスを利用する際の注意点
インデックスを利用する際の注意点として、カラムの値を直接比較しなければならないというものがあります。インデックスはカラムの値そのものを使用して構築されているので、カラムの値を加工してしまうとインデックスを検索することはできません。たとえば次のクエリでは、もしhire_カラムにインデックスがあったとしても、そのインデックスを利用できません。hire_カラムは関数の引数として利用されており、直接的にほかの値と比較されていないからです。
SELECT * FROM employees WHERE DATEDIFF(NOW(), hire_date) < 365;
hire_カラムのインデックスがあるとして、このクエリがインデックスを利用できるようにするには、次のように書き換える必要があるでしょう。これなら計算した結果の値とhire_カラムを比較しているので、このカラムにインデックスがあれば利用可能です。
SELECT * FROM employees WHERE hire_date > DATE_ADD(NOW(), INTERVAL -365 DAY);
どうしてもそのような書き換えができないという場合には関数インデックスを利用するという手があります。これは関数や演算を適用した結果の値に対して作成したインデックスのことです。MySQLのEmployeesサンプルデータベースには含まれていませんが、JSON型のデータを用いている場合には出番が多いでしょう。たとえば、MySQLのリファレスマニュアルでは次のような定義のインデックスが紹介されています。
CREATE TABLE employees (
data JSON,
INDEX ((CAST(data->>'$.name' AS CHAR(30)) COLLATE utf8mb4_bin))
);
関数インデックスを用いて検索を行うには、書かれた定義どおりの式を、検索条件で指定する必要がありますので注意しましょう。インデックスが実際に効いているかどうかについてはEXPLAINを見て判断してください。次のクエリはこの関数インデックスを用いた例です。MySQLのオプティマイザはCASTを自動的に省略するようになっている点に注意してください。
SELECT * FROM employees WHERE data->>'$.name' = 'Mikiya';
また、MySQLには生成カラムというものがあります。生成カラムとは、ほかのカラムの値から自動的に計算してカラムの値を決めるというものです。また、特定のカラムをデフォルトでは見えなくするというインビジブル
CREATE TABLE employees (
data JSON,
name VARCHAR(30) GENERATED ALWAYS AS (data->>'$.name') STORED INVISIBLE,
INDEX (name)
);
こちらの例の場合はnameカラムを検索条件で指定し、検索できます。インビジブルカラムは見えないだけで、明示的に指定するとアクセスすることが可能です。
MySQL 8.
SET sql_generate_invisible_primary_key=ON;
CREATE TABLE (・・・主キーがない定義・・・);
このようにテーブルを作成すると、my_という定義のカラムが追加され、なおかつこのカラムが主キーになります。my_カラムは見えないだけで存在していますので、あとになって別の主キーを追加する場合には注意が必要です。主キーがないテーブルの利用は推奨されませんが、どうしても必要な場合にはGIPKの利用をおすすめします。
インデックスを作成する場合のもうひとつの注意事項として、インデックスの並び順があります。デフォルトでは昇順でインデックスレコードはソートされますが、MySQL 8.
CREATE TABLE tbl_name (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
col1 INT, col2 INT, col3 INT,
INDEX ix1 (col1 ASC, col2 DESC, col3 ASC));
昇順か降順かはソートのときに重要になります。ソートでインデックスを利用するには、そのインデックスの並び順で行を順番に取得する必要があるからです。上記のようにcol2カラムに対して降順インデックスを持つテーブルに対してソートでインデックスを活用できるクエリには、次のようなものがあります。
SELECT * FROM tbl_name ORDER BY col1 ASC, col2 DESC, col3 ASC;
SELECT * FROM tbl_name ORDER BY col1 DESC, col2 ASC, col3 DESC;
SELECT * FROM tbl_name WHERE col1=100 ORDER BY col2 DESC, col3 ASC;
SELECT * FROM tbl_name WHERE col1=100 ORDER BY col2 ASC, col3 DESC;
ちょうどASCとDESCを入れ替えた場合にもインデックスが利用できる点に注意してください。ただし降順でインデックスをたどるのは、昇順よりも効率が落ちてしまいます。ページ内のインデックスレコードは片方向の連結リストで結ばれており、その方向はデフォルトでは昇順だからです。連結リストについては特集第3章で詳しく解説していますので、あわせて確認してみてください。降順でのソートを利用する場合には、ぜひインデックス自体を降順にすることを検討してください。
インデックスのコスト
インデックスは有用なものですので、ついつい
第一に、インデックスを保存しておくためのディスクスペースが必要です。ほとんどの場合はファイルシステムを介してディスク上にデータが保存されることになります。インデックスのサイズはほぼ行数に比例します。さらに、セカンダリインデックスはランダムな順序で削除・
また、セカンダリインデックスは常にデータ
実行計画を考慮したクエリの書き換えのポイント
いくらオプティマイザによって最適な実行計画が選択されると言っても、オプティマイザの見積もりはあくまでも見積もりであって限界があります。そのため、可能であればオプティマイザが最適な実行計画を選択しやすいようにクエリを工夫する必要があります。ここではクエリを書き換える際のポイントについて簡単に紹介したいと思います。
複雑過ぎるクエリ
JOINするテーブルが多過ぎるクエリなど、複雑なクエリではオプティマイザの動作そのものに時間がかかってしまいます。テーブル数が増えれば増えるほど、等価なJOINの順序の組み合わせが増えます。n個のテーブルの並びは、順列ですのでn!
FROM句のサブクエリや共通テーブル式
そのような問題が起きていることを知る最適な方法は、EXPLAINの実行時間を見ることです。EXPLAINはオプティマイザによって生成されるものですので、EXPLAINに時間がかかっている場合には、オプティマイザの実行に時間がかかっている可能性が高いからです。もしクエリが複雑過ぎる場合には、テンポラリテーブルを利用してJOINの中間結果を格納するなどの対策を検討してください。
GROUP BYのあるFROM句のサブクエリとLEFT JOIN
筆者はしばしば次のような形式のクエリを目にすることがあります。FROMサブクエリにGROUP BYが使われており、それが外部クエリにおいてLEFT JOINで結合されているというパターンです。
SELECT t2.* FROM t1 LEFT JOIN
(SELECT col1, MAX(col2), MAX(col3), MAX(col4) FROM t2 GROUP BY col1) t2
ON t1.col1 = t2.col2;
このクエリは、本来次のようにスカラサブクエリを用いて書きたいところです。
SELECT
col1,
(SELECT MAX(col2) FROM t2 WHERE t2.col1=t1.col1) AS col2,
(SELECT MAX(col3) FROM t2 WHERE t2.col1=t1.col1) AS col3,
(SELECT MAX(col4) FROM t2 WHERE t2.col1=t1.col1) AS col4
FROM t1;
しかし、このスカラサブクエリを用いた検索は効率がよくありません。なぜなら、同じ検索条件でサブクエリを3回実行する必要があるからです。この方法では、取得するカラム数が増えれば増えるほど効率は劣化します。このような問題が起きるのは、スカラサブクエリで取得できる値が1つだけだからです。複数のカラムの値を一度に取得するというのは、SQLの文法上できません。
この問題を解決する1つのアプローチとして有効なのが、JSONを利用する方法です。クエリの中でJSONを構築し、そのJSONに必要な値を詰め込むのです。そうすることでサブクエリの実行が1回で済むようになります。JSONを用いてサブクエリの実行を減らすには、次のようにクエリを書き換えます。
SELECT col1,
(SELECT JSON_OBJECT('col2', t2.col2, 'col3', t2.col3, 'col4', t2.col4)
FROM t2 WHERE t1.col1=t2.col1) AS j
FROM t1
この例ですとアプリケーションに返されるのはJSONとなります。もしアプリケーションが従来と同じ型の結果を得たい
SELECT col1,
CAST(j->'$.col2' AS INT) AS col2,
CAST(j->'$.col3' AS INT) AS col3
CAST(j->'$.col4' AS INT) AS col4 FROM
(SELECT t1.col1,
(SELECT JSON_OBJECT('col2', t2.col2, 'col3', t2.col3, 'col4', t2.col4)
FROM t2 WHERE t1.col1=t2.col1) AS j FROM t1) t;
最初の例のように、FROM句の内部でGROUP BY句を利用するのは効率が悪く、JSONを用いたほうがおおむね良好な性能を示すことが多いです。少し記述するのが面倒ですが、GROUP BYとLEFT JOINを使うよりも、実はスカラサブクエリで書くべきケースはよくあります。もちろんサブクエリの実行がきちんとインデックスを用いたものになっていることが条件です。とくに外部クエリに追加の検索条件があるというような場合には、サブクエリの実行回数がその分減ることになりますので、実行速度の差は顕著に現れてくるでしょう。
SQLの限界を知る
アプリケーションが要求する検索のパターンは実にさまざまで、中にはSQLが苦手とするものも多数あります。SQLが持つ演算はリレーショナルモデルに立脚しているので、それ以外の演算が要求される場合は自力で実装するなどの対応が必要です。たとえばグラフ理論のグラフの演算や全文検索などがそれにあたります。全文検索はRDBMS上でも実装されていますが、やはり餅は餅屋で専用の検索エンジンには敵いません。また、インデックスは予定されたクエリに対して威力を発揮するものです。検索条件が柔軟に変化するようなケースでは、インデックスを活用することは難しいでしょう。
SQLだけでは対応が難しいという場合には、RDBMS以外のデータベースと併用することも検討すると良いでしょう。複数のデータベースを運用する上では整合性を取るのが課題になってきますので、その点は注意が必要です。
まとめ
本章ではインデックスをどのように使いこなすかということについて、クエリ側の視点から解説しました。SQLは宣言型であるものの、高速に実行するにはクエリの書き方の工夫や、適切なインデックスの設計が不可欠だということがおわかりいただけたのではないでしょうか。ユーザーが何も手を加えなくてもすべてのクエリを理想どおりに実行してくれればよいのですが、現状ではまだインデックスを初めとして、手を加えなければいけないのが実情です。それでもRDBMSは多くのケースで実用に耐えうる性能を発揮します。
本稿が少しでも日々量産される遅いクエリと対峙するみなさんのお役に立てれば幸いです。