创建索引可提升查询速度，但需权衡维护成本；应在频繁查询、连接、排序的高区分度列上创建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
的表，包含以下列：
-
id
(int, 主键)
-
name
(VARCHAR)
-
category_id
(INT)
-
price
(DECIMAL)
我们经常使用
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) = 2023
，索引在
order_date
列上可能不会生效。
- 使用了LIKE模糊查询： 如果LIKE查询以通配符开头，例如
WHERE name LIKE '%abc'
，索引通常不会生效。
- 数据类型不匹配： 如果查询中使用的数据类型与索引列的数据类型不匹配，索引可能会失效。例如，如果
id
列是INT类型，而查询中使用
WHERE id = '123'
，索引可能不会生效。
- 优化器认为全表扫描更快： 在某些情况下，数据库优化器可能会认为全表扫描比使用索引更快，因此会选择全表扫描。
了解索引失效的原因可以帮助你编写更高效的SQL查询。
为什么索引会降低写入性能？
每次插入、更新或删除数据时，数据库不仅需要修改表中的数据，还需要更新相关的索引。这意味着更多的磁盘I/O操作，从而降低了写入性能。对于频繁写入的表，过多的索引会显著降低性能。因此，在设计索引时，需要在查询性能和写入性能之间进行权衡。
