索引が使われない理由 — 左端プレフィックスと、その反例

条件の書き方が正しくても、プランナが索引を使わない判断をする場合があります。PostgreSQL は極端な例で説明します。

An extreme example is that on a table that only occupies one disk page, you'll nearly always get a sequential scan plan whether indexes are available or not. The planner realizes that it's going to take one disk page read to process the table in any case, so there's no value in expending additional page reads to look at an index.

1ページしかないなら、索引を読む分が純粋な追加コストになります。コストの比較軸も書かれています。

Fetching rows separately is much more expensive than reading them sequentially, but because not all the pages of the table have to be visited, this is still cheaper than a sequential scan.

MySQL 側も同じ方向の記述です(こちらは How MySQL Uses Indexes のページ)。

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.

小さいテーブルと大半の行を読む問い合わせが挙げられています。ランダムアクセスは順次読みより高価だが、訪ねるページが少なければ得になる——この綱引きの結果として計画が決まります。

そして索引は「作れば得」ではありません。書き込み側のコストがあります。

After an index is created, the system has to keep it synchronized with the table. This adds overhead to data manipulation operations.

Therefore indexes that are seldom or never used in queries should be removed.

MySQL はもう一歩踏み込んで、読み側のコストにも言及します。

Indexes also add to the cost of inserts, updates, and deletes because each index must be updated.

Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use.

「索引のコスト = 書き込みが遅くなる」だけではありません。どれを使うか決める時間も消費されます。だから公式の結論はこうなります——“You must find the right balance to achieve fast queries using the optimal set of indexes.”

関数を使った条件については、公式の言い方を正確に取る必要があります。PostgreSQL は索引を式の上に作れると書きます。

An index column need not be just a column of the underlying table, but can be a function or scalar expression computed from one or more columns of the table.

This query can use an index if one has been defined on the result of the lower(col1) function: CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

検索時に式を計算し直さない点も明記されています——“the index expressions are not recomputed during an indexed search, since they are already stored in the index.”

MySQL 側は一致の条件を例で示します。

For a functional key part containing the SUBSTRING() function to be used in a query, the WHERE clause must contain SUBSTRING() with the same arguments. In the following example, only the second SELECT is able to use the index because that is the only query in which the arguments to SUBSTRING() match the index specification

CREATE TABLE tbl (col1 LONGTEXT, INDEX idx1 ((SUBSTRING(col1, 1, 10))));
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 9) = '123456789';
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 10) = '1234567890';

使えない理由は “The index is not used because the expressions in the query and the index differ.” です。引数が1違うだけで別の式になります。

なお MySQL の説明文には原文のまま引用すべき箇所があります——“Functional key parts that index expression values canalso be used in place of column or column prefix values.” "canalso" は原文のタイポで、直さずに引用します。実装の形も書かれています——“Functional indexes are implemented as hidden virtual generated columns”。