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

Slide 1

Slide 1 text

MySQL SQL チューニング 2024.03.21

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

得意・不得意あるよ 

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

Hybrid Transaction  Analytical Processing  (HTAP) 

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

MySQL Heatwave 

Slide 15

Slide 15 text

MySQL Heatwave 

Slide 16

Slide 16 text

TiDB 

Slide 17

Slide 17 text

TiDB 

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

スロークエリログ  • 実行時間が閾値を超えた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; 実行時間読み取ったレコード数行ロック時間は含まれないため、参考にしない

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

実行計画の取得  • 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 を省略して記載しています

Slide 38

Slide 38 text

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 | +----+-------------+-------+------+---------------+---------+--------------------+--------+----------+-------+

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

EXPLAINの出力形式 

Slide 41

Slide 41 text

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)

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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)

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

インデックスヒント / 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;

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

• ツリー構造  • データはソートされている  • リーフノード（末端）に値と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  …  … 

Slide 51

Slide 51 text

インデックスの構造      • ツリーを辿ることで目的のデータに素早くアクセスできる  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  …  … 

Slide 52

Slide 52 text

カーディナリティについて  > 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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

複合インデックス 

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

複合インデックスの構造  • カラムの順序に従ってデータがソートされる  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 

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

Slide 66

Slide 66 text

Slide 67

Slide 67 text

Slide 68

Slide 68 text

Slide 69

Slide 69 text

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.

Slide 70

Slide 70 text

Slide 71

Slide 71 text

Slide 72

Slide 72 text

Slide 73

Slide 73 text

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)

Slide 74

Slide 74 text

Slide 75

Slide 75 text

Slide 76

Slide 76 text

Slide 77

Slide 77 text

Slide 78

Slide 78 text

Slide 79

Slide 79 text

Slide 80

Slide 80 text

Slide 81

Slide 81 text

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;

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

Slide 84

Slide 84 text

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件集まったら、処理完了。 ※ 厳密にいうとインデックスマージにより複数使われる場合もある

Slide 85

Slide 85 text

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

Slide 86

Slide 86 text

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)

Slide 87

Slide 87 text

Slide 88

Slide 88 text

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

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  ：：：外部表（駆動表）内部表：

Slide 91

Slide 91 text

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  ：：：外部表：

Slide 92

Slide 92 text

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 | +----+-------------+-------+------+---------------+---------+------------+--------+----------+------------- + 外部表

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

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 | +----+-------------+-------+------+---------------+---------+------------+--------+----------+-------------+ • 外部表の最適化を考える 

Slide 95

Slide 95 text

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  ：：：

Slide 96

Slide 96 text

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 | +----+-------------+-------+-------+-----------------------+---------------+------------+-------+-----------------------+

Slide 97

Slide 97 text

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  ：：：

Slide 98

Slide 98 text

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引きなのでチューニングの余地なし 

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

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

Slide 102

Slide 102 text

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 件

Slide 103

Slide 103 text

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

Slide 104

Slide 104 text

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

Slide 105

Slide 105 text

Slide 106

Slide 106 text

• 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)

Slide 107

Slide 107 text

更新系クエリ 

Slide 108

Slide 108 text

Slide 109

Slide 109 text

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;

Slide 110

Slide 110 text

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;

Slide 111

Slide 111 text

Slide 112

Slide 112 text

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

Slide 113

Slide 113 text

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, …)”)

Slide 114

Slide 114 text

Slide 115

Slide 115 text

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

Slide 116

Slide 116 text

Thanks 