干MySQL运维和开发这些年踩过最多的坑就是大表性能瓶颈。一张表几千万行业务一高峰期查询秒变十几秒接口直接超时监控告警响成一片。很多人一遇到这种情况就喊分库分表其实大多数场景根本不需要上那么重的方案把索引、SQL写法、参数和架构分层理清楚就能解决掉80%的问题。这篇文章不绕弯子直接讲千万级数据量下MySQL优化的完整思路和实操手段。内容包括大表为什么慢、如何用数据定位瓶颈、索引怎么建才有效、深分页和复杂SQL怎么改写、哪些参数真正值得调、什么时候才需要分库分表最后附上几个我在项目中真实踩过的坑和排查记录。适合被线上大表卡得头疼的后端开发、DBA以及想系统了解MySQL性能优化的朋友。1. 先搞清楚千万级数据到底慢在哪优化之前一定要先明白“慢”的本质是什么。很多人一上来就加索引、改配置折腾半天没效果就是因为没搞懂大表查询慢的根源在哪里。1.1 千万级数据到底卡在哪里存储引擎的账要算清楚先说结论大表慢不是“数据太多放不下”这么简单而是数据量增大后I/O路径、内存命中率和锁竞争三个维度同时恶化。从InnoDB的存储结构说起。表数据按B树组织主键索引树的叶子节点存整行数据二级索引树的叶子节点存主键值。一个三层的B树大概能管理几千万行数据理论上走主键等值查询只需要三次I/O。那为什么实际线上这么慢关键在于随机I/O和缓存命中率。几千万行数据假设每行1KB总量就是几十GB远超出innodb_buffer_pool的容量很多服务器配8G或16G。数据页在内存和磁盘之间频繁换入换出这种“缓存颠簸”带来的随机磁盘I/O比顺序扫描还要命。你可以这么理解内存就相当于你的工位磁盘相当于几公里外的档案室每查一条数据都得跑去档案室翻一次——数据量一大来回跑的次数就指数上涨。还有一个很多人忽略的点——锁竞争。同一个热点行、同一个范围的记录被高频更新时行锁等待会直接把吞吐拖垮。尤其是那种“用户中心订单列表”类的场景用户频繁刷新页面后端反复查询同一批用户的订单数据锁等待和一致性读的开销会迅速堆积。所以说大表优化的第一课不是急着写SQL而是先算清楚你的查询到底触发了多少次物理I/O能不能把随机I/O变成顺序I/O能不能让更多热点数据留在内存里这三个问题想透了后面做的每一步优化才有的放矢。1.2 先诊断再优化用数据说话不要拍脑袋我的原则是任何优化动作之前必须先有数据支撑。常用三板斧慢查询日志定位具体SQLEXPLAIN看执行计划performance_schema做全局分析。慢查询日志是第一步。在MySQL里开启慢日志很简单但注意不要在生产上长时间开全局慢日志影响性能且日志膨胀很快。建议这样设置slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ONlong_query_time设为1秒对千万级表来说已经是比较严格的标准了。开一段时间后用mysqldumpslow按执行时间排个序看看TOP 10都是什么SQLmysqldumpslow -s t -t 10 /var/log/mysql/slow.log拿到慢SQL后立刻EXPLAIN看执行计划。重点关注四个字段字段含义危险信号type访问类型ALL全表扫描、index全索引扫描rows预估扫描行数与表总量同数量级时很危险key实际使用的索引NULL时说明没走索引Extra附加信息Using filesort、Using temporary都是性能杀手举个例子我在某个订单项目里定位过一条慢SQLEXPLAIN SELECT * FROM t_order WHERE status 1 ORDER BY create_time DESC LIMIT 20;执行计划显示typeALLrows3200万ExtraUsing filesort。三个危险信号全占了。这种SQL加再大的内存也没用因为优化器压根就没找到合适的索引路径只能把3200万行全捞出来再在内存里排序。这时候方案很明确建联合索引让WHERE和ORDER BY都能走索引。如果一条SQL在业务高峰期反复出现但又没法快速定位源头可以用performance_schema按语句指纹聚合统计SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 20;这个查询能直接告诉你哪些SQL扫描的总行数最多是真正的“吃性能大户”比单看慢日志更有全局视角。MySQL 5.7及以上还自带了sys schema用起来更顺手SELECT * FROM sys.statement_analysis WHERE rows_examined_avg 10000 ORDER BY rows_examined_avg DESC LIMIT 10;记住一句话没有执行计划分析的SQL优化都是耍流氓。你连它为什么慢都不知道改什么都是碰运气。2. 索引优化是性价比最高的一步索引是大表优化里投入产出比最高的一步。不需要改架构、不需要加机器建对几个索引很多慢查询能直接从秒级降到毫秒级。但索引也不是银弹建错了反而拖累写入性能。2.1 索引失效的常见场景这些坑你一定踩过先看最典型的索引失效场景我几乎在每个团队都见过第一个是对索引列做函数或运算。比如WHERE DATE(create_time) 2024-01-01这个写法你以为走了create_time索引其实MySQL要把每行都算一遍DATE函数索引就废了。正确写法是范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00第二个是隐式类型转换。如果phone字段是varchar类型查询条件写WHERE phone 13800138000MySQL会把字段转成数字去比较索引照样失效。反过来如果字段是bigint你却传了字符串同样失效。解决方法是保持类型一致字符串就加引号。第三个是前导模糊查询。WHERE name LIKE %张%这种查询在B树上没法定位起点只能全索引扫描。如果业务确实需要要么考虑用全文索引要么换搜索引擎要么老老实实接受全表扫。但LIKE 张%这种后缀匹配是可以用到索引的。第四个是OR条件使用不当。WHERE status 1 OR user_id 100即使两个字段都有索引优化器也可能放弃索引合并转而全表扫描。改写思路是拆成两个查询UNION ALL或者确保两边都能走索引后用UNION合并。第五个是复合索引没按最左前缀来。建立了(a, b, c)复合索引查询条件是WHERE b 1 AND c 2这个查询用不上索引。这是MySQL复合索引的基本规则后面单独展开讲。这里有个容易误解的地方WHERE status ! 1这种不等于条件不一定就全表扫如果该列区分度很高优化器也可能走索引。所以不要背死书一切以EXPLAIN结果为准。2.2 联合索引与覆盖索引大表查询提速的核心武器千万级表上单列索引往往不够用联合索引才是主角。设计联合索引要记住一个口诀等值条件在前排序字段紧随范围条件放最后。举例说明。订单查询高频场景是“查某个用户某个状态下最近的订单”SELECT * FROM t_order WHERE user_id 10086 AND status 1 ORDER BY create_time DESC LIMIT 20;索引应该建(user_id, status, create_time)而不是(status, user_id, create_time)更不是三个单列索引。为什么因为user_id是等值条件能最大程度缩小范围status也是等值create_time用来排序索引天然有序就可以避免filesort。再说覆盖索引。如果查询只需要某几列把这些列都放进索引里MySQL就不需要回表去主键索引捞整行数据了。这个优化在大表上效果极其明显。看一个真实案例。订单列表页的统计接口需要查某个用户累计下单金额SELECT SUM(amount) FROM t_order WHERE user_id 10086;建一个(user_id, amount)的联合索引这条SQL就完全不需要回表直接从二级索引里就能拿到所有amount值做聚合。对于几千万行的表回表次数从几万次降到接近零性能差距是数量级的。我自己总结的索引设计流程是这样的先收集业务里的高频查询按频率排序然后针对每条SQL分析WHERE、ORDER BY、GROUP BY涉及的列接着按“等值列优先、排序列次之、范围列垫底”的顺序组合成联合索引最后用EXPLAIN验证优化器是否真的在用这个索引。千万别一上来就建七八个索引真正用得上的往往就那么两三个。2.3 索引维护与冗余清理不加管理的索引是负债索引不是建完就完事了它是B树每次INSERT、UPDATE、DELETE都要同步维护会拖慢写入速度、放大日志量、占用额外磁盘。很多大表的写入慢恰恰是因为索引建得太多太杂。线上常见的两种索引问题冗余索引和重复索引。(a, b)和(a)就是冗余关系后者完全被前者覆盖属于白白消耗写入性能。(a, b)和(b, a)不算重复查询条件不同可能有各自的价值。判断方法是看优化器是否真的用到了。清理索引前先用两张视图诊断一下-- 查看从未被使用的索引注意这是基于当前统计的 SELECT * FROM sys.schema_unused_indexes; -- 查看冗余索引 SELECT * FROM sys.schema_redundant_indexes;有冗余索引的在业务低峰期删除就好。但大表加索引或者删索引要特别小心几千万行直接ALTER TABLE会长时间锁表业务直接不可用。保险的做法是用在线DDL低峰执行ALTER TABLE t_order ADD INDEX idx_user_status_time (user_id, status, create_time), ALGORITHMINPLACE, LOCKNONE;MySQL 5.6以后才支持这个语法8.0默认就是INPLACE。但即使如此几千万行的表构建索引也要跑一段时间建议先用pt-online-schema-change这类工具在业务几乎无感知的情况下完成索引变更。我曾在5000万行的表上加过索引用原生ALTER跑了40分钟中间有一段时间CPU和磁盘I/O飙升得很厉害业务虽然没有完全锁死但明显感觉到了延迟抖动。后来换用pt-osc来控制节奏情况好很多。3. 高频SQL与大表分页改造索引建对了基础查询就快了。但在大表场景里还有一批SQL是“建了索引也救不回来”的——深分页、多表关联、大聚合。这些SQL就得从写法上动刀。3.1 深分页优化16秒到50ms的实战先看一个经典场景。订单列表接口前端要展示第500页的数据SQL长这样SELECT * FROM t_order WHERE user_id 10086 ORDER BY create_time DESC LIMIT 500000, 20;这条SQL慢就慢在LIMIT 500000, 20。MySQL的LIMIT是先扫描前500020行然后丢弃前50万行只返回最后20行。扫描的行越多越慢而且越往后翻越慢这就是“深翻页”问题。优化的核心思路是不要扫描那么多行只要找到那20行的主键。有两个主流方案。方案一延迟关联。先用覆盖索引找出目标主键再回表取完整数据SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order WHERE user_id 10086 ORDER BY create_time DESC LIMIT 500000, 20 ) tmp ON t.id tmp.id;关键在于内层子查询只需要扫描二级索引不需要回表扫描500020行索引的成本远低于扫描500020行完整数据。我在一个千万级订单表上做过对比延迟关联比直接LIMIT快了一个数量级。方案二游标分页也叫keyset分页。把LIMIT换成“记住上一页最后一条”利用索引定位SELECT * FROM t_order WHERE user_id 10086 AND (create_time, id) (2024-06-01 10:30:00, 10086) ORDER BY create_time DESC, id DESC LIMIT 20;create_time有重复所以排序时加上id作为第二条件这样定位条件就完全确定走的是索引范围扫描。理想情况下每页都是毫秒级而且不随页数增加而变慢。缺点是不能随意跳页只适合“下一页/上一页”的交互场景。两种方案选哪个看业务需要跳页就选延迟关联性能提升也够只需要顺序翻页就选游标分页体验最好。我在实际项目里两种都试过游标分页对于千万级表几乎是“永远不慢”的方案强烈推荐给列表类场景。对分页方案有纠结的话列个对比方案适用场景优势劣势传统LIMIT数据量小、页码浅实现简单深翻页性能急剧恶化延迟关联需要跳页、数据量大性能稳定提升内层子查询仍需扫描较多索引页游标分页顺序翻页流式加载每页毫秒级、不随页码退化无法跳页3.2 join与子查询改写别让大表相互折磨多表关联在千万级表上是重灾区。常见问题不是“join本身有多慢”而是关联字段没有索引、驱动表选错、或者关联前没有先过滤数据。驱动表的选择很关键。MySQL的join一般是左表驱动右表优化器会倾向用小表当驱动表但偶尔会抽风。实际业务中宁可自己控制也不赌优化器。看一个例子SELECT u.id, u.name, o.order_no FROM t_user u INNER JOIN t_order o ON o.user_id u.id WHERE u.created_at 2024-01-01 LIMIT 100;t_user过滤后可能只有几千行但如果没有t_order(user_id)索引每一次关联都要全表扫描t_order的3200万行后果可想而知。所以第一件事就是确保关联字段有索引。如果确认优化器选错了驱动表可以用STRAIGHT_JOIN强制顺序SELECT u.id, u.name, o.order_no FROM t_user u STRAIGHT_JOIN t_order o ON o.user_id u.id WHERE u.created_at 2024-01-01 LIMIT 100;还有一类问题是子查询IN的写法。MySQL 8.0.16以前对IN子查询的优化很一般特别是子查询结果集大的时候。改写建议小结果集的IN问题不大大结果集优先转JOIN。举个例子-- 低效写法 SELECT * FROM t_order WHERE user_id IN (SELECT id FROM t_user WHERE status 0); -- 改写为JOIN SELECT o.* FROM t_order o INNER JOIN t_user u ON o.user_id u.id WHERE u.status 0;需要注意的是改写后如果t_user过滤出的数据量太大JOIN也可能撑不住。更好的做法是先评估子查询的结果集大小几万行以内可以接受几十万行以上就要考虑到底层数据模型是不是该调整了。3.3 count与聚合优化别把精确统计当唯一选项千万级表上做SELECT COUNT(*) FROM t_order绝对是个灾难。InnoDB不像MyISAM那样存了精确行数COUNT(*)必须逐行统计全表扫完才出结果。如果你的业务每次刷新页面都要实时精确总数几千万行下来CPU再强也扛不住。优化手段按业务要求分级如果只要近似值直接看执行计划EXPLAIN SELECT COUNT(*) FROM t_order;rows字段就是优化器估算的行数误差在10%以内很常见用来显示“约XXXX万条”完全够用。我做过一个数据大屏项目就是这么干的数据库零压力。如果业务确实需要精确值且实时性要求高那就不该直接查大表应该在业务层维护一张计数器表每次下单事务里同步更新。这样查询走的是几张几百行的小表秒出结果。代价是引入额外的事务逻辑注意并发更新的行锁问题。如果精确值只需要低频更新那就后台定时统计把结果落到缓存或统计表里。比如每个小时算一次总订单数页面直接读缓存。聚合查询同理。GROUP BY经常带出Using temporary和Using filesort本质上是中间结果集太大。优化思路很直接先过滤再分组、先缩小范围再聚合以及尽量让GROUP BY走索引。比如“统计某大客户最近一个月的订单数”不要全表GROUP BY先限制user_id和日期范围SELECT user_id, COUNT(*) FROM t_order WHERE user_id 10086 AND create_time 2024-05-01 GROUP BY user_id;4. 参数调整与架构分层SQL和索引层面优化完如果大表性能还是吃紧就要看数据库本身的配置和架构了。这一层的调整影响面大必须循序渐进。4.1 关键参数调优不是参数越大越好很多人的第一反应是把innodb_buffer_pool_size调大这本身没错但要用在刀刃上。InnoDB的数据和索引缓存都在这块内存里大表场景下缓存命中率决定了绝大多数查询的响应速度。我的习惯是如果服务器内存32G会把buffer pool设为20G左右同时观察命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;命中率 1 - reads / read_requests长期低于95%就说明内存确实不够调大buffer pool会有效果。但如果命中率已经99%以上调再大也没意义瓶颈在别处。另一个值得调的是redo log容量。MySQL 8.0.30以前用innodb_log_file_size之后用innodb_redo_log_capacity。默认值偏小大表高频写入时频繁的checkpoint会拖累性能。建议设置为4G或8G[mysqld] innodb_buffer_pool_size 20G innodb_redo_log_capacity 8G max_connections 500 max_allowed_packet 64M tmp_table_size 64Mmax_connections别贪大连接本身吃内存开太多容易OOM。tmp_table_size设置多大取决于你是否有大量GROUP BY和子查询过大可能把临时表都放进内存触发swap反而更糟。这里说句得罪人的话网上很多“MySQL配置优化20项”之类的文章照着抄只会害了你。每个业务的读写比、数据量、并发模型完全不一样唯一的正确路径是先监控、再调整、后验证。调整某个参数前后用压测对比QPS和延迟没有改善就回滚。4.2 读写分离与分库分表架构侧的最后手段如果索引、SQL、参数都优化到位单库单表还是扛不住才轮到架构层面的调整。但这是拆解复杂度的开始一旦引入你的系统就再也不是“一个连接串搞定一切”那么简单了。先说读写分离。互联网应用普遍读多写少把读流量分摊到从库能直接减轻主库的压力。工具上可以用ProxySQL、MaxScale或者在代码层配置多数据源。需要注意的坑是主从延迟刚写入的数据立即去从库查可能查不到。实践中的做法是“写后读关键路径走主库”用户的订单支付成功页、登录后的用户信息查询这类必须强一致的走主库历史列表、报表查询这类弱一致场景走从库。再说分库分表。单表超过两千万行或者单表容量超过几十GB并且索引和SQL优化已经做到位才考虑水平拆分。水平拆分的关键是选择分片键。用户维度的业务按user_id分时间维度的流水按时间范围分。分片键一旦选定所有查询都必须带上它否则中间件就要广播到所有分片执行性能灾难。所以我强烈建议在建表之初就预留分片键字段即使现在还用不上。很多团队上线时图省事没留等到数据量爆炸再迁移那就是一场手术级别的工程。具体工具上ShardingSphere是目前生态比较成熟的支持分片、读写分离、数据加密。MyCat也有团队在用但近几年的活跃度大不如前。还有一条路线是直接用MySQL原生分区表比如按时间范围分区CREATE TABLE t_order ( id BIGINT NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(10,2), PRIMARY KEY (id, create_time) ) PARTITION BY RANGE (YEAR(create_time)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN MAXVALUE );原生分区表的优点是应用层无感知查询条件里带上分区键就能自动裁剪分区。但它也有不少限制分区字段必须包含在主键里跨分区查询不一定快以及底层索引仍然是全局的复杂查询不一定能利用分区裁剪。我的建议是分区表适合有明显的冷热时间分布、按范围访问的场景比如日志流水、订单历史。它不是分库分表的替代品更多是归档查询的辅助手段。4.3 冷热数据归档大表“做减法”才是最快的优化前面讲的都是让查询走得更快但有时候最有效的优化是把表变小。冷热数据分离的核心是把访问频率低的历史数据从主表里挪出去。比如订单表保留最近12个月的数据更早的迁到t_order_archive归档表或者迁到ClickHouse之类的大数据组件做离线分析。主表体量下降后索引的层级更浅、缓存命中率更高、写入的索引维护成本更低这是治本的方法。归档的工具有现成的pt-archiver可以按条件批量把数据搬走边读边删不会一次性锁大量行pt-archiver --source hlocalhost,Dtest,tt_order \ --where create_time 2023-01-01 \ --dest hlocalhost,Dtest,tt_order_archive \ --limit 1000 --commit-each --bulk-delete--limit和--commit-each控制每个批次处理的行数--bulk-delete是批量删除操作的开关。跑之前一定要先在测试库验证确认不会影响在线写入。另外一个大字段的优化如果大表里混着TEXT或BLOB类型的大字段这类数据行体积大会把整个表的索引和缓存效率都拖垮。合理的做法是拆出去放到独立的扩展表主表只保留一个外键。查询列表页只访问主表详情页再按需关联扩展表。我自己做归档时的心得是不要一次性冲到深夜任务里把几千万行跑完服务端压力和主从延迟会传染到其他业务。分多个批次、控制速率、监控主从延迟一旦delay超过阈值就暂停等追平再继续。5. 常见问题与排查技巧实录最后这部分聊聊实际项目中遇到的案例和排查思路。有些问题排查起来非常绕记录一下能给后来人省不少时间。5.1 真实案例一深分页优化从16秒到50ms去年接手过一个用户订单中心的性能工单。线上反馈“第100页之后的订单列表打开特别慢”实测接口耗时基本在15秒以上。定位过程是这样的先看慢日志发现一条LIMIT偏移量很大的SQL频繁出现。EXPLAIN一看typeALLrows2800万ExtraUsing filesort。虽然表上有user_id索引但ORDER BY create_time没有索引支持MySQL只能先把该用户的订单全部捞出来再排序再丢弃前50万行最后返回20行。优化动作分两步先建(user_id, create_time)联合索引让排序走索引再把外层查询改成延迟关联。改完后单条查询耗时从16秒降到50ms附近接口整体返回时间直接降了两个数量级。这个案例给我最大的启发是深分页的“深”不是靠硬件堆出来的而是靠走索引和减少回表来解决的。优化思路一定要落在“让数据库少干活”上而不是“让数据库干得更快”。加内存、换SSD是必要的但那是最后兜底的手段。5.2 真实案例二一次锁等待引发的雪崩另一个案例是某次大促期间一个订单状态更新接口突然大量超时。监控看数据库线程堆积严重SHOW PROCESSLIST发现几十个线程都在等待同一个UPDATE语句UPDATE t_order SET status 2 WHERE status 1;问题很明显这个UPDATE没有利用索引是全表扫描的效果——每行都要锁一下、判断一下、决定是否更新。几千万行的表一次更新把几乎所有行都碰了一遍期间产生的锁竞争可想而知。排查时先用SHOW ENGINE INNODB STATUS确认锁等待确实集中在同一行范围然后检查t_order的索引情况发现status字段居然没有索引。给status建了索引后UPDATE的扫描范围从几千万行缩小到符合条件的几万行锁持有时间从一个量级降到了另一个量级接口超时问题随即消失。这个案例想提醒的是DML同样需要索引而且比SELECT更重要。SELECT慢只是查询迟一点返回UPDATE慢可能直接把整个库锁死。5.3 常用监控与巡检命令运维大表的日常操作列一份我日常巡检大表时高频使用的命令直接拿去用-- 实时查看正在执行的SQL SHOW FULL PROCESSLIST; -- 查看当前锁等待 SELECT * FROM sys.innodb_lock_waits; -- 查看InnoDB整体状态包括死锁、事务、缓冲池 SHOW ENGINE INNODB STATUS; -- 查看表大小TOP10 SELECT table_name, ROUND(((data_length index_length) / 1024 / 1024 / 1024), 2) AS size_gb FROM information_schema.tables WHERE table_schema your_db ORDER BY size_gb DESC LIMIT 10; -- 查看某个表的碎片率 SELECT table_name, data_free / 1024 / 1024 AS frag_mb FROM information_schema.tables WHERE table_schema your_db AND data_free 0;表碎片率也是个容易被忽略的细节。频繁UPDATE和DELETE后表内部会产生大量空洞导致扫描的物理页比实际数据多得多。对大表做OPTIMIZE TABLE有时能带来意外惊喜但同样要注意锁表风险最好用ALTER TABLE ... ENGINEInnoDB或者pt-table-rebuild这类工具在低峰期处理。日常巡检节奏建议每天看一次慢日志TOP SQL每周看一次全表扫描和未命中索引的SQL每月做一次索引使用率评估。很多大表问题是慢慢积累出来的定时体检永远好过故障时抢救。5.4 排查心法先看执行计划再动SQL排查优化类问题我的顺序永远是固定的先确认现象再收集证据再定位慢在哪一步最后才动手改。很多人上来就把SQL改一版试一下运气好碰对了就宣称优化成功实际上根本没有沉淀出方法论。具体到MySQL大表优化证据就是你手里的EXPLAIN结果。它对一条SQL的执行路径描述已经很清楚了type从ALL变成range或refkey从NULL变成实际使用的索引名rows从几千万降成几百Extra里的Using filesort和Using temporary消失这些指标任何一个改善都意味着实实在在的性能提升。在执行任何DDL之前一定要评估影响。给千万级表加索引、改字段、做归档都必须选定低峰期并且准备好回滚方案。越是性能吃紧的线上环境越要谨慎操作因为一旦锁表你面对的可能不只是慢查询而是整个业务的不可用。写在最后的个人体会如果让我给MySQL大表优化排个优先级我的排序是SQL写法 索引设计 参数调优 架构拆分。先把那些明显不合理的查询改掉再让索引高效地支撑起这些查询然后调参数锦上添花最后才轮到分库分表这种重量级方案。在实际操作中我发现很多团队把顺序搞反了——表一大就喊着分库分表结果分完之后发现主键、外键、查询逻辑全部要改中间件运维复杂度直线上升而性能问题并没有完全消失因为最根本的慢SQL逻辑没有被修正。还有一条要提醒做任何优化都要有对比数据支撑。改之前记录QPS、平均延迟、慢查询数量改之后再做同样的测量用数据说话而不是凭感觉说“快多了”。这套方法不止适用于MySQL大表几乎所有的数据库性能优化问题都可以套用。最后分享一个小技巧优化完之后最好保留当时的EXPLAIN结果和执行时间记录写入团队的wiki。大表优化不是一次性工作业务数据量持续增长同一套方案三个月后可能又失效了有历史记录做对比下次迭代的速度会快很多。Keep it simple, keep it measured。
MySQL千万级数据量性能优化实战：索引、SQL与架构全解析
干MySQL运维和开发这些年踩过最多的坑就是大表性能瓶颈。一张表几千万行业务一高峰期查询秒变十几秒接口直接超时监控告警响成一片。很多人一遇到这种情况就喊分库分表其实大多数场景根本不需要上那么重的方案把索引、SQL写法、参数和架构分层理清楚就能解决掉80%的问题。这篇文章不绕弯子直接讲千万级数据量下MySQL优化的完整思路和实操手段。内容包括大表为什么慢、如何用数据定位瓶颈、索引怎么建才有效、深分页和复杂SQL怎么改写、哪些参数真正值得调、什么时候才需要分库分表最后附上几个我在项目中真实踩过的坑和排查记录。适合被线上大表卡得头疼的后端开发、DBA以及想系统了解MySQL性能优化的朋友。1. 先搞清楚千万级数据到底慢在哪优化之前一定要先明白“慢”的本质是什么。很多人一上来就加索引、改配置折腾半天没效果就是因为没搞懂大表查询慢的根源在哪里。1.1 千万级数据到底卡在哪里存储引擎的账要算清楚先说结论大表慢不是“数据太多放不下”这么简单而是数据量增大后I/O路径、内存命中率和锁竞争三个维度同时恶化。从InnoDB的存储结构说起。表数据按B树组织主键索引树的叶子节点存整行数据二级索引树的叶子节点存主键值。一个三层的B树大概能管理几千万行数据理论上走主键等值查询只需要三次I/O。那为什么实际线上这么慢关键在于随机I/O和缓存命中率。几千万行数据假设每行1KB总量就是几十GB远超出innodb_buffer_pool的容量很多服务器配8G或16G。数据页在内存和磁盘之间频繁换入换出这种“缓存颠簸”带来的随机磁盘I/O比顺序扫描还要命。你可以这么理解内存就相当于你的工位磁盘相当于几公里外的档案室每查一条数据都得跑去档案室翻一次——数据量一大来回跑的次数就指数上涨。还有一个很多人忽略的点——锁竞争。同一个热点行、同一个范围的记录被高频更新时行锁等待会直接把吞吐拖垮。尤其是那种“用户中心订单列表”类的场景用户频繁刷新页面后端反复查询同一批用户的订单数据锁等待和一致性读的开销会迅速堆积。所以说大表优化的第一课不是急着写SQL而是先算清楚你的查询到底触发了多少次物理I/O能不能把随机I/O变成顺序I/O能不能让更多热点数据留在内存里这三个问题想透了后面做的每一步优化才有的放矢。1.2 先诊断再优化用数据说话不要拍脑袋我的原则是任何优化动作之前必须先有数据支撑。常用三板斧慢查询日志定位具体SQLEXPLAIN看执行计划performance_schema做全局分析。慢查询日志是第一步。在MySQL里开启慢日志很简单但注意不要在生产上长时间开全局慢日志影响性能且日志膨胀很快。建议这样设置slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ONlong_query_time设为1秒对千万级表来说已经是比较严格的标准了。开一段时间后用mysqldumpslow按执行时间排个序看看TOP 10都是什么SQLmysqldumpslow -s t -t 10 /var/log/mysql/slow.log拿到慢SQL后立刻EXPLAIN看执行计划。重点关注四个字段字段含义危险信号type访问类型ALL全表扫描、index全索引扫描rows预估扫描行数与表总量同数量级时很危险key实际使用的索引NULL时说明没走索引Extra附加信息Using filesort、Using temporary都是性能杀手举个例子我在某个订单项目里定位过一条慢SQLEXPLAIN SELECT * FROM t_order WHERE status 1 ORDER BY create_time DESC LIMIT 20;执行计划显示typeALLrows3200万ExtraUsing filesort。三个危险信号全占了。这种SQL加再大的内存也没用因为优化器压根就没找到合适的索引路径只能把3200万行全捞出来再在内存里排序。这时候方案很明确建联合索引让WHERE和ORDER BY都能走索引。如果一条SQL在业务高峰期反复出现但又没法快速定位源头可以用performance_schema按语句指纹聚合统计SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 20;这个查询能直接告诉你哪些SQL扫描的总行数最多是真正的“吃性能大户”比单看慢日志更有全局视角。MySQL 5.7及以上还自带了sys schema用起来更顺手SELECT * FROM sys.statement_analysis WHERE rows_examined_avg 10000 ORDER BY rows_examined_avg DESC LIMIT 10;记住一句话没有执行计划分析的SQL优化都是耍流氓。你连它为什么慢都不知道改什么都是碰运气。2. 索引优化是性价比最高的一步索引是大表优化里投入产出比最高的一步。不需要改架构、不需要加机器建对几个索引很多慢查询能直接从秒级降到毫秒级。但索引也不是银弹建错了反而拖累写入性能。2.1 索引失效的常见场景这些坑你一定踩过先看最典型的索引失效场景我几乎在每个团队都见过第一个是对索引列做函数或运算。比如WHERE DATE(create_time) 2024-01-01这个写法你以为走了create_time索引其实MySQL要把每行都算一遍DATE函数索引就废了。正确写法是范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00第二个是隐式类型转换。如果phone字段是varchar类型查询条件写WHERE phone 13800138000MySQL会把字段转成数字去比较索引照样失效。反过来如果字段是bigint你却传了字符串同样失效。解决方法是保持类型一致字符串就加引号。第三个是前导模糊查询。WHERE name LIKE %张%这种查询在B树上没法定位起点只能全索引扫描。如果业务确实需要要么考虑用全文索引要么换搜索引擎要么老老实实接受全表扫。但LIKE 张%这种后缀匹配是可以用到索引的。第四个是OR条件使用不当。WHERE status 1 OR user_id 100即使两个字段都有索引优化器也可能放弃索引合并转而全表扫描。改写思路是拆成两个查询UNION ALL或者确保两边都能走索引后用UNION合并。第五个是复合索引没按最左前缀来。建立了(a, b, c)复合索引查询条件是WHERE b 1 AND c 2这个查询用不上索引。这是MySQL复合索引的基本规则后面单独展开讲。这里有个容易误解的地方WHERE status ! 1这种不等于条件不一定就全表扫如果该列区分度很高优化器也可能走索引。所以不要背死书一切以EXPLAIN结果为准。2.2 联合索引与覆盖索引大表查询提速的核心武器千万级表上单列索引往往不够用联合索引才是主角。设计联合索引要记住一个口诀等值条件在前排序字段紧随范围条件放最后。举例说明。订单查询高频场景是“查某个用户某个状态下最近的订单”SELECT * FROM t_order WHERE user_id 10086 AND status 1 ORDER BY create_time DESC LIMIT 20;索引应该建(user_id, status, create_time)而不是(status, user_id, create_time)更不是三个单列索引。为什么因为user_id是等值条件能最大程度缩小范围status也是等值create_time用来排序索引天然有序就可以避免filesort。再说覆盖索引。如果查询只需要某几列把这些列都放进索引里MySQL就不需要回表去主键索引捞整行数据了。这个优化在大表上效果极其明显。看一个真实案例。订单列表页的统计接口需要查某个用户累计下单金额SELECT SUM(amount) FROM t_order WHERE user_id 10086;建一个(user_id, amount)的联合索引这条SQL就完全不需要回表直接从二级索引里就能拿到所有amount值做聚合。对于几千万行的表回表次数从几万次降到接近零性能差距是数量级的。我自己总结的索引设计流程是这样的先收集业务里的高频查询按频率排序然后针对每条SQL分析WHERE、ORDER BY、GROUP BY涉及的列接着按“等值列优先、排序列次之、范围列垫底”的顺序组合成联合索引最后用EXPLAIN验证优化器是否真的在用这个索引。千万别一上来就建七八个索引真正用得上的往往就那么两三个。2.3 索引维护与冗余清理不加管理的索引是负债索引不是建完就完事了它是B树每次INSERT、UPDATE、DELETE都要同步维护会拖慢写入速度、放大日志量、占用额外磁盘。很多大表的写入慢恰恰是因为索引建得太多太杂。线上常见的两种索引问题冗余索引和重复索引。(a, b)和(a)就是冗余关系后者完全被前者覆盖属于白白消耗写入性能。(a, b)和(b, a)不算重复查询条件不同可能有各自的价值。判断方法是看优化器是否真的用到了。清理索引前先用两张视图诊断一下-- 查看从未被使用的索引注意这是基于当前统计的 SELECT * FROM sys.schema_unused_indexes; -- 查看冗余索引 SELECT * FROM sys.schema_redundant_indexes;有冗余索引的在业务低峰期删除就好。但大表加索引或者删索引要特别小心几千万行直接ALTER TABLE会长时间锁表业务直接不可用。保险的做法是用在线DDL低峰执行ALTER TABLE t_order ADD INDEX idx_user_status_time (user_id, status, create_time), ALGORITHMINPLACE, LOCKNONE;MySQL 5.6以后才支持这个语法8.0默认就是INPLACE。但即使如此几千万行的表构建索引也要跑一段时间建议先用pt-online-schema-change这类工具在业务几乎无感知的情况下完成索引变更。我曾在5000万行的表上加过索引用原生ALTER跑了40分钟中间有一段时间CPU和磁盘I/O飙升得很厉害业务虽然没有完全锁死但明显感觉到了延迟抖动。后来换用pt-osc来控制节奏情况好很多。3. 高频SQL与大表分页改造索引建对了基础查询就快了。但在大表场景里还有一批SQL是“建了索引也救不回来”的——深分页、多表关联、大聚合。这些SQL就得从写法上动刀。3.1 深分页优化16秒到50ms的实战先看一个经典场景。订单列表接口前端要展示第500页的数据SQL长这样SELECT * FROM t_order WHERE user_id 10086 ORDER BY create_time DESC LIMIT 500000, 20;这条SQL慢就慢在LIMIT 500000, 20。MySQL的LIMIT是先扫描前500020行然后丢弃前50万行只返回最后20行。扫描的行越多越慢而且越往后翻越慢这就是“深翻页”问题。优化的核心思路是不要扫描那么多行只要找到那20行的主键。有两个主流方案。方案一延迟关联。先用覆盖索引找出目标主键再回表取完整数据SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order WHERE user_id 10086 ORDER BY create_time DESC LIMIT 500000, 20 ) tmp ON t.id tmp.id;关键在于内层子查询只需要扫描二级索引不需要回表扫描500020行索引的成本远低于扫描500020行完整数据。我在一个千万级订单表上做过对比延迟关联比直接LIMIT快了一个数量级。方案二游标分页也叫keyset分页。把LIMIT换成“记住上一页最后一条”利用索引定位SELECT * FROM t_order WHERE user_id 10086 AND (create_time, id) (2024-06-01 10:30:00, 10086) ORDER BY create_time DESC, id DESC LIMIT 20;create_time有重复所以排序时加上id作为第二条件这样定位条件就完全确定走的是索引范围扫描。理想情况下每页都是毫秒级而且不随页数增加而变慢。缺点是不能随意跳页只适合“下一页/上一页”的交互场景。两种方案选哪个看业务需要跳页就选延迟关联性能提升也够只需要顺序翻页就选游标分页体验最好。我在实际项目里两种都试过游标分页对于千万级表几乎是“永远不慢”的方案强烈推荐给列表类场景。对分页方案有纠结的话列个对比方案适用场景优势劣势传统LIMIT数据量小、页码浅实现简单深翻页性能急剧恶化延迟关联需要跳页、数据量大性能稳定提升内层子查询仍需扫描较多索引页游标分页顺序翻页流式加载每页毫秒级、不随页码退化无法跳页3.2 join与子查询改写别让大表相互折磨多表关联在千万级表上是重灾区。常见问题不是“join本身有多慢”而是关联字段没有索引、驱动表选错、或者关联前没有先过滤数据。驱动表的选择很关键。MySQL的join一般是左表驱动右表优化器会倾向用小表当驱动表但偶尔会抽风。实际业务中宁可自己控制也不赌优化器。看一个例子SELECT u.id, u.name, o.order_no FROM t_user u INNER JOIN t_order o ON o.user_id u.id WHERE u.created_at 2024-01-01 LIMIT 100;t_user过滤后可能只有几千行但如果没有t_order(user_id)索引每一次关联都要全表扫描t_order的3200万行后果可想而知。所以第一件事就是确保关联字段有索引。如果确认优化器选错了驱动表可以用STRAIGHT_JOIN强制顺序SELECT u.id, u.name, o.order_no FROM t_user u STRAIGHT_JOIN t_order o ON o.user_id u.id WHERE u.created_at 2024-01-01 LIMIT 100;还有一类问题是子查询IN的写法。MySQL 8.0.16以前对IN子查询的优化很一般特别是子查询结果集大的时候。改写建议小结果集的IN问题不大大结果集优先转JOIN。举个例子-- 低效写法 SELECT * FROM t_order WHERE user_id IN (SELECT id FROM t_user WHERE status 0); -- 改写为JOIN SELECT o.* FROM t_order o INNER JOIN t_user u ON o.user_id u.id WHERE u.status 0;需要注意的是改写后如果t_user过滤出的数据量太大JOIN也可能撑不住。更好的做法是先评估子查询的结果集大小几万行以内可以接受几十万行以上就要考虑到底层数据模型是不是该调整了。3.3 count与聚合优化别把精确统计当唯一选项千万级表上做SELECT COUNT(*) FROM t_order绝对是个灾难。InnoDB不像MyISAM那样存了精确行数COUNT(*)必须逐行统计全表扫完才出结果。如果你的业务每次刷新页面都要实时精确总数几千万行下来CPU再强也扛不住。优化手段按业务要求分级如果只要近似值直接看执行计划EXPLAIN SELECT COUNT(*) FROM t_order;rows字段就是优化器估算的行数误差在10%以内很常见用来显示“约XXXX万条”完全够用。我做过一个数据大屏项目就是这么干的数据库零压力。如果业务确实需要精确值且实时性要求高那就不该直接查大表应该在业务层维护一张计数器表每次下单事务里同步更新。这样查询走的是几张几百行的小表秒出结果。代价是引入额外的事务逻辑注意并发更新的行锁问题。如果精确值只需要低频更新那就后台定时统计把结果落到缓存或统计表里。比如每个小时算一次总订单数页面直接读缓存。聚合查询同理。GROUP BY经常带出Using temporary和Using filesort本质上是中间结果集太大。优化思路很直接先过滤再分组、先缩小范围再聚合以及尽量让GROUP BY走索引。比如“统计某大客户最近一个月的订单数”不要全表GROUP BY先限制user_id和日期范围SELECT user_id, COUNT(*) FROM t_order WHERE user_id 10086 AND create_time 2024-05-01 GROUP BY user_id;4. 参数调整与架构分层SQL和索引层面优化完如果大表性能还是吃紧就要看数据库本身的配置和架构了。这一层的调整影响面大必须循序渐进。4.1 关键参数调优不是参数越大越好很多人的第一反应是把innodb_buffer_pool_size调大这本身没错但要用在刀刃上。InnoDB的数据和索引缓存都在这块内存里大表场景下缓存命中率决定了绝大多数查询的响应速度。我的习惯是如果服务器内存32G会把buffer pool设为20G左右同时观察命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;命中率 1 - reads / read_requests长期低于95%就说明内存确实不够调大buffer pool会有效果。但如果命中率已经99%以上调再大也没意义瓶颈在别处。另一个值得调的是redo log容量。MySQL 8.0.30以前用innodb_log_file_size之后用innodb_redo_log_capacity。默认值偏小大表高频写入时频繁的checkpoint会拖累性能。建议设置为4G或8G[mysqld] innodb_buffer_pool_size 20G innodb_redo_log_capacity 8G max_connections 500 max_allowed_packet 64M tmp_table_size 64Mmax_connections别贪大连接本身吃内存开太多容易OOM。tmp_table_size设置多大取决于你是否有大量GROUP BY和子查询过大可能把临时表都放进内存触发swap反而更糟。这里说句得罪人的话网上很多“MySQL配置优化20项”之类的文章照着抄只会害了你。每个业务的读写比、数据量、并发模型完全不一样唯一的正确路径是先监控、再调整、后验证。调整某个参数前后用压测对比QPS和延迟没有改善就回滚。4.2 读写分离与分库分表架构侧的最后手段如果索引、SQL、参数都优化到位单库单表还是扛不住才轮到架构层面的调整。但这是拆解复杂度的开始一旦引入你的系统就再也不是“一个连接串搞定一切”那么简单了。先说读写分离。互联网应用普遍读多写少把读流量分摊到从库能直接减轻主库的压力。工具上可以用ProxySQL、MaxScale或者在代码层配置多数据源。需要注意的坑是主从延迟刚写入的数据立即去从库查可能查不到。实践中的做法是“写后读关键路径走主库”用户的订单支付成功页、登录后的用户信息查询这类必须强一致的走主库历史列表、报表查询这类弱一致场景走从库。再说分库分表。单表超过两千万行或者单表容量超过几十GB并且索引和SQL优化已经做到位才考虑水平拆分。水平拆分的关键是选择分片键。用户维度的业务按user_id分时间维度的流水按时间范围分。分片键一旦选定所有查询都必须带上它否则中间件就要广播到所有分片执行性能灾难。所以我强烈建议在建表之初就预留分片键字段即使现在还用不上。很多团队上线时图省事没留等到数据量爆炸再迁移那就是一场手术级别的工程。具体工具上ShardingSphere是目前生态比较成熟的支持分片、读写分离、数据加密。MyCat也有团队在用但近几年的活跃度大不如前。还有一条路线是直接用MySQL原生分区表比如按时间范围分区CREATE TABLE t_order ( id BIGINT NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(10,2), PRIMARY KEY (id, create_time) ) PARTITION BY RANGE (YEAR(create_time)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN MAXVALUE );原生分区表的优点是应用层无感知查询条件里带上分区键就能自动裁剪分区。但它也有不少限制分区字段必须包含在主键里跨分区查询不一定快以及底层索引仍然是全局的复杂查询不一定能利用分区裁剪。我的建议是分区表适合有明显的冷热时间分布、按范围访问的场景比如日志流水、订单历史。它不是分库分表的替代品更多是归档查询的辅助手段。4.3 冷热数据归档大表“做减法”才是最快的优化前面讲的都是让查询走得更快但有时候最有效的优化是把表变小。冷热数据分离的核心是把访问频率低的历史数据从主表里挪出去。比如订单表保留最近12个月的数据更早的迁到t_order_archive归档表或者迁到ClickHouse之类的大数据组件做离线分析。主表体量下降后索引的层级更浅、缓存命中率更高、写入的索引维护成本更低这是治本的方法。归档的工具有现成的pt-archiver可以按条件批量把数据搬走边读边删不会一次性锁大量行pt-archiver --source hlocalhost,Dtest,tt_order \ --where create_time 2023-01-01 \ --dest hlocalhost,Dtest,tt_order_archive \ --limit 1000 --commit-each --bulk-delete--limit和--commit-each控制每个批次处理的行数--bulk-delete是批量删除操作的开关。跑之前一定要先在测试库验证确认不会影响在线写入。另外一个大字段的优化如果大表里混着TEXT或BLOB类型的大字段这类数据行体积大会把整个表的索引和缓存效率都拖垮。合理的做法是拆出去放到独立的扩展表主表只保留一个外键。查询列表页只访问主表详情页再按需关联扩展表。我自己做归档时的心得是不要一次性冲到深夜任务里把几千万行跑完服务端压力和主从延迟会传染到其他业务。分多个批次、控制速率、监控主从延迟一旦delay超过阈值就暂停等追平再继续。5. 常见问题与排查技巧实录最后这部分聊聊实际项目中遇到的案例和排查思路。有些问题排查起来非常绕记录一下能给后来人省不少时间。5.1 真实案例一深分页优化从16秒到50ms去年接手过一个用户订单中心的性能工单。线上反馈“第100页之后的订单列表打开特别慢”实测接口耗时基本在15秒以上。定位过程是这样的先看慢日志发现一条LIMIT偏移量很大的SQL频繁出现。EXPLAIN一看typeALLrows2800万ExtraUsing filesort。虽然表上有user_id索引但ORDER BY create_time没有索引支持MySQL只能先把该用户的订单全部捞出来再排序再丢弃前50万行最后返回20行。优化动作分两步先建(user_id, create_time)联合索引让排序走索引再把外层查询改成延迟关联。改完后单条查询耗时从16秒降到50ms附近接口整体返回时间直接降了两个数量级。这个案例给我最大的启发是深分页的“深”不是靠硬件堆出来的而是靠走索引和减少回表来解决的。优化思路一定要落在“让数据库少干活”上而不是“让数据库干得更快”。加内存、换SSD是必要的但那是最后兜底的手段。5.2 真实案例二一次锁等待引发的雪崩另一个案例是某次大促期间一个订单状态更新接口突然大量超时。监控看数据库线程堆积严重SHOW PROCESSLIST发现几十个线程都在等待同一个UPDATE语句UPDATE t_order SET status 2 WHERE status 1;问题很明显这个UPDATE没有利用索引是全表扫描的效果——每行都要锁一下、判断一下、决定是否更新。几千万行的表一次更新把几乎所有行都碰了一遍期间产生的锁竞争可想而知。排查时先用SHOW ENGINE INNODB STATUS确认锁等待确实集中在同一行范围然后检查t_order的索引情况发现status字段居然没有索引。给status建了索引后UPDATE的扫描范围从几千万行缩小到符合条件的几万行锁持有时间从一个量级降到了另一个量级接口超时问题随即消失。这个案例想提醒的是DML同样需要索引而且比SELECT更重要。SELECT慢只是查询迟一点返回UPDATE慢可能直接把整个库锁死。5.3 常用监控与巡检命令运维大表的日常操作列一份我日常巡检大表时高频使用的命令直接拿去用-- 实时查看正在执行的SQL SHOW FULL PROCESSLIST; -- 查看当前锁等待 SELECT * FROM sys.innodb_lock_waits; -- 查看InnoDB整体状态包括死锁、事务、缓冲池 SHOW ENGINE INNODB STATUS; -- 查看表大小TOP10 SELECT table_name, ROUND(((data_length index_length) / 1024 / 1024 / 1024), 2) AS size_gb FROM information_schema.tables WHERE table_schema your_db ORDER BY size_gb DESC LIMIT 10; -- 查看某个表的碎片率 SELECT table_name, data_free / 1024 / 1024 AS frag_mb FROM information_schema.tables WHERE table_schema your_db AND data_free 0;表碎片率也是个容易被忽略的细节。频繁UPDATE和DELETE后表内部会产生大量空洞导致扫描的物理页比实际数据多得多。对大表做OPTIMIZE TABLE有时能带来意外惊喜但同样要注意锁表风险最好用ALTER TABLE ... ENGINEInnoDB或者pt-table-rebuild这类工具在低峰期处理。日常巡检节奏建议每天看一次慢日志TOP SQL每周看一次全表扫描和未命中索引的SQL每月做一次索引使用率评估。很多大表问题是慢慢积累出来的定时体检永远好过故障时抢救。5.4 排查心法先看执行计划再动SQL排查优化类问题我的顺序永远是固定的先确认现象再收集证据再定位慢在哪一步最后才动手改。很多人上来就把SQL改一版试一下运气好碰对了就宣称优化成功实际上根本没有沉淀出方法论。具体到MySQL大表优化证据就是你手里的EXPLAIN结果。它对一条SQL的执行路径描述已经很清楚了type从ALL变成range或refkey从NULL变成实际使用的索引名rows从几千万降成几百Extra里的Using filesort和Using temporary消失这些指标任何一个改善都意味着实实在在的性能提升。在执行任何DDL之前一定要评估影响。给千万级表加索引、改字段、做归档都必须选定低峰期并且准备好回滚方案。越是性能吃紧的线上环境越要谨慎操作因为一旦锁表你面对的可能不只是慢查询而是整个业务的不可用。写在最后的个人体会如果让我给MySQL大表优化排个优先级我的排序是SQL写法 索引设计 参数调优 架构拆分。先把那些明显不合理的查询改掉再让索引高效地支撑起这些查询然后调参数锦上添花最后才轮到分库分表这种重量级方案。在实际操作中我发现很多团队把顺序搞反了——表一大就喊着分库分表结果分完之后发现主键、外键、查询逻辑全部要改中间件运维复杂度直线上升而性能问题并没有完全消失因为最根本的慢SQL逻辑没有被修正。还有一条要提醒做任何优化都要有对比数据支撑。改之前记录QPS、平均延迟、慢查询数量改之后再做同样的测量用数据说话而不是凭感觉说“快多了”。这套方法不止适用于MySQL大表几乎所有的数据库性能优化问题都可以套用。最后分享一个小技巧优化完之后最好保留当时的EXPLAIN结果和执行时间记录写入团队的wiki。大表优化不是一次性工作业务数据量持续增长同一套方案三个月后可能又失效了有历史记录做对比下次迭代的速度会快很多。Keep it simple, keep it measured。