★ FEATURED ARTICLE 先说我上个月处理的一个事一张只有几万行的用户表跑了三年都好好的突然某天按手机号查询的接口稳定地慢到800毫秒以上。SQL本身挑不出毛病索引也建了可explain一眼看过去type是ALLrows拉满。绕了一圈才发现根因是查询条件传进来的是订单系统里的一个数字字面量而表里这个列是varcharMySQL在比较时把索引列做了隐式类型转换索引直接失效活活扫了整表。这件事之后我重新梳理了一遍自己脑子里的MySQL知识点发现真正决定线上查得快不快、稳不稳的往往不是那些花哨技巧而是几个底层机制能不能串起来。这篇我按自己实战里反复被验证的顺序来写先聊建表阶段最容易埋雷的字段类型和字符集再讲索引为什么快、快在哪里然后深入到事务与锁的协作方式接着是慢SQL排查的实际流程最后补上几个运维层面的参数权衡。适合刚接手MySQL维护的同学也适合写了好几年SQL但没系统捋过执行过程的人。1. 建表阶段就该确认的知识点字符集、字段类型与隐式转换的代价1.1 字符集与排序规则utf8mb4不是选秀而是底线字符集这个事看起来是初始化集群时配一次就完但影响的是后续每一次字符串比较和索引查找。MySQL 8.0已经把utf8mb4定为默认字符集可还有不少老库是从5.6时代传承下来的默认是utf8mb3也就是我们口语里说的utf8它最多只能存3字节的字符emoji和一些生僻字进去就是?或者直接报错。更隐蔽的是排序规则。utf8mb4下面常见的排序规则有general_ci和0900_ai_ci8.0的默认是0900_ai_ci它对Unicode的支持更完整按现代语言习惯排序。坑往往出在两张表join的时候如果两个表的这个字段一个用的utf8mb4_general_ci另一个用的utf8mb4_0900_ai_ciMySQL为了做比较可能无法直接用索引或者报collation冲突。我建议新建表统一用utf8mb4_0900_ai_ci老表在确认业务无感之后尽量迁移别让排序规则成为后面加索引时的隐形雷。另一个容易被忽略的是varchar长度和索引上限的关系。InnoDB在默认16KB页大小下单个索引键最大支持3072字节utf8mb4一个字符最多4字节所以理论上单列varchar超过768就会触碰这个上限实际上老版本是767字节的限制当年导致很多人建varchar(255)的utf8mb4前缀索引直接失败。现在的8.0虽然宽松了但联合索引几个大字段一叠照样可能超限。经验是像url、备注这类长字符串如果只是用来等值查询考虑前缀索引如果用来排序或覆盖索引就别指望太长的前缀数据模型上拆一张附属表更干净。还有一个小知识点是varchar(255)和varchar(256)的微妙差异。虽然对用户无感但在设计索引列时255和256在数据字典里的表示方式不同衍生出过生成列、临时表排序空间判断的问题。哪怕如今影响变小我依然习惯重要字段选长度时避开偶数边界少给自己找事。1.2 数值与时间字段别拿float存金额别拿int存手机号数值类型看着简单线上翻车案例一点都不少。最常见的三类问题第一金额用float或double。浮点数在计算机里是近似存储0.1加0.2能给你整出0.30000000000000004。算总价、算单价差异一旦积累对账就对不上这是财务口径绝对不能容忍的。金额字段一律DECIMAL比如DECIMAL(10,2)整数部分8位小数2位日常绝大多数业务都够用。如果还有更高精度需求DECIMAL最多能到65位总位数留足余量。第二手机号、身份证、卡号这类长得像数字的字段用int或bigint。int最大也就21亿多手机号11位直接爆掉就算用bigint存下了后续想做模糊查询、前缀匹配数字类型做不了LIKE只能先转字符串索引又废了。所以这些字段务必用varchar同时考虑加校验约束防止脏数据。第三时间字段的选择。timestamp和datetime都行但要知道差别timestamp是4字节范围到2038年而且它存的是UTC时间查询时会按会话时区转换同一个数在不同时区客户端看到的本地时间不同。datetime在5.6之后默认5字节范围大得多不涉及时区转换。如果系统有多地部署、有国际化诉求我宁可用datetime加明确时区处理省得被timestamp悄悄转换坑了。另外5.6.4之后datetime和timestamp都支持小数秒定时任务、日志流水这些对时间精度敏感的表别忘了在字段上加上精度定义。1.3 隐式类型转换一条看似正常的SQL为何全表扫描回到开头那个事故展开说其中的判断过程。表结构是phone varchar(20)查询条件是where phone 13800138000注意这个13800138000在SQL里是数字字面量。MySQL有一条规则比较操作里如果一个是字符串类型一个是数字类型会把字符串转成数字比较。那问题来了索引列phone是varchar它需要先被转成数字才能和右边的数字比于是索引树上没法直接用B树定位只能把整列取出来逐个转换再比对走全表扫描。反过来如果列是int查询条件是where id abcMySQL会把abc转成数字转出来是0此时索引列本身没被函数作用优化器依然能走索引只是语义已经是匹配id0容易查出意料之外的数据。所以原则是应用层传入的参数类型要和字段类型严格对齐避免依赖数据库的隐式转换。排查这类问题最快的方式就是explain看到typeALL同时对比一下where条件里字段的定义和传入值的类型。另一个同类坑是日期字段上套函数。where date(create_time) 2024-01-01这种写法同样是因为索引列被date()包裹优化器没办法走索引。正确做法是改成范围create_time 2024-01-01 00:00:00 and create_time 2024-01-02 00:00:00。效果一样但能命中索引大表上差异几百倍。2. 索引为什么快从B树物理结构看回表、最左前缀与索引下推2.1 聚簇索引与二级索引数据到底放在哪里我一直觉得索引优化如果只记规则不搞懂结构换个场景就懵了。InnoDB表的数据本身是按主键组织成一棵B树的这棵树叫聚簇索引叶子节点上挂着这一行的完整数据。二级索引也就是我们平时手动建的普通索引是另一棵B树叶子节点存的是主键值不是整行数据。所以用二级索引查询通常要分两步先在二级索引树上找到匹配的记录和对应的主键再拿着主键去聚簇索引树上查一次完整数据这个动作叫回表。回表不是免费午餐每回一次就是一次随机I/O。明白了这个结构很多结论就顺理成章为什么主键建议自增整数因为新插入的行主键有序直接追加到聚簇索引叶子节点末尾页分裂少如果主键是UUID这种杂乱字符串新行随机插入到索引树任意位置页分裂频繁写放大明显。为什么二级索引列不要选大字段因为二级索引叶子存的是主键值但非叶子节点会存索引列内容字段越大树高度越高I/O越多。还有个大方向的判断如果一张表的写入量巨大、查询面很窄可以认真评估是否要用二级索引覆盖完所有查询列尽量避免回表。但前提是确认索引树的维护成本能接受否则写性能会被拖垮。2.2 联合索引的最左前缀从排序角度理解而不是背口诀联合索引(a,b,c)本质是先按a排序a相同再按b排序b相同再按c排序。这是一个复合排序规则而不是三个独立索引的组合。背最左前缀的口诀容易碰到order by和group by场景就不知道怎么套了。举几个实际的例子。条件where a 1 and b 2能走部分索引在a定位之后b有序排列所以b的等值条件也能用来定位where b 2 and c 3这种由于没有a条件前缀联合索引的排序顺序帮不上忙只能扫全索引。可如果where b 2 and a 1呢优化器会做条件重排先提取a条件依然能用。这里有个细节条件里a写的顺序无关紧要但a必须作为等值条件出现。排序场景也一样。order by a, b可以走索引因为索引本来就是按a、b排好的。order by b, a则没法直接用这个索引因为复合排序前缀不匹配。但如果是where a 1 order by ba已经固定成等值后b在这个子树内是有序的所以也可以走索引避免filesort。MySQL 8.0还引入了跳跃扫描某些情况下where b ?可以跳过前缀a直接利用联合索引但依赖统计信息和成本估算别把它当常规武器。设计联合索引时经验法则是等值条件列放前面排序列放后面区分度高的不一定放最前。比如(a, b)联合索引如果a几乎是固定枚举值区分度很低而b区分度高单独给b建索引和联合索引的取舍就要看具体SQL形态。没有银弹最准的是拿到真实查询统计之后设计而不是拍脑袋。2.3 覆盖索引与索引下推让引擎少干活的两种机制这两个机制是MySQL为了减少扫描无谓数据做的优化发现它们可以从explain的Extra列看到。覆盖索引指的是查询需要的所有列都包含在某个二级索引里此时引擎直接从二级索引的叶子节点拿数据不再回表。比如表里有(name, age, address三列)建了一个(name, age)联合索引查询select name, age from user where name 张三这里name和age都在索引里引擎觉得没必要再回表取address列Extra会显示Using index。覆盖索引对高并发查询的提升非常明显相当于把随机I/O变成了顺序索引扫描。索引下推ICP是5.6引入的。没有ICP时二级索引根据索引条件定位到一批记录后会把整行主键回表取出来再到server层过滤其他条件。有了ICP引擎可以在索引遍历过程中直接过滤掉不符合条件的记录减少回表次数。最典型的是(name, age)联合索引查询where name like 张% and age 20name条件走索引age条件可以在索引扫描时直接判断不需要把满足姓氏的几十万行全部回表再筛年龄。注意Extra会显示Using index condition这不是慢的标志反而是好事。在实践里我经常干的优化手段是延迟关联先把覆盖索引用起来只取主键再join回主表取其他字段。深分页场景尤其好用后面慢SQL部分会专门讲。2.4 索引也不是越多越好冗余与维护成本二级索引不是免费的。每一棵二级索引B树都需要叶子节点存主键值和非叶子节点存索引列内容插入、删除、更新主键时所有索引都要同步维护。一个表挂了七八个索引写入快了但每次写入的IO放大也感人缓冲区里还可能因为各个索引页争抢而互相驱逐。常见的冗余是又建了a的独立索引又建了(a, b)联合索引。其实(a, b)联合索引已经覆盖了单独的a索引单独那个就是冗余白白增加维护成本。我在评审表结构时都会把所有索引列出来看前缀关系删掉那些能被联合索引前缀覆盖的重复索引。还有前缀索引问题。varchar(500)的URL字段直接建整列索引会很大可以只索引前N个字符比如index(url(64))。但代价是索引只对LIKE 前缀%这类查询有效无法用于排序也无法实现覆盖索引。取舍是一般如果只是等值查询业务上确定不会用URL排序前缀索引能大幅节省空间和写入成本。注意计算区分度时不能只看前N位唯一最好拿真实数据跑一下通常选择能让区分度接近整列的80%-90%的N值。3. 事务与锁的底层机制redo/undo、MVCC与next-key lock的协作方式3.1 WAL与redo log在掉电瞬间数据库如何自我修复事务的ACID里A原子性、I隔离性、D持久性不是凭空来的靠的就是redo log和undo log这两个日志体系。MySQL采取的是WALWrite-Ahead Logging写数据前先写日志。事务提交时最关键的一步是把redo log刷到磁盘而不是立刻把数据页刷到磁盘。这样即使系统在数据页刷盘前宕机重启后也能从redo log里把已提交但没落盘的数据重放出来保证持久性。理解这个机制就能看懂为什么innodb_flush_log_at_trx_commit这个参数值会直接影响数据安全与性能。设置为1每次事务提交都要把redo log刷到磁盘最安全但fsync次数多设置为0或2提交时先把日志写到操作系统缓存或只写内存性能高但断电可能丢最近一小段已提交事务。绝大多数线上系统我会坚持双1也就是innodb_flush_log_at_trx_commit1和sync_binlog1同时开启把崩溃恢复能力和主从一致性放在性能前面。有意思的是redo log是物理日志记录的是哪个页哪个偏移量改了成什么而binlog是逻辑日志记录的是这条SQL/这个行变更是什么。两者配合还有一个重要机制提交时通过内部XA的两阶段提交保证redo log和binlog一致。这也是为什么有些事故会出现主从数据不一致往往是因为两边刷盘策略不一致导致崩溃恢复后binlog缺了一段。3.2 undo log与MVCC读已提交和可重复读的真实差异undo log和redo log相反它记录的是改之前是什么样用来在事务回滚时还原数据同时也是MVCC多版本控制的核心。每个数据行还有隐藏的DB_TRX_ID和DB_ROLL_PTR前者是最后一次修改它的事务ID后者指向undo log里的上一个版本。MVCC的思路是读操作不阻塞写写操作不阻塞读。普通SELECT快照读不需要加锁它根据当前事务生成的ReadView去版本链里找一条对当前事务可见的版本。ReadView记录了生成时刻活跃事务列表判断一条记录的DB_TRX_ID是否小于可见范围。读已提交和可重复读的核心差别就在于ReadView生成时机RC每次SELECT都生成一个新的ReadView所以同一事务里两次SELECT可能看到别的已提交事务的后来版本RR是事务第一次执行SELECT时生成ReadView之后整个事务复用同一个所以不受其他事务后续提交影响。这也是为什么默认级别是RR而不是RC很多业务在RR下天然获得了事务内看到的数据前后一致的体验。但如果追求更高的并发写入吞吐和更少的间隙锁冲突很多团队会把隔离级别调成RC。要注意的是调整之后应用代码是否依赖RR的语义我见过有人把隔离级别改成RC之后同一事务里先查后更新的逻辑读出来的数据有了变化导致重复提交。3.3 当前读、间隙锁与幻读可重复读究竟锁住了什么MVCC解决了普通SELECT的隔离问题但UPDATE、DELETE以及SELECT ... FOR UPDATE是当前读必须读到最新已提交版本并加锁。在RR隔离级别下InnoDB默认使用next-key lock也就是记录锁加间隙锁的组合。间隙锁锁住的是索引记录之间的区间目的是防止别的事务在这个区间插入新记录从而避免幻读。举个例子。表里有主键id1、5、10三条记录事务A执行SELECT * FROM table WHERE id 5 FOR UPDATE如果这个查询是走了唯一索引精确匹配且记录存在InnoDB会退化成只锁id5这一条记录不加间隙。但如果是范围查询WHERE id BETWEEN 3 AND 7那会锁住(1,5)这个区间的插入、id5本身以及(5,10)这个区间也就是别的事务想插入id4或id6都会被阻塞即使这些记录当前不存在。理解了间隙锁再回头看为什么很多死锁发生在insert场景两个事务都先对某个不存在记录做了范围当前读持有间隙锁随后都想插入同一主键就会互相等待。所以在高并发写入场景RR的间隙锁有时比数据竞争本身更伤性能。这也是我建议业务对写冲突较敏感的表慎重评估是否降到RC的原因。3.4 死锁的排查与规避show engine innodb status该怎么看死锁出现时InnoDB会自动检测并回滚一个代价较小的事务。如果应用层没用死锁重试机制接口就会偶发报错。排查第一现场的手段是执行SHOW ENGINE INNODB STATUS重点看LATEST DETECTED DEADLOCK这一段里面会列出两个事务各自持有的锁和等待的锁以及对应的SQL。我们团队处理过最典型的一个死锁是两个事务都先UPDATE同一张主表的同一行再分别去UPDATE另外一张子表的不同行顺序相反。事务A锁了主表行X然后等子表行Y事务B锁了子表行Y然后等主表行X正好互相卡住。规避方法很简单所有涉及多行的写操作在应用层约定同一个访问顺序比如都按主表id从小到大更新就能消除循环等待。还有一个容易被忽略的点是innodb_deadlock_detect参数。高并发下死锁检测本身要遍历等待图极端情况会消耗不少CPU。如果业务能接受出现死锁时直接报错并由应用重试可以把死锁检测关闭让InnoDB在锁等待超时后回滚。我一般不轻易关因为这意味着死锁要靠lock_wait_timeout兜底默认50秒太久体验会很差。折中做法是保留检测但尽量从SQL层面减少锁冲突。4. 慢SQL排查方法论慢日志、执行计划解读与三条经典改写思路4.1 慢查询日志与工具分析先确认问题是否在SQL排查慢接口的第一步不是看执行计划而是先确认这一步慢到底是不是数据库慢。我养成习惯是看完整链路接口耗时里网络传输多少、应用处理多少、数据库执行多少。可以借助链路追踪里的span耗时没有的话就在数据库端打开慢查询日志。把long_query_time设置成1秒或更小比如0.5秒同时打开log_queries_not_using_indexes记录那些没走索引的查询。别小看这个参数很多上线前的性能隐患就是靠它暴露的。慢查询日志落在文件里之后直接用pt-query-digest这类工具聚合分析它能按指纹把SQL模板归类统计出平均耗时、最大耗时、总耗时占比。看聚合结果优先处理Total Time占比最高的那几个模板而不是纠结个别异常慢的孤立查询这是典型的抓大放小。要注意的是一个查询出现在慢日志里不一定就是SQL本身烂也可能是行锁等待、metadata lock、或者buffer pool抖动。我把explain结果拿到手之前都默认先看是否是锁等待用performance_schema或者information_schema里的innodb_trx表辅助判断别上来就改SQL。4.2 explain关键列逐项解读type、rows、Extra不是摆设explain是MySQL给出的执行计划但对每一列要会读不然容易误判。最常见的误判是看到type列出现index就以为走了索引很开心其实index代表全索引扫描相当于把整个二级索引树从叶子头扫到尾比ALL全表扫描快不了多少。真正合格的走索引是range、ref、eq_ref、const这一档。type列从好到差大致是system、const、eq_ref、ref、range、index、ALL。const表示主键或唯一索引等值命中最多一行eq_ref出现在join时被驱动表通过唯一索引等值匹配每行匹配一行ref表示普通索引等值命中range表示索引范围扫描一旦看到index或ALL就要警惕了。rows是优化器估算的扫描行数虽然不一定准但量级能说明很大问题。比如一个查询估算rows是50万即使type是ref也要反思是不是用了区分度很低的索引列。我经常对比实际返回行数和rows估算值差距特别大时多半是统计信息过期需要ANALYZE TABLE刷新。Extra列里几个标志要认识Using filesort不是真的在磁盘排序但说明排序没走索引需要额外排序行数大时是性能瓶颈Using temporary说明查询用了临时表常见于GROUP BY、DISTINCT、UNION操作Using index是好事覆盖索引Using index condition是ICP下推Using where说明在存储引擎层取到行后还在server层做了过滤。看到Using filesort和Using temporary同时出现基本可以断定这条SQL有改写空间。4.3 三个常见慢SQL改写案例从思路到验证第一个是深分页问题。SELECT * FROM orders WHERE user_id 123 ORDER BY id DESC LIMIT 100000, 20MySQL要先扫描前100020条再把前面100000条丢弃扫描量巨大。改写思路是延迟关联先通过覆盖索引找到id列表再回到主表取完整行。比如SELECT * FROM orders JOIN (SELECT id FROM orders WHERE user_id 123 ORDER BY id DESC LIMIT 100000, 20) AS tmp ON orders.id tmp.id ORDER BY orders.id DESC。子查询只扫二级索引不碰大字段通常速度提升一个量级。更好的方案是书签分页用上一页最后一条id作为where条件这需要前端配合改动但效果更彻底。第二个是函数包裹列。where date(create_time) 2024-06-01这种写法不仅索引失效而且写法不可控。改成create_time 2024-06-01 and create_time 2024-06-02优化器可以走range扫描。如果业务就是按天查可以考虑在表里冗余一个date字段并建立生成列索引让查询条件直接落在普通等值上。第三个是OR条件下优化器无法有效利用索引。where a 1 or b 2如果a和b各有索引MySQL可能需要分别扫描再合并用上索引合并机制但如果只有a索引OR条件只能全表扫。常见改法是拆成两条select用UNION ALL合并或者改写成两个条件独立分区查询。不过8.0对OR的优化已经很不错了不改之前先explain确认一下type和rows别为了改而改。所有改完的SQL都记得对比前后explain和真实耗时用数据说话。5. 容易被运维细节卡住的点连接数、缓冲池与主从延迟的权衡5.1 max_connections与连接池连接不是越多越好很多同学看到报错Too many connections第一时间就把max_connections从200调到500、1000。实际上这个参数不是越大越好每一条连接在MySQL侧都要分配线程栈、sort_buffer、join_buffer等资源连接数翻倍往往意味着内存翻倍。真正该查的是连接数为什么被打满。常见原因是应用连接池配置过大、慢查询拖长了单条连接占用时间、或者有连接泄漏没有归还。连接池大小的经验值不是网上传的越大并发越高在小并发场景连接池过大会导致大量连接空转。基准建议是CPU核心数乘2加磁盘数比如8核机器给16到20个连接就够当然具体要压测验证。判断连接池是否缺连接可以看应用侧连接池的active和idle曲线而不是看MySQL侧峰值。还有超时参数值得统一梳理。wait_timeout和interactive_timeout控制空闲连接关闭时间默认8小时偏长应用侧如果没做连接有效性检查会用上已经失效的连接出现Communications link failure这类报错。反过来超时设太短高并发下频繁重建连接又增加握手开销。我通常把这两项设成30到60分钟并确保应用连接池配置了test-on-borrow或定期心跳。5.2 Buffer Pool与写入刷盘内存决定上限配置决定下限InnoDB的数据页、索引页、undo页都会缓存在Buffer Pool里命中率直接决定读性能。innodb_buffer_pool_size该给多大传统建议是物理内存的70%但还要留出操作系统、连接线程、排序缓冲等余量。比如64G内存的机器给45G左右是比较稳的起步值后续看命中率微调。从SHOW STATUS LIKE Innodb_buffer_pool_read%能看到两个关键计数Innodb_buffer_pool_read_requests是逻辑读总次数Innodb_buffer_pool_reads是从磁盘物理读的次数命中率用(requests - reads) / requests计算。长期低于99%就要考虑调大缓冲池或者优化查询。顺便说一句MySQL 5.7之后的Buffer Pool是分实例的大缓冲池建议设置innodb_buffer_pool_instances大于1能减少并发访问时的内部锁竞争。写入刷盘也是容易被忽略的瓶颈。innodb_io_capacity控制后台刷脏页的IO上限如果配得太低脏页堆积会导致用户查询时触发同步刷盘表现为响应时间周期性打点。机械盘给200到400SSD给2000到8000。调整这类参数时不能只盯着某一个指标要同时观察每秒写入量、脏页比例和你应用的实际延迟曲线。5.3 主从延迟与读写分离何时该上何时别硬上读写分离能缓解主库读压力但它的假设是业务能接受主从延迟。一主一从和级联复制下延迟的常见来源是大事务、大DDL、缺少主键的更新语句、以及从库单线程复制时代遗留的瓶颈。MySQL 8.0的MTS并行复制已经能改善很多但大事务依然是延迟元凶因为它的SQL在从库上要串行执行。如果业务确实需要读写分离我建议做两个配套动作一是启用半同步复制主库提交时等待至少一个从库确认收到binlog后再返回客户端可以把延迟暴露为提交耗时而不是数据不一致二是在应用层对时效敏感的关键写操作做强制读主库比如刚下单的用户立即查询自己的订单列表可以按userId哈希路由到主库或者给一个短时间的读主白名单。分库分表同理不是性能问题的默认解。很多团队表里几百万行就吵着要分库分表其实加了正确的索引、消除了慢查询后单库撑住千万行根本不是问题。分库分表引入的分布式事务、跨库join、全局主键、数据迁移复杂度每一项都能吃掉一个团队好几周的精力。我的判断顺序是先查慢SQL和索引再考虑缓存然后是读写分离最后才上分库分表。每一步都验证收益别一上来就放大招。最后再分享一个我自己的习惯RDS控制台上的监控图只能看到平均趋势真正想定位问题还是要靠慢日志和explain。数据库的很多知识点恰恰是在处理一个个查不出来哪里慢的问题时才被真正记住的。遇到看似玄学的性能问题先回到字段类型、索引结构、事务隔离这几个基础维度往往答案就在那里。
阅读完成 · 觉得有帮助？
