# MySQL

公钥加密，私钥解密→加密

私钥加密，公钥解密→签名

mysql 常见错误

1044：当前用户没有访问数据库的权限

1045 密码错误

1065 无效的 SQL 语句，SQL 语句为空

1180 提交事务失败

1181 回滚事务失败

2002 数据库没有启动或者是端口被防火墙禁止

2003 错误不开放外链

innodb 行锁 >5.5

MyISAM 表锁

utf8mb4 四子节

utf8 三子节 可能导致截断产生 xss

模糊匹配变量名

1

show variables like “%character%”

本地变量只在当前窗口生效

1

set session var = bbb;

全局变量在所有窗口生效

1

set global var = aaa;

查看密码 5.5 以后 password 字段变为 authentication_string

1
2
3
4
5
6
7
8
9
10
11
12

select host,password,user from mysql.user;
mysql> select host,password,user from mysql.user;
+-----------+-------------------------------------------+---------+
| host      | password                                  | user    |
+-----------+-------------------------------------------+---------+
| localhost | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | root    |
| 127.0.0.1 | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | root    |
| ::1       | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | root    |
| localhost |                                           |         |
| localhost | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | root123 |
| 127.0.0.1 | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | root123 |
+-----------+-------------------------------------------+---------+

创建用户

1
2

create user 'llisi'@'localhost'identified by '123456';
grant all on sec.* to 'root'@'%' identified by '123456';

给用户加远程登录权限时必须在配置文件中修改 bindip

mysql.user 在新版中是一个视图，不能直接 insert 来新建用户。如果设置严格模式也可能导致 insert 插入用户失败

修改密码

1

Alert user ‘root’@‘localhost’ identified by ‘passed’

删除用户

1

drop user aaa@localhost

创建数据库

1
2

create database if not exist test;
drop database if exist test

查看建库语句：

1

show create database dbs;

创建表

1
2
3
4

create table if not exists yanchuang (
id int(5) unsigned not null primary key auto_increment，
name varchar(4)
);

varchar 后的数字代表显示字符长度，如果开启严格模式会报错 1406，关闭后插入的字符会缺失

int 后的数字必须配合 zerofill 使用，使用后代表最小的位数，如果不够用 0 填充的 int 后面的数字位数

插入数据

1
2

insert into test (name,age) values (haha,123);
insert into test set name="aaa",age=100;

# 索引

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

# 索引常见模型

三种常见、也比较简单的数据结构，它们分别是哈希表、有序数组和搜索树。

# 哈希表

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

你现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这时对应的哈希索引的示意图

将 ID_card_n2 通过哈希函数算出 N；然后，按顺序遍历，找到 User2。

四个 ID_card_n 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。

所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。key-value

比如 select * from users where name=“aaa”;

# 有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀。

这里我们假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。这时候如果你要查 ID_card_n2 对应的名字，用二分法就可以快速得到，这个时间复杂度是 O (log (N))。

同时很显然，这个索引结构支持范围查询。你要查身份证号在 [ID_card_X, ID_card_Y] 区间的 User，可以先用二分法找到 ID_card_X（如果不存在 ID_card_X，就找到大于 ID_card_X 的第一个 User），然后向右遍历，直到查到第一个大于 ID_card_Y 的身份证号，退出循环。

但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

所以，有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。

# 二叉搜索树

父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值。这样如果你要查 ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O (log (N))。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。

（2 ** 20）= 1048576

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用 “N 叉” 树。这里，“N 叉” 树中的 “N” 取决于数据块的大小。

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200 (MySql 默认一个存储页面的大小为 16K，一个整数（bigint）字段索引的长度为 8B, 另外每个索引还跟着 6B 的指向其子树的指针；所以 16K/14B ≈ 1170)。这棵树高是 3 的时候，就可以存 1200 的 2 次方个值，这已经 8 亿了。考虑到树根的数据块总是在内存中的，一个 8 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

# B-tree

由于每个节点还是存储数据，还是需要访问磁盘

# B+tree

InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

B+Tree 相对于 B-Tree 有几点不同：

1. B + 节点关键字搜索采用闭合区间
2. B+ 非叶节点不保存数据相关信息，只保存关键字和子节点的引用
3. B + 关键字对应的数据保存在叶子节点中
4. B + 叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。只有叶子节点才存放数据

B + 以页为单位，1 页 = 16kb，可以修改 innodb_page_size，只能在初始化时修改，即需要删除 ibdata1 和 ib_logfile0

添加索引 index，key

key 定义主键索引，一个表中只能有一个主键，一个主键可以包含多个字段

index 定义索引

1

primary key(id),

index idx_name (name) 其作用和 key ind_name(name) 相同

删除主键索引需要先删除 auto_increment，再删除主键索引

1
2
3

alter table chenke change id id int unsigned not null
alter table chenke drop primary key
desc chenke 

约束信息可以在 information_schema 表中查看

1

select * from information_schema.key_column_usage where table_name='test1';

创建索引

1
2
3
4

create index ind on test (id,name)    //
create index ind on test (id,name(5)) //以前五个字符当做索引，称为最左前缀索引

index index_name (`name`,`gender`,`age`) //name在最左

explain 查看是否命中索引，用于分析语句效率

1

explain seelct * from stu where gender=1

# InnoDB 索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

mysql> create table T(

id int primary key,

k int not null,

name varchar(16),

index (k))engine=InnoDB;

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

# 最左前缀索引

为了直观地说明这个概念，我们用（name，age）这个联合索引来分析

可以看到，索引项是按照索引定义里面出现的字段顺序排序的。

如果你要查的是所有名字第一个字是 “张” 的人，你的 SQL 语句的条件是 "where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

必须要有最左前缀在查询中，即使用索引，因为 mysql 查询时会优化，将最左前缀放到最前面

假设，你现在维护一个支持邮箱登录的系统，用户表是这么定义的：

1
2
3
4
5

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64), 
username varchar(100)
)engine=innodb; 

由于要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：

1

mysql> select f1, f2 from SUser where email='xxx';

如果 email 这个字段上没有索引，那么这个语句就只能做全表扫描。

比如，这两个在 email 字段上创建索引的语句：

1
2
3

mysql> alter table SUser add index index2(email);
mysql> alter table SUser add index index2(email(6));
由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。

如果拿整个邮箱做索引

email (6) 索引结构

从图中你可以看到，由于 email (6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。

如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：

1
2
3
4

从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。
这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是 index2（即 email (6) 索引结构），执行顺序是这样的：

1
2
3
4
5
6
7

从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。
在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。
通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。
但是，对于这个查询语句来说，如果你定义的 index2 不是 email(6) 而是 email(7），也就是说取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到 ID2，只扫描一行就结束了。

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

首先，你可以使用下面这个语句，算出这个列上有多少个不同的值：

1

mysql> select count(distinct email) as L from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下 4~7 个字节的前缀索引，可以用这个语句：

1
2
3
4
5
6

mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

当然，使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

# 索引下推

我们还是以市民表的联合索引（name, age）为例。

如果现在有一个需求：检索出表中 “名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

1

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。然后判断其他条件是否满足。

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

当创建 (a,b,c) 复合索引时，想要索引生效的话，只能使用 a 和 ab、ac 和 abc 三种组合

1
2
3

mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='a1' ; //索引生效
mysql>SELECT `a`,`b`,`c` FROM A WHERE `b`='b2' AND `c`='c2'; //索引失效
mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='a3' AND `c`='c3'; //索引生效，实际上值使用了索引a

三个字段联合索引测试

1
2
3
4
5
6
7

create table `user` (
`id` int(11) not null,
`name` varchar(45) default null,
`sex` varchar(45) default null,
`age` varchar(45) default null,
key `index_test` (`sex`,`age`,`name`) using btree
)ENGINE=InndDB DEFAULT charset=utf8mb4;

联合索引的顺序为：sex，age，name

1
2
3
4
5
6

SELECT * FROM user where age="4"; #未使用索引
SELECT * FROM user where name="2"; #未使用索引
SELECT * FROM user where sex="2" and age="3"; #使用索引
SELECT * FROM user where sex="2" and age="3" and name="4"; #使用索引
SELECT * FROM user where age="3" and name="4";  #未使用索引
SELECT * FROM user where sex="2" and name="4";  #使用索引

两个字段测试

1
2
3
4
5
6
7

create table `user` (
`id` int(11) not null,
`name` varchar(45) default null,
`sex` varchar(45) default null,
`age` varchar(45) default null,
key `index_test` (`sex`,`age`) using btree
)ENGINE=InndDB DEFAULT charset=utf8mb4;

1
2
3

explain SELECT * FROM index_demo.user where age="4"; #未使用索引
explain SELECT * FROM index_demo.user where sex="2" and age="3"; #使用索引
explain SELECT * FROM index_demo.user where age="3" and sex="4";  #使用索引

mysql 查询优化器会判断纠正这条 sql 语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

因为对于三个索引的时候，只要是前两个存在，不论顺序是什么都是会使用索引的，

# 覆盖索引

select * from T where k between 3 and 5

1
2
3
4
5
6
7
8

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

1
2
3
4
5
6

现在，我们一起来看看这条 SQL 查询语句的执行流程：
在 k 索引树上找到 k=3 的记录，取得 ID = 300；
再到 ID 索引树查到 ID=300 对应的 R3；
在 k 索引树取下一个值 k=5，取得 ID=500；
再回到 ID 索引树查到 ID=500 对应的 R4；
在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经 “覆盖了” 我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

# 其他索引

我们国家的身份证号，一共 18 位，其中前 6 位是地址码，所以同一个县的人的身份证号前 6 位一般会是相同的。

假设你维护的数据库是一个市的公民信息系统，这时候如果对身份证号做长度为 6 的前缀索引的话，这个索引的区分度就非常低了。

按照我们前面说的方法，可能你需要创建长度为 12 以上的前缀索引，才能够满足区分度要求。

但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

** 第一种方式是使用倒序存储。** 如果你存储身份证号的时候把它倒过来存，每次查询的时候，你可以这么写：

1

mysql> select field_list from t where id_card = reverse('input_id_card_string');

由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足够的区分度。当然了，实践中你不要忘记使用 count (distinct) 方法去做个验证。

** 第二种方式是使用 hash 字段。** 你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

1

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候，都同时用 crc32 () 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32 () 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

1

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这样，索引的长度变成了 4 个字节，比原来小了很多。

它们的相同点是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的所有市民了。同样地，hash 字段的方式也只能支持等值查询。

从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

# 一颗高度为 3 的 B + 树，能存储多少数据

# InnoDB 页结构

• 在 InnoDB 中，索引默认使用的数据结构为 B + 树，而 B+树里的每个节点都是一个页 ，默认的页大小为 16KB 。
• 非叶子节点存的是索引值以及页的偏移量，而叶子节点上存放的则是完整的每行记录

# 非叶子节点能存多少条数据

• 页默认 16KB

• File Header、Page Header 等一共占 102 个字节

• Infimum + Supremum 分别占 13 个字节

• 记录头占 5 个字节

• id 占为 int，占 4 个字节

• 页目录的偏移量占 4 个字节

1
2
3
4
5

   非叶子节点能存放的索引记录
=  (页大小 - File Header - Page Header - ...) / ( 记录头 + 主键 + 页偏移量)
= （16KB - 128B) / (5B + 4B + 4B) 
=  16256 / 13
=  1250 条

# 叶子节点能存多少条数据

• 变长列表占 1 个字节
• null 标志位忽略
• 记录头占 5 个字节
• id 占为 int，占 4 个字节
• name 为 VARCHAR，编码为 UTF8，为了好算，所有行记录我都只用两个中文，那就是 2 * 3B = 6 个字节
• 事务 ID 列占 6 个字节
• 回滚指针列占 7 个字节

1
2
3
4
5

   叶子节点能存放的数据记录
=  (页大小 - File Header - Page Header - ...) / ( 主键 + 字段 + 下一条记录的偏移量)
= （16KB - 128B) / (1B + 5B + 4B + 6B + 6B + 7B) 
=  16256 / 29
=  560 条

• 根节点能放 1250 条索引记录
• 第二层能放 1250 * 1250 = 1,562,500 条索引记录
• 叶子节点 1250 * 1250 * 560 = 875,000,000 条数据记录，八亿多条数据

# N 叉树的 N 可以修改吗

1
2
3
4
5

   非叶子节点能存放的索引记录
=  (页大小 - File Header - Page Header - ...) / ( 记录头 + 主键 + 页偏移量)
= （16KB - 128B) / (5B + 4B + 4B) 
=  16256 / 13
=  1250 条

主键大小使我们可控的，如果是 bigint = 956

我们也可以控制 Innodb_page_size，默认 16k，我们可以通过配置改为 8k

https://blog.csdn.net/weixin_44811851/article/details/125786092

# 视图

视图中的字段与对应的数据均来自已经存在的表，对于视图来说，这些已经存在的表就被称为” 基表”, 基表可以是一张表，也可以是多张表， 视图的本质可以理解为一条查询语句，视图中显示的结果，就是这条查询语句查询出的结果。

应该是 mysql 对于子查询的优化不是很好，而使用视图本身往往就意味着使用子查询，所以，如果我们必须使用视图时，最好将视图中的 sql 语句尽量优化，或者说，数据量大的时候尽量避免使用视图。

创建视图的原始表为基表

1
2

create view view1 as select * from stu where age > 100
select * from view1;

删除视图

1
2

drop view  view1
drop view if exists testvi;

查看视图

1

select * from information_schema.views where table_schema=stu;

修改视图

1

alter view testvi as select  name,age from students;

# DDL

1

insert into  stu (name,age) values ('haha',5),('shabi',6)

insert 字段可以调换顺序，前后一起调换即可

tinyint 最大范围为 127

1

insert into values ('haha',44)

不写字段名必须按照字段顺序和字段数插入，包括自增主键的值也需要手写

1

insert into stu set 1=1;

int (8) 代表显示 8 位数字，int 永远占四个字节

concat && group_concat()

concat 连接时数据不能超过一行

group_concat 将数据拼成一行

substr && substring

从第一位开始截取两位，mysql 从 1 开始下标

1
2

select substring('aaaaa',1,2)   // aa
select substr('aaaaaa',1,1)

删除表中所有数据

1
2
3
4

delete from stu where id in(1,2,3);
truncate table stu;
delete from tb1 where name rlike '^t.*';
[^abc] 匹配 或关系

更新

1

update yanchuang set name='aaa',age=333 where id = 1

修改表结构

1
2

alter table aaa add bbb int(10) not null after id;
alter table ccc add bbb int(10) not null first;

修改字段类型

1

alter table aaa modify no int not null

修改字段类型和内容

1

alter table chenke change stu students int not null

# ACID

原子性

一致性

持久性

隔离性

# 隔离级别

transaction-isolation =

transaction-isolation 默认为可重复读 REPEATABLE-READ

5.7 以前为 tx_isolation，之后变为 transcation_isolation

开启事务

1

begin /  start transaction

READ-UNCOMMITTED

该隔离级别下允许一个事务读取到其它事务未提交的写操作

数据不一致 - 脏读 - 读未提交造成的问题 (read-uncommitted)，即不提交也会使数据改变

1
2
3
4

begin 										begin
select a  //100								select a  //100
											update a = 200
select a  //200

读已提交 (read-committed) 未提交时不会收到脏数据污染，但提交后还是会受污染，造成幻读（不可重复读）

1
2
3
4
5
6
7

begin											begin
select a   //200								select a    //200
												update a = 300 
												select a    //300
select a   //200
												commit
select a   //300

repeatable-read 可重复读，修改后无论是否提交都不会改变查询结果，当两端都修改了数据后，会造成最先修改的一方的更新丢失

1
2
3
4
5
6
7

begin											begin
select a   //300								select a    //300
												update a = 400 		
select a   //300								select a    //400 
												commit
select a   //300
commit

1
2
3
4
5
6
7
8

begin											begin
select a   //400								select a    //400
												update a = 500
select a   //400								select a    //500
												commit
update a = 600
commit											
select a	//600								select a    //600

serialize

该隔离级别下很好的解决了 lost udpate 的问题

读时加锁，别的进程不能修改，效率慢

MySQL 提供了四种不同的隔离级别，分别是：read-uncommit、read-commit、repeated-read 和 serializable，后三种隔离级别分别结果了脏读、幻读、lost update 的问题。虽然 serializable 解决了全部的问题，但是实际运行时它的性能是最差的。所以日常生产环境中我们一般使用 read-commit、repeated-read 两种隔离级别，既能解决一些严重的不一致问题又能保持 MySQL 比较高的性能。

解决更新丢失

对于使用 InnoDB 存储引擎的表，其聚簇索引记录中包含了两个重要的隐藏列：

trx_id：每当事务对聚簇索引中的记录进行修改时，都会把当前事务的事务 id 记录到 trx_id 中。

roll_pointer：每当事务对聚簇索引中的记录进行修改时，都会把该记录的旧版本记录到 undo 日志中，通过 roll_pointer 这个指针可以用来获取该记录旧版本的信息。

如果在一个事务中多次对记录进行修改，则每次修改都会生成 undo 日志，并且这些 undo 日志通过 roll_pointer 指针串联成一个版本链，版本链的头结点是该记录最新的值，尾结点是事务开始时的初始值。

1
2
3

BEGIN;
UPDATE book SET stock = 200 WHERE id = 1;
UPDATE book SET stock = 300 WHERE id = 1;

undo log 记录回滚指针，可以在回滚的时候恢复原始数据，

事务隔离界别通过快照实现，但仅针对读已提交和可重复读

对于使用 Read Uncommitted 隔离级别的事务来说，只需要读取版本链上最新版本的记录即可；

对于使用 Serializable 隔离级别的事务来说，InnoDB 使用加锁的方式来访问记录；

事务 id 是递增分配的。ReadView 的机制就是在生成 ReadView 时确定了以下几种信息：

1
2
3
4
5
6
7

m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。

min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。

max_trx_id：表示生成ReadView时系统中将要分配给下一个事务的id值。

creator_trx_id：表示生成该ReadView的事务的事务id。

这样事务 id 就可以分成 3 个区间：

区间 (0, min_trx_id)：如果被访问版本的 trx_id 小于 m_ids 中的最小值 up_limit_id，说明生成该版本的事务在 ReadView 生成前就已经提交了，所以该版本可以被当前事务访问

区间 [min_trx_id, max_trx_id): 如果被访问版本的 trx_id 属性值在 m_ids 列表中最大值和最小值之间（包含），那就需要判断一下 trx_id 的值是不是在 m_ids 列表中。如果在，说明创建 ReadView 时生成该版本所属事务还是活跃的，因此该版本不可以被访问，需要查找 Undo Log 链得到上一个版本，然后根据该版本的 DB_TRX_ID 再从头计算一次可见性；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

区间 [max_trx_id, +∞)：如果被访问版本的 trx_id 大于 m_ids 列表中的最大值 low_limit_id，说明生成该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。需要根据 Undo Log 链找到前一个版本，然后根据该版本的 DB_TRX_ID 重新判断可见性

之前说到 ReadView 的机制只在 Read Committed 和 Repeatable Read 隔离级别下生效，所以只有这两种隔离级别才有 MVCC。在 Read Committed 隔离级别下，每次读取数据时都会生成 ReadView；而在 Repeatable Read 隔离级别下只会在事务首次读取数据时生成 ReadView，之后的读操作都会沿用此 ReadView。

Read Committed 下 MVCC 工作原理

在提交前 [min_trx_id, max_trx_id) 在活跃别表中，读的是快照，提交后 (0, min_trx_id) 事务不再活跃，可以访问最新记录

因为每次执行查询语句都会生成新的 ReadView，所以在 Read Committed 隔离级别下的事务读取到的是查询时刻表中已提交事务修改之后的数据。

Repeatable read 下 MVCC 工作原理

当第二次执行 SELECT 语句时不会生成新的 ReadView，依然会使用第一次查询时生成 ReadView。因此我们查询到的版本记录跟第一次查询到的结果是一样的：

只会查询出最开始的视图快照

普通读 / 快照读

普通读（也称快照读，英文名：Consistent Read），就是单纯的 SELECT 语句

快照读用于只能获得到快照的数据，不能获得最新数据 通过 undo log + mvcc 实现

事务会先使用 “排他锁” 锁定该行，将该行当前的值复制到 undo log 中，然后再真正地修改当前行的值，最后填写事务的 DB_TRX_ID ，使用回滚指针 DB_ROLL_PTR 指向 undo log 中修改前的行。

这里解释一下 DB_TRX_ID 和 DB_ROLL_PTR 所代表的含义：

• DB_TRX_ID : 6 字节 DB_TRX_ID 字段，表示最后更新的事务 id (update , delete , insert) 。此外，删除在内部被视为更新，其中行中的特殊位被设置为将其标记为已软删除。
• DB_ROLL_PTR : 7 字节回滚指针，指向前一个版本的 undo log 记录，组成 undo 链表。如果更新了行，则撤消日志记录包含在更新行之前重建行内容所需的信息。

解决快照读：永远读取最新的数据 - 当前读 ，当前读通过加锁

修改数据时一定会加锁，select 通过 for update 加锁 ，即悲观锁

select * from money where id=1 for update

1
2
3
4
5
6

begin															begin
select a for update   //800										select a for update //被加锁，卡主
update a = 900													
																update a = 1000  // 被加锁，只要第一个进程不commit，这啥都干不了
commit			
																update 锁被释放，update执行

线程 1 加锁时线程 2 只能不加锁的 select

当前读

当前读，读取的是最新版本，并且需要先获取对应记录的锁

1
2
3

select ... lock in share mode 、
select ... for update、
update 、delete 、insert 

当前读是通过 next-key 锁 (行记录锁 + 间隙锁) 来是实现的。

行锁（Record Lock）：锁直接加在索引记录上面。

间隙锁（Gap Lock）：是 Innodb 为了解决幻读问题时引入的锁机制，所以只有在 Read Repeatable 、Serializable 隔离级别才有。

Next-Key Lock ：Record Lock + Gap Lock，锁定一个范围并且锁定记录本身 。

测试可知 delete from T where age = 7; 语句在 age 上的加锁区间为 (4,10) , 图解如下

乐观锁

通过增加额外字段实现

# redo log

事务的原子性、一致性和持久性由事务的 redo 日志和 undo 日志来保证。

配置了 autocommit 后会自动提交，只有显示进入 begin 或者关闭该选项后手动提交

1
2
3
4
5
6
7

第1步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝

第2步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值

第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加 写的方式

第4步：定期将内存中修改的数据刷新到磁盘中

注意，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 中。它支持三种策略：

1
2
3
4
5

设置为0 ：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日志的同步）

设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）

设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。

=0 系统崩溃会丢失一秒数据

=2 在极端环境下会丢失一秒数据，只写入内存

<=5.5MyISAM 不支持 redolog

redo log crash safe writepos checkpoint

binlog 和 redolog 差异

在记录 1 刷盘后，记录 2 未刷盘时，数据库 crash。重启后，只通过 binlog 数据库无法判断这两条记录哪条已经写入磁盘哪条没有写入磁盘，不管是两条都恢复至内存，还是都不恢复，对 ID=2 这行数据来说，都不对。

通过插入大量数据可以查看三种刷盘策略的不同

1
2
3
4
5
6
7
8

(root@localhost) [hellodb]> call sp_testlog;
Query OK, 1 row affected (45.36 sec) #用时45秒

(root@localhost) [hellodb]> begin;call sp_testlog;commit;
#我们看到将存储过程call sp_testlog作为一个事务来说，其内部的十万条insert合起来一个事务的所有操作。
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (4.01 sec)
Query OK, 0 rows affected (0.49 sec)

备份

差异备份 - 和完全备份有多少区别就备份多少

全量备份

增量备份

mysqldump -uroot -p123 test > /tmp/bak.sql

mysqldump -uroot -p123 --databases test test1 > /tmp/bak.sql

xtrabackup

# binlog

Binlog 用于对还没没备份的数据进行恢复。它记录了所有的 DDL (create alter drop) 和 DML 语句（除了数据查询语句 select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。

mysqlbinlog 常见的选项有以下几个：
–start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间
–stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地服务器的时间 取值和上述一样
–start-position：从二进制日志中读取指定 position 事件位置作为开始。
–stop-position：从二进制日志中读取指定 position 事件位置作为事件截至

binlog 日志有两个最重要的使用场景
1）MySQL 主从复制：MySQL Replication 在 Master 端开启 binlog，Master 把它的二进制日志传递给 slaves 来达到
master-slave 数据一致的目的。
2）自然就是数据恢复了，通过使用 mysqlbinlog 工具来使恢复数据。

binlog 日志包括两类文件
1）二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件
2）二进制日志文件（文件名后缀为.00000*）记录数据库所有的 DDL 和 DML (除了数据查询语句 select) 语句事件。

开启：在配置文件中加入以下内容

[root@vm-002 ~]# vim /etc/my.cnf

log_bin = mysql_bin // 给 binlog 日志取名，并开启 binlog

每次服务器（数据库）重启，服务器会调用 flush logs;，新创建一个 binlog 日志！

flush logs; flush 刷新 log 日志，自此刻开始产生一个新编号的 binlog 日志文件

使用 mysqlbinlog 自带查看命令法：

–>binlog 是二进制文件，普通文件查看器 cat、more、vim 等都无法打开，必须使用自带的 mysqlbinlog 命令查看

–>binlog 日志与数据库文件在同目录中

–> 在 MySQL5.5 以下版本使用 mysqlbinlog 命令时如果报错，就加上 “–no-defaults” 选项

binlog_format 修改该变量

二进制日志有 3 种记录方式，三种方式如下：

statement 模式：记录对数据库做出修改的语句，比如，update A set test=’test’, 如果使用 statement 模式，那么这条 update 语句将会被记录到二进制日志中，使用 statement 模式时，优点是 binlog 日志量少，IO 压力小，性能较高，缺点是为了能够尽量的完全一致的还原操作，除了记录语句本身以外，可能还需要记录一些相关的信息，而且，在使用一些特定的函数时，并不能保证恢复操作与记录时完全一致。

row 模式：记录对数据库做出修改的语句所影响到的数据行以及这些行的修改，比如，update A set test=’test’，如果使用 row 模式，那么这条 update 语句所影响到的行所对应的修改，将会记录到 binlog 中，比如，A 表中有 1000 条数据，那么当执行这条 update 语句以后，由于 1000 条数据都会被修改，所以会有 1000 行数据被记录到二进制日志中，以及它们是怎样被修改的，使用 row 模式时，优点是能够完全的还原或者复制日志被记录时的操作，缺点是记录日志量较大，IO 压力大，性能消耗较大。

mixed 模式：混合使用上述两种模式，一般的语句使用 statment 方式进行保存，如果遇到一些特殊的函数，则使用 row 模式进行记录，这种记录方式被称之为 mixed，看上去这种方式似乎比较美好，但是在生产环境中，为了保险起见，一般会使用 row 模式。

DDL - create alter drop

DML - update，insert，delete

show master log // 查看最新生成 binlog

show binlog events in ‘mysqlbin.0000001’ // 以位置方式查看 binlog

mysqlbinlog mysqlbin.0000001 // 以时间方式查看 binlog

​

mysqlbinlog mysqllog000003 > 00003.sql

vim 00003.sql 查看 binlog 信息

mysql -uroot -p123456 < 00003.sql 恢复 binlog

flush log 刷新创建新 binlog

基于位置信息恢复

1

mysqlbinlog --start-position=660 --stop-position=773 --database=haha mysqlbin.00003 | mysql -uroot -p123456 -v

基于时间信息恢复

[root@vm-002 backup]# cp /var/lib/mysql/mysql-bin.000003 /opt/backup
[root@vm-002 backup]# mysqlbinlog /opt/backup/mysql-bin.000003 > /opt/backup/000003.sql
[root@vm-002 backup]# vim /opt/backup/000003.sql #删除里面的 drop 语句
[root@vm-002 backup]# mysql -uroot -p -v < /opt/backup/000003.sql