MySQLとインデックスと私

2021/05/24 サイボウズ開運研修

動画が以下のサイトからリンクされています
- https://blog.cybozu.io/entry/2021/07/20/100000

- これに矢印を書きながらぐりぐりやっていたわけなので、資料単体だとわかりづらいと思います…

Transcript

1. MySQLとインデックスと私 愛するあなたのため 2021/05/24 yoku0825 開発運用研修2021 データベース(?)
2. はじめに 昨年(?)の三苫さんの資料がとても網羅的でわかりやすいので絶対に読んでおくと 良いと思います データベース ‐ データベース設計 (サイボウズ社内用の資料でした) ‐ USE THE INDEX, LUKE! もお勧めです SQLのインデックスとそのチューニングについてのオンラインブック ‐ 1/123
3. ＼おはようございます／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ サイボウズ MySQLコンサルタント ‐ 2/123
4. (この資料における)インデックス #とは MySQLのInnoDBストレージエンジンの話です。RDBMSやストレージエンジンが 変わると多少事情は変わります イメージしやすくするための「たとえ」がほとんどなので、内部実装的な話はさらに事情が変 わります ‐ MySQLで一番よく使われる、B+Treeインデックスのことについて説明します InnoDBにおける「インデックス」はPRIMARY KEYのことも含みます。InnoDBで は「行データ」は「PRIMARY KEYのB+Treeのリーフ」にぶら下がっています 3/123
5. インデックス #とは 「主に検索のために」 「行データの部分集合を」 「あらかじめソートして複製したもの」 「ロックの単位でもある」 4/123
6. サンプルデータ MySQL :: Other MySQL Documentation から world database (world_x の方では ない) をダウンロードして入れ込みます $ wget https://downloads.mysql.com/docs/world.sql.gz $ zcat world.sql.gz | mysql80 $ mysql80 mysql> use world mysql> SHOW TABLES; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | 5/123
7. サンプルデータ 説明しやすくするために country_row_data テーブルと country_index_continent テーブルを作ります 長くなりすぎないようにカラムをそぎ落としました ‐ mysql> CREATE TABLE country_row_data ( -> clustered_index CHAR(3) NOT NULL PRIMARY KEY, -> data TEXT NOT NULL -> ); mysql> INSERT INTO country_row_data (clustered_index, data) -> SELECT code AS clustered_index, -> JSON_OBJECT('code', code, 'name', name, 'continent', continent, 'population', population) AS data -> FROM country; mysql> SELECT * FROM country_row_data LIMIT 3; +-----------------+--------------------------------------------------------------------------------------+ | clustered_index | data | +-----------------+--------------------------------------------------------------------------------------+ | ABW | {"code": "ABW", "name": "Aruba", "continent": "North America", "population": 103000} | | AFG | {"code": "AFG", "name": "Afghanistan", "continent": "Asia", "population": 22720000} | | AGO | {"code": "AGO", "name": "Angola", "continent": "Africa", "population": 12878000} | +-----------------+--------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) 6/123
8. サンプルデータ 説明しやすくするために country_row_data テーブルと country_index_continent テーブルを作ります mysql> CREATE TABLE country_index_continent ( -> continent varchar(32) NOT NULL, -> clustered_index CHAR(3) NOT NULL, -> KEY(continent) -> ); mysql> INSERT INTO country_index_continent (continent, clustered_index) -> SELECT continent, code AS clustered_index -> FROM country -> ORDER BY CAST(continent AS CHAR), code; mysql> SELECT * FROM country_index_continent LIMIT 3; +-----------+-----------------+ | continent | clustered_index | +-----------+-----------------+ | Asia | AFG | | Asia | ARE | 7/123
9. 行データの構造 PRIMARY KEY(code) です clustered_index はツリー化されているので、ここの検索は高速です(PRIMARY KEYによるイ ンデックスルックアップ) ‐ clustered_index 以外のカラムはひとまとまりになっているので、それ以外のカラムに触るとき は(必要のないカラムでも)行を全部取り出さないといけません row oriented(行指向)といいます ‐ +-----------------+--------------------------------------------------------------------------------------+ | clustered_index | data | +-----------------+--------------------------------------------------------------------------------------+ | ABW | {"code": "ABW", "name": "Aruba", "continent": "North America", "population": 103000} | | AFG | {"code": "AFG", "name": "Afghanistan", "continent": "Asia", "population": 22720000} | | AGO | {"code": "AGO", "name": "Angola", "continent": "Africa", "population": 12878000} | +-----------------+--------------------------------------------------------------------------------------+ 8/123
10. 行データの構造 ツリーじゃないけど雰囲気だけ感じ取ってください 9/123
11. INDEX(continent) 相当の構造 「部分集合」「あらかじめソート」のあたりを感じ取っていただきたい セカンダリーインデックスのリーフには「PRIMARY KEYの値」が書いてあります ‐ +-----------+-----------------+ | continent | clustered_index | +-----------+-----------------+ | Africa | AGO | | Africa | BDI | | Africa | BEN | +-----------+-----------------+ 10/123
12. INDEX(continent) 相当の構造 ツリーじゃないけど雰囲気だけ感じ取ってください 11/123
13. たとえば WHERE continent = Antarctica をインデックスで解決する処理 INDEX(continent) から continent = Antarctica をルックアップしてPRIMARY KEYの値を得ます mysql> SELECT continent, clustered_index -> FROM country_index_continent -> WHERE continent = 'Antarctica'; +------------+-----------------+ | continent | clustered_index | +------------+-----------------+ | Antarctica | ATA | | Antarctica | ATF | | Antarctica | BVT | | Antarctica | HMD | | Antarctica | SGS | +------------+-----------------+ 5 rows in set (0.00 sec) 12/123
14. INDEX(continent) から continent = Antarctica をルックアップ 13/123
15. INDEX(continent) から continent = Antarctica をルックアップ 14/123
16. INDEX(continent) から continent = Antarctica をルックアップ 15/123
17. たとえば WHERE continent = Antarctica をインデックスで解決する処理 得られた値を使ってPRIMARY KEYからルックアップします mysql> SELECT clustered_index, data -> FROM country_row_data -> WHERE clustered_index IN ('ATA', 'ATF', 'BVT', 'HMD', 'SGS'); +-----------------+--------------------------------------------------------------------------------------- ------------------------------+ | clustered_index | data | +-----------------+--------------------------------------------------------------------------------------- ------------------------------+ | ATA | {"code": "ATA", "name": "Antarctica", "continent": "Antarctica", "population": 0} | | ATF | {"code": "ATF", "name": "French Southern territories", "continent": "Antarctica", "p opulation": 0} | | BVT | {"code": "BVT", "name": "Bouvet Island", "continent": "Antarctica", "population": 0} | | HMD | {"code": "HMD", "name": "Heard Island and McDonald Islands", "continent": "Antarctic a", "population": 0} | | SGS | {"code": "SGS", "name": "South Georgia and the South Sandwich Islands", "continent": "Antarctica", "population": 0} | +-----------------+--------------------------------------------------------------------------------------- ------------------------------+ 5 rows in set (0.00 sec) 16/123
18. 得られた値を使ってPRIMARY KEYからルックアップ 17/123
19. 得られた値を使ってPRIMARY KEYからルックアップ 18/123
20. 得られた値を使ってPRIMARY KEYからルックアップ 19/123
21. 略 20/123
22. たとえば WHERE continent = Antarctica をインデックスで解決する処理 セカンダリーキーからルックアップしてPRIMARY KEYの値を得る 得られた値を使ってPRIMARY KEYからルックアップ 21/123
23. たとえば、インデックスで解決できない WHERE name = Japan を検索する処理 つまりテーブルスキャン mysql> SELECT clustered_index, data -> FROM country_row_data; +-----------------+----------------------------------------------------------------------------------------------- ----------------------+ | clustered_index | data | +-----------------+----------------------------------------------------------------------------------------------- ----------------------+ | ABW | {"code": "ABW", "name": "Aruba", "continent": "North America", "population": 103000} | | AFG | {"code": "AFG", "name": "Afghanistan", "continent": "Asia", "population": 22720000} | | AGO | {"code": "AGO", "name": "Angola", "continent": "Africa", "population": 12878000} | .. | ZAF | {"code": "ZAF", "name": "South Africa", "continent": "Africa", "population": 40377000} | | ZMB | {"code": "ZMB", "name": "Zambia", "continent": "Africa", "population": 9169000} | | ZWE | {"code": "ZWE", "name": "Zimbabwe", "continent": "Africa", "population": 11669000} | +-----------------+----------------------------------------------------------------------------------------------- ----------------------+ 239 rows in set (0.00 sec) 22/123
24. たとえば、インデックスで解決できない WHERE name = Japan を検索する処理 つまりテーブルスキャン 23/123
25. たとえば、インデックスで解決できない WHERE name = Japan を検索する処理 つまりテーブルスキャン 24/123
26. たとえば、インデックスで解決できない WHERE name = Japan を検索する処理 つまりテーブルスキャン 25/123
27. たとえば、インデックスで解決できない WHERE name = Japan を検索する処理 つまりテーブルスキャン 26/123
28. たとえば、インデックスで解決できない WHERE name = Japan を検索する処理 これをひたすら目(?)grep フェッチしてから追加のフィルタリング ‐ EXPLAINで Extra: Using where と表示されます ‐ +-----------------+------------------------------------------------------------------------------- --------------------------------------+ | clustered_index | data | +-----------------+------------------------------------------------------------------------------- --------------------------------------+ .. | JOR | {"code": "JOR", "name": "Jordan", "continent": "Asia", "population": 5083000 } | | JPN | {"code": "JPN", "name": "Japan", "continent": "Asia", "population": 12671400 0} | -- ここだ！ | KAZ | {"code": "KAZ", "name": "Kazakstan", "continent": "Asia", "population": 1622 3000} | -- けれど、他に "name = Japan" を満たすものがないという 保証はないので続けざるを得ない .. +-----------------+------------------------------------------------------------------------------- --------------------------------------+ 27/123
29. WHERE name = Japan を解決できるように INDEX(name) を作ることを考える 「ソート済の」「データのサブセット」を作る mysql> CREATE TABLE country_index_name ( -> name CHAR(52) NOT NULL, -> clustered_index CHAR(3) NOT NULL, -> KEY(name) -> ); mysql> INSERT INTO country_index_name (name, clustered_index) -> SELECT name, code AS clustered_index -> FROM country -> ORDER BY name, code; -- ソート済みの! mysql> SELECT name, clustered_index FROM country_index_name LIMIT 3; +-------------+-----------------+ | name | clustered_index | +-------------+-----------------+ | Afghanistan | AFG | 28/123
30. WHERE name = Japan を解決できるように INDEX(name) を作ることを考える そしてセカンダリーインデックスとPRIMARY KEYの二度引き mysql> SELECT name, clustered_index FROM country_index_name WHERE name = 'Japan'; +-------+-----------------+ | name | clustered_index | +-------+-----------------+ | Japan | JPN | +-------+-----------------+ 1 row in set (0.00 sec) mysql> SELECT clustered_index, data FROM country_row_data WHERE clustered_index = 'JPN'; +-----------------+------------------------------------------------------------------------------- -+ | clustered_index | data | +-----------------+------------------------------------------------------------------------------- -+ | JPN | {"code": "JPN", "name": "Japan", "continent": "Asia", "population": 12671400 0} | +-----------------+------------------------------------------------------------------------------- -+ 1 row in set (0.00 sec) 29/123
31. これが基本 的な動作 30/123
32. (イメージつ かめました?) 31/123
33. 複合(マルチカラム)インデックス やっぱり説明用に city_row_data を作ります mysql> CREATE TABLE city_row_data ( -> clustered_index INT NOT NULL PRIMARY KEY, -> data TEXT NOT NULL -> ); mysql> INSERT INTO city_row_data (clustered_index, data) -> SELECT id AS clustered_index, -> JSON_OBJECT('id', id, 'name', name, 'countrycode', countrycode, 'population', population) AS data -> FROM city; mysql> SELECT * FROM city_row_data LIMIT 3; +-----------------+---------------------------------------------------------------------------+ | clustered_index | data | +-----------------+---------------------------------------------------------------------------+ | 1 | {"id": 1, "name": "Kabul", "population": 1780000, "countrycode": "AFG"} | | 2 | {"id": 2, "name": "Qandahar", "population": 237500, "countrycode": "AFG"} | | 3 | {"id": 3, "name": "Herat", "population": 186800, "countrycode": "AFG"} | +-----------------+---------------------------------------------------------------------------+ 3 rows in set (0.00 sec) 32/123
34. 複合(マルチカラム)インデックス INDEX(countrycode, population) など作ってみます mysql> CREATE TABLE city_index_countrycode_population ( -> countrycode CHAR(3) NOT NULL, -> population int not null, -> clustered_index int not null, -> KEY(countrycode, population) -> ); mysql> INSERT INTO city_index_countrycode_population (countrycode, population, clustered_index) -> SELECT countrycode, population, id AS clustered_index -> FROM city -> ORDER BY countrycode, population; mysql> SELECT * FROM city_index_countrycode_population LIMIT 3; +-------------+------------+-----------------+ | countrycode | population | clustered_index | +-------------+------------+-----------------+ | ABW | 29034 | 129 | | AFG | 127800 | 4 | | AFG | 186800 | 3 | 33/123
35. 複合(マルチカラム)インデックス 34/123
36. WHERE countrycode = JPN 複合インデックスの左端のカラム「だけ」を使うクエリーは有効 mysql> SELECT countrycode, clustered_index -> FROM city_index_countrycode_population -> WHERE countrycode= 'JPN'; +-------------+-----------------+ | countrycode | clustered_index | +-------------+-----------------+ | JPN | 1779 | | JPN | 1778 | | JPN | 1777 | .. | JPN | 1534 | | JPN | 1533 | | JPN | 1532 | +-------------+-----------------+ 248 rows in set (0.00 sec) 35/123
37. WHERE countrycode = JPN mysql> SELECT clustered_index, data FROM city_row_data WHERE clustered_index IN (1779, 1778, ..); +-----------------+--------------------------------------------------------------------------------------- ---+ | clustered_index | data | +-----------------+--------------------------------------------------------------------------------------- ---+ | 1532 | {"id": 1532, "name": "Tokyo", "population": 7980230, "countrycode": "JPN"} | | 1533 | {"id": 1533, "name": "Jokohama [Yokohama]", "population": 3339594, "countrycode": "J PN"} | | 1534 | {"id": 1534, "name": "Osaka", "population": 2595674, "countrycode": "JPN"} | .. | 1777 | {"id": 1777, "name": "Narita", "population": 91470, "countrycode": "JPN"} | | 1778 | {"id": 1778, "name": "Kashiwazaki", "population": 91229, "countrycode": "JPN"} | | 1779 | {"id": 1779, "name": "Tsuyama", "population": 91170, "countrycode": "JPN"} | +-----------------+--------------------------------------------------------------------------------------- ---+ 248 rows in set (0.00 sec) 36/123
38. WHERE countrycode = JPN 37/123
39. WHERE countrycode = JPN 38/123
40. WHERE countrycode = JPN 39/123
41. WHERE countrycode = JPN 40/123
42. WHERE countrycode = JPN 余談(?) 非ユニークキーの場合1つ先のリーフノードも読む ‐ 41/123
43. WHERE population = 7980230 複合インデックスの左端を「使わない」インデックスは非効率的 mysql> SELECT countrycode, population, clustered_index -> FROM city_index_countrycode_population -> WHERE population = 7980230; -- このたとえだけ見るとまともに見えるけれど +-------------+------------+-----------------+ | countrycode | population | clustered_index | +-------------+------------+-----------------+ | JPN | 7980230 | 1532 | +-------------+------------+-----------------+ 1 row in set (0.00 sec) 42/123
44. WHERE population = 7980230 その1 (タイト)インデックススキャンと呼ばれるもの 43/123
45. WHERE population = 7980230 その1 (タイト)インデックススキャンと呼ばれるもの 44/123
46. WHERE population = 7980230 その2 45/123
47. WHERE population = 7980230 その2 46/123
48. WHERE population = 7980230 その2 47/123
49. インデックスコンディションプッシュダウン そんなに良いものでもないし、「テーブルスキャンよりはマシなのでは？」って時 に選ばれる（ことになっている） 行データをフェッチするときは「必要のないカラム」さえ同時に読まなければいけ ないので、「必要のないカラム」を読むコストが大きくなればなるほどインデック スコンディションプッシュダウンは効果を発揮する たとえばやたら大きいvarcharのカラムがインライン格納されちゃっているとか ‐ 二度引きの手間を差し引くとテーブルスキャンの方が速くなることも多々 ‐ 48/123
50. 複合インデックスを使い切る WHERE countrycode = JPN AND population = 91229 49/123
51. 複合インデックスを使い切る WHERE countrycode = JPN AND population = 91229 50/123
52. 複合インデックスを使い切る WHERE countrycode = JPN ORDER BY population 51/123
53. 複合インデックスを使い切る WHERE countrycode = JPN ORDER BY population 52/123
54. 複合インデックスを使い切れない ORDER BY countrycode ASC, population DESC 53/123
55. 複合インデックスを使い切れない ORDER BY countrycode ASC, population DESC 54/123
56. 複合インデックスを使い切れない WHERE countrycode IN (JPN, AFG) ORDER BY population 55/123
57. 複合インデックスを使い切れない WHERE countrycode IN (JPN, AFG) ORDER BY population 56/123
58. バリエーション 複合インデックスを使い切れるケース WHERE countrycode = ? AND population {=|>|<} ? ‐ WHERE countrycode = ? ORDER BY population {ASC|DESC} ‐ ORDER BY countrycode ASC, population ASC ‐ WHERE countrycode IN (?, ?) AND population {=|>|<} ? ‐ 複合インデックスを使い切れないケース WHERE countrycode = ? OR population = ? ‐ WHERE population = ? ORDER BY countrycode {ASC|DESC} ‐ ORDER BY population ASC, countrycode ASC ‐ ORDER BY countrycode ASC, population DESC ‐ WHERE countrycode IN (?, ?) ORDER BY population ASC ‐ 57/123
59. バリエーション ORDER BY countrycode ASC, population DESC だけは、時代(MySQL 8.0)が多少解 決した インデックスを作る段階で INDEX(countrycode ASC, population DESC) で作っておく ‐ MySQL 8.0でもASC, ASCで作ってたらやっぱり使えない ‐ 想像以上にフェッチしている行が多くなったときは図にしてみると案外合点がいっ たりする 追加ソートが必要になる Extra: Using filesort とかも図にするとわかりやすい ‐ 58/123
60. ひとやすみ 59/123
61. WHERE狙いのキー、 ORDER BY狙いの キー 60/123
62. WHERE狙いのキー、ORDER BY狙いのキー WHERE で絞り込みをかけるためにインデックスは超有効 ORDER BY によるソートのスキップもできることを説明しました 複合インデックスできれいに WHERE .. ORDER BY .. を処理しきるのが最速だけれ ど、使っている演算子によっては両方を一気に処理できないことがある そんな時に WHERE を優先して追加ソート( Extra: Using filesort )を選ぶのを WHERE狙い, ORDER BY を優先して追加フィルタリング( Extra: Using where )を選ぶのを ORDER BY狙い と呼 んでいます 造語です ‐ 基本的にORDER BY狙いを使う時は「LIMITが指定されている」時だけ 61/123
63. ORDER BY .. LIMIT .. の最適化 「52枚1組のトランプの山の中から、数字の小さい方から全てを昇順に並べ替え て、先頭の3枚を出してください。スートは問いません。ジョーカーは含まれてい ません」 SELECT .. FROM card ORDER BY num ASC LIMIT 3 ‐ 俺なら、52枚の束を繰って「なんでもいいからAを3枚見つけたらそれを出」せば いいと思う これができるのは俺が「Aが最小であること」「Aは3枚以上存在すること」を知っているから ‐ RDBMSは「Aが最小であること」も「Aが何枚存在するか」も知らない 全部フェッチしてソートしてからでないと「小さい方から3枚」が確定できない ‐ もし2組以上のトランプをごちゃまぜにして「Aはあるかも知れないしないかも知れない」「下 手すると52枚全部K」とかなると俺の戦略は使えなくなるのと一緒で ‐ 62/123
64. ORDER BY .. LIMIT .. の最適化 俺の「Aが最小であること」「Aは3枚以上存在すること」の知識に該当するものが RDBMSのインデックス( INDEX(num) ) インデックスを引くことで「最小の値」は調べがつく ‐ B+Treeなので連接リストをたどっていくだけで「小さい方から必要なだけ」 ‐ 「必要なぶんだけ取ったらあとはもう読まなくて良い」 ‐ LIMITと併用することで、 ORDER BY狙いの戦略 がフェッチする行の数を大きく減 らすことができる 63/123
65. INDEX(num) 64/123
66. INDEX(num) 65/123
67. INDEX(num) 66/123
68. WHERE狙いのキー、ORDER BY狙いのキー WHERE狙いのキー WHEREの条件にマッチする件数が少なければ少ないほど速い ORDER BY(+LIMIT)狙いのキー WHEREの条件にマッチする件数が多ければ多いほど速い ワーストケースは「WHEREの条件にマッチする件数がLIMITの件数より小さい」 (トランプだって言ってるのに「桜の20点」とかいわれると結局全部PKを引かないといけなくなる) 67/123
69. インデック スマージ 68/123
70. インデックスマージ 複数のインデックスを同時に使う EXPLAINで Extra: Using intersect と Extra: Using union のケース ‐ ただしネクストキーロックと無茶苦茶相性が悪い (単独では絞り込み切れない)それぞれのインデックスにロックを置くのでロック範囲がかえっ てバカでかくなる ‐ 69/123
71. INDEX(num) vs INDEX(suite) SELECT .. FROM card WHERE suite = 'club' AND num = 2 70/123
72. INDEX(num) vs INDEX(suite) SELECT .. FROM card WHERE suite = 'club' AND num = 2 71/123
73. INDEX(num) vs INDEX(suite) SELECT .. FROM card WHERE suite = 'club' AND num = 2 72/123
74. INDEX(num) vs INDEX(suite) PK IN (9, 12, 16, 30) AND PK IN (2, 3, 9, 12, ..) 73/123
75. INDEX(num) vs INDEX(suite) PK IN (9, 12, 16, 30) AND PK IN (2, 3, 9, 12, ..) Extra: Using intersect ‐ intersect .. 交差する ‐ 74/123
76. INDEX(num) vs INDEX(suite) SELECT .. FROM card WHERE suite = 'club' OR num = 2 75/123
77. INDEX(num) vs INDEX(suite) SELECT .. FROM card WHERE suite = 'club' OR num = 2 76/123
78. INDEX(num) vs INDEX(suite) SELECT .. FROM card WHERE suite = 'club' OR num = 2 77/123
79. INDEX(num) vs INDEX(suite) PK IN (9, 12, 16, 30, 2, 3, 9, 12, ..) Extra: Using union ‐ union .. 統一 ‐ 78/123
80. カバリングイ ンデックス 79/123
81. カバリングインデックス インデックスを使った検索のうち、「行そのもののデータを必要とせずに完結す る」実行戦略 COUNT とselect_listによる調整が主 80/123
82. COUNTのカバリングインデックス SELECT COUNT(*) FROM card WHERE num = 2 81/123
83. COUNTのカバリングインデックス SELECT COUNT(*) FROM card WHERE num = 2 82/123
84. COUNTのカバリングインデックスきかないやつ SELECT COUNT(*) FROM card WHERE num = 2 AND suite = 'spade' 83/123
85. COUNTのカバリングインデックスきかないやつ SELECT COUNT(*) FROM card WHERE num = 2 AND suite = 'spade' 84/123
86. select_listによるカバリングインデックス (world.cityに戻りますが) SELECT id, population FROM city WHERE countrycode = 'JPN' 85/123
87. select_listによるカバリングインデックス SELECT id, population FROM city WHERE countrycode = 'JPN' 86/123
88. カバリングインデックス 1行のサイズが大きくてPKを辿ってしまうと発生するI/Oが大きい時に効果的 更新には関係ない(行データに触らずに更新なんてできるわけがないから) よく狙うのは COUNT, 限定的だけど MIN, MAX, SUM なんかでもカバリングインデック スは狙いやすい 87/123
89. もうひとや すみ 88/123
90. インデックス #とは 「主に検索のために」 「行データの部分集合を」 「あらかじめソートして複製したもの」 「ロックの単位でもある」 89/123
91. インデックスのメンテナンス 当然ながら、インデックスを使って検索するには「インデックスと行データが完全 に同期されている状態」でなければならない INSERTされればインデックスもリーフが増え ‐ UPDATEされればリーフの位置が変わり ‐ DELETEされればリーフが消される ‐ しかも、トランザクション分離レベルの都合に合わせて transaction_isolation = REPEATABLE-READ なら、コミットされていても過去のデータを遡って 読むことがある(過去のインデックスももちろん保全しておかなければならない) ‐ 90/123
92. インデックスのメンテナンス 更新する時には書き込み量的には常に悪影響 UPDATE や DELETE のターゲットを絞り込むのにインデックスが使えるケースもあるので飽くま で「書き込み量」 ‐ 検索する時には(最適なインデックスを選んでいる限り)読み出し量的には好影響、 ワーストケースでも影響なし ただし、使うインデックスを間違えて選んであばばばばばばってなるリスクはある ‐ 本来ならSELECTした時に発生する絞り込みやソートのコストを、INSERT(や UPDATE/DELETE)時に転嫁するのがインデックス 91/123
93. インデックスの容量 「あらかじめソート済の部分集合を作って保管しておく」ので、行データ本体とは 別に追加の容量が必要になる InnoDBでは「行データ本体を入れたものと同じibdファイル」に入る 「リーフノードにPRIMARY KEYの値を含む」ので、PRIMARY KEYが短ければ短 いほどインデックスの容量は節約できる 「本来のPRIMARY KEYを NOT NULL UNIQUE に落として、それとは別に AUTO_INCREMENT PRIMARY KEY を振る」というのはMySQLにおけるインデックスの容量対策としては正解 ‐ NOT NULL UNIQUE がない状態で AUTO_INCREMENT PRIMARY KEY だけがあるのは単なるPRIMARY KEYの欠損なのでそれは考え直した方が良いと思いますが ‐ 92/123
94. インデックスの容量 mysql> SELECT table_schema, table_name, -> format_bytes(data_length) AS data_length, -> format_bytes(index_length) AS index_length -> FROM information_schema.tables -> WHERE table_schema = 'tpcc'; +--------------+------------+-------------+--------------+ | TABLE_SCHEMA | TABLE_NAME | data_length | index_length | +--------------+------------+-------------+--------------+ | tpcc | customer | 179.75 MiB | 13.55 MiB | | tpcc | district | 16.00 KiB | 0 bytes | | tpcc | history | 19.55 MiB | 12.03 MiB | | tpcc | item | 9.52 MiB | 0 bytes | | tpcc | new_orders | 2.52 MiB | 0 bytes | | tpcc | order_line | 204.77 MiB | 68.61 MiB | | tpcc | orders | 12.52 MiB | 6.52 MiB | | tpcc | stock | 339.89 MiB | 13.52 MiB | | tpcc | warehouse | 16.00 KiB | 0 bytes | +--------------+------------+-------------+--------------+ 9 rows in set (0.01 sec) 93/123
95. インデックスの功罪 インデックスが少なすぎると クエリーに時間がかかる 時間がかかるだけならまだしもロック範囲が大きくなることがある インデックスが多すぎると 倍々ゲームで容量を食われていく 実は少しずつ更新の時のレイテンシーが上がっていく 94/123
96. ネクストキーロック transaction_isolation >= REPEATABLE-READ の時に使われるロック transaction_isolation = READ-COMMITTED の場合は使われない ‐ ファントムリードを防ぐために「本来必要なリーフノードと次のリーフノード、更 にそれらのギャップ」をロックする 95/123
97. InnoDBのロック たとえばこんなテーブル PRIMARY KEY(name) ‐ KEY(age) ‐ KEY(gender) ‐ mysql> SELECT * FROM user; +----------------------+------+--------+ | name | age | gender | +----------------------+------+--------+ | yoku0825 | 38 | M | | yoku0825のムスメ | 6 | F | | yoku0825の妻 | NULL | F | | you0825のせがれ | 11 | M | +----------------------+------+--------+ 4 rows in set (0.00 sec) 96/123
98. InnoDBのロック infinum .. 無限小を表すリーフノード supernum .. 無限大を表すリーフノード [gap] .. リーフノードとリーフノードの隙間 97/123
99. InnoDBのロック REC .. そのリーフと手前のリーフの間のギャップ REC not GAP .. そのリーフそのもの GAP .. そのリーフの手前のギャップ 98/123
100. InnoDBのロック UPDATE user SET age = 39 WHERE name = 'yoku0825' トランザクション分離レベルにかかわらずPK引き ‐ 99/123
101. InnoDBのロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = 12 WHERE age = 11 REPEATABLE-READで非ユニークなインデックスを引いたUPDATE ‐ 100/123
102. InnoDBのロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = 12 WHERE age = 11 REPEATABLE-READで非ユニークなインデックスを引いたUPDATE ‐ 101/123
103. InnoDBのロック (6, yoku0825のムスメ) の直後から (38, yoku0825) の直前までがロックされている mysql> INSERT INTO user VALUES ('けんつ', 24, 'M'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO user VALUES ('けんつ', 10, 'M'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO user VALUES ('けんつ', 5, 'M'); Query OK, 1 row affected (0.00 sec) 102/123
104. InnoDBのロック (6, yoku0825のムスメ) の直後から (38, yoku0825) の直前までがロックされている mysql80 14> INSERT INTO user VALUES ('yoku0824のムスメ', 6, 'F'); Query OK, 1 row affected (0.01 sec) mysql80 14> INSERT INTO user VALUES ('yoku0826のムスメ', 6, 'F'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql80 14> INSERT INTO user VALUES ('yoku0824', 38, 'M'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql80 14> INSERT INTO user VALUES ('yoku0826', 38, 'M'); Query OK, 1 row affected (0.00 sec) 103/123
105. InnoDBのロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = age + 1 WHERE gender = 'F' REPEATABLE-READで非ユニークなインデックスを引いたUPDATEその2 ‐ 104/123
106. InnoDBのロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = age + 1 WHERE gender = 'F' REPEATABLE-READで非ユニークなインデックスを引いたUPDATEその2 ‐ 105/123
107. InnoDBのロック infinumから gender = M && name < yoku0825 がロックされている mysql> INSERT INTO user VALUES ('yoku0824のムスメ?', NULL, 'F'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO user VALUES ('yoku0824', NULL, 'M'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO user VALUES ('yoku0826', NULL, 'M'); Query OK, 1 row affected (0.00 sec) 106/123
108. インデックスマージに注意 UPDATE user SET age = age + 1 WHERE age = 38 AND gender = 'M' が INDEX(age) と INDEX(gender) のインデックスマージ ( Using intersect )を狙った場合 107/123
109. インデックスマージに注意 UPDATE user SET age = age + 1 WHERE age = 38 AND gender = 'M' が INDEX(age) と INDEX(gender) のインデックスマージ ( Using intersect )を狙った場合 108/123
110. InnoDBのロック infinum (無限小) と supernum (無限大)のリーフが存在する 「手前のギャップ」「ネクストキー(=次のリーフ)」が俺のたとえで見つからない 場合でも、これら2つのリーフは存在するので必ず「手前のギャップ(ワーストで無 限小からそのキーまで)」と「ネクストキー(ワーストでそのキーから無限大ま で)」は存在する transaction_isolation = READ-COMMITTED の時はギャップを取らないので transaction_isolation = REPEATABLE-READ と比べて REC は REC not GAP に ‐ REC not GAP はそのまま ‐ GAP だけのロックだったところはロックがとられない ‐ 109/123
111. InnoDBのロック てっきり読み出しの最適化にばかり使われると思っていたインデックスは実はロッ クにも使われている 仕組みを知らないとネクストキーロックは「何が起こっているのかよくわからない」 ‐ 「全ての読んだリーフとその手前のギャップのロック、ただしbreakするために読んだリーフ は手前のギャップだけ」 ‐ これもよくわからなくなったらツリーを書くと案外わかりやすかったりする infinumとsupernumのリーフが存在しているのを知っていればいける気がする 110/123
112. SELECTロックフリー transaction_isolation <= REPEATABLE-READ (つまり、 SERIALIZABLE 以外) の場 合、InnoDBはSELECTに行ロックを取らない 明示的にロックを置くための FOR SHARE (旧 LOCK IN SHARE MODE ), FOR UPDATE を除く ‐ 行ロックの話であって、メタデータロックやmutexは別 ‐ transaction_isolation = SERIALIZABLE の場合、共有ロックでネクストキーロック する 全てのSELECTに FOR SHARE (旧 LOCK IN SHARE MODE) がついているような感じ ‐ 111/123
113. SELECTロックフリー SELECTロックフリーを使ってネクストキーロックと戦う transaction_isolation = REPEATABLE-READ で SELECT pk FROM .. WHERE .. でPKを取り出した 後に DELETE FROM .. WHERE pk IN (..) でやっつけるのはロックが減って優しい ‐ transaction_isolation <= READ-COMMITTED な場合はそもそもネクストキーロックが発生しない のでそんなに嬉しくはないかも（件数が多くてORDER BYのオーバーヘッドが気になる場合は 有効） ‐ transaction_isolation = SERIALIZABLE の時でもセカンダリーインデックスの排他ロックを避 けられるので多少良くなることも ‐ なんだかこれ、「セカンダリーキーの二度引き」を手元で実装しなおすことになる わけで、まあなんか楽しいですよね :) 112/123
114. 疑似セカンダリーインデックス 「トランザクションに保護される」、あるいは「トランザクションに保護されなく て良い」 保護されたいなら、アプリのトランザクションで一緒に保護するか(増えた時に抜ける可能性が あるので微妙)トリガーで同期する ‐ 保護されなくて良い、バッチで洗い替えすれば良い、みたいなのなら楽 ‐ 「 行データの部分集合をあらかじめソートしたもの」 これを表現するために、結局「疑似セカンダリーインデックステーブル」にはセカンダリーイ ンデックスが必要なわけだけれど ‐ あたりを肝に命じておくと、「実際にはテーブルにインデックスを張らなくても (ALTER TABLEのオーバーヘッドが無視できないとか)セカンダリーインデックス を模すことができる」 結局は「いつ・どこにコストを転嫁するか」というはなし ‐ 113/123
115. 疑似セカンダリーインデックス mysql> CREATE TABLE country_index_continent ( -> continent varchar(32) NOT NULL, -> clustered_index CHAR(3) NOT NULL, -> KEY(continent) -> ); mysql> INSERT INTO country_index_continent (continent, clustered_index) -> SELECT continent, code AS clustered_index -> FROM country -> ORDER BY CAST(continent AS CHAR), code; mysql> SELECT * FROM country_index_continent LIMIT 3; +-----------+-----------------+ | continent | clustered_index | +-----------+-----------------+ | Asia | AFG | | Asia | ARE | | Asia | ARM | 114/123
116. 疑似セカンダリーインデックス mysql> SELECT clustered_index -> FROM country_index_continent -> WHERE continent = 'South America'; +---------------+-----------------+ | continent | clustered_index | +---------------+-----------------+ | South America | ARG | | South America | BOL | | South America | BRA | | South America | CHL | | South America | COL | | South America | ECU | | South America | FLK | | South America | GUF | | South America | GUY | | South America | PER | | South America | PRY | | South America | SUR | | South America | URY | | South America | VEN | +---------------+-----------------+ 14 rows in set (0.00 sec) 115/123
117. 疑似セカンダリーインデックス mysql> DELETE FROM country -> WHERE code IN ('ARG','BOL','BRA','CHL','COL','ECU','FLK','GUF','GUY','PER','PRY',' SUR','URY','VEN'); mysql> DELETE FROM country WHERE code IN ('ARG','BOL','BRA','CHL','COL','ECU','FLK','GUF' ,'GUY','PER','PRY','SUR','URY','VEN'); Query OK, 14 rows affected (0.00 sec) mysql> SELECT object_name, index_name, lock_type, lock_mode, COUNT(*) FROM performance_sc hema.data_locks GROUP BY 1, 2, 3, 4; +-------------+------------+-----------+---------------+----------+ | object_name | index_name | lock_type | lock_mode | COUNT(*) | +-------------+------------+-----------+---------------+----------+ | country | NULL | TABLE | IX | 1 | | country | PRIMARY | RECORD | X,REC_NOT_GAP | 14 | +-------------+------------+-----------+---------------+----------+ 116/123
118. 素直にインデックスのない行でDELETE mysql> DELETE FROM country WHERE continent = 'South America'; Query OK, 14 rows affected (0.00 sec) mysql> SELECT object_name, index_name, lock_type, lock_mode, COUNT(*) FROM performance_sc hema.data_locks GROUP BY 1, 2, 3, 4; +-------------+------------+-----------+-----------+----------+ | object_name | index_name | lock_type | lock_mode | COUNT(*) | +-------------+------------+-----------+-----------+----------+ | country | NULL | TABLE | IX | 1 | | country | PRIMARY | RECORD | X | 245 | +-------------+------------+-----------+-----------+----------+ 2 rows in set (0.01 sec) 117/123
119. まとめ 「主に検索のために」 「行データの部分集合を」 「あらかじめソートして複製したもの」 「ロックの単位でもある」 118/123
120. まとめ 「主に検索のために」 WHERE または ORDER BY LIMIT またはその両方に対して、フェッチする行データを大幅に刈り込 むことができる ‐ 読み出しI/Oの削減、また同じインデックスが頻繁に使われるならバッファプールヒット率の向 上 ‐ 「行データの部分集合を」 「あらかじめソートして複製したもの」 「ロックの単位でもある」 119/123
121. まとめ 「主に検索のために」 「行データの部分集合を」 ツリー形式で格納する以上、インデックスに使うカラムの並び順は大事 ‐ 迷った時の指針として「まず WHERE を AND でつないだ羅列」余力があれば「 WHERE の羅列の後 に ORDER BY 」 ‐ 行データが大きい時はカバリングインデックスというテクニックがある(万能ではない) ‐ 「あらかじめソートして複製したもの」 「ロックの単位でもある」 120/123
122. まとめ 「主に検索のために」 「行データの部分集合を」 「あらかじめソートして複製したもの」 同じibdファイルの中に追加で容量が必要 ‐ 書き込みI/O的には常にオーバーヘッドが乗る ‐ 「SELECTにかかるコストをINSERT/UPDATE/DELETEの時に転嫁しているだけ」 ‐ 「ロックの単位でもある」 121/123
123. まとめ 「主に検索のために」 「行データの部分集合を」 「あらかじめソートして複製したもの」 「ロックの単位でもある」 読むリーフの数が少なくなれば少なくなるほどロックの範囲が狭くなる ‐ インデックスマージは複数のインデックスでそれぞれロックがかかるのでロックの範囲がデカ い ‐ 122/123
124. Any Questions and/ or Suggestions? 123/123