一说MySQL优化很多人脑子里立刻飘出三个词索引、慢日志、调buffer。可真到生产环境扛过高并发、半夜爬起来看慢SQL之后你会发现优化根本不是某个单点招式的堆砌而是一套“定位瓶颈→提出假设→验证效果”的流程。这篇是MySQL优化系列的第一篇不打算一口气铺几十个参数而是把我平时排查SQL、设计索引、调配置时真正用到的思路和套路讲清楚。刚入门的开发者可以从头理顺方向写过一段时间后端、被线上问题折腾过的同学也可以对照检查自己是不是踩过同样的坑。1. 动手优化之前先搞清楚“慢”在哪里1.1 先回答三个问题别急着猜一个系统慢了先别急着打开慢查询日志找SQL。我习惯先逼自己回答三个问题慢是接口慢、页面慢还是只有数据库慢慢是持续的还是只在某个时间段出现的慢是不是集中在某几张表、某几个查询上这三个问题看起来朴素很多优化做无用功恰恰是因为跳过了它们。比如“接口慢”可能是跨服务调用超时“间歇慢”可能是连接池被短暂打满只有“持续慢”才大概率是SQL本身的问题。如果把“系统慢”直接等同于“MySQL慢”你很可能花一整天在数据库里找原因最后发现是别的服务拖了后腿。我自己就干过这种事线上接口偶尔卡顿我对着数据库查了半天执行计划最后才发现是上游一个第三方接口超时。所以先划清边界比什么都重要。1.2 用一套固定动作确认瓶颈确认瓶颈的动作我现在基本固定成一套先看系统监控包括CPU、内存、磁盘IO再用show processlist看当前正在跑的会话重点找“长查询”和“锁等待”然后开慢查询日志把1秒以上的SQL捞出来最后用EXPLAIN看集中出现的问题SQL。Linux服务器上我还会用vmstat和iostat看一眼磁盘是否已经很吃力。如果磁盘IO常年打满那你优化一百个SQL也抵不上先把慢盘换掉。这套动作最多花十分钟能让你避免把方向搞反。慢查询日志的临时开启方式也很简单SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;观察一天左右再用mysqldumpslow做汇总就能看到到底哪些SQL是高频慢查询哪些只是偶发。注意这种临时开启的方式重启后失效生产环境建议直接写进配置文件后面第4章会细说。1.3 三种典型的“假优化”操作这里说三个我踩过、也看别人反复踩的坑。第一盲目调大innodb_buffer_pool_size觉得内存够大就完事如果慢SQL本身在扫全表buffer再大也只是把全表扫从磁盘搬到内存根子没解决。第二看到EXPLAIN里type显示index就觉得万事大吉index本身是全索引扫描和ALL一样需要遍历。第三拿Redis缓存去掩盖慢SQL缓存确实挡掉了流量但SQL没修缓存击穿或穿透那一刻积压的查询会以更凶猛的姿态打回数据库。假优化最大的问题是短期指标好看长期隐患没解决。我见过一个项目每天定时任务跑一次慢SQLDBA就加一个索引三个月下来表上加了二十多个索引写入慢得离谱最后还是回滚掉大半才恢复正常。所以优化前先把“慢SQL清单”和“现有索引清单”拉出来这一步真不能省。2. 索引不是越多越好先搞懂生效与失效2.1 最左前缀原则很多人理解偏了联合索引(a, b, c)到底怎么走核心不是你的SQL里把a写在where的第一位而是where条件里有没有带上最左边的a列。where b? and a?会自动被优化器重排依然能用到a。真正会让索引失效的是where b?、where c?这种没有a的写法。还有一点容易被忽略范围条件左边的列能用索引范围条件右边的列会失效。where a? and b10 and c?c这个条件只能做过滤不能再用来继续缩小索引范围。理解这个规则之后你再看那些网上常见的“联合索引最左前缀”口诀会清楚很多——它不是让你背顺序而是逼你思考每个查询条件在索引树上的作用位置。2.2 三种常见的索引失效写法第一种隐式类型转换。比如phone字段是varchar你写where phone13800138000MySQL会把字段转成数字再做比较等于在字段上套了一个隐式函数索引失效。改成where phone13800138000就好。第二种在字段上套函数。where DATE(create_time)2025-01-01不走索引建议改成create_time 2025-01-01 00:00:00 and create_time 2025-01-02 00:00:00这样还能让范围查询命中索引。第三种前导通配符。like %abc%用不上索引非要模糊搜索要么改成like abc%要么考虑全文索引或单独建搜索服务。这三种情况有个共同点不是索引坏了而是你在索引列上做了“加工”让优化器没法按原样去B树里找。2.3 优化器不选索引时别硬刚加了索引不代表优化器一定用。最常见的原因是选择性太低。比如性别字段区分度不高优化器一算回表成本比全表扫还高就会放弃。另一个原因是统计信息过期可以执行ANALYZE TABLE刷新一下。还有一个很容易被忽略当表里同时有几个索引都能用优化器会根据rows估算选择它认为成本最小的那个而不是一定选你新加的那个。所以判断该不该给某个字段建索引可以先算区分度SELECT COUNT(DISTINCT col) / COUNT(*) FROM t;比例越小越不值钱低于0.1基本不要指望它扛起优化大旗。我在实际项目里见过有人给is_deleted这种只有0和1的字段建索引结果查询计划依然走全表扫纯属建了个寂寞。2.4 联合索引设计的实用顺序设计联合索引时我一般按这个顺序排等值条件列放前面需要排序的列放在中间范围查询列尽量放后面最后再看能不能利用覆盖索引。比如订单表经常按user_id查并按create_time排序那么idx_user_created(user_id, create_time)就比单独的idx_user(user_id)更好因为排序字段直接在索引里ORDER BY create_time就不用额外做filesort。如果查询还经常带status1的条件可以考虑(user_id, create_time, status)让Extra里直接出现Using index。当然索引会占空间、拖慢写入不要见条件就建每加一个索引都要清楚它是为哪几个高频查询服务的。索引不是勋章建得越多越光荣它是成本只有收益大于成本才值得。3. 慢SQL改写实战从EXPLAIN到案例落地3.1 拿到慢SQL先学会读EXPLAIN把慢SQL捞出来后我第一件事永远是执行EXPLAIN。只需要看四个关键字段type、key、rows、Extra。type是访问类型按效率从好到坏大致是system、const、eq_ref、ref、range、index、ALL看到index和ALL就要警惕这代表在遍历。key表示实际用到的索引NULL就是没走。rows是优化器估算的扫描行数这个数字越大越危险。Extra里出现Using filesort或Using temporary通常意味着排序和临时表成了瓶颈。先看懂这四个字段后面改SQL才有依据。很多同学拿到一条慢SQL不看执行计划就凭感觉加索引、改写法结果改了等于没改就是因为根本没确认瓶颈是扫描行数、排序还是临时表。同一个问题三种解法用错地方就是白干。3.2 从“回表”到“覆盖索引”省一次随机IO给个例子。订单表有id, user_id, status, amount, create_time常见查询是SELECT id, status FROM orders WHERE user_id 123;如果只有idx_user(user_id)这一条索引MySQL先通过索引找到user_id123的叶子节点拿到主键id再回表读整行取出status。回表是一次随机IO数据量大时很疼。优化方式是把查询需要的列都塞进索引ALTER TABLE orders ADD INDEX idx_user_status_id (user_id, status, id);这样查询需要的字段在idx_user_status_id的索引树上就能全部拿到Extra显示Using index不需要回表。这就是覆盖索引。别小看这个优化我见过同一套查询在千万级表上从800ms降到50ms出头。代价是索引变宽、写入变慢所以只对高频查询做覆盖别什么列都往里塞。3.3 深分页优化LIMIT 1000000, 20为什么会慢分页是慢SQL重灾区。LIMIT 1000000, 20慢不是慢在只取20条而是MySQL要把前1000020行都扫出来再丢掉。如果ORDER BY字段没有索引还要先做一次filesort代价翻倍。第一种优化是延迟关联先只查主键ID字段做分页再用主键回原表取完整行SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY create_time LIMIT 1000000, 20) tmp ON o.id tmp.id;第二种更推荐游标分页。前端记住上一页最后一条记录的create_time和id下一页查询变成SELECT * FROM orders WHERE create_time 2025-03-01 10:00:00 ORDER BY create_time DESC, id DESC LIMIT 20;注意ORDER BY必须带上主键或唯一列做辅助排序否则数据顺序不稳定翻页会出现重复或漏数据。这个方法对大数据量的深度翻页几乎是决定性优势无限滚动的列表基本都用这种思路。3.4 关联查询驱动表和JOIN条件下的细节关联查询优化里“小表驱动大表”是句老话但实际要看EXPLAIN第一行是谁也就是优化器选的驱动表。给被驱动表的关联列建索引是关联查询优化的关键。假设A表1000行、B表100万行JOIN条件是A.id B.a_id那么给B.a_id建索引后每扫A一行就能走索引去B里查扫描行数大幅下降。反过来如果B.a_id没索引每行驱动表都要全表扫B查询直接爆炸。还有子查询。MySQL对IN (SELECT ...)很多时候能优化成半连接但遇到复杂条件时可能性能不佳这时候拆成JOIN或临时表反而稳。我自己还有个原则不要在JOIN后的结果上做ORDER BY RAND()也别在关联字段上写函数否则索引用不上。关联查询的性能问题十有八九出在“小结果集驱动大结果集”这个顺序没被优化器善待或者关联列索引缺失。4. 配置参数里真正值得动的几个开关4.1 innodb_buffer_pool_size先看命中率再动手InnoDB的缓冲池是MySQL缓存数据和索引的主战场默认只有128M对稍微有点业务的服务器来说远远不够。但在调之前我建议先看命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;用Innodb_buffer_pool_read_requests除以Innodb_buffer_pool_reads可以算出读请求能从内存直接命中的比例。如果命中率已经很高调大buffer收益有限如果命中率低且大量读请求落在磁盘才值得加大。生产环境可以按物理内存的60%-75%来设8.0里可以动态调整SET GLOBAL innodb_buffer_pool_size 8 * 1024 * 1024 * 1024;动态调整之后观察效果确认有效再写进my.cnf持久化。千万别把内存吃光要给OS和连接线程留余地否则系统把内存页换出性能反而更难看。4.2 刷盘策略在性能和数据安全之间做选择这个参数是优化里典型的两难innodb_flush_log_at_trx_commit。设置为1每次事务提交都把redo log刷到磁盘最安全但最慢设置为0靠后台每秒刷一次性能最好但MySQL一旦崩溃可能丢最近1秒事务设置为2每次提交只写到操作系统缓存每秒再真正刷盘性能和安全各让一步。我的建议核心交易、财务类数据老老实实用1日志类、非核心业务能接受崩溃丢最后一秒数据才考虑20一般只在压测时看一眼生产不推荐。如果开了binlogsync_binlog和它要统一考虑不然宕机恢复时主从或事务一致性会出问题。很多人在“优化”旗号下把刷盘策略改成0表面上写入快了真遇到断电或崩溃你会发现丢数据的代价远大于那点性能提升。4.3 连接数、连接池和慢日志别让连接成为隐性瓶颈MySQL默认max_connections是151线上常遇到Too many connections。这个错误看着像连接数不够根源却常常是某几条慢SQL占着连接不放后面的请求全部排队。你把max_connections调成1000慢SQL一多照样被打穿还更容易把数据库内存耗尽。正确做法是先查SHOW STATUS LIKE Threads_connected;再看processlist里有没有大量query time很长的会话把慢SQL解决了连接数自然回落。慢查询日志的配置也有讲究slow_query_log 1 slow_query_log_file /var/lib/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1长期开着你会发现优化有方向。高并发业务可以把long_query_time调到0.5秒但注意日志量会涨磁盘空间要提前规划。我见过有人把log_queries_not_using_indexes打开后日志几分钟就写满几个G——这其实是在提醒你系统里没有索引的查询比你想象的多得多。4.4 版本选择和初始化配置优化从安装那刻就开始了参数优化绕不开版本。不少人还在用5.7甚至5.6新项目我建议直接上8.0因为8.0默认字符集是utf8mb4支持窗口函数、CTE优化器也改进了不少。网上搜mysql安装教程或mysql下载常常搜到一堆互相冲突的步骤我的经验是安装之后第一件事别急着导业务数据先确认character_set_server、default_storage_engine、max_connections、innodb_buffer_pool_size这几个基础项。再强调一次初始化时就把sql_mode确认好别等上线后因为SQL模式不一致出现诡异的报错。版本差异还会体现在排序和字符比较规则上8.0和5.7对utf8mb4的处理已经完全不同。不然以后你调参调得再好底层配置一开始就没立住后面早晚补课。5. 把优化前移到应用层连接池、缓存与分库分表边界5.1 连接池参数到底怎么设数据库扛不住很多时候是应用层连接池不会用。Java里常见的HikariCP我见过有人把maximumPoolSize直接设成500理由是并发高。结果呢数据库连接数被打满线程调度和上下文切换本身成了瓶颈。连接池不是越大越好建议先在低配置下压测再逐步调大观察。HikariCP可以从maximumPoolSize30、minimumIdle5起步根据CPU核数和业务平均响应时间再调。Druid的话重点看initialSize、maxActive、maxWaitmaxWait别设太小不然连接获取一抖动就开始报获取超时。核心思路是连接是稀有资源宁可让少量请求排队也别用海量连接把数据库压死。我调过最夸张的一个案例连接池从200降到40之后数据库CPU反而从90%降到30%原因就是大量连接都在空转等锁纯属内耗。5.2 缓存挡流量但拦不住击穿和穿透查询量大到一定程度Redis这类缓存是必备的但缓存不是优化MySQL的借口。用缓存最大的三个坑缓存雪崩、缓存击穿、缓存穿透。雪崩指大量key同时过期流量直接打到数据库破解办法是过期时间加随机值别整点集体失效。击穿指某个热点key失效的一瞬间大量请求同时去数据库重建缓存破解办法是互斥锁或逻辑过期让一个请求负责回源。穿透指查询一个根本不存在的key缓存和数据库都查不到于是每次查询都打库破解办法是空值缓存、布隆过滤器。这三个问题处理不好MySQL优化做得再好也会被流量冲垮。我在项目里吃过击穿的亏一个热门商品详情页的缓存刚好过期瞬间几千个请求同时去查库数据库连接直接被打满后面所有正常查询也一起遭殃。从那以后热点数据重建缓存必须加互斥锁这个习惯我一直保留到现在。5.3 读写分离和分库分表是优化还是重构要想清楚当单库读压力大、写量相对可控时读写分离是性价比很高的方案主库扛写入从库扛读。但要注意主从延迟刚写完立刻读可能读到旧数据业务上要做兜底比如强制走主库的读接口。读写分离解决了“读多写少”的问题但解决不了单表数据量过大的问题。至于分库分表我见过太多团队在数据量刚过千万时就开始拆结果分布式事务、跨节点排序、全局ID全来了。我的判断标准很简单单表数据量继续膨胀导致性能明显下降或者单个实例的CPU/IO长期打满其他手段都试过之后再考虑分库分表。这不是MySQL优化的常规动作更像一次架构重构应该单独立项做。真要走到这一步那是另一个系列的话题这篇先把思路立住就够了。最后分享一点我在实际项目里的体会。MySQL优化真正难的不是某一个知识点而是你愿不愿意先把“慢在哪”搞清楚。把慢查询日志开起来把索引清单整理出来把配置基线记录下来遇到问题按“先定位、再假设、后验证”的顺序走大部分性能问题都能在SQL和表设计这层解决掉。这篇是系列第一篇后面我会接着写EXPLAIN的完整解读、锁等待排查、主从与高可用这些方向。如果你在照着操作时发现某个参数调了没效果欢迎带着执行计划和表结构来讨论一起把坑填平。
MySQL优化实战：从定位瓶颈到索引与慢SQL排查
一说MySQL优化很多人脑子里立刻飘出三个词索引、慢日志、调buffer。可真到生产环境扛过高并发、半夜爬起来看慢SQL之后你会发现优化根本不是某个单点招式的堆砌而是一套“定位瓶颈→提出假设→验证效果”的流程。这篇是MySQL优化系列的第一篇不打算一口气铺几十个参数而是把我平时排查SQL、设计索引、调配置时真正用到的思路和套路讲清楚。刚入门的开发者可以从头理顺方向写过一段时间后端、被线上问题折腾过的同学也可以对照检查自己是不是踩过同样的坑。1. 动手优化之前先搞清楚“慢”在哪里1.1 先回答三个问题别急着猜一个系统慢了先别急着打开慢查询日志找SQL。我习惯先逼自己回答三个问题慢是接口慢、页面慢还是只有数据库慢慢是持续的还是只在某个时间段出现的慢是不是集中在某几张表、某几个查询上这三个问题看起来朴素很多优化做无用功恰恰是因为跳过了它们。比如“接口慢”可能是跨服务调用超时“间歇慢”可能是连接池被短暂打满只有“持续慢”才大概率是SQL本身的问题。如果把“系统慢”直接等同于“MySQL慢”你很可能花一整天在数据库里找原因最后发现是别的服务拖了后腿。我自己就干过这种事线上接口偶尔卡顿我对着数据库查了半天执行计划最后才发现是上游一个第三方接口超时。所以先划清边界比什么都重要。1.2 用一套固定动作确认瓶颈确认瓶颈的动作我现在基本固定成一套先看系统监控包括CPU、内存、磁盘IO再用show processlist看当前正在跑的会话重点找“长查询”和“锁等待”然后开慢查询日志把1秒以上的SQL捞出来最后用EXPLAIN看集中出现的问题SQL。Linux服务器上我还会用vmstat和iostat看一眼磁盘是否已经很吃力。如果磁盘IO常年打满那你优化一百个SQL也抵不上先把慢盘换掉。这套动作最多花十分钟能让你避免把方向搞反。慢查询日志的临时开启方式也很简单SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;观察一天左右再用mysqldumpslow做汇总就能看到到底哪些SQL是高频慢查询哪些只是偶发。注意这种临时开启的方式重启后失效生产环境建议直接写进配置文件后面第4章会细说。1.3 三种典型的“假优化”操作这里说三个我踩过、也看别人反复踩的坑。第一盲目调大innodb_buffer_pool_size觉得内存够大就完事如果慢SQL本身在扫全表buffer再大也只是把全表扫从磁盘搬到内存根子没解决。第二看到EXPLAIN里type显示index就觉得万事大吉index本身是全索引扫描和ALL一样需要遍历。第三拿Redis缓存去掩盖慢SQL缓存确实挡掉了流量但SQL没修缓存击穿或穿透那一刻积压的查询会以更凶猛的姿态打回数据库。假优化最大的问题是短期指标好看长期隐患没解决。我见过一个项目每天定时任务跑一次慢SQLDBA就加一个索引三个月下来表上加了二十多个索引写入慢得离谱最后还是回滚掉大半才恢复正常。所以优化前先把“慢SQL清单”和“现有索引清单”拉出来这一步真不能省。2. 索引不是越多越好先搞懂生效与失效2.1 最左前缀原则很多人理解偏了联合索引(a, b, c)到底怎么走核心不是你的SQL里把a写在where的第一位而是where条件里有没有带上最左边的a列。where b? and a?会自动被优化器重排依然能用到a。真正会让索引失效的是where b?、where c?这种没有a的写法。还有一点容易被忽略范围条件左边的列能用索引范围条件右边的列会失效。where a? and b10 and c?c这个条件只能做过滤不能再用来继续缩小索引范围。理解这个规则之后你再看那些网上常见的“联合索引最左前缀”口诀会清楚很多——它不是让你背顺序而是逼你思考每个查询条件在索引树上的作用位置。2.2 三种常见的索引失效写法第一种隐式类型转换。比如phone字段是varchar你写where phone13800138000MySQL会把字段转成数字再做比较等于在字段上套了一个隐式函数索引失效。改成where phone13800138000就好。第二种在字段上套函数。where DATE(create_time)2025-01-01不走索引建议改成create_time 2025-01-01 00:00:00 and create_time 2025-01-02 00:00:00这样还能让范围查询命中索引。第三种前导通配符。like %abc%用不上索引非要模糊搜索要么改成like abc%要么考虑全文索引或单独建搜索服务。这三种情况有个共同点不是索引坏了而是你在索引列上做了“加工”让优化器没法按原样去B树里找。2.3 优化器不选索引时别硬刚加了索引不代表优化器一定用。最常见的原因是选择性太低。比如性别字段区分度不高优化器一算回表成本比全表扫还高就会放弃。另一个原因是统计信息过期可以执行ANALYZE TABLE刷新一下。还有一个很容易被忽略当表里同时有几个索引都能用优化器会根据rows估算选择它认为成本最小的那个而不是一定选你新加的那个。所以判断该不该给某个字段建索引可以先算区分度SELECT COUNT(DISTINCT col) / COUNT(*) FROM t;比例越小越不值钱低于0.1基本不要指望它扛起优化大旗。我在实际项目里见过有人给is_deleted这种只有0和1的字段建索引结果查询计划依然走全表扫纯属建了个寂寞。2.4 联合索引设计的实用顺序设计联合索引时我一般按这个顺序排等值条件列放前面需要排序的列放在中间范围查询列尽量放后面最后再看能不能利用覆盖索引。比如订单表经常按user_id查并按create_time排序那么idx_user_created(user_id, create_time)就比单独的idx_user(user_id)更好因为排序字段直接在索引里ORDER BY create_time就不用额外做filesort。如果查询还经常带status1的条件可以考虑(user_id, create_time, status)让Extra里直接出现Using index。当然索引会占空间、拖慢写入不要见条件就建每加一个索引都要清楚它是为哪几个高频查询服务的。索引不是勋章建得越多越光荣它是成本只有收益大于成本才值得。3. 慢SQL改写实战从EXPLAIN到案例落地3.1 拿到慢SQL先学会读EXPLAIN把慢SQL捞出来后我第一件事永远是执行EXPLAIN。只需要看四个关键字段type、key、rows、Extra。type是访问类型按效率从好到坏大致是system、const、eq_ref、ref、range、index、ALL看到index和ALL就要警惕这代表在遍历。key表示实际用到的索引NULL就是没走。rows是优化器估算的扫描行数这个数字越大越危险。Extra里出现Using filesort或Using temporary通常意味着排序和临时表成了瓶颈。先看懂这四个字段后面改SQL才有依据。很多同学拿到一条慢SQL不看执行计划就凭感觉加索引、改写法结果改了等于没改就是因为根本没确认瓶颈是扫描行数、排序还是临时表。同一个问题三种解法用错地方就是白干。3.2 从“回表”到“覆盖索引”省一次随机IO给个例子。订单表有id, user_id, status, amount, create_time常见查询是SELECT id, status FROM orders WHERE user_id 123;如果只有idx_user(user_id)这一条索引MySQL先通过索引找到user_id123的叶子节点拿到主键id再回表读整行取出status。回表是一次随机IO数据量大时很疼。优化方式是把查询需要的列都塞进索引ALTER TABLE orders ADD INDEX idx_user_status_id (user_id, status, id);这样查询需要的字段在idx_user_status_id的索引树上就能全部拿到Extra显示Using index不需要回表。这就是覆盖索引。别小看这个优化我见过同一套查询在千万级表上从800ms降到50ms出头。代价是索引变宽、写入变慢所以只对高频查询做覆盖别什么列都往里塞。3.3 深分页优化LIMIT 1000000, 20为什么会慢分页是慢SQL重灾区。LIMIT 1000000, 20慢不是慢在只取20条而是MySQL要把前1000020行都扫出来再丢掉。如果ORDER BY字段没有索引还要先做一次filesort代价翻倍。第一种优化是延迟关联先只查主键ID字段做分页再用主键回原表取完整行SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY create_time LIMIT 1000000, 20) tmp ON o.id tmp.id;第二种更推荐游标分页。前端记住上一页最后一条记录的create_time和id下一页查询变成SELECT * FROM orders WHERE create_time 2025-03-01 10:00:00 ORDER BY create_time DESC, id DESC LIMIT 20;注意ORDER BY必须带上主键或唯一列做辅助排序否则数据顺序不稳定翻页会出现重复或漏数据。这个方法对大数据量的深度翻页几乎是决定性优势无限滚动的列表基本都用这种思路。3.4 关联查询驱动表和JOIN条件下的细节关联查询优化里“小表驱动大表”是句老话但实际要看EXPLAIN第一行是谁也就是优化器选的驱动表。给被驱动表的关联列建索引是关联查询优化的关键。假设A表1000行、B表100万行JOIN条件是A.id B.a_id那么给B.a_id建索引后每扫A一行就能走索引去B里查扫描行数大幅下降。反过来如果B.a_id没索引每行驱动表都要全表扫B查询直接爆炸。还有子查询。MySQL对IN (SELECT ...)很多时候能优化成半连接但遇到复杂条件时可能性能不佳这时候拆成JOIN或临时表反而稳。我自己还有个原则不要在JOIN后的结果上做ORDER BY RAND()也别在关联字段上写函数否则索引用不上。关联查询的性能问题十有八九出在“小结果集驱动大结果集”这个顺序没被优化器善待或者关联列索引缺失。4. 配置参数里真正值得动的几个开关4.1 innodb_buffer_pool_size先看命中率再动手InnoDB的缓冲池是MySQL缓存数据和索引的主战场默认只有128M对稍微有点业务的服务器来说远远不够。但在调之前我建议先看命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;用Innodb_buffer_pool_read_requests除以Innodb_buffer_pool_reads可以算出读请求能从内存直接命中的比例。如果命中率已经很高调大buffer收益有限如果命中率低且大量读请求落在磁盘才值得加大。生产环境可以按物理内存的60%-75%来设8.0里可以动态调整SET GLOBAL innodb_buffer_pool_size 8 * 1024 * 1024 * 1024;动态调整之后观察效果确认有效再写进my.cnf持久化。千万别把内存吃光要给OS和连接线程留余地否则系统把内存页换出性能反而更难看。4.2 刷盘策略在性能和数据安全之间做选择这个参数是优化里典型的两难innodb_flush_log_at_trx_commit。设置为1每次事务提交都把redo log刷到磁盘最安全但最慢设置为0靠后台每秒刷一次性能最好但MySQL一旦崩溃可能丢最近1秒事务设置为2每次提交只写到操作系统缓存每秒再真正刷盘性能和安全各让一步。我的建议核心交易、财务类数据老老实实用1日志类、非核心业务能接受崩溃丢最后一秒数据才考虑20一般只在压测时看一眼生产不推荐。如果开了binlogsync_binlog和它要统一考虑不然宕机恢复时主从或事务一致性会出问题。很多人在“优化”旗号下把刷盘策略改成0表面上写入快了真遇到断电或崩溃你会发现丢数据的代价远大于那点性能提升。4.3 连接数、连接池和慢日志别让连接成为隐性瓶颈MySQL默认max_connections是151线上常遇到Too many connections。这个错误看着像连接数不够根源却常常是某几条慢SQL占着连接不放后面的请求全部排队。你把max_connections调成1000慢SQL一多照样被打穿还更容易把数据库内存耗尽。正确做法是先查SHOW STATUS LIKE Threads_connected;再看processlist里有没有大量query time很长的会话把慢SQL解决了连接数自然回落。慢查询日志的配置也有讲究slow_query_log 1 slow_query_log_file /var/lib/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1长期开着你会发现优化有方向。高并发业务可以把long_query_time调到0.5秒但注意日志量会涨磁盘空间要提前规划。我见过有人把log_queries_not_using_indexes打开后日志几分钟就写满几个G——这其实是在提醒你系统里没有索引的查询比你想象的多得多。4.4 版本选择和初始化配置优化从安装那刻就开始了参数优化绕不开版本。不少人还在用5.7甚至5.6新项目我建议直接上8.0因为8.0默认字符集是utf8mb4支持窗口函数、CTE优化器也改进了不少。网上搜mysql安装教程或mysql下载常常搜到一堆互相冲突的步骤我的经验是安装之后第一件事别急着导业务数据先确认character_set_server、default_storage_engine、max_connections、innodb_buffer_pool_size这几个基础项。再强调一次初始化时就把sql_mode确认好别等上线后因为SQL模式不一致出现诡异的报错。版本差异还会体现在排序和字符比较规则上8.0和5.7对utf8mb4的处理已经完全不同。不然以后你调参调得再好底层配置一开始就没立住后面早晚补课。5. 把优化前移到应用层连接池、缓存与分库分表边界5.1 连接池参数到底怎么设数据库扛不住很多时候是应用层连接池不会用。Java里常见的HikariCP我见过有人把maximumPoolSize直接设成500理由是并发高。结果呢数据库连接数被打满线程调度和上下文切换本身成了瓶颈。连接池不是越大越好建议先在低配置下压测再逐步调大观察。HikariCP可以从maximumPoolSize30、minimumIdle5起步根据CPU核数和业务平均响应时间再调。Druid的话重点看initialSize、maxActive、maxWaitmaxWait别设太小不然连接获取一抖动就开始报获取超时。核心思路是连接是稀有资源宁可让少量请求排队也别用海量连接把数据库压死。我调过最夸张的一个案例连接池从200降到40之后数据库CPU反而从90%降到30%原因就是大量连接都在空转等锁纯属内耗。5.2 缓存挡流量但拦不住击穿和穿透查询量大到一定程度Redis这类缓存是必备的但缓存不是优化MySQL的借口。用缓存最大的三个坑缓存雪崩、缓存击穿、缓存穿透。雪崩指大量key同时过期流量直接打到数据库破解办法是过期时间加随机值别整点集体失效。击穿指某个热点key失效的一瞬间大量请求同时去数据库重建缓存破解办法是互斥锁或逻辑过期让一个请求负责回源。穿透指查询一个根本不存在的key缓存和数据库都查不到于是每次查询都打库破解办法是空值缓存、布隆过滤器。这三个问题处理不好MySQL优化做得再好也会被流量冲垮。我在项目里吃过击穿的亏一个热门商品详情页的缓存刚好过期瞬间几千个请求同时去查库数据库连接直接被打满后面所有正常查询也一起遭殃。从那以后热点数据重建缓存必须加互斥锁这个习惯我一直保留到现在。5.3 读写分离和分库分表是优化还是重构要想清楚当单库读压力大、写量相对可控时读写分离是性价比很高的方案主库扛写入从库扛读。但要注意主从延迟刚写完立刻读可能读到旧数据业务上要做兜底比如强制走主库的读接口。读写分离解决了“读多写少”的问题但解决不了单表数据量过大的问题。至于分库分表我见过太多团队在数据量刚过千万时就开始拆结果分布式事务、跨节点排序、全局ID全来了。我的判断标准很简单单表数据量继续膨胀导致性能明显下降或者单个实例的CPU/IO长期打满其他手段都试过之后再考虑分库分表。这不是MySQL优化的常规动作更像一次架构重构应该单独立项做。真要走到这一步那是另一个系列的话题这篇先把思路立住就够了。最后分享一点我在实际项目里的体会。MySQL优化真正难的不是某一个知识点而是你愿不愿意先把“慢在哪”搞清楚。把慢查询日志开起来把索引清单整理出来把配置基线记录下来遇到问题按“先定位、再假设、后验证”的顺序走大部分性能问题都能在SQL和表设计这层解决掉。这篇是系列第一篇后面我会接着写EXPLAIN的完整解读、锁等待排查、主从与高可用这些方向。如果你在照着操作时发现某个参数调了没效果欢迎带着执行计划和表结构来讨论一起把坑填平。
