尧图网页设计 
MySQL索引原理这话题看着像个基础八股文其实里面水挺深。我当年刚带团队时就吃过一次大亏一个运营后台的列表页数据量刚过百万接口直接超时数据库CPU被打满。当时第一反应是加配置、加缓存结果排查下来发现就是一条SQL没走索引全表扫了近一千万行。加了索引之后查询从2.7秒掉到30毫秒连SQL都不用改。那个瞬间我意识到不懂索引原理连排查问题的方向都是错的。这篇内容我会从索引的本质讲起一层层拆到InnoDB的B树结构再结合聚簇索引、回表、索引失效这些高频面试点最后落到EXPLAIN实战和索引运维经验上。不堆概念尽量用大白话和真实场景说清楚。适合刚入门的开发也适合写过一两年SQL但没系统理过索引原理的人。1. MySQL索引到底是什么一次慢查询事故给我的教训1.1 索引不是“优化神药”它本质上是一棵有序树很多人把索引理解成“给字段加个目录”这个类比方向是对的但不够准确。书的目录只是告诉你内容在第几页而MySQL索引要解决的是在海量数据里快速定位到目标行同时还得支持高效的插入、删除和更新。打个比方。你有一本没有页码、也没有目录的字典要在里面找一个字只能从第一页翻到最后一页。如果这本字典有一千页运气好可能翻十页就找到了运气差就得翻九百多页。这种查找方式在数据库里就叫全表扫描。复杂度是O(n)数据量翻倍耗时基本也翻倍。索引做的事情就是给字典加上“拼音目录”和“部首目录”。你不用一页页翻而是先查目录锁定大概位置再跳到具体区域精确查找。这样查询成本从O(n)降到了O(log n)级别。别小看这个log同样的千万级数据全表扫描可能要走几百万次磁盘IO走索引树可能只要二十多次磁盘IO。但是这里面有个关键点索引是要付出代价的。它需要额外的磁盘空间去存储树结构每次插入、更新、删除时除了维护数据本身还要维护索引树。这就是典型的时间换空间、空间换查询速度的思路。所以“多建索引一定好”这个想法是完全错误的。1.2 一张表最多能建多少个索引无脑添加不可取MySQL理论上每张表允许建16个索引每个索引最多包含16个列。但这个数字看看就好现实里一张表超过五六个索引写入性能就会肉眼可见地下降。我见过最离谱的情况是有人把一张只有二十几个字段的表建了将近十个索引。理由是“前端啥字段都要排序啥字段都要过滤”结果导入数据时一小时才导完几十万条因为每条插入都要维护十棵索引树每个索引都要做一次或多次磁盘写入。更讽刺的是这些索引里真正被查询用到的也就两三个。剩下那些索引纯粹是在“健身”——锻炼磁盘和CPU。判断索引有没有用最直接的方法是看慢查询日志和EXPLAIN里的实际使用情况而不是拍脑袋觉得“某个字段以后可能要用”。我个人在建索引时有一个习惯一张OLTP表索引数量控制在五个以内能用联合索引解决的绝不多建单列索引。后面章节我会专门讲联合索引的设计思路。2. InnoDB引擎下的索引底层B树、聚簇索引与非聚簇索引2.1 为什么MySQL偏偏选了B树面试八股文的经典问题为什么InnoDB用B树不用二叉树、红黑树或者哈希索引先说结论因为InnoDB的数据是存储在磁盘上的而磁盘IO的代价比内存访问高好几个数量级。所以在设计索引时核心目标不是“比较次数更少”而是“磁盘IO次数更少”。二叉树和红黑树的问题在于树太高。一个亿级数据量的二叉树树高可能接近33层。每读一层节点就要一次磁盘IO那就得读33次磁盘。而B树是“矮胖”结构一个节点能存很多个key同样亿级数据B树的高度通常只有3到4层。这意味着查找一个数据最多3到4次磁盘IO就能定位到目标。B树和B树的区别也是一个高频考点。B树的内节点和叶子节点都存数据而B树的所有数据都放在叶子节点内节点只存索引值。这样做有两个好处内节点不存数据就能存更多的key树自然更矮IO次数更少。B树的叶子节点之间用链表串起来天然支持范围查询和排序。只要找到范围起点沿着链表往后扫就行。B树要实现范围查询就很麻烦每个节点都要回溯。哈希索引的查询速度其实是O(1)比B树的log n还要快。但它只支持等值查询不支持范围查询、排序、前缀匹配。对MySQL这种通用数据库来说范围查询是刚需所以哈希索引只能做辅助比如自适应哈希索引。2.2 聚簇索引与非聚簇索引回表到底在回什么这是索引原理里最容易绕晕的概念但只要搞清楚了索引叶子节点到底存的是啥瞬间就通了。InnoDB的聚簇索引就是把数据行本身放在索引的叶子节点上。一张表只有一个聚簇索引通常就是主键索引。表数据按照主键的顺序物理存储这有点像电话簿——按姓氏拼音排列你找到姓就翻到了对应的联系方式。如果没有显式定义主键InnoDB会找第一个非空唯一索引作为聚簇索引。如果也没有InnoDB会隐藏一个6字节的row_id作为聚簇索引。非聚簇索引也叫二级索引或辅助索引。它的叶子节点存的是索引列的值 主键值不是完整的数据行。所以当你通过二级索引查到数据时先拿到主键值还要再回聚簇索引里查一次完整行这个过程就叫“回表”。举个例子。表结构如下CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50), age INT, INDEX idx_name (name) );当执行SELECT * FROM user WHERE name 张三时MySQL先走idx_name索引找到name为“张三”的叶子节点里面存的是主键值1。然后拿主键1再去聚簇索引里搜找到整行数据返回结果。这一步额外的查找就是回表。回表不是没代价的它意味着至少两次B树搜索。能不能避免能这就是下面要讲的覆盖索引。2.3 辅助索引与覆盖索引用空间换查询速度的关键手段既然二级索引会回表那能不能让查询的字段全部包含在索引里这样压根不需要回表能。这种情况就叫“覆盖索引”。比如把上面那条SQL改成SELECT id, name FROM user WHERE name 张三;id和name这两个字段都在idx_name这个二级索引的叶子节点里。MySQL直接扫索引树就能拿到结果不需要回聚簇索引。从执行计划里看Extra列会显示Using index。覆盖索引的价值不只是省一次查询更关键的是二级索引的叶子节点只存索引列和主键比聚簇索引存整行数据要小得多。同样大小的缓冲池Buffer Pool能装下的索引页更多IO次数自然更少。这也是为什么很多大厂做SQL优化时喜欢用“覆盖索引”去改造慢SQL。但覆盖索引不是万能的。索引列越多索引体积越大写入开销也越高。如果为了覆盖所有查询字段把select里的十几列全塞进索引那插入一条数据的成本会高到怀疑人生。覆盖索引用在高频查询、字段少的SQL上收益最大。3. 索引分类与创建策略从单列索引到联合索引3.1 索引分类大扫盲主键、唯一、普通、全文MySQL索引大致分四类在实际建表建索引时很常用。我做了一个表方便对照索引类型特点常见使用场景主键索引唯一且非空一张表只有一个聚簇索引每张表都应该有主键唯一索引值不能重复允许有多个可以是NULL手机号、身份证号、业务单号普通索引只加速查询不限制值重复高频查询的普通字段全文索引针对文本内容做分词匹配文章内容搜索、长文本LIKE查询主键索引和唯一索引的区别值得多说一句主键是物理层面的排序依据唯一索引是逻辑层面的约束。唯一索引也会自动创建索引树所以像“订单号”“用户手机号”这种需要唯一性约束的业务字段加唯一索引是一举两得——既保证数据不重复又提升查询速度。全文索引在MySQL 5.7之后支持了中文分词插件ngram但真要做复杂全文搜索、相关度排序还是建议上Elasticsearch专门的搜索引擎。MySQL全文索引更适合轻量级场景比如一个小型博客站的文章标题、摘要搜索。3.2 最左前缀原则联合索引的灵魂联合索引是面试高频考点也是实际优化里性价比最高的手段。它遵循最左前缀原则查询条件里必须包含联合索引最左边的列索引才会生效。举个例子。建立联合索引(city, age, name)它实际建立的索引树先按city排序同一city再按age排序同一city且同一age再按name排序。所以下面这条SQL能用到索引SELECT * FROM user WHERE city 北京 AND age 25;因为查询条件从最左边的city开始逐步向右匹配。但如果单独用age或name作为条件比如SELECT * FROM user WHERE age 25;这条SQL用不上联合索引因为索引树的第一层排序是city没有city条件MySQL不知道从哪个city分支找age。那“跳过中间列”行不行比如SELECT * FROM user WHERE city 北京 AND name 张三;这里city能用索引但name用不了。因为索引树的排序规则是同city下按age排序而不是直接按name排序。所以MySQL只是通过city把范围缩小了然后在范围内一条条过滤name。这种情况叫“索引截断”EXPLAIN里能明显看到key_len变小了。设计联合索引时一定把区分度高、最常作为过滤条件的字段放最左边然后逐步考虑排序字段。别把区分度极低的字段比如性别往联合索引前面塞否则索引会臃肿效果也很差。3.3 创建索引的SQL与操作要点创建索引的标准SQL-- 建表时创建 CREATE TABLE user ( id INT PRIMARY KEY, city VARCHAR(20), age INT, name VARCHAR(50), INDEX idx_city_age_name (city, age, name) ); -- 表已存在时添加 ALTER TABLE user ADD INDEX idx_city_age (city, age); -- 删除索引 DROP INDEX idx_city_age ON user;有几个实操要点常年踩坑务必留意第一区分度。区分度越大越好计算公式是COUNT(DISTINCT 字段) / COUNT(*)。如果一个字段100万行只有“男”“女”两个值区分度就是0.000002这种字段建索引收益极低。反过来主键区分度是1索引效果最强。第二字段长度。索引列太长一个数据页能存放的索引条目就少树会变高IO次数增加。比如一个VARCHAR(255)的字段如果业务只需要前10个字符来区分建议使用前缀索引ALTER TABLE user ADD INDEX idx_email_prefix (email(20));第三NULL值。尽量给字段定义NOT NULL。索引列允许NULL时查询条件要用IS NULL或IS NOT NULL索引在部分场景下不可用而且NULL值在索引里处理也更复杂。第四频繁更新的字段不适合建索引。索引列每次更新都涉及索引树调整更新越频繁索引维护成本越高。一张表如果有一个字段每小时被批量更新几十万次给它建索引就是给自己挖坑。4. 索引失效场景与EXPLAIN实战排查4.1 常见索引失效场景与原因速查表索引失效是面试题重灾区也是工作中最让人头疼的问题。我梳理了一份速查表每条都是实际SQL里踩过的坑场景原因正确处理违反最左前缀原则联合索引没从最左列开始调整查询条件或索引顺序对索引列使用函数如WHERE DATE(create_time) 2024-01-01改为create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00隐式类型转换字符串字段和数字比较统一参数类型或字段类型与参数匹配LIKE以%开头无法利用B树有序性尽量避免可考虑全文索引或ESOR连接非索引列只要有一个条件没索引整个语句可能不走索引拆成两个查询用UNION ALL合并对索引列做运算如WHERE id 1 5改为WHERE id 4函数处理和隐式类型转换是最隐蔽的因为SQL语句看起来很正常EXPLAIN出来却是全表扫描。举一个隐式类型转换的经典例子。表里字段是VARCHAR类型存储手机号SQL写成SELECT * FROM user WHERE phone 13812345678;这条SQL里phone是VARCHAR右边是数字MySQL会把phone转成数字再比较。一旦对索引列做了转换索引就失效了。正确写法是给参数加引号SELECT * FROM user WHERE phone 13812345678;4.2 EXPLAIN读法type、key、rows、Extra四个字段怎么组合分析排查索引是否生效EXPLAIN是首选工具。用法很简单EXPLAIN SELECT ... FROM ... WHERE ...;执行结果会返回一列信息重点看四个字段type访问类型从上到下性能从差到好依次是ALL全表扫描、index全索引扫描、range范围扫描、ref非唯一索引等值匹配、eq_ref唯一索引等值匹配、const/system主键或唯一索引定位单行。看到ALL就该警惕多半是索引失效。key实际用到的索引名。如果是NULL说明没走任何索引。rows预计扫描的行数只是个估算值但数量级参考意义很大。同样的SQLrows从一百万降到一千基本就是命中索引了。Extra里最能说明问题的几个值——Using filesort表示需要额外文件排序常见于ORDER BY没走索引。加了合适的联合索引后这个值会消失排序直接在索引顺序上完成。Using temporary用了临时表常见于GROUP BY/去重性能极差。Using index覆盖索引这是理想态。Using where在存储引擎层过滤后再回表或返回。如果是Using where Using index说明用了覆盖索引。4.3 实测案例一条SQL从全表扫描到命中索引的调优记录这是之前线上真实调优过的一条SQL。表结构大概长这样CREATE TABLE order_info ( id BIGINT PRIMARY KEY, user_id BIGINT, order_no VARCHAR(32), status TINYINT, create_time DATETIME );慢查询日志里抓到这条SQL执行耗时接近3秒SELECT * FROM order_info WHERE status 1 ORDER BY create_time DESC LIMIT 20;EXPLAIN结果显示typeALLrows800万Extra里有Using filesort。也就是说这条SQL把800万行全扫了一遍然后在内存里做文件排序最后取20条。这是最典型的“慢但不出错”的SQL。优化思路分两步。第一步先加一个普通索引(status, create_time)因为查询条件是status等值排序是create_time联合索引可以直接覆盖这两个需求。加完索引后EXPLAIN变typerefrows降到几十万Extra里的Using filesort消失了查询时间降到200毫秒左右。第二步进一步压性能。因为语句是SELECT *从二级索引拿到主键后还要回表取整行数据。虽然200毫秒已经可以接受但如果把SELECT字段改成只取必要字段并且在联合索引里加上这些字段就能变成Using index覆盖索引查询时间还能再压一半。这个案例里有一个核心的调优思路索引不光是让WHERE快还要让GROUP BY、ORDER BY、DISTINCT也快。与其追求“查询总是能用索引”不如在设计联合索引时把过滤、排序、分组字段整体塞进一个索引让一条SQL从执行到排序都不产生额外开销。5. 经验心得与常见问题排雷5.1 哪些情况下索引反而会拖慢性能索引不是银弹有些场景下建索引反而是负优化。第一高频写入的大表。像订单流水、日志记录这类每秒写入成千上万行的表每多一个索引就意味着每次插入要多维护一棵B树。索引数量过多时写入耗时成倍增长。这类表通常更看重写入吞吐量索引设计一定要克制。第二超大文本字段。对TEXT、BLOB这种大字段直接建索引索引体积会爆炸而且大多数场景下也不会真的用过这种索引做过滤。真要搜长文本用全文索引或者外部搜索引擎。第三小表。只有几百行数据的表全表扫描一次的成本可能比走索引还低。MySQL优化器碰到这种情况会直接放弃索引选择ALL。这种表建索引纯属浪费磁盘空间。第四低区分度高重复率字段。比如状态字段就“成功/失败/处理中”三种值建索引后MySQL发现扫描大量行才能拿到结果可能干脆就不走了。这个前面提到过是区分度问题。5.2 大批量导入数据时的索引处理技巧有经验的数据开发都有一个共识向一张已有索引的大表批量灌数据先删索引导完再重建速度能快好几倍。原因很简单插入一条数据时如果表上有多个索引MySQL每插一行就要维护一整棵索引树的顺序还要处理分页分裂的额外开销。在高并发导入场景下这个成本会被无限放大。先把索引删掉让数据只按聚簇索引顺序落地插入就是顺序写速度起飞。数据导完后再一次性建索引索引树的构建也比逐条维护高效得多。这套操作有一个前提导入过程中不能有线上查询在跑否则先删索引会导致查询全表扫描。如果条件不允许停服至少要把新表建成无索引状态导入完成后再改名上线。5.3 利用自增主键减少页分裂这也是InnoDB聚簇索引最常见的坑。如果主键是UUID这类随机字符串插入新行时新数据的主键值不是顺序递增的可能落在已有数据区间中间。这样InnoDB需要移动大量数据来为新值腾位置频繁引发“页分裂”不仅导入慢还会产生大量索引碎片占磁盘空间。而自增主键是严格递增的新行总是追加到B树最右侧几乎不触发页分裂。这也是为什么我一直建议InnoDB表默认用自增整型主键哪怕业务上没有合适的自然主键也要加一列自增id。如果非要用UUID当主键可以考虑改成顺序UUID或者用雪花算法生成趋势递增的数字ID都能显著减少页分裂问题。5.4 SHOW INDEX几分钟摸清一张表的索引家底接手一张老表或者排查性能问题时第一件事不是看业务代码而是先看索引长什么样SHOW INDEX FROM order_info;这个命令会列出表上所有索引包括索引名、列名、索引顺序、基数Cardinality等关键信息。基数代表索引列有多少个不同值数值越大代表区分度越好。如果发现某个索引的Cardinality相对于表行数极小那这个索引大概率是在“养老”。总结一句我自己的工作习惯索引设计不是一次性的。业务SQL在变数据规模在涨索引也需要定期用慢查询日志和EXPLAIN做“体检”。回头看看那些线上慢SQL大多数不是SQL写法有多复杂而是索引压根没设计对。把索引原理吃透了再去看优化方案整个思路都会清晰很多。 MySQL索引原理深入：从B+树到慢查询优化与EXPLAIN实战
MySQL索引原理这话题看着像个基础八股文其实里面水挺深。我当年刚带团队时就吃过一次大亏一个运营后台的列表页数据量刚过百万接口直接超时数据库CPU被打满。当时第一反应是加配置、加缓存结果排查下来发现就是一条SQL没走索引全表扫了近一千万行。加了索引之后查询从2.7秒掉到30毫秒连SQL都不用改。那个瞬间我意识到不懂索引原理连排查问题的方向都是错的。这篇内容我会从索引的本质讲起一层层拆到InnoDB的B树结构再结合聚簇索引、回表、索引失效这些高频面试点最后落到EXPLAIN实战和索引运维经验上。不堆概念尽量用大白话和真实场景说清楚。适合刚入门的开发也适合写过一两年SQL但没系统理过索引原理的人。1. MySQL索引到底是什么一次慢查询事故给我的教训1.1 索引不是“优化神药”它本质上是一棵有序树很多人把索引理解成“给字段加个目录”这个类比方向是对的但不够准确。书的目录只是告诉你内容在第几页而MySQL索引要解决的是在海量数据里快速定位到目标行同时还得支持高效的插入、删除和更新。打个比方。你有一本没有页码、也没有目录的字典要在里面找一个字只能从第一页翻到最后一页。如果这本字典有一千页运气好可能翻十页就找到了运气差就得翻九百多页。这种查找方式在数据库里就叫全表扫描。复杂度是O(n)数据量翻倍耗时基本也翻倍。索引做的事情就是给字典加上“拼音目录”和“部首目录”。你不用一页页翻而是先查目录锁定大概位置再跳到具体区域精确查找。这样查询成本从O(n)降到了O(log n)级别。别小看这个log同样的千万级数据全表扫描可能要走几百万次磁盘IO走索引树可能只要二十多次磁盘IO。但是这里面有个关键点索引是要付出代价的。它需要额外的磁盘空间去存储树结构每次插入、更新、删除时除了维护数据本身还要维护索引树。这就是典型的时间换空间、空间换查询速度的思路。所以“多建索引一定好”这个想法是完全错误的。1.2 一张表最多能建多少个索引无脑添加不可取MySQL理论上每张表允许建16个索引每个索引最多包含16个列。但这个数字看看就好现实里一张表超过五六个索引写入性能就会肉眼可见地下降。我见过最离谱的情况是有人把一张只有二十几个字段的表建了将近十个索引。理由是“前端啥字段都要排序啥字段都要过滤”结果导入数据时一小时才导完几十万条因为每条插入都要维护十棵索引树每个索引都要做一次或多次磁盘写入。更讽刺的是这些索引里真正被查询用到的也就两三个。剩下那些索引纯粹是在“健身”——锻炼磁盘和CPU。判断索引有没有用最直接的方法是看慢查询日志和EXPLAIN里的实际使用情况而不是拍脑袋觉得“某个字段以后可能要用”。我个人在建索引时有一个习惯一张OLTP表索引数量控制在五个以内能用联合索引解决的绝不多建单列索引。后面章节我会专门讲联合索引的设计思路。2. InnoDB引擎下的索引底层B树、聚簇索引与非聚簇索引2.1 为什么MySQL偏偏选了B树面试八股文的经典问题为什么InnoDB用B树不用二叉树、红黑树或者哈希索引先说结论因为InnoDB的数据是存储在磁盘上的而磁盘IO的代价比内存访问高好几个数量级。所以在设计索引时核心目标不是“比较次数更少”而是“磁盘IO次数更少”。二叉树和红黑树的问题在于树太高。一个亿级数据量的二叉树树高可能接近33层。每读一层节点就要一次磁盘IO那就得读33次磁盘。而B树是“矮胖”结构一个节点能存很多个key同样亿级数据B树的高度通常只有3到4层。这意味着查找一个数据最多3到4次磁盘IO就能定位到目标。B树和B树的区别也是一个高频考点。B树的内节点和叶子节点都存数据而B树的所有数据都放在叶子节点内节点只存索引值。这样做有两个好处内节点不存数据就能存更多的key树自然更矮IO次数更少。B树的叶子节点之间用链表串起来天然支持范围查询和排序。只要找到范围起点沿着链表往后扫就行。B树要实现范围查询就很麻烦每个节点都要回溯。哈希索引的查询速度其实是O(1)比B树的log n还要快。但它只支持等值查询不支持范围查询、排序、前缀匹配。对MySQL这种通用数据库来说范围查询是刚需所以哈希索引只能做辅助比如自适应哈希索引。2.2 聚簇索引与非聚簇索引回表到底在回什么这是索引原理里最容易绕晕的概念但只要搞清楚了索引叶子节点到底存的是啥瞬间就通了。InnoDB的聚簇索引就是把数据行本身放在索引的叶子节点上。一张表只有一个聚簇索引通常就是主键索引。表数据按照主键的顺序物理存储这有点像电话簿——按姓氏拼音排列你找到姓就翻到了对应的联系方式。如果没有显式定义主键InnoDB会找第一个非空唯一索引作为聚簇索引。如果也没有InnoDB会隐藏一个6字节的row_id作为聚簇索引。非聚簇索引也叫二级索引或辅助索引。它的叶子节点存的是索引列的值 主键值不是完整的数据行。所以当你通过二级索引查到数据时先拿到主键值还要再回聚簇索引里查一次完整行这个过程就叫“回表”。举个例子。表结构如下CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50), age INT, INDEX idx_name (name) );当执行SELECT * FROM user WHERE name 张三时MySQL先走idx_name索引找到name为“张三”的叶子节点里面存的是主键值1。然后拿主键1再去聚簇索引里搜找到整行数据返回结果。这一步额外的查找就是回表。回表不是没代价的它意味着至少两次B树搜索。能不能避免能这就是下面要讲的覆盖索引。2.3 辅助索引与覆盖索引用空间换查询速度的关键手段既然二级索引会回表那能不能让查询的字段全部包含在索引里这样压根不需要回表能。这种情况就叫“覆盖索引”。比如把上面那条SQL改成SELECT id, name FROM user WHERE name 张三;id和name这两个字段都在idx_name这个二级索引的叶子节点里。MySQL直接扫索引树就能拿到结果不需要回聚簇索引。从执行计划里看Extra列会显示Using index。覆盖索引的价值不只是省一次查询更关键的是二级索引的叶子节点只存索引列和主键比聚簇索引存整行数据要小得多。同样大小的缓冲池Buffer Pool能装下的索引页更多IO次数自然更少。这也是为什么很多大厂做SQL优化时喜欢用“覆盖索引”去改造慢SQL。但覆盖索引不是万能的。索引列越多索引体积越大写入开销也越高。如果为了覆盖所有查询字段把select里的十几列全塞进索引那插入一条数据的成本会高到怀疑人生。覆盖索引用在高频查询、字段少的SQL上收益最大。3. 索引分类与创建策略从单列索引到联合索引3.1 索引分类大扫盲主键、唯一、普通、全文MySQL索引大致分四类在实际建表建索引时很常用。我做了一个表方便对照索引类型特点常见使用场景主键索引唯一且非空一张表只有一个聚簇索引每张表都应该有主键唯一索引值不能重复允许有多个可以是NULL手机号、身份证号、业务单号普通索引只加速查询不限制值重复高频查询的普通字段全文索引针对文本内容做分词匹配文章内容搜索、长文本LIKE查询主键索引和唯一索引的区别值得多说一句主键是物理层面的排序依据唯一索引是逻辑层面的约束。唯一索引也会自动创建索引树所以像“订单号”“用户手机号”这种需要唯一性约束的业务字段加唯一索引是一举两得——既保证数据不重复又提升查询速度。全文索引在MySQL 5.7之后支持了中文分词插件ngram但真要做复杂全文搜索、相关度排序还是建议上Elasticsearch专门的搜索引擎。MySQL全文索引更适合轻量级场景比如一个小型博客站的文章标题、摘要搜索。3.2 最左前缀原则联合索引的灵魂联合索引是面试高频考点也是实际优化里性价比最高的手段。它遵循最左前缀原则查询条件里必须包含联合索引最左边的列索引才会生效。举个例子。建立联合索引(city, age, name)它实际建立的索引树先按city排序同一city再按age排序同一city且同一age再按name排序。所以下面这条SQL能用到索引SELECT * FROM user WHERE city 北京 AND age 25;因为查询条件从最左边的city开始逐步向右匹配。但如果单独用age或name作为条件比如SELECT * FROM user WHERE age 25;这条SQL用不上联合索引因为索引树的第一层排序是city没有city条件MySQL不知道从哪个city分支找age。那“跳过中间列”行不行比如SELECT * FROM user WHERE city 北京 AND name 张三;这里city能用索引但name用不了。因为索引树的排序规则是同city下按age排序而不是直接按name排序。所以MySQL只是通过city把范围缩小了然后在范围内一条条过滤name。这种情况叫“索引截断”EXPLAIN里能明显看到key_len变小了。设计联合索引时一定把区分度高、最常作为过滤条件的字段放最左边然后逐步考虑排序字段。别把区分度极低的字段比如性别往联合索引前面塞否则索引会臃肿效果也很差。3.3 创建索引的SQL与操作要点创建索引的标准SQL-- 建表时创建 CREATE TABLE user ( id INT PRIMARY KEY, city VARCHAR(20), age INT, name VARCHAR(50), INDEX idx_city_age_name (city, age, name) ); -- 表已存在时添加 ALTER TABLE user ADD INDEX idx_city_age (city, age); -- 删除索引 DROP INDEX idx_city_age ON user;有几个实操要点常年踩坑务必留意第一区分度。区分度越大越好计算公式是COUNT(DISTINCT 字段) / COUNT(*)。如果一个字段100万行只有“男”“女”两个值区分度就是0.000002这种字段建索引收益极低。反过来主键区分度是1索引效果最强。第二字段长度。索引列太长一个数据页能存放的索引条目就少树会变高IO次数增加。比如一个VARCHAR(255)的字段如果业务只需要前10个字符来区分建议使用前缀索引ALTER TABLE user ADD INDEX idx_email_prefix (email(20));第三NULL值。尽量给字段定义NOT NULL。索引列允许NULL时查询条件要用IS NULL或IS NOT NULL索引在部分场景下不可用而且NULL值在索引里处理也更复杂。第四频繁更新的字段不适合建索引。索引列每次更新都涉及索引树调整更新越频繁索引维护成本越高。一张表如果有一个字段每小时被批量更新几十万次给它建索引就是给自己挖坑。4. 索引失效场景与EXPLAIN实战排查4.1 常见索引失效场景与原因速查表索引失效是面试题重灾区也是工作中最让人头疼的问题。我梳理了一份速查表每条都是实际SQL里踩过的坑场景原因正确处理违反最左前缀原则联合索引没从最左列开始调整查询条件或索引顺序对索引列使用函数如WHERE DATE(create_time) 2024-01-01改为create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00隐式类型转换字符串字段和数字比较统一参数类型或字段类型与参数匹配LIKE以%开头无法利用B树有序性尽量避免可考虑全文索引或ESOR连接非索引列只要有一个条件没索引整个语句可能不走索引拆成两个查询用UNION ALL合并对索引列做运算如WHERE id 1 5改为WHERE id 4函数处理和隐式类型转换是最隐蔽的因为SQL语句看起来很正常EXPLAIN出来却是全表扫描。举一个隐式类型转换的经典例子。表里字段是VARCHAR类型存储手机号SQL写成SELECT * FROM user WHERE phone 13812345678;这条SQL里phone是VARCHAR右边是数字MySQL会把phone转成数字再比较。一旦对索引列做了转换索引就失效了。正确写法是给参数加引号SELECT * FROM user WHERE phone 13812345678;4.2 EXPLAIN读法type、key、rows、Extra四个字段怎么组合分析排查索引是否生效EXPLAIN是首选工具。用法很简单EXPLAIN SELECT ... FROM ... WHERE ...;执行结果会返回一列信息重点看四个字段type访问类型从上到下性能从差到好依次是ALL全表扫描、index全索引扫描、range范围扫描、ref非唯一索引等值匹配、eq_ref唯一索引等值匹配、const/system主键或唯一索引定位单行。看到ALL就该警惕多半是索引失效。key实际用到的索引名。如果是NULL说明没走任何索引。rows预计扫描的行数只是个估算值但数量级参考意义很大。同样的SQLrows从一百万降到一千基本就是命中索引了。Extra里最能说明问题的几个值——Using filesort表示需要额外文件排序常见于ORDER BY没走索引。加了合适的联合索引后这个值会消失排序直接在索引顺序上完成。Using temporary用了临时表常见于GROUP BY/去重性能极差。Using index覆盖索引这是理想态。Using where在存储引擎层过滤后再回表或返回。如果是Using where Using index说明用了覆盖索引。4.3 实测案例一条SQL从全表扫描到命中索引的调优记录这是之前线上真实调优过的一条SQL。表结构大概长这样CREATE TABLE order_info ( id BIGINT PRIMARY KEY, user_id BIGINT, order_no VARCHAR(32), status TINYINT, create_time DATETIME );慢查询日志里抓到这条SQL执行耗时接近3秒SELECT * FROM order_info WHERE status 1 ORDER BY create_time DESC LIMIT 20;EXPLAIN结果显示typeALLrows800万Extra里有Using filesort。也就是说这条SQL把800万行全扫了一遍然后在内存里做文件排序最后取20条。这是最典型的“慢但不出错”的SQL。优化思路分两步。第一步先加一个普通索引(status, create_time)因为查询条件是status等值排序是create_time联合索引可以直接覆盖这两个需求。加完索引后EXPLAIN变typerefrows降到几十万Extra里的Using filesort消失了查询时间降到200毫秒左右。第二步进一步压性能。因为语句是SELECT *从二级索引拿到主键后还要回表取整行数据。虽然200毫秒已经可以接受但如果把SELECT字段改成只取必要字段并且在联合索引里加上这些字段就能变成Using index覆盖索引查询时间还能再压一半。这个案例里有一个核心的调优思路索引不光是让WHERE快还要让GROUP BY、ORDER BY、DISTINCT也快。与其追求“查询总是能用索引”不如在设计联合索引时把过滤、排序、分组字段整体塞进一个索引让一条SQL从执行到排序都不产生额外开销。5. 经验心得与常见问题排雷5.1 哪些情况下索引反而会拖慢性能索引不是银弹有些场景下建索引反而是负优化。第一高频写入的大表。像订单流水、日志记录这类每秒写入成千上万行的表每多一个索引就意味着每次插入要多维护一棵B树。索引数量过多时写入耗时成倍增长。这类表通常更看重写入吞吐量索引设计一定要克制。第二超大文本字段。对TEXT、BLOB这种大字段直接建索引索引体积会爆炸而且大多数场景下也不会真的用过这种索引做过滤。真要搜长文本用全文索引或者外部搜索引擎。第三小表。只有几百行数据的表全表扫描一次的成本可能比走索引还低。MySQL优化器碰到这种情况会直接放弃索引选择ALL。这种表建索引纯属浪费磁盘空间。第四低区分度高重复率字段。比如状态字段就“成功/失败/处理中”三种值建索引后MySQL发现扫描大量行才能拿到结果可能干脆就不走了。这个前面提到过是区分度问题。5.2 大批量导入数据时的索引处理技巧有经验的数据开发都有一个共识向一张已有索引的大表批量灌数据先删索引导完再重建速度能快好几倍。原因很简单插入一条数据时如果表上有多个索引MySQL每插一行就要维护一整棵索引树的顺序还要处理分页分裂的额外开销。在高并发导入场景下这个成本会被无限放大。先把索引删掉让数据只按聚簇索引顺序落地插入就是顺序写速度起飞。数据导完后再一次性建索引索引树的构建也比逐条维护高效得多。这套操作有一个前提导入过程中不能有线上查询在跑否则先删索引会导致查询全表扫描。如果条件不允许停服至少要把新表建成无索引状态导入完成后再改名上线。5.3 利用自增主键减少页分裂这也是InnoDB聚簇索引最常见的坑。如果主键是UUID这类随机字符串插入新行时新数据的主键值不是顺序递增的可能落在已有数据区间中间。这样InnoDB需要移动大量数据来为新值腾位置频繁引发“页分裂”不仅导入慢还会产生大量索引碎片占磁盘空间。而自增主键是严格递增的新行总是追加到B树最右侧几乎不触发页分裂。这也是为什么我一直建议InnoDB表默认用自增整型主键哪怕业务上没有合适的自然主键也要加一列自增id。如果非要用UUID当主键可以考虑改成顺序UUID或者用雪花算法生成趋势递增的数字ID都能显著减少页分裂问题。5.4 SHOW INDEX几分钟摸清一张表的索引家底接手一张老表或者排查性能问题时第一件事不是看业务代码而是先看索引长什么样SHOW INDEX FROM order_info;这个命令会列出表上所有索引包括索引名、列名、索引顺序、基数Cardinality等关键信息。基数代表索引列有多少个不同值数值越大代表区分度越好。如果发现某个索引的Cardinality相对于表行数极小那这个索引大概率是在“养老”。总结一句我自己的工作习惯索引设计不是一次性的。业务SQL在变数据规模在涨索引也需要定期用慢查询日志和EXPLAIN做“体检”。回头看看那些线上慢SQL大多数不是SQL写法有多复杂而是索引压根没设计对。把索引原理吃透了再去看优化方案整个思路都会清晰很多。