サンプルデータ 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
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
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
バリエーション 複合インデックスを使い切れるケース 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
バリエーション ORDER BY countrycode ASC, population DESC だけは、時代(MySQL 8.0)が多少解 決した インデックスを作る段階で INDEX(countrycode ASC, population DESC) で作っておく ‐ MySQL 8.0でもASC, ASCで作ってたらやっぱり使えない ‐ 想像以上にフェッチしている行が多くなったときは図にしてみると案外合点がいっ たりする 追加ソートが必要になる Extra: Using filesort とかも図にするとわかりやすい ‐ 58/123
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
InnoDBのロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = age + 1 WHERE gender = 'F' REPEATABLE-READで非ユニークなインデックスを引いたUPDATEその2 ‐ 104/123
InnoDBのロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = age + 1 WHERE gender = 'F' REPEATABLE-READで非ユニークなインデックスを引いたUPDATEその2 ‐ 105/123
疑似セカンダリーインデックス 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
疑似セカンダリーインデックス 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
まとめ 「主に検索のために」 「行データの部分集合を」 ツリー形式で格納する以上、インデックスに使うカラムの並び順は大事 ‐ 迷った時の指針として「まず WHERE を AND でつないだ羅列」余力があれば「 WHERE の羅列の後 に ORDER BY 」 ‐ 行データが大きい時はカバリングインデックスというテクニックがある(万能ではない) ‐ 「あらかじめソートして複製したもの」 「ロックの単位でもある」 120/123
yoku0825
andpad
lamrongol
mixi_engineers
oracle4engineer
techtekt
policemankh
taro_nanairo
shohata
hikiroku
line_developers
onlinesalon
paulrobertlloyd
sachag
reverentgeek
jonyablonski
thekraken
trallard
mongodb
eitanlees
cherdarchuk
colly
dotmariusz
scottboms