❮
❯
TOC
KINA

KINA-0

Start having fun with KINA right now!
分类 后端原理教程

MySQL高级：SQL规范、架构、调优、锁…

作者

本文记录了MySQL的诸多高级知识点，涵盖了SQL规范、事务管理、索引应用、数据库调优以及分库分表技术。首先，文章详细解析了SQL查询语句的执行顺序以及多表查询的类型和用法。接着，阐释了事务的ACID特性及在并发运行中可能遇到的问题，如脏读和丢失修改。同时，介绍了MySQL的多种隔离级别及其应用场景。文章还分析了索引的底层数据结构，结合聚集索引和二级索引的区别。最后，针对分库分表的缓解性能瓶颈方法，强调了主从同步的重要性与优化策略。

1 SQL规范

1.1 查询语句的执行顺序

sql的编写顺序：

select           字段列表
from             表名列表
join on          表关联
where            条件列表
group by         分组字段列表
having           分组后的条件列表
order by         排序字段列表
limit            分页参数

sql的执行顺序：

from             表名列表
join on          表关联
where            条件列表
group by         分组字段列表
having           分组后的条件列表
select           字段列表
order by         排序字段列表
limit            分页参数

【例】给表、SQL字段起别名

select name ,age from tb_user where name = '张三'  order by age limit 10;

给表起别名。可以正常执行，如果from后不先执行，则select和order by无法引用

select u.name, u.age from tb_user u where name = '张三' order by u.age limit 10;

在where之后使用别名。执行报错，因为where在select之前执行

select u.name uname, u.age uage from tb_user u where uname = '张三' order by u.age limit 10;

在order by之后使用别名。可以正常执行，因为select执行完成后才会执行order by：

select u.name uname ,u.age uage from tb_user u where u.name = '张三' order by uage limit 10;

1.2 多表查询

多表关系

  • 一对多：在多的一方设置外键，关联一的一方的主键
  • 一对一：用于表结构拆分，在其中任何一方设置外键（给唯一约束UNIQUE）,关联另一方的主键
  • 对多对：需建立中间表，中间表包含两个外键，关联两张表的主键

多表查询

  • 内连接
    • 隐式：select ... from 表A, 表B where 条件...
    • 显式：select ... from 表A inner join 表B on 条件...
  • 外连接
    • 左外连接：select ...from 表A left join 表B on 条件...
    • 右外连接：select ...from 表A right join 表B on 条件...
  • 自连接：select ... from 表A 别名1,表A 别名2 where 条件...
  • 子查询：列子查询、行子查询、表子查询

内连接和外连接的区别

  • 内连接取出连接表中匹配到的数据，匹配不到的不保留
  • 外连接取出连接表中匹配到的数据，匹配不到的也会保留，其值为NULL。
    • 左外连接，以左表为主表
    • 右外连接，以右表为主表
  • 以某一个表为主表后，进行关联查询，不管能否关联上，主表的数据都会保留，关联不上的以NULL显示

1.3 CHAR和VARCHAR的区别

  • 最大长度：char最大长度为255字符；varchar最大长度为65535个字节。
  • 定长：char是定长的，不足的部分用隐藏空格填充；varchar是不定长的。
  • 空间使用：char浪费空间；varchar更加节省空间。
  • 查找效率：varchar需要计算内容占用的长度，而char不会，所以char查找效率很高，varchar查找效率更低。

在项目中视不同场景选择使用，例如枚举值可选择使用char，描述信息或名字类可选择使用varchar

2 事务

事务（Transaction）：由多个操作组成的一个逻辑单元，这些操作要么都成功，要么都失败。

2.1 ACID

  • 原子性（Atomicity）：组成事务的操作，要么全部成功,要么全部失败，不可能只执行一部分操作。
  • 一致性（Consistency）：系统（数据库）总是从一个一致性的状态转移到另一个一致性的状态，不会存在中间状态。
  • 隔离性（Isolation）：通常，一个事务在完全提交之前，对其他事务是不可见的
  • 持久性（Durability）：一旦事务提交，那就将永远保持这样，哪怕系统崩溃也不会影响这个事务的结果。

【例】转账：A向B转账500，转账成功，A扣除500元，B增加500元

  • 原子操作体现在要么都成功，要么都失败
  • 在转账的过程中，数据要一致，A扣除了500，B必须增加500
  • 在转账的过程中，隔离性体现在A向B转账，不能受其他事务干扰
  • 在转账的过程中，持久性体现在事务提交后，要把数据持久化（落盘操作）

2.2 并发事务的潜在问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

  • 脏读（Dirty Read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
  • 丢失修改（Lost to Modify）：指在一个事务读取一个数据时，另一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失。
    【例】事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
  • 不可重复读（Unrepeatableread）：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据，则在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不一样。这就发生了在一个事务内两次读到的数据是不一样的情况。
  • 幻读（Phantom read）：幻读与不可重复读类似。一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，如同发生了幻觉一样，因此称为幻读。

解决方法：对事务进行隔离

2.3 MySQL的隔离级别

MySQL的四种隔离级别如下：

2.3.1 未提交读（READ UNCOMMITED）

该隔离级别下，其他事务可以看到本事务没有提交的部分修改。因此会造成脏读的问题（读取到了其他事务未提交的部分,而之后该事务进行了回滚）。这个级别的性能没有足够大的优势，但是又有很多的问题，因此很少使用。

# 插入数据
insert into goods_innodb(name) values('华为');
insert into goods_innodb(name) values('小米');

# 会话一
set session transaction isolation level read uncommitted;   # 设置事务的隔离级别为read uncommitted
start transaction;      # 开启事务
select * from goods_innodb;     # 查询数据

# 会话二
set session transaction isolation level read uncommitted;   # 设置事务的隔离级别为read uncommitted
start transaction;      # 开启事务
update goods_innodb set name = '中兴' where id = 10;    # 修改数据

# 会话一
select * from goods_innodb;     # 查询数据

2.3.2 已提交读（READ COMMITED）

其他事务只能读取到本事务已经提交的部分。该隔离级别有不可重复读的问题，在同一个事务内的两次读取，拿到的结果可能不一样，因为可能存在另一个事务对数据进行了修改。

# 会话一
set session transaction isolation level read committed; # 设置事务的隔离级别为read committed
start transaction;  # 开启事务
select * from goods_innodb; # 查询数据

# 会话二
set session transaction isolation level read committed;     # 设置事务的隔离级别为read committed
start transaction;      # 开启事务
update goods_innodb set name = '中兴' where id = 1; # 修改数据

# 会话一
select * from goods_innodb;     # 查询数据

# 会话二
commit;     # 提交事务

# 会话一
select * from goods_innodb;     # 查询数据

2.3.3 可重复读（REPEATABLE READ）

【MySQL的默认隔离级别】该隔离级别解决了上面不可重复读的问题(看名字也知道)，但不能完全解决幻读。

select @@tx_isolation;

REPEATABLE READ

# 解决不可重复读
# 会话一
start transaction;  # 开启事务
select * from goods_innodb; # 查询数据

# 会话二
start transaction;  # 开启事务
update goods_innodb set name = '荣耀' where id = 1;  # 修改数据

# 会话一
select * from goods_innodb; # 查询数据

# 会话二
commit;     # 提交事务

# 会话一
select * from goods_innodb; # 查询数据
# 测试不会出现幻读的情况
# 会话一
start transaction;  # 开启事务
select * from goods_innodb; # 查询数据

# 会话二
start transaction;  # 开启事务
insert into goods_innodb(name) values('小米');  # 插入数据
commit;     # 提交事务

# 会话一
select * from goods_innodb; # 查询数据
# 测试出现幻读的情况
# 表结构进行修改
ALTER TABLE goods_innodb ADD version int(10) NULL;

# 会话一
start transaction;  # 开启事务
select * from goods_innodb where version = 1;   # 查询一条不满足条件的数据

# 会话二
start transaction;  # 开启事务
insert into goods_innodb(name, version) values('vivo', 1);    # 插入一条满足条件的数据 
commit;     # 提交事务

# 会话一
update goods_innodb set name = '金立' where version = 1;    # 将version为1的数据更改为'金立'
select * from goods_innodb where version = 1;   # 查询一条不满足条件的数据

2.3.4 可串行化（SERIALIZABLE）

最高的隔离级别，可以解决上文提到的所有问题，因为该级别强制将所有的操作串行执行，这会导致并发性能极速下降，因此不很常用。

3 索引

索引（Index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

3.1 常用的存储引擎

  • MyISAM存储引擎：访问快，不支持事务和外键。表结构保存在.frm文件中，表数据保存在.MYD文件中，索引保存在.MYI文件中。
  • InnoDB存储引擎：MySQL5.5版本后的默认存储引擎，支持事务，占用磁盘空间大，支持并发控制。表结构保存在.frm文件中，如果是共享表空间，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。如果是多表空间存储，每个表的数据和索引单独保存在.ibd中。
  • MEMORY存储引擎：内存存储，速度快，不安全，适合小量快速访问的数据。表结构保存在.frm中。

特性对比

特性 MyISAM InnoDB MEMORY
事务安全 不支持 支持 不支持
锁机制 表锁 表锁/行锁 表锁
外键 不支持 支持 不支持

3.2 索引的底层数据结构

3.2.1 二叉树

  • 二叉树作为索引结构会存在以下缺点：
    • 顺序插入时，会形成一个链表，查询性能大大降低。
    • 大数据量情况下，层级较深，检索速度慢。

二叉树

  • 红黑树是一颗自平衡二叉树，也存在一个缺点：
    • 大数据量情况下，层级较深，检索速度慢。

红黑树

因此在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+Tree。

3.2.2 B树

B树（B-Tree）是一种多路平衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。以一棵最大度数为5（5阶）的B树为例：

  • $n$ 阶的B树，每一个节点最多存储 $n-1$ 个key，对应 $n$ 个指针。
  • 一旦节点存储的key数量到达 $n$ ，就会裂变，中间元素向上分裂。
  • 在B树中，非叶子节点和叶子节点都会存放数据。

B树

【例】插入一组数据：100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250，结果如下

insert b-tree

3.2.3 B+树

B+树（B+Tree）是B树的变种，以一颗最大度数为4（4阶）的B+树为例，如下图所示

  • 绿色部分为索引部分，仅起到索引数据的作用，不存储数据。
  • 红色部分为数据存储部分，在叶结点中要存储具体的数据。（所有的数据都会出现在叶结点）
  • 叶结点形成一个单链表。

B+Tree

【例】插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250，结果如下
insert b+tree

MySQL索引数据结构对经典的B+Tree进行了优化：在原B+Tree的基础上，将叶结点单链表改为了循环双链表，提高区间访问的性能，利于排序。

mysql b+tree

3.3 聚集索引和二级索引

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

  • 聚集索引（Clustered Index）：将数据存储与索引放在一起，索引结构的叶结点保存了行数据。特点为必须有，且只有一个。
  • 二级索引（Secondary Index）：将数据与索引分开存储，索引结构的叶结点关联的是对应的主键

聚集索引选取规则：

  1. 如果存在主键，主键索引就是聚集索引。
  2. 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  3. 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引和二级索引的具体结构如下：

聚集索引和二级索引

  • 聚集索引的叶结点下挂的是这一行的数据 。
  • 二级索引的叶结点下挂的是该字段值对应的主键值。

3.4 回表查询

回表查询： 先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值获取数据。

【例】执行一条sql语句select * from user where name = 'Arm'，其中name字段已经创建了索引

回表查询

具体过程如下:

  1. 由于是根据name字段进行查询，所以先根据name = 'Arm'到name字段的二级索引中进行匹配查找。在二级索引中只能查找到Arm对应的主键值10。
  2. 由于查询返回的数据是*，所以此时还需根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
  3. 最终拿到这一行的数据并返回。

3.5 索引创建原则

  1. 针对于数据量较大，且查询比较频繁的表建立索引。
  2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

  • 针对于数据量较大，且查询比较频繁的表建立索引。
    针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
  • 不适合常见索引的字段
    • 更新频繁字段不适合创建索引
    • 若是不能有效区分数据的列不适合做索引列（如性别，区分度太低）
    • 对于那些查询中很少涉及的列，重复值比较多的列不建立索引（如省会，城市、月份）
    • 对于定义为text、image和bit的数据类型的列不建立索引

3.6 左前缀原则

如果索引了多列（联合索引），要遵守左前缀法则——查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效（后面的字段索引失效）。

【例】查看之前的tb_user表所创建的索引

左前缀原则

在该表中有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status。
查询时，最左边的列即profession必须存在，否则索引全部失效。 而且中间不能跳过某一列，否则该列后面的字段索引将失效。

3.7 覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

【例】执行SQL语句 select * from tb_user where id = 2;，根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

覆盖原则

超大分页问题：在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。
优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

4 调优

4.1 定位慢查询

可以开启MySQL的慢查询日志，其中记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，重启MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。

4.2 分析SELECT语句

若一个SQL语句执行很慢，可采用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

分析SELECT语句

Explain执行计划中各个字段的含义：

字段 含义
id select查询的序列号，表示查询中执行select子句或者是操作表的顺序
id相同，执行顺序从上到下；id不同，值越大，越先执行
select_type 表示 SELECT 的类型
常见的取值有：SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
type 表示连接类型，性能由好到差分为：NULL、system、const、eq_ref、ref、range、 index、all
possible_key 显示可能应用在这张表上的索引，一个或多个
key 实际使用的索引，如果为NULL则没有使用索引
key_len 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好
rows MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
filtered 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好
Extra 额外的建议

主要可以根据以下字段，判断sql是否需要优化，特别是是否能命中索引或命中索引的情况

  • type：通过sql的连接的类型进行优化
  • possible_key：通过它查看是否可能会命中索引
  • key：当前sql实际命中的索引
  • key_len：索引占用的大小
  • Extra：额外的优化建议

4.3 表的设计优化

  • 选择表合适存储引擎：
    • myisam: 应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。
    • Innodb： 事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。尽量 设计 所有字段都得有默认值,尽量避免null。
  • 选择合适的数据类型
    • 数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型，一般来说，数据库中的表越小，在它上面执行的查询也就会越快。
    • 设置合适的数值（tinyint int bigint），要根据实际情况选择
    • 设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低

4.4 索引优化

  • 表的主键、外键必须有索引
  • 数据量大的表应该有索引
  • 经常与其他表进行连接的表，在连接字段上应该建立索引
  • 经常出现在where子句中的字段，特别是大表的字段，应该建立索引
  • 索引应该建在选择性高的字段上 （sex性别这种就不适合）
  • 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引
  • 频繁进行数据操作的表，不要建立太多的索引
  • 删除无用的索引，避免对执行计划造成负面影响

4.5 sql语句优化

  • SELECT语句务必指明字段名称（避免直接使用select *）
  • SQL语句要避免造成索引失效的写法
  • SQL语句中IN包含的值不应过多
  • 当只需要一条数据的时候，使用limit 1
  • 如果排序字段没有用到索引，就尽量少排序
  • 如果限制条件中其他字段没有索引，尽量少用or
  • 尽量用union all代替union
  • 避免在where子句中对字段进行null值判断
  • 不建议使用%前缀模糊查询
  • 避免在where子句中对字段进行表达式操作
  • join优化：能用innerjoin 就不用left join right join，如必须使用 一定要已小表为驱动

4.6 主从复制、读写分离

当读的操作比较多的时候，为了避免写的操作所造成的性能影响，可以采用读写分离的架构。

基本原理：让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。主从复制指将事务性操作导致的变更同步到集群中的从数据库。

5 分库分表

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：

  1. IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。 请求数据太多，带宽不够，网络IO瓶颈。
  2. CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。

为了解决上述问题，需要对数据库进行分库分表处理。
分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

分库分表

5.1 主从同步

MySQL主从复制的核心就是二进制日志（BINLOG），二进制日志记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

具体的主从同步过程如下，分为三步：

  1. Master主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。
  2. 从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log。
  3. slave重做中继日志中的事件，改变反映它自己的数据。

主从同步

5.2 主从同步延时

5.2.1 延时原因

  • 随机重放：MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高。Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随机的，成本高很多。所以SQL Thread线程的速度赶不上主库写binlog的速度，就会产生主从延迟。
  • 锁等待：由于SQL Thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待，由此产生了延时。

5.2.2 解决方案

  1. 强制读主库：如果是类似支付这种对实时性要求非常高的业务，那么最直接的方法就是直接读主库，当然这种方法相当于从库做一个备份的功能了。
  2. 延迟读：在写入之后等一段时间再读。【例】写入后同步的时间是0.5s，读取的时候可以设置1s后再读，但是该方案主要存在的问题是不知道主从同步完成所需要的时间。
  3. 降低并发：对于随机重放的问题，控制主库写入的速度，由此自然减小主从延迟发生的概率。（原因：主库中sql可能并发执行，可以控制并发速度）
  4. 并行复制：推荐用此方案。MySQL 5.6版本后，通过将SQL线程转换为多个work线程来进行重放，由此可解决主从延迟的问题。

5.3 拆分策略

分库分表的形式主要是两种：垂直拆分和水平拆分。而拆分的粒度一般又分为分库和分表，所以组成的拆分策略最终如下图所示：

拆分策略

根据具体的业务需求具体分析，选择合适的拆分策略。

5.3.1 垂直拆分

  • 垂直分库：以表为依据，根据业务将不同表拆分到不同库中。
    • 每个库的表结构都不一样
    • 每个库的数据也不一样
    • 所有库的并集是全量数据

垂直分库

  • 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。
    • 每个表的结构都不一样
    • 每个表的数据也不一样，一般通过一列（主键/外键）关联
    • 所有表的并集是全量数据

垂直分表

5.3.2 水平拆分

  • 水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。
    • 每个库的表结构都一样
    • 每个库的数据都不一样
    • 所有库的并集是全量数据

水平分库

  • 水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。
    • 每个表的表结构都一样
    • 每个表的数据都不一样
    • 所有表的并集是全量数据

水平分表

5.4 实现技术

  • shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。
  • MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

MyCat

5.5 分库分表后id主键如何处理

  • 主键自增：数据库中间件可以设置主键的自增

主键自增

  • UUID：优点为本地生成，不需要基于数据库。缺点为UUID太长、占用空间大，作为主键性能太差——UUID不具有有序性，会导致B+树索引在写操作时有过多的随机写操作，不能产生有序的append操作，而需要进行insert操作，这将读取整个B+树节点到内存，在插入这条记录后再将整个节点写回磁盘，这种操作在记录占用空间较大的情况下性能下降明显
  • 雪花（snowflake）算法

snowflake算法

6 MySQL锁相关

6.1 锁的种类

按照锁的粒度可分为以下三类：

  • 全局锁：锁定数据库中的所有表。
  • 表级锁：每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
  • 行级锁：每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

6.2 意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁减少表锁的检查，使得表锁不用检查每行数据是否加锁。

【例】客户端一对表加了行锁后，客户端二给表加表锁的操作在有无意向锁的情形下分别如下所示

6.2.1 无意向锁

  1. 客户端1开启一个事务，然后执行DML操作，在执行DML语句时会对涉及到的行加行锁。

无意向锁1

  1. 当客户端2想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有则添加表锁，此时会从第一行数据检查到最后一行数据，效率较低。

无意向锁2

6.2.2 有意向锁

  1. 客户端1在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。

有意向锁1

  1. 其他客户端在对这张表加表锁时，会根据该表上所加的意向锁来判定是否可以成功加表锁，无需逐行判断行锁情况。

有意向锁2

6.3 共享锁和排他锁

InnoDB实现了以下两种类型的行锁：

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
  • 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

常见的SQL语句在执行时所加的行锁如下：

SQL 行锁类型 说明
INSERT ... 排他锁 自动加锁
UPDATE ... 排他锁 自动加锁
DELETE ... 排他锁 自动加锁
SELECT（正常） 不加任何锁
SELECT ... LOCK IN SHARE MODE 共享锁 需手动在SELECT后加LOCK IN SHARE MODE
SELECT ... FOR UPDATE 排他锁 需手动在SELECT后加FOR UPDATE

6.4 MVCC

MVCC（Multi-Version Concurrency Control，多版本并发控制）指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供的一个非阻塞读功能。MVCC的具体实现还需要依赖于数据库记录中的三个隐式字段、undo log日志、ReadView。

6.4.1 隐藏字段

隐藏字段

当创建了上表后，实际上除了这三个字段以外，InnoDB还会自动添加如下隐藏字段：

隐藏字段 含义
DB_TRX_ID 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID
DB_ROLL_PTR 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本
DB_ROW_ID 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段，否则不会生成

6.4.2 undo log与版本链

回滚日志（Undo Log）：在insert、update、delete的时候产生的便于数据回滚的日志。

  • 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
  • 当update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

【例】有一张表原始数据如下：

undo log与版本链

有四个并发事务同时在访问这张表：

  1. 当事务2执行第一条修改语句时，会将数据变更之前的样子记录于undo log日志；然后更新记录，并记录本次操作的事务ID。

版本链表1

  1. 当事务3执行第一条修改语句时，也会将数据变更之前的样子记录于undo log日志；然后更新记录，并记录本次操作的事务ID。

版本链表2

  1. 当事务4执行第一条修改语句时，也会将数据变更之前的样子记录于undo log日志；然后更新记录，并记录本次操作的事务ID。

版本链表3

  1. 最终观察可知，生成了一条版本链表。

6.4.3 ReadView

ReadView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

ReadView包含四个核心字段：

字段 含义
m_ids 当前活跃的事务ID集合
min_trx_id 最小活跃事务ID
max_trx_id 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id ReadView创建者的事务ID

在ReadView中规定了版本链数据的访问规则（其中trx_id代表当前undo log版本链对应事务ID）：

条件 是否可以访问 说明
trx_id == creator_trx_id 可以访问该版本 数据是当前这个事务更改的
trx_id < min_trx_id 可以访问该版本 数据已经提交
trx_id > max_trx_id 不可以访问该版本 该事务在ReadView生成后才开启
! min_trx_id <= trx_id <= max_trx_id 若trx_id不在m_ids中，则可以访问该版本 数据已经提交

不同的隔离级别生成ReadView的时机不同：

  • READ COMMITTED （RC）：在事务中每一次执行快照读时生成ReadView。
  • REPEATABLE READ（RR）：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

6.4.4 原理分析

6.4.4.1 RC隔离级别

RC隔离级别下，在事务中每一次执行快照读时生成ReadView。

【例】继续执行6.4.2中的4个并发事务，分析事务5中两次快照读是如何获取数据的。

在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读都会生成一个ReadView，那么两次生成的ReadView如下。

RC隔离级别

这两次快照读在获取数据时，需要根据所生成的ReadView以及ReadView的版本链访问规则到undo log版本链中匹配数据，最终决定此次快照读返回的数据。

  • 第一次快照读具体的读取过程
    1. 先匹配红色部分记录，对应的trx_id为4，带入右侧的匹配规则得，①、②、③、④均不满足，继续匹配undo log版本链的下一条。
    2. 再匹配绿色部分第1条记录，对应的trx_id为3，带入右侧的匹配规则得，①、②、③、④均不满足，继续匹配undo log版本链的下一条。
    3. 再匹配绿色部分第2条记录，对应的trx_id为2，带入右侧的匹配规则得，②满足，终止匹配，此次快照读返回的数据就是版本链中记录的这条数据。

第一次快照读

  • 第二次快照读具体的读取过程
    1. 先匹配红色部分记录，对应的trx_id为4，带入右侧的匹配规则得，①、②、③、④均不满足，继续匹配undo log版本链的下一条。
    2. 再匹配绿色部分第1条记录，对应的trx_id为3，带入右侧的匹配规则得，②满足，终止匹配，此次快照读返回的数据就是版本链中记录的这条数据。

第二次快照读

6.4.4.2 RR隔离级别

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

RR隔离级别

在一个事务中，执行两次相同的select语句，查询到的结果一样，因为ReadView都一样， ReadView的版本链匹配规则也一样， 那么最终快照读返回的结果也一样。

MVCC + 锁实现了事务的隔离性，一致性则由redo log与undo log保证。

MVCC + redo log + undo log

发表评论