MySQL

字数: 0 字时长: 0 分钟

第 1 章 MySQL 概述

1.1 数据库相关概念

（1）所谓安装 MySQL 数据库，就是在主机安装一个数据库管理系统【DBMS】，这个管理程序可以管理多个数据库

（2）一个数据库中可以创建多个表，以保存数据信息

1.2 MySQL 数据库

关系型数据库【RDBMS】

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库

特点：

使用表存储数据，格式统一，便于维护
使用 SQL 语言操作，标准统一，使用方便

数据模型

数据库的三大范式：

第一范式：确保表的每一列都是不可分割的基本数据单元，比如说用户地址，应该拆分成省、市、区、详细地址等 4 个字段

第二范式：要求表中的每一列都和主键直接相关，比如在订单表中，商品名称、单位、商品价格等字段应该拆分到商品表中，然后新建一个订单商品关联表，用订单编号和商品编号进行关联就好了

第三范式：非主键列应该只依赖于主键列，比如说在设计订单信息表的时候，可以把客户名称、所属公司、联系方式等信息拆分到客户信息表中，然后在订单信息表中用客户编号进行关联

在建表的时候需要考虑表是否符合数据库的三大范式，确保字段不可再分，消除非主键依赖，确保字段仅依赖于主键等，然后在选择字段类型时，应该尽量选择合适的数据类型，在字符集上，尽量选择 utf8mb4，这样不仅可以支持中文和英文，还可以支持表情符号等

当数据量较大时，比如上千万行数据，需要考虑分表，比如订单表，可以采用水平分表的方式来分散单表存储压力

[[三大范式]]

第 2 章 SQL

2.1 SQL 通用语法

SQL 语句可以单行或多行书写，以分号结尾
SQL 语句可以使用空格/缩进来增强语句的可读性
MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写
注释：

单行注释：-- 注释内容 或 # 注释内容
多行注释：/*注释内容*/

2.2 SQL 分类

2.3 DDL

2.3.1 DDL - 数据库操作

查询

查询所有数据库

show databases;

查询当前数据库

select database();

创建

create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

删除

drop database [if exists] 数据库名;

使用

use 数据库名;

2.3.2 DDL - 表操作 - 查询

查询当前数据库所有表

show tables;

查询表结构

desc 表名;

查询指定表的建表语句

show create table 表名;

2.3.3 DDL - 表操作 - 创建

sql

create table 表名(
	字段1 字段1类型[comment 字段1注释],
    字段2 字段2类型[comment 字段2注释],
    字段3 字段3类型[comment 字段3注释],
    ....
    字段n 字段n类型[comment 字段n注释]
)[comment 表注释];

注意：[...] 为可选参数，最后一个字段后面没有逗号

2.3.4 DDL - 表操作 - 数据类型

MySQL 中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型

2.3.4.1 数值类型

举例：score double(4,1) 其中 4 表示最长位数，分数最长是 100.0 四位，1 表示小数位数，规定小数位数只有一位

decimal[m,d]：可以支持更加精确的小数位，M 是这个数的位数（精度）的总数，D 是小数点（标度）后面的位数，如果 D 是 0，则值没有小数点或分数部分，M 最大是 65，D 最大是 30，如果 D 被省略，默认是 0 ，如果 M 被省略，默认是 10

2.3.4.2 字符串类型

定长字符串：比如有 char(10) 不管输入多少个字符都占 10

变长字符串：比如有 varchar(10) 输入几个字符就占几个字符

char(size)：最大是 255 个字符

varchar(size)：最大 65535-3 即 65532 个字节【因为 utf8 编码用 1-3 个字节用于记录大小】，当是 utf8 编码时，一个字符占 3 个字节，所以 size 最大就是 (65535-3)/3=21844 个字符

char(4)：这个 4 表示字符数（最大255）而不是字节数，不管是中文还是字母都是放四个，按字符计算

varchar(4)：这个 4 表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据

怎么存储 emoji？

因为 emoji 是 4 个字节的 UTF-8 字符，而 MySQL 的 utf8 字符集只支持最多 3 个字节的 UTF-8 字符，所以在 MySQL 中存储 emoji 时，需要使用 utf8mb4 字符集

2.3.4.3 日期时间类型

TimeStamp 在 insert 和 update 时，会自动更新时间

2.3.5 DDL - 表操作 - 修改

添加字段

alter table 表名 add 字段名类型(长度) [comment 注释] [约束];

案例：为 emp 表增加一个新的字段 nickname，类型为 varchar(20)

修改数据类型

alter table 表名 modify 字段名新数据类型(长度);

修改字段名和字段类型

alter table 表名 change 旧字段名新字段名类型(长度) [comment 注释] [约束];

删除字段

alter table 表名 drop 字段名;

修改表名

alter table 表名 rename to 新表名;

删除表

drop table [if exists] 表名;

删除指定表，并重新创建该表

truncate table 表名;

2.3.6 总结

DDL - 数据库操作

sql

show databases;
create database 数据库名;
use 数据库名;
select database();
drop database 数据库名;

DDL - 表操作

sql

show tables;
create table 表名 (字段 字段类型, 字段 字段类型);
desc 表名;
show create table 表名;
alter table 表名 add/modify/change/drop/rename to ...;
drop table 表名;

2.4 DML

2.4.1 介绍

DML 英文全称是 Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作

添加数据（insert）
修改数据（update）
删除数据（delete）

2.4.2 DML - 添加数据

给指定字段添加数据

insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...);

给全部字段添加数据

insert into 表名 values (值1, 值2, ...);

批量添加数据

insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

insert into 表名 values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

注意：

插入数据时，指定的字段顺序需要与值的顺序是一一对应的
字符串和日期型数据应该包含在引号中
插入的数据大小，应该在字段的规定范围内

2.4.3 DML - 修改数据

update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据

2.4.4 DML - 删除数据

delete from 表名 [where 条件]

注意：

delete 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
delete 语句不能删除某一个字段的值（可以使用 update）

2.4.5 总结

添加数据
修改数据
删除数据

2.5 DQL

2.5.1 介绍

DQL 英文全称是 Data Query Language（数据查询语句），数据查询语句用来查询数据库中表的记录

查询关键字：select

2.5.2 语法

sql

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

基本查询
条件查询（where）
聚合函数（count、max、min、avg、sum）
分组查询（group by）
排序查询（order by）
分页查询（limit）

2.5.3 DQL - 基本查询

查询多个字段

select 字段1, 字段2, 字段3 ... from 表名;

select * from 表名;

设置别名

select 字段1 [as 别名1], 字段2 [as 别名2] ... from 表名;

去除重复记录

select distinct 字段列表 from 表名;

查询出来的内容每个字段都相同才会去重

2.5.4 DQL - 条件查询

语法

select 字段列表 from 表名 where 条件列表;

条件

2.5.5 DQL - 聚合函数

介绍

将一列数据作为一个整体，进行纵向计算

常见的聚合函数

语法

select 聚合函数(字段列表) from 表名;

注意：

null 值不参与所有聚合函数运算

count(1)、count(*)、count(列名)的区别：

在现代主流数据库（如 MySQL、PostgreSQL、SQL Server、Oracle）中：

COUNT(*) 和 COUNT(1) 性能完全一样，没有任何区别。它们都会统计表中的总行数，包括所有列都为 NULL 的行。
COUNT(列名) 统计的是指定列中不为 NULL 的行数。
sum 仅对数值起作用，否则会报错

2.5.6 DQL - 分组查询

语法

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where 与 having 区别

执行时机不同：where 是分组之前进行过滤，不满足 where 条件不参与分组；而 having 是分组之后对结果进行过滤
判断条件不同：where 不能对聚合函数进行判断，而 having 可以

注意

执行顺序：where > 聚合函数 > having
分组之后，查询的字段一般为聚合函数和分组字段，查询其它字段无任何意义

2.5.7 DQL - 排序查询

语法

select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;

排序方式

ASC：升序（默认值）
DESC：降序

注意

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

2.5.8 DQL - 分页查询

语法

select 字段列表 from 表名 limit 起始索引, 查询记录数;

注意

起始索引从 0 开始，起始索引 = (查询页码 - 1) * 每页显示记录数
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL 中是 limit
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10

2.5.9 案例

2.5.10 DQL - 执行顺序

2.6 DCL

2.6.1 DCL - 介绍

DCL 英文全称是 Data Control Language（数据控制语言），用来管理数据库用户、控制数据库的访问权限

2.6.2 DCL - 管理用户

查询用户

java

use mysql;
select * from user;

创建用户

create user '用户名'@'主机名' identified by '密码';

修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

删除用户

drop user '用户名'@'主机名';

注意：

主机名可以使用 % 通配
这类 SQL 开发人员操作的比较少，主要是 DBA（Database Administrator 数据库管理员）使用

2.6.3 DCL - 权限控制

MySQL 中定义了很多种权限，但是常用的就以下几种：

查询权限

show crants for '用户名'@'主机名';

授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

注意

多个权限之间，使用逗号分隔
授权时，数据库名和表名可以使用 * 进行通配，代表所有

第 3 章函数

函数是指一段可以直接被另一段程序调用的程序或代码

3.1 字符串函数

MySQL 中内置了很多字符串函数，常用的几个如下：

使用：select 函数(参数);

3.2 数值函数

常见的数值函数如下：

3.3 日期函数

常见的日期函数如下：

3.4 流程函数

流程函数也是很常用的一类函数，可以在 SQL 语句中实现条件筛选，从而提高语句的效率

第 4 章约束

4.1 概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
目的：保证数据库中数据的正确，有效性和完整性
分类：

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

4.2 约束演示

案例：根据需求，完成表结构的创建

4.3 外键约束

4.3.1 概念

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

注意：目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的

4.3.2 语法

添加外键

第一种方式：

第二种方式：

删除外键

alter table 表名 drop foreign key 外键名称;

4.3.3 外键约束的删除/更新行为

语法：alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update cascade on delete cascade;

细节说明：

（1）外键指向的表的字段要求是 primary key 或者是 unique

（2）表的类型是 innodb，这样的表才支持外键

（3）外键字段的类型要和主键字段的类型一致（长度可以不同）

（4）外键字段的值，必须在主键字段中出现过，或者为 null（前提是外键字段允许为 null）

（5）一旦建立主外键的关系，数据不能随意删除了

第 5 章多表查询

5.1 多表关系

5.1.1 概述

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多（多对一）
多对多
一对一

5.1.2 一对多（多对一）

案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键

5.1.3 多对多

案例：学生与课程的关系
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

5.1.4 一对一

案例：用户与用户详情的关系
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其它详情字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）

5.2 多表查询概述

概述：指从多张表中查询数据
笛卡尔积：笛卡尔乘积是指在数学中，两个集合 A 集合和 B 集合的所有组合情况【在多表查询时，需要消除无效的笛卡尔积】
在默认情况下，当两个表查询时，规则是从第一张表中取出一行和第二张表的每一行进行组合，返回结果含有两张表的所有列，一共返回的记录数是第一张表行数 * 第二张表的行数
多表查询的条件不能少于表的个数 -1，否则会出现笛卡尔积

多表查询分类
- 连接查询
  内连接：相当于查询 A、B 交集部分数据
  外连接：
  左外连接：查询左表所有数据，以及两张表交集部分数据
  右外连接：查询右表所有数据，以及两张表交集部分数据
  自连接：当前表与自身的连接查询，自连接必须使用表别名
- 子查询

5.3 内连接

内连接查询语法：

隐式内连接

select 字段列表 from 表1, 表2 where 条件 ...;

显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

内连接查询的是两张表交集的部分

5.4 外连接

子查询是利用 where 子句对两张表或者多张表形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示。例如：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门该怎么办？用外连接

外连接查询语法：

左外连接

select 字段列表 from 表1 left [outer] jion 表2 on 条件...;

相当于查询表1（左表）的所有数据包含表1和表2交集部分的数据

右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件...;

相当于查询表2（右表）的所有数据包含表1和表2交集部分的数据

5.5 自连接

自连接是指在同一张表的连接查询【将同一张表看做两张表】

自连接查询语法：

select 字段列表 from 表A 别名A join 表A 别名B on 条件...;

自连接查询，可以是内连接查询，也可以是外连接查询

5.6 联合查询 - union，union all

对于 union 查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

java

select 字段列表 from 表A...
union [all]
select 字段列表 from 表B...;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重

5.7 子查询

概念：SQL 语句中嵌套 select 语句，称为嵌套查询，又称子查询

select * from t1 where column1 = (select column1 from t2);

子查询外部的语句可以是 insert/update/delete/select 中的任何一个

根据子查询结构的不同，分为：
- 标量子查询（子查询结果为单个值）
- 列子查询（子查询结果为一列）
- 行子查询（子查询结果为一行）
- 表子查询（子查询结果为多行多列）
根据子查询位置，分为：where 之后、from 之后、select 之后
标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询

常用的操作符：= <> > >= < <=

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询

常用的操作符：in、not in、any、some、all

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询

常用的操作符：=、<>、in、not in

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

常用的操作符：in

5.8 多表查询案例

5.9 表复制

自我复制数据【蠕虫复制】

有时为了对某个 SQL 语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

5.10 总结

多表关系

一对多：在多的一方设置外键，关联一的一方的主键

多对多：建立中间表，中间表包含两个外键，关联两张表的主键

一对一：用于表结构的拆分，在其中任何一方设置外键（unique），关联另一方的主键

多表查询

第 6 章事务

6.1 事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

默认 MySQL 的事务是自动提交的，也就是说，当执行一条 DML 语句，MySQL 会立即隐式的提交事务

6.2 事务操作

方式一：

查看/设置事务提交方式

java

select @@autocommit;
set @@autocommit = 0; //设置为手动提交事务

提交事务

java

commit;

回滚事务

java

rollback;

方式二：

开启事务

java

start transaction 或 begin;

提交事务

java

commit;

回滚事务

java

rollback;

6.3 事务四大特性【ACID】

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败

如果事务中的任何一个操作失败了，整个事务都会回滚到事务开始之前的状态，如同这些操作从未被执行过一样

一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态

一致性确保事务从一个一致的状态转换到另一个一致的状态，比如在银行转账事务中，无论发生什么，转账前后两个账户的总金额应保持不变，假如 A 账户（100 块）给 B 账户（10 块）转了 10 块钱，不管成功与否，A 和 B 的总金额都是 110 块

隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

隔离性意味着并发执行的事务是彼此隔离的，一个事务的执行不会被其它事务干扰，事务之间是井水不犯河水的，隔离性主要是为了解决事务并发执行时可能出现的脏读、不可重复读、幻读等问题

持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

持久性确保事务一旦提交，它对数据所做的更改就是永久性的，即使系统发生崩溃，数据也能恢复到最近一次提交的状态，MySQL 的持久性是通过 InnoDB 引擎的 redo log 实现的，在事务提交时，InnoDB 会先将修改操作写入 redo log，并刷盘持久化，崩溃后，InnoDB 会通过 redo log 恢复数据，从而保证事务提交成功的数据不会丢失

其中原子性主要通过 Undo Log 来实现，持久性通过 Redo Log 来实现，隔离性由 MVCC 和锁机制来实现，一致性则由其它三大特性共同保证

如何保证原子性：事务对数据进行修改前，会记录一份快照到 Undo Log，如果事务中有任何一步执行失败，系统会读取 Undo Log 将所有操作回滚，恢复到事务开始前的状态，从而保证事务要么全部成功，要么全部失败

如何保证持久性：MySQL 的持久性主要由预写 Redo Log、[[双写机制]]、两阶段提交以及 Checkpoint 刷盘机制共同保证，当事务提交时，MySQL 会先将事务的修改操作写入 Redo Log，并强制刷盘，然后再将内存中的数据页刷入磁盘，这样即使系统崩溃，重启后也能通过 Redo Log 重放恢复数据，在将数据页写入到磁盘时，如果发生崩溃，可能会导致数据页不完整，InnoDB 的数据页大小为 16KB，通常大于操作系统的 4KB 页大小，为了解决只写入部分的问题，MySQL 采用了双写机制，脏盘刷页时，先将数据页写入到一个双写缓冲区中，2M 的连续空间，然后再将其写入到磁盘的实际位置，崩溃恢复时，如果发现数据页不完整，会从双写缓冲区中恢复副本，确保数据页的完整性

在涉及主从复制时，MySQL 通过两阶段提交保证 Redo Log 和 Binlog 的一致性：第一阶段，写入 Redo Log 并标记为 prepare 状态，第二阶段，写入 Binlog 再提交 Redo Log 为 commit 状态

如何保证隔离性：隔离性主要通过锁机制和 MVCC 来实现，比如说一个事务正在修改某条数据时，MySQL 会通过临键锁来防止其它事务同时进行修改，避免数据冲突，同时，临键锁可以防止幻读现象的发生。MVCC 主要用来优化读操作，通过保存数据的历史版本，让读操作不需要加锁就能直接读取快照，提高读的并发性能，不同的隔离级别对应不同的实现策略，比如说在可重复读隔离级别下，事务第一次查询时会生成一个 Read View，之后所有读操作都复用这个视图，保证多次读取的结果一致

如何保证一致性：MySQL 的一致性并不是靠某一个机制单独保证的，而是原子性、隔离性和持久性协同作用的结果

6.4 并发事务产生的问题

说明：一个事务包含一条及以上的sql语句

脏读：一个事务读到另外一个事务还没有提交的数据

解读：开启事务 A 和事务 B，事务 A 查询 DB 中的一条数据，返回的是 DB 中原本的数据，此时事务 B 修改 DB 中的数据但没提交，此时事务 A 再次查询，返回的是事务 B 修改后的数据，这样事务 A 就读到事务 B 还没有提交的数据，这就是脏读

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读

解读：开启事务 A 和事务 B，当事务 A 查询 id = 1 时返回一个结果，此时事务 B 修改了 id = 1 的记录后提交，此时事务 A 再次查询 id = 1 时返回的是修改后的结果，此时同一个事务的同样的查询语句返回的数据不同，这就是不可重复读，正常情况的可重复读是当再次查询 id = 1 的结果时返回和第一次查询时一样的结果，当事务 A 提交后再次查询才能查到修改后的结果

幻读：

第一种解释：一个事务按照条件查询数据时没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影

解读：开启事务 A 和事务 B，当事务 A 执行 select id = 1 的语句时，此时因为 DB 中没有，所以显示没查到，此时事务 B 插入一条 id = 1 的数据后提交事务，此时 DB 中有了 id = 1 的数据，此时事务 A 插入 id = 1 的数据会报错，因为 id = 1 的数据已经存在，当事务 A 再次查询 id = 1 的这条数据时，因为解决了不可重复读的问题，导致第二次查询的结果也显示 DB 中没有，此时插入时就出现了幻影

第二种解释：当同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。例如，如果 SELECT 执行了两次，但第二次返回了第一次没有返回的行，则该行是“幻像”行。

幻读就是一个事务读到另一个事务新增加并提交的数据 insert。在同一个事务中，对于同一组数据读取到的结果不一致。比如，事务A 新增了一条记录，事务B 在事务A 提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。幻读出现的原因就是由于事务并发新增记录而导致的。

1、事例程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现有两条记录，共花了1.2万元，似乎出现了幻觉，这就是幻读。

2、分析在这个事例中，事务B读取了数据，接着另一个事务A插入了一条数据。在随后的查询中，事务B就会发现多了一条原本不存在的记录，就好像发生了幻觉一样，这是由于数据新增导致的。

6.5 事务的隔离级别

隔离级别定义了一个事务可能受其它事务影响的程度，MySQL 支持四种隔离级别，分别是：读未提交、读已提交、可重复读和串行化

隔离级别	脏读	不可重复读	幻读
Read uncommitted 读未提交	√	√	√
Read committed 读已提交	×	√	√
Repeatable Read 可重复读（默认）	×	×	√
Serialiable 串行化	×	×	×

java

-- 查看事务隔离级别
select @@transaction_isolation;

-- 设置事务隔离级别
set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}

注意：事务隔离级别越高，数据越安全，但是性能越低

事务的隔离级别是如何实现的？

读未提交通过行锁共享锁确保一个事务在更新行数据但没有提交的情况下，其它事务不能更新该行数据，但不会阻止脏读，意味着事务 2 可以在事务 1 提交之前读取到事务 1 修改的数据

读已提交会在更新数据前加行级排他锁，不允许其它事务写入或者读取未提交的数据，也就意味着事务 2 不能在事务 1 提交之前读取到事务 1 修改的数据，从而解决脏读问题

另外，读已提交会在每次读取数据前都生成一个新的 ReadView，所以会出现不可重复读的问题

可重复读只在第一次读操作时生成 ReadView，后续读操作都会使用这个 ReadView，从而避免不可重复读的问题

另外，对于当前读操作，可重复读会通过临键锁来锁住当前行和前间隙，防止其它事务在这个范围内插入数据，从而避免幻读的问题

串行化级别下，事务在读操作时，会先加表级共享锁，在写操作时，会先加表级排他锁，直到事务结束后才释放锁，这样就能确保事务之间不会相互干扰

第 7 章存储引擎

7.1 MySQL 体系结构

连接层：最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证及相关的安全方案，服务器也会为安全接入的每个客户端验证它所具有的操作权限
服务层：第二层架构主要完成大多数的核心服务功能，如 SQL 接口，并完成缓存的查询，SQL 的分析和优化，部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，如过程、函数等
引擎层：存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 和存储引擎进行通信，不同的存储引擎具有不同的功能，这样我们可以根据自己的需要来选取合适的存储引擎
存储层：主要是将数据存储在文件系统之上，并完成与存储引擎的交互

binlog 在服务层，负责记录 SQL 语句的变化，它记录了所有对数据库进行更改的操作，用于数据恢复、主从复制等

[[一条查询语句是如何执行的？]]

[[一条更新语句是如何执行的？]]

[[事务回滚是怎么实现的？]]

[[事务开始、两阶段提交、事务提交、双写机制]]

7.2 存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式，存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型

在创建表时，指定存储引擎

java

create table 表名(
	字段1 字段1类型 [comment 字段1注释],
    ......
    字段n 字段n类型 [comment 字段n注释]
)engine = innodb [comment 表注释];

查看当前数据库支持的存储引擎

java

show engines;

7.3 存储引擎特点

InnoDB

介绍：InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 存储引擎

特点：DML 操作遵循 ACID 模型，支持事务；行级锁，提高并发访问性能；支持外键 foreign key 约束，保证数据的完整性和正确性

文件：xxx.ibd：xxx 代表的是表名，InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引

MyISAM

介绍：MyISAM 是 MySQL 早期的默认存储引擎

特点：不支持事务，不支持外键；支持表锁，不支持行锁；访问速度快

文件：

xxx.sdi：存储表结构信息

xxx.MYD：存储数据

xxx.MYI：存储索引

Memory

介绍：Memory 引擎的表数据是存储在内存中的，由于受到硬件问题或断电问题的影响，只能将这些表作为临时表或缓存使用

特点：内存存放、hash 索引（默认）

文件：xxx.sdi：存储表结构信息

7.4 存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎，对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合

InnoDB：是 MySQL 的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择
MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的
MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存，MEMORY 的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

第 8 章索引

8.1 索引概述

索引是帮助 MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

优势	劣势
提高数据检索的效率，降低数据库的 IO 成本	索引列也是要占用空间的
通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行 insert、update、delete 时，效率降低

8.2 索引结构

MySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

索引结构	描述
B+Tree 索引	最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash 索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree（空间索引）	空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text（全文索引）	是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene，Solr，ES

二叉树

二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低，大数据量情况下，层级较深，检索速度慢

红黑树

红黑树可以自平衡，解决了二叉树在顺序插入时会形成一个链表的问题，红黑树本质也是二叉树，在大数据量的情况下，层级较深，检索速度慢

B-Tree【多路平衡查找树】

以一颗最大度数（max-degree）为 5（5阶）的 b-tree 为例（每个节点最多存储 4 个 key，5 个指针），树的度数指的是一个节点（灰色的方框是一个节点，它下面有 5 个节点）的子节点个数，每个节点最多存储 4 个 key，如 20/30/62/89，小于 20 走第一个指针，20 到 30 之间走第 2 个指针，依次类推

看 B 树是怎么构建的：

先往一个节点添加数据，因为一个节点只能存放 4 个 key，所以存放第 5 个 key 后，该节点会自动分裂，分裂规则是中间元素向上分裂，分裂后的节点下挂着两个分裂后的节点

B+Tree

以一颗最大度数为 4（4 阶）的 b+tree 为例

相对于 B-Tree 的区别：

所有的数据都会出现在叶子节点
叶子节点形成一个单向链表

页分裂、页合并

MySQL 索引数据结构对经典的 B+Tree 进行了优化，在原 B+Tree 的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能

Hash 索引

哈希索引就是采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后存储在 hash 表中，如果两个（或多个）键值，映射到一个相同的槽位上，它们就产生了 hash 冲突（也称为 hash 碰撞），可以通过链表来解决

Hash 索引的特点：

（1）Hash 索引只能用于对等比较（=，in），不支持范围查询（between，>，<，...）

（2）无法利用索引完成排序操作

（3）查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+tree 索引

（4）在 MySQL 中，支持 hash 索引的是 Memory 引擎，而 InnoDB 中具有自适应 hash 功能，hash 索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的

为什么 InnoDB 存储引擎选择使用 B+tree 索引结构？

相对于二叉树，层级更少，搜索效率高
对于 B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
相对于 Hash 索引，B+tree 支持范围匹配及排序操作

8.3 索引分类

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一（unique）索引作为聚集索引
如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

以下 SQL 语句，哪个执行效率高？为什么？

java

select * from user where id = 10;
select * from user where name = 'Arm';

备注：id 为主键，name 字段创建的有索引

select * from user where id = 10; 比 select * from user where name = 'Arm'; 效率高

InnoDB 主键索引的 B+tree 高度有多高呢？

假设：一行数据大小为 1k，一页中可以存储 16 行这样的数据，InnoDB 的指针占用 6 个字节的空间，主键占用的空间取决于主键的类型，如 int 占 4 个字节，bigint 占 8 个字节，这里假设主键类型是 bigint 如果高度为 2 可以存取多大的数据量：算出最上面的节点的指针数量：设 key 的数量为 n，指针数量比 key 多 1 个，一个页的大小是 16k，那么就有n * 8 + (n + 1) * 6 = 16 * 1024，算出 n 约为 1170，那么就有 1171 个指针，每一个指针就指向一个节点，一个节点可以存储 16 行数据，那么总共能存储1171 * 16 = 18736行数据如果高度为 3：第一层节点有 1171 个指针，那么第二层就会有 1171 个节点，第二层的每一个节点又会有 1171 个指针，那么第三层就会有 1171 * 1171个节点，那么就能存取1171 * 1171 * 16 = 21939856行数据

8.4 索引语法

创建索引

java

create [unique | fulltext] index index_name on table_name (index_col_name,...);

查看索引

java

show index from table_name;

删除索引

java

drop index index_name on table_name;

8.5 SQL 性能分析

SQL 执行频率

MySQL 客户端连接成功后，通过 show [session | global] status 命令可以提供服务器状态信息，通过如下指令，可以查看当前数据库的 insert、update、delete、select 的访问频次

java

show global status like 'Com_______' //这里是 7 个下划线，一个下划线代表一个字符

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认 10 秒）的所有 SQL 语句的日志。MySQL 的慢查询日志默认没有开启，需要在 MySQL 的配置文件（/etc/my.cnf）中配置如下信息：

java

## 开启 MySQL 慢日志查询开关
slow_query_log=1
## 设置慢日志的时间为 2 秒，SQL 语句执行时间超过 2 秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动 MySQL 服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log

profile 详情

show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了，通过 have_profiling 参数，能够看到当前 MySQL 是否支持 profile 操作：

java

select @@have_profiling;

默认 profiling 是关闭的，可以通过 set 语句在 session / global 级别开启 profiling：

java

set profiling = 1;

执行一系列的业务 SQL 的操作，然后通过如下指令查看指令的执行耗时：

explain 执行计划

explain 或者 desc 命令获取 MySQL 如何执行 select 语句的信息，包括在 select 语句执行过程中表如何连接和连接的顺序

语法：

8.6 索引使用原则

验证索引效率

最左前缀法则

如果索引了多列（联合索引），假设 profession、age、status 是联合索引，要遵守最左前缀法则，那么查询时要包含最左一列的字段索引才不会失效，如果跳跃某一列，后面的字段索引失效，如果包含所有列，那么所有列的顺序随意，索引不会失效（即最左边的一列必须存在，跟位置无关）

范围查询

联合索引中，出现范围查询（>，<），范围查询右侧的列索引失效

索引列运算

不要在索引列上进行运算操作，索引将失效

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效

or 连接的条件

用 or 分割开的条件，or 的一侧有索引一侧没有索引，那么索引不会生效，只有两侧都有索引，索引才会生效

数据分布影响

如果 MySQL 评估使用索引比全表更慢，则不使用索引

SQL 提示

SQL 提示，是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的，比如 profession 字段已经有了联合索引，然后我又给 profession 字段添加了单列索引，那么数据库就会在这两个索引中选择一个，SQL 提示就可以让我们自己规定 MySQL 选择使用哪个索引

覆盖索引

尽量使用覆盖索引，减少使用 select *，覆盖索引指查询使用了索引，并且需要返回的列在该索引中已经全部能够找到

先看该表中有哪些索引：

这里上面的查询是查询 id、profession、age、status，其中 id 有聚集索引，聚集索引的叶子节点存放的是整行的信息，其中 profession、age、status 有二级索引，二级索引的叶子节点存放的该行的主键 id，该查询走的是二级索引，因为二级索引的叶子节点存放了 id，所以不用回表查询，所以效率高。下面的查询是查询 id、profession、age、status、name，走的是二级索引，因为二级索引的叶子节点中找不到 name，所以会根据 id 回表查询聚集索引，从聚集索引的叶子节点拿到整行的信息，所以效率低

再看个例子：

该表的主键 id 有聚集索引，name 是二级索引

当有语句：select * from tb_user where id = 2;时，因为是根据 id 查，肯定走聚集索引，2 比 5 小，走左边，找到 2 后因为返回的是 *，又因为聚集索引叶子节点存的是该行所有数据，所以直接返回该行

当有语句：select id, name from tb_user where name = 'Arm' 时：会根据 name 的索引，即二级索引进行查找，因为 A 在 L 的前面，所以走 Lee 的左边，找到 Arm，因为要查找的是 id 和 name，又因为二级索引的叶子节点就是存放的 name 和主键 id，所以可以直接返回，不用回表查询

当有语句：select id, name, gender from tb_user where name = 'Arm'; 时：根据 name 的索引，即二级索引开始查找，找到 Arm 后发现没有 gender 字段，就会根据主键 id 回到聚集索引就行查找，找到 id 为 2 的数据行后提取出 gender 字段后再返回，这就不是覆盖索引了

问题：一张表，有四个字段（id，username，password，status），由于数据量大，需要对以下 SQL 语句进行优化，该如何进行才是最优方案：

java

select id,username,password from tb_user where username = "itcast";

解决：给 username 和 password 建立联合索引，这样就建立了关于 username 和 password 的二级索引，二级索引叶子节点挂的就是 id，这样就不用回表查询也可以查询到 id、username、password 这三个字段了

前缀索引

当字段类型为字符串（varchar，text 等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘 IO，影响查询效率，此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率

java

create index idx_xxxx on table_name(column(n));

索引前缀的长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

选择性计算公式

SQL

## 意思就是找出不同的 email 的数量除以所有记录的数量，如果结果是 1，说明 email 全不同
select count(distinct email) / count(*) from tb_user;

## 意思先取 email 的前 5 位，找出前 5 位不同的 email 的数量除以所有记录的数量，如果结果结果接近 1，说明选择性高，就可以以 email 的前 5 位建立索引
select count(distinct substring(email, 1, 5)) / count(*) from tb_user;

前缀索引的查询流程：

单列索引与联合索引

单列索引：即一个索引只包含单个列

联合索引：即一个索引包含了多个列

在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引而非单例索引

8.7 索引设计原则

8.8 总结

索引概述

索引是高效获取数据的数据结构

索引结构

索引分类

索引语法

SQL 性能分析

执行频次、慢查询日志、profile、explain

索引使用

联合索引：最左前缀法则（最左边的列必须存在，如果最左边的列不存在，整个索引就失效了，如果最左边的列存在，但是中间跳过了某列，那么后面的列失效），范围查询
索引失效：函数运算、字符串不加引号、在前面进行模糊匹配、or连接、MySQL 评估
SQL 提示：告诉 MySQL 要使用哪个索引
覆盖索引：查询返回的列在索引结构中都包含了，我不需要回表查询了，回表查询指在查询时先走二级索引检索到这一行数据的id，再根据 id 回到聚集索引中查找这一行的数据
前缀索引
单列/联合索引

索引设计原则

第 9 章 SQL 优化

9.1 插入数据

insert 优化

java

insert into tb_test values(1, 'tom');
insert into tb_test values(2, 'cat');
insert into tb_test values(3, 'jerry');
...

优化：

（1）批量插入

java

insert into tb_test values(1, 'tom'), (2, 'cat'), (3, 'jerry');

（2）手动提交事务

（3）主键顺序插入

主键顺序插入性能高于乱序插入

（4）大批量插入数据

如果一次性需要插入大批量数据，使用 insert 语句插入性能较低，此时可以使用 MySQL 数据库提供的 load 指令进行插入，操作如下：

9.2 主键优化

数据组织方式

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表

页分裂

页可以为空，也可以填充一半，也可以填充 100%，每个页包含了 2-N 行数据（如果一行数据多大，会行溢出），根据主键排列

解读主键乱序插入：比如已经插好了1,5,9,23,47,55,67,89,101,107（每页放5个），这时插入50时，会开辟一个新的页3，然后找到1号页的50%的位置，把50%以后的元素放到3号页，然后再把50插入到3号页中，最后重新分配指针

页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其它记录声明使用

当页中删除的记录达到 MERGE_THRESHOLD（默认为页的 50%），InnoDB 会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定

主键设计原则

满足业务需求的情况下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键
尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号
业务操作时，避免对主键的修改

9.3 order by 优化

9.4 group by 优化

9.5 limit 优化

9.6 count 优化

9.7 update 优化

第 10 章视图/存储过程/触发器

10.1 视图

介绍

视图是由数据库中的一个表或多个表导出的虚拟表，其作用是方便用户对数据的操作。视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。同样对视图的更新，会影响到原来表的数据。

视图是存储在数据库中的查询的SQL语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等；另一个原因是可使复杂的查询易于理解和使用。这个视图就像一个“窗口”，从中只能看到你想看的数据列。这意味着你可以在这个视图上使用SELECT *，而你看到的将是你在视图定义里给出的那些数据列。

举例：朕想要了解皇宫的国库的相关情况，想知道酒窖有什么酒，剩多少，窖藏多少年，于是派最信任的高公公去清点，高公公去国库清点后报给了朕；朕又想知道藏书情况，于是又派高公公去清点并回来报告给朕，又想知道金银珠宝如何，又派高公公清点。。。过一段时间又想知道藏书情况，高公公还得重新再去清点，皇上问一次，高公公就得跑一次路。后来皇上觉得高公公不容易，就成立了国库管理部门，小邓子负责酒窖，小卓子负责藏书，而小六子负责金库的清点。。。后来皇上每次想了解国库就直接问话负责人，负责人就按照职责要求进行汇报。安排专人管理后，每次皇上想要了解国库情况，就不必让高公公每次都跑一趟，而是指定的人员按照指定的任务完成指定的汇报工作就可以了。和数据库相对应，每次进行查询工作，都需要编写查询代码进行查询；而视图的作用就是不必每次都重新编写查询的SQL代码，而是通过视图直接查询即可。

创建

java

create [or replace] view 视图名称[(列名列表)] as select语句 [with[cascaded | local] check option]

查询

java

查看创建视图语句：show create view 视图名称;
查看视图数据：select * from 视图名称......;

修改

java

方式一：create [or replace] view 视图名称[(列名列表)] as select 语句 [with[cascaded | local] check option]
方式二：alter view 视图名称[(列名列表)] as select语句 [with[cascaded | local] check option]

删除

java

drop view [if exists] 视图名称 [, 视图名称] ...

视图的检查选项

当使用 with check option 子句创建视图时，MySQL 会通过视图检查正在更改的每个行，例如插入、更新、删除，以使其符合视图的定义，MySQL 允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性，为了确定检查的范围，MySQL 提供了两个选项：cascaded 和 local，默认值为 cascaded

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系，如果视图包含以下任何一项，则该视图不可更新：

聚合函数或窗口函数【sum()、min()、max()、count() 等】
distinct
group by
having
union 或者 union all

作用

简单

视图不仅可以简化用户对数据的理解，也可以简化它们的操作，那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后得操作每次指定全部的条件

安全

数据库可以授权，但不能授权到数据库特定行和特定的列上，通过视图用户只能查询和修改它们所能见到的数据

数据独立

视图可以帮助用户屏蔽真实表结构变化带来的影响

案例

10.2 存储过程

介绍

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用

特点

封装、复用
可以接收参数，也可以返回数据
减少网络交互，效率提升

创建

java

create procedure 存储过程名称([参数列表])
begin
    -- SQL语句
end;

调用

java

call 名称([参数]);

查看

java

select * from information_schema.routines where routine_schema = 'xxx'; --查询指定数据库的存储过程及状态信息
show create procedure 存储过程名称; --查询某个存储过程的定义

删除

java

drop procedure [if exists] 存储过程名称;

变量

10.3 存储函数

10.4 触发器

介绍

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后触发，并执行触发器中定义的 SQL 语句集合，触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其它的数据库是相似的，现在触发器还只支持行级触发，不支持语句级触发

语法

没听完

第 11 章锁

11.1 概述

介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制，在数据库中，除传统的计算资源（CPU、RAM、IO）的争用以外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂

分类

MySQL 中的锁，按照锁的粒度分，分为以下三类：

全局锁：锁定数据库中的所有表
表级锁：每次操作锁住整张表
行级锁：每次操作锁住对应的行数据

11.2 全局锁

介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的 DML 的写语句，DDL 语句，以及更新操作的事务提交语句都将被阻塞

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性

特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

（1）如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆

（2）如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟

在 InnoDB 引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份

java

mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql

11.3 表级锁

11.3.1 介绍

表级锁，每次操作锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低，应用在 MyISAM、InnoDB、BDB 等存储引擎中

对于表级锁，主要分为以下三类：

（1）表锁

（2）元数据锁（meta data lock、MDL）

（3）意向锁

11.3.2 表锁

对于表锁，分为两类：

（1）表共享读锁（read lock）

读锁，当前用户只能读不能写，其它用户也是只能读不能写

（2）表独占写锁（write lock）

写锁，当前用户可以读可以写，其它用户不能读不能写

语法：

（1）加锁：lock tables 表名... read/write

（2）释放锁：unlock tables / 客户端断开连接

[[表锁详解]]

11.3.3 元数据锁（meta data lock, MDL）

[[元数据锁详解]]

MDL 加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上，MDL 锁主要作用是维护表元数据（表结构）的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免 DML 与 DDL 冲突，保证读写的正确性

在 MySQL5.5 中引入了 MDL，当对一张表进行增删改查的时候，加 MDL 读锁（共享）；当对表结构进行变更操作的时候，加 MDL 写锁（排他）

MDL 写锁只有在执行 DDL 语句的时候才会加，平时我们的增删改查只是加了 MDL 读锁，MDL 读锁之间是可以共享的，并不会出现锁等待的情况

11.3.4 意向锁

为了避免 DML 在执行时，加的行锁与表锁的冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查

意向共享锁（IS）：由语句 select ... lock in share mode 添加，与表锁共享锁（read）兼容，与表锁排它锁（write）互斥
意向排他锁（IX）：由 insert、update、delete、select ... for update 添加，与表锁共享锁（read）及排它锁（write）都互斥，意向锁之间不会互斥

[[意向锁详解]]

11.4 行级锁

11.4.1 介绍

行级锁，每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高，应用在 InnoDB 存储引擎中

InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁，对于行级锁，主要分为以下三类：

行锁（Record Lock）：锁定单个行记录的锁，防止其它事务对此行进行 update 和 delete，在 RC、RR 隔离级别下都支持
间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其它事务在这个间隙进行 insert，产生幻读，在 RR 隔离级别下都支持

临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gap，在 RR 隔离级别下支持

[[行级锁详解]]

11.4.2 行锁

InnoDB 实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其它事务获得相同数据集的排它锁
排它锁（X）：允许获取排它锁的事务更新数据，阻止其它事务获得相同数据集的共享锁和排它锁

11.4.3 间隙锁/临键锁

注意：间隙锁唯一的目的是防止其它事务插入间隙，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

第 12 章 InnoDB 引擎

12.1 逻辑存储结构

[[说说 MySQL 的段区页行]]

[[MySQL存储的数据都是什么样的？]]

12.2 架构

MySQL5.5 版本开始，默认使用 InnoDB 存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛，下面是 InnoDB 架构图，左侧为内存结构，右侧为磁盘结构

内存架构

磁盘结构

后台线程

12.3 事务原理

事务

特性

原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
一致性：事务完成时，必须使所有的数据都保持一致状态
隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

12.3.1 原理

redo log

事务中的持久性就是通过 redo log 实现的，redo log 是重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性的，该日志文件由两部分组成：重做日志缓冲【redo log buffer】以及重做日志文件【redo log file】，前者是在内存中，后者在磁盘中，当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘发生错误时进行数据恢复使用。redo log 是物理日志，记录的是这个数据页里面的内容是什么样子的

redo log 没有介入前：

当对数据进行操作时，会先到缓冲区【Buffer Pool】操作数据，如果缓冲区没有该数据就会到磁盘加载该数据，在缓冲区对数据进行修改后该数据页称为脏页，然后缓冲区会把数据同步到磁盘，但是如果在把数据同步到磁盘的时候出错了，就无法保证数据持久性

redo log 介入后：

在内存区对数据进行增删改后 redo log 会记下数据页的变化，当事务提交时，会把数据页的变化刷新到磁盘区，一段时间后脏页在刷新到磁盘区的时候出错了就可以通过 redo log 进行恢复

为什么事务提交时会选择刷 redo log 日志到磁盘而不是直接刷脏页到磁盘？

MySQL（InnoDB）选择“提交时写 redo log”而非“提交时刷脏页”是一种经典的权衡（Trade-Off），其根本目的是为了极致地提升数据库的吞吐量和性能。

先理解WAL（Write-Ahead Logging）

核心思想：在数据页被持久化到磁盘上的数据文件（.ibd文件）之前，必须先将其对应的修改操作日志（即重做日志，Redo Log）持久化到磁盘。

目的：保证事务的持久性。即使发生宕机，只要重做日志存在，就能通过“重放”日志，将数据恢复到崩溃前的状态。
工作流程：
1. 事务开始时，数据修改首先在内存中的缓冲池 里进行。
2. 修改完成后，将这次修改的记录（例如“在表T的页P的偏移量O处写入数据D”）写入到重做日志缓冲区。
3. 当事务提交时，重做日志缓冲区 的内容会被强制刷写到磁盘上的重做日志文件（ib_logfile0， ib_logfile1）。这一步通常伴随着fsync操作，确保数据落盘。
4. 此时，事务就算提交成功了，可以向客户端返回成功。数据页本身（还在内存缓冲池中）会在后台由特定线程择机刷写到磁盘的数据文件。

WAL的优势：

高性能：将随机写（数据页）转换为了顺序写（重做日志），大大提升了I/O效率。
保证原子性与持久性：确保了已提交事务的数据不会丢失。

这种设计模式被称为 Write-Ahead Logging (WAL)，即预写式日志。下面我们从几个关键维度来详细拆解为什么 WAL 是更优的选择。

随机 I/O vs. 顺序 I/O（核心原因）

直接刷脏页（随机I/O）：
- 数据页（通常为16KB）在磁盘上的存储位置是随机的。如果事务修改了10个分散在不同位置的数据页，提交时就需要将这10个16KB的页面随机地写回磁盘。
- 磁盘（包括SSD）最怕随机写。它的速度比顺序写要慢几个数量级，因为它涉及到昂贵的磁头寻道（对于HDD）或块擦除（对于SSD）。
写 redo log（顺序I/O）：
- redo log 是只追加（append-only） 的日志文件。所有事务的修改记录都像排队一样，一个接一个地写在日志文件的末尾。
- 顺序写的性能极高。它几乎完全利用了磁盘的带宽，是现代数据库、消息队列（如Kafka）等高吞吐系统的基石。

结论一：将大量的小型随机写，转换为少量的连续顺序写，极大地提升了I/O效率。

组提交（Group Commit）—— 大幅降低 I/O 次数

直接刷脏页：
- 每个事务提交时，都需要等待它修改的所有脏页刷盘。如果100个事务同时提交，最坏情况下可能需要触发100次离散的I/O操作（每个事务修改的页不同）。
写 redo log：
- 多个事务可以将它们的 redo log 记录先准备好，然后在commit时，合并成一次fsync操作，一次性将一大批日志记录顺序写入磁盘。
- 这就像一个快递员，不是每有一个包裹就送一次，而是攒够一车包裹后统一配送，效率极高。

结论二：写 redo log 使得“组提交”成为可能，将高并发下的多次I/O合并为一次，极大地降低了磁盘I/O压力。

数据刷盘的粒度与时机

直接刷脏页（粗粒度，时机差）：
- 要刷就必须刷整个16KB的数据页，即使你只修改了其中的一个字节。
- 提交时必须同步等待所有脏页写完，事务才能返回成功，严重增加了事务响应时间（Commit Latency）。
写 redo log（细粒度，时机优）：
- redo log 只记录物理逻辑日志，即“在某个数据页的某个偏移量做了什么修改”。这条日志通常远小于16KB（可能只有几百字节）。
- 提交时只需要等待这个小得多的日志记录刷盘即可，事务响应时间极短。
- 至于那个16KB的完整脏页，InnoDB 有后台的刷脏线程，可以在系统空闲时、或者缓冲池需要腾出空间时，异步地、批量地将它们写回磁盘。这解放了繁忙的事务线程。

结论三：写小日志大大减少了提交时必须同步写入的数据量，降低了事务延迟。脏页刷盘则被推迟为异步操作，更加灵活高效。

undo log

事务的原子性需要 undo log 来实现，undo log 是回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和 MVCC【多版本并发控制】。undo log 与 redo log 记录物理日志【记录的是这个数据页里面的内容是什么样子的】不同，它是逻辑日志【记录的是每一步进行了什么操作】。可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录，当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚

undo log 销毁：undo log 在事务执行时产生，事务提交时，并不会立即删除 undo log，因为这些日志可能还用于 MVCC

undo log 存储：undo log 采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含 1024 个 undo log segment

12.4 MVCC

12.4.1 基本概念

当前读

读取的是记录的最新版本，读取时还要保证其它并发事务不能修改当前记录，会对读取的记录进行加锁，对于我们日常的操作如：select...lock in share mode(共享锁)，select...for update、update、insert、delete【排它锁】都是一种当前读

快照读

快照读是 InnoDB 通过 MVCC 实现的一种非阻塞读方式，当事务执行 select 查询时，InnoDB 并不会直接读当前最新的数据，而是根据事务开始时生成的 ReadView 去判断每条记录的可见性，从而读取符合条件的历史版本

简单的 select 语句【不加锁】就是快照读，快照读读取的是记录数据的可见版本，有可能是历史数据，不加锁是非阻塞读

（1）Read Committed：每次 select，都生成一个快照读

（2）Repeatable Read：开启事务后第一个 select 语句才是快照读的地方

（3）Serializable：快照读会退化为当前读

MVCC

MVCC 全称 Multi-Version Concurrency Control，多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突，快照读为 MySQL 实现 MVCC 提供了一个非阻塞读的功能，MVCC 的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log 日志、readView。MVCC 的作用就是在快照读时要通过 MVCC 来查找对应的历史版本

12.4.2 实现原理

记录中的隐藏字段

当创建一个表后，有：

数据库会默认创建三个隐式字段：

其中：

undo log

回滚日志，在 insert、update、delete 的时候产生的便于数据回滚的日志，当 insert 的时候，产生的 undo log 日志只在回滚时需要，在事务提交后，可被立即删除，而 update、delete 的时候，产生的 undo log 日志不仅在回滚时需要，在快照读时也需要，不会立即被删除

有一条记录：

开始事务 2，当事务 2 执行修改语句时，undo log 会先写日志，然后记录被修改，然后提交事务

开始事务 3，先记录日志，记录日志后再修改，然后提交事务

开始事务 4，事务 4 先查询记录，然后修改记录，先记录日志，然后再修改

不同事务或相同事务对同一条记录进行修改，会导致该记录的 undo log 生成一条记录版本的链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录

readview

ReadView【读视图】是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务【未提交】的 id，ReadView 中包含了四个核心字段：

版本链数据访问规则：

如果两个 AB 事务并发修改一个变量，那么 A 读到的值是什么，怎么分析？

事务 A 在读取时是否能读到事务 B 的修改，取决于 A 是快照读还是当前读，如果是快照读，InnoDB 会使用 MVCC 的 ReadView 判断记录版本是否可见，若事务 B 尚未提交或在 A 的视图不可见，则 A 会读到旧值，如果是当前读，则需要加锁，若 B 已提交可直接读取，否则 A 会阻塞直到 B 结束

不同的隔离级别生成 ReadView 的时机不同：

READ COMMITTED：在事务中每一次执行快照读时生成 ReadView

REPEATABLE READ：仅在事务中第一次执行快照读时生成 ReadView，后续复用该 ReadView

（1）READ COMMITTED 【读已提交】隔离级别下，在事务中每一次执行快照读时生成 ReadView

看事务 5 第一次查询时生成的 ReadView：其中 m_ids:{3,4,5}，m_ids 指的是当前活跃的事务 id 的集合，因为事务 2 已经提交，所以当前活跃的事务 id 只有 3,4,5；min_trx_id:3，min_trx_id 指最小活跃事务 id，所以为 3；max_trx_id:6，max_trx_id 指预分配事务 id，是当前最大事务的 id + 1，所以是 6；creator_trx_id:5，creator_trx_id 指 ReadView 创建者的事务 id，所以为 5

事务 5 第二次查询时生成的 ReadView 同理

那么这两次快照读提取的到底是哪个版本？

先看第一个快照读：

此时当前记录的事务 id 是 4，那么 trx_id = 4，让 trx_id 与四条规则进行比较，发现一个都满足不了，那么表示当前快照读查找到的数据就不应该是该事务 id = 4 的记录，然后就要沿着版本链继续找，此时当前事务的 id = 3，与四条规则进行比较发现也都不成立，说明当前版本也不成立，然后继续往下找，此时当前事务的 id = 2，与四条规则进行比较发现条件 2 成立，说明可以访问该版本，说明该快照读读的就是该版本，此时把该版本的记录直接返回

第二个快照读同理

（2）在 REPEATABLE READ【可重复读】的隔离级别下，仅在事务中第一次执行快照时生成 ReadView，后续复用该 ReadView

可重复读指的是在同一个事务中读取两个相同的数据返回的结果是一样的，两个 ReadView 都一样，匹配规则都一样，当然就可以返回相同的结果，这就保证了可重复读

12.5 总结

第 13 章运维

13.1 日志

13.1.1 二进制日志

二进制日志（binlog）记录了所有的 DDL 语句和 DML 语句，但不包括数据查询（select、show）语句

作用：

灾难时的数据恢复
MySQL 的主从复制，在 MySQL8 版本中，默认二进制日志是开启着的

日志格式：

MySQL 服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

statement：基于 SQL 语句的日志记录，记录的是 SQL 语句，对数据进行修改的 SQL 都会记录在日志文件中，比如有一个 update 影响了 5 行，只会记录这个 update 语句
row：基于行的日志记录，记录的是每一行的数据变更（默认），比如有一个 update 影响了 5 行数据，那么它会记录这 5 行数据之前是什么样，改后是什么样
mixed：混合了 statement 和 row 两种格式，默认采用 statement，在某些特殊情况下会自动切换为 row 进行记录

第 14 章补充

1. MySQL 日志文件有哪些？

有 6 大类：

错误日志（Error Log）：记录 MySQL 服务器启动、运行或停止时出现的问题
慢查询日志（Slow Query Log）：记录执行时间超过 long_query_time 值的所有 SQL 语句，这个时间值是可以配置的，默认情况下，慢查询日志功能是关闭的
一般查询日志（General Query Log）：记录 MySQL 服务器的启动关闭信息，客户端的连接信息，以及更新、查询的 SQL 语句等
二进制日志（Binary Log）：记录所有修改数据库状态的 SQL 语句，以及每个语句的执行时间，如 insert、update、delete 等，但不包括 select 和 show 这类的操作
重做日志（Redo Log）：记录对于 InnoDB 表的每个写操作，不是 SQL 级别的，而是物理级别的，主要用于崩溃恢复
回滚日志（Undo Log，或者叫事务日志）：记录数据被修改前的值，用于事务的回滚

其中错误日志用于问题诊断，慢查询日志用于 SQL 性能分析，general log 用于记录所有的 SQL 语句，binlog 用于主从复制和数据恢复，redo log 用于保证事务持久性，undo log 用于事务回滚和 MVCC

2. binlog 和 redo log 有什么区别？

大厂基本功 | MySQL 三大日志 ( binlog、redo log 和 undo log ) 的作用？ - 知乎

[[redo log 和 undo log]]

binlog 由 MySQL 的 Server 层实现，与存储引擎无关，redo log 由 InnoDB 存储引擎实现

binlog 记录的是逻辑日志，包括原始的 SQL 语句或者行数据变化，例如将 id = 2 这行数据的 age 字段 + 1

redo log 记录物理日志，即数据页的具体修改，例如将 page_id=123 上 offset=0x40 的数据从 18 修改为 26

binlog 是追加写入的，文件写满后会新建文件继续写入，不会覆盖历史日志，保存的是全量操作记录，redo log 是循环写入的，空间是固定的，写满后会覆盖旧的日志，仅保存未刷盘的脏页日志，已持久化的数据会被清除

另外，为保证两种日志的一致性，InnoDB 采用了两阶段提交策略，redo log 在事务执行过程中持续写入，并在事务提交前进入 prepare 状态，binlog 在事务提交的最后阶段写入，之后 redo log 会被标记为 commit 状态

可以通过回放 binlog 实现数据同步或者恢复到指定时间点；redo log 用来确保事务提交后即使系统宕机，数据仍然可以通过重放 redo log 恢复

3. 为什么要两阶段提交呢？

这是为了保证数据一致性。

我们把redo log和binlog的提交分成两步：

prepare：redo log写完了。
commit：binlog写完了，再把redo log状态改为commit。

这样做的原因是：

如果写完redo log（prepare）后，在写binlog之前崩溃：
- 重启恢复时，发现redo log是prepare状态但binlog不完整，则会回滚事务。两个日志的数据一致。
如果写完binlog后，在redo log commit之前崩溃：
- 重启恢复时，虽然redo log是prepare状态，但binlog是完整的（因为已经写完了）。MySQL会接受这个binlog，自动重做这个事务（将redo log状态改为commit并应用更新）。两个日志的数据依然一致。

如果没有两阶段提交，先写redo log再写binlog，或者反过来，在崩溃恢复时都无法保证两份日志的逻辑一致性，会导致主从数据库数据不一致或用binlog恢复出的数据与原库数据不一致。

4. [[redo log 的写入过程了解吗？]]

5. 什么是慢 SQL

MySQL 中有一个叫 long_query_time 的参数，原则上执行时间超过该参数值的 SQL 就是慢 SQL，会被记录到慢查询日志中

如何优化慢 SQL 呢？

首先，需要找到那些比较慢的 SQL，可以通过启用慢查询日志，记录那些超过指定执行时间的 SQL 查询，也可以使用 show processlist; 命令查看当前正在执行的 SQL 语句，找出执行时间较长的 SQL

或者在业务基建中加入对慢 SQL 的监控，常见的方案有字节码插桩、连接池扩展、ORM 框架扩展等

然后，使用 explain 查看慢 SQL 的执行计划，看看有没有用索引，大部分情况下，慢 SQL 的原因都是因为没有用到索引

最后，根据分析结果，通过添加索引、优化查询条件、减少返回字段等方式进行优化

哪些方法来优化 SQL？

SQL 优化的方法非常多，但本质上就一句话：尽可能少的扫描、尽快的返回结果

最常见的做法就是加索引、改写 SQL 让它用上索引，比如说使用覆盖索引、让联合索引遵守最左前缀原则等

查询优化：

不要 select *

分页优化：

延迟关联
书签

索引优化：

索引覆盖
避免使用 !=/<>
适当使用前缀索引
避免列上函数运算
正确使用联合索引

join 优化：

优化子查询
小表驱动大表
适当增加冗余字段
避免 join 太多的表

排序优化：

利用索引扫描做排序

union 优化：

条件下推
union all 替代 union

如何进行分页优化？

分页优化的核心是避免深度偏移带来的全表扫描，可以通过两种方式来优化：延迟关联和添加书签

延迟关联适用于需要从多个表中获取数据且主表行数较多的情况，它首先从索引表中检索出需要的行 ID，然后再根据这些 ID 去关联其它的表获取详细信息

添加书签的方式是通过记住上一次查询返回的最后一行主键值，然后在下一次查询的时候从这个值开始，从而跳过偏移量计算，仅扫描目标数据，适合翻页、资讯流等场景

为什么分页会变慢？

分页查询的效率问题主要是由于 offset 的存在，offset 会导致 MySQL 必须扫描和跳过 offset + limit 条数据，这个过程是非常耗时的

比如说，我们要查询第 100000 条数据，那么 MySQL 就必须扫描 100000 条数据，然后再返回 10 条数据

数据越多，偏移越大，就越慢

join 代替子查询有什么好处？

join 的 on 条件能更直接的触发索引，而子查询可能因嵌套导致索引失效
join 的一次连接操作替代了子查询的多次重复执行，尤其在大数据量的情况下性能差异明显

比如有两张表 orders 和 customers

子查询的写法：

join 的写法：

对于子查询，执行流程是这样的：

外层 orders 表的每一行都会触发一次子查询
如果 orders 表有 1000 条记录，则子查询会执行 1000 次
每次子查询都需要单独查询 customers 表（即使 customer_id 相同）

而 join 的执行流程是这样的：

数据库优化器会将两张表的连接操作合并为一次执行
通过索引（如 orders.customer_id 和 customers.customer_id）快速关联数据
仅执行一次关联操作，而非多次子查询

如何进行排序优化？

对 order by 涉及的字段创建索引，避免 filesort
如果是多个字段，联合索引需要保证 order by 的列是索引的最左前缀
可以适当调整排序参数，如增大 sort_buffer_size、max_length_for_sort_data 等，让排序在内存中完成
可以通过 where 和 limit 限制待排序的数据量，减少排序的开销

什么是 filesort

当不能使用索引生成排序结果的时候，MySQL 需要自己进行排序，如果数据量比较小，会在内存中进行，如果数据量比较大就需要写临时文件到磁盘再排序，我们将这个过程称为文件排序

全字段排序和 rowid 排序

当排序字段是索引字段且满足最左前缀原则时，MySQL 可以直接利用索引的有序性完成排序

当无法使用索引排序时，MySQL 需要在内存或磁盘中进行排序操作，分为全字段排序和 rowid 排序两种算法：

全字段排序会一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序，排序后直接返回结果，无需回表
- 以 select * from user where name = "张三" order by age 为例
- 从 name 索引中找到第一个满足 name = "张三" 的主键 id
- 根据主键 id 取出整行所有的字段，存入 sort buffer
- 重复上述过程直到处理完所有满足条件的行
- 对 sort buffer 中的数据按 age 排序，返回结果
- 优点：仅需要一次磁盘 IO
- 缺点：内存占用大，如果数量超过 sort buffer 的话，需要分片读取并借助临时文件合并排序，IO 次数反而会增加，也无法处理包含 text 和 blob 类型的字段
rowid 排序：
- 第一阶段：根据查询条件取出排序字段和主键 ID，存入 sort buffer 进行排序
- 第二阶段：根据排序后的主键 ID 回表取出其它需要的字段
- 同样以 select * from user where name = "张三" order by age 为例
- 从 name 索引中找到第一个满足 name = "张三" 的主键 id
- 根据主键 id 取出排序字段 age，连同主键 id 一起存入 sort buffer
- 重复上述过程直到处理完所有满足条件的行
- 对 sort buffer 中的数据按 age 排序
- 遍历排序后的主键 id，回表取出其它所需字段，返回结果
- 优点：内存占用较少，适合字段多或者数据量大的场景
- 缺点：需要两次磁盘 IO

6. SQL 的执行过程

SQL 的执行过程大致可以分为六个阶段：连接管理、语法解析、语义分析、查询优化、执行器调度、存储引擎读写等，Server 层负责理解和规划 SQL 怎么执行，存储引擎层负责数据的真正读写

客户端发送 SQL 语句给 MySQL 服务器
如果查询缓存打开则会优先查询缓存，缓存中有对应的结果就直接返回，不过，MySQL 8.0 已经移除了查询缓存，这部分的功能正在被 Redis 等缓存中间件取代
分析器对 SQL 语句进行语法分析，判断是否有语法错误
搞清楚 SQL 语句要干嘛后，MySQL 会通过优化器生成执行计划
执行器调用存储引擎的接口，执行 SQL 语句

SQL 执行过程中，优化器通过成本计算预估出执行效率最高的方式，基本的预估维度为：

IO 成本：从磁盘读取数据到内存的开销
CPU 成本：CPU 处理内存中数据的开销

基于这两个维度，可以得出影响 SQL 执行效率的因素有：

IO 成本：数据量越大，IO 成本越高，所以要尽量查询必要的字段，尽量分页查询，尽量通过索引加快查询
CPU 成本：尽量避免复杂的查询条件，如有必要，考虑对子查询结果进行过滤

7. explain 执行计划

explain 是 MySQL 提供的一个用于查看 SQL 执行计划的工具，可以帮助我们分析查询语句的性能问题

比如说 type=ALL,key=NULL 表示 SQL 正在全表扫描，可以考虑为 where 字段添加索引进行优化

Extra=Usingfilesort 表示 SQL 正在文件排序，可以考虑为 order by 字段添加索引

在 explain 输出结果中我最关注的字段是 type、key、rows 和 Extra

我会通过它们判断 SQL 有没有走索引、是否全表扫描、预估扫描行数是否太大，以及是否触发了 filesort 或临时表，一旦发现问题，比如 type=ALL 或者 Extra=Using filesort，我会考虑建索引、改写 SQL 或控制查询结果集来做优化

type 的执行效率等级，达到什么级别比较合适？

从高到低的效率排序是：system、const、eq_ref、ref、range、index 和 ALL

一般情况下，建议 type 值达到 const、eq_ref 或 ref，因为这些类型表明查询使用了索引，效率较高

如果是范围查询，range 类型也是可以接受的

8. 创建索引有哪些注意点

选择合适的字段
- 比如说频繁出现在 where、join、order by、group by 中的字段
- 优先选择区分度高的字段，比如用户 ID、手机号等唯一值多的，而不是性别、状态等区分度极低的字段，如果真的需要，可以考虑联合索引
要控制索引的数量，避免过渡索引，每个索引都要占用存储空间，单表的索引数量不建议超过 5 个
联合索引的时候要遵循最左前缀原则

9. 为什么 MongoDB 的索引用 B 树，而 MySQL 用 B+ 树？

MongoDB 通常以 JSON 格式存储文档，查询以单键查询（如 find({_id: 123})）为主，B 树的节点既存键又存数据的特性允许查询在非叶子节点提前终止，从而减少 IO 次数

10. 每个叶子节点能存放多少条数据？

如果单行数据大小为 1KB，那么每页可存储约 16 行（16KB/1KB）数据

假设有这样一个表结构：

那么一行数据的大小为：8 + 50 + 1 + 30 = 89 字节

行格式的开销为：行头 5 字节 + 指针 6 字节 + 可变长度字段开销 2 字节（name 和 email 各占 1 字节） + NULL 位图 1 字节 = 14 字节

所以每行数据的实际大小为：89 + 14 = 103 字节

每页大小默认为 16KB，那么每页最多可以存储 16384 / 103 = 158 行数据

一棵 B+ 树能存储多少条数据呢？

一棵 B+ 树能存多少数据，取决于它的分支因子和高度，在 InnoDB 中，页的默认大小为 16KB，当主键为 bigint 时，3 层 B+ 树通常可以存储约 2000 万条数据

计算：

关键参数：

页大小：默认 16KB
主键大小：假设是 bigint 类型，那么它的大小就是 8 个字节
页指针大小：InnoDB 源码中设置为 6 字节

所以非叶子节点可以存储 16384 / 14（键值 + 指针） = 1170 个这样的单元

当层高为 2 时，根节点可以存储 1170 个指针，指向 1170 个叶子节点，所以总数据量为 1170 x 16 = 18720 条

当层高为 3 时，根节点指向 1170 个非叶子节点，每个非叶子节点再指向 1170 个叶子节点，所以总数据量为 1170x1170x16=21902400 条（约 2190 万条）记录

11. 联合索引

联合索引就是把多个字段放在一个索引里，但必须遵守最左前缀原则，只有从第一个字段开始连续使用，索引才会生效

联合索引会按字段顺序构建 B+ 树，例如 (age, name) 索引会先按照 age 排序，age 相同则按照 name 排序，若两者都相同则按主键排序，确保叶子节点无重复索引项

创建 (A,B,C) 联合索引相当于同时创建了 (A)、(A, B) 和 (A, B, C) 三个索引

联合索引底层的存储结构是怎样的？

联合索引在底层采用 B+ 树结构进行存储，这一点与单列索引相同

与单列索引不同的是，联合索引的每个节点会存储所有索引列的值，而不仅仅是第一列的值，例如，对于联合索引 (a, b, c)，每个节点都包含 a、b、c 三列的值

联合索引属于非聚集索引，叶子节点存储的是联合索引各列的值和对应行的主键值，而不是完整的数据行，查询非索引字段时，需要通过主键值回表到聚集索引获取完整数据

12. 索引下推

索引下推是指 MySQL 把 where 条件尽可能下推到索引扫描阶段，在存储引擎层提前过滤掉不符合条件的记录

当查询条件包含索引列但未完全匹配时，ICP 会在存储引擎层过滤非索引列条件，以减少回表次数

传统的查询流程是：存储引擎通过联合索引定位到符合最左前缀条件的主键 ID，回表读取完整数据行并返回给 Server 层，Server 层对所有返回的行进行 where 条件过滤

有了 ICP 后，存储引擎在索引层直接过滤可下推的条件，仅对符合索引条件的记录回表读取数据，再返回给 Server 层进行剩余条件过滤

13. 遇到过 MySQL 死锁问题吗，怎么解决？

MySQL 的死锁是由于多个事务持有资源并相互等待引起的，我通过 show engine innodb status 查看死锁信息，定位到是加锁顺序不一致导致的，最后通过调整加锁顺序解决这个问题

14. 怎么分库？

分库的策略有两种：

第一种是垂直分库：按照业务模块将不同的表拆分到不同的库中，比如说用户、登录、权限等表放在用户库中，商品、分类、库存放在商品库中，优惠券、满减、秒杀放在活动库中

第二种是水平分库：按照一定的策略将一个表中的数据拆分到多个库中，比如哈希分片和范围分片，对用户 id 进行取模运算或者范围划分，将数据分散到不同的库中

15. 怎么分表？

当单表超过 500 万条数据，就可以考虑水平分表了，比如说我们可以将文章表拆分成多个表，如 article_0、article_9999、article_19999 等

16. 水平分库分表的分片策略有哪几种？

常见的分片策略有三种，范围分片、Hash 分片和路由分片

范围分片是根据某个字段的值范围进行水平拆分，适用于分片键具有连续性的场景

比如说将 user_id 作为分片键：

Hash 分片是指通过对分片键的值进行哈希取模，将数据均匀分布到多个库表中，适用于分片键具有离散性的场景

路由分片是通过路由配置来确定数据应该存储在哪个库表，适用于分片键不规律的场景

17. 分库分表带来的问题？

跨库事务无法依赖单机 MySQL 的 ACID 特性，需要使用分布式事务解决方案，如 Seata 的 AT 模式、TCC 模式等
跨库后无法使用 join 联表查询，可以在业务层进行拼接，或者把需要联表查询的数据放到 ES 中
自增 ID 在分片场景下容易冲突，需要使用全局唯一方案

MySQL ​

第 1 章 MySQL 概述 ​

1.1 数据库相关概念 ​

1.2 MySQL 数据库 ​

第 2 章 SQL ​

2.1 SQL 通用语法 ​

2.2 SQL 分类 ​

2.3 DDL ​

2.3.1 DDL - 数据库操作 ​

2.3.2 DDL - 表操作 - 查询 ​

2.3.3 DDL - 表操作 - 创建 ​

2.3.4 DDL - 表操作 - 数据类型 ​

2.3.4.1 数值类型 ​

2.3.4.2 字符串类型 ​

2.3.4.3 日期时间类型 ​

2.3.5 DDL - 表操作 - 修改 ​

2.3.6 总结 ​

2.4 DML ​

2.4.1 介绍 ​

2.4.2 DML - 添加数据 ​

2.4.3 DML - 修改数据 ​

2.4.4 DML - 删除数据 ​

2.4.5 总结 ​

2.5 DQL ​

2.5.1 介绍 ​

2.5.2 语法 ​

2.5.3 DQL - 基本查询 ​

2.5.4 DQL - 条件查询 ​

2.5.5 DQL - 聚合函数 ​

2.5.6 DQL - 分组查询 ​

2.5.7 DQL - 排序查询 ​

2.5.8 DQL - 分页查询 ​

2.5.9 案例 ​

2.5.10 DQL - 执行顺序 ​

2.6 DCL ​

2.6.1 DCL - 介绍 ​

2.6.2 DCL - 管理用户 ​

2.6.3 DCL - 权限控制 ​

第 3 章 函数 ​

3.1 字符串函数 ​

3.2 数值函数 ​

3.3 日期函数 ​

3.4 流程函数 ​

第 4 章 约束 ​

4.1 概述 ​

4.2 约束演示 ​

4.3 外键约束 ​

4.3.1 概念 ​

4.3.2 语法 ​

4.3.3 外键约束的删除/更新行为 ​

第 5 章 多表查询 ​

5.1 多表关系 ​

5.1.1 概述 ​

5.1.2 一对多（多对一） ​

5.1.3 多对多 ​

5.1.4 一对一 ​

5.2 多表查询概述 ​

5.3 内连接 ​

5.4 外连接 ​

5.5 自连接 ​

5.6 联合查询 - union，union all ​

5.7 子查询 ​

5.8 多表查询案例 ​

5.9 表复制 ​

5.10 总结 ​

第 6 章 事务 ​

6.1 事务简介 ​

6.2 事务操作 ​

6.3 事务四大特性【ACID】 ​

6.4 并发事务产生的问题 ​

6.5 事务的隔离级别 ​

第 7 章 存储引擎 ​

7.1 MySQL 体系结构 ​

7.2 存储引擎简介 ​

7.3 存储引擎特点 ​

7.4 存储引擎选择 ​

第 8 章 索引 ​

8.1 索引概述 ​

8.2 索引结构 ​

8.3 索引分类 ​

MySQL

第 1 章 MySQL 概述

1.1 数据库相关概念

1.2 MySQL 数据库

第 2 章 SQL

2.1 SQL 通用语法

2.2 SQL 分类

2.3 DDL

2.3.1 DDL - 数据库操作

2.3.2 DDL - 表操作 - 查询

2.3.3 DDL - 表操作 - 创建

2.3.4 DDL - 表操作 - 数据类型

2.3.4.1 数值类型

2.3.4.2 字符串类型

2.3.4.3 日期时间类型

2.3.5 DDL - 表操作 - 修改

2.3.6 总结

2.4 DML

2.4.1 介绍

2.4.2 DML - 添加数据

2.4.3 DML - 修改数据

2.4.4 DML - 删除数据

2.4.5 总结

2.5 DQL

2.5.1 介绍

2.5.2 语法

2.5.3 DQL - 基本查询

2.5.4 DQL - 条件查询

2.5.5 DQL - 聚合函数

2.5.6 DQL - 分组查询

2.5.7 DQL - 排序查询

2.5.8 DQL - 分页查询

2.5.9 案例

2.5.10 DQL - 执行顺序

2.6 DCL

2.6.1 DCL - 介绍

2.6.2 DCL - 管理用户

2.6.3 DCL - 权限控制

第 3 章函数

3.1 字符串函数

3.2 数值函数

3.3 日期函数

3.4 流程函数

第 4 章约束

4.1 概述

4.2 约束演示

4.3 外键约束

4.3.1 概念

4.3.2 语法

4.3.3 外键约束的删除/更新行为

第 5 章多表查询

5.1 多表关系

5.1.1 概述

5.1.2 一对多（多对一）

5.1.3 多对多

5.1.4 一对一

5.2 多表查询概述

5.3 内连接

5.4 外连接

5.5 自连接

5.6 联合查询 - union，union all

5.7 子查询

5.8 多表查询案例

5.9 表复制

5.10 总结

第 6 章事务

6.1 事务简介

6.2 事务操作

6.3 事务四大特性【ACID】

6.4 并发事务产生的问题

6.5 事务的隔离级别

第 7 章存储引擎

7.1 MySQL 体系结构

7.2 存储引擎简介

7.3 存储引擎特点

7.4 存储引擎选择

第 8 章索引

8.1 索引概述

8.2 索引结构

8.3 索引分类