MySQLパフォーマンス最適化シリーズの1つ

MySQLパフォーマンス最適化シリーズの1つ
 
背景紹介
マルチテーブル関連のSQLを書くとき、left jion(左関連)、right jion(右関連)、inner jion(内関連)などを思い浮かべます.
ただし、テーブルのデータ量が大きすぎる場合、クエリー条件を書いていないか、クエリー条件を書いている順番が異なると、明らかなパフォーマンスの違いがある可能性があります.
 
最近、ある同僚はSQLクエリーが遅いという問題に遭遇しました.tableA、tableB、tablecの3つのテーブルが共同でクエリーしたSQLは、クエリーの時間が50 s近くかかります.
 
 
原因分析
1、それぞれ3枚の表のデータ量を確認する
tableA:3千万+条記録;
tableB:5千+条記録;
tablec:7千+条記録;
 
2、SQLクエリロジックの確認
SQLは次のようになります(tableaのnameにはmiracleが含まれています.tableBのageは20歳より大きく、tablecの性別はmaleの連合情報です).

select *
from
tableA a, tableB b, tableC c
where a.id = c.id
and b.uuid = c.uuid
and a.name like '%miracle%'
and b.age > 20
and c.sex = 'male'

上記のSQLの効果は内部関連と等価であり、SQLの関連ロジックに基づいて、テーブル間の関連クエリーは、実は集合間で先に「デカルト積」を行い、クエリー条件に基づいてこのデカルト積結果セットを再びフィルタリングすることがわかります.
このときデカルト積の集合容量は,(3千万+)*(5千+)*(7千+)であり,100兆級の膨大なデータ集合であることがわかる.
したがって,この膨大な集合から,フィルタ条件に従って所望のデータを照会すると,当然遅くなる.
 
 
チューニング・スキーム
1、単一テーブルの前処理
tablea前処理(処理後、tableaの「有効」データレベルが1千+に下がる):

select * from tableA where name like '%miracle%'

 
tableB前処理(処理後、tableBの「有効」データレベルが2千+に下がる):

select * from tableB where age > 20

 
tablec前処理(処理後、tablecの「有効」データレベルが3千+に下がる):

select * from tableC where sex = 'male'

このとき,3枚の表「デカルト」のデータレベルは,(1千+)*(2千+)*(3千+),約10億級のデータ集合であった.以前に比べて、レベルは10万倍下がった.
 
2、クエリSQL構造の調整

select * 
from 
(select * from tableA where name like '%miracle%') a,
(select * from tableB where age > 20) b,
(select * from tableC where sex = 'male') c
where a.id = c.id
and b.uuid = c.uuid

このとき,SQLのクエリ時間は0.14 sであり,以前の50 sに比べてクエリ速度は数百倍に向上した.
 
3、表関連方式の変換(二次最適化)
上記の操作を行うと,クエリ速度が著しく向上した.
クエリーの効率をさらに向上させるには、関連方法を調整することができます.
3つのテーブルの関連付けであるため、左の関連付けと内側の関連付けはパフォーマンスに大きな差があります.
この場合、3つのテーブルの左の関連付けは、内部の関連付けクエリーよりもパフォーマンスが向上し、SQLは次のように調整されます.

select * 
from 
(select * from tableA where name like '%miracle%') a 
left jion 
(select * from tableC where sex = 'male') c on a.id = c.id  
left jion (select * from tableB where age > 20) b on b.uuid = c.uuid

このときtableaとtablecの左に関連するデカルト積集合容量は(1千+)*(2千+)であり、百万級のデータ集合であり、a.id=c.idフィルタリング後に1千+のデータ集合が得られる
tableaとtablecを左に関連付けた結果セットをtableBと左に関連付け、デカルト積集合容量は(1千+)*(3千+)であり、百万級のデータ集合でもある.
ステップ2の10億級に比べて1000倍も減少した.最終的に、上記SQLの実行時間は0.1 s未満
 
 
最適化の概要
データ・テーブルのデータ量が比較的大きいマルチテーブル・コンビネーション・クエリのシーン.
SQLの最適化の原則は次のとおりです.
1、前処理単表データ、各表の「有効」データを取得し、初の「降格」の目的を達成する.
2、関連関係を調整し、二次「降格」を実現する.
(説明:本明細書でいう「降格」とは、SQL実行の数を減らすことを意味する)
 
 
PS:
皆さんのお役に立てたらと思います.ありがとうございます.