sql怎样用CREATEINDEX给字段创建索引 sql创建索引的简单教程

日期：2025-08-20 00:00 / 作者：爱谁谁

创建索引可提升查询速度，但需权衡维护成本；应在频繁查询、连接、排序的高区分度列上创建B-tree等合适类型的索引，并避免过度索引。

sql使用CREATE INDEX语句为字段创建索引，这可以显著提高查询速度，特别是对于大型表。创建索引需要仔细考虑，错误的索引反而会降低性能。

创建索引的基本语法：

CREATE INDEX index_name
ON table_name (column1, column2, ...);

index_name

是你为索引选择的名字，

table_name

是你想要在其上创建索引的表名，括号内则是你想要索引的列名。你可以选择单个列，也可以选择多个列组成复合索引。

索引并非越多越好。每次数据更新（插入、删除、更新），索引也需要更新，这会增加数据库的负担。因此，需要权衡索引带来的查询速度提升和维护成本。

以下情况适合创建索引：

频繁用于WHERE子句的列： 这是最常见的场景。例如，
```
WHERE product_id = 123
```
，如果
```
product_id
```
列经常出现在WHERE子句中，那么为其创建索引可以大幅提高查询效率。
用于连接（JOIN）的列： 如果你经常使用某个列进行表连接，例如
```
ON orders.customer_id = customers.id
```
，那么在
```
customer_id
```
列上创建索引可以加速连接操作。
用于排序（ORDER BY）或分组（GROUP BY）的列： 对排序和分组的列创建索引可以避免全表扫描，提高排序和分组的效率。
唯一性约束的列： 虽然唯一性约束会自动创建索引，但显式创建索引可以更好地控制索引的属性。

不同的数据库系统支持不同的索引类型，常见的包括：

B-tree索引： 这是最常用的索引类型，适用于范围查询和精确匹配查询。大多数数据库默认使用B-tree索引。
哈希索引： 哈希索引只适用于精确匹配查询，不支持范围查询。它的优点是查找速度非常快，但缺点是不支持排序和范围查询。MySQL的Memory存储引擎支持哈希索引。
全文索引： 全文索引用于在文本中搜索关键词。它适用于大型文本字段，例如文章内容、评论等。
空间索引： 空间索引用于存储和查询地理空间数据。

选择哪种索引类型取决于你的具体需求和数据库系统的支持。

选择合适的索引列非常重要。以下是一些建议：

选择区分度高的列： 区分度是指列中不同值的数量。区分度越高，索引的效果越好。例如，性别列的区分度很低，不适合创建索引。而用户ID列的区分度很高，适合创建索引。
考虑列的数据类型： 较小的数据类型通常比大的数据类型更适合创建索引，因为它们占用更少的存储空间，并且比较速度更快。
注意复合索引的顺序： 对于复合索引，列的顺序非常重要。应该将区分度最高的列放在最前面。例如，如果经常使用
```
WHERE city = 'beijing' AND age > 20
```
进行查询，那么应该创建
```
INDEX(city, age)
```
，而不是
```
INDEX(age, city)
```
。
避免过度索引： 索引会增加数据库的维护成本，因此应该避免创建过多的索引。只对真正需要索引的列创建索引。

假设我们有一个名为

products

的表，包含以下列：

我们经常使用

category_id

和

price

进行查询，例如：

SELECT * FROM products WHERE category_id = 1 AND price < 100;

为了提高查询效率，我们可以创建一个复合索引：

CREATE INDEX idx_category_price ON products (category_id, price);

这将在

category_id

和

price

列上创建一个名为

idx_category_price

的复合索引。

大多数数据库系统都提供了工具来查看查询是否使用了索引。例如，在MySQL中，可以使用

EXPLAIN

语句来分析查询计划。

EXPLAIN SELECT * FROM products WHERE category_id = 1 AND price < 100;

EXPLAIN

语句会显示查询的执行计划，包括是否使用了索引、使用了哪个索引等信息。通过分析查询计划，可以判断索引是否有效，并进行优化。

索引需要定期维护，以确保其性能。以下是一些建议：

索引并非总是有效。在某些情况下，即使创建了索引，数据库也可能不会使用它。以下是一些常见的索引失效原因：

使用了函数或表达式： 如果在WHERE子句中使用了函数或表达式，索引可能会失效。例如，
```
WHERE YEAR(order_date) = 2025
```
，索引在
```
order_date
```
列上可能不会生效。
使用了LIKE模糊查询： 如果LIKE查询以通配符开头，例如
```
WHERE name LIKE '%abc'
```
，索引通常不会生效。
数据类型不匹配： 如果查询中使用的数据类型与索引列的数据类型不匹配，索引可能会失效。例如，如果
```
id
```
列是INT类型，而查询中使用
```
WHERE id = '123'
```
，索引可能不会生效。
优化器认为全表扫描更快： 在某些情况下，数据库优化器可能会认为全表扫描比使用索引更快，因此会选择全表扫描。

了解索引失效的原因可以帮助你编写更高效的SQL查询。

每次插入、更新或删除数据时，数据库不仅需要修改表中的数据，还需要更新相关的索引。这意味着更多的磁盘I/O操作，从而降低了写入性能。对于频繁写入的表，过多的索引会显著降低性能。因此，在设计索引时，需要在查询性能和写入性能之间进行权衡。