<返回更多

使用Python内置模块加速SQL查询

2023-08-22    
加入收藏

假设你正在查阅一本书的页面,你想要更快地找到你正在寻找的信息。你会怎么做呢?那么你可能会查找术语索引,然后跳转到引用特定术语的页面。SQL中的索引与书籍中的索引工作原理类似。

在大多数实际系统中,都将对包含大量行的数据库表运行查询(想象一下数百万行)。需要通过扫描所有行来检索结果的查询将非常慢。如果你知道经常需要根据某些列查询信息,可以在这些列上创建数据库索引。这将大大加快查询速度。

图片

那么今天跟随本文将学到什么呢?将学习如何使用sqlite3模块在Python/ target=_blank class=infotextkey>Python中连接和查询SQLite数据库。同时还将学习如何添加索引并看到它是如何提高性能的。

要按照本教程进行编码,确保工作环境中安装了Python 3.7+和SQLite。

注意:本教程中的示例和样本输出适用于Ubuntu LTS 22.04上的Python 3.10和SQLite3(版本3.37.2)。

在Python中连接到数据库

本文将使用内置的sqlite3模块。在开始运行查询之前,需要做到以下步骤:

【sqlite3】:https://docs.python.org/3/library/sqlite3.html

要连接到数据库,本文将使用sqlite3模块中的connect()函数。一旦建立了连接,就可以在连接对象上调用cursor()来创建一个数据库游标,如下所示:

import sqlite3

# 连接到数据库
db_conn = sqlite3.connect('people_db.db')
db_cursor = db_conn.cursor()

在这里,尝试连接到名为people_db的数据库。如果数据库不存在,运行上述代码片段将为我们创建SQLite数据库。

创建表格并插入记录

现在,本文将在数据库中创建一个表,并向其中添加记录。

people_db数据库中创建一个名为people的表,其中包含以下字段:

# 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安装:

【Faker】:https://faker.readthedocs.io/en/master/

$ pip install faker

安装Faker后,就可以将Faker类导入到Python脚本中:

# main.py
...
from faker import Faker
...

下一步是生成并插入people表中的记录。为了演示索引如何加快查询速度,本文将插入大量记录。在这里将插入10万条记录;将num_records变量设置为100000

然后执行以下操作:

使用如下代码生成并插入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表格上运行一个示例查询。

通过运行一个查询来:

本文将使用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"Query time without index: {(t2-t1)/1000} us")

以下是输出结果:

Output >>
[
    ("Tina Woods", "Tina.Woods@smith.com"),
    ("Toni Jackson", "Toni.Jackson@underwood.com"),
    ("Lisa Miller", "Lisa.Miller@solis-west.info"),
    ("Katherine Guerrero", "Katherine.Guerrero@schmidt-price.org"),
    ("Michelle Lane", "Michelle.Lane@carr-hardy.com"),
    ("Jane Johnson", "Jane.Johnson@graham.com"),
    ("Matthew Odom", "Matthew.Odom@willis.biz"),
    ("Isaac Daniel", "Isaac.Daniel@peck.com"),
    ("Jay Byrd", "Jay.Byrd@bailey.info"),
    ("Thomas Kirby", "Thomas.Kirby@west.com"),
]

Query time without index: 448.275 us

还可以通过在命令行中运行sqlite3 db_name来调用SQLite命令行客户端:

$ sqlite3 people_db.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.

要获取索引列表,可以运行.index

sqlite> .index

由于当前没有索引,因此不会列出任何索引。

还可以像这样检查查询计划:

sqlite> EXPLAIN QUERY PLAN SELECT name, email FROM people WHERE job='Product Manager' LIMIT 10;
QUERY PLAN
`--SCAN 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"Time to create index: {(t2 - t1)/1000} us")


Output >>
Time to create index: 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;
QUERY PLAN
`--SEARCH people USING INDEX people_job_index (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"Query time with index: {(t2-t1)/1000} us")

以下是输出结果:

Output >>
[
    ("Tina Woods", "Tina.Woods@smith.com"),
    ("Toni Jackson", "Toni.Jackson@underwood.com"),
    ("Lisa Miller", "Lisa.Miller@solis-west.info"),
    ("Katherine Guerrero", "Katherine.Guerrero@schmidt-price.org"),
    ("Michelle Lane", "Michelle.Lane@carr-hardy.com"),
    ("Jane Johnson", "Jane.Johnson@graham.com"),
    ("Matthew Odom", "Matthew.Odom@willis.biz"),
    ("Isaac Daniel", "Isaac.Daniel@peck.com"),
    ("Jay Byrd", "Jay.Byrd@bailey.info"),
    ("Thomas Kirby", "Thomas.Kirby@west.com"),
]

Query time with index: 167.179 us

可以看到查询现在大约需要167.179微秒来执行。

性能改进

对于本文的示例查询,使用索引的查询速度大约快2.68倍。在执行时间方面获得了62.71%的速度提升。

还可以尝试运行更多的查询:涉及筛选job列的查询,并查看性能的改进情况。

另请注意:由于只在job列上创建了索引,因此如果运行涉及其他列的查询,查询的运行速度不会比没有索引时更快。

结语

希望本指南帮助你了解如何通过创建数据库索引(在频繁查询的列上)显著加快查询速度。这是数据库索引的介绍。你还可以创建多列索引、同一列的多个索引等等。

可以在此Github存储库中找到本教程中使用的所有代码。

【GitHub】:https://github.com/balapriyac/sql-index-intro

关键词:Python      点击(4)
声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多Python相关>>>