ヤフー社内でやってるMySQLチューニングセミナー大公開

3.5K Views

February 22, 16

スライド概要

ヤフー社内でやってるMySQLチューニングセミナー大公開

profile-image

2023年10月からSpeaker Deckに移行しました。最新情報はこちらをご覧ください。 https://speakerdeck.com/lycorptech_jp

シェア

埋め込む »CMSなどでJSが使えない場合

関連スライド

各ページのテキスト
1.

ヤフー社内でやってる MySQLチューニングセミナー 大公開 2016/02/20 MyNA , JPUG 合同勉強会

2.

ヤフーはどんなRDB使ってるの? 2

3.

Yahoo! JAPANのRDB環境 Oracle Database MySQL Percona • 11g RAC Enterprise Edition • MySQL 5.1 (RR,Mixed) Percona 5.5 (RR,Mixed) Percona 5.6 (RC,RBR,GTID) • 約200DB • 約500DB • サーバ 200台, Exadata もあるよ • サーバ 300台

5.

自己紹介 • 三谷 智史(@mita2) • 所属 DBMS技術 RDB専門部隊 13名 • DB Administration 黒帯 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止 5

6.

DBAあるある • へんなクエリ流されて、負荷が爆発 • SQLがスパゲッティすぎて解読不能 RDBを正しく使ってもらうために、 社内でセミナーを実施しています。 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止 6

7.

ヤフー社内でやってる MySQLチューニングセミナー大公開 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止 7

8.

ご注意 • 個人の経験をもとに記載しています。間違った情報が含ま れてたりするかもしれません。 • Version 5.5 を前提に記載しております。最新のバージョ ンでは改善されているケースもあります。 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止 8

9.

本日の流れ 1. インデックスについて 2. SQLチューニング手順 1. 重いクエリの洗い出し 2. 実行計画の確認 3. SQLチューニング例 ※ InnoDBを前提。MyISAMはもう卒業しましょう。 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

10.

インデックスについて

11.

なぜ、インデックスで高速化するのか? • インデックスはツリー構造 • データはソートされている • リーフノードに値と行のPKを格納 SELECT * FROM t WHERE col1 = ‘f’ インデックス対象 a-p r-z a d f p r t pk=1 pk=3 pk=2 pk=9 pk=4 pk=5 PK col1 time 1 a 11:15 2 f 01:10 3 d 03:01 : : :

12.

カーディナリティ mysql> show index from t; +-----+----------+----------+------------+-<SNIP>-+-----------+-------------+ |Table|Non_unique| Key_name |Seq_in_index| | Collation | Cardinality | +-----+----------+----------+------------+--------+-----------+-------------+ | t | 0| PRIMARY | 1| | A | 5 | +-----+----------+----------+------------+--------+-----------+-------------+ 1 row in set (0.00 sec) インデックス内のユニークな値 の多さを表した指数 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

13.

カーディナリティとは • カーディナリティが低いと効果が少ない • カーディナリティが高いデータの例 • AUTO_INCREMENT、住所、更新日時 • カーディナリティが低いデータの例 • フラグ、カテゴリ、都道府県 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

14.

なぜ効果が少ないのか? SELECT * FROM t WHERE is_pk_even_num = 1 AND time <= 12:00 0 1 pk=1 pk=3 pk=5 : : : pk=2 pk=4 pk=6 : : : × 50万回 インデックス対象 PK is_pk_even_num (PKが偶数か?) time 1 0 11:15 2 1 12:31 3 0 13:44 4 1 14:01 : : : 1000000 1 20:01 • 半分までしか絞り込めない • インデックスを経由することによる オーバーヘッドが無視できない

15.

例外 SELECT * FROM t WHERE is_pk_1000multi = 1 AND time <= 12:00 インデックス対象 0 1 pk=1 pk=2 pk=3 : : : pk=1000 pk=2000 pk=3000 : PK is_pk_1000multi (PKが1000の倍数?) time 1 0 11:15 : 0 12:31 1000 1 13:44 : : 14:01 : : : 1 20:01 × 1000000 1000回 • 分布が偏っていれば効果が大きくなる • 条件によって効果が異なる Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

16.

Left-Most-Index 以下のインデックスは同じ? インデックスA. CREATE INDEX idxA1 ON mytable (col1); CREATE INDEX idxA2 ON mytable (col2); インデックスB. CREATE INDEX idxB1 ON mytable (col1, col2); インデックスC. CREATE INDEX idxC1 ON mytable (col2, col1); 違います Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

17.

複合インデックスでは指定順が重要 インデックスB. CREATE INDEX idxB1 ON mytable (col1, col2); a col1 a b~c col2 9 a pk=1 8 b pk=2 7 b pk=3 6 c pk=4 5 pk=5 インデックスC. CREATE INDEX idxC1 ON mytable (col2, col1); 7~9 5~6 col2 5 col1 c pk=1 6 b pk=4 7 b pk=2 8 a pk=3 9 a pk=5

18.

複合インデックスでは指定順が重要 インデックスA. CREATE INDEX idxA1 ON mytable (col1); CREATE INDEX idxA2 ON mytable (col2); インデックスB. CREATE INDEX idxB1 ON mytable (col1, col2); インデックスC. CREATE INDEX idxC1 ON mytable (col2, col1); 絞り込み対象カラム → 1.WHRER col1 = ‘x’ 2.WHERE col2 = ‘x’ 3.WHERE col1 = ‘x’ AND col2 = ‘x’ インデックスA idxA1 が利用される idxA2 が利用される idxA1, idxA2 どちらかが利用される。 ※ インデックスB idxB1 が利用される インデックスは利 用されない idxB1 が利用される。 idxA1, idxA2より高速。 インデックスC インデックスは利 用されない idxC1 が利用される idxC1が利用される。 idxA1, idxA2より高速。 ※ 場合によってはインデックスマージ

19.

インデックスを貼るときのポイント • カーディナリティの高いカラムを選ぶ • 複合インデックスのカラムの順に注意 • 更新性能・キャッシュ効率が低下するため、 必要なものだけに貼る Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

20.

SQLチューニング手順

21.

SQLチューニングの手順 1. 重いクエリランキングを出す 2. 実行計画を確認する 3. チューニング 1. SQLの書き換え 2. インデックスを貼る 3. テーブル構成をみなおす etc Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

22.

SQLチューニングの手順 1. 重いクエリランキングを出す 2. 実行計画を確認する 3. チューニング 1. SQLの書き換え 2. インデックスを貼る 3. テーブル構成をみなおす etc Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

23.

重いSQLの洗い出し方 STEP1 スロークエリーログに全SQLを記録 $ sudo rm mysqld-slow.log $ mysql –u user_name –p mysql> SET GLOBAL long_query_time = 0; mysql> SET GLOBAL log_slow_verbosity=full; mysql> FLUSH LOGS; スキャンした行数 実行時間 # User@Host: user[user] @ [1.2.3.4] # Query_time: 0.067361 Lock_time: 0.000030 Rows_sent: 16 SET timestamp=1346772142; SELECT zpam.zip_cd FROM zip_area_mast zpam WHERE zpam.point_cd = 11276 AND zpam.del_flag = 0; : テーブルロックした時間 行ロックした時間はカウン トされていので注意! Rows_examined: 121031 結果の行数

24.

重いSQLの洗い出し方 STEP2 • Percona Tookit の pt-query-digest で集計 $ sudo pt-query-digest --group-by fingerprint¥ --order-by Query_time:sum ¥ --since '2015-06-14 00:00:00' --until '2015-06-14 23:59:59' ¥ mysqld-slow.log 合計実行時間でソート オプション ソート条件 Query_time:avg 平均実行時間 Query_time:sum 合計実行時間 InnoDB_IO_r_bytes:sum バッファプールにヒットせず、DISKから読み取った データ量 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

25.

集計結果 # Query 5: 39.92 QPS, 229.19kx concurrency, ID 0x72BFE142E32896BD at byte 88442 # Time range: 2015-10-29 23:00:00 to 23:59:59 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= =======平均実行時間 ======= ======= 合計実行回数 # Count 31 143676 (1クエリあたり) # Exec time 11 660s 150us 623ms 5ms 18ms 10ms 490us # Lock time 20 13s 32us 5ms 91us 113us 30us 89us レスポンス行数(1クエリあたり) # Rows sent 0 264.02k 0 4 1.88 2.90 0.69 1.96 # Rows examine 0 264.02k 0 4 1.88 2.90 0.69 1.96 処理過程で読み込んだ行数 # Rows affecte 0 0 0 0 0 0 0 0 (1クエリあたり) # Rows read 0 264.02k 0 4 1.88 2.90 0.69 1.96 # Bytes sent 0 16.80M 83 171 122.61 143.84 17.33 124.25 # Merge passes 0 0 0 0 0 0 0 0 # Tmp tables 0 0 0 0 0 0 0 0 # Tmp disk tbl 0 0 0 0 0 0 0 0 # Tmp tbl size 0 0 0 0 0 0 0 0 # Query size 18 26.14M 148 271 190.81 246.02 51.01 143.84 キャッシュヒットせずに # InnoDB: DISKから読んだバイト数 # IO r bytes 9 786.66M 0 80.00k 5.61k 15.96k 9.17k 0 # IO r ops 9 49.17k 0 5 0.35 0.99 0.57 0 # IO r wait 14 569s 0 620ms 4ms 17ms 10ms クエリ0 <省略> SELECT topic_id FROM weather WHERE (consumeruri = ‘abc') AND (consumeruri_type = 'android')¥G

26.

SQLチューニングの手順 1. 重いクエリランキングを出す 2. 実行計画を確認する 3. チューニング 1. SQLの書き換え 2. インデックスを貼る 3. テーブル構成をみなおす etc Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

27.

実行計画の確認 • 実行計画=クエリの処理の流れ • 「EXPLAIN」をクエリの先頭に付ける • 更新系クエリはSELECTに書き換え UPDATE t SET col = newvalue WHERE condition = ‘x’; EXPLAIN SELECT col FROM t WHERE condition = ‘x’; Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

28.

EXPLAINの結果の例 mysql> EXPLAIN SELECT t1.pk, t1.col1, t2.col2 FROM t1 INNER JOIN t2 ON (t1.pk = t2.fk) WHERE t1.pk = 1; +----+------------+------+------+--------------------+--------+--------+------+-----+-------------+ | id | select_type| table| type | possible_keys | key | key_len| ref | rows| Extra | +----+------------+------+------+--------------------+--------+--------+------+-----+-------------+ | 1 | SIMPLE | t1 | const| PRIMARY,pk_col1_idx| PRIMARY| 4 | const| 1| | | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 5| Using where | +----+------------+------+------+--------------------+--------+--------+------+-----+-------------+ カラム 説明 id SELECTごとに振られるID。処理順ではない点に注意。 select_type SELECTの種類。SIMPLE, SUBQUERY, UNIONなど。 type テーブルへのアクセス方法。インデックスの利用有無、読取範囲などがわかる。 possible_keys 利用可能なインデックス。 key 実際に利用されたインデックス。possible_keys からインデックスの内容や統計情報を加味して、選択さ れたインデックス。 key_len 読み取ったインデックスのバイト数。 ref 比較するカラム。constの場合は定数(WHERE x = 1のような場合)。 rows スキャンする見積もり行数。JOINやサブクエリが関係する場合は外部表のrows × 内部表のrowsがス キャンする行になる。 Extra その他の情報

29.

SQLチューニングの手順 1. 重いクエリランキングを出す 2. 実行計画を確認する 3. チューニング 1. SQLの書き換え 2. インデックスを貼る 3. テーブル構成をみなおす etc Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

30.

チューニング例

31.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さてい て rows が大きい 5. 大量更新 6. データ削除 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

32.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さてい て rows が大きい 5. 大量更新 6. データ削除 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

33.
[beta]
【1.フルスキャン】 TYPE=ALL
mysql> EXPLAIN SELECT push_id FROM push_info
111万行に対し
WHERE deleted=1 AND datediff(now(), mod_date) > '180';
て絞り込み
+--+-----------+---------+----+-------------+----+-------+----+-------+--------------------------+
|id|select_type|table
|type|possible_keys|key |key_len|ref |rows |Extra
|
+--+-----------+---------+----+-------------+----+-------+----+-------+--------------------------+
|1 |SIMPLE
|push_info|ALL |NULL
|NULL|NULL |NULL|1115182|Using where;
|
+--+-----------+---------+----+-------------+----+-------+----+-------+--------------------------+

全部読み込み

行数多い

mysql> SHOW INDEX FROM push_info;
+-----------+------------+--------------------+--+----------+--------+------+------------+
| Table
| Non_unique | Key_name
|~| Sub_part | Packed | Null | Index_type |
+-----------+------------+--------------------+--+----------+--------+------+------------+
| push_info |
0 | PRIMARY
|~|
NULL | NULL |
| BTREE
|
+-----------+------------+--------------------+--+----------+--------+------+------------+

•
•
•

mod_dateとdeletedにインデックスを貼るべき
関数を適用した場合インデックスは効かない
すべての行に対してdatediffを実行してるのも×
• mod_date < date_sub(NOW(), INTERVAL 180 DAY) と書くべき

34.

インデックスが効かない条件 Extra 関数 式 否定構文 WHERE datediff(now(), mod_date) > '180' WHERE col1 / 2 = 0 WHERE col1 != 3 LIKE検索 ※ 前方一致除く WHERE col1 LIKE ‘%string%’ WHERE col1 LIKE ‘%string’ Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

35.
[beta]
【1.フルスキャン】チューニング結果
mysql> CREATE INDEX idx01 ON push_info(deleted, mod_date);

mysql> EXPLAIN SELECT push_id FROM push_info
WHERE deleted=1 AND mod_date < date_sub(NOW(), INTERVAL 180 DAY);
+----+-------------+-----------+-------+-------+------+------+--------------------------+
| id | select_type | table
| type | key | ref | rows | Extra
|
+----+-------------+-----------+-------+-------+------+------+--------------------------+
| 1 | SIMPLE
| push_info | range | idx01 | NULL | 10 |
Using index |
+----+-------------+-----------+-------+-------+------+------+--------------------------+

インデックスの
範囲読み込み

行数が10行

インデックスだ
けで解決でき
るクエリ

• Before: 380msec → After: 0msec

Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

36.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さていて rows が大きい 5. 大量更新 6. データ削除 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

37.

【2.ソート】 mysql> EXPLAIN SELECT order_time,seller_id,image_id,item_id,<省略> FROM order_master WHERE is_hidden_page = '0' ORDER BY order_time DESC LIMIT 20; +--+-----------+--------------+----+---------------+----+----------+-----------------------------+ |id|select_type| table |type| possible_keys |key | rows | Extra | +--+-----------+--------------+----+---------------+----+----------+-----------------------------+ | 1|SIMPLE | order_master |ALL | NULL |NULL| 12200494 | Using where; Using filesort | +--+-----------+--------------+----+---------------+----+----------+-----------------------------+ 1200万件 ソートしている • is_hidden_page は殆どの行で0 • カーディナリティが低いため× • ソートにインデックスは有効か? • LIMIT句がある場合に効果が高い Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

38.
[beta]
【2.ソート】チューニング結果
mysql> CREATE INDEX OM_OTM ON order_master (order_time);

mysql> EXPLAIN SELECT order_time,seller_id,image_id,item_id,<省略>
FROM order_master WHERE is_hidden_page = '0'
ORDER BY order_time DESC LIMIT 20;
+--+-------------+--------------+-----+---------------+--------+------+-------------+
|id| select_type | table
|type | possible_keys | key
| rows | Extra
|
+--+-------------+--------------+-----+---------------+--------+------+-------------+
| 1| SIMPLE
| order_master |index| NULL
| OM_OTM |
20 | Using where |
+--+-------------+--------------+-----+---------------+--------+------+-------------+

インデックス利用

20個だけ取る

ソートがなくなる

• インデックスはソート済みのため、ソートが不要
• 上位20件だけ取ったら、処理を終了
• Before: 4300sec → After: 0sec
Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

39.

【2.ソート】 CALC_FOUND_ROWSはNG • SQL_CALC_FOUND_ROWS句 • 条件にマッチしたレコードが全部で何件あったかを返す • LIMIT句と一緒に使う • 「全部で何件」= LIMIT句がないのと同じ Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

40.

【2.ソート】 考慮事項 • 1度に使われるインデックスは、 各テーブルにつき1つ • 選択が必要 • ソートを最適化する • 絞り込み(WHERE句)を最適化する Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

41.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さていて rows が大きい 5. 大量更新 6. データ削除 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

42.

【3.相関サブクエリ】 mysql> EXPLAIN SELECT team_name FROM team WHERE team_id IN (SELECT team_id FROM member WHERE skill IN ("C")); +----+--------------------+--------+------+---------------+省略+------+-------------+ | id | select_type | table | type | possible_keys | | rows | Extra | +----+--------------------+--------+------+---------------+----+------+-------------+ | 1 | PRIMARY | team | ALL | NULL | | 3 | Using where | | 2 | DEPENDENT SUBQUERY | member | ALL | NULL | | 5 | Using where | +----+--------------------+--------+------+---------------+----+------+-------------+ ■ team team_id ■ member team_name team_id member_name skill 1 Team1 1 Yahoo Taro C 2 Team2 1 Yahoo Jiro Python 3 Team3 2 Yahoo Hanako Ruby 2 Yahoo Saburo PHP 3 Yahoo Sirou Perl Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

43.

【3.相関サブクエリ】 期待する動作 最初に実行される mysql> EXPLAIN SELECT team_name ことを期待 FROM team WHERE team_id IN (SELECT team_id FROM member WHERE skill IN ("C")); ①skill=Cを探す ②team_id=1を探す team_id team_name team_id member_name skill 1 Team1 1 Yahoo Taro C 2 Team2 1 Yahoo Jiro Python 3 Team3 2 Yahoo Hanako Ruby 2 Yahoo Saburo PHP 3 Yahoo Sirou Perl • memberテーブルを1回読み込む • teamテーブルを1回読み込む • 合計=5 + 3 = 8行の読み込みを期待する

44.

【3.相関サブクエリ】 実際の動き mysql> EXPLAIN SELECT team_name FROM team WHERE team_id IN (SELECT team_id FROM member WHERE skill IN ("C")); team_id =1 AND skill=C のレコードを探す team_id member_name skill team_id team_name 1 Yahoo Taro C 1 Team1 1 Yahoo Jiro Python 2 Team2 2 Yahoo Hanako Ruby 3 Team3 2 Yahoo Saburo PHP 3 Yahoo Sirou Perl • 合計= 3 + 3 * 5 = 18行の読み込み • 実行計画の「PRIMARY」は外部表(親)を示す • なぜ、期待どうりに動かないのか? • MySQLの仕様です。。。(5.6で改善されます)

45.

【3.相関サブクエリ】 チューニング方法(1) +----+--------------------+--------+------+---------------+省略+------+-------------+ | id | select_type | table | type | possible_keys | | rows | Extra | +----+--------------------+--------+------+---------------+----+------+-------------+ | 1 | PRIMARY | team | ALL | NULL | | 3 | Using where | | 2 | DEPENDENT SUBQUERY | member | ALL | NULL | | 5 | Using where | +----+--------------------+--------+------+---------------+----+------+-------------+ インデックス未使用 mysql> CREATE INDEX idx_skill ON member(skill, team_id); mysql> EXPLAIN SELECT team_name FROM team WHERE team_id IN (SELECT team_id FROM member WHERE skill IN ("C")); +----+--------------------+--------+------+---------------+-----------+省略+------+-------------+ | id | select_type | table | type | possible_keys | key | | rows | Extra | +----+--------------------+--------+------+---------------+-----------+省略+------+-------------+ | 1 | PRIMARY | team | ALL | NULL | NULL | | 3 | Using where | | 2 | DEPENDENT SUBQUERY | member | ref | idx_skill | idx_skill | | 1 | Using where | +----+--------------------+--------+------+---------------+-----------+省略+------+-------------+ •サブクエリの実行にインデックスを利用 •3 + 3 * 1 = 6行に(見積もり)

46.

【3.相関サブクエリ】 チューニング方法(2) +----+--------------------+--------+------+---------------+-----------+省略+------+-------------+ | id | select_type | table | type | possible_keys | key | | rows | Extra | +----+--------------------+--------+------+---------------+-----------+省略+------+-------------+ | 1 | PRIMARY | team | ALL | NULL | NULL | | 3 | Using where | | 2 | DEPENDENT SUBQUERY | member | ref | idx_skill | idx_skill | | 1 | Using where | +----+--------------------+--------+------+---------------+-----------+省略+------+-------------+ mysql> EXPLAIN SELECT team_name FROM team JOIN member USING(team_id) WHERE member.skill IN ("C"); +----+-------------+--------+--------+---------------+-----------+---------------------+------+ | id | select_type | table | type | possible_keys | key | ref | rows | member表を先に読む +----+-------------+--------+--------+---------------+-----------+---------------------+------+ | 1 | SIMPLE | member | ref | idx_skill | idx_skill | const | 1 | | 1 | SIMPLE | team | eq_ref | PRIMARY | PRIMARY | test.member.team_id | 1 | +----+-------------+--------+--------+---------------+-----------+---------------------+------+ • JOINに書き換える • 相関サブクエリ、だいたいJOINで書き直せる • オプティマイザが親子を自動判断 • member表を外部表(駆動表)にする

47.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さ ていて rows が大きい 5. 大量更新 6. データ削除 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

48.

【4.JOIN】NL結合 • MySQLは「NestedLoop結合」のみをサポート • どんな結合条件でも対応可 • 最速とは限らないがバランスの良いアルゴリズム Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

49.

【4.JOIN】NL結合 • NLはループの中にSELECTが入っているイメージ mysql> EXPLAIN SELECT prefecture FROM prefecture INNER JOIN people USING(pref_id) WHERE people.age >= 120; +--+-----------+----------+----+-------------+-----------+--------------------+------+-----------+ 上に表示される |id|select_type|table |type|possible_keys|key | ref |rows |Extra | 表が外部表 +--+-----------+----------+----+-------------+-----------+--------------------+------+-----------+ | 1|SIMPLE |prefecture|ALL |pref_id |NULL | NULL | 47| | | 1|SIMPLE |people |ref |pref_id_idx |pref_id_idx| prefecture.pref_id |173891|Using where| +--+-----------+----------+----+-------------+-----------+--------------------+------+-----------+ pref_id prefecture pref_id pref_idカラムのインデックスを 利用して内部表からデータを取得 name age 1 Hokkaido 1 Yamada 20 2 Aomori 1 Takana 33 3 Iwate 2 Yamamoto 12 2 Ogawa 76 3 Inoue 90 ■ prefecture : : ■ cities - pref_id_idx ■ people : :

50.

【4.JOIN】NL結合 mysql> EXPLAIN SELECT prefecture FROM prefecture INNER JOIN people USING(pref_id) 17万行から WHERE people.age >= 120; age>=120を探す +--+-----------+----------+----+-------------+-----------+--------------------+------+-----------+ |id|select_type|table |type|possible_keys|key | ref |rows |Extra | +--+-----------+----------+----+-------------+-----------+--------------------+------+-----------+ | 1|SIMPLE |prefecture|ALL |pref_id |NULL | NULL | 47| | | 1|SIMPLE |people |ref |pref_id_idx |pref_id_idx| prefecture.pref_id |173891|Using where| +--+-----------+----------+----+-------------+-----------+--------------------+------+-----------+ ちょっと大きめ 47 x 17万行 スキャン ■ prefecture ■ people pref_id prefecture pref_id name age 1 Hokkaido 1 Yamada 20 2 Aomori 1 Takana 33 3 Iwate 2 Yamamoto 12 2 Ogawa 76 3 Inoue 90 : ■ cities - pref_id_idx x47 : : 17万行

51.

【4.JOIN】チューニング • ループ回数を少なくする • 結合条件以外の条件にマッチする件数を テーブルごとにカウント • 件数が少ないほうを外部表にする mysql> EXPLAIN SELECT prefecture FROM prefecture INNER JOIN people USING(pref_id) WHERE people.age >= 120; mysql> SELECT COUNT(*) FROM prefecture; +----------+ | COUNT(*) | +----------+ | 47 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM people where age >=120; +----------+ | COUNT(*) | +----------+ | 3 | +----------+ 1 row in set (0.70 sec) Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

52.

【4.JOIN】チューニング ・ 狙うべき動作 ■ people - age_idx pref_id prefecture 1 Hokkaido 2 ~~~ Aomori : ~~~ 41 Saga ■ people pref_id name age 1 Tokugawa 121 2 Toyotomi 125 ~~~~ ~~~~ ~~~~ 41 Akechi 130 3 Inoue 90 ※ MySQLはクラスタインデックスのため主キー=テーブルで実際はツリーです。

53.

【4.JOIN】チューニング mysql> CREATE INDEX age_idx ON people(age); Query OK, 0 rows affected (24.97 sec) mysql> EXPLAIN SELECT prefecture FROM prefecture INNER JOIN people USING(pref_id) 外部表が入れ替 WHERE people.age >= 120; +--+-----------+----------+------+--------------------+-------+---------------+----+-----------+ わった ループは3回 |id|select_type|table |type |possible_keys |key | ref |rows|Extra | +--+-----------+----------+------+--------------------+-------+---------------+----+-----------+ | 1|SIMPLE |people |range |pref_id_idx,age_idx |age_idx| NULL | 3|Using where| | 1|SIMPLE |prefecture|eq_ref|pref_id |pref_id| people.pref_id| 1|Using where| +--+-----------+----------+------+--------------------+-------+---------------+----+-----------+ • 3 x 1 行スキャン • Before: 4.3sec → After: 0sec Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止 1回のループに つき1行だけ

54.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さてい て rows が大きい 5. 大量更新 6. データ削除

55.

【6.大量の更新】 INSERT INTO quote_real_info (ask10_flag, ask10_order, ask10_price, … <170カラムぐらい> VALUES (NULL, NULL, '1150', '2012-06-13 15:10:00', '0', '900', …); INSERT INTO quote_real_info (ask10_flag, ask10_order, ask10_price, … <170カラムぐらい> VALUES (NULL, NULL, '1150', '2012-06-13 15:10:00', '0', '900', …); : : バルクインサートにより性能向上 INSERT INTO quote_real_info (ask10_flag, ask10_order, ask10_price, … <170カラムぐらい> VALUES (NULL, NULL, '1150’, '2012-06-13 15:10:00’, '0’, '900’, …), (NULL, NULL, '1152’, '2012-06-13 15:10:01’, '0’, '300', …), (NULL, NULL, '1153', '2012-06-13 15:10:00', '0', '100', …), (NULL, NULL, '1250', '2012-06-13 15:10:01', '0', '920', …), (NULL, NULL, '1151', '2012-06-13 15:10:00', '0', '500', …), 5万レコード (NULL, NULL, '1155', '2012-06-13 15:10:01', '0', '150', …)); /sec

56.

【6.大量の更新】チューニング方法 • RDBは通常コミット毎にDiskに書き込む •× Auto-Commit • バルクインサートはサーバ・クライアント間の通信 回数を減らせるため、より高速 高速 • 1件1件コミット • 複数件コミット • バルクインサート Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

57.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さてい て rows が大きい 5. 大量更新 6. データ削除 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

58.

【7.テーブルの削除】 DELETE FROM push_message; TRUNCATE TABLE push_message ; • TRUNCATE = DROP + CREATE • 注意:AUTO_INCREMENTがリセット • 条件を指定したい • パーティション化 + [DROP|TRUNCATE] PARTITION Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

59.

チューニングの難しいクエリ

60.

必要なデータが大きいもの SELECT COUNT(*) FROM access_log; SELECT SUM(a) FROM access_log WHERE data BETWEEN ‘1995-01-01’ AND NOW(); 必要なデータが大きいものは遅い • • • MySQLは1セッション=1スレッド=1CPU 作りから見直す必要がある 例) 集計テーブルを作り更新時に同時にカウ ントアップする Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

61.

まとめ

62.

まとめ • カーディナリティとキーの指定順に注意 • スロークエリログで犯人を特定 • EXPLAINで分析 • チューニング方法は様々・ケースバイケース • → 経験あるのみ! Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

63.

おしらせ

64.

MySQL Casual Slack • MySQL Casual の Slack (チャット) • 「MySQL Casual Slack」 で検索 Copyright (C) 2015 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止 64

65.

ありがとうございました