拓冰网站定制 
1. 建库之前先把这几件事想明白我这个标题写的是“数据库和表的操作”但你别小看这六个字。我入行这几年见过太多人——包括曾经的我自己——一上来就CREATE DATABASE xxx; CREATE TABLE yyy;咔咔一顿写结果上线没两天就出幺蛾子中文乱码、字段类型不够用、表结构改不动、慢查询拖垮接口……每一个问题追根溯源都能回到建库建表那几步没走稳。很多人觉得数据库操作嘛不就是增删改查背几条 SQL 语法就会了。但你真去面试或者自己维护线上库会发现最容易被拷问的恰恰是这些基础操作背后的取舍字符集为什么不能随便选字段为什么不能图省事全用VARCHAR为什么我明明建了索引查询还是慢为什么删一条几千万行大表里的数据能把整个业务锁死这篇文章我会从建库开始一路讲到建表、CRUD、索引、事务、锁再到几千万行大表的运维实操。我不打算给你念文档我按自己实际踩坑的顺序来讲哪些命令能直接抄哪些参数必须像调菜谱一样反复验证哪些“常识”其实坑了你。这篇内容适合刚入门的同学打好地基也适合有两年左右经验、遇到瓶颈想回头补基础的开发者和运维。就算是老手我文末整理的排查速查表关键时刻也能帮你省半小时。需要先说明的是下面所有内容以 MySQL 8.0 为主部分兼容 5.7。如果你的生产库还停留在这个版本之前我建议你认真考虑升级——不光是性能更是数据类型和优化器层面的巨大差距。2. 数据库层面创建、选型与权限管理2.1 为什么数据库命名和字符集是第一个大坑先看一段我工作中非常常见的错误写法CREATE DATABASE user; CREATE DATABASE test; CREATE DATABASE demo DEFAULT CHARACTER SET utf8;第一个user在 MySQL 8.0 里会直接报错因为USER是系统表名属于保留字。第二个test能建但你等于顺手埋了个雷很多自动化工具默认会扫描或忽略 test 库权限配置也容易混乱。第三个你指定了utf8但如果你的 MySQL 默认排序规则和版本不匹配插入中文依旧可能报错。我自己现在的建库标准写法是这样的CREATE DATABASE IF NOT EXISTS mall_order DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;注意三个关键点。第一库名一律用业务域子域比如mall_order、user_center不要用demo、test更不要用全拼缩写搞得谁都看不懂。第二字符集无脑选utf8mb4。它不是比utf8多几个字符这么简单utf8mb4才是真正的完整 UTF-8。为啥因为 MySQL 的utf8字符集最多支持 3 个字节而像 emoji 这类字符需要 4 个字节。你还别笑真有团队上线后发现用户昵称带个表情符号就直接写入失败的排查到凌晨两点就是字符集的问题。除此之外现在很多手机号、地址信息里都可能掺杂特殊符号用utf8mb4是唯一正确的选择。第三排序规则尽量跟着字符集版本走。MySQL 8.0 默认排序规则是utf8mb4_0900_ai_ci它比 5.7 时代的utf8mb4_general_ci排序更准确、性能更好。记住一条原则如果你不确切知道某个排序规则有什么特定用途就保持它跟默认一致不要画蛇添足。提示云厂商数据库开通时默认给的字符集往往没问题但自建的 MySQL尤其是用系统自带包安装的默认可能是latin1。你建库前务必先查一遍SHOW VARIABLES LIKE character_set_server;如果显示 latin1后续所有库表的字符集都要靠 DDL 显式指定否则乱码只是时间问题。2.2 三张系统表帮你摸清数据库底细操作数据库之前先学会“看”。MySQL 的信息模式information_schema里藏着三张高频使用的表很多人一上来就SHOW DATABASES;看个名字就完事遇到问题就懵。第一张是schemata记录所有数据库的字符集和排序规则。排查乱码第一站就是它SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA;第二张是tables记录每个表归属的库、表类型BASE TABLE 还是 VIEW、行数估算值、存储引擎、自增 ID 等。检查数据量、清理冷表全靠它SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, AUTO_INCREMENT, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA mall_order;注意TABLE_ROWS是估算值InnoDB 下并不精确精确行数只能COUNT(*)。但做容量规划时几倍的误差完全够用了。第三张是statistics也就是索引信息。排查“为什么建了索引没走”之前先确认索引到底建没建对SELECT TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA mall_order ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;这三张表建议你保存成常用查询它们比任何 GUI 工具的树形列表都好用特别是在命令行环境下批量排查问题。2.3 权限分配的边界感数据库建好了下一步是给各个应用建账号。新手容易犯的错是图省事所有应用共用 root 或者给一个账号ALL PRIVILEGES一把梭。这样做的后果是一旦某个业务接口存在 SQL 注入攻击者拿到账号等于拿到了整库的控制权一旦线上误操作连个追责隔离的边界都没有。我习惯的权限矩阵是这样的使用场景账号命名典型权限后端应用app_rw业务库上的 SELECT / INSERT / UPDATE / DELETE / INDEX数据统计bi_readonly业务库上的 SELECT定时任务/脚本job_xxx视任务需求最小化为目标表权限DBA 运维dba_admin更高级的管理权限仅限运维人员使用授权语句现在一般这么写CREATE USER app_rw% IDENTIFIED BY 强密码; GRANT SELECT, INSERT, UPDATE, DELETE ON mall_order.* TO app_rw%; FLUSH PRIVILEGES;这里我再加一句云数据库大多支持白名单机制不要把%放得太大。能限定内网网段就不要全开。另外启动 MySQL 时建议不要用skip-grant-tables我见过有人本地调试图方便加上这个参数结果暴露在公网后整库被拖走。别嫌我啰嗦这些坑每年都在不同的人身上重演。3. 存储引擎与表结构动手建表前要想清楚的事3.1 InnoDB 和 MyISAM一张表定生死现在 MySQL 8.0 建表默认就是 InnoDB但总有人从老项目里继承来 MyISAM 表。存储引擎这事属于“你可以不用但不能不查”的范畴。如果现在还有人告诉你“MyISAM 性能好”让你新建表用它你可以让他把这句话收回去了。直接抛几个关键差异都是我在实际工作中验证过的比较维度InnoDBMyISAM事务支持ACID 完整不支持一条大事务做到一半断电表直接标记损坏行级锁支持高并发写入友好仅表级锁写入一多直接排队阻塞崩溃恢复通过 redo log 自动恢复往往需要手动修复甚至丢数据全文索引8.0 已支持中文分词早期优势现在毫无优势数据文件表结构数据索引集中管理分离的 .frm/.MYD/.MYI有句话说得很直白只要不是纯读的历史归档表一律用 InnoDB。即使纯读场景InnoDB 引入了 Buffer Pool 后性能也不输。线上如果发现 MyISAM 表尽量在维护窗口平滑迁移ALTER TABLE old_table ENGINE InnoDB;注意这个操作在数据量大时会锁表最好在低峰期执行或使用工具在业务无感的情况下转换。3.2 字段类型选择宁可多花十分钟想清楚这是我见过最高频的“日常翻车点”。举个例子CREATE TABLE user_info ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 年龄, phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号, money DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 账户余额, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0-正常 1-禁用 2-注销, remark VARCHAR(255) NOT NULL DEFAULT COMMENT 备注, PRIMARY KEY (id), KEY idx_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户信息表;这里面的细节我逐个说。TINYINT占用 1 字节范围是 -128 到 127 或 0 到 255。存年龄、状态码这种小整数就别用INT纯粹浪费存储和索引空间。VARCHAR存的不是固定长度但别因此就把所有短文本都定义成VARCHAR(5000)因为内存临时表排序时是按定义长度分配内存的定义越大排序消费的内存和临时文件越多。DECIMAL(10,2)存金额精确小数绝不要用FLOAT/DOUBLE后者是浮点数算账时会出现 0.10.2 ! 0.3 的尴尬。手机号为什么用VARCHAR(20)不用BIGINT因为手机号可能带 86、- 等符号而且未来可能加入号段之外的信息数值类型完全没必要。比较隐蔽的一点是id用了INT UNSIGNED。UNSIGNED可以让同样 4 字节的情况下正数范围翻倍而对于绝大多数业务表千万级别的数据量用INT UNSIGNED完全够。什么时候要用BIGINT当你做分布式 ID 算法比如雪花 ID时生成的值会超过INT范围必须用BIGINT。这个选择一定要在建表时想清楚因为后期从 INT 改 BIGINT 的代价是重建整个索引树表越大越痛苦。status这类枚举字段我是强烈建议用TINYINT加上注释而不是用VARCHAR存中文。不要小看这个细节枚举用数字存既能用索引高效检索又能在代码里做映射还不会在数据库里出现“正常”和“正常 ”这种肉眼看不出来的差异导致统计出错。3.3 主键和索引表的灵魂就这两样主键这块我见过不少“建表不加主键”的操作MySQL 会给你生成隐藏主键但这东西对你毫无用处复制、日志、性能全受影响。所以建表一定要显式声明主键。至于主键类型自增INT/BIGINT和业务唯一键比如订单号二选一我个人在实际业务中更推荐自增主键加业务唯一键联合使用。自增主键的好处是写入顺序和索引顺序一致B 树不用频繁页分裂性能稳定。业务唯一键比如order_no单独建唯一索引既保证业务上不重复又不影响主键的自增特性。但是要注意自增主键在 MySQL 8.0 之前有个臭名昭著的“重启重置”问题——事务回滚后自增 ID 被消耗重启后可能复用导致旧记录被新记录覆盖。虽然 8.0 已经优化了这个问题但我建议你在重要业务表上仍然显式声明AUTO_INCREMENT的初始值并且在应用层尽量避免对 ID 的强依赖。普通索引的设计我简单给一个判断标准区分度低、查询频率高的字段单独建索引意义不大。比如性别字段只有 0、1、2 三个值建了索引优化器大概率也不用因为扫全表比走索引再回表更快。适合建索引的是像order_status created_at这种组合条件查询。关于复合索引最左前缀原则后面第 5 章我会结合回表问题细讲。4. 核心 CRUD 深入解析从 SQL 写法到底层原理4.1 INSERT 的三种形态你该选哪种增删改查是数据库操作的根基很多人觉得没啥好讲的但真想写出高性能、不出事的 DML里面的弯弯绕绕不少。INSERT在面试和实际使用中主要就三种场景第一种单条插入最朴素不做赘述。第二种批量插入。新手最容易犯的错是在应用代码里 for 循环单条 INSERT小数据量无所谓几万条数据就可能把数据库的连接和日志全部拖慢。正确做法是拼成一条多 VALUES 的语句或者用绑定变量批量执行INSERT INTO order_detail (order_id, product_id, num) VALUES (1001, 2001, 2), (1001, 2002, 1), (1002, 3001, 1);一次插入几百到几千条尽量控制在单条 SQL 的合理体积内几百 KB 没问题但别硬塞 10MB。多 VALUES 插入减少的是网络往返和 SQL 解析次数效果非常显著我在 benchmark 里实测过批量插入比循环单插性能能提升 5 到 10 倍。第三种INSERT ... ON DUPLICATE KEY UPDATE简称 upsert。这个语法在 8.0 里还有个新写法INSERT ... AS new ON DUPLICATE KEY UPDATE但核心思路一致如果唯一键冲突就执行更新而不是报错。日常同步数据、做统计累加它是神器。INSERT INTO user_daily_stats (user_id, stat_date, order_cnt, total_amount) VALUES (101, 2025-06-01, 3, 299.00) ON DUPLICATE KEY UPDATE order_cnt order_cnt VALUES(order_cnt), total_amount total_amount VALUES(total_amount);这里有个 8.0.19 以前要注意的坑VALUES()函数在未来的版本里会移除8.0.20 以后建议用别名方式。不过对于大多数还在用 5.7/8.0 早期版本的同学这个写法依旧没问题。关键是理解这个 upsert 在并发下有可能产生死锁或锁等待因为它涉及“先尝试插入冲突再更新”两步操作如果对同一行的写入并发很高要考虑对业务字段做个串行化改造。4.2 SELECT 的执行顺序远比你想的复杂关于SELECT我不想再重复背一遍语法而是想说说 MySQL 真正执行一条 SELECT 的内部顺序。这个顺序你搞明白了写复杂查询就不会晕。SQL 语句书写的顺序是SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT。但 MySQL 执行的顺序大体是这样的FROM先确定数据来源于哪张表如果多表 JOIN这一步还包含表的连接和笛卡尔积收缩。WHERE对行做初步过滤这时候还不能用 SELECT 里的别名。GROUP BY按分组字段聚合。HAVING对聚合后的组做过滤这里可以用聚合函数。SELECT选定要返回的列、计算表达式。ORDER BY排序。LIMIT做行数截断但这只是个“最后一步”优化器在特定场景下会下推提前。理解这个顺序能解决一个非常常见的问题为什么WHERE里不能用SELECT里的别名因为执行顺序上WHERE先于SELECT别名在WHERE执行时还不存在。而HAVING之所以能引用 SELECT 里的别名是因为它排在 SELECT 之后。这些看似“语法规定”的东西本质上都是执行顺序的自然结果。性能优化时优先看WHERE和JOIN条件能不能走索引因为只有在数据源头缩减后面的聚合、排序负担才会变小。很多人喜欢先把全表 SELECT 出来再在应用层过滤这种“全表搬运型”写法在数据量刚过万级别时没什么感觉到了百万千万级就是灾难。4.3 UPDATE 和 DELETE 的隐蔽风险说到UPDATE和DELETE最常见的惨案是忘了加WHERE条件或者WHERE条件没有索引。前者好理解全表更新/删除的后果不堪设想尤其是生产环境一秒就能让你上故障复盘会。我的习惯是任何UPDATE、DELETE先写WHERE再写前面的动作如果是处理线上数据先SELECT COUNT(*)确认影响行数再开事务执行执行完先查一遍再COMMIT条件字段必须有索引否则 InnoDB 会扫描全表并给每一行加锁这是造成线上大面积阻塞的经典原因。说到行锁我举一个例子你就知道多恐怖。假设user_order表有 5000 万行你执行DELETE FROM user_order WHERE user_id 123 AND order_status 0;如果联合索引(user_id, order_status)存在这条语句只锁匹配行如果没索引MySQL 会全表扫描并对逐行加锁直到找到所有匹配行。这个过程里其他所有对该表的写入操作都要排队最终结果就是“数据库卡死连接数暴涨应用雪崩”。我的建议是大表的DELETE不要一次全删按主键分批删除DELETE FROM user_order WHERE id IN ( SELECT id FROM ( SELECT id FROM user_order WHERE user_id 123 AND order_status 0 LIMIT 1000 ) t );每次删 1000 行中间停顿几秒让 InnoDB 有机会合并索引页、释放锁和 undo log。别小看这个操作几千万行大表做数据清理时这个“温水煮青蛙”式的删除是线上唯一的稳妥方案。如果你一次删 100 万行undo log 膨胀、主从延迟、锁持有时间过长每一样都能让你焦头烂额。5. 索引机制、回表与查询优化为什么加了索引还是慢5.1 B 树索引的底层常识讲清了才能明白为什么索引的核心是 B 树我尽量用大白话讲。B 树的叶子节点存储了全部索引字段值以及指向行数据的指针。在 InnoDB 里主键索引的叶子节点直接存储整行数据这叫聚集索引辅助索引非主键索引的叶子节点存储的是索引字段的值 主键值。所以当你用辅助索引查询时过程是这样的在辅助索引 B 树里查到你想要的索引值拿到对应主键值再用主键值到主键索引聚集索引里查整行数据。这第 3 步就是面试、优化里天天说的回表。回表本身并不可怕可怕的是它发生的次数太多比如辅助索引查出来 1 万行符合条件的记录每行都要回表性能自然暴跌。那怎么避免回表两个思路。第一个思路是覆盖索引。也就是让查询的列全部落在辅助索引中这样 B 树叶子节点上直接就有结果不需要回表。比如SELECT order_id, order_status FROM user_order WHERE user_id 123;如果你建了联合索引(user_id, order_status)那么查询需要的两列都在这颗索引树上MySQL 直接扫索引树不回表。这就是覆盖索引的威力。写 SQL 时你要有意识地让 SELECT 的列尽量保持在索引覆盖范围内尤其是在列表页、报表统计场景。第二个思路是当需要回表的行数占比太大时优化器会干脆放弃辅助索引改走全表扫描。这个临界点通常在全表数据的 10% 到 20% 左右实际由优化器成本模型算。所以别迷信“有索引就一定快”数据分布决定了索引该不该用。还有一点是最左前缀原则。联合索引(a, b, c)实际生效的查询组合是a、a,b、a,b,c。如果你查询条件是b或者c这个索引没法调用。我见过有团队建了个(user_id, order_status, created_at)索引却经常写WHERE order_status0 AND created_at ...索引直接失效因为他们跳过了第一个字段。5.2 索引失效的 5 个高频场景逐个排查这几个场景我几乎每周都会碰到列成表你们直接查场景错误示范原因正确姿势隐式类型转换WHERE phone 13800000000phone 是 VARCHARMySQL 把字符串列转成数字比索引失效WHERE phone 13800000000前导模糊WHERE name LIKE %张三%无法确定前缀B 树没法二分查找尽量张三%或者用全文索引函数包裹列WHERE DATE(created_at) 2025-06-01对列做函数操作打断索引匹配created_at 2025-06-01 AND created_at 2025-06-02隐式字符集不一致两表关联字段一个 utf8mb4 一个 utf8关联时发生隐式转换统一字符集排序规则不满足最左前缀联合索引(a,b,c)查询只用 b 或 c跳过前导列调整索引设计或查询条件其中隐式类型转换这条我印象特别深的是一次排查慢查询一个用户表规模 300 万查询用户手机号走了足足 1.2 秒。看执行计划type ALL直接全表扫。后来发现传入的是数字类型应用层没有转字符串而字段是 VARCHARMySQL 不得不对每一行的 phone 做数值转换再比较索引自然用不上。改成字符串后耗时降到几十毫秒。函数包裹列这条也经常被忽视。很多人图省事把条件写成WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2025-06-01这个写法在数据量小的开发环境完全没问题但到了生产环境每行记录都要先做一次 DATE_FORMAT 计算索引树完全无法利用。在我的线上经验里改成范围查询后执行计划从ALL变range这是日常优化中性价比最高的一类改动。5.3 执行计划实战教学STRAIGHT_JOIN 与小结果集驱动大结果集EXPLAIN一定要成为你的肌肉记忆。我这里挑两个高频点讲。第一个是驱动表的判断。MySQL 多表 JOIN 时会选择一张表作为驱动表先查这张表再用结果去关联被驱动表。优化器一般会选“小表驱动大表”即先用数据量小、过滤结果少的那张表作为驱动这样关联次数少。但优化器偶尔也会判断失误特别是统计信息过期的时候。这时候可以用STRAIGHT_JOIN强制指定驱动顺序SELECT STRAIGHT_JOIN a.*, b.* FROM big_table a INNER JOIN small_table b ON a.small_id b.id WHERE b.status 1;这里人为指定了big_table作为驱动表——我知道这跟理论是反的但实际中确实存在小表作为被驱动表比作为驱动表更高效的场景。写这句的意思是EXPLAIN没有标准答案一切以实际数据分布为准。STRAIGHT_JOIN只是最后的手段不要一开始就想着强制指定顺序先让优化器跑一把。第二个是Using filesort和Using temporary这两个指标。看到它们说明查询里有排序或分组没有走索引。解决办法很简单让 ORDER BY 字段和 WHERE 条件字段组成联合索引并且满足最左前缀。比如SELECT * FROM user_order WHERE user_id 123 ORDER BY created_at DESC;建索引(user_id, created_at)这一条语句里 WHERE 过滤和排序都可以走索引不需要临时文件排序。这是索引设计中非常经典的一招也是日常优化最容易见效的地方。6. 事务、锁与并发控制线上 DDL 的避坑指南6.1 隔离级别和幻读一张表讲透事务隔离级别你如果不能用一句话讲明白那上线出问题的时候一定吃大亏。MySQL 默认是 REPEATABLE READ为什么还很多团队刻意改成 READ COMMITTED得从隔离级别解决的问题说起。隔离级别胜读脏数据可重复读幻读锁范围READ UNCOMMITTED是否是无锁READ COMMITTED否否是行锁REPEATABLE READ默认否是否InnoDB 用间隙锁解决行锁间隙锁SERIALIZABLE否是否全部加锁重点解释一下幻读在 REPEATABLE READ 下事务 A 两次SELECT COUNT(*)结果一致但事务 B 插入了一条新记录并提交事务 A 如果执行UPDATE或INSERT ... SELECT会发现“多出来”的行像幽灵一样。InnoDB 靠间隙锁Gap Lock来解决它会在索引记录的间隙加锁让其他事务无法在这个间隙里插入数据。但间隙锁也带来了问题——死锁概率上升且并发插入的性能下降。所以很多互联网公司会把隔离级别改成 READ COMMITTED加 binlog 的 row 模式。我比较推荐这个组合它能避免大部分由间隙锁导致的死锁代价是“不可重复读”但大多数业务场景根本不在乎两次读之间的细微差异。如果你确实需要一致性快照读那就在事务里显式加SELECT ... FOR SHARE或FOR UPDATE而不是依赖全局隔离级别。6.2 大表在线变更三条路怎么选线上最怕的事之一就是给大表加字段。直接ALTER TABLE在 MySQL 5.6 之前会锁全表后来虽然有了在线 DDL 的ALGORITHMINPLACE大表变更仍然要小心。我总结三条路。第一小表千万行以下直接跑ALTER TABLE user_order ADD COLUMN ext_json JSON NULL COMMENT 扩展字段 AFTER status, ALGORITHMINPLACE, LOCKNONE;ALGORITHMINPLACE, LOCKNONE是 5.6 支持的在线变更方式理论上允许并发 DML。但实际执行时如果改动需要重建表还是会存在短暂的锁窗口。第二大表几千万到亿级别必须用工具比如gh-ost或pt-online-schema-change。它们原理类似创建一张影子表同步数据然后在业务低峰期切换。我自己用gh-ost较多因为它不依赖触发器对主库压力更小。命令大概是gh-ost \ --host127.0.0.1 --port3306 \ --userdba --passwordxxx \ --databasemall_order --tableuser_order \ --alterADD COLUMN ext_json JSON NULL COMMENT 扩展字段 \ --execute这个过程会经历全量复制增量追平如果表有外键或者没有主键它会直接拒绝工作。所以建议你从现在就开始检查线上表没有主键的表必须想办法补上。第三如果对工具链不熟最稳妥的策略是先建新表导入数据切换应用层配置。虽然听起来“很土”但反而最可控适合超大规模表或者强监管的业务。6.3 死锁和锁等待两分钟定位根因排查死锁MySQL 提供了现成的武器SHOW ENGINE INNODB STATUS\G在输出里搜 “LATEST DETECTED DEADLOCK”里面有死锁涉及的事务、SQL、锁类型能直接看到是哪两个事务互相等待资源。锁等待超时的定位则是看SELECT * FROM sys.innodb_lock_waits;MySQL 8.0 自带 sys 库这条语句能把“谁锁住了谁”直接列出来比在 performance_schema 里翻半天高效太多。我的排查习惯是三步走先看sys.innodb_lock_waits找出持锁事务和等待事务杀掉阻塞源最久的那个事务KILL thread_id回头分析业务代码为什么在同一行数据上有这么高的并发更新。究其原因多半是应用层对同一条记录频繁 update比如扣减库存、累加计数器。解决办法是减少单行热点比如把库存拆分成多行。关于死锁还有一个容易被忽略的原则InnoDB 死锁是“事务之间互相竞争资源”导致的它本身不是 bug而是并发控制机制在正常工作。你无法完全避免死锁能做的只是把死锁发生概率降到最低并且做好重试机制。常规手法包括统一 SQL 访问顺序让所有事务都按同一种顺序比如先 user 表后 order 表操作记录尽量缩小事务的持有锁的时间不要在一个事务里做远程调用或者耗时计算。7. 几千万行大表场景下的日常运维实战7.1 分页查询深度优化OFFSET 越翻越慢怎么办列表分页是很常见的需求但“深分页”问题在大表上体现得非常明显。LIMIT 1000000, 20不是只查 20 行而是要扫掉前面 100 万行再返回这个操作随着页码增长越来越慢最终拖垮接口。我用得最多的优化方案是延迟关联延迟 joinSELECT t1.id, t1.order_no, t1.amount FROM user_order t1 INNER JOIN ( SELECT id FROM user_order WHERE order_status 0 ORDER BY created_at DESC LIMIT 1000000, 20 ) t2 ON t1.id t2.id;思路是先用覆盖索引查出主键再根据主键回表取最终要的行。因为子查询里只查 id 字段索引完全覆盖不需要读取整行数据外层再把需要的行拼回来只涉及 20 行回表代价小到忽略不计。实测在 3000 万行数据上普通深分页需要 5 秒以上这个写法通常能卡在百毫秒级。另一个我认为值得所有团队做的事是不要让用户真的翻到第 100 万行。业务侧做“只加载前 N 页”的限制或者用“下一页基于最大 ID/时间戳”的游标分页SELECT * FROM user_order WHERE created_at 2025-06-01 00:00:00 ORDER BY created_at DESC LIMIT 20;游标分页的本质是把“翻页”变成“偏移”永远只查 20 条记录性能与页数无关。这种方式特别适合信息流、订单列表这类按时间倒序的场景。7.2 大表加索引、清理数据的窗口期策略大表操作的根本原则就一条把对线上业务的影响降到最低。影响主要来自三方面锁、IO 压力、主从延迟。锁的问题前面已经说了用工具解决。IO 压力怎么控制gh-ost和pt-online-schema-change都有 throttle 参数可以把复制速度控制在一个安全的临界点以内。我通常会把最大复制线程数调低让迁移过程在老业务的空闲带宽内慢慢跑虽然慢一点但稳。主从延迟是大表操作的另一个隐形杀手。如果主库上大批量更新数据从库的 SQL 线程可能追不上主库的 binlog延迟一会从几百毫秒拉到几十秒读从库的业务就全乱了。解决思路是“小步快跑”也就是我在第 4.3 节说的分批 DELETE/UPDATE。每次几百行等几秒观察从库延迟指标下降再继续。云数据库控制台一般都有主从延迟监控自建环境就看SHOW REPLICA STATUS里的Seconds_Behind_Source。最后分享一个我自己常用的“三步验证法”每次改完大表之后必做先看EXPLAIN确认执行计划走了预期索引扫描行数从百万级降到千级看慢查询日志抓几条新 SQL 实测响应时间观察业务监控确认没有锁等待、CPU 异常、从库延迟飙升。这套流程我已经用了好几年几乎成了肌肉记忆也是我认为做数据库运维最核心的“手艺”。7.3 数据库连接池与同步工具的选择逻辑大表运维绕不开连接池和数据同步。连接池这块每门语言都有对应的成熟方案。Java 生态常见 HikariCPGo 生态常见 database/sql 自带的连接池Python 常见 SQLAlchemy 的 pool。但大家容易忽略的是“连接池大小到底设多少”。一个常见的误解是连接数越大越好实际恰恰相反。数据库能同时处理的并发连接是有限的而每条连接背后都有内存开销和事务状态。我在一个高并发项目里曾把连接池最大连接数调到 200结果数据库 CPU 直接被打满改成 50 后整体吞吐反而上升。原因在于多余连接都在排队反而增加了上下文切换开销。连接池的核心参数如下参数建议值说明maximumPoolSize核心业务 20~50更多不意味着更快minimumIdle与 maximum 相同或略低避免频繁创建连接connectionTimeout3000~5000ms超过直接快速失败idleTimeout10~30 分钟释放空闲连接maxLifetime小于数据库 wait_timeout防止被服务端断开后继续使用数据同步工具我区分两个场景。一是业务层面的同构同步比如主从复制推荐如canal这类基于 binlog 的增量解析工具它能把数据库变更实时推送到下游。二是异构数据同步比如 MySQL 到 Elasticsearch常用flinkx、DataX、或云厂商自带的 DTS。选型逻辑其实是看你对延迟的容忍度和对数据一致性的要求没有银弹。凡是涉及跨表合并、多源关联的同步我都会把主键冲突策略和幂等设计放在第一位因为同步工具重播数据时如果没有幂等机制很容易造成线上数据错乱。8. 常见问题排查速查表与实操心得我整理了近一年工作中高频出现的问题按现象、原因、快速处理方式做成一张表。这张表在平时排查问题时非常好用你也可以直接抄走。现象可能原因快速排查与处理连接数打满应用报 Too many connections慢查询积压 / 连接池过大SHOW PROCESSLIST;把长查询杀了调整连接池中文乱码库/表字符集不是 utf8mb4查库表字符集统一 utf8mb4必要时转码数据某条查询突然变慢统计信息过期优化器选错索引ANALYZE TABLE 表名;重新收集统计信息插入数据时死锁多事务对同一行反向更新SHOW ENGINE INNODB STATUS\G定位事务统一访问顺序主从延迟很大大事务或者备份任务占 IO分析 binlog 里的大事务拆分、错峰明明有索引执行计划显示全表扫隐式转换 / 函数包裹列 / 区分度低按 5.2 节逐一排查分页越翻越慢深分页导致扫描大量行延迟关联或游标分页改造删数据特别慢无索引条件加锁全表 / undo 膨胀分批删除条件走索引再讲两个不算常见但一旦碰到就很要命的问题。一个是 MySQL 8.0 下 SSL 连接报错。排查方向是确认客户端和服务端的 SSL 协议版本是否匹配经常是 JDBC 驱动版本过旧导致的。在配置里加useSSLfalse只能治标正确做法是升级驱动并正确配置 SSL 证书校验。另一个是自增 ID 耗尽。当你建表用了INT UNSIGNED最大范围约 42 亿对多数业务够用可一旦碰到秒级插入量大的日志表ID 耗尽只是时间问题。耗尽后插入会报主键冲突应用端表现为插入失败且看不出明显日志。预防办法就是建表时对增长极快的表直接用BIGINT并做好容量监控。最后分享一段我自己的心法数据库操作这门手艺看起来静如处子用起来动如脱兔。所谓“高手”不是会把上百行 SQL 背下来而是在建表之前就想过表会怎么长大、查询会怎么访问、数据会怎么删除、索引会不会被隐式转换拦截。很多线上事故本来就是可以提前避免的只要你在设计阶段多花那十分钟后面就能少熬好几个通宵。 MySQL建库建表与索引优化实战：从基础到千万级大表运维
1. 建库之前先把这几件事想明白我这个标题写的是“数据库和表的操作”但你别小看这六个字。我入行这几年见过太多人——包括曾经的我自己——一上来就CREATE DATABASE xxx; CREATE TABLE yyy;咔咔一顿写结果上线没两天就出幺蛾子中文乱码、字段类型不够用、表结构改不动、慢查询拖垮接口……每一个问题追根溯源都能回到建库建表那几步没走稳。很多人觉得数据库操作嘛不就是增删改查背几条 SQL 语法就会了。但你真去面试或者自己维护线上库会发现最容易被拷问的恰恰是这些基础操作背后的取舍字符集为什么不能随便选字段为什么不能图省事全用VARCHAR为什么我明明建了索引查询还是慢为什么删一条几千万行大表里的数据能把整个业务锁死这篇文章我会从建库开始一路讲到建表、CRUD、索引、事务、锁再到几千万行大表的运维实操。我不打算给你念文档我按自己实际踩坑的顺序来讲哪些命令能直接抄哪些参数必须像调菜谱一样反复验证哪些“常识”其实坑了你。这篇内容适合刚入门的同学打好地基也适合有两年左右经验、遇到瓶颈想回头补基础的开发者和运维。就算是老手我文末整理的排查速查表关键时刻也能帮你省半小时。需要先说明的是下面所有内容以 MySQL 8.0 为主部分兼容 5.7。如果你的生产库还停留在这个版本之前我建议你认真考虑升级——不光是性能更是数据类型和优化器层面的巨大差距。2. 数据库层面创建、选型与权限管理2.1 为什么数据库命名和字符集是第一个大坑先看一段我工作中非常常见的错误写法CREATE DATABASE user; CREATE DATABASE test; CREATE DATABASE demo DEFAULT CHARACTER SET utf8;第一个user在 MySQL 8.0 里会直接报错因为USER是系统表名属于保留字。第二个test能建但你等于顺手埋了个雷很多自动化工具默认会扫描或忽略 test 库权限配置也容易混乱。第三个你指定了utf8但如果你的 MySQL 默认排序规则和版本不匹配插入中文依旧可能报错。我自己现在的建库标准写法是这样的CREATE DATABASE IF NOT EXISTS mall_order DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;注意三个关键点。第一库名一律用业务域子域比如mall_order、user_center不要用demo、test更不要用全拼缩写搞得谁都看不懂。第二字符集无脑选utf8mb4。它不是比utf8多几个字符这么简单utf8mb4才是真正的完整 UTF-8。为啥因为 MySQL 的utf8字符集最多支持 3 个字节而像 emoji 这类字符需要 4 个字节。你还别笑真有团队上线后发现用户昵称带个表情符号就直接写入失败的排查到凌晨两点就是字符集的问题。除此之外现在很多手机号、地址信息里都可能掺杂特殊符号用utf8mb4是唯一正确的选择。第三排序规则尽量跟着字符集版本走。MySQL 8.0 默认排序规则是utf8mb4_0900_ai_ci它比 5.7 时代的utf8mb4_general_ci排序更准确、性能更好。记住一条原则如果你不确切知道某个排序规则有什么特定用途就保持它跟默认一致不要画蛇添足。提示云厂商数据库开通时默认给的字符集往往没问题但自建的 MySQL尤其是用系统自带包安装的默认可能是latin1。你建库前务必先查一遍SHOW VARIABLES LIKE character_set_server;如果显示 latin1后续所有库表的字符集都要靠 DDL 显式指定否则乱码只是时间问题。2.2 三张系统表帮你摸清数据库底细操作数据库之前先学会“看”。MySQL 的信息模式information_schema里藏着三张高频使用的表很多人一上来就SHOW DATABASES;看个名字就完事遇到问题就懵。第一张是schemata记录所有数据库的字符集和排序规则。排查乱码第一站就是它SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA;第二张是tables记录每个表归属的库、表类型BASE TABLE 还是 VIEW、行数估算值、存储引擎、自增 ID 等。检查数据量、清理冷表全靠它SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, AUTO_INCREMENT, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA mall_order;注意TABLE_ROWS是估算值InnoDB 下并不精确精确行数只能COUNT(*)。但做容量规划时几倍的误差完全够用了。第三张是statistics也就是索引信息。排查“为什么建了索引没走”之前先确认索引到底建没建对SELECT TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA mall_order ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;这三张表建议你保存成常用查询它们比任何 GUI 工具的树形列表都好用特别是在命令行环境下批量排查问题。2.3 权限分配的边界感数据库建好了下一步是给各个应用建账号。新手容易犯的错是图省事所有应用共用 root 或者给一个账号ALL PRIVILEGES一把梭。这样做的后果是一旦某个业务接口存在 SQL 注入攻击者拿到账号等于拿到了整库的控制权一旦线上误操作连个追责隔离的边界都没有。我习惯的权限矩阵是这样的使用场景账号命名典型权限后端应用app_rw业务库上的 SELECT / INSERT / UPDATE / DELETE / INDEX数据统计bi_readonly业务库上的 SELECT定时任务/脚本job_xxx视任务需求最小化为目标表权限DBA 运维dba_admin更高级的管理权限仅限运维人员使用授权语句现在一般这么写CREATE USER app_rw% IDENTIFIED BY 强密码; GRANT SELECT, INSERT, UPDATE, DELETE ON mall_order.* TO app_rw%; FLUSH PRIVILEGES;这里我再加一句云数据库大多支持白名单机制不要把%放得太大。能限定内网网段就不要全开。另外启动 MySQL 时建议不要用skip-grant-tables我见过有人本地调试图方便加上这个参数结果暴露在公网后整库被拖走。别嫌我啰嗦这些坑每年都在不同的人身上重演。3. 存储引擎与表结构动手建表前要想清楚的事3.1 InnoDB 和 MyISAM一张表定生死现在 MySQL 8.0 建表默认就是 InnoDB但总有人从老项目里继承来 MyISAM 表。存储引擎这事属于“你可以不用但不能不查”的范畴。如果现在还有人告诉你“MyISAM 性能好”让你新建表用它你可以让他把这句话收回去了。直接抛几个关键差异都是我在实际工作中验证过的比较维度InnoDBMyISAM事务支持ACID 完整不支持一条大事务做到一半断电表直接标记损坏行级锁支持高并发写入友好仅表级锁写入一多直接排队阻塞崩溃恢复通过 redo log 自动恢复往往需要手动修复甚至丢数据全文索引8.0 已支持中文分词早期优势现在毫无优势数据文件表结构数据索引集中管理分离的 .frm/.MYD/.MYI有句话说得很直白只要不是纯读的历史归档表一律用 InnoDB。即使纯读场景InnoDB 引入了 Buffer Pool 后性能也不输。线上如果发现 MyISAM 表尽量在维护窗口平滑迁移ALTER TABLE old_table ENGINE InnoDB;注意这个操作在数据量大时会锁表最好在低峰期执行或使用工具在业务无感的情况下转换。3.2 字段类型选择宁可多花十分钟想清楚这是我见过最高频的“日常翻车点”。举个例子CREATE TABLE user_info ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 年龄, phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号, money DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 账户余额, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0-正常 1-禁用 2-注销, remark VARCHAR(255) NOT NULL DEFAULT COMMENT 备注, PRIMARY KEY (id), KEY idx_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户信息表;这里面的细节我逐个说。TINYINT占用 1 字节范围是 -128 到 127 或 0 到 255。存年龄、状态码这种小整数就别用INT纯粹浪费存储和索引空间。VARCHAR存的不是固定长度但别因此就把所有短文本都定义成VARCHAR(5000)因为内存临时表排序时是按定义长度分配内存的定义越大排序消费的内存和临时文件越多。DECIMAL(10,2)存金额精确小数绝不要用FLOAT/DOUBLE后者是浮点数算账时会出现 0.10.2 ! 0.3 的尴尬。手机号为什么用VARCHAR(20)不用BIGINT因为手机号可能带 86、- 等符号而且未来可能加入号段之外的信息数值类型完全没必要。比较隐蔽的一点是id用了INT UNSIGNED。UNSIGNED可以让同样 4 字节的情况下正数范围翻倍而对于绝大多数业务表千万级别的数据量用INT UNSIGNED完全够。什么时候要用BIGINT当你做分布式 ID 算法比如雪花 ID时生成的值会超过INT范围必须用BIGINT。这个选择一定要在建表时想清楚因为后期从 INT 改 BIGINT 的代价是重建整个索引树表越大越痛苦。status这类枚举字段我是强烈建议用TINYINT加上注释而不是用VARCHAR存中文。不要小看这个细节枚举用数字存既能用索引高效检索又能在代码里做映射还不会在数据库里出现“正常”和“正常 ”这种肉眼看不出来的差异导致统计出错。3.3 主键和索引表的灵魂就这两样主键这块我见过不少“建表不加主键”的操作MySQL 会给你生成隐藏主键但这东西对你毫无用处复制、日志、性能全受影响。所以建表一定要显式声明主键。至于主键类型自增INT/BIGINT和业务唯一键比如订单号二选一我个人在实际业务中更推荐自增主键加业务唯一键联合使用。自增主键的好处是写入顺序和索引顺序一致B 树不用频繁页分裂性能稳定。业务唯一键比如order_no单独建唯一索引既保证业务上不重复又不影响主键的自增特性。但是要注意自增主键在 MySQL 8.0 之前有个臭名昭著的“重启重置”问题——事务回滚后自增 ID 被消耗重启后可能复用导致旧记录被新记录覆盖。虽然 8.0 已经优化了这个问题但我建议你在重要业务表上仍然显式声明AUTO_INCREMENT的初始值并且在应用层尽量避免对 ID 的强依赖。普通索引的设计我简单给一个判断标准区分度低、查询频率高的字段单独建索引意义不大。比如性别字段只有 0、1、2 三个值建了索引优化器大概率也不用因为扫全表比走索引再回表更快。适合建索引的是像order_status created_at这种组合条件查询。关于复合索引最左前缀原则后面第 5 章我会结合回表问题细讲。4. 核心 CRUD 深入解析从 SQL 写法到底层原理4.1 INSERT 的三种形态你该选哪种增删改查是数据库操作的根基很多人觉得没啥好讲的但真想写出高性能、不出事的 DML里面的弯弯绕绕不少。INSERT在面试和实际使用中主要就三种场景第一种单条插入最朴素不做赘述。第二种批量插入。新手最容易犯的错是在应用代码里 for 循环单条 INSERT小数据量无所谓几万条数据就可能把数据库的连接和日志全部拖慢。正确做法是拼成一条多 VALUES 的语句或者用绑定变量批量执行INSERT INTO order_detail (order_id, product_id, num) VALUES (1001, 2001, 2), (1001, 2002, 1), (1002, 3001, 1);一次插入几百到几千条尽量控制在单条 SQL 的合理体积内几百 KB 没问题但别硬塞 10MB。多 VALUES 插入减少的是网络往返和 SQL 解析次数效果非常显著我在 benchmark 里实测过批量插入比循环单插性能能提升 5 到 10 倍。第三种INSERT ... ON DUPLICATE KEY UPDATE简称 upsert。这个语法在 8.0 里还有个新写法INSERT ... AS new ON DUPLICATE KEY UPDATE但核心思路一致如果唯一键冲突就执行更新而不是报错。日常同步数据、做统计累加它是神器。INSERT INTO user_daily_stats (user_id, stat_date, order_cnt, total_amount) VALUES (101, 2025-06-01, 3, 299.00) ON DUPLICATE KEY UPDATE order_cnt order_cnt VALUES(order_cnt), total_amount total_amount VALUES(total_amount);这里有个 8.0.19 以前要注意的坑VALUES()函数在未来的版本里会移除8.0.20 以后建议用别名方式。不过对于大多数还在用 5.7/8.0 早期版本的同学这个写法依旧没问题。关键是理解这个 upsert 在并发下有可能产生死锁或锁等待因为它涉及“先尝试插入冲突再更新”两步操作如果对同一行的写入并发很高要考虑对业务字段做个串行化改造。4.2 SELECT 的执行顺序远比你想的复杂关于SELECT我不想再重复背一遍语法而是想说说 MySQL 真正执行一条 SELECT 的内部顺序。这个顺序你搞明白了写复杂查询就不会晕。SQL 语句书写的顺序是SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT。但 MySQL 执行的顺序大体是这样的FROM先确定数据来源于哪张表如果多表 JOIN这一步还包含表的连接和笛卡尔积收缩。WHERE对行做初步过滤这时候还不能用 SELECT 里的别名。GROUP BY按分组字段聚合。HAVING对聚合后的组做过滤这里可以用聚合函数。SELECT选定要返回的列、计算表达式。ORDER BY排序。LIMIT做行数截断但这只是个“最后一步”优化器在特定场景下会下推提前。理解这个顺序能解决一个非常常见的问题为什么WHERE里不能用SELECT里的别名因为执行顺序上WHERE先于SELECT别名在WHERE执行时还不存在。而HAVING之所以能引用 SELECT 里的别名是因为它排在 SELECT 之后。这些看似“语法规定”的东西本质上都是执行顺序的自然结果。性能优化时优先看WHERE和JOIN条件能不能走索引因为只有在数据源头缩减后面的聚合、排序负担才会变小。很多人喜欢先把全表 SELECT 出来再在应用层过滤这种“全表搬运型”写法在数据量刚过万级别时没什么感觉到了百万千万级就是灾难。4.3 UPDATE 和 DELETE 的隐蔽风险说到UPDATE和DELETE最常见的惨案是忘了加WHERE条件或者WHERE条件没有索引。前者好理解全表更新/删除的后果不堪设想尤其是生产环境一秒就能让你上故障复盘会。我的习惯是任何UPDATE、DELETE先写WHERE再写前面的动作如果是处理线上数据先SELECT COUNT(*)确认影响行数再开事务执行执行完先查一遍再COMMIT条件字段必须有索引否则 InnoDB 会扫描全表并给每一行加锁这是造成线上大面积阻塞的经典原因。说到行锁我举一个例子你就知道多恐怖。假设user_order表有 5000 万行你执行DELETE FROM user_order WHERE user_id 123 AND order_status 0;如果联合索引(user_id, order_status)存在这条语句只锁匹配行如果没索引MySQL 会全表扫描并对逐行加锁直到找到所有匹配行。这个过程里其他所有对该表的写入操作都要排队最终结果就是“数据库卡死连接数暴涨应用雪崩”。我的建议是大表的DELETE不要一次全删按主键分批删除DELETE FROM user_order WHERE id IN ( SELECT id FROM ( SELECT id FROM user_order WHERE user_id 123 AND order_status 0 LIMIT 1000 ) t );每次删 1000 行中间停顿几秒让 InnoDB 有机会合并索引页、释放锁和 undo log。别小看这个操作几千万行大表做数据清理时这个“温水煮青蛙”式的删除是线上唯一的稳妥方案。如果你一次删 100 万行undo log 膨胀、主从延迟、锁持有时间过长每一样都能让你焦头烂额。5. 索引机制、回表与查询优化为什么加了索引还是慢5.1 B 树索引的底层常识讲清了才能明白为什么索引的核心是 B 树我尽量用大白话讲。B 树的叶子节点存储了全部索引字段值以及指向行数据的指针。在 InnoDB 里主键索引的叶子节点直接存储整行数据这叫聚集索引辅助索引非主键索引的叶子节点存储的是索引字段的值 主键值。所以当你用辅助索引查询时过程是这样的在辅助索引 B 树里查到你想要的索引值拿到对应主键值再用主键值到主键索引聚集索引里查整行数据。这第 3 步就是面试、优化里天天说的回表。回表本身并不可怕可怕的是它发生的次数太多比如辅助索引查出来 1 万行符合条件的记录每行都要回表性能自然暴跌。那怎么避免回表两个思路。第一个思路是覆盖索引。也就是让查询的列全部落在辅助索引中这样 B 树叶子节点上直接就有结果不需要回表。比如SELECT order_id, order_status FROM user_order WHERE user_id 123;如果你建了联合索引(user_id, order_status)那么查询需要的两列都在这颗索引树上MySQL 直接扫索引树不回表。这就是覆盖索引的威力。写 SQL 时你要有意识地让 SELECT 的列尽量保持在索引覆盖范围内尤其是在列表页、报表统计场景。第二个思路是当需要回表的行数占比太大时优化器会干脆放弃辅助索引改走全表扫描。这个临界点通常在全表数据的 10% 到 20% 左右实际由优化器成本模型算。所以别迷信“有索引就一定快”数据分布决定了索引该不该用。还有一点是最左前缀原则。联合索引(a, b, c)实际生效的查询组合是a、a,b、a,b,c。如果你查询条件是b或者c这个索引没法调用。我见过有团队建了个(user_id, order_status, created_at)索引却经常写WHERE order_status0 AND created_at ...索引直接失效因为他们跳过了第一个字段。5.2 索引失效的 5 个高频场景逐个排查这几个场景我几乎每周都会碰到列成表你们直接查场景错误示范原因正确姿势隐式类型转换WHERE phone 13800000000phone 是 VARCHARMySQL 把字符串列转成数字比索引失效WHERE phone 13800000000前导模糊WHERE name LIKE %张三%无法确定前缀B 树没法二分查找尽量张三%或者用全文索引函数包裹列WHERE DATE(created_at) 2025-06-01对列做函数操作打断索引匹配created_at 2025-06-01 AND created_at 2025-06-02隐式字符集不一致两表关联字段一个 utf8mb4 一个 utf8关联时发生隐式转换统一字符集排序规则不满足最左前缀联合索引(a,b,c)查询只用 b 或 c跳过前导列调整索引设计或查询条件其中隐式类型转换这条我印象特别深的是一次排查慢查询一个用户表规模 300 万查询用户手机号走了足足 1.2 秒。看执行计划type ALL直接全表扫。后来发现传入的是数字类型应用层没有转字符串而字段是 VARCHARMySQL 不得不对每一行的 phone 做数值转换再比较索引自然用不上。改成字符串后耗时降到几十毫秒。函数包裹列这条也经常被忽视。很多人图省事把条件写成WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2025-06-01这个写法在数据量小的开发环境完全没问题但到了生产环境每行记录都要先做一次 DATE_FORMAT 计算索引树完全无法利用。在我的线上经验里改成范围查询后执行计划从ALL变range这是日常优化中性价比最高的一类改动。5.3 执行计划实战教学STRAIGHT_JOIN 与小结果集驱动大结果集EXPLAIN一定要成为你的肌肉记忆。我这里挑两个高频点讲。第一个是驱动表的判断。MySQL 多表 JOIN 时会选择一张表作为驱动表先查这张表再用结果去关联被驱动表。优化器一般会选“小表驱动大表”即先用数据量小、过滤结果少的那张表作为驱动这样关联次数少。但优化器偶尔也会判断失误特别是统计信息过期的时候。这时候可以用STRAIGHT_JOIN强制指定驱动顺序SELECT STRAIGHT_JOIN a.*, b.* FROM big_table a INNER JOIN small_table b ON a.small_id b.id WHERE b.status 1;这里人为指定了big_table作为驱动表——我知道这跟理论是反的但实际中确实存在小表作为被驱动表比作为驱动表更高效的场景。写这句的意思是EXPLAIN没有标准答案一切以实际数据分布为准。STRAIGHT_JOIN只是最后的手段不要一开始就想着强制指定顺序先让优化器跑一把。第二个是Using filesort和Using temporary这两个指标。看到它们说明查询里有排序或分组没有走索引。解决办法很简单让 ORDER BY 字段和 WHERE 条件字段组成联合索引并且满足最左前缀。比如SELECT * FROM user_order WHERE user_id 123 ORDER BY created_at DESC;建索引(user_id, created_at)这一条语句里 WHERE 过滤和排序都可以走索引不需要临时文件排序。这是索引设计中非常经典的一招也是日常优化最容易见效的地方。6. 事务、锁与并发控制线上 DDL 的避坑指南6.1 隔离级别和幻读一张表讲透事务隔离级别你如果不能用一句话讲明白那上线出问题的时候一定吃大亏。MySQL 默认是 REPEATABLE READ为什么还很多团队刻意改成 READ COMMITTED得从隔离级别解决的问题说起。隔离级别胜读脏数据可重复读幻读锁范围READ UNCOMMITTED是否是无锁READ COMMITTED否否是行锁REPEATABLE READ默认否是否InnoDB 用间隙锁解决行锁间隙锁SERIALIZABLE否是否全部加锁重点解释一下幻读在 REPEATABLE READ 下事务 A 两次SELECT COUNT(*)结果一致但事务 B 插入了一条新记录并提交事务 A 如果执行UPDATE或INSERT ... SELECT会发现“多出来”的行像幽灵一样。InnoDB 靠间隙锁Gap Lock来解决它会在索引记录的间隙加锁让其他事务无法在这个间隙里插入数据。但间隙锁也带来了问题——死锁概率上升且并发插入的性能下降。所以很多互联网公司会把隔离级别改成 READ COMMITTED加 binlog 的 row 模式。我比较推荐这个组合它能避免大部分由间隙锁导致的死锁代价是“不可重复读”但大多数业务场景根本不在乎两次读之间的细微差异。如果你确实需要一致性快照读那就在事务里显式加SELECT ... FOR SHARE或FOR UPDATE而不是依赖全局隔离级别。6.2 大表在线变更三条路怎么选线上最怕的事之一就是给大表加字段。直接ALTER TABLE在 MySQL 5.6 之前会锁全表后来虽然有了在线 DDL 的ALGORITHMINPLACE大表变更仍然要小心。我总结三条路。第一小表千万行以下直接跑ALTER TABLE user_order ADD COLUMN ext_json JSON NULL COMMENT 扩展字段 AFTER status, ALGORITHMINPLACE, LOCKNONE;ALGORITHMINPLACE, LOCKNONE是 5.6 支持的在线变更方式理论上允许并发 DML。但实际执行时如果改动需要重建表还是会存在短暂的锁窗口。第二大表几千万到亿级别必须用工具比如gh-ost或pt-online-schema-change。它们原理类似创建一张影子表同步数据然后在业务低峰期切换。我自己用gh-ost较多因为它不依赖触发器对主库压力更小。命令大概是gh-ost \ --host127.0.0.1 --port3306 \ --userdba --passwordxxx \ --databasemall_order --tableuser_order \ --alterADD COLUMN ext_json JSON NULL COMMENT 扩展字段 \ --execute这个过程会经历全量复制增量追平如果表有外键或者没有主键它会直接拒绝工作。所以建议你从现在就开始检查线上表没有主键的表必须想办法补上。第三如果对工具链不熟最稳妥的策略是先建新表导入数据切换应用层配置。虽然听起来“很土”但反而最可控适合超大规模表或者强监管的业务。6.3 死锁和锁等待两分钟定位根因排查死锁MySQL 提供了现成的武器SHOW ENGINE INNODB STATUS\G在输出里搜 “LATEST DETECTED DEADLOCK”里面有死锁涉及的事务、SQL、锁类型能直接看到是哪两个事务互相等待资源。锁等待超时的定位则是看SELECT * FROM sys.innodb_lock_waits;MySQL 8.0 自带 sys 库这条语句能把“谁锁住了谁”直接列出来比在 performance_schema 里翻半天高效太多。我的排查习惯是三步走先看sys.innodb_lock_waits找出持锁事务和等待事务杀掉阻塞源最久的那个事务KILL thread_id回头分析业务代码为什么在同一行数据上有这么高的并发更新。究其原因多半是应用层对同一条记录频繁 update比如扣减库存、累加计数器。解决办法是减少单行热点比如把库存拆分成多行。关于死锁还有一个容易被忽略的原则InnoDB 死锁是“事务之间互相竞争资源”导致的它本身不是 bug而是并发控制机制在正常工作。你无法完全避免死锁能做的只是把死锁发生概率降到最低并且做好重试机制。常规手法包括统一 SQL 访问顺序让所有事务都按同一种顺序比如先 user 表后 order 表操作记录尽量缩小事务的持有锁的时间不要在一个事务里做远程调用或者耗时计算。7. 几千万行大表场景下的日常运维实战7.1 分页查询深度优化OFFSET 越翻越慢怎么办列表分页是很常见的需求但“深分页”问题在大表上体现得非常明显。LIMIT 1000000, 20不是只查 20 行而是要扫掉前面 100 万行再返回这个操作随着页码增长越来越慢最终拖垮接口。我用得最多的优化方案是延迟关联延迟 joinSELECT t1.id, t1.order_no, t1.amount FROM user_order t1 INNER JOIN ( SELECT id FROM user_order WHERE order_status 0 ORDER BY created_at DESC LIMIT 1000000, 20 ) t2 ON t1.id t2.id;思路是先用覆盖索引查出主键再根据主键回表取最终要的行。因为子查询里只查 id 字段索引完全覆盖不需要读取整行数据外层再把需要的行拼回来只涉及 20 行回表代价小到忽略不计。实测在 3000 万行数据上普通深分页需要 5 秒以上这个写法通常能卡在百毫秒级。另一个我认为值得所有团队做的事是不要让用户真的翻到第 100 万行。业务侧做“只加载前 N 页”的限制或者用“下一页基于最大 ID/时间戳”的游标分页SELECT * FROM user_order WHERE created_at 2025-06-01 00:00:00 ORDER BY created_at DESC LIMIT 20;游标分页的本质是把“翻页”变成“偏移”永远只查 20 条记录性能与页数无关。这种方式特别适合信息流、订单列表这类按时间倒序的场景。7.2 大表加索引、清理数据的窗口期策略大表操作的根本原则就一条把对线上业务的影响降到最低。影响主要来自三方面锁、IO 压力、主从延迟。锁的问题前面已经说了用工具解决。IO 压力怎么控制gh-ost和pt-online-schema-change都有 throttle 参数可以把复制速度控制在一个安全的临界点以内。我通常会把最大复制线程数调低让迁移过程在老业务的空闲带宽内慢慢跑虽然慢一点但稳。主从延迟是大表操作的另一个隐形杀手。如果主库上大批量更新数据从库的 SQL 线程可能追不上主库的 binlog延迟一会从几百毫秒拉到几十秒读从库的业务就全乱了。解决思路是“小步快跑”也就是我在第 4.3 节说的分批 DELETE/UPDATE。每次几百行等几秒观察从库延迟指标下降再继续。云数据库控制台一般都有主从延迟监控自建环境就看SHOW REPLICA STATUS里的Seconds_Behind_Source。最后分享一个我自己常用的“三步验证法”每次改完大表之后必做先看EXPLAIN确认执行计划走了预期索引扫描行数从百万级降到千级看慢查询日志抓几条新 SQL 实测响应时间观察业务监控确认没有锁等待、CPU 异常、从库延迟飙升。这套流程我已经用了好几年几乎成了肌肉记忆也是我认为做数据库运维最核心的“手艺”。7.3 数据库连接池与同步工具的选择逻辑大表运维绕不开连接池和数据同步。连接池这块每门语言都有对应的成熟方案。Java 生态常见 HikariCPGo 生态常见 database/sql 自带的连接池Python 常见 SQLAlchemy 的 pool。但大家容易忽略的是“连接池大小到底设多少”。一个常见的误解是连接数越大越好实际恰恰相反。数据库能同时处理的并发连接是有限的而每条连接背后都有内存开销和事务状态。我在一个高并发项目里曾把连接池最大连接数调到 200结果数据库 CPU 直接被打满改成 50 后整体吞吐反而上升。原因在于多余连接都在排队反而增加了上下文切换开销。连接池的核心参数如下参数建议值说明maximumPoolSize核心业务 20~50更多不意味着更快minimumIdle与 maximum 相同或略低避免频繁创建连接connectionTimeout3000~5000ms超过直接快速失败idleTimeout10~30 分钟释放空闲连接maxLifetime小于数据库 wait_timeout防止被服务端断开后继续使用数据同步工具我区分两个场景。一是业务层面的同构同步比如主从复制推荐如canal这类基于 binlog 的增量解析工具它能把数据库变更实时推送到下游。二是异构数据同步比如 MySQL 到 Elasticsearch常用flinkx、DataX、或云厂商自带的 DTS。选型逻辑其实是看你对延迟的容忍度和对数据一致性的要求没有银弹。凡是涉及跨表合并、多源关联的同步我都会把主键冲突策略和幂等设计放在第一位因为同步工具重播数据时如果没有幂等机制很容易造成线上数据错乱。8. 常见问题排查速查表与实操心得我整理了近一年工作中高频出现的问题按现象、原因、快速处理方式做成一张表。这张表在平时排查问题时非常好用你也可以直接抄走。现象可能原因快速排查与处理连接数打满应用报 Too many connections慢查询积压 / 连接池过大SHOW PROCESSLIST;把长查询杀了调整连接池中文乱码库/表字符集不是 utf8mb4查库表字符集统一 utf8mb4必要时转码数据某条查询突然变慢统计信息过期优化器选错索引ANALYZE TABLE 表名;重新收集统计信息插入数据时死锁多事务对同一行反向更新SHOW ENGINE INNODB STATUS\G定位事务统一访问顺序主从延迟很大大事务或者备份任务占 IO分析 binlog 里的大事务拆分、错峰明明有索引执行计划显示全表扫隐式转换 / 函数包裹列 / 区分度低按 5.2 节逐一排查分页越翻越慢深分页导致扫描大量行延迟关联或游标分页改造删数据特别慢无索引条件加锁全表 / undo 膨胀分批删除条件走索引再讲两个不算常见但一旦碰到就很要命的问题。一个是 MySQL 8.0 下 SSL 连接报错。排查方向是确认客户端和服务端的 SSL 协议版本是否匹配经常是 JDBC 驱动版本过旧导致的。在配置里加useSSLfalse只能治标正确做法是升级驱动并正确配置 SSL 证书校验。另一个是自增 ID 耗尽。当你建表用了INT UNSIGNED最大范围约 42 亿对多数业务够用可一旦碰到秒级插入量大的日志表ID 耗尽只是时间问题。耗尽后插入会报主键冲突应用端表现为插入失败且看不出明显日志。预防办法就是建表时对增长极快的表直接用BIGINT并做好容量监控。最后分享一段我自己的心法数据库操作这门手艺看起来静如处子用起来动如脱兔。所谓“高手”不是会把上百行 SQL 背下来而是在建表之前就想过表会怎么长大、查询会怎么访问、数据会怎么删除、索引会不会被隐式转换拦截。很多线上事故本来就是可以提前避免的只要你在设计阶段多花那十分钟后面就能少熬好几个通宵。