【学习笔记】MySQL

第一章、基础架构与日志系统


首先,我认为,认识一件事,要从宏观的角度先对事物有个整体的分析和认识,再对细节进行剖析。这样才能不被细节所拘束,可以达到更高的高度。

一、基础架构

由下图所示,Mysql主要分为两个部分,第一部分是Server层,第二个是引擎层。

Mysql逻辑框架

1. Server层:

a. 连接器:管理连接,权限认证

  • 用于跟客户端建立连接,获取权限、维持和管理连接。连接器在完成经典的TCP握手后,就要开始认证你的身份,首先认证用户名和密码,其次获取对应的权限。
  • 使用show processlist可以查看连接数量。客户端如果太长时间没动静,连接器就会自动将它断开。这个时间是由参数wait_timeout控制的,默认值是8小时。
  • 连接分为长连接和短连接
    • 长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。
    • 短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。
    • 因为连接较复杂,所以尽量使用长连接。
    • 但是长连接累计下来会产生大量的内存,所以应当定期杀死长连接或使用mysql_reset_connection来重新初始化连接资源(MySQL 5.7 或更高版本)。

b. 查询缓存:命中缓存,基本不用

  • MySQL在进行查询请求时,先会看一下查询缓存中是否有这个key-value值,有的话直接返回。
  • 但是一般数据库属于动态系统,除非长期静止不变的数据,否则一般不用。
  • MySQL 8.0已删除这个功能。

c. 分析器:词法分析、语法分析

  • 首先,进行“词法分析”,区分出字符串分别是什么,代表什么意思。
  • 其次,进行“语法分析”,分析SQL语句是否符合语法规则。

d. 优化器:执行计划生成,索引选择

  • 决定使用什么索引,如何关联等。

  • 不同的选择方案,执行结果相同,但是效率不同。

    1
    2
    3
    select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;
    1.可以先从表t1里面取出c=10的记录的ID值,再根据ID值关联到表t2,再判断t2里面d的值是否等于20。
    2.也可以先从表t2里面取出d=20的记录的ID值,再根据ID值关联到t1,再判断t1里面c的值是否等于10。

e.执行器:操作引擎,返回结果

  • 首先,判断用户对该表是否有执行权限
  • 如果有权限,打开表执行。

2. 存储引擎:

  • 负责数据的存储和提取。
  • 其架构模式是插件式的,支持
    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
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    - 现在最常用的存储引擎是```InnoDB```,它从```MySQL 5.5.5```版本开始成为了默认存储引擎。

    ### 3. 课后问题:

    - 如果表 T 中没有字段 k,而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误: “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢?
    - 分析器,`MySQL`会在分析阶段判断语句是否正确,表是否存在,列是否存在等。



    ------

    ## 二、日志系统

    在数据更新时,有两个重要的日志模块,分别为```redo log(重做日志)```和```bin log(归档日志)```。

    ### 1. redo log [Inno DB引擎层]

    在古代酒馆中,掌柜会有一个总账本和一个粉板,用于记录赊账的记录。***这是由于,总账本数量太大,不好快速记录。***因此,便采用如下这种方式:

    - 先将赊账数据,记录在粉板上;
    - 等到不忙时,再将粉板上的数据记录上总帐本。

    这里,```redo log```就相当于粉板,```InnoDB```引擎会将记录先写在redo log上,等不忙时,再更新到数据库中。

    需要主要的是,粉板是有储存限制的,它的构造如下图所示。

    ![](http://imgs.windranger.wang/redolog1.jpg)

    - ```write pos```是当前记录的位置,一边写一边后移,写到第3号文件末尾后就回到0号文件开头。```checkpoint```是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录更新到数据文件。
    - ```write pos```和```checkpoint```之间的是“粉板”上还空着的部分,可以用来记录新的操作。如果```write pos```追上```checkpoint```,表示“粉板”满了,这时候不能再执行新的更新,得停下来先擦掉一些记录,把checkpoint推进一下。
    - 有了redo log,```InnoDB```就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为**crash-safe**。

    ### 2. bin log[Server 层]

    产生原因:

    - 最开始,```MySQL```使用的```MyISAM```,没有crash-safe的能力。
    - ```InnoDB```是另一个公司以插件形式引入```MySQL```的,既然只依靠```binlog```是没有crash-safe能力的,所以```InnoDB```使用另外一套日志系统——也就是redo log来实现crash-safe能力。

    ### 3.两类日志的区别:

    a. 所属

    - redo log是```InnoDB```引擎特有的;

    - ```binlog```是```MySQL```的Server层实现的,所有引擎都可以使用。

    b. 不同形式

    - redo log是物理日志,记录的是“在某个数据页上做了什么修改”;(记录了什么做了什么改变)

    - ```binlog```是逻辑日志,记录的是这个语句的原始逻辑,比如“给ID=2这一行的c字段加1 ”。

    举例:给ID=2的这一行的c属性加了1。并且,它有两种模式:

    - statement: 记录```Sql```语句
    - row:记录更新前后的行的内容

    c. 存储方式

    - redo log是循环写的,空间固定会用完;

    - ```binlog```是可以追加写入的。“追加写”是指```binlog```文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。

    ### 4.update 执行流程

    ![](http://imgs.windranger.wang/update%E6%89%A7%E8%A1%8C%E6%B5%81%E7%A8%8B.png)

    1. 执行器先找引擎取ID=2这一行。ID是主键,引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回。
    2. 执行器拿到引擎给的行数据,把这个值加上1,比如原来是N,现在就是N+1,得到新的一行数据,再调用引擎接口写入这行新数据。
    3. 引擎将这行新数据更新到内存中,同时将这个更新操作记录到redo log里面,此时redo log处于prepare状态。然后告知执行器执行完成了,随时可以提交事务。
    4. 执行器生成这个操作的bin log,并把bin log写入磁盘。
    5. 执行器调用引擎的提交事务接口,引擎把刚刚写入的redo log改成提交(commit)状态,更新完成。

    # 第二章、事务隔离与数据库索引

    ------



    ## 一、事务隔离

    ### (一)隔离性与隔离级别

    1. 引入

    事务的四大特性:ACID(Atomicity、Consistency、Isolation、Durability,即原子性、一致性、隔离性、持久性)。

    简单来说,事务就是要保证一组数据库操作(例如转账:A-10,B+10),要么全部成功,要么全部失败。

    在MySQL中,事务支持是在引擎层实现的。([Mysql包括引擎层和Server层](https://windranger.wang/2019/08/06/%E4%B8%80%E3%80%81%E6%9E%B6%E6%9E%84%E4%B8%8E%E6%97%A5%E5%BF%97-Mysql%E5%AD%A6%E4%B9%A0/))

    2. 隔离级别:

    **事务的隔离级别与效率是成负相关的,隔离级别越高,效率越低。**

    1. 读未提交:一个事务还没提交时,它做的变更就能被别的事务看到。

    2. 读提交:一个事务提交之后,它做的变更才会被其他事务看到。

    3. 可重复读:一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。

    4. 串行化:顾名思义是对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。



    Eg:如同这样的一个过程,有两个事务,事务A和B。

    ![](http://imgs.windranger.wang/mysql2.jpg)

    1. 读未提交: V1=2,V2=2,V3=2。
    - 事务B还没提交,A已经看到了。

    2. 读提交:V1=1,V2=2,V3=2。
    - 事务B提交后,才能被A看到。

    3. 可重复读:V1=1,V2=1,V3=2。
    - 之所以V2还是1,是因为遵循:事务在执行期间看到的数据前后必须是一致的。

    4. 串行化:V1=1,V2=1,V3=2。
    - 事务B执行“将1改成2”的时候,会被锁住。
    - 直到事务A提交后,事务B才可以继续执行。



    在实现上,数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准。

    - 读未提交 隔离级别下,没有视图。
    - 可重复读 隔离级别下,这个视图是在事务启动时创建的,整个事务存在期间都用这个视图。
    - 读提交 隔离级别下,这个视图是在每个SQL语句开始执行的时候创建的。
    - 串行化 隔离级别下,直接用加锁的方式来避免并行访问。

    3. 配置方式

    - 设置transaction-isolation的值。

    - 可以用show variables like 'transaction_isolation'查看当前的隔离级别。

    4. **脏读、幻读、不可重复读**

    - 脏读:脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。

    - 不可重复读:是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。即不能读到相同的数据内容。
    - 一个编辑人员两次读取同一文档,但在两次读取之间,作者重写了该文档。
    - 当编辑人员第二次读取文档时,文档已更改。
    - 原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档,则可以避免该问题。

    - 幻读:是指当事务不是独立执行时发生的一种现象。
    1. 第一个事务的用户 对一个表中所有行数据进行了修改。
    2. 第二个事务的用户 向正在修改的表中插入一行新数据。
    3. 第一个事务的用户 发现表中还有没有修改的数据行,就好像发生了幻觉一样。

    | | 脏读 | 幻读 | 不可重复读 |
    | -------- | -------- | -------- | ---------- |
    | 读未提交 | 可能出现 | 可能出现 | 可能出现 |
    | 读提交 | 不会出现 | 可能出现 | 可能出现 |
    | 可重复读 | 不会出现 | 不会出现 | 可能出现 |
    | 串行化 | 不会出现 | 不会出现 | 不会出现 |



    ### (二)事务隔离的实现

    在MySQL中,实际上每条记录在更新的时候都会同时记录一条回滚操作。

    - 【记录】 回滚操作:当前值和更新值。
    - 【删除】当没有事务再需要用到这些回滚日志时,回滚日志会被删除。

    这里我们展开说明“可重复读”,假设一个值从1被按顺序改成了2、3、4,在回滚日志里面就会有类似下面的记录。

    - 从1>>2 ,记录回滚段 read-view A

    ...

    - 从4>>5 ,记录回滚段 read-view C

    ![](http://imgs.windranger.wang/mysql2_1.jpg)



    ### (三)事务的启动方式

    - 应当避免长事务。长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间。

    MySQL的事务启动方式有以下几种:

    1. 显式启动事务语句, begin 或 start transaction。配套的提交语句是commit,回滚语句是rollback。
    2. set autocommit=0,这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句,这个事务就启动了,而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句,或者断开连接。

    有些客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令。这就导致接下来的查询都在事务中,如果是长连接,就导致了意外的长事务。

    因此,建议使用set autocommit=1, 通过显式语句的方式来启动事务。此外,可以使用”commit work and chain“语法,减少语句的交互次数。





    ------

    ## 二、数据库索引

    **索引的为了提高数据查询的效率,就像书的目录一样。**

    ### (一)索引的常见模型

    - **哈希表+链表**:**适用于只有等值查询的场景**,区间查询慢。
    - **有序数组**:**只适用于静态存储引擎**,查询快、插入慢。
    - **二叉搜索树**:
    - 每个节点的左儿子小于父节点,父节点又小于右儿子。
    - 由于查询磁盘效率问题(IO问题),一般使用N叉树代替二叉树。以InnoDB的一个整数字段索引为例,这个N差不多是1200。这棵树高是4的时候,就可以存1200的3次方个值,这已经17亿了。
    - B-树:https://mp.weixin.qq.com/s/rDCEFzoKHIjyHfI_bsz5Rw
    - B+树:https://mp.weixin.qq.com/s/jRZMMONW3QP43dsDKIV9VQ
    - 相对于B-树优点:1.IO次数更少 2.查询性能稳定 3. 范围查询简便

    ### (二)InnoDB 的索引模型

    - 在InnoDB中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。
    - 每一个索引在InnoDB里面对应一棵B+树。

    假设,我们有一个主键列为ID的表,表中有字段k,并且在k上有索引。

    这个表的建表语句是:

    ```mysql
    mysql>create table T(id int primary key, k int not null, name varchar(16), index (k))engine=InnoDB;

向表T中插入数据 R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)。建立了主键和索引(k)的两棵树。

  • 聚簇索引(clustered index):主键索引(主索引树)

  • 二级索引(secondary index):非主键索引(xx键索引树)

基于主键索引和普通索引的查询有什么区别?

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

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

(三)索引维护

  • 分裂:当插值时,数据页已经满了,根据B+树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去,这个过程称为分裂。

    • 性能受影响
    • 整体的空间利用率减低50%
  • 合并:当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。

  • 索引的使用与建立:

    • 原则:主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。

    • 自增主键的使用(NOT NULL PRIMARY KEY AUTO_INCREMENT)

      • 优点:每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。
    • 业务逻辑的字段做主键

      • KV场景:直接将这个索引设置为主键,可以避免每次查询需要搜索两棵树。

(四)覆盖索引

根据上图所示,如果建立以下这条查询语句。在这个过程中,先根据k索引树查询对应的ID值,然后在根据ID在主索引树上查找数据。其中,回到主键索引树搜索的过程,我们称为回表。(这一过程会消耗时间)

1
select * from T where k between 3 and 5

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

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

Eg: 当有一个高频请求,要根据市民的身份证号查询他的姓名时;我们应当建立(身份证号、姓名)的联合索引【覆盖索引】。

(五)最左前缀原则

B+树这种索引结构,可以利用索引的“最左前缀”,来定位记录。如图所示,建立一个(姓名,年龄)的联合索引,此时数据的排列方式是根据 姓名 的顺序排列的。

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

(六)索引下推

我们还是以市民表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是10岁的所有男孩”。那么,SQL语句是这么写的:

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

你已经知道了前缀索引规则,所以这个语句在搜索索引树的时候,只能用 “张”,找到第一个满足条件的记录ID3。当然,这还不错,总比全表扫描要好。

然后,判断其他条件是否满足。【每一个箭头表示一次回表过程】

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

    在(name,age)索引里面,我特意去掉了age的值,这个过程InnoDB并不会去看age的值,只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此,需要回表4次。

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

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