MySQL の SQL クエリチューニングの要所を掴む勉強会

MySQL SQL チューニング 2024.03.21

自己紹介  • 三谷　智史（@mita2）  • 好きなデータベース：MySQL  • 2020.3 ~ ANDPADのデータベース技術顧問  •
相談は、DM or #dev_dbパフォーマンスチューニングへどうぞ  • http://mita2db.hateblo.jp/ 

アジェンダ  • MySQL の得意なこと、苦手なこと  • データベースのチューニング手段と特徴  • SQLチューニングの流れ  • インデックス 
• SQLチューニング例  • まとめ 

データベースとは  • データベース  • 検索や蓄積が容易にできるよう整理された情報の集まり    • DBMS (Database Management
System)  • データベースをコンピューター上で管理するための仕組み 

MySQL はたくさんある  データベースのうちの１つにすぎない 

得意・不得意あるよ 

ワークロード  • OLTP  • Online Transaction Processing  • 少数の行・多くのカラムを処理  •
参照・更新の頻度に偏りがある  • クイックなレスポンスを期待する  • OLAP/DWH • OnLine Analytical Processing • Data WareHouse  • 集計・解析  • 大量の行の少数のカラムを処理する  • OLTPほど高速なレスポンスは求められない   

参照・更新の頻度に偏りがある  • クイックなレスポンスを期待する    • OLAP/DWH • OnLine Analytical Processing • Data WareHouse  • 集計・解析  • 大量の行の少数のカラムを処理する  • OLTPほど高速なレスポンスは求められない   

参照・更新の頻度に偏りがある  • クイックなレスポンスを期待する  • OLAP/DWH • OnLine Analytical Processing • Data WareHouse  • 集計・解析  • 大量の行の少数のカラムを処理する  • OLTPほど高速なレスポンスは求められない   

OLTP/OLAP 両方対応できる  データベースはあるのか？ 

Hybrid Transaction  Analytical Processing  (HTAP) 

HTAPな製品  • Oracle Exadata  • MySQL Heatwave  • TiDB  •
Google AlloyDB 

MySQL Heatwave 

TiDB 

ワークロードとエンジン  • HTAPは、エンジンを自動的に使い分けることで、「両対応」  • １つのデータベース（エンジン）で  両方のワークロードを扱うのは今でも困難    • それだけ、エンジンとワークロードのミスマッチは致命的  •
MySQL (InnoDB) で、大規模な集計・解析のクエリは高速に処理できない 

ここまでのまとめ  • MySQL は OLTP が得意な (高速に処理できる) DB    •
広範囲の SUM や COUNT を高速に実行したいなら別のDBと併用  • 例）󰢃ログをMySQLに保存し、集計  • 　　👌ある程度サマリーした結果をMySQLに保存し、集計 

DBのチューニング方法の比較  方法内容対象効果 SQLチューニング SQLの変更インデックスの追加など個別のSQL
何十倍改善することもパラメータチューニング DBの設定変更利用しているHWに適した設定に変更するなど DB全体数％程度

SQLチューニングのゴール  • 性能要件を満たすこと  • レイテンシ、スループット、インフラコスト etc..    • 󰢃よくある間違い  •
全てのSQLを限界まで最適化された状態にする 

SQLチューニングの流れ  1. 対象とするSQLを決める  2. 実行計画を確認する  3. 改善する  • インデックス追加、SQLの書き換え etc.. 

対象となるSQLを決める  1. Datadog APM から探す  2. Datadog のスロークエリログ Dashboard から探す 
3. AWS Performance Insight から探す   

Datadog APM  • APM -> Service Catalog -> Resources  •
遅いリクエストに含まれる、SQLのレイテンシを確認する  社外非公開 

スロークエリログ  • 実行時間が閾値を超えたSQLが記録される  • 閾値は、long_query_time パラメータで指定  • ANDPAD origin DB
では 1 sec   # Time: 2019-01-27T02:27:46.477924Z # User@Host: root[root] @ localhost [] Id: 19 # Query_time: 0.000059 Lock_time: 0.000019 Rows_sent: 1 Rows_examined: 1 SET timestamp=1548556066; SELECT c FROM sbtest1 WHERE id=4999; 実行時間読み取ったレコード数行ロック時間は含まれないため、参考にしない

スロークエリログ Dashboard  • スロークエリログを集計して、可視化したもの  • Dashboard -> Slow Query Ranking 
社外非公開 

AWS Performance Insight  • インスタンスごとに、ASS (Avg Active Session) 上位25件を確認可能  •
軽いSQLでも実行回数が多いと上位に  社外非公開 

チューニングの余地を推定するTIPS  • SQLチューニングの多くは、結果を求めるために不必要な行の読み取りを削減することで達成される  結果を求めるために欠かせない行の読み取り結果を求めるために欠かせない行の読み取り結果を求めるために不必要な行の読み取り

Rows_sent vs Rows_examined  • Rows_examined  • 読み取った行数  • Rows_sent  •
結果セットの行数    • Rows_examined >> Rows_sent ならチューニング余地がある可能性が高い    ※ 集約関数（SUM, COUNT, DISTINCT etc..）は外す必要あり   

Datadog スロークエリログ  • Logs -> Search -> Service: RDS  社外非公開 

Performance Insight  • Rows_sent/call を ON に  社外非公開 

実行計画  • MySQLがクエリをどう処理するかを表したもの    • SQL は「手続型言語」ではない  • Structured Query
Language  • 「欲しい結果」だけを述べる    • 実行計画をみて、改善の余地がないかを確認する 

実行計画の取得  • EXPLAIN + クエリ      • 出力例  >
EXPLAIN SELECT * FROM salaries s, employees e WHERE s.emp_no = e.emp_no; +----+-------------+-------+------+---------------+---------+--------------------+--------+----------+-------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+---------+--------------------+--------+----------+-------+ | 1 | SIMPLE | e | ALL | PRIMARY | NULL | NULL | 299645 | 100.00 | NULL | | 1 | SIMPLE | s | ref | PRIMARY | PRIMARY | employees.e.emp_no | 10 | 100.00 | NULL | +----+-------------+-------+------+---------------+---------+--------------------+--------+----------+-------+ ※ 紙面の都合上、partitions と key_len を省略して記載しています

EXPLAINの説明  id SELECTごとに割り当てられる連番、処理順ではない select_type SELECTの種類 type データのアクセス方法。ALL、index、const など possible_keys 利用可能なインデックス
key 実際に利用するインデックス（possible_keys のうちのどれか） ref インデックスと比較されるカラム、const は定数を表す rows スキャンする行数の見積もり filtered 条件によってフィルターされる行の割合(%)の見積もり Extra その他もろもろ、ソートの有無や、一時テーブルの利用有無など +----+-------------+-------+------+---------------+---------+--------------------+--------+----------+-------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+---------+--------------------+--------+----------+-------+ | 1 | SIMPLE | e | ALL | PRIMARY | NULL | NULL | 299645 | 100.00 | NULL | | 1 | SIMPLE | s | ref | PRIMARY | PRIMARY | employees.e.emp_no | 10 | 100.00 | NULL | +----+-------------+-------+------+---------------+---------+--------------------+--------+----------+-------+

過去の実行計画は見れない  • EXPLAIN を実行したタイミングで選択されたもの  • データの内容や分布が実行計画に影響を与える  • もしかしたら、過去は違っていたかもしれない     

EXPLAINの出力形式 

EXPLAIN FORMAT=TREE  • MySQL 8.0 でサポート  • FORMAT=TREE  • ツリー形式、親子関係がわかりやすい 
• いくぶん、わかりやすい言葉で表現されている（eg. Table scan）      > EXPLAIN FORMAT=TREE SELECT * FROM salaries s, employees e WHERE s.emp_no = e.emp_no AND salary > 100 \G *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=510750 rows=1.55e+6) -> Table scan on e (cost=30504 rows=299645) -> Filter: (s.salary > 100) (cost=0.567 rows=5.18) -> Index lookup on s using PRIMARY (emp_no=e.emp_no) (cost=0.567 rows=10.4)

EXPLAIN ANALYZE  • MySQL 8.0 でサポート    • EXPLAIN 無印
  • 表示されている数値は、統計情報に基づく「見積もり」    • EXPLAIN ANALYZE  • 実際にクエリを実行する  • 実際に時間がかかっている処理の特定が可能 

EXPLAIN ANALYZE 例  > EXPLAIN ANALYZE SELECT * FROM salaries
s, employees e WHERE s.emp_no = e.emp_no \G *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=509454 rows=3.1e+6) (actual time=0.747..3140 rows=2.84e+6 loops=1) -> Table scan on e (cost=30471 rows=299645) (actual time=0.499..207 rows=300024 loops=1) -> Index lookup on s using PRIMARY (emp_no=e.emp_no) (cost=0.563 rows=10.4) (actual time=0.00643..0.00904 rows=9.48 loops=300024) 1 row in set (3.65 sec) > EXPLAIN FORMAT=TREE SELECT * FROM salaries s, employees e WHERE s.emp_no = e.emp_no \G *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=510750 rows=3.1e+6) -> Table scan on e (cost=30504 rows=299645) -> Index lookup on s using PRIMARY (emp_no=e.emp_no) (cost=0.567 rows=10.4) 1 row in set (0.00 sec)

実行計画まとめ  • SQLをどう処理するかはMySQLが決める    • EXPLAINで、処理過程を見て、最適化の余地がないか確認する    • EXPAIN（無印)
だけでなく、EXPLAIN FORMAT=TREE や EXPLAIN ANALYZE も使って、読み解いていこう 

インデックスヒント / Optimizer Hints  • MySQLが最適な実行計画を選択してくれないケースがある  • クエリに「ヒント」を記載し、意図した実行計画を強制する     
• インデックスヒント  https://dev.mysql.com/doc/refman/8.0/ja/index-hints.html  • Optimizer Hints  https://dev.mysql.com/doc/refman/8.0/ja/optimizer-hints.html  SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;

インデックスとは  • インデックスを使うと高速化する  • CREATE idx1 ON tbl (column1) 
  • なぜだろう？ 

• ツリー構造  • データはソートされている  • リーフノード（末端）に値とPKが保存  インデックスの構造  pk  col1  updated 
1  a  2019/10/01  2  z  2019/03/05  3  h  2018/12/10  4  r  2017/09/07  ：：： CREATE INDEX idx_col1 ON tbl(col1) a  1  h  3  …  a-p r-z r  4  …  z  2  …  … 

インデックスの構造      • ツリーを辿ることで目的のデータに素早くアクセスできる  pk  col1  updated  1  a 
2019/10/01  2  z  2019/03/05  3  h  2018/12/10  4  r  2017/09/07  ：：： SELECT * FROM tbl WHERE col1 = 'h' a  1  h  3  …  a-p r-z r  4  …  z  2  …  … 

カーディナリティについて  > show index from sbtest.sbtest1 \G *********************** 1. row
*********************** Table: sbtest1 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id <省略> Cardinality: 2665 Sub_part: NULL Packed: NULL Null: Index_type: BTREE

カーディナリティ  • カーディナリティが低い  • インデックスの効果が発揮されにくい  • カーディナリティの高いデータ  • 例）AUTO_INCREMNT、シーケンス、更新日時  •
カーディナリティの低いデータ  • 例）フラグ、都道府県、性別 

なぜ、効かない？  • カーディナリティが小さい  • 条件にマッチするレコード数が多い＝絞り込めない  SELECT * FROM tbl_1million WHERE
flag = 'true' and salary > 200000 true  1  true  3  false  2  false  4  x50万 x50万条件にマッチする割合が多く、絞り込めない

例外  • 分布が偏っている場合  • 割合の少ないデータを選択する場合は有効  SELECT * FROM tbl_1million WHERE
flag = 'false' and salary > 200000 true  1  true  3  false  2  false  4  x99.9万 x0.1万 1/999 に絞り込める。インデックスによる絞り込み効果が出る。

複合インデックス 

複合インデックス  • 複数のカラムを対象としたインデックス  • 例）CREATE INDEX idx1 ON (col1, col2) 

Left Most Index  • 効く条件  • WHERE col1 = xxx 
• WHERE col1 = xxx AND col2 = xxx  • 効かない条件  • WHERE col2 = xxx  CREATE INDEX idx ON tbl (col1, col2) CREATE INDEX idx ON tbl (col2, col1) • 効く条件 • WHERE col2 = xxx • WHERE col1 = xxx AND col2 = xxx • 効かない条件 • WHERE col1 = xxx

複合インデックスの構造  • カラムの順序に従ってデータがソートされる  CREATE INDEX idx ON tbl (col1, col2)
CREATE INDEX idx ON tbl (col2, col1) 10,d  1  20,a  2  30,b  3  40,c  4  a,20  2  b,30  3  c,40  4  d,10  1  PK  col1  col2  1  10  d  2  20  a  3  30  b  4  40  c 

手当たり次第  インデックスを貼れば… 

インデックスの作成しすぎに注意  • アンチパターン「インデックスショットガン」    • デメリット  • 容量が増える  • キャッシュ効率の低下 
• 更新パフォーマンスの低下 

インデックス作成のポイント  • カーディナリティの高いカラムを選ぶ  • 複合インデックスは順番に注意する  • 必要なものだけに貼る 

SQLチューニング例  1. テーブルフルスキャン  2. インデックスフルスキャンとカバーリングインデックス  3. ソート  4. JOIN  5.
大量INSERT  6. 大量DELETE 

1.テーブルフルスキャン - 式インデックス  • 式インデックスはできるだけ避ける  • 式が変わるとインデックスを追加する必要がある  mysql> CREATE INDEX
func_idx1 ON sbtest1(( DATEDIFF(d, '2014-06-01') )); Query OK, 0 rows affected (8.51 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE INDEX func_idx2 ON sbtest1(( DATEDIFF(d, '2024-01-01') )); Query OK, 0 rows affected (8.51 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE INDEX func_idx3 ON sbtest1(( DATEDIFF(d, NOW()) )); ERROR 3758 (HY000): Expression of functional index 'func_idx3' contains a disallowed function.

2.インデックスフルキャン  • インデックスに含まれているもの  • インデックス対象のカラム＋PK          •
インデックスは、部分的なテーブルとみなせる  d  pk  2017/09/07  4  2018/12/10  3  2019/03/05  2  2019/10/01  1  ：： pk  d  col1  col2  k  1  2019/10/01  AAAA  xxxx  1234  2  2019/03/05  ABAB  aaaa  51441  3  2018/12/10  ACAC  bbbb  1313  4  2017/09/07  ADAD  ccccc  131567  ：：：：   テーブルインデックス CREATE INDEX idx_d ON sbtest1(d)

3. ソート – LIMIT 句の効果  • 「LIMIT句+ORDER BYカラムのインデックス」の効果が落ちるケース  • 1.
LIMIT 句が深い      • 2. WHERE条件にマッチするレコードがなかなか見つからない  SELECT * FROM sbtest1 WHERE d < '2014-10-01' ORDER BY c LIMIT 1000000, 10; SELECT * FROM people WHERE age > 200 ORDER BY height LIMIT 10;

ソートを最適化するのが  必ずしもベストではない 

3.ソート – WHEREかORDER BYか   • インデックスは原則１テーブルにつき１つしか利用されない※      
• どっちの処理を最適化する？    SELECT * FROM sbtest1 WHERE d < '2014-10-01' ORDER BY c LIMIT 10; インデックス対象動き WHERE 狙いのインデックスカラム d d < '2014-10-01' を満たすレコードの検索をインデックスで効率化。条件を満たすレコードを抽出してからソートし、上位10件のみ返す。 ORDER BY 狙いのインデックスカラム c インデックスを使って、ソートを最適化（スキップ）。カラム c の順にレコードを確認し、d < '2014-10-01' を満たすかチェック。10件集まったら、処理完了。 ※ 厳密にいうとインデックスマージにより複数使われる場合もある

3.ソート  • WHERE 狙いのインデックス  • 全体のうちWHERE条件を満たすレコード数が少ない  • ソートしたとしても、件数が少なければ負荷は低い    •
ORDER BY狙いのインデックス  • 全体のうちWHERE条件を満たすレコード数が多い  • LIMIT句による打ち切りに期待できるケース   

3.ソート - 降順インデックス  • MySQL 8.0 (Aurora V3) でサポート  •
MySQL 5.7 以前はASCのみ（DESCでも無視して、ASCで作成）  a  h  …  a-p r-z r  …  z  …  …  z  r  …  z-r p-a h  …  a  …  …  CREATE INDEX idx_col1 ON tbl(col1 ASC) CREATE INDEX idx_col1 ON tbl(col1 DESC)

4.JOIN  • 代表的なテーブルのJOINアルゴリズム  • Nested Loop Join (NLJ)  • Hash
Join  • Merge Sort Join 

4.JOIN – NLJ の概要  • 片方のテーブルから一行ずつ読み取り、もう一方のテーブルのレコードを結合していく  • 一般的なプログラミングの for/foreach
のイメージ  SELECT * FROM employees emp INNER JOIN salaries sal WHERE emp.emp_no = sal.emp_no AND emp.hire_date >= '1995-01-01' and sal.salary >= 133000; emp_no  hire_date  first_name  1  2019/10/01  Georgi  2  1982/03/05  Bezalel  3  2000/12/10  Parto  4  2017/09/07  Chirstian  ：：： emp_no  salary  from_date  1  160000  2019/10/01  1  180000  2019/12/15  2  190000  1982/04/01  2  200000  1983/04/01  3  130000  2000/12/10  3  140000  2010/05/13  ：：：外部表（駆動表）内部表：

4.JOIN – NLJ の概要  • INNER JOINでは、どちらのテーブルも外部表になり得る       
  • LEFT/RIGHT OUTER JOIN では外部表は固定    SELECT emp.emp_no, first_name, salary FROM employees emp LEFT JOIN salaries sal WHERE emp.emp_no = sal.emp_no AND emp.hire_date >= '1995-01-01' and sal.salary >= 133000 emp_no  hire_date  first_name  1  2019/10/01  Georgi  2  1982/03/05  Bezalel  3  2000/12/10  Parto  4  2017/09/07  Chirstian  ：：： emp_no  salary  from_date  1  160000  2019/10/01  1  180000  2019/12/15  2  190000  1982/04/01  2  200000  1983/04/01  3  130000  2000/12/10  3  140000  2010/05/13  ：：：外部表：

4.JOIN – EXPLAIN  • 同じ id を持つ行の上にある表が外部表  mysql> EXPLAIN SELECT
* FROM employees emp INNER JOIN salaries sal WHERE emp.emp_no = sal.emp_no AND emp.hire_date >= '1995-01-01' and sal.salary >= 133000; +----+-------------+-------+------+---------------+---------+------------+--------+----------+------------- + | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+---------+------------+--------+----------+------------- + | 1 | SIMPLE | emp | ALL | PRIMARY | NULL | NULL | 299645 | 33.33 | Using where | | 1 | SIMPLE | sal | ref | PRIMARY | PRIMARY | emp.emp_no | 10 | 33.33 | Using where | +----+-------------+-------+------+---------------+---------+------------+--------+----------+------------- + 外部表

4.JOIN - NLJ のチューニング  1. 外部表の読み取りを最適化する  2. 内部表の読み取りを最適化する  3. 適切な外部表の選択 

4.JOIN – 外部表の読み取りの最適化  mysql> EXPLAIN SELECT * FROM employees emp
INNER JOIN salaries sal WHERE emp.emp_no = sal.emp_no AND emp.hire_date >= '1995-01-01' and sal.salary >= 133000; +----+-------------+-------+------+---------------+---------+------------+--------+----------+-------------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+---------+------------+--------+----------+-------------+ | 1 | SIMPLE | emp | ALL | PRIMARY | NULL | NULL | 299645 | 33.33 | Using where | | 1 | SIMPLE | sal | ref | PRIMARY | PRIMARY | emp.emp_no | 10 | 33.33 | Using where | +----+-------------+-------+------+---------------+---------+------------+--------+----------+-------------+ • 外部表の最適化を考える 

4.JOIN – 外部表の読み取りの最適化  • 外部表だけのSELECT文を考えて、チューニングする  • 結合条件以外の条件、ソートを抽出  SELECT emp.emp_no, emp.first_name,
sal.salary FROM employees emp INNER JOIN salaries sal WHERE emp.emp_no = sal.emp_no AND emp.hire_date >= '1995-01-01' and sal.salary >= 133000 SELECT emp.emp_no, emp.first_name FROM employees emp WHERE emp.hire_date >= '1995-01-01' emp_no  hire_date  first_name  1  2019/10/01  AAAA  2  2019/03/05  ABAB  3  2018/12/10  ACAC  4  2017/09/07  ADAD  ：：：

mysql> EXPLAIN SELECT * FROM employees emp INNER JOIN salaries
sal WHERE emp.emp_no = sal.emp_no AND emp.hire_date >= '1995-01-01' and sal.salary >= 133000; +----+-------------+-------+------+---------------+---------+------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+------------+--------+-------------+ | 1 | SIMPLE | emp | ALL | PRIMARY | NULL | NULL | 299645 | Using where | | 1 | SIMPLE | sal | ref | PRIMARY | PRIMARY | emp.emp_no | 10 | Using where | +----+-------------+-------+------+---------------+---------+------------+--------+-------------+ 4.JOIN – 外部表の読み取りの最適化  • hire_date にインデックスを追加    mysql> CREATE INDEX idx_hire_date ON employees(hire_date); mysql> EXPLAIN SELECT * FROM employees emp INNER JOIN salaries sal WHERE emp.emp_no = sal.emp_no AND hire_date >= '1995-01-01' and sal.salary >= 133000; +----+-------------+-------+-------+-----------------------+---------------+------------+-------+-----------------------+ | id | select_type | table | type | possible_keys | key | ref | rows | Extra | +----+-------------+-------+-------+-----------------------+---------------+------------+-------+-----------------------+ | 1 | SIMPLE | emp | range | PRIMARY,idx_hire_date | idx_hire_date | NULL | 66194 | Using index condition | | 1 | SIMPLE | sal | ref | PRIMARY | PRIMARY | emp.emp_no | 10 | Using where | +----+-------------+-------+-------+-----------------------+---------------+------------+-------+-----------------------+

4.JOIN – 内部表の読み取りの最適化  • 同様に、内部表だけのSELECT文を考えて、チューニングする  • 内部表の場合は結合条件も残す  SELECT emp.emp_no, emp.first_name,
sal.salary FROM employees emp INNER JOIN salaries sal WHERE emp.emp_no = sal.emp_no AND emp.hire_date >= '1995-01-01' and sal.salary >= 133000 SELECT salary FROM salaries sal WHERE sal.emp_no = ? sal.salary >= 133000 emp_no  salary  from_date  1  160000  2019/10/01  1  180000  2019/12/15  2  190000  1982/04/01  2  200000  1983/04/01  3  130000  2000/12/10  3  140000  2010/05/13  ：：：

4.JOIN – 内部表の読み取りの最適化  mysql> EXPLAIN SELECT * FROM employees emp
INNER JOIN salaries sal WHERE emp.emp_no = sal.emp_no AND emp.hire_date >= '1995-01-01' and sal.salary >= 133000; +----+-------------+-------+------+---------------+---------+------------+--------+----------+-------------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+---------+------------+--------+----------+-------------+ | 1 | SIMPLE | emp | ALL | PRIMARY | NULL | NULL | 299645 | 33.33 | Using where | | 1 | SIMPLE | sal | ref | PRIMARY | PRIMARY | emp.emp_no | 10 | 33.33 | Using where | +----+-------------+-------+------+---------------+---------+------------+--------+----------+-------------+ • 今回のサンプルではPK引きなのでチューニングの余地なし 

4.JOIN - NLJ のチューニング  1. 外部表の読み取りを最適化する  2. 内部表の読み取りを最適化する  3. 適切な外部表の選択 

4.JOIN – 適切な外部表の選択  emp_no  hire_date  first_name  1  2019/10/01  Georgi  2 
1982/03/05  Bezalel  3  2000/12/10  Parto  4  2017/09/07  Chirstian  ：：： emp_no  salary  from_date  1  160000  2019/10/01  1  180000  2019/12/15  2  190000  1982/04/01  2  200000  1983/04/01  3  130000  2000/12/10  3  140000  2010/05/13  ：：：外部表 x ? 回： emp_no  hire_date  first_name  1  2019/10/01  Georgi  2  1982/03/05  Bezalel  3  2000/12/10  Parto  4  2017/09/07  Chirstian  ：：： emp_no  salary  from_date  1  160000  2019/10/01  1  180000  2019/12/15  2  190000  1982/04/01  2  200000  1983/04/01  3  130000  2000/12/10  3  140000  2010/05/13  ：：：外部表： x ? 回 employees salaries

4.JOIN – 適切な外部表の選択  • それぞれのテーブルから結合条件を除外した、件数を考える  SELECT COUNT(*) FROM employees emp
WHERE hire_date >= '1995-01-01' SELECT COUNT(*) FROM salaries sal WHERE sal.salary >= 133000 SELECT emp.emp_no, emp.first_name, sal.salary FROM employees emp INNER JOIN salaries sal WHERE emp.emp_no = sal.emp_no AND emp.hire_date >= '1995-01-01' AND sal.salary >= 133000

4.JOIN – 適切な外部表の選択  • それぞれのテーブルから結合条件を除外した、件数を考える  SELECT COUNT(*) FROM employees emp
WHERE hire_date >= '1995-01-01' SELECT COUNT(*) FROM salaries sal WHERE sal.salary >= 150000 SELECT emp.emp_no, first_name, salary FROM employees emp INNER JOIN salaries sal WHERE emp.emp_no = sal.emp_no AND emp.hire_date >= '1990-01-01' AND sal.salary >= 150000 34000 件 1000 件

4.JOIN – 適切な外部表の選択  emp_no  hire_date  first_name  1  2019/10/01  Georgi  2 
1982/03/05  Bezalel  3  2000/12/10  Parto  4  2017/09/07  Chirstian  ：：： emp_no  salary  from_date  1  160000  2019/10/01  1  180000  2019/12/15  2  190000  1982/04/01  2  200000  1983/04/01  3  130000  2000/12/10  3  140000  2010/05/13  ：：：外部表 x 1000 ： emp_no  hire_date  first_name  1  2019/10/01  Georgi  2  1982/03/05  Bezalel  3  2000/12/10  Parto  4  2017/09/07  Chirstian  ：：： emp_no  salary  from_date  1  160000  2019/10/01  1  180000  2019/12/15  2  190000  1982/04/01  2  200000  1983/04/01  3  130000  2000/12/10  3  140000  2010/05/13  ：：：外部表： x 34000 employees salaries

4.JOIN – 適切な外部表の選択  • 小さな外部表を選択するようチューニングしてみる  • 外部表により作成するインデックスも変わる  外部表内部表ループ回数
作成するインデックス employees salaries 34000 employees (hire_date) - ※ PK利用 salaries employees 1000 - ※ PK利用 salaries (salary)

• salary にインデックスを追加   mysql> SELECT * FROM employees emp INNER
JOIN salaries sal WHERE emp.emp_no = sal.emp_no AND hire_date >= '1995-01-01' and sal.salary >= 133000; +--------+------------+------------+-----------+--------+------------+--------+--------+------------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | emp_no | salary | from_date | to_date | +--------+------------+------------+-----------+--------+------------+--------+--------+------------+------------+ | 20004 | 1952-03-07 | Radoslaw | Pfau | M | 1995-11-24 | 20004 | 133112 | 1998-11-23 | 1999-07-16 | | 77152 | 1962-12-23 | Geraldo | Bednarek | F | 1995-07-09 | 77152 | 133637 | 2001-07-07 | 2002-07-07 | | 77152 | 1962-12-23 | Geraldo | Bednarek | F | 1995-07-09 | 77152 | 133731 | 2002-07-07 | 9999-01-01 | +--------+------------+------------+-----------+--------+------------+--------+--------+------------+------------+ 3 rows in set (0.31 sec) 4.JOIN – 適切な外部表の選択  mysql> SELECT * FROM employees emp INNER JOIN salaries sal WHERE emp.emp_no = sal.emp_no AND hire_date >= '1995-01-01' and sal.salary >= 133000; +--------+------------+------------+-----------+--------+------------+--------+--------+------------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | emp_no | salary | from_date | to_date | +--------+------------+------------+-----------+--------+------------+--------+--------+------------+------------+ | 20004 | 1952-03-07 | Radoslaw | Pfau | M | 1995-11-24 | 20004 | 133112 | 1998-11-23 | 1999-07-16 | | 77152 | 1962-12-23 | Geraldo | Bednarek | F | 1995-07-09 | 77152 | 133637 | 2001-07-07 | 2002-07-07 | | 77152 | 1962-12-23 | Geraldo | Bednarek | F | 1995-07-09 | 77152 | 133731 | 2002-07-07 | 9999-01-01 | +--------+------------+------------+-----------+--------+------------+--------+--------+------------+------------+ 3 rows in set (0.02 sec)

更新系クエリ 

5.大量INSERT  • ソートする  • １コミットにまとめる  • MySQLはコミット時にディスクに書く  INSERT INTO tbl
(col1, col2, col3) VALUES(‘11111’, ‘aaaaa’, ‘AAAAA’); INSERT INTO tbl (col1, col2, col3) VALUES(‘22222’, ‘bbbbb’, ‘BBBBB’); ： BEGIN; INSERT INTO tbl (col1, col2, col3) VALUES(‘11111’, ‘aaaaa’, ‘AAAAA’); INSERT INTO tbl (col1, col2, col3) VALUES(‘22222’, ‘bbbbb’, ‘BBBBB’); ： COMMIT;

5.大量INSERT  • AP-DBの通信の往復を減らすとなお良い  INSERT INTO tbl (col1, col2, col3) VALUES(‘11111’,
‘aaaaa’, ‘AAAAA’), (‘22222’, ‘bbbbb’, ‘BBBBB’)...; ： BEGIN; INSERT INTO tbl (col1, col2, col3) VALUES(‘11111’, ‘aaaaa’, ‘AAAAA’); INSERT INTO tbl (col1, col2, col3) VALUES(‘22222’, ‘bbbbb’, ‘BBBBB’); ： COMMIT;

6.大量DELETE  • 全件削除  • TRUNCATEを使う  • TRUNCATE＝DROP+CREATE  • 注意：AUTO_INCREMENT がリセットされる 
  • 月や日単位での削除  • 日付のレンジパーティション + DROP PARTITION が有効 

6.大量DELETE  • 広範囲にロックを取らないように工夫  • MySQLは削除する行だけでなく、スキャンした行をロックする    • 対象を一旦SELECTし、その後、主キーである程度まとめて消していく  rows =
execute(“SELECT id FROM tbl WHERE expired < NOW() and flag = 0 ORDER BY id”) for (i = 0; i < len(rows); i = i + 50) execute(“DELETE FROM tbl WHERE id IN (:id1, :id2, :id3, :id4, :id5, …)”)

まとめ  • 用途にあった、DBMSを選定しましょう  • 遅いクエリを見つけて、実行計画を確認し、改善する  • インデックスを作成するときには、カーディナリティ、カラム順を考慮 

Thanks 

MySQL の SQL クエリチューニングの要所を掴む勉強会

MySQL の SQL クエリチューニングの要所を掴む勉強会

More Decks by ANDPAD inc

Other Decks in Technology

Featured

Transcript