【解释】如何在SQL中创建数据库索引 索引是数据库中的一种数据结构，可以加快数据库的查询速度。当你在数据库中执行查询时，如果没有索引，数据库将按顺序扫描整个表来找到符合条件的数据。而如果有了索引，数据库可以更快地定位到所需的数据。 在SQL中，可以使用CREATE INDEX语句来创建索引。该语句的基本语法如下： CREATE INDEX index_name ON table_name (column1, column2, …); 其中，index_name是索引的名称，table_name是要创建索引的表名，column1、column2等是要创建索引的列名。 创建索引的过程比较简单，只需执行CREATE INDEX语句即可。然而，需要注意的是，在创建索引之前，你需要确保数据库中的表已经存在，并且该表中的数据量较大。否则，创建索引可能会导致性能下降。 此外，还有一些其他的注意事项需要考虑。例如，应该选择合适的列作为索引列，以及应该避免在不必要的列上创建索引等。 总的来说，创建索引是提高数据库查询性能的一种重要手段。通过合理地创建索引，你可以加快查询速度，提高数据库的响应性能。

想要加快数据库查询速度吗？学习如何使用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的，但你也可以使用其他你喜欢的rdbms，比如postgresql和mysql。

使用记录填充数据库表

你也可以使用python的内置random模块来创建和插入记录到数据库中。然而，我们将使用faker来填充数据库表，以生成一百万行数据。

以下python脚本：

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

您也可以在github上找到代码。。

# main.py
# 导入库
import sqlite3
from faker import faker
import random

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

# 创建表
db_cursor.execute('''create table customers (
                  id integer primary key,
                  first_name text,
                  last_name text,
                  city text,
                  num_orders integer)''')

# 创建faker对象
fake = faker()
faker.seed(27)

# 创建并插入100万条记录
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))

# 提交事务并关闭游标和连接
db_conn.commit()
db_cursor.close()
db_conn.close()

现在我们可以开始查询了。

在city列上创建索引

假设您想通过基于city列进行筛选来获取客户信息。您的select查询将如下所示：

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
        > 

因为我们在city列上创建了索引，所以涉及基于city列进行筛选的查询在where子句中的速度将更快。

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

查询	没有索引的时间	有索引的时间
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 (‘new wesley’, ‘new steven’, ‘new carmenmouth’);	0.247 s	0.003 s

我们可以看到，存在索引的检索时间比没有索引的快了几个数量级。

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

您应该始终检查性能增益是否大于创建数据库索引的开销。以下是一些需要牢记的最佳实践：

• 选择正确的列来创建索引。避免创建过多的索引，因为这会带来很大的开销。
• 每当更新了索引列时，对应的索引也应该被更新。因此，创建数据库索引（虽然加快了检索速度），但会显著减慢插入和更新操作的速度。因此，应该在频繁查询但很少更新的列上创建索引。

在什么情况下不应创建索引？

现在，您应该已经了解何时以及如何创建索引。但是，让我们也说一下什么时候不需要创建数据库索引：

• 当数据库表较小且不包含大量行时，全表扫描检索数据的开销不会很大。
• 不要在很少用于检索的列上创建索引。当在不经常查询的列上创建索引时，创建和维护索引的成本超过了性能的提升。

总结

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

• 当查询数据库以检索数据时，您可能需要根据某些列进行频繁过滤。在这些经常查询的列上创建数据库索引可以提高性能。
• 要在单个列上创建索引，使用语法：create index index_name on table (column)。如果要创建多列索引，请使用：create index index_name on table (column_1, column_2,...,column_k)
• 每当修改了索引列时，对应的索引也应该被更新。因此，选择正确的列-经常查询但更新较少-来创建索引。
• 如果数据库表相对较小，创建、维护和更新索引的成本将大于性能的提升。

在大多数现代数据库管理系统中，有一个查询优化器，它会检查在特定列上的索引是否可以使查询运行更快。接下来，让我们学习数据库设计的最佳实践。