Jared's blog

MySQL进阶

# 2.MySQL进阶

# 逻辑架构

MySQL是典型的C/S架构，即Client/Server架构，服务器端程序使用的是mysqld。

这里为简化的三层结构：

# 连接层

客户端和服务器建立连接，客户端发送至SQL至服务器端。

  1. 客户端通过TCP三次握手后建立连接

  2. MySQL通过TCP传输的账号密码进行身份认证和权获得

  3. MySQL有对应的连接池，采用长链接模式复用TCP连接，以解决无限的创建和销毁带来的资源耗尽、性能下降问题。

# 服务层

包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

  1. 服务层主要完成大多数核心服务功能：SQL接口、缓存查询、SQL的分析和优化、内置函数执行...

  2. 服务层会解析查询 并创建相应的内部解析树，并对其完成相应的优化：如确定查询表的顺序，是否利 用索引等，最后生成相应的执行操作

# 存储引擎层

与数据库文件打交道，负责数据的存储和读取。

  1. 插件式存储引擎层：真正的负责了MySQL中的数据存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。

# SQL执行流程

# 1. 建立连接

# 2. 查询缓存

MySQL在拿到一个查询请求之后，会先到查询缓存中查看，key是查询语句，value是查询结果。（效率低，8.0之后抛弃了这个功能。）

mysql> show global variables like '%query_cache_type%';
#query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON. 2 (DEMAND)
#其中2代表当sql语句中有SQL_CACHE关键词时才缓存。
eg: select SQL_CACHE * from test where ID=5;
1
2
3
4

监控查询缓存的命中率

mysql> show status like '%Qcache%';
# Qcache_free_blocks: 查询缓存中剩余的blocks，越大内存碎片越多，需要时间进行整理。
# Qcache_free_memory：查询缓存内存大小，可知当前系统查询内存是否够用。
# Qcache_hits: 查询缓存命中次数，越大表示效果越理想。
# Qcache_inserts: 与Qcache_hits相反，表示未命中需要插入到查询缓存中。
# Qcache_Lowmem_ prunes: 因查询内存不足需要移出查询缓存的数量。
# Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
# Qcache_queries_in_cache: 当前缓存中缓存的查询数量
# Ocache_total_blocks: 当前缓存的block数量。
1
2
3
4
5
6
7
8
9

# 3. 解析器

词法分析、语法分析、语义检查—>解析树

  1. 词法分析(Lexical scanner)：作用是将整个查询分解为多个元素。比如：select，提取表名、关键字、查询条件

  2. 语法规则(Grammar rule module)：寻找sql语法规则组合，产生一个序列，执行这些规则相关的代码。

# 4. 优化器

优化器的作用就是以它认为的最优的执行方案去执行（有时候可能也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

其中优化器分为两个阶段

  1. 逻辑查询优化：这个阶段主要是使用关系代数对SQL语句做一些等价变换，对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。

  2. 物理查询优化：一般分为两种

    1. 基于规则的优化（RBO，Rule-Based Optimizer） 这种方式主要是基于一些预置的规则对查询进行优化。

    2. 基于代价的优化（CBO，Cost-Based Optimizer） 这种方式会根据模型计算出各个可能的执行计划的代价，然后选择代价最少的那个。它会利用数据库里面的统计信息来做判断。

# 5. 执行器

调用存储引擎API执行，具体实现由选择的存在引擎决定

# 缓冲池

InnoDB存储引擎是以页为单位来管理存储空间的，DBMS会申请占用内存来作为数据缓冲池，在对数据进行操作时，需要把磁盘上的页缓存到BufferPool中，这样的好处是可以人磁盘活动最小化，减少磁盘直接进行I/O 的时间。

# 存储引擎

# InnoDB

  • 支持外键。外键约束其实降低了表的查询速度，增加了表之间的耦合度。

  • 支持事务。默认的事务隔离级别是可重复读，通过MVCC(并发版本控制)来实现。

  • 支持行级锁

  • 使用缓冲池，将索引和数据全部缓存起来，加快查询的速度。

# MyISAM

mysql5.5之前的默认数据库引擎。拥有较高的插入、查询速度，但是不支持事务

# Memory

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

# **Archive **

非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度，但其对查询的支持相对较差

# **Federated **

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

# **CSV **

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

# BlackHole

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

# ERFORMANCE_SCHEMA

该引擎主要用于收集数据库服务器性能参数；

# Mrg_Myisam Merge

是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作；

# 索引

# 索引的数据结构

# 哈希

  • 类似于hashmap，仅能满足等值查询，不支持范围查询

# B TREE

B树是一个平衡多路查找树，Blance Tree，是为磁盘等外存储设备设计的一种平衡查找数。

特点：

  • 关键字集合分布在整棵树中

  • 任何一个关键字出现且只出现在一个结点中

  • 搜索有可能在非叶子结点结束

  • 其搜索性能等价于在关键字集合内做一次二分查找

问题点:

传统⽤来搜索的平衡⼆叉树有很多，如 AVL 树，红⿊树等。这些树在⼀般情况下查询性能⾮常好，但当数据⾮常⼤的时候它们就⽆能为⼒了。原因当数据量⾮常⼤时，内存不够⽤，无法将全部数据读入内存，⼤部分数据只能存放在磁盘上，只有需要的数据才加载到内存中。⼀般⽽⾔内存访问的时间约为50 ns，⽽磁盘在 10 ms 左右。速度相差了近 5 个数量级，磁盘读取时间远远超过了数据在内存中⽐较的时间。这说明程序⼤部分时间会阻塞在磁盘 IO 上。而B树数据存储在各个节点上，那么每次读入内存的信息就比较有效，一次查询可能产生很多次IO， 那么我们如何减少磁盘 IO 次数，于是有B+树。

# B+TREE

B+树是在B树基础上的一种优化，使其更适合实现存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

B+树相对于B树有几点不同：

  • 非叶子节点只存储键值信息。

  • 所有叶子节点之间都有一个链指针。

  • 数据记录都存放在叶子节点中。

总结一下这种结构的优点：

  • B+ 树的层级更少： 相较于 B 树 B+ 每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快

  • B+ 树查询速度更稳定： B+ 所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定

  • B+ 树支持范围查询： 叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针

  • B+ 树天然具备排序功能： B+ 树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高

  • B+ 树全节点遍历更快： B+ 树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像 B 树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

# 索引的分类

  • 从 功能逻辑 按照上说，索引主要有4种：普通索引、唯一索引、主键索引、全文索引。

  • 按照 物理实现方式，索引可以分为2种：聚簇索引和非聚簇索引。

    B+树索引可以分为聚集索引和非聚集索引，这里不是指单独的索引类型，而是一种数据存储的方式。上面的B+树示例图为聚集索引。

    聚集索引（聚簇索引）

    存储记录是物理上连续存在，物理存储按照索引排序，所以一个表最多只能有一个聚集索引，Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚集索引。聚集索引的B+树中的叶子节点存放的是整张表的行记录数据。

    非聚集索引（非聚簇索引）

    非聚集索引是逻辑上的连续，物理存储并不连续，数据在物理存储不按照索引排序。

    非聚集索引索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据，这个过程称为回表。

    PS： Innodb里非主键索引又被称为二级索引、辅助索引，均属于非聚集索引

  • 按照 作用字段个数 进行划分，分成单列索引和联合索引。

# 创建索引

  1. 创建表的时候创建索引

    CREATE TABLE 表名 (
  [col_name data_type] ,
  [UNIQUE | FULLTEXT | SPATIAL ] [INDEX | KEY ] [index_name] (col_name [length]) [ASC | DESC]
);
    1
    2
    3
    4
    5

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

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

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

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

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

    • ASC 或 DESC 指定升序或者降序的索引值存储。 特例：主键索引使用主键约束的方式来创建。

    eg:

    CREATE TABLE book(
 book_id INT,
 book_name VARCHAR(100)
 INDEX index_bname(book_name)
);


# 查看表的索引
SHOW INDEX FROM 表名；
    1
    2
    3
    4
    5
    6
    7
    8
    9

  2. 在已存在的表上创建索引

    # 1
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATTAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC|DESC]

# 2
CREATE [UNIQUE | FULLTEXT | SPATTAL] [INDEX | KEY] [index_name] ON 表名 (col_name[length],...) [ASC|DESC]
    1
    2
    3
    4
    5

    eg:

    ALTER TABLE book ADD INDEX idx_bkname(book_name(30));
CREATE INDEX idx_bkname on book(book_name);
    1
    2

# 哪些情况适合创建索引

1、字段的数值有唯一性的限制 2、经常作为 WHERE 查询条件的字段 3、经常用在join的列上，这些列主要是一些外键 4、DISTINCT 字段需要创建索引 5、区分度高（散列性高）的列适合作为索引，和第4点有点类似 6、经常用在GROUP BY 和 ORDER BY 的列 7、经常需要根据范围（<,≤,=,>,≥,BETWEEN,IN）进行搜索的列上，其指定范围是连续的，可以利用索引已经排序的特点

# 哪些情况不适合创建索引

1、在 where 中使用不到的字段，不要设置索引 2、数据量小的表最好不要使用索引 3、有大量重复数据的列上不要建立索引 4、避免对经常更新的表创建过多的索引 5、不建议用无序的值作为索引 6、删除不再使用或者很少使用的索引 7、不要定义冗余或重复的索引

# 索引的优化和查询优化

# 最左匹配原则

MySQL 建立联合索引有最左匹配的原则，即最左优先：

如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；

如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引。也就是会先以最左边的字段顺序建立索引，再依次建立索引。

以上面的（a，b，c）索引来说，对于最左边字段a来说，a是有顺序索引。b是无序的，但（a，b）是有序的，也就是在a有序的基础上看，b也是有序的。同样（a，b，c）也是这样。

不符合最左原则会导致索引失效

以（a，b，c）索引为例

  • 查询条件中没有第一个字段

    比如 where b = 2 ，因为建立索引树的时候，a是第一个，没有最左边的字段，即使后面的字段建立了索引，也无法命中。

  • 查询条件中，缺少第二个字段

    比如where a = 1 and c = 2，通过a 字段可以匹配出一部分数据，但是没有b字段，就无法向下进行匹配。

  • 索引顺序（查询优化器）

    如果索引顺序是a,b 但是查询语句是 where b=2 and a = 1，这时候索引也能命中。这是由于mysql查询优化器会自动调整where 的条件顺序。

  • 范围查询

    比如where a = 1 and b > 100 and c = 2，此时 a b会走索引，c 不会走。

    mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。like 要注意一下：如果通配符% 不出现在开头，则可以走索引。

# 覆盖索引

如果 select 后面查询的字段都可以从这个索引的树中获取，而不用回表，这种情况一般可以说是用到了覆盖索引。在执行计划的 extra列里会有using index。

假设你定义一个联合索引CREATE INDEX idx_name_age ON t(name,age);

SELECT name,age from t where name='张三'

查找的字段 name 和 age 都包含在联合索引 idx_name_age 的索引树中，这样的查询就是覆盖索引查询。

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

# 索引下推

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

还以t表的联合索引（name, age）为例。

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

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

无索引下推执行流程为

索引下推执行流程

# 联合索引的优点

介绍了上述的最左匹配原则、覆盖索引、索引下推，共同点都是基于联合索引，由此总结一下联合索引的优点：

减少开销： 建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引： 对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

筛选效率高： 索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w。

有序： 索引本身是有顺序的，当要对索引字段排序时，那么查询到的数据天然就是有顺序的，减少了排序的开销

# 性能分析工具

# 查询系统性能参数

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

SHOW [GLOBAL | SESSION] STATUS LIKE '参数';
# 一些常用的性能参数如下
Connections：连接MysQL服务器的次数。
Uptime：MysQL服务器的上线时间。
Slow_queries：慢查询的次数。
Innodb_rows_read: Select查询返回的行数
Innodb_rows_inserted：执行INSERT操作插入的行数 一
Innodb_rows_updated：执行UPDATE操作更新的行数
Innodb_rows_deleted：执行DELETE操作删除的行数
Com_select：查询操作的次数。
Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
Com_update：更新操作的次数。
Com_delete：删除操作的次数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 慢查询日志

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值 的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上（不含10秒）的语句，认为是超出了我们的最大忍耐时间值。它的主要作用是帮助我们发现那些执行时间特别长的SQL查询，默认是关闭的。

补充：除了long_query_time还有一个系统变量min_examined_row_limit意思是查询扫描过的最少记录数扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并目查询执行时间超过 long_query_time 的值，那么，这个查询就被记录到慢查询日志中；反之，则不被记录到慢查询日志中。

  • 1、相关命令

    # 查看慢查询是否开启
SHOW variables like '%slow_query_log';
# 开启全局慢查询日志
set global slow_query_log='ON';
# 查看并设置阈值
SHOW variables like '%long_query_time%';
set global long_query_time=1;™
    1
    2
    3
    4
    5
    6
    7

  • 2、也可以在配置文件中设置 如下的方式相较于前面的命令行方式，可以看作是永久设置的方式。 修改my.cnf文件，[mysqld]下增加或修改参数 long_query_time、 slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。

    [mysqld]
slow_query_log=ON ＃ 开启慢查询日志的开关
slow_query_1og_file=/var/lib/mysql/slow.1og ＃慢查询目志的目录和文件名信息
long_query_time=3 #设置慢查询的國值为3秒，超出此设定值的SQL即被记录到慢查询日志
1og_output=FILE
    1
    2
    3
    4
    5

# 慢查询日志分析工具：mysqldumpslow

mysqldumpslow --help # 查看具体指令
-a:不将数字抽象成N，字符串抽象成S
-s：是表示按照何种方式排序：
  - c: 访问次数
  - l: 锁定时间
  - r：返回记录
  - t: 查询时间
  - al:平均锁定时间
  - ar:平均返回记录数
  - at:平均查询时间（默认方式）
  - ac:平均查询次数
-t：即为返回前面多少条的数据；
-g：后边搭配一个正则匹配模式，大小写不敏感的；€
1
2
3
4
5
6
7
8
9
10
11
12
13
14

工作常用参考:

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib /mysql/slow.1og
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.1og
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.1og
#另外建议在使用这些命令时结合 和more 使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.1og more
1
2
3
4
5
6
7
8

# EXPLAIN

Explain可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的SQL语句的，分析所执行的语句或者表结构的性能瓶颈。

explain中的列：

  1. id

    改列为执行的顺序，每个号码表示一趟独立的查询，id列越大执行优先级越高，id相同则从上往下执行，id为null则最后执行

  2. select_type

    • SIMPLE：简单查询。不包含子查询和union

    • PRIMARY：复杂查询中的最外层的select

    • DERIVED：包含在from子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表

      eg: EXPLAIN SELECT * from ( select 1 union all select 1 ) t;

    • SUBQUERY：包含在select中的子查询（不在from子句中）

      eg: explain select (select 1 from t LIMIT 1) from tbl_sys_api;

    • UNION：在union中的第二个和随后的select

      eg: EXPLAIN select 1 union all select 1;

    • UNION RESULT: 从union临时表检索结果的result

  3. table

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

  4. type

    这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

    依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

    性能优化的目标，得保证查询至少达到range级别，最好达到const

    • NULL： mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

    • const： 表示通过索引一次就找到了，用于primary key 或者unique key的列与常量比较时，所以表中只有一条记录，查询速度快

    • system： 表只有一行记录，const类型的特例，一般很少出现，可以忽略

    • eq_ref：唯一性索引扫描，primary key 或者unique key 索引的所有部分被连接使用，最多只返回一条符合条件的记录。

    • ref： 非唯一索引说明，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行

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

    • index： 扫描全索引，一般是扫描某个二级索引，比all会快一些（index是从索引中读取的，而all是从磁盘中读取）

    • all： 全表扫描

  5. possible_keys

    显示可能应用在这张表中的索引，一个或多个。

    查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

  6. key

    实际使用的索引。如果为NULL，则没有使用索引。explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

  7. key_len

    这表示用到的索引字段的字节数，通过这个值可以算出具体使用了索引中的哪些列。

  8. ref

    这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名，指的是 “=”号后面的东西。

  9. rows

    检查的行数，读取的行数越少越好。

  10. filterd

    表示存储引擎返回的数据在server层（及其他过滤条件）过滤后，剩下多少满足查询的记录数量的比例

  11. Extra

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

    • Using index： 查询的列被索引覆盖，及覆盖索引的场景，不用回表

    • Useing where： 查询的where条件列未被索引覆盖

    • Using filesort： mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种无法利用索引完成的排序操作称为“文件排序”。这种情况下一般也是要考虑使用索引来优化的。

      如按照age排序时，age无索引，会文件排序，按照city则不会产生文件排序

    • NULL： 查询的列未被索引覆盖，查询的where条件走了索引

    • Useing index condition： 查询的列不完全被索引覆盖，条件使用索引，是一个范围

    • Using temporary：mysql需要创建一张临时表来处理查询

# 数据库日志

# REDO LOG

我们知道Innodb是以页来管理存储空间的，在访问页时，需要把磁盘上的页缓存到内存中的缓冲池中，然后缓冲池中的脏页会以一定的频率刷入磁盘（checkpoint机制）。所以最坏的情况就是事务提交后，刚写入缓冲池，数据库宕机了，那么这段数据就是丢失的，无法恢复。所以Innodb就采用了WAL（Write-Ahead Logging）技术，思想就是先写日志，再写磁盘只有日志写入成功，才算事务提交成功，这里的日志就是redo log。

redo log 是存储引擎层（innodb）生成的日志，记录的是“物理级别”上页修改操作，比如页号xxx、偏移量yyy、写入了zzz数据。主要是为了保证数据的可靠性，也就是保证事务的持久性 保证ACID中的D。

好处

  • redo日志降低了刷盘频率

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

特点

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

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

# UNDO LOG

redo log是事务持久性的保证，undo就是事务原子性的保证。

在事务中更新数据的前置操作其实是要先写入一个undo log，主要是为了避免事务执行到一半可能出现的各种情况，如服务器错误、宕机，主动发起的ROLLBACK语句等，出现以上情况我们就需要将数据改为原先的样子，这个过程称为回滚。

undo日志的作用

  • 回滚数据

    用户对undo日志可能有误解：

    undo用于将数据库物理地恢复到执行语句或事务之前的样子,但事实并非如此。undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。 这是因为在多用户并发系统中，可能会有数十、数百甚至数干个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

  • MVCC undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCc的实现是通过undo来完成。当用户读取一行记录时，若 该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

# BIN LOG

binary log，二进制日志文件，也叫作变更日志 (update log)。它记录了数据库所有执行的 DDL 和 DML等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。

它以事件形式记录并保存在二进制文件中。通过这些信息，我们可以再现数据更新操作的全过程。如果想要记录所有语句（例如，为了识别有问题的查询），需要使用通用查询日志。

binlog主要应用场景：

  • 一是用于数据恢复

    如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。

  • 二是用于数据复制

    由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数 据一致的目的。

可以说MysQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

# 锁

加锁目的是为了解决事务的隔离性问题，让事务之间相互不影响，每个事务进行操作的时候都必须先对数据加上一把锁，防止其他事务同时操作数据

按锁的粒度可以分为全局锁、表级锁、行锁、间隙锁、临间锁。

按锁的属性可以分为共享锁、排他锁

# 全局锁

全局锁就是对整个数据库实例加锁，使用场景比较少。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是，做全库逻辑备份。备份要加锁的原因主要是获得统一的视图，保证数据的逻辑一致，比如有一个用户余额表，与用户商品表，余额减了，那相应的用户会增加商品，也就是要保证全局的统一视图。

对于备份的场景补充说明：官方自带的逻辑备份工具是 mysqldump。导数据之前就会启动一个事务，通过MVCC（多并发版本控制）来拿到一致性视图。对于MyISAM这种不支持事务的引擎，则需要FTWRL命令。

# 表级锁

表级锁：又分为 表锁和元数据锁（meta data lock，MDL）

  • 表锁语法是lock tables … read/write，用

  • 元数据锁（metadata lock)，由server层实现，不需要显示的加，增删改查会加mdl读锁，对表结构进行变更时会加mdl写锁，读读不互斥，读写、写写互斥。

对表结构进行变更时会加mdl写锁，而执行一个ddl操作需要扫描全表的数据，那么对于很大的表进行变更的时候，是否会对服务产生影响呢？

先给出如下结论：

  • 创建二级索引、删除索引、重命名索引、改变索引类型——不“锁表” 。

  • 添加字段、删除字段、重命名字段、调整字段顺序、设置字段默认值、删除字段默认值、修改auto-increment值、调整字段允许NULL、调整字段不允许NULL —— 不“锁表” 。

  • 扩展Varchar字段大小——不“锁表”

  • 更改字段数据类型，如varchar改成text——“锁表”

实现机制：主要是依靠MySQL5.6.7 的新特性Online DDL ， 该特性解决了早期版本MySQL进行DDL操作同时带来锁表的问题，在DDL执行的过程当中依然可以保证读写状态，不影响数据库对外提供服务，大大提高了数据库和表维护的效率，当然Online DDL 并不是绝对安全，更不是可以随意的执行。线上操作还是需要在业务低峰期谨慎操作。

# 行级锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

InnoDB实现了如下两种标准的行级锁：

  • 共享锁（Share Lock即S Lock）: 允许事务对一条行数据进行读取，显示加锁格式如下

    SELECT * FROM t WHERE ... LOCK IN SHARE MODE

  • 排他锁（eXclusive Lock即X Lock）: 允许事务对一条行数据进行删除或更新，显示加锁格式如下

    SELECT * FROM t WHERE ... FOR UPDATE

默认情况下innodb用的是隐式加锁。另外，对于普通SELECT语句，InnoDB不会加任何锁。

InnoDB行锁是给索引项加锁来实现的。这样的实现方式意味着当一个事务对表的某一行加锁后，后面的每个需要对该表加持表锁的事务都需要遍历整个索引树才能知道自己是否能够进行加锁，这样就会很浪费时间和损耗数据库性能。

于是有了意向锁(Intention locks)的概念：如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是我们的意向锁。

意向共享锁 IS/意向排他锁 IX 属于表锁，取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。

S X IS IX
S 兼容 互斥 兼容 互斥
X 互斥 互斥 互斥 互斥
IS 兼容 互斥 兼容 兼容
IX 互斥 互斥 兼容 兼容

# 间隙锁与临键锁

  • 间隙锁（Gap lock）

    间隙锁是在事务加锁后其锁住的是表记录的某一个区间（开区间），当表的相邻ID之间出现空隙则会形成一个区间，比如表里面的数据id 为 1,7,10 ，那么会形成以下几个间隙区间，(负无穷，1)，(1，7)(7，10)(10，正无穷)。

    间隙锁作用： 用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的，防止幻读问题。也就是说间隙锁的应用场景包括并发读取、并发更新、并发删除和并发插入。

    触发条件： 查询条件必须命中索引，范围查询，等值查询未命中记录（若命中，则会升级为行锁）。间隙锁只会出现在REPEATABLE_READ（重复读)的事务级别中。在RU和RC隔离级别下没有间隙锁。

  • 临键锁（Next-Key Lock）

    临键锁是行锁+间隙锁，即临键锁是是一个左开右闭的区间，比如（3，5]。

    InnoDB在RR隔离级别下，如果你使用select ... in share mode或者select ... for update语句，那么InnoDB会使用临键锁，因而可以防止幻读；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。

# MVCC

# 参考

MySQL需要掌握的原理：https://www.jianshu.com/p/31770ad88010 (opens new window)

索引下推：https://baijiahao.baidu.com/s?id=1716515482593299829&wfr=spider&for=pc (opens new window)

MVCC实现原理：https://www.cnblogs.com/qdhxhz/p/15750866.html (opens new window)

上次更新: 2022/10/29, 01:54:52
MySql基础入门
设计模式

← 设计模式→

Theme by Vdoing Copyright © 2020-2022 Jared H
粤ICP备20046800号
×