MySQL零基础学习手册

学者猫头鹰
0 阅读1小时+

第一章 基础入门（环境搭建与MySQL认知）

1.1 MySQL 简介与核心优势

1.1.1 概念解析

MySQL是一款开源免费、跨平台的关系型数据库管理系统（RDBMS），最初由瑞典MySQL AB公司开发，现归属Oracle公司。关系型数据库的核心特点，是将数据以「行+列」的二维数据表形式结构化存储，通过表关联关系管理业务数据，是目前互联网企业应用最广泛的数据库。

1.1.2 核心特性

  • 开源免费：社区版完全免费，个人学习、企业商用均无版权成本，使用门槛极低。

  • 轻量高效：资源占用少、读写响应快，既能适配中小型业务，也可支撑高并发场景的核心数据存储。

  • 跨平台兼容：支持Windows、Linux、MacOS主流操作系统，部署方式灵活。

  • 生态成熟：社区活跃、官方文档完善、配套运维工具丰富，行业岗位需求量大，学习性价比高。

  • 稳定可靠：经过数十年企业生产环境打磨，故障率低，可长期稳定运行于线上业务。

1.1.3 高频面试考点

Q：MySQL和Redis的核心区别及业务适用场景？

A：MySQL是磁盘持久化的关系型数据库，支持完整ACID事务和复杂多表关联查询，能严格保障数据一致性，主要用于存储业务核心、需要永久落地的正式数据。Redis是内存型非关系型数据库，读写性能极强，但不支持复杂事务与强数据一致性，主要用于热点数据缓存、分布式锁、接口限流、用户会话存储等场景。二者互为补充，企业项目中通常搭配使用。

1.2 MySQL 环境部署（Windows/Linux 通用方案）

1.2.1 概念解析

搭建本地运行环境是学习MySQL开发与运维的基础。目前行业主流稳定版本为MySQL 8.0，对比老旧的5.7版本，在安全性、功能性、性能上均有大幅优化，适配现代开发规范，新手学习和企业开发均优先选用该版本。

1.2.2 部署流程

Windows 系统：下载MySQL8.0官方安装包 → 向导式安装 → 设置root管理员密码 → 启动数据库服务 → 配置系统环境变量，实现全局指令调用。

Linux（CentOS）系统：通过yum源安装mysql-server服务 → 执行数据库初始化配置 → 启动MySQL服务 → 设置开机自启，保障服务器重启后数据库自动运行。

1.2.3 实操指令

# 1. 启动MySQL服务（Windows cmd/Linux终端通用）
net start mysql80  
# 2. 登录MySQL数据库
# -u 指定登录用户名（默认超级管理员root）；-p 触发密码输入校验
mysql -u root -p  
# 3. 退出数据库客户端
exit;  
# 4. 查看MySQL版本，验证安装是否成功
SELECT VERSION();

1.2.4 规范注意事项

  • 安装阶段务必牢记root初始密码，密码丢失需重新初始化数据库才能重置，操作繁琐且存在数据丢失风险。

  • 生产环境禁止开启远程匿名登录，仅对指定业务服务器IP开放数据库访问权限，规避非法访问风险。

  • MySQL8.0默认采用caching_sha2_password密码加密方式，部分老旧客户端工具不兼容，连接失败时需手动适配加密规则。

1.2.5 高频面试考点

Q：MySQL8.0 相较于5.7版本的核心迭代优势？

A：1. 密码认证插件升级为caching_sha2_password，账号安全性大幅提升；2. 原生支持窗口函数、CTE递归查询等高级SQL特性，可快速实现复杂数据分析；3. 优化锁机制与事务调度逻辑，有效降低高并发场景的锁冲突概率；4. 废弃查询缓存等低效功能，减少系统资源冗余占用；5. 完善原子DDL机制，避免表结构变更过程中出现数据损坏、数据不一致问题。

1.3 MySQL 整体架构（核心分层）

1.3.1 概念解析

MySQL整体运行架构分为Server层和存储引擎层两大核心模块，这是理解MySQL底层执行逻辑、事务机制、索引原理的基础。所有SQL执行、权限校验、事务管理、索引查询逻辑，均依托该分层架构实现。

1.3.2 架构流程

客户端发起请求 → 连接层权限与连接校验 → Server层解析、优化、执行SQL → 存储引擎层读写数据 → 磁盘落地数据并返回执行结果

1.3.3 分层详解

1. Server层

MySQL核心服务层，为所有存储引擎共用，不负责数据存储，仅处理各类业务逻辑。

  • 连接层：处理客户端连接请求、校验访问权限、管理数据库连接线程，控制并发连接数量。

  • 查询缓存：用于缓存高频查询结果，MySQL8.0已彻底废弃，该功能缓存命中率低、维护成本高，实用性差。

  • 解析器：校验SQL语法合法性、解析语句语义，生成标准化语法树。

  • 优化器：自动分析SQL执行方案，筛选最优索引和JOIN顺序，降低执行开销。

  • 执行器：调用存储引擎接口，执行解析后的SQL逻辑，返回查询或操作结果。

2. 存储引擎层

可插拔式数据读写核心层，负责实际的数据存储、读取与事务处理，不同引擎适配不同业务场景。

  • InnoDB：MySQL8.0默认存储引擎，支持事务、行级锁、MVCC、外键约束，数据安全性和并发性能优异，是生产环境唯一首选引擎。

  • MyISAM：老旧传统引擎，不支持事务和MVCC并发机制，仅支持表级锁，并发读写阻塞严重，仅适用于无数据更新的静态场景，目前已基本退出生产环境。

1.3.4 实操指令

# 查看MySQL支持的所有存储引擎
SHOW ENGINES;
# 查看当前数据库默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';

1.3.5 规范注意事项

  • Server层仅处理逻辑运算，不存储任何业务数据，所有数据均由存储引擎层统一管理、落地磁盘。

  • InnoDB是目前唯一支持完整事务、MVCC并发控制的主流存储引擎，生产环境禁止使用MyISAM。

1.3.6 高频面试考点

Q：InnoDB与MyISAM存储引擎的核心差异及生产选型规范？

A：核心差异主要有四点：1. 事务支持：InnoDB支持完整ACID事务，MyISAM不支持，无法保障数据一致性；2. 锁机制：InnoDB默认行级锁，并发读写性能优异，MyISAM仅支持表级锁，任意读写操作都会阻塞全表并发；3. 并发控制：InnoDB支持MVCC无锁读，适配高并发场景，MyISAM无任何并发优化机制；4. 数据安全：InnoDB支持宕机自动恢复，数据容错性高，MyISAM宕机后极易出现数据损坏、索引失效问题。

选型规范：企业生产环境统一使用InnoDB，MyISAM仅可用于无更新、无事务需求的静态离线日志场景，严禁用于核心业务数据表。

第二章 核心原理（底层核心机制解析）

2.1 InnoDB 行格式与数据存储

2.1.1 概念解析

InnoDB以数据行为最小数据存储单位，每一行数据除了开发者自定义的业务字段外，还包含三个系统隐藏字段，支撑事务、MVCC、索引关联、数据回滚等底层核心功能。MySQL8.0默认采用Dynamic动态行格式，可适配长短文本混合存储的各类业务场景。

2.1.2 结构原理

InnoDB完整行结构 = 自定义业务字段 + 3个系统隐藏字段 + 行标记位

核心隐藏字段：DB_ROW_ID（行ID）、DB_TRX_ID（事务ID）、DB_ROLL_PTR（回滚指针）

2.1.3 字段说明

  • DB_TRX_ID：记录当前行数据最后一次修改的事务ID，是事务隔离、数据版本区分的核心依据。

  • DB_ROLL_PTR：指向undo log回滚日志地址，用于实现事务数据回滚和MVCC历史快照读取。

  • DB_ROW_ID：无主键数据表的默认唯一标识，数据表自定义主键后，该字段自动失效，不再参与索引构建。

2.1.4 规范注意事项

  • 所有业务数据表必须手动设置主键，无主键表会自动生成隐藏ROW_ID作为主键，索引结构杂乱、检索效率低，且无法人工管控。

  • Dynamic动态行格式可自动压缩超长文本、字符串字段，节省磁盘空间，适配绝大多数业务场景。

2.1.5 高频面试考点

Q：InnoDB强制数据表建立主键的核心原因？

A：1. InnoDB聚簇索引完全依托主键构建，无主键时系统自动生成隐藏ROW_ID，索引结构无序杂乱，检索效率大幅降低；2. 隐藏主键无法用于多表关联和精准索引优化，整体数据库查询性能受损；3. 无主键数据表会导致MVCC数据版本链混乱，破坏事务隔离性与数据一致性；4. 无主键场景下，数据新增、更新易触发频繁的页分裂，产生大量索引碎片，持续损耗数据库性能。因此生产环境所有业务表必须自定义主键。

2.2 B+树索引核心结构

2.2.1 概念解析

MySQL InnoDB的所有索引均基于B+树数据结构实现。B+树是专为磁盘IO优化的多路平衡查找树，核心特点为非叶子节点仅存储索引键，叶子节点存储完整数据或主键，且所有叶子节点通过双向链表有序串联，是MySQL高效查询的底层核心支撑。

2.2.2 结构原理

B+树层级结构：根节点 → 分支节点（非叶子节点，仅存索引键） → 叶子节点（有序存储完整数据/主键，双向链表串联）

2.2.3 核心优势

  • 层级极低：千万级数据量仅需3-4层树结构即可承载，单次查询磁盘IO次数少，查询效率稳定。

  • 全量有序：所有叶子节点通过有序链表串联，天然适配范围查询、排序、分页等高频业务场景。

  • 节点轻量化：非叶子节点仅存储索引键，不存储完整数据，内存可缓存更多索引节点，进一步减少磁盘交互。

2.2.4 规范注意事项

  • 索引查询本质是B+树层级遍历，树层级越多，磁盘IO次数越多，查询速度越慢。

  • 数据表频繁增删改数据，会触发B+树节点分裂与合并，产生索引碎片，长期积累会降低查询性能。

2.2.5 高频面试考点

Q：MySQL索引选用B+树的核心原因，为何舍弃二叉树、普通B树与哈希表？

A：1. 普通二叉树层级过深，大数据量下磁盘IO频繁，且易出现树倾斜问题，查询效率极不稳定；2. 普通B树非叶子节点存储完整数据，节点占用空间大，同等数据量下树层级更高，IO开销远大于B+树；3. 哈希表仅支持精准等值查询，不支持范围查询、排序、分页，无法适配数据库绝大多数业务场景；4. B+树非叶子节点轻量化、层级少，叶子节点有序串联，完美适配磁盘IO特性与数据库高频查询场景，综合性能最优。

2.3 索引页与数据页机制

2.3.1 概念解析

InnoDB的最小磁盘存储单元是数据页（Page），MySQL8.0默认页大小为16KB，全局固定且不建议随意修改。所有索引数据、业务数据均存储在数据页与索引页中，索引页存放B+树非叶子节点索引信息，数据页存放叶子节点完整业务数据。

2.3.2 核心参数

innodb_page_size = 16KB（默认固定值，生产环境不建议修改，修改需重新初始化数据库）

2.3.3 运行原理

MySQL读取数据时，不会单次读取单条数据，而是一次性加载整页16KB数据到内存缓存，大幅减少磁盘交互次数，提升读写效率。每个数据页内部存储多条有序数据行，页与页之间通过链表关联，构成完整的数据存储结构。

2.3.4 规范注意事项

  • 单页存储数据过多会触发页分裂，存储数据过少会产生页空洞，两种情况都会造成性能损耗。

  • 数据页大小为全局统一配置，修改该参数需重新初始化数据库、清空所有数据，生产环境严禁随意修改。

2.3.5 高频面试考点

Q：InnoDB数据页固定16KB的设计核心意义？

A：16KB是磁盘IO、内存缓存、数据检索效率的最优平衡值。磁盘读写以页为最小单元，16KB可一次性加载批量数据，减少磁盘交互次数；同时规避了页过大导致单次IO负载过高、内存缓存利用率低，或页过小导致B+树层级增多、查询效率下降的问题，是适配关系型数据库读写场景的标准化最优设计。

2.4 聚簇索引与二级索引

2.4.1 概念解析

聚簇索引（主键索引）：InnoDB默认核心索引，每张表有且仅有一个，其B+树叶子节点直接存储完整的整行业务数据。

二级索引（普通索引）：开发者自定义的普通索引、唯一索引，其B+树叶子节点仅存储「索引列值+主键值」，不存储完整行数据。

2.4.2 结构原理

聚簇索引：索引键 = 数据表主键，叶子节点 = 完整行数据

二级索引：索引键 = 自定义普通字段，叶子节点 = 索引字段值 + 对应主键值

2.4.3 查询机制

通过二级索引查询非索引字段数据时，会先在二级索引树中匹配主键值，再通过主键查询聚簇索引树获取完整行数据，该过程称为回表查询，会增加一次磁盘IO，损耗查询性能。

2.4.4 实操案例

# 1. 创建用户表，主键自动生成聚簇索引
CREATE TABLE user(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键（聚簇索引）',
    name VARCHAR(20) NOT NULL COMMENT '用户名',
    phone VARCHAR(11) COMMENT '手机号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 2. 创建普通二级索引
CREATE INDEX idx_user_name ON user(name);

2.4.5 规范注意事项

  • 主键优先使用自增INT/BIGINT类型，禁止使用UUID作为主键，UUID无序会频繁触发聚簇索引页分裂，产生大量索引碎片。

  • 二级索引查询非索引字段必然触发回表操作，高频查询场景可通过覆盖索引优化，规避回表性能损耗。

2.4.6 高频面试考点

Q：聚簇索引与二级索引的核心差异及性能影响？

A：1. 数量差异：单张表有且仅有一个聚簇索引，可根据业务需求创建多个二级索引；2. 存储结构：聚簇索引叶子节点存储完整行数据，二级索引仅存储索引列+主键；3. 查询逻辑：聚簇索引查询直接获取完整数据，无额外IO，二级索引大多需要回表查询；4. 写入性能：二级索引数量越多，数据表增删改成本越高，每次数据变更需同步更新所有索引结构。

2.5 事务ACID四大特性

2.5.1 概念解析

事务是数据库操作的最小执行单元，一组关联SQL语句会被整体封装，执行时要么全部成功提交，要么全部失败回滚，以此保障业务数据准确一致。ACID四大特性是数据库事务的核心标准，也是数据一致性的底层保障。

2.5.2 特性详解

  • A（原子性）：事务不可分割，作为整体执行，要么全部执行成功，要么全部撤销回滚，无部分执行的情况。

  • C（一致性）：事务执行前后，数据库整体数据状态合法合规、逻辑一致，不会出现数据错乱、矛盾问题。

  • I（隔离性）：多个事务并发执行时相互隔离、互不干扰，通过不同隔离级别控制并发冲突。

  • D（持久性）：事务一旦提交成功，数据会永久落地磁盘，服务器宕机、重启后数据不会丢失。

2.5.3 实操案例

# 手动开启事务
START TRANSACTION;
# 执行业务更新SQL
UPDATE user SET name='张三' WHERE id=1;
UPDATE user SET phone='13800138000' WHERE id=1;
# 无异常则提交事务，数据持久化
COMMIT;
# 出现异常执行回滚，撤销所有操作
# ROLLBACK;

2.5.4 规范注意事项

  • MySQL默认开启自动提交事务，单条SQL执行后会自动提交，需手动关闭autocommit才能自主管控事务。

  • 事务执行过程中服务器宕机，MySQL会通过undo log自动回滚未完成事务，保障原子性。

2.5.5 高频面试考点

Q：MySQL事务ACID四大特性的底层保障机制？

A：1. 原子性：由undo log回滚日志保障，事务失败时通过undo log撤销所有操作；2. 持久性：由redo log重做日志保障，事务提交后日志落地磁盘，宕机重启可恢复数据；3. 隔离性：由锁机制+MVCC多版本并发控制共同实现，适配不同隔离级别；4. 一致性：依托原子性、持久性、隔离性，结合数据库约束与业务逻辑共同保障，是事务的最终核心目标。

2.6 事务隔离级别与MVCC

2.6.1 概念解析

多事务并发执行时，会产生脏读、不可重复读、幻读三类数据问题。MySQL提供四种逐级递进的事务隔离级别，用于解决各类并发冲突。MVCC（多版本并发控制）是InnoDB实现事务隔离、提升并发性能的核心机制，可实现无锁读写，大幅提升数据库并发能力。

2.6.2 隔离级别详解

  1. 读未提交：可读取其他事务未提交的数据，存在脏读、不可重复读、幻读问题，生产环境完全禁用。

  2. 读已提交：仅能读取其他事务已提交的数据，解决脏读问题，仍存在不可重复读、幻读问题。

  3. 可重复读：同一事务内多次读取同一数据结果一致，解决脏读、不可重复读，仅存在少量幻读问题，是InnoDB默认隔离级别。

  4. 串行化：所有事务串行排队执行，彻底解决所有并发问题，但并发性能极低，极少用于生产环境。

2.6.3 MVCC核心原理

MVCC通过undo log保存数据的历史修改版本，结合数据行的事务ID与Read View读写视图，实现快照读，无需加锁即可读取数据，同时兼顾事务隔离性与并发性能。

2.6.4 读写模式区分

  • 快照读：普通SELECT查询，基于MVCC读取数据历史快照，无锁、高并发，是最常用的数据读取模式。

  • 当前读：UPDATE、DELETE、INSERT、SELECT ... FOR UPDATE等操作，读取数据最新版本，会加锁阻塞并发事务。

2.6.5 实操案例

# 查看当前数据库事务隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';
# 设置全局事务隔离级别为可重复读（InnoDB默认）
SET GLOBAL transaction_isolation = 'REPEATABLE-READ';

2.6.6 规范注意事项

  • InnoDB默认可重复读级别，通过临键锁机制解决绝大部分幻读问题，可满足常规生产业务需求。

  • MVCC仅适用于读已提交、可重复读级别下的普通SELECT快照读，在读未提交、串行化级别及所有当前读操作中不生效。

2.6.7 高频面试考点

Q：InnoDB MVCC的完整实现原理及适用场景？

A：MVCC即多版本并发控制，核心作用是实现无锁并发读，提升数据库高并发性能。其实现依托三大核心要素：1. 数据行隐藏的事务ID、回滚指针，记录数据修改版本信息；2. undo log版本链，存储数据所有历史修改快照；3. Read View读写视图，管控不同事务的数据可见性。

事务执行普通SELECT快照读时，会通过Read View比对数据版本链，筛选出符合当前隔离级别的可见数据版本，全程无需加锁，完美适配业务高频查询场景，是InnoDB高并发能力的核心支撑。

2.7 InnoDB锁机制（行锁/间隙锁/临键锁）

2.7.1 概念解析

锁是数据库解决并发事务资源竞争、保障数据安全的核心机制。InnoDB依托索引实现精细化锁控制，分为行级锁、间隙锁、临键锁，相较于MyISAM的全表锁，并发性能大幅提升，是高并发业务稳定运行的核心保障。

2.7.2 锁类型详解

  • 行锁：精准锁定单条数据行，锁粒度最小、并发性能最高。仅在SQL命中有效索引时生效，仅锁定当前操作数据行，不阻塞其他行的读写，适用于精准单条数据更新场景。

  • 间隙锁（Gap Lock）：锁定索引数据之间的空白区间，不锁定已有数据行。核心作用是防止其他事务在锁定区间插入新数据，从根源规避幻读问题，仅在可重复读隔离级别下生效。

  • 临键锁（Next-Key Lock）：InnoDB默认锁算法，是行锁+间隙锁的结合体，锁定当前数据行及数据前后的空白区间，既能锁定已有数据，也能禁止区间内插入新数据，最大限度解决并发幻读问题。

2.7.3 核心匹配规则

锁的类型与生效范围，由索引命中情况和事务隔离级别共同决定，核心规则如下：

  • 可重复读隔离级别（InnoDB默认）：精准匹配唯一索引且数据存在时，临键锁降级为行锁；普通索引查询、范围查询、精准匹配无数据时，触发间隙锁或临键锁；完全未命中索引时，锁升级为表锁。

  • 读已提交及更低级别：无间隙锁、临键锁机制，仅支持行锁与表锁，无法彻底解决幻读问题。

2.7.4 实操案例

-- 前提：student 表 stu_age 为普通索引，事务隔离级别为可重复读
-- 1. 精准命中唯一索引，触发行锁
UPDATE student SET stu_name='测试' WHERE stu_id=1;

-- 2. 普通索引范围查询，触发临键锁/间隙锁
UPDATE student SET stu_name='测试' WHERE stu_age > 20;

-- 3. 无索引条件更新，锁升级为全表锁，阻塞所有并发写入
UPDATE student SET stu_name='测试';

2.7.5 规范注意事项

  • 无索引或索引失效的更新、删除语句，会直接升级为表锁，阻塞全表所有并发操作，是线上业务卡顿、超时的高频诱因。

  • 间隙锁仅在可重复读隔离级别下生效，是InnoDB解决幻读的核心手段，且仅针对索引区间生效。

  • 范围查询极易触发大范围临键锁，锁定大量空白索引区间，高并发场景下易引发锁等待、死锁问题，需尽量精简范围条件。

  • 主键/唯一索引精准匹配锁粒度最细、性能最优，生产环境优先使用精准索引条件操作数据。

2.7.6 高频面试考点

Q：间隙锁的触发场景、作用及潜在风险？

A：

触发场景：仅在可重复读事务隔离级别下生效，常见场景包括普通索引范围查询、精准匹配无对应数据、分页查询、区间更新/删除等。

核心作用：锁定索引空白区间，禁止其他事务在区间内插入新数据，从数据库底层解决幻读问题，保障同一事务内数据读取的稳定性与一致性。

潜在风险：间隙锁锁定的是空白索引区间，而非真实数据行，会扩大锁的影响范围，出现「无数据却被锁」的情况。高并发场景下易引发跨事务锁等待、死锁、接口超时等问题，因此线上业务需严格规避大范围、模糊、无精准索引的更新删除语句。

2.7.7 锁超时机制与故障解析

锁超时是MySQL并发场景高频故障，指当前事务请求的锁资源被其他事务长期持有，等待时长超过数据库阈值后，事务强制终止并抛出异常，避免线程永久阻塞，是保障数据库并发稳定性的重要机制。

1. 核心参数配置

  • innodb_lock_wait_timeout：锁等待超时阈值，默认50秒，生产环境可根据业务场景微调，高并发短事务场景建议调低至10-20秒，快速释放卡死事务，避免连接堆积。

  • 该参数为全局+会话级可配置，修改后即时生效，无需重启数据库。

2. 超时核心触发场景

  • 长事务阻塞：前置事务执行耗时过久，长期持有行锁、间隙锁，后续事务持续等待锁资源，触发超时。

  • 大范围锁竞争：范围查询、无索引更新触发临键锁/表锁，锁范围过大，大量事务排队等待。

  • 事务嵌套冗余逻辑：事务内包含非数据库业务逻辑、循环查询，拉长锁持有时间，极易引发后续事务超时。

  • 死锁临界等待：多事务锁竞争激烈，未形成闭环死锁，但长期互相等待，触发锁超时。

3. 生产规避与优化方案

  • 严格精简事务粒度，事务内仅保留核心增删改SQL，剥离所有业务计算、接口调用等冗余逻辑，缩短锁持有时长。

  • 杜绝无索引、大范围更新删除，精准命中索引缩小锁范围，减少锁竞争。

  • 高并发场景合理调低锁超时阈值，快速释放卡死事务，避免数据库连接耗尽。

  • 代码层面捕获锁超时异常，配置自动重试机制，处理偶发性锁等待超时问题。

2.7.8 锁等待堆积成因与排查优化

锁等待堆积是线上严重性能故障，指大量并发事务同时等待同一锁资源，形成排队阻塞，导致接口批量超时、数据库连接爆满、业务卡顿瘫痪，多由锁范围过大、长事务、索引失效引发。

1. 核心成因

  • 锁粒度失控：索引失效触发表锁、范围查询触发大范围临键锁/间隙锁，单锁阻塞大量并发事务。

  • 热点行竞争：高频更新同一行热点数据（如商品库存、用户余额），大量事务争抢同一行锁，形成等待队列堆积。

  • 长事务常驻锁资源：个别事务长期不提交，持续持有锁，后续所有关联事务全部阻塞等待。

2. 实操排查指令
# 查看当前锁等待、锁阻塞详情
SELECT * FROM performance_schema.data_locks;
# 查看锁等待超时事务日志
SHOW ENGINE INNODB STATUS;
3. 紧急处理与长效优化

  • 紧急止损：通过锁查询定位阻塞源头事务ID，执行KILL指令杀死卡死长事务，快速释放锁资源，恢复业务。

  • 优化热点数据：热点行数据做数据分片、库存拆分，分散锁竞争压力，避免单点锁瓶颈。

  • 固化索引规范：所有更新删除语句必须命中精准索引，杜绝锁升级、大范围锁生成。

  • 监控告警：配置锁等待时长、阻塞事务数监控，提前感知锁堆积问题，规避批量故障。

2.7.9 行锁升级表锁完整机制与避坑

InnoDB默认行级锁仅在索引精准命中场景生效，一旦索引失效或无索引条件，精细化行锁会直接升级为全表锁，是线上并发卡顿、锁阻塞的TOP级诱因，属于生产高频致命坑点。

1. 锁升级核心原理

InnoDB行锁依托索引机制实现，数据库仅能通过索引定位具体数据行。当SQL无法命中有效索引、索引失效、无查询条件时，数据库无法精准锁定目标行，只能锁定整张数据表，锁粒度从最小行锁直接升级为最高粒度表锁，阻塞全表所有读写并发。

2. 行锁升级表锁高频场景

  • 无索引条件操作：UPDATE/DELETE语句未携带WHERE查询条件，全表操作直接触发表锁。

  • 索引失效场景：索引字段做函数运算、隐式类型转换、前后模糊匹配，导致索引失效，行锁升级为表锁。

  • 索引不生效场景：查询条件字段无索引、索引区分度极低（如全表统一状态值），优化器放弃索引，走全表扫描并触发表锁。

  • 事务跨批量无索引操作：事务内多条无索引更新语句，持续持有表锁，长期阻塞并发。

3. 核心危害

  • 表锁阻塞全表所有增删改操作，无关业务数据读写全部被阻塞，引发批量接口超时。

  • 表锁持有期间极易引发大规模锁等待堆积，耗尽数据库连接，导致服务瘫痪。

  • 高并发场景下单次表锁升级，即可引发连锁性能雪崩，影响全站业务。

4. 生产强制避坑规范

  • 所有UPDATE、DELETE业务语句，必须携带精准索引WHERE条件，禁止无索引、无条件操作。

  • 上线前通过EXPLAIN校验所有更新删除SQL，确保索引有效命中，杜绝索引失效SQL上线。

  • 禁止对索引字段做函数运算、类型转换，避免隐性索引失效触发锁升级。

  • 低区分度字段不单独作为更新筛选条件，避免优化器放弃索引导致表锁升级。

第三章 SQL 实战（标准语法与落地实操）

3.1 SQL四大分类（DDL/DML/DQL/DCL）

3.1.1 概念解析与分类说明

SQL是操作数据库的标准结构化查询语言，所有数据库操作可划分为四大类，覆盖结构定义、数据操作、数据查询、权限管控全场景，是开发人员日常使用最频繁的核心语法，零基础必须熟练掌握。

  • DDL 数据定义语言：负责定义、修改、删除数据库、数据表、索引等数据结构，核心指令：CREATE、ALTER、DROP。

  • DML 数据操作语言：负责数据表数据的增删改操作，核心指令：INSERT、UPDATE、DELETE。

  • DQL 数据查询语言：负责数据表数据查询，使用频率最高，核心指令：SELECT。

  • DCL 数据控制语言：负责数据库账号权限管理，核心指令：GRANT、REVOKE。

3.2 DDL语法详解（库、表、索引操作）

3.2.1 实操案例

# 1. 创建数据库，指定utf8mb4编码，支持表情符号与特殊字符
CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 2. 切换使用指定数据库
USE test_db;

# 3. 创建业务数据表（生产标准规范模板）
CREATE TABLE IF NOT EXISTS student(
    stu_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '学生主键ID',
    stu_name VARCHAR(30) NOT NULL COMMENT '学生姓名',
    stu_age TINYINT DEFAULT 0 COMMENT '学生年龄',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';

# 4. 表结构修改：新增字段
ALTER TABLE student ADD COLUMN stu_gender TINYINT COMMENT '性别 1-男 2-女';

# 5. 创建普通单列索引
CREATE INDEX idx_stu_name ON student(stu_name);

# 6. 删除索引
DROP INDEX idx_stu_name ON student;

# 7. 删除数据表
DROP TABLE IF EXISTS student;

3.2.2 规范注意事项

  • 所有业务数据表必须手动指定ENGINE=InnoDB、CHARSET=utf8mb4，禁止使用数据库默认参数，规避编码乱码、引擎不规范等问题。

  • 生产环境禁止随意执行DROP、ALTER语句，表结构变更会触发锁表，直接影响线上业务运行。

  • 业务数据表必须标配创建时间、更新时间字段，实现自动赋值、自动更新，便于数据追溯与统计。

3.2.3 高频面试考点

Q：MySQL utf8与utf8mb4字符集的核心区别及生产规范？

A：MySQL内置的utf8并非标准UTF-8编码，仅支持最大3字节字符，无法存储Emoji表情、生僻特殊符号，业务中极易出现存储报错。utf8mb4是标准4字节UTF-8编码，兼容所有Unicode字符，支持表情符号及特殊字符存储。

生产规范：所有数据库、数据表统一使用utf8mb4字符集、utf8mb4_unicode_ci排序规则，彻底规避特殊字符存储异常问题。

3.3 DML语法详解（增删改）

3.3.1 实操案例

# 1. 单条数据插入
INSERT INTO student(stu_name,stu_age,stu_gender) VALUES ('李四',20,1);

# 2. 批量插入数据（性能远高于循环单条插入，减少数据库连接交互）
INSERT INTO student(stu_name,stu_age,stu_gender) 
VALUES ('王五',21,2),('赵六',19,1);

# 3. 数据更新（必须带精准索引条件）
UPDATE student SET stu_age=22 WHERE stu_id=1;

# 4. 数据删除（禁止省略WHERE条件）
DELETE FROM student WHERE stu_id=2;

3.3.2 规范注意事项

  • 生产环境UPDATE、DELETE语句必须携带精准索引WHERE条件，禁止全表更新、全表删除，避免引发批量数据事故。

  • 批量写入数据优先使用批量VALUES语法，减少网络IO与数据库交互次数，大幅提升写入性能。

  • 业务数据优先采用逻辑删除（新增is_delete标记字段），尽量避免物理删除，便于数据恢复与溯源。

3.4 DQL语法详解（查询核心，全覆盖）

3.4.1 基础与条件查询

# 生产禁用：全表查询所有字段
SELECT * FROM student;

# 生产规范：按需指定查询字段
SELECT stu_id,stu_name,stu_age FROM student;

# 多条件精准查询
SELECT * FROM student WHERE stu_age >=20 AND stu_gender=1;

# 后缀模糊查询（可命中索引）
SELECT * FROM student WHERE stu_name LIKE '李%';

3.4.2 分组排序与分页查询

# 分组统计：按性别分组统计人数
SELECT stu_gender,COUNT(*) AS num FROM student GROUP BY stu_gender;

# 排序查询：按年龄倒序
SELECT * FROM student ORDER BY stu_age DESC;

# 分页查询：LIMIT 偏移量,条数
SELECT * FROM student LIMIT 0,10;

3.4.3 规范注意事项

  • 生产查询严禁使用SELECT *，仅查询业务所需字段，减少网络传输与IO开销。

  • 模糊查询优先使用前缀匹配xx%，禁止使用%xx、%xx%前后模糊匹配，会直接导致索引失效。

  • 大偏移量分页（LIMIT 10000,10）性能极差，生产需通过主键分页优化。

3.5 数据筛选高阶SQL（保留极值数据、子查询、联表查询）

3.5.1 保留最新/最旧一条数据 SQL 写法（生产高频）

日常业务中常需筛选「每组最新一条、每组最旧一条」数据，如每个用户最新订单、每个商品最早记录，是报表统计、数据去重的核心写法，提供两种生产通用、性能最优的实现方案。

1. 窗口函数写法（MySQL8.0+ 首选，性能最优）

适配大数据量分组取极值，无重复数据、逻辑清晰、性能远超子查询，生产优先使用。

# 需求：按用户分组，查询每个用户最新的一条订单数据
# 原理：PARTITION BY 分组 + ORDER BY 时间排序，取排名第一的数据
SELECT order_id,user_id,order_time,order_amount
FROM (
    SELECT 
        order_id,user_id,order_time,order_amount,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time DESC) AS rn
) AS temp
WHERE rn = 1;

# 每组取最旧一条数据（仅修改排序规则为正序）
SELECT order_id,user_id,order_time,order_amount
FROM (
    SELECT 
        order_id,user_id,order_time,order_amount,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time ASC) AS rn
) AS temp
WHERE rn = 1;
2. 关联子查询写法（兼容MySQL5.7及以下低版本）

适配低版本数据库，小数据量场景使用，大数据量性能弱于窗口函数。

# 关联子查询实现分组取最新一条
SELECT o1.* FROM order_info o1
INNER JOIN (
    SELECT user_id,MAX(order_time) AS max_time 
    FROM order_info 
    GROUP BY user_id
) o2 ON o1.user_id = o2.user_id AND o1.order_time = o2.max_time;
规范注意事项

  • 分组排序字段（order_time）必须建立索引，否则全表排序性能极差；

  • 存在同用户同时间多条数据时，ROW_NUMBER会随机取一条，需改用RANK函数适配重复极值场景；

  • 大数据量绝对优先窗口函数，规避子查询嵌套带来的循环扫描开销。

3.5.2 IN/EXISTS 子查询优劣、选型场景

IN和EXISTS是开发最常用的子查询语法，二者底层执行逻辑完全不同，错误选型会直接导致全表扫描、性能暴跌，是面试高频、生产重点优化点。

1. 底层执行原理

  • IN子查询：先执行子查询，查出所有结果集存入内存，再将外层表数据与结果集做匹配比对，适合子查询结果集小的场景；

  • EXISTS子查询：先遍历外层表，逐行带入子查询做存在性判断，匹配成功立即终止当前行判断，无需遍历全部结果，适合外层表数据量小的场景。

2. 核心优劣对比

  • IN优势：子查询结果集固定，匹配效率稳定，代码可读性高；劣势：子查询数据量过大（超1000条）会放弃索引、触发全表扫描，不支持NULL值匹配。

  • EXISTS优势：支持大数据量子查询，逐行匹配、短路执行，内存占用低；劣势：外层表数据量大时，遍历开销极高，执行效率骤降。

3. 生产选型黄金规则

  • 优先用IN：子查询结果集小（1000条以内）、外层表数据量大；

  • 优先用EXISTS：子查询结果集超大、外层表数据量小；

  • 绝对避坑：禁止IN嵌套无索引字段的大表子查询，必触发性能瓶颈。

实操案例
# IN 用法：子查询结果少，查询所有有订单的用户
SELECT * FROM user 
WHERE user_id IN (SELECT user_id FROM order_info WHERE order_status=1);

# EXISTS 用法：外层用户表数据少，校验存在性
SELECT * FROM user u
WHERE EXISTS (SELECT 1 FROM order_info o WHERE o.user_id=u.user_id AND order_status=1);

3.5.3 LEFT JOIN/RIGHT JOIN/INNER JOIN 区别与避坑

联表查询是业务开发核心语法，三类JOIN用法极易混淆，且存在大量隐性数据误差、性能坑点，需严格区分场景并遵守规范。

1. 核心区别详解

  • INNER JOIN（内连接）：只返回两张表中匹配关联条件的交集数据，无匹配数据直接过滤，无空值数据，数据精准。

  • LEFT JOIN（左连接）：以左表为基准，返回左表所有数据，右表匹配不到则填充NULL值，保留左表全量数据。

  • RIGHT JOIN（右连接）：以右表为基准，返回右表所有数据，左表匹配不到则填充NULL值，生产极少使用，可通过LEFT JOIN等价替换。

2. 生产高频避坑点（重点）

  • 左连接筛选右表字段失效坑：LEFT JOIN后WHERE条件筛选右表字段，会直接降级为INNER JOIN，丢失左表无匹配数据。 正确规范：右表筛选条件必须放在ON后，不可放WHERE后。

  • NULL值统计坑：左连接产生的NULL值会被COUNT(字段)忽略，导致统计数据偏差，优先使用COUNT(*)统计。

  • 冗余数据坑：右表存在一对多数据时，左表单条数据会被关联出多条重复数据，需提前去重。

  • 语法规范坑：生产禁止使用RIGHT JOIN，统一用LEFT JOIN，代码可读性、一致性更高。

正反案例对比
# 错误写法！右表条件放WHERE，LEFT JOIN降级为INNER JOIN
SELECT u.*,o.order_amount FROM user u
LEFT JOIN order_info o ON u.user_id=o.user_id
WHERE o.order_status=1;

# 正确写法！右表过滤条件放在ON后
SELECT u.*,o.order_amount FROM user u
LEFT JOIN order_info o ON u.user_id=o.user_id AND o.order_status=1;

3.5.4 联表查询索引失效、笛卡尔积问题

1. 联表索引失效核心场景与解决方案

联表查询90%的性能问题源于索引失效，核心集中在关联字段、字段类型、编码不匹配三大场景。

  • 关联字段无索引：JOIN关联字段未建索引，两张大表联表必触发全表扫描。 规范：所有联表ON条件字段，必须建立索引。

  • 字段类型不一致（高频坑）：左表关联字段INT、右表VARCHAR，或字段长度不一致，触发隐式类型转换，索引完全失效。 规范：联表字段类型、长度必须完全统一。

  • 字符集编码不一致：两张表编码分别为utf8、utf8mb4，关联时触发隐式转换，索引失效。 规范：全库全表统一utf8mb4编码。

  • 关联字段函数运算：ON条件中对索引字段做SUBSTR、DATE等运算，索引失效。 规范：禁止索引字段做函数运算，提前预处理数据。

2. 笛卡尔积成因与彻底规避方案

笛卡尔积：联表查询无有效ON关联条件，或关联条件失效，导致左表N条数据、右表M条数据，最终返回N*M条数据，数据量爆炸、数据库直接卡死。

  • 触发成因：漏写ON关联条件、ON条件恒成立、关联字段全部失效、多表联表关联逻辑混乱。

  • 严重危害：瞬时生成千万级冗余数据，耗尽CPU、内存、磁盘IO，引发服务雪崩。

  • 规避规范：1. 多表联表必须写精准ON关联条件，禁止漏写；2. 联表前校验关联字段索引、类型、编码一致性；3. 禁止模糊关联、恒等关联条件；4. 复杂多表联表先用小结果集临时表中转。

3.5.5 临时表触发场景、性能损耗

MySQL执行计划中的Using temporary（使用临时表）是高频性能缺陷标记，需熟练掌握触发场景、损耗原理与规避方案。

1. 临时表核心触发场景

  • 使用DISTINCT去重、GROUP BY分组统计、UNION合并查询结果；

  • 联表查询无有效索引、分组字段无索引；

  • 多字段分组、非索引字段排序+分组组合查询；

  • 子查询嵌套多层、结果集复杂拼接。

2. 核心性能损耗

  • 内存/磁盘开销：小结果集临时表存内存，大结果集自动落地磁盘，磁盘IO暴涨；

  • 重复排序开销：临时表生成后需二次排序、去重，额外消耗CPU资源；

  • 无法复用索引：临时表无索引结构，后续查询全表扫描，性能极差；

  • 连接阻塞风险：大临时表生成耗时久，长期占用数据库连接，引发接口超时。

3.5.6 Using temporary 性能问题彻底规避方案

针对临时表性能缺陷，提供生产可落地的全套优化方案，彻底消除Using temporary标记。

  • 索引优化（核心方案）：GROUP BY、DISTINCT、ORDER BY字段建立联合索引，让数据库直接通过索引完成分组、排序、去重，无需创建临时表；

  • SQL逻辑重构：拆分复杂GROUP BY、UNION查询，分批统计数据，避免单次生成超大结果集；

  • 规避无效去重：业务数据本身无重复时，删除冗余DISTINCT关键字，杜绝无效临时表生成；

  • 替换低效语法：能用窗口函数分组统计的场景，替代传统GROUP BY，减少临时表依赖；

  • 优化联表逻辑：联表查询提前过滤数据，缩小结果集范围，避免大结果集触发磁盘临时表。

高频面试考点

Q：EXPLAIN中Using temporary、Using filesort代表什么？如何优化？

A：Using temporary表示SQL执行生成临时表，多用于分组、去重场景；Using filesort代表文件排序，未命中索引排序规则，需磁盘排序。二者均为性能缺陷。优化核心：给分组、排序、去重字段建立索引，通过索引有序特性规避临时表与文件排序；拆分复杂SQL、缩小查询结果集、重构业务查询逻辑，彻底消除两大性能问题。

3.6 CASE WHEN 条件函数

3.5 CASE WHEN 条件函数

3.5.1 概念解析

CASE WHEN是MySQL核心条件逻辑函数，等价于代码中的if-else分支判断，支持多条件、区间条件判断，可用于数据查询、字段衍生、分组统计、行转列、数据分层等场景，是数据分析、复杂业务统计的高频核心语法。

3.5.2 语法结构

简单CASE语法（等值匹配）

CASE 字段 WHEN 条件值1 THEN 结果1 WHEN 条件值2 THEN 结果2 ELSE 默认结果 END

复杂CASE语法（区间/多逻辑匹配，主流用法）

CASE WHEN 条件表达式1 THEN 结果1 WHEN 条件表达式2 THEN 结果2 ELSE 默认结果 END

3.5.3 实操案例

# 案例1：根据年龄生成年龄段衍生字段
SELECT 
    stu_id,
    stu_name,
    stu_age,
    CASE 
        WHEN stu_age < 18 THEN '未成年'
        WHEN stu_age BETWEEN 18 AND 22 THEN '青年'
        ELSE '成年' 
    END AS age_level
FROM student;

# 案例2：行转列统计男女人数
SELECT
    COUNT(CASE WHEN stu_gender=1 THEN 1 END) AS male_num,
    COUNT(CASE WHEN stu_gender=2 THEN 1 END) AS female_num
FROM student;

3.5.4 规范注意事项

  • CASE语句必须以END结尾，语法不可缺失，否则直接报错。

  • 所有分支返回的数据类型必须统一，避免类型冲突报错。

  • 未设置ELSE时，不匹配条件的数据会返回NULL，业务统计需提前做好空值处理。

3.5.5 高频面试考点

Q：CASE WHEN与IF函数的选型场景与优劣对比？

A：IF函数仅支持二元真假判断，语法简单，但无法处理多分支、区间条件，仅适用于简单业务判断。CASE WHEN支持多条件分支、区间判断、批量统计，可实现行转列、分层统计等复杂逻辑，可读性、可维护性、扩展性更强。企业开发中，简单二元判断可使用IF函数，复杂条件逻辑统一优先使用CASE WHEN。

3.6 窗口函数（MySQL8.0核心新特性）

3.6.1 概念解析

窗口函数是MySQL8.0重磅新特性，核心用于实现分组内排序、排名、累计统计。区别于GROUP BY分组聚合（一组仅返回一条数据），窗口函数分组后会保留所有原始行数据，同时附加统计、排名结果，是数据分析、报表统计的核心语法。

3.6.2 函数分类

  • 排名类窗口函数：ROW_NUMBER()、RANK()、DENSE_RANK()，用于数据排序排名。

  • 聚合类窗口函数：SUM/AVG/MAX/MIN + OVER()，用于分组累计、均值、极值统计。

3.6.3 实操案例

# 按年龄倒序，对比三种排名函数效果
SELECT
    stu_name,
    stu_age,
    -- 连续不重复排名，无并列、无跳号
    ROW_NUMBER() OVER(ORDER BY stu_age DESC) AS row_num,
    -- 并列同名次，后续序号跳号（常规考试排名）
    RANK() OVER(ORDER BY stu_age DESC) AS rank_num,
    -- 并列同名次，后续序号连续不跳号
    DENSE_RANK() OVER(ORDER BY stu_age DESC) AS dense_rank_num
FROM student;

3.6.4 规范注意事项

  • 窗口函数仅MySQL8.0及以上版本支持，5.7及以下低版本不兼容。

  • OVER()括号内可通过PARTITION BY实现分组窗口排序，通过ORDER BY实现组内排序。

3.6.5 高频面试考点

Q：ROW_NUMBER、RANK、DENSE_RANK三大排名函数的业务选型？

A：

  1. ROW_NUMBER：生成连续唯一序号，无并列、无跳号，适用于需要唯一排序标记、数据分页、去重排序场景。

  2. RANK：数值相同名次并列，后续序号跳号，适配传统考试、竞赛排名场景。

  3. DENSE_RANK：数值相同名次并列，后续序号连续不跳号，适用于档位分级、层级统计场景。

3.7 DCL权限管理语法

3.7.1 概念解析

DCL数据控制语言，主要用于数据库账号创建、权限分配与回收，管控不同用户的数据库访问与操作权限，是数据库安全运维的核心手段，可有效规避越权操作、数据泄露风险。

3.7.2 实操案例

# 1. 创建普通业务用户，适配MySQL8.0加密规则，仅本地访问
CREATE USER 'test_user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Test@123456';

# 2. 授予该用户指定数据库全部权限
GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost';

# 3. 刷新权限（权限变更必须执行方可生效）
FLUSH PRIVILEGES;

# 4. 回收用户所有权限
REVOKE ALL PRIVILEGES ON test_db.* FROM 'test_user'@'localhost';

3.7.3 规范注意事项

  • 生产业务程序禁止使用root超级账号，必须创建独立低权限专属账号，最小化安全风险。

  • 权限新增、修改、回收后，必须执行FLUSH PRIVILEGES刷新权限，否则变更不生效。

  • 禁止开启匿名账号、禁止配置%全局远程访问，避免外网非法访问数据库。

3.7.4 高频面试考点

Q：生产环境数据库权限设计核心规范？

A：严格遵循最小权限原则与账号隔离原则。

  1. 严禁使用root超级账号运行业务程序，按需创建独立业务账号、运维账号、只读账号；

  2. 权限按需分配，业务账号仅授予增删改查等必要操作权限，杜绝ALL PRIVILEGES全量授权；

  3. 严格管控访问来源，禁止配置%全局远程访问权限，仅对白名单服务器IP开放连接；

  4. 定期清理闲置账号、回收冗余权限，常态化排查数据库账号安全隐患，防止越权操作与数据泄露。

第四章 MySQL 性能优化（生产实战调优）

4.1 性能优化整体思路与层级

4.1.1 概念解析

MySQL性能卡顿、接口超时、慢查询等线上问题，并非单一因素导致，需遵循「由浅入深、从外到内」的层级优化思路。优化优先级从高到低依次为：业务架构优化 > SQL语句优化 > 索引优化 > 表结构优化 > 数据库配置优化 > 硬件优化。其中SQL与索引优化是成本最低、收益最高的核心手段，也是日常开发优化的重点。

4.1.2 优化层级详解

  • 业务层优化（最高优先级）：规避不合理的业务逻辑、减少重复查询、冷热数据分离、合理使用缓存，从根源减少数据库压力。大部分线上性能瓶颈，本质都是业务设计不合理导致的无效数据库请求。

  • SQL与索引优化（核心重点）：优化低效SQL、建立合理索引、杜绝索引失效，减少磁盘IO与锁等待，是日常开发最常用、性价比最高的优化方式。

  • 表结构优化：合理设计字段类型、拆分大表、避免冗余字段、规范主键设计，减少数据存储冗余，提升读写效率。

  • 数据库配置优化：调整内存、IO、连接数等核心参数，适配服务器硬件配置，最大化利用系统资源。

  • 硬件优化（最后手段）：升级CPU、扩容内存、更换高速SSD硬盘，仅在软件优化到位后仍存在性能瓶颈时使用。

4.1.3 规范注意事项

  • 严禁一上来就修改数据库配置、升级硬件，多数性能问题均可通过SQL和索引优化解决，盲目升级只会增加成本，无法根治问题。

  • 优化遵循「先定位、再优化、后验证」原则，禁止盲目改代码、加索引，避免引入新的性能隐患。

4.2 慢查询日志（问题定位核心工具）

4.2.1 概念解析

慢查询日志是MySQL内置的性能排查核心工具，用于记录执行耗时超过指定阈值的SQL语句，是线上定位慢SQL、接口卡顿、数据库负载过高问题的首要手段。通过慢查询日志可以精准抓取低效SQL，针对性完成优化，解决绝大多数数据库性能瓶颈。

4.2.2 核心参数说明

  • slow_query_log：慢查询日志总开关，OFF为关闭，ON为开启，生产环境建议长期开启。

  • long_query_time：慢查询阈值，单位秒，默认10秒，生产环境建议设置为1秒，精准捕捉轻微慢查询。

  • slow_query_log_file：慢查询日志存储路径，可自定义配置，方便日志收集与分析。

  • log_queries_not_using_indexes：开启后可记录所有未使用索引的SQL，提前规避潜在性能问题。

4.2.3 实操指令

# 1. 查看慢查询日志相关配置
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

# 2. 临时开启慢查询日志（重启失效）
SET GLOBAL slow_query_log = ON;
# 3. 设置慢查询阈值为1秒
SET GLOBAL long_query_time = 1;
# 4. 记录未使用索引的SQL
SET GLOBAL log_queries_not_using_indexes = ON;

4.2.4 规范注意事项

  • 慢查询日志开启后会轻微占用磁盘IO，但对整体数据库性能影响极低，生产环境必须常态化开启。

  • 阈值不宜设置过大，1秒为生产最优标准，过大会遗漏短耗时高频慢SQL，长期堆积引发性能问题。

  • 定期清理、归档慢查询日志，避免日志文件过大占用磁盘空间，影响日志解析效率。

4.2.5 高频面试考点

Q：慢查询日志的作用与生产配置规范？

A：慢查询日志核心用于精准定位执行效率低下的SQL，是数据库性能优化、故障排查的基础工具。生产规范：1. 永久开启慢查询总开关，无需关闭；2. 慢查询阈值统一设置为1秒；3. 开启无索引SQL记录，提前规避索引缺失问题；4. 定期分析日志，优化高频慢SQL，避免性能问题累积；5. 配合日志分析工具，批量统计Top耗时SQL，提升优化效率。

4.3 EXPLAIN 执行计划（SQL优化核心神器）

4.3.1 概念解析

EXPLAIN是MySQL专属的SQL分析指令，可模拟SQL执行过程，输出完整执行计划，无需实际运行SQL即可精准判断索引是否生效、查询扫描范围、连接顺序、执行效率等核心信息，是SQL优化、索引失效排查的核心工具，开发与运维必备技能。

4.3.2 核心字段详解（高频重点）

  • id：SQL执行序号，id越大越先执行，id相同从上到下依次执行，用于判断多表查询、子查询执行顺序。

  • select_type：查询类型，区分普通查询、联合查询、子查询，判断SQL复杂度。

  • table：当前查询对应的数据表名称。

  • type：核心字段，查询性能等级，性能从优到劣：system > const > eq_ref > ref > range > index > all。生产环境禁止出现all全表扫描。

  • key：当前SQL实际命中的索引，为NULL表示未命中任何索引，出现索引失效。

  • key_len：索引有效长度，用于判断联合索引是否完全命中，可验证索引使用效率。

  • rows：MySQL预估需要扫描的数据行数，数值越小查询效率越高。

  • Extra：额外执行信息，包含Using filesort（文件排序）、Using temporary（临时表）等性能缺陷标记，是优化重点。

4.3.3 实操案例

# 解析普通查询执行计划
EXPLAIN SELECT stu_id,stu_name FROM student WHERE stu_age=20;

# 解析分页查询执行计划
EXPLAIN SELECT * FROM student ORDER BY stu_age DESC LIMIT 0,10;

4.3.4 规范注意事项

  • type字段为优化核心，生产环境常规查询需保证达到ref/range级别，绝对禁止all全表扫描。

  • Extra出现Using filesort、Using temporary代表SQL存在严重性能问题，必须针对性优化。

  • EXPLAIN仅为预估执行计划，受MySQL优化器影响，结果仅供参考，需结合实际业务数据验证。

4.3.5 高频面试考点

Q：EXPLAIN中type字段各类值的含义与生产达标标准？

A：type是执行计划的核心性能指标：1. const/eq_ref：精准命中唯一索引、主键索引，性能最优，适用于单条精准查询；2. ref：命中普通索引，多条数据匹配，是日常业务查询最优达标级别；3. range：范围查询命中索引，适配WHERE、LIMIT、区间条件场景；4. index：遍历整棵索引树，性能较差；5. all：全表扫描，性能最差，生产环境绝对禁止。

生产标准：常规业务查询type不低于ref，范围查询不低于range，杜绝index、all级别查询。

4.4 索引失效大全（生产高频避坑）

4.4.1 概念解析

很多场景下表中已建立索引，但SQL执行时依旧走全表扫描，本质是触发了索引失效规则。索引失效是线上慢SQL、数据库压力过载的最主要原因，必须熟记所有失效场景，在开发阶段提前规避。

4.4.2 全量索引失效场景

  • 1. 模糊查询首尾通配：%xxx、%xxx% 前后模糊匹配，直接索引失效，仅前缀xxx%可命中索引。

  • 2. 索引列参与运算：对索引字段做加减乘除、函数运算，如WHERE age+10=30，索引完全失效。

  • 3. 隐式类型转换：字符串索引字段传入数字、数字字段传入字符串，触发自动类型转换，索引失效。

  • 4. 使用!=、<>、NOT IN、NOT EXISTS：非等值判断大概率触发索引失效，走全表扫描。

  • 5. OR连接无索引字段：OR前后字段一个有索引、一个无索引，会导致整体索引失效。

  • 6. 联合索引不满足最左前缀原则：跳过最左侧索引列，直接使用后续索引字段，联合索引完全失效。

  • 7. 数据量过少/数据倾斜：数据表数据量极小，或查询条件匹配绝大部分数据，MySQL优化器认为全表扫描更快，主动放弃索引。

4.4.3 失效优化实操方案

# 索引失效（首尾模糊）
EXPLAIN SELECT * FROM student WHERE stu_name LIKE '%李%';
# 优化后（前缀模糊，命中索引）
EXPLAIN SELECT * FROM student WHERE stu_name LIKE '李%';

# 索引失效（索引列运算）
EXPLAIN SELECT * FROM student WHERE YEAR(create_time)=2025;
# 优化后（常量运算，字段不参与计算）
EXPLAIN SELECT * FROM student WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31';

4.4.4 规范注意事项

  • 开发阶段写完SQL必须用EXPLAIN校验索引生效情况，提前规避失效问题。

  • 无法规避的模糊查询、非等值查询，可通过业务改造、 ES替代查询等方式优化。

  • 联合索引严格遵循最左前缀原则，索引字段排序需贴合高频查询条件。

4.4.5 高频面试考点

Q：联合索引最左前缀原理与落地规范？

A：联合索引基于B+树有序构建，排序规则严格遵循索引字段定义顺序。查询时必须匹配最左侧的首个索引字段，后续字段才能依次生效，跳过最左字段会导致整个联合索引失效。

落地规范：1. 高频查询、等值查询字段放在联合索引最左侧，范围查询字段放最右侧；2. 日常查询严格遵循索引字段顺序，不跨字段、不跳字段查询；3. 避免建立冗余联合索引，合理复用已有索引，减少索引维护开销。

4.5 覆盖索引与回表优化

4.5.1 概念解析

覆盖索引：二级索引包含SQL查询所需的所有字段，无需回表查询聚簇索引，直接通过二级索引返回数据，彻底规避回表IO开销，是高频查询场景最优索引优化方案。

普通二级索引仅存储索引列+主键，查询非索引字段必须回表；覆盖索引将查询字段全部纳入索引，实现「索引即数据」，查询性能大幅提升。

4.5.2 实操案例

# 普通索引：查询触发回表
CREATE INDEX idx_stu_name ON student(stu_name);
SELECT stu_name,stu_age FROM student WHERE stu_name='张三';

# 覆盖索引：包含所有查询字段，无回表
CREATE INDEX idx_name_age ON student(stu_name,stu_age);
SELECT stu_name,stu_age FROM student WHERE stu_name='张三';

4.5.3 核心优势

  • 杜绝回表查询，减少一次磁盘IO，查询性能显著提升。

  • 二级索引数据量更小、加载更快，内存缓存命中率更高。

  • 高并发、高频查询场景优化收益极高，是低成本高效优化手段。

4.5.4 规范注意事项

  • 覆盖索引并非越多字段越好，索引字段过多会增大索引体积，降低索引检索效率。

  • 仅针对高频、热点查询场景构建覆盖索引，低频查询无需刻意优化，避免索引冗余。

  • EXPLAIN中Extra字段出现Using index，代表成功命中覆盖索引。

4.5.5 高频面试考点

Q：什么是回表查询？如何彻底优化回表性能问题？

A：回表查询是指通过二级索引匹配到主键后，需要再次扫描聚簇索引获取完整字段数据的二次查询过程，会额外消耗磁盘IO，降低查询性能。

优化方案：1. 优先使用覆盖索引，将查询字段全部纳入二级索引，直接消除回表；2. 优先查询主键、索引字段，减少非索引字段查询；3. 拆分复杂查询，精简返回字段，最小化回表开销。

4.6 分页与排序深度优化

4.6.1 问题解析

常规分页 LIMIT offset,size 在小偏移量下性能正常，当offset超大（如LIMIT 10000,10）时，MySQL会先扫描前10010条数据，丢弃前10000条，仅返回后10条，扫描数据量巨大，性能极差，是线上分页接口超时的高频问题。

4.6.2 优化方案（主键分页）

# 低效写法：大偏移量分页
SELECT * FROM student ORDER BY stu_id LIMIT 10000,10;

# 高效写法：主键WHERE分页（延迟关联）
SELECT * FROM student WHERE stu_id > 10000 ORDER BY stu_id LIMIT 10;

4.6.3 排序优化规范

  • ORDER BY 字段必须建立索引，避免出现Using filesort文件排序。

  • 排序字段尽量使用数字、主键等离散度高、体积小的字段，禁止超长字符串排序。

  • 多字段排序需建立联合索引，贴合排序字段顺序，保证索引有序匹配。

4.6.4 高频面试考点

Q：大偏移量LIMIT分页性能差的原因与最优解决方案？

A：性能差原因：MySQL分页机制为前置扫描，大偏移量需要遍历并丢弃大量无效数据，IO开销极高。最优解决方案：采用主键自增分页，通过WHERE条件过滤前置数据，直接定位分页起始位置，无需扫描无效数据，性能提升数十倍。适用于所有有序、主键连续的分页场景，是生产标准优化方案。

4.7 表结构与字段优化

4.7.1 字段类型优化原则

  • 越小越好：在满足业务前提下，优先选用占用空间最小的数据类型，减少数据页存储压力，提升读写效率。

  • 避免NULL：字段默认设置NOT NULL，NULL值会占用额外存储空间，且会导致索引失效、查询逻辑复杂。

  • 禁用大字段：TEXT、BLOB超大字段单独分表存储，避免主表数据过大影响常规查询性能。

  • 统一字符集：全库全表统一utf8mb4，避免跨字段编码转换引发性能问题与乱码。

4.7.2 冷热数据分离与分表思路

单表数据量超过千万级后，索引体积过大、查询效率下降、DDL操作锁表风险升高，需做冷热分离或分表优化。热数据单独建表用于日常读写，冷数据归档至备份表，减少主表数据体量，保障业务性能稳定。

4.7.4 千万级数据批量去重优化（生产高频）

数据去重是数据清洗、业务数据迭代中的高频场景，千万级大批量数据去重极易出现锁表、超时、数据库CPU飙升问题，普通去重语法无法适配大数据量场景，本节补齐单字段、多字段、保留指定数据的批量高效去重方案与底层优化逻辑。

1. 核心去重规则定义

业务重复数据判定规则：单字段重复（如手机号、唯一编码一致）、多字段联合重复（如用户姓名+身份证号一致）；去重核心原则：保留最新数据/最早数据，删除冗余重复数据，生产优先保留最新创建、最新更新的有效数据。

2. 中小数据量通用去重（万级以内）
# 单字段去重：保留id最大的最新数据，删除重复旧数据
DELETE t1 FROM student t1,student t2 
WHERE t1.stu_name = t2.stu_name AND t1.stu_id < t2.stu_id;

# 多字段联合去重：姓名+年龄重复，保留最新数据
DELETE t1 FROM student t1,student t2 
WHERE t1.stu_name = t2.stu_name AND t1.stu_age = t2.stu_age AND t1.stu_id < t2.stu_id;
3. 千万级大数据量批量去重（生产最优方案）

直接执行DELETE联表去重会触发全表扫描、长事务、锁表卡死，千万级数据必须采用「临时表中转去重」方案，拆分事务、减少锁占用、规避超时问题，是企业标准化大数据去重手段。

# 步骤1：创建临时表，存储去重后的唯一有效数据（分组保留最新主键）
CREATE TEMPORARY TABLE temp_student_unique 
SELECT MAX(stu_id) AS stu_id FROM student GROUP BY stu_name,stu_age;

# 步骤2：批量删除重复数据，仅保留临时表中的有效数据
DELETE FROM student WHERE stu_id NOT IN (SELECT stu_id FROM temp_student_unique);

# 步骤3：销毁临时表，释放资源
DROP TEMPORARY TABLE IF EXISTS temp_student_unique;
4. 千万级去重核心优化细节

  • 拆分批量操作：千万级数据禁止一次性全量去重，通过LIMIT分批删除，避免长事务锁表、日志溢出。

  • 命中索引优化：分组去重的字段必须建立联合索引，避免GROUP BY触发临时表、文件排序。

  • 低峰期执行：大数据去重属于高危操作，必须凌晨业务低峰期执行，提前备份原始数据。

  • 关闭临时日志：大批量操作可临时调整日志刷盘参数，减少IO压力，操作完成后还原。

5. 高频面试考点

Q：千万级数据为什么不能直接用联表DELETE去重？批量去重的最优方案？

A：直接联表删除存在三大致命问题：1. 会触发全表扫描与全表锁，阻塞线上所有读写业务；2. 生成超大事务，redo/undo log日志暴涨，极易引发数据库超时、宕机；3. 批量删除产生大量索引碎片，严重损耗后续查询性能。最优方案为临时表中转去重，先筛选保留有效数据，再批量删除冗余数据，拆分事务压力、缩小锁范围，适配千万级及以上数据量去重场景。

4.7.5 NULL与空字符串''的区别、存储与查询避坑指南

NULL和空字符串''是开发中极易混淆的字段值，二者在存储占用、查询规则、索引生效、统计逻辑上差异极大，是线上数据错乱、查询结果异常、索引失效的隐性高频坑，底层原理与避坑规则为面试必考、生产必备知识点。

1. 核心本质与存储差异

  • NULL（空值）：代表「无数据、未赋值」，是不确定值。InnoDB中NULL字段会占用额外存储空间（1字节NULL标记位），且不会存入索引统计基数，会影响索引命中率。

  • 空字符串''：代表「有数据、值为空」，是确定有效值。不占用数据存储长度，仅占用字段结构位，可正常参与索引统计、排序、匹配。

2. 查询逻辑大坑（高频踩坑）

  • 匹配规则不同：WHERE 字段=NULL 永远查询不到数据，MySQL中NULL不等于任何值（包括自身），必须使用 IS NULL / IS NOT NULL 判断；而 WHERE 字段='' 可精准匹配空字符串。

  • 聚合函数坑：COUNT(字段) 会忽略NULL值，统计不到空数据；但会统计空字符串''，极易导致统计数据偏差。COUNT(*) 可统计所有行，包含NULL和空字符串行。

  • 唯一索引坑：普通唯一索引允许存在多个NULL值（NULL不判重），但仅允许一个空字符串''，会引发重复数据校验异常。

  • 排序规则坑：排序时NULL值优先级高于空字符串，会默认排在最前方，导致业务排序错乱。

3. 生产强制规范

  • 所有业务字段默认设置 NOT NULL DEFAULT ''（字符串）、NOT NULL DEFAULT 0（数值），彻底杜绝NULL值。

  • 查询语句禁止使用 =NULL、!=NULL，统一使用 IS NULL/IS NOT NULL。

  • 数据统计优先使用COUNT(*)，规避COUNT(字段)忽略NULL值导致的统计误差。

4. 面试考点

Q：NULL和空字符串的核心区别？生产为什么禁止字段为NULL？

A：1. 存储不同：NULL是不确定空值，占用额外标记存储空间；''是确定空值，无额外开销。2. 查询不同：NULL无法用等值查询匹配，仅支持IS判断；''支持常规等值查询。3. 统计不同：聚合函数忽略NULL，不忽略''，易造成统计偏差。4. 索引不同：NULL会影响索引基数统计，降低索引命中率，唯一索引可重复存NULL，违背唯一性约束。因此生产环境所有字段禁止为NULL，统一设置默认值。

4.7.6 IN、OR、LIMIT 1 高频优化细节

IN、OR、LIMIT 1是日常开发最高频的查询语法，看似简单，实则存在大量隐性性能坑，多数慢SQL均由不当使用这三类语法导致，本节补齐底层原理与精细化优化方案。

1. IN语句优化细节

  • 底层原理：IN匹配离散数据时，MySQL会转化为多个等值查询，命中索引；但IN后集合数据量过大时，优化器会判定全表扫描更快，主动放弃索引。

  • 优化规范：IN集合元素控制在1000以内，超量拆分分批查询；禁止IN子查询嵌套未索引字段，极易触发全表扫描；连续数值IN可替换为BETWEEN AND，性能更优。

  • 避坑点：IN包含NULL值时，查询结果会自动过滤NULL数据，引发数据缺失，需提前过滤集合NULL。

2. OR语句优化细节

  • 底层原理：OR最大问题是索引截断失效，OR前后字段索引不一致、或单侧无索引，直接整体索引失效走全表扫描。

  • 优化规范：OR查询的所有条件字段必须建立联合索引，保证所有条件均可命中索引；多条件OR可拆解为多条UNION查询，规避索引失效问题。

  • 经典坑点：WHERE name='张三' OR age=20，若仅name有索引、age无索引，整条SQL索引完全失效。

3. LIMIT 1 优化细节

  • 底层原理：LIMIT 1会告知优化器只需匹配一条数据，命中数据后立即终止扫描，减少IO开销。

  • 优化规范：精准唯一查询必须加LIMIT 1，避免全索引扫描；搭配精准索引条件使用，性能最优；禁止无索引条件下使用LIMIT 1，仍会触发全表扫描，仅减少返回行数，不减少扫描行数。

  • 高阶优化：主键精准查询可省略LIMIT 1，主键唯一约束天然保证仅一条数据，无性能损耗。

4. 面试高频考点

Q：IN查询什么时候会失效？OR语句核心优化方案？

A：IN失效场景：IN集合数据量过大、IN子查询字段无索引、包含NULL干扰数据。OR语句核心问题是单侧无索引导致整体失效，优化核心为统一条件字段索引、使用联合索引覆盖所有OR条件，复杂场景用UNION拆分查询，彻底规避全表扫描。

  • 禁止使用FLOAT、DOUBLE存储金额，精准小数业务统一使用DECIMAL类型，避免精度丢失。

  • 时间字段优先使用DATETIME，兼容可读性与时区，TIMESTAMP存在时间范围限制。

  • 所有业务字段添加备注，规范字段定义，便于后期维护与迭代。

4.8 缓冲池、脏页、刷盘机制（底层面试高频）

InnoDB缓冲池、脏页、刷盘机制是MySQL内存与磁盘交互的核心底层原理，解释了「为什么内存修改数据不用实时落盘、高并发写入如何提速、宕机如何恢复数据」，是中高级开发、运维核心面试考点，此前仅提及参数，本节完整补齐底层原理、淘汰策略、刷新机制与优化细节。

4.8.1 缓冲池核心原理

缓冲池（innodb_buffer_pool）是InnoDB最大的内存核心组件，是MySQL读写性能的核心保障，核心作用是缓存磁盘数据页与索引页，减少磁盘IO交互。MySQL所有数据读写优先操作内存缓冲池，而非直接操作磁盘，磁盘IO是机械操作，速度远慢于内存，缓冲池彻底规避了频繁磁盘读写的性能瓶颈。

1. 缓冲池缓存内容

  • 热点业务数据页、B+树索引页（核心缓存对象）；

  • undo log日志页、锁信息、事务数据；

  • 自适应哈希索引、缓存字典信息。

2. 缓冲池读写流程

  • 读流程：接收查询请求 → 优先查询缓冲池内存数据 → 命中直接返回，未命中则从磁盘加载数据页到缓冲池 → 返回数据。

  • 写流程：接收更新请求 → 修改缓冲池中的内存数据页 → 生成redo log日志 → 后台线程异步刷盘落盘，无需实时写磁盘。

4.8.2 脏页核心概念与产生原理

脏页（Dirty Page）：缓冲池中内存数据已修改、但未同步更新到磁盘的数据页。内存数据与磁盘数据不一致，因此称为脏页。

干净页：内存数据与磁盘数据完全一致，无修改、无需同步。

1. 脏页产生场景

所有数据更新、新增、删除操作均会产生脏页：事务修改缓冲池内存数据后，为提升性能，不会立即同步磁盘，等待后台线程批量刷盘，短暂时间内形成脏页。

2. 脏页核心特性

  • 脏页仅存在于缓冲池内存中，重启数据库未刷盘脏页会丢失，依托redo log恢复；

  • 脏页积累过多会占用大量内存，导致缓冲池可用空间不足，触发性能下降；

  • 脏页刷盘是批量异步操作，单次刷盘多页数据，极大提升写入性能。

4.8.3 脏页刷新策略（核心底层机制）

InnoDB有完善的脏页刷盘机制，分四种触发场景，按需将脏页同步至磁盘，平衡性能与数据安全，是面试高频考点。

1. 定时主动刷盘（常规场景）

后台page_cleaner线程默认每秒执行一次，主动扫描缓冲池，将少量老旧脏页批量刷入磁盘，常态化清理脏页，避免堆积，不影响业务读写性能。

2. 内存不足强制刷盘

缓冲池空闲内存不足、需要加载新数据页时，会优先挑选最老旧、最久未访问的脏页刷盘释放空间，保证新数据正常加载。

3. 脏页比例阈值刷盘

通过参数innodb_max_dirty_pages_pct控制（默认75%），当缓冲池脏页占比达到阈值，会触发强制快速刷盘，极速清理脏页，防止内存溢出。

4. 数据库关闭刷盘

MySQL正常关机时，会一次性将所有残留脏页全部刷入磁盘，保证内存与磁盘数据完全一致，无数据丢失。异常宕机则依赖redo log重启恢复数据。

4.8.4 LRU缓存淘汰机制（缓冲池核心算法）

缓冲池内存空间有限，无法缓存所有数据，InnoDB优化了传统LRU算法，实现冷热数据分离的改良LRU淘汰机制，精准保留热点数据、淘汰冷数据，最大化缓存命中率。

1. LRU链表结构

缓冲池LRU链表分为两部分：热数据区（5/8空间）、冷数据区（3/8空间）。

  • 热数据区：存放高频访问、热点业务数据，长期保留不轻易淘汰；

  • 冷数据区：存放低频访问、临时加载数据，优先淘汰释放空间。

2. 淘汰规则

  • 新加载的数据页优先放入冷数据区头部；

  • 冷数据区数据被再次访问，晋升至热数据区；

  • 缓冲池空间不足时，优先淘汰冷数据区尾部最久未访问的冷数据页；

  • 有效避免一次性全量加载数据冲刷热点缓存的问题。

4.8.5 缓冲池高阶优化细节（生产落地）

  • 内存配比优化：专属MySQL服务器，innodb_buffer_pool_size设置为物理内存50%-70%，最大化缓存热点数据，减少磁盘IO；多服务混布则适当降低配比，避免内存溢出。

  • 脏页阈值优化：高并发写入业务，可适当降低innodb_max_dirty_pages_pct阈值（调整为50%），常态化清理脏页，避免瞬时大量刷盘引发的性能抖动。

  • 多实例分片优化：大内存服务器开启缓冲池多实例，分散缓存压力，提升并发读写缓存效率。

  • 规避冷数据冲刷：避免业务一次性批量查询全表冷数据，防止冲刷热点缓存、降低缓存命中率。

4.8.6 高频面试考点

Q1：什么是脏页？脏页过多会引发什么问题？

A：脏页是缓冲池中已修改、未同步磁盘的数据页。脏页过多会导致：1. 缓冲池可用内存不足，缓存命中率下降，磁盘IO激增；2. 触发大批量集中刷盘，占用大量IO资源，引发业务读写卡顿、超时；3. 宕机后需要恢复的redo log数据增多，数据库重启耗时大幅增加。

Q2：InnoDB改良LRU算法的核心优势？

A：传统LRU算法会出现「批量冷数据冲刷热点数据」的问题，导致缓存命中率骤降。改良后的冷热分区LRU算法，将新数据放入冷区、热点数据保留在热区，优先淘汰冷数据，精准保护热点业务缓存，大幅提升高并发场景下的缓存利用率与查询性能。

Q3：缓冲池、脏页、redo log的协作关系？

A：数据修改优先更新缓冲池内存生成脏页，同时写入redo log日志保障数据持久性；后台线程异步刷新脏页到磁盘；若中途宕机，未刷盘的脏页数据可通过redo log重放恢复，三者协同实现「高性能内存读写+数据绝对安全」的平衡。

4.8.7 数据库核心参数调优

  • innodb_buffer_pool_size：InnoDB缓冲池，核心参数，用于缓存索引与数据页，生产环境服务器专属MySQL时，设置为物理内存的50%-70%，大幅减少磁盘IO。

  • max_connections：最大连接数，默认偏小，生产环境调整为1000-2000，适配高并发连接场景。

  • innodb_log_file_size：redo log日志大小，合理增大可减少日志刷新频率，提升写入性能。

4.8.8 规范注意事项

  • 参数调优必须结合服务器硬件配置，盲目调大内存参数会导致服务器内存溢出、服务宕机。

  • 参数修改后需重启MySQL服务，生产环境需在低峰期操作，做好备份与回滚方案。

  • 优先优化SQL与索引，参数调优仅作为辅助优化手段，不能解决本质性能问题。

4.8.1 核心内存参数

  • innodb_buffer_pool_size：InnoDB缓冲池，核心参数，用于缓存索引与数据页，生产环境服务器专属MySQL时，设置为物理内存的50%-70%，大幅减少磁盘IO。

  • max_connections：最大连接数，默认偏小，生产环境调整为1000-2000，适配高并发连接场景。

  • innodb_log_file_size：redo log日志大小，合理增大可减少日志刷新频率，提升写入性能。

4.8.2 规范注意事项

  • 参数调优必须结合服务器硬件配置，盲目调大内存参数会导致服务器内存溢出、服务宕机。

  • 参数修改后需重启MySQL服务，生产环境需在低峰期操作，做好备份与回滚方案。

  • 优先优化SQL与索引，参数调优仅作为辅助优化手段，不能解决本质性能问题。

第五章 线上故障与死锁排查实战

5.1 数据库死锁成因与排查

5.1.1 死锁概念

死锁是两个或多个事务互相持有对方所需的锁，同时等待对方释放锁，循环等待、互不释放，导致事务永久阻塞的线上严重故障，会直接引发接口超时、业务阻塞、数据库连接堆积。

5.1.2 死锁产生四大必要条件

  • 互斥条件：资源锁同一时间仅能被一个事务持有。

  • 请求保持：事务持有已有锁的同时，请求获取新的资源锁。

  • 不可剥夺：已持有锁不能被其他事务强制抢占，只能主动释放。

  • 循环等待：多个事务形成闭环锁等待链路。

5.1.3 死锁排查实操

# 查看最近一次死锁日志
SHOW ENGINE INNODB STATUS;

5.1.4 死锁解决方案与规避规范

  • 统一更新顺序：所有业务更新数据，统一按主键升序/降序执行，彻底打破循环等待条件。

  • 缩小事务粒度：事务内仅保留核心SQL，精简事务执行时间，减少锁持有时长。

  • 避免范围更新：杜绝大范围、无精准索引的更新删除，减少间隙锁与临键锁冲突。

  • 增加重试机制：代码层面捕获死锁异常，自动重试事务，规避偶发死锁问题。

5.2 连接数爆满与连接超时排查

5.2.1 故障成因

数据库连接数爆满、连接超时，核心诱因包括：慢SQL长时间占用连接、事务未及时提交、代码未释放数据库连接、max_connections参数配置过小等，会直接导致新业务请求无法连接数据库，服务瘫痪。

5.2.2 排查实操指令

# 查看当前所有数据库连接
SHOW PROCESSLIST;

# 查看最大连接数配置
SHOW VARIABLES LIKE 'max_connections';

# 杀死卡死的慢连接（id为查询到的连接ID）
KILL 连接ID;

5.2.3 优化规避方案

  • 开启连接超时自动释放参数，自动回收闲置、卡死连接。

  • 优化慢SQL，缩短SQL执行时间，快速释放连接资源。

  • 代码层面使用数据库连接池，合理配置最大最小连接数，避免连接泄露。

5.3 三大核心日志深度解析（redo/undo/binlog）

日志是MySQL实现事务、数据恢复、主从同步的底层核心，此前章节零散提及相关日志作用，本节完整补齐三大日志的原理、流程、差异及生产应用，是底层原理、面试、生产故障排查的必考核心。

5.3.1 undo log 回滚日志

1. 核心作用

undo log 是InnoDB专属日志，核心保障事务原子性与MVCC多版本并发控制。事务执行数据修改前，会先将数据原始版本写入undo log，若事务回滚，可通过该日志恢复数据原状；同时undo log存储的历史数据版本，是MVCC版本链的核心数据来源。

2. 工作流程

  • 事务执行INSERT/UPDATE/DELETE操作前，记录当前数据原始快照到undo log；

  • 事务正常提交，undo log不会立即删除，会保留用于支撑后续MVCC快照读；

  • 事务异常回滚，MySQL反向执行undo log中记录的逆向操作，撤销所有数据修改；

  • 后台purge线程会定期清理过期、无事务引用的undo log，释放磁盘空间。

3. 核心特性

  • 属于逻辑日志，记录的是SQL逆向逻辑，而非物理数据修改；

  • 仅InnoDB引擎支持，MyISAM无undo log，无法支持事务回滚；

  • 不落地磁盘实时刷盘，随事务生命周期生成与复用。

5.3.2 redo log 重做日志

1. 核心作用

redo log 是InnoDB专属日志，核心保障事务持久性，解决「内存修改数据未刷盘、服务器宕机导致数据丢失」的问题，是MySQL宕机自动恢复的核心依托。

2. WAL预写日志机制（核心原理）

MySQL采用WAL（预写日志）机制，核心逻辑：先写日志，后刷数据。事务修改数据时，先将修改内容写入redo log缓冲区，再刷新到磁盘日志文件，再异步将内存数据页刷入磁盘数据文件。即便中途宕机，重启后可通过redo log重放未落地的数据，保证事务数据不丢失。

3. 工作流程

  • 事务修改数据，内存缓冲区数据变更，未立即写入磁盘；

  • 同步写入redo log buffer（日志缓冲区）；

  • 事务提交，redo log buffer数据刷入磁盘redo log文件；

  • 后台线程异步将内存脏页刷入磁盘数据文件；

  • 宕机重启后，系统扫描redo log，重放未落地的事务数据，完成数据恢复。

4. 核心参数与生产规范

innodb_log_file_size：redo log单文件大小，生产建议设置为1G-2G，减少日志频繁切换刷盘的性能损耗；日志文件过大则宕机恢复耗时增加，需平衡配置。

5.3.3 binlog 二进制日志

1. 核心作用

binlog（二进制日志）是MySQL Server层通用日志，所有存储引擎均支持，核心作用：数据备份恢复、主从复制、数据审计。记录数据库所有增删改操作的日志，不记录查询语句。

2. 三种日志格式（生产核心选型）

  • STATEMENT（语句模式）：记录执行的原始SQL语句。优点：日志体积小、占用磁盘少；缺点：存在时间函数、随机函数等上下文依赖问题，主从同步易出现数据不一致，生产基本淘汰。

  • ROW（行模式，生产默认）：记录数据修改前后的行数据详情。优点：精准记录数据变更，无数据不一致问题，适配所有业务场景；缺点：日志体积偏大，批量更新会生成大量日志。

  • MIXED（混合模式）：自动兼容STATEMENT和ROW模式，普通SQL用语句模式，特殊风险SQL用行模式，折中方案，中低并发业务可选用。

3. 核心应用场景

  • 线上误删、误更新数据，通过binlog日志精准恢复指定时间段数据；

  • 主从架构中，主库通过binlog同步数据到从库，实现读写分离；

  • 记录所有数据变更操作，用于安全审计、操作溯源。

5.3.4 三大日志核心区别与协作机制（面试必考）

日志类型归属层级核心作用生命周期日志类型
undo logInnoDB引擎层事务回滚、MVCC版本链事务提交后延迟清理逻辑日志
redo logInnoDB引擎层宕机数据恢复、保障持久性数据落盘后复用覆盖物理逻辑日志
binlogServer公共层数据恢复、主从复制、审计手动/定时清理归档逻辑日志

完整协作流程：事务执行先写undo log（备份原始数据）→ 内存数据变更 → 写redo log buffer → 事务提交刷盘redo log → 写入binlog日志 → 异步刷盘数据文件，三大日志共同保障事务ACID特性与数据安全。

5.4 SQL完整执行全链路流程（面试必背）

补齐MySQL从接收请求到返回结果的完整执行链路，打通Server层与引擎层协作逻辑，解决「SQL到底如何执行」的底层认知盲区。

5.4.1 完整执行步骤

  1. 连接校验：客户端发起连接，连接层校验账号密码、IP权限、连接池状态，建立有效连接；

  2. 查询缓存校验（8.0废弃）：5.7及以下版本优先校验缓存是否存在结果，8.0直接跳过该步骤；

  3. 语法解析：解析器校验SQL语法合法性、关键字合规性，生成抽象语法树，拦截非法SQL；

  4. 预处理：校验表、字段、权限是否合法，处理SQL占位符与参数；

  5. 优化器优化：自动选择最优索引、最优JOIN顺序、最优执行方案，生成执行计划；

  6. 执行器执行：调用对应存储引擎接口，校验事务、锁状态，发起数据读写请求；

  7. 引擎层读写：InnoDB通过B+树索引检索数据，命中缓存或磁盘数据，触发MVCC读视图、锁机制、日志写入；

  8. 结果返回：封装查询结果，返回给客户端，完成事务提交或回滚，释放资源。

5.5 索引碎片整理（生产实操优化）

5.5.1 碎片成因

数据表频繁增删改、大范围删除数据、无序主键写入，会导致B+树节点分裂、合并，产生大量页空洞、无效索引空间，即为索引碎片。碎片过多会导致：索引体积变大、查询IO增加、读写性能下降、磁盘空间浪费。

5.5.2 碎片产生高频场景

  • 大批量DELETE删除表数据，仅删除数据不回收页空间；

  • 使用UUID无序主键，频繁触发页分裂；

  • 频繁更新索引字段，导致索引节点频繁变动。

5.5.3 碎片整理实操指令与规范

# 整理单表索引碎片（InnoDB专属）
OPTIMIZE TABLE 表名;
生产规范

  • 碎片整理会锁表，禁止业务高峰期执行，必须凌晨低峰期操作；

  • 单表碎片率超过30%时执行整理，低碎片率无需操作，避免无效锁表；

  • 大表整理耗时久，提前做好业务停机、备份预案。

5.6 主从复制与读写分离（生产架构核心）

5.6.1 核心概念

主从复制是企业MySQL高可用架构的基础，通过一台主库（Master）、多台从库（Slave）实现数据同步，基于binlog日志完成数据同步，搭配读写分离可大幅提升数据库并发吞吐量，规避单库性能瓶颈。

5.6.2 主从复制完整流程

  1. 主库开启binlog日志，所有数据增删改操作均记录到binlog；

  2. 从库IO线程连接主库，拉取主库binlog日志，写入本地relay log（中继日志）；

  3. 从库SQL线程读取本地中继日志，重放日志中的SQL逻辑，同步主库数据；

  4. 最终实现主从数据一致性，完成数据同步。

5.6.3 主从延迟成因与解决方案

1. 延迟核心成因

  • 主库写入并发量大，binlog生成速度远超从库重放速度；

  • 从库硬件配置低于主库，IO、CPU性能不足；

  • 大事务、批量写入产生超大binlog日志，从库重放耗时久；

  • 单线程SQL重放（MySQL5.7默认），无法并行同步。

2. 生产优化方案

  • MySQL8.0开启并行复制，提升从库日志重放效率；

  • 拆分大事务、批量写入，避免超大binlog日志生成；

  • 统一主从硬件配置，保证从库性能匹配主库；

  • 规避大批量DELETE/UPDATE操作，减少日志同步压力。

5.6.4 读写分离架构与落地规范

架构逻辑：写请求全部路由至主库，读请求分摊至多个从库，分担主库压力，提升并发查询能力。

生产避坑：主从存在短暂延迟，核心实时查询、新增后立即查询的场景，必须查主库，避免读取从库过期数据。

5.7 线上数据误删/误更新恢复实操（救命技能）

5.7.1 恢复原理

依托binlog日志精准恢复数据，binlog记录了所有数据变更的完整语句，可精准定位误操作时间段、操作语句，回滚误删、误更新数据，是生产数据事故的核心补救手段。

5.7.2 实操恢复流程

  • 立即停止业务写入，避免新数据覆盖日志位点；

  • 定位误操作的开始、结束时间，筛选对应时间段binlog日志；

  • 解析binlog日志，提取误操作前的原始数据；

  • 反向执行恢复语句，还原数据；

  • 校验数据完整性，恢复业务访问。

5.7.3 核心实操指令

# 查看指定时间段binlog日志内容
mysqlbinlog --start-datetime="2026-01-01 00:00:00" --stop-datetime="2026-01-01 23:59:59" 日志文件路径

# 导出binlog日志为SQL文件，用于数据恢复
mysqlbinlog 日志文件路径 > recovery.sql

5.8 线上高危故障全套排查链路（补齐缺失运维体系）

5.8.1 CPU 100% 故障排查流程

  • 通过top指令确认MySQL进程CPU占比；

  • 开启慢查询日志，抓取高频耗时SQL；

  • EXPLAIN分析SQL，定位全表扫描、文件排序、临时表问题；

  • 优化索引、改写低效SQL、拆分大查询；

  • 监控CPU回落，验证优化效果。

5.8.2 磁盘IO打满故障排查流程

  • 查看磁盘读写占用，确认MySQL日志、数据文件IO消耗；

  • 排查大批量写入、大事务刷盘、日志频繁切换问题；

  • 优化批量写入逻辑、拆分大事务、调整日志参数；

  • 清理冗余日志、归档冷数据，释放磁盘IO资源。

5.8.3 锁超时/锁等待排查完整流程

  • 执行SHOW ENGINE INNODB STATUS查看锁等待、死锁日志；

  • 定位阻塞源头事务、锁类型（行锁/间隙锁/表锁）；

  • 排查无索引更新、大范围锁、无序更新问题；

  • 杀死阻塞卡死事务，临时恢复业务；

  • 优化SQL与事务逻辑，永久规避锁冲突。

第六章 高频面试总结与生产规范手册

6.1 索引核心面试必背

  • 索引结构选用B+树的核心原因：层级少、IO少、有序适配范围查询、节点轻量化。

  • 聚簇索引与二级索引区别：聚簇索引存整行数据，二级索引存索引列+主键，存在回表。

  • 索引失效核心场景：模糊首尾匹配、索引列运算、隐式转换、最左前缀不匹配、非等值查询。

  • 覆盖索引原理：索引包含所有查询字段，消除回表，提升查询性能。

6.2 事务与锁高频面试

  • ACID四大特性底层保障：原子性undo log、持久性redo log、隔离性锁+MVCC、一致性综合保障。

  • 四大隔离级别差异与解决问题：读未提交、读已提交、可重复读、串行化。

  • MVCC实现原理：隐藏字段+undo log版本链+Read View视图，实现无锁快照读。

  • 行锁、间隙锁、临键锁触发场景与死锁规避方案。

6.3 生产开发强制规范

  • 所有业务表必须主键自增、InnoDB引擎、utf8mb4编码，标配创建、更新时间。

  • UPDATE/DELETE必须带精准索引条件，禁止全表操作。

  • 禁止SELECT *，按需查询字段，杜绝索引失效SQL上线。

  • 大字段分表存储，冷热数据分离，单表数据量控制在千万级以内。

  • 慢查询日志常态化开启，定期优化Top慢SQL，保障数据库性能稳定。

  • 事务精简粒度、统一更新顺序，规避死锁与长事务问题。

6.4 线上高频故障复盘总结

6.4.1 接口超时故障TOP5

  • 慢SQL阻塞：索引失效、大偏移分页、全表更新删除，导致单条SQL执行秒级甚至分钟级，长期占用数据库连接，引发批量接口超时。解决方案：常态化监控慢查询日志，上线前EXPLAIN校验所有业务SQL，杜绝全表操作与无效索引SQL。

  • 长事务堆积：事务内嵌套大量业务逻辑、冗余SQL、循环查询，事务执行时间过长，锁资源不释放，引发锁等待、连接堆积。解决方案：最小化事务粒度，仅保留数据库操作，剥离业务计算逻辑，缩短锁持有时间。

  • 死锁阻塞：多事务无序更新、范围更新触发间隙锁，形成循环锁等待，导致事务卡死。解决方案：统一数据表更新顺序，规避大范围无索引更新，代码增加死锁重试机制。

  • 连接数爆满：连接泄露、闲置连接不释放、慢SQL常驻连接，耗尽数据库最大连接数，新请求无法接入。解决方案：配置合理连接池参数、开启连接超时回收、定期查杀卡死连接。

  • 数据库CPU飙升：高频全表扫描、大量文件排序、临时表生成，占用超高CPU资源，数据库整体吞吐量下降。解决方案：优化排序索引、杜绝索引失效SQL、拆分超大复杂查询。

6.4.2 数据异常故障汇总

  • 脏数据写入：未开启事务、事务隔离级别过低、并发更新无锁控制，导致数据覆盖、数据错乱。规避：核心数据更新必须开启事务，精准索引条件更新，保障数据一致性。

  • 数据丢失：随意执行DROP/DELETE、无备份删数据、误操作全表删除。规避：生产禁止物理删除，统一逻辑删除，高危操作提前备份、低峰执行、双人复核。

  • 乱码问题：库表字符集非utf8mb4，无法兼容表情与特殊符号。规避：所有库、表、字段统一固化utf8mb4字符集。

6.5 索引设计黄金规范（生产落地版）

6.5.1 索引创建原则

  • 高频优先原则：仅为高频查询、统计、排序、分页字段建索引，低频字段无需建索引，减少索引维护开销。

  • 离散度优先原则：优先为离散度高、区分度大的字段建立索引，如手机号、账号、唯一编码，性别、状态等低离散字段不适合单独建索引。

  • 联合索引有序原则：联合索引字段顺序严格遵循「等值字段在前、范围字段在后」，贴合业务查询逻辑，保证最左前缀生效。

  • 精简冗余原则：避免重复索引、冗余索引，新建索引前校验已有索引能否复用，控制单表索引数量，单表索引不超过5个。

6.5.2 索引禁用场景

  • 数据量百级以内的小表，无需建立索引，全表扫描效率高于索引检索。

  • 频繁增删改、极少查询的字段，建索引会大幅提升写入开销，无优化收益。

  • 低离散、固定枚举值字段，单独索引命中率极低，无优化意义。

6.6 高频面试真题完整版（含标准答案）

6.6.1 基础原理类真题

Q1：InnoDB和MyISAM的核心区别，生产如何选型？

A：核心区别四点：①事务：InnoDB支持完整ACID事务，MyISAM不支持；②锁机制：InnoDB行级锁，并发性能高，MyISAM表级锁，并发阻塞严重；③并发控制：InnoDB支持MVCC无锁读，适配高并发，MyISAM无并发优化；④数据安全：InnoDB支持宕机自动恢复，MyISAM易数据损坏。生产统一选用InnoDB，MyISAM仅适用于静态离线日志场景。

Q2：为什么InnoDB表必须有主键，推荐自增主键？

A：InnoDB聚簇索引依托主键构建，无主键会生成隐藏ROW_ID，索引无序、效率低，且破坏MVCC版本链。自增主键有序写入，可有效避免页分裂、索引碎片，写入性能稳定，占用空间小、检索效率高；UUID无序主键会频繁触发页分裂，严重损耗数据库性能。

6.6.2 索引优化类真题

Q3：联合索引最左前缀原则原理及失效场景？

A：联合索引基于B+树按字段顺序有序排序，查询必须匹配最左侧首个字段，后续字段才能生效。失效场景：跳过最左字段查询、查询字段顺序与索引顺序不匹配、最左字段条件失效，均会导致整个联合索引失效。

Q4：覆盖索引和回表查询的关系，如何彻底优化回表？

A：二级索引仅存储索引列+主键，查询非索引字段需要通过主键二次查询聚簇索引，即为回表，会增加磁盘IO。覆盖索引将所有查询字段纳入二级索引，无需回表，是优化回表问题的最优方案，高频热点查询优先使用覆盖索引。

6.6.3 事务与锁类真题

Q5：MVCC实现原理，解决了什么问题？

A：MVCC即多版本并发控制，依托行隐藏字段、undo log版本链、Read View视图三大核心实现。通过读取数据历史快照实现无锁读，解决了高并发读场景下的锁竞争问题，在保证事务隔离性的同时，大幅提升数据库并发读写性能，适用于RC、RR隔离级别快照读。

Q6：死锁产生的条件及生产规避方案？

A：死锁四大必要条件：互斥、请求保持、不可剥夺、循环等待。生产规避：统一多表更新顺序、缩小事务粒度、规避大范围索引区间操作、代码增加死锁自动重试机制。

6.6.4 生产优化类真题

Q7：大偏移量LIMIT分页性能差的原因与优化方案？

A：原因：MySQL分页会前置扫描所有前置数据，大偏移量需要扫描并丢弃大量无效数据，IO开销极高。优化：使用主键WHERE条件分页，通过主键阈值直接定位分页起始位置，无需扫描无效数据，性能提升显著。适用于所有有序、主键连续的分页场景，是生产标准优化方案。

Q8：线上慢SQL排查完整流程是什么？

A：①开启慢查询日志，抓取超时SQL；②使用EXPLAIN分析执行计划，定位索引失效、全表扫描、文件排序等问题；③优化SQL语句、调整索引结构、改写低效语法；④线上灰度发布，验证优化效果；⑤持续监控日志，确认问题彻底解决。

Q9：redo log、undo log、binlog三者区别与协作关系？

A：1. 归属与作用：undo log、redo log为InnoDB引擎层日志，前者保障事务原子性与MVCC，后者保障事务持久性；binlog为Server层通用日志，用于数据恢复、主从复制、审计。2. 日志类型：undo、binlog为逻辑日志，redo log为物理逻辑日志。3. 协作流程：事务先写undo log备份原始数据，再修改内存数据、写入redo log，事务提交后写入binlog，最终异步落地磁盘数据，三者协同支撑MySQL事务与数据安全体系。

Q10：主从延迟的核心成因与生产解决方案？

A：核心成因：主库写入并发高、binlog生成快，从库单线程重放效率不足；大事务、批量写入产生超大日志；从库硬件性能弱于主库。解决方案：开启从库并行复制、拆分超大事务、避免批量数据操作、匹配主从硬件配置、优化binlog日志格式，同时核心实时查询走主库，规避延迟数据问题。

Q7：大偏移量LIMIT分页性能差的原因与优化方案？

A：原因：MySQL分页会前置扫描所有前置数据，大偏移量需要扫描并丢弃大量无效数据，IO开销极高。优化：使用主键WHERE条件分页，通过主键阈值直接定位分页起始位置，无需扫描无效数据，性能提升显著。

Q8：线上慢SQL排查完整流程是什么？

A：①开启慢查询日志，抓取超时SQL；②使用EXPLAIN分析执行计划，定位索引失效、全表扫描、文件排序等问题；③优化SQL语句、调整索引结构、改写低效语法；④线上灰度发布，验证优化效果；⑤持续监控日志，确认问题彻底解决。

6.7 终极生产红线规范（绝对禁止）

  • 禁止生产执行无WHERE条件的UPDATE、DELETE全表操作。

  • 禁止上线带索引失效、全表扫描、文件排序的低效SQL。

  • 禁止使用root账号运行业务程序，禁止开放数据库全局远程访问。

  • 禁止大字段、超大文本存入业务主表，禁止单表数据量超千万不做拆分。

  • 禁止长事务、大事务，禁止事务内嵌套复杂业务逻辑与循环查询。

  • 禁止随意修改数据库核心参数、随意删除业务数据与数据表。

  • 禁止使用FLOAT/DOUBLE存金额、禁止数据库字段默认NULL、禁止乱建冗余索引。

附录：MySQL常用命令速查手册

附录1：数据库状态查询指令

# 查看数据库版本
SELECT VERSION();
# 查看当前登录用户
SELECT USER();
# 查看当前数据库
SELECT DATABASE();
# 查看所有数据库
SHOW DATABASES;
# 查看数据库下所有数据表
SHOW TABLES;
# 查看表结构
DESC 表名;
# 查看表创建语句
SHOW CREATE TABLE 表名;

附录2：性能状态监控指令

# 查看慢查询配置
SHOW VARIABLES LIKE '%slow_query%';
# 查看事务隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';
# 查看存储引擎配置
SHOW VARIABLES LIKE 'default_storage_engine';
# 查看当前数据库连接
SHOW PROCESSLIST;
# 查看InnoDB引擎状态（死锁排查）
SHOW ENGINE INNODB STATUS;

附录3：生产常用优化指令

# 开启慢查询日志
SET GLOBAL slow_query_log = ON;
# 设置慢查询阈值1秒
SET GLOBAL long_query_time = 1;
# 刷新权限
FLUSH PRIVILEGES;
# 杀死卡死连接
KILL 连接ID;
# 分析SQL执行计划
EXPLAIN SQL语句;
avatar
文章
阅读
粉丝