MYSQL基础知识和案例分享

• 2019 年 10 月 27 日
• 筆記

1

MyISAM

· Mysql 5.1之前默认的存储引擎，支持包括全文索引、压缩、空间函数(GIS)等，不支持事务和行级锁。最大的缺陷是崩溃后无法安全恢复。

· 对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时对表加排他锁

· 可以使用myisampack对表进行压缩。压缩表不能进行修改（除非先解压表，修改数据，再次压缩）。压缩表可以极大的减少磁盘空间占用，因此也可以减少磁盘I/O

2

MariaDB

· MariaDB是由Mysql之父主导开发的Mysql一个分支，因为Oracle收购Mysql后可能存在闭源的风险。

· MariaDB可以视作Mysql的替代品，基本保证二进制兼容。

· MariaDB采用的引擎有Percona公司的XtraDB(InnoDB引擎的改进加强版), 和用来替代MyISAM的新引擎Maria(Mysql之父的女儿…, 该引擎后来改名Aria)

3

InnoDB

· 历史：从Mysql 5.1的InnoDB plugin到 5.5中的原生编译。

· 现在是Mysql的默认事务型引擎，也是最重要的使用最广泛的存储引擎。

· 采用MVCC来支持高并发，实现了四个标准的隔离级别， 默认级别是Repeatable Read, 并通过间隙锁策略防止幻读。

· InnoDB表是基于聚簇索引建立的。

二、事务隔离级别

1

READ UNCOMMITTED 未提交读

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）

2

READ COMMITTED 提交读 (RC)

大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。

3

REPEATABLE READ 可重复读 (RR)

MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

4

SERIALIZABLE 串行化

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

三、 锁基本理论

1

常用存储引擎的锁

· MyISAM和MEMORY采用表级锁（table-level locking）

· BDB采用页面锁（page-leve locking）或表级锁，默认为页面锁

· InnoDB支持行级锁（row-level locking）和表级锁，默认为行级锁

各种锁特点

· 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生冲突的概率最高，并发度最低

· 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

· 页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

2

锁的实现

锁的存放

A. table->locks 存放一个表的所有表级锁。

B. lock_sys->rec_hash存放所有表的行锁。Hash值根据(spaceid, pageno)来计算。

C. trx->trx_locks存放事务的所有锁，包括表级锁和行级锁。一个事务的所有锁，在事务结束时，一起释放。代码在lock_release_off_kernel().如果有等待的锁可以被授权，则会将等待的锁,转变为被授权的锁，并唤醒相应的事务。

行锁的唯一识别

第一印象想到的是，用每行记录的键值来做行锁的唯一识别.但是键值占用空间比较大。

InnoDB使用Page NO.+Heap NO.来做行锁的唯一识别。我们可以将Heap no.理解为页面上的一个自增数值。每条物理记录在被创建时，都会分配一个唯一的heap no.

A. 键值可以理解为一个逻辑值，page no. + heap no. 是物理的。

B. 物理的虽然占用空间小，但是处理要复杂一些。如：在分裂一个B+Tree页面时，一半的记录要移到新的页面中，因此要对存在的锁进行迁移。

3

多版本并发控制MVCC

· Multi-Version Concurrency Control

是通过保存数据在某个时间点的快照来实现的。

读类型: 快照Snapshot/当前Current

•快照读：简单的select操作，属于快照读，不加锁。(Serializable读加读锁)

select * from table where ?;

•当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

◦select * from table where ? lock in share mode;

◦select * from table where ? for update;

◦insert into table values (…);

◦update table set ? where ?;

◦delete from table where ?;

· 不同的存储引擎的MVCC实现不同，有乐观并发控制，有悲观并发控制。

· InnoDB是通过在每行记录后面保存两个隐藏的列来实现的。一个保存行的创建时间，一个保存行的过期时间(或删除时间)。这个不是实际的时间值，而是系统版本号(System Version Number)。

· 每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的的系统版本号作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

· MVCC 只在RC和RR两个隔离级别下工作。这样设计使大部分读操作不用加锁，读和写不冲突。

4

InnoDB锁的类型

摘自MySQL官网:

Shared and Exclusive Locks 共享锁和排他锁

Intention Locks 意向锁

Record Locks 记录锁

Gap Locks 间隙锁

Next-Key Locks = Gap Locks + Record Locks

Insert Intention Locks

AUTO-INC Locks

Predicate Locks for Spatial Indexes

共享锁(S)和排他锁(X)

· (S)hared Lock共享锁: 持有该锁的事务可以读一行

· E(x)clusive Lock排他锁: 持有该锁的事务可以更新或删除一行

· 事务T1在行记录r上持有S锁， 事务T2在r上请求S锁是准许的，最终T1 T2同时还有r上的S锁；但T2在r上请求X锁是不允许的

· 事务T1在记录r上持有X锁，事务T2不能在r上再请求任何类型的锁

意向锁(I)

· (I)ntention Lock意向锁: 是一种表锁，用于表示一个事务随后将对该表的行记录请求某种类型的锁。

Intention shared(IS): 意向共享锁。

SELECT … LOCK IN SHARE MODE

Intention exclusive(IX):意向排他锁

SELECT … FOR UPDATE

· 意向锁协议：

A. 请求获取表t某一行的S锁之前，必须在表t上获取IS或者更强的锁；

B. 请求获取某一行的X锁之前，必须在表上获取IX锁；

· 意向锁主要用来表示某人正在锁定或者即将要锁定行记录

记录锁Record Locks

· 记录锁(行锁)是一种在索引记录上设置的锁，锁定特定的记录行，其他任何事务不能更新和删除，当然也不能insert相同的记录。

· 记录锁永远锁定的是索引，即使表没有索引。InnoDB会隐含创建一个聚簇索引，用这个索引进行记录锁。

间隙锁Gap Locks

· 间隙锁是一种在索引记录的间隙上设置，或者在索引范围的间隙上设置的锁。例如 SELECT c1 FOR UPDATE FROM t WHERE c1 BETWEEN 10 and 20; 不允许其他事务在c1列插入15这个值。开区间不包括记录本身

· 间隙锁是性能和并发之间妥协的产物，可以通过设置事务隔离级别为 RC或者开启系统变量innodb_locks_unsafe_for_binlog(已过时)，来禁用间隙锁

· 间隙锁在同一个gap上没有冲突一说，事务A在gap上有个S锁，事务B允许在同一个gap上持有X锁。

The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

· 间隙锁在Innodb是“纯抑制”，意思是它只阻止其他事务插入数据到这个间隙， 而不会阻止其他事务获取这个间隙上的间隙锁。因此间隙X锁与间隙S锁效果相同。

Next-Key Locks

· next-key锁是记录锁和间隙锁的组合。是开闭区间，包括记录本身

· 假设索引记录有值 10， 11， 13， 20， 那可能的next-key lock包括：

 (-∞, 10]  (10, 11]  (11, 13]  (13,20]  (20,+ ∞)

· Innodb默认就是在RR隔离级别下， 使用的next-key locks用来查询和索引扫描，阻止幻读的发生

· next-key locks 会降级（优化）为record locks:

 当索引含有唯一约束时

 锁定一条记录

· 由于事务的隔离性和一致性要求，会对所有扫描到的record加锁。比如：update … where/delete .. where/select …from…lock in share mode/ select .. from .. for update这都是next-key lock。

· 相对的还有 previous-key locks, 锁定区间与next-key 的开闭相反。

插入意向锁

· Insert Intention是由insert操作设置的一种特殊的Gap lock，发生在行插入之前

· 提高并发插入性能。假设索引记录有4，7，两个不同事务分别插入5，6， 每个事务都在区间4，7上先请求“插入意向锁”，在获取插入行的排他锁之前，这样就不会互相造成阻塞因为这两行不冲突

自增长锁AUTO-INC Locks

· 在InnoDB的内存结构中，每个含有自增长值的表都有一个自增长计数器(auto-increment counter)。对含有自增长计数器的表进行插入操作是，这个计数器会被初始化，执行如下的语句来得到计数器的值：

select max(auto_inc_col) from t for update;

插入操作会根据这个自增长的计数器值加一赋予自增长列。这个实现方式叫做 AUTO-INC Locks.

· 一种特殊的表锁，为了提高插入的性能，锁不是在一个事务完成后释放，在完成对自增长值的SQL语句后立即释放。并发插入场景，事务必须等待前一个插入的完成(虽然不用等待事务的完成)。所以有时候也会被称为“语句”级别的锁。

· 因为不是事务级别的锁，innodb的自增主键很有可能不是延续的。默认innodb_autoinc_lock_mode=1，可预判需要多少行，并一次性预生成。

· InnoDB引擎中，自增长的列必须是索引，同时必须是索引的第一列。如果不是Mysql会抛出异常。

innodb_autoinc_lock_mode = 0 (“traditional” lock mode：全部使用表锁)

innodb_autoinc_lock_mode = 1 (默认)(“consecutive” lock mode：可预判行数时使用新方式，不可时使用表锁)

innodb_autoinc_lock_mode = 2 (“interleaved” lock mode：全部使用新方式，不安全，不适合replication)

隐式锁转显式锁

· 如果一个表有很多的索引，那么操作一个记录时，岂不是要加很多锁到不同的B-Tree上吗？

· Innodb 实现了一个延迟加锁的机制，来减少加锁的数量，在代码中称为隐式锁(Implicit Lock)。隐式锁中有个重要的元素,事务ID（trx_id)。

· 隐式锁的特点

A. 只有在很可能发生冲突时才加锁，减少了锁的数量。

B. 隐式锁是针对被修改的B+Tree记录，因此都是Record类型的锁。不可能是Gap或Next-Key类型。

· 隐式锁的使用

A. INSERT操作只加隐式锁，不需要显示加锁。

B. UPDATE,DELETE在查询时，直接对查询用的Index和主键使用显示锁，其他索引上使用隐式锁。

C. INSERT，UPDATE，DELETE对B+Tree们的操作都是从主键的B+Tree开始，因此对主键加锁可以有效的阻止死锁。

隐式锁其实就是没有锁，比较悲观的顺序化机制。比如我插入一条记录，会假设有其他人也要并发插入数据，但是这是假设的场景而已。可能根本没有并发，那隐式锁也就最终会被取消掉；但是一旦有并发，他们就会查询这个隐式锁所在的事务是不是活跃的，是的话就把我的隐式锁升级为显示锁，自己获取共享锁并标记为等待状态。

隐式锁的逻辑过程如下：

A. InnoDB的每条记录中都一个隐含的trx_id字段，这个字段存在于簇索引的B+Tree中。

B. 在操作一条记录前，首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚).

如果是活动的事务，首先将隐式锁转换为显式锁(就是为该事务添加一个锁)。

C. 检查是否有锁冲突，如果有冲突，创建锁，并设置为waiting状态。如果没有冲突不加锁,跳到E。

D. 等待加锁成功，被唤醒，或者超时。

E. 写数据，并将自己的trx_id写入trx_id字段。Page Lock可以保证操作的正确性。

一致性非锁定读

Consistent Nonlocking Read

· 指InnoDB存储引擎通过MVCC的方式来读取当前执行时间数据库中行的数据，如果读取的行正在执行DELETE或UPDATE操作，这是读取操作不会因此等待行上锁的释放。相反的，InnoDB会去读取行的一个快照数据

· 在事务隔离级别RC和RR下，InnoDB存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。在rc事务隔离级别下，对于快照数据，非一致性读总是被锁定行的最新一份快照数据。而在RR事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

锁定读

Locking Read

SELECT … LOCK IN SHARE MODE：锁定读取行，其他session可以读锁定行，但不能修改直到S锁的事务释放。

SELECT … FOR UPDATE ：锁定读取行以及关联的索引，与直接update这些行实现的效果一致。其他事务不能再更新这些行，也不能加S锁。

示例1：保证parent表有数据，child表才可以插入一条记录。S锁可以保证事务内查询存在的数据不会在 select和insert to child期间被其他事务删除。

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

示例2: 并发更新计数器

SELECT counter_field FROM child_codes FOR UPDATE; //排他锁

UPDATE child_codes SET counter_field = counter_field + 1;

LAST_INSERT_ID(): 在当前connection上下文内，最近一次操作insert或update影响自增列的最终ID

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);

SELECT LAST_INSERT_ID();

四、 索引的分类及使用

1

索引的类型

· 索引在存储引擎层而不是服务器层实现的，所以没有统一的索引标准。

· B-Tree索引对索引列是顺序组织存储的，所以适合范围查找。适用于全键值、键值范围或键前缀查找。启动键前缀查找只适用于根据最左前缀的查找。

· 哈希索引基于哈希表实现，只有精准匹配索引所有列的查询才有效。哈希表中保存指向每个数据行的指针。是Memory引擎的默认索引类型。限制有：只包含哈希和行指针，不存储字段值；不是按照索引列的值顺序存储的，无法用于排序；不支持部分索引列匹配查找，因为哈希索引始终使用索引列的全部内容来计算哈希值的；只支持等值比较查找不支持范围查找；哈希冲突问题；

· 空间数据索引(R-Tree): MyISAM表支持空间索引，可以用作地理数据存储。不常用

· 全文索引是一种特殊类型索引，查找的是文本中的关键词，而不是直接比较索引中的值。

2

三星系统three-star索引

一星：索引将相关的记录放到一起；

二星：索引中的数据顺序和查找中的排列顺序一致；

三星：索引中的列包含了查询中需要的全部列；

3

高性能的索引策略

正确的创建和使用索引是实现高性能查询的基础。

· 独立的列：指索引列不能使表达式的一部分，也不能是函数的参数。

· 前缀索引和索引选择性：对于text或者很长的varchar类型的列，需要索引开始的部分字符，可以大大结余索引空间，MySQL也不允许索引这些列的完整长度。诀窍在于要选择足够长的前缀以保证较高的选择性。

· 多列索引：当多个索引做相交操作时(AND条件)，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。当多个索引做联合操作时(OR条件)，需要耗费大量的CPU和内存在算法的缓存、排序和合并操作上，而优化器不会把这些计算到“查询成本”(COST)中, 优化器只关心随机页面读取。有些时候还不如改成UNION的方式更好。

4

聚簇索引

· 并不是一种单独的索引类型，而是一种数据的存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行

· 无法同时把数据行放在两个不同的地方，所以一个表只能有一个聚簇索引。

优点：数据集中，减少磁盘IO；数据访问快；

缺点：插入速度严重依赖于插入顺序(自增长, 避免UUID)，按照主键的顺序插入式加载数据到InnoDB表中速度最快的方式；可能面临页分裂(page split)问题; 二级索引访问需要两次索引查找(二级索引的叶子节点保存的是行的主键值，不是行记录物理位置的指针)；

· 题外：顺序的主键什么时候回造成更坏的结果？对于高并发，主键的上界会成为“热点”，因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是 AUTO-INC锁机制。考虑更改 innodb_autoinc_lock_mode参数配置

5

覆盖索引

· 通常大家会根据查询的WHERE条件来创建合适的索引，设计优秀的索引应该考虑整个查询，而不单单是WHERE条件部分

· 索引条目远小于数据行的大小，如果只需要读取索引，会极大减少数据访问量

· 索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多

· InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

五、 案例分析

1

加锁组合分析

第一案例首选“何登成”大神的博文：

http://hedengcheng.com/?p=771

2

主键或唯一索引分析

假设有表T(id, name)， id是主键或者唯一索引，已存在5条记录。在不同操作下锁定的区间情况如图，若有出入，请以实际验证结果为准。

3

非唯一索引分析

假设有表T(id, name)， id是非唯一索引，已存在5条记录。在不同操作下锁定的区间情况如图，若有出入，请以实际验证结果为准。

4

一个不可思议的死锁

仍然参见何登成博文

http://hedengcheng.com/?p=844#_Toc378337494

5

INSERT SELECT并发

Insert into t_dest (col1,col2,col3…)

select var1, var2, max(col)-var2

from t_source

where …

· select 语句在这个语境下隐含会加上S锁，类似于加上 lock in share mode;

· 多线程并发运行insert select语句，多个事务会同时先对t_source表进行当前读并获取S锁后，再插入到t_dest。即，内嵌的select并不是互斥的。这种情况下可以将select单独剥离出来使用快照读，然后通过乐观锁等方式对并发插入的数据一致性控制。

6

并发插入的死锁

7

死锁的预防

1. 如果使用insert…select语句备份表格且数据量较大，在单独的时间点操作，避免与其他sql语句争夺资源，或使用select into outfile加上load data infile代替 insert…select，这样不仅快，而且不会要求锁定

2. 一个锁定记录集的事务，其操作结果集应尽量简短，以免一次占用太多资源，与其他事务处理的记录冲突。

3.更新或者删除表格数据，sql语句的where条件都是主键或都是索引，避免两种情况交叉，造成死锁。对于where子句较复杂的情况，将其单独通过sql得到后，再在更新语句中使用。

4. sql语句的嵌套表格不要太多，能拆分就拆分，避免占有资源同时等待资源，导致与其他事务冲突。

5. 对定点运行脚本的情况，避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句。

6.应用程序中增加对死锁的判断，如果事务意外结束，重新运行该事务，减少对功能的影响。