【MYSQ精炼系列篇】【MySQL使用】

【MYSQL】【基础知识】【MySQL的基本使用】

本文基于5.7.20版本mysql
个人知识总结，或有疏漏错误，仅供参考。
第一章和第二章可忽略不看
1
2
3

一、MySQL服务器的安装与使用

以下cmd界面都默认管理员打开，如果不是，可能会有问题。右键管理员打开
1

1.1、解压mysql包，并将解压后的包复制到安装目录

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TXizYdXv-1663240394892)(https://gitee.com/simpletime/https://gitee.com/simpletime/img/raw/master/it/sql/use_sql/raw/master/it/sql/use_sql/image-20220428140736579.png)]

1.2、新增并配置ini配置文件

[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
#设置mysql的安装目录
basedir=D:/wsoft/mysql/mysql5720/
#设置mysql数据库的数据的存放目录data
datadir=D:/wsoft/mysql/mysql5720/data/
#允许最大连接数
max_connections=200
#服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#日志备份
server-id = 1
log-bin=D:/wsoft/mysql/mysql5720/mysql_binlog
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

1.3、进入cmd界面

1.4、初始化资源目录

mysqld --initialize
1

1.5、安装mysql服务

mysqld -install
1

1.6、启动服务

net start mysql
1

1.7、修改初始密码

1.7.1、首先登录mysql

输入一下命令行，执行后无需输入密码直接回车即可
mysql -u root -p
1
2

如果遇到报错如何解决（一般是二次安装会导致这种情况）:

1.关闭服务

net stop mysql
1

2.执行命令行

mysqld --skip-grant-tables
1

3.再次打开一个cmd界面

4.执行登录命令行，直接回车无需输入密码（第一个cmd界面不要关闭）

1.7.2.修改密码

use mysql;
update user set authentication_string=password("123456") where user="root";
1
2

1.8.关闭所有cmd界面，重新打开界面，使用新密码登录，如下图即可成功

二、MYSQL语法

测试使用表：

2.1、基础知识

2.1.1、运算符

2.1.1.1、比较运算符

=，>,<,>=,<=，<>(不等于)，!=，is null,is not null
in， not in， 
between and ，not between and，
like(模糊查询)
%叫通配符，主要用于模糊查询，
like '%cs%' 中间含有字符串'cs'
like 'cs%' 开头是字符串'cs'
like '%cs' 结尾是字符串'cs'
1
2
3
4
5
6
7
8

示例

select account from test_cash where account like '%255'
1

结果

2.1.1.2、逻辑运算符

逻辑非：Not
逻辑与：And
逻辑或：Or
逻辑异或：Xor
逻辑等价：Eqv
1
2
3
4
5

2.1.1.3、连接操作符

&，用于连接字符串；-，用于连接字符串
1

2.1.1.4、其他运算符

ALL、ANY和SOME，其中ANY和SOME等价；作用于比较运算符和子查询之间，作用类似EXISTS、NOT EXISTS、IN、NOT IN 以及其他逻辑意义。
ALL：是所有，表示全部都满足才返回true
ANY/SOME：是任意一个 ，表示有任何一个满足就返回true
"=ANY"与"IN"相同
"<>ALL"与"NOT IN"相同
1
2
3
4
5

示例

select account
from test_cash 
where account = ANY(select account from test_profit)
等价于
select a.account 
from test_cash v 
inner join test_profit a on a.account = v.account
等价于
select account 
from test_cash  
where account in(select account from test_profit)
1
2
3
4
5
6
7
8
9
10
11

2.1.2、字段类型

int 整型  int(10) 表示显示10位，超过正常显示，小于用0补齐
varchar 字符串类型
char 字符串型
datetime 时间类型 YYYY-MM-DD HH:MM:SS
date YYYY-MM-DD
time HH:MM:SS
timestamp: YYYY-MM-DD HH:MM:SS,可设置为数值型，在数值后+0，YYYYMMDDHHMMSS
decimal 小数类型   decimal(数值总长度，小数位数) 数值总长度包括小数点
1
2
3
4
5
6
7
8

注：

char与varchar的区别：
char(10)便是存进去的字符串最多占10个字节
varchar(10)则是可变的，如果不满10，则按对应大小字节存储，但是这样就没有char快
varchar不会去掉末尾的空格，char会
1
2
3
4

2.1.3、注释

单行注释
--
#
全局注释
/**
*/
1
2
3
4
5
6

2.2、查询

2.2.1、基本查询

select 字段 [as 别名]
from 数据库.表名 [as 别名]
[where 条件]
[group by 字段]
[having 条件]  //可写聚合函数，分组后的条件筛选
[order by 字段 desc倒序/asc顺序]
[limit 数量]/[limit 索引,数量]
1
2
3
4
5
6
7

2.2.2、连接查询

MYSQL连接查询分两种连接（还有一种特殊的连接交叉连接），一种是内连接，一种是外连接，每种连接又分等值连接、非等值连接和自连接。由于版本迭代，MySQL可分为92年语法和99年语法，92语法中的from后面多表相互连接是用逗号，后用查询条件是用where（本质上是先产生笛卡尔积结果表，在根据where条件过滤）；99语法中的from后面多表相互连接的是用join,后面查询条件前用 on。后者与sqlsrv的语法几乎一致，且官方也建议后一种。(92年语法示例有时间再写)
1

2.2.2.1、第一部分

内连接只有一种；
inner join 可简写为 join
外连接分两种，一个是左外连接，一个是右外连接，简称左连接和右连接；
left outer join 可简写为 left join
right outer join 可简写为 right join
1
2
3
4
5

2.2.2.1.1、内连接

代码模板：

select 
from tableA A
inner join TableB B on B.Key = A.Key
1
2
3

结果示意图：

测试结果：

只有当连接的表都有数据时，才会显示，可以认为是取交集
SELECT * FROM test_cash a inner join  test_profit b on a.account =b.account
相当于
SELECT * FROM test_cash a cross join  test_profit b where a.account=b.account
1
2
3
4

2.2.2.1.2、外连接

代码模板：

left outer join:
select 
from tableA A
left join TableB B on B.Key = A.Key
right outer join:
select 
from tableA A
right join TableB B on B.Key = A.Key
1
2
3
4
5
6
7
8

测试结果：

TableA为基表，TableB为参考表。左连接查询时，可以查询出TableA中的所有记录和TableB中匹配连接条件的记录。如果TableA的某行在TableB中没有匹配行，那么在返回结果中，TableB的字段值均为空值（NULL）
1

2.2.2.1.3、交叉连接

代码模板：

select 
from tableA A
cross join TableB B
1
2
3

结果示意图:

笛卡尔积
1

测试结果:

交叉连接，其实就是笛卡尔积现象的结果
SELECT * FROM test_cash  cross join  test_profit 
同SELECT * FROM test_cash a,test_profit b（92年语法）
1
2
3

2.2.2.2、第二部分

以下三种连接其实是进一步细分，本质上只是条件的变化或者联表的特殊性而已；等值连接直译就是条件相等，非等值连接就是条件不相等，自连接就是连接自己。
1

2.2.2.2.1、等值连接

等值连接在上述内外连接时已经写过，即B.Key = A.Key，所以就不多赘述了
1

2.2.2.2.2、非等值连接

代码模板：

以左连接为例：
select 
from tableA A
left join TableB B on < 不等条件 >
1
2
3
4

测试结果：

SELECT * FROM test_cash a left join  test_list b on a.balance BETWEEN b.min_score and b.max_score 
1

2.2.2.2.3、自连接

代码模板：

以左连接为例：
select 
from tableA A
left join TableA B on A.Key1=B.Key2
1
2
3
4

使用场景示例

比如员工和管理主管账号存在同一张表里，此时需要知道员工的上级主管，这时，就需要用到自连接。
1

2.2.2.3、拓展

外连接的特殊使用：

以左连接示例，
如果你需要A表有的数据，但又不需要B表相关的数据，则可以使用 where B.key is null;
1
2

结果示意图：

测试结果：

SELECT * FROM test_cash a 
left join  test_profit b on a.account =b.account 
1
2

SELECT * FROM test_cash a 
left join  test_profit b on a.account =b.account 
where b.account is null
1
2
3

2.2.3、子查询

出现在其他语句中的select语句，称为子查询或者内查询；外部的查询语句，称为主查询或外查询。
子查询分为列子查询、行子查询、表子查询以及标量子查询；而这些子查询又分别可以在不同的地方使用，select后面、from后面、join后面、where和having后面 exists后面。
1
2

2.2.3.1、第一部分

子查询分为列子查询、行子查询、表子查询以及标量子查询。列子查询返回的数据一般是某列数据的集合；行子查询返回的数据一般是一条数据，即结果集为一行多列；列子查询返回的数据一般是某个字段的集合，即结果集为一列多行；最后，表子查询返回的数据一般是临时表，即结果集为多列多行，标量子查询返回的数据一般是只有一个数值或者一个字符串，即结果集为一行一列。
1

注：底下示例主要展示这些子查询的结果样式，使得你们有个直观的印象，都用于嵌套到其他select查询里，单独使用不能称为子查询

2.2.3.1.1、列子查询

代码：

select DISTINCT account from  test_cash_transfer_record
1

示例代码：

select account,balance from test_cash  
where account in (select DISTINCT account from  test_cash_transfer_record)
1
2

2.2.3.1.2、行子查询

代码：

select id,account from test_cash_transfer_record where id=1
1

示例代码：

select *  
from test_cash_record 
where ROW(id,account)= (select id,account from test_cash_transfer_record where id=1 )
1
2
3

结果：

2.2.3.1.3、表子查询

代码：

select account,balance from test_cash
1

代码示例：

select account  
from(select account,balance from test_cash )a 
where a.balance>100
1
2
3

结果：

2.2.3.1.4、标量子查询

代码：

select account from test_cash_transfer_record where id=1
1

示例代码：

select account,balance from test_cash  
where account = (select account from test_cash_transfer_record where id=1
1
2

结果：

2.2.3.2、第二部分

子查询分别可以在不同的地方使用，select后面、from后面、join后面、where和having后面 exists后面。
1

2.2.3.2.1、select后面子查询

代码

select account,(select balance from test_cash_profit a where a.account =b.account) 
from test_cash b
1
2

结果

2.2.3.2.2、from后面子查询

2.2.3.2.3、join后面子查询

2.2.3.2.4、where后面子查询

2.2.3.2.5、having后面子查询

2.2.3.2.6、exists后面子查询

2.2.3.2.7、关联子查询（****不太会，以后再写）

SELECT userid,cid,score  from test_course v 
1

示例代码

每门课程前两名的用户信息
SELECT userid,cid,score  
from test_course v  
where(select count(*) from test_course a where a.cid =v.cid and a.score>v.score ) in (1,2) 
order by cid desc
1
2
3
4
5

2.2.4、联合查询

作用：
合并两个或多个 SELECT 语句的结果集
条件：
内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
如果允许重复的值，使用 UNION ALL
1
2
3
4
5

union/union all

SELECT expression1, expression2, ... expression_n 
FROM tables [WHERE conditions] 
UNION [ALL | DISTINCT] 
SELECT expression1, expression2, ... expression_n 
FROM tables 
[WHERE conditions];

解释：
expression1, expression2, ... expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选， 检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。
1
2
3
4
5
6
7
8
9
10
11
12
13

2.3、SQL 常用语句

2.3.1、如果有主键或者唯一键冲突则不插入

insert ignore into
1

2.3.2、如果有主键或者唯一键冲突则更新(注意这个会影响自增的增量)

INSERT INTO  room_remarks(room_id,room_remarks)VALUE(1,'sdf') ON DUPLICATE KEY UPDATE room_remarks = '234';
1

2.3.3、从查询语句中导入

INSERT INTO user_v2 SELECT * FROM user;
INSERT INTO user_v2(id,num) SELECT id,num FROM user;
1
2

2.3.4、如果有就用新的替代，values 如果不包含自增列，自增列的值会变化

REPLACE INTO room_remarks(room_id,room_remarks) VALUE(1,'sdf');
1

2.3.5、连表更新

UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id
1

2.3.6、连表删除

DELETE user FROM user,black WHERE user.id=black.id
1

2.3.7、备份表

CREATE TABLE user_info SELECT * FROM user_info;
1

2.3.8、复制表结构

CREATE TABLE user_v2 LIKE user
1

2.4、常用函数

注：|:或
column:字段（查询对应表里的字段）
str:字符串，常量
len:长度
pos:位置
1
2
3
4
5

2.4.1、日期函数

2.4.1.1、字符串转时间（格式化）

str_to_date(str, format)
select str_to_date(’08/09/2008′, ‘%m/%d/%Y’); — 2008-08-09
select str_to_date(’08/09/08′ , ‘%m/%d/%y’); — 2008-08-09
select str_to_date(‘08.09.2008′, ‘%m.%d.%Y’); — 2008-08-09
select str_to_date(’08:09:30′, ‘%h:%i:%s’); — 08:09:30
select str_to_date(‘08.09.2008 08:09:30′, ‘%m.%d.%Y %h:%i:%s’); — 2008-08-09 08:09:30
1
2
3
4
5
6

2.4.1.2、时间转字符串

2.4.1.2.1、date_format(date,format)

mysql> select date_format(‘2008-08-08 22:23:01′, ‘%Y%m%d%H%i%s’);
+—————————————————-+
| date_format(‘2008-08-08 22:23:01′, ‘%Y%m%d%H%i%s’) |
+—————————————————-+
| 20080808222301 |
+—————————————————-+
1
2
3
4
5
6

2.4.1.2.1、time_format(time,format)

mysql> select time_format(’22:23:01′, ‘%H.%i.%s’);
+————————————-+
| time_format(’22:23:01′, ‘%H.%i.%s’) |
+————————————-+
| 22.23.01 |
+————————————-+
1
2
3
4
5
6

2.4.1.3、获取日期

2.4.1.3.1、年

year(time) 返回日期年份 1000-9999
获取指定曰期是一年中的第几天，返回值范围是1~366
dayofyear(time)
获取季度
quarter(time) 季度 1-4
1
2
3
4
5

2.4.1.3.2、月

获取指定日期中的月份
month(time) 返回日期月份 1-12
获取指定日期是一个月中是第几天，返回值范围是1~31
dayofmonth(time) 参数日期无实际意义时，返回null,比如2月30日，同day(time)
获取指定日期中的月份英文名称
monthname(time) 返回日期月份名  December
1
2
3
4
5
6

2.4.1.3.3、日

获取指定日期时间的日期部分
date(time)
获取指定日期时间的时间部分
time(time)
1
2
3
4

2.4.1.3.4、周

获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
week(time)
获取指定日期对应的一周的索引位置值
dayofweek(time) 1-7 1:星期日 7;星期日
weekday(time) 0-6 0:星期一 6：星期日  符合我的习惯
获取指定曰期对应的星期几的英文名称
dayname(time) 工作日名称 周 星期几
1
2
3
4
5
6
7

2.4.1.3.4、时分秒

hour(time) 返回小时 24小时制
minute(time) 返回分钟
second(time) 返回秒数
将时间参数转换为秒数
time_to_sec(time)
将秒数转换为时间，与TIME_TO_SEC 互为反函数
sec_to_time(timestamp)
1
2
3
4
5
6
7

2.4.1.4、获取当前时间

获取当前时间 YYYYMMDD
YYYY-MM-DD :curdate() cur_date()
HH:MM:SS :curtime() cur_time()
YYYY-MM-DD HH:MM:SS : now() sysdate()

unix_timestamp()时间戳
from_unixtime(timestamp) 时间戳转时间
1
2
3
4
5
6
7

2.4.1.5、时间加减

2.4.1.5.1、按天加

date_add()：

set @dt = now();
select date_add(@dt, interval 1 day); — add 1 day
select date_add(@dt, interval 1 hour); — add 1 hour
select date_add(@dt, interval 1 minute); — …
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);
select date_add(@dt, interval -1 day); — sub 1 day

示例：
select date_add(@dt, interval ’01:15:30′ hour_second);
+————————————————+
| date_add(@dt, interval ’01:15:30′ hour_second) |
+————————————————+
| 2008-08-09 13:28:03 |
+————————————————+

mysql> select date_add(@dt, interval ‘1 01:15:30′ day_second);
+————————————————-+
| date_add(@dt, interval ‘1 01:15:30′ day_second) |
+————————————————-+
| 2008-08-10 13:28:03 |
+————————————————-+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

2.4.1.5.2、按天减

同date_add()
1

2.4.1.5.3、按月加

period_add(P,N)：
select period_add(200808,2), period_add(20080808,-2)
+———————-+————————-+
| period_add(200808,2) | period_add(20080808,-2) |
+———————-+————————-+
| 200810 | 20080806 |
+———————-+————————-+
1
2
3
4
5
6
7

2.4.1.5.4、按月减

period_diff(P1,P2)：日期 P1-P2，返回 N 个月。
mysql> select period_diff(200808, 200801);
+—————————–+
| period_diff(200808, 200801) |
+—————————–+
| 7 |
+—————————–+
1
2
3
4
5
6
7

2.4.1.5.5、日期相减

datediff(date1,date2)：两个日期相减 date1 – date2，返回天数。
select datediff(‘2008-08-08′, ‘2008-08-01′); — 7
select datediff(‘2008-08-01′, ‘2008-08-08′); — -7
1
2
3

2.4.1.5.6、时间相减

timediff(time1,time2)：两个日期相减 time1 – time2，返回 time 差值。
select timediff(‘2008-08-08 08:08:08′, ‘2008-08-08 00:00:00′); — 08:08:08
select timediff(’08:08:08′, ’00:00:00′); — 08:08:08
1
2
3

2.4.2、字符串函数

2.4.2.1、格式化

format(X,D,[locale]) 
-- X：数字
-- D：指定小数位数
-- locale：指定国家语言(默认的locale为en_US)
-- 格式化数字，也就是保留几位小数
1
2
3
4
5

2.4.2.2、替换

insert(column|str，pos，len，str)
-- param1: 被操作字段，也可以是常量
-- pos:开始位置
-- len:替换长度
-- param4:替换内容
-- insert 有点像特异性替换，目标更加清晰

replace (column|str，str，str)
-- param1: 被操作字段，也可以是常量
-- param2:需要替换的字符串
-- param3:替换结果字符串
-- replace有点像批量替换，针对某种字段全部替换
1
2
3
4
5
6
7
8
9
10
11
12

2.4.2.3、截取

substring(column|str，pos，[len]) 
-- param1：查询的字段，也可以是常量
-- pos：开始位置，从1开始，而不像语言索引是从0开始的；可为负数，倒推即可；包含当前位置
-- len：截取的长度，可省略, 省略后默认截取开始位置截取到最后一位

left（column|str，len）
-- param1：查询的字段，也可以是常量
-- len：截取的长度
-- 从左边第一位开始截取

right（column|str，len）
-- param1：查询的字段，也可以是常量
-- len：截取的长度
-- 从右边第一位开始截取
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.4.2.4、连接

concat(column|str，column|str)
-- 字符串拼接，就不多解释了

小知识：
group_concat() 用于分组，将字段用逗号分隔拼接成字符串
1
2
3
4
5

2.4.2.5、大小写转换

lower(column|str)
upper(column|str)
1
2

三、数据库之数据结构

这一章节主要是为了让你们了解到在innodb中，聚集索引和非聚集索引是如何查找到数据的，方便后面学习锁机制

顶端的节点我们称为根节点，没有子节点的节点我们称之为叶子节点。
演化过程：二叉树 --> 二叉查找树 --> 平衡二叉树 --> B树 --> B+树
1
2

3.1、二叉查找树（二叉排序树、BST）

3.1.1、什么是二叉查找树

如图所示：

3.1.2、二叉查找树的特征

二叉树的每个节点最多只能有2个子节点
左子树的键值总是小于根的键值，右子树的键值总是大于根的键值
右子节点的键值都大于当前节点的键值

3.1.3、二叉查找树的使用

如图，我们为user表（用户信息表）建立了一个二叉查找树的索引。图中的圆为二叉查找树的节点，节点中存储了键(key)和数据(data)。键对应user表中的id，数据对应user表中的行数据。

如果我们需要查找id=12的用户信息，利用我们创建的二叉查找树索引，查找流程如下：

将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点>的右子节点作为当前节点
继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点
把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即id=1>2,name=xm

利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要6次才能找到。

3.1.4、缺点

如图，在极端情况下，二叉查找树还可以如下图所示：

这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找id=17的用户信息，我们需要查找7次，也就相当于全表扫描了。导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。

3.2、平衡二叉树（AVL树）

3.2.1、什么是平衡二叉树

为了使二叉查找树一直保持平衡，平衡二叉树应运而生。如下图所示：

3.2.2、平衡二叉树的特征

每个节点的左右子树的高度不能超过1
满足二叉查找树的特点

3.2.3、平衡二叉树的使用

如图所示，平衡二叉树和非平衡二叉树的对比：

由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

3.2.4、缺点

因为内存的易失性。一般情况下，我们都会选择将user表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。
另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。
如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块；我们都知道平衡二叉树可是每个节点只存储一个键值和数据的；那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？可以想象到二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！

3.3、B-Tree（B树、平衡多路查找树）

3.3.1、什么是B树

为了解决平衡二叉树单个节点（磁盘块）无法存储多个键值和数据，B树由此而来，B树（Balance Tree）即为平衡树的意思，下图即是一颗B树：

注意：
– 图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。
– 图中的每个节点称为页，页就是我们上面说的磁盘块，在mysql中数据读取的基本单位都是页，所以我们这里叫做页更符合mysql中索引的底层数据结构。

3.3.2、B树的特征【以后需要完善】

每个节点存储了更多的键值(key)和数据(data)
任何一个关键字出现且只出现在一个结点中
搜索有可能在非叶子结点结束
其搜索性能等价于在关键字全集内做一次二分查找

3.3.3、B树的使用

从下图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点。
子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下： 1. 先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。 2. 将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。 3. 将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。

3.3.4、缺点

B-Tree结构图中每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

3.4、B+Tree(B+树)

3.4.1、什么是B+树

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：
（1）对于主键的范围查找和分页查找，
（2）从根节点开始，进行随机查找。

根据下图我们来看下B+树和B树有什么不同：

B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。
因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。

注：

有心的读者可能还发现上图B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引。
通过上图可以看到，在innodb中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

3.4.2、B+树的特征

只有叶子结点存储数据，索引每一次查找，都必须一次一次，一直找到树的最大深度处，也就是叶子结点的深度，才能找到value
所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息（这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度）
B+Tree的高度一般都在2_{4层（MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1}3次磁盘I/O操作）

3.4.3、B+树的使用

还是这张B+树索引图，现在我们应该知道这就是聚集索引，表中的数据存储在其中。
现在假设我们要查找id>=18并且id<40的用户数据。对应的sql语句为select * from user where id>=18 and id <40，其中id为主键。
具体的查找过程如下：

一般根节点都是常驻内存的，也就是说页1已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。从内存中读取到页1，要查找这个id>=18 and id <40或者范围值，我们首先需要找到id=18的键值。从页1中我们可以找到键值18，此时我们需要根据指针p2，定位到页3。
要从页3中查找数据，我们就需要拿着p2指针去磁盘中进行读取页3。从磁盘中读取页3后将页3放入内存中，然后进行查找，我们可以找到键值18，然后再拿到页3中的指针p1，定位到页8。
同样的页8页不在内存中，我们需要再去磁盘中将页8读取到内存中。将页8读取到内存中后。因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值18。此时因为已经到数据页了，此时我们已经找到一条满足条件的数据了，就是键值18对应的数据。因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页8中的键值依次进行遍历查找并匹配满足条件的数据。我们可以一直找到键值为22的数据，然后页8中就没有数据了，此时我们需要拿着页8中的p指针去读取页9中的数据。
因为页9不在内存中，就又会加载页9到内存中，并通过和页8中一样的方式进行数据的查找，直到将页12加载到内存中，发现41大于40，此时不满足条件。那么查找到此终止。最终我们找到满足条件的所有数据为：(18,kl),(19,kl),(22,hj),(24,io),(25,vg),(29,jk),(31,jk),(33,rt),(34,ty),(35,yu),(37,rt),(39,rt)。总共12条记录。

而innodb的聚集索引一般情况来说就是主键索引（特殊情况：没有主键，只有索引；没有主键也没有索引，mysql自动生成的隐式索引），但是除了主键索引，我们还有其他类型的索引。

下面就说一下innodb的非聚集索引查找数据：

读者看到这张图的时候可能会蒙，这是啥东西啊？怎么都是数字。如果有这种感觉，请仔细看下图中红字的解释。什么？还看不懂？那我再来解释下吧。首先，这个非聚集索引表示的是用户幸运数字的索引（为什么是幸运数字？一时兴起想起来的:-)），此时表结构是这样的。

id	name	luckyNum
1	zs	23
2	ls	7

在叶子节点中，不在存储所有的数据了，存储的是键值和主键。对于叶子节点中的x-y，比如1-1。左边的1表示的是索引的键值，右边的1表示的是主键值。如果我们要找到幸运数字为33的用户信息，对应的sql语句为select * from user where luckNum=33。查找的流程跟聚集索引一样，这里就不详细介绍了。我们最终会找到主键值47，找到主键后我们需要再到聚集索引中查找具体对应的数据信息，此时又回到了聚集索引的查找流程。
下面看下具体的查找流程图：

3.4.4、缺点

略

3.5、红黑树（补充了解即可）

3.5.1、什么是红黑树

本质还是一个二叉树，但是他叫做二叉平衡树。解决二叉树一边倒的可能性。平衡树在插入和删除的时候，会通过旋转操作将树的左右节点达到平衡。

如下图：

3.5.2、红黑树的使用

略

3.5.3、红黑树的特征

任何一个节点都有颜色，红色或黑色
根节点是黑色的
父子节点之间不能出现两个连续的红节点
任何一个根节点，遍历到他的子孙节点，所经过的黑色节点数必须相同
空节点被认为是黑色的

顺口溜：

一头一脚黑，
 黑连红不连。
 插入看叔伯，
 删除看兄弟。
1
2
3
4

3.5.4、红黑树的缺点

在实际场景应用当中，MySQL表数据，一般情况下都是比较庞大、海量的。如果使用红黑树，树的高度会特别高，红黑树虽说查询效率很高。但是在海量数据的情况下，树的高度并不可控。如果我们要查询的数据，正好在树的叶子节点。那查询会非常慢。故而MySQL并没有采用红黑树来组织索引。

3.6、Hash表（补充了解即可）

3.6.1、什么是Hash表

略

3.6.2、Hash表的使用

略

3.6.3、Hash表的特征

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据

3.6.4、hash索引的缺点

hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.AAAA和AAAAB的索引没有相关性
hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询
hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低

四、MySQL数据索引类别

4.1、物理存储角度

聚集索引：按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据。聚集索引的叶子节点称为数据页，聚集索引的这个特性决定了主索引文件和数据文件为同一份文件，数据的排列顺序和索引的排列顺序一致。
非聚集索引（又叫辅助索引，二级索引）：在聚集索引之上创建的索引称之为辅助索引。辅助索引只存储主键的值,如果使用辅助索引搜索数据就必须先从辅助索引取到主键的值,再使用主键的值去主键索引上查询，直到找到叶子节点上的数据返回，这个操作也称之为回表。辅助索引中数据的排列顺序和索引的排列顺序不一定一致。

有一种特殊的辅助索引叫做**覆盖索引**，覆盖索引是非聚集组合索引的一种形式，可以**避免回表操作**。覆盖索引包括在查询里的select、join和where子句用到的所有列（即建立索引的字段正好覆盖了查询语句[select子句]与查询条件[Where子句]中所涉及的字段）。
不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B+Tree索引做覆盖索引。
1
2

4.1.1、聚集索引和非聚集索引的区别

聚集索引一个表只能有一个（一般使用的是主键等不经常更新的列），而非聚集索引一个表可以存在多个
聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
索引是通过树的数据结构来描述的。聚集索引的叶节点就是数据节点，而非聚集索引的叶节点是数据存放的地址，有一个指针指向对应的数据块
聚集索引的查询效率比非聚集索引高，因为只要找到第一个索引值记录，其余连续性的记录在物理上一样连续存放
聚集索引的修改效率比非聚集索引低，因为为了使数据和索引的排列顺序一致，在插入记录的时候,会对数据页重新排序

4.1.2、唯一索引和主键的区别

主键既是约束,也是一种特殊的唯一索引,主键字段不能为NULL
唯一性索引列允许空值，而主键列不允许为空值
主键列在创建时，已经默认为空值且是唯一索引了
主键可以被其他表引用为外键，而唯一索引不能
一个表最多只能创建一个主键，但可以创建多个唯一索引

4.2、逻辑角度

主键索引：它是一种特殊的唯一索引，不允许有空值，一般是在建表的时候同时创建主键索引。
唯一索引：它与前面的普通索引类似，不同的是索引列的值必须唯一，但允许有空值。
单值索引：普通索引的一种，最基本的索引，它没有任何的限制，仅加速查询。
联合索引（又叫做多列索引，复合索引）：普通索引的一种，多列值组成一个索引，专门用于复合查询，其效率大于索引合并（索引合并是使用多个单列索引组合搜索）。
全文索引：只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。fulltext。优先级最高，先执行。

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。
和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如
select * from user where match(name) against('aaa');
全文索引使用注意事项：
    全文索引必须在字符串、文本字段上建立。
    全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
    全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
    全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*
select * from user where match(name) against('a*' in boolean mode);
1
2
3
4
5
6
7
8
9

空间索引：SPATIAL （了解即可）

五、数据库的存储引擎

5.1、官方支持的存储引擎（5.7）

MyISAM

拥有较高的插入，查询速度，但不支持事务
InnoDB

5.5.8版本后Mysql的默认数据库引擎，支持ACID事务，支持行级锁定
BDB

源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性
Memory

所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失
Archive

将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用
Federated

将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用
Cluster/NDB

高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用
CSV

逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。
BlackHole

黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继

5.2、InnoDB存储引擎与MyISAM存储引擎的区别

5.2.1、存储结构（索引）

InnoDB的数据文件本身就是主索引文件；而MyISAM的主索引和数据是分开的。
InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

InnoDB主键索引就是聚集索引，而其他索引则是非聚集索引；相对于MyISAM，MyISAM不管主键索引还是其他索引都是非聚集索引；并且他的主键索引和普通索引的叶子节点都会存储数据的文件地址。

MyISAM索引文件在数据库中存放的对应表的磁盘文件有*.frm，.MYD，.MYI结尾的三个文件：

frm文件是存放的表结构，表的定义信息；
MYD文件是存放着表中的数据；
MYI文件存放着表的索引信息；
1
2
3

InnoDB索引文件在数据库中存放的对应表的磁盘文件有*.frm，*.ibd结尾的两个文件：

frm文件是存放的表结构，表的定义信息；
*.ibd文件是存放着表中的数据、索引信息；
1
2

5.2.2、锁

MyISAM使用的是表锁
InnoDB使用行锁

5.2.3、事务

MyISAM没有事务支持和MVCC
InnoDB支持事务和MVCC

5.2.4、主键

MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址
InnoDB如果没有设定主键或非空唯一索引，就会自动生成一个6字节的主键，数据是主索引的一部分，附加索引保存的是主索引的值

5.2.5、外键

MyISAM不支持
InnoDB支持

六、SQL优化（索引优化）

6.1、如何使用索引

查询搜索频率高的列
join 在经常使用表连接的列上创建索引这些列主要是一些外键，可以加快表连接的速度
where 需要根据范围进行搜索的列因为索引已经排序，所以其指定的范围是连续的
order by 在经常需要排序的列上创建索引因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询
where 在经常使用 WHERE 子句的列上创建索引，加快条件的判断速度

6.2、索引优化的工具（EXPLAIN）

6.2.1、使用

示例：

我们一般优化主要需要看的主要有这几个字段：

type、key、key_len、ref、Extra

6.2.2、字段解释

6.2.2.1、type

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到 range 级别，最好达到 ref；
1
2
3

（1）const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条数据时为 system；（使用主键或者唯一索引的时候会出现）

（2）eq_ref：primary key 或 unique key 索引组成列全部被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。（在连接查询的时候，使用了主键或唯一索引的全部字段）

（3）ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行；（使用的普通索引或联合唯一索引的部分前缀）

此示例采用了唯一索引的部分前缀：

（4）range：范围扫描通常出现在 in(), between , > , <, >= 等操作中。使用一个索引来检索给定范围的行。

（5）index：扫描全表索引，通常比 All 快一些；所有字段都建立了索引，使用 * 查询，则 type 为 index；

（6）ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了。

6.2.2.2、key

1.这一列显示mysql**实际**采用哪个索引来优化对该表的访问。
2.如果没有使用索引，则该列是 NULL。
3.如果想强制mysql使用或忽视 possible_keys 列中的索引，在查询中使用 force index、ignore index。
1
2
3

6.2.2.3、possible_keys(和key配合使用)

1.这一列显示查询**可能**使用哪些索引来查找。
2.explain 时可能出现 possible_keys 有值，而 key 显示 NULL 的情况，这是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
3.如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。
1
2
3

6.2.2.4、key_len

这一列显示了mysql 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

key_len计算规则如下：
字符串
    char(n)：n字节长度
    varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
数值类型
    tinyint：1字节
    smallint：2字节
    int：4字节
    bigint：8字节
时间类型
    date：3字节
    timestamp：4字节
    datetime：8字节
1
2
3
4
5
6
7
8
9
10
11
12
13

注意：如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

如图：key_len长度为4，src_device_id是int类型，int字节长度为4

6.2.2.5、Extra

这一列展示的是额外信息。常见的重要值如下：
Using index > Using index condition > Using where

（1）Using index：使用覆盖索引；覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。也就是查询的结果集中的所有字段都是在索引中的；

（2）Using index condition：查询的列不完全被索引覆盖，where条件中是一个联合索引的前导列的范围；

如下图，dsf非索引列：

（3）Using where：使用 where 语句来处理结果，查询的列未被索引覆盖；在查找使用索引的情况下，需要回表去查询所需的数据

如下图，dsf非索引列：

（4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。（distinct 查询可能会使用到临时表）

如下图，sk没有索引，此时创建了张临时表来 distinct：

如果命中了索引，则会将索引树加载到内存中，然后去重。

（5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

比如排序字段未使用索引：

（6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段；已经被 MySQL 优化过了；

6.2.2.6、select_type

select_type 表示对应行是简单还是复杂的查询。简单查询只有 simple，复杂查询有：primary，subquery，derived，union；

（1）simple：简单查询。查询不包含子查询和 union；
（2）primary：最外层的 select ；----复杂查询
（3）subquery：包含在 select 中的子查询（不在 from 子句中） – 复杂查询
（4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）； --复杂查询

（5）union：在 union 中的第二个和之后的 select 都为 union；

6.2.2.7、table

这一列表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的select 行 id。
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

6.2.2.8、ref

这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：device_management_service_mapping.id）。

6.2.2.9、rows

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。扫描的索引可能的行数

6.3、索引优化的小技巧

6.3.1、查询

最左前缀法则

如果建立了联合索引，要遵守最左前缀法则。指的是查询从联合索引的最左前列开始并且不跳过索引中的列

如果中间使用了范围检索，后面的列将无法命中索引
like以通配符开头（‘$abc…’）mysql索引失效会变成全表扫描操作
索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时，尽量使用not null 约束以及默认值。
如果有order by、group by的场景，请注意利用索引的有序性

order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort 的情况，影响查询性能。
例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。
如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;，索引(a,b)无法排序。
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
即使只有单纯的范围查询，如果范围过大，也会不走索引，当然可以将范围切割成多个小范围
mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
is null, is not null 也无法使用索引

6.3.2、更新/删除

在数据库隔离级别为可重复读下，在进行更新和删除操作时，需要将查询条件的字段增加索引，不然会导致表锁，造成锁等待
在读已提交的级别下，其实也会出现，但是mysql将它优化了，搜索过程会锁表，但是查询到结果后，就将不符合条件的数据解锁

6.4、比较常见的几种优化场景

6.4.1、分页查询

在进行分页查询优化时，我们首先要明白两点：

Innodb聚集索引其实就是主键索引（正常情况）<使用聚集索引，就不需要回表，速度很快>
MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下。

基于这两个前提，我们可以通过获取主键id来确定范围，避免查询前面的offset行，只要确定了第offset行，就可以快速查找到想要的数据。

实现方式有两种，但本质上都一样：

子查询法（主键是自增id）

SELECT * FROM articles WHERE  id >=  
(SELECT id FROM articles  WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10 
1
2

JOIN

select * from employees e 
inner join (select id from employees limit 3000000,10) ed on e.id = ed.id;
1
2

6.5、强制使用或禁止使用索引

- 强制使用某个索引：select * from table force index(索引名称) limit 2。
- 禁止使用某个索引：select * from table ignore index(idx_user) limit 2。
- 禁用缓存(在测试时去除缓存的影响)：select SQL_NO_CACHE from table limit 2。
1
2
3

七、MYSQL事务与隔离级别

7.1、并发事务带来的问题

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。
例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除。

7.2、事务特性（ACID）

原子性： 事务是最小单位，不可再分;要么全部执行成功，要么全部失败回滚。
一致性： 事务必须使数据库从一个一致的状态变到另外一个一致的状态，也就是执行事务之前和之后的状态都必须处于一致的状态。

不一致性包含三点：脏读，不可重复读，幻读
隔离性： 事务A和事务B之间具有隔离性;
持久性: 是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中);

7.3、MYSQL隔离级别

高并发的应用一般使用读已提交，追求安全的应用一般使用可重复读
1

7.3.1、READ UNCOMMITTED(读未提交)

（其他事务还未提交的数据就可以被读取到）

读读/读写：事务不做任何隔离操作
写写：获取记录的排他锁，不能同时进行，除非一个事务 提交或回滚
1
2

7.3.2、READ COMMITTED(读已提交简称RC)

（其他事务提交或者回滚，它会立即读到）

 读读：事务读的是事务最初的快照 mvcc机制
 读写：读的是快照数据，写的也是快照数据 mvcc机制
 写写：获取记录的排他锁，不能同时进行，除非一个事务 提交或回滚
1
2
3

7.3.3、REPEATABLE READ(可重复读简称RR)

（其他事务提交或者回滚，它会立即读到）

 读读：事务读的是事务最初的快照 mvcc机制
 读写：读的是快照数据，写的也是快照数据 mvcc机制
 写写：获取记录的排他锁，不能同时进行，除非一个事务 提交或回滚
1
2
3

7.3.4、SERIALIZABLE(序列化)

读读 ：共享锁多个事务可以同时获取
读写 : 共享锁和排它锁
写写 ： 排它锁和排它锁
1
2
3

7.5、隔离级别实现机制

7.5.1、Read Uncommitted（未提交读）

在RU级别下，所有的事务都可以看到其他未提交事务所修改的数据，也就是说，在这个隔离级别下会产生脏读和幻读的问题。该级别性能也并不比其他隔离级别好多少，因此很少实际使用。

7.5.2、READ COMMITTED(读已提交简称RC)

在RC级别下，一个事务开始执行后，只能看到其他已经提交的事务造成的修改，不能看到未提交事务对数据的修改，解决了脏读的问题。但仍然没有解决不可重复读的问题。很多DBMS默认隔离级别都是RC。（MySQL除外）

实现原理: MVCC多版本并发控制

在每一次进行快照读的时候，都会创建新的ReadView，因此可以读取其他事务提交后的数据

7.5.3、REPEATABLE READ(可重复读简称RR)

在RR级别下，保证同一个事务中多次读取同样的记录的结果是一致的。解决了不可重复读和幻读。

实现原理：MVCC和 Next-key Lock

当事务第一次对数据进行快照读时，MVCC会创建ReadView，之后的每一次快照读，都会根据之前创建的ReadView进行数据读取，不会读取到其他事务对数据的修改，因此解决了不可重复读和幻读。

当事务进行当前读时，会以Next-Key Lock的方式对读取到的数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是Record Lock和Gap Lock的组合。会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。那样当其他事务便不能对上锁的数据进行修改和插入，保证不会产生幻读！

7.5.4、SERIALIZABLE(序列化)

略

7.6、MVCC原理

八、MYSQL锁类别

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。
1

8.1、实现思想

8.1.1、乐观锁

乐观锁则认为对于同一个数据的并发操作，是不会发生修改的（或者增删改少，查多）。在更新数据的时候，会采用不断尝试更新的方式来修改数据。也就是先不管资源有没有被别的线程占用，直接取申请操作，如果没有产生冲突，那就操作成功，如果产生冲突，有其他线程已经在使用了，那么就不断地轮询。乐观的认为，不加锁的并发操作是没有事情的。就是通过记录一个数据历史记录的多个版本，如果修改完之后发现有冲突再将版本返回到没修改的样子，乐观锁就是不加锁。好处就是减少上下文切换，坏处是浪费CPU时间。
乐观锁其实是一种思想，认为不会锁定的情况下去更新数据，如果发现不对劲，才不更新(回滚)。在数据库中往往添加一个version字段（版本号）来实现。乐观锁可以用来避免更新丢失。

利用数据版本号（version）机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。
1

8.1.1.1、使用version字段（版本号）

一般来说，我们可以通过在表里增加version字段，来实现乐观锁

8.1.1.2、使用_cc

商品库存扣减时，尤其是在秒杀、聚划算这种高并发的场景下，若采用version号作为乐观锁，则每次只有一个事务能更新成功，业务感知上就是大量操作失败。因为version的粒度太大，更新失败的概率也就会变大。所以控制锁的粒度也是很重要的。

扩展训练：

在阿里很多系统中都能看到常用的features、params等字段，这些字段如果不进行版本控制，在并发场景下非常容易出现更新丢失的问题。

比如：

我们期望最终更新的结果为:

a=1;b=1;c=1;

此时若SQL写成了

update    
    lg_order
set    
    features=#features#
where    
    order_id=#order_id#
1
2
3
4
5
6

那么随着T-A和T-B的先后顺序不同，我们得到的结果有可能会是a=1;b=1;或a=1;c=1;

所以就要引入乐观锁进行版本控制。

若此时采用乐观锁，利用全局字段version进行处理，则会发现与lg_order的其他字段变更有非常高的冲突率，因为version字段是全局的。不管是不是features字段的更新，只要是这一行数据有更新就都会自增version字段。

update    
    lg_order
set    
    features=#features#,    
    version=version+1
where    
    order_id=#order_id#    
    and version=#ori_version#
1
2
3
4
5
6
7
8

这种SQL会因为version的失败而导致非常高的失败率，因为其他字段也在并发变更。

怎么办？

我们会发现一般设计库表的时，凡事拥有features类似字段的，都会有一个features_cc与之成对出现，很多厂内年轻一辈的程序员很少注意到这个字段，我们努力纠正过很久，现在应该好很多了。

version和_cc的区别是什么？

version和_cc是2个版本控制，一个是控制这一条数据，一个是控制这个一个字段的。比如一个字段a，如果引入version字段，那么这个version字段表示的时这一行数据的版本，就是说不管这一行数据哪个字段进行更新，都会对version字段进行自增。但是a_cc就不一样，a_cc专门用来表示a这个字段的版本号，只有a字段更新才会对a_cc进行自增，其他字段更新不会对a_cc进行自增。

features_cc的作用就是features的乐观锁版本的控制，这样就规避了使用version与整个字段冲突的尴尬。

update    
    lg_order
set    
    features=#features#,    
    features_cc= features_cc +1
where    
    order_id=#order_id#    
    and features_cc =#ori_ features_cc#
1
2
3
4
5
6
7
8

这里需要注意的是，需要应用owner仔细review自己相关表的SQL，要求所有涉及到这个表features字段的变更都必须加上features_cc= features_cc +1进行计算，否则会引起并发冲突，平时要做好保护措施，不然很中意中标。

8.1.2、悲观锁

悲观锁认为对于同一个数据的并发操作，一定是会发生修改的（或者增删改多，查少），哪怕没有修改，也会认为修改。因此对于同一个数据的并发操作，悲观锁采取加锁的形式。悲观的认为，不加锁的并发操作一定会出问题。
不管是共享锁亦或是排它锁，都是悲观锁。

8.2、锁的粒度(范围)

按照对数据操作的锁粒度来分：行级锁、表级锁、页级锁、间隙锁
MyISAM和MEMORY采用表级锁(table-level locking)
BDB采用页面锁(page-level locking)或表级锁，默认为页面锁
InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁
1
2
3
4

8.2.1、全局锁

全局锁就是对整个数据库实例加锁
命令是 Flush tables with read lock (FTWRL)

整个库处于只读状态的时候
以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句
全局锁的典型使用场景是，做全库逻辑备份

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新

8.2.2、表级锁

表级锁是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分mysql引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。开销小，加锁快，不会出现死锁。发生锁冲突的概率最高，并发度也最低。

8.2.2.1、共享锁/排它锁

8.2.2.1.1、共享锁

LOCK TABLES tbl_name READ,[ tbl_name {READ | WRITE},…]
用读锁锁表，会阻塞其他事务修改表数据，但不会阻塞其他事务读该表。 表锁
1
2

8.2.2.1.2、排它锁

LOCK TABLES tbl_name WRITE,[ tbl_name {READ | WRITE},…]
用写锁锁表，会阻塞其他事务读和写。  表锁
1
2

解锁表：UNLOCK TABLES

8.2.2.2、元数据锁

元数据锁是server层的锁，表级锁，每执行一条DML、DDL语句时都会申请metadata锁，DML操作需要metadata读锁锁，DDL操作需要metadata写锁，metadata加锁过程是系统自动控制，无法直接干预，读锁和写锁的阻塞关系如下：
1. 读锁和写锁之间相互阻塞，即同一个表上的DML和DDL之间互相阻塞。
2. 写锁和写锁之间互相阻塞，即两个session不能对表同时做表定义变更，需要串行操作。
3. 读锁和读锁之间不会产生阻塞。也就是增删改查不会因为metadata lock产生阻塞，可以并发执行，日常工作中大家看到的DML之间的锁等待是innodb行锁引起的，和metadata lock无关。

申请metadata锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到metadata锁锁后，直到事务执行完才会将锁释放。（这里有种特殊情况如果事务中包含DDL操作，mysql会在DDL操作语句执行前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放）。
熟悉innodb行锁的同学这里可能有点疑困惑，因为行锁分类和metadata lock很类似，也主要分为读锁和写锁，或者叫共享锁和排他锁，读写锁之间阻塞关系也一致。二者最重要的区别一个是表锁，一个是行锁，且行锁中的读写操作对应在metadata lock中都属于读锁。

8.2.2.3、意向共享锁/意向排它锁

8.2.2.3.1、意向共享锁

IS锁、意向共享锁、Intention Shared Lock。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。

8.2.2.3.2、意向排它锁

IX锁、意向排他锁、Intention Exclusive Lock。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

8.2.2.4、自增锁(AUTO-INC锁)

自增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。防止并发插入数据的时候自增id出现异常。
当一张表的某个字段是自增列时，innodb会在该索引的末位加一个排它锁。为了访问这个自增的数值，需要加一个表级锁，不过这个表级锁的持续时间只有当前sql，而不是整个事务，即当前sql执行完，该表级锁就释放了。其他session无法在这个表级锁持有时插入任何记录。

8.2.2.5、扩展

8.2.2.5.1、什么时候使用表锁

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ表。

8.2.2.5.2、使用表锁的注意事项

在InnoDB下，使用表锁要注意以下两点:

（１）使用LOCK TALBES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，ＭySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。

（２）在用LOCAK TABLES对InnoDB锁时要注意，要将AUTOCOMMIT设为0，否则ＭySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；而COMMIT或ROLLBACK并不能释放用LOCAK TABLES加的表级锁，所以一般我们必须先提交事务后，再用UNLOCK TABLES释放表锁，正确的方式见如下语句:

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;
1
2
3
4
5

8.2.3、页面锁

页面锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录。BDB 支持页面锁。开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

8.2.4、行级锁

行级锁是mysql中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。Innodb的行锁是通过锁住聚集索引实现的。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。开销大，加锁慢，会出现死锁。发生锁冲突的概率最低，并发度也最高。

8.2.4.1、共享锁

Shared Locks（S锁），针对同一份数据，多个读操作可以同时进行而不会互相影响

select * from mchopin where id = 3 lock in share mode;读行锁，仅对一行数据加了读锁。 行锁
1

8.2.4.2、排他锁

Exclusive Locks（X锁），当前写操作没有完成前，它会阻断其他写锁和读锁

select * from mchopin where id = 3 for update;写行锁，仅对一行数据加了写锁。  行锁
1

锁的兼容性：

这里的共享锁和排它锁都是表级别的

1.意向锁的存在是为了协调行锁和表锁的关系，支持多粒度(表锁与行锁)的锁并存。
2.申请意向锁的动作是数据库完成的。
3.IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。就是说当对一个行加锁之后，如果有打算给行所在的表加一个表锁，必须先看看该表的行有没有被加锁，否则就会出现冲突。IS锁和IX锁就避免了判断表中行有没有加锁时对每一行的遍历。直接查看表有没有意向锁就可以知道表中有没有行锁。
4.在添加行锁之前会先添加对应的意向锁。之后如果底下又有事务需要添加表锁，会先判断这张表是否存在意向锁：
	如果没有意向锁的话，则需要遍历所有整个表判断是否有行锁的存在，以免发生冲突
	如果有了意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了有行级锁的存在或者即将有行级锁的存在。因而无需遍历整个表，即可获取结果
1
2
3
4
5
6
7
8

8.3、锁模式（算法）

8.3.1、记录锁（Record Lock）

单个行记录上的锁。这个也是我们日常认为的行锁。
记录锁是对索引记录的锁（即锁的是聚集索引）。例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;阻止任何其他事务插入、更新或删除值为 t.c1是10。
记录锁总是锁定索引记录，即使一个表是没有索引定义。对于此类情况，InnoDB创建一个隐藏的聚集索引和使用此索引进行记录锁定。

8.3.2、间隙锁（Gap Lock）

间隙锁，锁定一个范围，但不包括记录本身（只不过它的锁粒度比记录锁的锁整行更大一些，他是锁住了某个范围内的多个行，包括根本不存在的数据）。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。该锁只会在隔离级别是RR或者以上的级别内存在。间隙锁的目的是为了让其他事务无法在间隙中新增数据。
间隙锁是在索引记录之间的间隙上的锁，或锁在第一个索引记录之前或最后一个索引记录之后的间隙上。间隙锁和间隙锁本身之间并不冲突，仅仅和插入这个操作发生冲突。

8.3.2.1、插入意向锁(了解)

插入意向锁是Gap锁，不是意向锁，是insert操作产生的。当多个事务同时将不同的数据写入同一个索引间隙时，不需要等待其他事务完成，也不会发生锁等待。
假定有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个插入意向锁，加到4-7之间，得到插入行上的排他锁，但不会相互锁定，因为数据行并不冲突。
插入意向锁不会阻止任何锁，插入记录会持有记录锁。

8.3.3、临键锁（Next-Key Lock）

Record Lock+索引前面的Gap Lock，锁定一个范围，并且锁定记录本身。next-key锁是InnoDB默认的锁。next-Key锁规定是左开右闭区间。

假设索引包含值 10、11、13 和 20。该索引可能的下一个键锁包括以下内容间隔，其中圆括号表示排除区间端点和方括号表示包含端点：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
1
2
3
4
5

九、MYSQL锁机制解析

9.1、术语常识

9.1.1、当前读

即加锁读，读取记录的最新版本，会加锁保证其他并发事务不能修改当前记录，直至获取锁的事务释放锁；
使用当前读的操作主要包括：显式加锁的读操作与插入/更新/删除等写操作

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
1
2
3
4
5
6
7
8

9.1.2、快照读

即不加锁读，读取记录的快照版本而非最新版本，通过MVCC实现；
select * from table where ?；
1
2

9.1.3、死锁

死锁 就是 两个以上的会话 在 抢占 资源过程中 ，产生相互等待的情况；有点绕是不是，其实很简单 死锁是建立在 锁等待的基础上，session A 获取 id = 1 的写锁 ， session B 获取 id =2 的写锁 ，此时由于索引不同，故不会长生锁等待现象 ； 当 session A 尝试 获取 id =2 的 写锁时 ，由于 id = 2 写锁已经被 session B 获取 ，故产生锁等待；当 session B 尝试 获取 id = 1 的写锁时 ，由于id =1 写锁已经被 session A 获取, 此时 产生锁等待； 由于 session A 与 session B 同时 都在 锁 等待状态，产生了等待对方释放锁，故会产生死锁；
1

9.1.3.1、死锁的处理方式

9.1.3.1.1、直接进入等待，直到超时

这个超时时间可以通过参数innodb_lock_wait_timeout来设置（默认50s）

对于在线服务来说，这个等待时间往往是无法接受的。
如果设置成1s，这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待，则会造成很多误伤

9.1.3.1.2、(推荐）主动死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行

将参数innodb_deadlock_detect设置为on，表示开启这个逻辑

如果出现很多事务都要更新同一行的场景（热点行），每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的CPU资源。因此，你就会看到CPU利用率很高，但是每秒却执行不了几个事务。
1. 对于上述的情况，如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉（头痛医头）
2. 控制并发度，如过同一行同时最多只有10个线程在更新，那么死锁检测的成本很低，就不会CPU占用高的问题。这个并发控制最好是在数据库Server端 / 中间件进行，而不能在客户端，因为通常会有很多客户端/很多连接/很多线程。其思路一般是：对于相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
3. 将一行改成逻辑上的多行来减少锁冲突
但在涉及外部锁，或涉及锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获取所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的SQL语句，绝大部分都可以避免。下面就通过实例来介绍几种死锁的常用方法:

在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序为访问表，这样可以大大降低产生死锁的机会。如果两个session访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可能避免。
在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低死锁的可能。
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应该先申请共享锁，更新时再申请排他锁，甚至死锁。
在REPEATEABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可以避免问题。
当隔离级别为READ COMMITED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第１个线程提交后，第２个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第３个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。

9.1.4、锁等待

锁等待的意思非常好理解，就是session (事物会话，开启一个事物代表一个会话)A 对 某行数据获取独占锁(在这边一般就是写锁)，然后session B 对相同的行进行获取独占锁就发生了锁等待；简单理解就是 小孩子抢玩具，谁先抢到 谁 先玩，没抢到的玩具的孩子只能 等待 抢到玩具孩子玩腻了再给你，瞬间泪奔有木有，就是这么残酷，当然MYSQL 没 这么残忍 其 还是有一个保留参数 innodb_lock_wait_timeout 指定死锁 的时间，如果超过 死锁等待时间就是报异常；
1

9.2、锁分析相关配置及命令行

9.2.1、行锁分析

show status like 'innodb_row_lock%';
对于各个状态说明如下：
Innodb_row_lock_current_waits:当前正在等待锁的数量；
Innodb_row_lock_time:从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间长度；
Innodb_row_lock_waits:系统启动到现在总共等待的次数
1
2
3
4
5
6
7

9.2.2、表锁分析

show status like 'table%';
对于各个状态说明如下：
table_locks_immediate ：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加一；
table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较重的表级锁争用情况；
1
2
3
4

9.2.3、事务分析

#查看事务
select * from information_schema.INNODB_TRX;
#查看锁
select * from information_schema.INNODB_LOCKS;
#查看锁等待
select * from information_schema.INNODB_LOCK_WAITS;
#释放锁，trx_mysql_thread_id可以从INNODB_TRX表中查看。
kill trx_mysql_thread_id;

注:MySQL8.0删除了information_schema.innodb_locks，添加了performance_schema.data_locks，可以通过performance_schema.data_locks查看事务的锁情况
1
2
3
4
5
6
7
8
9
10

9.2.4、锁监控及调试

#查看隔离级别(5.7.20开始)：
show global variables like 'transaction_isolation';
show variables like 'tx_isolation';
#查看事务是否自动提交
show variables like 'autocommit';
#查看间隙锁是否开启：（8.0以上好像没有这个了）其默认值为OFF，即启用间隙锁
show variables like 'innodb_locks_unsafe_for_binlog';
#查看innodb监控是否开启
show variables like 'innodb_status_output';
----------------------------前提-------------------------------------
#要想查看锁日志，首先要开启锁监控（InnoDB锁监控是随着InnoDB标准监控输入一起打印的，因此要启用InnoDB锁监控需要一并开启InnoDB标准监控。）
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;
----------------------------期间准备--------------------------------------
#设置隔离级别
set global transaction_isolation=1;
set global tx_isolation=1;
#关闭自动的事务提交；
set autocommit = 0;
----------------------------核心命令行--------------------------------------
#核心命令行，主要用来查看锁和事务等相关信息，分析锁和事务
show engine innodb status;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

9.2.4.1、show engine innodb status （TRANSACTIONS部分解释）

锁范围解释：
假设有三条记录，id主键分别是10，15，20
日志显示锁记录主键id为15
lock_mode X locks rec but not gap 是只有行锁；	15 那条数据的行锁
locks gap before rec，就是只有间隙锁；			15 那条数据之前的间隙，不包含 15  （10,15）
lock_mode X [waiting] 表示next-key lock；		   15 那条数据的间隙，包含 15 
1
2
3
4
5
6

9.2.5、其他

- 查看字符集：SHOW VARIABLES LIKE 'character_set%';
- 查看排序规则：SHOW VARIABLES LIKE 'collation%';
- 查看进程：show processlist;
- 杀死进程： kill 进程id
1
2
3
4

9.3、数据准备

9.3.1、测试表结构

mysql> desc userinfo;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | NO   | PRI | NULL    |       |
| name   | varchar(255) | YES  |     | NULL    |       |
| age    | int(11)      | YES  | MUL | NULL    |       |
| phone  | varchar(255) | YES  | UNI | NULL    |       |
| remark | varchar(255) | YES  |     | NULL    |       |
| uid    | int(11)      | YES  | UNI | NULL    |       |
+--------+--------------+------+-----+---------+-------+
6 rows in set (0.04 sec)
1
2
3
4
5
6
7
8
9
10
11
12

9.3.2、测试表数据

mysql> select * from userinfo;
+----+--------------+-----+-------+--------+-----+
| id | name         | age | phone | remark | uid |
+----+--------------+-----+-------+--------+-----+
|  0 | mayun        |  20 | 1000  | 马云   |   0 |
|  5 | liuqiangdong |  23 | 5555  | 刘强东 |   5 |
| 10 | mahuateng    |  18 | 1010  | 马化腾 |  10 |
| 15 | liyanhong    |  27 | 1515  | 李彦宏 |  15 |
| 20 | wangxing     |  23 | 2020  | 王兴   |  20 |
| 25 | zhangyiming  |  38 | 2525  | 张一鸣 |  25 |
+----+--------------+-----+-------+--------+-----+
6 rows in set (0.04 sec)
1
2
3
4
5
6
7
8
9
10
11
12

9.3.3、创建语句

CREATE TABLE `userinfo` (
  `id` int(11) NOT NULL COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄，普通索引列',
  `phone` varchar(255) DEFAULT NULL COMMENT '手机，唯一索引列',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `uid` int(11) DEFAULT NULL COMMENT '用户 唯一索引列',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_userinfo_phone` (`phone`) USING BTREE COMMENT '手机号码，唯一索引',
  UNIQUE KEY `idx_uid` (`uid`) USING BTREE,
  KEY `idx_user_info_age` (`age`) USING BTREE COMMENT '年龄，普通索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (0, 'mayun', 20, '1000', '马云', 0);
INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (5, 'liuqiangdong', 23, '5555', '刘强东', 5);
INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (10, 'mahuateng', 18, '1010', '马化腾', 10);
INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (15, 'liyanhong', 27, '1515', '李彦宏', 15);
INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (20, 'wangxing', 23, '2020', '王兴', 20);
INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (25, 'zhangyiming', 38, '2525', '张一鸣', 25);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

9.4、InnoDB加锁规则

原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间。
原则2：查找过程中访问到的对象才会加锁。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。（这个在MySQL8.0以后已经修复，但是在MySQL5.7版本中有这个问题）

等值查询：通过树搜索的方式定位记录。遍历就是遍历叶子节点。加锁应该是加在叶子节点上，在树搜索中遇到的，不会锁住。
1
2
3
4
5
6
7

9.4、隔离级别与锁的实际应用场景（RR级别）

在进行以下实验室，需要将隔离级别设置为RR级别，事务的自动提交设置为0 ，锁监控要打开
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;
set autocommit = 0;
set global transaction_isolation=2;
操作、验证和日志都是分别打开了一个命令行界面进行操作的；日志主要执行show engine innodb status命令查看锁状态，操作界面执行加锁语句，验证界面主要执行新增和更新语句用于验证锁的范围；操作和验证界面都需要执行set autocommit = 0；当测试完毕可以执行rollback回滚操作，方便下次操作。
1
2
3
4
5
6

9.4.1、主键索引

加锁时，会先给表添加意向锁，IX 或 IS；
加锁是如果是多个范围，是分开加了多个锁，每个范围都有锁
主键等值查询，数据存在时，会对该主键索引的值加行锁 
主键等值查询，数据不存在时，会对查询条件主键值所在的间隙添加间隙锁
主键等值查询，范围查询时情况则比较复杂：
    8.0.17 版本是前开后闭，而 8.0.18 版本及以后，修改为了前开后开区间；
    临界 <= 查询时，8.0.17 会锁住下一个 next-key 的前开后闭区间，而 8.0.18 及以后版本，修复了这个 bug。
1
2
3
4
5
6
7

9.4.1.1、主键索引等值查询存在

9.4.1.1.1、操作

mysql>  set @@autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `userinfo` where id =15 for update;
+----+-----------+-----+-------+--------+-----+
| id | name      | age | phone | remark | uid |
+----+-----------+-----+-------+--------+-----+
| 15 | liyanhong |  27 | 1515  | 李彦宏 |  15 |
+----+-----------+-----+-------+--------+-----+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10

9.4.1.1.2、日志

9.4.1.1.3、验证

mysql> set autocommit  =0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `test`.`userinfo` SET `name` = 'liyanhong', `age` = 22, `phone` = '1515', `remark` = '李彦宏' WHERE `id` = 15;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (14, 'liuqiangdong', 23, '555514', '刘强东', 14);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (16, 'liuqiangdong', 23, '555516', '刘强东', 16);
Query OK, 1 row affected (0.00 sec)
mysql> 
1
2
3
4
5
6
7
8
9
10

9.4.1.1.4、结果

只锁了id为15的记录
1

9.4.1.1.5、解析

索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
1

9.4.1.2、主键索引等值查询不存在

9.4.1.2.1、操作

mysql> SELECT * FROM `userinfo` where id =16 for update;
Empty set
1
2

9.4.1.2.2、日志

9.4.1.2.3、验证

mysql> UPDATE `test`.`userinfo` SET `name` = 'liyanhong', `age` = 22, `phone` = '1515', `remark` = '李彦宏' WHERE `id` = 15;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET `name` = 'wangxing', `age` = 24, `phone` = '2020', `remark` = '王兴' WHERE `id` = 20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (16, 'liuqiangdong', 23, '555516', '刘强东', 16);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (21, 'liuqiangdong', 23, '21', '刘强东', 21);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (14, 'liuqiangdong', 23, '555514', '刘强东', 14);
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

9.4.1.2.4、结果

间隙锁（15,20）
1

9.4.1.2.5、解析

先给查询加个临建锁:(15,20],然后因为16不等于20，所以临键锁退化成间隙锁（优化2），所以最终结果为（15,20）
1

9.4.1.3、主键索引范围查询存在

9.4.1.3.1、操作

mysql> SELECT * FROM `userinfo` where id >9 and id <15 for update;
+----+-----------+-----+-------+--------+-----+
| id | name      | age | phone | remark | uid |
+----+-----------+-----+-------+--------+-----+
| 10 | mahuateng |  18 | 1010  | 马化腾 |  10 |
+----+-----------+-----+-------+--------+-----+
1 row in set (0.02 sec)
1
2
3
4
5
6
7

9.4.1.3.2、日志

9.4.1.3.3、验证

mysql> UPDATE `test`.`userinfo` SET `name` = 'liuqiangdong', `age` = 22, `phone` = '5555', `remark` = '刘强东' WHERE `id` = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET `name` = 'liyanhong', `age` = 22, `phone` = '1515', `remark` = '李彦宏' WHERE `id` = 15;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (16, 'liuqiangdong', 23, '16', '刘强东', 16);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (4, 'liuqiangdong', 23, '4', '刘强东', 4);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (14, 'liuqiangdong', 23, '555514', '刘强东', 14);
1205 - Lock wait timeout exceeded; try restarting transaction

验证流程：
首先通过更新记录为id为5和15的记录，确定开闭区间；
然后通过id为4,16的插入操作确定范围的大小；
最后通过插入id为14的记录确定之间的间隙是否存在。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

9.4.1.3.4、结果

临键锁 (5,10](10,15]  综合(5,15]
1

9.4.1.3.5、解析

不满足id>9的第一条记录是5，不满足id<15的第一条记录是15，根据临键锁左开右闭区间得到结果(5,15]（原则1）
1

9.4.1.4、主键索引范围查询存在有等值查询

9.4.1.4.1、操作

mysql> SELECT * FROM `userinfo` where id >9 and id <=15 for update;
+----+-----------+-----+-------+--------+-----+
| id | name      | age | phone | remark | uid |
+----+-----------+-----+-------+--------+-----+
| 10 | mahuateng |  18 | 1010  | 马化腾 |  10 |
| 15 | liyanhong |  27 | 1515  | 李彦宏 |  15 |
+----+-----------+-----+-------+--------+-----+
2 rows in set (0.03 sec)
1
2
3
4
5
6
7
8

9.4.1.4.2、日志

9.4.1.4.3、验证

mysql> UPDATE `test`.`userinfo` SET `name` = 'liuqiangdong', `age` = 22, `phone` = '5555', `remark` = '刘强东' WHERE `id` = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET `name` = 'wangxing', `age` = 24, `phone` = '2020', `remark` = '王兴' WHERE `id` = 20;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (16, 'liuqiangdong', 23, '16', '刘强东', 16);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (4, 'liuqiangdong', 23, '4', '刘强东', 4);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (21, 'liuqiangdong', 23, '21', '刘强东', 21);
Query OK, 1 row affected (0.00 sec)

验证流程：
首先根据5,20确认开闭区间
然后根据16确认是否存在间隙
最后根据4,21确认真实范围
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

9.4.1.4.4、结果

临键锁 (5,10] (10,15] (15,20]
1

9.4.1.4.5、解析

id >9 and id <=15 这两个条件应该分为三个条件 id >9、id <15、id=15
id >9的临键锁为(5,10]，id <15的临键锁为(10,15]，而id=15的临键锁按理来说应该是15（根据优化1，退化为行锁），但是实际上是(15,20]（即所谓的一个bug）
1
2

9.4.1.5、主键索引范围查询不存在

9.4.1.5.1、操作

mysql> SELECT * FROM `userinfo` where id >7 and id <9 for update;
Empty set
1
2

9.4.1.5.2、日志

9.4.1.5.3、验证

mysql> UPDATE `test`.`userinfo` SET `name` = 'liuqiangdong', `age` = 22, `phone` = '5555', `remark` = '刘强东' WHERE `id` = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET `name` = 'mahuateng', `age` = 233, `phone` = '1010', `remark` = '马化腾' WHERE `id` = 10;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (4, 'liuqiangdong', 23, '4', '刘强东', 4);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (11, 'liuqiangdong', 23, '11', '刘强东', 11);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (6, 'liuqiangdong', 23, '55556', '刘强东', 6);
1205 - Lock wait timeout exceeded; try restarting transaction

验证流程：
首先根据5,10确认开闭区间
然后根据6确认是否存在间隙
最后根据4,11确认真实范围
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

9.4.1.5.4、结果

临键锁 (5,10]
1

9.4.1.5.5、解析

7的临键锁为(5,10]，9的临键锁还是(5,10]，合并后为(5,10]。
1

9.4.2、唯一索引

非主键唯一索引等值查询，数据存在，for update 是会在主键加锁的，而 for share 只有在走覆盖索引的情况下，会仅在自己索引上加锁；
非主键索引等值查询，数据不存在，无论是否索引覆盖，相当于一个范围查询，仅仅会在非主键索引上加锁，加的还是间隙锁，前开后开区间；
在非主键唯一索引范围查询时，不是覆盖索引的时候，会对相应的范围加前开后闭区间，并且如果存在数据，会对对应的主键加行锁；
在非主键唯一索引范围查询时，如果是覆盖索引时，会对所有的后闭区间对应的主键，加行锁；
在非主键唯一索引加锁时，还是存在 next-key 锁住下一个区间的 bug。
1
2
3
4
5

9.4.2.1、唯一索引等值查询存在

9.4.2.1.1、操作

mysql> SELECT * FROM `userinfo` where uid = 5 for update;
+----+--------------+-----+-------+--------+-----+
| id | name         | age | phone | remark | uid |
+----+--------------+-----+-------+--------+-----+
|  5 | liuqiangdong |  23 | 5555  | 刘强东 |   5 |
+----+--------------+-----+-------+--------+-----+
1 row in set (0.03 sec)
1
2
3
4
5
6
7

9.4.2.1.2、日志

9.4.2.1.3、验证

无
1

9.4.2.1.4、结果

行锁 uid=5的数据
1

9.4.2.1.5、解析

无
1

9.4.2.2、唯一索引等值查询不存在

9.4.2.1.1、操作

mysql> SELECT * FROM `userinfo` where uid = 6 for update;
Empty set
1
2

9.4.2.1.2、日志

9.4.2.1.3、验证

mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (6, 'liuqiangdong', 23, '55556', '刘强东', 6);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> UPDATE `test`.`userinfo` SET `name` = 'liuqiangdong', `age` = 22, `phone` = '5555', `remark` = '刘强东' WHERE `uid` = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET `name` = 'mahuateng', `age` = 233, `phone` = '1010', `remark` = '马化腾' WHERE `uid` = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3
4
5
6
7
8

9.4.2.1.4、结果

只锁住了唯一索引uid的索引  间隙锁(5,10)  这里指的是uid的范围；
1

9.4.2.1.5、解析

先插入uid=6的数据，判断是否存在间隙；其次再更新5和10的记录判断间隙的范围（优化2）。
没有锁住主键索引，意味着基于主键索引的加锁操作不受限制
1
2

9.4.2.3、唯一索引范围查询存在

9.4.2.1.1、操作

mysql> SELECT * FROM `userinfo` where uid >9 and uid <15 for update;
+----+-----------+-----+-------+--------+-----+
| id | name      | age | phone | remark | uid |
+----+-----------+-----+-------+--------+-----+
| 10 | mahuateng | 233 | 1010  | 马化腾 |  10 |
+----+-----------+-----+-------+--------+-----+
1 row in set (0.01 sec)
1
2
3
4
5
6
7

9.4.2.1.2、日志

9.4.2.1.3、验证

mysql> UPDATE `test`.`userinfo` SET `name` = 'liuqiangdong', `age` = 22, `phone` = '5555', `remark` = '刘强东' WHERE `uid` = 5;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET `name` = 'liyanhong', `age` = 22, `phone` = '1515', `remark` = '李彦宏' WHERE `uid` = 15;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (6, 'liuqiangdong', 23, '55556', '刘强东', 6);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> UPDATE `test`.`userinfo` SET `name` = 'mahuateng', `age` = 233, `phone` = '1010', `remark` = '马化腾' WHERE `id` = 10;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> UPDATE `test`.`userinfo` SET `name` = 'liyanhong', `age` = 22, `phone` = '1515', `remark` = '李彦宏' WHERE `id` = 15;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

验证流程：
首先根据5,10确认开闭区间
然后根据6确认是否存在间隙
最后根据id=10，15测试确认主键索引是否加锁
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

9.4.2.1.4、结果

临键锁(5,10]，(10,15] 结果合并(5,15]
1

9.4.2.1.5、解析

从日志里面可以看出，在uid索引表里增加了两个临键锁，最终范围是(5,15]，且在主键索引表里也增加了一个行锁，锁住了uid=15的记录，即id=15的记录
1

9.4.2.4、唯一索引范围查询存在有等值查询

9.4.2.1.1、操作

mysql> SELECT * FROM `userinfo` where uid >9 and uid <=15 for update;
+----+-----------+-----+-------+--------+-----+
| id | name      | age | phone | remark | uid |
+----+-----------+-----+-------+--------+-----+
| 10 | mahuateng | 233 | 1010  | 马化腾 |  10 |
| 15 | liyanhong |  22 | 1515  | 李彦宏 |  15 |
+----+-----------+-----+-------+--------+-----+
2 rows in set (0.02 sec)
1
2
3
4
5
6
7
8

9.4.2.1.2、日志

9.4.2.1.3、验证

mysql> UPDATE `test`.`userinfo` SET `name` = 'liuqiangdong', `age` = 22, `phone` = '5555', `remark` = '刘强东' WHERE `uid` = 5;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET `name` = 'wangxing', `age` = 24, `phone` = '2020', `remark` = '王兴' WHERE `uid` = 20;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (6, 'liuqiangdong', 23, '55556', '刘强东', 6);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> UPDATE `test`.`userinfo` SET `name` = 'mahuateng', `age` = 233, `phone` = '1010', `remark` = '马化腾' WHERE `id` = 10;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> UPDATE `test`.`userinfo` SET `name` = 'liyanhong', `age` = 22, `phone` = '1515', `remark` = '李彦宏' WHERE `id` = 15;
1205 - Lock wait timeout exceeded; try restarting transaction

验证流程：
首先根据5,20确认开闭区间
然后根据6确认是否存在间隙
最后根据id=10，15测试确认主键索引是否加锁
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

9.4.2.1.4、结果

uid索引表;
临键锁(5,10](10,15](15,20] 合并 (5,20]
主键索引表
记录锁 10,15
1
2
3
4

9.4.2.1.5、解析

首先他会锁住uid索引表里的数据，其次再根据uid索引表里的两个行锁（uid=10,15）找到主键索引的记录并锁住。
这里和主键索引类似，都存在bug,按理来说，uid=15根据优化1会退化成行锁，但实际并没有，而是依旧产生了一个间隙锁（这个就是所谓的“一个bug”）
1
2

9.4.2.5、唯一索引范围查询不存在

9.4.2.1.1、操作

mysql> SELECT * FROM `userinfo` where uid >7 and uid <9 for update;
Empty set
1
2

9.4.2.1.2、日志

9.4.2.1.3、验证

mysql> UPDATE `test`.`userinfo` SET `name` = 'liuqiangdong', `age` = 22, `phone` = '5555', `remark` = '刘强东' WHERE `uid` = 5;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET `name` = 'mahuateng', `age` = 233, `phone` = '1010', `remark` = '马化腾' WHERE `uid` = 10;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (6, 'liuqiangdong', 23, '55556', '刘强东', 6);
1205 - Lock wait timeout exceeded; try restarting transaction

验证流程：
首先根据5,20确认开闭区间
然后根据6确认是否存在间隙
1
2
3
4
5
6
7
8
9
10
11

9.4.2.1.4、结果

uid索引表
临键锁(5,10]
1
2

9.4.2.1.5、解析

uid大于7 锁范围是(5,10] uid<9的锁范围也是(5,10]，合并为(5,10]
1

9.4.3、普通索引

普通索引等值查询，因为不能确定唯一性，所以即使定位到记录，也是会向后查询，直到查询到不为该值的记录，从而锁定该值的区间；
普通索引的锁也是加载该索引上的，如果涉及到存在的记录，会对该主键加行锁；
对普通索引无效的，就是优化1，所以也就不存在“一个bug”,因为只有是唯一索引的时候才会退化为行锁，普通索引不会退化成行锁，加的都是临键锁，只是有可能退化成间隙锁。
1
2
3

9.4.3.1、普通索引等值查询存在

9.4.3.1.1、操作

mysql> SELECT * FROM `userinfo` where age = 20 for update;
+----+-------+-----+-------+--------+-----+
| id | name  | age | phone | remark | uid |
+----+-------+-----+-------+--------+-----+
|  0 | mayun |  20 | 1000  | 马云   |   0 |
+----+-------+-----+-------+--------+-----+
1 row in set (0.02 sec)
1
2
3
4
5
6
7

9.4.3.1.2、日志

9.4.3.1.3、验证

mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (19, 'liuqiangdong', 19, '19', '刘强东', 19);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> UPDATE `test`.`userinfo` SET  `remark` = '刘强东' WHERE `age` = 18;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET  `remark` = '刘强东' WHERE `age` = 23;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET `name` = 'mayun', `age` = 20, `phone` = '100110', `remark` = '马云', `uid` = 0 WHERE `id` = 0;
1205 - Lock wait timeout exceeded; try restarting transaction

验证流程：
首先根据18,23确认开闭区间
然后根据19确认是否存在间隙
最后确认age=20的数据是否在主键索引表里加锁
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

9.4.3.1.4、结果

age索引表：
临键锁(18,20] 间隙锁(20,23) 合并(18,23)
主键索引表：
行锁 id=0
1
2
3
4

9.4.3.1.5、解析

因为普通索引不是唯一索引，所以临键锁不会退化成行锁，所以会给age索引表添加一个临键锁，然后将其中的age=20的记录（行锁，临键锁=行锁+间隙锁）在主键索引加锁，然后再加一个间隙锁。
1

9.4.3.2、普通索引等值查询不存在

9.4.3.1.1、操作

mysql> SELECT * FROM `userinfo` where age = 19 for update;
Empty set
1
2

9.4.3.1.2、日志

9.4.3.1.3、验证

mysql> UPDATE `test`.`userinfo` SET  `remark` = '刘强东' WHERE `age` = 18;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET  `remark` = '刘强东' WHERE `age` = 20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (19, 'liuqiangdong', 19, '19', '刘强东', 19);
1205 - Lock wait timeout exceeded; try restarting transaction

验证流程：
首先根据18,20确认开闭区间
然后根据19确认是否存在间隙
1
2
3
4
5
6
7
8
9
10
11
12

9.4.3.1.4、结果

age索引表：
间隙锁 (18,20)
1
2

9.4.3.1.5、解析

根据优化2 临键锁退化成间隙锁
1

9.4.3.3、普通索引范围查询存在

9.4.3.1.1、操作

mysql> SELECT * FROM `userinfo` where age >9 and age <23 for update;
+----+-----------+-----+-------+--------+-----+
| id | name      | age | phone | remark | uid |
+----+-----------+-----+-------+--------+-----+
| 10 | mahuateng |  18 | 1010  | 马化腾 |  10 |
|  0 | mayun     |  20 | 1000  | 马云   |   0 |
+----+-----------+-----+-------+--------+-----+
2 rows in set (0.03 sec)
1
2
3
4
5
6
7
8

9.4.3.1.2、日志

9.4.3.1.3、验证

mysql> UPDATE `test`.`userinfo` SET  `remark` = '刘强东' WHERE `age` = 23;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (24, 'liuqiangdong', 24, '24', '刘强东', 24);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (1, 'liuqiangdong', 1, '1', '刘强东', 1);
1205 - Lock wait timeout exceeded; try restarting transaction

验证流程：
首先根据23,24确认开闭区间
然后根据1确认是否存在间隙
1
2
3
4
5
6
7
8
9
10

9.4.3.1.4、结果

age索引表：
临键锁(18,20](20,23],(negative infinity,18] 合并(negative infinity,23]
主键索引表：
行锁 id =0,10                                            
1
2
3
4

9.4.3.1.5、解析

略
1

9.4.3.4、普通索引范围查询存在有等值查询

9.4.3.1.1、操作

mysql> SELECT * FROM `userinfo` where age >21 and age <=23 for update;
+----+--------------+-----+-------+--------+-----+
| id | name         | age | phone | remark | uid |
+----+--------------+-----+-------+--------+-----+
|  5 | liuqiangdong |  23 | 5555  | 刘强东 |   5 |
| 20 | wangxing     |  23 | 2020  | 王兴   |  20 |
+----+--------------+-----+-------+--------+-----+
2 rows in set (0.05 sec)
1
2
3
4
5
6
7
8

9.4.3.1.2、日志

9.4.3.1.3、验证

mysql> UPDATE `test`.`userinfo` SET  `remark` = '刘强东' WHERE `age` = 20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET  `remark` = '刘强东' WHERE `age` = 27;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (28, 'liuqiangdong', 28, '24', '刘强东', 28);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (21, 'liuqiangdong', 23, '21', '刘强东', 21);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (19, 'liuqiangdong', 19, '19', '刘强东', 19);
Query OK, 1 row affected (0.00 sec)
验证流程：
首先根据20,27确认开闭区间
其次根据28,19确认真实范围
然后根据21确认是否存在间隙
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

9.4.3.1.4、结果

age索引表：
临键锁：(20,23],(23,27],(20,23] 合并(20,27]
主键索引表:
行锁：id=5,20
1
2
3
4

9.4.3.1.5、解析

age >21 锁范围为(20,23]  age <23 锁范围为(23,27] age=23的锁范围为(20,23],(23,27]
1

9.4.3.5、普通索引范围查询不存在

9.4.3.1.1、操作

mysql> SELECT * FROM `userinfo` where age >24 and age <26 for update;
Empty set
1
2

9.4.3.1.2、日志

9.4.3.1.3、验证

mysql> UPDATE `test`.`userinfo` SET  `remark` = '刘强东' WHERE `age` = 23;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0
mysql> UPDATE `test`.`userinfo` SET  `remark` = '刘强东' WHERE `age` = 27;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (28, 'liuqiangdong', 28, '24', '刘强东', 28);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (22, 'liuqiangdong', 22, '22', '刘强东', 22);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test`.`userinfo` (`id`, `name`, `age`, `phone`, `remark`, `uid`) VALUES (24, 'liuqiangdong', 24, '21114', '刘强东', 24);
1205 - Lock wait timeout exceeded; try restarting transaction

验证流程：
首先根据23,27确认开闭区间
其次根据28,22确认真实范围
然后根据24确认是否存在间隙
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

9.4.3.1.4、结果

age索引表：
临键锁(23,27]
1
2

9.4.3.1.5、解析

age >24锁范围为(23,27]， age <26锁范围为(23,27]，合并为(23,27]
1

9.4.4、无索引

普通字段查询，会查询全表，这里锁的话就会锁住主键的所有区间。
1

略

9.4.5、字符串类型索引

略需要熟悉字符集排序规则不想写了

9.5、隔离级别与锁的实际应用场景（RC级别）

首先在这个级别下我们可以理解为是没有间隙锁的，只会有行锁。当然他有个锁释放过程--存储引擎层会对扫描到的记录加X排他锁，但MySQL Server层对此进行优化，将不满足条件记录上的锁进行释放。
在进行以下实验时，记得将隔离级别设置为RC
1
2

9.5.1、主键索引

9.5.1.1、主键索引等值查询存在

9.5.1.1.1、操作

mysql> SELECT * FROM `userinfo` where id = 20 for update;
+----+----------+-----+-------+--------+-----+
| id | name     | age | phone | remark | uid |
+----+----------+-----+-------+--------+-----+
| 20 | wangxing |  23 | 2020  | 王兴   |  20 |
+----+----------+-----+-------+--------+-----+
1 row in set (0.03 sec)

1
2
3
4
5
6
7
8

9.5.1.1.2、日志

9.5.1.1.3、结果

记录锁 id=20
1

9.5.1.2、主键索引等值查询不存在

9.5.1.2.1、操作

mysql> SELECT * FROM `userinfo` where id = 19 for update;
Empty set
1
2

9.5.1.2.2、日志

9.5.1.2.3、结果

只加了一个意向排它锁
1

9.5.1.3、主键索引范围查询存在

9.5.1.3.1、操作

mysql> SELECT * FROM `userinfo` where id >9 and id <15 for update;
+----+-----------+-----+-------+--------+-----+
| id | name      | age | phone | remark | uid |
+----+-----------+-----+-------+--------+-----+
| 10 | mahuateng |  18 | 1010  | 马化腾 |  10 |
+----+-----------+-----+-------+--------+-----+
1 row in set (0.04 sec)
1
2
3
4
5
6
7

9.5.1.3.2、日志

9.5.1.3.3、结果

行锁 id=10
1

9.5.1.4、主键索引范围查询不存在

9.5.1.4.1、操作

mysql> SELECT * FROM `userinfo` where id >11 and id <15 for update;
Empty set
1
2

9.5.1.4.2、日志

9.5.1.4.3、结果

不知道这个行锁是什么意思，测试时，为感觉到有被加锁
1

9.5.2、唯一索引

9.5.2.1、唯一索引等值查询存在

9.5.2.1.1、操作

mysql> SELECT * FROM `userinfo` where uid =5 for update;
+----+--------------+-----+-------+--------+-----+
| id | name         | age | phone | remark | uid |
+----+--------------+-----+-------+--------+-----+
|  5 | liuqiangdong |  23 | 5555  | 刘强东 |   5 |
+----+--------------+-----+-------+--------+-----+
1 row in set (0.03 sec)
1
2
3
4
5
6
7

9.5.2.1.2、日志

9.5.2.1.3、结果

uid索引和主键索引上都加了一个行锁
1

9.5.2.2、唯一索引等值查询不存在

9.5.2.2.1、操作

mysql> SELECT * FROM `userinfo` where uid =6 for update;
Empty set
1
2

9.5.2.2.2、日志

9.5.2.2.3、结果

只有一个意向排它锁
1

9.5.2.3、唯一索引范围查询存在

9.5.2.3.1、操作

mysql> SELECT * FROM `userinfo` where uid >6 and uid <11 for update;
+----+-----------+-----+-------+--------+-----+
| id | name      | age | phone | remark | uid |
+----+-----------+-----+-------+--------+-----+
| 10 | mahuateng |  18 | 1010  | 马化腾 |  10 |
+----+-----------+-----+-------+--------+-----+
1 row in set (0.04 sec)
1
2
3
4
5
6
7

9.5.2.3.2、日志

9.5.2.3.3、结果

uid索引：
锁住了 uid=10,15
主键索引
锁住了 id = 10
1
2
3
4

9.5.2.4、唯一索引范围查询存在有等值查询

9.5.2.4.1、操作

mysql> SELECT * FROM `userinfo` where uid >6 and uid <=15 for update;
+----+-----------+-----+-------+--------+-----+
| id | name      | age | phone | remark | uid |
+----+-----------+-----+-------+--------+-----+
| 10 | mahuateng |  18 | 1010  | 马化腾 |  10 |
| 15 | liyanhong |  27 | 1515  | 李彦宏 |  15 |
+----+-----------+-----+-------+--------+-----+
2 rows in set (0.05 sec)
1
2
3
4
5
6
7
8

9.5.2.4.2、日志

9.5.2.4.3、结果

uid索引：
锁住了uid=10,15,20
主键索引：
锁住了id=10,15
1
2
3
4

9.5.2.5、唯一索引范围查询不存在

9.5.2.5.1、操作

mysql> SELECT * FROM `userinfo` where uid >11 and uid <13 for update;
Empty set
1
2

9.5.2.5.2、日志

9.5.2.5.3、结果

uid索引：
锁住了uid=15
1
2

9.5.3、普通索引

9.5.3.1、普通索引等值查询存在

9.5.3.1.1、操作

mysql> SELECT * FROM `userinfo` where age=20 for update;
+----+-------+-----+-------+--------+-----+
| id | name  | age | phone | remark | uid |
+----+-------+-----+-------+--------+-----+
|  0 | mayun |  20 | 1000  | 马云   |   0 |
+----+-------+-----+-------+--------+-----+
1 row in set (0.04 sec)
1
2
3
4
5
6
7

9.5.3.1.2、日志

9.5.3.1.3、结果

age索引：
行锁 age=20
主键索引：
行锁 id=0
1
2
3
4

9.5.3.2、普通索引等值查询不存在

9.5.3.2.1、操作

mysql> SELECT * FROM `userinfo` where age=21 for update;
Empty set
1
2

9.5.3.2.2、日志

9.5.3.2.3、结果

意向排他锁
1

9.5.3.3、普通索引范围查询存在

9.5.3.3.1、操作

mysql> SELECT * FROM `userinfo` where age >22 and age <27 for update;
+----+--------------+-----+-------+--------+-----+
| id | name         | age | phone | remark | uid |
+----+--------------+-----+-------+--------+-----+
|  5 | liuqiangdong |  23 | 5555  | 刘强东 |   5 |
| 20 | wangxing     |  23 | 2020  | 王兴   |  20 |
+----+--------------+-----+-------+--------+-----+
2 rows in set (0.06 sec)
1
2
3
4
5
6
7
8

9.5.3.3.2、日志

9.5.3.3.3、结果

age索引：
行锁 age=23,27
主键索引：
行锁 id=5,20
1
2
3
4

9.5.3.4、普通索引范围查询存在有等值查询

9.5.3.4.1、操作

mysql> SELECT * FROM `userinfo` where age >21 and age <=27 for update;
+----+--------------+-----+-------+--------+-----+
| id | name         | age | phone | remark | uid |
+----+--------------+-----+-------+--------+-----+
|  5 | liuqiangdong |  23 | 5555  | 刘强东 |   5 |
| 15 | liyanhong    |  27 | 1515  | 李彦宏 |  15 |
| 20 | wangxing     |  23 | 2020  | 王兴   |  20 |
+----+--------------+-----+-------+--------+-----+
3 rows in set (0.05 sec)
1
2
3
4
5
6
7
8
9

9.5.3.4.2、日志

9.5.3.4.3、结果

age索引：
行锁 age=5,15,20
主键索引：
行锁 id=5,15,20
1
2
3
4

9.5.3.5、普通索引范围查询不存在

9.5.3.5.1、操作

mysql> SELECT * FROM `userinfo` where age >20 and age <22 for update;
Empty set
1
2

9.5.3.5.2、日志

9.5.3.5.3、结果

age索引：
行锁 age=23
1
2

9.5.4、无索引

将age的索引删除，用于测试
1

9.5.4.1、无索引等值查询存在

9.5.4.1.1、操作

mysql> SELECT * FROM `userinfo` where age =20 for update;
+----+-------+-----+-------+--------+-----+
| id | name  | age | phone | remark | uid |
+----+-------+-----+-------+--------+-----+
|  0 | mayun |  20 | 1000  | 马云   |   0 |
+----+-------+-----+-------+--------+-----+
1 row in set (0.04 sec)
1
2
3
4
5
6
7

9.5.4.1.2、日志

9.5.4.1.3、结果

锁住了id=0
1

9.5.4.2、无索引等值查询不存在

9.5.4.2.1、操作

mysql> SELECT * FROM `userinfo` where age =19 for update;
Empty set
1
2

9.5.4.2.2、日志

9.5.4.2.3、结果

不知道这个算不算是锁
1

9.5.4.3、无索引范围查询存在

9.5.4.3.1、操作


mysql> SELECT * FROM `userinfo` where age >19 and age <23 for update;
+----+-------+-----+-------+--------+-----+
| id | name  | age | phone | remark | uid |
+----+-------+-----+-------+--------+-----+
|  0 | mayun |  20 | 1000  | 马云   |   0 |
+----+-------+-----+-------+--------+-----+
1 row in set (0.04 sec)
1
2
3
4
5
6
7
8

9.5.4.3.2、日志

9.5.4.3.3、结果

锁住了id=0
1

9.5.4.4、无索引范围查询存在有等值查询

9.5.4.4.1、操作

mysql> SELECT * FROM `userinfo` where age >20 and age <=23 for update;
+----+--------------+-----+-------+--------+-----+
| id | name         | age | phone | remark | uid |
+----+--------------+-----+-------+--------+-----+
|  5 | liuqiangdong |  23 | 5555  | 刘强东 |   5 |
| 20 | wangxing     |  23 | 2020  | 王兴   |  20 |
+----+--------------+-----+-------+--------+-----+
2 rows in set (0.05 sec)
1
2
3
4
5
6
7
8

9.5.4.4.2、日志

9.5.4.4.3、结果

只在主键索引加了锁 id=5,20
1

9.5.4.5、无索引范围查询不存在

9.5.4.5.1、操作

mysql> SELECT * FROM `userinfo` where age >20 and age <22 for update;
Empty set
1
2

9.5.4.5.2、日志

9.5.4.5.3、结果

略
1

十、MySQL日志

10.1、错误日志

10.1.1、定义

错误日志存储在 MySQL 数据库的数据文件夹下，通常名称为 hostname.err。其中，hostname 表示 MySQL 服务器的主机名。

10.1.2、配置

在 MySQL 配置文件中，错误日志所记录的信息可以通过 log-error 和 log-warnings 来定义，其中，log-err 定义是否启用错误日志功能和错误日志的存储位置，log-warnings 定义是否将警告信息也记录到错误日志中。错误日志默认开启且无法被禁止。

10.1.2.1、log_error

将 log_error 选项加入到 MySQL 配置文件的 [mysqld] 组中，形式如下：

[mysqld]
log-error=dir/{filename}
1
2

dir参数：指定错误日志的存储路径
filename参数：指定错误日志的文件名；省略参数时文件名默认为主机名，存放在 Data 目录中。
注意：

1. 重启 MySQL 服务后，参数开始生效，可以在指定路径下看到 filename.err 的文件，如果没有指定 filename，那么错误日志将直接默认为 hostname.err。
2. 错误日志中记录的并非全是错误信息，例如 MySQL 如何启动 InnoDB 的表空间文件、如何初始化自己的存储引擎等，这些也记录在错误日志文件中
1
2

10.1.2.2、log_warnings

将 log_warnings 选项加入到 MySQL 配置文件的 [mysqld] 组中，形式如下：

[mysqld]
log_warnings=2
1
2

10.1.2.3、log_error_verbosity

此参数与log_warnings参数作用大致相同，从MySQL 5.7.2开始，首选log_error_verbosity。将 log_warnings 选项加入到 MySQL 配置文件的 [mysqld] 组中，形式如下：

[mysqld]
log_error_verbosity=3
1
2

10.1.3、使用

10.1.3.1、查看日志

SHOW VARIABLES LIKE 'log_error';
1

#查看当前警告信息级别
show variables like "%log_warnings%";

0 表⽰不记录警告信息。
1 表⽰警告信息⼀并记录到错误⽇志中。
2 表⽰"失败的连接"的信息和创建新连接时"拒绝访问"类的错误信息也会被记录到错误⽇志中。
1
2
3
4
5
6

在这里插入图片描述

#查看当前警告信息级别
show variables like "%log_error_verbosity%";

1 错误信息； 
2 错误信息和告警信息；
3 错误信息、告警信息和通知信息
1
2
3
4
5
6

10.1.3.2、删除错误日志（备份错误日志）

在 MySQL 中，可以使用 mysqladmin 命令来开启新的错误日志，以保证 MySQL 服务器上的硬盘空间。mysqladmin 命令的语法如下：

mysqladmin -uroot -p flush-logs
1

执行该命令后，MySQL 服务器首先会自动创建一个新的错误日志，然后将旧的错误日志更名为 filename.err-old。

10.2、二进制日志

10.2.1、定义

二进制日志（Binary Log）也可叫作变更日志（Update Log），是 MySQL 中非常重要的日志。主要用于记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，不包含数据记录查询操作，主要记录了语句发生时间、执行时长、操作数据等其它额外信息。二进制日志（binlog）主要用于数据库恢复（基于时间点的恢复，在备份文件恢复的基础上，通过binlog日志，可以将数据库恢复到某一时间点）和主从复制，以及审计操作。如果 MySQL 数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
1

10.2.2、配置

[mysqld]
log-bin=dir/[filename]

示例：
server-id = 1
log-bin=D:/wsoft/mysql/mysql5720/mysql_binlog
注:server-id = 1这个必须配置，不然无法启动服务 后面的数字随便填写，但必须唯一
1
2
3
4
5
6
7

dir 参数指定二进制文件的存储路径
filename 参数指定二进制文件的文件名，其形式为 filename.number，number 的形式为 000001、000002 等

10.2.3、参数

10.2.3.1、sync_binlog

用于控制cache的数据commit多少次才刷到磁盘上，默认是0，也就是让数据库自己决定同步的频率。如设置成1的话，则每commit一次就会将cache的数据同步到磁盘上，这样做最安全，但是性能最差。MySQL中默认的设置是 sync_binlog=0，即不作任何强制性的磁盘刷新指令，这个设置性能是最好的，但风险也是最大的。一旦系统崩溃（Crash），在文件系统缓存中的所有二进制日志信息都会丢失。从而带来数据不完整问题。

sync_binlog=0，当事务提交后，Mysql仅仅是将binlog_cache中的数据写入binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，而是让Filesystem自行决定什么时候来做同步。
sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同时文件系统将Binlog文件缓存刷新到磁盘。

10.2.3.2、binlog_format

指定二进制日志的类型。分别有STATEMENT、ROW、MIXED三种值。MySQL 5.7.6之前默认为STATEMENT模式，MySQL 5.7.7之后默认为ROW模式。

基于段的格式binlog_format=STATEMENT

优点：日志记录量相对较小,节约磁盘及网络I/O
缺点：可能造成MySQL复制的主备服务器数据不一致
1
2

基于行的日志格式binlog_ format= ROW

优点：row格式可以避免MySQL复制中出现的主从不一致问题
缺点：记录日志的量比较大

同一SQL语句修改了10000条数据的情况下
基于段的日志格式只会记录这个SQL语句
基于行的日志会有10000条记录分别记录每一行的数据修改

误操作而修改了数据库中的数据,同时又没有备份可以
恢复时,我们就可以通过分析二进制日志,对日志中记录
的数据修改操作做反向处理的方式来达到恢复数据的目的

1
2
3
4
5
6
7
8
9
10
11

混合日志格式binlog_format= MIXED

根据SQL语句由系统决在基于段和基于行的日志格式中进行选择
数据量的大小由所执行的SQL语句决定
1
2

mysql二进制日志格式对复制的影响：

基于SQL语名的复制( SBR )
二进制日志格式使用的是statement格式
基于SQL段的日志是在从库上重新执行记录的SQL

   优点
       生成的日志量少,节约网络转输I/O
       并不强制要求主从数据库的表定义完全相同
       相比于基于行的复制方式更为灵活
   缺点
       对于非确定性事件,无法保证主从复制数据的一致性
       对于存储过程,触法器，自定义函数进行的修改也可能造成数据不一致
       相比于基于行的复制方式在从上执行时需要更多的行锁
1
2
3
4
5
6
7
8

基于行的复制( RBR )
二进制日志格式使用的是基于行的日志格式
基于行的日志则是在从库上直接应用对数据库行的修改

   优点
       可以应用于任何SQL的复制包括非确定函数,存储过程等
       可以减少数据库锁的使用
   缺点
       要求主从数据库的表结构相同,否则可能会中断复制
       无法在从上单独执行触法器（基于行的复制是直接在从服务器上面应用，主数据库对行的修改，而不是在从服务器上面重新执行sql）
1
2
3
4
5
6

混合模式
根据实际内容在以上两者间切换

mysql复制工作方式
    1.主将变更写入二进制日志
    2.从读取主的二进制日志变更并写入到relay_log中（基于日志点的复制、基于GTID的复制）
    3.在从上重放relay_log中的日志
1
2
3
4

注：具体怎么复制就不写了，以后再说

10.2.4、使用

10.2.4.1、查看bin_log是否开启

show variables like 'log_bin';
1

10.2.4.2、查看所有bin_log日志

show binary logs;
1

10.2.4.3、查看当前正在写入的二进制日志文件

show master status;
1

10.2.4.4、查看sync_binlog

show variables like 'sync_binlog';
1

10.2.4.5、查看日志模式

show variables like 'binlog_format';
1

10.2.4.6、删除bin_log日志

RESET MASTER;删除所有binlog日志，新日志编号从头开始
PURGE MASTER LOGS TO ‘mysql-bin.010’; ||删除mysql-bin.010之前所有日志
PURGE MASTER LOGS BEFORE ‘2003-04-02 22:46:26’; ||删除2003-04-02 22:46:26之前产生的所有日志
1
2
3

10.2.5、恢复数据

原始数据：

误操作后数据：

UPDATE `test`.`device_management_service_mapping` SET `src_device_id` = 113 WHERE `id` = 86;
UPDATE `test`.`device_management_service_mapping` SET `src_device_id` = 11 WHERE `id` = 86;
1
2

目标-还原到第一行SQL的位置：

第一步：查看bin_log日志，确定还原点

查看最新bin_log日志
```
show binary logs;
1
```

查看还原点

show binlog EVENTS in 'mysql_binlog.000001';
1

第二步：指定位置还原

mysqlbinlog --no-defaults "D:\wsoft\mysql\mysql5720\mysql_binlog.000001" -d test --skip-gtids --start-position=219 --stop-position=520>test.sql
1

注：还支持时间点还原（–start-datetime=‘2019-03-01 00:00:00’ –stop-datetime=‘2019-03-10 00:00:00’）

第三步：登录mysql并指定数据库

mysql -uroot -p123456;
use test;
1
2

第四步：导入生成的sql文件

source D:/wsoft/mysql/mysql5720/bin/test.sql;
1

还原后的数据：

注：如果导入SQL文件失败，可能有以下几种情况：

windows和linux斜杠不一样
没有文件操作权限
编码问题

可以在执行过程中先关闭bin-log日志，还原完毕后在开启，以防产生新的二进制数据

10.3、慢查询日志

10.3.1、定义

MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。
具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。
long_query_time的默认值为10，意思是记录运行10秒以上的语句。
默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入日志文件和数据库表。
1
2
3
4
5
6

10.3.2、配置

slow_query_log = 1
slow_query_log_file = /tmp/mysql_slow.log
...
1
2
3

10.3.3、参数

slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭。
log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志。秒
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库。
1
2
3
4
5
6

10.3.4、使用

10.3.4.1、查看慢查询日志是否启用

show variables like '%slow_query_log%'
1

10.3.4.2、查看慢查询日志阈值

show variables like 'long_query_time%';
1

10.3.4.3、查询日志存储方式

show variables like '%log_output%';
1

10.3.4.4、查询未使用索引的查询也被记录到慢查询日志中设置是否开启

show variables like 'log_queries_not_using_indexes';
1

10.3.4.5、查询慢查询SQL条数

show global status like '%Slow_queries%';
1

10.3.5、mysqldumpslow工具

[root@localhost ~]# mysqldumpslow --help
 Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
  --verbose    verbose
  --debug      debug
  --help       write this text to standard output
  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default（排序方式）
                 al: average lock time（平均锁定时间）
                 ar: average rows sent（平均返回记录数）
                 at: average query time（平均查询时间）
                  c: count（访问计数）
                  l: lock time（锁定时间）
                  r: rows sent（返回记录）
                  t: query time（查询时间）
   -r           reverse the sort order (largest last instead of first)
   -t NUM       just show the top n queries（返回前面n条数据）
   -a           don't abstract all numbers to N and strings to 'S'
   -n NUM       abstract numbers with at least n digits within names
   -g PATTERN   grep: only consider stmts that include this string（正则匹配模式，大小写不敏感）
   -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
                default is '*', i.e. match all
   -i NAME      name of server instance (if using mysql.server startup script)
   -l           don't subtract lock time from total time
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

得到返回记录集最多的10个SQL：

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
1

得到访问次数最多的10个SQL：

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
1

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
1

另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
1

10.4、通用查询日志

10.4.1、定义

通用查询日志(General Query Log)用来记录用户的所有操作，包括启动和关闭 MySQL 服务、更新语句和查询语句等。

10.4.2、配置

在 MySQL 中，可以通过在 MySQL 配置文件添加 log 选项来开启通用查询日志，格式如下：

[mysqld]
log=dir/filename
1
2

其中，dir 参数指定通用查询日志的存储路径；filename 参数指定日志的文件名。如果不指定存储路径，通用查询日志将默认存储到 MySQL 数据库的数据文件夹下。如果不指定文件名，默认文件名为 hostname.log，其中 hostname 表示主机名。

10.4.3、使用

10.4.3.1、查看通用日志查询是否开启

SHOW VARIABLES LIKE '%general%';
1

10.4.3.2、开启通用查询日志

SET GLOBAL general_log=on;
1

10.4.3.3、删除通用查询日志

mysqladmin -uroot -p flush-logs
1

10.5、redo 重做日志

作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redo log执行重做，达到事务一致性
1

10.6、undo回滚日志

作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交 隔离级别就是通过mvcc+undo实现
1

10.7、relay log 中继日志

作用：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放
1

十一、MYSQL相关配置和命令

11.1、mysql服务的启动、停止和删除

 net stop 服务名
 net start 服务名
 sc delete 服务名
 一般服务名默认为mysql
1
2
3
4

11.2、登录mysql

mysql （-h）-u 用户名 -p 用户密码
注意，如果是连接到另外的机器上，则需要加入一个参数-h机器IP
1
2

11.3、退出mysql

exit (回车)
1

11.4、导入sql文件

use 数据库名;
source d:/mysql.sql;
1
2

11.5、导入导出

导入：
mysqlimport -u root -p用户密码 < mysql.dbname。
导出：
mysqldump --opt test > mysql.test
即将数据库test数据库导出到mysql.test文本文件
例：mysqldump -u root -p用户密码 --databases dbname > mysql.dbname
1
2
3
4
5
6

11.6、修改密码

mysqladmin -u用户名 -p旧密码 password 新密码
1

11.7、增加新用户

grant select on 数据库.* to 用户名@登录主机 identified by "密码"
1

十二、参考

[1 ]: https://blog.csdn.net/guorui_java/article/details/118558095	"mysql索引详解"
[2 ]: 高性能mysql
[3 ]: https://www.cnblogs.com/dbbull/p/15212037.html	"MySQL索引完全解读"
[4 ]: https://blog.csdn.net/xiantianga6883/article/details/121095288	"MyISAM 和 InnoDB 索引的区别"

[5 ]: http://dljz.nicethemes.cn/news/show-243163.html	"事务Transaction学习笔记"

[6 ]: https://blog.csdn.net/cy973071263/article/details/104490345	"MySQL的锁与事务隔离级别详解"

[7 ]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html	"官方-MySQL 5.7 参考手册"

[8 ]: http://c.biancheng.net/view/7745.html	"日志分类"

[9 ]: https://blog.csdn.net/Gjikcheng/article/details/89674749	"MySQL 的数据库错误日志设置"

[10 ]: https://blog.csdn.net/BushQiang/article/details/117868288	"MySQL二进制日志"

[11 ]: https://blog.csdn.net/qq_44901285/article/details/124160540	"MySQL通过Binlog恢复数据"
[12 ]: https://blog.csdn.net/lovelichao12/article/details/123465147	"MySQL 高级知识之慢查询日志"

[13 ]: https://www.cnblogs.com/poloyy/p/12890763.html	"MySQL常用函数"
[14 ]: https://blog.csdn.net/a360284634/article/details/123272792	"Mysql数据库锁与隔离级别"

[15 ]: https://blog.csdn.net/qq_36535538/article/details/117692855	"MySQL next-key lock 加锁范围总结"
[16 ]: https://www.cnblogs.com/michael9/p/12443975.html	"关于 InnoDB 锁的超全总结"
[17 ]: https://www.utf8-chartable.de/unicode-utf8-table.pl?utf8=dec	"utf8字符集"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

相关阅读:
社交网络用户行为分析,各类社交软件用户分析
 iOS小技能：iOS15崩溃排查技巧（symbolicatecrash符号化分析问题、导出和隐藏符号）
二叉树的遍历方式和代码
 重构：改善既有代码的设计
 另眼旁观 Linkerd 2.12 的发布：服务网格标准的曙光？
11.11 校招实习内推面经
 hive 内部表、外部表、使用场景举例、内部表与外部表转换
 Java PrintStream.println方法具有什么功能呢？
【Godot4自学手册】第十八节主人公的血量显示
 滴滴拟从美股退市；全球首台升降摄像头iPhone诞生；Twitter采取毒丸计划阻止马斯克的敌意收购|极客头条
原文地址：https://blog.csdn.net/qq_39248747/article/details/126877644