致力于分享mysql相关知识,收集mysql相关工具!

mysql菜鸟教程

专栏导航

13.5 索引使用原则：什么时候建索引？

      索引是提升数据库查询性能的利器，但并非越多越好。每个索引都会占用额外的存储空间，并增加数据写入（INSERT、UPDATE、DELETE）时的维护开销。因此，合理地创建索引需要在查询性能和写入性能之间找到平衡。本节将总结一套实用的索引使用原则，帮助你判断何时应该创建索引，何时应该避免。

一、适合创建索引的场景

以下情况，考虑为列创建索引：

1. 频繁作为查询条件的列

如果一个列经常出现在 WHERE 子句中，为其创建索引可以大幅加速数据筛选。

-- 经常按 user_id 查询订单
CREATE INDEX idx_orders_user_id ON orders(user_id);

2. 经常用于连接其他表的列

在 JOIN 操作中，连接条件中的列（通常是外键）如果有索引，可以显著提高连接效率。

-- orders 表的 user_id 经常与 users 表的 id 连接
CREATE INDEX idx_orders_user_id ON orders(user_id);

3. 经常需要排序的列

如果经常按某列排序（ORDER BY），为该列创建索引可以避免文件排序（filesort），直接利用索引的有序性返回结果。

-- 经常按创建时间倒序查看订单
CREATE INDEX idx_orders_created_at ON orders(created_at);

4. 经常需要分组的列

GROUP BY 本质上也需要排序，所以与排序类似，对分组列创建索引可以提升性能。

-- 经常按状态分组统计
CREATE INDEX idx_orders_status ON orders(status);

5. 具有高选择性的列

选择性 = 不同值的数量 / 总行数。选择性越高（接近 1），索引过滤掉的数据越多，效果越好。例如，身份证号的选择性很高，而性别字段的选择性很低（只有“男”“女”）。

-- 身份证号选择性极高，适合建索引
CREATE UNIQUE INDEX idx_users_id_card ON users(id_card);

-- 性别选择性极低，一般不建议建索引
-- CREATE INDEX idx_users_gender ON users(gender);  -- 不推荐

6. 需要保证唯一性的列

唯一索引不仅能加速查询，还能强制列值的唯一性，常用于用户名、邮箱等字段。

CREATE UNIQUE INDEX idx_users_email ON users(email);

二、不适合创建索引的场景

以下情况，创建索引可能得不偿失：

1. 数据量很小的表

如果表只有几十行甚至几行，全表扫描可能比使用索引更快（因为索引有额外的访问开销）。此时无需创建索引。

2. 频繁更新的列

如果某个列经常被更新，每次更新都需要同步维护索引，会带来额外的 I/O 和 CPU 开销。对于这种列，应权衡查询需求与更新成本。

3. 选择性很低的列

如性别、状态等只有少数几个值的列。即使创建了索引，查询时可能仍需扫描大量行（因为每个值对应很多行），优化器甚至可能放弃使用索引而选择全表扫描。

4. 很少在查询中使用的列

索引的价值在于被查询使用。如果一个列很少出现在 WHERE、JOIN、ORDER BY 中，为其创建索引就是浪费资源。

5. 很长的字符串列

对于很长的字符串（如 VARCHAR(255) 或 TEXT），创建完整列的索引会占用大量空间，且比较成本高。此时可以考虑前缀索引，只索引字符串的前几个字符。

-- 对文章标题的前 20 个字符建立索引
CREATE INDEX idx_articles_title ON articles(title(20));

三、复合索引的使用原则

复合索引（多列索引）更加强大，但需要遵循最左前缀原则：

  • 索引 (a, b, c) 可以用于查询条件：

    • a = ?

    • a = ? AND b = ?

    • a = ? AND b = ? AND c = ?

    • a = ? ORDER BY b（排序利用索引）

    • a = ? GROUP BY b 等

  • 不能直接用于：

    • b = ?

    • c = ?

    • b = ? AND c = ?

  • 可以用于部分情况，如 a = ? AND c = ? 会使用索引的 a 部分过滤，但无法利用 c（需要额外过滤）。

设计建议：

  • 将最常用、选择性最高的列放在最左边。

  • 考虑查询模式，尽量让一个复合索引覆盖多个常用查询，减少索引数量。

  • 避免创建功能重叠的冗余索引。例如已有 (a, b) 索引，再创建 (a) 索引通常是冗余的（除非 (a) 索引有其他用途，如长度更小）。

四、索引列的排序方向

创建索引时可以指定列的排序方向（ASC 或 DESC）。如果查询中的 ORDER BY 方向与索引一致，可以避免额外的排序操作。在 MySQL 8.0 中，支持降序索引，这为多列混合排序提供了优化可能。

-- 创建支持 (a 升序, b 降序) 的索引
CREATE INDEX idx_a_asc_b_desc ON t (a ASC, b DESC);

如果查询需要 ORDER BY a ASC, b DESC，这个索引可以直接返回有序结果。

五、如何评估索引是否有效：EXPLAIN

使用 EXPLAIN 关键字可以查看查询的执行计划，判断是否使用了索引。

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

关注输出中的 type（访问类型，如 ref、range 表示使用了索引）、possible_keys（可能使用的索引）、key（实际使用的索引）和 rows（扫描的行数）。如果 key 为 NULL 且 rows 很大，说明没有使用索引，可能需要考虑创建或调整索引。

六、案例分析

假设我们有一个订单表 orders，包含列：order_id (主键), user_id, status, created_at, total_amount。

常见查询：

  • 查询某个用户的所有订单：WHERE user_id = ?

  • 查询某个时间段内的订单：WHERE created_at BETWEEN ? AND ?

  • 统计各种状态的数量：GROUP BY status

  • 查询某个用户某段时间的订单：WHERE user_id = ? AND created_at BETWEEN ? AND ?

索引建议：

  1. 为 user_id 创建索引（普通索引）。

  2. 为 created_at 创建索引（范围查询）。

  3. 为 status 创建索引？选择性低（通常只有几种状态），如果数据量大且状态分布均匀，可能有用；否则可考虑不建。

  4. 对于组合查询 user_id + created_at，可以创建复合索引 (user_id, created_at)，这样能同时覆盖单列查询和组合查询。

避免：

  • 如果已有复合索引 (user_id, created_at)，通常不再需要单独的 (user_id) 索引（复合索引左前缀可覆盖），除非 user_id 查询特别频繁且希望索引更紧凑。

七、索引使用原则总结

查询驱动：只为常用的查询条件、连接、排序、分组列创建索引。

高选择性优先：选择性高的列更值得建索引。

避免过多索引：每个额外索引都会增加写开销。通常单表索引数控制在 5 个以内。

复合索引左前缀：复合索引按最左原则设计，尽量覆盖多个查询。

考虑列顺序：将最常用、区分度高的列放在复合索引左侧。

冗余索引检查：将最常用、区分度高的列放在复合索引左侧。

小表示需索引：数据量小的表不必索引。

长字符串用前缀：对长文本使用前缀索引。

定期评估：随着业务变化，使用 EXPLAIN 和慢查询日志持续优化索引。

    索引设计是性能调优的核心之一，没有放之四海而皆准的规则。理解业务查询模式，结合实际数据特点，才能做出合理的索引决策。


所有评论

↑
关于我 备案号:蜀ICP备2023042032号-1