Window関数: RANK SELECT name, dept_id AS dept, salary, RANK() OVER w AS `rank` FROM employee WINDOW w AS (PARTITION BY dept_id ORDER BY salary DESC); name dept_id salary rank Newt NULL 75000 1 Ed 10 100000 1 Newt 10 80000 2 Fred 10 70000 3 Michael 10 70000 3 Jon 10 60000 5 Dag 10 NULL 6 Pete 20 65000 1 Lebedev 20 65000 1 Jeff 30 300000 1 Will 30 70000 2 27
SELECT… FOR UPDATE の拡張 SELECT * FROM tickets WHERE id IN (1,2,3,4) AND order_id IS NULL FOR UPDATE NOWAIT; SELECT * FROM tickets WHERE id IN (1,2,3,4) AND order_id IS NULL FOR UPDATE SKIP LOCKED; 行が既にロックされ ていれば、直ぐに エラーを返す 行が既にロックされてい れば、その行に対する ロック取得はあきらめる 29
オプティマイザヒントの拡張 • SET_VARヒント – SQL単位でシステム変数を変更できるヒント – セッション単位で変更可能なシステム変数をSQL単位で変更可能に （max_allowed_packetなど一部のセッション変数は変更不可） SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name; INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2); SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=ON') */ name,region FROM country WHERE region='Eastern Asia'; 使用例 31
MySQL 8.0: リソースグループの例 0 20,000 40,000 60,000 80,000 100,000 120,000 140,000 160,000 No Resource Group With Resource Group Queries per Second Select Update System Configuration : Oracle Linux 7, Intel(R) Xeon(R) CPU E7-4860 2.27GHz 40 cores-HT (40 Cores Shared) (40 Cores for Select) (10 Cores for Update RG) 32
• const（次ページ参照）の特殊なケース • システムテーブルへのアクセス Type: system 46 typeの値 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
• インデックスを使って、特定の範囲の レコードを取得する • BETWEENや不等号(>, <=, <, >=)、INを使うと 出現 Type: range 55 typeの値 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
• インデックスフルスキャン • 以下の2つのケースで出現 – インデックスをフルスキャンすることでテーブル フルスキャンを回避できる場合 （Extraに Using index が表示される） – テーブルフルスキャン実行時に、ソート処理を回避 するためにインデックスを使用できる場合 （Extraに Using index が表示されない） • 効率よさそうな名前だが、実際は重い 処理の場合もある – 可能な場合は、インデックスで絞り込めるよう テーブル構造かSQLを変更すべき Type: index 56 typeの値 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
Extra 64 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • クエリーを解決する方法に関連する追加情報 Using filesort Using index Using index condition Using index for group-by Using join buffer (Block Nested Loop) Using join buffer (Batched Key Access) Using MRR Using temporary Using where
• ソートに必要な領域がメモリ上の領域 （sort_buffer_size）を超えた場合に出現 • 行数が少ない場合を除き、非常に遅い • JOIN時、JOIN実行後にファイルソートする 場合は特に遅くなる – EXPLAINの最初の行にUsing temporary; Using filesortが表示される Extra: Using filesort 65 Using filesort Using index Using index condition Using index for group-by Using join buffer (Block Nested Loop) Using join buffer (Batched Key Access) Using MRR Using temporary Using where
• インデックスにアクセスするだけでクエリー を完結できる場合に出現 • 行データにアクセスする必要が無いため、 非常に高速 Extra: Using index 66 Using filesort Using index Using index condition Using index for group-by Using join buffer (Block Nested Loop) Using join buffer (Batched Key Access) Using MRR Using temporary Using where
• Using index condition – インデックスコンディションプッシュダウンを行う （ストレージエンジン側でWHERE句による絞込みを 行う） • Using index for group-by – GROUP BY または DISTINCT を使ったクエリーを、 インデックスだけで処理できる Extra: Using index condition / Using index for group-by 67 Using filesort Using index Using index condition Using index for group-by Using join buffer (Block Nested Loop) Using join buffer (Batched Key Access) Using MRR Using temporary Using where
• JOINバッファを使用して結合処理を行う • 結合アルゴリズムによって、Block Nested Loop / Batched Key Access が出力される Extra: Using join buffer 68 Using filesort Using index Using index condition Using index for group-by Using join buffer (Block Nested Loop) Using join buffer (Batched Key Access) Using MRR Using temporary Using where
• MRR(Multi-Range Read)最適化を行う – テーブルが大きくストレージエンジンのキャッシュに 格納されていない場合、セカンダリインデックスでの 範囲スキャンを使用して行を読み取る時に、ベース テーブルへのランダムディスクアクセスが多発する 場合がある – Disk-Sweep Multi-Range Read (MRR) 最適化を使用す ると、MySQLは最初にインデックスだけをスキャンし、 該当する行のキーを収集することによって、範囲ス キャンのランダムディスクアクセスの回数を軽減する – [MRRの動作] インデックスだけをスキャン => ソート => 主キーの順番にベーステーブルから行を取得 Extra: Using MRR 69 Using filesort Using index Using index condition Using index for group-by Using join buffer (Block Nested Loop) Using join buffer (Batched Key Access) Using MRR Using temporary Using where
• クエリーの実行に内部的なテンポラリ テーブルを使う場合に出現 • 遅くなる傾向あり • 以下のような処理で出現 – JOIN後にソート – GROUP BYとORDER BYの併用 – UNION – 集合関数（SUM等） 、、、など Extra: Using temporary 70 Using filesort Using index Using index condition Using index for group-by Using join buffer (Block Nested Loop) Using join buffer (Batched Key Access) Using MRR Using temporary Using where
• 行をフェッチした後に、更にWHERE条件で の絞り込みが必要な場合に出現 • WHERE句があって、インデックスが使われ ていない場合にこれが出てくる • インデックスが使われても、インデックスで の検索後に更にもう一段階WHEREによる 絞込みが必要な場合にも出てくる Extra: Using where 71 Using filesort Using index Using index condition Using index for group-by Using join buffer (Block Nested Loop) Using join buffer (Batched Key Access) Using MRR Using temporary Using where
TREE形式のEXPLAINも開発中 • MySQL 8.0.16のリリースノートより抜粋 – 「Added an experimental tree format for EXPLAIN output, which prints the generated iterator tree, and is intended to help users understand how execution was actually set up. EXPLAINFORMAT=TREE is currently unsupported in production and both its syntax and output are subject to change in subsequent versions of MySQL.」 • 参考情報 – TREE explain format in MySQL 8.0.16 https://gdsotirov.blogspot.com/2019/06/tree-explain-format-in-mysql-8016.html ※現時点では本番環境での使用はサポートされていません。 また、今後のバージョンでフォーマットが変更される可能性もあります。 77
yoshiakiyamasaki
ryosk7
oracle4engineer
cyberagentdevelopers
rkaga
ufried
smt7174
yuu26
mujyun
freee
masahirokawahara
marcelosomers
62gerente
garrettdimon
philhawksworth
jponch
holman
eileencodes
colly
chriscoyier
marktimemedia
bluesmoon
