如何使用索引加速SQL查询 [Python版]
使用索引加速SQL查询 [Python版]
假设你正在翻阅一本书的页面。你想要更快地找到你要找的信息。你会怎么做呢？嗯，你可能会查阅术语索引，然后跳转到引用特定术语的页面。 SQL中的索引的工作方式与书籍中的索引类似。
在大多数实际系统中，您将对包含大量行的数据库表运行查询（想象一下数百万行）。需要通过扫描所有行来检索结果的查询将非常缓慢。如果您知道您经常需要基于某些列查询信息，您可以在这些列上创建数据库索引。这将显著加快查询速度。
那么今天我们要学习什么呢？我们将学习如何在Python中连接和查询SQLite数据库-使用sqlite3模块。我们还将学习如何添加索引并查看它如何提高性能。
要在本教程中编写代码，您应该在您的工作环境中安装Python 3.7+和SQLite。
注意：本教程中的示例和样本输出适用于Ubuntu LTS 22.04上的Python 3.10和SQLite3（版本3.37.2）。
连接到Python中的数据库
我们将使用内置的sqlite3模块。在我们开始运行查询之前，我们需要：
- 连接到数据库
- 创建一个数据库游标来运行查询
要连接到数据库，我们将使用sqlite3模块的
connect()函数。一旦我们建立了连接，我们可以在连接对象上调用cursor()来创建一个数据库游标，如下所示：
import sqlite3
# 连接到数据库
db_conn = sqlite3.connect('people_db.db')
db_cursor = db_conn.cursor()
这里我们尝试连接到数据库“
people_db。如果数据库不存在，运行上述代码段将为我们创建SQLite数据库。
创建表并插入记录
现在，我们将在数据库中创建一个表，并用记录填充它。
让我们在people_db数据库中创建一个名为people的表，该表具有以下字段：
- name
- job
# main.py
...
# 创建表
db_cursor.execute('''CREATE TABLE people (
id INTEGER PRIMARY KEY,
name TEXT,
email TEXT,
job TEXT)''')
...
# 提交事务并关闭游标和数据库连接
db_conn.commit()
db_cursor.close()
db_conn.close()
使用Faker生成合成数据
现在，我们需要将记录插入表中。为此，我们将使用Faker-一种用于生成合成数据的Python包-通过pip安装：
$ pip install faker
安装faker后，您可以将Faker类导入Python脚本：
# main.py
...
from faker import Faker
...
下一步是生成并插入people表中的记录。为了知道索引如何加速查询，让我们插入大量记录。在这里，我们将插入10万条记录；将num_records变量设置为100000。
然后，我们执行以下操作：
- 实例化一个
Faker对象fake并设置种子以便我们可以获得可复制性。 - 使用名字和姓氏生成一个名字字符串-通过在
fake对象上调用first_name()和last_name()。 - 通过调用
domain_name()生成一个虚假域名。 - 使用名字和姓氏以及域名生成电子邮件字段。
- 使用
job()为每个个人记录获取一个职位。
我们生成并插入people表的记录：
# 创建并插入记录
fake = Faker() # 确保导入：from faker import Faker
Faker.seed(42)
num_records = 100000
for _ in range(num_records):
first = fake.first_name()
last = fake.last_name()
name = f"{first} {last}"
domain = fake.domain_name()
email = f"{first}.{last}@{domain}"
job = fake.job()
db_cursor.execute('INSERT INTO people (name, email, job) VALUES (?,?,?)', (name,email,job))
# 提交事务并关闭游标和数据库连接
db_conn.commit()
db_cursor.close()
db_conn.close()
现在main.py文件的代码如下：
# main.py
# 导入
import sqlite3
from faker import Faker
# 连接数据库
db_conn = sqlite3.connect('people_db.db')
db_cursor = db_conn.cursor()
# 创建表
db_cursor.execute('''CREATE TABLE people (
id INTEGER PRIMARY KEY,
name TEXT,
email TEXT,
job TEXT)''')
# 创建并插入记录
fake = Faker()
Faker.seed(42)
num_records = 100000
for _ in range(num_records):
first = fake.first_name()
last = fake.last_name()
name = f"{first} {last}"
domain = fake.domain_name()
email = f"{first}.{last}@{domain}"
job = fake.job()
db_cursor.execute('INSERT INTO people (name, email, job) VALUES (?,?,?)', (name,email,job))
# 提交事务并关闭游标和数据库连接
db_conn.commit()
db_cursor.close()
db_conn.close()
运行此脚本 – 一次 – 以使用num_records数量的记录填充表。
查询数据库
现在我们有了包含10万条记录的表，让我们在people表上运行一个示例查询。
让我们运行一个查询来：
- 获取工作职位为“产品经理”的记录的姓名和电子邮件，并且
- 将查询结果限制为10条记录。
我们将使用time模块的默认计时器来获取查询的大致执行时间。
# sample_query.py
import sqlite3
import time
db_conn = sqlite3.connect("people_db.db")
db_cursor = db_conn.cursor()
t1 = time.perf_counter_ns()
db_cursor.execute("SELECT name, email FROM people WHERE job='Product manager' LIMIT 10;")
res = db_cursor.fetchall()
t2 = time.perf_counter_ns()
print(res)
print(f"没有索引的查询时间：{(t2-t1)/1000} us")
以下是输出结果：
输出 >>
[
("Tina Woods", "[email protected]"),
("Toni Jackson", "[email protected]"),
("Lisa Miller", "[email protected]"),
("Katherine Guerrero", "[email protected]"),
("Michelle Lane", "[email protected]"),
("Jane Johnson", "[email protected]"),
("Matthew Odom", "[email protected]"),
("Isaac Daniel", "[email protected]"),
("Jay Byrd", "[email protected]"),
("Thomas Kirby", "[email protected]"),
]
没有索引的查询时间：448.275 us
您还可以通过在命令行中运行sqlite3 db_name来调用SQLite命令行客户端：
$ sqlite3 people_db.db
SQLite version 3.37.2 2022-01-06 13:25:41
输入“.help”获取用法提示。
要获取索引列表，可以运行.index：
sqlite> .index
由于当前没有索引，因此不会列出任何索引。
您还可以这样查看查询计划：
sqlite> EXPLAIN QUERY PLAN SELECT name, email FROM people WHERE job='Product Manager' LIMIT 10;
查询计划
`--扫描 people
这里的查询计划是扫描所有行，效率较低。
在特定列上创建索引
要在特定列上创建数据库索引，可以使用以下语法：
CREATE INDEX index-name on table (column(s))
假设我们经常需要查找具有特定职位的个人记录。在职位列上创建索引people_job_index会很有帮助：
# create_index.py
import time
import sqlite3
db_conn = sqlite3.connect('people_db.db')
db_cursor =db_conn.cursor()
t1 = time.perf_counter_ns()
db_cursor.execute("CREATE INDEX people_job_index ON people (job)")
t2 = time.perf_counter_ns()
db_conn.commit()
print(f"创建索引所需时间：{(t2 - t1)/1000} us")
输出 >>
创建索引所需时间：338298.6 us
尽管创建索引需要这么长时间，但这是一次性操作。在运行多个查询时，仍然会获得显著的加速。
现在，如果在SQLite命令行客户端上运行.index，您将得到：
sqlite> .index
people_job_index
使用索引查询数据库
如果现在查看查询计划，您应该能够看到我们现在使用索引people_job_index在job列上搜索people表：
sqlite> EXPLAIN QUERY PLAN SELECT name, email FROM people WHERE job='Product manager' LIMIT 10;
查询计划
`--使用索引 people_job_index 搜索 people (job=?)
您可以重新运行sample_query.py。只需修改print()语句，然后查看查询运行所需的时间：
# sample_query.py
import sqlite3
import time
db_conn = sqlite3.connect("people_db.db")
db_cursor = db_conn.cursor()
t1 = time.perf_counter_ns()
db_cursor.execute("SELECT name, email FROM people WHERE job='Product manager' LIMIT 10;")
res = db_cursor.fetchall()
t2 = time.perf_counter_ns()
print(res)
print(f"使用索引的查询时间：{(t2-t1)/1000} us")
以下是输出结果：
输出 >>
[
("Tina Woods", "[email protected]"),
("Toni Jackson", "[email protected]"),
("Lisa Miller", "[email protected]"),
("Katherine Guerrero", "[email protected]"),
("Michelle Lane", "[email protected]"),
("Jane Johnson", "[email protected]"),
("Matthew Odom", "[email protected]"),
("Isaac Daniel", "[email protected]"),
("Jay Byrd", "[email protected]"),
("Thomas Kirby", "[email protected]"),
]
使用索引的查询时间：167.179 us
我们可以看到，查询现在只需约167.179微秒。
性能改进
对于我们的示例查询，使用索引的查询速度大约快2.68倍。在执行时间上，我们获得了62.71%的速度提升。
您还可以尝试运行一些其他查询：涉及对职位列进行过滤的查询，并观察性能改进。
还请注意：由于我们只在职位列上创建了索引，如果运行涉及其他列的查询，查询速度不会比没有索引时更快。
总结和下一步
希望本指南能帮助您了解如何通过在经常查询的列上创建数据库索引来显著加快查询速度。这是关于数据库索引的介绍。您还可以创建多列索引、同一列的多个索引等等。
您可以在此GitHub存储库中找到本教程中使用的所有代码。祝您编码愉快！Bala Priya C是来自印度的开发者和技术作家。她喜欢在数学、编程、数据科学和内容创作的交叉领域工作。她感兴趣和擅长的领域包括DevOps、数据科学和自然语言处理。她喜欢阅读、写作、编码和咖啡！目前，她正在通过撰写教程、指南、观点文章等来学习和与开发者社区分享知识。
