MySQL-Notes

数据库：MySQL笔记

如题。

1. 数据库与MySQL基本概念

1.1 数据库基本概念

  • 数据库（DB，database）：数据存储的仓库，数据库又可以分为关系型数据库和非关系型数据库。
  • 关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
  • 数据库管理系统（DBMS，database management system）：操纵和管理数据库的大型软件，包括Oracle、MySQL、IBM db2等等。
  • SQL（structed query language）：操作关系型数据库的编程语言，是一套统一标准，即对于不同的DBMS，都可以通过同样的SQL去操作。

1.2 MySQL介绍与使用

MySQL是主流的DBMS之一，用于操作关系型数据库，该软件的架构为C-S模式，如图所示。

每一个MySQL服务器中可以建立多个关系型数据库，每个关系型数据库由若干表组成。

下载安装MySQL后，使用MySQL服务前要先启动服务端。安装的时候默认是自动启动的，如果没有选择自动启动或者关闭了服务端，可以在命令行（需管理员模式/root）中输入命令。

1
2
3
# Windows
net start mysql80 # 启动服务
net stop mysql80 # 关闭服务 
1
2
3
4
5
# Ubuntu
systemctl start mysql
systemcts restart mysql
systemctl stop mysql
systemctl status mysql

在服务器正常运行之后，通过客户端与服务端进行连接，可以通过MySQL提供的命令行，输入密码连接；也可以直接使用操作系统（如Windows）的命令行，输入指令连接（需要将mysql写入系统环境变量）。

1
2
3
mysql -u root -p 
# -h xx.xx.xx.xx  指定ip地址
# -P xxxx 指定连接端口，MySQL服务端默认监听3306端口

随后输入密码，即可进入。

2. SQL语法与单表操作

2.1 SQL通用语法

  1. sql可以单行或多行书写，以分号结尾；
  2. sql语句可以使用空格/缩进来增强语句的可读性；
  3. mysql数据库的sql语句不区分大小写，关键字建议使用大写。
  4. 注释：
    1. 单行注释: -- 注释内容 或 # 注释内容（MySQL特有）
    2. 多行注释：/* 注释内容 */

2.2 DDL

DDL（Data Definition Language，数据定义语言），用来定义数据库对象（数据库、表、字段）。

2.2.1 数据库DDL

  • 查询
1
2
3
SHOW DATABASES; -- 查询所有数据库

SELECT DATABASE(); -- 查询当前数据库
  • 创建
1
2
CREATE DATABASE [IF NOT EXISTS] 数据库名 
[DEFAULT CHARSET 字符集] [COLLATE 排序规则]; -- 创建数据库
  • 删除
1
DROP DATABASE [IF EXISTS] 数据库名; -- 删除某个数据库
  • 使用
1
USE 数据库名; -- 使用某个数据库

2.2.2 表操作

  • 查询
1
2
3
4
5
SHOW TABLES; -- 查询当前数据库所有表

DESC 表名; -- 查询表结构

SHOW CREATE TABLE 表名; -- 查询指定表的建表语句
  • 创建
1
2
3
4
5
6
7
CREATE TABLE 表名(
    字段 字段类型 [约束] [COMMENT 字段注释],
    ....
    字段 字段类型 [约束] [COMMENT 字段注释]
)[COMMENT 表注释];

-- `COMMENT`关键字后的注释是会被写入数据库的，和`--`的注释不同。
  • 修改
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 添加字段
ALTER TABLE 表名 
ADD 字段名 数据类型(长度) [约束] [COMMENT 字段注释]; 

-- 修改数据类型
ALTER TABLE 表名 
MODIFY 字段名 新数据类型(长度) [约束] [COMMENT 字段注释]; 

--修改字段名和字段类型
ALTER TABLE 表名 
CHANGE 旧字段名 新字段名 新数据类型(长度) [约束] [COMMENT 字段注释];

-- 删除字段
ALTER TABLE 表名 
DROP 字段名; 

-- 重命名表
ALTER TABLE 表名 
RENAME TO 新表名;
  • 删除
1
2
3
4
5
6
7
8
-- 删除表
DROP TABLE 
[IF EXISTS] 表名; 

-- 删除指定表，并重新创建该表
TRUNCATE TABLE 表名; 

-- 在删除表时，表中的数据也会被删除。

2.2.3 MySQL字段类型

  • 数值类型
类型 大小 有符号 (SIGNED) 范围 无符号 (UNSIGNED) 范围 描述
TINYINT 1 byte (-128, 127) (0, 255) 小整数值
SMALLINT 2 bytes (-32768, 32767) (0, 65535) 大整数值
MEDIUMINT 3 bytes (-8388608, 8388607) (0, 16777215) 大整数值
INT（INTEGER） 4 bytes (-2147483648, 2147483647) (0, 4294967295) 大整数值
BIGINT 8 bytes (−2^63, 2^63−1) (0, 2^64−1) 极大整数值
FLOAT 4 bytes (~−3.402823466E+38, ~3.402823466E+38) 0 和 (~1.175494351E−38, ~3.402823466E+38) 单精度浮点数值
DOUBLE 8 bytes (~−1.7976931348623157E+308, ~1.7976931348623157E+308) 0 和 (~2.2250738585072014E−308, ~1.7976931348623157E+308) 双精度浮点数值
DECIMAL 依赖于 M(精度) 和 D(标度) 依赖于 M(精度) 和 D(标度) 小数值（精确定点数）
  • 字符串类型
类型 大小 描述
CHAR 0–255 bytes 定长字符串
VARCHAR 0–65535 bytes 变长字符串
TINYBLOB 0–255 bytes 不超过 255 个字节的二进制数据
TINYTEXT 0–255 bytes 短文本字符串
BLOB 0–65 535 bytes 二进制形式的长文本数据
TEXT 0–65 535 bytes 长文本数据
MEDIUMBLOB 0–16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0–16 777 215 bytes 中等长度文本数据
LONGBLOB 0–4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0–4 294 967 295 bytes 极大文本数据
  1. MySQL可以存储二进制数据，但由于性能不高，在开发中常常不这么做，而是将二进制数据存放在专有的文件服务器上。
  1. CHAR和VARCHAR对比：CHAR和VARCHAR需要通过()指定存储的字符串的字符长度；CHAR类型存放数据，即使数据的长度小于声明长度，也会补全，因此为定长，而VARCHAR不会进行补全；CHAR类型数据插入性能高，VARCHAR类型数据插入性能低。
  • 日期类型
类型 大小 范围 格式 描述
DATE 3 1000-01-01 至 9999-12-31 YYYY-MM-DD 日期值
TIME 3 -838:59:59 至 838:59:59 HH:MM:SS 时间值或持续时间
YEAR 1 1901 至 2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00 至 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:01 至 2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 混合日期和时间值，时区相关

2.3 DML

DML（Data Manipulation Language），数据操作语言，用来对数据库表中的数据进行增删改操作。

  • 增
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 指定字段添加数据 
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,...)...;

-- 1. 插入的字段顺序与值的顺序一一对应。
-- 2. 字符串和日期型数据的值应该包含在引号`''`内。
-- 3. 插入的数据大小，应该在字段的规定范围内。
  • 改
1
2
3
4
5
6
UPDATE 表名
SET 字段名1 = 值1, 字段名2=值2,...
[WHERE 条件];

-- `WHERE`语句为而可选项，如果没有条件，则会修改整张表所有的数据。
-- `WHERE`语句的条件可选项可以见DQL的条件查询。
  • 删
1
2
3
4
5
6
DELETE FROM 表名
[WHERE 条件];

-- `WHERE`语句为可选项，如果没有条件，则会删除整张表中的数据。
-- `WHERE`语句的条件可选项可以见DQL的条件查询。
-- `DELETE`语句删除的是一条或多条**完整的记录**，不能删除某条记录的字段，后者可以使用`UPDATE`语句将字段值赋值为空。

2.4 DQL

DQL（Data Query Language，数据查询语言），用来查询数据库中表的记录，基本格式如下：

1
2
3
4
5
6
7
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数;

2.4.1 基本查询

1
2
3
4
5
6
7
8
9
10
-- 查询全部字段
SELECT 字段1, 字段2, ... FROM 表名;
SELECT * FROM 表名;

-- 设置字段别名
SELECT 字段1 AS 别名1, 字段2 AS 别名2, ... FROM 表名;

-- 去重查询 DISTINCT
SELECT DISTINCT 字段列表 FROM 表名;

2.4.2 条件查询

1
SELECT 字段列表 FROM 表名 WHERE 条件列表;

WHERE语句中的条件表达式可使用比较运算符和逻辑运算符进行构建，具体可用符号如下：

  • 比较运算符
比较运算符 功能
> 大于
>= 大于等于
< 小于
<= 小于等于
= 等于
<> 或 != 不等于
BETWEEN 最小值 AND 最大值 在某个范围之内（包含最小、最大值）
IN(...) 在IN后的列表中的值
LIKE ... 模糊匹配 _匹配单个字符，%匹配任意个字符（占位符）
IS NULL 判断为空
  • 逻辑运算符
逻辑运算符 功能
AND 或 && 并（多个条件同时成立）
OR 或 || 或（多个条件任意一个成立）
NOT 或 ! 取反（不是）

2.4.3 聚合函数和分组查询

  • 聚合函数

聚合函数作用于某一列数据，进行纵向计算，常见聚合函数如下：

聚合函数 功能
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和

使用聚合函数时，指定聚合函数作用的列（即字段名）即可：

1
SELECT 聚合函数(字段列表) FROM 表名;
  1. NULL值不参与聚合函数运算。
  1. AVG后也可以加上逻辑表达式，计算的就是逻辑表达式为真的占比，常用来替代SUM(IF...) / COUNT(*)的逻辑，在sql语句中很常用。
  • 分组查询

分组查询常常需要用到聚合函数，其基本语法如下：

1
2
3
4
5
SELECT 字段列表
FROM 表名
[WHERE 条件]
GROUP BY 分组字段名
[HAVING 分组后过滤的条件]
  1. WHERE在分组之前进行过滤，不满足条件，不参与分组（实际作用对象是每一条记录）；HAVING是分组后对结果进行过滤（实际作用结果是对每一个组）。
  1. 在WHERE语句中不能使用聚合函数，在HAVING语句中可以使用聚合函数。
  1. 执行顺序：WHERE -> SELECT后的聚合函数 -> HAVING。
  1. 使用分组查询，SELECT后的字段一般为聚合函数及分组字段，查询其他字段无意义。

2.4.4 排序查询

1
2
3
SELECT 字段列表
FROM 表名
ORDER BY 字段1 排序方式1, 字段2 排序方式2, ... ;

排序方式有两种可选：

  1. ASC：升序（默认值）
  2. DESC：降序

多字段排序指的是先按照第一个字段以及排序方式进行排序，如果第一个字段相同，则按照第二个字段以及排序方式进行排序，以此类推。

2.4.5 分页查询

1
2
3
SELECT 字段列表
FROM 表名
LIMIT 起始索引, 查询记录数;
  1. 起始索引从0开始，起始索引=（查询页码 - 1）* 每页显示记录数。（查询页码是从1开始）
  1. 如果查询的是第一页数据，起始索引可以省略，直接简写为LIMIT 查询记录数。

2.4.6 DQL语句执行顺序

  1. FROM语句定位对应的表。
  2. WHERE语句对记录进行筛选。
  3. GROUP BY根据字段对记录进行分组。
  4. HAVING对组进行筛选。
  5. SELECT从筛选的结果中选出相应的字段名以及对应的字段值/聚合函数作用后的字段值。
  6. ORDER BY对于选出的最终结果进行排序。
  7. LIMIT对于最终结果进行分页展示。
  1. 这里会涉及到使用别名的作用域问题。
  1. 只要别名是在同一个查询块的 SELECT 子句里定义的，MySQL就允许在同级的 ORDER BY、GROUP BY、HAVING 中直接引用它——解析器会先解析SELECT列表，把别名和它的表达式对应起来，然后在构造执行计划时替换回去。（MySQL特有）

2.5 DCL

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

2.5.1 管理用户

  • 查询用户
1
2
USE mysql;
SELECT * FROM user;
  • 创建用户
1
2
3
4
CREATE USER '用户名'@'主机名' 
IDENTIFIED BY '密码';

-- 如果希望用户在任何主机都可以访问数据库，可以将`'主机名'`替换为`'%'`。
  • 修改用户密码
1
2
ALTER USER '用户名'@'主机名'
IDENTIFIED with mysql_native_password BY '新密码';
  • 删除
1
DROP USER '用户名'@'主机名';

2.5.2 管理权限

  • 权限分类

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

权限 说明
ALL, ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 创建数据库/表
  • 查询权限
1
2
SHOW GRANTS
FOR '用户名'@'主机名';
  • 授予权限
1
2
3
GRANT 权限列表
ON 数据库.表名
TO '用户名'@'主机名';
  • 撤销权限
1
2
3
4
5
REVOKE 权限列表
ON 数据库.表名
FROM '用户名'@'主机名';

-- 这里的数据库和表名，如果需要是全部，可以用`*`来代替。

2.6 内置函数

函数是指一段可以直接被另一段程序调用的程序或代码，在MySQL中内置了许多不同的函数。

2.6.1 字符串函数

函数 功能
CONCAT(S1, S2, ... SN) 将S1、S2...SN拼接成一个字符串
LOWER(str) 将字符串str全部转为小写
UPPER(str) 将字符串str全部转为大写
LPAD(str, n, pad) 在字符串左侧使用pad进行填充，直到字符串总长度达到n；如果本身str已经超过了n，则对str进行截取，取左侧n个字符
RPAD(str, n, pad) 在字符串右侧使用pad进行填充，直到字符串总长度达到n；如果本身str已经超过了n，则对str进行截取，取右侧n个字符
TRIM(str) 去掉字符串头部和尾部的空格
SUBSTRING(str, start, len) 返回字符串str从start位置起的len个长度的字符串（索引从1开始）， 当省略第三个字段的时候，默认到字符串末尾
CHAR_LENGTH(str) 计算字符长度，无论中文字符、英文字符、数字都算1个长度
LENGTH(str) 计算字节长度（与编码有关），英文字符、数字仍然为一个字节，汉字在utf8中三个字节，在gbk中两个字节

2.6.2 数值函数

函数 功能
CEIL(x) 向上取整
FLOOR(x) 向下取整
MOD(x, y) 返回 x mod y的结果
RAND() 返回0~1内的随机数
ROUND(x, y) 求参数x的四舍五入值，保留y位小数

2.6.3 日期函数

函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定date的年份
MONTH(date) 获取指定date的月份
DAY(date) 获取指定date的日
DATE_ADD(date, INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2) 返回起始时间date1和结束时间date2之间的天数。（date1 - date2）
DATE_FORMAT(date, format) 用于以不同的格式显示日期/时间数据。date 参数是合法的日期，format 规定日期/时间的输出格式，如%Y表示年份，%M表示月份

2.6.4 流程函数

函数 功能
IF(value, t, f) 如果value为true，返回t，否则返回f
IFNULL(value1, value2) 如果value1不为空，返回value1，否则返回value2
CASE WHEN val1 THEN res1 ... ELSE default END 如果val1为true，返回res1，否则返回default默认值
CASE expr WHEN val1 THEN res1 ... ELSE default END 如果expr的值等于val1，返回res1，否则返回default默认值

2.7 约束

约束是作用于表中字段上的限制，用于限制存储在表中的数据，常用于DDL语句中有关表的操作，如创建表、修改表中字段结构；使用约束用来保证数据库中数据的正确、有效性和完整性。

2.7.1 约束的分类

约束 描述 关键字
非空约束 限制字段的数据不能为null NOT NULL
唯一约束 保证该字段的所有数据都是唯一的，即不能重复 UNIQUE
主键约束 主键是一行数据的唯一标识，要求非空且唯一 PRIMARY KEY
默认约束 保存数据时，如果未指定该字段的值，则采用默认值 DEFAULT
检查约束（8.0.16版本后） 保证字段值满足某个条件 CHECK
外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 FOREIGN KEY
自动增长 用来让插入数据的该字段值自动增长 AUTO_INCREMENT
  1. 同一个字段可以有多个约束条件，多个约束之间只需要空格隔开即可。
  1. 可以指定多个字段为主键，以primary key (字段列表)的形式。
  1. 可以用CONSTRAINT关键字给约束命名，语法为CONSTRAINT 约束名 约束类型。
  • 约束使用案例
1
2
3
4
5
6
7
8
-- 约束创建表格demo
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
    age INT CHECK ( age > 0 AND age <= 120 ) COMMENT '年龄',
    status CHAR(1) DEFAULT '1' COMMENT '状态',
    gender CHAR(1) COMMENT '性别'
) COMMENT '用户表';

2.7.2 外键约束详解

外键所在的当前表为子表/从表，外键所指向的外部表为父表/主表。

  • 添加外键
1
2
3
4
5
6
7
8
9
10
-- 创建表的时候添加外键
CREATE TABLE 表名(
    字段名 数据类型,
    ...
    [CONSTRAINT 外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);

-- 修改表的时候添加外键
ALTER TABLE 表名
ADD [CONSTRAINT 外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
  • 删除外键
1
2
ALTER TABLE 表名
DROP FOREIGN KEY 外键名称;
  • 外键关系更新/删除行为
行为名 说明
NO ACTION 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
RESTRICT 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
CASCADE 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录。
SET NULL 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null。（这就要求该外键允许取null）
SET DEFAULT 父表有变更时，子表将外键列设置为一个默认的值（Innodb不支持）

默认的行为模式为NO ACTION和RESTRICT，这两者的行为是一致的。

设置行为模式的语法如下：

1
2
3
4
ALTER TABLE 表名
ADD [CONSTRAINT 外键名称] FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名)
ON UPDATE [行为名]
ON DELETE [行为名];

3. 多表与高级查询

3.1 多表关系与多表查询

  • 多表关系
  1. 一对多（多对一）

案例：部门与员工的关系，一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，指向一的一方的主键。

如案例中，员工为多的一方，则员工表中建立一个外键，对应部门ID（部门表中的主键）。

  1. 多对多

案例：学生与课程的关系，一个学生可以选修多门课程，一门课程也可以供多个学生选择。

实现：抽出两张表的主键，作为第三张表的两个外键。

  1. 一对一

案例：用户与用户详情的关系

常用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，提升操作效率。

实现：选取任意一张表建立外键，指向另一张表的主键，并且要求该外键的约束条件为UNIQUE。

  • 多表查询

多表查询就是在一条DQL语句中，从多张表中查询数据；多表查询时，表中的记录会进行笛卡尔积，查询的过程中最重要的就是消除无效的笛卡尔积。

多表查询可进行如下分类：

3.2 连接查询

3.2.1 内连接

内连接查询的是两张表交集的部分；内连接可以没有连接条件（即WHERE/ON语句），这种情况下，默认为笛卡尔积。

1
2
3
4
5
6
7
8
9
-- 隐式内连接
SELECT 字段列表
FROM 表1, 表2
WHERE 连接条件; 

-- 显式内连接
SELECT 字段列表
FROM 表1 [INNER] JOIN 表2
ON 连接条件;

3.2.2 外连接

外连接包含左外连接和右外连接，他们分别包含左表和右表的全部数据。

1
2
3
4
5
6
7
8
9
-- 左外连接
SELECT 字段列表
FROM 表1 LEFT [OUTER] JOIN 表2
ON 连接条件;

-- 右外连接
SELECT 字段列表
FROM 表1 RIGHT [OUTER] JOIN 表2
ON 连接条件;
  1. 左外连接用得比较多，理论上可以不需要右外连接。
  1. 左外连接保证只要左表有数据，最终的输出的记录数一定和左表的记录数相同，也就是说，哪怕右表不存在任何记录，左表中有记录，也会产生连接的结果，此时右表的字段均为NULL。
  1. 外连接不能省略ON，否则会报错。

3.2.3 自连接

相当于将一张表看成是两张表来做多表连接查询，使用的时候对表起别名即可，可以使用内连接/外连接。

1
2
3
4
5
SELECT 字段列表
FROM 表1 [AS] 别名1, 表2 [AS] 别名2
ON 连接条件;

-- 以上使用了隐式内连接，除此之外显式内连接、外连接都可以使用。

3.3 联合查询

联合查询可以将多次查询的结果合并起来，形成一个新的查询结果集。

1
2
3
4
5
6
7
SELECT 字段列表
FROM 表1
....
UNION [ALL]
SELECT 字段列表
FROM 表2
....;
  1. 多次查询可以做联合查询的前提条件是返回的字段列表的数目和类型必须一一对应，保持一致。
  1. UNION ALL会将两个查询的结果直接合并到一起，UNION会对合并之后的数据进行去重。

3.4 子查询

SQL语句（INSERT/DELETE/UPDATE/SELECT）中嵌套的SELECT语句，称为子查询/嵌套查询，如：

1
2
3
4
5
6
-- 子查询示例
SELECT *
FROM t1
WHERE column1 = (SELECT column1
                FROM t2
                );
  • 根据子查询的结果分类
  1. 标量子查询：子查询结果为单个值。
  2. 列子查询：子查询结果为一列。
  3. 行子查询：子查询结果为一行。
  4. 表子查询：子查询结果为多行多列。
  • 根据子查询位置分类
  1. WHERE之后。
  2. FROM之后。
  3. SELECT之后。

3.4.1 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），如查询某个表的单个字段，且根据筛选条件，只返回了一条记录。

常用的操作符： = <> > >= < <= IS NULL IS NOT NULL

通过这些操作符，在外层查询的WHERE语句中，可以让某个字段与标量子查询的结果进行比较，完成筛选。

3.4.2 列子查询

子查询返回的结果是一列（可以是多行），如查询某个表的单个字段，且根据筛选条件，返回了多个记录（可以看成是多个标量组成的一个集合）。

常用的操作符：

操作符 描述
IN 在指定的集合范围之内
NOT IN 不在指定的集合范围之内
ANY/SOME 子查询返回列表中，有任意一个元素满足指定条件即可
ALL 子查询返回列表中的所有元素都必须满足指定条件

ALL/ANY/SOME的用法，都只能与比较运算符相结合，且比较运算符必须位于前方，如 > SOME (子查询)。

3.4.3 行子查询

子查询返回的结果是一行（可以是多列），如查询某个表的多个字段，且根据筛选条件，返回了一条记录（可以看成是按SELECT后的字段顺序对应的值按序组成的行向量）。

常用的操作符：= <>

3.4.4 表子查询

子查询返回的结果是多行多列，如查询某个表的多个字段，且根据筛选条件，返回了多条记录（可以看成是多个行向量组成的集合）。

常用的操作符：IN NOT IN EXISTS NOT EXISTS

4. 数据库对象与编程

4.1 视图

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的；在MySQL中允许基于一张视图继续创建视图。

视图的作用在于：

  1. 简化用户对数据的理解和一些操作：可以将经常使用的查询定义为视图，往后只需要查询视图就可以，简化SELECT语句的编写。
  2. 安全：通过视图和检查选项功能保证用户只能查询和修改满足约束条件的数据。
  3. 数据独立：视图帮助用户屏蔽真实表结构变化带来的影响。

4.1.1 视图SQL语法

  • 创建视图
1
2
3
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] 
AS SELECT 语句
[WITH [CASCADED | LOCAL] CHECK OPTION];
  • 查询视图
1
2
SHOW CREATE VIEW 视图名称; -- 查看创建视图的语句
SELECT * FROM 视图名称; -- 查看视图数据
  • 修改视图
1
2
3
4
5
6
7
8
9
-- 方法一（和创建一样）
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] 
AS SELECT 语句
[WITH [CASCADED | LOCAL] CHECK OPTION];

-- 方法二
ALTER VIEW 视图名称[(列名列表)] 
AS SELECT 语句
[WITH [CASCADED | LOCAL] CHECK OPTION];
  • 删除视图
1
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ... ;

4.1.2 视图的更新

视图的可更新指的是能够对视图进行插入/更新/删除记录的操作，并能够影响到实际表中的数据。要使视图可更新，视图中的行与基础表中的行必须存在一对一的关系，如果视图的SELECT中包含以下关键词，则该视图不可更新。

  • 聚合函数、窗口函数
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION、UNION ALL
  • JOIN

JOIN的情况比较特殊，假设视图中的SELECT语句中对表a和表b做了连接操作，但选取的字段全部来自其中一张表，只在这种情况下视图是可更新的，且在插入时需要指定字段名。

4.1.3 检查选项

在可更新的视图上，如果视图在创建时在SELECT语句的WHERE子句中指定了视图的条件，可以通过检查选项保证在对视图进行更新（插入/修改/删除记录）的时候只能对同样符合条件的记录进行操作，否则不能操作。

具体判断流程如下，在更新视图时，检查更新的记录与创建视图时的约束条件：

  1. 对于无检查选项的视图：无需满足约束条件，如果存在父视图，则递归检查父视图；
  2. 对于使用了默认（CASCADED）检查选项的视图：则需要满足当前的约束条件，同时级联向上，不管其父视图是否使用检查选项、检查选项使用的是CASCADED还是LOCAL，都需要满足其约束条件，直到最顶层；
  3. 对于使用了本地（LOCAL）检查选项的视图：需要满足当前的约束条件，如果存在父视图，则递归检查其父视图。

事实上LOCAL和CASCADED的级别就在于，CASCADED要求向上递归的所有条件都必须满足，而LOCAL只要求满足当前视图的约束条件。

4.2 存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，实际上是数据库SQL语言层面的代码封装与复用，其表现形式类似于函数，能够接收参数、具有返回值。

存储过程能够减少网络交互，提升数据库操作的效率。

4.2.1 存储过程SQL语法

  • 创建存储过程
1
2
3
4
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
-- 一系列SQL语句
END;
  • 调用存储过程
1
CALL 存储过程名称([参数列表]);
  • 查看存储过程
1
2
3
4
5
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名';

-- 查询指定存储过程的创建信息
SHOW CREATE PROCEDURE 存储过程名称;
  • 删除存储过程
1
DROP PROCEDURE [IF EXISTS] 存储过程名称;

4.2.2 变量

  1. 系统变量

系统变量是MySQL服务器提供的变量，可分为全局变量（GLOBAL）和会话变量（SESSION）。

1
2
3
4
5
6
7
8
9
10
-- 系统变量有关的SQL语句

-- 1. 查看系统变量
SHOW [SESSION | GLOBAL] VARIABLES;
SHOW [SESSION | GLOBAL] VARIABLES LIKE '....'; -- 可用模糊匹配缩小范围
SELECT @@[SESSION | GLOBAL].系统变量名; -- 精确查找

-- 2. 设置系统变量
SET [SESSION | GLOBAL] 系统变量名 = 值;
SET @@[SESSION | GLOBAL].系统变量名 = 值;
  1. 在MySQL服务器重新启动后，全局变量会恢复默认设置，如果希望永久改变，需要修改MySQL的配置文件。
  1. 如果省略SESSION/GLOBAL，则默认为SESSION。
  1. 用户定义变量

用户定义变量，即用户根据需要自己定义的变量；用户定义变量无需声明，直接使用@变量名即可，如未赋值，默认为NULL；用户定义变量的作用域为当前会话。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 用户定义变量相关SQL

-- 使用SET进行赋值
SET @变量名 = expr [, @变量名2 = expr2] ... ;
SET @变量名 := expr [, @变量名2 = expr2] ... ;

-- 使用SELECT进行赋值

SELECT @变量名 := expr [, @变量名2 = expr2] ... ;
SELECT 字段名 INTO @变量名 FROM 表名 ... ; -- 从表中读取某个数据（必须是单个值）到自定义的变量中

-- 使用自定义变量
SELECT @变量名;
  1. 局部变量

局部变量是根据需要定义的在局部生效的变量；局部变量之前需要进行声明；局部变量用于存储过程内的局部变量和输入参数，作用范围是其声明语句所在的BEGIN...END块。

1
2
3
4
5
6
7
8
9
10
11
-- 局部变量相关SQL

-- 声明语句
DECLARE 局部变量名 变量类型 [DEFAULT 默认值];
-- 变量类型的范围和数据库字段类型一致：包括INT、BIGINT、CHAR、VARCHAR等
-- 未声明默认值，默认为NULL

-- 赋值
SET 变量名 = 值;
SET 变量名 := 值;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;

4.2.3 参数

在定义存储过程的时候，可以指定参数，在参数列表中声明参数时，需要声明参数的类型，包括IN、OUT、INOUT三种类型。

类型 含义
IN 该类参数作为输入（默认类型）
OUT 该类参数作为输出，即作为返回值
INOUT 既可以作为输入参数，也可以作为输出参数 
1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE 存储过程名([IN | OUT | INOUT] 参数名 参数类型, ....)
BEGIN
  -- SQL语句
  -- 将结果存储在OUT类型的参数中
END;


CALL 存储过程名(参数列表); 
-- 参数列表要和创建存储过程的参数列表顺序一致
-- 对于IN类型，可以直接传入值或赋值后的自定义变量；
-- 对于OUT类型，可以传入一个用户自定义变量用于接收返回值
-- 对于INOUT类型，可以传入一个赋值后的用户自定义变量，同时接收返回值

4.2.4 分支语句

  • IF
1
2
3
4
5
6
7
8
9
10
11
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
  IF 条件1 THEN
    ...;
  ELSEIF 条件2 THEN
    ...;
  ELSE
    ...;
  END IF;
END;

  • CASE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 与sql基础语句的CASE结构类似
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
  CASE val  --依次比较val和WHEN后的值，如果相等则走对应分支
    WHEN val1 THEN 
    ....;
    WHEN val2 THEN
    ....;
    ELSE
    ....;
  END CASE;
END;

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
  -- 依次计算expr1、expr2...，某个为真则走对应分支
  -- 只会走第一个为真的分支
  CASE  
    WHEN expr1 THEN 
    ....;
    WHEN expr2 THEN
    ....;
    ELSE
    ....;
  END CASE;
END;

4.2.5 循环语句

  • WHILE
1
2
3
4
5
6
7
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
  WHILE 条件 DO
    ...
  END WHILE
END;

  • REPEAT

相当于do-while循环。

1
2
3
4
5
6
7
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
  REPEAT 
    ...
    UNTIL 条件
  END REPEAT
END;
  • LOOP

可用作无限循环，相当于While(true){}。

1
2
3
4
5
6
7
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
  [begin_label:] LOOP
    ...
  END LOOP [end_label];
END;

以下两条语句可用在LOOP的循环体中，相当于break和continue。

1
2
LEAVE label;
ITERATE label;

注意，这里的begin_label用于标识循环块开始的地方，如果要写end_label，则end_label必须与对应的begin_label相同。

4.2.6 游标

游标（CURSOR）是用来存储查询结果集的数据类型，在存储过程/存储函数中可以使用游标对结果集进行循环的处理。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 游标相关SQL
-- 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句; -- 将'查询语句'的返回结果存储到'游标名称'对应的游标中。

-- 打开游标
OPEN 游标名称; -- 只有打开游标之后，才能从中读取数据

-- 获取游标记录
FETCH 游标名称 INTO 变量名1, [变量名2]; -- 每次读取一行的数据，按字段存入指定的变量中

-- 关闭游标
CLOSE 游标名称

4.2.7 条件处理程序

条件处理程序（HANDLER）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤；例如，在WHILE循环中读取游标的值，判断游标读取完毕就需要用到HANDLER。

1
2
3
4
-- 条件处理程序相关SQL
DECLARE 处理动作 HANDLER FOR 状态码 处理语句

-- 当捕获到指定状态码，执行处理语句，最后进行处理动作
  • 状态码

可以使用SQLSTATE关键字加上状态码的形式，比如：

1
SQLSTATE '02000'

对应一些特定的状态码，可以用专属的关键字，比如：

1
2
3
4
SQLWARNING -- 所有以01开头的SQLSTATE代码的简写
NOT FOUND -- 所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION -- 所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

  • 处理动作
1
2
CONTINUE -- 继续执行当前存储程序（如当前的存储过程/存储函数）
EXIT -- 终止执行当前存储程序

4.2.8 存储过程示例代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 从user表中读出小于指定年龄的记录的id和name，存入另一张表user_name中。
DROP PROCEDURE IF EXISTS p;

CREATE PROCEDURE p(IN age INT)
BEGIN
    DECLARE id INT;
    DECLARE name VARCHAR(10);
    DECLARE flag INT DEFAULT 1; -- 用于控制循环是否继续
    DECLARE cursor_id_name CURSOR
        FOR SELECT user.id, user.name
            FROM user
            WHERE user.age < age;
    DECLARE continue HANDLER FOR NOT FOUND SET flag = 0;

    DROP TABLE IF EXISTS user_name;
    CREATE TABLE user_name (
      id INT,
      name VARCHAR(10)
    );

    OPEN cursor_id_name;
    insert_loop: LOOP
        FETCH cursor_id_name INTO id, name;
        IF FLAG = 0 THEN
            LEAVE insert_loop;
        END IF;
        INSERT INTO user_name VALUES(id, name);
    END LOOP insert_loop;
    CLOSE cursor_id_name;
end;

CALL p(22);

4.3 存储函数

存储函数具有返回值，存储函数的参数只能是IN类型的，调用的时候像普通函数一样使用，无需通过CALL调用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 参数类型 [DETERMINISTIC | NO SQL | READS SQL DATA]
BEGIN
  -- SQL语句
  RETURN ... =;
END;

-- 参数类型说明
-- 使用MySQL支持的数据类型

-- characteristic参数说明

-- 1. DETERMINISTIC: 相同的输入参数总是产生相同的结果；
-- 2. NO SQL: 不包含SQL语句；
-- 3. READS SQL DATA: 包含读取数据的语句，但不包含写入数据的语句。

4.4 触发器

  • 定义

触发器定义在数据库的层面，在数据库中针对于某个表发生了增/删/改操作前/后，会触发并执行触发器中定义的SQL语句集合。在MySQL中，只支持行级触发：指的是在一条增/删/改语句执行后，针对于被影响的每一行数据，都会触发一次触发器。

  • 作用

触发器可以用于在数据库端确保数据的完整性、日志记录、数据校验等操作。

  • SQL语法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建触发器
CREATE TRIGGER 触发器名
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名 FOR EACH ROW -- 行级触发器
BEGIN
  -- 触发器被触发后执行的语句
END;

-- 查看当前数据库存在的触发器
SHOW TRIGGERS;

-- 删除指定数据库的触发器

DROP TRIGGER [数据库名.]触发器名; -- 未指定数据库名则默认为当前数据库

在编写触发器被触发后执行的语句时，可以通过NEW和OLD关键字指代数据行，可以通过NEW/OLD.字段名的形式读取一行的某个字段，对于不同的DML语句，其NEW、OLD的含义如下表所示：

类型 NEW OLD
INSERT 被插入的行记录 ~
DELETE ~ 被删除的行记录
UPDATE 更新后的行记录 更新前的行记录

5. 事务与并发控制

5.1 事务基础

5.1.1 事务概念

事务是一组逻辑上不可分割的操作集合，要么全部成功，要么全部失败，并且对系统状态的影响是可控、可恢复、可隔离的。

5.1.2 手动操作事务

在MySQL中使用事务进行操作，可以用两种方式。

  1. 手动提交事务

在MySQL中，默认事务是自动提交的：即执行一条DML语句后，MySQL会立即隐式地提交任务，可以通过查看其系统参数@@autocommit得知。

1
SELECT @@autocommit; -- 默认为1，即自动提交事务

可以将事务提交设置为手动。

1
SET @@autocommit = 0; -- 在当前console中生效

随后可以进行事务的提交和回滚。

1
2
3
4
5
-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;
  1. 开启事务

不改变事务的自动提交参数也可以通过开启事务的方式操作事务。

1
2
START TRANSACTION;  -- 其后的多句sql作为一个事务
BEGIN;

随后进行手动提交/回滚，操作与之前相同。

1
2
3
4
5
-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

5.1.3 事务四大特性ACID

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

5.2 并发事务

为提高数据库系统的操作效率，数据库系统允许多个事务之间进行并发操作，并设置了不同的隔离级别，提高操作效率的同时，可能会产生并发导致的问题。

5.2.1 并发事务存在的问题

以下问题均出现在两个事务A、B并发执行且访问同一张表的相关数据的情况下，且不同的隔离级别会避免其中的一些/全部问题。

问题类型 问题说明
脏读 事务A读取到了事务B未提交的数据，而随后事务B执行回滚，则事务A读到的数据即为脏数据。
不可重复读 事务A多次读取同一数据，而事务B在事务A多次读取的间隙中修改了该数据，导致事务A多次读取同一数据的结果不一致。
幻读 事务A读取了几行数据后，事务B插入了一些新数据；随后的查询中，事务A可能查询到一些原本不存在的记录。
  • 脏读复现

使用MySQL手动模拟并发，对于以上三类问题进行简单的说明，可在阅读完后一节隔离级别后再来看这部分复现内容：

假设现在使用的数据库中有一张表users，包含主键id、name、money三个属性。在两个命令行窗口中使用MySQL客户端，执行如下指令。

命令行1 命令行2 说明
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; ~ 将事务A的隔离级别设置为READ UNCOMMITTED。
START TRANSACTION; START TRANSACTION; 开启两个事务，分别对应事务A和事务B。
SELECT * FROM users; ~ 模拟事务A第一条SQL。
~ UPDATE users SET money = money - 2000 WHERE name = 'Jack'; 模拟事务B第一条SQL，更新了数据，但尚未提交。
SELECT * FROM users; ~ 模拟事务A第二条SQL，此时事务A能够读取到被更新的数据（即B事务更改但未提交的数据），因为这是READ UNCOMMITTED决定的，从而出现脏读。
... ... 后续操作省略。
  • 不可重复读复现
命令行1 命令行2 说明
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; ~ 将事务A的隔离级别设置为READ COMMITTED。
START TRANSACTION; START TRANSACTION; 开启两个事务，分别对应事务A和事务B。
SELECT * FROM users; ~ 模拟事务A第一条SQL。
~ UPDATE users SET money = money - 2000 WHERE name = 'Jack'; 模拟事务B第一条SQL，更新了数据，但尚未提交。
SELECT * FROM users; ~ 模拟事务A第二条SQL，此时事务A不能读取到更改的数据，因为事务B未提交，避免了脏读。
~ COMMIT; 事务B提交。
SELECT * FROM users; ~ 模拟事务A第三条SQL，此时事务A读取到了更改的数据，因为事务B已提交，与第一次读取结果不一致，出现不可重复读问题。
... ... 后续操作省略。
  • 幻读复现

MySQL InnoDB 在REPEATABLE READ下使用 MVCC + next-key/gap Lock：普通SELECT读到固定快照、SELECT ... FOR UPDATE 会阻止范围内插入。因此即便在 RR 级别故意穿插插入，也观察不到“前后两次读取的行集不同”的经典幻读现象。为了复现幻读，需要降低隔离到READ COMMITTED（或禁用 gap Lock），让当前读无法锁住范围，才能看到新插入的“幻行”：

命令行1 命令行2 说明
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; ~ 将事务A的隔离级别设置为READ COMMITTED（不使用Gap Lock）。
START TRANSACTION; START TRANSACTION; 分别开启事务A、事务B。
SELECT * FROM users WHERE money >= 0 FOR UPDATE; ~ 事务A进行第一次当前读，锁住当前满足条件的行集。
~ INSERT INTO users VALUES (NULL, 'Tony', 0); 事务B插入一条满足上述条件的新记录。
~ COMMIT; 事务B提交，插入生效。
SELECT * FROM users WHERE money >= 0 FOR UPDATE; ~ 事务A第二次当前读会返回比第一次更多的记录，看到了事务B插入的“幻行”。
... ... 后续操作省略。

5.2.2 隔离级别

  • 隔离级别分类

为在并发的效率与产生的问题之间达到平衡，数据库提供不同等级的隔离级别供开发者使用：从上至下，隔离级别越高，安全性越高，效率越低。

隔离级别 脏读 不可重复读 幻读 说明
READ UNCOMMITTED √ √ √ 设置该隔离级别的会话中的事务可以读取并发事务尚未提交的数据。在实际开发中使用少，对数据一致性的保护太弱。
READ COMMITTED × √ √ 设置该隔离级别的会话中的事务只能读取并发事务已提交的数据。 这是大多数数据库（Oracle、SQL Server）的默认隔离级别。
REPEATABLE READ × × √（MySQL InnoDB引擎中 ×） 设置该隔离级别的会话中的事务能够保证在该事务中读取同一数据的结果能保持一致（除非自己修改）；这是MySQL的默认隔离级别，另外在MySQL InnoDB存储引擎中，通过MVCC和锁机制，同样几乎解决了幻读问题。
SERIALIZABLE × × × 完全服从ACID的隔离级别，串行化处理，安全性极高，但效率低 。
  • 查看与设置隔离级别
1
2
3
4
5
6
7
-- 查看当前会话的隔离级别
SELECT @@TRANSACTION_ISOLATION;

-- 设置隔离级别
SET {SESSION | GLOBAL} 
TRANSACTION ISOLATION LEVEL 
{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

5.3 锁

本节与下一节的内容处理并发事务的解决方案均为InnoDB引擎的解决方案。

在数据库中，通过锁机制保证数据库中的数据被多个事务并发访问的一致性、有效性，可从不同的角度对锁进行分类：

  1. 按照锁的作用范围分：
  • 全局锁：锁定数据库中的所有表。
  • 表级锁：每次操作锁住整张表。
  • 行级锁：每次操作锁住对应的行数据。
  1. 按照锁模式分：
  • 共享锁（读锁、S）：多个读锁可以并行（兼容），和写锁不兼容。
  • 排他锁（写锁、X）：写锁与任何类型的锁都不兼容。
  1. 按照锁所在层次分：
  • 服务层：在服务层实现的锁机制，如全局锁、（显式）表锁、元数据锁等，不同的存储引擎都可以使用这些锁。
  • 存储引擎层：由存储引擎自己实现的锁机制，如InnoDB的行锁、InnoDB的意向锁、MyISAM的表锁等。

5.3.1 全局锁

全局锁对整个数据库实例进行加锁，加锁后整个实例就处于只读状态，属于全局共享锁；其目的是阻塞了一切的写语句（DML、DDL），保证整个数据库只能被读取、不能被修改；全局锁常用于对数据库做备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

1
2
3
4
5
-- 加全局锁
FLUSH TABLES WITH READ LOCK;

-- 释放锁
UNLOCK TABLES;

5.3.2 表级锁

表级锁，每次操作锁住整张表，主要分为三类：表锁、元数据锁、意向锁。

5.3.2.1 表锁

表锁是用于控制并发事务对于表中数据操作的锁，可分为表读锁（表共享锁）、表写锁（表排他锁）两类。

（显式）表锁（LOCK TABLES）是MySQL服务器层提供的通用功能，各存储引擎都可以用，但只在“主动写出来”时才生效。

  • 语法
1
2
3
4
5
-- 加锁
LOCK TABLES 表名 READ/WRITE;

-- 释放锁
UNLOCK TABLES;
  • 作用
对某表使用表锁类型 当前事务 并行事务
表读锁 可以读取表中数据，修改表中数据会报错。 可以读取表中数据，修改表中数据会阻塞。
表写锁 可以读取表中数据，可以修改表中数据。 读取表中数据、修改表中数据均会被阻塞。

表读锁与表读锁是兼容的，表写锁与任何锁都不兼容。

5.3.2.2 元数据锁

元数据锁（Meta Data Lock，MDL），作用是维持表元数据的一致性：在表上有活动事务时，不允许对元数据进行写入操作（如DDL语句中关于表的部分）。

  1. 表的元数据：可以简单理解为一张表的表结构，如拥有的字段等。
  1. 在MySQL 5.5中引入了元数据锁，在服务层实现：当某事务对一张表进行增删改查的时候，该事务在该表加MDL读锁（共享锁）；当某事务对表结构进行变更操作时，该事务在该表加MDL写锁（排他锁）。
  1. MDL共享锁与MDL共享锁之间是兼容的，MDL排他锁与MDL共享锁、MDL排他锁都是互斥的。
  1. 元数据锁加锁过程是系统自动控制，无需显式使用，在访问一张表的时候就会自动加上。
5.3.2.3 意向锁

意向锁常指的是InnoDB存储引擎中实现的意向锁，由于InnoDB存储引擎默认使用的是行级锁，如果同时还使用到了表级锁，那么就需要判断所使用的行级锁和表级锁是否兼容/互斥，意向锁的作用就在于简化这一检查过程，使得无需遍历全表的行级锁即可判断。

  • 分类与使用

意向锁可分为意向共享锁（Intent Shared lock，IS）和意向排他锁（Intent eXclusive lock，IX）。

在使用特定的DML语句操作表的时候，就会给这张表加上特定的意向锁，如：

  1. SELECT ... LOCK IN SHARE MODE: 给操作表加意向共享锁。
  2. SELECT/INSERT/UPDATE/DELETE ... FOR UPDATE: 给操作表加意向排他锁。
  1. 普通的SELECT语句，即没有使用LOCK IN SHARE MODE/FOR UPDATE的语句，则默认是快照读，既不会使用行锁，也不会使用意向锁。
  1. INSERT/UPDATE/DELETE即使没有FOR UPDATE也会使用意向排他锁。
  • 作用具体说明

在加上特定的意向锁后，某个想要加表级别锁的事务就可以比较意向锁与表级别锁之间的兼容性判断表级别锁是否可以加成功了，避免了扫描表获取行级别锁的过程，将时间复杂度降低到了O(1)。

具体来说，只有表级别锁和意向锁均为共享锁的情况才能兼容，即加锁成功，其余情况均互斥。

注意，意向锁之间均是兼容的，一个表可以同时有多个意向共享锁和意向排他锁。

5.3.3 行级锁

行级锁，每次操作锁住对应的行数据，应用在InnoDB存储引擎中；由于InnoDB数据的存储结构是通过索引组织的，因此行锁是通过对索引上的索引项加锁来实现的。

行级锁主要分为三类：

  1. 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行UPDATE和DELETE，在RC和RR隔离级别下都支持。
  2. 间隙锁（Gap Lock）：锁定索引记录间隙（不含记录），确保索引记录间隙不变，防止其他事务在这个间隙进行INSERT，产生幻读；在RR隔离级别下支持。
  3. 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据并锁住数据前面（指的是B+数底层的双向链表）的间隙，在RR隔离级别下支持。
  • 行锁

行锁可分为共享锁（S）和排他锁（X），共享锁之间互相兼容，排他锁与共享锁/排他锁之间互斥。

  1. 行共享锁：允许一个事务读取一行数据，同时允许其他事务在该行加共享锁，但不允许其他事务在该行加排他锁。
  2. 行排他锁：允许一个事务更新改行数据，同时其他事务都不允许在该行加共享锁/排他锁。

  • INSERT/UPDATE/DELETE：自动加行排他锁。

  • SELECT：快照读，不加行锁。

  • SELECT...FOR UPDATE：加行排他锁。

  • SELECT...LOCK IN SHARE MODE：加行共享锁。

  • 实际流程

在RR隔离级别下（MySQL默认隔离级别）运行，InnoDB使用临键锁（Next-Key Lock）进行搜索和索引扫描，用以防止幻读，具体情境如下：

  • 无可用二级索引：只能扫描聚簇索引，此时会在聚簇索引上加大量临键锁，效果上接近把整张表锁住，相当于表锁。
  • 存在可用二级索引：
    • 唯一索引 + 等值查询 + 记录不存在：此时临键锁简化为间隙锁（只需要控制这个位置不会被插入即可）
    • 唯一索引 + 等值查询 + 记录存在：临键锁简化为行锁（只会有这样一条记录，不需要防止插入）
    • 非唯一索引 + 等值查询：向右遍历到最后一个不满足需求的记录，在最后这个记录前的间隙上使用间隙锁（防止在这个位置继续插入满足条件的记录），所有命中的记录仍使用临键锁。
    • 范围查询：访问到不满足条件的第一个值为止，在该记录前的间隙上使用间隙锁，所有在范围内的记录使用临键锁。

5.4 MVCC、redo log、undo log

5.4.1 redo log

重做日志，记录的是事务提交时数据页的物理修改，用来实现事务的持久性。

日志部分分为两部分，分别位于内存（redo log buffer）和磁盘（redo log file）中。在某个事务中做了一系列修改数据的操作，都会记录在redo log buffer中，在事务提交时，将redo log buffer刷盘至磁盘上的redo log file中进行持久化。

该事务修改数据在内存中产生了一些脏页，由于事务的提交并非伴随着脏页的写回，假设此时系统崩溃，那么内存中的这些脏页便会消失。在系统恢复的时候，就会根据已经持久化的redo log修改内存中的页，再找实际刷回磁盘。

redo log的刷盘遵循WAL（Write-Ahead Logging），即如果某个脏页想要刷盘，首先要保证其对应的redo log中的数据已经被刷盘了。

  1. 在严格持久性的InnoDB引擎下，如果某个事务被标记为成功提交，则redo log一定已经被成功持久化了。
  1. 在上面的情景中，假设事务未成功提交，系统崩溃了，则会根据redo log进行重放，然后通过undo log回滚。

5.4.2 undo log

回滚日志，用于记录数据被修改前的信息，作用为：提供回滚（保证事务的原子性）和实现MVCC（多版本并发控制）。

undo log中的日志中的每一条记录为与当前操作逻辑相反的操作，因此回滚时，只需要根据undo log中的语句重新执行则可以恢复操作数据前的数据。undo log在事务执行时产生，事务提交时，不会立即删除undo log，因为这些日志可能还用于MVCC。

5.4.3 MVCC

  • 概念

MVCC（Multi Version Concurrency Control，多版本并发控制）通过数据快照实现了不同事务的当前读能够无锁地获取不同版本的数据，并且使得快照读、写操作之间不会因为锁的问题而阻塞。

  • 作用

如果不存在MVCC，系统必须频繁地对读写操作进行加锁来保证数据的正确性，会增加锁的获取和释放的开销，导致整体系统响应速度变慢。

  • 原理

通过表中数据的隐藏字段、undo log、readview来实现MVCC。

  1. 隐藏字段

首先，表中数据除了自定义的字段外，还会有两个隐藏字段用于实现MVCC:

隐藏字段 含义
DB_TRX_ID 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务的ID。
DB_ROLL_PTR 回滚指针，指向这条记录的上一个版本（位于undo log中）。
  1. undo log

    对于undo log，在上一小节中已经小作介绍了，不过上一节讲的是其数据回滚的功能，这里侧重讲它用于MVCC的功能。

在事务中出现INSERT、UPDATE和DELETE语句时，会根据反向逻辑记录于undo log中，具体来说：

  • INSERT语句：产生的undo log日志只在回滚时需要，在事务提交后即被删除。

    因为INSERT之前所谓的旧纪录就是不存在，不会影响MVCC。

  • UPDATE/DELETE语句：产生的undo log日志不会随着事务提交后就被立刻删除，因为需要用于MVCC。

根据对于一条记录的依次删改，则会产生版本链，即根据回滚指针字段指向旧记录，从而组成了链表：其中，最新记录作为链表的头部，在链表中位置越靠后代表着越古早的数据。

  1. readview

readview是快照读执行时提取数据的依据，其中包含四个核心字段：

字段 含义
m_ids 当前活跃的事务ID集合（即已开启且未提交的事务）
min_trx_id 最小活跃事务ID
max_trx_id 预分配事务ID，当前最大事务ID（全局）+1
creator_trx_id ReadView创建者的事务ID（即执行快照读的事务ID）

需要补充一下事务ID分配的规则，只有当事务第一次执行INSERT/UPDATE/DELETE语句时，会为其分配ID，且分配方式是全局递增的形式，理论上只要使用过并提交的事务ID就不会再被复用；假设某个事务只执行过SELECT语句，则它的事务ID一直是0。

  1. MVCC核心过程

对于选中的某条记录，从最新版本开始，一路沿着版本链往旧版本走，找到第一个“对当前事务可见”的版本，如果没有，就当这条记录对当前事务“不存在”。

比较是否可见的方式即：比较这些字段与选中的记录的版本链中的每一个结点中的最近修改事务ID（我们设为trx_id）的关系，用以判断当前快照读是否能够访问相应版本的数据：

  • trx_id == creator_trx_id: 可以访问（该版本就是由当前事务修改得到的）
  • trx_id < min_trx_id: 可以访问（造成该版本的事务已经提交）
  • min_trx_id <= trx_id <= max_trx_id:
    • trx_id $\in$ m_id: 不可以访问（造成该版本的事务未提交）
    • trx_id $\notin$ m_id：可以访问（造成该版本的事务已经提交）
  • trx_id > max_trx_id: 不可以访问（说明该事务是在readview生成后才开启）

对于不同的隔离级别，readview的生成时机不同：

  • RC（READ COMMITTED）: 在事务中每次执行快照读，都会生成readview。
  • RR（REPEATED READ）：在事务第一次执行快照读的时候生成readview，后续复用该readview。

6. MySQL架构与存储引擎

6.1 MySQL逻辑架构

6.1.1 连接层

不同的语言依据同样的MySQL连接协议开发不同的客户端连接器与MySQL的服务端进行连接，在MySQL的连接层负责进行身份认证和权限认证。

6.1.2 服务层

服务层完成数据库系统的大多数核心功能。

  • 查询缓存

执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。

  • 分析器/解析器

没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

  • 优化器

按照 MySQL 认为最优的方案去执行。

  • 执行器

执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。

6.1.3 引擎层

存储引擎真正地负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。

MySQL中的存储引擎采用插件式架构，不同的存储引擎具有不同的功能，支持InnoDB、MyISAM、Memory等多种不同的存储引擎，甚至可以自主编写存储引擎。

InnoDB是默认的存储引擎，在绝大多数场景下，使用InnoDB就是最好的选择。

6.1.4 存储层

主要是将数据存储在文件系统上，并完成与存储引擎的交互。

6.2 存储引擎概览

存储引擎是数据库管理系统架构中的核心层，是存储数据、建立索引、更新/查询数据等技术的实现方式。

6.2.1 指定存储引擎

存储引擎是基于表的，也就是说，在同一个数据库的不同表可以使用不同的存储引擎。

  • 查看数据库管理系统支持的引擎类型
1
SHOW ENGINES;

如在MySQL 8.0.40版本中使用如上语句的查询结果如下所示：

  • 指定存储引擎

在使用建表语句的时候，可以指定表使用的存储引擎类型。

1
2
3
4
5
CREATE TABLE 表名 (
  字段名 字段类型,
  ...,
  字段名，字段类型
) ENGINE = 存储引擎类型;

如果没有指定，则默认使用InnoDB作为表的存储引擎。

6.2.2 InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5后作为MySQL的默认存储引擎。

  • 特点
  1. 支持事务：DML操作遵循ACID模型。
  2. 使用行级锁：提高并发访问性能。
  3. 支持外键：支持外键约束，保证数据的完整性和正确性。
  • 存储文件

在MySQL默认开启参数innodb_file_per_table的情况下，使用InnoDB的每张表都会对应一个表空间文件xxx.ibd，存储表结构、数据和索引。

6.2.3 MyISAM

MyISAM是MySQL早期版本的默认存储引擎。

  • 特点
  1. 不支持事务。
  2. 支持表锁，不支持行锁。
  3. 不支持外键约束。
  4. 访问速度较快。
  • 存储文件

使用MyISAM作为存储引擎的表，会对应三个文件：

  1. xxx.sdi: 存储表结构信息。
  2. xxx.MYD: 存储数据。
  3. xxx.MYI: 存储索引。

6.2.4 Memory

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

  • 特点
  1. 数据存放于内存中，访问速度快。
  2. 默认使用哈希索引。
  • 存储文件

使用Memory作为存储引擎的表，对应一个文件：

  1. xxx.sdi: 存储表结构信息。

表中的数据和索引都存放在内存中，不会存放在磁盘上。

6.2.5 存储引擎对比和选择

特点 InnoDB MyISAM Memory
存储限制 64TB 有 有
事务 支持 - -
锁机制 行锁 表锁 表锁
B+Tree索引 支持 支持 支持
Hash索引 - - 支持
全文索引 支持（MySQL 5.6版本后） 支持 -
空间使用 高 低 几乎不使用
内存使用 高 低 中等
批量插入速度 低 高 高
支持外键约束 支持 - -

在实际开发中，几乎都会选择InnoDB作为默认存储引擎。

MyISAM在实际开发中即使用到，也会选择使用其他NoSQL作为替代；Memory如果要用于缓存，也会优先选择Redis作为解决方案。

6.3 InnoDB存储引擎详解

在MySQL 5.5之后InnoDB作为默认的存储引擎，便于事务处理，具有崩溃恢复特性，在日常开发中使用广泛。

6.3.1 逻辑存储结构

InnoDB逻辑存储结构

  1. 表空间（Tablespace）

InnoDB逻辑存储结构的最高层。

如果用户启用了参数innodb_file_per_table（在MySQL 8.0版本默认开启），则数据库中的每一张表会对应一个表空间（在磁盘上存储为一个.ibd文件）。

  1. 段（Segment）

段可以分为数据段、索引段、回滚段，事实上就是InnoDB在一个索引上，为不同用途的页分的“页集合”。

  • 数据段：专门用来存放B+树的叶子节点页。
  • 索引段：专门用来存放B+树的非叶子节点页。
  1. 区（Extent）

区是表空间的单元结构，每个区固定大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即在一个区中一共有64个连续的页。

  1. 页（Page）

页是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16K。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区。

  1. 行（ROW）

InnoDB存储引擎数据是按行进行存放的，在一行中默认有两个隐藏字段：

  • Trx_id: 记录最近一次改动该行记录的事务ID。
  • Roll_pointer: 每次改动行记录，将该指针指向改动前的记录内容（改动前的记录内容会被写入undo日志中）。

6.3.2 InnoDB系统架构

从内存和磁盘存储两个方面梳理InnoDB存储引擎的系统架构。

InnoDB架构概览

6.3.2.1 内存架构

InnoDB存储引擎的内存结构可分为四大块：

  1. 缓冲池（Buffer Pool）

缓冲池可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘I/O，加快处理速度。

缓冲池以页（Page）作为单位，采用链表数据结构管理页；有三种状态的页：

  • 空闲页（Free Page）：未被使用的页。
  • 干净页（Clean Page）：被使用的页，且该数据并未被修改过。
  • 脏页（Dirty Page）：脏页，被使用的页且数据经过了修改，该页数据与磁盘中对应的数据产生了数据不一致。
  1. 按照B+树读取并不一定意味着I/O开销。通过B+树结构读取实际上就是按照顺序读取一定的页，如果这些页都存在于缓冲池中，则没有额外的I/O开销。
  1. 缓冲池中页的组织形式为链表，但本身维护一个哈希表，因此根据页号读取的时间复杂度是O(1)。
  1. 更改缓冲区（Change Buffer）

更改缓冲区针对于非唯一二级索引，在执行大量DML语句时，如果这些数据不在缓冲池中，不会直接进行磁盘I/O，而是将数据变更存在更改缓冲区中。在未来数据被读取时，再将磁盘中读出的数据与更改记录合并，存入缓冲池中，随后在特定的时刻刷新到磁盘中。

更改缓冲区避免了在更改缓冲池中不存在的页时，立即进行磁盘I/O的操作，而是在后续数据被使用（读取）的时候才进行读取、合并及最终写入，减少磁盘I/O的次数。

强调非唯一是因为，唯一索引在插入/更改数据的时候需要保证数据的唯一性，需要做唯一性检查，因此不能采用这种更改缓冲的机制。

  1. 自适应哈希索引（Adaptive Hash Index）

自适应哈希索引用于优化对于缓冲池数据的查询，在查找某条记录所在的页号时，可以直接根据AHI得到，无需通过B+树的结构一步一步读取页。自适应哈希索引，无需人工干预，是系统根据情况自动完成。

参数：adaptive_hash_index

  1. 日志缓冲区（Log Buffer）

日志缓冲区用来保存要写入到磁盘中的log日志数据（redo log、undo log），默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除多行的事务，增加日志缓冲区的大小可以节省磁盘I/O。

参数：

  • innodb_log_buffer_size：缓冲区大小
  • innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，取值包含以下三个：
    • 0：每秒将日志写入并刷新到磁盘一次。
    • 1：日志在每次事务提交时写入并刷新到磁盘。（默认值）
    • 2：日志在每次事务提交后写入，并每秒刷新到磁盘一次。
6.3.2.2 磁盘架构
  1. 系统表空间（System Tablespace）
  • 更改缓冲区的存储区域。
  • 如果表是在系统表空间而不是每个表文件或通用表空间中创建的，在系统表空间中可能包含表和索引数据。
  • 参数：innodb_data_file_path
  • 对应文件：ibdata1

更改缓冲区在磁盘上也是以B+树的形式存储，其结点也是页，不过语义与索引生成的B+树不同；在更改缓冲区的B+树上叶子节点对应的页中记录的是某个页需要进行怎样的修改；在系统崩溃的时候，通过磁盘上的更改缓冲区与redo日志恢复内存中更改缓冲区的B+树。

  1. 单表空间（File-Per-Table Tablespace）

在开启innodb_file_per_table后，每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中。

  1. 通用表空间（General Tablespace）

使用CREATE TABLESPEACE语法创建的通用表空间，在创建表时，可以指定该表的存储空间。

1
2
3
4
5
6
7
8
9
-- 创建通用表空间

CREATE TABLESPACE 表空间名 ADD DATAFILE 'xxx.ibd' ENGINE = 存储引擎名;

-- 创建表并指定存储表空间
CREATE TABLE 表名(
  .....
) ENGINE = 存储引擎名 TABLESPACE 表空间名;

  1. 撤销表空间（Undo Tablespaces）

MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小为16M），用于存储undo log日志。

  1. 临时表空间（Temporary Tablespace）

InooDB使用会话临时表空间和全局临时表空间；存储用户创建的临时表数据。

  1. 双写缓冲文件（Doublewrite Buffer Files）

InnoDB引擎将数据页从内存中的缓冲池刷新到磁盘前，先将数据页写入双写缓冲文件中，便于系统异常时恢复数据。

  1. 重做日志（Redo Log）

用以实现事务的持久性。当事务提交后，把所有修改信息保存到磁盘中的重做日志中，用于在刷新脏页到磁盘发生错误时，进行数据恢复使用。

6.3.2.3 工作线程
  1. Master Thread

核心后台线程，负责调度其他线程、将缓冲池中的数据异步刷新到磁盘中（包括脏页的刷新、合并插入缓存、undo页的回收）。

  1. IO Thread

在InnoDB存储引擎中大量使用了AIO来处理IO请求，IO Thread负责处理这些IO请求的回调，具体可分为以下四类：

线程类型 默认个数 职责
Read Thread 4 负责读操作
Write Thread 4 负责写操作
Log Thread 1 负责将日志缓冲区刷新到磁盘
Insert Buffer Thread 1 负责将写缓冲区内容刷新到磁盘
  1. Purge Thread

主要用于回收事务已经提交了的undo log。

  1. Page Cleaner Thread

协助Master Thread刷新脏页到磁盘的线程。

7. 索引与查询优化

7.1 索引概念与分类

索引是帮助MySQL高效获取数据的数据结构（有序）；当MySQL查找记录的时候，就可以通过索引及相关的查找算法快速定位到所需信息，无需全表扫描。

7.1.1 索引优劣

  • 优点
  1. 提高数据检索效率，降低数据库IO成本。
  2. 通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗。
  • 缺点
  1. 索引列需要额外占用存储空间。
  2. 降低了更新表（增删改）的速度和效率。

7.1.2 按照数据结构划分

  • 索引数据结构分类
索引结构 描述
B+Tree索引 存储引擎 MyISAM 和 InnoDB 实现 B+Tree 索引都是使用 B+Tree，但二者实现方式不一样。MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。
Hash索引 底层通过哈希表（类似于Java中的HashMap）实现。只有精确匹配索引列的查询才有效，不支持范围查询。
R-Tree（空间索引） 是MyISAM引擎的特殊索引类型，主要用于地理空间数据类型（geometry）。通常使用搜索引擎ElasticSearch代替。
Full-text（全文索引） 通过建立倒排索引，快速匹配文档，效率低，使用少。通常使用搜索引擎ElasticSearch代替。
  • 存储引擎支持的索引
索引 InnoDB MyISAM Memory
B+Tree索引 支持 支持 支持
Hash索引 不支持 不支持 支持
R-Tree索引 不支持 支持 不支持
Full-text索引 支持（5.6版本后） 支持 不支持

7.1.3 按照底层存储方式划分

  • 聚簇索引

索引和实际数据“簇”在一起，叶子节点就是完整的数据行。

  • 非聚簇索引

索引结构和数据分开存放的索引，往往根据索引结构查到的数据并非直接是记录（ROW）的数据，还需要经过额外的步骤进行查询。

7.1.4 按照字段特性划分

  • 主键索引

建立关于表中主键的索引，默认自动创建，一个表只能有一个主键索引。

  • 唯一索引

建立关于表中UNIQUE修饰的字段的索引，一个表可以有多个唯一索引。

  • 普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为UNIQUE。

  • 前缀索引

在VARCHAR、CHAR类型的字段上建立索引时，如果文本的值较长，则会导致索引较大；可以指定使用文本的前n个值建立索引，称为前缀索引。

7.1.5 按照使用字段的数量划分

  • 单列索引

使用一个字段建立的索引。

  • 联合索引

使用多个字段建立的索引，建立索引时多个字段之间的排列顺序对于联合索引的效果是有影响的。

在业务场景中，如果存在多个查询条件，建议针对查询字段优先建立联合索引，而非单独建立多个单列索引。

7.2 InnoDB索引实现

  1. 从数据结构角度，选用B+树索引作为存储结构：
  • 改良B+树

以InnoDB实现的B+树索引为例，其在数据结构中B+树的基础结构上进行了部分改良，包括：

  1. 叶子节点之间组成双向链表。
  2. 每个节点都对应着InnoDB逻辑存储结构中的页（数据页，Page）。

在B+树的结构中，只有叶子节点存放数据，非叶子节点仅存放key和指针。

当具体搜索某条数据时，通过B+树的索引结构，定位到叶节点中的某个数据页，在数据页中进行查询。

当搜索某个范围内的数据时，通过B+树的索引结构，定位到范围下界所在的数据页，在数据页中定位到相应记录后，按序访问双向链表即可获取指定范围内的所有记录。

  • InnoDB数据页详解

在学习InnoDB存储引擎时，曾提到其逻辑存储结构。其中，页（16K）是磁盘I/O操作的最小单元，也是B+树索引的节点组成，具体包含以下几个部分：

其中每部分的作用如下所示：

名称 说明
文件头 File Header 表示页的信息，包含两个指针，分别指向上一个数据页和下一个数据页，连接起来的数据页组成双向链表。
页头 Page Header 表示页的状态信息，负责指导这页在 B+ 树中的位置和角色，如当前页的B+树层级、本页属于哪个索引等。
最小和最大记录 Infimum Supremum 两个虚拟的伪记录，分别表示页中的最小记录和最大记录
用户记录 User Records 存储行记录内容
空闲空间 Free Space 存储该页中未被使用的空间
页目录 Page Directory 存储用户记录的相对位置，对记录起到索引作用
文件尾 File Tailer 校验页是否完整

File Header中的两个指针及其构成的双向链表并非B+树底层叶子节点的指针和双向链表，这里的指针和双向链表是一个区为了便于管理自己所拥有的页而建立的。

在一个数据页内，最小记录（伪记录）、用户记录、最大记录（伪记录）按照索引键顺序构成一条单向链表，为加快在页内的查找效率，将单向链表分组，同时在页目录中建立槽（slot），每个槽指向一个分组中的最大记录。如下图所示：

在一个数据页内查找数据的方式：首先通过二分法定位到槽，然后遍历槽内的记录，找到指定记录。

  1. 从底层存储方式角度

InnoDB中的索引既有聚簇索引、也有非聚簇索引。

聚簇索引：如InnoDB中的主键索引，使用主键作为key，在B+树的叶子节点的数据部分，即对应的ROW数据，此为聚簇索引。

InnoDB引擎要求必须包含聚簇索引：

  • 如果存在主键，主键索引即为聚簇索引；
  • 如果不存在主键，将使用第一个唯一索引作为聚簇索引；
  • 如果不存在主键、也不存在唯一索引，则InnoDB会自动生成一列rowid作为隐藏的聚簇索引。

非聚簇索引：InnoDB中根据非主键字段建立的索引，使用非主键字段作为key，在B+树的叶子节点的数据部分，对应的是相应记录在聚簇索引中的主键值/聚簇索引所用的键值，此为非聚簇索引。在InnoDB的术语中，通常称之为二级索引/辅助索引。

  1. 之所以InnoDB引擎要求必须包含聚簇索引，是因为InnoDB在使用非聚簇索引查询后，如果查询的字段不是主键字段、建立索引的字段，则会根据主键字段在聚簇索引中查询完整记录得到对应字段值，这是InnoDB中回表查询的体现。
  1. 如果在二级索引中，建立索引的字段和主键能够覆盖SELECT语句中查询的全部字段，则称为覆盖索引，此时就不需要进行回表查询了。

7.3 索引语法与使用

  • 创建索引
1
2
CREATE [UNIQUE | FULLTEXT] INDEX index_name
ON table_name(col_name [ASC DESC], ....);

排序的选项默认为ASC，在对使用ORDER BY的语句优化时会用到。 

1
2
3
4
-- 前缀索引建立
CREATE INDEX idx_name
ON table_name(col_name(n));

  • 查看索引
1
2
SHOW INDEX
FROM table_name;
  • 删除索引
1
2
DROP INDEX index_name
ON table_name;
  • SQL提示

在执行SQL语句的时候，MySQL优化器会自动选择它认为最好的方案执行SQL语句，如果要强制MySQL通过某条索引执行语句，可以使用下列语句：

1
2
3
4
5
SELECT * FROM table_name USE INDEX(idx_name) WHERE ...; -- 建议MySQL使用（未必一定会使用）

SELECT * FROM table_name IGNORE INDEX(idx_name) WHERE ...; -- 忽略某个索引

SELECT * FROM table_name FORCE INDEX(idx_name) WHERE ...; -- 强制使用某个索引

7.4 SELECT性能分析

7.4.1 SQL执行频率

查看增删改查相应的SQL语句在全局/当前会话中，在当前数据库中执行的次数。

1
SHOW GLOBAL STATUS LIKE 'Com_______';
  1. 下划线有7条。
  1. 这样做的意义是：索引有助于优化以SELECT语句为主的数据库。

7.4.2 SQL慢查询日志

可以参考慢查询日志，慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

慢查询日志默认没有开启，可以通过以下指令查看：

1
SHOW VARIABLES LIKE 'slow_query_log';

需要在MySQL的配置文件/etc/my.cnf（Linux上）配置如下信息：

注意不同Linux版本可能的配置文件路径不同，Ubuntu上是在 /etc/mysql/mysql.conf.d/mysqld.cnf 上进行修改。

1
2
3
4
5
# 开启MySQL慢日志查询开关
slow_query_log=1

# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，则会被视为慢查询，记录慢查询日志
long_query_time=2

配置完成重启MySQL服务器后，可以查看/var/lib/mysql/localhost-slow.log，其会记录下自定义条件下的慢查询。

Ubuntu上的保存路径是在/var/lib/mysql/hostname-slow.log，其中hostname是主机名。

7.4.3 profile详情

查看当前MySQL是否支持profile操作：

1
SELECT @@have_profiling

如果支持，默认profiling是关闭的，可以通过set语句在session/global级别开启profiling。

1
2
SELECT @@profiling; -- 默认为0
SET profiling = 1;

打开profile之后，可以执行一系列SQL操作，通过如下指令可以查看指令的执行耗时：

1
2
3
4
5
6
7
8
9
-- 查看每一条SQL的耗时基本情况
SHOW PROFILES;

-- 查看指定query_id的SQL语句各个阶段的耗时情况
SHOW PROFILES FOR QUERY query_id;

-- 查看指定query_id的SQL语句CPU的使用情况
SHOW PROFILE CPU FOR QUERY query_id;

7.4.4 EXPLAIN执行计划

EXPLAIN执行计划支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。我们一般多用于分析 SELECT 查询语句，语法如下：

1
EXPLAIN SELECT.... ;

通过该语句，数据库会返回一张具有多个字段的表，记录的是针对当前的查询语句的具体执行流程，其中包含的字段及含义：

字段名 含义
id SELECT查询的序列标识符
select_type SELECT关键字对应的查询类型
table 用到的表名
type 表的访问方法
possible_keys 可能用到的索引
key 实际用到的索引
key_len 所选索引的长度
ref 当使用索引等值查询时，与索引作比较的列或常量
rows 预计要读取的行数
filtered 按表条件过滤后，留存的记录数的百分比
Extra 附加信息

id: id 如果相同，从上往下依次执行；id 不同，id 值越大，执行优先级越高。如果行引用其他行的并集结果，则该值可以为 NULL。

select_type: 查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

  1. SIMPLE：简单查询，不包含UNION或者子查询。
  2. PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT将被标记为PRIMARY。
  3. SUBQUERY：子查询中的第一个SELECT。
  4. UNION：在UNION语句中，UNION之后出现的SELECT。
  5. DERIVED：在FROM中出现的子查询将被标记为 DERIVED。
  6. UNION RESULT：UNION 查询的结果。

table: 查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：

  1. <unionM,N> : 本行引用了 id 为 M 和 N 的行的 UNION 结果；
  2. <derivedN> : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。
  3. <subqueryN> : 本行引用了 id 为 N 的表所产生的的物化子查询结果。

type(重要)：查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

  1. system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
  2. const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
  3. eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
  4. ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
  5. index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
  6. range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
  7. index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
  8. ALL：全表扫描。

possible_keys: possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

key（重要）：key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

key_len: key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。

rows: rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。

Extra（重要）：这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

  1. Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
  2. Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
  3. Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
  4. Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
  5. Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
  6. Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。

7.5 索引失效

索引失效的核心逻辑就在于，在某些条件下，数据库无法利用B+树的有序性进行高效搜索，从而导致索引失效。

  • 对索引列使用计算/函数

假设id为主键，则以下操作会导致索引失效：

1
SELECT * FROM t_user WHERE id + 1 = 9; -- 对索引列进行了计算

假设在t_user表的name字段建立了一个二级索引，则以下操作会导致索引失效：

1
SELECT * FROM t_user WHERE LENGTH(name) = 6; -- 对于索引列使用了函数

这都是因为建立的索引是针对指定列的值构建的B+树，而非使用计算/函数后的值，因此无法在B+树中进行搜索。

  • 对索引使用左模糊匹配

在使用LIKE关键词的时候，如果模糊匹配的对象包含左边，如LIKE %ABC、LIKE %ABC%，都会导致索引失效。

因为B+树构建时是按照从左到右的顺序比较指定列（如VARCHAR类型的name），在左侧进行模糊匹配的情况下，无法通过树形的结构找出所有可能匹配的记录。

  • 对于VARCHAR类型的字段没有使用''进行包裹

如在phone字段（VARCHAR类型）建立索引后进行查找，如果没有使用''，会导致索引失效。

1
SELECT * FROM t_user WHERE phone = 123456789;

根本原因在于，由于phone字段是VARCHAR类型的，此时提供的值为int类型；而在mysql中，字符串会隐式转换为数值类型，数值类型并不会隐式转换为字符串类型；因此，在这里会对phone字段进行类型转换，相当于在这个建立了索引的字段上作用了函数，导致索引失效。

  • 联合索引失效情况

在使用联合索引的情况中，需要遵循最左匹配原则，否则会导致索引失效/部分失效。

如在(a, b, c)三个字段上使用了联合索引（注意和顺序有关），以下的sql语句可以正确使用索引。

1
2
3
SELECT * FROM t_user WHERE a = 1; -- 部分索引
SELECT * FROM t_user WHERE a = 1 AND b = 2; -- 部分索引
SELECT * FROM t_user WHERE a = 1 AND b = 2 AND c = 3; -- 部分索引

注意，在以上的WHERE语句中a、b、c三个字段的出现顺序并不重要，只需要出现就可以，MySQL会自动优化。

但如果WHERE语句中某个字段未出现，则在联合索引的建立顺序中，该字段前方的字段能够正常使用索引，该字段后方的字段哪怕出现在WHERE语句中，也不会使用索引（索引截断）。

1
2
3
SELECT * FROM t_user WHERE b = 2; -- 不会使用索引，因为联合索引中a没有出现，后方索引都失效
SELECT * FROM t_user WHERE c = 3; -- 不会使用索引，因为联合索引中a没有出现，后方索引都失效
SELECT * FROM t_user WHERE b = 2 AND c = 3; -- 不会使用索引，因为联合索引中a没有出现，后方索引都失效

对于索引截断的情况，不同的MySQL版本有不同的处理方式。

1
SELECT * FROM t_user WHERE a = 1 AND c = 3;

MySQL 5.5及之前：在存储引擎层，a通过索引查找到主键，回表查询到完整数据行，返回服务层；在服务层中对返回的数据中c的字段进行比较。

MySQL 5.6及之后：在存储引擎层，通过a字段查找索引的过程中，对索引包含的字段先做判断，直接过滤掉不满足条件的记录，返回服务层。即会使用索引下推的功能：被截断的字段不会在服务层进行判断，而是会被下推到存储引擎层进行条件判断；这样做减少了回表次数，提升了性能。

在使用EXPLAIN解释SELECT语句时，返回的EXTRA字段中如果有USING INDEX CONDITION，则说明使用了索引下推功能。

针对联合索引的范围查询，如果在某个字段的筛选条件中出现>或<，则该字段的后方字段的索引会失效。这是因为联合索引构建的B+树是依次比较字段的值，如果某个字段出现了>或<，则后面的字段就无法保证有序了。

如果是>=、<=、BETWEEN及前缀匹配LIKE，则不会导致索引失效。

  • WHERE子句中的OR

OR两侧的查询条件如果有一侧字段建立了索引，另一侧字段未建立索引，则会导致建立索引的字段不会通过索引进行查询。

如果都建立了索引，则会分别根据两个索引进行查找，再将结果进行合并。使用EXPLAIN解释类似语句的时候，会发现type的值为index merge。

7.6 常见SQL优化技巧

在SELECT性能分析章节中，主要针对的是SELECT语句的性能优化，本章主要介绍对于增删改语句的优化。

7.6.1 INSERT优化

  1. 批量插入

在一条INSERT语句中插入多条记录。

1
INSERT INTO table VALUES (...), (...), (...);
  1. 手动提交事务

在进行INSERT操作前，手动开启事务，在所有插入语句结束后，手动提交事务；避免系统自动频繁地开启、提交事务。

1
2
3
4
START TRANSACTION;
-- A LOT OF INSERT...

COMMIT;
  1. 按照主键顺序递增插入

如果乱序插入，可能会导致频繁的页分裂，降低性能，可以参考主键优化。

  1. 大批量数据插入使用LOAD

一次性插入大批量的数据，可以使用LOAD语句直接将写有大量数据的本地文件传入数据库中的对应表中。

本地文件结构：包含有多条按序存储的记录，字段之间可以自定义分隔符（如,），记录之间也需要自定义分隔符（如\n）。

在连接MySQL服务端时，加上参数--local-infile：

1
mysql --local-infile -u root -p

进入MySQL后，设置全局参数local_infile为1；

1
SET GLOBAL local_infile = 1;

接下来，可以执行load指令：

1
2
3
4
LOAD DATA LOCAL INFILE 'path/to/infile'
INTO TABLE table_name
FIELDS TERMINATED BY ',' -- 指定字段分隔符
LINES TERMINATED BY '\n'; -- 指定记录分隔符

7.6.2 主键优化

在InnoDB中，聚集索引以主键作为key构建B+树，其每个节点为一个数据页（page）。B+树的叶子节点构成双向链表，在每个叶子节点中，即数据页中，存放着多行（ROW）记录，这些记录按照主键的顺序有序排列，这就导致整个B+树的底层节点可以看成是按照主键大小顺序排列的链表，可以参考InnoDB索引详解。

  • 页分裂

由于数据页的大小是固定的（16K），如果乱序插入主键，而插入的位置正好在先前已经装满数据的页中，那么就会出现页分裂：MySQL重新申请一块空间作为数据页，将装满的页中的一半数据以及新插入的数据放入新的数据页，重新组织B+树的结构。

因此，乱序插入可能会导致MySQL的额外开销，降低性能。

  • 页合并

从B+树的某个叶子节点的数据页中删除一行记录时，实际上记录并没有被物理删除，而是被标记为删除，其空间允许被其他记录占用。

当某个数据页中删除的记录达到参数MERGE_THRESHOLD时，InnoDB会开始寻找最靠近的页（前或后），查看是否可以将两个页的记录合并到一个页中。

  • 主键设计原则
  1. 满足业务需求的情况下，尽量减低主键的长度。

InnoDB中所有二级索引的叶子节点都存放记录对应的主键值，主键越长，所有索引都更大、更占内存、更慢。

  1. 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

  2. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号等。

  3. 业务操作时，避免对主键的修改。

7.6.3 ORDER BY优化

在EXPLAIN使用ORDER BY语句的SELECT语句时，在EXTRA字段可能会出现Using filesort。

Using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后在 排序缓冲区（sort buffer） 中完成排序操作，所有不是通过索引直接返回排序结果的排序都属于这一类。（效率低）

使用索引优化：如果可以通过索引直接读取到相关的有序数据（因为B+树底层的双向链表是根据key的大小关系进行排列的），那么就不需要额外的排序操作，可以直接返回，这种情况下效率高。

问题的关键就在于索引构建的B+树的底层节点的排序顺序与ORDER BY要求的是否一致，如果一致，则不会额外进行排序。

  • ORDER BY 单一字段

此时，只要在该字段上存在索引，在不存在索引失效的情况下，可以直接读取到相关的有序数据。降序/升序都可以，因为底层是双向链表，两个方向都可以按序访问。

  • ORDER BY 多个字段

此时，按照联合索引进行访问的话，要求多个字段的排序方式一致（都为降序或升序），那么在联合索引未失效的情况下，可以直接在索引中查询到有序数据并返回。

如果多个字段的排序方式不同，那么在建立联合索引的时候，其字段的排序方式应该与ORDER BY中一致，否则无法通过索引直接返回有序数据，需要经过额外的排序。

  1. 是否通过索引直接返回有序数据与是否为覆盖索引（是否回表）无关。
  1. 是否回表只影响是否出现 Using index，不直接决定会不会 Using filesort，但优化器在“选索引 + 回表成本 + 是否 filesort”之间会做代价权衡。
  1. 在ORDER BY中也要符合联合索引的最左匹配原则，否则会失效。

7.6.4 GROUP BY优化

在EXPLAIN使用GROUP BY语句的SELECT语句时，在EXTRA字段可能会出现Using temporary。

Using temporary：说明产生了临时表用来存储信息，效率低。

使用索引优化：可以通过建立索引，这样让某个字段值相同的数据会优先存放在一起，这样返回的数据本身就是已经分好组的了，不需要使用临时表了。

  1. GROUP BY和ORDER BY两者的优化思路非常相似，可以参考上面的ORDER BY的思路进行一个类比。
  1. GROUP BY在使用联合索引进行优化时，同样需要遵循最左匹配原则。

7.6.5 LIMIT优化

当LIMIT a, b中a参数较大时，查询的效率会很低。

覆盖索引+子查询优化：首先在子查询中按照某种排序方式找到对应分页指定的数据的主键（覆盖索引），然后将该子查询作为表与原表进行连接，查找指定的字段。

例如在user中，按照name排序后，找出索引数从20000开始的10行数据，user表中id为主键。

首先根据name建立索引：

1
CREATE INDEX idx_name ON user(name);

构建子查询：

1
2
3
4
5
SELECT id
FROM user
ORDER BY name
LIMIT 20000, 10;
-- 这样的话

最终查询:

1
2
3
4
5
6
7
8
9
SELECT s.*
FROM user s, (
  SELECT id
  FROM user
  ORDER BY name
  LIMIT 20000, 10
) t
WHERE s.id = t.id;

7.6.6 COUNT优化

聚合函数COUNT通过读取指定的字段值，返回读取的记录中指定字段不为null的个数。

数据库的服务层维护一个值count作为COUNT的结果。在InnoDB引擎中，每次从引擎中返回一条非空记录，count加1，动态维护，速度慢；在MyISAM引擎中，count作为元数据可直接返回，速度快。

先说结论，效率排名：COUNT(*) = COUNT(1) > COUNT(主键) > COUNT(字段)。

  • COUNT(字段)

如果在该字段上没有not null约束，则会从二级索引（如果在指定字段上存在）扫描/全表扫描（聚簇索引），并且取出字段值，在服务层中会对其进行是否为空的判断，计数累加。

如果在该字段上有not null约束，则会从二级索引（如果在指定字 段上存在）扫描/全表扫描（聚簇索引），并且取出字段值，在服务层中直接计数累加。

  1. 这里的字段指的是非主键。
  1. MySQL优化器优先通过二级索引进行扫描，这是因为二级扫描中每个节点存储的值更少，IO开销小。同样的逻辑可以推出：如果有多个二级索引，优化器会优先选择键长更短的二级索引。
  • COUNT(主键)

从二级索引（如果存在）/ 聚簇索引（如果不存在二级索引）扫描，读取ID值/记录中的ID值，返回服务层，直接计数累加。

  1. 由于主键自身含有NOT NULL的约束，因此服务层不需要判断。
  1. 优先选取二级索引的原因也与IO有关。
  • COUNT(1)和COUNT(*)

MySQL对于COUNT(*)做了优化，其实际效果与COUNT(1)差不多。

从二级索引（如果存在）/聚簇索引（如果不存在二级索引）扫描，不取值，返回服务层，直接计数累加。

7.6.7 UPDATE优化

这里主要是针对于UPDATE语句处理不当时，可能会导致行锁失效成为“表锁”。

需要理解InnoDB的锁机制是基于索引的，如果并发事务中，条件语句中的字段没有索引，则会在聚簇索引上扫描所有的记录，这样都会被上行锁，看上去就好像变成了表锁。

例如，在如下这张表上：

1
2
3
4
5
6
7
CREATE TABLE user (
    id     INT PRIMARY KEY,
    name   VARCHAR(50),
    age    INT,
    INDEX idx_age(age)
) ENGINE=InnoDB;

开启一个事务，执行如下UPDATE语句:

1
2
3
-- 事务 A
BEGIN;
UPDATE user SET name = 'Alice' WHERE age = 18;

此时，在age字段上存在二级索引，锁机制就会精准定位到该二级索引中age=18的这一小部分记录；其他age != 18的行在别的事务中可以进行更新，也就是行锁。

1
2
3
4
-- 事务B
BEGIN;
UPDATE user SET name = 'Bob' WHERE age = 19; -- ok
UPDATE user SET name = 'Cat' WHERE age = 18; -- 得不到锁

但如果，是这样的UPDATE语句：

1
2
3
4
-- 事务A，没有给 name 建索引
BEGIN;
UPDATE user SET age = age + 1 WHERE name = 'Bob';

由于在name上没有索引，所以事务A只能扫描聚簇索引，一个一个比对各条记录的name字段，每个被扫描的记录都会被上锁，因此看上去就好像成为了表锁，可以参考之前行级锁的相关内容。

注意，这个行为是在默认的隔离级别RR下的实现，在RC隔离级别下的实现有所不同。

7.7 索引使用原则

  1. 针对于数据量大、查询频繁的表建立索引。

  2. 针对于常作为查询条件、排序、分组的字段建立索引。

  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引效率越高。

  4. 字符串类型的字段，如果字段值较长，可以考虑前缀索引。

  5. 尽量使用联合索引，减少单列索引；联合索引常常可以成为覆盖索引，节省存储空间、避免回表操作，提高查询效率。

  6. 控制索引的数量：索引越多，则对于表的增删改的效率就越低，且会占据更多的磁盘空间。

  7. 如果索引列不能存储NULL值，在创建表时对该字段使用约束NOT NULL；这样MySQL优化器可以更好地确定哪个索引能最有效用于查询。

8. 运维与管理

8.1 系统数据库简介

MySQL数据库安装完成后，自带以下四个数据库：

数据库名 作用
mysql 存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等）。
information_schema 提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等。
perfomance_schema 为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数。
sys 包含了一系列方便数据库管理人员利用perfomance_schema数据库进行性能调优和诊断的视图。

8.2 常用客户端与管理工具

8.2.1 mysql

MySQL的客户端工具。

1
2
3
4
5
6
7
8
9
10
11
mysql [options] [database]

# 选项 ：
# -u, --user=name #指定用户名
# -p, --password[=name] #指定密码
# -h, --host=name #指定服务器IP或域名
# -P, --port=port #指定连接端口
# -e, --execute=name #执行SQL语句并退出

# 示例
mysql -uroot –p123456 db01 -e "select * from stu";

8.2.2 mysqladmin

mysqladmin是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

1
2
3
4
5
6
7
8
9
10
11
mysqladmin [options] command ...

# 选项：
# -u, --user=name #指定用户名
# -p, --password[=name] #指定密码
# -h, --host=name #指定服务器IP或域名
# -P, --port=port #指定连接端口

# 示例：
mysqladmin -uroot –p1234 drop 'test01';
mysqladmin -uroot –p1234 version;

8.2.3 mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具。

1
2
3
4
5
6
7
8
9
10
11
12
13
mysqlbinlog [options] log-files1 log-files2 ...

# 选项：
# -u, --user=name #指定用户名
# -p, --password[=name] #指定密码
# -d, --database=name # 指定数据库名称，只列出指定的数据库相关操作。
# -o, --offset=n #忽略掉日志中的前n行命令。
# -r,--result-file=name # 将输出的文本格式日志输出到指定文件。
# -s, --short-form # 显示简单格式， 省略掉一些信息。
# --start-datatime=date1 --stop-datetime=date2 # 指定日期间隔内的所有日志。
# --start-position=pos1 --stop-position=pos2 # 指定位置间隔内的所有日志。
# -v #将行事件(数据变更)重构为SQL语句
# -vv #将行事件(数据变更)重构为SQL语句，并输出注释信息

8.2.4 mysqlshow

mysqlshow客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

1
2
3
4
5
6
7
8
mysqlshow [options] [db_name] [table_name]  [col_name]

# 选项 
# -u, --user=name #指定用户名
# -p, --password[=name] #指定密码
# --count # 显示数据库及表的统计信息（数据库，表 均可以不指定）
# -i # 显示指定数据库或者指定表的状态信息

8.2.5 mysqldump

mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A

# 选项
# -u, --user=name # 指定用户名
# -p, --password[=name] #指定密码
# -h, --host=name # 指定服务器ip或域名
# -P, --port=xxxx #指定连接端口
# --add-drop-database # 在每个数据库创建语句前加上 drop database 语句
# --add-drop-table # 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)
# -n, --no-create-db # 不包含数据库的创建语句
# -t, --no-create-info # 不包含数据表的创建语句
# -d --no-data # 不包含数据
# -T, --tab=name # 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件

8.2.6 mysqlimport和source

mysqlimport是客户端数据导入工具，用来导入mysqldump加-T参数后导出的文本文件。

1
2
3
4
5
6
7
mysqlimport [options] db_name textfile1 [textfile2...]

# 选项
# -u, --user=name # 指定用户名
# -p, --password[=name] #指定密码
# -h, --host=name # 指定服务器ip或域名
# -P, --port=xxxx #指定连接端口

source在MySQL客户端中使用，可以直接导入.sql文件。

1
source \path\to\xxxx.sql\xxxx.sql;

8.3 日志

8.3.1 错误日志

错误日志记录了MySQL服务器启动和停止时、以及服务器在运行过程中发生任何严重错误时的相关信息。该日志默认开启，可以按照如下指令查看相关信息：

1
SHOW VARIABLES LIKE '%log_error%';

8.3.2 二进制日志

二进制日志记录了所有的DDL语句和DML语句，可用于灾难时的数据恢复和MySQL的主从复制。在MySQL8.0版本中，默认二进制日志是开启的，可以按照如下指令查看相关信息：

1
2
3
4
5
SHOW VARIABLES LIKE '%log_bin%';

-- log_bin_basename: 当前数据库服务器的binlog日志的基础名称，具体的binlog文件名需要在该基础名称上加上编号。
-- log_bin_index: binlog的索引文件，记录了当前服务器关联的binlog文件。

二进制日志不包含查询（SELECT和SHOW）语句。

  • 格式

MySQL服务器提供了多种格式来记录二进制日志，相关参数：binlog_format：

日志格式 含义
STATEMENT 记录对数据进行修改的SQL语句
ROW 记录每一行的数据变更（默认）
MIXED 混合STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录
  • 查看

由于二进制日志为二进制文件无法直接读取，可以利用工具mysqlbinlog使用。

  • 删除

在MySQL8.0中，默认30天会对二进制日志进行自动删除，通过参数binlog_expire_logs_seconds控制。

此外，也可以通过如下指令手动批量删除二进制日志：

1
2
3
4
5
RESET MASTER; -- 删除全部binlog日志，删除后日志编号重新从binlog.000001开始。

PURGE MASTER LOGS TO 'binlog.xxxxxx'; -- 删除xxxxxx编号前的所有日志

PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh24:mi:ss'; -- 删除在指定日期前产生的所有日志。

8.3.3 查询日志

查询日志记录了客户端的所有操作语句，包括查询语句、数据操作语句等。默认情况下，查询日志并未开启，可查看参数general_log，参数general_log_file指定了查询日志的保存文件（xxx.log）。

8.3.4 慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time、扫描记录数不小于min_examined_row_limit的SQL语句日志。

  • 参数说明
    • slow_query_log：是否开启慢查询日志（0：未开启，默认；1：开启）。
    • long_query_time: 慢查询的时间阈值，单位为秒（默认为10s）。
    • log_slow_admin_statements：是否记录管理语句（操作数据库服务器本身的语句）中的慢查询语句（0：未开启，默认；1：开启）。
    • log_queries_not_using_indexes: 是否记录未使用索引语句中的慢查询语句（0：未开启，默认；1：开启）。

9. 高可用与扩展架构

9.1 主从分离

9.1.1 主从复制概念

主从复制是指主数据库的DDL和DML操作通过二进制日志传到从库服务器中，在从库上对这些日志重新执行（重做），从而使得从库和主库的数据保持同步。MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

主从复制概览图

  • 具体步骤
  1. 主库在事务提交时，把数据变更记录在二进制文件bin log中。
  2. 从库读取主库的二进制文件，写入到自身的中继日志relay log中。
  3. 从库重做中继日志中的事件，使自身数据与主库保持一致。
  • 优点
  1. 主库出现问题，可以快速切换到从库提供服务。
  2. 实现读写分离，降低主库的访问压力。
  3. 可以在从库中执行备份，以避免备份期间影响主库服务。

9.1.2 实验复现

  • 主从服务器相关信息

主服务器：Windows宿主机，IP地址为10.203.255.46，MySQL服务器端口为3306 从服务器：Linux虚拟机，IP地址为10.0.2.15。

  • 配置主服务器
  1. 修改配置文件
1
2
3
4
5
6
7
8
9
# 修改MySQL配置文件

server-id=1 # mysql服务ID，保证整个集群环境中唯一，取值范围：1 ~ 2^32 - 1

read-only=0 # 1:只读 0：读写

# binlog-ignore-db=xxxx # 不需要同步的数据库

# binlog-do-db=xxxx # 指定同步的数据库
  1. 重启MySQL服务器
1
2
systemctl restart mysqld #Linux
net start mysql80 #windows，或者win+r services.msc 手动重启mysql80
  1. 创建远程登录账号授予主从复制权限
1
2
CREATE USER 'ygh'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'ygh'@'%';
  1. 查看二进制日志坐标
1
SHOW MASTER STATUS;

查看从哪个日志文件（File）的哪个位置（Position）开始推送日志。

主服务器配置示例

  • 配置从服务器
  1. 修改配置文件
1
2
3
4
5
# 修改MySQL配置文件

server-id=2 # mysql服务ID，保证整个集群环境中唯一，取值范围：1 ~ 2^32 - 1，与主服务器不同即可

read-only=1 # 1:只读 0：读写
  1. 重启MySQL服务器
1
systemctl restart mysql #Linux
  1. 登录mysql,设置主库配置
1
2
3
4
5
6
7
8
9
-- MySQL 8.0.23之后（包括）
CHANGE REPLICATION SOURCE TO 
SOURCE_HOST='10.203.255.46',SOURCE_USER='ygh',SOURCE_PASSWORD='123456',
SOURCE_LOG_FILE='JARVIS-bin.000165',SOURCE_LOG_POS=658;

-- MySQL 8.0.23之前
CHANGE MASTER TO 
MASTER_HOST='10.203.255.46',MASTER_USER='ygh',MASTER_PASSWORD='123456',
MASTER_LOG_FILE='JARVIS-bin.000165',MASTER_LOG_POS=658;
  1. 开启同步操作
1
2
START REPLICA; -- 8.0.23后（包括）
START SLAVE; -- 8.0.23前

如要关闭，把START改成STOP即可。

  1. 查看主从同步状态
1
2
SHOW REPLICA STATUS; -- 8.0.23后（包括）
SHOW SLAVE STATUS; -- 8.0.23前
  • 测试
  1. 在主服务器上执行一系列操作。
1
2
3
4
5
6
7
8
9
CREATE DATABASE db01;
USE db01;
CREATE TABLE tb_user(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  sev CHAR(1)
) ENGINE = innodb;

INSERT INTO tb_user VALUES(NULL, 'ygh', '1'),(NULL, 'ccq', '0');

  1. 查看从服务器上是否有相应的数据。

9.2 分库分表

  • 解决问题

单数据库如果存储量过多会产生一些问题：

  1. IO瓶颈：数据过多，而内存空间有限（缓冲池大小有限），产生大量磁盘IO，效率降低；请求数据太多，带宽不够，网络IO瓶颈。
  2. CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量CPU资源，请求数太多，CPU出现瓶颈。
  • 解决思想

分库分表的中心思想即将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

拆分数据的方式大体上可分为垂直拆分和水平拆分两类：

  1. 垂直拆分：会改动单一数据库/表的结构，拆分成多个结构不同的数据库/表。
  2. 水平拆分：不改动单一数据库/表的结构，拆分成多个结构相同的数据库/表，只是将记录进行分散存储。
  • 技术实现
  1. shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截、解析、改写及路由处理；需自行编码配置实现，只支持Java语言，性能较高。
  2. MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

9.3 读写分离

9.3.1 读写分离概念

  • 思想

读写分离即将数据库的读和写操作分开，以对应不同的数据库服务器：主服务器提供写操作，从数据库提供读操作。这样能够有效地减轻单台数据库的压力。

  • 实现

通过中间件（如MyCat）与主从复制完成读写分离的操作；具体来说，应用程序统一访问中间件（如MyCat），后者对SQL语句进行路由（将读写SQL分离），而为了保证数据库数据的一致性，可以通过主从分离（即传递二进制日志文件）实现。

9.3.2 一主一从读写分离

使用一台主服务器和一台从服务器，主从服务器之间通过主从复制实现同步、通过中间件分发SQL实现负载均衡。

9.3.3 双主双从读写分离

在一主一从读写分离的模式下，如果主服务器宕机，则业务系统就只能读不能写入数据了。提高系统的鲁棒性，可以使用双主双从的架构。

一个主机m1用于处理写，另外一台主机m2以及两台从机用于处理读；当一台主机宕机的时候，另一台主机处理写，剩余的两台从机用于读。

参考学习资源：