【解释】如何在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) - 每当修改了索引列时，对应的索引也应该被更新。因此，选择正确的列-经常查询但更新较少-来创建索引。
- 如果数据库表相对较小，创建、维护和更新索引的成本将大于性能的提升。
在大多数现代数据库管理系统中，有一个查询优化器，它会检查在特定列上的索引是否可以使查询运行更快。接下来，让我们学习数据库设计的最佳实践。
