优化数据库查询性能, 巧妙利用索引加速

阿九筒
198 阅读3分钟

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()
  • 构造完成的数据库

image.png

3. 查询数据

3.1. 未加索引查询结果

  • 查询语句
SELECT
	* 
FROM
	"test_index" 
WHERE
	tel = '3955777869';
  • 查询时间：3.8S

img_v3_02ft_77e5486d-7842-48aa-ae0c-17351465925g.jpg

3.2. 增加索引后的查询结果

  • 给tel字段增加索引,使用以下SQL语句：
CREATE INDEX idx_tel ON test_index ( tel );

  • 在2000万行数据上添加索引用时56秒。请注意对于大数据量的表格，添加索引可能需要较长时间。 image.png

  • 添加索引后，执行查询：

SELECT
	* 
FROM
	"test_index" 
WHERE
	tel = '3955777869';
  • 查询时间缩短至0.001s

image.png

3.3. 结论

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

4. 查询提速分析

4.1. 索引为何能加快查询速度？

数据库索引采用B+树结构，能够显著优化查询速度。 image.png

4.2. 数据库索引为什么选择B+树？

  • 在探讨数据库索引为何选择B+树之前，我们先了解几种常见的树型数据结构。

  1. ‌二叉搜索树‌：

    • 当数据量较大时，二叉搜索树的深度会相应增加，导致查询速度变慢。
    • 每个节点仅存储一个数据，查询过程中可能产生大量的磁盘I/O操作。
      image.png

  2. ‌B树‌：

    • 与二叉树不同，B树的子节点不是二分，而是m分叉。
    • B树完美地利用了“局部性原理”。
      image.png

  3. ‌哈希‌：

    • 例如HashMap，查询、插入、修改、删除的平均时间复杂度都是O(1)。
    • 然而，哈希索引在处理排序、分组、范围查询（如>、<）时，时间复杂度会降至O(n)。

  4. ‌B+树‌：

    • B+树是B树的一种变形，其叶子节点存储关键字及相应记录的地址，而叶子节点以上的各层则作为索引使用。
    • 在B+树中，当索引部分的某个节点的关键字与所查关键字相等时，查找并不停止，而是继续沿着该关键字左边的指针向下，直至找到关键字所在的叶子节点。
    • B+树的设计使得数据库索引能够高效地支持范围查询、排序和分组操作，同时减少磁盘I/O次数，提高查询性能。

4.3. 结论

综上所述，数据库索引选择B+树是因为它能够在保持较低树高度的同时，优化磁盘I/O性能，并支持高效的范围查询、排序和分组操作。这些特性使得B+树成为数据库索引的理想选择。

avatar
文章
阅读
粉丝