MySQL教程

MySQL前置知识

相关概念

部署

数据类型及其数据表列属性

数据表列属性有：

关于属性 CHARACTER SET name

1.创建数据库时指明字符集

CREATE DATABASE IF NOT EXISTS dbtest12 CHARACTER SET 'utf8';

SHOW CREATE DATABASE dbtest12;

2.创建表的时候，指明表的字符集

CREATE TABLE temp(
id INT
)CHARACTER SET 'utf8';

SHOW CREATE TABLE temp;

3.创建表，指明表中的字段时，可以指定字段的字符集

CREATE TABLE temp1(
id INT,
`name` VARCHAR(15) CHARACTER SET 'gbk'
);

SHOW CREATE TABLE temp1;

数值类型

整数类型

整数有以下类型：tinyint、smallint、mediumint、int、bigint

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e6NAQq8O-1689213896436)(./assets/image-20230614012819750.png)]

同一种整数类型，有符号与无符号所表示的数值范围也不同。其中，有符号整数的最小值是一个负数，无符号整数的最小值是0。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hJjZWTbP-1689213896437)(./assets/image-20230614013014147-6677416.png)]

如果使用的数据类型超出了整数类型的范围，则MySQL会抛出相应的错误。因此在实际使用的时候，应该首先确认好数据的取值范围，然后根据确认的结果选择合适的整数类型。

浮点数类型

浮点数类型主要有两种：单精度浮点数FLOAT和双精度浮点数DOUBLE。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xaf5lnVW-1689213896437)(./assets/image-20230614013117634.png)]

对于浮点数来说，有符号与无符号所表示的数值范围也是不同的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iVZIyBDT-1689213896437)(./assets/image-20230614013148660.png)]

浮点数类型中的FLOAT和DOUBLE类型在不指定数据精度时，默认会按照实际的计算机硬件和操作系统决定的数据精度进行显示。如果用户指定的精度超出了浮点数类型的数据精度，则MySQL会自动进行四舍五入操作。

对于浮点数来说，可以使用(M,D)的方式进行表示精度，(M,D)表示当前数值包含整数位和小数位一共会显示M位数字，其中，小数点后会显示D位数字，M又被称为精度，D又被称为标度。

点数不写精度和标度时，会按照计算机硬件和操作系统决定的数据精度进行显示。如果用户指定的精度超出了浮点数类型的数据精度，则MySQL会自动进行四舍五入操作，数据能够插入MySQL中，并能够正常显示。

定点数类型

MySQL中的定点数类型只有DECIMAL一种类型。DECIMAL类型也可以使用(M,D)进行表示。定点数在MySQL内部是以字符串的形式进行存储的，它的精度比浮点数更加精确，适合存储表示金额等需要高精度的数据。

使用定点数类型表示数据时，当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。

时间与日期类型

YEAR

从MysQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是”YYYY”，没必要写成YEAR(4)，
从MySQL8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型。

• 以4位字符串或数字格式表示YEAR类型，其格式为WYY，最小值为1901，最大值为2155。
• 以2位字符串格式表示YEAR类型，最小值为00，最大值为99。
  • 当取值为01到69时，表示2001到2069；
  • 当取值为70到99时，表示1970到1999；
  • 当取值整数的0或00添加的话，那么是0000年；
  • 当取值是日期/字符串的"0"添加的话，是2000年。

DATE类型

在向 DATE 类型的字段插入数据时，同样需要满足一定的格式条件。

• 以YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期，其最小取值为1000-01-01，最大取值为
  9999-12-03。WYYMMDD格式会被转化为YYYY-MM-DD格式。
• 以YY-MM-DD 格式或者YYMMDD 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足
  YEAR类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99
  时，会被转化为1970到1999。
• 使用 CURRENT_DATE()或者 NOW() 函数，会插入当前系统的日期。

TIME类型

使用“HH:MM:SS” 格式来表示 TIME 类型，其中， HH 表示小时， MM 表示分钟， SS 表示秒。

向TIME类型的字段插入数据时，可以使用的格式

• ①可以使用带有冒号的字符串，比如’ D HH:MM:SS’ 、 ’ HH:MM:SS ’ 、 ’ HH:MM ’ 、 ’ D HH:MM ’ 、 ’ D HH ’ 或 ’ SS ’ 格式，都能被正确地插入TIME 类型的字段中。其中 D 表示天，其最小值为 0 ，最大值为 34 。如果使用带有 D 格式的字符串插入TIME 类型的字段时， D 会被转化为小时，计算格式为 D*24+HH 。当使用带有冒号并且不带 D 的字符串表示时间时，表示当天的时间，比如12:10 表示 12:10:00 ，而不是 00:12:10 。
• ②可以使用不带有冒号的字符串或者数字，格式为 ’ HHMMSS ’ 或者 HHMMSS 。如果插入一个不合法的字符串或者数字， MySQL 在存 储数据时，会将其自动转化为00:00:00 进行存储。比如 1210 ， MySQL 会将最右边的两位解析成秒，表示00:12:10，而不是 12:10:00 。
• ③使用 CURRENT_TIME() 或者 NOW() ，会插入当前系统的时间。

DATETIME类型

在格式上为DATE 类型和 TIME 类型的组合，可以表示为 YYYY-MM-DD HH:MM:SS ，其中 YYYY 表示年份， MM 表示月份，DD 表示日期， HH 表示小时， MM 表示分钟， SS 表示秒。

• 以YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
  • 以YYYYMMDD HHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式。
• 以 YY-MM-DD HH:MM:SS 格式或者 YYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则，00到69表示2000到2069；70到99表示1970到1999。
• 使用函数CURRENT_TIMESTAMBC) 和NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间

• YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期插入DATETIME类型的字段时，会自动转换为YYYY-MM-DD 00:00:00；

• 以YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS格式的字符串插入DATE类型的字段时，会自动转换为YYYY-MM-DD进行存储。

• 在查询、删除、修改语句的where字段中，也会遇到上述情况。

TIMESTAMP

显示格式与 DATETIME 类型相同，都是 YYYY - MM - DD HH:MM:SS ，需要 4 个字节的存储空间。但是 TIMESTAMP 存储的时间范围比 DATETIME 要小很多，只能存储 “1970-01-01 00:00:01 UTC”到 “2038-01-19 03:14:07 UTC” 之间的时间。其中， UTC 表示世界统一时间，也叫 作世界标准时间。

存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用 TIMESTAMP 存储的同一个时间值，在不同的时区查询时会显示不同的时间。

TIMESTAMP和DATETIME的区别

• TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
• 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
• TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

文本字符串类型

MySQL中，文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、 LONGTEXT 、 ENUM 、 SET 等类型。

char与varchar类型

CHAR和VARCHAR类型都可以存储比较短的字符串。

CHAR类型

• CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
• 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
• 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。

VARCHAR类型

• VARCHAR(M) 定义时， 必须指定长度M，否则报错。

• MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字 节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。

• 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

varchar与char的选择

• 存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个 byte用于存储信息长度，本来打算节约存储的，结果得不偿失。
• 固定长度的。比如使用uuid作为主键，那么使用char更合适。
• 十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个 非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。
• 具体存储引擎中的情况：
  • MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化，从而使 数据检索更快，用空间换时间。
  • MEMORY 存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用
    CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。
  • InnoDB 存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。这样节省空间，对磁盘I/0和数据存储总量比较好。

TEXT类型

TEXT用来保存文本类型的字符串

由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用 CHAR(M)，或者 VARCHAR(M)。

TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR， VARCHAR来代替。还有TEXT类型不用加默认值，加了也没用。而且text和blob类型的数据删除后容易导致 “空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用 一个表。

ENUM类型

ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM 类型只允许从成员中选取单个值，不能一次选取多个值。 其所需要的存储空间由定义ENUM类型时指定的成员个数决定。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xjDgAyFU-1689213896437)(./assets/image-20230614163403786-6731650.png)]

• 当ENUM类型包含1～255个成员时，需要1个字节的存储空间；
• 当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。
• ENUM类型的成员个数的上限为65535个。

CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);
 
INSERT INTO test_enum
VALUES('春'),('秋');
 
# 忽略大小写
INSERT INTO test_enum
VALUES('UNKNOW');
 
# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum
VALUES('1'),(3);
 
# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');
 
# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);
 
 
SELECT * FROM test_enum;

SET类型

• SET表示一个字符对象，可以包含0个或多个成员，但成员个数的上限为64，设置字段值时，可以取取值范围内的0个或多个值。
• SET类型在存储数据时成员个数越多，其占用的存储空间越大。注意：SET类型在选取成员时，可以一次 选择多个成员，这一点与ENUM类型不同。

CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);
 
INSERT INTO test_set (s) 
VALUES ('A'), ('A,B');
 
#插入重复的SET类型成员时，MySQL会自动删除重复的成员
INSERT INTO test_set (s) 
VALUES ('A,B,C,A');
 
#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。
INSERT INTO test_set (s) 
VALUES ('A,B,C,D');#Data truncated for column 's' at row 1
 
SELECT *
FROM test_set;

二进制字符串类型

MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据。

BINARY与VARBINARY类型

• BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。
• BINARY (M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未指定(M)，表示只能存储1个字节 。例如BINARY (8)，表示最多能存储8个字节，如果字段值不足(M)个字 节，将在右边填充’\0’以补齐指定长度。
• VARBINARY (M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个 字节来存储数据的字节数。VARBINARY类型必须指定(M) ，否则报错。
  

BLOB

• BLOB是一个二进制大对象 ，可以容纳可变数量的数据。
• MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如图片 、 音频和视频 等。
• 需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上 ，并将图片、音频和视频的访问路径存储到MySQL中。
  

TEXT和BLOB的使用注意事项：

• BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值
  会在数据表中留下很大的"空洞"，以后填入这些"空洞"的记录可能长度不同。为了提高性能，建议定期 使用 OPTIMIZE TABLE 功能对这类表进行碎片整理。
• 如果需要对大文本字段进行模糊查询，MysQL 提供了 前缀索引。但是仍然要在不必要的时候避免检 索大型的BLOB或TEXT值。例如，SELECT * 查询就不是很好的想法，除非你能够确定作为约束条件的
  WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。
• 把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可
  以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会 减少主表中的
  碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过
  网络传输大量的BLOB或TEXT值。

JSON类型

Mysql5.7.8版本及其以后提供了一个原生的Json字段类型，Json类型的值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式。在Json列插入或者更新的时候将会自动验证Json文本，未通过验证的文本将产生一个错误信息。

MySQL 提供了一些内置的 JSON 操作函数，用于处理和查询存储在 JSON 类型列中的数据。以下是一些常用的 MySQL JSON 操作函数：

1. JSON_EXTRACT(json_doc, path)：从 JSON 文档中提取指定路径的值。例如，JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') 将返回 “John”。

2. JSON_UNQUOTE(json_val)：移除 JSON 字符串值的引号。例如，JSON_UNQUOTE('"John"') 将返回 “John”。

3. JSON_SEARCH(json_doc, one_or_all, search_str [, escape_char [, path] ...])：在 JSON 文档中搜索指定字符串，并返回匹配的路径。one_or_all 参数可选，用于指定搜索所有匹配项还是仅返回第一个匹配项。例如，JSON_SEARCH('{"name": "John", "age": 30}', 'all', 'John') 将返回 ["$.name"]。

4. JSON_ARRAYAGG(expr)：将表达式的结果作为 JSON 数组聚合。例如，SELECT JSON_ARRAYAGG(name) FROM users 将返回包含所有用户名的 JSON 数组。

5. JSON_OBJECT(key1, value1 [, key2, value2, ...])：创建一个 JSON 对象，其中包含指定的键值对。例如，JSON_OBJECT('name', 'John', 'age', 30) 将返回 {"name": "John", "age": 30}。

6. JSON_ARRAY(elements)：创建一个 JSON 数组，其中包含指定的元素。例如，JSON_ARRAY('John', 30) 将返回 ["John", 30]。

这些函数只是 MySQL 提供的一部分 JSON 操作函数，还有其他更多函数可用于处理 JSON 数据。你可以根据实际需求选择适合的函数来操作和查询 JSON 类型列中的数据。

需要注意的是，这些 JSON 操作函数在 MySQL 5.7 及更高版本中可用，要确保你的 MySQL 版本支持这些函数的使用。

CREATE TABLE test_json(
js json
);
 
INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing",
"city":"beijing"}}');
 
SELECT * FROM test_json;

当需要检索JSON类型的字段中数据的某个具体值时，可以使用“->”和“->>”符号

SELECT 
js -> '$.name' AS NAME,
js -> '$.age' AS age ,
js -> '$.address.province' AS province, 
js -> '$.address.city' AS city
FROM test_json;

-- 创建测试表
CREATE TABLE `tab_json` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `data` json DEFAULT NULL COMMENT 'json字符串',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='json测试表';

-- 查询tab_json表
select * from tab_json;
-- 删除tab_json表
drop table tab_json;

-- ----------------------------------------------------------------
-- 新增数据
INSERT INTO `tab_json`(`id`, `data`) VALUES (1, '{"Tel": "132223232444", "name": "david", "address": "Beijing"}');
INSERT INTO `tab_json`(`id`, `data`) VALUES (2, '{"Tel": "13390989765", "name": "Mike", "address": "Guangzhou"}');
INSERT INTO `tab_json`(`id`, `data`) VALUES (3, '{"success": true,"code": "0","message": "","data": {"name": "jerry","age": "18","sex": "男"}}');
INSERT INTO `tab_json`(`id`, `data`) VALUES (4, '{"success": true,"code": "1","message": "","data": {"name": "tome","age": "30","sex": "女"}}');

-- ----------------------------------------------------------------
-- json_extract
select json_extract('{"name":"Zhaim","tel":"13240133388"}',"$.tel");
select json_extract('{"name":"Zhaim","tel":"13240133388"}',"$.name");

-- ----------------------------------------------------------------
-- 对tab_json表使用json_extract函数
select json_extract(data,'$.name') from tab_json;

#如果查询没有的key,那么是可以查询,不过返回的是NULL.
select json_extract(data,'$.name'),json_extract(data,'$.Tel') from tab_json;  
select json_extract(data,'$.name'),json_extract(data,'$.tel') from tab_json;  
select json_extract(data,'$.name'),json_extract(data,'$.address') from tab_json;

-- ----------------------------------------------------------------
-- 条件查询
select json_extract(data,'$.name'),json_extract(data,'$.Tel') from tab_json where json_extract(data,'$.name') = 'Mike';  

-- ----------------------------------------------------------------
-- 嵌套json查询
select * from tab_json where json_extract(data,'$.success') = true;  
select json_extract(data,'$.data') from tab_json where json_extract(data,'$.success') = true;  
-- 查询data对应json中key为name的值
select json_extract( json_extract(data,'$.data'),'$.name') from tab_json where json_extract(data,'$.code') = "1";  
select json_extract( json_extract(data,'$.data'),'$.name'),json_extract( json_extract(data,'$.data'),'$.age') from tab_json where json_extract(data,'$.code') = "0";  

-- ----------------------------------------------------------------
-- 性能验证 , 通过验证全部都是全表扫描，使用场景：数据量不大json字符串较大则可以采用，数据量较大不建议使用。
explain select * from tab_json where json_extract(data,'$.success') = true;  
explain select json_extract(data,'$.data') from tab_json where json_extract(data,'$.success') = true;  
-- 查询data对应json中key为name的值
explain select json_extract( json_extract(data,'$.data'),'$.name') from tab_json where json_extract(data,'$.code') = "1";  
explain select json_extract( json_extract(data,'$.data'),'$.name'),json_extract( json_extract(data,'$.data'),'$.age') from tab_json where json_extract(data,'$.code') = "0"; 

-- ----------------------------------------------------------------

-- 查询json对接集合对象
INSERT INTO `tab_json`(`id`, `data`) VALUES (5, '{"employee":[{"name": "zhangsan", "code": "20220407001", "age": "18"},{"name": "zhangsan2", "code": "20220407002", "age": "28"}]}');

INSERT INTO `tab_json`(`id`, `data`) VALUES (6, '{"employee":[{"name": "lisi", "code": "20220407003", "age": "16"},{"name": "lisi2", "code": "20220407004", "age": "26"}]}');

-- 查询data对应json值的employee集合对象中name为zhangsan的用户
select * from tab_json tj where JSON_CONTAINS(json_extract(tj.data,'$.employee'),JSON_OBJECT('name', "zhangsan"));
-- zhangsan3不存在返回空
select * from tab_json tj where JSON_CONTAINS(json_extract(tj.data,'$.employee'),JSON_OBJECT('name', "zhangsan3"));

MySQL 用户操作

DDL常见操作

DDL：Data Define Language数据定义语言，主要用来对数据库、表进行一些管理操作。

文中涉及到的语法用[]包含的内容属于可选项，下面做详细说明。

数据库管理

创建库

create database [if not exists] 库名;

删除库

drop databases [if exists] 库名;

建库通用的写法

drop database if exists 旧库名;create database 新库名;

数据表管理

约束讲解

在讲解数据表管理之前，必须先讲解约束。约束用于限制表中数据的规则，强制执行表中数据的完整性和准确性。

根据约束数据列的限制，约束可以分为单列约束和多列约束。

根据约束的作用范围，约束可以分为列级约束（只能作用在一个列上）和表级约束（可以作用在多个列上，单独定义）

根据约束的作用可以分为：

• NOT NULL非空约束，规定某个字段不能为空

• UNIQUE唯一约束**，**规定某个字段在整个表中是唯一的

• PRIMARY KEY主键(非空且唯一)约束

• FOREIGN KEY外键约束

• CHECK检查约束

• DEFAULT默认值约束

NOT NULL (非空约束)

限定某个字段/某列的值不允许为空

空字符串’'不等于NULL，0也不等于NULL 。一个表可以有很多列都分别限定了非空

添加非空约束

建表时

CREATE TABLE 表名称( 
  字段名 数据类型, 
  字段名 数据类型 NOT NULL, 
  字段名 数据类型 NOT NULL 
);

建表后

alter table 表名称 modify 字段名 数据类型 not null;

删除非空约束

alter table 表名称 modify 字段名 数据类型 NULL;#去掉not null，相当于修改某个非注解字段，该字段允 许为空 

或
alter table 表名称 modify 字段名 数据类型;#去掉not null，相当于修改某个非注解字段，该字段允许为空

UNIQUE（唯一性约束）

用来限制某个字段/某列的值不能重复。

• 同一个表可以有多个唯一约束。
• 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
• 唯一性约束允许列值为空。
• 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
• MySQL会给唯一约束的列上默认创建一个唯一索引。

添加约束

建表时

create table 表名称( 
  字段名 数据类型, 
  字段名 数据类型 unique, 
  字段名 数据类型 unique key, 
  字段名 数据类型 
);
create table 表名称( 
  字段名 数据类型, 
  字段名 数据类型, 
  字段名 数据类型, 
  [constraint 约束名] unique key(字段1,字段2...) 
);

建表后

#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的

#方式1： 
alter table 表名称 add unique key(字段1,字段2...); 

#方式2： 
alter table 表名称 modify 字段名 字段类型 unique;

删除约束

删除唯一约束只能通过删除唯一索引的方式删除，唯一索引名就和唯一约束名一样。

如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

ALTER TABLE 表名 DROP INDEX 唯一约束名;

PRIMARY KEY（主键约束）

主键约束用来唯一标识表中的一行记录。主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。

• 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建
• 主键约束对应着表中的一列或者多列（复合主键）
• MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用
• 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了

添加约束

建表时

create table 表名称( 
  字段名 数据类型 primary key, #列级模式 
  字段名 数据类型, 
  字段名 数据类型 
);

create table 表名称( 
  字段名 数据类型, 
  字段名 数据类型, 
  字段名 数据类型, 
  [constraint 约束名] primary key(字段名) #表级模式 
);
// 添加符合主键约束只能在表级模式下添加

建表后

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); 
	#字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

删除约束

删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

alter table 表名称 drop primary key;

AUTO_INCREMENT（自增列约束）

某个字段的值自增

• 一个表最多只能有一个自增长列
• 当需要产生唯一标识符或顺序值时，可设置自增长
• 自增长列约束的列必须是键列（主键列，唯一键列）
• 自增约束的列的数据类型必须是整数类型
• 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值

FOREIGN KEY（外键约束）

DEFAULT（默认值约束）

创建表

create table 表名(
    字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];

1. 在同一张表中，字段名不能相同
2. 宽度和约束条件为可选参数，字段名和类型是必须的
3. 最后一个字段后不能加逗号
4. 类型是用来限制 字段 必须以何种数据类型来存储记录
5. 类型其实也是对字段的约束(约束字段下的记录必须为XX类型)
6. 类型后写的 约束条件 是在类型之外的 额外添加的约束

约束说明

not null：标识该字段不能为空

mysql> create table test1(a int not null comment '字段a');
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 values (null);
ERROR 1048 (23000): Column 'a' cannot be null
mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

default value：为该字段设置默认值，默认值为value

mysql> drop table IF EXISTS test2;
Query OK, 0 rows affected (0.01 sec)
mysql> create table test2(    
  ->   a int not null comment '字段a',   
  ->   b int not null default 0 comment '字段b'    
  -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test2(a) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> select *from test2;
+---+---+
| a | b |
+---+---+
| 1 | 0 |
+---+---+
1 row in set (0.00 sec)

上面插入时未设置b的值，自动取默认值0

primary key：标识该字段为该表的主键，可以唯一的标识记录，插入重复的会报错

两种写法，如下：

方式1：跟在列后，如下：

mysql> drop table IF EXISTS test3;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table test3(   
  ->   a int not null comment '字段a' primary key    
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test3 (a) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test3 (a) values (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

方式2：在所有列定义之后定义，如下：

mysql> drop table IF EXISTS test4;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test4(    
  ->   a int not null comment '字段a',    
  ->   b int not null default 0 comment '字段b',    
  ->   primary key(a)    
  -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test4(a,b) values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test4(a,b) values (1,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

插入重复的值，会报违法主键约束

方式2支持多字段作为主键，多个之间用逗号隔开，语法：primary key(字段1,字段2,字段n)，示例：

mysql> drop table IF EXISTS test7;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table test7(    
  ->    a int not null comment '字段a',    
  ->    b int not null comment '字段b',    
  ->   PRIMARY KEY (a,b)    
  ->  );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test7(a,b) VALUES (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test7(a,b) VALUES (1,1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'

foreign key：为表中的字段设置外键

语法：foreign key(当前表的列名) references 引用的外键表(外键表中字段名称)

mysql> drop table IF EXISTS test6;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table IF EXISTS test5;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test5(   
  ->   a int not null comment '字段a' primary key   
  -> );
Query OK, 0 rows affected (0.02 sec)

mysql> create table test6(    
  ->   b int not null comment '字段b',   
  ->   ts5_a int not null,   
  ->   foreign key(ts5_a) references test5(a)   
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test5 (a) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test6 (b,test6.ts5_a) values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test6 (b,test6.ts5_a) values (2,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`javacode2018`.`test6`, CONSTRAINT `test6_ibfk_1` FOREIGN KEY (`ts5_a`) REFERENCES `test5` (`a`))

说明：表示test6中ts5_a字段的值来源于表test5中的字段a。

注意几点：

• 两张表中需要建立外键关系的字段类型需要一致
• 要设置外键的字段不能为主键
• 被引用的字段需要为主键
• 被插入的值在外键表必须存在，如上面向test6中插入ts5_a为2的时候报错了，原因：2的值在test5表中不存在

unique key(uq)：标识该字段的值是唯一的

支持一个到多个字段，插入重复的值会报违反唯一约束，会插入失败。

定义有2种方式:

方式1：跟在字段后，如下：

mysql> drop table IF EXISTS test8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test8(    
  ->    a int not null comment '字段a' unique key    
  ->  );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test8(a) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test8(a) VALUES (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'a'

方式2：所有列定义之后定义，如下：

mysql> drop table IF EXISTS test9;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test9(    
  ->    a int not null comment '字段a',    
  ->   unique key(a)    
  ->  );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test9(a) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test9(a) VALUES (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'a'

方式2支持多字段，多个之间用逗号隔开，语法：primary key(字段1,字段2,字段n)，示例：

mysql> drop table IF EXISTS test10;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test10(    
  ->   a int not null comment '字段a',   
  ->   b int not null comment '字段b',   
  ->   unique key(a,b)    
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test10(a,b) VALUES (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test10(a,b) VALUES (1,1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'a'

auto_increment：标识该字段的值自动增长（整数类型，而且为主键）

mysql> drop table IF EXISTS test11;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test11(    
  ->   a int not null AUTO_INCREMENT PRIMARY KEY comment '字段a',   
  ->   b int not null comment '字段b'   
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test11(b) VALUES (10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test11(b) VALUES (20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test11;
+---+----+
| a | b  |
+---+----+
| 1 | 10 |
| 2 | 20 |
+---+----+
2 rows in set (0.00 sec)

字段a为自动增长，默认值从1开始，每次+1

关于自动增长字段的初始值、步长可以在mysql中进行设置，比如设置初始值为1万，每次增长10

注意：

自增长列当前值存储在内存中，数据库每次重启之后，会查询当前表中自增列的最大值作为当前值，如果表数据被清空之后，数据库重启了，自增列的值将从初始值开始

我们来演示一下：

mysql> delete from test11;
Query OK, 2 rows affected (0.00 sec)

mysql> insert into test11(b) VALUES (10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test11;
+---+----+
| a | b  |
+---+----+
| 3 | 10 |
+---+----+
1 row in set (0.00 sec)

上面删除了test11数据，然后插入了一条，a的值为3，执行下面操作：

删除test11数据，重启mysql，插入数据，然后看a的值是不是被初始化了？如下：

mysql> delete from test11;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test11;
Empty set (0.00 sec)

mysql> exit
Bye

C:\Windows\system32>net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。

C:\Windows\system32>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。

C:\Windows\system32>mysql -uroot -p
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use javacode2018;
Database changed

mysql> select * from test11;
Empty set (0.01 sec)

mysql> insert into test11 (b) value (100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test11;
+---+-----+
| a | b   |
+---+-----+
| 1 | 100 |
+---+-----+
1 row in set (0.00 sec)

删除表

drop table [if exists] 表名;

修改表名

alter table 表名 rename [to] 新表名;

表设置备注

alter table 表名 comment '备注信息';

表中列的管理

添加列

alter table 表名 add column 列名 类型 [列约束];

mysql> drop table IF EXISTS test14;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> create table test14(
    ->   a int not null AUTO_INCREMENT PRIMARY KEY comment '字段a'
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> alter table test14 add column b int not null default 0 comment '字段b';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table test14 add column c int not null default 0 comment '字段c';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> insert into test14(b) values (10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test14;                                                 c
+---+----+---+
| a | b  | c |
+---+----+---+
| 1 | 10 | 0 |
+---+----+---+
1 row in set (0.00 sec)

修改列

alter table 表名 modify column 列名 新类型 [约束];
或者
alter table 表名 change column 列名 新列名 新类型 [约束];

2种方式区别：modify不能修改列名，change可以修改列名

删除列

alter table 表名 drop column 列名;

DML常见操作

DML(Data Manipulation Language)数据操作语言，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是必须要掌握的指令，DML和SQL中的select熟称CRUD（增删改查）。

插入数据

插入单行数据

insert into 表名[(字段,字段)] values (值,值);
或者
insert into 表名 set 字段 = 值,字段 = 值;

说明：

• 值和字段需要一一对应
• 如果是字符型或日期类型，值需要用单引号引起来；如果是数值类型，不需要用单引号
• 字段和值的个数必须一致，位置对应
• 字段如果不能为空，则必须插入值
• 可以为空的字段可以不用插入值，但需要注意：字段和值都不写；或字段写上，值用null代替
• 表名后面的字段可以省略不写，此时表示所有字段，顺序和表中字段顺序一致。

批量插入

insert into 表名 [(字段,字段)] values (值,值),(值,值),(值,值);
或者
insert into 表 [(字段,字段)] 
数据来源select语句;

数据来源select语句可以有很多种写法，需要注意：select返回的结果和插入数据的字段数量、顺序、类型需要一致。

更新数据

单表更新

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

有些表名可能名称比较长，为了方便操作，可以给这个表名起个简单的别名，更方便操作一些。

如果无别名的时候，表名就是别名。

多表更新

多表更新就是同时更新多个表中的数据

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

-- 无别名方式
update test1,test2 set test1.a = 2 ,test1.b = 2, test2.c1 = 10;
-- 无别名方式
update test1,test2 set test1.a = 2 ,test1.b = 2, test2.c1 = 10 where test1.a = test2.c1;
-- 别名方式更新
update test1 t1,test2 t2 set t1.a = 2 ,t1.b = 2, t2.c1 = 10 where t1.a = t2.c1;
-- 别名的方式更新多个表的多个字段
update test1 as t1,test2 t2 set t1.a = 2 ,t1.b = 2, t2.c1 = 10 where t1.a = t2.c1;

删除数据

delete删除

delete单表删除

delete [别名] from 表名 [[as] 别名] [where条件];

如果无别名的时候，表名就是别名

如果有别名，delete后面必须写别名

如果没有别名，delete后面的别名可以省略不写。

-- 删除test1表所有记录
delete from test1;
-- 删除test1表所有记录
delete test1 from test1;
-- 有别名的方式，删除test1表所有记录
delete t1 from test1 t1;
-- 有别名的方式删除满足条件的记录
delete t1 from test1 t1 where t1.a>100;

delete多表删除

delete [别名1,别名2] from 表1 [[as] 别名1],表2 [[as] 别名2] [where条件];

别名可以省略不写，但是需要在delete后面跟上表名，多个表名之间用逗号隔开。

delete t1 from test1 t1,test2 t2 where t1.a=t2.c2;

truncate删除

truncate只是删除表中的数据

truncate 表名;

drop、truncate、delete区别

DQL常见操作

查询操作需要重点关注以下几点：

• 查询什么：数据表的记录的列值，函数

• 从哪里查询：单表、多表

• 查询的条件：where条件查询

• 查询的结果如何显示：排序、分页、分组

基本的查询

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

• select关键字后接的可以是 函数、表达式、*(表中所有的列)

• select关键字后面的字段可以使用别名：select 字段 [as] 别名 from 表名 [as] 别名

条件查询

select 列名 from 表名 where 列 运算符 值

条件查询运算符

• 值如果是字符串类型，需要用单引号或者双引号引起来。
• sql语句中尽量使用<>来做不等判断
• 对于>、<、>=、<=，数值按照大小比较；字符按照ASCII码对应的值进行比较，比较时按照字符对应的位置一个字符一个字符的比较。

逻辑查询运算符

当我们需要使用多个条件进行查询的时候，需要使用逻辑查询运算符。

逻辑运算符	描述
AND	多个条件都成立
OR	多个条件中满足一个

like

select 列名 from 表名 where 列 like pattern;

pattern中可以包含通配符，有以下通配符：

%：表示匹配任意一个或多个字符

_：表示匹配任意一个字符。

between … and

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围，这些值可以是数值、文本或者日期，属于一个闭区间查询。

selec 列名 from 表名 where 列名 between 值1 and 值2;

返回对应的列的值在[值1,值2]区间中的记录

使用between and可以提高语句的简洁度

两个临界值不要调换位置，只能是大于等于左边的值，并且小于等于右边的值。

in/not in

IN 操作符允许我们在 WHERE 子句中规定多个值。

not in和in刚好相反，in是列表中被匹配的都会被返回，NOT IN是和列表中都不匹配的会被返回。

select 列名 from 表名 where 字段 in (值1,值2,值3,值4);
select 列名 from 表名 where 字段 not in (值1,值2,值3,值4);

in 后面括号中可以包含多个值，对应记录的字段满足in中任意一个都会被返回

in列表的值类型必须一致或兼容

in列表中不支持通配符。

is null / is not null

查询运算符（=、<、>、!=）、like、between and、in、not in对NULL值查询不起效。

mysql为我们提供了查询空值的语法：IS NULL、IS NOT NULL、<=>（安全等于）

<=>：既可以判断NULL值，又可以判断普通的数值，可读性较低，用得较少

建议创建表的时候，尽量设置表的字段不能为空，给字段设置一个默认值

排序查询（order by）

select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc|desc];

需要排序的字段跟在order by之后；

asc|desc表示排序的规则，asc：升序，desc：降序，默认为asc；

支持多个字段进行排序，多字段排序之间用逗号隔开。

order by必须在where之后

分页查询

select 列 from 表 limit [offset,] count;  // 限制返回的行数

offset：表示偏移量，通俗点讲就是跳过多少行，offset可以省略，默认为0，表示跳过0行；范围：[0,+∞)。

count：跳过offset行之后开始取数据，取count行记录；范围：[0,+∞)。

limit中offset和count的值不能用表达式,而且不能为负值。

• 获取前n行数据：select 列 from 表 limit 0,n;

• 获取排名第n到m的记录：select 列 from 表 limit n-1,m-n+1;

• 分页操作：select 列 from 表名 limit (page - 1) * pageSize,pageSize;

  • page：表示第几页
  • pageSize：每页显示多少条记录

分组查询

SELECT column, group_function,... FROM table
[WHERE condition]
GROUP BY group_by_expression
[HAVING group_condition];

• group_function：聚合函数。

• group_by_expression：分组表达式，多个之间用逗号隔开。

• group_condition：分组之后对数据进行过滤。

• 分组中，select后面只能有两种类型的列：

  1. 出现在group by后的列
  2. 或者使用聚合函数的列
  

where和having的区别

where是在分组（聚合）前对记录进行筛选，而having是在分组结束后的结果里筛选，最后返回整个sql的查询结果。

可以把having理解为两级查询，即含having的查询操作先获得不含having子句时的sql查询结果表，然后在这个结果表上使用having条件筛选出符合的记录，最后返回这些记录，因此，having后是可以跟聚合函数的，并且这个聚集函数不必与select后面的聚集函数相同。having中可以使用聚合函数，但是where不能使用聚合函数

组合使

select 列 from 
表名
where [查询条件]
group by [分组表达式]
having [分组过滤条件]
order by [排序条件]
limit [offset,] count;

连接查询

当我们查询的数据来源于多张表的时候，我们需要用到连接查询，连接查询使用率非常高。

连接查询可以分为：

• 内连接
• 外连接
• 左连接
• 右连接

笛卡尔积

介绍连接查询之前，我们需要先了解一下笛卡尔积。

笛卡尔积简单点理解：有两个集合A和B，笛卡尔积表示A集合中的元素和B集合中的元素任意相互关联产生的所有可能的结果。假如A中有m个元素，B中有n个元素，A、B笛卡尔积产生的结果有m*n个结果，相当于循环遍历两个集合中的元素，任意组合。

java伪代码表示如下：

for(Object eleA : A){    
  for(Object eleB : B){        
    System.out.print(eleA+","+eleB);    
  }
}

sql中笛卡尔积语法

select 字段 from 表1,表2[,表N];
或者
select 字段 from 表1 join 表2 [join 表N];

内连接

同时将两表作为参考对象，根据ON后给出的两表的条件将两表连接起来。结果则是两表同时满足ON后的条件的部分才会列出

select 字段 from 表1 inner join 表2 on 连接条件;
或
select 字段 from 表1 join 表2 on 连接条件;
或
select 字段 from 表1, 表2 [where 关联条件];

内连接相当于在笛卡尔积的基础上加上了连接的条件。

当没有连接条件的时候，内连接上升为笛卡尔积。

过程用java伪代码如下：

for(Object eleA : A){    
  for(Object eleB : B){        
    if(连接条件是否为true){            
      System.out.print(eleA+","+eleB);        
    }    
  }
}

外连接

外连接涉及到2个表，分为：主表和从表，要查询的信息主要来自于哪个表，谁就是主表。

外连接查询结果为主表中所有记录。如果从表中有和on条件匹配的，则显示匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示null。

最终：外连接查询结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录。

外连接分为2种：

左外连接（左连接）：使用left join关键字，left join左边的是主表。

右外连接（右连接）：使用right join关键字，right join右边的是主表。

左连接

是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。

select 列 from 主表 left join 从表 on 连接条件;

右连接

是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。

案例演示

内连接

select * from a_table a inner join b_table b on a.a_id = b.b_id;

查询结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1l6JITMG-1689213896438)(./assets/ac0e8ac798064f6bb7a63b6e7c205d00.png)]

左连接

先查左边表所有数据，然后按on条件拼接

select * from a_table a left join b_table b on a.a_id = b.b_id;

查询结果：

右连接

先查右边表所有数据，然后按on条件拼接

select * from a_table a right join b_table b on a.a_id = b.b_id;

查询结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Gq7Zwr8X-1689213896438)(./assets/e90bef2ba6a042c0b6fd25c61495ac00-6823086.png)]

子查询

• 出现在select语句中的select语句，称为子查询或者内查询。外部的select查询语句称为主查询或外查询。
• 我们按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询 、 多行子查询。

单行子查询

子查询返回的结果只有一条记录称为单行子查询。

单行子查询中使用的比较操作符：

// 查询工资大于149号员工工资的员工的信息
select last_name 
from employees
where salary > 
  						(select salary
               from employees
               where employee_id = 149);

多行子查询

子查询返回的结果有多条记录称为多行子查询。

多行比较操作符：

• 重点关注any与all的区别

相关子查询

子查询中引用了外部查询中出现的表的列。它比非相关子查询慢。

SELECT *
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department
);

TCL讲解（事务）

事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。

事务（transaction）应该具有的四个特性：

• 原子性（Atomicity）：事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性（Consistency）：指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
• 隔离性（Isolation）：在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间
• 持久性（Durability）：一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响。

事务分为隐式事务和显式事务两种：

• 隐式事务：该事务没有明显的开启和结束标记，它们都具有自动提交事务的功能；DML语句（insert、update、delete）就是隐式事务。

• 显示事务：该事务具有明显的开启和结束标记；使用显式事务的前提是你得先把自动提交事务的功能给禁用。禁用自动提交功能就是设置autocommit变量值为0（0:禁用 1:开启）

  查看当前的autocommit变量值：select @@autocommit

  设置autocommit变量值：set @@autocommit

开启事务

开启事务的操作

开启事务操作主要是开启显式事务。开启事务有两种方式：

方式一：

//设置不自动提交事务
set autocommit=0;
//执行事务操作
commit|rollback;

方式二：

start transaction;//开启事务
//执行事务操作
commit|rollback;

方式三：

;//开启事务
//执行事务操作
commit|rollback;

案例演示：

mysql> create table test1 (a int);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test1;
Empty set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+------+
| a    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values (3);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

savepoint关键字

在事务中我们执行了一大批操作，可能我们只想回滚部分数据，怎么做呢？

我们可以将一大批操作分为几个部分，然后指定回滚某个部分。可以使用savepoin来实现，效果如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> savepoint part1;//设置一个保存点
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)
mysql> rollback to part1;//将savepint = part1的语句到当前语句之间所有的操作回滚
Query OK, 0 rows affected (0.00 sec)
mysql> commit;//提交事务
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

从上面可以看出，执行了2次插入操作，最后只插入了1条数据。

savepoint需要结合rollback to sp1一起使用，可以将保存点sp1到rollback to之间的操作回滚掉。

事务隔离级别

解决的问题

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采用必要的隔离机制，就会发生以下各种并发问题。

• 脏读：对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的（一个事务读取了另一个事务更新但没有提交的数据）

• 不可重复读：对于两个事务T1,T2，T1读取了一个字段，然后T2更新了该字段之后，T1在读取同一个字段，值就不同了

• 幻读：对于两个事务T1,T2，T1在A表中读取了一个字段，然后T2又在A表中插入了一些新的数据时，T1再读取该表时，就会发现神不知鬼不觉的多出几行了…

不可重复读与幻读的区别：

• 不可重复读强调的是修改；
• 幻读的重点是新增或者删除；原来的存在的现在不存在了；原来不存在的现在存在了。
• 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

幻影记录：将同一事务的两次读取返回的结果进行对比，第二次查询返回的结果

所以，为了避免以上出现的各种并发问题，我们就必然要采取一些手段。mysql数据库系统提供了四种事务的隔离级别，用来隔离并发运行各个事务，使得它们相互不受影响，这就是数据库事务的隔离性。

四种隔离级别

mysql中的四种事务隔离级别如下：

• read uncommitted（读未提交数据）：允许事务读取未被其他事务提交的变更。（脏读、不可重复读和幻读的问题都会出现）。

• read committed（读已提交数据）：只允许事务读取已经被其他事务提交的变更。（可以避免脏读，但不可重复读和幻读的问题仍然可能出现）

• repeatable read（可重复读）（mysql默认隔离级别）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新(update)。（可以避免脏读和不可重复读，但幻读仍然存在）

• serializable（串行化）：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可避免，但性能十分低下（因为你不完成就都不可以弄，效率太低）

查看与设置事务隔离级别

查看当前的事务隔离级别通过 tx_isolation变量或者transaction_isolation(版本5.7.20以后使用)；mysql的默认隔离级别repeatable read（可重复读)

select @@tx_isolation / @@transaction_isolation;

设置事务隔离级别：

#设置当前mysql连接的隔离级别:
set session transaction isolation level read uncommitted;
#设置数据库系统的全局的隔离级别:
set global transaction isolation level read uncommitted;

• 当前mysql连接的隔离级别：另外一个并发的“mysqy程序”的隔离级别不会受到当前连接的影响，而是保持默认的repeatable read。

• 全局的事务隔离级别：设置全局的事务隔离级别之后，整个mysql数据库（包括所有打开的mysql程序连接）的隔离级别都会随之改变，除非服务器重启，不然就不会恢复默认了。

事务底层实现

事务有4种特性：原子性、一致性、隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢？

• 锁机制 实现了事务的隔离性。
• 事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。
  • REDO LOG 称为 重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。
  • UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

有的DBA或许会认为 UNDO 是 REDO 的逆过程，其实不然。REDO 和 UNDO都可以视为是一种 恢复操作，但是：

• redo log: 是存储引擎层 (innodb) 生成的日志，记录的是"物理级别"上的页修改操作，比如页号xxx，偏移量yyy写入了’zzz’数据。主要为了保证数据的可靠性。
• undo log: 是存储引擎层 (innodb) 生成的日志，记录的是 逻辑操作 日志，比如对某一行数据进行了INSERT语句操作，那么undo log就记录一条与之相反的DELETE操作。主要用于 事务的回滚 (undo log 记录的是每个修改操作的 逆操作) 和 一致性非锁定读 (undo log 回滚行记录到某种特定的版本——MVCC，即多版本并发控制)。

事务日志

事务日志是为了实现事务的一致性、持久性和原子性。

redo日志

引入redo日志的原因

InnoDB存储引擎是以页为单位来管理存储空间的。在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘 (checkPoint机制)，通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降太快。

然而由于checkpoint 并不是每次变更的时候就触发 的，而是master线程隔一段时间去处理的。所以最坏的情况就是事务提交后，刚写完缓冲池，数据库宕机了，那么这段数据就是丢失的，无法恢复。

为了实现事务的持久性，InnoDB引擎的事务引入了WAL (Write-Ahead Logging)技术，这种技术的思想就是先写日志(redo log)，再写磁盘，只有日志写入成功，才算事务提交成。当发生宕机且数据未刷到磁盘的时候，可以通过redo log`来恢复。

redo log组成

Redo log可以简单分为以下两个部分：

• 重做日志的缓冲 (redo log buffer) ，保存在内存中，是易失的。

在服务器启动时就会向操作系统申请了一大片称之为 redo log buffer (redo日志缓冲区)的 连续内存 空间。这片内存空间被划分为若干个连续的redo log block。一个redo log block占用512字节大小。

• 重做日志文件 (redo log file) ，保存在硬盘中，是持久的。

  参数设置：innodb_log_buffer_size：

  redo log buffer 大小，默认 16M ，最大值是4096M，最小值为1M。

  mysql> show variables like '%innodb_log_buffer_size%';
  +------------------------+----------+
  | Variable_name          | Value    |
  +------------------------+----------+
  | innodb_log_buffer_size | 16777216 |
  +------------------------+----------+
  

Redo日志的好处、特点

• 好处

  • redo日志降低了刷盘频率

  • redo日志占用的空间非常小

存储表空间ID、页号、偏移量以及需要更新的值，所需的存储空间是很小的，刷盘快。

• 特点

  • redo日志是顺序写入磁盘的

    在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序ID，效率比随机IO快。

  • 事务执行过程中，redo log不断记录

    redo log跟bin log的区别，redo log是存储引擎层产生的，而bin log是数据库层产生的。假设一个事务，对表做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而bin log不会记录，直到这个事务提交，才会一次写入到bin log文件中。

redo log的工作流程

以一个更新事务为例，redo log 流转过程，如下图所示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZscfAT0Y-1689213896438)(./assets/image-20220710204810264-16574572910841.png)]

1. 先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝；
2. 生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值；
3. 当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式；
4. 定期将内存中修改的数据刷新到磁盘中；

Write-Ahead Log(预先日志持久化)：在持久化一个数据页之前，先将内存中相应的日志页持久化。

redo log的刷盘策略

redo log的写入并不是直接写入磁盘的，InnoDB引擎会在写redo log的时候先写redo log buffer，之后以某种刷盘策略刷入到真正的redo log file 中（先写入文件系统缓存中，再写入到硬盘中的redo.file中）。

刷盘策略只是控制redo log buffer中的日志刷新到redo.file的过程。

redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到 文件系统缓存 （page cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系统自己来决定（比如page cache足够大了）。那么对于InnoDB来说就存在一个问题，如果交给系统来同 步，同样如果系统宕机，那么数据也丢失了（虽然整个系统宕机的概率还是比较小的）。

针对这种情况，InnoDB给出 innodb_flush_log_at_trx_commit 参数，该参数控制 commit提交事务 时，如何将 redo log buffer 中的日志刷新到 redo log file 中。

  MySQL [user_db]> show variables like 'innodb_flush_log_at_trx_commit';
  +--------------------------------+-------+
  | Variable_name                  | Value |
  +--------------------------------+-------+
  | innodb_flush_log_at_trx_commit | 1     |
  +--------------------------------+-------+
  1 row in set (0.006 sec)

另外，InnoDB存储引擎有一个后台线程，每隔1秒，就会把redo log buffer中的内容写到文件系统缓存(page cache)，然后调用刷盘操作。也就是说，一个没有提交事务的redo log记录，也可能会刷盘。因为在事务执行过程 redo log 记录是会写入 redo log buffer中，这些redo log 记录会被后台线程刷盘。

除了后台线程每秒1次的轮询操作，还有一种情况，当redo log buffer占用的空间即将达到innodb_log_buffer_size（这个参数默认是16M）的一半的时候，后台线程会主动刷盘。

Innodb_flush_log_at_trx_commit=1

1. 开启事务，更新数据，生成一条重做日志并写入 redo log buffer中；
2. 提交事务时，将redo log buffer中的重做日志写入到文件系统缓存中（同时将buffer中的该事务有关的内容清空），然后写入到硬盘的redo log文件中

如果步骤1和步骤2之间的时间超过一秒，那么后台线程会先执行刷盘。

• 只要事务提交成功，redo log记录就一定在硬盘中，不会有任何数据丢失。
• 如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。可以保证ACID中的D，数据绝对不会丢失，但是效率是最差的。

Innodb_flush_log_at_trx_commit=2

1. 开启事务，更新数据，生成一条重做日志并写入 redo log buffer中；
2. 提交事务时，将redo log buffer中的重做日志写入到文件系统缓存中（同时将buffer中的该事务有关的内容清空）

提交事务只保证重做日志写入到文件系统缓存中，不保证刷盘到redo.file中。

Innodb_flush_log_at_trx_commit=0

提交事务时不会要求redo log buffer中的重做日志写入到文件系统缓存中，更不会要求刷新到redo.file中。这种策略会有丢失数据的风险，也无法保证ACID中的D。

写入redo log buffer 过程

Mini-Transaction

MySQL把对底层页面中的一次原子访问过程称之为一个Mini-Transaction，简称mtr，比如，向某个索引对应的B+树中插入一条记录的过程就是一个Mini-Transaction。一个所谓的mtr可以包含一组redo日志，在进行崩溃恢复时这一组redo日志可以作为一个不可分割的整体。

一个事务可以包含若干条语句，每一条语句其实是由若干个 mtr 组成，每一个 mtr 又可以包含若干条 redo日志，画个图表示它们的关系就是这样：

redo log block的结构

前面讲到过，在服务器启动时就会向操作系统申请了一大片称之为 redo log buffer (redo日志缓冲区)的 连续内存 空间。这片内存空间被划分为若干个连续的redo log block。一个redo log block占用512字节大小。

一个redo log block是由日志头、日志体、日志尾组成。日志头占用12字节，日志尾占用8字节，所以一个block真正能存储的数据是512-12-8=492字节。

为什么一个block设计成512字节？

这个和磁盘的扇区有关，机械磁盘默认的扇区就是512字节，如果你要写入的数据大于512字节，那么要写入的扇区肯定不止一个，这时就要涉及到盘片的转动，找到下一个扇区，假设现在需要写入两个扇区A和1B，如果扇区A写入成功，而扇区B写入失败，那么就会出现 非原子性 的写入，而如果每次只写入和扇区的大小一样的512字节，那么每次的写入都是原子性的。

真正的redo日志都是存储到占用496字节大小的log block body中，图中的log block header和log block trailer存储的是一些管理信息。

• log block header 的属分别如下：
  • LOG_ BLOCK_HDR_NO: log buffer是由log block组成，在内部log buffer就好似一个数组，因此LOG_ BLOCK_ HDR_NO用来标记这个数组中的位置。其是递增并且循环使用的，占用4个字节，但是由于第一位用来判断是否是flush bit，所以最大的值为2G。
  • LOG_ BLOCK_HDR_DATA_LEN： 表示block中已经使用了多少字节，初始值为12（因为 1og block body从第12个字节处开始）。随着往block中写入的redo日志越来也多，本属性值也跟着增长。如果 10gblock body 已经被全部写满，那么本属性的值被设置为 512。
  • LOG_ BLOCK_FIRST_REC_GROUP：一条redo日志也可以称之为一条redo日志记录 (redo log record)，一个mtr会生产多条redo日志记录，这些redo日志记录被称之为一个redo日志记录组 (redo log recordgroup)。LOG_BLOCK_FIRST_REC_GROUP就代表该block中第一个mtr生成的redo日志记录组的偏移量（其实也就是这个block里第一个mtr生成的第一条redo日志的偏移量）。如果该值的大小和LOG_ BLOCK_HDR_ DATA_LEN 相同，则表示当前log block不包含新的日志。
  • LOG_BLOCK_CHECKPOINT_NO：占用4字节，表示该log block最后被写入时的 checkpoint。
• log block trailer 中属性的意思如下：
  • LOG_BLOCK_CHECKSUM： 表示block的校验值，用于正确性校验（其值和LOG_BLOCK_HDR_NO相同），我们暂时不关心它。

redo 日志写入log buffer

向 log buffer 中写入redo日志的过程是顺序的，也就是先往前边的block中写，当该block的空闲空间用完之后再往下一个block中写。当我们想往 log buffer 中写入redo日志时，第一个遇到的问题就是应该写在哪个 block的哪个偏移量处，所以InnoDB 的设计者特意提供了一个称之为 buf_free的全局变量，该变量指明后续写入的redo日志应该写入到 log buffer 中的哪个位置，如图所示：

—个mtr执行过程中可能产生若干条redo日志，这些redo日志是一个不可分割的组，所以其实并不是每生成一条redo日志，就将其插入到log buffer中，而是每个mtr运行过程中产生的日志先暂时存到一个地方，当该mtr结束的时候，将过程中产生的一组redo日志再全部复制到log bufer中。我们现在假设有两个名为T1、T2 的事务，每个事务都包含2个mtr，我们给这几个mtr命名一下：

• 事务T1 的两个mtr 分别称为mtr_T1-1和mtr-T1_2。
• 事务T2的两个mtr 分别称为mtr_T2_1 和mtr-_T2_2。

每个mtr都会产生一组redo日志，用示意图来描述一下这些 mtr 产生的日志情况：

不同的事务可能是 并发 执行的，所以 T1 、 T2 之间的 mtr 可能是 交替执行 的。每当一个mtr执行完成时，伴随该mtr生成的一组redo日志就需要被复制到log buffer中，也就是说不同事务的mtr可能是交替写入log buffer的，我们画个示意图（为了美观，我们把一个mtr中产生的所有redo日志当做一个整体来画）：

有的mtr产生的redo日志量非常大，比如mtr_t1_2产生的redo日志占用空间比较大，占用了3个block来存储。

undo日志（未完成）

log是事务持久性的保证，undo log是事务原子性的保证。在事务中 更新数据的前置操作 其实是要先写入一个 undo log。

undo日志的必要性

事务需要保证 原子性 ，也就是事务中的操作要么全部完成，要么什么也不做。但有时候事务执行到一半会出现一些情况，比如：

• 情况一：事务执行过程中可能遇到各种错误，比如 服务器本身的错误 ， 操作系统错误 ，甚至是突然 断电 导致的错误。
• 情况二：程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前事务的执行。

以上情况出现，我们需要把数据改回原先的样子，这个过程称之为 回滚 ，这样就可以造成一个假象：这 个事务看起来什么都没做，所以符合 原子性 要求。

每当我们要对一条记录做改动时（这里的改动 可以指 INSERT、 DELETE、 UPDATE)，都需要"留一手"一一把回滚时所需的东西记下来。比如：

• 插入一条记录 时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录刪掉就好了。（对于每个INSERT， InnoDB存储引擎会完成一个DELETE)

• 删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。（对于每个DELETE， InnoDB存储引擎会执行一个INSERT)

• 修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录 更新为旧值就好了。（对于每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去）

MysQL把这些为了回滚而记录的这些内容称之为撤销日志 或者 回滚日志（即 undo log）。注意，由于查询操作(SELECT）并不会修改任何用户记录，所以在查询操作执行时，并 不需要记录相应的undo日志。此外，undo log 会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。

undo log的作用有：

• 回滚数据：将数据库逻辑地恢复到原来的样子
• MVCC：当用户读取一行记录时，若该记录以及被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

undo的存储结构

锁机制

事务的隔离性是由锁来保证的

当多个事务并发访问数据时，会出现以下三种情况：

• 读-读：并发事务相继读取相同的记录，由于没有更改数据，所以不存在并发安全的问题。

• 写-写：并发事务相继对相同的记录进行修改，导致脏读。因为任何一种隔离级别都不允许发生脏写，所以多个未提交的事务对同一个记录修改时需要加锁，保证它们是顺序执行的。

• 写-读或读-写：一个事务进行读取操作，另一个进行改动操作，这种情况下可能发生脏读 、不可重复读 、幻读的问题。

有以下两种解决方案解决脏读、不可重复读、幻读问题：

• 读操作利用MVVC并发控制，写操作进行加锁，读-写操作彼此并不冲突，性能更改。

  • 在READ COMMITED的隔离级别下，一个事务在执行过程中每次执行select操作时都会生成一个ReadView，ReadView的存在本身就保证了事务不可读取到未提交的事务所做的更改，也就是避免了脏读现象。

  • 在 REPEATABLE READ 隔离级别下，一个事务在执行过程中只有 第一次执行SELECT操作 才会生成一个ReadView，之后的SELECT操作都 复用 这个ReadView，这样也就避免了不可重复读和幻读的问题。

  • 读写都采用加锁的方式，读写也需要排队执行，性能较差。

锁的分类

从数据操作的类型分类

• 共享锁（S锁）：也称读锁。针对同一份数据，多个事务的读操作不会相互影响，也不会相互阻塞。

• 排他锁（X锁）：也称写锁。当前写操作没有完成前，它会阻断其他写锁和读锁。这样 就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

  对于 InnoDB引擎来说，读锁和写锁可以加在表上，也可以加在行上。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SZxdU96N-1689213896438)(./assets/image-20230621214903366-7355345-7355347.png)]

讲解完共享锁与排它锁，下面讲讲它们的应用。

共享锁（读锁）

单纯的select语句并不会加任何锁，只是快照读。需要锁定读的话，必须手动添加锁。

对读取的记录加共享锁：

SELECT ... LOCK IN SHARE MODE;
--或者
SELECT ... FOR SHARE [NOWAIT|SKIP LOCKED];
-- 8.0新特性，NOWAIT表示不等待直接报错，
-- SKIP LOCKED表示立即返回，但返回的结果不包含被锁定的行

如果当前事务执行了该语句（SELECT … LOCK IN SHARE MODE;），那么它会对读取到的记录加共享锁，其他事务能继续获取这些记录的共享锁，但是不能获取这些记录的排它锁，那么他们会阻塞，直到当前事务提交之后将这些记录的共享锁释放。

对读取的记录加排它锁：

SELECT ... FOR UPDATE;

SELECT ... FOR UPDATE [NOWAIT|SKIP LOCKED];
-- 8.0新特性，NOWAIT表示不等待直接报错。
-- SKIP LOCKED表示立即返回，但返回的结果不包含被锁定的行

如果当前事务执行该语句，那么它会为读取到的记录加排它锁，不允许别的事务获取这些记录的排他锁、共享锁。别的事务想要获取这些记录的排他锁、共享锁，那么他们会阻塞，直到事务提交之后将这些记录上的排他锁释放掉。

排它锁（写锁）

写操作无非就是这三种：delete、update、insert

• delete：底层是先获取这条记录的排它锁，再执行删除操作

• update：

  • ①如果不是修改主键且修改后数据占用空间不变，则获取X锁，然后直接修改即可

  • ②如果是修改主键或者是记录修改后占用空间发生变化，则先获取X锁，再删除记录，最后重新插入新的记录

• insert：新插入记录加不了锁，但MySQL会通过建立隐式锁保护这个新插入的记录不被别的事务访问。

从锁的粒度分类

从数据操作的粒度划分：表级锁、页级锁、行锁。

每个层级的锁数量是有限制的，因为锁会占用内存空间， 锁空间的大小是有限的 。当某个层级的锁数量超过了这个层级的阈值时，就会进行 锁升级 。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如 InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

InnoDB中的行锁

行锁（Row Lock）也称为记录锁，就是锁住某一行（某条记录 row）。只有InnoDB存储引擎才有行级锁的功能，所以本小节标题写的是InnoDB的行级锁。

• InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。在InnoDB存储引擎中，默认情况下使用行级锁，而不是表级锁。
• MySQL服务器层并没有实现行锁机制，行级锁只在存储引擎层实现。
• InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁

在MySQL中，行锁可以根据其作用范围和行级别的锁定方式进行分类。下面是MySQL行锁的两种主要分类：

1. 共享锁（Shared Lock）和排他锁（Exclusive Lock）：

   • 共享锁（Shared Lock）：也称为读锁（Read Lock），允许多个事务同时持有共享锁，并且可以读取被锁定行的数据，但不能修改或删除数据。其他事务也可以同时获取相同的共享锁，但不能获取排他锁。
   • 排他锁（Exclusive Lock）：也称为写锁（Write Lock），一个事务持有排他锁时，其他事务无法同时持有任何锁（包括共享锁和排他锁），并且不能读取、修改或删除被锁定行的数据。只有当持有排他锁的事务释放锁后，其他事务才能获取锁。

2. 记录锁（Record Lock）、间隙锁（Gap Lock）和临建锁（Next-Key Lock）：

   • 记录锁（Record Lock）：在索引记录上设置的锁，用于锁定单个行。当使用SELECT ... FOR UPDATE语句并且条件使用唯一索引或主键索引进行等值检索时，MySQL会使用记录锁。
   • 间隙锁（Gap Lock）：在索引范围之间的间隙上设置的锁，用于防止其他事务在该范围内插入新行。当使用SELECT ... FOR UPDATE语句并且条件使用普通索引进行范围检索时，MySQL会使用间隙锁。
   • 临建锁（Next-Key Lock）：结合了记录锁和间隙锁的特性，可以同时锁定索引记录和间隙。临建锁用于防止幻读和范围插入问题。当使用SELECT ... FOR UPDATE语句并且条件使用唯一索引或主键索引进行范围检索时，MySQL会使用临建锁。

   间隙锁在可重复读的隔离级别下，会存在死锁问题。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

对记录进行增删改查过程中加的锁是什么类型：

• insert、delete、update操作时，对记录添加的是行级别的排它锁，是一种自动加锁行为。

• select操作时：select ...for share是加共享锁，select ...for udate是加排它锁。select语句无论是加共享锁还是排它锁，都会根据where条件来添加何种类型的行锁：

  • where条件中使用唯一索引或主键索引：

    • 进行等值检索时，能够查询到数据，对查询到的记录加记录锁（REC_NOT_GAP）；不能查到数据，那么加间隙锁。
    • 进行范围检索，无论能不能查找到记录，都是加间隙锁。

  • where条件中使用除唯一索引或主键索引之外的索引：

    • 进行等值检索时，能够查询到数据，

    • 进行范围检索，无论能否查找到数据，

  • where条件中没有使用任何索引，那么会进行全表扫描，对所有遍历过的记录仅仅加排它锁，遍历完之后，表中所有记录都被加行级别的排它锁，mysql会自动进行锁升级，只对整个表加排它锁。

首先我们创建表如下：

CREATE TABLE student (
	id INT,
    name VARCHAR(20),
    class VARCHAR(10),
    PRIMARY KEY (id)
) Engine=InnoDB CHARSET=utf8;

向这个表里插入几条记录：

INSERT INTO student VALUES
(1, '张三', '一班'),
(3, '李四', '一班'),
(8, '王五', '二班'),
(15, '赵六', '二班'),
(20, '钱七', '三班');

mysql> SELECT * FROM student;

student表中的聚簇索引的简图如下所示。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wiHqk7xN-1689213896438)(./assets/image-20220713163353648.png)]

这里把B+树的索引结构做了超级简化，只把索引中的记录给拿了出来，下面看看都有哪些常用的行锁类型。

① 记录锁（Record Locks）

记录锁也就是仅仅把一条记录锁，官方的类型名称为：LOCK_REC_NOT_GAP。比如我们把id值为8的那条记录加一个记录锁的示意图如果所示。仅仅是锁住了id值为8的记录，对周围的数据没有影响。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-srsUkdb7-1689213896439)(./assets/image-20220713164811567.png)]

举例如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TrVS5iIS-1689213896439)(./assets/image-20220713164948405.png)]

记录锁是有S锁和X锁之分的，称之为 S型记录锁 和 X型记录锁 。

• 当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；
• 当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁。

加锁的执行过程中所有扫描到的行都会被锁上，因此必须确定条件使用了索引，这样才能精准锁定，而如果没有索引，会进行全表扫描，那么就会锁住无关紧要的数据。

② 间隙锁（Gap Locks）

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方 案解决，也可以采用 加锁 方案解决。但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些 幻影记录 加上 记录锁 。InnoDB提出了一种称之为 Gap Locks 的锁，官方的类型名称为： LOCK_GAP ，我们可以简称为 gap锁 。比如，把id值为8的那条 记录加一个gap锁的示意图如下。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0gAibxEL-1689213896439)(./assets/image-20220713171650888.png)]

图中id值为8的记录加了gap锁，意味着 不允许别的事务在id值为8的记录前边的间隙插入新记录 ，其实就是 id列的值(3, 8)这个区间的新记录是不允许立即插入的。比如，有另外一个事务再想插入一条id值为4的新 记录，它定位到该条新记录的下一条记录的id值为8，而这条记录上又有一个gap锁，所以就会阻塞插入 操作，直到拥有这个gap锁的事务提交了之后，id列的值在区间(3, 8)中的新记录才可以被插入。

**gap锁的提出仅仅是为了防止插入幻影记录而提出的。**虽然有共享gap锁和独占gap锁这样的说法，但是它们起到的作用是相同的。而且如果对一条记录加了gap锁（不论是共享gap锁还是独占gap锁），并不会限制其他事务对这条记录加记录锁或者继续加gap锁。

举例：

Session1	Session2
select * from student where id=5 lock in share mode;
	select * from student where id=5 for update;

这里session2并不会被堵住。因为表里并没有id=5这条记录，因此session1加的是间隙锁(3,8)。而session2也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙锁，不允许插入值。但，它们之间是不冲突的。

注意，给一条记录加了 gap锁 只是 不允许 其他事务往这条记录前边的间隙 插入新记录，那对于最后一条记录之后的间隙，也就是student 表中id值为 20 的记录之后的间隙该咋办呢？也就是说给哪条记录加 gap锁 才能阻止其他事务插入id 值在(20，+∞）这个区间的新记录呢？这时候我们在讲数据页时介绍的两条伪记录派上用场了：

• Infimum记录，表示该页面中最小的记录。
• Supremun记录，表示该页面中最大的记录。

为了实现阻止其他事务插入id值再(20,正无穷)这个区间的新纪录，我们可以给索引中的最后一条记录，也就是id值为20的那条记录所在页面的Supremun记录加上一个gap锁，如图所示。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aE0qAkH9-1689213896439)(./assets/image-20220713174108634.png)]

mysql> select * from student where id > 20 lock in share mode;
Empty set (0.01 sec)

检测：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aWUUVNFd-1689213896439)(./assets/image-20230623202545871.png)]

这样就可以阻止其他事务插入iad值在(20，+∞)这个区间的新记录。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。下面的例子会产生死锁

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GbNHfmQn-1689213896439)(./assets/image-20230623210250618.png)]

（1）session 1执行 select .... for update 语句，由于id=5这一行并不存在，因此会加上间隙锁 (3， 8);

（2）session 2 执行 select ... for update 语句，同样会加上间隙锁（3，8)，间隙锁之间不会冲突，因此这个语句
可以执行成功；

（3）session 2 试图插入一行(5，“罗翔”，“二班”），被 session 1 的间隙锁挡住了，只好进入等待；

(4) session 1 试图插入一行(6, “马化腾”，“三班”），被 session 2 的间隙锁挡住了。至此，两个 session 进入互相等
待状态，形成死锁。当然，InnoDB 的死锁检测马上就发现了这对死锁关系，让 session 1 的 insert 语句报错返回，同时让session 1事务停止，session 2的事务不会停止。

③ 临键锁（Next-Key Locks）

临键锁（Next-Key Locks） == 记录锁 + 间隙锁

有时候我们既想 锁住某条记录 ，又想 阻止 其他事务在该记录前边的 间隙插入新记录 ，所以InnoDB就提 出了一种称之为 Next-Key Locks 的锁，官方的类型名称为： LOCK_ORDINARY ，我们也可以简称为 next-key锁 。Next-Key Locks是在存储引擎 innodb 、事务级别在 可重复读 的情况下使用的数据库锁， innodb默认的锁就是Next-Key locks。比如，我们把id值为8的那条记录加一个next-key锁的示意图如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R9g95rHq-1689213896439)(./assets/image-20220713192549340.png)]

next-key锁的本质就是一个记录锁和一个gap锁的合体，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙。

begin;
select * from student where id <=8 and id > 3 for update;

我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了 gap锁(next-key锁也包含gap锁），如果有的话，插入操作需要等待，直到拥有 gap锁的那个事务提交。但是InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入 新记录，但是现在在等待。InnoDB就把这种类型的锁命名为 Insert Intention Locks，官方的类型名称为：LOCK_INSERT_INTENTION，我们称为插入意向锁。插入意向锁是一种 Gap锁，不是意向锁，在insert操作时产生。插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。该锁用以表示插入意向，当多个事务在同一区间（gap）插入位置不同的多条数据时，事务之间不需要互相等待。假设存在两条值分别为4和7的记录，两个不同的事务分别试图插入值为5和6的两条记录，每个事务在获取插入行上独占的（排他）锁前，都会获取（4，7）之间的间隙锁，但是因为数据行之间并 不冲突，所以两个事务之间并不会产生冲突（阻塞等待）。

总结来说，插入意向锁的特性可以分成两部分：

(1)插入意向锁是一种 特殊的间隙锁一一间隙锁可以锁定开区间内的部分记录。

(2)插入意向锁之间 互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索
引）不冲突，那么事务之间就不会出现冲突等待。

注意，虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是 行锁。

比如，把id值为8的那条记录加一个插入意向锁的示意图如下：

比如，现在T1为id值为8的记录加了一个gap锁，然后T2和T3分别想向student表中插入ia值分别为4、5的两条记录，所以现在为id值为8的记录加的锁的示意图就如下所示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WA0r70Yn-1689213896439)(./assets/image-20230624011500790.png)]

从图中可以看到，由于T1持有gap锁，所以T2和下3需要生成一个插入意向锁的锁结构并且处于等待状态。当T1提交后会把它获取到的锁都释放掉，这样T2和T3就能获取到对应的插入意向锁了（本质上就是把插入意向锁对应锁结构的is_waiting属性改为false），T2和T3之间也并不会相互阳塞，它们可以同时获取到id值为8的插入意向锁，然后执行插入操作。事实上插入意向锁井不会阻止别的事务继续获取该记录上任何类型的锁。

表锁

表锁的作用是锁定整张表。表锁又可分为：表级别的S锁和X锁、意向锁、元数据锁、自增锁。

① 表级别的S锁、X锁

• 表级别的S锁：对表进
• 表级别的X锁：

一般情况下，不会使用InnoDB存储引擎提供的表级别的 S锁 和 X锁 。只会在一些特殊情况下，比方说 崩溃恢复 过程中用到。比如，在系统变量 autocommit=0，innodb_table_locks = 1 时， 手动获取 InnoDB存储引擎提供的表t 的 S锁 或者 X锁 可以这么写：

• LOCK TABLES t READ ：InnoDB存储引擎会对表 t 加表级别的 S锁 。

• LOCK TABLES t WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁 。

• UNLOCK TABLES：当前会话被锁的表全部解锁。

尽量避免在使用InnoDB存储引擎的表上使用 LOCK TABLES 这样的手动锁表语句，它们并不会提供 什么额外的保护，只是会降低并发能力而已。InnoDB的厉害之处还是实现了更细粒度的 行锁 ，关于 InnoDB表级别的 S锁 和 X锁 大家了解一下就可以了。

**举例：**下面我们讲解MyISAM引擎下的表锁。

步骤1：创建表并添加数据

CREATE TABLE mylock(
id INT NOT NULL PRIMARY KEY auto_increment,
NAME VARCHAR(20)
)ENGINE myisam;

# 插入一条数据
INSERT INTO mylock(NAME) VALUES('a');

# 查询表中所有数据
SELECT * FROM mylock;
+----+------+
| id | Name |
+----+------+
| 1  | a    |
+----+------+

步骤2：查看表上加过的锁

SHOW OPEN TABLES; # 主要关注In_use字段的值
或者
SHOW OPEN TABLES where In_use > 0;

或者

上面的结果表明，当前数据库中没有被锁定的表

步骤3：手动增加表锁命令

LOCK TABLES t READ; # 存储引擎会对表t加表级别的共享锁。共享锁也叫读锁或S锁（Share的缩写）
LOCK TABLES t WRITE; # 存储引擎会对表t加表级别的排他锁。排他锁也叫独占锁、写锁或X锁（exclusive的缩写）

比如：

步骤4：释放表锁

UNLOCK TABLES; # 使用此命令解锁当前会话被锁的表

比如：

步骤5：加读锁

我们为mylock表加read锁（读锁阻塞写操作），观察阻塞的情况，流程如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hIkdoW7C-1689213896439)(./assets/image-20220711220553225.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-brRrfFSe-1689213896439)(./assets/image-20220711220616537.png)]

步骤6：加写锁

为mylock表加write锁，观察阻塞的情况，流程如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XAtWy0QO-1689213896439)(./assets/image-20220711220711630.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WhFWXQ1E-1689213896440)(./assets/image-20220711220730112.png)]

总结：

MyISAM在执行查询语句（SELECT）前，会给涉及的所有表加读锁，在执行增删改操作前，会给涉及的表加写锁。InnoDB存储引擎是不会为这个表添加表级别的读锁和写锁的。

MySQL的表级锁有两种模式：（以MyISAM表进行操作的演示）

• 表共享读锁（Table Read Lock）

• 表独占写锁（Table Write Lock）

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KKeT0luE-1689213896440)(./assets/image-20220711220929248.png)]

② 意向锁 （intention lock）

InnoDB 支持 多粒度锁（multiple granularity locking） ，它允许 行级锁 与 表级锁 共存，而意向锁就是其中的一种 表锁 。

1. 意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁和行锁）的锁并存。
2. 意向锁是一种不与行级锁冲突表级锁，这一点非常重要。
3. 表明“某个事务正在某些行持有了锁或该事务准备去持有锁”

提出背景

当事务A想获取某个表的表锁的时候，需要对该表下的每一行记录进行遍历，确保该表下的任何一条记录都没有被其他事务加行级别的排它锁。如果表中的任一一条记录存在行级别的排它锁，则需要等待其他事务释放所有锁后才能进行加锁。其中事务A遍历的成本很大，需要一个更好的方法避免这种问题，所以就提出了意向锁的概念。

当事务B对表中的某个记录加行锁的时候，MySQL存储引擎首先对粒度更粗的表加意向锁，其他事务获取表锁的时候，看表上是否存在意向锁，如果存在则直接等待事务B释放锁，减少了锁查询的消耗。

意向锁分类

意向锁分为两种：

• 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

  -- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
  SELECT column FROM table ... LOCK IN SHARE MODE;
  

• 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

  -- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
  SELECT column FROM table ... FOR UPDATE;
  

意向锁是由存储引擎 自己维护的 ，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前， InooDB 会先获取该数据行 所在数据表的对应意向锁 。

**举例：**创建表teacher,插入6条数据，事务的隔离级别默认为Repeatable-Read，如下所示。

CREATE TABLE `teacher` (
	`id` int NOT NULL,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `teacher` VALUES
('1', 'zhangsan'),
('2', 'lisi'),
('3', 'wangwu'),
('4', 'zhaoliu'),
('5', 'songhongkang'),
('6', 'leifengyang');

mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

假设事务A获取了某一行的排他锁，并未提交，语句如下所示:

BEGIN;

SELECT * FROM teacher WHERE id = 6 FOR UPDATE;

事务B想要获取teacher表的读锁，语句如下：

BEGIN;

LOCK TABLES teacher READ;

因为共享锁与排他锁互斥，所以事务 B在试图对 teacher 表加共享锁的时候，必须保证两个条件。
（1) 当前没有其他事务持有 teacher 表的排他锁
(2）当前没有其他事务持有 teacher 表中任意一行的排他锁。
为了检测是否满足第二个条件，事务B必须在确保 teacher 表不存在任何排他锁的前提下，去检测表中的每一行是否存在排他锁。很明显这是一个效率很差的做法，但是有了意向锁之后，情况就不一样了。
意向锁是怎么解决这个问题的呢？首先，我们需要知道意向锁之间的兼容互斥性，如下所示。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QGGloqaH-1689213896440)(./assets/image-20230622193323359.png)]

事务A获取了某一行的排它锁，但未提交：

BEGIN;

SELECT * FROM teacher WHERE id = 6 FOR UPDATE;

此时teacher表存在两把锁：teacher表上的意向排他锁与id=6的数据行上的排他锁。事务B想要获取teacher表的共享锁。

BEGIN;

LOCK TABLES teacher READ;

此时事务B检测事务A持有teacher表的意向排他锁，就可以得知事务A一定持有该表中某些数据行的排它锁，那么事务B对teacher表的加锁请求就会被排斥（阻塞），而无需去检测表中的每一行数据是否存在排他锁。

意向锁的并发性

意向锁不会与行级的共享 / 排他锁互斥！正因为如此，意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性。（不然我们直接用普通的表锁就行了）

我们扩展一下上面 teacher表的例子来概括一下意向锁的作用（一条数据从被锁定到被释放的过程中，可 能存在多种不同锁，但是这里我们只着重讲解意向锁）。

实例讲解

事务A先获得了某一行的排他锁，并未提交：

BEGIN;

SELECT * FROM teacher WHERE id = 6 FOR UPDATE;

事务A获取了teacher表上的意向排他锁。事务A获取了id为6的数据行上的排他锁。之后事务B想要获取teacher表上的共享锁。

BEGIN;

LOCK TABLES teacher READ;

事务B检测到事务A持有teacher表的意向排他锁。事务B对teacher表的加锁请求被阻塞（排斥）。最后事务C也想获取teacher表中某一行的排他锁。

BEGIN;

SELECT * FROM teacher WHERE id = 5 FOR UPDATE;

事务C申请teacher表的意向排他锁。事务C检测到事务A持有teacher表的意向排他锁。因为意向锁之间并不互斥，所以事务C获取到了teacher表的意向排他锁。因为id为5的数据行上不存在任何排他锁，最终事务C成功获取到了该数据行上的排他锁。

从上面的案例可以得到如下结论：

1. InnoDB 支持 多粒度锁 ，特定场景下，行级锁可以与表级锁共存
2. 意向锁之间互不排斥，但除了 IS 与 S 兼容外， 意向锁会与 共享锁 / 排他锁 互斥
3. IX，IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突
4. 意向锁在保证并发性的前提下，实现了 行锁和表锁共存 且 满足事务隔离性 的要求

③ 自增锁（AUTO-INC锁）

在使用MySQL过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性。举例：

CREATE TABLE `teacher` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

由于这个表的id字段声明了AUTO_INCREMENT，意味着在书写插入语句时不需要为其赋值，SQL语句修改 如下所示。

INSERT INTO `teacher` (name) VALUES ('zhangsan'), ('lisi');

上边的插入语句并没有为id列显式赋值，所以系统会自动为它赋上递增的值，结果如下所示。

mysql> select * from teacher;
+----+----------+
| id | name     |
+----+----------+
| 1  | zhangsan |
| 2  | lisi     |
+----+----------+
2 rows in set (0.00 sec)

现在我们看到的上面插入数据只是一种简单的插入模式，所有插入数据的方式总共分为三类，分别是 “ Simple inserts ”，“ Bulk inserts ”和“ Mixed-mode inserts ”。

1. “Simple inserts” （简单插入）

可以 预先确定要插入的行数 （当语句被初始处理时）的语句。包括没有嵌套子查询的单行和多行INSERT...VALUES()和 REPLACE 语句。比如我们上面举的例子就属于该类插入，已经确定要插入的行 数。

2. “Bulk inserts” （批量插入）

事先不知道要插入的行数 （和所需自动递增值的数量）的语句。比如 INSERT ... SELECT ， REPLACE ... SELECT 和 LOAD DATA 语句，但不包括纯INSERT。 InnoDB在每处理一行，为AUTO_INCREMENT列

3. “Mixed-mode inserts” （混合模式插入）

这些是“Simple inserts”语句但是指定部分新行的自动递增值。例如 INSERT INTO teacher (id,name) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 只是指定了部分id的值。

另一种类型的“混合模式插入”是 INSERT ... ON DUPLICATE KEY UPDATE 。

对于上面数据插入的案例，MysQL中采用了 自增锁 的方式来实现，AUTO-NC锁是当向使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁，在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。也正因为此，其并发性显然并不高，当我们向一个有AUTO_INCREMENT关键字的主键插入值的时候，每条语句都要对这个表锁进行竞争，这样的并发潜力其实是很低下的，所以innodb通过 innodb-autoinc-lock_mode的不同取值来提供不同的锁定机制，来显著提高SQL语句的可伸缩性和性能。

innodb_autoinc_lock_mode有三种取值，分别对应与不同锁定模式：

（1）innodb_autoinc_lock_mode = 0(“传统”锁定模式)

在此锁定模式下，所有类型的insert语句都会获得一个特殊的表级AUTO-INC锁，用于插入具有 AUTO_INCREMENT列的表。这种模式其实就如我们上面的例子，即每当执行insert的时候，都会得到一个 表级锁(AUTO-INC锁)，使得语句中生成的auto_increment为顺序，且在binlog中重放的时候，可以保证 master与slave中数据的auto_increment是相同的。因为是表级锁，当在同一时间多个事务中执行insert的 时候，对于AUTO-INC锁的争夺会 限制并发 能力。

（2）innodb_autoinc_lock_mode = 1(“连续”锁定模式)

在 MySQL 8.0 之前，连续锁定模式是 默认 的。

在这个模式下，“bulk inserts”仍然使用AUTO-INC表级锁，并保持到语句结束。这适用于所有INSERT … SELECT，REPLACE … SELECT和LOAD DATA语句。同一时刻只有一个语句可以持有AUTO-INC锁。

对于“Simple inserts”（要插入的行数事先已知），则通过在 mutex（轻量锁） 的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁， 它只在分配过程的持续时间内保持，而不是直到语句完成。不使用表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。如果另一个事务保持AUTO-INC锁，则“Simple inserts”等待AUTO-INC锁，如同它是一个“bulk inserts”。

（3）innodb_autoinc_lock_mode = 2(“交错”锁定模式)

从 MySQL 8.0 开始，交错锁模式是 默认 设置。

在这种锁定模式下，所有类INSERT语句都不会使用表级AUTO-INC 锁，并且可以同时执行多个语句。这是最快和最
可扩展的锁定模式，但是当使用基于语句的复制或恢复方案时，从二进制日志重播SQL语句时，这是不安全的。

在此锁定模式下，自动递增值 保证 在所有并发执行的所有类型的insert语句中是 唯一 且 单调递增 的。但是，由于多个语句可以同时生成数字（即，跨语句交叉编号），导致插入语句的id值可能不是连续的。

如果执行的语句是“simple inserts"，其中要插入的行数已提前知道，除了"Mixed-mode inserts"之外，为单个语句生成的数字不会有间隙。但是，当执行"bulk inserts"时，在由任何给定语句分配的自动递增值中可能存在间隙。

④ 元数据锁（MDL锁）

MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。MDL 的作用是，保证读写的正确性。比 如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个 表结构做变更 ，增加了一 列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删查改。读写锁之间、写锁之间都是互斥的，用来保证变更表结构操作的安全性，解决了DML和DDL操作之间的一致性问题。不需要显式使用，在访问一个表的时候会被自动加上。

页锁

页锁就是在 页的粒度 上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我 们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

每个层级的锁数量是有限制的，因为锁会占用内存空间， 锁空间的大小是有限的 。当某个层级的锁数量超过了这个层级的阈值时，就会进行 锁升级 。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如 InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

从锁的态度分类

从对待锁的态度来看锁的话，可以将锁分成乐观锁和悲观锁，从名字中也可以看出这两种锁是两种看待 数据并发的思维方式 。需要注意的是，乐观锁和悲观锁并不是锁，而是锁的 设计思想 。

悲观锁（Pessimistic Locking）

悲观锁是一种思想，顾名思义，就是很悲观，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。

悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 阻塞 直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞， 用完后再把资源转让给其它线程）。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。Java中 synchronized 和 ReentrantLock 等独占锁就是悲观锁思想的实现。

案例演示

商品秒杀过程中，库存数量的减少，避免出现 超卖的情况。比如，商品表中有一个字段为quantity表示当前该商品的库存量。假设商品为华为mate40，id为1001, quantity=100个。如果不使用锁的情况下，操作方法如下所示

#第1步：查出商品库存
select quantity from items where id = 1001;
#第2步：如果库存大于0，则根据商品信息生产订单
insert into orders(item_id) values(1001);
#第3步：修改商品的库存，num表示购买数量
update items set quantity = quantity-num where id = 1001;

这样写的话，在并发量小的公司没有大的问题，但是如果在 高并发环境下可能出现以下问题：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7naBD4qN-1689213896440)(./assets/image-20230624011944160.png)]

其中线程B此时己经下单并且减完库存，这个时候线程A依然去执行step3，就造成了超卖。

我们使用悲观锁可以解决这个问题，商品信息从查询出来到修改，中间有一个生成订单的过程，使用悲观锁的原理就是，当我们在查询items信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么整个过程中，因为数据被锁定了，就不会出现有第三者来对其进行修改了。而这样做的前提是需要将要执行的SQL语句放在同一个事务中，否则达不到锁定数据行的目的。

修改如下：

#第1步：查出商品库存
select quantity from items where id = 1001 for update;
#第2步：如果库存大于0，则根据商品信息生产订单
insert into orders (item_id) values(1001);
#第3步：修改商品的库存，num表示购买数量
update items set quantity = quantity-num where id = 1001;

select ... for update 是MysQL中悲观锁。此时在items表中，id为1001的那条数据就被我们锁定了，其他的要执行select quantity from items where id = 1001 for update;语句的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

注意：select L..for update语句执行过程中所有扫描的行都会被锁上，因此在MysQL中用悲观锁必须确定使用了索引，而不是全表扫描，否则将会把整个表锁住。

悲观锁不适用的场景较多，它存在一些不足，因为悲观锁大多数情况下依靠数据库的锁机制来实现，以保证程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是 长事务而言，这样的 开销往往无法承受，这时就需要乐观锁。

乐观锁（Optimistic Locking）

乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用 版本号机制 或者 CAS机制 实现。乐观锁适用于多读的应用类型， 这样可以提高吞吐量。在Java中 java.util.concurrent.atomic 包下的原子变量类就是使用了乐观锁的一种实现方式：CAS实现的。

1. 乐观锁的版本号机制

在表中设计一个 版本字段 version ，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行 UPDATE ... SET version=version+1 WHERE version=version 。此时 如果已经有事务对这条数据进行了更改，修改就不会成功。

这种方式类似我们熟悉的SVN、CVS版本管理系统，当我们修改了代码进行提交时，首先会检查当前版本号与服务器上的版本号是否一致，如果一致就可以直接提交，如果不一致就需要更新服务器上的最新代码，然后再进行提交。

2. 乐观锁的时间戳机制

时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行 比较，如果两者一致则更新成功，否则就是版本冲突。

你能看到乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个戳（版本号或 者时间戳），从而证明当前拿到的数据是否最新。

依然使用上面秒杀的案例，执行流程如下：

#第1步：查出商品库存
select quantity from items where id = 1001;

#第2步：如果库存大于0，则根据商品信息生产订单
insert into orders(item_id) values(1001);

#第3步：修改商品的库存，num表示购买数量
update items set quantity = quantity-num, version = version+1 where id = 1001 and version = ＃{version);

注意，如果数据表是 读写分离的表，当matser表中写入的数据没有及时同步到slave表中时，会造成更新一直失败的问题。此时需要强制读取master表中的数据（即将selec语句放到事务中即可，这时候查询的就是master主库了。）

如果我们对同一条数据进行 频繁的修改 的话，那么就会出现这么一种场景，每次修改都只有一个事务能更新成功，在业务感知上面就有大量的失败操作。我们把代码修改如下：

#第1步：查出商品库存
select quantity from items where id = 1001;

#第2步：如果库存大于0，则根据商品信息生产订单
insert into orders (item_id) values (1001);

#第3步：修改商品的库存，num表示购买数量
update items set quantity = quantity-num where id = 1001 and quantity-num>0;

这样就会使每次修改都能成功，而且不会出现超卖的现象。

3. 两种锁的适用场景

从这两种锁的设计思想中，我们总结一下乐观锁和悲观锁的适用场景：

1. 乐观锁 适合 读操作多 的场景，相对来说写的操作比较少。它的优点在于 程序实现 ， 不存在死锁 问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
2. 悲观锁 适合 写操作多 的场景，因为写的操作具有 排它性 。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止 读-写 和 写-写 的冲突。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-53H118ef-1689213896441)(./assets/image-20230624014528313-7542331.png)]

按加锁的方式划分

按加锁的方式划分：显式锁、隐式锁。

隐式锁

一个事务在执行INSERT操作时，如果即将插入的间隙已经被其他事务加了 gap锁，那么本次 INSERT 操作会阻塞，并且当前事务会在该间隙上加一个 插入意向锁，否则一般情况下 INSERT 操作是不加锁的。那如果一个事务首先插入了一条记录（此时并没有在内存生产与该记录关联的锁结构），然后另一个事务：

• 立即使用 select ... lock in share mode; 语句读取这条记录，也就是要获取这条记录的S锁，或者使用select ... for update语句读取这条记录，也就是要获取这条记录的x锁，怎么办？

  如果允许这种情况的发生，那么可能产生脏读问题。

• 立即修改这条记录，也就是要获取这条记录的 x锁，怎么办？

  如果允许这种情况的发生，那么可能产生脏写问题。

这时候我们前边提过的 事务id 又要起作用了。我们把聚簇索引和二级索引中的记录分开看一下：

• 情景一：对于聚簇索引记录来说，有一个 trx_id 隐藏列，该隐藏列记录着最后改动该记录的 事务 id 。那么如果在当前事务中新插入一条聚簇索引记录后，该记录的 trx_id 隐藏列代表的的就是 当前事务的 事务id ，如果其他事务此时想对该记录添加 S锁 或者 X锁 时，首先会看一下该记录的 trx_id 隐藏列代表的事务是否是当前的活跃事务，如果是的话，那么就帮助当前事务创建一个 X 锁 （也就是为当前事务创建一个锁结构， is_waiting 属性是 false ），然后自己进入等待状态 （也就是为自己也创建一个锁结构， is_waiting 属性是 true ）。
• 情景二：对于二级索引记录来说，本身并没有 trx_id 隐藏列，但是在二级索引页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 属性，该属性代表对该页面做改动的最大的 事务id ，如 果 PAGE_MAX_TRX_ID 属性值小于当前最小的活跃 事务id ，那么说明对该页面做修改的事务都已 经提交了，否则就需要在页面中定位到对应的二级索引记录，然后回表找到它对应的聚簇索引记 录，然后再重复 情景一 的做法。

即：一个事务对新插入的记录可以不显式的加锁（生成一个锁结构），但是由于 事务id的存在，相当于加了一个隐式锁。别的事务在对这条记录加 S锁或者x锁时，由于隐式锁 的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态。隐式锁是一种延迟加锁的机制，从而来减少加锁的数量。隐式锁在实际内存对象中并不含有这个锁信息。只有当产生锁等待时，隐式出转化为显式锁。InnoDB 的 insert 操作，对插入的记录不加锁，但是此时如果另一个线程进行当前读，类似以下的用例，session 2会锁等待 session 1, 那么这是如何实现的呢？

显式锁

通过特定的语句进行加锁的锁称为显式锁

select .... lock in share mode
select .... for update

全局锁与死锁

全局锁

全局锁就是对 整个数据库实例 加锁。当你需要让整个库处于 只读状态 的时候，可以使用这个命令，之后 其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结 构等）和更新类事务的提交语句。全局锁的典型使用 场景 是：做 全库逻辑备份

全局锁的命令：

Flush tables with read lock

死锁

两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁。

如何处理死锁：

• 等待，直到超时（innodb_lock_wait_timeout=50s）；即当两个事务互相等待时，当一个事务等待时间超过设置的阈值时，就将其回滚，另外事务继续进行。这种方法简单有效，在innodb中，参数 innodb_lock_wait_timeout 用来设置超时时间。缺点：对于在线服务来说，这个等待时间往往是无法接受的。那将此值修改短一些，比如1s，o.1s是否合适？不合适，容易误伤到普通的锁等待。

• 使用死锁检测处理死锁程序：方式1检测死锁太过被动，innodb还提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发。

  这是一种较为主动的死锁检测机制，要求数据库保存锁的信息链表和事务等待链表两部分信息。

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SS4JPpHj-1689213896441)(./assets/image-20220713221758941.png)]

  基于这两个信息，可以绘制wait-for graph（等待图）

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1oRLDqWH-1689213896441)(./assets/image-20220713221830455.png)]

  死锁检测的原理是构建一个以事务为顶点，锁为边的有向图，判断有向图是否存在环，存在既有死锁。

  一旦检测到回路、有死锁，这时候InnoDB存储引擎会选择回滚undo量最小的事务，让其他事务继续执行（innodb_deadlock_detect=on表示开启这个逻辑）。

  缺点：每个新的被阻塞的线程，都要判断是不是由于自己的加入导致了死锁，这个操作时间复杂度是O(n)。如果100个并发线程同时更新同一行，意味着要检测100*100=1万次，1万个线程就会有1千万次检测。

  如何解决？

  • 方式1：关闭死锁检测，但意味着可能会出现大量的超时，会导致业务有损。
  • 方式2：控制并发访问的数量。比如在中间件中实现对于相同行的更新，在进入引擎之前排队，这样在InnoDB内部就不会有大量的死锁检测工作。

  进一步的思路：

  可以考虑通过将一行改成逻辑上的多行来减少锁冲突。比如，连锁超市账户总额的记录，可以考虑放到多条记录上。账户总额等于这多个记录的值的总和。

锁的内部结构

我们前边说对一条记录加锁的本质就是在内存中创建一个锁结构与之关联，那么是不是一个事务对多条记录加锁，就要创建多个锁结构呢？比如：

# 事务T1
SELECT * FROM user LOCK IN SHARE MODE;

理论上创建多个锁结构没问题，但是如果一个事务要获取10000条记录的锁，生成10000个锁结构也太崩溃了！所以决定在对不同记录加锁时，如果符合下边这些条件的记录会放在一个锁结构中。

• 在同一个事务中进行加锁操作
• 被加锁的记录在同一个页面中
• 加锁的类型是一样的
• 等待状态是一样的

InnoDB 存储引擎中的 锁结构 如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-32vqEyDw-1689213896441)(./assets/image-20220714132306208.png)]

结构解析：

• 锁所在的事务信息 ：在事务执行过程中会产生表锁或者行锁，则对应的锁结构中锁所在的事务信息保存一个指针，通过指针能够在内存中找到该事务的详细信息。

• 索引信息 ：对于 行锁 来说，需要记录一下被加锁的记录是属于哪个索引的。这里也是一个指针。

• 表锁／行锁信息 ：表锁结构 和 行锁结构 在这个位置的内容是不同的：

  • 表锁在此处存储的是该表锁对应的表信息；

  • 行锁则在此处存储这三个重要信息：

    • Space ID ：记录所在表空间。

    • Page Number ：记录所在页号。

    • n_bits ：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同 的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个n_bis属性代表使用了多少比特位。

    n_bits的值一般都比页面中记录条数多一些。主要是为了之后在页面中插入了新记录后 也不至于重新分配锁结构。

• type_mode ：是一个32位的数，被分成了 lock_mode 、 lock_type 和 rec_lock_type 三个部分，如图所示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3LphhNTb-1689213896441)(./assets/image-20220714133319666.png)]

• 锁的模式（ lock_mode ），占用低4位，可选的值如下：

• LOCK_IS （十进制的 0 ）：表示共享意向锁，也就是 IS锁 。
• LOCK_IX （十进制的 1 ）：表示独占意向锁，也就是 IX锁 。
• LOCK_S （十进制的 2 ）：表示共享锁，也就是 S锁 。
• LOCK_X （十进制的 3 ）：表示独占锁，也就是 X锁 。
• LOCK_AUTO_INC （十进制的 4 ）：表示 AUTO-INC锁 。

在InnoDB存储引擎中，LOCK_IS，LOCK_IX，LOCK_AUTO_INC都算是表级锁的模式，LOCK_S和 LOCK_X既可以算是表级锁的模式，也可以是行级锁的模式。

• 锁的类型（ lock_type ），占用第5～8位，不过现阶段只有第5位和第6位被使用：
  • LOCK_TABLE （十进制的 16 ），也就是当第5个比特位置为1时，表示表级锁。
  • LOCK_REC （十进制的 32 ），也就是当第6个比特位置为1时，表示行级锁。
• 行锁的具体类型（ rec_lock_type ），使用其余的位来表示。只有在 lock_type 的值为 LOCK_REC 时，也就是只有在该锁为行级锁时，才会被细分为更多的类型：
  • LOCK_ORDINARY （十进制的 0 ）：表示 next-key锁 。
  • LOCK_GAP （十进制的 512 ）：也就是当第10个比特位置为1时，表示 gap锁 。
  • LOCK_REC_NOT_GAP （十进制的 1024 ）：也就是当第11个比特位置为1时，表示正经 记录锁 。
  • LOCK_INSERT_INTENTION （十进制的 2048 ）：也就是当第12个比特位置为1时，表示插入意向锁。其他的类型：还有一些不常用的类型我们就不多说了。
• is_waiting 属性呢？基于内存空间的节省，所以把 is_waiting 属性放到了 type_mode 这个32 位的数字中：
  • LOCK_WAIT （十进制的 256 ） ：当第9个比特位置为 1 时，表示 is_waiting 为 true ，也 就是当前事务尚未获取到锁，处在等待状态；当这个比特位为 0 时，表示 is_waiting 为 false ，也就是当前事务获取锁成功。

5. 其他信息 ：

为了更好的管理系统运行过程中生成的各种锁结构而设计了各种哈希表和链表。

6. 一堆比特位 ：

如果是 行锁结构 的话，在该结构末尾还放置了一堆比特位，比特位的数量是由上边提到的 n_bits 属性 表示的。InnoDB数据页中的每条记录在 记录头信息 中都包含一个 heap_no 属性，伪记录 Infimum 的 heap_no 值为 0 ， Supremum 的 heap_no 值为 1 ，之后每插入一条记录， heap_no 值就增1。 锁结 构 最后的一堆比特位就对应着一个页面中的记录，一个比特位映射一个 heap_no ，即一个比特位映射 到页内的一条记录。

锁监控

关于MySQL锁的监控，我们一般可以通过检查 InnoDB_row_lock 等状态变量来分析系统上的行锁的争夺情况

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.01 sec)

对各个状态量的说明如下：

• Innodb_row_lock_current_waits：当前正在等待锁定的数量；
• Innodb_row_lock_time ：从系统启动到现在锁定总时间长度；（等待总时长）
• Innodb_row_lock_time_avg ：每次等待所花平均时间；（等待平均时长）
• Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
• Innodb_row_lock_waits ：系统启动后到现在总共等待的次数；（等待总次数）

对于这5个状态变量，比较重要的3个见上面（灰色）。

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

其他监控方法：

MySQL把事务和锁的信息记录在了 information_schema 库中，涉及到的三张表分别是 INNODB_TRX 、 INNODB_LOCKS 和 INNODB_LOCK_WAITS 。

MySQL5.7及之前 ，可以通过information_schema.INNODB_LOCKS查看事务的锁情况，但只能看到阻塞事 务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况。

MySQL8.0删除了information_schema.INNODB_LOCKS，添加了 performance_schema.data_locks ，可以通过performance_schema.data_locks查看事务的锁情况，和MySQL5.7及之前不同， performance_schema.data_locks不但可以看到阻塞该事务的锁，还可以看到该事务所持有的锁。

同时，information_schema.INNODB_LOCK_WAITS也被 performance_schema.data_lock_waits 所代 替。

我们模拟一个锁等待的场景，以下是从这三张表收集的信息

锁等待场景，我们依然使用记录锁中的案例，当事务2进行等待时，查询情况如下：

（1）查询正在被锁阻塞的sql语句。

SELECT * FROM information_schema.INNODB_TRX\G;

重要属性代表含义已在上述中标注。

（2）查询锁等待情况

SELECT * FROM data_lock_waits\G;
*************************** 1. row ***************************
							ENGINE: INNODB
		REQUESTING_ENGINE_LOCK_ID: 139750145405624:7:4:7:139747028690608
REQUESTING_ENGINE_TRANSACTION_ID: 13845 #被阻塞的事务ID
			REQUESTING_THREAD_ID: 72
			REQUESTING_EVENT_ID: 26
REQUESTING_OBJECT_INSTANCE_BEGIN: 139747028690608
		BLOCKING_ENGINE_LOCK_ID: 139750145406432:7:4:7:139747028813248
BLOCKING_ENGINE_TRANSACTION_ID: 13844 #正在执行的事务ID，阻塞了13845
			BLOCKING_THREAD_ID: 71
			BLOCKING_EVENT_ID: 24
BLOCKING_OBJECT_INSTANCE_BEGIN: 139747028813248
1 row in set (0.00 sec)

（3）查询锁的情况

mysql > SELECT * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139750145405624:1068:139747028693520
ENGINE_TRANSACTION_ID: 13847
THREAD_ID: 72
EVENT_ID: 31
OBJECT_SCHEMA: atguigu
OBJECT_NAME: user
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139747028693520
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139750145405624:7:4:7:139747028690608
ENGINE_TRANSACTION_ID: 13847
THREAD_ID: 72
EVENT_ID: 31
OBJECT_SCHEMA: atguigu
OBJECT_NAME: user
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139747028690608
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 1
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139750145406432:1068:139747028816304
ENGINE_TRANSACTION_ID: 13846
THREAD_ID: 71
EVENT_ID: 28
OBJECT_SCHEMA: atguigu
OBJECT_NAME: user
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139747028816304
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139750145406432:7:4:7:139747028813248
ENGINE_TRANSACTION_ID: 13846
THREAD_ID: 71
EVENT_ID: 28
OBJECT_SCHEMA: atguigu
OBJECT_NAME: user
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139747028813248
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
4 rows in set (0.00 sec)

ERROR:
No query specified

从锁的情况可以看出来，两个事务分别获取了IX锁，我们从意向锁章节可以知道，IX锁互相时兼容的。所 以这里不会等待，但是事务1同样持有X锁，此时事务2也要去同一行记录获取X锁，他们之间不兼容，导 致等待的情况发生。

多版本并发控制（MVCC）

MVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的多个版本管理来实现数据库的 并发控制 。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样 在做查询的时候就不用等待另一个事务释放锁。

MVCC没有正式的标准，在不同的DBMS中MVCC的实现方式可能是不同的，也不是普遍使用的（大家可以参考相关的DBMS文档）。这里讲解InnoDB中MVCC的实现机制（MySQL其他的存储引擎并不支持它）。

快照读与当前读

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理 读-写冲突 ，做到 即使有读写冲突时，也能做到 不加锁 ， 非阻塞并发读 ，而这个读指的就是 快照读 , 而非 当前读 。当前 读实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。

快照读

快照读又叫一致性读，读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞 读；比如这样：

SELECT * FROM player WHERE ...

之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下， 避免了加锁操作，降低了开销。

既然是基于多版本，那么快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

当前读

当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务 不能修改当前记录，会对读取的记录进行加锁。加锁的 SELECT，或者对数据进行增删改都会进行当前读。比如：

SELECT * FROM student LOCK IN SHARE MODE; # 共享锁
SELECT * FROM student FOR UPDATE; # 排他锁
INSERT INTO student values ... # 排他锁
DELETE FROM student WHERE ... # 排他锁
UPDATE student SET ... # 排他锁

索引(未完成)

索引提出背景

索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教科书的目录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。MySQL中也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IrGk32Ah-1689213896441)(./assets/image-20230706213653073.png)]

如上图所示，数据库没有索引的情况下，数据分布在硬盘不同的位置上面，读取数据时，摆臂需要前后摆动查询数据，这样操作非常消耗时间。如果数据顺序摆放，那么也需要从1到6行按顺序读取，这样就相当于进行了6次IO操作，依旧非常耗时。如果我们不借助任何索引结构帮助我们快速定位数据的话，我们查找 Col 2 = 89 这条记录，就要逐行去查找、去比较。从Col 2 = 34 开始，进行比较，发现不是，继续下一行。我们当前的表只有不到10行数据，但如果表很大的话，有上千万条数据，就意味着要做很多很多次硬盘I/0才能找到。现在要查找 Col 2 = 89 这条记录。CPU必须先去磁盘查找这条记录，找到之后加载到内存，再对数据进行处理。这个过程最耗时间就是磁盘I/O（涉及到磁盘的旋转时间（速度较快），磁头的寻道时间(速度慢、费时)）

假如给数据使用 二叉树 这样的数据结构进行存储，如下图所示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vDKfQGEq-1689213896441)(./assets/image-20220616142723266.png)]

对字段 Col 2 添加了索引，就相当于在硬盘上为 Col 2 维护了一个索引的数据结构，即这个 二叉搜索树。二叉搜索树的每个结点存储的是 (K, V) 结构，key 是 Col 2，value 是该 key 所在行的文件指针（地址）。比如：该二叉搜索树的根节点就是：(34, 0x07)。现在对 Col 2 添加了索引，这时再去查找 Col 2 = 89 这条记录的时候会先去查找该二叉搜索树（二叉树的遍历查找）。读 34 到内存，89 > 34; 继续右侧数据，读 89 到内存，89==89；找到数据返回。找到之后就根据当前结点的 value 快速定位到要查找的记录对应的地址。我们可以发现，只需要 查找两次 就可以定位到记录的地址，查询速度就提高了。

索引优缺点

使用索引就是为了 减少磁盘I/O的次数，加快查询速率（索引存放在磁盘中）。但是索引也有缺点：① 创建索引和维护索引会耗费很多时间；② 索引存储在磁盘中，占用磁盘空间；③ 索引会降低更新表的速度。

因此，选择使用索引时，需要综合考虑索引的优点和缺点。

索引的演变过程

索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

• 从功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
• 按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
• 按照 作用字段个数 进行划分，分成单列索引和联合索引。

聚集索引

每个表有且一定会有一个聚集索引，整个表的数据存储在聚集索引中，mysql索引是采用B+树结构保存在文件中，叶子节点存储主键的值以及对应记录的数据，非叶子节点不存储记录的数据，只存储主键的值。当表中未指定主键时，mysql内部会自动给每条记录添加一个隐藏的rowid字段（默认4个字节）作为主键，用rowid构建聚集索引。

聚集索引在mysql中又叫主键索引。

非聚集索引

也是b+树结构，不过有一点和聚集索引不同，非聚集索引叶子节点存储字段（索引字段）的值以及对应记录主键的值，其他节点只存储字段的值（索引字段）。

非聚集索引可以分为普通索引、唯一索引、主键索引、单列索引和多列索引

普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在表student的字段name 上建立一个普通索引，查询记录时就可以根据该索引进行查询。

唯一索引

使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的但允许有空值。在一张数据表里可以有多个唯一索引。例如，在表student的字段email中创建唯一性索引，那么字段email的值就必须是唯一的。通过唯一性索引,可以更快速地确定某条记录。

主键索引

主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUE，一张表里最多只有一个主键索引。

单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。

多列索引(组合索引)

多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段id，name和，gender上建立一个多列索引idx_id_name_gender，只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循最左前缀集合。

全文索引

索引操作

创建索引

CREATE TABLE table_name [col_name data_type] 
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

• UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；

• INDEX与KEY为同义词，两者的作用相同，用来指定创建索引；

• index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；

• col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择

• length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

• ASC或DESC指定升序或者降序的索引值存储。

创建普通索引

在book表中的year_publication字段上建立普通索引，SQL语句如下：

CREATE TABLE book( 
    book_id INT , 
    book_name VARCHAR(100), 
    authors VARCHAR(100), 
    info VARCHAR(100) , 
    comment VARCHAR(100), 
    year_publication YEAR, 
    INDEX(year_publication) 
);

创建唯一索引

CREATE TABLE test1( 
    id INT NOT NULL, 
    name varchar(30) NOT NULL, 
    UNIQUE INDEX uk_idx_id(id) 
);

创建主键索引

CREATE TABLE student ( 
    id INT(10) UNSIGNED AUTO_INCREMENT, 
    student_no VARCHAR(200),
    student_name VARCHAR(200), 
    PRIMARY KEY(id) 
);

创建单列索引

CREATE TABLE test2( 
    id INT NOT NULL, 
    name CHAR(50) NULL, 
    INDEX single_idx_name(name(20)) 
);

创建单列索引

CREATE TABLE test2( 
    id INT NOT NULL, 
    name CHAR(50) NULL, 
    INDEX single_idx_name(name(20)) 
);

创建组合索引

CREATE TABLE test3( 
    id INT(11) NOT NULL, 
    name CHAR(30) NOT NULL, 
    age INT(11) NOT NULL, 
    info VARCHAR(255), 
    INDEX multi_idx(id,name,age) 
);

创建全文索引

CREATE TABLE `papers` ( 
    id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `title` varchar(200) DEFAULT NULL, 
    `content` text, PRIMARY KEY (`id`), 
    FULLTEXT KEY `title` (`title`,`content`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

创建空间索引

CREATE TABLE test5( 
    geo GEOMETRY NOT NULL, 
    SPATIAL INDEX spa_idx_geo(geo) 
) ENGINE=MyISAM;

删除索引

ALTER TABLE table_name DROP INDEX index_name;
或
DROP INDEX index_name ON table_name;

删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

查看索引

show index from 表名;

修改索引

修改索引本质上就是先删除索引，然后创建新索引。

MySQL8.0索引新特性

支持降序索引

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

从MySQL 8.x开始支持隐藏索引（invisible indexes），只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。

如何操作：

方法一：创建索引的时候设置索引的可见性
ALTER TABLE tablename 
ADD INDEX indexname (propname [(length)]) INVISIBLE;

方法二：
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引 
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的

索引的设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑相应准则。

哪些情况适合创建索引

• 字段的数值有唯一性的限制 。索引本身可以起到约束的作用，比如唯一索引、主键索引都可以起到唯一性约束的

• 频繁作为WHERE查询条件的字段、UPDATE、DELETE的WHERE条件列

• 经常GROUP BY和ORDER BY的列

• DISTINCT字段需要创建索引

• 多表JOIN连接操作时，创建索引注意事项：

  • 首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
  • 其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
  • 最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致，因为不同的类型，会进行转换后在进行比较，转换会用到函数，使用函数，索引就会失效。

• 使用列的类型小的创建索引；这里所说的类型大小指的就是该类型表示的数据范围的大小。

• 使用最频繁的列放到联合索引的左侧

哪些情况不适合创建索引

• 数据量小的表最好不要使用索引

• 有大量重复数据的列上不要建立索引

• 避免对经常更新的表创建过多的索引：索引太多，在更新索引的时候也会造成负担，从而影响效率。

• 不建议用无序的值作为索引

• 删除不再使用或者很少使用的索引：

  表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因：

• 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
• 索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
• 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。

数据库设计与优化

数据库设计规范（未完成）

性能分析工具的使用

0. 数据库服务器的优化步骤

当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。

整个流程划分成了 观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使 用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zCpBeDns-1689213896441)(./assets/image-20230624152858558-7591741.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g8aSxloB-1689213896441)(./assets/image-20220627162345815.png)]

我们可以通过观察了解数据库整体的运行状态，通过性能分析工具可以让我们了解执行慢的SQL都有哪些，查看具体的SQL执行计划，甚至是SQL执行中的每一步的成本代价，这样才能定位问题所在，找到了问题，再采取相应的行动。

详细解释一下这张图：

首先在 S1 部分，我们需要观察服务器的状态是否存在周期性的波动。如果 存在周期性波动，有可能是周期性节点的原因，比如双十—、促销活动等。这样的话，我们可以通过 A1 这一步骤解决，也就是加缓存，或者更改缓存失效策略。

如果缓存策略没有解决，或者不是周期性波动的原因，我们就需要进一步 分析查询延迟和卡顿的原因。接下来进入S2这一步，我们需要开启慢查询。慢查询可以帮我们定位执行慢的 SQL语句。我们可以通过设置long_query_time 参数定义“慢”的阈值，如果 SQL执行时间超过了 long_query_time，则会认为是慢查询。当收集上来这些慢查询之后，我们就可以通过分析工具对慢查询日志进行分析。

在S3 这一步骤中，我们就知道了执行慢的 SQL，这样就可以针对性地用 EXPLAIN 查看对应 SQL语句的执行计划，或者使用 show profile 查看 SQL中每一个步骤的时间成本。这样我们就可以了解 SQL 查询慢是因为执行时间长，还是等待时间长。

如果是 SQL等待时间长，我们进入A2 步骤。在这一步骤中，我们可以 调优服务器的参数，比如适当增加数据库缓冲池等。如果是 SQL执行时间长，就进入A3 步骤，这一步中我们需要考虑是索引设计的问题？还是查询关联的数据表过多？还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。

如果 A2 和 A3 都不能解决问题，我们需要考虑数据库自身的 SQL 查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤。如果已经达到了 性能瓶颈，进入 A4 阶段，需要考虑增加服务器，采用 读写分离的架构，或者考虑对数据库进行 分库分表，比如垂直分库、垂直分表和水平分表等。以上就是数据库调优的流程思路。如果我们发现执行 SQL时存在不规则延迟或卡顿的时候，就可以采用分析工具帮我们定位有问题的 SQL，这三种分析工具你可以理解是 SQL调优的三个步骤：慢查询、explain 和 show profile。

1. 查看系统性能参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。

SHOW STATUS语句语法如下：

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下：

• Connections：连接MySQL服务器的次数。
• Uptime：MySQL服务器的上线时间。
• Slow_queries：慢查询的次数。
• Innodb_rows_read：Select查询返回的行数
• Innodb_rows_inserted：执行INSERT操作插入的行数
• Innodb_rows_updated：执行UPDATE操作更新的 行数
• Innodb_rows_deleted：执行DELETE操作删除的行数
• Com_select：查询操作的次数。
• Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
• Com_update：更新操作 的次数。
• Com_delete：删除操作的次数。

若查询MySQL服务器的连接次数，则可以执行如下语句:

SHOW STATUS LIKE 'Connections';

若查询服务器工作时间，则可以执行如下语句:

SHOW STATUS LIKE 'Uptime';

若查询MySQL服务器的慢查询次数，则可以执行如下语句:

SHOW STATUS LIKE 'Slow_queries';

慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。

再比如，如下的指令可以查看相关的指令情况：

SHOW STATUS LIKE 'Innodb_rows_%';

2. 统计SQL的查询成本: last_query_cost

一条SQL查询语句在执行前需要查询执行计划，如果存在多种执行计划的话，MySQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。

如果我们想要查看某条SQL语句的查询成本，可以在执行完这条SQL语句之后，通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SQL 语句所需要读取的读页的数量。

我们依然使用第8章的 student_info 表为例：

CREATE TABLE `student_info` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `student_id` INT NOT NULL ,
    `name` VARCHAR(20) DEFAULT NULL,
    `course_id` INT NOT NULL ,
    `class_id` INT(11) DEFAULT NULL,
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：

SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id = 900001;

运行结果（1 条记录，运行时间为 0.042s ）

然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name   |   Value  |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+

如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？

SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id BETWEEN 900001 AND 900100;

运行结果（100 条记录，运行时间为 0.046s ）：

然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询。

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name   |   Value   |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+

你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间 基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然 页 数量（last_query_cost）增加了不少 ，但是通过缓冲池的机制，并 没有增加多少查询时间 。

**使用场景：**它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

SQL查询时一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：

1. 位置决定效率。如果页就在数据库 缓冲池 中，那么效率是最高的，否则还需要从 内存 或者 磁盘 中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
2. 批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的(差不多10ms)，而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是进程使用的数据就要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

3. 定位执行慢的 SQL：慢查询日志

MysQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long-query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上（不含10秒）的语句，认为是超出了我们的最大忍耐时间值。

它的主要作用是，帮助我们发现那些执行时间特别长的 SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阳塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。

默认情况下，MySQL数据库 没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

开启慢查询日志参数

1. 开启 slow_query_log

在使用前，我们需要先查下慢查询是否已经开启，使用下面这条命令即可：

mysql > show variables like '%slow_query_log';

我们可以看到 slow_query_log=OFF，我们可以把慢查询日志打开，注意设置变量值的时候需要使用 global，否则会报错：

mysql > set global slow_query_log='ON';

然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：

你能看到这时慢查询分析已经开启，同时文件保存在 /var/lib/mysql/atguigu02-slow.log 文件 中。

2. 修改 long_query_time 阈值

接下来我们来看下慢查询的时间阈值设置，使用如下命令：

mysql > show variables like '%long_query_time%';

这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：

#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句
mysql > set global long_query_time = 1;
mysql> show global variables like '%long_query_time%';

mysql> set long_query_time=1;
mysql> show variables like '%long_query_time%';

补充：配置文件中一并设置参数

如下的方式相较于前面的命令行方式，可以看做是永久设置的方式。

修改 my.cnf 文件，[mysqld] 下增加或修改参数 long_query_time、slow_query_log 和 slow_query_log_file 后，然后重启 MySQL 服务器。

[mysqld]
slow_query_log=ON  # 开启慢查询日志开关
slow_query_log_file=/var/lib/mysql/atguigu-low.log  # 慢查询日志的目录和文件名信息
long_query_time=3  # 设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE

如果不指定存储路径，慢查询日志默认存储到MySQL数据库的数据文件夹下。如果不指定文件名，默认文件名为hostname_slow.log。

查看慢查询数目

查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

案例演示

步骤1. 建表

CREATE TABLE `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `stuno` INT NOT NULL ,
    `name` VARCHAR(20) DEFAULT NULL,
    `age` INT(3) DEFAULT NULL,
    `classId` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步骤2：设置参数 log_bin_trust_function_creators

创建函数，假如报错：

This function has none of DETERMINISTIC......

• 命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

步骤3：创建函数

随机产生字符串：（同上一章）

DELIMITER //
CREATE FUNCTION rand_string(n INT)
	RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
    	SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    	SET i = i + 1;
    END WHILE;
    RETURN return_str;
END //
DELIMITER ;

# 测试
SELECT rand_string(10);

产生随机数值：（同上一章）

DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
    RETURN i;
END //
DELIMITER ;

#测试：
SELECT rand_num(10,100);

步骤4：创建存储过程

DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0; #设置手动提交事务
    REPEAT #循环
    SET i = i + 1; #赋值
    INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
    ((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
    UNTIL i = max_num
    END REPEAT;
    COMMIT; #提交事务
END //
DELIMITER ;

步骤5：调用存储过程

#调用刚刚写好的函数, 4000000条记录,从100001号开始

CALL insert_stu1(100001,4000000);

测试及分析

1. 测试

mysql> SELECT * FROM student WHERE stuno = 3455655;
+---------+---------+--------+------+---------+
|   id    |  stuno  |  name  | age  | classId |
+---------+---------+--------+------+---------+
| 3523633 | 3455655 | oQmLUr |  19  |    39   |
+---------+---------+--------+------+---------+
1 row in set (2.09 sec)

mysql> SELECT * FROM student WHERE name = 'oQmLUr';
+---------+---------+--------+------+---------+
|   id    |  stuno  |  name  |  age | classId |
+---------+---------+--------+------+---------+
| 1154002 | 1243200 | OQMlUR | 266  |   28    |
| 1405708 | 1437740 | OQMlUR | 245  |   439   |
| 1748070 | 1680092 | OQMlUR | 240  |   414   |
| 2119892 | 2051914 | oQmLUr | 17   |   32    |
| 2893154 | 2825176 | OQMlUR | 245  |   435   |
| 3523633 | 3455655 | oQmLUr | 19   |   39    |
+---------+---------+--------+------+---------+
6 rows in set (2.39 sec)

从上面的结果可以看出来，查询学生编号为“3455655”的学生信息花费时间为2.09秒。查询学生姓名为 “oQmLUr”的学生信息花费时间为2.39秒。已经达到了秒的数量级，说明目前查询效率是比较低的，下面 的小节我们分析一下原因。

2. 分析

show status like 'slow_queries';

补充说明：

除了上述变量，控制慢查询日志的还有一个系统变量：min_examined_row_limit。这个变量的意思是，查询扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并目查询执行时间超过 long_query_time 的值，那么，这个查询就被记录到慢查询日志中；反之，则不被记录到慢查询日志中。

这个值默认是 0。与long_query_time=10合在一起，表示只要查询的执行时间超过 10秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。你也可以根据需要，通过修改“myini”文件，来修改查询时长，或者通过 SET 指令，用 SQL语句修改min_examined_row_limit的值。

慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow 。

查看mysqldumpslow的帮助信息

mysqldumpslow --help

mysqldumpslow 命令的具体参数如下：

• -a: 不将数字抽象成N，字符串抽象成S
• -s: 是表示按照何种方式排序：
  • c: 访问次数
  • l: 锁定时间
  • r: 返回记录
  • t: 查询时间
  • al:平均锁定时间
  • ar:平均返回记录数
  • at:平均查询时间 （默认方式）
  • ac:平均查询次数
• -t: 即为返回前面多少条的数据；
• -g: 后边搭配一个正则匹配模式，大小写不敏感的；

举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：

mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log

[root@bogon ~]# mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log

Reading mysql slow query log from /var/lib/mysql/atguigu01-slow.log
Count: 1 Time=2.39s (2s) Lock=0.00s (0s) Rows=13.0 (13), root[root]@localhost
SELECT * FROM student WHERE name = 'S'

Count: 1 Time=2.09s (2s) Lock=0.00s (0s) Rows=2.0 (2), root[root]@localhost
SELECT * FROM student WHERE stuno = N

Died at /usr/bin/mysqldumpslow line 162, <> chunk 2.

工作常用参考：

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

关闭慢查询日志

MySQL服务器停止慢查询日志功能有两种方法：

方式1：永久性方式

[mysqld]
slow_query_log=OFF

或者，把slow_query_log一项注释掉 或 删除

[mysqld]
#slow_query_log =OFF

重启MySQL服务，执行如下语句查询慢日志功能。

SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录
SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长

方式2：临时性方式

使用SET语句来设置。

（1）停止MySQL慢查询日志功能，具体SQL语句如下。

SET GLOBAL slow_query_log=off;

（2）重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，具体SQL语句如下。

SHOW VARIABLES LIKE '%slow%';
#以及
SHOW VARIABLES LIKE '%long_query_time%';

删除慢查询日志

使用SHOW语句显示慢查询日志信息，具体SQL语句如下。

SHOW VARIABLES LIKE `slow_query_log%`;

从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下 手动删除慢查询日志文件 即可。

使用命令 mysqladmin flush-logs 来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件。

mysqladmin -uroot -p flush-logs slow

慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份。

4. 查看 SQL 执行成本：SHOW PROFILE

show profile 是 MySQL 提供的可以用来分析当前会话中 SQL 都做了什么、执行的资源消耗工具的情况，可用于 sql 调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

我们可以在会话级别开启这个功能。

mysql > show variables like 'profiling';

通过设置 profiling=‘ON’ 来开启 show profile:

mysql > set profiling = 'ON';

然后执行相关的查询语句。接着看下当前会话都有哪些 profiles，使用下面这条命令：

mysql > show profiles;

你能看到当前会话一共有 2 个查询。如果我们想要查看最近一次查询的开销，可以使用：

mysql > show profile;

mysql> show profile cpu,block io for query 2

**show profile的常用查询参数： **

① ALL：显示所有的开销信息。

② BLOCK IO：显示块IO开销。

③ CONTEXT SWITCHES：上下文切换开销。

④ CPU：显示CPU开销信息。

⑤ IPC：显示发送和接收开销信息。

⑥ MEMORY：显示内存开销信 息。

⑦ PAGE FAULTS：显示页面错误开销信息。

⑧ SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。

⑨ SWAPS：显示交换次数开销信息。

日常开发需注意的结论：

① converting HEAP to MyISAM: 查询结果太大，内存不够，数据往磁盘上搬了。

② Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。

③ Copying to tmp table on disk：把内存中临时表复制到磁盘上，警惕！

④ locked。

如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。

注意：

不过SHOW PROFILE命令将被启用，我们可以从 information_schema 中的 profiling 数据表进行查看。

5. 分析查询语句：EXPLAIN

定位了查询慢的 SQL 之后，我们就可以使用 EXPLAIN 或 DESCRIBE 工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。

MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)。

这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。MysQL为我们提供了 EXPLAIN 语句来帮助我们查看某个查询语句的具体执行计划，大家看懂EXPLAIN语句的各个输出项，可以有针对性的提升我们查询语句的性能。

通过使用explain，我们可以知道：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

• MySQL 5.6.3以前只能 explain select ；MYSQL 5.6.3以后就可以 explain select，update， delete
• 在5.7以前的版本中，想要显示 partitions 需要使用 explain partitions 命令；想要显示 filtered 需要使用 explain extended 命令。在5.7版本后，默认explain直接显示partitions和 filtered中的信息。

explain基本用法

EXPLAIN 或 DESCRIBE语句的语法形式如下：

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样：

mysql> EXPLAIN SELECT 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BB9Cg9Uq-1689213896442)(./assets/image-20230624164426381.png)]

EXPLAIN 语句输出的各个列的作用如下：

数据准备

1. 建表

CREATE TABLE s1 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE s2 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

2. 设置参数 log_bin_trust_function_creators

创建函数，假如报错，需开启如下命令：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

3. 创建函数

DELIMITER //
CREATE FUNCTION rand_string1(n INT)
	RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
        SET i = i + 1;
    END WHILE;
    RETURN return_str;
END //
DELIMITER ;

4. 创建存储过程

创建往s1表中插入数据的存储过程：

DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO s1 VALUES(
        (min_num + i),
        rand_string1(6),
        (min_num + 30 * i + 5),
        rand_string1(6),
        rand_string1(10),
        rand_string1(5),
        rand_string1(10),
        rand_string1(10));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;

创建往s2表中插入数据的存储过程：

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO s2 VALUES(
        (min_num + i),
        rand_string1(6),
        (min_num + 30 * i + 5),
        rand_string1(6),
        rand_string1(10),
        rand_string1(5),
        rand_string1(10),
        rand_string1(10));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;

5. 调用存储过程

s1表数据的添加：加入1万条记录：

CALL insert_s1(10001,10000);

s2表数据的添加：加入1万条记录：

CALL insert_s2(10001,10000);

explain各列作用

为了让大家有比较好的体验，我们调整了下 EXPLAIN 输出列的顺序。

1. table

不论我们的查询语句有多复杂，里边儿包含了多少个表 ，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该 表的表名（有时不是真实的表名字，可能是简称）。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QTZhdC58-1689213896442)(./assets/image-20230624170031400.png)]

这个查询语句只涉及对s1表的单表查询，所以 EXPLAIN 输出中只有一条记录，其中的table列的值为s1，表明这条记录是用来说明对s1表的单表访问方法的。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lP6qDEl2-1689213896442)(./assets/image-20230624170117988.png)]

可以看出这个连接查询的执行计划中有两条记录，这两条记录的table列分别是s1和s2，这两条记录用来分别说明对s1表和s2表的访问方法是什么。

2. id

查询语句中每出现一个 select 关键字，MySQL就会为它分配一个唯一的id值。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dQLPWrmh-1689213896442)(./assets/image-20230624171337198.png)]

从上图中可知，查询语句中只有一个select关键字，所以就分配了一个id值。

对于连接查询来说，一个select关键字后边的from字句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d5jXfUmX-1689213896442)(./assets/image-20230624171132408-7597896.png)]

可以看到，上述连接查询中参与连接的s1和s2表分别对应一条记录，但是这两条记录对应的id都是1。这里需要大家记住的是，在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出现在前边的表表示驱动表，出现在后面的表表示被驱动表。所以从上边的EXPLAIN输出中我们可以看到，查询优化器准备让s1表作为驱动表，让s2表作为被驱动表来执行查询。

对于包含子查询的查询语句来说，就可能涉及多个select关键字，所以在**包含子查询的查询语句的执行计划中，每个select关键字都会对应一个唯一的id值，

explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kaqQhr4J-1689213896442)(./assets/image-20230624172023833.png)]

从输出结果中我们可以看到，s1表在外层查询中，外层查询有一个独立的 select 关键字，所以第一条记录的id
值就是1，s2表在子查询中，子查询有一个独立的select 关键字，所以第二条记录的 id 值就是2。

注意点

①查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划就好了，比如说：

explain select * from s1 where key1 in (select key2 from s2 where common_field = 'a');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cJTKuf9A-1689213896442)(./assets/image-20230624172350621.png)]

虽然我们的查询语句是一个子查询，但是执行计划中s1和s2表对应的记录的id值全部是1，这就表明查询优化器将子查询转换为了连接查询。

② 对于包含UNION子句的查询语句来说，每个SELECT关键字对应一个id值也是没错的，但是分配的id数比select关键字个数多。但是

explain select * from s1 union select * from s2;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rqd4LZBt-1689213896442)(./assets/image-20230624172911773.png)]

对于union去重，MySQL在内部创建了一个临时表；是否会多分配id值，需要看MySQL版本。

跟union对比起来，union all就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含 UNEON ALL 子句的查询的执行计划中，就没有那个id为NULL的记录，如下所示：

explain select * from s1 union all select * from s2;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hjJS2lvF-1689213896442)(./assets/image-20230624174026957.png)]

小结:

• id如果相同，可以认为是一组，从上往下顺序执行
• 在所有组中，id值越大，优先级越高，越先执行
• 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

3. select_type

一条大的查询语句里边可以包含若干个select关键字，每个select关键字代表着一个小的查询语句，而每个select关键字的from子句中都可以包含若干张表（这些表用来做连接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个select关键字中的表来说，它们的id值是相同的。

MySQL为每一个select关键字代表的小查询都定义了一个称之为 select_type 的属性，意思是我们只要知道了某个小查询的 select_type属性，就知道了这个 小查询在整个大查询中扮演了一个什么角色，我们看一下select_type都能取哪些值，请看官方文档：

具体分析如下：

• SIMPLE

  查询语句中不包含UNION、子查询或者连接查询都算作是SIMPLE类型
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Vu8rkYHk-1689213896442)(./assets/image-20230624174833905.png)]

• PRIMARY

对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type的值就是PRIMARY,比方说：

• UNION

对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询意外，其余的小查询的select_type值就是UNION，可以对比上一个例子的效果。

explain select * from s1 union select * from s2;

explain select * from s1 union all select * from s2;

explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3t7YHvpZ-1689213896442)(./assets/image-20230624202336503.png)]

• UNION RESULT：UNION之后的结果

  MySQL 选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT，看上图可知。

• SUBQUERY

  如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY，看上图可知。

  explain select * from s1 where key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
  

• DEPENDENT SUBQUERY：依赖于外部查询的子查询

  explain select * from s1 where key1 in (select key1 from s2 where s1.key2 = s2.key2) or key3 = 'a';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5oTd8AUw-1689213896443)(./assets/image-20230624202559594.png)]

• DEPENDENT UNION：依赖于外部查询的UNION。

  explain select * from s1 where key1 in (select key1 from s2 where key1 = 'a' union select key1 from s1 where key1 = 'b');
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YGeD7hak-1689213896443)(./assets/image-20230624202637617.png)]

• DERIVED：派生表，from 当中的子查询派生出来的新表。

  explain select * from (select key1, count(*) as c from s1 group by key1) as derived_s1 where c > 1;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I7ZNqAaT-1689213896443)(./assets/image-20230624202706019.png)]

• MATERIALIZED：在SQL执行过程中，将子查询的结果集保存到一个临时表中，获取该结果集的的数据直接访问此临时表。

  explain select * from s1 where key1 in (select key1 from s2);
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-89MOQADI-1689213896443)(./assets/image-20230624202728790.png)]

4. partitions (可略)

5. type ☆

执行计划的一条记录就代表着MySQL对某个表的 执行查询时的访问方法 , 又称“访问类型”，其中的 type 列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。

完整的访问方法有： system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range 、index 、ALL 。

我们详细解释一下：

• system

  当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。比方说我们新建一个MyISAM表，并为其插入一条记录：

  mysql> create table t(i int) engine=MyISAM;
  Query OK, 0 rows affected (0.05 sec)
  
  mysql> insert into t values(1);
  Query OK, 1 row affected (0.01 sec)
  

  然后我们看一下查询这个表的执行计划：

  explain select * from t;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E52aFRfm-1689213896443)(./assets/image-20230624203813465.png)]

  可以看到type列的值就是system了dele

  测试，可以把表改成使用InnoDB存储引擎，试试看执行计划的type列是什么。ALL

• const

  当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const, 比如：

  mysql> explain select * from s1 where id = 10005;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lYOyVwnn-1689213896443)(./assets/image-20230624203917007.png)]

• eq_ref

  在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较）。则对该被驱动表的访问方法就是eq_ref，比方说：

  explain select * from s1 inner join s2 on s1.id = s2.id;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fqCMQKwy-1689213896443)(./assets/image-20230624204021749.png)]

  从执行计划的结果中可以看出，MySQL打算将s2作为驱动表，s1作为被驱动表，重点关注s1的访问 方法是 eq_ref ，表明在访问s1表的时候可以 通过主键的等值匹配 来进行访问。

• ref

  当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref，比方说下边这个查询：

  explain select * from s1 where key1 = 'a';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Uhdtiur1-1689213896444)(./assets/image-20230624204134185.png)]

• fulltext：全文索引

• ref_or_null

  当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null，比如说：

  explain select * from s1 where key1 = 'a' or key1 is null;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lerg8dEI-1689213896444)(./assets/image-20230624204248114.png)]

• index_merge

  一般情况下对于某个表的查询只能使用到一个索引，但单表访问方法时在某些场景下可以使用Interseation、union、Sort-Union这三种索引合并的方式来执行查询。我们看一下执行计划中是怎么体现MySQL使用索引合并的方式来对某个表执行查询的：

  explain select * from s1 where key1 = 'a' or key3 = 'a';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mP6oZaRn-1689213896444)(./assets/image-20230624204340408.png)]

  从执行计划的 type 列的值是 index_merge 就可以看出，MySQL 打算使用索引合并的方式来执行 对 s1 表的查询。

• unique_subquery

  类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery，比如下边的这个查询语句：

  explain select * from s1 where key2 in (select id from s2 where s1.key1 = s2.key1) or key3 = 'a';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iapXasdi-1689213896444)(./assets/image-20230624204437704.png)]

• index_subquery

  index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引，比如这样：

  explain select * from s1 where common_field in (select key3 from s2 where s1.key1 = s2.key1) or key3 = 'a';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uKt7jWFI-1689213896444)(./assets/image-20230624204603240.png)]

• range

  explain select * from s1 where key1 in ('a', 'b', 'c');
  或者：
  explain select * from s1 where key1 > 'a' and key1 < 'b';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v3osMmEr-1689213896444)(./assets/image-20230624204828688.png)]

• index

  当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index，比如这样：

  explain select key_part2 from s1 where key_part3 = 'a';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PDG3TqGF-1689213896444)(./assets/image-20230624204909541.png)]

  上述查询中的所有列表中只有key_part2 一个列，而且搜索条件中也只有 key_part3 一个列，这两个列又恰好包含在idx_key_part这个索引中，可是搜索条件key_part3不能直接使用该索引进行ref和range方式的访问，只能扫描整个idx_key_part索引的记录，所以查询计划的type列的值就是index。

  再一次强调，对于使用InnoDB存储引擎的表来说，二级索引的记录只包含索引列和主键列的值，而聚簇索引中包含用户定义的全部列以及一些隐藏列，所以扫描二级索引的代价比直接全表扫描，也就是扫描聚簇索引的代价更低一些。

• ALL

  最熟悉的全表扫描，就不多说了，直接看例子：

  explain select * from s1;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7vlTXL4G-1689213896444)(./assets/image-20230624204954293.png)]

**小结: **

**结果值从最好到最坏依次是： **

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

其中比较重要的几个提取出来（见上图中的粗体）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求）

6. possible_keys和key

在EXPLAIN语句输出的执行计划中，possible_keys列表示在某个查询语句中，对某个列执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。比方说下面这个查询：

explain select * from s1 where key1 > 'z' and key3 = 'a';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EKWKZjcp-1689213896444)(./assets/image-20230624205233398.png)]

上述执行计划的possible_keys列的值是idx_key1, idx_key3，表示该查询可能使用到idx_key1, idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定采用idx_key3。

7. key_len ☆

实际使用到的索引长度 (即：字节数)

帮你检查是否充分的利用了索引，值越大越好，主要针对于联合索引，有一定的参考意义。

explain select * from s1 where id = 10005;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tnqgVTjm-1689213896444)(./assets/image-20230624205500649.png)]

int 占用 4 个字节

explain select * from s1 where key2 = 10126;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DYNj6NaR-1689213896444)(./assets/image-20230624205522971.png)]

key2上有一个唯一性约束，是否为NULL占用一个字节，那么就是5个字节

explain select * from s1 where key1 = 'a';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P1RpFRxC-1689213896445)(./assets/image-20230624205543975.png)]

key1 VARCHAR(100) 一个字符占3个字节，100*3，是否为NULL占用一个字节，varchar的长度信息占两个字节。

explain select * from s1 where  key_part1 = 'a';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wTfoeJ8I-1689213896445)(./assets/image-20230624205649793.png)]

explain select * from s1 where key_part1 = 'a' AND key_part2 = 'b';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FFUqOnM3-1689213896445)(./assets/image-20230624205707251.png)]

联合索引中可以比较，key_len=606的好于key_len=303

**练习： **

key_len的长度计算公式：

varchar(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

8. ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const、 eq-ref、ref、ref.or-null、unique_subquery、index-subquery其中之一时，ref列展示的就是与索引列作等值匹配的结构是什么，比如只是一个常数或者是某个列。大家看下边这个查询：

explain select * from s1 where key1 = 'a';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EyLuLYG8-1689213896445)(./assets/image-20230624210043375.png)]

可以看到ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数，当然有时候更复杂一点:

explain select * from s1  inner join s2 on s1.id = s2.id;
explain select * from s1  inner join s2 on s2.key1 = UPPER(s1.key1);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2f1lxYNM-1689213896445)(./assets/image-20230624210251925.png)]

9. rows ☆

预估的需要读取的记录条数，值越小越好。

explain select * from s1 where key1 > 'z';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ASdAc5bw-1689213896445)(./assets/image-20230624210346048.png)]

10. filtered

某个表经过搜索条件过滤后剩余记录条数的百分比

如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v3m9pHsI-1689213896445)(./assets/image-20220704131323242.png)]

对于单表查询来说，这个filtered的值没有什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数 (即: rows * filtered)

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SkwXy5L8-1689213896445)(./assets/image-20220704131644615.png)]

从执行计划中可以看出来，查询优化器打算把s1作为驱动表，s2当做被驱动表。我们可以看到驱动表s1表的执行计划的rows列为9688，filtered列为10.00，这意味着驱动表s1的扇出值就是9688 x 10.00% = 968.8，这说明还要对被驱动表执行大约968次查询。

11. Extra ☆

顾名思义，Extra列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑选比较重要的额外信息介绍给大家。

• No tables used

  当查询语句没有FROM子句时将会提示该额外信息，比如：

  mysql> EXPLAIN SELECT 1;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h7cclFXk-1689213896445)(./assets/image-20220704132345383.png)]

• Impossible WHERE

  当查询语句的WHERE子句永远为FALSE时将会提示该额外信息

  mysql> EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QBVpKPs2-1689213896445)(./assets/image-20220704132458978.png)]

• Using where

  

  mysql> EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RBfkgSaN-1689213896446)(./assets/image-20220704132655342.png)]

  

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZLpFiB6y-1689213896446)(./assets/image-20220704133130515.png)]

• No matching min/max row

  当查询列表处有MIN或者MAX聚合函数，但是并没有符合WHERE子句中的搜索条件的记录时。

  mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E6B5I7eb-1689213896446)(./assets/image-20220704134324354.png)]

• Using index

  当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用覆盖索引的情况下，在Extra列将会提示该额外信息。比方说下边这个查询中只需要用到idx_key1而不需要回表操作:

  mysql> EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IvIJng87-1689213896446)(./assets/image-20220704134931220.png)]

• Using index condition

  有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询：

  SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
  

  

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WbsB9vEK-1689213896446)(./assets/image-20220704140441702.png)]

• Using join buffer (Block Nested Loop)

  在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法。

  mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9qGZQIsw-1689213896446)(./assets/image-20220704140815955.png)]

• Not exists

  当我们使用左(外)连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示这个信息：

  mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tGh5LWK8-1689213896446)(./assets/image-20220704142059555.png)]

• Using intersect(...) 、 Using union(...) 和 Using sort_union(...)

  如果执行计划的Extra列出现了Using intersect(...)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的...表示需要进行索引合并的索引名称；

  如果出现Using union(...)提示，说明准备使用Union索引合并的方式执行查询;

  如果出现Using sort_union(...)提示，说明准备使用Sort-Union索引合并的方式执行查询。

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qTwv88Ks-1689213896446)(./assets/image-20220704142552890.png)]

• Zero limit

  当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读取任何记录，将会提示该额外信息

  mysql> EXPLAIN SELECT * FROM s1 LIMIT 0;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-p37dwNRV-1689213896446)(./assets/image-20220704142754394.png)]

• Using filesort

  有一些情况下对结果集中的记录进行排序是可以使用到索引的。

  mysql> EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jLvgpfjo-1689213896446)(./assets/image-20220704142901857.png)]

  

  mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qsMkZM1x-1689213896446)(./assets/image-20220704143518857.png)]

  需要注意的是，如果查询中需要使用filesort的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用文件排序的执行方式改为索引进行排序。

• Using temporary

  

  mysql> EXPLAIN SELECT DISTINCT common_field FROM s1;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DsWrIJHQ-1689213896446)(./assets/image-20220704150030005.png)]

  再比如：

  mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tEa3Ms77-1689213896447)(./assets/image-20220704150156416.png)]

  执行计划中出现Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大的成本的，所以我们最好能使用索引来替代掉使用临时表，比方说下边这个包含GROUP BY子句的查询就不需要使用临时表：

  mysql> EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3V2cRSbN-1689213896447)(./assets/image-20220704150308189.png)]

  从 Extra 的 Using index 的提示里我们可以看出，上述查询只需要扫描 idx_key1 索引就可以搞 定了，不再需要临时表了。

• 其他

  其它特殊情况这里省略。

索引优化和查询优化

数据准备

学员表 插 50万 条， 班级表 插 1万 条。

CREATE DATABASE atguigudb2;
USE atguigudb2;

步骤1：建表

CREATE TABLE `class` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `className` VARCHAR(30) DEFAULT NULL,
    `address` VARCHAR(40) DEFAULT NULL,
    `monitor` INT NULL ,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `stuno` INT NOT NULL ,
    `name` VARCHAR(20) DEFAULT NULL,
    `age` INT(3) DEFAULT NULL,
    `classId` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
    #CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步骤2：设置参数

• 命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

步骤3：创建函数

保证每条数据都不同。

#随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
#假如要删除
#drop function rand_string;

随机产生班级编号

#用于随机产生多少到多少的编号
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;
#假如要删除
#drop function rand_num;

步骤4：创建存储过程

#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, name ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
#假如要删除
#drop PROCEDURE insert_stu;

创建往class表中插入数据的存储过程

#执行存储过程，往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
#假如要删除
#drop PROCEDURE insert_class;

步骤5：调用存储过程

class

#执行存储过程，往class表添加1万条数据
CALL insert_class(10000);

stu

#执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);

步骤6：删除某表上的索引

创建存储过程

DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
        DECLARE done INT DEFAULT 0;
        DECLARE ct INT DEFAULT 0;
        DECLARE _index VARCHAR(200) DEFAULT '';
        DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 AND index_name <>'PRIMARY' ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
		DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
#若没有数据返回,程序继续,并将变量done设为2
        OPEN _cur;
        FETCH _cur INTO _index;
        WHILE _index<>'' DO
            SET @str = CONCAT("drop index " , _index , " on " , tablename );
            PREPARE sql_str FROM @str ;
            EXECUTE sql_str;
            DEALLOCATE PREPARE sql_str;
            SET _index='';
            FETCH _cur INTO _index;
        END WHILE;
    CLOSE _cur;
END //
DELIMITER ;

执行存储过程

CALL proc_drop_index("dbname","tablename");

索引失效案例

全值匹配我最爱

系统中经常出现的sql语句如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';

建立索引前执行：（关注执行时间）

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';
Empty set, 1 warning (0.28 sec)

建立索引

CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_classid ON student(age,classId);
CREATE INDEX idx_age_classid_name ON student(age,classId,name);

建立索引后执行：

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';
Empty set, 1 warning (0.01 sec)

可以看到，创建索引/前的查询时间是e.28秒，创建索引1后的查询时间是0.01 秒，索引帮助我们极大的提高了查
询效率。

最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀原则，即SQL语句中的过滤条件要使用联合索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有用这些字段中第一个字段时，多列（或联合）索引不会被使用。

数据库其他优化

日志

备份