创建索引，这些知识应该了解

索引是数据库中用于提高查询性能的重要数据结构。正确创建和使用索引可以大幅提升数据库查询速度，但不当的索引设计也可能带来性能问题。本文将从索引的基本概念、类型、创建原则、优化技巧等方面进行详细介绍。

一、索引的基本概念

索引（Index）是数据库中一种特殊的数据结构，它类似于书籍的目录，可以帮助数据库快速定位到数据表中的特定记录，而不需要逐行扫描整个表。

1.1 索引的作用

提高查询速度：通过索引可以快速定位到需要的数据，避免全表扫描
加速排序：对索引列进行排序操作时，可以利用索引的有序性
保证唯一性：唯一索引可以确保数据的唯一性约束
加速表连接：在JOIN操作中，索引可以显著提升连接性能

1.2 索引的代价

虽然索引能带来性能提升，但也会产生一定的代价：

存储空间：索引需要额外的存储空间
维护成本：当数据发生INSERT、UPDATE、DELETE操作时，索引也需要相应更新
创建时间：在大表上创建索引需要一定的时间

二、索引的类型

2.1 按数据结构分类

B-Tree索引

最常见的索引类型，适用于大多数场景
支持范围查询和排序操作
MySQL的InnoDB和MyISAM存储引擎默认使用B-Tree索引

Hash索引

基于哈希表实现，查询速度极快
只支持等值查询，不支持范围查询
MySQL的Memory存储引擎支持Hash索引

全文索引

用于文本搜索，支持全文检索
MySQL的MyISAM和InnoDB（5.6+）支持全文索引

空间索引

用于地理空间数据查询
MySQL的MyISAM存储引擎支持空间索引

2.2 按功能分类

普通索引（INDEX）

最基本的索引类型，没有任何限制
允许在索引列中插入重复值和空值

唯一索引（UNIQUE）

索引列的值必须唯一，但允许有空值
一个表可以有多个唯一索引

主键索引（PRIMARY KEY）

特殊的唯一索引，不允许有空值
一个表只能有一个主键索引

复合索引（联合索引）

由多个列组成的索引
遵循最左前缀原则

三、索引的创建原则

3.1 适合创建索引的列

WHERE子句中的列：经常出现在WHERE条件中的列应该创建索引
JOIN连接的列：用于表连接的列应该创建索引
ORDER BY子句中的列：经常用于排序的列应该创建索引
GROUP BY子句中的列：用于分组的列应该创建索引
高选择性列：列中不同值的数量较多（高基数）的列适合创建索引

3.2 不适合创建索引的列

低选择性列：列中不同值的数量很少（低基数）的列，如性别、状态等
频繁更新的列：经常发生UPDATE操作的列，索引维护成本高
很少用于查询的列：几乎不参与查询条件的列
大文本列：TEXT、BLOB等大文本类型不适合创建普通索引

3.3 索引命名规范

使用有意义的名称，如：idx_user_name、idx_order_date
统一命名风格，便于管理和维护
避免使用关键字和特殊字符

四、复合索引的使用

4.1 最左前缀原则

复合索引遵循最左前缀原则，即查询条件必须包含索引的最左边的列，才能使用该索引。

例如，创建了索引 (name, age, city)：

✅ WHERE name = 'John' - 可以使用索引
✅ WHERE name = 'John' AND age = 25 - 可以使用索引
✅ WHERE name = 'John' AND age = 25 AND city = 'Beijing' - 可以使用索引
❌ WHERE age = 25 - 不能使用索引（缺少最左边的列）
❌ WHERE city = 'Beijing' - 不能使用索引（缺少最左边的列）

4.2 索引列顺序的选择

在创建复合索引时，列的顺序很重要：

选择性高的列放在前面：选择性高的列能更快地缩小查询范围
经常用于WHERE条件的列放在前面：提高查询效率
用于排序的列放在最后：如果查询需要排序，将排序列放在索引末尾

五、索引的优化技巧

5.1 覆盖索引

覆盖索引是指索引包含了查询所需的所有列，查询可以直接从索引中获取数据，而不需要回表查询。

-- 创建覆盖索引
CREATE INDEX idx_user_info ON users(name, age, email);

-- 查询可以直接使用索引，无需回表
SELECT name, age, email FROM users WHERE name = 'John';

5.2 索引下推（Index Condition Pushdown）

索引下推是MySQL 5.6引入的优化技术，可以在索引遍历过程中就对索引中包含的字段进行判断，过滤掉不满足条件的记录，减少回表次数。

5.3 避免索引失效

以下情况会导致索引失效：

使用函数：WHERE UPPER(name) = 'JOHN' - 索引失效
类型转换：WHERE id = '123' - 如果id是数字类型，索引可能失效
前导模糊查询：WHERE name LIKE '%John' - 索引失效
OR条件：WHERE name = 'John' OR age = 25 - 如果age没有索引，整个查询可能无法使用索引
**NOT、!=、<>**：这些操作符可能导致索引失效

六、索引的维护

6.1 查看索引信息

-- 查看表的索引
SHOW INDEX FROM table_name;

-- 查看索引使用情况
EXPLAIN SELECT * FROM table_name WHERE column = 'value';

6.2 重建索引

当索引碎片过多或数据发生大量变更时，可以重建索引：

-- MySQL
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name ADD INDEX index_name (column_name);

-- 或者使用OPTIMIZE TABLE
OPTIMIZE TABLE table_name;

6.3 监控索引使用情况

定期检查索引的使用情况，删除未使用的索引：

1
2
3

-- 查看索引使用统计（需要开启performance_schema）
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'database_name' AND object_name = 'table_name';

七、实际案例分析

7.1 电商订单表索引设计

假设有一个订单表 orders，包含以下字段：

order_id (主键)
user_id (用户ID)
order_date (订单日期)
status (订单状态)
total_amount (订单金额)

推荐的索引设计：

-- 主键索引（自动创建）
PRIMARY KEY (order_id)

-- 用户查询自己的订单
CREATE INDEX idx_user_date ON orders(user_id, order_date);

-- 按状态查询订单
CREATE INDEX idx_status_date ON orders(status, order_date);

-- 按金额范围查询
CREATE INDEX idx_amount ON orders(total_amount);

7.2 查询优化示例

优化前：

SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY order_date DESC 
LIMIT 10;

如果没有合适的索引，这个查询可能需要进行全表扫描和文件排序。

优化后：

-- 创建复合索引
CREATE INDEX idx_user_date ON orders(user_id, order_date DESC);

-- 查询可以直接使用索引，无需排序
SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY order_date DESC 
LIMIT 10;

八、常见问题与解答

Q1: 索引越多越好吗？

A: 不是。索引虽然能提高查询速度，但也会增加存储空间和维护成本。过多的索引会影响INSERT、UPDATE、DELETE操作的性能。应该根据实际查询需求创建必要的索引。

Q2: 为什么有时候有索引但查询还是很慢？

A: 可能的原因包括：

索引没有正确使用（如违反了最左前缀原则）
索引选择性太低
查询返回的数据量太大
索引碎片过多，需要重建

Q3: 如何判断索引是否有效？

A: 使用 EXPLAIN 命令查看查询执行计划，检查是否使用了索引。关注 key 字段，如果显示索引名称，说明使用了索引。

Q4: 复合索引的列顺序可以调整吗？

A: 可以，但需要根据实际查询模式来调整。不同的列顺序会影响索引的使用效果。应该将最常用、选择性最高的列放在前面。

九、总结

索引是数据库性能优化的重要手段，但需要合理使用：

理解索引原理：了解索引的数据结构和工作原理
分析查询模式：根据实际查询需求创建索引
遵循创建原则：在合适的列上创建合适的索引
定期维护优化：监控索引使用情况，及时调整和优化
避免过度索引：不要创建过多不必要的索引

正确使用索引可以大幅提升数据库查询性能，但需要在实际应用中不断优化和调整，找到最适合自己业务场景的索引策略。

本文标题：创建索引，这些知识应该了解

本文作者： OSChina

发布时间： 2021年04月15日 09:19

最后更新： 2025年12月29日 11:53

原始链接： https://haoxiang.eu.org/%E5%88%9B%E5%BB%BA%E7%B4%A2%E5%BC%95%E8%BF%99%E4%BA%9B%E7%9F%A5%E8%AF%86%E5%BA%94%E8%AF%A5%E4%BA%86%E8%A7%A3/

赏

狂欢马克思

专注硬件开发