推薦：使用 NSDT場(chǎng)景編輯器助你快速搭建可二次編輯器的3D應(yīng)用場(chǎng)景

假設(shè)您正在篩選一本書的頁面。而且您希望更快地找到所需的信息。你是怎么做到的？好吧，您可能會(huì)查找術(shù)語索引，然后跳轉(zhuǎn)到引用特定術(shù)語的頁面。SQL 中的索引的工作方式與書籍中的索引類似。

在大多數(shù)實(shí)際系統(tǒng)中，您將針對(duì)具有大量行（例如數(shù)百萬行）的數(shù)據(jù)庫表運(yùn)行查詢。需要全表掃描所有行以檢索結(jié)果的查詢將非常慢。如果您知道必須經(jīng)?；谀承┝胁樵冃畔ⅲ瑒t可以在這些列上創(chuàng)建數(shù)據(jù)庫索引。這將大大加快查詢速度。

那么我們今天會(huì)學(xué)到什么呢？我們將學(xué)習(xí)使用 sqlite3 模塊在 Python 中連接和查詢 SQLite 數(shù)據(jù)庫。我們還將學(xué)習(xí)如何添加索引，并了解它如何提高性能。

要按照本教程編寫代碼，您應(yīng)該在工作環(huán)境中安裝 Python 3.7+ 和 SQLite。

注意：本教程中的示例和示例輸出適用于 Ubuntu LTS 3.10 上的 Python 3.3 和 SQLite37（版本 2.22.04）。

在 Python 中連接到數(shù)據(jù)庫

我們將使用內(nèi)置的 sqlite3 模塊。在開始運(yùn)行查詢之前，我們需要：

• 連接到數(shù)據(jù)庫
• 創(chuàng)建數(shù)據(jù)庫游標(biāo)以運(yùn)行查詢

若要連接到數(shù)據(jù)庫，我們將使用

來自 sqlite3 模塊的 connect（） 函數(shù)。建立連接后，我們可以調(diào)用連接對(duì)象來創(chuàng)建數(shù)據(jù)庫游標(biāo)，如下所示：cursor()

import sqlite3

# connect to the db
db_conn = sqlite3.connect('people_db.db')
db_cursor = db_conn.cursor()

在這里，我們嘗試連接到數(shù)據(jù)庫

people_db.如果數(shù)據(jù)庫不存在，運(yùn)行上面的代碼片段將為我們創(chuàng)建 sqlite 數(shù)據(jù)庫。

創(chuàng)建表并插入記錄

現(xiàn)在，我們將在數(shù)據(jù)庫中創(chuàng)建一個(gè)表，并用記錄填充它。

讓我們?cè)跀?shù)據(jù)庫中創(chuàng)建一個(gè)名為?people?的表，其中包含以下字段：people_db

• 名字
• 電子郵件
• 工作

# main.py
...
# create table
db_cursor.execute('''CREATE TABLE people (
                  id INTEGER PRIMARY KEY,
                  name TEXT,
                  email TEXT,
                  job TEXT)''')


...

# commit the transaction and close the cursor and db connection
db_conn.commit()
db_cursor.close()
db_conn.close()

使用偽造器生成合成數(shù)據(jù)

我們現(xiàn)在必須在表中插入記錄。為此，我們將使用?Faker——一個(gè)用于合成數(shù)據(jù)生成的 Python 包——可通過?pip?安裝：

$ pip install faker

安裝faker后，可以將類導(dǎo)入到Python腳本中：Faker

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

下一步是生成記錄并將其插入人員表。為了讓我們知道索引如何加快查詢速度，讓我們插入大量記錄。在這里，我們將插入 100K 條記錄;將變量設(shè)置為 100000。num_records

然后，我們執(zhí)行以下操作：

• 實(shí)例化一個(gè)對(duì)象并設(shè)置種子，以便我們獲得可重現(xiàn)性。Fakerfake
• 使用名字和姓氏獲取名稱字符串 - 通過調(diào)用對(duì)象和對(duì)象。first_name()last_name()fake
• 通過調(diào)用生成假域。domain_name()
• 使用名字和姓氏以及域生成電子郵件字段。
• 使用 獲取每個(gè)單獨(dú)記錄的作業(yè)。job()

我們生成記錄并將其插入到表中：people

# create and insert records
fake = Faker() # be sure to import: 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))

# commit the transaction and close the cursor and db connection
db_conn.commit()
db_cursor.close()
db_conn.close()

現(xiàn)在，main.py 文件具有以下代碼：

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

# connect to the db
db_conn = sqlite3.connect('people_db.db')
db_cursor = db_conn.cursor()

# create table
db_cursor.execute('''CREATE TABLE people (
                  id INTEGER PRIMARY KEY,
                  name TEXT,
                  email TEXT,
                  job TEXT)''')


# create and insert records
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))

# commit the transaction and close the cursor and db connection
db_conn.commit()
db_cursor.close()
db_conn.close()

運(yùn)行此腳本一次，以使用記錄數(shù)填充表。num_records

查詢數(shù)據(jù)庫

現(xiàn)在我們有了包含 100K 條記錄的表，讓我們對(duì)表運(yùn)行一個(gè)示例查詢。people

讓我們運(yùn)行一個(gè)查詢來：

• 獲取職位名稱為“產(chǎn)品經(jīng)理”的記錄的名稱和電子郵件，以及
• 將查詢結(jié)果限制為 10 條記錄。

我們將使用 time 模塊中的默認(rèn)計(jì)時(shí)器來獲取查詢的大致執(zhí)行時(shí)間。

# 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

您還可以通過在命令行運(yùn)行來調(diào)用 SQLite 命令行客戶端：sqlite3 db_name

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

要獲取索引列表，您可以運(yùn)行：.index

sqlite> .index

由于當(dāng)前沒有索引，因此不會(huì)列出任何索引。

您還可以像這樣檢查查詢計(jì)劃：

sqlite> EXPLAIN QUERY PLAN SELECT name, email FROM people WHERE job='Product Manager' LIMIT 10;
QUERY PLAN
`--SCAN people

這里的查詢計(jì)劃是掃描所有效率低下的行。

在特定列上創(chuàng)建索引

若要在特定列上創(chuàng)建數(shù)據(jù)庫索引，可以使用以下語法：

CREATE INDEX index-name on table (column(s))

假設(shè)我們需要經(jīng)常查找具有特定職位的個(gè)人的記錄。在作業(yè)列上創(chuàng)建索引會(huì)有所幫助：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

盡管創(chuàng)建索引需要這么長時(shí)間，但這是一次性操作。運(yùn)行多個(gè)查詢時(shí)，您仍將獲得顯著的加速。

現(xiàn)在，如果您在 SQLite 命令行客戶端上運(yùn)行，您將獲得：.index

sqlite> .index
people_job_index

使用索引查詢數(shù)據(jù)庫

如果您現(xiàn)在查看查詢計(jì)劃，您應(yīng)該能夠看到我們現(xiàn)在使用作業(yè)列上的索引搜索表：peoplepeople_job_index

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=?)

您可以重新運(yùn)行sample_query.py。僅修改語句并查看查詢現(xiàn)在運(yùn)行需要多長時(shí)間：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

我們看到查詢現(xiàn)在大約需要 167.179 微秒來執(zhí)行。

性能改進(jìn)

對(duì)于我們的示例查詢，使用 index 進(jìn)行查詢的速度大約快 2.68 倍。我們?cè)趫?zhí)行時(shí)間中獲得了 62.71% 的百分比加速。

您還可以嘗試運(yùn)行更多查詢：涉及對(duì)作業(yè)列進(jìn)行篩選并查看性能改進(jìn)的查詢。

另請(qǐng)注意：由于我們僅在作業(yè)列上創(chuàng)建了索引，因此，如果您運(yùn)行的查詢涉及其他列，則查詢的運(yùn)行速度不會(huì)比沒有索引時(shí)快。

總結(jié)和后續(xù)步驟

我希望本指南能幫助您了解在頻繁查詢的列上創(chuàng)建數(shù)據(jù)庫索引如何顯著加快查詢速度。這是對(duì)數(shù)據(jù)庫索引的介紹。您還可以創(chuàng)建多列索引、同一列的多個(gè)索引等等。

原文鏈接：如何使用索引加速 SQL 查詢 [Python 版] (mvrlink.com)文章來源地址http://www.zghlxwxcb.cn/news/detail-649284.html

到了這里，關(guān)于如何使用索引加速 SQL 查詢 [Python 版]的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！