做数据库排查这几年我见过最典型的场景就是线上一条SQL跑了几秒开发同学二话不说发来一句“帮我加个索引”。等真把索引加上去要么效果立竿见影要么根本没用要么查询倒是快了写入却被拖垮。其实MySQL创建索引这件事看起来只是一条DDL背后却牵扯到数据结构、字段选择、执行计划、锁竞争一堆问题。这篇文章没有废话直接围绕“MySQL创建索引”把原理、语法、选型、验证和避坑一次讲透。适合正处于入门阶段的后端开发也适合被慢查询折磨的运维测试同学准备面试的朋友同样可以把里面的内容当成一套系统的索引知识提纲。1. 创建索引前先搞懂索引到底解决了什么问题很多人建索引就是“查得慢就加”但如果不理解索引的底层逻辑很容易把索引加错位置甚至加出负优化。所以在动手写CREATE INDEX之前我先花点篇幅把索引的本质说清楚。1.1 没有索引时MySQL是怎么找数据的你可以把InnoDB表想象成一本没有目录的书。查询一条记录时MySQL只能从第一个数据页开始一页一页翻把每一行都拿出来比对条件直到找到目标。这就是全表扫描typeALL。全表扫描的特点有两个一是简单数据量小的时候完全没问题二是当表数据量上了百万、千万级别线性扫描的成本会急剧上升单次查询可能要读几千个数据页IO和CPU都被拉满。这里还要说一个InnoDB的特殊性表数据本身就是按主键顺序组织的一棵聚簇索引树。也就是说如果你不建任何索引MySQL依然会基于主键来组织存储普通查询仍要走主键树的顺序扫描。主键都查不到数据的情况下如果没有二级索引可用那就只能用最原始的全表扫描了。我之前帮人排查过一条慢SQL表只有三十万行按业务订单号查询却要2秒多。原因就是订单号字段上没有索引MySQL老老实实把全表扫了一遍。加上索引后变成毫秒级差别就是这么直观。1.2 B树索引的核心原理为什么能“跳着找”MySQL最常用的索引结构是B树。你可以把B树理解成一套“多层目录”根节点记录索引键的“分界值”非叶子节点逐层缩小范围叶子节点才真正存放数据指针或主键值。由于每一层都会过滤掉大量数据查询路径长度基本就是树的高度一般三到四层就能覆盖千万级数据。这里借用图书馆的类比管理员想知道某本书的位置不需要从第一排书架开始数而是先看分区标签再到对应书架最后精确到编号。B树干的也是这件事。那么问题来了为什么不直接用二叉树或者哈希表二叉树在极端情况下会退化成链表而且每个节点只存一个键树太高、IO次数多哈希表虽然单点查最快但做不了范围查询和排序。B树的优势在于非叶子节点不存数据一页能塞很多索引键树的宽度大、高度低同时叶子节点有序排列天然支持BETWEEN、、、排序等操作。这就是它成为MySQL索引基石的原因。1.3 索引的分类先别急着建得知道建的是哪种MySQL官方把索引分成几类但平时我们用得最多的其实就那几种。我整理了一个表格方便对照索引类型特点典型场景普通索引仅加速查询允许重复值和NULL查询频繁但无唯一性要求的字段唯一索引索引列值唯一允许NULL但只能有一个NULL的约束场景需注意用户手机号、邮箱、身份证等主键索引特殊的唯一索引InnoDB中叶子节点存整行数据每张表必须有且只能有一个主键联合索引多个字段按顺序组成一棵索引树多条件组合查询、覆盖索引优化全文索引基于分词文本的检索适合大文本文章内容、标题搜索空间索引针对地理坐标类型GIS相关应用这里面最需要区分的其实是聚簇索引和二级索引主键索引的叶子节点直接保存整行数据查一次就能拿到所有列二级索引的叶子节点只保存索引列和主键值如果查询列不全在索引里还得拿着主键回聚簇索引再查一次这个过程叫回表。理解这一点后面分析覆盖索引和索引失效就顺理成章了。2. MySQL创建索引的四种方式与字段选择实战语法很简单但“在哪张表、哪个字段、用什么形式建”才是真正考水平的地方。先过语法再讲字段选择方法。2.1 四种创建方式建表、ALTER、CREATE INDEX、约束第一种建表时直接指定索引CREATE TABLE order_info ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_order_no (order_no), UNIQUE KEY uk_user_order (user_id, order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;第二种已经存在的表用ALTER TABLE增加索引ALTER TABLE order_info ADD INDEX idx_status_time (status, create_time);第三种用独立的CREATE INDEX语句CREATE INDEX idx_create_time ON order_info (create_time);第四种比较隐蔽建立唯一约束或主键约束时MySQL会隐式创建唯一索引或主键索引不需要你单独再建。日常开发里建表阶段一起设计好索引最省事但是线上大表加索引我会推荐ALTER TABLE因为语句语义清晰配合后面的在线DDL参数也好控制。2.2 哪些字段值得建索引区分度才是第一指标很多人挑字段凭直觉查询条件里有这个字段就建。这个思路不算错但容易踩坑。真正的判断标准是区分度也就是字段值的离散程度。区分度计算公式很朴素-- 区分度 去重后的值数量 / 总行数 SELECT COUNT(DISTINCT col_name) / COUNT(*) FROM table_name;这个比值越接近1说明字段值越多样索引过滤效果越好。比如订单号基本每行都不同区分度接近1非常适合建唯一索引而状态字段可能只有0、1、2三个值区分度极低单独建索引意义不大更适合放在联合索引的后面。性别字段更典型一张一百万的表性别区分度只有0.002左右单独建索引基本是浪费空间优化器也不一定选它。那长字符串字段怎么办比如存储了一堆很长的URL或备注直接整列建索引会导致索引页占用大、B树变宽变矮的优势被削弱。此时可以建前缀索引只取前N个字符进入索引ALTER TABLE article ADD INDEX idx_summary_prefix (summary(20));N怎么定用不同前缀长度分别算区分度取“区分度接近整列但长度最短”的那个值SELECT COUNT(DISTINCT LEFT(summary, 10)) / COUNT(*) AS p10, COUNT(DISTINCT LEFT(summary, 20)) / COUNT(*) AS p20, COUNT(DISTINCT LEFT(summary, 30)) / COUNT(*) AS p30 FROM article;如果p20已经非常接近全列区分度那20就够了别贪长。2.3 联合索引的字段顺序等值在前范围在后联合索引不是把几个字段随便拼在一起顺序决定了索引能否被高效利用。最左前缀原则说过很多次真正设计时我习惯按两条规则走第一条等值条件的字段放前面范围条件的字段放后面。比如查询是WHERE status 1 AND create_time 2024-01-01那么(status, create_time)就是合理的顺序status先精确过滤create_time再走范围。第二条高区分度的字段放前面。如果两个字段都是等值条件那谁的区分度高谁在前因为B树第一层就能过滤更多数据。有个真实例子某业务表上有两列a和b业务方分别建了两个单列索引查询WHERE a1 AND b2时优化器只能选其中一个索引再去回表过滤效率远不如一个(a,b)联合索引。联合索引的优势在于所有过滤条件都在同一棵索引树上完成判断。3. 索引怎么选普通、唯一还是联合场景对比类型选错同样会埋雷。这一章把选择逻辑讲透顺便回答一个被问烂的问题普通索引和唯一索引到底该用哪个。3.1 普通索引 vs 唯一索引差的不只是约束业务上如果确定字段值不允许重复直接用唯一索引这没什么好纠结的。但如果只是“查询需要”并不需要唯一约束我建议首选普通索引。原因不在查询性能而在写入性能。InnoDB在更新索引页时有一个change buffer机制当目标数据页不在内存里时普通索引可以先只做“记录变更”的缓冲之后再由后台线程合并这样能大幅减少随机磁盘IO。但唯一索引不行——它必须在写入时立刻检查唯一性必须先把数据页读入内存自然也就用不上change buffer。所以高并发写入场景下同一张表普通索引的写入成本通常会低于唯一索引。反过来如果你硬要在一个本该唯一的业务字段上放弃唯一索引那业务层就要自己兜住脏数据风险。我的建议是只要业务语义上要求唯一就建唯一索引别为了那点写入性能牺牲数据正确性如果能允许重复就老老实实建普通索引。3.2 联合索引与覆盖索引回表的成本怎么省联合索引除了能同时过滤多个条件还有另一个隐藏收益覆盖索引。所谓覆盖索引就是查询涉及的所有列都能在索引树上找到MySQL不需要回表。我举个例子-- 表结构里有 idx_user_status (user_id, status) SELECT user_id, status FROM t WHERE user_id 10086;这个查询只需要user_id和status两列而这两列都在索引里执行计划的Extra字段会显示Using index意思是数据直接从索引拿完全不需要回表。对比一下SELECT user_id, status, create_time FROM t WHERE user_id 10086;由于create_time不在索引里MySQL拿到主键后还得回聚簇索引查一次Extra字段通常就变成了Using index condition或干脆没有Using index。回表一次两次无所谓但如果查询返回大量行每行都回表代价就被放大了。设计联合索引时一个常见套路是“将要查询的列都塞进索引里”这段被称为索引覆盖优化。代价是索引会变大写入会更重所以也要克制别为了一两句查询就把整张表的列全塞进索引。3.3 索引与排序ORDER BY 也可以不慢热词里有“mysql排序”这里必须提一嘴B树叶子节点本身有序如果查询的排序字段正好命中索引MySQL就可以利用索引顺序直接返回数据避免 filesort。但ORDER BY想走索引同样受最左前缀约束。举例来说索引是(status, create_time)查询WHERE status 1 ORDER BY create_time DESC是可以走索引的但WHERE create_time 2024-01-01 ORDER BY status就没法完全利用索引因为第一列是范围条件排序字段的索引顺序已经中断了。设计联合索引时把常用来排序的字段放在等值条件字段后面是性价比很高的做法。4. 创建完索引怎么确认它真的生效了很多情况下索引建了不等于会用。MySQL有一个查询优化器它会基于统计信息决定到底走哪个索引甚至决定干脆不走索引。所以创建完索引之后必须学会看执行计划。4.1 EXPLAIN 基本用法与关键列在SQL前面加一个EXPLAINMySQL就会告诉你这条语句打算怎么执行而不会真正执行它EXPLAIN SELECT * FROM order_info WHERE order_no 202401010001;关键字段我逐一解释type访问类型从好到差大致是const、eq_ref、ref、range、index、ALL。看到ALL说明全表扫描这是最需要警惕的。ref和range属于比较健康的范围const表示通过主键或唯一索引精确定位。key实际选用的索引名如果为NULL说明这条语句没有可用索引。key_len索引使用的字节长度。这个值越小越好但也不是越小越好——联合索引里这个值可以帮你判断到底用到了第几列。比如(status, user_id)索引如果取出的key_len只等于status字段的长度说明优化器只用了第一列。rowsMySQL预估需要扫描的行数。这个数字急剧下降说明索引起效果了。Extra包含很多重要提示。Using index表示覆盖索引Using index condition表示使用了索引下推Using filesort表示排序没走索引Using temporary表示使用了临时表通常伴随分组或去重。看执行计划时我有个习惯先看type和key再看rows和Extra四列基本能判断索引设计是否合理。4.2 索引失效的常见写法踩坑清单下面这些场景索引明明是存在的却因为SQL写法问题用不上——每一个都是我实际排查中遇到过的情况。对索引列做函数操作-- 反例create_time 上的索引会失效 WHERE DATE(create_time) 2024-01-01 -- 正例改为范围查询 WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00隐式类型转换最常见的坑是电话号码、订单号这类字符串字段直接跟数字比较-- 反例phone 是 varchar却跟整数比较索引失效 WHERE phone 13812345678 -- 正例写成字符串 WHERE phone 13812345678前导模糊查询-- 反例%abc 导致索引失效 WHERE summary LIKE %abc -- 正例abc% 可以走索引 WHERE summary LIKE abc%OR连接的条件里含非索引列-- 反例idx_a 存在但 c 列没有索引整个 OR 可能全表扫描 WHERE a 1 OR c 2联合索引不满足最左前缀这个也常见。索引是(a,b,c)但查询条件是b1 AND c2这种写法顶多能让b撞上索引的开头实际上索引根本无从用起只能全表扫。!、NOT IN、IS NOT NULL这类否定条件也可能让优化器放弃索引因为它估算扫描范围接近全表。这类场景没有固定解法得看实际数据分布不能一概而论。5. 索引也会“帮倒忙”性能与运维注意事项把索引说得这么神奇但它并不是越多越好。生产环境里“索引过多引发的故障”一点也不比“缺索引”少。5.1 索引过多会造成哪些问题首先每个索引都是一棵B树需要占用磁盘空间。数据量大的表几十个索引吃掉几个GB空间很正常。其次写入变慢。每次插入、更新、删除都需要维护所有二级索引树。一张表索引越多写入放大越严重。有些极端案例里一条UPDATE语句要同时改七八棵索引树事务提交时间直接拉长。第三内存被稀释。InnoDB的缓冲池是有限的索引页占用过多数据页的缓存命中率就会下降反而让查询变慢。第四给优化器添乱。可选的索引太多优化器选错索引的概率也会增加。我见过一张表建了二十多个单列索引结果一条组合查询半天选不出合适索引执行计划奇慢无比。所以我的实践准则是单表索引数量控制在5个左右超过8个就要审视联合索引列数控制在3到4列以内能用联合索引覆盖一组高频查询就不用分别建单列索引。5.2 在线建索引的注意事项与大表实操线上大表加索引怕什么怕锁表、怕主从延迟。MySQL 5.6开始支持在线DDL8.0里很多操作默认使用ALGORITHMINPLACE不再全程锁表但仍有一些需要注意的细节。对于关键业务表我习惯显式写出算法和锁级别ALTER TABLE big_table ADD INDEX idx_user_id (user_id), ALGORITHMINPLACE, LOCKNONE;LOCKNONE表示允许DML并发执行这是最理想的。但要注意如果表上有全文索引或者某些特殊结构可能会降级为LOCKSHARED甚至LOCKEXCLUSIVE。执行前最好先查一下官方文档中对每种操作的算法和锁级别支持情况。对于几十亿行的超大规模表就算LOCKNONE构建索引期间的IO压力、临时排序文件占用磁盘等问题也不能忽视。我的做法是先关注磁盘空间和IO负载再选择业务低峰窗口执行同时用performance_schema监控锁等待和主从延迟。还有一条血的教训ALTER TABLE如果中途失败不仅操作本身要紧还要注意可能残留的临时文件。所以大表操作前务必先备份、先演练最好在测试环境用真实数据量模拟一遍评估耗时。5.3 索引与锁行锁为什么依赖索引热词里有“mysql锁的分类”和索引的关系非常密。InnoDB的行锁是基于索引项实现的不是普普通通“锁住某一行”。这意味着什么如果你的UPDATE或DELETE语句没有走索引InnoDB为了保证数据一致性会在扫描过程中给所有记录加锁表面上就是“一把锁把整张表给锁了”并发性能一落千丈。典型场景是UPDATE order_info SET status 2 WHERE order_no 202401010001;如果order_no上没有索引这条UPDATE会扫全表并且逐行加锁其他事务的写操作全部被堵住。一旦在order_no上建了索引锁的范围就缩小到那一条索引记录对应的行并发立刻恢复。排查死锁时也经常要回到索引上来。很多死锁的根源是两个事务用了不同的索引顺序访问同一批数据或者某个SQL没走索引导致锁范围过大。所以锁问题排查的第一板斧永远是先确认SQL的执行计划。6. 实战复盘一次慢查询的索引优化全过程前面讲了一堆概念这章用一个我处理过的真实案例把流程走一遍。6.1 场景描述订单表查询慢某业务线有个订单表order_info当时数据量大约1200万行。业务反馈一个管理后台页面打开要4秒钟定位到SQL大致形态是SELECT order_no, user_id, amount, create_time FROM order_info WHERE status 1 AND user_id 10086 ORDER BY create_time DESC LIMIT 20;字段的分布情况status只有几个值区分度极低user_id区分度很高create_time是普通时间字段。表上当时只有一个主键索引和idx_user_id。6.2 优化步骤与前后对比先看执行计划发现key只用了idx_user_idrows预估扫描了该用户名下几千条订单然后Using filesort做排序同时因为status、amount不在索引里还有回表。问题很清晰用户ID过滤后仍然有大量记录需要排序和回表慢就慢在这。我的优化方案是建一个联合索引ALTER TABLE order_info ADD INDEX idx_status_user_time (status, user_id, create_time);这个设计有三层意图第一status虽然是低区分度字段但它能先精确过滤出“待处理订单”第二user_id负责二次精确过滤第三create_time作为联合索引的最后一列让ORDER BY create_time DESC直接走索引顺序省掉filesort。优化后再次EXPLAINtype从ref提升到ref并且Extra不再出现Using filesortrows从几千降到了几十。实际接口耗时从4秒降到50毫秒以内。这里有朋友可能会问为什么不把user_id放第一位因为查询条件里status 1是等值条件user_id 10086也是等值单从过滤效果上看user_id区分度更高放前面似乎更优。但如果把create_time放到第二位等值条件中间夹着排序字段联合索引就无法在最后一列上提供有序性。所以这个案例里我优先保住了排序字段的有序性。等值条件中间夹着排序字段联合索引就无法在最后一列上提供有序性。等值条件中间夹着排序字段联合索引就无法在最后一列上提供有序性。这其实是索引设计中一个经典的两难排序受益和过滤受益的优先级需要根据实际SQL决定。在这个场景里status user_id组合已经能把过滤范围压得很小剩下的排序收益就显得更值钱。6.3 复盘与长期建议这个案例给我的长期经验是上线前必须把业务的高频SQL整理出来统一设计索引而不是等慢查询报警了再逐个补。每周看一次慢查询日志把rows扫描量大的SQL标出来用EXPLAIN分析一遍基本能避免大多数性能事故。工具方面MySQL自带的performance_schema和sys库就能拿到很多统计信息不用一开始就上重型监控平台。慢查询日志开启方式也很简单SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time设成1秒低于这个阈值的不记录避免日志量太大。生产环境我建议设成0.5秒配合日志定期分析。最后再说一句我个人的体会索引设计的本质是在读、写、存储之间做平衡。加索引前先问自己三个问题——这个查询是不是高频字段区分度够不够有没有办法用覆盖索引避免回表想清楚这三件事大部分索引问题都能提前避免。至于那些“索引建了却不生效”的怪现象绝大多数都能在SQL写法里找到答案慢慢排查你一定会有收获。 MySQL创建索引：从原理到实战，一文讲透选型与避坑
做数据库排查这几年我见过最典型的场景就是线上一条SQL跑了几秒开发同学二话不说发来一句“帮我加个索引”。等真把索引加上去要么效果立竿见影要么根本没用要么查询倒是快了写入却被拖垮。其实MySQL创建索引这件事看起来只是一条DDL背后却牵扯到数据结构、字段选择、执行计划、锁竞争一堆问题。这篇文章没有废话直接围绕“MySQL创建索引”把原理、语法、选型、验证和避坑一次讲透。适合正处于入门阶段的后端开发也适合被慢查询折磨的运维测试同学准备面试的朋友同样可以把里面的内容当成一套系统的索引知识提纲。1. 创建索引前先搞懂索引到底解决了什么问题很多人建索引就是“查得慢就加”但如果不理解索引的底层逻辑很容易把索引加错位置甚至加出负优化。所以在动手写CREATE INDEX之前我先花点篇幅把索引的本质说清楚。1.1 没有索引时MySQL是怎么找数据的你可以把InnoDB表想象成一本没有目录的书。查询一条记录时MySQL只能从第一个数据页开始一页一页翻把每一行都拿出来比对条件直到找到目标。这就是全表扫描typeALL。全表扫描的特点有两个一是简单数据量小的时候完全没问题二是当表数据量上了百万、千万级别线性扫描的成本会急剧上升单次查询可能要读几千个数据页IO和CPU都被拉满。这里还要说一个InnoDB的特殊性表数据本身就是按主键顺序组织的一棵聚簇索引树。也就是说如果你不建任何索引MySQL依然会基于主键来组织存储普通查询仍要走主键树的顺序扫描。主键都查不到数据的情况下如果没有二级索引可用那就只能用最原始的全表扫描了。我之前帮人排查过一条慢SQL表只有三十万行按业务订单号查询却要2秒多。原因就是订单号字段上没有索引MySQL老老实实把全表扫了一遍。加上索引后变成毫秒级差别就是这么直观。1.2 B树索引的核心原理为什么能“跳着找”MySQL最常用的索引结构是B树。你可以把B树理解成一套“多层目录”根节点记录索引键的“分界值”非叶子节点逐层缩小范围叶子节点才真正存放数据指针或主键值。由于每一层都会过滤掉大量数据查询路径长度基本就是树的高度一般三到四层就能覆盖千万级数据。这里借用图书馆的类比管理员想知道某本书的位置不需要从第一排书架开始数而是先看分区标签再到对应书架最后精确到编号。B树干的也是这件事。那么问题来了为什么不直接用二叉树或者哈希表二叉树在极端情况下会退化成链表而且每个节点只存一个键树太高、IO次数多哈希表虽然单点查最快但做不了范围查询和排序。B树的优势在于非叶子节点不存数据一页能塞很多索引键树的宽度大、高度低同时叶子节点有序排列天然支持BETWEEN、、、排序等操作。这就是它成为MySQL索引基石的原因。1.3 索引的分类先别急着建得知道建的是哪种MySQL官方把索引分成几类但平时我们用得最多的其实就那几种。我整理了一个表格方便对照索引类型特点典型场景普通索引仅加速查询允许重复值和NULL查询频繁但无唯一性要求的字段唯一索引索引列值唯一允许NULL但只能有一个NULL的约束场景需注意用户手机号、邮箱、身份证等主键索引特殊的唯一索引InnoDB中叶子节点存整行数据每张表必须有且只能有一个主键联合索引多个字段按顺序组成一棵索引树多条件组合查询、覆盖索引优化全文索引基于分词文本的检索适合大文本文章内容、标题搜索空间索引针对地理坐标类型GIS相关应用这里面最需要区分的其实是聚簇索引和二级索引主键索引的叶子节点直接保存整行数据查一次就能拿到所有列二级索引的叶子节点只保存索引列和主键值如果查询列不全在索引里还得拿着主键回聚簇索引再查一次这个过程叫回表。理解这一点后面分析覆盖索引和索引失效就顺理成章了。2. MySQL创建索引的四种方式与字段选择实战语法很简单但“在哪张表、哪个字段、用什么形式建”才是真正考水平的地方。先过语法再讲字段选择方法。2.1 四种创建方式建表、ALTER、CREATE INDEX、约束第一种建表时直接指定索引CREATE TABLE order_info ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_order_no (order_no), UNIQUE KEY uk_user_order (user_id, order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;第二种已经存在的表用ALTER TABLE增加索引ALTER TABLE order_info ADD INDEX idx_status_time (status, create_time);第三种用独立的CREATE INDEX语句CREATE INDEX idx_create_time ON order_info (create_time);第四种比较隐蔽建立唯一约束或主键约束时MySQL会隐式创建唯一索引或主键索引不需要你单独再建。日常开发里建表阶段一起设计好索引最省事但是线上大表加索引我会推荐ALTER TABLE因为语句语义清晰配合后面的在线DDL参数也好控制。2.2 哪些字段值得建索引区分度才是第一指标很多人挑字段凭直觉查询条件里有这个字段就建。这个思路不算错但容易踩坑。真正的判断标准是区分度也就是字段值的离散程度。区分度计算公式很朴素-- 区分度 去重后的值数量 / 总行数 SELECT COUNT(DISTINCT col_name) / COUNT(*) FROM table_name;这个比值越接近1说明字段值越多样索引过滤效果越好。比如订单号基本每行都不同区分度接近1非常适合建唯一索引而状态字段可能只有0、1、2三个值区分度极低单独建索引意义不大更适合放在联合索引的后面。性别字段更典型一张一百万的表性别区分度只有0.002左右单独建索引基本是浪费空间优化器也不一定选它。那长字符串字段怎么办比如存储了一堆很长的URL或备注直接整列建索引会导致索引页占用大、B树变宽变矮的优势被削弱。此时可以建前缀索引只取前N个字符进入索引ALTER TABLE article ADD INDEX idx_summary_prefix (summary(20));N怎么定用不同前缀长度分别算区分度取“区分度接近整列但长度最短”的那个值SELECT COUNT(DISTINCT LEFT(summary, 10)) / COUNT(*) AS p10, COUNT(DISTINCT LEFT(summary, 20)) / COUNT(*) AS p20, COUNT(DISTINCT LEFT(summary, 30)) / COUNT(*) AS p30 FROM article;如果p20已经非常接近全列区分度那20就够了别贪长。2.3 联合索引的字段顺序等值在前范围在后联合索引不是把几个字段随便拼在一起顺序决定了索引能否被高效利用。最左前缀原则说过很多次真正设计时我习惯按两条规则走第一条等值条件的字段放前面范围条件的字段放后面。比如查询是WHERE status 1 AND create_time 2024-01-01那么(status, create_time)就是合理的顺序status先精确过滤create_time再走范围。第二条高区分度的字段放前面。如果两个字段都是等值条件那谁的区分度高谁在前因为B树第一层就能过滤更多数据。有个真实例子某业务表上有两列a和b业务方分别建了两个单列索引查询WHERE a1 AND b2时优化器只能选其中一个索引再去回表过滤效率远不如一个(a,b)联合索引。联合索引的优势在于所有过滤条件都在同一棵索引树上完成判断。3. 索引怎么选普通、唯一还是联合场景对比类型选错同样会埋雷。这一章把选择逻辑讲透顺便回答一个被问烂的问题普通索引和唯一索引到底该用哪个。3.1 普通索引 vs 唯一索引差的不只是约束业务上如果确定字段值不允许重复直接用唯一索引这没什么好纠结的。但如果只是“查询需要”并不需要唯一约束我建议首选普通索引。原因不在查询性能而在写入性能。InnoDB在更新索引页时有一个change buffer机制当目标数据页不在内存里时普通索引可以先只做“记录变更”的缓冲之后再由后台线程合并这样能大幅减少随机磁盘IO。但唯一索引不行——它必须在写入时立刻检查唯一性必须先把数据页读入内存自然也就用不上change buffer。所以高并发写入场景下同一张表普通索引的写入成本通常会低于唯一索引。反过来如果你硬要在一个本该唯一的业务字段上放弃唯一索引那业务层就要自己兜住脏数据风险。我的建议是只要业务语义上要求唯一就建唯一索引别为了那点写入性能牺牲数据正确性如果能允许重复就老老实实建普通索引。3.2 联合索引与覆盖索引回表的成本怎么省联合索引除了能同时过滤多个条件还有另一个隐藏收益覆盖索引。所谓覆盖索引就是查询涉及的所有列都能在索引树上找到MySQL不需要回表。我举个例子-- 表结构里有 idx_user_status (user_id, status) SELECT user_id, status FROM t WHERE user_id 10086;这个查询只需要user_id和status两列而这两列都在索引里执行计划的Extra字段会显示Using index意思是数据直接从索引拿完全不需要回表。对比一下SELECT user_id, status, create_time FROM t WHERE user_id 10086;由于create_time不在索引里MySQL拿到主键后还得回聚簇索引查一次Extra字段通常就变成了Using index condition或干脆没有Using index。回表一次两次无所谓但如果查询返回大量行每行都回表代价就被放大了。设计联合索引时一个常见套路是“将要查询的列都塞进索引里”这段被称为索引覆盖优化。代价是索引会变大写入会更重所以也要克制别为了一两句查询就把整张表的列全塞进索引。3.3 索引与排序ORDER BY 也可以不慢热词里有“mysql排序”这里必须提一嘴B树叶子节点本身有序如果查询的排序字段正好命中索引MySQL就可以利用索引顺序直接返回数据避免 filesort。但ORDER BY想走索引同样受最左前缀约束。举例来说索引是(status, create_time)查询WHERE status 1 ORDER BY create_time DESC是可以走索引的但WHERE create_time 2024-01-01 ORDER BY status就没法完全利用索引因为第一列是范围条件排序字段的索引顺序已经中断了。设计联合索引时把常用来排序的字段放在等值条件字段后面是性价比很高的做法。4. 创建完索引怎么确认它真的生效了很多情况下索引建了不等于会用。MySQL有一个查询优化器它会基于统计信息决定到底走哪个索引甚至决定干脆不走索引。所以创建完索引之后必须学会看执行计划。4.1 EXPLAIN 基本用法与关键列在SQL前面加一个EXPLAINMySQL就会告诉你这条语句打算怎么执行而不会真正执行它EXPLAIN SELECT * FROM order_info WHERE order_no 202401010001;关键字段我逐一解释type访问类型从好到差大致是const、eq_ref、ref、range、index、ALL。看到ALL说明全表扫描这是最需要警惕的。ref和range属于比较健康的范围const表示通过主键或唯一索引精确定位。key实际选用的索引名如果为NULL说明这条语句没有可用索引。key_len索引使用的字节长度。这个值越小越好但也不是越小越好——联合索引里这个值可以帮你判断到底用到了第几列。比如(status, user_id)索引如果取出的key_len只等于status字段的长度说明优化器只用了第一列。rowsMySQL预估需要扫描的行数。这个数字急剧下降说明索引起效果了。Extra包含很多重要提示。Using index表示覆盖索引Using index condition表示使用了索引下推Using filesort表示排序没走索引Using temporary表示使用了临时表通常伴随分组或去重。看执行计划时我有个习惯先看type和key再看rows和Extra四列基本能判断索引设计是否合理。4.2 索引失效的常见写法踩坑清单下面这些场景索引明明是存在的却因为SQL写法问题用不上——每一个都是我实际排查中遇到过的情况。对索引列做函数操作-- 反例create_time 上的索引会失效 WHERE DATE(create_time) 2024-01-01 -- 正例改为范围查询 WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00隐式类型转换最常见的坑是电话号码、订单号这类字符串字段直接跟数字比较-- 反例phone 是 varchar却跟整数比较索引失效 WHERE phone 13812345678 -- 正例写成字符串 WHERE phone 13812345678前导模糊查询-- 反例%abc 导致索引失效 WHERE summary LIKE %abc -- 正例abc% 可以走索引 WHERE summary LIKE abc%OR连接的条件里含非索引列-- 反例idx_a 存在但 c 列没有索引整个 OR 可能全表扫描 WHERE a 1 OR c 2联合索引不满足最左前缀这个也常见。索引是(a,b,c)但查询条件是b1 AND c2这种写法顶多能让b撞上索引的开头实际上索引根本无从用起只能全表扫。!、NOT IN、IS NOT NULL这类否定条件也可能让优化器放弃索引因为它估算扫描范围接近全表。这类场景没有固定解法得看实际数据分布不能一概而论。5. 索引也会“帮倒忙”性能与运维注意事项把索引说得这么神奇但它并不是越多越好。生产环境里“索引过多引发的故障”一点也不比“缺索引”少。5.1 索引过多会造成哪些问题首先每个索引都是一棵B树需要占用磁盘空间。数据量大的表几十个索引吃掉几个GB空间很正常。其次写入变慢。每次插入、更新、删除都需要维护所有二级索引树。一张表索引越多写入放大越严重。有些极端案例里一条UPDATE语句要同时改七八棵索引树事务提交时间直接拉长。第三内存被稀释。InnoDB的缓冲池是有限的索引页占用过多数据页的缓存命中率就会下降反而让查询变慢。第四给优化器添乱。可选的索引太多优化器选错索引的概率也会增加。我见过一张表建了二十多个单列索引结果一条组合查询半天选不出合适索引执行计划奇慢无比。所以我的实践准则是单表索引数量控制在5个左右超过8个就要审视联合索引列数控制在3到4列以内能用联合索引覆盖一组高频查询就不用分别建单列索引。5.2 在线建索引的注意事项与大表实操线上大表加索引怕什么怕锁表、怕主从延迟。MySQL 5.6开始支持在线DDL8.0里很多操作默认使用ALGORITHMINPLACE不再全程锁表但仍有一些需要注意的细节。对于关键业务表我习惯显式写出算法和锁级别ALTER TABLE big_table ADD INDEX idx_user_id (user_id), ALGORITHMINPLACE, LOCKNONE;LOCKNONE表示允许DML并发执行这是最理想的。但要注意如果表上有全文索引或者某些特殊结构可能会降级为LOCKSHARED甚至LOCKEXCLUSIVE。执行前最好先查一下官方文档中对每种操作的算法和锁级别支持情况。对于几十亿行的超大规模表就算LOCKNONE构建索引期间的IO压力、临时排序文件占用磁盘等问题也不能忽视。我的做法是先关注磁盘空间和IO负载再选择业务低峰窗口执行同时用performance_schema监控锁等待和主从延迟。还有一条血的教训ALTER TABLE如果中途失败不仅操作本身要紧还要注意可能残留的临时文件。所以大表操作前务必先备份、先演练最好在测试环境用真实数据量模拟一遍评估耗时。5.3 索引与锁行锁为什么依赖索引热词里有“mysql锁的分类”和索引的关系非常密。InnoDB的行锁是基于索引项实现的不是普普通通“锁住某一行”。这意味着什么如果你的UPDATE或DELETE语句没有走索引InnoDB为了保证数据一致性会在扫描过程中给所有记录加锁表面上就是“一把锁把整张表给锁了”并发性能一落千丈。典型场景是UPDATE order_info SET status 2 WHERE order_no 202401010001;如果order_no上没有索引这条UPDATE会扫全表并且逐行加锁其他事务的写操作全部被堵住。一旦在order_no上建了索引锁的范围就缩小到那一条索引记录对应的行并发立刻恢复。排查死锁时也经常要回到索引上来。很多死锁的根源是两个事务用了不同的索引顺序访问同一批数据或者某个SQL没走索引导致锁范围过大。所以锁问题排查的第一板斧永远是先确认SQL的执行计划。6. 实战复盘一次慢查询的索引优化全过程前面讲了一堆概念这章用一个我处理过的真实案例把流程走一遍。6.1 场景描述订单表查询慢某业务线有个订单表order_info当时数据量大约1200万行。业务反馈一个管理后台页面打开要4秒钟定位到SQL大致形态是SELECT order_no, user_id, amount, create_time FROM order_info WHERE status 1 AND user_id 10086 ORDER BY create_time DESC LIMIT 20;字段的分布情况status只有几个值区分度极低user_id区分度很高create_time是普通时间字段。表上当时只有一个主键索引和idx_user_id。6.2 优化步骤与前后对比先看执行计划发现key只用了idx_user_idrows预估扫描了该用户名下几千条订单然后Using filesort做排序同时因为status、amount不在索引里还有回表。问题很清晰用户ID过滤后仍然有大量记录需要排序和回表慢就慢在这。我的优化方案是建一个联合索引ALTER TABLE order_info ADD INDEX idx_status_user_time (status, user_id, create_time);这个设计有三层意图第一status虽然是低区分度字段但它能先精确过滤出“待处理订单”第二user_id负责二次精确过滤第三create_time作为联合索引的最后一列让ORDER BY create_time DESC直接走索引顺序省掉filesort。优化后再次EXPLAINtype从ref提升到ref并且Extra不再出现Using filesortrows从几千降到了几十。实际接口耗时从4秒降到50毫秒以内。这里有朋友可能会问为什么不把user_id放第一位因为查询条件里status 1是等值条件user_id 10086也是等值单从过滤效果上看user_id区分度更高放前面似乎更优。但如果把create_time放到第二位等值条件中间夹着排序字段联合索引就无法在最后一列上提供有序性。所以这个案例里我优先保住了排序字段的有序性。等值条件中间夹着排序字段联合索引就无法在最后一列上提供有序性。等值条件中间夹着排序字段联合索引就无法在最后一列上提供有序性。这其实是索引设计中一个经典的两难排序受益和过滤受益的优先级需要根据实际SQL决定。在这个场景里status user_id组合已经能把过滤范围压得很小剩下的排序收益就显得更值钱。6.3 复盘与长期建议这个案例给我的长期经验是上线前必须把业务的高频SQL整理出来统一设计索引而不是等慢查询报警了再逐个补。每周看一次慢查询日志把rows扫描量大的SQL标出来用EXPLAIN分析一遍基本能避免大多数性能事故。工具方面MySQL自带的performance_schema和sys库就能拿到很多统计信息不用一开始就上重型监控平台。慢查询日志开启方式也很简单SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time设成1秒低于这个阈值的不记录避免日志量太大。生产环境我建议设成0.5秒配合日志定期分析。最后再说一句我个人的体会索引设计的本质是在读、写、存储之间做平衡。加索引前先问自己三个问题——这个查询是不是高频字段区分度够不够有没有办法用覆盖索引避免回表想清楚这三件事大部分索引问题都能提前避免。至于那些“索引建了却不生效”的怪现象绝大多数都能在SQL写法里找到答案慢慢排查你一定会有收获。