MySQLとインデックスと私

by yoku0825

Slide 1

Slide 1 text

MySQLとインデックスと私愛するあなたのため 2021/05/24 yoku0825 開発運用研修2021 データベース(?)

Slide 2

Slide 2 text

はじめに昨年(?)の三苫さんの資料がとても網羅的でわかりやすいので絶対に読んでおくと良いと思いますデータベース ‐ データベース設計 (サイボウズ社内用の資料でした) ‐ USE THE INDEX, LUKE! もお勧めです SQLのインデックスとそのチューニングについてのオンラインブック ‐ 1/123

Slide 3

Slide 3 text

＼おはようございます／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ サイボウズ MySQLコンサルタント ‐ 2/123

Slide 4

Slide 4 text

(この資料における)インデックス #とは MySQLのInnoDBストレージエンジンの話です。RDBMSやストレージエンジンが変わると多少事情は変わりますイメージしやすくするための「たとえ」がほとんどなので、内部実装的な話はさらに事情が変わります ‐ MySQLで一番よく使われる、B+Treeインデックスのことについて説明します InnoDBにおける「インデックス」はPRIMARY KEYのことも含みます。InnoDBでは「行データ」は「PRIMARY KEYのB+Treeのリーフ」にぶら下がっています 3/123

Slide 5

Slide 5 text

インデックス #とは「主に検索のために」「行データの部分集合を」「あらかじめソートして複製したもの」「ロックの単位でもある」 4/123

Slide 6

Slide 6 text

Slide 7

Slide 7 text

サンプルデータ説明しやすくするために 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

Slide 8

Slide 8 text

サンプルデータ説明しやすくするために 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

Slide 9

Slide 9 text

行データの構造 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

Slide 10

Slide 10 text

行データの構造ツリーじゃないけど雰囲気だけ感じ取ってください 9/123

Slide 11

Slide 11 text

INDEX(continent) 相当の構造「部分集合」「あらかじめソート」のあたりを感じ取っていただきたいセカンダリーインデックスのリーフには「PRIMARY KEYの値」が書いてあります ‐ +-----------+-----------------+ | continent | clustered_index | +-----------+-----------------+ | Africa | AGO | | Africa | BDI | | Africa | BEN | +-----------+-----------------+ 10/123

Slide 12

Slide 12 text

INDEX(continent) 相当の構造ツリーじゃないけど雰囲気だけ感じ取ってください 11/123

Slide 13

Slide 13 text

Slide 14

Slide 14 text

INDEX(continent) から continent = Antarctica をルックアップ 13/123

Slide 15

Slide 15 text

INDEX(continent) から continent = Antarctica をルックアップ 14/123

Slide 16

Slide 16 text

INDEX(continent) から continent = Antarctica をルックアップ 15/123

Slide 17

Slide 17 text

たとえば 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

Slide 18

Slide 18 text

得られた値を使ってPRIMARY KEYからルックアップ 17/123

Slide 19

Slide 19 text

得られた値を使ってPRIMARY KEYからルックアップ 18/123

Slide 20

Slide 20 text

得られた値を使ってPRIMARY KEYからルックアップ 19/123

Slide 21

Slide 21 text

略 20/123

Slide 22

Slide 22 text

たとえば WHERE continent = Antarctica をインデックスで解決する処理セカンダリーキーからルックアップしてPRIMARY KEYの値を得る得られた値を使ってPRIMARY KEYからルックアップ 21/123

Slide 23

Slide 23 text

たとえば、インデックスで解決できない 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

Slide 24

Slide 24 text

たとえば、インデックスで解決できない WHERE name = Japan を検索する処理つまりテーブルスキャン 23/123

Slide 25

Slide 25 text

たとえば、インデックスで解決できない WHERE name = Japan を検索する処理つまりテーブルスキャン 24/123

Slide 26

Slide 26 text

たとえば、インデックスで解決できない WHERE name = Japan を検索する処理つまりテーブルスキャン 25/123

Slide 27

Slide 27 text

たとえば、インデックスで解決できない WHERE name = Japan を検索する処理つまりテーブルスキャン 26/123

Slide 28

Slide 28 text

たとえば、インデックスで解決できない 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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

これが基本的な動作 30/123

Slide 32

Slide 32 text

(イメージつかめました?) 31/123

Slide 33

Slide 33 text

複合(マルチカラム)インデックスやっぱり説明用に 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

Slide 34

Slide 34 text

複合(マルチカラム)インデックス 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

Slide 35

Slide 35 text

複合(マルチカラム)インデックス 34/123

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

WHERE countrycode = JPN 37/123

Slide 39

Slide 39 text

WHERE countrycode = JPN 38/123

Slide 40

Slide 40 text

WHERE countrycode = JPN 39/123

Slide 41

Slide 41 text

WHERE countrycode = JPN 40/123

Slide 42

Slide 42 text

WHERE countrycode = JPN 余談(?) 非ユニークキーの場合1つ先のリーフノードも読む ‐ 41/123

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

WHERE population = 7980230 その1 (タイト)インデックススキャンと呼ばれるもの 43/123

Slide 45

Slide 45 text

WHERE population = 7980230 その1 (タイト)インデックススキャンと呼ばれるもの 44/123

Slide 46

Slide 46 text

WHERE population = 7980230 その2 45/123

Slide 47

Slide 47 text

WHERE population = 7980230 その2 46/123

Slide 48

Slide 48 text

WHERE population = 7980230 その2 47/123

Slide 49

Slide 49 text

インデックスコンディションプッシュダウンそんなに良いものでもないし、「テーブルスキャンよりはマシなのでは？」って時に選ばれる（ことになっている）行データをフェッチするときは「必要のないカラム」さえ同時に読まなければいけないので、「必要のないカラム」を読むコストが大きくなればなるほどインデックスコンディションプッシュダウンは効果を発揮するたとえばやたら大きいvarcharのカラムがインライン格納されちゃっているとか ‐ 二度引きの手間を差し引くとテーブルスキャンの方が速くなることも多々 ‐ 48/123

Slide 50

Slide 50 text

複合インデックスを使い切る WHERE countrycode = JPN AND population = 91229 49/123

Slide 51

Slide 51 text

複合インデックスを使い切る WHERE countrycode = JPN AND population = 91229 50/123

Slide 52

Slide 52 text

複合インデックスを使い切る WHERE countrycode = JPN ORDER BY population 51/123

Slide 53

Slide 53 text

複合インデックスを使い切る WHERE countrycode = JPN ORDER BY population 52/123

Slide 54

Slide 54 text

複合インデックスを使い切れない ORDER BY countrycode ASC, population DESC 53/123

Slide 55

Slide 55 text

複合インデックスを使い切れない ORDER BY countrycode ASC, population DESC 54/123

Slide 56

Slide 56 text

複合インデックスを使い切れない WHERE countrycode IN (JPN, AFG) ORDER BY population 55/123

Slide 57

Slide 57 text

複合インデックスを使い切れない WHERE countrycode IN (JPN, AFG) ORDER BY population 56/123

Slide 58

Slide 58 text

バリエーション複合インデックスを使い切れるケース 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

Slide 59

Slide 59 text

バリエーション ORDER BY countrycode ASC, population DESC だけは、時代(MySQL 8.0)が多少解決したインデックスを作る段階で INDEX(countrycode ASC, population DESC) で作っておく ‐ MySQL 8.0でもASC, ASCで作ってたらやっぱり使えない ‐ 想像以上にフェッチしている行が多くなったときは図にしてみると案外合点がいったりする追加ソートが必要になる Extra: Using filesort とかも図にするとわかりやすい ‐ 58/123

Slide 60

Slide 60 text

ひとやすみ 59/123

Slide 61

Slide 61 text

WHERE狙いのキー、 ORDER BY狙いのキー 60/123

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

ORDER BY .. LIMIT .. の最適化俺の「Aが最小であること」「Aは3枚以上存在すること」の知識に該当するものが RDBMSのインデックス( INDEX(num) ) インデックスを引くことで「最小の値」は調べがつく ‐ B+Treeなので連接リストをたどっていくだけで「小さい方から必要なだけ」 ‐ 「必要なぶんだけ取ったらあとはもう読まなくて良い」 ‐ LIMITと併用することで、 ORDER BY狙いの戦略がフェッチする行の数を大きく減らすことができる 63/123

Slide 65

Slide 65 text

INDEX(num) 64/123

Slide 66

Slide 66 text

INDEX(num) 65/123

Slide 67

Slide 67 text

INDEX(num) 66/123

Slide 68

Slide 68 text

WHERE狙いのキー、ORDER BY狙いのキー WHERE狙いのキー WHEREの条件にマッチする件数が少なければ少ないほど速い ORDER BY(+LIMIT)狙いのキー WHEREの条件にマッチする件数が多ければ多いほど速いワーストケースは「WHEREの条件にマッチする件数がLIMITの件数より小さい」 (トランプだって言ってるのに「桜の20点」とかいわれると結局全部PKを引かないといけなくなる) 67/123

Slide 69

Slide 69 text

インデックスマージ 68/123

Slide 70

Slide 70 text

インデックスマージ複数のインデックスを同時に使う EXPLAINで Extra: Using intersect と Extra: Using union のケース ‐ ただしネクストキーロックと無茶苦茶相性が悪い (単独では絞り込み切れない)それぞれのインデックスにロックを置くのでロック範囲がかえってバカでかくなる ‐ 69/123

Slide 71

Slide 71 text

INDEX(num) vs INDEX(suite) SELECT .. FROM card WHERE suite = 'club' AND num = 2 70/123

Slide 72

Slide 72 text

INDEX(num) vs INDEX(suite) SELECT .. FROM card WHERE suite = 'club' AND num = 2 71/123

Slide 73

Slide 73 text

INDEX(num) vs INDEX(suite) SELECT .. FROM card WHERE suite = 'club' AND num = 2 72/123

Slide 74

Slide 74 text

INDEX(num) vs INDEX(suite) PK IN (9, 12, 16, 30) AND PK IN (2, 3, 9, 12, ..) 73/123

Slide 75

Slide 75 text

INDEX(num) vs INDEX(suite) PK IN (9, 12, 16, 30) AND PK IN (2, 3, 9, 12, ..) Extra: Using intersect ‐ intersect .. 交差する ‐ 74/123

Slide 76

Slide 76 text

INDEX(num) vs INDEX(suite) SELECT .. FROM card WHERE suite = 'club' OR num = 2 75/123

Slide 77

Slide 77 text

INDEX(num) vs INDEX(suite) SELECT .. FROM card WHERE suite = 'club' OR num = 2 76/123

Slide 78

Slide 78 text

INDEX(num) vs INDEX(suite) SELECT .. FROM card WHERE suite = 'club' OR num = 2 77/123

Slide 79

Slide 79 text

INDEX(num) vs INDEX(suite) PK IN (9, 12, 16, 30, 2, 3, 9, 12, ..) Extra: Using union ‐ union .. 統一 ‐ 78/123

Slide 80

Slide 80 text

カバリングインデックス 79/123

Slide 81

Slide 81 text

カバリングインデックスインデックスを使った検索のうち、「行そのもののデータを必要とせずに完結する」実行戦略 COUNT とselect_listによる調整が主 80/123

Slide 82

Slide 82 text

COUNTのカバリングインデックス SELECT COUNT(*) FROM card WHERE num = 2 81/123

Slide 83

Slide 83 text

COUNTのカバリングインデックス SELECT COUNT(*) FROM card WHERE num = 2 82/123

Slide 84

Slide 84 text

COUNTのカバリングインデックスきかないやつ SELECT COUNT(*) FROM card WHERE num = 2 AND suite = 'spade' 83/123

Slide 85

Slide 85 text

COUNTのカバリングインデックスきかないやつ SELECT COUNT(*) FROM card WHERE num = 2 AND suite = 'spade' 84/123

Slide 86

Slide 86 text

select_listによるカバリングインデックス (world.cityに戻りますが) SELECT id, population FROM city WHERE countrycode = 'JPN' 85/123

Slide 87

Slide 87 text

select_listによるカバリングインデックス SELECT id, population FROM city WHERE countrycode = 'JPN' 86/123

Slide 88

Slide 88 text

カバリングインデックス 1行のサイズが大きくてPKを辿ってしまうと発生するI/Oが大きい時に効果的更新には関係ない(行データに触らずに更新なんてできるわけがないから) よく狙うのは COUNT, 限定的だけど MIN, MAX, SUM なんかでもカバリングインデックスは狙いやすい 87/123

Slide 89

Slide 89 text

もうひとやすみ 88/123

Slide 90

Slide 90 text

インデックス #とは「主に検索のために」「行データの部分集合を」「あらかじめソートして複製したもの」「ロックの単位でもある」 89/123

Slide 91

Slide 91 text

インデックスのメンテナンス当然ながら、インデックスを使って検索するには「インデックスと行データが完全に同期されている状態」でなければならない INSERTされればインデックスもリーフが増え ‐ UPDATEされればリーフの位置が変わり ‐ DELETEされればリーフが消される ‐ しかも、トランザクション分離レベルの都合に合わせて transaction_isolation = REPEATABLE-READ なら、コミットされていても過去のデータを遡って読むことがある(過去のインデックスももちろん保全しておかなければならない) ‐ 90/123

Slide 92

Slide 92 text

インデックスのメンテナンス更新する時には書き込み量的には常に悪影響 UPDATE や DELETE のターゲットを絞り込むのにインデックスが使えるケースもあるので飽くまで「書き込み量」 ‐ 検索する時には(最適なインデックスを選んでいる限り)読み出し量的には好影響、ワーストケースでも影響なしただし、使うインデックスを間違えて選んであばばばばばばってなるリスクはある ‐ 本来ならSELECTした時に発生する絞り込みやソートのコストを、INSERT(や UPDATE/DELETE)時に転嫁するのがインデックス 91/123

Slide 93

Slide 93 text

インデックスの容量「あらかじめソート済の部分集合を作って保管しておく」ので、行データ本体とは別に追加の容量が必要になる 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

Slide 94

Slide 94 text

Slide 95

Slide 95 text

インデックスの功罪インデックスが少なすぎるとクエリーに時間がかかる時間がかかるだけならまだしもロック範囲が大きくなることがあるインデックスが多すぎると倍々ゲームで容量を食われていく実は少しずつ更新の時のレイテンシーが上がっていく 94/123

Slide 96

Slide 96 text

ネクストキーロック transaction_isolation >= REPEATABLE-READ の時に使われるロック transaction_isolation = READ-COMMITTED の場合は使われない ‐ ファントムリードを防ぐために「本来必要なリーフノードと次のリーフノード、更にそれらのギャップ」をロックする 95/123

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

InnoDBのロック infinum .. 無限小を表すリーフノード supernum .. 無限大を表すリーフノード [gap] .. リーフノードとリーフノードの隙間 97/123

Slide 99

Slide 99 text

InnoDBのロック REC .. そのリーフと手前のリーフの間のギャップ REC not GAP .. そのリーフそのもの GAP .. そのリーフの手前のギャップ 98/123

Slide 100

Slide 100 text

InnoDBのロック UPDATE user SET age = 39 WHERE name = 'yoku0825' トランザクション分離レベルにかかわらずPK引き ‐ 99/123

Slide 101

Slide 101 text

InnoDBのロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = 12 WHERE age = 11 REPEATABLE-READで非ユニークなインデックスを引いたUPDATE ‐ 100/123

Slide 102

Slide 102 text

InnoDBのロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = 12 WHERE age = 11 REPEATABLE-READで非ユニークなインデックスを引いたUPDATE ‐ 101/123

Slide 103

Slide 103 text

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

Slide 104

Slide 104 text

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

Slide 105

Slide 105 text

InnoDBのロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = age + 1 WHERE gender = 'F' REPEATABLE-READで非ユニークなインデックスを引いたUPDATEその2 ‐ 104/123

Slide 106

Slide 106 text

InnoDBのロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = age + 1 WHERE gender = 'F' REPEATABLE-READで非ユニークなインデックスを引いたUPDATEその2 ‐ 105/123

Slide 107

Slide 107 text

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

Slide 108

Slide 108 text

インデックスマージに注意 UPDATE user SET age = age + 1 WHERE age = 38 AND gender = 'M' が INDEX(age) と INDEX(gender) のインデックスマージ ( Using intersect )を狙った場合 107/123

Slide 109

Slide 109 text

インデックスマージに注意 UPDATE user SET age = age + 1 WHERE age = 38 AND gender = 'M' が INDEX(age) と INDEX(gender) のインデックスマージ ( Using intersect )を狙った場合 108/123

Slide 110

Slide 110 text

InnoDBのロック infinum (無限小) と supernum (無限大)のリーフが存在する「手前のギャップ」「ネクストキー(=次のリーフ)」が俺のたとえで見つからない場合でも、これら2つのリーフは存在するので必ず「手前のギャップ(ワーストで無限小からそのキーまで)」と「ネクストキー(ワーストでそのキーから無限大まで)」は存在する transaction_isolation = READ-COMMITTED の時はギャップを取らないので transaction_isolation = REPEATABLE-READ と比べて REC は REC not GAP に ‐ REC not GAP はそのまま ‐ GAP だけのロックだったところはロックがとられない ‐ 109/123

Slide 111

Slide 111 text

InnoDBのロックてっきり読み出しの最適化にばかり使われると思っていたインデックスは実はロックにも使われている仕組みを知らないとネクストキーロックは「何が起こっているのかよくわからない」 ‐ 「全ての読んだリーフとその手前のギャップのロック、ただしbreakするために読んだリーフは手前のギャップだけ」 ‐ これもよくわからなくなったらツリーを書くと案外わかりやすかったりする infinumとsupernumのリーフが存在しているのを知っていればいける気がする 110/123

Slide 112

Slide 112 text

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

Slide 113

Slide 113 text

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

Slide 114

Slide 114 text

疑似セカンダリーインデックス「トランザクションに保護される」、あるいは「トランザクションに保護されなくて良い」保護されたいなら、アプリのトランザクションで一緒に保護するか(増えた時に抜ける可能性があるので微妙)トリガーで同期する ‐ 保護されなくて良い、バッチで洗い替えすれば良い、みたいなのなら楽 ‐ 「行データの部分集合をあらかじめソートしたもの」これを表現するために、結局「疑似セカンダリーインデックステーブル」にはセカンダリーインデックスが必要なわけだけれど ‐ あたりを肝に命じておくと、「実際にはテーブルにインデックスを張らなくても (ALTER TABLEのオーバーヘッドが無視できないとか)セカンダリーインデックスを模すことができる」結局は「いつ・どこにコストを転嫁するか」というはなし ‐ 113/123

Slide 115

Slide 115 text

疑似セカンダリーインデックス 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

Slide 116

Slide 116 text

疑似セカンダリーインデックス 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

Slide 117

Slide 117 text

疑似セカンダリーインデックス 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

Slide 118

Slide 118 text

Slide 119

Slide 119 text

まとめ「主に検索のために」「行データの部分集合を」「あらかじめソートして複製したもの」「ロックの単位でもある」 118/123

Slide 120

Slide 120 text

まとめ「主に検索のために」 WHERE または ORDER BY LIMIT またはその両方に対して、フェッチする行データを大幅に刈り込むことができる ‐ 読み出しI/Oの削減、また同じインデックスが頻繁に使われるならバッファプールヒット率の向上 ‐ 「行データの部分集合を」「あらかじめソートして複製したもの」「ロックの単位でもある」 119/123

Slide 121

Slide 121 text

まとめ「主に検索のために」「行データの部分集合を」ツリー形式で格納する以上、インデックスに使うカラムの並び順は大事 ‐ 迷った時の指針として「まず WHERE を AND でつないだ羅列」余力があれば「 WHERE の羅列の後に ORDER BY 」 ‐ 行データが大きい時はカバリングインデックスというテクニックがある(万能ではない) ‐ 「あらかじめソートして複製したもの」「ロックの単位でもある」 120/123

Slide 122

Slide 122 text

まとめ「主に検索のために」「行データの部分集合を」「あらかじめソートして複製したもの」同じibdファイルの中に追加で容量が必要 ‐ 書き込みI/O的には常にオーバーヘッドが乗る ‐ 「SELECTにかかるコストをINSERT/UPDATE/DELETEの時に転嫁しているだけ」 ‐ 「ロックの単位でもある」 121/123

Slide 123

Slide 123 text

まとめ「主に検索のために」「行データの部分集合を」「あらかじめソートして複製したもの」「ロックの単位でもある」読むリーフの数が少なくなれば少なくなるほどロックの範囲が狭くなる ‐ インデックスマージは複数のインデックスでそれぞれロックがかかるのでロックの範囲がデカい ‐ 122/123

Slide 124

Slide 124 text

Any Questions and/ or Suggestions? 123/123