[Explained] 如何在 SQL 中创建数据库索引

作者
Tweet
Share
Share
Pin
0 Shares

想提升数据库查询效率吗？本文将介绍如何利用SQL创建数据库索引，优化查询性能，加速数据检索。

在从数据库表中提取数据时，你常常需要根据特定列进行筛选。

假设你需要编写SQL查询来检索符合特定条件的数据。默认情况下，查询会执行全表扫描，直到找到所有符合条件的记录，然后返回结果。

当查询包含数百万行的大型数据库表时，这种做法效率低下。 通过创建数据库索引，可以显著提升此类查询的速度。

什么是数据库索引？

就像在书中查找特定术语，你会直接翻到索引页，而不是逐页扫描整本书，数据库索引也类似。

数据库索引是一组指向实际数据的指针或引用，这些指针按照优化的方式排列，以便快速检索数据。 内部实现上，索引可能会使用B+树或哈希表等数据结构。 因此，数据库索引显著提高了数据检索的速度和效率。

在SQL中创建数据库索引

现在我们了解了数据库索引及其加速数据检索的原理，接下来学习如何在SQL中创建数据库索引。

在进行数据筛选操作时（使用WHERE子句指定检索条件），某些列会被更频繁地查询。

CREATE INDEX index_name ON table (column)

其中：

  • index_name 是你创建的索引的名称。
  • table 指的是关系数据库中的表。
  • column 是指你希望创建索引的表列的名称。

你还可以根据需要在多个列上创建索引（多列索引）。语法如下：

CREATE INDEX index_name ON table (column_1, column_2,...,column_k)

下面我们来看一个实际示例。

理解数据库索引的性能提升

为了演示创建索引的优势，我们需要先创建一个包含大量记录的数据库表。以下示例使用 SQLite， 你也可以选择其他关系数据库，如 PostgreSQL 或 MySQL。

用记录填充数据库表

你可以使用Python内置的random模块生成随机数据并插入数据库。 但是，这里我们使用 Faker 库来填充一百万行数据。

下面的Python脚本：

  • 创建并连接到 customer_db 数据库。
  • 创建一个名为 customers 的表，包含 first_name, last_name, city 和 num_orders 字段。
  • 生成模拟数据，并将一百万条记录插入到 customers 表中。

你可以在 GitHub 上找到完整代码。

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

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

# create table
db_cursor.execute('''CREATE TABLE customers (
                  id INTEGER PRIMARY KEY,
                  first_name TEXT,
                  last_name TEXT,
                  city TEXT,
                  num_orders INTEGER)''')

# create a Faker object
fake = Faker()
Faker.seed(27)

# create and insert 1 million records
num_records = 1_000_000

for _ in range(num_records):
    first_name = fake.first_name()
    last_name = fake.last_name()
    city = fake.city()
    num_orders = random.randint(0,100)
    db_cursor.execute('INSERT INTO customers (first_name, last_name, city, num_orders) VALUES (?,?,?,?)', (first_name, last_name, city, num_orders))

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

现在我们准备开始查询了。

在city列上创建索引

假设你需要根据城市筛选客户信息。 你的SQL查询会是类似这样：

SELECT column(s) FROM customers
WHERE condition;

因此，我们在customers表的city列上创建名为 city_idx 的索引：

CREATE INDEX city_idx ON customers (city);

⚠️ 创建索引需要花费一定时间，而且是一次性操作。 然而，当你需要频繁查询（基于city列筛选）时，性能优势会非常显著。

删除数据库索引

要删除索引，可以使用 DROP INDEX 语句，如下所示：

DROP INDEX index_name;

比较有索引和没有索引的查询时间

如果想在Python脚本中运行查询，可以使用内置计时器来获取查询的执行时间。

或者，也可以使用sqlite3命令行客户端。 要使用命令行客户端访问 customer_db.db，请在终端运行以下命令：

$ sqlite3 customer_db.db;

要获得大致的执行时间，可以使用 sqlite3 的内置 .timer 功能，如下所示：

sqlite3 > .timer on
        > <query here>

由于我们在city列上创建了索引，在WHERE子句中涉及根据city列进行筛选的查询会快得多。

首先，运行查询。然后，创建索引，并重新运行相同的查询。 比较两种情况下的执行时间。以下是一些示例：

查询 无索引耗时 有索引耗时
SELECT * FROM customers WHERE city LIKE ‘New%’ LIMIT 10; 0.100 s 0.001 s
SELECT * FROM customers WHERE city=’New Wesley’; 0.148 s 0.001 s
SELECT * FROM customers WHERE city IN (‘新韦斯利’, ‘新史蒂文’, ‘新卡门茅斯’); 0.247 s 0.003 s

我们可以看到，在city列上创建索引后，检索速度显著提升，快了几个数量级。

创建和使用数据库索引的最佳实践

你应该始终评估性能的提升是否大于创建索引的开销。 以下是一些需要考虑的最佳实践：

  • 选择适合创建索引的列。避免创建过多索引，这会带来额外的维护开销。
  • 每次更新索引列时，对应的索引也必须更新。因此，创建数据库索引虽然能提高检索速度，但会降低插入和更新操作的速度。 理想情况下，应该在查询频率高但更新频率低的列上创建索引。

什么时候不应该创建索引？

现在你已经了解何时以及如何创建索引，我们还要说明什么情况下可能不需要索引：

  • 当数据库表较小，没有大量行时，全表扫描的成本并不高昂。
  • 不要在很少用于检索的列上创建索引。在不常查询的列上创建索引，其成本会高于性能提升。

总结

我们来回顾一下学习到的内容：

  • 当查询数据库以检索数据时，你可能需要更频繁地根据特定列进行过滤。 在这些频繁查询的列上创建数据库索引，可以提高查询性能。
  • 要在单个列上创建索引，请使用语法: CREATE INDEX index_name ON table (column)。要创建多列索引，请使用： CREATE INDEX index_name ON table (column_1,column_2,…,column_k)
  • 每当修改索引列时，对应的索引也应该更新。 因此，选择正确的列（查询频率高，更新频率低的列）创建索引至关重要。
  • 如果数据库表相对较小，创建、维护和更新索引的成本可能会大于性能收益。

在大多数现代数据库管理系统中，查询优化器会检查特定列上的索引是否能提升查询速度。 接下来，我们将学习数据库设计的最佳实践。