MySQL索引优化是提升SQL查询效率的核心方法，使用得当能让慢查询性能显著提升，反之则可能影响数据库整体性能。本文将分享5个在开发中高频出现且非常实用的索引优化技巧，帮助你避开常见陷阱。
01 前缀索引：应对大字符串字段
当需要为长度较大的字符串字段（例如 varchar(255) 类型的用户名、备注等）建立索引时，直接创建完整字段的索引会占用大量存储空间，并可能拖慢查询速度。此时，前缀索引（Prefix Index）是一个高效的解决方案。
前缀索引的核心作用在于减小索引键值的大小，使得单个索引页能够存储更多的索引条目，从而提升索引的查询效率。
那么，如何确定合适的前缀长度呢？可以通过计算索引的“选择性”（Selectivity）来决定。执行以下SQL，当比值越接近1时，说明该前缀长度的区分度越高，通常是最合适的长度：
SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;
需要注意的是，前缀索引存在一些局限性：
ORDER BY 排序失效： 使用前缀索引的字段无法用于 ORDER BY 排序操作。- 无法作为覆盖索引： 由于索引只包含了字段的前缀部分，当查询需要完整字段值时，无法避免回表操作。
02 覆盖索引：避免回表的关键
许多开发者都曾遇到过因“回表”操作导致的查询性能下降问题，而覆盖索引正是解决这一问题的关键。
简单来说，回表是指通过二级索引查找到主键值后，还需要根据这些主键值回到聚簇索引（即主键索引）中去查找完整的行记录。而覆盖索引则意味着，查询语句所请求的所有字段（包括 SELECT 子句和 WHERE 子句中的字段）都包含在同一个索引的键值中。这样，数据库引擎在索引的B+Tree叶子节点上就能获取全部所需数据，无需再次访问聚簇索引，从而彻底避免了回表带来的额外I/O开销。
例如，对于高频查询 SELECT username, age FROM users WHERE username = ‘xxx’，可以创建一个 (username, age) 的联合索引。由于该索引已经包含了查询所需的所有字段，数据库无需回表，查询性能将得到显著提升。这正是在数据库查询优化中非常核心的一环。
03 主键索引：自增设计提升效率
在InnoDB存储引擎中，主键索引（聚簇索引）的叶子节点直接存储了完整的行数据，因此主键的设计会直接影响数据的插入和查询效率。
使用自增主键时，新插入的数据的主键值总是递增的，数据库只需要将数据顺序追加到当前索引页的末尾。当页面写满时，会自动开辟新的页面。这种方式插入效率高，且能有效减少页分裂和内存碎片。
而如果使用非自增主键（例如UUID或随机字符串），由于主键值随机，新数据可能被插入到现有数据页的中间位置。这会导致频繁的数据移动和页分裂操作，不仅降低插入速度，还会产生存储碎片，进而拖慢后续的查询性能。
此外，主键字段应尽可能选择较短的。因为InnoDB的二级索引的叶子节点存储的正是主键值，主键越短，二级索引占用的存储空间就越小，查询效率也越高。
04 规避常见的索引失效场景
有时明明建立了索引，查询速度却依然很慢，这很可能是因为索引失效了。牢记以下几个高频失效场景，可以避免许多性能问题：
- 模糊匹配：
LIKE ‘%xxx’（左模糊）和 LIKE ‘%xxx%’（左右模糊）会导致索引失效，而 LIKE ‘xxx%’（右模糊）可以正常使用索引。
- 索引列操作： 对索引列进行计算、使用函数、或发生隐式的类型转换（例如将
varchar 列与 int 值比较），都会导致索引失效。
- 联合索引的最左匹配原则： 对于联合索引
(a, b, c)，查询条件必须从最左边的列 a 开始，才能有效利用索引。查询 b 或 c 而跳过 a 将无法使用该索引。
- OR 条件： 如果
OR 连接的条件中，并非所有字段都建立了索引，则整个查询可能无法使用索引。例如 WHERE a = 1 OR b = 2，若只有 a 有索引而 b 没有，优化器可能会选择全表扫描。
- IN 参数过多： 虽然
IN 操作符通常可以使用索引，但当 IN 列表中的值数量非常庞大时，MySQL优化器经过成本计算后，可能会认为全表扫描比使用索引逐个查找更高效，从而放弃使用索引。
- NOT 系操作符：
!=、<>、NOT IN、NOT LIKE 等操作通常无法有效利用B+树索引进行范围查找，优化器往往会选择全表扫描。
- ORDER BY 顺序与索引不一致： 例如，索引为
(a, b)，但查询的排序条件是 ORDER BY b, a（顺序相反）或 ORDER BY a ASC, b DESC（排序方向不一致），索引将无法被完全用于排序优化。
- 字段字符集不一致： 在表关联查询时，如果连接字段的字符集或排序规则（Collation）不同，MySQL会在比较时自动进行转换，这相当于对索引列使用了函数，导致索引失效。
- 范围查询后的列失效： 在联合索引中，如果某个字段使用了范围查询（
>、<、BETWEEN），那么该字段之后的所有索引列将无法再被用于过滤。因此，在设计联合索引时，应尽量将需要范围查询的字段放在最后。
05 索引列尽量设置为 NOT NULL
不要忽视 NULL 值对索引的影响。建议将所有索引列都设置为 NOT NULL，并赋予一个合适的默认值（如 0 或空字符串 ””）。原因如下：
- 增加优化器负担：
NULL 值会使索引统计和值比较变得更加复杂。例如，COUNT(column) 会忽略 NULL 值，这可能影响优化器对执行计划的选择。
- 浪费存储空间： 在InnoDB中，对于允许为
NULL 的列，需要额外的空间来记录哪些行该列的值为 NULL，这增加了存储开销。
总结
MySQL索引优化并不需要追求过于复杂的技巧，熟练掌握以上几个基础且高频的技巧，就能解决日常开发中大部分的性能问题。其核心原则可以归纳为：让索引更紧凑、更高效地工作，并避免其意外失效，从而最大限度地减少不必要的I/O操作和数据移动。
掌握这些索引优化知识，是每一位后端开发者提升系统性能的必修课。如果你想深入探讨更多数据库或系统架构相关的实践，欢迎来云栈社区交流分享。
发表于 9 小时前
|
查看: 0|
回复: 0
