8.MySQL规范、慢查询优化、锁、事务特性

8.MySQL规范和慢查询优化

一、基础设计规范（10条）

1. 命名规则：数据库、表、字段均用“小写+下划线”，表名格式“业务模块_功能”（如order_payment），避免关键字/中文/大写。
2. 主键设计：优先自增整数（INT UNSIGNED AUTO_INCREMENT），中间表用复合主键（如user_role(user_id+role_id)），禁用UUID。
3. 字段类型：手机号用CHAR(11)，金额用DECIMAL(10,2)（避浮点数精度丢失），按“最小够用”选择。
4. 非空约束：必填字段设NOT NULL，配默认值（如create_time DEFAULT CURRENT_TIMESTAMP）。
5. 索引命名：唯一索引uk_字段名（如uk_user_phone），普通索引idx_字段名（如idx_user_time）。
6. 冗余控制：不存可计算数据（如“订单总金额”不单独存，由“单价×数量”计算）。
7. 字符集：统一utf8mb4（支持emoji），禁用utf8。
8. 存储引擎：优先InnoDB（支持事务/行锁/MVCC），日志表用MyISAM。
9. 字段含义：字段名体现业务（如user_id而非id），避免歧义。
10. 表结构简洁：单表字段不超过20个，拆分复杂字段（如用户信息拆user_base和user_detail）。

二、性能优化规范（12条）

1. 索引数量：单表索引≤5个，仅给“where/join/order by/group by”字段建索引。
2. 避免索引失效：不做索引字段函数操作（如DATE(create_time)='2024-01-01'）、不等于判断（!=）、前缀%模糊查询（like '%name'）。
3. 分表分库：单表数据超1000万行分表（如按时间分order_202401），按业务模块分库（用户库/订单库）。
4. 外键替代：业务代码维护关联关系，禁用MySQL外键（减少耦合/提升性能）。
5. 事务控制：事务含必要操作，用READ COMMITTED隔离级别，避长事务。
6. 查询优化：禁用select *，明确指定字段；大数据量分页用“主键偏移”（where id>1000 limit 20）。
7. 批量操作：批量插入用values(...)，批量更新用case when，避循环单条操作。
8. 大字段处理：text/blob单独分表（如富文本存user_content表），用VARCHAR(255)存短链接。
9. 索引覆盖：查询字段均从索引获取（如select user_id from user where name='a'），避回表。
10. 锁优化：更新用for update行锁（基于索引），高并发用乐观锁（version字段）。
11. 表优化：定期用OPTIMIZE TABLE整理碎片，分析慢查询日志（slow_query_log）。
12. 连接池配置：连接数设为“CPU核心数×2+1”，避连接泄露（用完关闭）。

三、安全与维护规范（8条）

1. 权限最小化：应用账号仅授select/insert/update/delete，禁用root账号。
2. 敏感数据加密：密码用bcrypt/SHA256加盐哈希，手机号/身份证用AES对称加密。
3. 防SQL注入：用预编译语句（PreparedStatement）或ORM框架（MyBatis），不拼接SQL。
4. 日志记录：关键操作（注册/支付）记日志（操作人/时间/SQL），便于排查。
5. 备份策略：全量备份（凌晨）+增量备份（每小时），备份文件加密，定期测试恢复。
6. 版本控制：表结构变更用SQL脚本（如V1.0.0__create_user.sql），避手动修改。
7. 大事务拆分：单次事务操作≤1000条数据，大量数据拆小事务批量执行。
8. 监控告警：监控CPU/内存/连接数/慢查询，阈值告警（如连接数超80%预警）。

四、慢查询优化核心方案

4.1 优先优化高并发SQL

• 核心逻辑：高并发SQL发生问题影响范围更广，优化成本更低，需优先处理。
• 示例对比：
  • SQL1：每小时执行10000次，每次20个IO，优化后省2万次IO（易优化、影响大）；
  • SQL2：每小时执行10次，每次20000个IO，优化后省2万次IO（难优化、影响小）；
• 结论：优先优化SQL1这类高并发SQL，而非仅看单次IO节省量。

4.2 定位性能瓶颈（优化前必做）

1. IO瓶颈：数据访问耗时久，核心排查“是否正确使用索引”（如索引未命中导致全表扫描）；
2. CPU瓶颈：数据运算耗时久，核心排查“数据的运算、分组、排序逻辑”（如group by/order by未基于索引，导致内存排序）；
3. 网络带宽瓶颈：辅助优化方向，需通过增大网络带宽缓解（非数据库层面核心优化点）。

4.3 明确优化目标

• 结合因素：数据库当前运行状态、SQL在数据库中的业务关联度、SQL具体功能；
• 核心目标：明确SQL“最好/最差资源消耗”，最终以“提升用户体验”为导向（如查询响应时间≤100ms）。

4.4 从explain执行计划入手

• 核心作用：explain是唯一直观展示SQL执行状态的工具，优化前需确认“索引是否命中”“表连接顺序”“扫描行数”，避免盲目优化。

4.5 关键优化手段

1. 小结果集驱动大结果集：
   原理：减少内层表读取次数，如外层循环5次（小结果集）、内层1000次（大结果集），仅需5次数据库连接；反之需1000次连接，效率大幅降低。
2. 索引中完成排序：
   原理：索引本身有序，order by字段在索引中时，可直接利用索引排序，避免内存/磁盘排序（内存不足时触发落盘，耗时剧增）。
3. 只获取需要的列：
   禁止select *，明确指定字段（如select user_id, user_name from user），减少数据传输量与内存占用。
4. 用最有效过滤条件：
   误区：where条件并非越多越好；正确做法：优先保留高过滤性条件（如where user_id=1比where user_status=1 and user_level=2过滤性更强，优先用前者缩小范围）。
5. 避免复杂join与子查询：
   限制：join表数量≤3张；拆分复杂SQL为多个单表查询，在程序中封装结果（避免join占用过多资源导致其他进程等待）。
6. 合理设计并利用索引：
   （1）判定是否建索引：
   ① 频繁作为查询条件的字段，建议建索引；
   ② 唯一性差的字段（如状态、类型字段），不适合单独建索引（查询结果超全表15%时索引失效）；
   ③ 更新频繁的字段，不适合建索引（更新时需同步更新索引，增资源消耗）；
   ④ 不出现在where中的字段，不建索引。
   （2）选择合适索引：
   ① 单键索引：选当前查询过滤性更好的字段；
   ② 联合索引：过滤性最好的字段排在索引字段顺序前列；
   ③ 联合索引：优先选在where中出现频次高的字段。

五、MySQL 锁核心知识点（面试速通版）

5.1 MySQL 锁的分类（按核心维度划分）

1. 按锁粒度划分
   锁类型 适用场景 优缺点 代表存储引擎
   表锁 全表操作（如alter table、全表查询） 优点：开销小、加锁快；缺点：粒度粗、并发低 MyISAM（默认）、InnoDB（支持但少用）
   行锁 单行 / 多行数据操作（如update … where id=1） 优点：粒度细、并发高；缺点：开销大、加锁慢 InnoDB（默认）
   页锁 介于表锁与行锁之间（按数据页锁定） 优点：平衡粒度与并发；缺点：易产生死锁 BDB（极少用，几乎淘汰）
2. 按锁功能划分（实际工作高频）
   （1）共享锁（S 锁，读锁）
   作用：多个事务可同时加 S 锁，允许 “读” 但禁止 “写”；
   加锁方式：select … lock in share mode；
   冲突规则：S 锁与 S 锁兼容，与 X 锁（写锁）冲突；
   场景：需确保数据 “读取时不被修改”（如统计报表查询）。
   （2）排他锁（X 锁，写锁）
   作用：仅一个事务可加 X 锁，禁止其他事务加任何锁（读 + 写）；
   加锁方式：update/delete/insert（InnoDB 自动加）、select … for update（手动加）；
   冲突规则：X 锁与任何锁（S/X）都冲突；
   场景：数据修改（如订单状态更新、用户余额扣减）。
   （3）意向锁（InnoDB 特有，避免表锁与行锁冲突）
   作用：提前声明 “事务将对表加行锁”，避免表锁与行锁的冲突检查（减少开销）；
   分类：意向共享锁（IS 锁，事务计划加 S 锁）、意向排他锁（IX 锁，事务计划加 X 锁）；
   规则：加行锁前必须先加对应意向锁；表锁会检查意向锁（如加表 X 锁时，需确保无 IS/IX 锁）。

5.2 InnoDB 行锁的核心实现（面试重中之重）

InnoDB 行锁是基于索引实现的（无索引则退化表锁），核心分为 3 类：

1. 记录锁（Record Lock）
   作用：锁定 “具体某一行数据”（基于主键 / 唯一索引）；
   示例：update user set name=‘a’ where id=1（id 是主键，锁定 id=1 的行）；
   场景：精准定位单行数据的修改，并发最高。
2. 间隙锁（Gap Lock）
   作用：锁定 “数据间隙”（无实际数据的区间），防止 “幻读”（RR 隔离级别特有）；
   示例：update user set age=20 where age between 10 and 30（若表中 age 有 15、25，会锁定 (10,15)、(15,25)、(25,30) 三个间隙）；
   注意：仅 RR 及以上隔离级别生效，RC 级别无间隙锁（靠 MVCC 避免幻读）。
3. 临键锁（Next-Key Lock）
   作用：Record Lock + Gap Lock 的组合（锁定 “行 + 间隙”），InnoDB 行锁默认类型；
   规则：锁定区间为 “左开右闭”（如索引值 10、20，临键锁区间是 (-∞,10]、(10,20]、(20,+∞)）；
   场景：RR 级别下避免幻读的核心（防止其他事务在间隙中插入数据）。

5.3 锁的实际问题与解决方案

1. 行锁退化表锁的原因
   核心原因：where 条件未使用索引（或索引失效，如函数操作、类型转换）；
   示例：update user set name=‘b’ where phone=‘13800138000’（phone 无索引 → 行锁退化表锁）；
   解决方案：给 where 条件字段建有效索引，避免索引失效。
2. 死锁及解决（面试必问）
   （1）死锁产生条件（4 个缺一不可）
   互斥：资源（锁）只能被一个事务占用；
   持有并等待：事务持有一个锁，同时等待另一个锁；
   不可剥夺：事务已持有的锁不能被强行夺走；
   循环等待：事务 A 等事务 B 的锁，事务 B 等事务 A 的锁。
   （2）死锁排查与解决
   排查：执行show engine innodb status查看最近一次死锁日志（含涉事事务、SQL、锁类型）；
   解决方案：
   ① 按 “固定顺序加锁”（如事务 1 先加 id=1 的锁，再加 id=2 的锁；事务 2 同顺序）；
   ② 用tryLock()（如 Java 中 ReentrantLock）或select … for update wait 5（超时自动释放）；
   ③ 拆分长事务为短事务（减少锁持有时间）；
   ④ 避免批量更新时的循环加锁（如按主键排序后批量更新）。
3. 锁冲突的优化思路
   减少锁持有时间：事务内仅包含必要操作（如避免事务内等待用户输入）；
   缩小锁粒度：用行锁替代表锁（确保索引有效）；
   读写分离：读操作走从库（加 S 锁），写操作走主库（加 X 锁），降低主库锁冲突；
   用乐观锁替代悲观锁：高并发读场景（如商品库存查询），用version字段（update … where id=1 and version=2）避免加锁。

六、事务特性

6.1 原子性（Atomicity）：要么全做，要么全不做

实现核心：Undo Log（回滚日志）
原理：
事务执行时，InnoDB 会记录每条操作的 “反向操作” 到 Undo Log（如插入记录时记录 “删除该记录” 的操作，更新记录时记录 “恢复旧值” 的操作）；
若事务执行失败（如报错、手动rollback），InnoDB 通过 Undo Log 反向执行所有操作，将数据回滚到事务开始前的状态；
Undo Log 分类：Insert Undo（仅用于事务回滚，事务提交后可删除）、Update Undo（还用于 MVCC 多版本读，需由清理线程定期回收）。

6.2 一致性（Consistency）：数据从一个一致状态到另一个一致状态

实现核心：原子性、隔离性、持久性共同保障 + 约束校验
原理：
原子性确保事务不会 “部分执行”（如转账不会只扣钱不进账）；
隔离性确保并发事务不会相互干扰（如不会读取到未提交的脏数据）；
持久性确保已提交事务的修改不会丢失；
额外约束：数据库层（主键唯一、外键关联）+ 业务层（字段非空、金额非负）共同维持数据逻辑一致。

6.3 隔离性（Isolation）：并发事务相互隔离，避免干扰

实现核心：锁机制 + MVCC（多版本并发控制）
原理：
锁机制：控制并发写操作的冲突：
行锁（Record Lock）：锁定单行数据，避免 “脏写”（如update user set balance=100 where user_id=1会锁定user_id=1的行）；
间隙锁（Gap Lock）+ 临键锁（Next-Key Lock）：在 RR（可重复读）隔离级别下避免 “幻读”（锁定数据间隙，防止其他事务插入新行）；
MVCC：解决并发读 - 写冲突（实现 “读不加锁，写不阻塞读”）：
构成：隐藏列（DB_TRX_ID事务 ID、DB_ROLL_PTR回滚指针）+ Undo Log（形成版本链）+ Read View（读视图）；
工作逻辑：事务读操作时，通过 Read View 判断版本链中 “可见的历史版本”（如 RC 级别每次查询生成新 Read View，RR 级别事务内复用 Read View），避免脏读、不可重复读。

6.4 持久性（Durability）：已提交事务的修改永久保存

实现核心：Redo Log（重做日志）+ WAL 机制（Write-Ahead Logging）
原理：
WAL 机制：事务修改数据时，先将 “数据修改的物理日志” 写入 Redo Log（先写内存缓冲区，再定期刷盘），再修改内存中的数据页（后续异步刷盘到磁盘文件）；
崩溃恢复：若数据库崩溃，重启时通过 Redo Log 重做 “已提交但未刷盘到数据文件” 的操作，确保数据不丢失。