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の技術発表資料です
May 21, 2024
Programming
0
26
DBのindex(B-tree周り) に触れてみよう
indexに関するイントロダクションです。今回は詳細については触れていませんが、B-treeを例に、indexとはどのようなものかについて触れています。
NearMeの技術発表資料です
May 21, 2024
Tweet
Share
More Decks by NearMeの技術発表資料です
See All by NearMeの技術発表資料です
Object–relational mapping and query builder battle 1: Intro to Prisma
nearme_tech
1
10
深層学習モデルの最適化 -Deep Learning Tuning Playbookを読む-
nearme_tech
1
24
機械学習を支える連続最適化
nearme_tech
1
24
サードパーティクッキーの終焉と Topics APIによる代替の可能性
nearme_tech
1
70
ONNXハンズオン
nearme_tech
2
21
Infrastructure as Code: Intro to Pulumi
nearme_tech
1
33
OR-Toolsの中⾝ -VRPの解法について-
nearme_tech
0
81
GTFSのデータを Streamlitで可視化してみた
nearme_tech
0
67
Offset / Cursor Paginationについて
nearme_tech
3
150
Other Decks in Programming
See All in Programming
Spring Bootと行レベルセキュリティではじめるマルチテナントアーキテクチャ / Multi-tenant architecture using Row-Level Security
jaguar_imo
4
1.1k
spaCy meets LLMs: Using Generative AI for Structured Data
inesmontani
PRO
1
550
dockerの豆知識を沢山話す
mal0961
1
210
앱 성능 영혼까지 끌어올리기
veronikapj
0
730
Automatisiere deine Prozesse mit GitHub Actions!
ahus1
0
260
DroidKnights 2024 - Compose UI 컴포넌트 설계와 테스트
wisemuji
0
680
Google Cloudで始めるプラットフォームエンジニアリング
ymd65536
0
320
about #67401 //go:linkname
andpad
1
18k
Android アプリのプロジェクトをモダンにし続ける工夫
numeroanddev
1
380
Bottom-Up Architecture – Bridging the Achitecture Code Gap
olivergierke
3
130
ボトムアップではじめるFour Keys・SPACEを用いた開発プロセスの改善事例 〜開発生産性に向き合ってチームの成長を実感する〜
nomuson
1
210
Vertical Architectures for Scalable Angular Applications
manfredsteyer
PRO
0
190
Featured
See All Featured
Robots, Beer and Maslow
schacon
PRO
155
8k
Building Applications with DynamoDB
mza
89
5.8k
The Straight Up "How To Draw Better" Workshop
denniskardys
228
130k
Typedesign – Prime Four
hannesfritz
36
2.2k
No one is an island. Learnings from fostering a developers community.
thoeni
16
2.2k
Product Roadmaps are Hard
iamctodd
PRO
46
10k
[RailsConf 2023] Rails as a piece of cake
palkan
31
4.2k
Designing on Purpose - Digital PM Summit 2013
jponch
112
6.6k
A Philosophy of Restraint
colly
198
16k
From Idea to $5000 a Month in 5 Months
shpigford
377
46k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
323
21k
The Mythical Team-Month
searls
217
42k
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