【学习笔记】MySQL

学习极客时间《MySQL实战45讲》笔记。

第一章、基础知识


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

一、基础架构

由下图所示,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
    - 现在最常用的存储引擎是```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上,等不忙时,再更新到数据库中。需要主要的是,粉板是有储存限制的,它的构造如下图所示。

    <img src="http://imgs.windranger.wang//image-20200413080754437.png" alt="image-20200413080754437" style="zoom: 50%;" />

    - ```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`引擎特有的;
    - `bin log`是`MySQL`的Server层实现的,所有引擎都可以使用。

    b. 不同形式

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

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

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

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

    c. 存储方式

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

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

    ### 4.update 执行流程

    <img src="http://imgs.windranger.wang/update%E6%89%A7%E8%A1%8C%E6%B5%81%E7%A8%8B.png" style="zoom: 33%;" />

    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)状态,更新完成。

    该更新流程很绕,进行了两阶段提交,这是因为要保证数据的一致性。

    仍然用前面的 update 语句来做例子。假设当前 ID=2 的行,字段 c 的值是 0,再假设执行 update 语句过程中在写完第一个日志后,第二个日志还没有写完期间发生了 crash,会出现什么情况呢?

    1. 先写 redo log 后写 binlog。假设在 redo log 写完,binlog 还没有写完的时候,MySQL 进程异常重启。由于我们前面说过的,redo log 写完之后,系统即使崩溃,仍然能够把数据恢复回来,所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了,这时候 binlog 里面就没有记录这个语句。因此,之后备份日志的时候,存起来的 binlog 里面就没有这条语句。然后你会发现,如果需要用这个 binlog 来恢复临时库的话,由于这个语句的 binlog 丢失,这个临时库就会少了这一次更新,恢复出来的这一行 c 的值就是 0,与原库的值不同。
    2. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash,由于 redo log 还没写,崩溃恢复以后这个事务无效,所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以,在之后用 binlog 来恢复的时候就多了一个事务出来,恢复出来的这一行 c 的值就是 1,与原库的值不同。

    ### 5、数据恢复(bin log+整库备份)

    怎样让数据库恢复到半个月内任意一秒的状态?前面我们说过了,binlog 会记录所有的逻辑操作,并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内可以恢复,那么备份系统中一定会保存最近半个月的所有 binlog,同时系统会定期做整库备份。这里的“定期”取决于系统的重要性,可以是一天一备,也可以是一周一备。当需要恢复到指定的某一秒时,比如某天下午两点发现中午十二点有一次误删表,需要找回数据,那你可以这么做:

    首先,找到最近的一次全量备份,如果你运气好,可能就是昨天晚上的一个备份,从这个备份恢复到临时库;

    然后,从备份的时间点开始,将备份的 binlog 依次取出来,重放到中午误删表之前的那个时刻。这样你的临时库就跟误删之前的线上库一样了,然后你可以把表数据从临时库取出来,按需要恢复到线上库去。

    备份周期选择(一天一备 or 一周一备):

    - 恢复数据丢失的时间,既然需要恢复,肯定是数据丢失了。如果一天一备份的话,只要找到这天的全备,加入这天某段时间的binlog来恢复,如果一周一备份,假设是周一,而你要恢复的数据是周日某个时间点,那就,需要全备+周一到周日某个时间点的全部binlog用来恢复,时间相比前者需要增加很多;看业务能忍受的程度。
    - 数据库丢失,如果一周一备份的话,需要确保整个一周的binlog都完好无损,否则将无法恢复;而一天一备,只要保证这天的binlog都完好无损;当然这个可以通过校验,或者冗余等技术来实现。

    ### 6、如何避免长事务对业务的影响?

    首先,从应用开发端来看:

    1. 确认是否使用了 set autocommit=0(不自动提交)。这个确认工作可以在测试环境中开展,把 MySQL 的 general_log 开起来,然后随便跑一个业务逻辑,通过 general_log 的日志来确认。一般框架如果会设置这个值,也就会提供参数来控制行为,你的目标就是把它改成 1。
    2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要,但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。业务连接数据库的时候,根据业务本身的预估,通过 SET MAX_EXECUTION_TIME 命令,来控制每个语句执行的最长时间,避免单个语句意外执行太长时间。

    其次,从数据库端来看:

    1. 监控 information_schema.Innodb_trx 表,设置长事务阈值,超过就报警 / 或者 kill;
    2. Percona 的 pt-kill 这个工具不错,推荐使用;
    3. 在业务功能测试阶段要求输出所有的 general_log,分析日志行为提前发现问题;
    4. 如果使用的是 MySQL 5.6 或者更新版本,把 innodb_undo_tablespaces 设置成 2(或更大的值)。如果真的出现大事务导致回滚段过大,这样设置后清理起来更方便。

    ## 三、事务隔离

    ### 1、事务的四大特性

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

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

    在MySQL中,事务支持是在引擎层实现的。

    ### 2、隔离级别

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

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

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

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

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

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

    <img src="http://imgs.windranger.wang/mysql2.jpg" style="zoom: 50%;" />

    - 读未提交: V1=2,V2=2,V3=2。事务B还没提交,A已经看到了。
    - 读提交:V1=1,V2=2,V3=2。事务B提交后,才能被A看到。
    - 可重复读:V1=1,V2=1,V3=2。之所以V2还是1,是因为遵循:事务在执行期间看到的数据前后必须是一致的。
    - 串行化:V1=1,V2=1,V3=2。事务B执行“将1改成2”的时候,会被锁住。直到事务A提交后,事务B才可以继续执行。

    ### 3、配置方式

    设置transaction-isolation的值。可以用show variables like 'transaction_isolation'查看当前的隔离级别。

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

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

    ### 4、脏读、幻读、不可重复读

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

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

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

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

    ### 5、事务隔离的实现

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

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

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

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

    ...

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

    <img src="http://imgs.windranger.wang/mysql2_1.jpg" style="zoom:50%;" />

    ### 6、事务的启动方式

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

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

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

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

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

    ## 四、数据库索引

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

    ### 1、索引的常见模型

    - **哈希表+链表**:**适用于只有等值查询的场景**,区间(范围)查询慢。
    - **有序数组**:**只适用于静态存储引擎**,查询快、插入慢。
    - **二叉搜索树**:
    - 每个节点的左儿子小于父节点,父节点又小于右儿子。
    - 由于查询磁盘效率问题(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. 范围查询简便

    ### 2、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索引树搜索一次。这个过程称为回表。

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

3、索引维护

  • 分裂:当插值时,数据页已经满了,根据B+树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去,这个过程称为分裂。
    • 性能受影响
    • 整体的空间利用率减低50%
  • ==使用自增主键,==是指自增列上定义的主键,在建表语句中一般是这么定义的: NOT NULL PRIMARY KEY AUTO_INCREMENT。插入新记录的时候可以不指定 ID 的值,系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。也就是说,自增主键的插入数据模式,正符合了我们前面提到的递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。

  • 合并:当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。

  • 索引的使用与建立:

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

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

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

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

4、覆盖索引

根据上图所示,如果建立以下这条查询语句。在这个过程中,先根据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: 当有一个高频请求,要根据市民的身份证号查询他的姓名时;我们应当建立(身份证号、姓名)的联合索引【覆盖索引】。

5、最左前缀原则

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

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

6、索引下推

我们还是以市民表的联合索引(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次。

7、重建索引

表 T 有普通索引k,主键索引id,如果你要重建索引 k,你的两个 SQL 语句可以这么写:

  • `alter table T drop index k;``
  • `alter table T add index(k);

如果你要重建主键索引,也可以这么写:

  • alter table T drop primary key;
  • alter table T add primary key(id);

我的问题是,对于上面这两个重建索引的作法,说出你的理解。如果有不合适的,为什么,更好的方法是什么?

==索引可能因为删除,或者页分裂等原因,导致数据页有空洞,重建索引的过程会创建一个新的索引,把数据按顺序插入,这样页面的利用率最高,也就是索引更紧凑、更省空间。==

  • 重建索引 k 的做法是合理的,可以达到省空间的目的。
  • 但是,重建主键的过程不合理。不论是删除主键还是创建主键,都会将整个表重建。所以连着执行这两个语句的话,第一个语句就白做了。这两个语句,你可以用这个语句代替 :alter table T engine=InnoDB

五、锁

MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

1、全局锁

全局锁:就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命令,==之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。==全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

2、表锁

MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。

2.1 表锁

表锁的语法是 lock tables … read/write。与 FTWRL 类似,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句,则其他线程写 t1、读写 t2 的语句都会被阻塞。同时,线程 A 在执行 unlock tables 之前,也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许,自然也不能访问其他表。

2.2 元数据锁

另一类表级的锁是 MDL(metadata lock)。MDL 不需要显式使用,在访问一个表的时候会被自动加上。MDL 的作用是,保证读写的正确性。你可以想象一下,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。

因此,在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

  • 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。

因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。虽然 MDL 锁是系统默认会加的,但却是你不能忽略的一个机制。

举例:给一个小表加个字段,导致整个库挂了。你肯定知道,给一个表加字段,或者修改字段,或者加索引,需要扫描全表的数据。在对大表操作的时候,你肯定会特别小心,以免对线上服务造成影响。而实际上,即使是小表,操作不慎也会出问题。我们来看一下下面的操作序列,假设表 t 是一个小表

image-20200414080446128

我们可以看到 session A 先启动,这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁,因此可以正常执行。之后 session C 会被 blocked,是因为 session A 的 MDL 读锁还没有释放,而 session C 需要 MDL 写锁,因此只能被阻塞。如果只有 session C 自己被阻塞还没什么关系,但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了,所有对表的增删改查操作都需要先申请 MDL 读锁,就都被锁住,等于这个表现在完全不可读写了。如果某个表上的查询语句频繁,而且客户端有重试机制,也就是说超时后会再起一个新 session 再请求的话,这个库的线程很快就会爆满。你现在应该知道了,事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。

如何安全地给小表加字段?首先我们要解决长事务,事务不提交,就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中,你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行,要考虑先暂停 DDL,或者 kill 掉这个长事务。但考虑一下这个场景。如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?这时候 kill 可能未必管用,因为新的请求马上就来了。==比较理想的机制是,在 alter table 语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到 MDL 写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。==

3、行锁

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

==行锁,就是针对数据表中行记录的锁。这很好理解,比如事务 A 更新了一行,而这时候事务 B 也要更新同一行,则必须等事务 A 的操作完成后才能进行更新。==

两端锁:

在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。即B要等到A commit后才能执行。

image-20200414081359509

死锁与死锁检测:

当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。

image-20200414082052255

解决方法:

  1. 直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
  2. 发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑。

怎么解决由这种热点行更新导致的性能问题呢?

问题的症结在于,死锁检测要耗费大量的 CPU 资源。

  1. 一种头痛医头的方法,就是如果你能确保这个业务一定不会出现死锁,可以临时把死锁检测关掉。但是这种操作本身带有一定的风险,因为业务设计的时候一般不会把死锁当做一个严重错误,毕竟出现死锁了,就回滚,然后通过业务重试一般就没问题了,这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时,这是业务有损的。
  2. 另一个思路是控制并发度。根据上面的分析,你会发现如果并发能够控制住,比如同一行同时最多只有 10 个线程在更新,那么死锁检测的成本很低,就不会出现这个问题。一个直接的想法就是,在客户端做并发控制。但是,你会很快发现这个方法不太可行,因为客户端很多。我见过一个应用,有 600 个客户端,这样即使每个客户端控制到只有 5 个并发线程,汇总到数据库服务端以后,峰值并发数也可能要达到 3000。因此,这个并发控制要做在数据库服务端。
  3. 如果你有中间件,可以考虑在中间件实现;如果你的团队有能修改 MySQL 源码的人,也可以做在 MySQL 里面。基本思路就是,对于相同行的更新,在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。
  4. 你可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例,可以考虑放在多条记录上,比如 10 个记录,影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候,随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10,可以减少锁等待个数,也就减少了死锁检测的 CPU 消耗。这个方案看上去是无损的,但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少,比如退票逻辑,那么这时候就需要考虑当一部分行记录变成 0 的时候,代码要有特殊处理。

六、事务隔离

在 MySQL 里,有两个“视图”的概念:

  1. 一个是 view。它是一个用查询语句定义的虚拟表,在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ,而它的查询方法与表一样。
  2. 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图,即 consistent read view,用于支持 RC(Read Committed,读提交)和 RR(Repeatable Read,可重复读)隔离级别的实现。它没有物理结构,作用是事务执行期间用来定义“我能看到什么数据”。

事务隔离的实现:每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本,这就是数据库的多版本并发控制(MVCC)。

“快照”在 MVCC 里是怎么工作的?

在可重复读隔离级别下,事务在启动的时候就“拍了个快照”。注意,这个快照是基于整库的。(根据事务id实现)

==InnoDB 里面每个事务有一个唯一的事务 ID,叫作 transaction id。==它是在事务开始的时候向 InnoDB 的事务系统申请的,是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候,都会生成一个新的数据版本,并且把 transaction id 赋值给这个数据版本的事务 ID,记为 row trx_id。同时,旧的数据版本要保留,并且在新的数据版本中,能够有信息可以直接拿到它。也就是说,数据表中的一行记录,其实可能有多个版本 (row),每个版本有自己的 row trx_id。如图所示,就是一个记录被多个事务连续更新后的状态。(通过undo log 结合当前数据模拟出)

image-20200414085535061

一致性视图(read-view):在实现上, InnoDB 为每个事务构造了一个数组,用来保存这个事务启动瞬间,当前正在“活跃”的所有事务 ID。“活跃”指的就是,启动了但还没提交。数组里面事务 ID 的最小值记为低水位,当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位,就组成了当前事务的一致性视图(read-view)。而数据版本的可见性规则,就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

image-20200414085857716

这样,对于当前事务的启动瞬间来说,一个数据版本的 row trx_id,有以下几种可能:

  1. 如果落在绿色部分,表示这个版本是已提交的事务或者是当前事务自己生成的,这个数据是可见的;
  2. 如果落在红色部分,表示这个版本是由将来启动的事务生成的,是肯定不可见的;
  3. 如果落在黄色部分,那就包括两种情况
    • 若 row trx_id 在数组中,表示这个版本是由还没提交的事务生成的,不可见;
    • 若 row trx_id 不在数组中,表示这个版本是已经提交了的事务生成的,可见。

一个数据版本,对于一个事务视图来说,除了自己的更新总是可见以外,有三种情况:

  • 版本未提交,不可见;
  • 版本已提交,但是是在视图创建后提交的,不可见;
  • 版本已提交,而且是在视图创建前提交的,可见。