文章を書く前に、まず飛友科技の陸先生が提供してくださったドキュメントに感謝します。
声明：以下の最適化案はすべて「Mysql-インデックス-BTree タイプ」に基づいています。
一、EXPLAIN
MySQL の最適化では、EXPLAIN を活用して SQL の実行計画を確認しましょう。
以下に簡単な例を示します。注目すべきデータを (1,2,3,4,5) でマークしています。
- type 列：接続タイプ。良い SQL 文は少なくとも range レベルに達する必要があります。all レベルは避けるべきです。
- key 列：使用されたインデックス名。インデックスが選択されていない場合、値は NULL です。強制インデックスを使用することもできます。
- key_len 列：インデックスの長さ。
- rows 列：スキャン行数。この値は推定値です。
- extra 列：詳細説明。よく見られる好ましくない値として、
Using filesort、Using temporaryがあります。
二、SQL 文で IN に含まれる値が多すぎないようにする
MySQL は IN に対して最適化を行っており、IN 内の定数をすべて配列に格納し、その配列をソートしています。ただし、値の数が多いと、消費も大きくなります。例：select id from table_name where num in(1,2,3) のような連続した値の場合、between を使えるなら in は避けましょう。また、結合に置き換えることも検討してください。
三、SELECT 文では必ずフィールド名を指定する
SELECT * は不必要な消費（CPU、IO、メモリ、ネットワーク帯域）を増やします。また、カバリングインデックスを使用できる可能性を減らします。テーブル構造が変更された場合、フロントエンドも更新する必要があります。そのため、SELECT の後ろには直接フィールド名を記述することを推奨します。
四、1 行だけ必要な場合は limit 1 を使用する
これにより、EXPLAIN の type 列を const タイプにすることができます。
五、ソートフィールドにインデックスが使用されていない場合は、ソートを極力避ける
六、制約条件の他のフィールドにインデックスがない場合は、or を極力避ける
or の両側のフィールドのうち、どちらかがインデックスフィールドでなく、他の条件もインデックスフィールドでない場合、そのクエリはインデックスを使用しなくなります。多くの場合、union all または union（必要な場合）を使って “or” を代替する方が良い結果が得られます。
七、可能な限り union all を union の代わりに使用する
union と union all の違いは、前者が結果セットをマージした後に一意性フィルタリング処理を行う必要がある点です。これにはソートが伴い、大量の CPU 演算が増加し、リソース消費と遅延が大きくなります。ただし、union all を使用する前提条件は、2 つの結果セットに重複データがないことです。
八、ORDER BY RAND() を使用しない
select id from `table_name` order by rand() limit 1000;
上記の SQL 文は次のように最適化できます。
select id from `table_name` t1 join (select rand() * (select max(id) from `table_name`) as nid) t2 on t1.id > t2.nid limit 1000;
九、in と exists、not in と not exists を区別する
select * from 表A where id in (select id from 表B)
上記の SQL 文は次と同等です。
select * from 表A where exists(select * from 表B where 表B.id=表A.id)
in と exists を区別する主な理由は、駆動順序の変更（性能変化の鍵）です。exists の場合は外部テーブルが駆動テーブルとなり、先にアクセスされます。IN の場合はサブクエリが先に実行されます。したがって、IN は外部テーブルが大きく内部テーブルが小さい場合に適しており、EXISTS は外部テーブルが小さく内部テーブルが大きい場合に適しています。
not in と not exists については、not exists の使用を推奨します。効率の問題だけでなく、not in には論理的な問題が発生する可能性があります。not exists を代替する効率的な SQL 文を書くにはどうすればよいでしょうか？
元の SQL 文：
select colname … from A表 where a.id not in (select b.id from B表)
効率的な SQL 文：
select colname … from A表 Left join B表 on where a.id = b.id where b.id is null
取得される結果セットは下図の通りで、A 表には存在するが B 表には存在しないデータを示します。
十、合理的なページネーション方式を使用してページングの効率を向上させる
select id,name from table_name limit 866613, 20
上記の SQL 文でページネーションを行うと、テーブルのデータ量が増えるにつれて、単純な limit によるページネーションクエリが徐々に遅くなることがあります。
最適化方法は以下の通りです。前のページの最大行の id を取得し、その最大 id を基準に次のページの開始点を制限します。例えば、この列では前のページの最大 id が 866612 です。SQL は次のように記述できます。
select id,name from table_name where id> 866612 limit 20
十一、分割クエリ
一部のユーザー選択ページでは、ユーザーが選択する時間範囲が広すぎてクエリが遅くなることがあります。主な原因はスキャン行数が多すぎることです。この場合、プログラムで分割してクエリを実行し、ループで結果を結合して表示することができます。
下図の SQL 文のように、スキャン行数が数百万以上になる場合は分割クエリを使用できます。
十二、where 句でフィールドに null 値判定を行わない
null の判定は、エンジンがインデックスを放棄して全テーブルスキャンを行う原因となります。
十三、% 接頭辞のあいまい検索は推奨しない
例：LIKE “%name” や LIKE “%name%” のようなクエリは、インデックスが無効になり全テーブルスキャンが行われます。ただし、LIKE “name%” は使用可能です。
%name% を検索するにはどうすればよいでしょうか？
下図のように、secret フィールドにインデックスを追加しても、explain の結果では使用されていません。
この問題を解決するには、全文インデックス を使用します。
クエリで select id,fnum,fdst from table_name where user_name like '%zhangsan%'; のような文をよく使います。通常のインデックスではこのようなクエリ要件を満たせません。幸い、MySQL には全文インデックスがあり、これを支援します。
全文インデックスを作成する SQL 構文：
ALTER TABLE `table_name` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);
全文インデックスを使用する SQL 文：
select id,fnum,fdst from table_name where match(user_name) against('zhangsan' in boolean mode);
注意：全文インデックスを作成する前に、DBA に作成可能かどうかを確認してください。また、クエリ文の記述方法が通常のインデックスと異なる点に注意してください。
十四、where 句でフィールドに対する式操作を避ける
例えば、
select user_id,user_project from table_name where age*2=36;
ではフィールドに算術演算を行っています。これによりエンジンがインデックスを放棄する原因となるため、以下のように変更することを推奨します。
select user_id,user_project from table_name where age=36/2;
十五、暗黙の型変換を避ける
where 句で column フィールドの型と渡されるパラメータの型が一致しない場合に型変換が発生します。where 内のパラメータの型を事前に確認することを推奨します。
十六、複合インデックスでは、最左接頭辞ルールを遵守する
例として、インデックスに id、name、school フィールドが含まれている場合、id フィールドだけ、または id、name の順序で使用できますが、name;school ではこのインデックスを使用できません。そのため、複合インデックスを作成する際には、インデックスフィールドの順序に注意し、よく使われるクエリフィールドを先頭に配置してください。
十七、必要に応じて force index を使用してクエリに特定のインデックスを強制する
MySQL のオプティマイザが適切と判断したインデックスを使用して SQL 文を検索することがありますが、そのインデックスが意図したものでない場合があります。その場合は force index を使用してオプティマイザに指定したインデックスを強制できます。
十八、範囲検索クエリに注意する
複合インデックスでは、between、>、< などの範囲検索条件があると、後続のインデックスフィールドが無効になります。
十九、JOIN の最適化について
- LEFT JOIN：A テーブルが駆動テーブル
- INNER JOIN：MySQL が自動的にデータ量の少ないテーブルを駆動テーブルとして選択
- RIGHT JOIN：B テーブルが駆動テーブル
注意：MySQL には full join はありません。以下の方法で代用できます。
select * from A left join B on B.name = A.name
where B.name is null
union all
select * from B;
可能な限り inner join を使用し、left join を避ける
結合クエリに参加するテーブルは最低 2 テーブルあり、通常は大小関係があります。接続方式が inner join の場合、他のフィルタ条件がなければ MySQL は自動的に小さいテーブルを駆動テーブルとして選択しますが、left join では駆動テーブルの選択が左から右へという原則に従い、left join の左側のテーブルが駆動テーブルになります。
インデックスを適切に利用する
被駆動テーブルのインデックスフィールドを on の制約フィールドとして使用します。
小テーブルで大テーブルを駆動する
原理図から、駆動テーブルを減らせると、ネストループのループ回数が減り、IO 総量と CPU 演算回数を削減できることが直感的にわかります。
STRAIGHT_JOIN を巧みに使う
inner join では MySQL が駆動テーブルを選択しますが、group by や order by などで「Using filesort」「Using temporary」が発生する場合など、特殊な状況では別のテーブルを駆動テーブルとして選択したいことがあります。STRAIGHT_JOIN を使用すると結合順序を強制できます。STRAIGHT_JOIN の左側にあるテーブル名が駆動テーブル、右側が被駆動テーブルです。STRAIGHT_JOIN を使用する前提条件は、そのクエリが内部結合（inner join）であることです。他の結合では STRAIGHT_JOIN の使用は推奨しません。クエリ結果が不正確になる可能性があります。
この方法により、処理時間を 3 分の 1 に削減できる場合があります。
ここでは上記の最適化案のみを挙げましたが、他にも最適化方法があります。ぜひ試して探してみてください。ご関心をお寄せいただきありがとうございます。
最後に、以下もご覧ください：http://fenxianglu.cn/
