$30 off During Our Annual Pro Sale. View Details »

MySQLとインデックスと私

 MySQLとインデックスと私

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

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

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

yoku0825
PRO

May 24, 2021
Tweet

More Decks by yoku0825

Other Decks in Technology

Transcript

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

    View Slide

  2. はじめに
    昨年(?)の三苫さんの資料がとても網羅的でわかりやすいので絶対に読んでおくと
    良いと思います
    データベース

    データベース設計 (サイボウズ社内用の資料でした)

    USE THE INDEX, LUKE! もお勧めです
    SQLのインデックスとそのチューニングについてのオンラインブック

    1/123

    View Slide

  3. \おはようございます/
    yoku0825@とある企業のDBA
    オラクれない

    ポスグれない

    マイエスキューエる

    生息域
    Twitter: @yoku0825

    Blog: 日々の覚書

    日本MySQLユーザ会

    MySQL Casual

    サイボウズ MySQLコンサルタント

    2/123

    View Slide

  4. (この資料における)インデックス #とは
    MySQLのInnoDBストレージエンジンの話です。RDBMSやストレージエンジンが
    変わると多少事情は変わります
    イメージしやすくするための「たとえ」がほとんどなので、内部実装的な話はさらに事情が変
    わります

    MySQLで一番よく使われる、B+Treeインデックスのことについて説明します
    InnoDBにおける「インデックス」はPRIMARY KEYのことも含みます。InnoDBで
    は「行データ」は「PRIMARY KEYのB+Treeのリーフ」にぶら下がっています
    3/123

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  11. INDEX(continent) 相当の構造
    「部分集合」「あらかじめソート」のあたりを感じ取っていただきたい
    セカンダリーインデックスのリーフには「PRIMARY KEYの値」が書いてあります

    +-----------+-----------------+
    | continent | clustered_index |
    +-----------+-----------------+
    | Africa | AGO |
    | Africa | BDI |
    | Africa | BEN |
    +-----------+-----------------+
    10/123

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide


  21. 20/123

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  38. WHERE countrycode = JPN
    37/123

    View Slide

  39. WHERE countrycode = JPN
    38/123

    View Slide

  40. WHERE countrycode = JPN
    39/123

    View Slide

  41. WHERE countrycode = JPN
    40/123

    View Slide

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

    41/123

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  46. WHERE population = 7980230 その2
    45/123

    View Slide

  47. WHERE population = 7980230 その2
    46/123

    View Slide

  48. WHERE population = 7980230 その2
    47/123

    View Slide

  49. インデックスコンディションプッシュダウン
    そんなに良いものでもないし、「テーブルスキャンよりはマシなのでは?」って時
    に選ばれる(ことになっている)
    行データをフェッチするときは「必要のないカラム」さえ同時に読まなければいけ
    ないので、「必要のないカラム」を読むコストが大きくなればなるほどインデック
    スコンディションプッシュダウンは効果を発揮する
    たとえばやたら大きいvarcharのカラムがインライン格納されちゃっているとか

    二度引きの手間を差し引くとテーブルスキャンの方が速くなることも多々

    48/123

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  60. ひとやすみ
    59/123

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  64. ORDER BY .. LIMIT .. の最適化
    俺の「Aが最小であること」「Aは3枚以上存在すること」の知識に該当するものが
    RDBMSのインデックス( INDEX(num) )
    インデックスを引くことで「最小の値」は調べがつく

    B+Treeなので連接リストをたどっていくだけで「小さい方から必要なだけ」

    「必要なぶんだけ取ったらあとはもう読まなくて良い」

    LIMITと併用することで、 ORDER BY狙いの戦略 がフェッチする行の数を大きく減
    らすことができる
    63/123

    View Slide

  65. INDEX(num)
    64/123

    View Slide

  66. INDEX(num)
    65/123

    View Slide

  67. INDEX(num)
    66/123

    View Slide

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

    View Slide

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

    View Slide

  70. インデックスマージ
    複数のインデックスを同時に使う
    EXPLAINで Extra: Using intersect と Extra: Using union のケース

    ただしネクストキーロックと無茶苦茶相性が悪い
    (単独では絞り込み切れない)それぞれのインデックスにロックを置くのでロック範囲がかえっ
    てバカでかくなる

    69/123

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  79. INDEX(num) vs INDEX(suite)
    PK IN (9, 12, 16, 30, 2, 3, 9, 12, ..)
    Extra: Using union

    union .. 統一

    78/123

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  89. もうひとや
    すみ
    88/123

    View Slide

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

    View Slide

  91. インデックスのメンテナンス
    当然ながら、インデックスを使って検索するには「インデックスと行データが完全
    に同期されている状態」でなければならない
    INSERTされればインデックスもリーフが増え

    UPDATEされればリーフの位置が変わり

    DELETEされればリーフが消される

    しかも、トランザクション分離レベルの都合に合わせて
    transaction_isolation = REPEATABLE-READ なら、コミットされていても過去のデータを遡って
    読むことがある(過去のインデックスももちろん保全しておかなければならない)

    90/123

    View Slide

  92. インデックスのメンテナンス
    更新する時には書き込み量的には常に悪影響
    UPDATE や DELETE のターゲットを絞り込むのにインデックスが使えるケースもあるので飽くま
    で「書き込み量」

    検索する時には(最適なインデックスを選んでいる限り)読み出し量的には好影響、
    ワーストケースでも影響なし
    ただし、使うインデックスを間違えて選んであばばばばばばってなるリスクはある

    本来ならSELECTした時に発生する絞り込みやソートのコストを、INSERT(や
    UPDATE/DELETE)時に転嫁するのがインデックス
    91/123

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  96. ネクストキーロック
    transaction_isolation >= REPEATABLE-READ の時に使われるロック
    transaction_isolation = READ-COMMITTED の場合は使われない

    ファントムリードを防ぐために「本来必要なリーフノードと次のリーフノード、更
    にそれらのギャップ」をロックする
    95/123

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    99/123

    View Slide

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

    100/123

    View Slide

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

    101/123

    View Slide

  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

    View Slide

  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

    View Slide

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

    104/123

    View Slide

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

    105/123

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    REC not GAP はそのまま

    GAP だけのロックだったところはロックがとられない

    109/123

    View Slide

  111. InnoDBのロック
    てっきり読み出しの最適化にばかり使われると思っていたインデックスは実はロッ
    クにも使われている
    仕組みを知らないとネクストキーロックは「何が起こっているのかよくわからない」

    「全ての読んだリーフとその手前のギャップのロック、ただしbreakするために読んだリーフ
    は手前のギャップだけ」

    これもよくわからなくなったらツリーを書くと案外わかりやすかったりする
    infinumとsupernumのリーフが存在しているのを知っていればいける気がする
    110/123

    View Slide

  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

    View Slide

  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

    View Slide

  114. 疑似セカンダリーインデックス
    「トランザクションに保護される」、あるいは「トランザクションに保護されなく
    て良い」
    保護されたいなら、アプリのトランザクションで一緒に保護するか(増えた時に抜ける可能性が
    あるので微妙)トリガーで同期する

    保護されなくて良い、バッチで洗い替えすれば良い、みたいなのなら楽

    「 行データの部分集合をあらかじめソートしたもの」
    これを表現するために、結局「疑似セカンダリーインデックステーブル」にはセカンダリーイ
    ンデックスが必要なわけだけれど

    あたりを肝に命じておくと、「実際にはテーブルにインデックスを張らなくても
    (ALTER TABLEのオーバーヘッドが無視できないとか)セカンダリーインデックス
    を模すことができる」
    結局は「いつ・どこにコストを転嫁するか」というはなし

    113/123

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  120. まとめ
    「主に検索のために」
    WHERE または ORDER BY LIMIT またはその両方に対して、フェッチする行データを大幅に刈り込
    むことができる

    読み出しI/Oの削減、また同じインデックスが頻繁に使われるならバッファプールヒット率の向


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

    View Slide

  121. まとめ
    「主に検索のために」
    「行データの部分集合を」
    ツリー形式で格納する以上、インデックスに使うカラムの並び順は大事

    迷った時の指針として「まず WHERE を AND でつないだ羅列」余力があれば「 WHERE の羅列の後
    に ORDER BY 」

    行データが大きい時はカバリングインデックスというテクニックがある(万能ではない)

    「あらかじめソートして複製したもの」
    「ロックの単位でもある」
    120/123

    View Slide

  122. まとめ
    「主に検索のために」
    「行データの部分集合を」
    「あらかじめソートして複製したもの」
    同じibdファイルの中に追加で容量が必要

    書き込みI/O的には常にオーバーヘッドが乗る

    「SELECTにかかるコストをINSERT/UPDATE/DELETEの時に転嫁しているだけ」

    「ロックの単位でもある」
    121/123

    View Slide

  123. まとめ
    「主に検索のために」
    「行データの部分集合を」
    「あらかじめソートして複製したもの」
    「ロックの単位でもある」
    読むリーフの数が少なくなれば少なくなるほどロックの範囲が狭くなる

    インデックスマージは複数のインデックスでそれぞれロックがかかるのでロックの範囲がデカ


    122/123

    View Slide

  124. Any Questions and/
    or Suggestions?
    123/123

    View Slide