尧图网站建设 
先讲个前两天刚处理的线上问题一条订单查询SQL数据量才两百多万行表里明明建了索引可查询还是要将近三秒。EXPLAIN一放出来type是ALL索引完全没走优化器直接全表扫了。加了一行force index之后耗时掉到几十毫秒差距快一百倍。这种问题在MySQL日常开发和运维里太常见了。索引是最基础的性能手段但同时也是最容易用错、最容易翻车的东西。这篇内容我就围绕MySQL索引和性能分析把原理、选型、失效场景、EXPLAIN实操、真实优化案例这些讲透适合正在做MySQL性能调优的开发者、刚接触数据库索引的新人以及准备面试的人希望你看完能少踩几个坑。1. 索引到底为什么能让查询变快1.1 全表扫描的本质是代价太高先想一个最基本的场景一张订单表有100万行没有索引。你要执行SELECT * FROM orders WHERE user_id 12345MySQL只能从第一个数据页开始一页一页读把每一行都翻出来检查user_id。这个动作叫全表扫描。数据是存在磁盘上的读磁盘这件事很贵。哪怕MySQL有Buffer Pool做了缓存冷数据第一次读的随机IO也是毫秒级起步。100万行如果按平均每行200字节算大概200MB数据全表扫一遍的IO成本你自己体感一下就知道了。所以索引存在的第一性原理很简单减少需要扫描的数据量。它像书的目录你要找某一章内容不需要从第一页翻到最后一页直接查目录定位页码就行。MySQL里这个“目录”结构默认是B树。1.2 B树为什么成了默认选择很多新手会问为什么不用哈希索引为什么不用红黑树哈希做单点等值查询确实快O(1)复杂度但MySQL的InnoDB默认用B树核心原因是它同时支持范围查询和排序。哈希一次只能命中一个值做不了WHERE create_time 2024-01-01也做不了ORDER BY create_time。现实业务里范围查询和排序太常见了所以哈希索引一般只用于InnoDB的“自适应哈希索引”这类内部加速场景普通表上的索引默认走B树。红黑树为什么不行它是二叉树树深度和数据量成对数关系。100万行数据树高大约20层每次查询要走20次磁盘IO太深了。B树是“矮胖”结构一个节点可以存很多键值三层到四层就能覆盖千万级数据。比如InnoDB默认页大小16KB如果一个索引键加上指针占32字节一个叶子页大约能放500个键三层B树能覆盖的上限大概是上亿级别实际查询从根节点到叶子节点只要2到3次IO。这个特性才是它能扛住高并发业务的原因。另外有个细节要注意InnoDB的B树叶子节点上存的是整行数据这叫聚簇索引主键就是它的聚簇索引。其他二级索引的叶子节点存的是主键值。所以你用二级索引查一条数据需要先在二级索引B树里找到主键再回到主键索引B树里取整行这个动作叫回表。理解回表是你理解后面覆盖索引、索引失效这些概念的基础。1.3 回表和覆盖索引是性能分析的分水岭回表是二级索引查询的常见损耗。举个具体例子一个用户表CREATE TABLE user (id INT PRIMARY KEY, name VARCHAR(50), age INT, city VARCHAR(50))你在name字段上建了索引。执行SELECT * FROM user WHERE name 张三执行过程是先在name索引树里找到主键id再拿id到主键索引树里取整行。这就是两次B树搜索。如果你把SQL改成SELECT id, name FROM user WHERE name 张三而name索引叶子节点上就有id和name两个字段那MySQL就不用回表了直接从name索引树上拿数据。这种只需要扫描索引树本身就能返回结果的索引叫覆盖索引。覆盖索引几乎是最省IO的查询路径命中它的时候Extra字段会显示Using index。我在实际优化里有个习惯尽量让高频查询落在覆盖索引里。尤其在大宽表、字段特别多的情况下你select的字段越少越容易设计出覆盖索引性能收益也越明显。这一点在后面的优化案例里还会用到。2. 索引的类型怎么选才不会踩坑2.1 先分清主键索引、唯一索引、普通索引索引不是只有一种不同类型的索引在约束力、查询性能和写入代价上都有区别。下面这张表我平时复盘的时候经常用先帮你过一遍索引类型特点约束典型使用场景主键索引每张表一个InnoDB聚簇索引非空唯一每张表的id字段唯一索引值唯一仅约束不聚簇唯一允许NULL业务唯一键如手机号、订单号普通索引只加速查询无约束无高频where条件字段全文索引对文本分词索引无大文本内容的模糊搜索复合索引多个字段组成一个索引无多条件过滤、频繁组合查询唯一索引和普通索引选择上的一个常见误区是为了防重复数据在有很多重复写入的表上建唯一索引。唯一索引每次写入都要做唯一性校验多一轮查找所以写入性能会有一点牺牲。业务上如果确实需要唯一约束那没得选该建就建但如果你只是想让查询快一点千万别顺手加唯一约束用普通索引就够了。主键索引还有一层隐藏作用影响所有二级索引的大小。因为二级索引叶子节点存的是主键值如果你的主键是VARCHAR(64)的UUID那么每一个二级索引的体积都会被拖大写入IO和内存占用都会增加。这也是为什么我一直建议用自增整型主键能短就不要长。2.2 复合索引的字段顺序是性能胜负手复合索引也叫联合索引它的核心规则是“最左前缀原则”。MySQL在一个复合索引里查找数据时只能从最左侧的字段开始连续匹配。比如你建立了idx_user_status (user_id, status)查询条件里如果只有status这个索引是走不了的但如果是user_id或user_id status就能命中。这个特性带了两个实际问题字段顺序怎么排、多建几个单列索引是不是更好。我的经验是字段顺序主要看三条第一区分度高的字段放前面。比如user_id比status的区分度通常高得多条件过滤掉的数据更多索引树遍历的路径更短。第二高频等值查询字段放前面范围查询字段放后面。等值匹配能让索引精确定位范围条件只能锁一段区间放后面能保留更多索引下推和排序的可能性。第三尽量让索引满足多个SQL而不是每个SQL建一个独立索引。否则会出现冗余索引比如你已经建了(user_id, status, create_time)再单独建user_id索引就是重复的浪费磁盘和写入时间。2.3 前缀索引和覆盖索引怎么用才经济字符串字段很长的时候直接建完整字段索引会非常占空间。比如content TEXT或者超长的url VARCHAR(255)你可以只取前N个字符做前缀索引用空间换存储成本。Prefix索引的核心问题是怎么确定N。我通常用一个SQL来估算区分度SELECT COUNT(DISTINCT content) AS full_cnt, COUNT(DISTINCT LEFT(content, 10)) AS prefix_cnt FROM article;然后对比两个数如果prefix_cnt / full_cnt接近1说明前10个字符已经能很好地区分数据。常见经验值是做到95%以上但具体看业务。还要提醒一点前缀索引虽然省空间但它有两个限制。一是它不能用于覆盖索引因为索引里存的不是完整字段值二是它无法支持ORDER BY content这类排序优化。所以前缀索引只适合那种长度超长、又不要求排序的字段。覆盖索引的适用场景则相反它要求你索引里包含的字段能覆盖SELECT和WHERE子句的全部需要。它牺牲一点索引体积换来免除回表IO。这两种策略没有谁绝对好关键是看你的查询模式高频大宽表查询优先覆盖索引超长字段过滤优先前缀索引。3. 索引失效的典型场景性能翻车的重灾区3.1 函数运算和类型转换是隐藏杀手我见过太多线上事故用户反馈“明明有索引查询还是慢”查下来就是WHERE子句里对索引列做了函数计算。比如WHERE DATE(create_time) 2024-06-01你本意是按天查但优化器看到DATE()函数后会觉得无法直接使用索引索引就失效了。正确做法是写成范围WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00这种写法还能让B树做范围扫描效率更高。函数不止DATE包括LEFT(name, 3)、CONCAT(a, b)、ABS(num)这类对索引列做计算的操作全都会让索引失效。另一个隐蔽问题是隐式类型转换。如果字段是VARCHAR类型但SQL入参是整型数字MySQL会尝试自动转换。比如WHERE phone_number 13800138000phone_number是字符串等号右侧是数字优化器会隐式把字符串字段转成数字再做比较结果索引失效。这种问题在字段存手机号、订单号这种纯数字字符串时特别容易发生。检查方法很简单看EXPLAIN的Extra里是不是出现了Using where或者key_len异常偏大再核对一下字段类型。3.2 字符集和排序规则不一致也会破坏索引这一个坑比较隐蔽但一踩就是大坑。两表关联查询时如果A表的字符集是utf8B表是utf8mb4它们的排序规则不一样关联字段做比较时MySQL无法直接利用索引合并或索引嵌套查找往往会退化到全表扫描加额外过滤。我在优化一些老系统时经常遇到这种情况因为早期表用了utf8后来新表默认变成utf8mb4双方一JOIN就出问题。解决方案分两种。如果两表规模不大统一改字符集即可ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;如果表很大直接ALTER会锁表很久可以用工具灰度操作或者临时把关联条件改成显式一致。但长期看统一库表字符集才是根治办法。新库我建议直接全库utf8mb4别再用老utf8能避免一堆莫名其妙的问题。3.3 LIKE、OR、NOT IN和排序场景的实际表现很多接触过索引的人知道LIKE %xxx%这种前后都有通配符的写法用不上索引但会认为LIKE abc%应该能走索引。实际确实可以因为B树的有序性支持范围扫描前缀匹配相当于查一个连续区间。真正让索引失效的是前导通配LIKE %abc它无法确定起始位置。OR条件又是一个容易翻车的地方。WHERE user_id 1 OR status 2就算user_id和status都有单列索引MySQL也可能不会同时用两个索引然后合并而是直接扫全表。早期版本对索引合并优化并不激进优化器评估后可能觉得合并比全表扫还贵。规避方式是拆成两个SQL用UNION ALL或者直接把两个条件合并到复合索引里。注意如果OR连接的是同一字段上的范围匹配比如WHERE id 1 OR id 3优化器通常能正常处理。NOT IN和这类否定条件也偏慢它们本质上是扫描全量再排除。如果数据量大只能全表扫能做的优化很少最多是在逻辑层面改成等值于剩余值集合。ORDER BY排序同样和索引密切相关。B树叶子节点本身就有序如果SQL的排序字段是索引字段且索引扫描方向一致MySQL会直接利用索引顺序Extra显示Using index而不是文件排序Using filesort。一旦排序字段没有配合索引顺序就会触发Using filesort在临时文件里排序数据量大时就非常耗时。这不是索引失效但同样属于索引设计没吃透业务查询的情况。4. 用EXPLAIN做性能分析的完整实操流程4.1 EXPLAIN的输出字段到底怎么看说到性能分析EXPLAIN是绕不开的。很多人知道EXPLAIN SELECT ...但输出结果不会读或者只看type一格这远远不够。我把字段列成一张速查表你在定位问题的时候按这个顺序读字段含义关注重点type访问类型ALL、index、range、ref、eq_ref、const从左到右性能递减possible_keys可能用到的索引如果为NULL说明没索引可用key实际用到的索引与possible_keys对比看优化器选择key_len用到的索引字节长度可以看出复合索引真正用了几个字段rows预估扫描行数数值越大代价越高filtered返回行数与扫描行数比过滤性越差锁定的行越多Extra额外信息Using filesort、Using temporary都是慢查询信号type这一列我重点强调。ALL是最差的全表扫index是扫描整个索引树range是范围扫描ref是非唯一等值匹配eq_ref是唯一索引等值匹配const是主键或唯一索引等值且只有一行。线上核心查询至少要到ref级别如果是范围查询也要到range。看到ALL和index通常就是要优化的信号。4.2 用EXPLAIN定位一条慢SQL的完整过程我拿一个实际的慢SQL演示一下。假设有这样一张表CREATE TABLE user_order ( id INT PRIMARY KEY, user_id INT, status TINYINT, order_time DATETIME, amount DECIMAL(10,2), KEY idx_user_time (user_id, order_time) ) ENGINEInnoDB;某天有个查询EXPLAIN SELECT id, user_id, amount FROM user_order WHERE status 1 ORDER BY order_time DESC;运行EXPLAIN后你很可能会看到这样的结果type为ALLpossible_keys为NULLkey为NULLExtra里出现Using filesort。为什么因为我们只在(user_id, order_time)上建了索引但SQL的条件根本没有user_id只有status从最左前缀原则就知道这个索引用不上只能全表扫排序还要额外做文件排序。这时候性能分析就看两件事第一是否需要加索引第二怎么加。针对这个SQL可以考虑给status建单列索引至少能过滤掉大量不相关行如果你想兼顾排序可以建(status, order_time)复合索引这样等值匹配status后order_time天然有序排序也省了。这是从SQL反推索引的过程。4.3 key_len的计算能揭露索引的真实消耗key_len这个字段不少老手都忽视但它很有用。它表示在索引中实际使用到的字节长度你可以靠它判断复合索引里真正用上了几个字段。按照InnoDB对UTF8MB4字符集的规则来算一个VARCHAR(100)字段的最大字节数是这样100 * 4 400字节加上变长字段长度记录2字节如果字段允许NULL再加1字节总长度一般是403。如果是INT类型固定4字节。DATETIME在MySQL 5.6以后是5字节。举个例子复合索引(user_id INT, status TINYINT, order_time DATETIME)如果EXPLAIN显示key_len为9说明只用了user_id(4) status(1) ...不对。我们来细算INT占4TINYINT占1DATETIME在5.6占5可空标识各加1那是41151 12。如果key_len显示为9大致可以推断只用到了user_id和statusorder_time没进入范围匹配。这样你就能判断优化器是否真的把你设计的复合索引用完整了。顺便说一个很实用的辅助命令EXPLAIN FORMATJSON SELECT ...;JSON格式会输出cost_info和attached_condition等更细的估算信息能看出优化器是基于什么选择走索引或全表扫的。另外SHOW WARNINGS在EXPLAIN之后执行可以看到MySQL对SQL做了哪些改写这个技巧能帮你发现隐式转换和常量折叠这类问题。5. 一个真实优化案例把索引用明白5.1 场景和最初的SQL有次优化一个订单查询接口业务逻辑是根据用户ID查他的待支付订单按下单时间倒序只取前10条。原始SQL如下SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id 5678 AND status 0 AND create_time 2024-05-01 ORDER BY create_time DESC LIMIT 10;orders表当时有约800万行有idx_user (user_id)单列索引也有idx_create_time (create_time)单列索引。结果查询平均耗时在900ms左右接口超时频发。第一眼判断这个SQL条件里有等值字段user_id、status有范围字段create_time还有排序字段create_time。两个单列索引没法同时覆盖三个字段优化器只能二选一然后去回表过滤其他条件。回表量一大性能自然崩。5.2 复合索引怎么设计才对我的优化方式是设计复合索引。关键是字段顺序怎么放。这里我从三条原则推导第一步等值条件放前面。user_id和status都是等值但user_id区分度远高于status所以user_id放在最前status放第二。第二步范围字段放后面。create_time是范围匹配放最后不会破坏前面的等值匹配但如果把create_time放中间比如(user_id, create_time, status)后面的status就只能在索引里做“索引下推”不如把status放在等值区域。第三步排序字段复用索引。查询要求ORDER BY create_time DESC而create_time已经是索引最后一个字段并且前面的等值条件已经固定到唯一值那么B树叶子自然就是按create_time排序的可以顺便避免文件排序。最终建立的索引是ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);建索引的时候还有一个选择是建(user_id, status, create_time)还是(user_id, create_time)我选了三个字段的版本。原因很简单status虽然区分度低但它是等值条件放在中间可以最大化过滤行数在MySQL 8.0的“索引条件下推”特性里status也能参与早期过滤减少回表次数。5.3 优化验证和细节调整建好索引后我重新跑EXPLAINEXPLAIN SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id 5678 AND status 0 AND create_time 2024-05-01 ORDER BY create_time DESC LIMIT 10;结果里type变成了range或refkey显示idx_user_status_timekey_len能覆盖user_id和statusExtra里没有Using filesortrows预估从几百万降到了几百。实际接口耗时从900ms降到了30ms左右效果很明显。但这里还有个隐藏细节值得说。虽然索引已经覆盖了所有过滤和排序字段但SELECT里还有order_no、amount这两个不在索引里的字段。所以即使查到目标行MySQL还是需要回表取完整行。500行合格LIMIT 10限制下回表也就10次问题不大。如果这个查询被调得极其频繁还可以进一步做覆盖索引比如把order_no、amount加进来。但收益要权衡索引更大插入和更新更慢。我在实际中不会无脑扩大索引只在吞吐有压力时才考虑。这次优化因为LIMIT 10回表成本可控所以不做覆盖索引也够了。我在这个案例里还做了个小验证单独用FORMATJSON的EXPLAIN看了cost_info发现优化器的估算扫描行数从原来的70万行变成约800行这直接解释了耗时为何骤降。6. 索引维护中的避坑经验与面试常客6.1 生产环境加索引不是“一条SQL”的事很多新人上来就执行ALTER TABLE big_table ADD INDEX idx_name (col)然后就被锁表问题坑了。MySQL 5.6以后InnoDB支持在线DDL但也不是完全无锁它需要等待一个时间点获取元数据锁长时间持有期依然可能阻塞读写。如果是几百G的大表常规ALTER的风险依然很大。我目前的建议是两条路第一低峰期执行并且用工具监控线程状态和主从延迟。执行前先看Performance Schema或SHOW PROCESSLIST确认没有长事务卡住。第二如果表太大使用gh-ost或pt-online-schema-change这类在线改表工具。原理是创建一张影子表然后同步增量数据最后切换。这个过程不会长时间锁原表但要注意工具对磁盘空间、主从延迟的要求。另外加索引后一定要检查慢查询日志里的相关SQL是否真的变快并且观察一个周期内的写入性能有没有明显恶化。索引不是白来的它占用额外磁盘空间还让INSERT、UPDATE、DELETE变慢因为每次数据变更都要同步更新索引树。6.2 常见问题速查把坑提前堵住我整理了运维和开发中遇到频率最高的几个问题方便你排障时快速对照症状可能原因排查与处理明明有索引但不走索引列使用了函数、隐式转换改写SQL避免对索引列运算LIKE模糊前导通配符导致慢LIKE %keyword%尝试全文索引、ES或限制右侧通配OR条件导致全表扫优化器放弃索引合并拆UNION ALL或复合索引覆盖复合索引只命中一部分违反最左前缀原则把最常用等值字段放最左关联查询特别慢两边字符集或排序规则不一致统一utf8mb4关联字段类型一致ORDER BY触发文件排序索引顺序和排序要求不一致调整复合索引字段顺序或方向主从延迟时加索引大表DDL耗时过长用在线DDL工具低峰执行这个表不是万能药但在排查初期能帮你定位到90%的常见情况。剩下的少数疑难需要结合optimizer_trace去进一步看优化器决策。6.3 性能分析除了EXPLAIN还要联动慢日志索引和性能分析并不只是EXPLAIN的事。线上有条件的话开启慢查询日志是发现索引问题的第一入口SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2;查询慢日志里耗时高的SQL然后逐个EXPLAIN。要批量分析时可以用pt-query-digest这类工具对日志聚合按查询耗时和次数排序找出真正值得优化的Top SQL。有一种情况要注意单次不慢、但次数极多的小查询也可能拖垮数据库。比如某个高频接口每次查询只花10ms但每秒调用1000次那就是每秒10000次数据库交互索引稍不给力CPU和IO都可能成为瓶颈。这种优化要看“总执行次数 × 每次代价”而不是只看单条耗时。我在系统级调优时还会配合调innodb_buffer_pool_size。如果缓冲池太小每次查询都命中冷数据索引B树的优势会被磁盘IO稀释。典型的经验是Buffer Pool设置到总内存的60%到75%左右具体根据业务内存占用调整。这是更大的一个话题但和索引命中率直接相关值得留意。我个人的经验总结一句就是索引设计要贴着业务SQL走先收集真实查询再分析模式最后才建索引不要背下所有规则就到处套。比如最左前缀原则是底线但字段顺序要靠区分度和查询频率来排。踩过几次坑之后我现在做任何索引变更都会先写EXPLAIN的before和after对比用rows和key_len说话。毕竟指标不骗人感觉常骗人。 MySQL索引与性能分析：从B+树原理到EXPLAIN实战优化
先讲个前两天刚处理的线上问题一条订单查询SQL数据量才两百多万行表里明明建了索引可查询还是要将近三秒。EXPLAIN一放出来type是ALL索引完全没走优化器直接全表扫了。加了一行force index之后耗时掉到几十毫秒差距快一百倍。这种问题在MySQL日常开发和运维里太常见了。索引是最基础的性能手段但同时也是最容易用错、最容易翻车的东西。这篇内容我就围绕MySQL索引和性能分析把原理、选型、失效场景、EXPLAIN实操、真实优化案例这些讲透适合正在做MySQL性能调优的开发者、刚接触数据库索引的新人以及准备面试的人希望你看完能少踩几个坑。1. 索引到底为什么能让查询变快1.1 全表扫描的本质是代价太高先想一个最基本的场景一张订单表有100万行没有索引。你要执行SELECT * FROM orders WHERE user_id 12345MySQL只能从第一个数据页开始一页一页读把每一行都翻出来检查user_id。这个动作叫全表扫描。数据是存在磁盘上的读磁盘这件事很贵。哪怕MySQL有Buffer Pool做了缓存冷数据第一次读的随机IO也是毫秒级起步。100万行如果按平均每行200字节算大概200MB数据全表扫一遍的IO成本你自己体感一下就知道了。所以索引存在的第一性原理很简单减少需要扫描的数据量。它像书的目录你要找某一章内容不需要从第一页翻到最后一页直接查目录定位页码就行。MySQL里这个“目录”结构默认是B树。1.2 B树为什么成了默认选择很多新手会问为什么不用哈希索引为什么不用红黑树哈希做单点等值查询确实快O(1)复杂度但MySQL的InnoDB默认用B树核心原因是它同时支持范围查询和排序。哈希一次只能命中一个值做不了WHERE create_time 2024-01-01也做不了ORDER BY create_time。现实业务里范围查询和排序太常见了所以哈希索引一般只用于InnoDB的“自适应哈希索引”这类内部加速场景普通表上的索引默认走B树。红黑树为什么不行它是二叉树树深度和数据量成对数关系。100万行数据树高大约20层每次查询要走20次磁盘IO太深了。B树是“矮胖”结构一个节点可以存很多键值三层到四层就能覆盖千万级数据。比如InnoDB默认页大小16KB如果一个索引键加上指针占32字节一个叶子页大约能放500个键三层B树能覆盖的上限大概是上亿级别实际查询从根节点到叶子节点只要2到3次IO。这个特性才是它能扛住高并发业务的原因。另外有个细节要注意InnoDB的B树叶子节点上存的是整行数据这叫聚簇索引主键就是它的聚簇索引。其他二级索引的叶子节点存的是主键值。所以你用二级索引查一条数据需要先在二级索引B树里找到主键再回到主键索引B树里取整行这个动作叫回表。理解回表是你理解后面覆盖索引、索引失效这些概念的基础。1.3 回表和覆盖索引是性能分析的分水岭回表是二级索引查询的常见损耗。举个具体例子一个用户表CREATE TABLE user (id INT PRIMARY KEY, name VARCHAR(50), age INT, city VARCHAR(50))你在name字段上建了索引。执行SELECT * FROM user WHERE name 张三执行过程是先在name索引树里找到主键id再拿id到主键索引树里取整行。这就是两次B树搜索。如果你把SQL改成SELECT id, name FROM user WHERE name 张三而name索引叶子节点上就有id和name两个字段那MySQL就不用回表了直接从name索引树上拿数据。这种只需要扫描索引树本身就能返回结果的索引叫覆盖索引。覆盖索引几乎是最省IO的查询路径命中它的时候Extra字段会显示Using index。我在实际优化里有个习惯尽量让高频查询落在覆盖索引里。尤其在大宽表、字段特别多的情况下你select的字段越少越容易设计出覆盖索引性能收益也越明显。这一点在后面的优化案例里还会用到。2. 索引的类型怎么选才不会踩坑2.1 先分清主键索引、唯一索引、普通索引索引不是只有一种不同类型的索引在约束力、查询性能和写入代价上都有区别。下面这张表我平时复盘的时候经常用先帮你过一遍索引类型特点约束典型使用场景主键索引每张表一个InnoDB聚簇索引非空唯一每张表的id字段唯一索引值唯一仅约束不聚簇唯一允许NULL业务唯一键如手机号、订单号普通索引只加速查询无约束无高频where条件字段全文索引对文本分词索引无大文本内容的模糊搜索复合索引多个字段组成一个索引无多条件过滤、频繁组合查询唯一索引和普通索引选择上的一个常见误区是为了防重复数据在有很多重复写入的表上建唯一索引。唯一索引每次写入都要做唯一性校验多一轮查找所以写入性能会有一点牺牲。业务上如果确实需要唯一约束那没得选该建就建但如果你只是想让查询快一点千万别顺手加唯一约束用普通索引就够了。主键索引还有一层隐藏作用影响所有二级索引的大小。因为二级索引叶子节点存的是主键值如果你的主键是VARCHAR(64)的UUID那么每一个二级索引的体积都会被拖大写入IO和内存占用都会增加。这也是为什么我一直建议用自增整型主键能短就不要长。2.2 复合索引的字段顺序是性能胜负手复合索引也叫联合索引它的核心规则是“最左前缀原则”。MySQL在一个复合索引里查找数据时只能从最左侧的字段开始连续匹配。比如你建立了idx_user_status (user_id, status)查询条件里如果只有status这个索引是走不了的但如果是user_id或user_id status就能命中。这个特性带了两个实际问题字段顺序怎么排、多建几个单列索引是不是更好。我的经验是字段顺序主要看三条第一区分度高的字段放前面。比如user_id比status的区分度通常高得多条件过滤掉的数据更多索引树遍历的路径更短。第二高频等值查询字段放前面范围查询字段放后面。等值匹配能让索引精确定位范围条件只能锁一段区间放后面能保留更多索引下推和排序的可能性。第三尽量让索引满足多个SQL而不是每个SQL建一个独立索引。否则会出现冗余索引比如你已经建了(user_id, status, create_time)再单独建user_id索引就是重复的浪费磁盘和写入时间。2.3 前缀索引和覆盖索引怎么用才经济字符串字段很长的时候直接建完整字段索引会非常占空间。比如content TEXT或者超长的url VARCHAR(255)你可以只取前N个字符做前缀索引用空间换存储成本。Prefix索引的核心问题是怎么确定N。我通常用一个SQL来估算区分度SELECT COUNT(DISTINCT content) AS full_cnt, COUNT(DISTINCT LEFT(content, 10)) AS prefix_cnt FROM article;然后对比两个数如果prefix_cnt / full_cnt接近1说明前10个字符已经能很好地区分数据。常见经验值是做到95%以上但具体看业务。还要提醒一点前缀索引虽然省空间但它有两个限制。一是它不能用于覆盖索引因为索引里存的不是完整字段值二是它无法支持ORDER BY content这类排序优化。所以前缀索引只适合那种长度超长、又不要求排序的字段。覆盖索引的适用场景则相反它要求你索引里包含的字段能覆盖SELECT和WHERE子句的全部需要。它牺牲一点索引体积换来免除回表IO。这两种策略没有谁绝对好关键是看你的查询模式高频大宽表查询优先覆盖索引超长字段过滤优先前缀索引。3. 索引失效的典型场景性能翻车的重灾区3.1 函数运算和类型转换是隐藏杀手我见过太多线上事故用户反馈“明明有索引查询还是慢”查下来就是WHERE子句里对索引列做了函数计算。比如WHERE DATE(create_time) 2024-06-01你本意是按天查但优化器看到DATE()函数后会觉得无法直接使用索引索引就失效了。正确做法是写成范围WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00这种写法还能让B树做范围扫描效率更高。函数不止DATE包括LEFT(name, 3)、CONCAT(a, b)、ABS(num)这类对索引列做计算的操作全都会让索引失效。另一个隐蔽问题是隐式类型转换。如果字段是VARCHAR类型但SQL入参是整型数字MySQL会尝试自动转换。比如WHERE phone_number 13800138000phone_number是字符串等号右侧是数字优化器会隐式把字符串字段转成数字再做比较结果索引失效。这种问题在字段存手机号、订单号这种纯数字字符串时特别容易发生。检查方法很简单看EXPLAIN的Extra里是不是出现了Using where或者key_len异常偏大再核对一下字段类型。3.2 字符集和排序规则不一致也会破坏索引这一个坑比较隐蔽但一踩就是大坑。两表关联查询时如果A表的字符集是utf8B表是utf8mb4它们的排序规则不一样关联字段做比较时MySQL无法直接利用索引合并或索引嵌套查找往往会退化到全表扫描加额外过滤。我在优化一些老系统时经常遇到这种情况因为早期表用了utf8后来新表默认变成utf8mb4双方一JOIN就出问题。解决方案分两种。如果两表规模不大统一改字符集即可ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;如果表很大直接ALTER会锁表很久可以用工具灰度操作或者临时把关联条件改成显式一致。但长期看统一库表字符集才是根治办法。新库我建议直接全库utf8mb4别再用老utf8能避免一堆莫名其妙的问题。3.3 LIKE、OR、NOT IN和排序场景的实际表现很多接触过索引的人知道LIKE %xxx%这种前后都有通配符的写法用不上索引但会认为LIKE abc%应该能走索引。实际确实可以因为B树的有序性支持范围扫描前缀匹配相当于查一个连续区间。真正让索引失效的是前导通配LIKE %abc它无法确定起始位置。OR条件又是一个容易翻车的地方。WHERE user_id 1 OR status 2就算user_id和status都有单列索引MySQL也可能不会同时用两个索引然后合并而是直接扫全表。早期版本对索引合并优化并不激进优化器评估后可能觉得合并比全表扫还贵。规避方式是拆成两个SQL用UNION ALL或者直接把两个条件合并到复合索引里。注意如果OR连接的是同一字段上的范围匹配比如WHERE id 1 OR id 3优化器通常能正常处理。NOT IN和这类否定条件也偏慢它们本质上是扫描全量再排除。如果数据量大只能全表扫能做的优化很少最多是在逻辑层面改成等值于剩余值集合。ORDER BY排序同样和索引密切相关。B树叶子节点本身就有序如果SQL的排序字段是索引字段且索引扫描方向一致MySQL会直接利用索引顺序Extra显示Using index而不是文件排序Using filesort。一旦排序字段没有配合索引顺序就会触发Using filesort在临时文件里排序数据量大时就非常耗时。这不是索引失效但同样属于索引设计没吃透业务查询的情况。4. 用EXPLAIN做性能分析的完整实操流程4.1 EXPLAIN的输出字段到底怎么看说到性能分析EXPLAIN是绕不开的。很多人知道EXPLAIN SELECT ...但输出结果不会读或者只看type一格这远远不够。我把字段列成一张速查表你在定位问题的时候按这个顺序读字段含义关注重点type访问类型ALL、index、range、ref、eq_ref、const从左到右性能递减possible_keys可能用到的索引如果为NULL说明没索引可用key实际用到的索引与possible_keys对比看优化器选择key_len用到的索引字节长度可以看出复合索引真正用了几个字段rows预估扫描行数数值越大代价越高filtered返回行数与扫描行数比过滤性越差锁定的行越多Extra额外信息Using filesort、Using temporary都是慢查询信号type这一列我重点强调。ALL是最差的全表扫index是扫描整个索引树range是范围扫描ref是非唯一等值匹配eq_ref是唯一索引等值匹配const是主键或唯一索引等值且只有一行。线上核心查询至少要到ref级别如果是范围查询也要到range。看到ALL和index通常就是要优化的信号。4.2 用EXPLAIN定位一条慢SQL的完整过程我拿一个实际的慢SQL演示一下。假设有这样一张表CREATE TABLE user_order ( id INT PRIMARY KEY, user_id INT, status TINYINT, order_time DATETIME, amount DECIMAL(10,2), KEY idx_user_time (user_id, order_time) ) ENGINEInnoDB;某天有个查询EXPLAIN SELECT id, user_id, amount FROM user_order WHERE status 1 ORDER BY order_time DESC;运行EXPLAIN后你很可能会看到这样的结果type为ALLpossible_keys为NULLkey为NULLExtra里出现Using filesort。为什么因为我们只在(user_id, order_time)上建了索引但SQL的条件根本没有user_id只有status从最左前缀原则就知道这个索引用不上只能全表扫排序还要额外做文件排序。这时候性能分析就看两件事第一是否需要加索引第二怎么加。针对这个SQL可以考虑给status建单列索引至少能过滤掉大量不相关行如果你想兼顾排序可以建(status, order_time)复合索引这样等值匹配status后order_time天然有序排序也省了。这是从SQL反推索引的过程。4.3 key_len的计算能揭露索引的真实消耗key_len这个字段不少老手都忽视但它很有用。它表示在索引中实际使用到的字节长度你可以靠它判断复合索引里真正用上了几个字段。按照InnoDB对UTF8MB4字符集的规则来算一个VARCHAR(100)字段的最大字节数是这样100 * 4 400字节加上变长字段长度记录2字节如果字段允许NULL再加1字节总长度一般是403。如果是INT类型固定4字节。DATETIME在MySQL 5.6以后是5字节。举个例子复合索引(user_id INT, status TINYINT, order_time DATETIME)如果EXPLAIN显示key_len为9说明只用了user_id(4) status(1) ...不对。我们来细算INT占4TINYINT占1DATETIME在5.6占5可空标识各加1那是41151 12。如果key_len显示为9大致可以推断只用到了user_id和statusorder_time没进入范围匹配。这样你就能判断优化器是否真的把你设计的复合索引用完整了。顺便说一个很实用的辅助命令EXPLAIN FORMATJSON SELECT ...;JSON格式会输出cost_info和attached_condition等更细的估算信息能看出优化器是基于什么选择走索引或全表扫的。另外SHOW WARNINGS在EXPLAIN之后执行可以看到MySQL对SQL做了哪些改写这个技巧能帮你发现隐式转换和常量折叠这类问题。5. 一个真实优化案例把索引用明白5.1 场景和最初的SQL有次优化一个订单查询接口业务逻辑是根据用户ID查他的待支付订单按下单时间倒序只取前10条。原始SQL如下SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id 5678 AND status 0 AND create_time 2024-05-01 ORDER BY create_time DESC LIMIT 10;orders表当时有约800万行有idx_user (user_id)单列索引也有idx_create_time (create_time)单列索引。结果查询平均耗时在900ms左右接口超时频发。第一眼判断这个SQL条件里有等值字段user_id、status有范围字段create_time还有排序字段create_time。两个单列索引没法同时覆盖三个字段优化器只能二选一然后去回表过滤其他条件。回表量一大性能自然崩。5.2 复合索引怎么设计才对我的优化方式是设计复合索引。关键是字段顺序怎么放。这里我从三条原则推导第一步等值条件放前面。user_id和status都是等值但user_id区分度远高于status所以user_id放在最前status放第二。第二步范围字段放后面。create_time是范围匹配放最后不会破坏前面的等值匹配但如果把create_time放中间比如(user_id, create_time, status)后面的status就只能在索引里做“索引下推”不如把status放在等值区域。第三步排序字段复用索引。查询要求ORDER BY create_time DESC而create_time已经是索引最后一个字段并且前面的等值条件已经固定到唯一值那么B树叶子自然就是按create_time排序的可以顺便避免文件排序。最终建立的索引是ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);建索引的时候还有一个选择是建(user_id, status, create_time)还是(user_id, create_time)我选了三个字段的版本。原因很简单status虽然区分度低但它是等值条件放在中间可以最大化过滤行数在MySQL 8.0的“索引条件下推”特性里status也能参与早期过滤减少回表次数。5.3 优化验证和细节调整建好索引后我重新跑EXPLAINEXPLAIN SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id 5678 AND status 0 AND create_time 2024-05-01 ORDER BY create_time DESC LIMIT 10;结果里type变成了range或refkey显示idx_user_status_timekey_len能覆盖user_id和statusExtra里没有Using filesortrows预估从几百万降到了几百。实际接口耗时从900ms降到了30ms左右效果很明显。但这里还有个隐藏细节值得说。虽然索引已经覆盖了所有过滤和排序字段但SELECT里还有order_no、amount这两个不在索引里的字段。所以即使查到目标行MySQL还是需要回表取完整行。500行合格LIMIT 10限制下回表也就10次问题不大。如果这个查询被调得极其频繁还可以进一步做覆盖索引比如把order_no、amount加进来。但收益要权衡索引更大插入和更新更慢。我在实际中不会无脑扩大索引只在吞吐有压力时才考虑。这次优化因为LIMIT 10回表成本可控所以不做覆盖索引也够了。我在这个案例里还做了个小验证单独用FORMATJSON的EXPLAIN看了cost_info发现优化器的估算扫描行数从原来的70万行变成约800行这直接解释了耗时为何骤降。6. 索引维护中的避坑经验与面试常客6.1 生产环境加索引不是“一条SQL”的事很多新人上来就执行ALTER TABLE big_table ADD INDEX idx_name (col)然后就被锁表问题坑了。MySQL 5.6以后InnoDB支持在线DDL但也不是完全无锁它需要等待一个时间点获取元数据锁长时间持有期依然可能阻塞读写。如果是几百G的大表常规ALTER的风险依然很大。我目前的建议是两条路第一低峰期执行并且用工具监控线程状态和主从延迟。执行前先看Performance Schema或SHOW PROCESSLIST确认没有长事务卡住。第二如果表太大使用gh-ost或pt-online-schema-change这类在线改表工具。原理是创建一张影子表然后同步增量数据最后切换。这个过程不会长时间锁原表但要注意工具对磁盘空间、主从延迟的要求。另外加索引后一定要检查慢查询日志里的相关SQL是否真的变快并且观察一个周期内的写入性能有没有明显恶化。索引不是白来的它占用额外磁盘空间还让INSERT、UPDATE、DELETE变慢因为每次数据变更都要同步更新索引树。6.2 常见问题速查把坑提前堵住我整理了运维和开发中遇到频率最高的几个问题方便你排障时快速对照症状可能原因排查与处理明明有索引但不走索引列使用了函数、隐式转换改写SQL避免对索引列运算LIKE模糊前导通配符导致慢LIKE %keyword%尝试全文索引、ES或限制右侧通配OR条件导致全表扫优化器放弃索引合并拆UNION ALL或复合索引覆盖复合索引只命中一部分违反最左前缀原则把最常用等值字段放最左关联查询特别慢两边字符集或排序规则不一致统一utf8mb4关联字段类型一致ORDER BY触发文件排序索引顺序和排序要求不一致调整复合索引字段顺序或方向主从延迟时加索引大表DDL耗时过长用在线DDL工具低峰执行这个表不是万能药但在排查初期能帮你定位到90%的常见情况。剩下的少数疑难需要结合optimizer_trace去进一步看优化器决策。6.3 性能分析除了EXPLAIN还要联动慢日志索引和性能分析并不只是EXPLAIN的事。线上有条件的话开启慢查询日志是发现索引问题的第一入口SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2;查询慢日志里耗时高的SQL然后逐个EXPLAIN。要批量分析时可以用pt-query-digest这类工具对日志聚合按查询耗时和次数排序找出真正值得优化的Top SQL。有一种情况要注意单次不慢、但次数极多的小查询也可能拖垮数据库。比如某个高频接口每次查询只花10ms但每秒调用1000次那就是每秒10000次数据库交互索引稍不给力CPU和IO都可能成为瓶颈。这种优化要看“总执行次数 × 每次代价”而不是只看单条耗时。我在系统级调优时还会配合调innodb_buffer_pool_size。如果缓冲池太小每次查询都命中冷数据索引B树的优势会被磁盘IO稀释。典型的经验是Buffer Pool设置到总内存的60%到75%左右具体根据业务内存占用调整。这是更大的一个话题但和索引命中率直接相关值得留意。我个人的经验总结一句就是索引设计要贴着业务SQL走先收集真实查询再分析模式最后才建索引不要背下所有规则就到处套。比如最左前缀原则是底线但字段顺序要靠区分度和查询频率来排。踩过几次坑之后我现在做任何索引变更都会先写EXPLAIN的before和after对比用rows和key_len说话。毕竟指标不骗人感觉常骗人。