Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
DBのindex(B-tree周り) に触れてみよう
Search
NearMeの技術発表資料です
PRO
May 21, 2024
Programming
0
110
DBのindex(B-tree周り) に触れてみよう
indexに関するイントロダクションです。今回は詳細については触れていませんが、B-treeを例に、indexとはどのようなものかについて触れています。
NearMeの技術発表資料です
PRO
May 21, 2024
Tweet
Share
More Decks by NearMeの技術発表資料です
See All by NearMeの技術発表資料です
Rustで作る強化学習エージェント
nearme_tech
PRO
0
30
ビームサーチ
nearme_tech
PRO
0
30
WASM入門
nearme_tech
PRO
0
32
ESLintをもっと有効活用しよう
nearme_tech
PRO
0
23
リファクタリングのための第一歩
nearme_tech
PRO
0
63
ガウス過程回帰とベイズ最適化
nearme_tech
PRO
1
180
確率的プログラミング入門
nearme_tech
PRO
2
110
Observability and OpenTelemetry
nearme_tech
PRO
2
44
観察研究における因果推論
nearme_tech
PRO
1
160
Other Decks in Programming
See All in Programming
クリーンアーキテクチャから見る依存の向きの大切さ
shimabox
4
880
Serverless Rust: Your Low-Risk Entry Point to Rust in Production (and the benefits are huge)
lmammino
1
140
ARA Ansible for the teams
kksat
0
160
GitHub Actions × RAGでコードレビューの検証の結果
sho_000
0
280
『テスト書いた方が開発が早いじゃん』を解き明かす #phpcon_nagoya
o0h
PRO
6
1.9k
仕様変更に耐えるための"今の"DRY原則を考える
mkmk884
8
2.4k
Software Architecture
hschwentner
6
2.1k
Djangoにおける複数ユーザー種別認証の設計アプローチ@DjangoCongress JP 2025
delhi09
PRO
4
380
DRFを少しずつ オニオンアーキテクチャに寄せていく DjangoCongress JP 2025
nealle
2
180
Amazon ECS とマイクロサービスから考えるシステム構成
hiyanger
2
580
Rubyで始める関数型ドメインモデリング
shogo_tksk
0
120
一休.com のログイン体験を支える技術 〜Web Components x Vue.js 活用事例と最適化について〜
atsumim
0
670
Featured
See All Featured
Building a Scalable Design System with Sketch
lauravandoore
461
33k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
233
17k
Learning to Love Humans: Emotional Interface Design
aarron
273
40k
No one is an island. Learnings from fostering a developers community.
thoeni
21
3.1k
4 Signs Your Business is Dying
shpigford
182
22k
Done Done
chrislema
182
16k
What’s in a name? Adding method to the madness
productmarketing
PRO
22
3.3k
Build The Right Thing And Hit Your Dates
maggiecrowley
34
2.5k
Bash Introduction
62gerente
611
210k
Statistics for Hackers
jakevdp
797
220k
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
smashingmag
251
21k
Site-Speed That Sticks
csswizardry
4
390
Transcript
0 DBのindex(B-tree周り) に触れてみよう 2024-05-17 第90回NearMe技術勉強会 Kaito Asahi
1 indexに関して • indexの役割について ◦ クエリの高速化 ◦ 検索の最適化 ◦ ソートの高速化
◦ ユニーク性の保証 …
2 indexに関して • indexの役割について ◦ クエリの高速化 ◦ 検索の最適化 ◦ ソートの高速化
◦ ユニーク性の保証 … どういう仕組みなんだ...?
3 ☕少し脱線 〜SQLは⾮⼿続き型の⾔語〜 • SQLの命令の例 SELECT name, age FROM users
WHERE age >= 20; → 何が欲しいのかを命令するSQL文 → どのようにとってきて欲しいのかは命令していない!!(非手続き型) ∴ 効率の良い DB設計が求められる 💨👍 • どこにどのような 情報があるのか?(インデックス ) • どのように データを取ることが最適か?(統計情報)
4 indexの種類について(MySQL) • B-tree ◦ 最も一般的 なインデックスタイプ ◦ 等価検索および範囲検索に有効 •
HASH ◦ メモリーベースのストレージエンジン で使用される ◦ 等価検索に有効 • FULLTEXT ◦ テキスト検索用 に最適化されている • SPATIAL ◦ 地理データなどの空間的なデータ に最適化されている
5 indexの種類について(MySQL) • B-tree(今日はこれを中心) ◦ 最も一般的 なインデックスタイプ ◦ 等価検索および範囲検索に有効 •
HASH ◦ メモリーベースのストレージエンジン で使用される ◦ 等価検索に有効 • FULLTEXT ◦ テキスト検索用 に最適化されている • SPATIAL ◦ 地理データなどの空間的なデータ に最適化されている
6 ハンズオン(まずは触れてみる) • B-tree 1. MySQLのDocker imageをpullする $ docker pull
mysql 2. コンテナを起動する(rootアカウントのパスワード:root) $ docker run --name test-mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:latest
7 ハンズオン(まずは触れてみる) • B-tree 3. スライド33枚目にあるPythonファイルで、sqlファイルを取得し、これをローカルからコンテナ 内に入 れる $ docker
cp employees_data.sql test-mysql:/ 4. コンテナに入る $ docker exec -it test-mysql sh
8 ハンズオン(まずは触れてみる) • B-tree 5. mysqlにログインする(パスワード:root) sh-5.1# mysql -u root
-p 6. DBを作成する(ここでは、test_db) mysql> CREATE DATABASE test_db; mysql> USE test_db; 7. employeesテーブルを作成する mysql> CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary INT );
9 ハンズオン(まずは触れてみる) • B-tree 8. 一旦ログアウト(exit)し、10万レコードのデータを挿入する sh-5.1# mysql -u root
-p test_db < employees_data.sql 9. もう一度ログインし、id <= 100の範囲でデータをとってみる mysql> USE test_db; mysql> SELECT * FROM employees WHERE id <= 100 ORDER BY id;
10 ハンズオン(まずは触れてみる) • B-tree(コスト⽐較する) A. インデックスを適⽤していないとき mysql> EXPLAIN SELECT *
FROM employees WHERE department = 'Engineering'; +----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 99904 | 10.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
11 ハンズオン(まずは触れてみる) • B-tree(コスト⽐較する) B. インデックスを適⽤するとき mysql> CREATE INDEX idx_department
ON employees(department); +----+-------------+-----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_department | idx_department | 203 | const | 40024 | 100.00 | NULL | +----+-------------+-----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+ 1 row in set, 1 warning (0.01 sec) mysql> EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';
12 ハンズオン(まずは触れてみる) • B-tree(コスト⽐較する) ⾒⽐べると... +----+-------------+-----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+ | id | select_type
| table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_department | idx_department | 203 | const | 40024 | 100.00 | NULL | +----+-------------+-----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+ 1 row in set, 1 warning (0.01 sec) +----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 99904 | 10.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 処理する行数の推定値は 半減以下 インデックスが適用 されている
13
14 B-treeインデックスの仕組み • B-tree ◦ ノードの高さが同じになるような木構造をもつ → 平衡木
◦ 検索、挿入、更新、削除には O(logn) の計算量 ◦ index対象の列はソートされた状態 9 15 4 12 19 2 3 6 8 15 19 ルートノード 中間ノード 葉ノード
15 B-treeインデックスの仕組み • B-tree ◦ ノードの高さが同じになるような木構造をもつ → 平衡木
◦ 検索、挿入、更新、削除には O(logn) の計算量 ◦ index対象の列はソートされた状態 ◦ 等価検索に有効 Ex) 6を検索:9 → 12 → 6を辿れば良い 9 15 4 12 19 2 3 6 8 15 19 ルートノード 中間ノード 葉ノード
16 B-treeインデックスの仕組み • B-tree ◦ ノードの高さが同じになるような木構造をもつ → 平衡木
◦ 検索、挿入、更新、削除には O(logn) の計算量 ◦ index対象の列はソートされた状態 ◦ 等価検索に有効 Ex) 6を検索:9 → 12 → 6を辿れば良い 9 15 4 12 19 2 3 6 8 15 19 ルートノード 中間ノード 葉ノード 各ノードで t 個に分岐されるのであれば、探索範囲は 1/t となるので
17 B-treeインデックスの仕組み • B-tree ◦ ノードの高さが同じになるような木構造をもつ → 平衡木
◦ 検索、挿入、更新、削除には O(logn) の計算量 ◦ index対象の列はソートされた状態 ◦ 等価検索に有効 ◦ 範囲検索に有効 Ex) 6以下の値の検索:9 → 12 → 6より左の値 9 15 4 12 19 2 3 6 8 15 19 ルートノード 中間ノード 葉ノード ソートのおかげ
18 B-treeインデックスの仕組み • B-tree ◦ ノードの高さが同じになるような木構造をもつ → 平衡木
◦ 検索、挿入、更新、削除には O(logn) の計算量 ◦ index対象の列はソートされた状態 ◦ 等価検索に有効 ◦ 範囲検索に有効 ▪ “!=”(否定)には効果を持たない → 左と右どちらに値があるのか不明... 9 15 4 12 19 2 3 6 8 15 19 ルートノード 中間ノード 葉ノード
19 indexは張れば張るほど良いのでは!!!
20 indexは張れば張るほど良いのでは!!!
21 B-treeインデックスを有効にするために • カーディナリティの高い ものに ◦ なるべく値の重複の少ない列に • 大きなテーブル に対して利用
• WHERE句などの選択条件 に使用されている列に
22 B-treeインデックスを有効にするために • カーディナリティの高い ものに ◦ なるべく値の重複の少ない列 に(バリエーションがより豊富な列) 1. カーディナリティが高いもの
a. ユーザーID b. メールアドレス c. シリアルナンバー 2. カーディナリティが低いもの a. 性別 b. 婚姻状況 c. 部署名
23 B-treeインデックスを有効にするために • 大きなテーブル に対して利用 データが n 個の場合... 1. 全検索には
O(n) だけ時間がかかる 2. B-treeインデックスでは、O(logn) だけ時間がかかる → 小さい n では、nとlognにあまり差はない → より大きい n に対しては、 B-treeインデックスはとても有効!!
24 B-treeインデックスを有効にするために • WHERE句などの選択条件 に使用されている列に → ソートされた id での条件(id <
100)→ 主キーであれば、基本的にB-treeが適用される ※ 比較が曖昧なものにはインデックスが有効にならない 1. 否定(<>, !=) 2. OR 3. IS NULL 4. 値に演算を行なっている(id * 3 < 100, SUBSTR(id, 1, 1) = …など)
25 indexに最適なカラムとはどのようなものか? ~IDについて~ • データの挿入 1. 連番やソート可能なindexをもつ場合 1,2,3,...,100 1,2,3,...,40 41,42,43,...80
81,82,83,...,100 1,...10 91,...100 ・・・ 新しい
26 indexに最適なカラムとはどのようなものか? ~IDについて~ • データの挿入 1. 連番やソート可能なindexをもつ場合 1,2,3,...,100 1,2,3,...,40 41,42,43,...80
81,82,83,...,100 1,...10 91,...100 ・・・ 新しい 新しいデータをどの葉ノードに入れるかの チェックは右端だけで済む
27 indexに最適なカラムとはどのようなものか? ~IDについて~ • データの挿入 1. 連番やソート可能なindexをもつ場合 1,2,3,...,100 1,2,3,...,40 41,42,43,...80
81,82,83,...,100 1,...10 91,...100 ・・・ 新しい 新しいデータをどの葉ノードに入れるかの チェックは右端だけで済む しかもキャッシュが あればもっと速い!! 最新のデータをバッファプールに おけばキャッシュヒット率は高くなる
28 indexに最適なカラムとはどのようなものか? ~IDについて~ • データの挿入 2. UUIDなどのランダムな場合 ・・・ 新しい
29 indexに最適なカラムとはどのようなものか? ~IDについて~ • データの挿入 2. UUIDなどのランダムな場合 ・・・ 新しい どこに入れるべきか不明なので、
入れる場所の探索にコストがかかる
30 indexに最適なカラムとはどのようなものか? ~IDについて~ • データの挿入 2. UUIDなどのランダムな場合 ・・・ 新しい どこに入れるべきか不明なので、
入れる場所の探索にコストがかかる キャッシュを利用 できる保証がない ... キャッシュにヒットするかは運ゲー 😇
31 補⾜スライド
32 今回利⽤したsqlファイルの⽣成⽅法 import random # 生成するデータの数 num_records = 100000 #
例として10万レコードを生成 departments = ['Engineering', 'HR', 'Marketing', 'Sales', 'Finance'] names = ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace', 'Hannah', 'Ivan', 'Julia'] with open('employees_data.sql', 'w') as file: file.write("USE test_db;\n") # 使用するデータベース(test_db)を指定 file.write("INSERT INTO employees (name, department, salary) VALUES\n") # レコードを生成 for i in range(num_records): name = random.choice(names) department = random.choice(departments) salary = random.randint(30000, 120000) # 最後のレコード以外はカンマを付ける if i < num_records - 1: file.write(f"('{name}', '{department}', {salary}),\n") else: file.write(f"('{name}', '{department}', {salary});\n")
33 参考⽂献 • 達人に学ぶDB設計 徹底指南書 ~初級者で終わりたくないあなたへ ◦ https://www.shoeisha.co.jp/book/detail/9784798124704 • 計算量同士の比較と入力サイズによる比較
◦ https://algo-logic.info/complexity-compare/ • MySQLでプライマリキーをUUIDにする前に知っておいて欲しいこと ◦ https://techblog.raccoon.ne.jp/archives/1627262796.html • UUIDとULIDの違いと種類を解説【ULID=ソート可能なUUID?】 ◦ https://giginc.co.jp/blog/giglab/uuid-ulid • ソート可能なUUID互換のulidが便利そう ◦ https://qiita.com/kai_kou/items/b4ac2d316920e08ac75a • MySQLバッファプールについて ◦ https://dev.mysql.com/doc/refman/8.0/ja/innodb-buffer-pool.html • 参照の局所性 ◦ https://ja.wikipedia.org/wiki/%E5%8F%82%E7%85%A7%E3%81%AE%E5%B1%80%E6%89 %80%E6%80%A7
34 Thank you