プロジェクトでよく使われる19のMy SQL最適化

在写文章之前，首先感谢飞友科技陆老师提供的文档。。

声明一下：下面的优化方案都是基于 “ Mysql-索引-BTree 类型 ” 的

1. EXPLAIN

做 MySQL 优化，我们要善用 EXPLAIN 查看 SQL 执行计划。

ここでは、焦点を当てるデータ（1、2、3、4、5）に注釈を付ける簡単な例を示します。

typeカラム、接続のタイプ。良いSQLステートメントは少なくとも範囲レベルに達する必要があります。すべてのレベルの排除
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、メモリ、ネットワーク帯域幅）を追加します。オーバーレイインデックスを使用する可能性を高めます。テーブル構造が変更されるとフロントエンドを更新する必要があります。選択の直後にフィールド名を追加します。

必要なデータが1つだけの場合は、制限1を使用します。

これはEXPLAINのtypeカラムをconst型にするためです。

ソートフィールドがインデックスを使用しない場合は、ソートを最小限に抑える。

制限内の他のフィールドにインデックスがない場合は、使用を最小限に抑えます。

またはフィールドの両側のうち、1つがインデックスフィールドではなく、他の条件もインデックスフィールドではない場合、クエリがインデックスされない状況になります。“or”の代わりにunion allや“union”（必要に応じて）を使う方が効果的であることが多い

UNION ALLをUNIONに置き換える

union 和 union all 的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

8、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ステートメントをページングに使用すると、テーブルデータの量が増加するにつれて、制限ページングクエリを直接使用すると遅くなることがわかります。

前のページの最大行数のIDを取り、その最大IDに基づいて次のページの開始点を制限することができる。例えば、この列では、前のページの最大IDは866 6 12でした。SQLは以下のように記述できます：

select id,name from table_name where id> 866612 limit 20

XI.サブセクションの問い合わせ

一部のユーザー選択ページでは、一部のユーザーが選択した時間枠が大きすぎ、クエリが遅くなる可能性があります。主な理由は、スキャンの行数が多すぎることです。この場合、プログラム、セグメント、ループ、結果のマージ処理を介して表示することができます。

次のSQL文のように、スキャンされた行数が100万以上の場合、セグメントクエリを使用できます。

w here節でフィールドをnull値で判断しないでください。

nullを判断すると、エンジンはインデックスの使用を放棄し、フルテーブルスキャンを実行します。

%接頭辞ファジークエリの使用は推奨されない

LIKE “%name”やLIKE “%name%”のようなクエリは、インデックスを無効にし、完全なテーブルスキャンを実行します。“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に問い合わせて作成できるかどうかを確認してください。また、クエリ文の書き方と通常のインデックスの書き方の違いに注意する必要があります。

w here節でフィールドを表現する操作を避ける

例えば、

select user_id,user_project from table_name where age*2=36;

フィールドに対して算術演算が行われるため、エンジンはインデックスの使用を放棄します。

select user_id,user_project from table_name where age=36/2;

15.暗黙の型変換の回避

w here節のカラムフィールドの型と渡されたパラメータの型が一致しない場合の型変換は、まずw hereのパラメータの型を決定することをお勧めします。

16.ジョイントインデックスについては、最左接頭辞の法則に従う。

例えば、インデックスにはフィールドid、name、schoolが含まれており、idフィールドを直接使用することもできますが、name;schoolはこのインデックスを使用できません。したがって、ジョイントインデックスを作成するときは、インデックスフィールドの順序に注意し、一般的なクエリフィールドを先頭に置きます。

必要に応じてフォースインデックスを使用して、クエリをインデックスに強制できます。

My SQLオプティマイザはSQLステートメントを取得するために適切と思われるインデックスを取りますが、使用するインデックスが必要ではない場合があります。フォースインデックスを使用して、オプティマイザにインデックスを使用させることができます。

十八、注意範囲の照会文

ユニオンインデックスの場合、between、>、<などの範囲クエリが存在すると、後続のインデックスフィールドが無効になります。

19.ジョイン最適化について

Left Join Aテーブルは駆動テーブルです。
INNER JOIN My SQLは、データの少ないテーブル駆動テーブルを自動的に見つけます。
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;

** 内側の結合を使用し、左の結合は避けてください。

連合クエリに関与するテーブルは少なくとも2つのテーブルであり、通常はサイズが異なります。ジョインモードがインナージョインの場合、My SQLは他のフィルタ条件がない場合、自動的に小さなテーブルをドライバテーブルとして選択しますが、左ジョインはドライバテーブルの選択において左が右を駆動する原則に従います。つまり、左ジョインの左のテーブル名はドライバテーブルです。

** インデックスの合理的な利用 **

駆動されるテーブルのインデックスフィールドをonの制限フィールドとする。

** 小さなテーブルを使って大きなテーブルを動かす **

回路図から、駆動テーブルを減らすことができれば、ネストされたループ内のループ数を減らし、IOの総量とCPU演算の回数を減らすことがわかります。

** STRAIGHT_JOINを使用します **

内部結合はMy SQLによって駆動テーブルを選択しますが、グループ別、順序別などの“ファイルを使用”、“テンプレートを使用”など、別のテーブルを駆動テーブルとして選択する必要がある特別なケースもあります。STRAIGHT_JOINは接続順序を強制します。STRAIGHT_JOINの左側のテーブル名が駆動テーブル、右側が駆動テーブルです。STRAIGHT_JOINを使用するための前提条件は、クエリが内部ジョイン、すなわち内部ジョインであることです。他のリンクではSTRAIGHT_JOINを使用しないでください。

この方法は3 倍の時間を短縮することができる。

ここでは、上記の最適化スキームのみを列挙し、もちろん、他の最適化方法は、誰もが探索することができます。

写到最后，欢迎关注：http://fenxianglu.cn/