1. 亿级数据慢不是玄学先拆清楚响应链路在哪一环卡住先描述一个我再熟悉不过的场景业务群突然有人你说某个查询接口从上午开始响应时间从200ms涨到了2.8s高峰期直接拖垮了依赖它的页面。登录服务器一查订单表已经有1.2亿行开发同事的第一反应是这表太大了得换ClickHouse/Doris或者在考虑分库分表。先别急着上重武器。做单表亿级数据查询优化第一步永远是搞清楚慢到底发生在哪个环节。MySQL单表亿级并不是什么不可触碰的禁区很多场景下它完全可以在秒级内完成查询——前提是你知道它在干什么。一条SQL从发出到返回走的是这样一条链路客户端发SQL到服务端经过连接器、分析器、优化器生成执行计划执行器根据执行计划访问存储引擎通常是InnoDBInnoDB通过B树索引定位数据页把数据页从磁盘加载到内存Buffer Pool在内存中做过滤、排序、聚合返回结果这中间有四个最典型的瓶颈点瓶颈点表现形式量化指标全表扫描typeALL扫描行数表总行数百万行起步就开始吃力亿级基本不可接受随机IO回表通过二级索引找到主键再回聚簇索引取整行数据每行一次随机IOSSD上约0.1ms机械盘约10ms深分页偏移LIMIT 1000000, 20先扫出100万行再丢弃越往后翻越慢呈线性增长排序/临时表ORDER BY字段无索引产生filesort或临时表内存临时表不够会落到磁盘临时表慢几个数量级很多人有个误解以为加了索引就一定快。实际上索引设计不合理的时候反而比全表扫描更慢。我见过一个真实案例开发给亿级表的status字段枚举值只有0和1加了单列索引查询走这个索引后发现区分度太低优化器直接放弃索引改走全表扫描——这就是典型的索引白加。所以优化之前先要有量化的意识。不要凭感觉说数据量太大了而是要明确回答当前这条SQL扫描了多少行回表多少次排序是在内存还是磁盘这些信息全都在EXPLAIN输出里。2. 定位问题三分法慢查询日志、EXPLAIN与执行计划的翻译2.1 慢查询日志与分析工具先把病号捞出来生产环境不可能只盯着一条SQL看所以第一步是打开慢查询日志捞出一批需要优化的SQL。建议的配置如下slow_query_log ON slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 0.5 log_queries_not_using_indexes ON这里的几个参数都有讲究。long_query_time我习惯设在0.5秒而不是默认的10秒因为生产环境里很多温水煮青蛙式的慢SQL耗时在1秒左右等它涨到10秒再发现就晚了。log_queries_not_using_indexes能帮你抓到那些没走索引的全表扫描这类SQL在数据量过亿后是定时炸弹。日志捞出来之后如果行数多到人眼看不过来用pt-query-digest做聚合分析pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt它会自动按总执行时间和平均执行时间排序帮你找到真正值得优化的罪魁祸首而不是那些只执行了一次但恰好慢的偶发SQL。2.2 读懂EXPLAIN的关键信号type、rows、Extra拿到慢SQL后下一步永远是执行EXPLAIN。很多人看EXPLAIN只会看有没有用索引其实信息量远不止这些。我重点看四列第一列是type。从好到差大致是systemconsteq_refrefrangeindexALL。碰到ALL全表扫描或index全索引扫描就需要高度警惕。亿级表上出现ALL基本就是灾难现场。第二列是rows。优化器估算的需要扫描的行数这个值直接决定了查询的量级。如果rows显示10万那这条SQL再怎么优化也快不到哪去因为它真的要碰10万行数据。第三列是key和key_len。key是实际使用的索引key_len是索引字段的长度。通过key_len可以判断联合索引到底用到了几个字段——比如说联合索引(user_id, status, create_time)如果key_len只显示了user_id的长度说明后面的字段没用上。第四列是Extra最容易被忽视但信息量最大常见Extra值含义严重程度Using index覆盖索引扫描无需回表最佳Using where存储引擎返回后Server层再过滤正常Using filesort排序无法用索引完成需要额外排序需要关注Using temporary使用了临时表常见于GROUP BY、DISTINCT需要关注Using index condition索引下推部分WHERE条件下推到存储引擎较好一个很常见的陷阱看到Extra里有Using filesort就以为慢其实如果排序数据量不大、在内存中能完成性能也不差。真正的坑是排序数据量超过sort_buffer_size后落到磁盘EXPLAIN看不出来得靠后面讲的实际观测。2.3 实测观测把EXPLAIN的猜测验证成事实EXPLAIN里的rows是估算值不是真实值。想要准确的扫描行数可以用EXPLAIN ANALYZEMySQL 8.0.18EXPLAIN ANALYZE SELECT user_id, order_no, amount, create_time FROM t_order WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 20;它会把实际的执行时间、实际扫描行数、实际回表行数都打印出来。我习惯在做大优化之前跑一次优化之后再跑一次用真实数字对比效果——扫描行数从8000万降到1.2万耗时从2.3s降到180ms比任何口头描述都有说服力。3. 核心实战索引设计与SQL改写是怎么把秒级拉到毫秒级的3.1 联合索引设计的最左前缀与字段顺序取舍索引不是建了就完了也不是越多越好。亿级表上每多一个索引写入时就要多维护一颗B树INSERT/UPDATE的性能都会受影响。所以索引设计贵精不贵多。联合索引的核心设计原则是最左前缀——查询条件里必须包含联合索引的第一列索引才可能被用上。但实际工作中更纠结的是字段顺序怎么排。这里给一个我一直沿用的优先级公式等值查询字段 区分度高的字段 排序/分组字段 范围查询字段举个例子一个订单表要支持查某用户某状态下的订单按创建时间倒序联合索引应该这样建ALTER TABLE t_order ADD INDEX idx_user_status_time (user_id, status, create_time);user_id和status都是等值查询谁放前面看区分度。如果status只有0/1/2三种值区分度约等于没有放前面会导致索引内同一个值下挂的数据量巨大效率反而低。把高区分度的user_id放第一列索引树能快速收敛到目标用户的少量记录。关于create_time放最后原因有二一是ORDER BY create_time DESC可以直接用索引排序避免filesort二是范围查询BETWEEN、、放在最左前缀的最右端即使范围条件用不上索引了前面的等值条件依然能精准定位。3.2 回表与覆盖索引让索引自给自足回到刚才那条查询SELECT user_id, order_no, amount, create_time FROM t_order WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 20;假设只有idx_user_status_time这个索引SQL执行流程是通过索引找到满足条件的20条记录的主键id再根据主键回聚簇索引查order_no和amount。这20次回表如果是顺序IO还好但主键在物理存储上通常不是按查询顺序排列的就退化成20次随机IO。解决方案是覆盖索引——把要查询的列全部塞进索引里ALTER TABLE t_order ADD INDEX idx_user_status_time_cover (user_id, status, create_time, order_no, amount);这样查询需要的所有数据都在二级索引的叶子节点上不需要回表EXPLAIN里会看到Using index。注意覆盖索引的优势不只是省去回表IO更关键的是二级索引比聚簇索引小得多相同内存能缓存更多索引页IO效率更高。不过覆盖索引不是无脑建——索引列越多写入越慢、占用空间越大。实战原则是只把高频查询需要的列加进去TEXT/BLOB大字段千万不能放索引。3.3 深分页优化为什么LIMIT 1000000, 20一定能拖垮数据库这是单表大分页最经典的坑。看这条SQLSELECT id, order_no, amount FROM t_order ORDER BY create_time DESC LIMIT 1000000, 20;执行流程是扫描出全部符合条件的数据 → 按create_time排序 → 抛弃前100万行 → 返回第1000001到1000020行。注意那前100万行虽然被抛弃了但每一行都真实地经过了扫描、排序、回表消耗的时间和IO一分不少。而且排序本身如果不走索引会产生Using filesort如果排序列数据量大到超过sort_buffer_size还要落磁盘那个性能损耗是灾难级的。两种成熟解法解法一延迟关联延迟join先通过覆盖索引快速定位到目标行的主键再用主键去关联原表取需要的列SELECT o.id, o.order_no, o.amount FROM ( SELECT id FROM t_order ORDER BY create_time DESC LIMIT 1000000, 20 ) tmp JOIN t_order o ON tmp.id o.id;子查询里只有id和create_time可以走覆盖索引MySQL扫描1,000,020个索引条目后只拿这20个主键去回表回表次数从100万次降到20次。实测在千万级数据下这种写法通常能带来10倍以上的性能提升。解法二游标分页深分页的根源是偏移量——偏移量越大前面浪费的扫描越多。如果能改成基于上次查询的最后一个值继续往后取就完全避免了偏移量浪费-- 第一页 SELECT id, order_no, amount FROM t_order WHERE create_time 2024-12-01 00:00:00 ORDER BY create_time DESC LIMIT 20; -- 第二页把上一页最后一条记录的 create_time 和 id 传进来 SELECT id, order_no, amount FROM t_order WHERE (create_time 2024-12-01 00:00:00 AND create_time 2024-11-30 20:00:00) OR (create_time 2024-11-30 20:00:00 AND id 123456) ORDER BY create_time DESC, id DESC LIMIT 20;游标分页的代价是失去了随机跳页能力适合新闻流、订单流这类只需要上一页/下一页的场景。如果产品经理一定要跳页那就用延迟关联这也是相对最优解了。3.4 SQL习惯自查四个让索引失效的低级错误亿级表上一次索引失效就是一次线上事故。下面这几个坑我在代码评审里见过太多次函数包裹索引列WHERE DATE(create_time) 2024-12-01。一旦对索引列做了函数运算优化器就无法使用B树的顺序查找能力只能逐个判断。正确做法是写成范围条件WHERE create_time 2024-12-01 AND create_time 2024-12-02。隐式类型转换WHERE phone 13800138000而phone列是VARCHAR类型。MySQL会把字符串列转成数字比较相当于对索引列做了一次CAST()索引就废了。保持WHERE phone 13800138000。前模糊匹配WHERE name LIKE %张%。%放在最前面的模糊查询无法利用B树有序性。如果业务确实要支持中缀搜索该上Elasticsearch就上别为难MySQL。OR连接非索引列WHERE user_id 123 OR status 1。优化器可能放弃索引。可以拆成两个查询用UNION ALL合并或者用IN改写。4. 索引之外的第二战场数据组织与中间结果层4.1 冷热数据分离给亿级表瘦身很多时候表数据量大是因为历史数据一直堆积。订单表2亿行里可能1.8亿行都是超过2年的老订单访问频率极低却每时每刻都在拖累索引层和Buffer Pool。一个务实的方案是冷热分离把主表只保留最近N个月的热数据历史数据按月归档到历史表或ODS层。应用层写一个路由策略查近3个月走主表查更早的走历史表必要时用UNION ALL合并两端结果。这个方案的好处是立竿见影主表从2亿降到2000万所有索引、Buffer Pool、统计信息都瞬间呼吸顺畅了。缺点是需要改造数据访问层业务上也要能接受实时库查不到太久远的历史这个设定。4.2 汇总表/中间结果表用空间换时间如果查询模式是固定的统计某用户某月的订单总额每次都实时扫订单表聚合数据量大后一定扛不住。这种场景最直接的做法是预计算——建一张汇总表用定时任务或消息机制在订单落库时增量更新汇总值CREATE TABLE t_user_month_summary ( user_id BIGINT NOT NULL, month VARCHAR(7) NOT NULL, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0, order_count INT NOT NULL DEFAULT 0, update_time DATETIME NOT NULL, PRIMARY KEY (user_id, month) );查询月总额直接查汇总表毫秒级返回。代价是数据不是绝对实时的但对报表、看板这类场景完全够用。这个思路可扩展为预聚合定时刷新过期失效的通用模式本质上是把实时计算压力转移为离线计算压力非常适合读多写少、统计固定的业务。4.3 缓存层的正确用法缓存查询结果而非缓存数据很多人一优化就问要不要上Redis。我的看法是MySQL单表亿级优化应该先把索引、SQL、数据组织做到位最后才考虑缓存。缓存真正解决的是热点数据的高频重复查询而不是帮你掩盖烂SQL。比如一个商品详情页一天百万次请求查同一批商品把这些商品信息的查询结果缓存10分钟数据库压力瞬间大减。但订单列表这种跟用户强相关、条件多变的数据缓存命中率低一致性问题还多硬上缓存反而容易出事故。即使要上缓存也要设置合理的过期时间、做好缓存穿透和雪崩防护。缓存是加速层不是兜底层——底层SQL该优化还是得优化。4.4 什么时候才应该考虑分库分表或换引擎说实话把索引、SQL、数据组织、缓存都做到位之后单表亿级还是慢的场景已经不多了。真正需要分库分表的信号是**单表数据量超过2亿且写入TPS持续走高B树层级增加到4层以上单表索引已经建到极限但查询模式依旧横跨多个索引维度写入和读取相互干扰严重行锁、间隙锁竞争明显分库分表是成本极高的方案——分布式ID、跨节点join、全局排序分页、分布式事务每个问题都会在后续开发中反复折磨你。如果你的查询确实是大规模聚合分析型比如统计亿级订单的行业分布那确实该考虑引入OLAP引擎如Doris、ClickHouse列存向量化执行对这种分析型查询是降维打击。但OLTP场景的日常单行/多行查询MySQL会一直凑合能用别轻易为了酷炫而换引擎。5. 运维参数兜底把你的InnoDB配置喂饱5.1 innodb_buffer_pool_size这是最值钱的参数InnoDB的数据页、索引页都缓存在Buffer Pool里。如果Buffer Pool太小每次查询都要从磁盘读数据页亿级表再好的索引也是白搭——你能想象每次查索引都要去磁盘读B树节点吗经验值是Buffer Pool设置为可用物理内存的60%-75%。比如机器有64GB内存给MySQL开48GB左右。可以通过下面的SQL确认命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;Innodb_buffer_pool_read_requests很大而Innodb_buffer_pool_reads很小说明命中率高反之就要考虑扩容或优化内存占用。注意修改这个参数需要重启实例而且innodb_buffer_pool_instances建议也一并设置Buffer Pool大于等于16GB时设置多个instance可以减少内部锁竞争。5.2 连接数、连接池与慢SQL的连锁反应一个很容易被忽略的容量陷阱和连接数有关。当某几条慢SQL把持连接时间过长时连接池的连接会被占满后来的正常请求全部排队等待——表现就是整个系统假死。我非常强调三件事连接池maxActive不是越大越好。每个连接都占用MySQL资源线程栈、内存连接数过高反而导致上下文切换加剧。一般建议核心服务连接池控制在20-50之间。给连接设置maxWait。取不到连接的等待时间设短一点快速失败让上游感知到问题而不是无限期阻塞拖垮调用链。慢SQL治理要常态化。上线新的聚合查询SQL之前养成先用EXPLAIN看执行计划的习惯别让慢SQL在生产环境自然生长。5.3 硬件选型SSD不是加分项是必需品如果上面的优化都做了、SQL也能走最优索引但单次查询还需要数十毫秒——那大概率瓶颈已经转移到硬件上了。机械硬盘的随机IO延迟在10ms级别而NVMe SSD可以做到0.02ms差了3个数量级。对于亿级数据量的表我强烈建议至少使用SSD。另外如果有条件把ib_logfile、tmpdir、mysql-datadir放到不同的物理磁盘上可以避免日志写入和查询读写在同一个盘上争抢IO。6. 一个完整的优化实录订单表1.2亿行2.8秒到180毫秒最后分享一个我手头完整的实战案例把这篇文章里的所有方法串起来。业务场景一个B2C平台的订单表1.2亿行需要支持用户查询自己某状态下的订单列表按时间倒序分页。SQL长这样SELECT id, order_no, sku_id, amount, status, create_time FROM t_order WHERE user_id 123456 AND status 1 ORDER BY create_time DESC LIMIT 0, 20;第一轮排查慢查询日志显示这条SQL平均耗时2.8秒。EXPLAIN发现typeALLrows1.2亿——典型的全表扫描。查了下原表上有一个单列索引idx_status但区分度太低优化器弃用了。第一轮优化建立联合索引(user_id, status, create_time)。再次EXPLAINtyperefrows32000执行时间降到480ms。看起来进步巨大但离秒级还是差着档次。第二轮分析虽然行数收敛到3.2万但Extra里没有Using index意味着还是要回表3.2万次取order_no、amount等字段。这就是当前480ms的主要成本。第二轮优化改为覆盖索引把高频查询字段也加进去ALTER TABLE t_order ADD INDEX idx_user_status_time_cover (user_id, status, create_time, order_no, sku_id, amount);执行时间从480ms降到210ms。EXPLAIN显示Using index回表完全消失。第三轮优化业务方反应翻到第50页以后接口又回到了秒级。这是因为分页到深处出现了先扫后丢的深翻页问题。改造为延迟关联SELECT o.id, o.order_no, o.sku_id, o.amount, o.status, o.create_time FROM ( SELECT id FROM t_order WHERE user_id 123456 AND status 1 ORDER BY create_time DESC LIMIT 100000, 20 ) tmp JOIN t_order o ON tmp.id o.id;加上游标分页逻辑后稳定在180ms左右。整个优化过程没有加服务器、没有分库分表就是纯靠索引重建、SQL改写和数据访问方式调整。一轮轮走下来我的体会挺深的做这种优化最怕的不是不会优化而是不知道去哪找问题。熟悉EXPLAIN的每一列、理解B树的访问路径、清楚回表和深分页的成本模型这些基础功比任何优化神器都管用。最后再分享一个被反复验证的小技巧大表做任何索引变更一定要选在业务低峰期并且先用pt-online-schema-change这类工具在线变更直接对1.2亿行的表执行ALTER TABLE ADD INDEX锁表时间会让你怀疑人生。优化是好事但别让优化本身变成事故。