如果你接手过一套正在线上跑的MySQL架构或者正在准备MySQL方向的面试那存储引擎、主从复制、分库分表这三块内容迟早要碰到。我自己就是被真实故障教育过的人第一次是MyISAM的表锁导致全站请求排队第二次是主从延迟让报表数据跑偏了好几天第三次是单表数据量破亿之后一条简单的COUNT查询直接慢到几十秒。这篇文章把这三块内容串起来讲清楚不会堆概念而是从选型原理、配置步骤、拆分策略到实例排错把我验证过的经验和踩过的坑一次说透。这同时也算是我MySQL系列的开篇后面会继续深入索引、事务、性能调优等方向。这篇先解决最基础也最关键的架构性问题。1. 存储引擎选型InnoDB和MyISAM的那点差异决定了系统的生死1.1 为什么说选错引擎比SQL写错更致命很多人刚接触MySQL的时候只知道建表时有个ENGINE参数默认是InnoDB但这背后意味着什么很多人并没有真正理解。等到线上出问题才明白存储引擎直接决定了并发模型、数据安全、性能上限。MyISAM最核心的特征是表级锁。什么意思任何一条写操作比如UPDATE某一行都会把整张表锁住期间的读操作全部阻塞。从业务视角看就是高并发场景下请求莫名其妙堆积CPU占用看起来不高但响应时间飙升。我记得比较清楚的一次故障线上某个活动表用的MyISAM活动开始的瞬间大量用户同时抢实际不过是几百个并发写直接把表锁住了整个服务接近不可用。InnoDB用的是行级锁。同样是UPDATE一行InnoDB只会锁住涉及的行其他行的读写不受影响。这个差异在低并发场景下体现不明显一旦并发上来就是能不能继续跑的区别。再一个关键差异是事务。InnoDB支持完整的事务特性原子性、一致性、隔离性、持久性。转账、扣库存、订单状态变更这些业务逻辑必须依赖事务否则中间一步失败就会出现严重的账务不一致。MyISAM不支持事务写完一半挂了数据就停留在中间状态根本没有回滚能力。1.2 崩溃恢复能力的差距生产环境中最容易被忽略的是存储引擎的崩溃恢复能力。MyISAM写入数据时只是简单地把数据写到文件里没有预写日志。如果数据库进程突然被杀掉、或者主机断电数据文件有可能处于不一致状态。轻则需要REPAIR TABLE重则数据直接损坏丢失。InnoDB的做法是写数据之前先写redo log把每一个操作记录下来。即使数据库崩了重启时也能通过redo log把数据恢复到崩溃前的状态保证提交过的事务不丢失。这也是为什么绝大多数生产环境必须用InnoDB。我把两者在关键维度的差异整理成了一个表方便对照能力维度InnoDBMyISAM事务支持支持ACID不支持锁粒度行级锁表级锁MVCC并发控制支持不支持崩溃恢复redo log恢复无预写日志外键约束支持不支持全文索引8.0内置支持原生支持聚簇索引是数据按主键组织否数据和索引分离适用场景OLTP在线交易只读分析、临时表1.3 查看和修改引擎的实操实际运维中最常用的几条命令得记牢。查看当前表用的什么引擎SHOW TABLE STATUS WHERE Name orders\G;也可以在information_schema里查整个库的情况SELECT table_name, engine FROM information_schema.TABLES WHERE table_schema 你的库名;在MySQL里查看当前默认存储引擎SHOW VARIABLES LIKE default_storage_engine;如果发现某个核心业务表还在用MyISAM要改回InnoDB大表不要直接ALTER后面第6章会专门说这个问题。修改语句本身很简单ALTER TABLE orders ENGINE InnoDB;但这句话对千万级以上的表会锁表很久生产环境必须用在线DDL工具这个细节很关键后面细讲。MyISAM也不是一无是处。如果某个场景是纯读、无并发写、数据量可控比如内部的报表库、数据仓库的明细快照表MyISAM的压缩表特性反而有优势磁盘占用小扫描性能也够。但绝大多数互联网在线业务老老实实InnoDB就行。2. 主从复制链路拆解binlog到relay log背后发生了什么2.1 三个线程的协作模型理解了存储引擎之后再看主从复制就顺理成章了。主从复制的底层逻辑其实很简单主库把所有变更写进binlog从库去拉取这些日志再在本地重放一遍。整个过程由三个线程协作完成。主库端有一个Binlog Dump线程从库端有两个线程I/O线程和SQL线程。I/O线程做的事情是连接到主库请求binlog内容主库的Binlog Dump线程负责把日志推送给从库的I/O线程。I/O线程拿到日志后不是直接执行而是先写入从本地的中继日志文件relay log。然后SQL线程读取relay log把里面的操作在从库上重放变成数据变更。这里有个容易误解的点很多人以为是主库主动推送binlog到从库。实际上是从库主动发起连接请求主库只是被动响应。理解这一点对排查问题很有帮助比如从库连不上主库时报的往往是I/O线程的状态异常。2.2 binlog_format参数怎么选binlog有三种格式STATEMENT、ROW、MIXED。这个参数直接决定主从复制的一致性和性能。STATEMENT格式记录的是SQL语句本身。比如在主库执行UPDATE user SET age 18binlog里存的就是这条SQL从库拿到后原样执行。优点是日志量小但缺点很致命如果SQL里有NOW()、RAND()这类非确定性函数主库执行结果和从库执行结果可能不一样。还有带LIMIT的UPDATE或DELETE如果数据排列顺序不同主从执行的影响行数也不同最终数据就会不一致。ROW格式记录的是每一行数据的变更前后状态不关心SQL本身长什么样。优点是绝对可靠任何语句在主从执行结果一定一致。缺点是日志量成倍增加尤其是大批量UPDATE或DELETE的时候binlog文件会膨胀得很快。MIXED是MySQL默认的自动判断模式。MySQL会根据SQL语句判断是否有不确定性如果存在就切换成ROW记录否则用STATEMENT。我的建议是生产环境直接设置binlog_format ROW。日志量大的问题可以通过调整binlog过期时间、定期清理来缓解。另外一个实际原因如果后面想接Canal这类中间件做数据同步、异构数据处理只有ROW格式才能解析出完整的数据变更事件。2.3 主从延迟是怎么产生的主从延迟是最常见的问题表象是主库写入之后从库查询数据要等一会儿才能读到。延迟的核心原因可以归类成几个从库SQL线程单线程重放是延迟的头号杀手。主库可以十几个线程并发写从库在早期版本只能串行执行relay log写入能力天然不足。MySQL 5.7开始支持并行复制8.0进一步优化配置了slave_parallel_workers之后能显著降低延迟。配置从库并行复制的参数需要在my.cnf里设置slave_parallel_workers 8 slave_parallel_type LOGICAL_CLOCK如果从库上还跑着分析类的重查询或者备份任务磁盘IO和CPU被占用同样会拖慢同步速度。建议备份优先在专门的备份实例上做不要和核心从库抢资源。大事务是最容易被忽略的延迟源。比如一次批量活动更新几十万行binlog可能几百MB从库SQL线程要连续重放很久期间所有其他变更都卡住了。规避方式是把大事务拆成小批次提交比如每1万行提交一次。监控延迟比较简单执行SHOW SLAVE STATUS\G; -- 关注 Seconds_Behind_Master 字段这个字段表示从库落后主库的秒数等于0时说明已经追上。如果长期不为0先查是否在执行大事务再看并行复制参数是否生效。3. 从零配置一主一从含远程库单表同步的两种解法3.1 主库侧的配置与账号主从复制的配置过程不复杂但每一步都要仔细。先处理主库。修改主库的my.cnf开启binlog并设置唯一的server-id[mysqld] server-id 1 log-bin mysql-bin binlog_format ROW sync_binlog 1server-id在整个复制拓扑里必须是唯一的这是MySQL用来区分不同实例的标识。sync_binlog1表示每次事务提交后立即刷盘保证主库崩溃时binlog不丢但对磁盘写入性能有一定影响SSD场景下可以接受。修改配置后重启MySQL用下面的命令确认binlog已经开启SHOW VARIABLES LIKE log_bin; SHOW MASTER STATUS;然后创建复制专用账号授予复制权限即可不需要给超级权限CREATE USER repl% IDENTIFIED BY 你设置的密码; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES;这里注意如果主库已经存在业务数据从库要先把这些数据同步过去才能开始复制。数据导出的推荐做法是用mysqldump并且加上--master-data2参数mysqldump --single-transaction --master-data2 -u root -p --all-databases backup.sql--single-transaction保证导出期间不会锁表--master-data2会在备份文件头部用注释记录当时主库的binlog文件名和位置后面配置从库时需要用到。3.2 从库侧的配置与启动复制从库的my.cnf单独配置[mysqld] server-id 2 read_only ONread_onlyON很关键它让从库拒绝非超级权限账号的写操作防止有人误操作导致主从数据不一致。但注意read_only不会阻止复制线程写入也不影响SHOW MASTER STATUS的查看。从库先导入主库备份mysql -u root -p backup.sql然后查看备份文件里记录的binlog位置。在backup.sql里搜索CHANGE MASTER TO会看到类似这样的一行注释-- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS82345;有了这个信息后在从库执行CHANGE MASTER TO MASTER_HOST主库IP, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORD你设置的密码, MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS82345;启动复制START SLAVE;在MySQL 8.0里命令是START REPLICA不过START SLAVE依然兼容。确认状态SHOW SLAVE STATUS\G;重点关注两个字段都必须是YesSlave_IO_Running: YesSlave_SQL_Running: Yes另外再看Seconds_Behind_Master是否为0。如果I/O线程是No通常是网络不通、账号密码错误、或者binlog位置填错。SQL线程是No说明relay log重放中遇到了错误比如主库执行过从库没权限执行的DDL或者数据冲突。3.3 把远程库某张表同步到本地的两种可行方案热搜里有个很具体的需求把远程库的这张表同步到本地。这个需求看起来简单但很容易走弯路。需要先说清楚MySQL主从复制本质上是整个实例级别的同步不是为单张表设计的。不过在特定场景下可以用两种方案实现。方案一定时导出导入适合数据量可控、延迟容忍度高的场景。比如每天凌晨或者每小时同步一次直接跳过复制链路# 在本地机器执行 mysqldump -h 远程库IP -u username -p database_name table_name table_data.sql mysql -h 127.0.0.1 -u root -p local_database table_data.sql这种方式实现成本最低但数据不是实时的并且每次全量导出导入对源库有一定IO压力。数据量大到几个GB就要考虑用更高效的工具比如DataX支持增量同步但部署复杂一些。方案二在主从复制链路上加过滤规则适合希望接近实时同步单张表的场景。在从库my.cnf里配置[mysqld] replicate-wild-do-table testdb.target_table然后重启从库。这样从库只会执行目标表的复制事件其他表的变更会被忽略。这里有个很实际的坑如果目标表所在的库还有其他表也要同步用replicate-wild-do-table会误伤所以规划时要考虑清楚过滤范围。还有一个问题是DDL语句在某些版本里不会受到表级过滤规则约束可能把不该执行的库级DDL也应用到从库需要谨慎操作。综合来看如果只是企业内部的远程数据汇总方案一足够如果对实时性要求高并且已经规划了主从架构方案二更合适。真正的大规模异构同步后续可以考虑引入Canal把binlog解析后投递到目标端那又是另一个话题了。4. 分库分表的临界点与拆分策略别等慢查询打爆了才后悔4.1 到底数据量多大才需要分库分表这是被问得最多的问题网上流传的说法是单表超过2000万就要分表其实这个数字没有普适性。更合理的判断标准是当前单表是否已经出现了性能瓶颈且优化已经没法解决。判断依据大概有这几个索引命中率显著下降。MySQL的InnoDB索引是B树结构数据量增大到一定级别后索引层数会增加每次查询的IO次数上升。通常千万级以内三层索引树已经能覆盖大部分场景到了上亿级别索引树可能变深或者缓存命中率下降查询开始变慢。写入QPS打满单库上限。即便全部走索引单库的写入TPS也有限制通常在每秒几千到几万取决于硬件和磁盘。如果写入已经持续打满SQL再怎么优化也上不去了。磁盘持续高水位。单库的binlog、数据文件、索引文件都在增长磁盘扩容频繁备份耗时越来越长。慢查询开始累积而且集中在同一张表上。我自己的经验是优先做优化而不是急于分库分表。先看索引是否合理冗余字段是否处理了冷热数据能否归档读多写少的场景是否可以考虑增加从库分担读压力。这些手段都试过了瓶颈还在再考虑分库分表。4.2 垂直拆分和水平拆分先拆哪个分库分表不等于一上来就按订单ID取模分散数据它包含两种不同的拆分方式。垂直拆分是把一张大表的字段拆到多张表或者多个库里。比如把用户表拆成用户基础表、用户扩展信息表、用户认证表。好处是实现简单对业务改动相对集中。缺点是如果拆得太细原本一条SQL能拿到的数据要多次查询才能拼装跨库跨表的join会越来越多。水平拆分是把同一张表的数据行按规则分到多个表或多个库中比如订单表按订单ID取模分成orders_0、orders_1、orders_2等多张物理表每张表的结构完全一样只是数据范围不同。水平拆分能真正突破单表的数据量和写入瓶颈但带来的问题也最多比如跨分片查询、聚合统计、分布式事务都需要改造。实际业务里通常先做垂直拆分把不必要的字段挪走把热点数据控制在一个相对小的范围内然后再做水平拆分。垂直拆分是第一步水平拆分才是真正的大手术。4.3 分片键选择决定拆分成败水平拆分中最重要的决策是选择分片键。分片键选错了后面所有查询都会变得很难受。最理想的分片键是业务里最核心的访问维度。比如电商订单场景用户查询自己订单的频率远高于运营后台查询那么就应该按user_id分片。这样查询某个用户的所有订单可以精确路由到固定的分片一条SQL直接查出来。按user_id分片可以用取模和范围两种方式。取模的方式是user_id % 64得到分片号数据分布比较均匀但扩容时所有数据都要重新分布这是最麻烦的问题。范围方式是根据user_id的区间分片比如1到1000万存分片01000万到2000万存分片1扩容时只需要新增分片迁移量小但可能存在数据倾斜比如大客户聚集在某些区间。分片键和业务查询不匹配时跨分片查询几乎是绕不过去的。比如电商系统按user_id分片运营后台要查某个商品最近7天在不同地区的销量就得遍历所有分片再汇总性能很差。解决思路是设置一个全局表或者索引表记录商品和分片的映射关系先查映射表定位到分片再去目标分片执行查询。另一个常见误区是所有字典数据、配置数据都跟着主表一起分片。实际上这些数据量很小、修改不频繁应该做成全库复制也就是每个分片都存一份完整的副本查询时直接本地取不需要跨分片访问。中间件方面目前社区里比较主流的方案是Apache ShardingSphere另外还有一些公司在用MyCat。ShardingSphere支持JDBC和Proxy两种形态JDBC形态嵌入应用性能较好但每个应用都要集成Proxy形态相当于独立的数据库代理层应用无侵入但多了一层网络转发。选择哪种要根据团队的运维能力和架构现状来定没有标准答案。5. 拆分后的硬骨头分布式ID、跨库查询与数据搬迁5.1 自增主键失效后分布式ID怎么生成分库分表以后原先的数据库自增主键立刻失效。原因很简单每个分片的自增序列独立若干分片各自生成的主键会重复。这时候需要引入全局唯一ID生成方案。最差的方案是用UUID直接做主键。字符串长度长而且无序性导致索引的随机IO非常严重B树的页面会频繁进行分裂和页分裂写入性能大幅下降。业界比较成熟的方案是雪花算法Snowflake。它的核心构成是1位符号位 41位毫秒时间戳 10位机器ID 12位序列号。在同一毫秒内最多可以生成4096个不同ID并且整体趋势递增对索引友好。现在很多语言的第三方库都实现了雪花算法比如常见的snowflake库或者美团开源的Leaf。还有一种是利用Redis的INCR命令生成ID简单可靠但依赖Redis高可用如果Redis挂了ID服务也跟着不可用。数据库发号表的方式则是单独维护一张表记录当前ID水印每次取一个批次发号适合对ID生成频率不高的场景。5.2 跨分片的查询、排序和事务问题分库分表之后原来一个SQL能搞定的排序、分页、聚合变成了跨分片问题。比如要按时间倒序分页查看订单列表所有分片都得先把各自的数据排好序然后在应用层合并归并。数据量小的时候在内存里归并排序还能接受。数据量大或者分片特别多就会很吃力。务实的做法是事先避开这种场景在订单表里冗余一个全局唯一订单号字段并保证它本身携带时间特征这样在应用层合并时的排序依据只会落在少数几列上。跨分片join也尽量别做。拆分之后不同表可能在不同的库甚至不同的实例上数据库层面根本没法做好join。替代办法有几个字段冗余比如订单表直接冗余用户名省掉下单时调用户表的查询多次单表查询后在应用层组装或者把数据同步到Elasticsearch做检索聚合再回表取详情。分布式事务是更麻烦的事。分布式数据库中间件通常提供XA分布式事务但性能开销很大。业务上更常见的做法是放弃强一致采用最终一致性方案比如本地消息表、RocketMQ事务消息、TCC模式等。这些方案各有适用场景但对业务代码的改动都不小所以很多团队在分库分表时会尽量把需要强一致的数据放在同一个分片内从源头规避分布式事务。拿订单系统举例下单这个动作涉及订单主表、订单明细表如果这两张表在同一个分片内事务仍然支持如果订单表和用户表不在同一分片就只能用最终一致性方案。这也是为什么分片键和分片粒度一定要提前规划而不是上线后再说。5.3 存量数据怎么平滑搬迁移最粗暴的方式是停机迁移。选定一个业务低峰期比如凌晨2点到4点把应用停掉导出所有旧数据做清洗和分片导入新库再启动应用切换流量。这个方案的优点是可以预见的坑少、实现简单缺点是需要停服业务规模到了一定程度产品经理和老板未必接受。更平滑的是双写方案。过程大概是先在应用层把读写同时写到旧库和新库新库从旧库拉取全量数据作为基础双写一段时间后用对账任务检查两边数据是否一致不一致的部分回溯旧库的binlog重新回放等数据一致且稳定后把应用读流量逐步切到新库最后停掉旧库的写。这个方案对应用层改造要求高但可以在不中断业务的情况下完成迁移。数据迁移工具方面离线全量同步可以用DataX增量同步可以考虑采用Canal订阅binlog后转发到目标端。这里的关键点是增量同步和双写可能会有重复写的问题必须设计好幂等策略否则数据被写两次或状态被覆盖对账会很难看。坦白说分库分表的迁移是最考验耐心和细致程度的环节。别指望一次切换成功每一步都要验证、备份、回退预案准备好。6. 主从复制与分库分表最容易踩的坑6.1 切换存储引擎的隐性问题如果把一张千万级的大表从MyISAM改成InnoDB直接执行ALTER TABLE ENGINE InnoDB在MySQL 8.0之前会全程锁表业务上的写入会被阻塞非常久。正确的做法是用在线DDL工具比如pt-online-schema-change或者gh-ost实现无锁切换。它们是创建一个新表结构然后通过触发器或者解析binlog的方式把旧数据逐步同步到新表最后在原表上做一次原子rename。还有一个容易忽略的细节修改某个表的引擎之前先查一下它有没有和其他表存在外键关系。如果外键字段的类型、索引不满足InnoDB的要求外键约束可能创建失败或者改成InnoDB之后原有的外键行为发生变化。6.2 从库数据不一致的排查与修复主从数据不一致最常见的诱因是有人在从库手动写了数据。设置了read_only之后只靠普通账号写不进去但如果有账号有SUPER权限依然能绕过read_only。这就需要在运维规范上控制好账号权限不能给应用账号超级权限。另一个诱因是主库执行了大事务从库重放期间一条报错中断了SQL线程。比如主库创建了一张表从库上因为某种原因表已经存在重放时报告错SQL线程就会停下来。没有及时发现的话从库就会一直停留在旧状态。检测不一致的常用工具是pt-table-checksum它可以对比主从之间的表数据把差异记录出来。修复用pt-table-sync但务必在确认差异范围后再执行而且最好先在测试环境验证。我自己处理过一次比较典型的问题从库磁盘满了I/O线程断开业务侧因为没有告警硬生生隔了一周才发现。从那之后我对主从状态加了两层监控一层是定时抓取SHOW SLAVE STATUS的各个关键字段另一层是定期执行pt-table-checksum做数据校验。6.3 锁表问题的排查链路锁表问题在热搜里反复出现确实也是实践中的高频故障。出现锁表时不要慌按照下面这个链路来排查。先看当前所有连接在做什么SHOW FULL PROCESSLIST;重点看State列很多锁等待会显示Waiting for table metadata lock、Waiting for table level lock、或者处于Locked状态。如果出现大量Waiting for table metadata lock通常是有人执行了DDL而DDL在等待某条查询释放表的元数据锁。查询本身可能一直不结束或者被事务卡住。这种场景下先找出阻塞源头。在MySQL 8.0里可以直接查performance_schema.data_lock_waits比较麻烦的方式是查information_schema.innodb_trx看当前事务列表然后根据事务的开始时间和状态判断谁在持有锁。造成行锁竞争的最常见原因是索引失效。比如UPDATE一个字符串字段没加引号或者隐式类型转换导致索引没有走到位最终InnoDB给整张表的所有行都加了锁。表面上看是行锁实际效果和表锁没区别。这个问题的规避方法是把SQL的执行计划养成习惯任何一个UPDATE或DELETE在生产执行之前先用EXPLAIN看一遍访问类型是不是range、ref或者eq_ref如果是ALL就得停下来先处理索引。死锁问题主要靠参数兜底。innodb_lock_wait_timeout控制等待锁的时间默认50秒可以在my.cnf中调低到10秒左右让业务快速失败后重试。同时开启innodb_deadlock_detectMySQL会定期检测死锁并回滚其中某个事务避免互相僵持。6.4 关于MySQL面试题的一些方向既然热搜里有mysql面试题我也顺便说两句。面试官问存储引擎本质是想考察你能否判断线上场景的技术选型问主从复制本质是考察你对数据一致性和高可用的理解问分库分表更多是考察面对大规模数据时你有没有系统性的思考能力。所以光记住概念不够最好能展开说说你实际遇到过什么问题、怎么排查的。比如提到MIXED binlog格式如果能说清楚为什么不建议在生产环境用STATEMENT就会比单纯背概念有说服力得多。面试中还有一个高频点是问你MySQL 8.0相比旧版本的变化比如默认认证插件改成了caching_sha2_password如果客户端驱动版本太老就会出现认证失败连接不上这在部署新版本的时候非常容易踩中提醒大家在升级时同步更新驱动。如果自己搭过一套主从、跑过分库分表的迁移演练把这些经历讲出来比背一百道面试题都管用。我个人在实际操作中的体会是数据库架构设计没有银弹。存储引擎选型、主从复制、分库分表这三件事本质上都是在做取舍用一致性换性能用复杂度换容量。每次动手之前先问一句当前瓶颈到底在哪、不拆行不行、拆了之后受益的是哪个查询把这一步想明白后面很多坑都可以提前躲开。
