SQLチューニングの一般的手順
ログを遅くチェックするなど、実行効率の低いSQL文を特定する
SQLの実行計画を分析する
type、rows、filtered、extraに重点を置いてください。
上から下へ、効率的になる。
- 全テーブルスキャン。
- インデックス全体のスキャン
- rangeインデックス範囲スキャン、慣用語<<=>= between inなどの操作
- 非一意インデックススキャンまたは一意インデックスプレフィックススキャンを使用して、関連クエリにしばしば現れる単一のレコードを返します。
- eq_ref ref refに似ていますが、ユニークなインデックスを使用し、プライマリキーを使用する関連クエリを使用します。
- const/system 1つのレコードで、システムは主キーや一意のインデックスクエリなど、一致する行の他の列を定数として処理します。
- null My SQLはテーブルやインデックスにアクセスせず、結果を直接返します。
虽然上至下，效率越来越高，但是根据 cost 模型，假设有两个索引idx1(a, b, c),idx2(a, c)，SQL 为select * from t where a = 1 and b in (1, 2) order by c;如果走 idx1，那么是 type 为 range，如果走 idx2，那么 type 是 ref；当需要扫描的行数，使用 idx2 大约是 idx1 的 5 倍以上时，会用 idx1，否则会用 idx2
Extra
- ファイルソートの使用：My SQLは、ソート順に行を取得する方法を把握するために追加のパスを必要とします。結合タイプに基づいてすべてのローを参照し、WHERE句に一致するすべてのローに対してソート·キーとローへのポインタを保存することで、ソートを完了します。その後、キーワードがソートされ、ソート順に行が取得されます。
- Temporaryの使用：中間結果を保存するために一時テーブルを使用し、パフォーマンスが悪く、最適化が必要です。
- Using index：該当するselect 操作に上書きインデックスCoveing Indexを使用して、表のデータ行にアクセスしたことを避けて、効率が良いです! using w hereも表示される場合、インデックス検索では条件付きデータを直接クエリできません。
- インデックス条件の使用：My SQL 5.6以降に追加されたICPでは、インデックス条件の使用は、サービス層ではなくストレージエンジン層でデータをフィルタリングし、既存のデータをインデックス化してテーブルに戻すデータを削減します。
3プロフィール分析の表示
SQLが実行されるスレッドの状態と消費時間を知る。
デフォルトはオフで、“set profiling = 1;”をオンにします。
SHOW PROFILES ;
SHOW PROFILE FOR QUERY #{id};
4 trace
trace解析オプティマイザが実行計画を選択する方法traceファイルを使用すると、クーポンがBではなくA実行計画を選択する理由がわかります。
set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;
5問題点の特定と対応策の採用
- インデックスの最適化
- SQL文のチューニングSQLの変更、IN問合せセグメンテーション、時間問合せセグメンテーション、前回のデータに基づくフィルタリング
- 他の実装に切り替える：ES、マルチサイロなど
- データの断片化
マイクロサービスのアイデアに基づいて、B 2 C電子商取引シナリオでプロジェクトの実際の運用を構築します。主な技術スタックはSpring Boot + Dubboです。将来的にはSpring Cloud Alibabaになります。
プロジェクトのアドレスhttps//github.com/YunaiV/onemall
シーン分析
ケース1：左端の一致
索引付き
KEY `idx_shopid_orderno` (`shop_id`,`order_no`)
SQL文{{SQLぶん}}
select * from _t where orderno=''
查询匹配从左往右匹配，要使用 order_no 走索引，必须查询条件携带 shop_id 或者索引(shop_id,order_no)调换前后顺序。
ケース2：暗黙の変換
索引付き
KEY `idx_mobile` (`mobile`)
SQL文{{SQLぶん}}
select * from _user where mobile=12345678901
暗黙の変換はインデックスを無効にする演算と同じです。mobileは数値を使用する文字型で、文字列マッチングを使用する必要があります。さもなければ、My SQLは暗黙の置換を使用し、インデックスが無効になります。
ケース3、大きなページ
索引付き
KEY `idx_a_b_c` (`a`, `b`, `c`)
SQL文{{SQLぶん}}
select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;
大きなページングのシナリオでは、製品の最適化要件を優先することができます。最適化がない場合は、次の2つの最適化方法があります。
- 1つは、前回の最後のデータ、すなわち上記のcを渡してから、“c < xxx”処理を行うことですが、これは一般的にインタフェースプロトコルを変更する必要があり、必ずしも実現可能ではありません。
- もう一つは、遅延相関処理を使用してSQLテーブルを減らすことですが、インデックスを完全にカバーする必要があることを覚えておくために、SQLは以下のように変更します。
SELECT
t1.*
FROM
_t t1,
( SELECT id FROM _t WHERE a = 1 AND b = 2 ORDER BY c DESC LIMIT 10000, 10 ) t2
WHERE
t1.id = t2.id;
ケース4：in + order by
索引付き
KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)
SQL文{{SQLぶん}}
SELECT
*
FROM
_order
WHERE
shop_id = 1
AND order_status IN ( 1, 2, 3 )
ORDER BY
created_at DESC
LIMIT 10
in 查询在 MySQL 底层是通过n*m的方式去搜索，类似 union，但是效率比 union 高。
in 查询在进行 cost 代价计算时（代价 = 元组数 * IO平均值），是通过将 in 包含的数值，一条条去查询获取元组数的，因此这个计算过程会比较的慢，所以 MySQL 设置了个临界值(eq_range_index_dive_limit)，5.6 之后超过这个临界值后该列的 cost 就不参与计算了。
これにより、実行計画の選択が不正確になる。デフォルトは200です。つまり、in 条件が200以上のデータを持つと、inのコスト計算に問題が発生し、My SQLが選択するインデックスが不正確になる可能性があります。
处理方式，可以(order_status, created_at)互换前后顺序，并且调整 SQL 为延迟关联。
ケース5、範囲クエリブロック、後続のフィールドはインデックス化できない
索引付き
KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)
SQL文{{SQLぶん}}
SELECT
*
FROM
_order
WHERE
shop_id = 1
AND created_at > '2021-01-01 00:00:00'
AND order_status = 10
“In，Between”の検索結果
ケース6-インデックスを使用できない高速検索が等しくない、含まれない。（ICPで使用できます）
select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1
インデックスでは、NOT、!=、を避けてください。<>、! <、!>、NOT EXISTS、NOT IN、NOT Lなど
ケース7：オプティマイザがインデックスを使用しない場合。
必要なデータ量が少ない場合、オプティマイザはセカンダリインデックスを選択しますが、アクセスされたデータがテーブル全体のデータのかなりの部分（通常は約20%）を占める場合、オプティマイザはクラスタインデックスを使用してデータを検索することを選択します。
select * from _order where order_status = 1
未払いの注文をすべて検索します。通常、注文は非常に少なく、インデックスが作成されていてもインデックスを使用することはできません。
ケース8：複雑な問い合わせ
select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';
select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;
特定のデータを統計する場合は、複数の倉庫に置き換えることができます。
ビジネスにこのような複雑なクエリがある場合は、SQLを続けることをお勧めしませんが、ESなどを使用して解決するなど、他の方法を使用することをお勧めします。
ケース9：ASCとDESCの混合
select * from _t where a=1 order by b desc, c asc
desc 和asc混用时会导致索引失效
第10章ビッグデータ
プッシュビジネスデータストレージの場合、データ量は非常に大きくなる可能性があります。スキームの選択では、最終的にMy SQLに保存することを選択し、7日間などの有効期間を保存します。
その後、頻繁なクリーニングデータはデータ断片化になり、データ断片化処理のためにDBAに連絡する必要があります。