1. 装得上还得连得上安装选型、初始密码与 ERROR 2002 排查绝大多数人卡在 MySQL 上的第一道坎其实跟 SQL 本身没什么关系。项目跑起来、表建好了、代码里jdbc也写了结果mysql -uroot -p回车屏幕上给你甩一句ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock。那一刻的心态是很崩的因为你甚至还没开始写真正的业务查询。我见过太多人在这一步反复重装数据库装了三遍还是同样的错其实是没搞清楚装和连是两件独立的事。这一节先把环境这条链路理顺版本怎么挑、三种安装方式各有什么代价、初始密码去哪儿找、连不上时按什么顺序排查。这些东西看起来是体力活但它们是后面所有增删改查和高效查询的地基地基没打牢后面优化做得再漂亮也白搭。1.1 5.7 还是 8.0先搞清楚差异再动手新手最容易犯的错是跟着一个三年前的教程装 5.7。MySQL 5.7 已经停止官方维护新项目基本没有理由再上它。但我也不建议无脑上最新版因为客户端工具、驱动、框架适配需要时间。对比项5.78.0默认字符集latin1历史遗留utf8mb4默认认证插件mysql_native_passwordcaching_sha2_password窗口函数不支持支持CTE 公共表表达式不支持支持查询缓存有鸡肋已移除索引隐藏不支持支持 INVISIBLE INDEX官方维护状态已停止持续维护对新手来说最实际的差异是默认认证插件。8.0 用caching_sha2_password一些老驱动、老客户端连的时候会报认证失败需要额外配置allowPublicKeyRetrieval或者把用户改成mysql_native_password。所以如果你用的是比较老的 Java 项目第一次连 8.0 报错大概率不是密码错了是认证插件对不上。我的建议很直接新项目统一用 8.0 当前稳定小版本学习、练手也用 8.0。只有当你维护的是遗留系统、框架版本很老时才继续用 5.7并且明确它只是维持运行不是继续发展。1.2 三条安装路径的取舍安装方式没有绝对的好坏只有适配场景。下面这张表是我实际用下来觉得最靠谱的对照方式适合场景优点坑点包管理器yum/apt单机学习、小型服务快一条命令搞定版本受仓库限制配置文件位置分散Docker开发环境、快速搭建环境隔离删了重来成本低数据卷不映射会丢数据离线 rpm/tar.gz内网服务器、国产系统可控不依赖外网依赖顺序、权限、systemd 单元要自己处理包管理器的坑在于配置文件分散。CentOS 系默认读/etc/my.cnfDebian 系可能读/etc/mysql/my.cnf再 include/etc/mysql/mysql.conf.d/mysqld.cnf。很多人改了配置不生效就是因为改错了文件——同一个目录下可能有mysqld.cnf和mysql.cnf两个文件前者给服务端后者给客户端。Docker 方式最需要注意的是数据卷。一定要把数据目录挂出来docker run -d --name mysql8 \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDYourStrongPass \ -v /data/mysql8:/var/lib/mysql \ -v /data/mysql8/conf:/etc/mysql/conf.d \ mysql:8.0没有-v /data/mysql8:/var/lib/mysql这一行容器一删数据全没。这个坑我踩过重装之后看着空荡荡的数据库只能苦笑。离线安装常见于内网和国产系统比如银河麒麟、统信。rpm 包要按依赖顺序来一般先装mysql-community-common再装libs然后client最后server。顺序错了会一直报依赖缺失。tar.gz 方式则要自己建mysql用户、自己初始化、自己写 systemd 单元文件步骤多但可控性最强。1.3 初始密码藏在哪里这是个高频问题不同安装方式答案完全不同。CentOS/RHEL 用 rpm 安装时root 的临时密码写在错误日志里grep temporary password /var/log/mysqld.logDebian/Ubuntu 用 apt 安装时root 默认走auth_socket插件不让你用密码登录。正确姿势是切到 root 用 socket 进去然后改sudo mysql ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY YourStrongPass; FLUSH PRIVILEGES;Docker 启动时如果没指定MYSQL_ROOT_PASSWORD随机密码会打到日志里docker logs mysql8 21 | grep -i generated root password注意如果你在 1Panel 这类面板里遇到root 没权限九成不是权限丢了而是你连的是localhost走 socket但账号只授权了%或者反过来。先用mysql -uroot -h127.0.0.1 -P3306 -p和mysql -uroot -p分别试一次能快速判断是 socket 的问题还是授权主机的问题。1.4 ERROR 2002 的完整排查顺序这条报错的信息量其实很大它明确告诉你客户端尝试通过 socket 文件连接但那个文件不存在或者服务没起来。按下面顺序走基本一次能定位先确认服务在不在systemctl status mysqld或ps -ef | grep mysqld。服务没起什么都别谈去看错误日志。确认 socket 文件路径mysql --help | grep socket看客户端默认路径再对比my.cnf里[mysqld]段的socket配置。两边不一致就是这个问题。手动指定 socket 试一次mysql -uroot -p -S /var/lib/mysql/mysql.sock。能进去就说明客户端路径配置错了在[client]段补一行socket即可。检查数据目录权限ls -ld /var/lib/mysql属主必须是mysql。改过目录位置之后权限没跟着改是另一个高频原因。K8s 环境比如 Kubesphere 里部署的 MySQL逻辑类似只不过你要先kubectl exec进 Pod再在容器内做上面的排查。临时密码通常存在 Secret 里用kubectl get secret mysql-secret -o jsonpath{.data.mysql-root-password}取出来 base64 解码。2. 增删改查背后的数据流转为什么会写 SQL不等于会用 MySQL我面试过不少人SQL 写得飞快INSERT INTO ... VALUES (...)、SELECT ... WHERE ... ORDER BY ...张口就来但问他这条 INSERT 提交之后数据到底在哪一刻落到了磁盘上就答不上来了。这不是刁难是因为不理解这个链路你就无法解释为什么有时候批量插入快、有时候慢为什么开了事务和没开事务性能差好几倍为什么UPDATE不带索引条件会把整张表锁住。增删改查的语法只是表层真正决定你天花板的是下面这层认识。2.1 一条 INSERT 在落盘之前经历了什么InnoDB 的写路径可以粗略理解成先记账再归档。打个比方你在一家公司做报销不会每次收到发票就跑去财务室入总账而是先在便签上记一笔月底再统一整理进账本。redo log 就是那张便签数据页才是账本。具体流程是这样的数据先写入Buffer Pool内存中的数据页缓存此时内存里的页变成脏页。同时把这次修改以物理日志的形式写进redo log buffer再按innodb_flush_log_at_trx_commit的策略刷到磁盘。提交时还要写binlog用于主从复制和数据恢复。为了两阶段提交的原子性redo log 先进入 prepare 状态binlog 写完后再把 redo 置为 commit。innodb_flush_log_at_trx_commit这个参数是新手最该知道的一个取舍点取值行为数据安全性能1每次提交都刷盘最高不丢数据最慢2提交写 OS 缓存每秒刷盘断电可能丢 1 秒折中0每秒刷一次进程崩溃可能丢 1 秒最快生产环境老老实实用 1并发压力大到扛不住再考虑 2但你要清楚自己在赌什么。至于sync_binlog同理设成 1 是最安全的。提示批量插入时不要一条一条 commit。把 1000 条包在一个事务里或者用INSERT INTO t (a,b) VALUES (1,2),(3,4),(5,6)...的多值语法性能差异可以是几十倍。原因是每条 commit 都对应一次日志刷盘次数少了开销自然下来了。2.2 一条 SELECT 从敲下回车到拿到结果服务端处理查询是分层执行的理解这个分层你就能解释很多玄学现象。连接器负责握手、认证、分配连接资源。这就是为什么连接池里的空闲连接会占用服务端线程也是wait_timeout存在的意义。分析器做词法分析和语法分析你的 SQL 拼错了列名报错就是这里发出的。优化器决定用哪个索引、多表 join 的顺序怎么排。这里会用到统计信息所以统计信息不准执行计划就会跑偏。执行器按优化器的计划调用存储引擎接口逐行取数据做过滤后返回。明确了这条链路两件事就说得通了。第一同一条 SQL 执行快慢不同往往是优化器选了不同的索引因为统计信息变了或者数据分布变了。第二SELECT 也会产生锁在可重复读隔离级别下普通的SELECT是快照读不加锁但SELECT ... FOR UPDATE会加行锁这是两码事。我在排查线上问题时习惯先看SHOW PROCESSLIST确认那些Sleep状态的长连接是不是把连接数占满了。很多数据库连不上的告警根因其实是应用侧连接没归还。2.3 UPDATE 语句里最容易写错的几个地方UPDATE是事故高发区。我列几个实际见过的问题。第一忘写 WHERE。这个不用多说但确实天天有人干。建议开两个开关客户端设sql_safe_updates1或者直接给生产账号只授SELECT改数据走工单。开了 safe updates 之后不带 WHERE 或者 WHERE 里没用上索引的 UPDATE 会被直接拦下来。第二WHERE 条件里发生隐式类型转换。这个非常隐蔽UPDATE users SET status1 WHERE phone13800000000和WHERE phone13800000000看着差不多但后者如果phone是 varchar就会触发全表扫描然后是整表行锁。后面第 5 节会详细讲。第三大更新不分批。一次性更新 200 万行的 UPDATE会撑大事务、产生大量 undo、还可能触发主从延迟。稳妥的做法是按主键分批UPDATE orders SET is_settled 1 WHERE id 0 AND id 100000 AND is_settled 0 LIMIT 1000;在外层循环反复执行直到影响行数为 0。每次 1000 行事务小、锁持有时间短、主从延迟可控。第四并发场景下的丢失更新。两个请求同时读到stock10各自减 1 写回最后变成 9 而不是 8。解决办法是让数据库来算UPDATE stock SET num num - 1 WHERE id 1 AND num 0或者用悲观锁SELECT ... FOR UPDATE或者加version字段做乐观锁。2.4 DELETE、TRUNCATE、DROP 到底差在哪这三个语句都能清空数据但机制完全不同用错了后果差很多。语句类型能否回滚自增计数器触发触发器空间释放DELETEDML能不重置会不立即释放TRUNCATEDDL不能重置为 1不会立即释放DROPDDL不能-不会表一起没了删除大数据量时如果一个DELETE太慢或者把 undo 撑爆可以用重建表的思路建一张新表把保留的数据INSERT ... SELECT过去然后 rename 换名。这在线上做数据清理时比直接 DELETE 稳妥得多因为新表写入走的是顺序路径锁的粒度也小。3. 索引与最左前缀把全表扫描挡在门外索引这块内容网上教程多如牛毛但大多数人看完之后还是不知道什么时候该建、什么时候建了没用。核心问题在于很多讲解停留在索引像书的目录而没说清楚为什么它是 B 树而不是二叉树也没说清楚为什么(a,b,c)这个联合索引查 b 用不上。这一节我尽量把这两件事讲透。3.1 用图书馆的检索卡片理解 B 树和聚簇索引图书馆有几百万本书你怎么在几十秒内找到某一本靠的不是把书架全扫一遍而是靠检索卡片柜。卡片按书名字典序排列每张卡片告诉你书在哪个区、哪一排、哪一格。这个卡片柜就是索引。InnoDB 的索引结构是B 树特点是非叶子节点只存键值不存数据所有数据都在叶子节点而且叶子节点之间用链表串起来。这样做的好处有两个一是树的层数低三千万行数据一般也就三到四层查一次最多三四次磁盘 IO二是支持范围查询找到起点后顺着链表往后扫即可。再说聚簇索引。InnoDB 的主键索引就是聚簇索引叶子节点直接存整行数据。所以按主键查是最快的一次就能拿到全部字段。而你在其他列上建的索引叫二级索引叶子里存的是该列的值 主键值。这就引出一个关键概念——回表。你按name查到一行二级索引叶子里只有name和id你需要id再去主键索引里捞一次完整数据这就是回表等于查了两棵树。覆盖索引就是用来消除回表的。如果你的查询只需要id和name而索引正好包含这两列引擎在二级索引里就能把数据凑齐不需要回表-- 假设有联合索引 idx_name_age(name, age) -- 下面这条走覆盖索引Extra 里会显示 Using index SELECT name, age FROM users WHERE name zhangsan;这就是为什么我常建议别写SELECT *。你只要三列却把十几列都查出来回表次数蹭蹭涨覆盖索引也没法用。3.2 联合索引与最左前缀为什么查 b 用不上 (a,b,c)联合索引idx_abc(a, b, c)的排序规则本质上是先按 a 排a 相同的按 b 排b 相同的再按 c 排。就像字典里先按拼音首字母排首字母相同的按第二个字母排。所以WHERE a1能用上WHERE a1 AND b2能用上WHERE a1 AND b2 AND c3全部用上WHERE b2用不上因为 b 的排序在全局是无序的WHERE a1 AND c3只有 a 用得上c 用不上8.0 的索引下推能部分缓解但作用有限范围查询会截断后面的列。WHERE a1 AND b2 AND c3a 用得上b 用得上范围但 c 用不上因为 b 已经是一个范围范围内 c 相对无序。理解了这条建索引就有了方向把等值查询的列放前面范围查询的列放后面排序列的用法要单独看。如果你既要用WHERE a? ORDER BY b那(a,b)这个顺序通常是对的如果是WHERE a? ORDER BY b那索引基本帮不上排序会走 filesort。3.3 索引失效的清单照着对一遍索引失效不是一个错误它是优化器的理性选择——当它判断走索引不如全表扫快时就放弃了索引。但有些失效是我们自己写出来的完全可以避免。写法是否走索引原因WHERE id 5走正常等值WHERE id 1 6不走列上做了运算WHERE DATE(create_time) 2024-01-01不走列上套了函数WHERE create_time 2024-01-01 AND create_time 2024-01-02走改成范围写法WHERE name LIKE abc%走前缀匹配WHERE name LIKE %abc不走前导模糊WHERE phone 13800000000列是 varchar不走隐式类型转换WHERE a 1 OR b 2可能不走OR 两侧索引不同会退化成全表WHERE status ! 0通常不走区分度太低优化器判断全表更快经验DATE(create_time) 2024-01-01这个写法我几乎每次代码评审都能看到。改成范围写法之后很多慢查询当场就消失了不用加任何索引。这是性价比最高的一类优化。3.4 索引不是越多越好建索引的代价是每次写操作都要同步维护所有相关索引。一张表上挂七八个索引插入性能会被拖垮磁盘占用也上去了。我的判断标准是单表索引控制在 5 个以内联合索引尽量覆盖多个查询场景优先考虑把常用查询合并到同一个联合索引上。建之前先用EXPLAIN验证有没有真的用上建之后观察一段时间不用的索引用ALTER TABLE ... ALTER INDEX ... INVISIBLE先隐藏起来确认没人依赖再删。这个先隐藏再删的流程能避免删错索引导致线上故障。4. 用 EXPLAIN 和慢查询日志做一次真实的查询优化前面讲的都是原理这一节讲怎么落地。我把它拆成三步先看懂执行计划再学会抓出慢 SQL最后完整走一遍优化过程。4.1 EXPLAIN 的每一列在说什么在 SQL 前面加一个EXPLAIN就能看到优化器的执行计划。重点看这几列type访问类型性能从好到坏依次是system const eq_ref ref range index ALL。看到ALL基本就是全表扫描要警惕看到index是全索引扫描比全表好一点但也不理想。key实际用到的索引。如果是NULL说明没走索引。rows预估扫描行数。这个数字不准但数量级有参考价值几百万行和几百行是两种性质。filtered过滤后剩余的百分比越低说明扫描浪费越大。Extra最关键的一列。Using filesort表示要额外排序Using temporary表示用了临时表Using index表示覆盖索引是好消息Using index condition表示用了索引下推。一个典型的坏计划长这样------------------------------------------------------------------------------ | id | type | key | possible_keys | rows | Extra | ------------------------------------------------------------------------------ | 1 | ALL | NULL | idx_status | 892134 | Using where; Using filesort | ------------------------------------------------------------------------------typeALL、keyNULL、rows接近九十万、还要 filesort四条坏消息凑齐了。这种查询就是那种测试环境几百行很快上线一千万行直接超时的典型。4.2 打开慢查询日志让问题自己浮出来靠用户投诉才发现慢查询太被动了。正确做法是把慢查询日志打开让数据库主动记录。[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1 min_examined_row_limit 100几个参数的含义long_query_time 1表示超过 1 秒的记录log_queries_not_using_indexes会记录没用索引的查询但线上高峰期要小心这个开关会产生大量日志建议只在排查期开min_examined_row_limit用来过滤掉那些扫描行数很少、只是偶尔慢的噪音。日志拿到手之后别一行一行看用工具聚合# 按出现次数排序看最频繁的慢查询 mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # 按总耗时排序看最拖累整体性能的 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log更专业的做法是上pt-query-digest它能给出这条 SQL 占总响应时间的百分比帮你判断哪一条最值得优化。很多时候 Top 1 的那条 SQL 优化掉整体响应时间能降三成。4.3 一次深分页查询的完整改造讲一个我实际遇到过的案例。订单列表页用户翻到很后面接口直接超时。原始 SQLSELECT * FROM orders WHERE user_id 10086 ORDER BY id DESC LIMIT 1000000, 20;问题在LIMIT 1000000, 20。MySQL 的处理方式是先按顺序找到满足条件的前 1000020 行然后把前 1000000 行丢掉只返回最后 20 行。等于白扫了一百万行。而且SELECT *还要回表雪上加霜。第一步优化改成延迟关联。先在覆盖索引上把主键捞出来再用主键回表取数据SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE user_id 10086 ORDER BY id DESC LIMIT 1000000, 20 ) t ON o.id t.id;子查询走的是idx_user_id(user_id)上的覆盖索引叶子节点里本来就有主键不需要回表扫描速度快很多。拿到 20 个 id 之后再回表总共只回表 20 次。第二步优化如果业务允许改成游标分页。让前端传上一次返回的最后一条 id而不是页码SELECT * FROM orders WHERE user_id 10086 AND id 1234567 ORDER BY id DESC LIMIT 20;这样每次都是WHERE id ?的范围查询直接走主键无论翻到第几页耗时都恒定。代价是不能再跳到第 500 页但对于信息流、订单列表这类场景用户本来也不关心具体页码改成加载更多体验反而更好。第三步给user_id建索引如果还没建并确认id是主键。这两步做完同样的查询从 3 秒降到 30 毫秒。注意ORDER BY的方向和索引顺序要匹配。ORDER BY id DESC在主键索引上是天然的逆序扫描效率很高但如果你写ORDER BY create_time DESC, id ASC这种混合方向在旧版本里可能导致无法使用索引排序需要改成同方向。8.0 支持降序索引这个限制有所缓解。5. 字段类型与隐式转换那些悄悄让索引失效的小动作建表的时候随手写字段类型是很多人忽略的一步。反正存进去能拿出来就行这种想法在数据量小的时候没问题几千行怎么查都快。等到百万级你当初省下的那两分钟设计时间会用几十个小时的排查时间还回来。5.1 用字符串存日期省事一时坑很久create_time存成varchar(20)值是2024-01-01 10:00:00看着挺整齐。问题出在两个方面。第一无法高效做范围查询。你要查某个月的订单得写WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00。这个字符串比较是按字典序走的对于固定格式的日期字符串恰好也能用但前提是格式必须严格统一。一旦有人存了2024-1-1这种不补零的格式比较结果就乱了。第二无法使用日期函数。你要按周统计、按月分组就得先转换SELECT DATE_FORMAT(create_time, %Y-%m) AS m, COUNT(*) FROM orders GROUP BY m;但如果create_time本身就是DATETIME类型可以直接DATE_FORMAT甚至可以用上一些优化手段。更关键的是字符串比较没法利用 MySQL 内部的日期优化。我的建议非常明确时间字段一律用DATETIME或TIMESTAMP。二者的区别是TIMESTAMP只到 2038 年且有自动时区转换DATETIME范围更大但不做时区转换。业务时间用DATETIME更省心需要和 UTC 打交道的场景用TIMESTAMP。至于字符串转日期确实有需求的时候用STR_TO_DATESELECT STR_TO_DATE(2024/01/01 10:30, %Y/%m/%d %H:%i);但请注意这个函数如果放在WHERE条件里套在列上索引必然失效。正确做法是在应用层或者用DATE常量比较别让函数作用在列上。5.2 int(5) 不是长度限制别再误解了int(5)这个写法在很多人眼里是这个字段最多存 5 位数。完全不是。括号里的数字是显示宽度只在ZEROFILL时起作用比如int(5) zerofill存 123 显示成00123。它既不限制取值范围也不影响存储空间。INT固定占 4 字节范围是 -2147483648 到 2147483647。要存手机号不能用 INT位数不够且会丢前导零要存更长的用BIGINT。真正需要关注的是有符号还是无符号。INT UNSIGNED的范围是 0 到 4294967295适合做自增主键、状态值这类不可能为负的字段。但要注意UNSIGNED之间相减如果结果为负会报错或者溢出写a - b的时候要留神。5.3 隐式转换索引失效的头号嫌疑人这是我要重点讲的一个坑因为它太隐蔽了。假设phone是varchar(20)你写SELECT * FROM users WHERE phone 13800000000;数值和字符串比较时MySQL 会把字符串转成数字再比。这个转换是作用在phone列上的每一行都要转一次索引直接失效全表扫描。反过来user_id是INT你写成SELECT * FROM users WHERE user_id 10086;这个是没问题的字符串转数字常量侧转换不影响索引。所以规律是让列保持原类型把转换放在常量侧。实操心得我在排查慢查询时第一件事就是拿EXPLAIN看key列。如果明明是等值查询、字段上也有索引却显示keyNULL八成就是隐式转换。这时候把参数值的引号检查一遍问题往往就解决了。这个检查不需要改代码逻辑成本极低。5.4 默认值、NOT NULL 和 NULL 的选择DEFAULT看似是个小细节其实关系到数据一致性。数值型字段给个默认 0通常比NULL好因为NULL参与算术运算结果永远是NULL聚合函数也会跳过它。统计库存总量的时候如果有NULL值SUM的结果可能让你意外。字符串字段给空串还是 NULL取决于业务语义。如果未填写和填了空字符串是两种状态那就用NULL如果区分不了直接NOT NULL DEFAULT 更省事。时间字段的默认值我一般这么设计create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP这样插入时不用手动传时间更新时自动维护应用层代码能少写不少。要注意的是ON UPDATE CURRENT_TIMESTAMP会让这一行到底有没有被改过变得不可判断如果你需要精确的修改审计还是得用触发器或者应用层显式赋值。还有一个容易忽略的点尽量避免在唯一索引的列上使用 NULL。因为 SQL 标准里NULL ! NULL多个 NULL 值在唯一索引里是不冲突的会导致你以为建了唯一约束实际上没起作用。6. 锁表排查与事务边界半夜被叫起来的那个问题线上最怕的告警不是慢查询是接口全部超时。很多时候根因就是一把锁——某个事务没提交把整张表或者几行数据锁住了后面所有请求全排队。这种问题在白天流量大的时候特别容易爆发而且一旦爆发就是全局性的。6.1 先找到谁在锁谁在等遇到疑似锁等待先看当前的会话状态SHOW PROCESSLIST;或者更详细一点SELECT * FROM information_schema.processlist WHERE command ! Sleep ORDER BY time DESC;找到那些State里写着Waiting for table metadata lock或者Waiting for row lock的会话。接下来看正在运行的事务SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx ORDER BY trx_started ASC;trx_started越早说明这个事务开得越久。一个开了十分钟还没提交的事务基本就是元凶。8.0 里查锁信息用performance_schema.data_locks和data_lock_waits也可以用sys.innodb_lock_waits这个视图它直接告诉你是哪个会话阻塞了哪个会话非常直观。SELECT * FROM sys.innodb_lock_waits\G找到阻塞源之后KILL thread_id掉那个会话业务能立刻恢复。但记住KILL 只是止血不是治病根因还是代码里的事务没控制好。6.2 行锁、间隙锁与那个著名的死锁InnoDB 默认是行级锁但行锁是加在索引上的。如果你的WHERE条件没走索引InnoDB 就只能锁住整个索引的所有记录效果等同于锁表。这就是为什么加了索引就不锁表了这个说法成立。间隙锁是另一个需要知道的点。在可重复读隔离级别下为了保证同一事务内两次范围查询结果一致InnoDB 会锁住范围之间的空隙防止别人往里插数据。这在插入密集的场景下容易造成锁等待。死锁是两个事务互相等待对方持有的锁谁都不肯放手。InnoDB 有死锁检测机制会主动回滚代价较小的那个事务并在错误日志里打印死锁信息LATEST DETECTED DEADLOCK *** (1) TRANSACTION: TRANSACTION 12345, ACTIVE 5 sec ... *** (1) HOLDS THE LOCK(S): RECORD LOCKS ... index idx_a ... *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS ...看死锁日志其实有固定套路找到两个事务各自持有的锁和等待的锁然后看它们加锁的顺序。绝大多数死锁都是因为不同事务加锁顺序不一致造成的。解决办法是统一加锁顺序——比如两个事务都要改 A 表和 B 表那就规定先改 A 再改 B别一个先 A 一个先 B。6.3 事务边界应该划在哪里我见过最典型的问题是把事务开在 Controller 层里面还调了个远程接口。远程调用超时 30 秒事务就挂了 30 秒锁也就握了 30 秒。三条原则我一直提醒团队的同事事务里只放数据库操作。RPC 调用、文件读写、消息发送全部放到事务外面用本地消息表或者事务提交后的回调处理。事务尽量短。单事务操作行数控制在几千以内超大批量操作拆成多批。不要在事务里等待用户输入。这个听起来离谱但真有人这么干过用一个事务包住创建订单 → 等用户支付 → 更新状态直接把表锁到天荒地老。提示开发环境可以用SET SESSION innodb_lock_wait_timeout 5把锁等待超时调短一点这样死锁和锁等待能更快暴露出来而不是一直卡着。生产环境这个值要按业务容忍度设置默认 50 秒通常偏长。7. 应用层连接JDBC 参数、连接池与跨库数据同步前面讲的都是数据库内部的事这一节落到应用侧。因为很多数据库问题其实是连接配置问题——参数没写对、连接池设错、网络断了没重连现象看起来都像数据库挂了。7.1 JDBC URL 里那几个参数的真正含义一个完整的连接串大概长这样jdbc:mysql://127.0.0.1:3306/demo?useUnicodetruecharacterEncodingutf8mb4serverTimezoneAsia/ShanghaiuseSSLfalseallowPublicKeyRetrievaltruerewriteBatchedStatementstrue逐条解释characterEncodingutf8mb4客户端和服务端协商字符集避免中文乱码。要写utf8mb4不要写utf8后者在 MySQL 里是阉割版存不了 emoji。serverTimezoneAsia/Shanghai老版本驱动不加这个会报时区错误导致时间差 8 小时。8.0 驱动改善了但显式写上没有坏处。useSSLfalse关闭 SSL。这个参数在 8.0 驱动里已经被sslMode取代了新的写法是sslModeDISABLED。如果你用的是 MySQL Connector/J 8.0.13 之后的版本useSSL会告警甚至被忽略。allowPublicKeyRetrievaltrue这个参数就是给caching_sha2_password准备的。不开的话第一次连接会因为拿不到公钥而报认证失败。rewriteBatchedStatementstrue批量插入的加速开关。不开的话addBatch和executeBatch是一条条发过去的开了之后驱动会把多条合成一条INSERT ... VALUES (...),(...),(...)性能提升非常明显。关于 SSL 配置简单说清楚三种状态参数写法含义适用场景sslModeDISABLED完全不用 SSL内网、同机sslModePREFERRED能用就用默认值折中sslModeREQUIRED必须用跨公网、合规要求配置不匹配时最常见的报错是SSL connection error或者PKIX path building failed前者是客户端要 SSL 服务端没有后者是证书链不被信任。内网环境下直接用DISABLED最省事跨网络务必用REQUIRED并导入正确的 CA 证书。7.2 连接池大小怎么定maxLifetime 为什么要比 wait_timeout 小连接池是个很容易被凭感觉调参的地方。maxPoolSize设成 100、200 的情况很常见理由是并发高嘛。但连接数不是越大越好服务端每个连接都是一个线程上下文切换的开销会把收益吃回去。有个经验公式可以用作起点连接数 CPU核数 * 2 磁盘数4 核机器配 SSD大概就是 8 到 10 个连接。听起来很少对不对但在压测中你会发现超过这个数量后吞吐量就不再增长了反而延迟上升。连接池的正确用法是让它成为瓶颈提示——当请求排队等连接时说明你的瓶颈在数据库加连接解决不了问题该优化 SQL 或者加缓存。maxLifetime要设得比 MySQL 的wait_timeout小。原因很简单服务端会在连接空闲超过wait_timeout后主动断开如果连接池不知道还以为这个连接可用借出去用的时候就会报通信异常。所以连接池的maxLifetime要设成wait_timeout减几秒留出提前淘汰的余量。connectionTimeout是获取连接的最长等待时间设太短会在高峰期大量报超时设太长会让请求堆积。3 到 5 秒是我常用的区间。Java 项目里HikariCP 是首选配置项少、性能好。Spring Boot 2.x 之后默认就是它spring.datasource.hikari.maximum-pool-size直接配即可。7.3 把远程库的表同步到本地几种可行方案这个需求很常见测试环境在远端你想把某张表的数据拉一份到本地做调试。方案有几个按数据量和实时性要求选。方案一mysqldump 导出再导入。适合几十万行以内、偶尔同步一次的场景。# 导出远程库的某张表 mysqldump -h remote_host -uuser -p --single-transaction \ --set-gtid-purgedOFF db_name orders orders.sql # 导入本地 mysql -hlocalhost -uroot -p db_name orders.sql--single-transaction保证导出期间不加锁对线上影响小。--set-gtid-purgedOFF在本地没有开启 GTID 时避免报错。方案二SELECT INTO OUTFILE LOAD DATA。适合几百万行的批量搬运速度比 INSERT 快得多。-- 远程执行 SELECT * FROM orders INTO OUTFILE /tmp/orders.txt FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n; -- 拷回本地后导入 LOAD DATA INFILE /tmp/orders.txt INTO TABLE orders FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n;注意secure_file_priv参数会限制导出路径通常是/var/lib/mysql-files/要按实际配置来。方案三主从复制。需要持续同步、对实时性有要求的场景用这个。核心是配置server-id、开log-bin、记录binlog位点然后在从库上CHANGE MASTER TO指向主库。整个链路是 binlog → relay log → 从库回放。如果只是临时同步一张表方案一最省事如果要长期同步整个库方案三如果数据量特别大又不想影响主库可以考虑用中间件工具做增量抽取。方案四定时增量同步。用update_time字段做水位线SELECT * FROM orders WHERE update_time ? AND update_time ?;每次记录上次同步到的最大时间下次从这里继续。这个方案简单可靠但前提是表里得有update_time并且它真的被维护着。我见过因为update_time不更新导致同步漏数据的情况用之前一定要确认业务代码确实在维护这个字段。实操心得从远程同步数据到本地时先把索引和主键约束去掉导完数据再重建。这样导入速度能快好几倍。另外字符集一定要对齐远端utf8mb4本地utf8导入时会遇到各种乱码和截断排查起来很烦。7.4 存储过程和常见的那几类看起来是面试题的问题存储过程在业务开发里用得越来越少原因是可维护性差、调试困难、版本管理麻烦。但有两类场景还是值得用大批量数据的分批处理和定时统计任务。一个声明式存储过程的骨架DELIMITER $$ CREATE PROCEDURE batch_update_status(IN batch_size INT) BEGIN DECLARE done INT DEFAULT 0; DECLARE v_id BIGINT; DECLARE cur CURSOR FOR SELECT id FROM orders WHERE status 0 AND update_time DATE_SUB(NOW(), INTERVAL 7 DAY) LIMIT 1000; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT error occurred AS msg; END; OPEN cur; read_loop: LOOP FETCH cur INTO v_id; IF done 1 THEN LEAVE read_loop; END IF; UPDATE orders SET status 9 WHERE id v_id; END LOOP; CLOSE cur; END$$ DELIMITER ;这里面值得说的是异常处理。DECLARE EXIT HANDLER FOR SQLEXCEPTION能在出错时回滚并给出提示不写这个的话出错后游标可能不关闭事务也可能悬着。我见过因为存储过程出错没处理好导致锁一直不释放的情况排查了半天才发现是这里。至于那些常被问到的题型——主从延迟怎么排查、分库分表怎么选分片键、慢查询怎么优化——背后考的都是同一件事你有没有真的在生产环境里解决过问题。答案不重要思路和踩过的坑才重要。8. 最后说几句关于从入门到高效这件事写到这里从装环境、写增删改查、建索引、优化查询、排查锁、配连接池这条链路基本走完了。我个人在实际操作中的体会是MySQL 这东西的成长曲线不是线性的。前三个月你在学语法觉得自己会了半年后你遇到第一个慢查询发现以前的写法全是隐患再往后你会开始关心执行计划、隔离级别、锁的粒度这时候才算真正入门。真正让人进阶的往往不是新知识而是一次次被线上问题教育——每一次半夜被叫起来排查锁表你对事务边界的理解就深一分。如果你现在还在用SELECT *、还在WHERE条件里套函数、还在用字符串存日期那今天就可以挑一条改掉。不用全改一次改一个习惯。等哪天你的接口从 3 秒降到 30 毫秒回头看那些改动都只是几个字符的事。最后再分享一个小技巧给自己负责的每个库开一个慢查询日志每周花十分钟看一眼 Top 10。绝大多数生产事故在爆发之前都已经在慢查询日志里出现过很多次了。提前处理掉远比事后救火轻松。