浏览文档:小林coding
了解Mysql
查询语句执行流程
- Server 层负责建立连接、分析和执行 SQL
- 存储引擎层负责数据的存储和提取
连接器
MySQL 是基于 TCP 协议进行传输
# 登录命令
mysql -h$ip -u$user -p;
# 查看被多少个客户端连接
show processlist;
# 空闲连接的最大空闲时长
show variables like 'wait_timeout';
# 手动断开空闲连接
kill connection + id;
# 最大连接数
show variables like 'max_connections';
解决长连接占用内存问题
- 定期断开长连接
- 客户端主动重置连接
查询缓存
如果 SQL 是查询语句(select 语句),MySQL 就会先去查询缓存( Query Cache )里查找缓存数据,看看之前有没有执行过这一条命令,这个查询缓存是以 key-value 形式保存在内存中的,key 为 SQL 查询语句,value 为 SQL 语句查询的结果。
对于更新比较频繁的表,查询缓存的命中率很低的,因为只要一个表有更新操作,那么这个表的查询缓存就会被清空。所以,MySQL 8.0 版本直接将查询缓存删掉了。
解析SQL
解析器只负责构建语法树和检查语法,但是不会去查表或者字段存不存在。
- 词法分析。构建出 SQL 语法树,这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。
- 语法分析。根据词法分析的结果,判断SQL是否正确,错误则报错。
执行SQL
三阶段
-
prepare 阶段,也就是预处理阶段
-
optimize 阶段,也就是优化阶段
-
execute 阶段,也就是执行阶段
预处理器
- 检查 SQL 查询语句中的表或者字段是否存在(不存在则报错)
- 将
select *
中的*
符号,扩展为表上的所有列
优化器
优化器主要负责将 SQL 查询语句的执行方案确定下来(制定一个执行计划)(比如在表里面有多个索引的时候,优化器会基于查询成本的考虑,来决定选择使用哪个索引)。
select id from product where id > 1 and name like 'i%';
如果查询语句既有主键索引id,也有普通索引name,优化器会决定来使用哪个索引。
执行器
根据执行计划执行 SQL 查询语句,从存储引擎读取记录,返回给客户端(执行器查询是一个while循环)
- 主键索引查询:执行器把主键查询条件交给存储引擎,查出唯一一条记录(让存储引擎定位符合条件的第一条记录)
- 全表扫描:执行器让存储引擎读取表中第一条记录集,执行器会这条记录是否符合要求;然后一直循环,直到记录读完
- 索引下推:将判断条件交给了存储引擎层(在判断第一个条件后,不回表,由存储引擎层判断第二个条件是否成立)
存储记录原理
MySQL 数据库的文件存放目录
# /var/lib/mysql/
SHOW VARIABLES LIKE 'datadir';
/var/lib/mysql/数据库
目录中有三个文件
db.opt
用来存储当前数据库的默认字符集和字符校验规则。table.frm
table的表结构会保存在这个文件。table.ibd
(独占表空间文件)table的表数据会保存在这个文件。
表空间文件的结构
表空间由段(segment)、区(extent)、页(page)、行(row)组成
-
行(row)
- 数据库表中的记录都是按行(row)进行存放的,每行记录根据不同的行格式,有不同的存储结构。
-
页(page)
- InnoDB 的数据是按「页」为单位来读写的(按行读效率太低)
- 默认每个页的大小为 16KB
- 页的类型有很多,常见的有数据页、undo 日志页、溢出页等等。
-
区(extent)
- 让链表中相邻的页的物理位置也相邻
- 在表中数据量大的时候,为某个索引分配空间的时候就不再按照页为单位分配了,而是按照区(extent)为单位分配。每个区的大小为 1MB,对于 16KB 的页来说,连续的 64 个页会被划为一个区,这样就使得链表中相邻的页的物理位置也相邻,就能使用顺序 I/O 了。
-
段(segment)
- 表空间是由各个段(segment)组成的,段是由多个区(extent)组成的。
- 段一般分为数据段(存放 B + 树的叶子节点的区的集合)、索引段(存放 B + 树的非叶子节点的区的集合)和回滚段(存放的是回滚数据的区的集合)等
InnoDB的行格式
InnoDB 提供了 4 种行格式,分别是 Redundant(古老)、Compact(紧凑)、Dynamic(基于 Compact 改进)和 Compressed(基于 Compact 改进) 行格式。(MySQL 5.1 版本之后,行格式默认设置成 Compact;MySQL5.7 版本之后,默认使用 Dynamic 行格式)
COMPACT行格式
记录的额外信息
- 变长字段长度列表(varchar(n))
- 这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放
- NULL 是不会存放在行格式中记录的真实数据部分里的
# 例子
# 表
# id name phone age (其中name和phone是变长字段)
# 变长字段长度列表中会存如下:
# phone列占用的字节数 name列占用的字节数
变长字段长度列表」的信息要按照逆序存放
- 主要是因为「记录头信息」中指向下一个记录的指针,指向的是下一条记录的「记录头信息」和「真实数据」之间的位置,这样的好处是向左读就是记录头信息,向右读就是真实数据。使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中,这样就可以提高 CPU Cache 的命中率。
每个数据库表的行格式都有「变长字段字节数列表」吗?
- 「变长字段长度列表」只出现在数据表有变长字段的时候(如果没有变长字段,是没必要的,还可以节省空间)
- NULL值列表
- 二进制位的值为
1
时,代表该列的值为NULL - 二进制位的值为
0
时,代表该列的值不为NULL
- 二进制位的值为
每个数据库表的行格式都有「NULL 值列表」吗?
当数据表的字段都定义成 NOT NULL 的时候,这时候表里的行格式就不会有 NULL 值列表了。(在设计数据库表的时候,通常都是建议将字段设置为 NOT NULL)
「NULL 值列表」是固定 1 字节空间吗?如果这样的话,一条记录有 9 个字段值都是 NULL,这时候怎么表示?
「NULL 值列表」的空间不是固定 1 字节的。(如果当前记录有9个字段是NULL,会创建2字节的NULL值列表,以此类推)
- 记录头信息
- delete_mask:标识此条数据是否被删除(类比软删除)
- next_record:下一条记录的位置
- record_type:表示当前记录的类型,0表示普通记录,1表示B+树非叶子节点记录,2表示最小记录,3表示最大记录
- ……
记录的真实数据
- row_id:如果我们建表的时候指定了主键或者唯一约束列,那么就没有 row_id 隐藏字段。(row_id不是必需的,占用 6 个字节)
- trx_id:事务id,表示这个数据是由哪个事务生成的。 (trx_id是必需的,占用 6 个字节)
- roll_pointer:这条记录上一个版本的指针。(roll_pointer 是必需的,占用 7 个字节)
varchar(n)中n最大取值
MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外,其他所有的列(不包括隐藏列和记录头信息)占用的字节长度加起来不能超过 65535 个字节。
- 一行数据的最大字节数 65535,其实是包含「变长字段长度列表」和 「NULL 值列表」所占用的字节数的
如果只有一个字段,且该字段是varchar(n)类型,那么最大取值是65532(字符集为 ascii,如果是utf-8需要除以3)
无论该记录是单字段还是多字段都要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535
行溢出
Compact行格式:
当发生行溢出时,在记录的真实数据处只会保存该列的一部分数据,而把剩余的数据放在「溢出页」中,然后真实数据处用 20 字节存储指向溢出页的地址,从而可以找到剩余数据所在的页。
Compressed 和 Dynamic 这两个行格式:
这两种格式采用完全的行溢出方式,记录的真实数据处不会存储该列的一部分数据,只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。
索引
索引概念
索引是数据的目录
索引分类
- 按「数据结构」分类:B+tree索引、Hash索引、Full-text索引。
- 按「物理存储」分类:聚簇索引(主键索引)、二级索引(辅助索引)。
- 按「字段特性」分类:主键索引、唯一索引、普通索引、前缀索引。
- 按「字段个数」分类:单列索引、联合索引。
数据结构分类
InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎,B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。
InnoDB 存储引擎会根据不同的场景选择不同的列作为索引:
- 如果有主键,默认会使用主键作为聚簇索引的索引键(key);
- 如果没有主键,就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键(key);
- 在上面两个都没有的情况下,InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键(key);
其它索引都属于辅助索引(Secondary Index),也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。
- 主键查询数据
select * from table where id= 5;
(叶子节点之间是双向链表)
通过范围查找到最终数据
B+Tree 相比于 B 树和二叉树来说,最大的优势在于查询效率很高,因为即使在数据量很大的情况,查询一个数据的磁盘 I/O 依然维持在 3-4次。
- 二级索引查询数据
select * from table where table_no = '0002';
(叶子节点之间是双向链表)
这种sql语句会先检索二级索引,找到其中的主键id,然后回表,去查询主键索引的树,找到最终数据
在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」,也就是只需要查一个 B+Tree 就能找到数据。
为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构?
- 相比于b tree 相同的磁盘 I/O 次数下,就能查询更多的节点(因为b+只有叶子节点存数据)
- 相比于二叉,它只要3~4次io操作,远优于二叉
- 相比于hash,它具有更广泛的应用场景(hash是等值查询,b+是范围查询)
物理存储分类
- 主键索引的 B+Tree 的叶子节点存放的是实际数据,所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里。
- 二级索引的 B+Tree 的叶子节点存放的是主键值,而不是实际数据。
字段特性分类
- 主键索引(PRIMARY KEY)(一张表最多只有一个主键索引,索引列的值不允许有空值)
- 唯一索引(UNIQUE KEY;UNIQUE INDEX)(一张表可以有多个唯一索引,索引列的值必须唯一,但是允许有空值)
- 普通无影(INDEX)(既不要求字段为主键,也不要求字段为 UNIQUE)
- 前缀索引(INDEX(column_name(length))……)(可以建立在字段类型为 char、 varchar、binary、varbinary 的列上)
字段个数分类
- 单列索引
- 联合索引(复合索引)
联合索引
# 下方的sql语句就是将product_no和name组合成联合索引
CREATE INDEX index_product_no_name ON product(product_no, name);
使用联合索引时,存在最左匹配原则
- 假如创建了一个
(a, b, c)
联合索引,是先按 a 排序,在 a 相同的情况再按 b 排序,在 b 相同的情况再按 c 排序。所以,b 和 c 是全局无序,局部相对有序的,这样在没有遵循最左匹配原则的情况下,是无法利用到索引的。
联合索引范围查询
并不是查询过程使用了联合索引查询,就代表联合索引中的所有字段都用到了联合索引进行索引查询
联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引,但是在范围查询字段的后面的字段无法用到联合索引。
例子:
select * from t_table where a > 1 and b = 2
,联合索引(a, b)哪一个字段用到了联合索引的 B+Tree?
在符合 a > 1 条件的二级索引记录的范围里,b 字段的值是无序的。
只有 a 字段用到了联合索引进行索引查询,而 b 字段并没有使用到联合索引。
- 如果是a大于等于1,就是都用到了联合索引
总结:联合索引的最左匹配原则,在遇到范围查询(如 >、<)的时候,就会停止匹配,也就是范围查询的字段可以用到联合索引,但是在范围查询字段的后面的字段无法用到联合索引。注意,对于 >=、<=、BETWEEN、like 前缀匹配的范围查询,并不会停止匹配
索引下推
MySQL 5.6 引入的索引下推优化, 可以在联合索引遍历过程中,对联合索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
索引区分度
建立联合索引时,要把区分度大的字段排在前面,这样区分度大的字段越有可能被更多的 SQL 使用到。
区分度 = 某个字段column不同值的个数/表的总行数
问题
数据页角度看B+树
InnoDB存储数据
InnoDB 的数据是按「数据页」为单位来读写的,InnoDB 数据页的默认大小是 16KB
作用如下:
在 File Header 中有两个指针,分别指向上一个数据页和下一个数据页,连接起来的页相当于一个双向的链表
数据页中的记录按照「主键」顺序组成单向链表,单向链表的特点就是插入、删除非常方便,但是检索效率不高,最差的情况下需要遍历链表上的所有节点才能完成检索。所以需要目录,即索引
页目录创建的过程如下:
- 将所有的记录划分成几个组,这些记录包括最小记录和最大记录,但不包括标记为“已删除”的记录;
- 每个记录组的最后一条记录就是组内最大的那条记录,并且最后一条记录的头信息中会存储该组一共有多少条记录,作为 n_owned 字段(上图中粉红色字段)
- 页目录用来存储每组最后一条记录的地址偏移量,这些地址偏移量会按照先后顺序存储起来,每组的地址偏移量也被称之为槽(slot),每个槽相当于指针指向了不同组的最后一个记录。
从图中可以看出,页目录就是由多个槽组成的,槽相当于分组记录的索引。然后,因为记录是按照「主键值」从小到大排序的,所以我们通过槽查找记录时,可以使用二分法快速定位要查询的记录在哪个槽(哪个记录分组),定位到槽后,再遍历槽内的所有记录,找到对应的记录,无需从最小记录开始遍历整个页中的记录链表。
最小记录可以从上一个最大记录加一得到
B+树
上面我们都是在说一个数据页中的记录检索,因为一个数据页中的记录是有限的,且主键值是有序的,所以通过对所有记录进行分组,然后将组号(槽号)存储到页目录,使其起到索引作用,通过二分查找的方法快速检索到记录在哪个分组,来降低检索的时间复杂度。
但是,当我们需要存储大量的记录时,就需要多个数据页,这时我们就需要考虑如何建立合适的索引,才能方便定位记录所在的页。
所以,InnoDB 采用了 B+ 树作为索引
- 只有叶子节点(最底层的节点)才存放了数据,非叶子节点(其他上层节)仅用来存放目录项作为索引。
- 非叶子节点分为不同层次,通过分层来降低每一层的搜索量;
- 所有节点按照索引键大小排序,构成一个双向链表,便于范围查询;
一点点分下去就查询到需要的数据了
聚簇索引和二级索引
如果某个查询语句使用了二级索引,但是查询的数据不是主键值,这时在二级索引找到主键值后,需要去聚簇索引中获得数据行,这个过程就叫作「回表」,也就是说要查两个 B+ 树才能查到数据。不过,当查询的数据是主键值时,因为只在二级索引就能查询到,不用再去聚簇索引查,这个过程就叫作「索引覆盖」,也就是只需要查一个 B+ 树就能找到数据。
Mysql为什么采用B+树
设计一个适合 MySQL 索引的数据结构,至少满足以下要求:
- 能在尽可能少的磁盘的 I/O 操作中完成查询工作;
- 要能高效地查询某一个记录,也要能高效地执行范围查找;
二分查找
二分查找法每次都把查询的范围减半,这样时间复杂度就降到了 O(logn),但是每次查找都需要不断计算中间位置。
二分查找树
二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点,右子树的所有节点都大于这个节点,这样我们在查询数据时,不需要计算中间节点的位置了,只需将查找的数据与节点的数据进行比较。
但是有一个问题:
当每次插入的元素都是二叉查找树中最大的元素,二叉查找树就会退化成了一条链表,查找数据的时间复杂度变成了 O(n)
由于树是存储在磁盘中的,访问每个节点,都对应一次磁盘 I/O 操作,也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数,所以树的高度越高,就会影响查询性能。
自平衡二叉树
为了解决二叉查找树会在极端情况下退化成链表的问题,后面就有人提出平衡二叉查找树(AVL 树)。就是增加条件约束:每个节点的左子树和右子树的高度差不能超过 1。
当树的节点越多的时候,并且树的分叉数 M 越大的时候,M 叉树的高度会远小于二叉树的高度。所以后面会使用b树。
B树
-
为了解决降低树的高度的问题,后面就出来了 B 树,它不再限制一个节点就只能有 2 个子节点,而是允许 M 个子节点 (M>2),从而降低树的高度。
-
但是 B 树的每个节点都包含数据(索引+记录),而用户的记录数据的大小很有可能远远超过了索引数据,这就需要花费更多的磁盘 I/O 操作次数来读到「有用的索引数据」。
B+树
MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构,原因有:
- B+ 树的非叶子节点不存放实际的记录数据,仅存放索引,因此数据量相同的情况下,相比存储即存索引又存记录的 B 树,B+树的非叶子节点可以存放更多的索引,因此 B+ 树可以比 B 树更「矮胖」,查询底层节点的磁盘 I/O次数会更少。
- B+ 树有大量的冗余节点(所有非叶子节点都是冗余索引),这些冗余索引让 B+ 树在插入、删除的效率都更高,比如删除根节点的时候,不会像 B 树那样会发生复杂的树的变化;
- B+ 树叶子节点之间用链表连接了起来,有利于范围查询,而 B 树要实现范围查询,因此只能通过树的遍历来完成范围查询,这会涉及多个节点的磁盘 I/O 操作,范围查询效率不如 B+ 树。
Mysql单表不要超过2000w行?
- MySQL 的表数据是以页的形式存放的,页在磁盘中不一定是连续的。
- 页的空间是 16K, 并不是所有的空间都是用来存放数据的,会有一些固定的信息,如,页头,页尾,页码,校验码等等。
- 在 B+ 树中,叶子节点和非叶子节点的数据结构是一样的,区别在于,叶子节点存放的是实际的行数据,而非叶子节点存放的是主键和页号。
- 索引结构不会影响单表最大行数,2000W 也只是推荐值,超过了这个值可能会导致 B + 树层级更高,影响查询性能。
索引失效
主键索引和二级索引的存储方式在上面说过了
对索引使用模糊匹配
当我们使用左或者左右模糊匹配的时候,也就是 like %xx
或者 like %xx%
这两种方式都会造成索引失效。(like xx%
是前缀索引吧)
为什么like关键字做或者左右模糊匹配无法走索引呢?
因为索引 B+ 树是按照「索引值」有序排列存储的,只能根据前缀进行比较。
Mysql使用like “%x”,索引一定会失效吗?
- 例子:
- 如果表中只有两个字段,分别是主键索引id和二级索引name,然后使用like模糊查询所有字段
- 那么,使用%xx和%xx%,索引不会失效
- 因为只有两个字段,没有非索引字段。然后这个查询的数据都在二级索引的 B+ 树,因为二级索引的 B+ 树的叶子节点包含「索引值+主键值」,所以查二级索引的 B+ 树就能查到全部结果了,这个就是覆盖索引。(但是,是通过全扫描二级索引的 B+ 树的方式查询到数据的,也就是遍历了整颗索引树。)
- 为什么选择全扫描二级索引树,而不扫描聚簇索引树呢?
- 因为二级索引树的记录东西很少,就只有「索引列+主键值」,而聚簇索引记录的东西会更多
- 为什么这个数据表加了非索引字段,执行同样的查询语句后,怎么变成走的是全表扫描呢?
- 因为这个时候,需要查询出来的数据,不是全在索引树中;需要回表查询,优化器就会认为成本太高,就使用全表扫描了。
对索引使用函数
如果查询条件中对索引字段使用函数,就会导致索引失效。
# 例子
select * from t_user where length(name)=6;
为什么对索引使用函数,就无法走索引了呢?
因为索引保存的是索引字段的原始值,而不是经过函数计算后的值,自然就没办法走索引了。
不过,从 MySQL 8.0 开始,索引特性增加了函数索引,即可以针对函数计算后的值建立一个索引,也就是说该索引的值是函数计算后的值,所以就可以通过扫描索引来查询数据。
对索引进行表达式计算
在查询条件中对索引进行表达式计算,也是无法走索引的。
# 例子
explain select * from t_user where id + 1 = 10;
原因类似对索引使用函数,因为计算了
对索引隐式类型转换
如果索引字段是字符串类型,但是在条件查询中,输入的参数是整型的话,你会在执行计划的结果发现这条语句会走全表扫描。
如:字符串类型在sql语句中使用整型匹配(但是整型使用字符串是不会失效的)
# 字符串类型使用整型匹配,其中phone是字符串类型
select * from t_user where phone = 1300000001;
# CAST(phone AS signed int)
# 如果把phone转为整型,会使用函数,所以会导致索引失效
原因:MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较。
联合索引非最左匹配
多个普通字段组合在一起创建的索引就叫做联合索引,也叫组合索引
(a, b, c)
联合索引只使用a和c,会出现索引截断。MySQL5.5的会在判断a后回表给server层判断c;之后的版本会索引下推来判断。
为什么联合索引不遵循最左匹配原则就会失效?
原因是,在联合索引的情况下,数据是按照索引第一列排序,第一列数据相同时才会按照第二列排序。
WHERE子句中的OR
在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。
因为 OR 的含义就是两个只要满足一个即可,因此只有一个条件列是索引列是没有意义的,只要有条件列不是索引列,就会进行全表扫描。
count(*)和count(1)的区别和性能差距
性能:count(*) = count(1) > count(主键字段) > count(字段)
count()
count() 是一个聚合函数,函数的参数不仅可以是字段名,也可以是其他任意表达式,该函数作用是统计符合查询条件的记录中,函数指定的参数不为 NULL 的记录有多少个。
count(主键字段)
MySQL 的 server 层会维护一个名叫 count 的变量。
- server 层会循环向 InnoDB 读取一条记录,如果 count 函数指定的参数不为 NULL,那么就会将变量 count 加 1,直到符合查询的全部记录被读完,就退出循环。最后将 count 变量的值发送给客户端。
如果存在二级索引,则会走二级索引;因为二级索引记录占用空间小于同等记录的聚簇索引
count(1)
InnoDB 循环遍历聚簇索引(主键索引),将读取到的记录返回给 server 层,但是不会读取记录中的任何字段的值,因为 count 函数的参数是 1,不是字段,所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL,因此 server 层每从 InnoDB 读取到一条记录,就将 count 变量加 1。
count(*)
count(\*
) 其实等于 count(0
),也就是说,当你使用 count(*
) 时,MySQL 会将 *
参数转化为参数 0 来处理。
- 所以,count(*) 执行过程跟 count(1) 执行过程基本一样的,性能没有什么差异。
而且 MySQL 会对 count(*) 和 count(1) 有个优化,如果有多个二级索引的时候,优化器会使用key_len 最小的二级索引进行扫描。
count(字段)
count(字段) 的执行效率相比前面的 count(1)、 count(*)、 count(主键字段) 执行效率是最差的。
- 对于这个查询来说,会采用全表扫描的方式来计数,所以它的执行效率是比较差的。
不要使用 count(字段) 来统计记录个数,因为它的效率是最差的,会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数,建议给这个字段建立一个二级索引。
count()使用遍历
原因: InnoDB 存储引擎是支持事务的,同一个时刻的多个查询,由于多版本并发控制(MVCC)的原因,InnoDB 表“应该返回多少行”也是不确定的。
在使用 InnoDB 存储引擎时,就需要扫描表来统计具体的记录。避免查询结果出现错误(这个错误就是幻读等)。
优化count(*)
如果对一张大表经常用 count(*) 来做统计,其实是很不好的。
第一种,近似值
如果你的业务对于统计个数不需要很精确,比如搜索引擎在搜索关键词的时候,给出的搜索结果条数是一个大概值。
这时,我们就可以使用 show table status 或者 explain 命令来表进行估算。
第二种,额外表保存计数值
如果是想精确的获取表的记录总数,我们可以将这个计数值保存到单独的一张计数表中。
当我们在数据表插入一条记录的同时,将计数表中的计数字段 + 1。也就是说,在新增和删除操作时,我们需要额外维护这个计数表。
事务
锁
种类
在 MySQL 里,根据加锁的范围,可以分为全局锁、表级锁和行锁三类。
全局锁
# 使用全局锁后,整个数据库就处于只读状态了
# 无法对数据增删改,也无法更改表结构
flush tables with read lock
# 释放全局锁
unlock tables
全局锁应用场景
全局锁主要应用于做全库逻辑备份,这样在备份数据库期间,不会因为数据或表结构的更新,而出现备份文件的数据与预期的不一样。
原因:
- 在备份数据的时候,如果有人更改数据,会造成备份数据的紊乱
全局锁缺点和避免缺点
缺点:
- 加上全局锁,意味着整个数据库都是只读状态。在业务进行时备份,会造成业务停滞
避免缺点:
- InnoDB引擎支持的事务支持可重复读的隔离级别,那么在备份数据库之前先开启事务,会先创建 Read View,然后整个事务执行期间都在用这个 Read View,而且由于 MVCC 的支持,备份期间业务依然可以对数据进行更新操作。
- 使用 mysqldump 时加上
–single-transaction
参数的时候,就会在备份数据库之前先开启事务。 - MyISAM 这种不支持事务的引擎,在备份数据库时就要使用全局锁的方法。
表级锁
种类:
- 表锁、元数据锁(MDL)、意向锁、AUTO-INC 锁
表锁
# 表级别的共享锁,也就是读锁;
lock tables t_s read;
# 表级别的独占锁,也就是写锁;
lock tables t_s write;
# 释放当前会话所有的表锁
unlock tables
-
表锁除了会限制别的线程的读写外,也会限制本线程接下来的读写操作,知道锁被释放。
-
尽量避免在使用 InnoDB 引擎的表使用表锁,因为表锁的颗粒度太大,会影响并发性能,InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。
元数据锁
不需要显示的使用 MDL,因为当我们对数据库表进行操作时,会自动给这个表加上 MDL:
- 对一张表进行 CRUD 操作时,加的是 MDL 读锁
- 对一张表做结构变更操作的时候,加的是 MDL 写锁
MDL 是为了保证当用户对表执行 CRUD 操作时,防止其他线程对这个表结构做了变更。
- 当执行CRUD操作时,会阻塞更改表的操作,直到CRUD操作执行完毕
- 当更改表时,会阻塞CRUD操作,直到更改表完成
MDL什么时候释放?
MDL 是在事务提交后才会释放,这意味着事务执行期间,MDL 是一直持有的。
- 如果线程1启动了事务,并执行查询语句,就会给表加上MDL读锁;这时线程2修改了该表字段,但是由于线程1事务一直没有提交,线程2就无法申请到MDL写锁,就会阻塞。之后所有的查询语句都会被阻塞。
为什么线程2无法申请到MDL写锁后,就会阻塞之后的查询操作?
- 这是因为申请 MDL 锁的操作会形成一个队列,队列中写锁获取优先级高于读锁,一旦出现 MDL 写锁等待,会阻塞后续该表的所有 CRUD 操作。
- 所以为了能安全的对表结构进行变更,在对表结构变更前,先要看看数据库中的长事务,是否有事务已经对表加上了 MDL 读锁,如果可以考虑 kill 掉这个长事务,然后再做表结构的变更。
意向锁
表锁和行锁是满足读读共享、读写互斥、写写互斥的。
-
意向共享锁和意向独占锁是表级锁,不会和行级的共享锁和独占锁发生冲突,而且意向锁之间也不会发生冲突,只会和共享表锁(*lock tables … read*)和独占表锁(*lock tables … write*)发生冲突。
-
意向锁的目的是为了快速判断表里是否有记录被加锁。
例子:意向独占锁就是在给表加独占表锁时(在对记录加独占锁前,先会加上表级别的意向独占锁),直接跟独占表锁说表中是否有加了独占锁的记录,不需要再去遍历了。
- 普通的 select 语句是利用 MVCC 实现一致性读,是无锁的。
- 当执行插入、更新、删除操作,需要先对表加上「意向独占锁」,然后对该记录加独占锁。
AUTO-INC锁
在插入数据时,会加一个表级别的 AUTO-INC 锁,然后为被 AUTO_INCREMENT
修饰的字段赋值递增的值,等插入语句执行完成后,才会把 AUTO-INC 锁释放掉。(主键自增)
InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量
- 当 innodb_autoinc_lock_mode = 0,就采用 AUTO-INC 锁,语句执行结束后才释放锁;
- 当 innodb_autoinc_lock_mode = 2,就采用轻量级锁,申请自增主键后就释放锁,并不需要等语句执行后才释放。
- 当 innodb_autoinc_lock_mode = 1:
- 普通 insert 语句,自增锁在申请之后就马上释放;
- 类似 insert … select 这样的批量插入数据的语句,自增锁还是要等语句结束后才被释放;
当 innodb_autoinc_lock_mode = 2 是性能最高的方式,但是当搭配 binlog 的日志格式是 statement 一起使用的时候,在「主从复制的场景」中会发生数据不一致的问题。
主库采用轻量级锁,在新增时,生成的id可能不连续;而从库俺顺序执行语句时,在从库上「不会」发生像主库那样两个 session 「同时」执行向表 t2 中插入数据的场景。所以,在备库上执行了 session B 的 insert 语句,生成的结果里面,id 都是连续的。这时,主从库就发生了数据不一致。
所以,binlog 日志格式要设置为 row,这样在 binlog 里面记录的是主库分配的自增值,到备库执行的时候,主库的自增值是什么,从库的自增值就是什么。
结论:当 innodb_autoinc_lock_mode = 2 时,并且 binlog_format = row,既能提升并发性,又不会出现数据一致性问题。
行级锁
普通select添加行锁,这种查询加锁被称为锁定读。
//对读取的记录加共享锁
select ... lock in share mode;
//对读取的记录加独占锁
select ... for update;
这两条语句必须在一个事务中,因为当事务提交了,锁就会被释放,所以在使用这两条语句的时候,要加上 begin、start transaction 或者 set autocommit = 0。
共享锁(S锁)满足读读共享,读写互斥。独占锁(X锁)满足写写互斥、读写互斥。
行级锁的类型主要有三类:
- Record Lock,记录锁,也就是仅仅把一条记录锁上;
- Gap Lock,间隙锁,锁定一个范围,但是不包含记录本身;
- Next-Key Lock:Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定记录本身。
Record Lock
Record Lock 称为记录锁,锁住的是一条记录。
- 共享锁(S锁),又称为读锁,如果数据对象加上共享锁之后,则该数据库对象可以被其他事务查看但无法修改和删除数据。
- 排他锁(X锁),又称为写锁、独占锁,如果数据对象加上排他锁,则其他的事务不能对它读取【如果读取没有加锁是可以读取的】和修改。
Gap Lock
Gap Lock 称为间隙锁,只存在于可重复读隔离级别,目的是为了解决可重复读隔离级别下幻读的现象。
- 例子:一个范围 id 为(3,5)间隙锁,那么其他事务就无法插入 id = 4 这条记录了
间隙锁虽然存在 X 型间隙锁和 S 型间隙锁,但是并没有什么区别,间隙锁之间是兼容的,即两个事务可以同时持有包含共同间隙范围的间隙锁,并不存在互斥关系,因为间隙锁的目的是防止插入幻影记录而提出的。
Next-Key Lock
Next-Key Lock 称为临键锁,是 Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定记录本身。
- 例子:一个范围 id 为(3,5] 的 next-key lock,那么其他事务即不能插入 id = 4 记录,也不能修改 id = 5 这条记录。
next-key lock 是包含间隙锁+记录锁的,如果一个事务获取了 X 型的 next-key lock,那么另外一个事务在获取相同范围的 X 型的 next-key lock 时,是会被阻塞的。
原因:虽然相同范围的间隙锁是多个事务相互兼容的,但对于记录锁,我们是要考虑 X 型与 S 型关系,X 型的记录锁与 X 型的记录锁是冲突的。
插入意向锁
事务 A 已经对表加了一个范围 id 为(3,5)间隙锁,在事物 A 还没提交的时候,事务 B 向该表插入一条 id = 4 的新记录,这时会判断到插入的位置已经被事务 A 加了间隙锁,于是事物 B 会生成一个插入意向锁,然后将锁的状态设置为等待状态(并不是意味着事务成功获取到了锁),然后阻塞,直到事务A提交事务,然后插入意向锁状态转为正常状态(代表事务成功获取到了锁)。
- 插入意向锁名字虽然有意向锁,但是它并不是意向锁,它是一种特殊的间隙锁,属于行级别锁。
插入意向锁与间隙锁的另一个非常重要的差别是:尽管「插入意向锁」也属于间隙锁,但两个事务却不能在同一时间内,一个拥有间隙锁,另一个拥有该间隙区间内的插入意向锁(当然,插入意向锁如果不在间隙锁区间内则是可以的)。
加锁
# 锁定读
//对读取的记录加共享锁(S型锁)
select ... lock in share mode;
//对读取的记录加独占锁(X型锁)
select ... for update;
# update delete
//对操作的记录加独占锁(X型锁)
update table .... where id = 1;
//对操作的记录加独占锁(X型锁)
delete from table where id = 1;
加行级锁
加锁的对象是索引,加锁的基本单位是 next-key lock
select * from performance_schema.data_locks\G;
这条语句,查看事务执行 SQL 过程中加了什么锁。
唯一索引(主键索引)加锁的流程图(如果是二级索引的唯一索引,除了流程图中对二级索引的加锁规则之外,还会对查询到的记录的主键索引项加「记录锁」,流程图没有提示这一个点):
非唯一索引加锁的流程图:
没有加索引的查询:
- 如果锁定读查询语句,没有使用索引列作为查询条件,或者查询语句没有走索引查询,导致扫描是全表扫描。那么,每一条记录的索引上都会加 next-key 锁,这样就相当于锁住的全表,这时如果其他事务对该表进行增、删、改操作的时候,都会被阻塞。
- 在线上在执行 update、delete、select … for update 等具有加锁性质的语句,一定要检查语句是否走了索引,如果是全表扫描的话,会对每一个索引加 next-key 锁,相当于把整个表锁住了,这是挺严重的问题。
问题
update沒加索引会锁全表?
在 update 语句的 where 条件没有使用索引,就会全表扫描,于是就会对所有记录加上 next-key 锁(记录锁 + 间隙锁),相当于把整个表锁住了。(关键还得看这条语句在执行过程种,优化器最终选择的是索引扫描,还是全表扫描,如果走了全表扫描,就会对全表的记录加锁了。)
如何避免:
sql_safe_updates
参数设置为 1,开启安全更新模式。- update 语句必须满足如下条件之一才能执行成功:
- 使用 where,并且 where 条件中必须有索引列;
- 使用 limit;
- 同时使用 where 和 limit,此时 where 条件中可以没有索引列;
- delete 语句必须满足以下条件能执行成功:
- 同时使用 where 和 limit,此时 where 条件中可以没有索引列;
- 如果 where 条件带上了索引列,但是优化器最终扫描选择的是全表,而不是索引的话,我们可以使用
force index([index_name])
可以告诉优化器使用哪个索引,以此避免有几率锁全表带来的隐患。
记录锁+间隙锁可以防止删除操作导致的幻读吗?
MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读问题。
- 在有事务读取信息的时候,删除行为(包括增加、修改)会被阻塞
死锁
原因
数据:
情况如下:
-
本质就是两个事务的查询语句(无法查询到)加上了x型的锁(可能是next-key Lock,也可能是 Gap Lock),导致互相都没法插入语句
-
为什么不能插入,因为当我们执行以下插入语句时,会在插入间隙上获取插入意向锁,而插入意向锁与间隙锁是冲突的,所以当其它事务持有该间隙的间隙锁时,需要等待其它事务释放间隙锁之后,才能获取到插入意向锁。而间隙锁与间隙锁之间是兼容的,所以所以两个事务中
select ... for update
语句并不会相互影响。
- 上述只是单纯的读,属于快照读,不会上锁,所以互不影响,但是会出现两个重复的订单,有可能出现幻读
为什么间隙锁与间隙锁之间是兼容的?
间隙锁的意义只在于阻止区间被插入,因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁,共享和排他的间隙锁是没有区别的,他们相互不冲突,且功能相同,即两个事务可以同时持有包含共同间隙的间隙锁。
共同间隙包括两种场景:
- 两个间隙锁的间隙区间完全一样;
- 一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。
next-key lock 是包含间隙锁+记录锁的,如果一个事务获取了 X 型的 next-key lock,那么另外一个事务在获取相同范围的 X 型的 next-key lock 时,是会被阻塞的。
插入意向锁是什么?
插入意向锁是一种特殊的间隙锁,但不同于间隙锁的是,该锁只用于并发插入操作。
- 每插入一条新记录,都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁,如果已加间隙锁,此时会生成一个插入意向锁,然后锁的状态设置为等待状态(PS:MySQL 加锁时,是先生成锁结构,然后设置锁的状态,如果锁状态是等待状态,并不是意味着事务成功获取到了锁,只有当锁状态为正常状态时,才代表事务成功获取到了锁),现象就是 Insert 语句会被阻塞。
Insert语句加锁
Insert 语句在正常执行时是不会生成锁结构的,它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。
隐式锁就是在 Insert 过程中不加锁,只有在特殊情况下,才会将隐式锁转换为显示锁,列举两个场景:
- 如果记录之间加有间隙锁,为了避免幻读,此时是不能插入记录的;
- 如果 Insert 的记录和已有记录存在唯一键冲突,此时也不能插入记录;
记录之间有间隙锁
如何加锁就是上述原因中的情况
遇到唯一键冲突
在插入新记录时,插入了一个与「已有的记录的主键或者唯一二级索引列值相同」的记录,此时插入就会失败
如果主键索引重复:
- 当隔离级别为读已提交时,插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁。
- 当隔离级别是可重复读(默认隔离级别),插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁。
如果唯一二级索引列重复:
- 不论是哪个隔离级别,插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加 S 型 next-key 锁。对的,没错,即使是读已提交隔离级别也是加 next-key 锁,这是读已提交隔离级别中为数不多的给记录添加间隙锁的场景。
例子:
避免死锁
死锁的四个必要条件:互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁,这些条件必然成立,但是只要破坏任意一个条件就死锁就不会成立。
在数据库层面,有两种策略通过「打破循环等待条件」来解除死锁状态:
- 设置事务等待锁的超时时间。在 InnoDB 中,参数
innodb_lock_wait_timeout
是用来设置超时时间的,默认值时 50 秒。 - 开启主动死锁检测。主动死锁检测在发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数
innodb_deadlock_detect
设置为 on,表示开启这个逻辑,默认就开启。
也可以业务的角度来预防死锁
日志
详细看:小林coding 日志
undo log
回滚日志:Innodb 存储引擎层生成的日志,实现了事务中的原子性,主要用于事务回滚和 MVCC。
redo log
重做日志:Innodb 存储引擎层生成的日志,实现了事务中的持久性,主要用于掉电等故障恢复;
binlog
归档日志:是 Server 层生成的日志,主要用于数据备份和主从复制;