企业官网建设流程全解析

1. 装得上还得连得上：安装选型、初始密码与 ERROR 2002 排查

绝大多数人卡在 MySQL 上的第一道坎，其实跟 SQL 本身没什么关系。项目跑起来、表建好了、代码里jdbc也写了，结果mysql -uroot -p回车，屏幕上给你甩一句ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'。那一刻的心态是很崩的，因为你甚至还没开始写真正的业务查询。我见过太多人在这一步反复重装数据库，装了三遍还是同样的错，其实是没搞清楚"装"和"连"是两件独立的事。

这一节先把环境这条链路理顺：版本怎么挑、三种安装方式各有什么代价、初始密码去哪儿找、连不上时按什么顺序排查。这些东西看起来是"体力活"，但它们是后面所有增删改查和高效查询的地基，地基没打牢，后面优化做得再漂亮也白搭。

1.1 5.7 还是 8.0：先搞清楚差异再动手

新手最容易犯的错是"跟着一个三年前的教程装 5.7"。MySQL 5.7 已经停止官方维护，新项目基本没有理由再上它。但我也不建议无脑上最新版，因为客户端工具、驱动、框架适配需要时间。

对新手来说，最实际的差异是默认认证插件。8.0 用caching_sha2_password，一些老驱动、老客户端连的时候会报认证失败，需要额外配置allowPublicKeyRetrieval或者把用户改成mysql_native_password。所以如果你用的是比较老的 Java 项目，第一次连 8.0 报错，大概率不是密码错了，是认证插件对不上。

我的建议很直接：新项目统一用 8.0 当前稳定小版本，学习、练手也用 8.0。只有当你维护的是遗留系统、框架版本很老时，才继续用 5.7，并且明确它只是"维持运行"，不是"继续发展"。

1.2 三条安装路径的取舍

安装方式没有绝对的好坏，只有适配场景。下面这张表是我实际用下来觉得最靠谱的对照：

包管理器的坑在于配置文件分散。CentOS 系默认读/etc/my.cnf，Debian 系可能读/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_PASSWORD=YourStrongPass \ -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.log

Debian/Ubuntu 用 apt 安装时，root 默认走auth_socket插件，不让你用密码登录。正确姿势是切到 root 用 socket 进去，然后改：

sudo mysql ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourStrongPass'; FLUSH PRIVILEGES;

Docker 启动时如果没指定MYSQL_ROOT_PASSWORD，随机密码会打到日志里：

docker logs mysql8 2>&1 | 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 文件连接，但那个文件不存在或者服务没起来"。按下面顺序走，基本一次能定位：

1. 先确认服务在不在：systemctl status mysqld或ps -ef | grep mysqld。服务没起，什么都别谈，去看错误日志。
2. 确认 socket 文件路径：mysql --help | grep socket看客户端默认路径，再对比my.cnf里[mysqld]段的socket=配置。两边不一致就是这个问题。
3. 手动指定 socket 试一次：mysql -uroot -p -S /var/lib/mysql/mysql.sock。能进去就说明客户端路径配置错了，在[client]段补一行socket=即可。
4. 检查数据目录权限：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 就是那张便签，数据页才是账本。

具体流程是这样的：

1. 数据先写入Buffer Pool（内存中的数据页缓存），此时内存里的页变成"脏页"。
2. 同时把这次修改以物理日志的形式写进redo log buffer，再按innodb_flush_log_at_trx_commit的策略刷到磁盘。
3. 提交时还要写binlog，用于主从复制和数据恢复。
4. 为了两阶段提交的原子性，redo log 先进入 prepare 状态，binlog 写完后再把 redo 置为 commit。

innodb_flush_log_at_trx_commit这个参数是新手最该知道的一个取舍点：

生产环境老老实实用 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_updates=1，或者直接给生产账号只授SELECT，改数据走工单。开了 safe updates 之后，不带 WHERE 或者 WHERE 里没用上索引的 UPDATE 会被直接拦下来。

第二，WHERE 条件里发生隐式类型转换。这个非常隐蔽，UPDATE users SET status=1 WHERE phone='13800000000'和WHERE phone=13800000000看着差不多，但后者如果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 行，事务小、锁持有时间短、主从延迟可控。

第四，并发场景下的丢失更新。两个请求同时读到stock=10，各自减 1 写回，最后变成 9 而不是 8。解决办法是让数据库来算：UPDATE stock SET num = num - 1 WHERE id = 1 AND num > 0，或者用悲观锁SELECT ... FOR UPDATE，或者加version字段做乐观锁。

2.4 DELETE、TRUNCATE、DROP 到底差在哪

这三个语句都能"清空数据"，但机制完全不同，用错了后果差很多。

删除大数据量时，如果一个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 a=1能用上
• WHERE a=1 AND b=2能用上
• WHERE a=1 AND b=2 AND c=3全部用上
• WHERE b=2用不上，因为 b 的排序在全局是无序的
• WHERE a=1 AND c=3只有 a 用得上，c 用不上（8.0 的索引下推能部分缓解，但作用有限）

范围查询会截断后面的列。WHERE a=1 AND b>2 AND c=3，a 用得上，b 用得上（范围），但 c 用不上，因为 b 已经是一个范围，范围内 c 相对无序。

理解了这条，建索引就有了方向：把等值查询的列放前面，范围查询的列放后面，排序列的用法要单独看。如果你既要用WHERE a=? ORDER BY b，那(a,b)这个顺序通常是对的；如果是WHERE a>? ORDER BY b，那索引基本帮不上排序，会走 filesort。

3.3 索引失效的清单，照着对一遍

索引失效不是一个"错误"，它是优化器的理性选择——当它判断走索引不如全表扫快时，就放弃了索引。但有些失效是我们自己写出来的，完全可以避免。

经验：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 | +----+-------+------+---------------+------+---------+------+---------+----------------+

type=ALL、key=NULL、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 一次深分页查询的完整改造

讲一个我实际遇到过的案例。订单列表页，用户翻到很后面，接口直接超时。

原始 SQL：

SELECT * 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_DATE：

SELECT 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列。如果明明是等值查询、字段上也有索引，却显示key=NULL，八成就是隐式转换。这时候把参数值的引号检查一遍，问题往往就解决了。这个检查不需要改代码逻辑，成本极低。

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?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true

逐条解释：

• characterEncoding=utf8mb4：客户端和服务端协商字符集，避免中文乱码。要写utf8mb4不要写utf8，后者在 MySQL 里是阉割版，存不了 emoji。
• serverTimezone=Asia/Shanghai：老版本驱动不加这个会报时区错误，导致时间差 8 小时。8.0 驱动改善了，但显式写上没有坏处。
• useSSL=false：关闭 SSL。这个参数在 8.0 驱动里已经被sslMode取代了，新的写法是sslMode=DISABLED。如果你用的是 MySQL Connector/J 8.0.13 之后的版本，useSSL会告警甚至被忽略。
• allowPublicKeyRetrieval=true：这个参数就是给caching_sha2_password准备的。不开的话，第一次连接会因为拿不到公钥而报认证失败。
• rewriteBatchedStatements=true：批量插入的加速开关。不开的话，addBatch和executeBatch是一条条发过去的；开了之后，驱动会把多条合成一条INSERT ... VALUES (...),(...),(...)，性能提升非常明显。

关于 SSL 配置，简单说清楚三种状态：

配置不匹配时最常见的报错是"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-purged=OFF db_name orders > orders.sql # 导入本地 mysql -hlocalhost -uroot -p db_name < orders.sql

--single-transaction保证导出期间不加锁，对线上影响小。--set-gtid-purged=OFF在本地没有开启 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。绝大多数生产事故在爆发之前，都已经在慢查询日志里出现过很多次了。提前处理掉，远比事后救火轻松。