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
30
DBのindex(B-tree周り) に触れてみよう
indexに関するイントロダクションです。今回は詳細については触れていませんが、B-treeを例に、indexとはどのようなものかについて触れています。
NearMeの技術発表資料です
PRO
May 21, 2024
Tweet
Share
More Decks by NearMeの技術発表資料です
See All by NearMeの技術発表資料です
2つの曲線を比較する方法ってあるの? 〜フレシェ距離を試してみた〜 with Python
nearme_tech
PRO
1
18
Constrained K-means Clustering (クラスタサイズの制限をしたK-means法) を調べてみた
nearme_tech
PRO
0
16
VRPの近傍操作SWAP*について調べてみた
nearme_tech
PRO
1
49
新人エンジニアが読んでためになった本
nearme_tech
PRO
2
21
Object–relational mapping and query builder battle 1: Intro to Prisma
nearme_tech
PRO
1
29
深層学習モデルの最適化 -Deep Learning Tuning Playbookを読む-
nearme_tech
PRO
1
52
機械学習を支える連続最適化
nearme_tech
PRO
1
44
サードパーティクッキーの終焉と Topics APIによる代替の可能性
nearme_tech
PRO
1
81
ONNXハンズオン
nearme_tech
PRO
2
28
Other Decks in Programming
See All in Programming
リハビリmruby
kishima
1
160
はしめてのプログラミングとロボット制御
watawatavoltage
0
290
DDDを志して3年経ったら「DDDの皮を被ったクリーンアーキテクチャ」になった話【デブサミ2024夏】
texmeijin
1
620
Microservices rules (July 2024) : what good looks like
cer
PRO
0
1.6k
英語
s_shimotori
1
220
しくじり先生 Image Matching Challenge 2024 編
goosehaaan
0
810
最近追加した型の紹介とその振り返り
aki19035vc
0
180
日付と正規化
megmogmog1965
0
140
iOSアプリでクリップボードにコピーしたことをユーザーに伝えるちょうど良いフィードバックを探す
ski
0
100
AWS初心者ってどうやってAWSを学ぶ?〜アプリエンジニアがやってよかったアーキテクチャ学習方法〜
yamanashi_ren01
0
190
AHC035解説
terryu16
0
730
Prompt FlowによるLLMアプリケーション開発
yuto2000
1
1k
Featured
See All Featured
Documentation Writing (for coders)
carmenintech
63
4.2k
Speed Design
sergeychernyshev
9
270
A designer walks into a library…
pauljervisheath
201
24k
4 Signs Your Business is Dying
shpigford
178
21k
Faster Mobile Websites
deanohume
303
30k
The Invisible Customer
myddelton
117
13k
"I'm Feeling Lucky" - Building Great Search Experiences for Today's Users (#IAC19)
danielanewman
224
21k
XXLCSS - How to scale CSS and keep your sanity
sugarenia
245
1.2M
Optimizing for Happiness
mojombo
373
69k
Rebuilding a faster, lazier Slack
samanthasiow
78
8.5k
Build The Right Thing And Hit Your Dates
maggiecrowley
28
2.2k
Art, The Web, and Tiny UX
lynnandtonic
291
20k
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