Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQLとインデックスと私

 MySQLとインデックスと私

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

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

0deae06ab5d86b39feeec2e23a30b88a?s=128

yoku0825
PRO

May 24, 2021
Tweet

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