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

一、索引的基本概念

索引（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 最左前缀原则

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

1
2
3
4
5
6
例如，创建了索引 `(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 索引列顺序的选择

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

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

五、索引的优化技巧

5.1 覆盖索引

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

1
2
3
4
5
6
-- 创建覆盖索引
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 避免索引失效

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

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

六、索引的维护

6.1 查看索引信息

– 查看表的索引

1
SHOW INDEX FROM table_name;

– 查看索引使用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
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 监控索引使用情况

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

– 查看索引使用统计（需要开启performance_schema）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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 查询优化示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
优化前：
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);

-- 查询可以直接使用索引，无需排序

八、常见问题与解答

8.1 Q1: 索引越多越好吗

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

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

A: 可能的原因包括

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

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

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

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

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

九、总结

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

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

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

本文标题： 创建索引这些知识

发布时间： 2022年01月05日 00:00

最后更新： 2026年09月16日 05:40

原始链接： https://haoxiang.eu.org/e28ad751/

版权声明： 本文著作权归作者所有，均采用CC BY-NC-SA 4.0许可协议，转载请注明出处！

赏
× 喜欢就赞赏一下呗！
打赏二维码