SQL篇——【MySQL篇：SQL理论】索引优化、锁机制与 InnoDB
本文系统介绍了MySQL的核心技术，主要内容包括：1.存储引擎：对比InnoDB、MyISAM、Memory的特点及适用场景，重点分析InnoDB的事务支持、行级锁等特性。2.索引优化：详解B+Tree结构原理，索引分类（主键、唯一、联合等），使用原则（最左前缀、覆盖索引等）及失效场景。3.SQL性能优化：包括插入数据、order by、group by、limit等操作的优化方法，以及执行计划分
目录
本文系统介绍了MySQL的核心技术，主要内容包括：
1.存储引擎：对比InnoDB、MyISAM、Memory的特点及适用场景，重点分析InnoDB的事务支持、行级锁等特性。
2.索引优化：详解B+Tree结构原理，索引分类（主键、唯一、联合等），使用原则（最左前缀、覆盖索引等）及失效场景。
3.SQL性能优化：包括插入数据、order by、group by、limit等操作的优化方法，以及执行计划分析技巧。
4.高级特性：视图、存储过程、触发器的使用，锁机制（全局锁、表锁、行锁）及事务原理（redo log、undo log、MVCC）。
5.管理工具：介绍常用系统数据库和命令行工具（mysqladmin、mysqldump等）。学习重点在于理解存储引擎差异、索引原理和SQL优化技巧，并通过实践掌握性能调优方法。
1. 存储引擎
1.1 MySQL体系结构（四层）
-
连接层：处理客户端连接、授权认证、安全方案
-
服务层：SQL接口、查询缓存、SQL解析优化、内置函数
-
引擎层：真正负责数据的存储和提取（插件式架构）
-
存储层：将数据存储在文件系统上
核心思想：MySQL采用插件式存储引擎架构，可根据业务需求选择合适的存储引擎。
1.2 存储引擎介绍
-
概念：存储数据、建立索引、更新/查询数据等技术的实现方式
-
基于表而非库：不同表可以选择不同的存储引擎
-
指定存储引擎：建表时通过
ENGINE指定
-- 创建表时指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型,
......
) ENGINE = INNODB;
-- 查询当前数据库支持的存储引擎
SHOW ENGINES;
-- 示例：创建MyISAM表和Memory表
CREATE TABLE my_myisam(
id int,
name varchar(10)
) ENGINE = MyISAM;
CREATE TABLE my_memory(
id int,
name varchar(10)
) ENGINE = Memory;1.3 存储引擎特点对比
InnoDB（默认存储引擎）
-
特点：
-
支持事务（ACID）
-
支持行级锁
-
支持外键约束
-
支持崩溃恢复
-
-
文件：
表名.ibd（存储表结构、数据、索引） -
逻辑存储结构：
表空间 → 段 → 区（1M） → 页（16KB） → 行
MyISAM
-
特点：
-
不支持事务、外键
-
支持表锁
-
访问速度快
-
-
文件：
-
表名.sdi：表结构信息 -
表名.MYD：数据文件 -
表名.MYI：索引文件
-
Memory
-
特点：
-
数据存储在内存中
-
默认使用Hash索引
-
适合临时表/缓存
-
-
文件：
表名.sdi（只存储表结构）
三大存储引擎对比
| 特点 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 事务安全 | 支持 | - | - |
| 锁机制 | 行锁、表锁 | 表锁 | 表锁 |
| 外键支持 | 支持 | - | - |
| 存储限制 | 64TB | 有 | 有 |
| 内存使用 | 高 | 低 | 中等 |
1.4 存储引擎选择原则
-
InnoDB：需要事务、并发控制、外键约束（大多数场景）
-
MyISAM：以读操作为主，很少更新（历史数据归档）
-
Memory：临时表、缓存表（数据量小，重启丢失）
2. 索引
2.1 索引概述
-
定义：帮助MySQL高效获取数据的有序数据结构
-
作用：提高查询效率，类似书的目录
无索引 vs 有索引
-
无索引：全表扫描，性能低
-
有索引：快速定位数据
2.2 索引结构
2.2.1 常见索引结构
| 索引结构 | 描述 | 支持引擎 |
|---|---|---|
| B+Tree | 最常见的索引类型 | InnoDB、MyISAM、Memory |
| Hash | 哈希表实现，精确匹配快 | Memory |
| R-tree | 空间索引，地理数据 | MyISAM |
| Full-text | 全文索引，文本搜索 | InnoDB（5.6+）、MyISAM |
2.2.2 为什么使用B+Tree？
-
相对于二叉树：层级更少，搜索效率高
-
相对于B-Tree：非叶子节点只存索引，不存数据，一页可存更多键值
-
相对于Hash索引：支持范围查询和排序
2.2.3 B+Tree结构特点
-
所有数据存储在叶子节点
-
叶子节点形成双向链表（便于范围查询）
-
非叶子节点只存索引，不存数据
2.3 索引分类
按功能分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对主键创建 | 默认自动创建，唯一且非空 | PRIMARY |
| 唯一索引 | 保证字段值唯一 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位数据 | 可以有多个 | - |
| 全文索引 | 文本关键词搜索 | 可以有多个 | FULLTEXT |
按存储形式分类（InnoDB）
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引 | 数据和索引存储在一起 | 必须有且只有一个（主键） |
| 二级索引 | 数据和索引分开存储 | 可以有多个，叶子节点存主键值 |
聚集索引选取规则：
-
有主键 → 主键索引
-
无主键，有唯一索引 → 第一个唯一索引
-
都没有 → 自动生成rowid作为隐藏聚集索引
回表查询：通过二级索引找到主键，再通过聚集索引获取完整数据
2.4 索引语法
创建索引
-- 创建索引
CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名 (字段名, ...);
-- 示例
CREATE INDEX idx_user_name ON tb_user(name);
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession, age, status);查看索引
SHOW INDEX FROM 表名;删除索引
DROP INDEX 索引名 ON 表名;2.5 SQL性能分析
2.5.1 SQL执行频率
-- 查看当前数据库INSERT、UPDATE、DELETE、SELECT的访问频次
SHOW GLOBAL STATUS LIKE 'Com_______';2.5.2 慢查询日志
-- 查看慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log';
-- 开启慢查询（在配置文件中设置）
slow_query_log=1
long_query_time=2 -- SQL执行超过2秒视为慢查询2.5.3 profile详情
-- 查看是否支持profile
SELECT @@have_profiling;
-- 开启profile
SET profiling = 1;
-- 查看所有SQL耗时
SHOW PROFILES;
-- 查看指定SQL各阶段耗时
SHOW PROFILE FOR QUERY query_id;2.5.4 EXPLAIN执行计划
-- 查看SQL执行计划
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;执行计划字段含义：
-
id：查询序列号，id越大越先执行
-
type：连接类型（性能：const > eq_ref > ref > range > index > all）
-
key：实际使用的索引
-
rows：预估扫描行数
-
Extra：额外信息（Using index、Using where等）
2.6 索引使用原则
2.6.1 最左前缀法则
规则：联合索引，查询从最左列开始，不跳过索引中的列
-- 联合索引(profession, age, status)
-- 生效：profession、profession+age、profession+age+status
-- 失效：age、status、age+status
EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' AND age = 31;
EXPLAIN SELECT * FROM tb_user WHERE age = 31 AND status = '0'; -- 失效2.6.2 索引失效情况
-
索引列运算：在索引列上进行计算、函数、类型转换
SELECT * FROM tb_user WHERE SUBSTRING(phone, 10, 2) = '15'; -- 失效 -
字符串不加引号：隐式类型转换导致索引失效
SELECT * FROM tb_user WHERE phone = 17799990015; -- 失效（应为字符串） -
模糊查询：头部模糊匹配导致索引失效
SELECT * FROM tb_user WHERE profession LIKE '软件%'; -- 生效 SELECT * FROM tb_user WHERE profession LIKE '%工程'; -- 失效 -
or连接：or前后字段都要有索引，否则失效
SELECT * FROM tb_user WHERE id = 10 OR age = 23; -- age无索引则全失效 -
数据分布影响：MySQL评估全表扫描更快时，不走索引
2.6.3 SQL提示
-- 建议使用指定索引
SELECT * FROM tb_user USE INDEX(idx_user_pro) WHERE profession = '软件工程';
-- 忽略指定索引
SELECT * FROM tb_user IGNORE INDEX(idx_user_pro) WHERE profession = '软件工程';
-- 强制使用索引
SELECT * FROM tb_user FORCE INDEX(idx_user_pro) WHERE profession = '软件工程';2.6.4 覆盖索引
概念：查询使用了索引，且需要返回的列在索引中都能找到
优势：避免回表查询，提高性能
-- 覆盖索引示例（id, profession在索引中）
SELECT id, profession FROM tb_user WHERE profession = '软件工程' AND age = 31;2.6.5 前缀索引
适用场景：字符串类型字段较长时，只索引部分前缀
-- 为email字段创建前缀索引（前5个字符）
CREATE INDEX idx_email_5 ON tb_user(email(5));2.6.6 单列索引 vs 联合索引
-
单列索引：一个索引只包含单个列
-
联合索引：一个索引包含多个列
建议：多条件查询时，尽量使用联合索引，减少回表
2.7 索引设计原则
-
数据量大、查询频繁的表建立索引
-
常作为查询条件、排序、分组的字段建立索引
-
选择区分度高的列作为索引
-
字符串字段较长可建前缀索引
-
尽量使用联合索引，减少单列索引
-
控制索引数量（不是越多越好）
-
索引列尽量设置为NOT NULL
3. SQL优化
3.1 插入数据优化
-- 1. 批量插入（减少连接开销）
INSERT INTO tb_test VALUES(1,'Tom'),(2,'Cat'),(3,'Jerry');
-- 2. 手动提交事务（减少事务开销）
START TRANSACTION;
INSERT ...;
COMMIT;
-- 3. 主键顺序插入（性能高于乱序）
-- 4. 大批量数据使用LOAD指令
LOAD DATA LOCAL INFILE '文件路径' INTO TABLE 表名;3.2 主键优化
-
页分裂：乱序插入导致页分裂，影响性能
-
页合并：删除记录达到阈值（MERGE_THRESHOLD）时合并页
-
设计原则：
-
满足业务需求下，降低主键长度
-
选择顺序插入，使用AUTO_INCREMENT
-
避免使用UUID、身份证号等自然主键
-
避免修改主键
-
3.3 order by优化
-
Using filesort：通过排序缓冲区排序，性能低
-
Using index：通过索引直接返回有序数据，性能高
-- 创建索引支持排序
CREATE INDEX idx_user_age_phone ON tb_user(age, phone);
-- 遵循最左前缀法则
SELECT * FROM tb_user ORDER BY age, phone; -- 生效
SELECT * FROM tb_user ORDER BY phone, age; -- 可能失效3.4 group by优化
-- 使用索引提高分组效率
CREATE INDEX idx_pro_age_sta ON tb_user(profession, age, status);
-- 分组也遵循最左前缀法则
SELECT profession, COUNT(*) FROM tb_user GROUP BY profession; -- 生效
SELECT age, COUNT(*) FROM tb_user GROUP BY age; -- 可能Using temporary3.5 limit优化
-- 大数据量分页优化（覆盖索引+子查询）
SELECT * FROM tb_sku t,
(SELECT id FROM tb_sku ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;3.6 count优化
效率排序：count(字段) < count(主键) < count(1) ≈ count(*)
建议：尽量使用count(*)
3.7 update优化
注意：InnoDB的行锁是针对索引加的锁，如果更新的字段没有索引，行锁会升级为表锁
-- 有索引：行锁
UPDATE course SET name = 'javaEE' WHERE id = 1;
-- 无索引：可能升级为表锁
UPDATE course SET name = 'SpringBoot' WHERE name = 'PHP'; -- name无索引4. 视图/存储过程/触发器
4.1 视图
4.1.1 视图概念
-
虚拟表，只保存查询SQL逻辑，不保存查询结果
-
数据来自基表，使用时动态生成
4.1.2 视图语法
-- 创建视图
CREATE [OR REPLACE] VIEW 视图名称 AS SELECT语句;
-- 查询视图
SELECT * FROM 视图名称;
-- 修改视图
ALTER VIEW 视图名称 AS SELECT语句;
-- 删除视图
DROP VIEW [IF EXISTS] 视图名称;4.1.3 检查选项
-
CASCADED：级联检查，检查当前视图和依赖视图的条件
-
LOCAL：本地检查，只检查当前视图条件
4.1.4 视图更新条件
视图不可更新情况：
-
包含聚合函数
-
包含DISTINCT
-
包含GROUP BY
-
包含HAVING
-
包含UNION
4.1.5 视图作用
-
简化复杂查询
-
数据安全（隐藏敏感字段）
-
数据独立（屏蔽表结构变化）
4.2 存储过程
4.2.1 存储过程概念
-
预编译的SQL语句集合
-
可接受参数、返回数据
-
减少网络交互，提高效率
4.2.2 基本语法
-- 创建存储过程
DELIMITER $$ -- 修改结束符
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
-- SQL语句
END$$
DELIMITER ; -- 改回结束符
-- 调用存储过程
CALL 存储过程名称([参数]);
-- 查看存储过程
SHOW CREATE PROCEDURE 存储过程名称;
-- 删除存储过程
DROP PROCEDURE [IF EXISTS] 存储过程名称;4.2.3 变量
-- 系统变量（服务器提供）
SHOW [SESSION|GLOBAL] VARIABLES; -- 查看
SET [SESSION|GLOBAL] 系统变量名=值; -- 设置
-- 用户定义变量（@变量名）
SET @var_name = 值;
SELECT @var_name;
-- 局部变量（在BEGIN...END中声明使用）
DECLARE 变量名 数据类型 [DEFAULT 默认值];
SET 变量名 = 值;4.2.4 参数
| 类型 | 含义 | 备注 |
|---|---|---|
| IN | 输入参数 | 默认 |
| OUT | 输出参数 | 可作为返回值 |
| INOUT | 输入输出参数 | 既传入又传出 |
-- 带参数的存储过程
CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
-- SQL语句
END;4.2.5 流程控制
-- IF判断
IF 条件 THEN
...
ELSEIF 条件2 THEN
...
ELSE
...
END IF;
-- CASE
CASE
WHEN 条件1 THEN ...
WHEN 条件2 THEN ...
ELSE ...
END CASE;
-- 循环（WHILE、REPEAT、LOOP）
WHILE 条件 DO
...
END WHILE;
REPEAT
...
UNTIL 条件
END REPEAT;
LOOP
...
IF 条件 THEN LEAVE 标签; END IF;
END LOOP;4.2.6 游标
-- 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
-- 打开游标
OPEN 游标名称;
-- 获取游标数据
FETCH 游标名称 INTO 变量[, 变量];
-- 关闭游标
CLOSE 游标名称;4.2.7 条件处理程序
-- 定义条件处理程序
DECLARE 处理动作 HANDLER FOR 条件 处理语句;
-- 示例：当NOT FOUND时关闭游标并退出
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE 游标名称;4.3 存储函数
-- 创建存储函数
CREATE FUNCTION 函数名称(参数列表)
RETURNS 返回值类型 [characteristic]
BEGIN
-- SQL语句
RETURN 返回值;
END;
-- 调用存储函数
SELECT 函数名称(参数);4.4 触发器
4.4.1 触发器概念
-
在insert/update/delete之前或之后自动执行
-
保证数据完整性、日志记录、数据校验
4.4.2 触发器语法
-- 创建触发器
CREATE TRIGGER 触发器名称
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名 FOR EACH ROW
BEGIN
-- 触发器逻辑
END;
-- 查看触发器
SHOW TRIGGERS;
-- 删除触发器
DROP TRIGGER [数据库名.]触发器名称;4.4.3 NEW和OLD
| 触发器类型 | NEW | OLD |
|---|---|---|
| INSERT | 新增的数据 | NULL |
| UPDATE | 修改后的数据 | 修改前的数据 |
| DELETE | NULL | 删除前的数据 |
5. 锁
5.1 锁分类（按粒度）
-
全局锁：锁定整个数据库实例
-
表级锁：锁定整张表
-
行级锁：锁定某一行记录
5.2 全局锁
-- 加全局锁（整个数据库只读）
FLUSH TABLES WITH READ LOCK;
-- 释放锁
UNLOCK TABLES;应用场景：全库逻辑备份
缺点：业务停摆，主从延迟
替代方案：mysqldump --single-transaction（不加锁备份）
5.3 表级锁
5.3.1 表锁
-- 加表锁
LOCK TABLES 表名 READ; -- 读锁
LOCK TABLES 表名 WRITE; -- 写锁
-- 释放锁
UNLOCK TABLES;特点：
-
读锁：阻塞其他客户端的写，不阻塞读
-
写锁：阻塞其他客户端的读和写
5.3.2 元数据锁（MDL）
-
系统自动控制，维护表元数据一致性
-
避免DML与DDL冲突
锁类型：
-
增删改查：MDL读锁（共享）
-
修改表结构：MDL写锁（排他）
5.3.3 意向锁
-
意向共享锁（IS）：与表读锁兼容，与表写锁互斥
-
意向排他锁（IX）：与表读锁、写锁都互斥
作用：避免行锁与表锁冲突，提高加表锁效率
5.4 行级锁
5.4.1 行锁类型
-
行锁（Record Lock）：锁定单行记录
-
间隙锁（Gap Lock）：锁定索引记录间隙
-
临键锁（Next-Key Lock）：行锁+间隙锁组合
5.4.2 行锁演示
-- 共享锁（S）
SELECT ... LOCK IN SHARE MODE;
-- 排他锁（X）
SELECT ... FOR UPDATE;
INSERT/UPDATE/DELETE; -- 自动加排他锁注意：
-
行锁是针对索引加的锁
-
无索引字段更新会导致行锁升级为表锁
5.4.3 间隙锁/临键锁
作用：解决幻读问题（RR隔离级别）
规则：
-
唯一索引等值查询，记录不存在时 → 间隙锁
-
非唯一索引等值查询，向右遍历到不满足条件时 → 间隙锁
-
范围查询 → 临键锁
6. InnoDB引擎
6.1 逻辑存储结构
表空间 → 段 → 区（1M） → 页（16KB） → 行隐藏字段：
-
DB_TRX_ID：最近修改事务ID
-
DB_ROLL_PTR：回滚指针，指向undo log
-
DB_ROW_ID：隐藏主键（无主键时生成）
6.2 架构
6.2.1 内存结构
-
Buffer Pool：缓冲池，缓存数据页
-
Change Buffer：更改缓冲区，缓存非唯一二级索引的修改
-
Adaptive Hash Index：自适应哈希索引
-
Log Buffer：日志缓冲区
6.2.2 磁盘结构
-
系统表空间：ibdata1文件
-
独立表空间：表名.ibd文件（每表一个）
-
通用表空间：用户创建的表空间
-
撤销表空间：存储undo log
-
临时表空间：存储临时表数据
-
双写缓冲区：保证数据页写入的可靠性
-
重做日志：保证事务持久性
6.3 事务原理
6.3.1 redo log
-
作用：保证事务持久性
-
特点：物理日志，记录数据页的物理修改
-
流程：事务提交 → redo log buffer → redo log file
-
WAL：Write-Ahead Logging（先写日志，后写磁盘）
6.3.2 undo log
-
作用：保证事务原子性，实现MVCC
-
特点：逻辑日志，记录数据的逻辑变化
-
存储：回滚段（rollback segment）
6.4 MVCC（多版本并发控制）
6.4.1 基本概念
-
当前读：读取记录最新版本，加锁（select for update）
-
快照读：读取记录可见版本，不加锁（普通select）
-
MVCC：维护数据多个版本，实现非阻塞读
6.4.2 实现原理
三要素：
-
隐藏字段：DB_TRX_ID、DB_ROLL_PTR
-
undo log版本链：记录数据历史版本
-
ReadView：快照读时生成的数据可见性规则
6.4.3 ReadView规则
判断数据版本是否可见：
-
trx_id == creator_trx_id：当前事务修改，可见
-
trx_id < min_trx_id：事务已提交，可见
-
trx_id > max_trx_id：事务在ReadView后开启，不可见
-
min_trx_id ≤ trx_id ≤ max_trx_id：如果不在活跃事务列表中，可见
不同隔离级别的ReadView生成时机：
-
RC：每次快照读生成新的ReadView
-
RR：第一次快照读生成ReadView，后续复用
7. MySQL管理
7.1 系统数据库
| 数据库 | 作用 |
|---|---|
| mysql | 存储用户、权限等信息 |
| information_schema | 数据库元数据信息 |
| performance_schema | 性能监控数据 |
| sys | 性能调优和诊断视图 |
7.2 常用工具
7.2.1 mysql客户端
mysql -u root -p123456 -e "SELECT * FROM stu"7.2.2 mysqladmin管理工具
mysqladmin -u root -p123456 version
mysqladmin -u root -p123456 drop 'test01'7.2.3 mysqlbinlog日志工具
mysqlbinlog binlog.000008
mysqlbinlog -s binlog.000008 # 简单格式7.2.4 mysqlshow对象查看
mysqlshow -u root -p123456 --count
mysqlshow -u root -p123456 db01 --count7.2.5 mysqldump备份工具
# 备份整个数据库
mysqldump -u root -p123456 db01 > db01.sql
# 只备份表结构
mysqldump -u root -p123456 -d db01 > db01_structure.sql
# 只备份数据
mysqldump -u root -p123456 -t db01 > db01_data.sql7.2.6 source导入数据
-- 在MySQL客户端中执行
SOURCE /root/db01.sql;学习建议
-
理解存储引擎区别：重点掌握InnoDB特性（事务、行锁、外键）
-
索引是核心：理解B+Tree结构、索引分类、使用原则
-
SQL优化是重点：掌握执行计划分析、索引优化技巧
-
锁机制要理解：了解不同锁的适用场景和影响
-
原理性内容：理解事务的ACID实现原理、MVCC机制
-
多实践：所有SQL语句和优化技巧都要亲手实践
葡萄城是专业的软件开发技术和低代码平台提供商，聚焦软件开发技术，以“赋能开发者”为使命，致力于通过表格控件、低代码和BI等各类软件开发工具和服务
更多推荐
19
0- 0
已为社区贡献1条内容
所有评论(0)