MySQL 的学习

-

关于 MySQL 基础, 架构, 事物, 并发, 索引的笔记.

内容主要来自于<高性能 MySQL>.

MySQL架构和基础

MySQL逻辑架构图

大致可分为三层

  • 连接层: 不是MySQL独有的, 大部分C/S架构都有类似的.
  • 缓存/解析器/优化器层: 大多数核心功能服务在这里, 所有跨存储引擎的存储过程, 触发器, 视图等也在这层.
  • 存储引擎层: 服务器层(上面两层)通过API与存储引擎层通信, 这一层不会去解析SQL, 不同存储引擎间也不会相互通信, 只是简单的响应上层服务器的请求.

缓存/解析/优化与执行

缓存

对于SELECT语句在解析之前会先检查查询缓存(Query Cache), 如果能命中缓存服务器就不再执行查询解析, 优化和执行的整个过程.

解析

MySQL会解析查询, 创建内部数据结构(解析树), 并对其进行优化(重写查询, 决定表的读写顺序), 选择合适的索引等.

优化器

用户可以通过关键字影响优化器的决策进程, 也可以请求优化器解释(explain)它是如何做优化决策的. 优化器通过请求存储引擎提供的信息来做优化. 优化器是存储引擎无关的, 但不同的存储引擎会影响优化器的决策.

并发控制

可以通过两种类型的锁系统来实现并发控制, 分别是共享锁(shared lock)和排他锁(exclusive lock), 也叫读锁(read lock)和写锁(write lock). 读锁是共享的, 不会相互阻塞, 在同一时刻可以同时读取同一个资源. 而写锁是排他的, 一个写锁会阻塞其他的读锁和写锁.

锁粒度与锁策略

一般锁定的资源数据量越少(锁粒度越小), 那么系统的并发程度就越高. 但是锁的各种操作(获得锁, 检查锁, 释放锁)会消耗时间和资源从而影响系统性能. 锁策略就是在锁的开销和数据的安全性之间寻求平衡. MySQL中两种重要的锁策略:

  • 表锁(table lock): MySQL中最基本的开销最小的策略, 写锁能插入到锁队列中的读锁前.
  • 行级锁(row lock): 行级锁可以最大程度地支持并发处理(同时也带来了最大的锁开销). 行级锁只在存储引擎层实现.

事务

事务并非专属MySQL, 它是构成单一逻辑操作的操作集合.
一个良好的事务处理系统必须具备ACID特性.

  • 原子性(atomicity): 一个事务必须被视为一个不可分割的最小工作单位.
  • 一致性(consistency): 数据库总是从一个一致性(有效正确)的状态转换到另外一个一致性(有效正确)的状态.
  • 隔离性(isolation): 并发访问时, 事务之间相互影响的程度.(可以通过修改隔离级别来控制)
  • 持久性(durability): 事务提交后修改会永久保存到数据库中.(持久性也分很多种不同级别)

MySQL中可以根据业务来选择是否需要支持事务的存储引擎.

隔离级别

较低级别的隔离通常可以执行更高的并发, 系统的开销也更低.
在并发执行事务时通常会遇到下列问题:

  • 脏读(dirty read): 事务A读取了事务B修改了但未提交的数据.
  • 不可重复读(nonrepeatable read): 事务A读取了数据后, 事务B修改了该数据并提交, 未提交的事务A再次读取同样的数据发现和之前读的时候不一致.
  • 幻读(phantom read): 事务A读取了某个范围内的记录, 事务B在该范围内新增了记录并提交, 事务A再次读取该范围记录时就会产生幻行(phantom row).

为解决上述问题, 就有了隔离级别的概念:

  • READ UNCOMMITTED(未提交读): 事务中的修改即使没有提交对其他事务也是可见的, 因此会导致脏读, 不可重复读, 幻读.
  • READ COMMITTED(提交读): 解决了脏读问题, 但是还会有不可重复读, 幻读的问题. 因此这个级别也叫不可重复读.
  • REPEATABLE READ(可重复读): 解决了脏读和不可重复读的问题, 但是还会有幻读的问题.
  • SERIALIZABLE(可串行化): 最高的隔离级别, 通过强制事务串行化执行, 避免了上述的问题.

和大多数数据库的默认隔离级别(READ COMMITTED)不一样, MySQL的默认隔离级别是REPEATABLE READ.

多版本并发控制(MVCC, multiversion concurrency control)

MySQL中不同存储引擎的MVCC实现是不同的, 它可以使大多数读操作不用加锁, 因此开销更低. InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的. 一个保存了行的创建版本号, 一个保存行的删除版本号. 而这个版本号是系统自动递增的事务版本号. 在InnoDB可重复读隔离级别下, MVCC是如何操作的:

  • SELECT
    • a.只查找创建标识早于当前事务版本号的行.
    • b.行的删除标识未定义, 或者大于当前事务版本号.
  • INSERT
    • 保存当前系统版本号作为创建标识.
  • DELETE
    • 保存当前系统版本号作为删除标识.
  • UPDATE
    • 同时保存当前版本号为创建标识和删除标识.

MVCC只工作在READ COMMITTED和REPEATABLE READ隔离级别下, 因为READ UNCOMMITTED总是读取最新数据, 不符合当前事务版本的数据行. SERIALIZABLE则会对所有行加锁.

Next-Key Lock

MySQL InnoDB支持三种锁定方式, 默认加锁方式是next-key lock锁.

  • 行锁(Record Lock): 锁直接加在索引记录上, 锁住key.
  • 间隙锁(Gap Lock): 锁定索引记录间隙, 确保索引记录的间隙不变.
  • Next-Key Lock: 行锁和间隙锁的组合.

间隙锁在InnoDB的作用就是防止其他事务的插入操作, 以此来防止幻读的发生. 使用间隙锁的条件:

  • 1.必须在RR隔离级别下
  • 2.检索条件必须有索引(全表扫描会锁定整张表)

索引的类型

MySQL中索引是在存储引擎层而不是服务器层实现的, 所以相同类型的索引在不同存储引擎下的工作方式可能并不一样. 而InnoDB底层使用的是B+树数据结构来存储数据.

B-Tree索引

一般索引没有特殊说明那多半说的就是B-Tree索引了. 但是很多说是用B-Tree数据结构存储数据的实际上用的又是B+Tree, 比如InnoDB, 所以就放在InnoDB说了.

哈希索引, 空间数据索引, 全文索引, 其他索引类别…

// TODO

存储引擎

InnoDB

InnoDB使用的是B-Tree索引, 但底层实现使用的是B+Tree数据结构来存储数据.
同时InnoDB使用的是聚簇索引的数据存储方式, 具体的底层实现方式是在同一个结构中保存了索引的键(Key)和数据行.

最小存储单位

磁盘的基本存储单位是扇区(sector), 一般来说硬盘驱动器(HDD)的一个扇区大小为512字节, 所以基于这种磁盘建立的文件系统或者什么都好, 占用的磁盘空间一定都是512字节的倍数. 比如你在NTFS文件系统中创建一个文件, 打了两个字符进去保存, 然后点开属性你就会发现文件大小是2字节, 但是占用空间确是4K(实际上我自己亲自测试了一下发现并不是这样, 大小是2字节没错可是占用空间是0字节, 这是因为这个文件实在太小了, NTFS直接把数据存在文件元数据中了, 也就是NTFS MFT). 因为这种文件系统的基本存储单位是4K字节, 占用8个扇区. 而InnoDB的最小存储单位叫页(Page), 它默认占用16K字节, 你也可以手动看看:

1
SHOW VARIABLES LIKE 'innodb_page_size';

默认输出的值是16384, 是这么多个字节的意思, 16384/1024=16K.

主键索引

InnoDB的索引底层由树组成, 因此主要有两种节点, 这些节点每个都会占用一页, 一种大概长这样, 是非叶子结点:

我这画图功夫希望你看得懂… 红色的代表Key, 绿色代表存储指针的地方, 蓝色就代表页了. 一个页除了Key和指针之外还有一些这个页的元数据, 比如页号啊, 用于事务的事务ID, MVCC回滚指针什么的这里没画出来.

另一种节点是叶子节点, 大概长这样:

依然是占用一整页, 其中红色代表的是Key, 黄色代表的是数据.

使用InnoDB创建一张表时, 会默认使用主键创建聚簇索引, 这个通过主键创建的索引树也叫主键索引, 它的叶子节点存储的是整行数据. 我们创建表:

1
2
3
4
5
6
7
CREATE TABLE people
(
    pel_id           int AUTO_INCREMENT PRIMARY KEY,
    pel_first_name      char(16),
    pel_last_name     char(16),
    pel_birthday  datetime
);

然后插入10行数据, 那么通过主键创建的索引(主键索引)大概是长这个样子的(为了便于理解我们这里一个页中只存放3条记录,实际情况可以存放很多):

(图中的一处错误:叶子节点的指针应该是双向的) 图中红色的代表Key, 黄色是数据字段值, 绿色的正方形小方块代表指针. 可以看到在叶子节点页中, 不仅保存了主键Key, 还保存数据行的所有数据, 术语”聚簇”就是表示数据行和相邻的键值紧凑地存储在一起(并不总是这样). 因此InnoDB的主键索引也可以叫聚簇索引(clustered index). 而非叶子的节点页中只包含了索引列, 这个例子中的索引列是红色的地方, 也就是对应表中的pel_id.

二级索引(secondary indexes)

二级索引和聚簇索引差不多, 主要是叶子节点的数据变成了Key+主键, 如果按照上面的例子创建一个由pel_birthday做Key的索引.

1
CREATE INDEX people_pel_birthday_index ON people(pel_birthday);

那么树大概长这个样子:

(图中的一处错误:叶子节点的指针应该是双向的) 可以看到这个二级索引叶子节点的数据实际上是主键值, 因此如果主键值过大, 那么索引占用的空间就大. 要是想获取行的其它数据还要通过这个主键再回主键索引中查询, 这个步骤称作回表.

覆盖索引

这个不是InnoDB特有的, 这是满足某种情况我们就说索引覆盖. 如果一个索引包含了所有需要查询字段的值, 那么就无需回表, 这就是覆盖索引. 如果WHERE条件中的列是有索引可以覆盖, 那么可以使用延迟关联的策略来尽可能利用索引.(延迟覆盖简单来说就是利用WHERE条件中能被索引覆盖的条件查出对应的主键, 再使用这些主键从主键索引做连接查询过滤等, 提高效率)

// todo:可预测性读, 自适应哈希, 插入缓冲区…

MyISAM

和InnoDB不同, MyISAM的主键索引和二级索引数据分布则要简单得多.

数据分布

MyISAM是按照数据插入的顺序存储在磁盘上, 同样以上面的表为例子, 假设我把上面InnoDB的数据dump出来然后随机插入到MyISAM引擎的表, 那么插进去的数据在MyISAM的分布方式是这样的:

在行的旁边显示了行号(或者说是地址), 从0开始递增. 它会根据数据定长还是变长的行使用不同的策略来跳过行号.

主键索引

MyISAM索引的叶子节点存放的是数据的行号(或者说是地址). 在索引找到行号(或者说是地址)后, 就会到数据分布中取出对应的记录.

(图中的一处错误:叶子节点的指针应该是双向的)

二级索引

MyISAM的其它索引和主键索引大同小异. 比如使用pel_birthday做索引:

(图中的一处错误:叶子节点的指针应该是双向的)

可以看到和主键索引一样, 叶子节点保存的也是数据的行号.

InnoDB和MyISAM数据和索引区别

高性能MySQL(第3版)中作者画了一张抽象图来描述.

图就比较直观了.

上面只用了单键值的索引做例子, 而多键值的索引大概是这样子的:

index (a,b)的联合索引结构如上图(出自《MySQL技术内幕-InnoDB存储引擎》), 观察到每个节点中均同时包含a,b两个字段信息, 且字段a全局有序, 字段b局部有序(仅在字段a值相同时, 字段b是有序的).

MySQL执行计划Extra

  • Using index: 查询的列被索引覆盖, 并且WHERE筛选条件是索引的前导列.
  • Using where Using index: 查询的列被索引覆盖, 并且WHERE筛选条件是索引列之一, 可能不是索引的前导列. 这里的Using where代表使用了过滤，跟是否回表没有关系. 索引列的范围搜索也会出现它.
  • Using index condition: 查询的列不全在索引中, 查询条件可以用到索引.(这个需要开启索引条件下推(ICP, Index-Condition-Pushdown)功能才有)

参考:
高性能MySQL(第3版)
面试题：InnoDB中一棵B+树能存多少行数据？
MySQL InnoDB Sorted Index Builds
MySQL索引下推