尧图网站开发 
做MySQL优化的这些年我见过太多人一遇到慢查询就条件反射式地加索引结果有时候快如闪电有时候却毫无变化甚至更慢。标题这个提问“mysql索引优化能改善慢查询吗”答案其实不是简单的“能”或“不能”而是“在正确的前提下能在错误的理解下不能”。这篇文章我会结合自己实际的排查经历从慢查询日志的分析、索引失效的典型场景、执行计划的解读到一次真实索引优化的完整过程把这条链路彻底讲透。无论你是刚入门的新手还是已经写过大量SQL的开发这篇文章都能给你一套可以直接落地的排查方法论。1. 慢查询定位先搞清楚慢在哪里再谈怎么优化1.1 慢查询日志的开启与分析动手优化之前第一件事不是看索引而是先确认慢查询日志到底记录了哪些语句。很多同学在本地开发环境根本不开慢查询日志到了生产环境出了问题就拍脑袋猜这是大忌。开启慢查询日志其实很简单在MySQL配置文件通常是my.cnf或my.ini的[mysqld]段下加入两行slow_query_log 1 slow_query_log_file /data/mysql/log/slow-query.log long_query_time 1 log_queries_not_using_indexes 1这里的long_query_time 1表示超过1秒的SQL会被记录下来建议一开始设置1秒后面可以根据实际情况调整到0.5秒甚至更低。log_queries_not_using_indexes这个参数非常有价值它会把那些没走索引的SQL也记录到慢日志中哪怕执行时间很短——因为在大数据量下这种SQL迟早会拖垮数据库。还有一种临时开启方式不需要重启MySQL直接执行SQLSET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;不过这种方式在MySQL重启后会丢失生产环境还是建议写进配置文件。拿到慢查询日志之后我习惯先用mysqldumpslow这个内置工具做个初步统计mysqldumpslow -s at -t 10 /data/mysql/log/slow-query.log这个命令会按平均执行时间倒序排列前10条最耗时的SQL。注意看返回值里的Rows examined和Rows sent这两个关键指标。如果Rows examined非常大而Rows sent很小意味着这条SQL扫描了大量数据却只返回了少量结果这种场景就是索引优化的重点对象。注意慢查询日志文件会持续增大生产环境一定要配置log_rotate或者定时清理策略否则磁盘被撑爆的后果比慢查询还严重。1.2 定位慢SQL的三板斧执行计划、状态值、Profile拿到慢SQL之后不能直接上去建索引先做三个基础诊断。第一板斧是EXPLAIN。直接在慢SQL前面加上EXPLAIN关键字MySQL会返回一张执行计划表格核心关注这几个字段type连接类型从好到差依次是system const eq_ref ref range index ALL。看到ALL基本就是全表扫描指数优化重点关注的类型。key实际用到的索引名。如果为NULL说明没走索引。rows预估扫描的行数。这个数字越大性能越差。Extra常见值Using where、Using index、Using filesort等。出现Using filesort意味着排序没有用上索引这种场景通常可以通过建立合适的联合索引来消除。第二板斧是SHOW PROFILEMySQL 8.0之后用SHOW PROFILE或performance_schema监控。这个方法用来分析一条SQL内部各阶段的耗时占比SET profiling 1; -- 执行你的慢SQL SHOW PROFILES; SHOW PROFILE FOR QUERY 1;返回结果会列出executing、Sending data、Sorting result等阶段的耗时。如果Sending data占大头说明数据读取和传输是瓶颈如果Sorting result很大那就要重点查ORDER BY相关的字段是否走索引。第三板斧是查看SHOW GLOBAL STATUS里的Handler_read_first、Handler_read_key、Handler_read_rnd_next这几个计数器的变化。比如Handler_read_rnd_next值特别高说明大量的随机读操作典型特征就是没有走索引而进行的全表扫描。这三个诊断做完基本就能确定一条SQL慢的根源是“扫描行数太多”“排序开销太大”还是“回表次数过多”。不同的病因对应不同的索引策略这也是为什么我不建议跳过诊断直接加索引的原因。2. 索引生效与失效为什么你建的索引不生效2.1 联合索引的最左前缀原则联合索引是最容易被误解的知识点之一。很多开发同学在(a, b, c)三个字段上建了个联合索引然后理直气壮地说我已经建索引了为什么查询还是慢。问题往往出在查询条件没有遵守最左前缀原则。所谓最左前缀指的是查询条件必须从联合索引的最左列开始并且不能跳过中间的列。举个例子假设在(user_id, status, create_time)三个字段上建了联合索引idx_user_status_time那么下面这些查询可以利用索引WHERE user_id 1001 WHERE user_id 1001 AND status 1 WHERE user_id 1001 AND status 1 AND create_time 2024-01-01但下面这些查询无法充分利用这个联合索引WHERE status 1 WHERE create_time 2024-01-01 WHERE status 1 AND create_time 2024-01-01一条通俗的类比联合索引就像一本按“姓氏-名字-手机号”排序的电话簿。你直接按“手机号”去查人等于把整本电话簿翻一遍完全用不上排序规则按“姓氏-名字”去查就能快速定位到那一小段。理解了这个原则之后设计联合索引时就要有意识地考虑字段顺序。区分度高的字段、等值查询的字段放在前面范围查询的字段放在后面这能最大程度利用索引树的有序性。2.2 让索引失效的七个典型场景我总结了七种最容易踩的索引失效场景这里全部列出来建议大家收藏自查第一在索引列上使用函数。比如WHERE DATE(create_time) 2024-01-01’哪怕create_time上建了索引也用不上因为MySQL需要先对每一行的create_time执行函数计算后才能比较。正确写法是改成范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02。第二隐式类型转换。当索引列是varchar类型查询条件却传入了数字时MySQL会隐式地把字符串转成数字导致索引失效。比如WHERE phone 13812345678而phone字段是varchar这条SQL就全表扫了。正确写法是WHERE phone 13812345678。第三前导模糊匹配。WHERE name LIKE %张 这种以通配符开头的模糊查询无法使用基于B树的索引。但WHERE name LIKE 张%可以。如果业务确实需要后模糊匹配考虑用全文索引或搜索引擎方案。第四OR连接的条件中有一个字段没有索引。比如WHERE a 1 OR b 2即使a上有索引如果b上没有索引整个查询可能退化为全表扫描。这种情况建议改为UNION或者两边都建立索引。第五索引列参与运算。WHERE num 1 10这种对索引列做算术运算的写法会让索引失效改写为WHERE num 9。第六负向查询。!、、NOT IN、NOT LIKE这类操作符在大多数情况下无法走索引。这跟B树的索引组织方式有关——负向查询意味着需要扫描几乎所有不匹配的节点优化器评估后觉得全表扫更划算。第七数据分布的影响。当优化器评估后认为索引选择性太差或者需要扫描超过表中大约20%到30%的数据时它宁可选择全表扫描也不走索引。比如一个性别字段只有“男”“女”两种值在上面建了索引也可能被优化器放弃。这种场景下索引基本没有意义不做也罢。2.3 回表与覆盖索引的概念理解了回表才能理解覆盖索引的价值。InnoDB的聚簇索引主键索引的叶子节点上存储了整行数据而二级索引普通索引的叶子节点只存储了索引列和主键值。当查询的列不在二级索引中时MySQL需要通过主键值回到聚簇索引中查找完整行记录这个额外操作就叫做回表。回表本身是正常的但如果查询涉及大量数据回表次数多就会产生大量的随机I/O性能自然下降。覆盖索引就是针对这个问题的优化手段——把查询需要的列都包含在索引中此时二级索引的叶子节点已经包含了全部所需数据无需回表。举个例子如果业务上经常执行这条SQLSELECT user_id, status FROM orders WHERE status 1 AND create_time 2024-01-01;那么可以设计联合索引idx_status_time_user(status, create_time, user_id)让查询的所有字段都包含在这个索引中。这样查询直接在索引树上完成数据读取Extra列会显示Using index意味着这是一个覆盖索引扫描性能会明显提升。提示不要无脑把所有查询列都塞进索引。索引也是需要存储空间的写入时也需要更新索引字段越多写入成本越高。设计覆盖索引时要结合真实的业务查询频率来判断高频且简单就能覆盖的查询才值得这么做。3. 一次真实的索引优化实践从2.8秒到12毫秒3.1 场景需求与表结构为了把前面讲的原理串起来我这里分享一个我近期处理的真实案例。某电商业务有一个订单查询页面运营同学反馈打开非常慢接口平均响应时间3秒左右。我抓取了对应的SQL表现如下SELECT order_id, user_id, status, total_amount, pay_time, express_company, express_no FROM t_order WHERE status 2 AND pay_time 2024-06-01 AND pay_time 2024-06-30 ORDER BY pay_time DESC LIMIT 20;执行计划显示type为ALL全表扫描预估rows约48万行Extra中还有Using where和Using filesort。表结构的关键部分这样定义CREATE TABLE t_order ( id bigint(20) NOT NULL AUTO_INCREMENT, order_id varchar(32) NOT NULL COMMENT 订单号, user_id bigint(20) NOT NULL COMMENT 用户ID, status tinyint(4) NOT NULL COMMENT 订单状态1待支付 2已支付 3已发货 4已完成, total_amount decimal(10,2) NOT NULL DEFAULT 0.00, pay_time datetime DEFAULT NULL, express_company varchar(50) DEFAULT NULL, express_no varchar(50) DEFAULT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;可以看到原来的表只有user_id上的索引完全无法支撑这个按status和pay_time过滤的查询。3.2 索引设计过程按照前面讲的诊断方法我分析出这条SQL的核心痛点是全表扫描了48万条数据然后还要在内存中做filesort排序最后只取20条返回。在这里索引需要同时解决过滤和排序两个问题。我设计了联合索引(status, pay_time)并给出设计依据status是等值查询条件放在联合索引最左边匹配最左前缀原则。pay_time在查询中是范围条件同时ORDER BY pay_time DESC复用了索引的排序特性放在status之后过滤出一个相对较小的数据集时已经是有序的Using filesort就会被消除。为什么不需要把order_id、user_id这些查询列也塞进索引因为查询涉及total_amount、express_company、express_no等大字段全部塞进索引会导致索引体积膨胀牺牲写入性能。而且这个查询最终返回数据量只有20条回表成本完全可以接受。我直接通过DDL创建索引生产环境在线执行注意用ALGORITHM和LOCK选项避免长时间锁表ALTER TABLE t_order ADD INDEX idx_status_paytime (status, pay_time), ALGORITHMINPLACE, LOCKNONE;执行完再看执行计划type为refkey用的是idx_status_paytimerows从48万降到约6.3万Extra只剩Using where不再有Using filesort。3.3 优化前后效果对比为了让大家更直观地理解优化效果我做了多轮对比测试。同样在6月的已支付订单里按支付时间倒序取20条执行结果如下指标优化前优化后全表扫描行数约48万约6.3万排序方式Using filesort内存排序索引天然有序无filesort第一次执行耗时2.85秒含冷缓存0.35秒含冷缓存第二次执行耗时1.97秒0.012秒这里有个细节值得关注优化前第二次执行仍有接近2秒的耗时因为全表扫描需要把48万行数据从磁盘加载到内存单纯依赖Buffer Pool缓存效果有限优化后第二次执行耗时降到12毫秒是因为索引树的中间节点被缓存后每次查询只需要沿着索引定位到那一小段数据再回表取20条即可。当然这里我还要主动说明一点这个索引对这个查询是有效的但并不意味着在所有订单状态上都能起同样效果。比如status4已完成的订单如果占据了表中绝大多数数据优化器可能会判断全表扫描更优走索引反而慢。索引优化永远要结合数据分布来评估这也是为什么同样一条SQL在不同业务库中表现会差很多的原因。3.4 配套的SQL改写建议索引建好之后我还顺手改写了这条SQL的一个小问题把pay_time的BETWEEN写法改成半开区间写法规避边界值问题。原来的写法AND pay_time 2024-06-01 AND pay_time 2024-06-30改成AND pay_time 2024-06-01 AND pay_time 2024-07-01这样做的好处是当pay_time字段包含时分秒时BETWEEN或写法会漏掉6月30日当天23:59:59之后的数据而 2024-07-01天然包含整个6月30日最后一秒之前的所有时间点逻辑上更严谨。这个属于SQL书写习惯层面的优化能让索引效果发挥得更稳定。4. 索引之外的性能提升组合拳4.1 SQL改写技巧索引不是万能的。有时候就算有合适的索引SQL写法不讲究性能一样上不去。这里分享几个我在实际工作中高频用到的SQL改写技巧。第一避免使用SELECT *。尽量只查询业务需要的字段一方面减少网络传输数据量另一方面有机会命中覆盖索引。如果某个表有50个字段而你的列表页只需要其中5个SELECT *会让回表概率大增。改写成明确的字段列表后联合索引可能直接覆盖所有查询列Extra中出现Using index性能完全不是一个量级。第二分页深了要改思路。LIMIT 100000, 20这种深分页SQL会让MySQL扫描前100020条然后丢弃前10万条代价非常高。常见的优化方向有两个一是记录上一页最后一条数据的排序字段值用WHERE条件定位下一页比如WHERE id 100000 ORDER BY id DESC LIMIT 20二是利用子查询或JOIN先在索引上定位起始位置再取数据比如先查这20条数据的主键ID再用主键去关联原表取完整数据。第三慎用DISTINCT。DISTINCT本质是去重如果是在大表上查询去重后的结果集开销往往比想象中大得多。先确认业务是否真的需要去重有些场景改用EXISTS或者GROUP BY配合索引效果更好但也要分情况测试没有银弹。4.2 查询条件与索引设计的顺序我遇到过很多“SQL看着没问题但就是慢”的情况最后排查下来原因是查询条件的先后顺序和联合索引的字段顺序不一致导致优化器没能用到最优索引。这里分享一条设计原则。当一条SQL有多个等值条件和范围条件混合时等值条件对应字段放在联合索引靠前的位置范围条件对应字段放在后面。为什么因为等值条件可以精确定位到某一段连续的索引区间范围条件只能缩小扫描范围但无法让每一层索引都精确定位。如果把范围条件放在前面联合索引在范围之后的字段排序意义就大打折扣了。举个例子索引设计为(user_id, status, pay_time)那么查询WHERE status 2 AND pay_time 2024-06-01 AND user_id 1001这种写法虽然逻辑上结果一样但优化器在选择索引时可能不会把这个查询匹配到最优的索引路径上。最好把查询条件也写成user_id 1001 AND status 2 AND pay_time 2024-06-01保证等值条件在前、范围条件在后让优化器能够直接命中联合索引的最优前缀。4.3 硬件与配置层面的辅助手段索引优化是性能提升的核心手段但绝不是唯一手段。当一条SQL已经充分使用索引、行数扫描极少时如果仍然慢就要从更底层的层面找原因了。最常见的是Buffer Pool配置。InnoDB的Buffer Pool用于缓存数据页和索引页。如果设置过小索引页被频繁淘汰每次查询都要产生磁盘I/O性能自然上不去。一个经验值是Buffer Pool设为机器物理内存的60%到80%但具体要看机器是专用数据库还是混部部署。还有一个很容易被忽略的参数是innodb_buffer_pool_instances。在MySQL 5.7及之后版本如果Buffer Pool总大小超过1GB建议调整为多个实例通常设为8减少并发场景下的锁竞争。8.0版本还有一个innodb_buffer_pool_chunk_size参数可以调整内存分配粒度但这些偏底层的参数调整需要长时间观察和压测不建议一上来就动。另外查询缓存Query Cache这个特性在MySQL 8.0已经被移除了如果你的版本还开着它建议关闭因为它在多写场景下维护缓存本身的锁竞争开销会拖慢整体性能。5. 常见问题与排查技巧实录5.1 慢查询排查问题速查表我整理了一份平时排查慢查询时的高频问题对照表直接对着排查效率会高很多。典型症状可能原因排查方向执行计划显示typeALL无索引或索引被放弃先看过滤条件的字段是否有索引检查数据分布确认优化器为什么放弃索引keynull但明明建了索引索引列上使用了函数/隐式转换/前导模糊检查WHERE条件写法按前面列的失效场景逐个对照Extra出现Using filesortORDER BY字段不在索引中或顺序不匹配设计联合索引时把排序字段包含进来注意排序方向和索引方向一致有索引但rows依然很大索引区分度低或范围条件范围过大查看字段基数可能是索引设计不合理考虑覆盖索引或改写查询单条SQL执行很快但接口很慢频繁连接数据库/多次查询N1用数据库慢日志不一定能抓到问题需要排查应用层是否循环调用SQL偶尔慢但大部分时间快缓存淘汰冷数据加载观察第一次执行和第二次执行耗时差异判断Buffer Pool命中率这张表是我踩了无数坑之后总结出来的。碰到一张新的慢查询我基本按这个框架去推导大多数问题在十分钟内就能定位到根因。5.2 一个容易忽略的坑隐式字符集不一致最后分享一个比较隐蔽的坑。两个表关联查询其中一个表的字段是utf8mb4字符集另一个表是utf8字符集关联字段如果用户名字符串MySQL在做表连接时需要在内存中做字符集转换这个转换过程可能导致关联字段上的索引失效。有一次我在排一个多表JOIN慢查询时单表EXPLAIN都正常但整个JOIN的执行计划里驱动表直接全表扫了。排查半天最后发现是关联字段字符集不一致。解决办法也很简单把两张表的关联字段统一成utf8mb4然后重新建索引问题就消失了。这类字符集问题在生产环境中并不少见尤其是老系统升级或不同模块交接的场景。如果排查索引没问题但查询仍然慢记得检查一下表结构里的CHARSET和COLLATE配置是否一致。再补充一个容易被忽略的细节使用前缀索引时要注意区分度。比如对varchar(200)的URL字段建前缀索引如果只取前10个字符区分度可能非常低优化器很可能放弃索引。我习惯用一条件SQL来测试不同前缀长度的区分度SELECT COUNT(DISTINCT LEFT(url, 10)) AS prefix10, COUNT(DISTINCT LEFT(url, 20)) AS prefix20, COUNT(DISTINCT url) AS full_count FROM t_url;通过对比prefix10和full_count的比值选择区分度能够达到80%以上同时又尽量短的长度作为前缀索引长度这样既能减小索引体积又不至于丧失选择性。5.3 索引过多也是灾难还有一个必须提醒的误区索引不是越多越好。每多一个索引INSERT、UPDATE、DELETE时的索引维护成本都会增加。如果一张表有10个索引写入一条记录就要维护10棵索引树在高并发写入场景下性能下降非常明显。我给一个建议表上索引总数尽量控制在5个以内超过这个数就要审视是否有冗余索引。举个常见的冗余场景你已经有了(a, b)联合索引又单独建了a字段的单列索引这时a单列索引就是完全冗余的因为(a, b)联合索引天然支持只按a查询的场景。可以用下面的SQL查询冗余索引通过比较索引的字段前缀来识别SELECT s1.TABLE_NAME, s1.INDEX_NAME, GROUP_CONCAT(s1.COLUMN_NAME ORDER BY s1.SEQ_IN_INDEX) AS index_columns FROM information_schema.STATISTICS s1 GROUP BY s1.TABLE_NAME, s1.INDEX_NAME ORDER BY s1.TABLE_NAME, index_columns;拿到这张索引清单后人工对比前缀一样的索引组合把重复的删掉即可。上线前先在测试环境观察删除索引后的执行计划变化确保没有查询因为删了冗余索引而走全表扫。我在实际项目中见过一张表最多堆了16个索引的情况删掉6个冗余索引后写入性能提升了将近30%而查询性能完全不受影响。性能优化有时候是减法不是加法。做MySQL性能优化这几年我最深的一个体会是索引优化的核心不是“会不会写CREATE INDEX”而是“能不能读懂一条SQL的执行计划”。你建的每个索引都应该有明确的依据要么减少了扫描行数要么消除了文件排序要么实现了覆盖索引。如果连执行计划都懒得看加了索引也只是碰运气。希望这篇文章能把慢查询排查的完整路径讲清楚——从慢日志定位到执行计划分析再到索引设计落地最后用实际数据验证效果。下次再碰到慢查询别急着加索引先按这套流程走一遍你会发现很多问题的答案自己就浮出水面了。 
做MySQL优化的这些年我见过太多人一遇到慢查询就条件反射式地加索引结果有时候快如闪电有时候却毫无变化甚至更慢。标题这个提问“mysql索引优化能改善慢查询吗”答案其实不是简单的“能”或“不能”而是“在正确的前提下能在错误的理解下不能”。这篇文章我会结合自己实际的排查经历从慢查询日志的分析、索引失效的典型场景、执行计划的解读到一次真实索引优化的完整过程把这条链路彻底讲透。无论你是刚入门的新手还是已经写过大量SQL的开发这篇文章都能给你一套可以直接落地的排查方法论。1. 慢查询定位先搞清楚慢在哪里再谈怎么优化1.1 慢查询日志的开启与分析动手优化之前第一件事不是看索引而是先确认慢查询日志到底记录了哪些语句。很多同学在本地开发环境根本不开慢查询日志到了生产环境出了问题就拍脑袋猜这是大忌。开启慢查询日志其实很简单在MySQL配置文件通常是my.cnf或my.ini的[mysqld]段下加入两行slow_query_log 1 slow_query_log_file /data/mysql/log/slow-query.log long_query_time 1 log_queries_not_using_indexes 1这里的long_query_time 1表示超过1秒的SQL会被记录下来建议一开始设置1秒后面可以根据实际情况调整到0.5秒甚至更低。log_queries_not_using_indexes这个参数非常有价值它会把那些没走索引的SQL也记录到慢日志中哪怕执行时间很短——因为在大数据量下这种SQL迟早会拖垮数据库。还有一种临时开启方式不需要重启MySQL直接执行SQLSET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;不过这种方式在MySQL重启后会丢失生产环境还是建议写进配置文件。拿到慢查询日志之后我习惯先用mysqldumpslow这个内置工具做个初步统计mysqldumpslow -s at -t 10 /data/mysql/log/slow-query.log这个命令会按平均执行时间倒序排列前10条最耗时的SQL。注意看返回值里的Rows examined和Rows sent这两个关键指标。如果Rows examined非常大而Rows sent很小意味着这条SQL扫描了大量数据却只返回了少量结果这种场景就是索引优化的重点对象。注意慢查询日志文件会持续增大生产环境一定要配置log_rotate或者定时清理策略否则磁盘被撑爆的后果比慢查询还严重。1.2 定位慢SQL的三板斧执行计划、状态值、Profile拿到慢SQL之后不能直接上去建索引先做三个基础诊断。第一板斧是EXPLAIN。直接在慢SQL前面加上EXPLAIN关键字MySQL会返回一张执行计划表格核心关注这几个字段type连接类型从好到差依次是system const eq_ref ref range index ALL。看到ALL基本就是全表扫描指数优化重点关注的类型。key实际用到的索引名。如果为NULL说明没走索引。rows预估扫描的行数。这个数字越大性能越差。Extra常见值Using where、Using index、Using filesort等。出现Using filesort意味着排序没有用上索引这种场景通常可以通过建立合适的联合索引来消除。第二板斧是SHOW PROFILEMySQL 8.0之后用SHOW PROFILE或performance_schema监控。这个方法用来分析一条SQL内部各阶段的耗时占比SET profiling 1; -- 执行你的慢SQL SHOW PROFILES; SHOW PROFILE FOR QUERY 1;返回结果会列出executing、Sending data、Sorting result等阶段的耗时。如果Sending data占大头说明数据读取和传输是瓶颈如果Sorting result很大那就要重点查ORDER BY相关的字段是否走索引。第三板斧是查看SHOW GLOBAL STATUS里的Handler_read_first、Handler_read_key、Handler_read_rnd_next这几个计数器的变化。比如Handler_read_rnd_next值特别高说明大量的随机读操作典型特征就是没有走索引而进行的全表扫描。这三个诊断做完基本就能确定一条SQL慢的根源是“扫描行数太多”“排序开销太大”还是“回表次数过多”。不同的病因对应不同的索引策略这也是为什么我不建议跳过诊断直接加索引的原因。2. 索引生效与失效为什么你建的索引不生效2.1 联合索引的最左前缀原则联合索引是最容易被误解的知识点之一。很多开发同学在(a, b, c)三个字段上建了个联合索引然后理直气壮地说我已经建索引了为什么查询还是慢。问题往往出在查询条件没有遵守最左前缀原则。所谓最左前缀指的是查询条件必须从联合索引的最左列开始并且不能跳过中间的列。举个例子假设在(user_id, status, create_time)三个字段上建了联合索引idx_user_status_time那么下面这些查询可以利用索引WHERE user_id 1001 WHERE user_id 1001 AND status 1 WHERE user_id 1001 AND status 1 AND create_time 2024-01-01但下面这些查询无法充分利用这个联合索引WHERE status 1 WHERE create_time 2024-01-01 WHERE status 1 AND create_time 2024-01-01一条通俗的类比联合索引就像一本按“姓氏-名字-手机号”排序的电话簿。你直接按“手机号”去查人等于把整本电话簿翻一遍完全用不上排序规则按“姓氏-名字”去查就能快速定位到那一小段。理解了这个原则之后设计联合索引时就要有意识地考虑字段顺序。区分度高的字段、等值查询的字段放在前面范围查询的字段放在后面这能最大程度利用索引树的有序性。2.2 让索引失效的七个典型场景我总结了七种最容易踩的索引失效场景这里全部列出来建议大家收藏自查第一在索引列上使用函数。比如WHERE DATE(create_time) 2024-01-01’哪怕create_time上建了索引也用不上因为MySQL需要先对每一行的create_time执行函数计算后才能比较。正确写法是改成范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02。第二隐式类型转换。当索引列是varchar类型查询条件却传入了数字时MySQL会隐式地把字符串转成数字导致索引失效。比如WHERE phone 13812345678而phone字段是varchar这条SQL就全表扫了。正确写法是WHERE phone 13812345678。第三前导模糊匹配。WHERE name LIKE %张 这种以通配符开头的模糊查询无法使用基于B树的索引。但WHERE name LIKE 张%可以。如果业务确实需要后模糊匹配考虑用全文索引或搜索引擎方案。第四OR连接的条件中有一个字段没有索引。比如WHERE a 1 OR b 2即使a上有索引如果b上没有索引整个查询可能退化为全表扫描。这种情况建议改为UNION或者两边都建立索引。第五索引列参与运算。WHERE num 1 10这种对索引列做算术运算的写法会让索引失效改写为WHERE num 9。第六负向查询。!、、NOT IN、NOT LIKE这类操作符在大多数情况下无法走索引。这跟B树的索引组织方式有关——负向查询意味着需要扫描几乎所有不匹配的节点优化器评估后觉得全表扫更划算。第七数据分布的影响。当优化器评估后认为索引选择性太差或者需要扫描超过表中大约20%到30%的数据时它宁可选择全表扫描也不走索引。比如一个性别字段只有“男”“女”两种值在上面建了索引也可能被优化器放弃。这种场景下索引基本没有意义不做也罢。2.3 回表与覆盖索引的概念理解了回表才能理解覆盖索引的价值。InnoDB的聚簇索引主键索引的叶子节点上存储了整行数据而二级索引普通索引的叶子节点只存储了索引列和主键值。当查询的列不在二级索引中时MySQL需要通过主键值回到聚簇索引中查找完整行记录这个额外操作就叫做回表。回表本身是正常的但如果查询涉及大量数据回表次数多就会产生大量的随机I/O性能自然下降。覆盖索引就是针对这个问题的优化手段——把查询需要的列都包含在索引中此时二级索引的叶子节点已经包含了全部所需数据无需回表。举个例子如果业务上经常执行这条SQLSELECT user_id, status FROM orders WHERE status 1 AND create_time 2024-01-01;那么可以设计联合索引idx_status_time_user(status, create_time, user_id)让查询的所有字段都包含在这个索引中。这样查询直接在索引树上完成数据读取Extra列会显示Using index意味着这是一个覆盖索引扫描性能会明显提升。提示不要无脑把所有查询列都塞进索引。索引也是需要存储空间的写入时也需要更新索引字段越多写入成本越高。设计覆盖索引时要结合真实的业务查询频率来判断高频且简单就能覆盖的查询才值得这么做。3. 一次真实的索引优化实践从2.8秒到12毫秒3.1 场景需求与表结构为了把前面讲的原理串起来我这里分享一个我近期处理的真实案例。某电商业务有一个订单查询页面运营同学反馈打开非常慢接口平均响应时间3秒左右。我抓取了对应的SQL表现如下SELECT order_id, user_id, status, total_amount, pay_time, express_company, express_no FROM t_order WHERE status 2 AND pay_time 2024-06-01 AND pay_time 2024-06-30 ORDER BY pay_time DESC LIMIT 20;执行计划显示type为ALL全表扫描预估rows约48万行Extra中还有Using where和Using filesort。表结构的关键部分这样定义CREATE TABLE t_order ( id bigint(20) NOT NULL AUTO_INCREMENT, order_id varchar(32) NOT NULL COMMENT 订单号, user_id bigint(20) NOT NULL COMMENT 用户ID, status tinyint(4) NOT NULL COMMENT 订单状态1待支付 2已支付 3已发货 4已完成, total_amount decimal(10,2) NOT NULL DEFAULT 0.00, pay_time datetime DEFAULT NULL, express_company varchar(50) DEFAULT NULL, express_no varchar(50) DEFAULT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;可以看到原来的表只有user_id上的索引完全无法支撑这个按status和pay_time过滤的查询。3.2 索引设计过程按照前面讲的诊断方法我分析出这条SQL的核心痛点是全表扫描了48万条数据然后还要在内存中做filesort排序最后只取20条返回。在这里索引需要同时解决过滤和排序两个问题。我设计了联合索引(status, pay_time)并给出设计依据status是等值查询条件放在联合索引最左边匹配最左前缀原则。pay_time在查询中是范围条件同时ORDER BY pay_time DESC复用了索引的排序特性放在status之后过滤出一个相对较小的数据集时已经是有序的Using filesort就会被消除。为什么不需要把order_id、user_id这些查询列也塞进索引因为查询涉及total_amount、express_company、express_no等大字段全部塞进索引会导致索引体积膨胀牺牲写入性能。而且这个查询最终返回数据量只有20条回表成本完全可以接受。我直接通过DDL创建索引生产环境在线执行注意用ALGORITHM和LOCK选项避免长时间锁表ALTER TABLE t_order ADD INDEX idx_status_paytime (status, pay_time), ALGORITHMINPLACE, LOCKNONE;执行完再看执行计划type为refkey用的是idx_status_paytimerows从48万降到约6.3万Extra只剩Using where不再有Using filesort。3.3 优化前后效果对比为了让大家更直观地理解优化效果我做了多轮对比测试。同样在6月的已支付订单里按支付时间倒序取20条执行结果如下指标优化前优化后全表扫描行数约48万约6.3万排序方式Using filesort内存排序索引天然有序无filesort第一次执行耗时2.85秒含冷缓存0.35秒含冷缓存第二次执行耗时1.97秒0.012秒这里有个细节值得关注优化前第二次执行仍有接近2秒的耗时因为全表扫描需要把48万行数据从磁盘加载到内存单纯依赖Buffer Pool缓存效果有限优化后第二次执行耗时降到12毫秒是因为索引树的中间节点被缓存后每次查询只需要沿着索引定位到那一小段数据再回表取20条即可。当然这里我还要主动说明一点这个索引对这个查询是有效的但并不意味着在所有订单状态上都能起同样效果。比如status4已完成的订单如果占据了表中绝大多数数据优化器可能会判断全表扫描更优走索引反而慢。索引优化永远要结合数据分布来评估这也是为什么同样一条SQL在不同业务库中表现会差很多的原因。3.4 配套的SQL改写建议索引建好之后我还顺手改写了这条SQL的一个小问题把pay_time的BETWEEN写法改成半开区间写法规避边界值问题。原来的写法AND pay_time 2024-06-01 AND pay_time 2024-06-30改成AND pay_time 2024-06-01 AND pay_time 2024-07-01这样做的好处是当pay_time字段包含时分秒时BETWEEN或写法会漏掉6月30日当天23:59:59之后的数据而 2024-07-01天然包含整个6月30日最后一秒之前的所有时间点逻辑上更严谨。这个属于SQL书写习惯层面的优化能让索引效果发挥得更稳定。4. 索引之外的性能提升组合拳4.1 SQL改写技巧索引不是万能的。有时候就算有合适的索引SQL写法不讲究性能一样上不去。这里分享几个我在实际工作中高频用到的SQL改写技巧。第一避免使用SELECT *。尽量只查询业务需要的字段一方面减少网络传输数据量另一方面有机会命中覆盖索引。如果某个表有50个字段而你的列表页只需要其中5个SELECT *会让回表概率大增。改写成明确的字段列表后联合索引可能直接覆盖所有查询列Extra中出现Using index性能完全不是一个量级。第二分页深了要改思路。LIMIT 100000, 20这种深分页SQL会让MySQL扫描前100020条然后丢弃前10万条代价非常高。常见的优化方向有两个一是记录上一页最后一条数据的排序字段值用WHERE条件定位下一页比如WHERE id 100000 ORDER BY id DESC LIMIT 20二是利用子查询或JOIN先在索引上定位起始位置再取数据比如先查这20条数据的主键ID再用主键去关联原表取完整数据。第三慎用DISTINCT。DISTINCT本质是去重如果是在大表上查询去重后的结果集开销往往比想象中大得多。先确认业务是否真的需要去重有些场景改用EXISTS或者GROUP BY配合索引效果更好但也要分情况测试没有银弹。4.2 查询条件与索引设计的顺序我遇到过很多“SQL看着没问题但就是慢”的情况最后排查下来原因是查询条件的先后顺序和联合索引的字段顺序不一致导致优化器没能用到最优索引。这里分享一条设计原则。当一条SQL有多个等值条件和范围条件混合时等值条件对应字段放在联合索引靠前的位置范围条件对应字段放在后面。为什么因为等值条件可以精确定位到某一段连续的索引区间范围条件只能缩小扫描范围但无法让每一层索引都精确定位。如果把范围条件放在前面联合索引在范围之后的字段排序意义就大打折扣了。举个例子索引设计为(user_id, status, pay_time)那么查询WHERE status 2 AND pay_time 2024-06-01 AND user_id 1001这种写法虽然逻辑上结果一样但优化器在选择索引时可能不会把这个查询匹配到最优的索引路径上。最好把查询条件也写成user_id 1001 AND status 2 AND pay_time 2024-06-01保证等值条件在前、范围条件在后让优化器能够直接命中联合索引的最优前缀。4.3 硬件与配置层面的辅助手段索引优化是性能提升的核心手段但绝不是唯一手段。当一条SQL已经充分使用索引、行数扫描极少时如果仍然慢就要从更底层的层面找原因了。最常见的是Buffer Pool配置。InnoDB的Buffer Pool用于缓存数据页和索引页。如果设置过小索引页被频繁淘汰每次查询都要产生磁盘I/O性能自然上不去。一个经验值是Buffer Pool设为机器物理内存的60%到80%但具体要看机器是专用数据库还是混部部署。还有一个很容易被忽略的参数是innodb_buffer_pool_instances。在MySQL 5.7及之后版本如果Buffer Pool总大小超过1GB建议调整为多个实例通常设为8减少并发场景下的锁竞争。8.0版本还有一个innodb_buffer_pool_chunk_size参数可以调整内存分配粒度但这些偏底层的参数调整需要长时间观察和压测不建议一上来就动。另外查询缓存Query Cache这个特性在MySQL 8.0已经被移除了如果你的版本还开着它建议关闭因为它在多写场景下维护缓存本身的锁竞争开销会拖慢整体性能。5. 常见问题与排查技巧实录5.1 慢查询排查问题速查表我整理了一份平时排查慢查询时的高频问题对照表直接对着排查效率会高很多。典型症状可能原因排查方向执行计划显示typeALL无索引或索引被放弃先看过滤条件的字段是否有索引检查数据分布确认优化器为什么放弃索引keynull但明明建了索引索引列上使用了函数/隐式转换/前导模糊检查WHERE条件写法按前面列的失效场景逐个对照Extra出现Using filesortORDER BY字段不在索引中或顺序不匹配设计联合索引时把排序字段包含进来注意排序方向和索引方向一致有索引但rows依然很大索引区分度低或范围条件范围过大查看字段基数可能是索引设计不合理考虑覆盖索引或改写查询单条SQL执行很快但接口很慢频繁连接数据库/多次查询N1用数据库慢日志不一定能抓到问题需要排查应用层是否循环调用SQL偶尔慢但大部分时间快缓存淘汰冷数据加载观察第一次执行和第二次执行耗时差异判断Buffer Pool命中率这张表是我踩了无数坑之后总结出来的。碰到一张新的慢查询我基本按这个框架去推导大多数问题在十分钟内就能定位到根因。5.2 一个容易忽略的坑隐式字符集不一致最后分享一个比较隐蔽的坑。两个表关联查询其中一个表的字段是utf8mb4字符集另一个表是utf8字符集关联字段如果用户名字符串MySQL在做表连接时需要在内存中做字符集转换这个转换过程可能导致关联字段上的索引失效。有一次我在排一个多表JOIN慢查询时单表EXPLAIN都正常但整个JOIN的执行计划里驱动表直接全表扫了。排查半天最后发现是关联字段字符集不一致。解决办法也很简单把两张表的关联字段统一成utf8mb4然后重新建索引问题就消失了。这类字符集问题在生产环境中并不少见尤其是老系统升级或不同模块交接的场景。如果排查索引没问题但查询仍然慢记得检查一下表结构里的CHARSET和COLLATE配置是否一致。再补充一个容易被忽略的细节使用前缀索引时要注意区分度。比如对varchar(200)的URL字段建前缀索引如果只取前10个字符区分度可能非常低优化器很可能放弃索引。我习惯用一条件SQL来测试不同前缀长度的区分度SELECT COUNT(DISTINCT LEFT(url, 10)) AS prefix10, COUNT(DISTINCT LEFT(url, 20)) AS prefix20, COUNT(DISTINCT url) AS full_count FROM t_url;通过对比prefix10和full_count的比值选择区分度能够达到80%以上同时又尽量短的长度作为前缀索引长度这样既能减小索引体积又不至于丧失选择性。5.3 索引过多也是灾难还有一个必须提醒的误区索引不是越多越好。每多一个索引INSERT、UPDATE、DELETE时的索引维护成本都会增加。如果一张表有10个索引写入一条记录就要维护10棵索引树在高并发写入场景下性能下降非常明显。我给一个建议表上索引总数尽量控制在5个以内超过这个数就要审视是否有冗余索引。举个常见的冗余场景你已经有了(a, b)联合索引又单独建了a字段的单列索引这时a单列索引就是完全冗余的因为(a, b)联合索引天然支持只按a查询的场景。可以用下面的SQL查询冗余索引通过比较索引的字段前缀来识别SELECT s1.TABLE_NAME, s1.INDEX_NAME, GROUP_CONCAT(s1.COLUMN_NAME ORDER BY s1.SEQ_IN_INDEX) AS index_columns FROM information_schema.STATISTICS s1 GROUP BY s1.TABLE_NAME, s1.INDEX_NAME ORDER BY s1.TABLE_NAME, index_columns;拿到这张索引清单后人工对比前缀一样的索引组合把重复的删掉即可。上线前先在测试环境观察删除索引后的执行计划变化确保没有查询因为删了冗余索引而走全表扫。我在实际项目中见过一张表最多堆了16个索引的情况删掉6个冗余索引后写入性能提升了将近30%而查询性能完全不受影响。性能优化有时候是减法不是加法。做MySQL性能优化这几年我最深的一个体会是索引优化的核心不是“会不会写CREATE INDEX”而是“能不能读懂一条SQL的执行计划”。你建的每个索引都应该有明确的依据要么减少了扫描行数要么消除了文件排序要么实现了覆盖索引。如果连执行计划都懒得看加了索引也只是碰运气。希望这篇文章能把慢查询排查的完整路径讲清楚——从慢日志定位到执行计划分析再到索引设计落地最后用实际数据验证效果。下次再碰到慢查询别急着加索引先按这套流程走一遍你会发现很多问题的答案自己就浮出水面了。