巧妙利用索引加速，最佳化資料庫查詢效能

1. 新建表 - 測試使用SQLITE

• 建立資料庫檔案

• 使用命令列建立test.db

touch ./test.db

• 新建表

• 使用一下SQL語句在SQLite中建立test_index表

CREATE TABLE "test_index" (
  "id" INTEGER NOT NULL,
  "name" VARCHAR(32),
  "password" VARCHAR(128),
  "tel" VARCHAR(11),
  "is_admin" BOOLEAN,
  "update_time" DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "is_deleted" INTEGER,
  PRIMARY KEY ("id")
);

2. 構造資料

使用faker模組構造2000W條不同資訊的用戶數據.

• 安裝Faker

pip install Faker==30.8.0

• Python指令碼(test.py)

db = r"./test.db"

import sqlite3
from faker import Faker
import random

# 初始化 Faker 庫
fake = Faker()

# 連線到 SQLite 資料庫
conn = sqlite3.connect(db)
cursor = conn.cursor()

# 開始生成和插入資料
num_records = 20000000  # 2000萬條資料，大概檔案2GB檔案
batch_size = 10000  # 每次插入的記錄數，減少事務提交的次數提高效能

for i in range(0, num_records, batch_size):
    records = []
    for _ in range(batch_size):
        name = fake.name()
        password = fake.email()
        tel = fake.phone_number()
        is_admin = 1
        update_time = fake.date_time()
        is_deleted = 0
        records.append((name, password, tel, is_admin, update_time, is_deleted))

    # 執行批次插入
    cursor.executemany(
        'INSERT INTO test_index (name, password, tel, is_admin, update_time, is_deleted) VALUES (?, ?, ?, ?, ?, ?)',
        records)
    conn.commit()
    print(f'{i + batch_size}/{num_records} records inserted')

# 關閉連線
conn.close()

• 構造完成的資料庫

3. 查詢資料

3.1. 未加索引查詢結果

• 查詢語句

SELECT
	* 
FROM
	"test_index" 
WHERE
	tel = '3955777869';

• 查詢時間：3.8S

3.2. 增加索引後的查詢結果

• 給tel欄位增加索引,使用以下SQL語句：

CREATE INDEX idx_tel ON test_index ( tel );

• 在2000萬行資料上新增索引用時56秒。請注意對於大資料量的表格，新增索引可能需要較長時間。

• 新增索引後，執行查詢：

SELECT
	* 
FROM
	"test_index" 
WHERE
	tel = '3955777869';

• 查詢時間縮短至0.001s

3.3. 結論

• 索引可以有效增加查詢速度

4. 查詢提速分析

4.1. 索引為何能加快查詢速度？

資料庫索引採用B+樹結構，能夠顯著最佳化查詢速度。

4.2. 資料庫索引為什麼選擇B+樹？

• 在探討資料庫索引為何選擇B+樹之前，我們先了解幾種常見的樹型數據結構。

1. ‌二叉搜尋樹‌：

• 當資料量較大時，二叉搜尋樹的深度會相應增加，導致查詢速度變慢。

• 每個節點僅儲存一個數據，查詢過程中可能產生大量的磁碟I/O操作。
  

2. ‌B樹‌：

• 與二叉樹不同，B樹的子節點不是二分，而是m分叉。

• B樹完美地利用了“區域性性原理”。
  

3. ‌雜湊‌：

• 例如HashMap，查詢、插入、修改、刪除的平均時間複雜度都是O(1)。

• 然而，雜湊索引在處理排序、分組、範圍查詢（如>、<）時，時間複雜度會降至O(n)。

4. ‌B+樹‌：

• B+樹是B樹的一種變形，其葉子節點儲存關鍵字及相應記錄的地址，而葉子節點以上的各層則作為索引使用。

• 在B+樹中，當索引部分的某個節點的關鍵字與所查關鍵字相等時，查詢並不停止，而是繼續沿著該關鍵字左邊的指標向下，直至找到關鍵字所在的葉子節點。

• B+樹的設計使得資料庫索引能夠高效地支援範圍查詢、排序和分組操作，同時減少磁碟I/O次數，提高查詢效能。

4.3. 結論

綜上所述，資料庫索引選擇B+樹是因為它能夠在保持較低樹高度的同時，最佳化磁碟I/O效能，並支援高效的範圍查詢、排序和分組操作。這些特性使得B+樹成為資料庫索引的理想選擇。