假設你正在查閱一本書的頁面，你想要更快地找到你正在尋找的信息。你會怎么做呢？那么你可能會查找術語索引，然后跳轉到引用特定術語的頁面。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的表，其中包含以下字段：
-
name
-
emAIl
-
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安裝：
【Faker】：https://faker.readthedocs.io/en/master/
$ pip install faker
安裝Faker后，就可以將Faker類導入到Python腳本中：
# main.py
...
from faker import Faker
...
下一步是生成并插入people表中的記錄。為了演示索引如何加快查詢速度，本文將插入大量記錄。在這里將插入10萬條記錄；將num_records變量設置為100000。
然后執行以下操作：
-
實例化一個
Faker對象fake并設置種子以獲得可復現性。 -
使用
first_name()和last_name()在fake對象上調用，獲取一個名字字符串。 -
通過調用
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"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
