大概五年前我刚接手一个订单系统时被一个查询不到一秒、加了索引反而慢到十几秒的问题折腾得差点怀疑人生。后来才明白MySQL的索引不是加了就快而是一套需要理解底层逻辑才能真正驾驭的机制。这篇文章不打算写得像官方文档那样干巴巴而是把我这些年用索引、优化索引、排查索引失效问题积累下来的东西一次性理清楚从B树的结构到联合索引的设计再到Explain诊断和日常维护一条线走完。1. 为什么单行查询也要依赖索引——B树与磁盘IO的本质关系1.1 没有索引时MySQL是怎么找数据的很多初学者有个误区觉得一张表数据量不大全表扫描无所谓。但实际上MySQL存储引擎操作的最小单位是页默认一个页16KB数据在磁盘上按页存放。全表扫描意味着从第一个数据页开始一页一页读入内存再逐行匹配查询条件。假设一张表有500万行一行平均200字节那就是大约1GB的数据量。如果不走索引查询引擎需要扫描所有数据页。哪怕SSD的顺序读速度能做到几百MB每秒这1GB的数据全部过一遍也要好几秒。重要的是这还只是单次查询的开销如果这个查询在线上每秒被调用几十次数据库基本就废了。索引的本质就是给数据创建一种额外的、有序的查找结构让数据库不需要全表扫描就能定位到目标行。1.2 为什么偏偏是B树而不是二叉搜索树或者哈希表先说哈希索引。哈希索引的查找复杂度是O(1)理论上比B树更快。但它只能用于等值比较不支持范围查询也不支持排序。实际业务里WHERE age 20或者ORDER BY create_time这类操作太常见了所以InnoDB默认索引结构不是哈希。再说二叉搜索树。二叉搜索树在数据分布不均匀时容易退化成链表深度越来越大。即便用平衡二叉树如AVL树一个500万行的表树的高度也在22层左右。MySQL一次磁盘IO对应树的一层每多一层就多一次IO22次IO在磁盘上就是几十毫秒。B树则是多路搜索树一个节点可以存储多个键值。InnoDB的一个页默认16KB假设一条索引记录键值指针大约占用16字节那一个页大约能存储1000个键值。在B树中非叶子节点只存索引键和指针不存实际数据因此扇出fanout可以做得非常大。实际计算一下假设B树的高度为3层第一层约1000个键值第二层约1000*1000100万第三层约10亿个键值。也就是说一棵高度为3的B树可以轻松支撑亿级数据量的等值查询。3层意味着最多3次磁盘IO加上根节点通常常驻内存实际往往只需要2次磁盘IO速度完全不在一个量级。这里要强调一个容易忽略的细节B树的叶子节点之间通过双向链表串联并且叶子节点本身按索引键值顺序排列。这个设计让范围查询、排序操作不再需要回溯父节点只需要顺着链表往后扫就行这也是B树能统治关系型数据库索引领域的根本原因。1.3 聚簇索引与二级索引同一个B树两种完全不同的形态InnoDB的数据表本身就是一棵B树这棵树按主键排序叶子节点里存放的是完整的行记录这种索引就叫聚簇索引也叫主键索引。除了聚簇索引之外其他索引统称二级索引Secondary Index。二级索引的叶子节点里存的不是完整行记录而是索引键值加上主键值。比如一张订单表主键是id你对user_id建了一个索引那这棵B树的叶子节点就是user_idid的组合。这个设计会带来两个关键结论通过主键查询时InnoDB可以直接在聚簇索引的叶子节点拿到整行数据这就是回表不存在的场景。通过二级索引查询时InnoDB需要先在二级索引的B树里找到主键值再回到聚簇索引的B树里找完整行记录这个过程就是回表。理解这个结构后面讲覆盖索引、索引下推都会轻松很多。2. 索引的分类远比你想的复杂——从功能角色到存储形态2.1 功能层面的四种索引从功能上分MySQL索引主要分为普通索引、唯一索引、主键索引和全文索引。普通索引是最基础的索引只加速查询不限制数据的唯一性。像订单表的user_id字段一个用户可以下很多订单给它建普通索引即可。唯一索引在普通索引的基础上额外约束字段值不能重复。比如用户表的mobile字段要求手机号唯一就适合建唯一索引。它的一个优点是对可空字段建唯一索引时可以允许多个NULL值因为MySQL认为NULL和NULL不等。主键索引是特殊的唯一索引一个表只能有一个不能为NULL。InnoDB要求表必须有主键如果没有显式指定它会找第一个非空的唯一索引作为主键实在没有就隐式生成一个6字节的rowid。全文索引专门用于全文检索场景比如搜索文章正文中的关键词。MySQL的全文索引在中文分词方面并不好使生产环境大多数情况下会直接引入Elasticsearch之类的搜索引擎但了解它的存在还是必要的。索引类型允许重复允许NULL一个表可创建数量典型场景普通索引是是多个加速常用查询条件唯一索引否是多个保证业务字段唯一主键索引否否1个表数据物理排序依据全文索引是是多个大文本模糊检索2.2 存储形态层面的聚簇索引和二级索引前面讲过InnoDB下索引从存储形态上分就是聚簇索引和二级索引两种。聚簇索引的叶子节点存储完整行数据所以InnoDB表又被称为索引组织表。它的一个显著特点是数据行物理存储顺序和主键顺序基本一致。这意味着插入数据时如果主键是自增整数新数据总是追加在B树的末尾不需要挪动已有数据写入效率很高。反过来如果主键是随机UUID每次插入都可能触发节点分裂、数据重排写入性能和页空间利用率都会明显变差。二级索引每个表可以建多个但二级索引建的太多不是好事。每多一个二级索引插入一条数据时就要额外维护一棵B树的结构。写入放大是真实存在的索引建的越多写入越慢。一个表的二级索引数量我个人的经验是控制在5个以内超过就要辩证看了。2.3 联合索引和前缀索引的取舍联合索引是在多个字段上建立的一棵B树。和很多人想的不一样联合索引不是把几个字段独立建立索引而是按照字段定义顺序先按第一个字段排序第一个字段相同时再按第二个字段排序以此类推。前缀索引只针对字符串类型字段取字符串的前N个字符建立索引而不是整个字符串。比如用户昵称字段nickname VARCHAR(50)单独建整个字段索引索引体积会很大。取前10个字符建立索引索引体积小很多查询速度反而可能更快。但前缀索引的代价是它无法用于ORDER BY和GROUP BY操作同时也不能实现覆盖索引扫描。3. 联合索引与最左前缀一条SQL匹配索引的完整逻辑3.1 最左前缀原则的本质联合索引(a, b, c)的B树排序规则是先按a排序a相同再按b排序b相同再按c排序。所以如果查询条件跳过了a只用了b和c那排序关系对于b来说是全局无序的索引就无法用于快速定位只能扫描大量数据页。举个例子索引是(user_id, status, create_time)下面这三条SQL的命中情况完全不同WHERE user_id 100 AND status 1命中索引。WHERE status 1无法走索引因为跳过了最左边的user_id。WHERE user_id 100 AND create_time 2024-01-01只能用到user_id这一列create_time的部分可以用于范围过滤但status没用到所以整体不是完全命中索引。很多文章把这个叫最左前缀原则我更愿意把它理解为联合索引里从最左列开始连续的一段列才能参与索引匹配。中间断开了后面的就全部失效了。3.2 联合索引字段顺序设计的两条硬经验设计联合索引时字段顺序的选择决定了这个索引能不能被多个查询复用。我常用的判断逻辑有两个第一区分度高的字段放前面。区分度就是字段不同值的比例。比如user_id的区分度远高于status通常只有几个固定值把区分度高的放前面可以让B树更快地收敛到目标范围。第二经常用于等值查询的字段放前面常用于范围查询的字段放后面。等值查询可以直接精确定位范围查询、、BETWEEN只能锁定一个区间。如果把范围字段放前面等值字段放后面等值字段就无法利用索引精确定位了。第二点尤其容易被忽视。比如索引(age, name)执行WHERE age 20 AND name 张三时age定位出一个大范围name在这个范围里已经无序了只能一个个过滤。而索引(name, age)执行WHERE name 张三 AND age 20时name直接精确定位到张三的所有记录再在这些记录中做age的范围筛选。还有一个联合索引的进阶技巧叫索引跳跃扫描。MySQL 8.0的某些版本对WHERE b 1这种跳过了最左列的查询会自动优化成对a的每个不同值都做一次索引查询。但这个优化依赖a的基数很低并且是优化器自动判断的不能把它当成常规手段。3.3 排序字段和索引的配合ORDER BY走索引是很多高性能查询的关键。但排序能不能走索引取决于排序字段的顺序和方向是否和联合索引的键值排序顺序一致。比如索引(a, b)以下情况都可以避免文件排序ORDER BY a直接沿索引顺序扫描。ORDER BY a, b完全匹配索引顺序。WHERE a 1 ORDER BY ba先被等值条件确定b再排序正好符合a相同按b排序的索引结构。但ORDER BY b单独出现或者ORDER BY a DESC, b ASC这种方向不一致的排序就没法直接利用索引了。因为索引是(a ASC, b ASC)排列的没法同时满足a降序和b升序。值得一提的是MySQL 8.0支持降序索引。8.0之前虽然可以写INDEX(a DESC)但实际上不会改变存储顺序。8.0之后真正按降序存储的索引就存在了这可以有效解决多字段排序方向不一致的问题。4. 回表、覆盖索引与索引下推三个必须分清的概念4.1 回表到底回什么回表的完整过程是通过二级索引找到主键值再拿着主键值去聚簇索引中查找完整行记录。比如表t有主键id二级索引idx_user_id(user_id)执行SELECT * FROM t WHERE user_id 123的执行过程是从idx_user_id的B树中定位到user_id123的叶子节点拿到主键id。从聚簇索引的B树中用主键id定位到完整行记录取出所有字段。如果筛选出来的记录很多比如有1000条那就要回表1000次这1000次主键查询虽然很快但累积起来的随机IO成本不可忽略。4.2 覆盖索引让SELECT的字段在索引里就能全部拿到覆盖索引本身不是一个独立的索引类型而是一种索引覆盖了查询所需的所有字段的状态。还是上面的例子如果执行的是SELECT user_id FROM t WHERE user_id 123那查询的列和过滤的列都在idx_user_id这棵二级索引树上完全不需要回表。覆盖索引能带来的性能提升非常明显。二级索引树的体积通常比聚簇索引小很多一次查询只扫二级索引既能减少磁盘IO也降低了内存占用。实际开发中设计索引时习惯性地想一想业务查询要返回哪些列把高频查询中需要的列加入联合索引就能让很多查询变成覆盖索引查询。但要注意不要为了让所有查询都覆盖而塞入太多列这会无限膨胀索引体积索引维护成本反而会超过收益。4.3 索引下推减少回表次数的隐藏功臣索引下推Index Condition Pushdown, ICP是MySQL 5.6引入的优化。没有ICP时存储引擎用二级索引取出记录后将整条记录回表到聚簇索引然后在Server层判断WHERE里的其他条件。开启ICP后存储引擎在二级索引内部会把WHERE条件中能被二级索引列判断的部分先过滤掉只对剩下的记录回表。举一个真实例子。联合索引(user_id, status)执行SELECT * FROM t WHERE user_id 123 AND status 1无ICP先通过user_id123取出所有记录主键全部回表取得完整行再在Server层过滤status1。有ICP在二级索引内部直接判断status1只把status等于1的记录主键拿去回表。如果你的表里user_id123有1000条记录其中只有100条status1那么ICP帮忙省掉了900次回表。这个优化默认开启不需要额外配置但理解它有助于你分析执行计划时更清楚Extra列里的Using index condition是什么意思。5. 索引失效高频场景老手也会中招的八个坑5.1 对索引列做了函数操作或者隐式类型转换WHERE DATE(create_time) 2024-01-01这个写法很常见但它会让create_time上的索引完全失效。因为索引中存储的是原始的create_time值不是DATE(create_time)的计算结果数据库必须逐行计算函数值才能判断是否匹配整棵B树等于白建。正确的做法是写成范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02。隐式类型转换也是一个隐蔽的坑。比如mobile字段是VARCHAR类型索引建在mobile上执行WHERE mobile 13800138000时MySQL会把字符串字段和数字比较它倾向于把字符串转换成数字再比较导致索引列上发生隐式函数操作索引就失效了。所以SQL里字符串字段的等值条件一定要记得加引号。5.2 LIKE模糊匹配的头部通配符LIKE %关键词这个写法因为通配符在最前面B树的排序优势发挥不出来只能全索引扫描或者全表扫描。LIKE 关键词%则是可以走索引的因为字符串从开头起就按索引顺序排列。遇到不得不做后缀匹配的业务比如搜索手机号尾号常规解法是引入Elasticsearch这类搜索引擎或者单独建立倒排索引表而不是指望MySQL的LIKE。5.3 OR连接导致的索引失效WHERE user_id 123 OR status 1这条SQL如果user_id和status上都有独立索引MySQL是有可能改成UNION分别走两个索引的。但如果status上没有索引那OR要求只要满足其中任何一个条件都算匹配优化器只能选择全表扫描。所以OR条件涉及的每个字段都必须有索引可用否则整体走不了索引。更稳妥的方案是改写为UNION ALL但前提是业务语义允许去掉重复数据。5.4 负向查询与不等于操作WHERE status ! 1、WHERE status NOT IN (1, 2)、WHERE status NOT LIKE A%这类负向查询本质上是要扫描所有不等于某个值的记录。如果这个值分布非常广比如100万行里只有一行status不等于1理论上走索引覆盖扫描比全表扫描更快但MySQL的优化器通常不会这么聪明大部分情况下它会选择全表扫描。这类查询没有特别完美的优化手段要么把SQL改写成正向查询比如WHERE status 2 UNION ALL WHERE status 3前提是status只能取有限个值要么接受全表扫描。5.5 联合索引中没有用到第一列前面说过联合索引(a, b)中直接查询b通常是走不了索引的。这是最基础的坑但也是实际工作中出现频率最高的坑。每当你发现一个查询没走索引第一反应应该是看WHERE条件里有没有包含相关联合索引的最左列。5.6 对可空字段使用IS NULL或者IS NOT NULL关于IS NULL能否走索引其实要看MySQL版本和优化器。在MySQL 8.0的InnoDB引擎下WHERE column IS NULL是可以走索引的。但WHERE column IS NOT NULL如果匹配比例太高优化器通常认为全表扫描比索引扫描回表更快。从设计角度来说尽量避免让索引列允许NULL。更好的做法是给字段设置一个业务上的默认值比如状态默认0这样查询条件可以统一写成WHERE status 0不仅语义清晰也更容易走索引。5.7 索引列参与计算WHERE price * 10 100和WHERE price 10虽然前者可能是有意为之但一旦索引列参与算术运算索引就会失效。因为B树上的值都是原始存储值函数或表达式计算出的结果无法直接与索引键做比较。正确做法永远是把计算移到等号右侧。5.8 字符集不一致导致的隐式转换消耗表连接的字段如果字符集不一致比如一张表用utf8mb4另一张表用latin1MySQL会在连接过程中对其中一列做隐式字符集转换。这种转换本质上也是函数操作索引同样会失效代价是产生临时表和文件排序。建表时统一字符集尤其是关联字段的字符集必须一致这是数据库设计的基础规范。我接手过的项目里因为早期建表不统一导致后期关联查询慢的案例至少遇到过三次。6. Explain诊断与索引设计原则让索引真正服务于业务6.1 Explain执行计划到底应该怎么读排查SQL性能问题的第一步不是瞎猜而是用EXPLAIN看执行计划。下面这条SQL是标准姿势EXPLAIN SELECT user_id, status FROM order_table WHERE user_id 100 AND status 1;重点关注这几列type访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL基本就是全表扫描了是最需要警惕的。key实际使用的索引名。如果是NULL说明这条SQL根本没走任何索引。rows估计扫描的行数。这个值越小越好但它只是一个估算值受统计信息影响不一定精确。Extra这里的信息量最大。出现Using filesort说明排序没用上索引出现Using temporary说明查询创建了临时表出现Using index说明是覆盖索引扫描是理想状态。举一个我实际排查过的慢查询案例SELECT * FROM payment_log WHERE merchant_id 88 AND pay_status 2 ORDER BY create_time DESC LIMIT 10;执行计划显示typeref、keyidx_merchant看起来走了索引但Extra里有Using filesort。原因是idx_merchant只建在了merchant_id上排序字段create_time不在索引里。优化方案是把索引改成联合索引(merchant_id, pay_status, create_time)这样merchant_id等值定位、pay_status过滤、create_time直接按索引顺序取出一次索引扫描搞定Using filesort消失查询耗时从800ms降到了10ms以下。6.2 索引设计的原则宁缺毋滥按需创建索引不是越多越好这个道理大家都知道。但实际的索引设计到底该怎么做我总结了一套可落地的流程第一步找出高频查询。打开慢查询日志或者和业务方沟通列出前20条执行最频繁或者响应最慢的SQL。第二步为每条SQL分析最优索引。用前面讲的最左前缀原则判断哪些字段应该组合在一起。能复用的索引就尽量复用避免每个查询单独建一个索引。第三步删除冗余索引。如果一个索引(a, b)已经存在那么(a)单独的索引基本就是冗余的。用SHOW INDEX FROM table_name查看现有索引再用sys.schema_redundant_indexes这个视图可以直接查出冗余索引。第四步考虑写入成本。索引影响读也影响写。每次插入、更新、删除都要同步维护所有相关索引。如果业务是高频写入场景比如订单流水表索引就不能建太多。我见过最夸张的一张线上表只有6个字段建了9个索引。写入性能差到一天只能写入几十万条数据后来删掉5个冗余索引写入耗时下降了接近70%。6.3 索引维护的日常碎片、统计信息和不可见索引索引维护不只体现在设计阶段上线之后同样需要注意。InnoDB的B树在频繁删除和更新后索引页会产生碎片。碎片过多会导致扫描页数增加、缓存命中率下降。可以用ALTER TABLE table_name ENGINEInnoDB重建表来整理碎片但这个过程会锁表必须在低峰期执行。还有统计信息的问题。优化器要不要走索引、走哪个索引依赖表的统计信息。MySQL 8.0默认开启了自动重算统计信息innodb_stats_auto_recalcON大部分情况下不需要人工干预但如果遇到优化器选错索引的情况可以手动执行ANALYZE TABLE table_name刷新统计信息。MySQL 8.0还有一个我比较喜欢的功能叫不可见索引Invisible Indexes。它最大的价值在于你可以把一个索引临时改成不可见观察线上查询有没有变慢用来验证这个索引是不是真的有用。比直接删除索引安全得多毕竟删了再重建的成本很高。ALTER TABLE order_table ALTER INDEX idx_user_id INVISIBLE; ALTER TABLE order_table ALTER INDEX idx_user_id VISIBLE;6.4 索引与事务隔离级别之间的微妙关系最后聊一个很多人没意识到的问题索引和锁机制是绑定在一起的。InnoDB的行锁实际上是通过索引实现的不是单纯锁住某一行物理记录。如果一条UPDATE语句的WHERE条件没有走索引那InnoDB会锁住聚簇索引中的所有记录然后在Server层逐条过滤。这就是为什么明明只更新一条数据却把整张表锁住、导致大量阻塞的原因。所以从并发事务的角度来看给高频率更新的WHERE字段建立索引不只是查询优化的问题更直接关系到系统的并发能力。我曾经处理过一个线上事故某定时任务执行UPDATE account SET balance balance - 1 WHERE status 0status字段没有索引结果这条语句虽然只更新了几行却把整张表的的写锁拖了近百秒所有用户下单都卡住了。给status加上索引后同样的语句执行时间降到毫秒级。这个案例足以说明索引在事务和锁的维度上价值甚至比查询加速还要大。索引的每个知识点单独看都不算难但真正难的是在实际业务里组合运用建什么样的联合索引能最优覆盖多条高频SQL哪些看似合规的查询其实悄悄放弃了索引INSERT压力大的表该容忍多少索引冗余。把这些边界想清楚MySQL的索引能力才算真正变成自己的了。
大概五年前我刚接手一个订单系统时被一个查询不到一秒、加了索引反而慢到十几秒的问题折腾得差点怀疑人生。后来才明白MySQL的索引不是加了就快而是一套需要理解底层逻辑才能真正驾驭的机制。这篇文章不打算写得像官方文档那样干巴巴而是把我这些年用索引、优化索引、排查索引失效问题积累下来的东西一次性理清楚从B树的结构到联合索引的设计再到Explain诊断和日常维护一条线走完。1. 为什么单行查询也要依赖索引——B树与磁盘IO的本质关系1.1 没有索引时MySQL是怎么找数据的很多初学者有个误区觉得一张表数据量不大全表扫描无所谓。但实际上MySQL存储引擎操作的最小单位是页默认一个页16KB数据在磁盘上按页存放。全表扫描意味着从第一个数据页开始一页一页读入内存再逐行匹配查询条件。假设一张表有500万行一行平均200字节那就是大约1GB的数据量。如果不走索引查询引擎需要扫描所有数据页。哪怕SSD的顺序读速度能做到几百MB每秒这1GB的数据全部过一遍也要好几秒。重要的是这还只是单次查询的开销如果这个查询在线上每秒被调用几十次数据库基本就废了。索引的本质就是给数据创建一种额外的、有序的查找结构让数据库不需要全表扫描就能定位到目标行。1.2 为什么偏偏是B树而不是二叉搜索树或者哈希表先说哈希索引。哈希索引的查找复杂度是O(1)理论上比B树更快。但它只能用于等值比较不支持范围查询也不支持排序。实际业务里WHERE age 20或者ORDER BY create_time这类操作太常见了所以InnoDB默认索引结构不是哈希。再说二叉搜索树。二叉搜索树在数据分布不均匀时容易退化成链表深度越来越大。即便用平衡二叉树如AVL树一个500万行的表树的高度也在22层左右。MySQL一次磁盘IO对应树的一层每多一层就多一次IO22次IO在磁盘上就是几十毫秒。B树则是多路搜索树一个节点可以存储多个键值。InnoDB的一个页默认16KB假设一条索引记录键值指针大约占用16字节那一个页大约能存储1000个键值。在B树中非叶子节点只存索引键和指针不存实际数据因此扇出fanout可以做得非常大。实际计算一下假设B树的高度为3层第一层约1000个键值第二层约1000*1000100万第三层约10亿个键值。也就是说一棵高度为3的B树可以轻松支撑亿级数据量的等值查询。3层意味着最多3次磁盘IO加上根节点通常常驻内存实际往往只需要2次磁盘IO速度完全不在一个量级。这里要强调一个容易忽略的细节B树的叶子节点之间通过双向链表串联并且叶子节点本身按索引键值顺序排列。这个设计让范围查询、排序操作不再需要回溯父节点只需要顺着链表往后扫就行这也是B树能统治关系型数据库索引领域的根本原因。1.3 聚簇索引与二级索引同一个B树两种完全不同的形态InnoDB的数据表本身就是一棵B树这棵树按主键排序叶子节点里存放的是完整的行记录这种索引就叫聚簇索引也叫主键索引。除了聚簇索引之外其他索引统称二级索引Secondary Index。二级索引的叶子节点里存的不是完整行记录而是索引键值加上主键值。比如一张订单表主键是id你对user_id建了一个索引那这棵B树的叶子节点就是user_idid的组合。这个设计会带来两个关键结论通过主键查询时InnoDB可以直接在聚簇索引的叶子节点拿到整行数据这就是回表不存在的场景。通过二级索引查询时InnoDB需要先在二级索引的B树里找到主键值再回到聚簇索引的B树里找完整行记录这个过程就是回表。理解这个结构后面讲覆盖索引、索引下推都会轻松很多。2. 索引的分类远比你想的复杂——从功能角色到存储形态2.1 功能层面的四种索引从功能上分MySQL索引主要分为普通索引、唯一索引、主键索引和全文索引。普通索引是最基础的索引只加速查询不限制数据的唯一性。像订单表的user_id字段一个用户可以下很多订单给它建普通索引即可。唯一索引在普通索引的基础上额外约束字段值不能重复。比如用户表的mobile字段要求手机号唯一就适合建唯一索引。它的一个优点是对可空字段建唯一索引时可以允许多个NULL值因为MySQL认为NULL和NULL不等。主键索引是特殊的唯一索引一个表只能有一个不能为NULL。InnoDB要求表必须有主键如果没有显式指定它会找第一个非空的唯一索引作为主键实在没有就隐式生成一个6字节的rowid。全文索引专门用于全文检索场景比如搜索文章正文中的关键词。MySQL的全文索引在中文分词方面并不好使生产环境大多数情况下会直接引入Elasticsearch之类的搜索引擎但了解它的存在还是必要的。索引类型允许重复允许NULL一个表可创建数量典型场景普通索引是是多个加速常用查询条件唯一索引否是多个保证业务字段唯一主键索引否否1个表数据物理排序依据全文索引是是多个大文本模糊检索2.2 存储形态层面的聚簇索引和二级索引前面讲过InnoDB下索引从存储形态上分就是聚簇索引和二级索引两种。聚簇索引的叶子节点存储完整行数据所以InnoDB表又被称为索引组织表。它的一个显著特点是数据行物理存储顺序和主键顺序基本一致。这意味着插入数据时如果主键是自增整数新数据总是追加在B树的末尾不需要挪动已有数据写入效率很高。反过来如果主键是随机UUID每次插入都可能触发节点分裂、数据重排写入性能和页空间利用率都会明显变差。二级索引每个表可以建多个但二级索引建的太多不是好事。每多一个二级索引插入一条数据时就要额外维护一棵B树的结构。写入放大是真实存在的索引建的越多写入越慢。一个表的二级索引数量我个人的经验是控制在5个以内超过就要辩证看了。2.3 联合索引和前缀索引的取舍联合索引是在多个字段上建立的一棵B树。和很多人想的不一样联合索引不是把几个字段独立建立索引而是按照字段定义顺序先按第一个字段排序第一个字段相同时再按第二个字段排序以此类推。前缀索引只针对字符串类型字段取字符串的前N个字符建立索引而不是整个字符串。比如用户昵称字段nickname VARCHAR(50)单独建整个字段索引索引体积会很大。取前10个字符建立索引索引体积小很多查询速度反而可能更快。但前缀索引的代价是它无法用于ORDER BY和GROUP BY操作同时也不能实现覆盖索引扫描。3. 联合索引与最左前缀一条SQL匹配索引的完整逻辑3.1 最左前缀原则的本质联合索引(a, b, c)的B树排序规则是先按a排序a相同再按b排序b相同再按c排序。所以如果查询条件跳过了a只用了b和c那排序关系对于b来说是全局无序的索引就无法用于快速定位只能扫描大量数据页。举个例子索引是(user_id, status, create_time)下面这三条SQL的命中情况完全不同WHERE user_id 100 AND status 1命中索引。WHERE status 1无法走索引因为跳过了最左边的user_id。WHERE user_id 100 AND create_time 2024-01-01只能用到user_id这一列create_time的部分可以用于范围过滤但status没用到所以整体不是完全命中索引。很多文章把这个叫最左前缀原则我更愿意把它理解为联合索引里从最左列开始连续的一段列才能参与索引匹配。中间断开了后面的就全部失效了。3.2 联合索引字段顺序设计的两条硬经验设计联合索引时字段顺序的选择决定了这个索引能不能被多个查询复用。我常用的判断逻辑有两个第一区分度高的字段放前面。区分度就是字段不同值的比例。比如user_id的区分度远高于status通常只有几个固定值把区分度高的放前面可以让B树更快地收敛到目标范围。第二经常用于等值查询的字段放前面常用于范围查询的字段放后面。等值查询可以直接精确定位范围查询、、BETWEEN只能锁定一个区间。如果把范围字段放前面等值字段放后面等值字段就无法利用索引精确定位了。第二点尤其容易被忽视。比如索引(age, name)执行WHERE age 20 AND name 张三时age定位出一个大范围name在这个范围里已经无序了只能一个个过滤。而索引(name, age)执行WHERE name 张三 AND age 20时name直接精确定位到张三的所有记录再在这些记录中做age的范围筛选。还有一个联合索引的进阶技巧叫索引跳跃扫描。MySQL 8.0的某些版本对WHERE b 1这种跳过了最左列的查询会自动优化成对a的每个不同值都做一次索引查询。但这个优化依赖a的基数很低并且是优化器自动判断的不能把它当成常规手段。3.3 排序字段和索引的配合ORDER BY走索引是很多高性能查询的关键。但排序能不能走索引取决于排序字段的顺序和方向是否和联合索引的键值排序顺序一致。比如索引(a, b)以下情况都可以避免文件排序ORDER BY a直接沿索引顺序扫描。ORDER BY a, b完全匹配索引顺序。WHERE a 1 ORDER BY ba先被等值条件确定b再排序正好符合a相同按b排序的索引结构。但ORDER BY b单独出现或者ORDER BY a DESC, b ASC这种方向不一致的排序就没法直接利用索引了。因为索引是(a ASC, b ASC)排列的没法同时满足a降序和b升序。值得一提的是MySQL 8.0支持降序索引。8.0之前虽然可以写INDEX(a DESC)但实际上不会改变存储顺序。8.0之后真正按降序存储的索引就存在了这可以有效解决多字段排序方向不一致的问题。4. 回表、覆盖索引与索引下推三个必须分清的概念4.1 回表到底回什么回表的完整过程是通过二级索引找到主键值再拿着主键值去聚簇索引中查找完整行记录。比如表t有主键id二级索引idx_user_id(user_id)执行SELECT * FROM t WHERE user_id 123的执行过程是从idx_user_id的B树中定位到user_id123的叶子节点拿到主键id。从聚簇索引的B树中用主键id定位到完整行记录取出所有字段。如果筛选出来的记录很多比如有1000条那就要回表1000次这1000次主键查询虽然很快但累积起来的随机IO成本不可忽略。4.2 覆盖索引让SELECT的字段在索引里就能全部拿到覆盖索引本身不是一个独立的索引类型而是一种索引覆盖了查询所需的所有字段的状态。还是上面的例子如果执行的是SELECT user_id FROM t WHERE user_id 123那查询的列和过滤的列都在idx_user_id这棵二级索引树上完全不需要回表。覆盖索引能带来的性能提升非常明显。二级索引树的体积通常比聚簇索引小很多一次查询只扫二级索引既能减少磁盘IO也降低了内存占用。实际开发中设计索引时习惯性地想一想业务查询要返回哪些列把高频查询中需要的列加入联合索引就能让很多查询变成覆盖索引查询。但要注意不要为了让所有查询都覆盖而塞入太多列这会无限膨胀索引体积索引维护成本反而会超过收益。4.3 索引下推减少回表次数的隐藏功臣索引下推Index Condition Pushdown, ICP是MySQL 5.6引入的优化。没有ICP时存储引擎用二级索引取出记录后将整条记录回表到聚簇索引然后在Server层判断WHERE里的其他条件。开启ICP后存储引擎在二级索引内部会把WHERE条件中能被二级索引列判断的部分先过滤掉只对剩下的记录回表。举一个真实例子。联合索引(user_id, status)执行SELECT * FROM t WHERE user_id 123 AND status 1无ICP先通过user_id123取出所有记录主键全部回表取得完整行再在Server层过滤status1。有ICP在二级索引内部直接判断status1只把status等于1的记录主键拿去回表。如果你的表里user_id123有1000条记录其中只有100条status1那么ICP帮忙省掉了900次回表。这个优化默认开启不需要额外配置但理解它有助于你分析执行计划时更清楚Extra列里的Using index condition是什么意思。5. 索引失效高频场景老手也会中招的八个坑5.1 对索引列做了函数操作或者隐式类型转换WHERE DATE(create_time) 2024-01-01这个写法很常见但它会让create_time上的索引完全失效。因为索引中存储的是原始的create_time值不是DATE(create_time)的计算结果数据库必须逐行计算函数值才能判断是否匹配整棵B树等于白建。正确的做法是写成范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02。隐式类型转换也是一个隐蔽的坑。比如mobile字段是VARCHAR类型索引建在mobile上执行WHERE mobile 13800138000时MySQL会把字符串字段和数字比较它倾向于把字符串转换成数字再比较导致索引列上发生隐式函数操作索引就失效了。所以SQL里字符串字段的等值条件一定要记得加引号。5.2 LIKE模糊匹配的头部通配符LIKE %关键词这个写法因为通配符在最前面B树的排序优势发挥不出来只能全索引扫描或者全表扫描。LIKE 关键词%则是可以走索引的因为字符串从开头起就按索引顺序排列。遇到不得不做后缀匹配的业务比如搜索手机号尾号常规解法是引入Elasticsearch这类搜索引擎或者单独建立倒排索引表而不是指望MySQL的LIKE。5.3 OR连接导致的索引失效WHERE user_id 123 OR status 1这条SQL如果user_id和status上都有独立索引MySQL是有可能改成UNION分别走两个索引的。但如果status上没有索引那OR要求只要满足其中任何一个条件都算匹配优化器只能选择全表扫描。所以OR条件涉及的每个字段都必须有索引可用否则整体走不了索引。更稳妥的方案是改写为UNION ALL但前提是业务语义允许去掉重复数据。5.4 负向查询与不等于操作WHERE status ! 1、WHERE status NOT IN (1, 2)、WHERE status NOT LIKE A%这类负向查询本质上是要扫描所有不等于某个值的记录。如果这个值分布非常广比如100万行里只有一行status不等于1理论上走索引覆盖扫描比全表扫描更快但MySQL的优化器通常不会这么聪明大部分情况下它会选择全表扫描。这类查询没有特别完美的优化手段要么把SQL改写成正向查询比如WHERE status 2 UNION ALL WHERE status 3前提是status只能取有限个值要么接受全表扫描。5.5 联合索引中没有用到第一列前面说过联合索引(a, b)中直接查询b通常是走不了索引的。这是最基础的坑但也是实际工作中出现频率最高的坑。每当你发现一个查询没走索引第一反应应该是看WHERE条件里有没有包含相关联合索引的最左列。5.6 对可空字段使用IS NULL或者IS NOT NULL关于IS NULL能否走索引其实要看MySQL版本和优化器。在MySQL 8.0的InnoDB引擎下WHERE column IS NULL是可以走索引的。但WHERE column IS NOT NULL如果匹配比例太高优化器通常认为全表扫描比索引扫描回表更快。从设计角度来说尽量避免让索引列允许NULL。更好的做法是给字段设置一个业务上的默认值比如状态默认0这样查询条件可以统一写成WHERE status 0不仅语义清晰也更容易走索引。5.7 索引列参与计算WHERE price * 10 100和WHERE price 10虽然前者可能是有意为之但一旦索引列参与算术运算索引就会失效。因为B树上的值都是原始存储值函数或表达式计算出的结果无法直接与索引键做比较。正确做法永远是把计算移到等号右侧。5.8 字符集不一致导致的隐式转换消耗表连接的字段如果字符集不一致比如一张表用utf8mb4另一张表用latin1MySQL会在连接过程中对其中一列做隐式字符集转换。这种转换本质上也是函数操作索引同样会失效代价是产生临时表和文件排序。建表时统一字符集尤其是关联字段的字符集必须一致这是数据库设计的基础规范。我接手过的项目里因为早期建表不统一导致后期关联查询慢的案例至少遇到过三次。6. Explain诊断与索引设计原则让索引真正服务于业务6.1 Explain执行计划到底应该怎么读排查SQL性能问题的第一步不是瞎猜而是用EXPLAIN看执行计划。下面这条SQL是标准姿势EXPLAIN SELECT user_id, status FROM order_table WHERE user_id 100 AND status 1;重点关注这几列type访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL基本就是全表扫描了是最需要警惕的。key实际使用的索引名。如果是NULL说明这条SQL根本没走任何索引。rows估计扫描的行数。这个值越小越好但它只是一个估算值受统计信息影响不一定精确。Extra这里的信息量最大。出现Using filesort说明排序没用上索引出现Using temporary说明查询创建了临时表出现Using index说明是覆盖索引扫描是理想状态。举一个我实际排查过的慢查询案例SELECT * FROM payment_log WHERE merchant_id 88 AND pay_status 2 ORDER BY create_time DESC LIMIT 10;执行计划显示typeref、keyidx_merchant看起来走了索引但Extra里有Using filesort。原因是idx_merchant只建在了merchant_id上排序字段create_time不在索引里。优化方案是把索引改成联合索引(merchant_id, pay_status, create_time)这样merchant_id等值定位、pay_status过滤、create_time直接按索引顺序取出一次索引扫描搞定Using filesort消失查询耗时从800ms降到了10ms以下。6.2 索引设计的原则宁缺毋滥按需创建索引不是越多越好这个道理大家都知道。但实际的索引设计到底该怎么做我总结了一套可落地的流程第一步找出高频查询。打开慢查询日志或者和业务方沟通列出前20条执行最频繁或者响应最慢的SQL。第二步为每条SQL分析最优索引。用前面讲的最左前缀原则判断哪些字段应该组合在一起。能复用的索引就尽量复用避免每个查询单独建一个索引。第三步删除冗余索引。如果一个索引(a, b)已经存在那么(a)单独的索引基本就是冗余的。用SHOW INDEX FROM table_name查看现有索引再用sys.schema_redundant_indexes这个视图可以直接查出冗余索引。第四步考虑写入成本。索引影响读也影响写。每次插入、更新、删除都要同步维护所有相关索引。如果业务是高频写入场景比如订单流水表索引就不能建太多。我见过最夸张的一张线上表只有6个字段建了9个索引。写入性能差到一天只能写入几十万条数据后来删掉5个冗余索引写入耗时下降了接近70%。6.3 索引维护的日常碎片、统计信息和不可见索引索引维护不只体现在设计阶段上线之后同样需要注意。InnoDB的B树在频繁删除和更新后索引页会产生碎片。碎片过多会导致扫描页数增加、缓存命中率下降。可以用ALTER TABLE table_name ENGINEInnoDB重建表来整理碎片但这个过程会锁表必须在低峰期执行。还有统计信息的问题。优化器要不要走索引、走哪个索引依赖表的统计信息。MySQL 8.0默认开启了自动重算统计信息innodb_stats_auto_recalcON大部分情况下不需要人工干预但如果遇到优化器选错索引的情况可以手动执行ANALYZE TABLE table_name刷新统计信息。MySQL 8.0还有一个我比较喜欢的功能叫不可见索引Invisible Indexes。它最大的价值在于你可以把一个索引临时改成不可见观察线上查询有没有变慢用来验证这个索引是不是真的有用。比直接删除索引安全得多毕竟删了再重建的成本很高。ALTER TABLE order_table ALTER INDEX idx_user_id INVISIBLE; ALTER TABLE order_table ALTER INDEX idx_user_id VISIBLE;6.4 索引与事务隔离级别之间的微妙关系最后聊一个很多人没意识到的问题索引和锁机制是绑定在一起的。InnoDB的行锁实际上是通过索引实现的不是单纯锁住某一行物理记录。如果一条UPDATE语句的WHERE条件没有走索引那InnoDB会锁住聚簇索引中的所有记录然后在Server层逐条过滤。这就是为什么明明只更新一条数据却把整张表锁住、导致大量阻塞的原因。所以从并发事务的角度来看给高频率更新的WHERE字段建立索引不只是查询优化的问题更直接关系到系统的并发能力。我曾经处理过一个线上事故某定时任务执行UPDATE account SET balance balance - 1 WHERE status 0status字段没有索引结果这条语句虽然只更新了几行却把整张表的的写锁拖了近百秒所有用户下单都卡住了。给status加上索引后同样的语句执行时间降到毫秒级。这个案例足以说明索引在事务和锁的维度上价值甚至比查询加速还要大。索引的每个知识点单独看都不算难但真正难的是在实际业务里组合运用建什么样的联合索引能最优覆盖多条高频SQL哪些看似合规的查询其实悄悄放弃了索引INSERT压力大的表该容忍多少索引冗余。把这些边界想清楚MySQL的索引能力才算真正变成自己的了。
