Share
Embed
Transcript
116 slides
-
自己紹介 • 三谷 智史(@mita2) • 好きなデータベース:MySQL • 2020.3 ~ ANDPADのデータベース技術顧問 •
相談は、DM or #dev_dbパフォーマンスチューニング へどうぞ • http://mita2db.hateblo.jp/ -
アジェンダ • MySQL の得意なこと、苦手なこと • データベースのチューニング手段と特徴 • SQLチューニングの流れ • インデックス
• SQLチューニング例 • まとめ -
アジェンダ • MySQL の得意なこと、苦手なこと • データベースのチューニング手段と特徴 • SQLチューニングの流れ • インデックス
• SQLチューニング例 • まとめ -
データベースとは • データベース • 検索や蓄積が容易にできるよう整理された情報の集まり • DBMS (Database Management
System) • データベースをコンピューター上で管理するための仕組み -
ワークロード • OLTP • Online Transaction Processing • 少数の行・多くのカラムを処理 •
参照・更新の頻度に偏りがある • クイックなレスポンスを期待する • OLAP/DWH • OnLine Analytical Processing • Data WareHouse • 集計・解析 • 大量の行の少数のカラムを処理する • OLTPほど高速なレスポンスは求められない -
ワークロード • OLTP • Online Transaction Processing • 少数の行・多くのカラムを処理 •
参照・更新の頻度に偏りがある • クイックなレスポンスを期待する • OLAP/DWH • OnLine Analytical Processing • Data WareHouse • 集計・解析 • 大量の行の少数のカラムを処理する • OLTPほど高速なレスポンスは求められない -
ワークロード • OLTP • Online Transaction Processing • 少数の行・多くのカラムを処理 •
参照・更新の頻度に偏りがある • クイックなレスポンスを期待する • OLAP/DWH • OnLine Analytical Processing • Data WareHouse • 集計・解析 • 大量の行の少数のカラムを処理する • OLTPほど高速なレスポンスは求められない -
HTAPな製品 • Oracle Exadata • MySQL Heatwave • TiDB •
Google AlloyDB -
ワークロードとエンジン • HTAPは、エンジンを自動的に使い分けることで、「両対応」 • 1つのデータベース(エンジン)で 両方のワークロードを扱うのは今でも困難 • それだけ、エンジンとワークロードのミスマッチは致命的 •
MySQL (InnoDB) で、大規模な集計・解析のクエリは高速に処理できない -
ここまでのまとめ • MySQL は OLTP が得意な (高速に処理できる) DB •
広範囲の SUM や COUNT を高速に実行したいなら別のDBと併用 • 例)ログをMySQLに保存し、集計 • 👌ある程度サマリーした結果をMySQLに保存し、集計 -
アジェンダ • MySQL の得意なこと、苦手なこと • データベースのチューニング手段と特徴 • SQLチューニングの流れ • インデックス
• SQLチューニング例 • まとめ -
DBのチューニング方法の比較 方法 内容 対象 効果 SQLチューニング SQLの変更 インデックスの追加 など 個別のSQL
何十倍改善 することも パラメータ チューニング DBの設定変更 利用しているHWに適した設定 に変更するなど DB全体 数%程度 -
アジェンダ • MySQL の得意なこと、苦手なこと • データベースのチューニング手段と特徴 • SQLチューニングの流れ • インデックス
• SQLチューニング例 • まとめ -
SQLチューニングのゴール • 性能要件を満たすこと • レイテンシ、スループット、インフラコスト etc.. • よくある間違い •
全てのSQLを限界まで最適化された状態にする -
対象となる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; 実行時間 読み取った レコード数 行ロック時間は含ま れないため、参考 にしない -
AWS Performance Insight • インスタンスごとに、ASS (Avg Active Session) 上位25件を確認可能 •
軽いSQLでも実行回数が多いと上位に 社外非公開 -
Rows_sent vs Rows_examined • Rows_examined • 読み取った行数 • Rows_sent •
結果セットの行数 • Rows_examined >> Rows_sent ならチューニング余地がある可能性が高い ※ 集約関数(SUM, COUNT, DISTINCT etc..)は外す必要あり -
実行計画 • 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 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; -
アジェンダ • MySQL の得意なこと、苦手なこと • データベースのチューニング手段と特徴 • SQLチューニングの流れ • インデックス
• SQLチューニング例 • まとめ -
• ツリー構造 • データはソートされている • リーフノード(末端)に値と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 に絞り込める。インデック スによる絞り込み効果が出る。 -
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 -
アジェンダ • MySQL の得意なこと、苦手なこと • データベースのチューニング手段と特徴 • SQLチューニングの流れ • インデックス
• SQLチューニング例 • まとめ -
SQLチューニング例 1. テーブルフルスキャン 2. インデックスフルスキャンとカバーリングインデックス 3. ソート 4. JOIN 5.
大量INSERT 6. 大量DELETE -
SQLチューニング例 1. テーブルフルスキャン 2. インデックスフルスキャンとカバーリングインデックス 3. ソート 4. JOIN 5.
大量INSERT 6. 大量DELETE -
1.テーブルフルスキャン • rowsが大きい • type = ALL で key が
NULL • possible_keys も NULL • → 適用できるインデックスがないのでテーブルを全部読み取っている mysql> EXPLAIN SELECT * FROM sbtest1 WHERE DATEDIFF(d, '2014-06-01') < 0; +----+-------------+---------+------+---------------+------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | ref | rows | Extra | +----+-------------+---------+------+---------------+------+------+--------+-------------+ | 1 | SIMPLE | sbtest1 | ALL | NULL | NULL | NULL | 986400 | Using where | +----+-------------+---------+------+---------------+------+------+--------+-------------+ -
1.テーブルフルスキャン - 式インデックス • MySQL 8.0 / Aurora v3 で式インデックスがサポート
mysql> EXPLAIN SELECT * FROM sbtest1 WHERE DATEDIFF(d, '2014-06-01') < 0; +----+-------------+---------+------+---------------+------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | ref | rows | Extra | +----+-------------+---------+------+---------------+------+------+--------+-------------+ | 1 | SIMPLE | sbtest1 | ALL | NULL | NULL | NULL | 986400 | Using where | +----+-------------+---------+------+---------------+------+------+--------+-------------+ 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 -
1.テーブルフルスキャン - 式インデックス • チューニング後 mysql> EXPLAIN SELECT * FROM
sbtest1 WHERE DATEDIFF(d, '2014-06-01') < 0; +----+-------------+---------+-------+---------------+-----------+------+-------+------------------------+ | id | select_type | table | type | possible_keys | key | ref | rows | Extra | +----+-------------+---------+-------+---------------+-----------+------+-------+------------------------+ | 1 | SIMPLE | sbtest1 | range | func_idx1 | func_idx1 | NULL | 72648 | Using where; Using MRR | +----+-------------+---------+-------+---------------+-----------+------+-------+------------------------+ -
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. -
1.テーブルフルスキャン • WHERE条件である d にインデックスを追加 • WHERE句を書き換え • d <
'2014-06-01' mysql> CREATE INDEX idx_d ON sbtest1(d); mysql> EXPLAIN SELECT * FROM sbtest1 WHERE d < "2014-06-01"; +----+-------------+---------+-------+---------------+-------+------+-------+----------+---------------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+---------+-------+---------------+-------+------+-------+----------+---------------+ | 1 | SIMPLE | sbtest1 | range | idx_d | idx_d | NULL | 71516 | 100.00 | Using index ~ | +----+-------------+---------+-------+---------------+-------+------+-------+----------+---------------+ -
SQLチューニング例 1. テーブルフルスキャン 2. インデックスフルスキャンとカバーリングインデックス 3. ソート 4. JOIN 5.
大量INSERT 6. 大量DELETE -
2.インデックスフルキャン • rows が大きい • type = index • possible_keys
が NULL だが、 key がある • → type = index は インデックスのフルスキャンを表す mysql> EXPLAIN SELECT d FROM sbtest1 WHERE DATEDIFF(NOW(), d) > 3000; +----+-------------+---------+-------+---------------+-------+------+--------+----------+-----------~ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra +----+-------------+---------+-------+---------------+-------+------+--------+----------+-----------~ | 1 | SIMPLE | sbtest1 | index | NULL | idx_d | NULL | 986400 | 100.00 | Using index ~ +----+-------------+---------+-------+---------------+-------+------+--------+----------+-----------~ -
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) -
2.インデックスフルキャン • テーブルの代わりに、インデックスをフルスキャン • 「インデックス」という響きから、効率的な処理と勘違いしがち mysql> EXPLAIN SELECT d FROM
sbtest1 WHERE DATEDIFF(NOW(), d) > 3000; +----+-------------+---------+-------+---------------+-------+------+--------+----------+-----------~ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra +----+-------------+---------+-------+---------------+-------+------+--------+----------+-----------~ | 1 | SIMPLE | sbtest1 | index | NULL | idx_d | NULL | 986400 | 100.00 | Using index ~ +----+-------------+---------+-------+---------------+-------+------+--------+----------+-----------~ mysql> EXPLAIN SELECT d, k FROM sbtest1 WHERE DATEDIFF(NOW(), d) > 3000; +----+-------------+---------+------+---------------+------+------+--------+----------+-------------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+---------+------+---------------+------+------+--------+----------+-------------+ | 1 | SIMPLE | sbtest1 | ALL | NULL | NULL | NULL | 986400 | 100.00 | Using where | +----+-------------+---------+------+---------------+------+------+--------+----------+-------------+ 参照するカラムを追加すると、 テーブルフルスキャン (type=ALL)に -
2.インデックスフルキャン • チューニング方法 • インデックスを使って、絞り込めるよう、関数を除外 mysql> EXPLAIN SELECT d FROM
sbtest1 WHERE d < NOW() - INTERVAL 3000 DAY; +----+-------------+---------+-------+---------------+-------+------+--------+----------+-------------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+---------+-------+---------------+-------+------+--------+----------+-------------+ | 1 | SIMPLE | sbtest1 | range | idx_d | idx_d | NULL | 385994 | 100.00 | Using index~ +----+-------------+---------+-------+---------------+-------+------+--------+----------+-------------+ -
2. カバーリングインデックス mysql> EXPLAIN SELECT d, k FROM sbtest1 WHERE
d < NOW() - INTERVAL 3500 DAY; +----+-------------+---------+-------+---------------+-------+------+-------+----------+--------- | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra +----+-------------+---------+-------+---------------+-------+------+-------+----------+--------- | 1 | SIMPLE | sbtest1 | range | idx_d | idx_d | NULL | 80822 | 100.00 | ~ | +----+-------------+---------+-------+---------------+-------+------+-------+----------+--------- mysql> CREATE INDEX idx_d_k ON sbtest1(d, k); mysql> EXPLAIN SELECT d, k FROM sbtest1 WHERE d < NOW() - INTERVAL 3500 DAY; +----+-------------+---------+-------+---------------+---------+------+-------+----------+---------------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+---------+-------+---------------+---------+------+-------+----------+---------------+ | 1 | SIMPLE | sbtest1 | range | idx_d,idx_d_k | idx_d_k | NULL | 82266 | 100.00 | Using index ~ | +----+-------------+---------+-------+---------------+---------+------+-------+----------+---------------+ • Extra = Using index は「カバーリングインデックス」を表す • 読取りがインデックスで完結していることを表す(テーブルを見てない) • 参照するカラムのみで構成するインデックスを作成し、読取りを減らすテクニック -
SQLチューニング例 1. テーブルフルスキャン 2. インデックスフルスキャンとカバーリングインデックス 3. ソート 4. JOIN 5.
大量INSERT 6. 大量DELETE -
3.ソート • Using filesort • ソート処理が行われていることを表す • CPU負荷の原因になっているケースが多い • 「file」と付いているが特に意識する必要はない
LAIN SELECT * FROM sbtest1 WHERE d < '2014-10-01' ORDER BY c LIMIT 10; +----+-------------+---------+-------+---------------+-------+------+--------+----------+------------------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+---------+-------+---------------+-------+------+--------+----------+------------------+ | 1 | SIMPLE | sbtest1 | range | idx_d | idx_d | NULL | 141556 | 100.00 | ~ Using filesort | +----+-------------+---------+-------+---------------+-------+------+--------+----------+------------------+ -
3.ソート • インデックスは対象のカラムでソートされている • ツリーを辿ることで、ソート済みデータが手に入る mysql> CREATE INDEX idx_c
ON sbtest1(c); Query OK, 0 rows affected (30.40 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM sbtest1 WHERE d < '2014-10-01' ORDER BY c LIMIT 10; +----+-------------+---------+-------+---------------+-------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+---------+-------+---------------+-------+------+------+----------+-------------+ | 1 | SIMPLE | sbtest1 | index | idx_d | idx_c | NULL | 69 | 14.35 | Using where | +----+-------------+---------+-------+---------------+-------+------+------+----------+-------------+ Using filesort が消える -
3. ソート – LIMIT 句の効果 • LIMIT句と組み合わせると効果が高い • インデックスを使って、カラム c
の順番にデータを読み取っていき、WHERE 条件 を満たすレコードが 10件 見つかったら完了 mysql> EXPLAIN SELECT * FROM sbtest1 WHERE d < '2014-10-01' ORDER BY c LIMIT 10; +----+-------------+---------+-------+---------------+-------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+---------+-------+---------------+-------+------+------+----------+-------------+ | 1 | SIMPLE | sbtest1 | index | idx_d | idx_c | NULL | 69 | 14.35 | Using where | +----+-------------+---------+-------+---------------+-------+------+------+----------+-------------+ -
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. ソート – LIMIT 句の効果 • 試しに LIMIT 句を外してみると? mysql>
EXPLAIN SELECT * FROM sbtest1 WHERE d < '2014-10-01' ORDER BY c; +----+-------------+---------+-------+---------------+-------+------+--------+----------+----------------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+---------+-------+---------------+-------+------+--------+----------+----------------+ | 1 | SIMPLE | sbtest1 | range | idx_d | idx_d | NULL | 141556 | 100.00 | Using filesort | +----+-------------+---------+-------+---------------+-------+------+--------+----------+----------------+ mysql> EXPLAIN SELECT * FROM sbtest1 WHERE d < '2014-10-01' ORDER BY c LIMIT 10; +----+-------------+---------+-------+---------------+-------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+---------+-------+---------------+-------+------+------+----------+-------------+ | 1 | SIMPLE | sbtest1 | index | idx_d | idx_c | NULL | 69 | 14.35 | Using where | +----+-------------+---------+-------+---------------+-------+------+------+----------+-------------+ -
3.ソート – WHEREかORDER BYか • インデックスは原則1テーブルにつき1つしか利用されない※
• どっちの処理を最適化する? 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) -
3.ソート - 降順インデックス • ASC,DESC がまざった複数カラムによるORDER BYの最適化が可能に mysql> CREATE INDEX
idx_d_asc_c_asc ON sbtest1(d ASC, c ASC); mysql> EXPLAIN SELECT * FROM sbtest1 ORDER BY d ASC, c DESC LIMIT 10; +----+-------------+---------+------+---------------+------+------+--------+----------+----------------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+---------+------+---------------+------+------+--------+----------+----------------+ | 1 | SIMPLE | sbtest1 | ALL | NULL | NULL | NULL | 986400 | 100.00 | Using filesort | +----+-------------+---------+------+---------------+------+------+--------+----------+----------------+ mysql> CREATE INDEX idx_d_asc_c_desc ON sbtest1(d ASC, c DESC); mysql> EXPLAIN SELECT * FROM sbtest1 ORDER BY d ASC, c DESC LIMIT 10; +----+-------------+---------+-------+---------------+------------------+------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra | +----+-------------+---------+-------+---------------+------------------+------+------+----------+-------+ | 1 | SIMPLE | sbtest1 | index | NULL | idx_d_asc_c_desc | NULL | 10 | 100.00 | NULL | +----+-------------+---------+-------+---------------+------------------+------+------+----------+-------+ -
SQLチューニング例 1. テーブルフルスキャン 2. インデックスフルスキャンとカバーリングインデックス 3. ソート 4. JOIN 5.
大量INSERT 6. 大量DELETE -
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 – 外部表の読み取りの最適化 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 – 適切な外部表の選択 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) -
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 – 適切な外部表の選択 • salary にインデックスを追加 mysql> CREATE INDEX idx_salary ON salaries(salary); 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 | sal | range | PRIMARY,idx_salary | idx_salary | NULL | 1027 | Using index condition | | 1 | SIMPLE | emp | eq_ref | PRIMARY,idx_hire_date | PRIMARY | emp.sal.emp_no | 1 | Using where | +----+-------------+-------+--------+-----------------------+------------+----------------+------+-----------------------+ 外部表が入れ替わった -
• 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) -
SQLチューニング例 1. テーブルフルスキャン 2. インデックスフルスキャンとカバーリングインデックス 3. ソート 4. JOIN 5.
大量INSERT 6. 大量DELETE -
5.大量INSERT • ソートする • 1コミットにまとめる • 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; -
SQLチューニング例 1. テーブルフルスキャン 2. インデックスフルスキャンとカバーリングインデックス 3. ソート 4. JOIN 5.
大量INSERT 6. 大量DELETE -
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, …)”) -
アジェンダ • MySQL の得意なこと、苦手なこと • データベースのチューニング手段と特徴 • SQLチューニングの流れ • インデックス
• SQLチューニング例 • まとめ