本文共 1342 字，大约阅读时间需要 4 分钟。

MySQL 索引基础、规则与优化策略

索引在数据库中扮演着重要角色，其核心目标是通过快速定位数据来减少查询时间。以下是关于MySQL索引的详细分析：

索引的基础

索引可以被视为数据库的“目录”，用于快速定位数据。其真正价值体现在树搜索功能上，而非全表或全索引扫描，从而显著减少扫描行数。

• 索引结构：MySQL索引的存储结构是B+树，等值查询的时间复杂度为O(log(N))。B+树是有序存储，叶子节点之间通过指针连接。
• 写入慢查询：执行时间超过long_query_time（默认10秒，线上通常设置为1秒甚至更短）会被视为慢查询，并记录到慢查询日志。
• SQL分析工具：使用EXPLAIN分析SQL语句，rows字段反映预计扫描行数，这并非精确值，而是通过采样统计得出的。
• 索引效率：关注扫描行数而非是否使用索引。若使用了索引或全表/全索引扫描，索引的价值未能体现。

索引的规则

了解索引的工作原理有助于优化数据库性能。

• 覆盖索引：普通索引的叶子节点存储主键值。若查询内容不止是主键值，需通过回表操作获取完整记录。
• 最左前缀原则：为最常用查询创建索引，减少索引总数并降低MySQL负担。
• 索引下推（Index Condition Pushdown）：在MySQL 5.6及以后的版本中引入，减少回表操作，提升查询效率。
• 索引更新机制：普通索引更新使用change buffer缓存结果，而非每次都写入磁盘。对于时效性较低的数据，优先使用普通索引而非唯一索引。

索引的优化策略

通过合理设计索引，可以显著提升数据库性能。

• 长字符串索引：MySQL 5.7引入了虚拟列，适用于对长字符串字段的查询优化。
• 双主库架构：设置auto_increment_increment为2，一个写奇数一个写偶数，防止主键冲突。
• 大数据处理：对于大表查询，可以通过临时表创建索引以减少开销。
• 字符串前缀索引：基于count(distinct)确定前缀长度，最大程度地提高索引区分度。
• 逆序存储或哈希值索引：适用于等值查找场景，但不适合范围扫描，可能导致全索引扫描。
• 优化器选择问题：优化器可能选择错误索引，建议通过force index强制指定、修改SQL语句、删除或添加索引，或使用analyze table重新统计索引信息。
• JOIN操作优化：被驱动表使用NLJ算法，优先让小表（满足条件的数据量较少）作为驱动表，以减少查询次数。
• 避免全表扫描：EXPLAIN结果中Extra列出现Block Nested Loop (BNL)时，表明被驱动表进行了全表扫描。若无法避免，可适当调大join_buffer_size以减少全表扫描次数。

索引的锁机制

索引锁机制确保数据并发操作的正确性。

• 锁单位：默认为next-key锁，具有前开后闭特性。
• 锁升级：在查找过程中访问到的对象会被加锁。
• 索引等值查询：唯一索引在加锁时会退化为行锁（InnoDB）。
• 索引区间查询：若等值查询向右遍历且最后一个值不满足条件，锁会退化为区间锁（前开后开）。
• 逆序遍历：在有ORDER BY ... DESC要求时，索引会从后向前遍历。

通过以上知识，合理设计和优化索引，可以显著提升数据库性能，减少查询时间并提高系统稳定性。