全方位解析 MySQL 及相关面试题

写在前面

• 文章是在前人的基础上进行总结整理再加上自己的一点理解，仅作为自己学习的记录，不作任何商业用途！
• 如果在文章中发现错误或者侵权问题，欢迎指出，谢谢！

MySQL 架构

• MySQL 采用的是 C/S 架构，即客户端(Client)与服务端(Server)架构，我们在使用 MySQL 的时候，都是以客户端的身份，发送请求连接到运行服务端的 MySQL 守护进程，MySQL 服务器端则根据我们的请求进行处理并把处理后的结果返回给我们
• 和其它数据库相比，MySQL 的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎
• 架构图

• 从上图可以看出来 MySQL 主要分为连接层、服务层、引擎层和存储层
  • 连接层：主要是和客户端进行连接，负责连接和授权认证等工作
  • 服务层：主要完成大部分的核心服务功能， 包括查询缓存、SQL 解析、优化等操作，而 服务层与存储引擎之间的交互则通过统一的 API 进行调用
  • 引擎层：存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取
  • 存储层：主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互

连接管理器

• 连接管理器的作用是管理和维持所有 MySQL 客户端的请求连接，当我们向 MySQL 发起请求时，连接管理器会负责创建连接并校验用户的权限

查询缓存

• 当我们建立连接以后，如果执行的是 select 操作，那么连接器会先从查询缓存中查询之前是否执行过这条语句
  • 如果执行过则会返回缓存结果
  • 如果没有执行过则会继续后面的操作
• 查询缓存是把查询语句当作一个 key，查询结果当用 value，建立起来的 key-value 缓存结构

• 不推荐使用查询缓存
  • 因为只要数据库表发生了更新则会将所有的查询缓存给清除掉，所以往往都是不能够命中查询缓存的，存在的意义不大

解析器

• 这条 SQL 要做什么

• 当在查询缓存中没有命令查询时，则需要真正执行语句，这时候就交给解析器进行词法分析和语法分析

优化器

• 这条 SQL 如何做

• 经过分析器的词法和语法分析，MySQL 服务器已经知道这条 SQL 语句要做什么了，但是还需要进行优化，比如
  • 当涉及到多个索引时，决定用哪个索引
  • 多表关联时，决定连接顺序

  # 该步骤决定了先从 t1 表中取出 c=10 的 ID 值还是先查找出 t2 表中 d=20 的 ID 值
  select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
  复制代码

执行器

• 真正的执行步骤

• SQ L语句在经过查询优化器的优化后，接下来就交由执行器开始执行，不过执行器在开始执行前，会判断用户对相应的数据表是否有权限
• 若有权限，打开表后根据表的引擎定义，使用引擎提供的接口向底层的存储发送相应的指令，存储引擎负责具体执行，并将执行结果告诉执行器，然后再返回给客户端

存储引擎

• 后文讲解

相关面试题

• Q：问数据库的架构图？
  • 总体来说分为四层，连接层用来连接客户端并进行授权认证，服务层是主要的核心部分，包含了查询缓存、解析器、优化器、执行器等部分，引擎层是 MySQL 真正负责数据存取，不同的引擎有不同的功能，存储层则是将数据存储在文件系统中并和引擎层进行交互
• Q：一条查询的 SQL 语句是怎么执行的？
  • 就是上图中每一流程中描述的过程

存储引擎

• MySQL 支持 9 种存储引擎

• 存储引擎，也叫做表类型，具体作用便是决定一个数据表怎么处理和存储表中的数据，MySQL 支持多种不同的存储引擎，而且存储引擎被设计为可插拔式的，在同一个数据库中不同的数据表可以使用不同的存储引擎

查询存储引擎

• 查询 MySQL 支持的存储引擎

  mysql > show engines
  复制代码

  +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
  +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
  | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
  | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
  | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
  | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
  | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
  | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
  | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
  | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
  +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  9 rows in set (0.00 sec)
  复制代码

• 查询表的存储引擎

  --查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
  show create table tablename
  
  --准确查看某个数据库中的某一表所使用的存储引擎
  show table status like 'tablename'
  show table status from database where name="tablename"
  复制代码

指定存储引擎

• Innodb 是 MySQL 的默认存储引擎

• 指定 user 表的存储引擎

  CREATE TABLE users(
      uid int not null,
      username varchar(32) not null,
      email varchar(64) not null,
      gender tinyint not null,
      primary key(uid)
  ) engine=MyISAM;
  复制代码

修改存储引擎

• 数据库表的存储引擎也可以修改，修改 user 表的存储引擎

  ALTER TABLE users ENGINE=InnoDB;
  复制代码

存储引擎对比

• 主要是指 InnoDB 和 MyISAM 两个存储引擎对比

  • 物理文件存储结构对比

    • MyISAM

      1. .frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
      2. .MYD (MYData) 文件：MyISAM 存储引擎专用，用于存储MyISAM 表的数据
      3. .MYI (MYIndex)文件：MyISAM 存储引擎专用，用于存储MyISAM 表的索引相关信息

    • InnoDB

      1. .frm 文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
      2. .ibd 文件或 .ibdata 文件： 这两种文件都是存放 InnoDB 数据的文件

      之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据

      独享表空间存储方式使用 .ibd 文件，并且每个表一个 .ibd 文件 共享表空间存储方式使用 .ibdata 文件，所有表共同使用一个或多个 .ibdata 文件

• 其他对比

	MyISAM	InnoDB
存储空间	MyISAM 可被压缩，存储空间较小	InnoDB 的表需要更多的内存和存储，因为它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
外键	不支持	支持
锁范围	表锁，即操作一条数据会锁住整张表，不适合高并发操作	行级锁，即操作一条数据仅锁住那一行，适合高并发操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，因此对内存要求较高，内存大小对性能有决定性的影响
AUTO_INCREMENT	MyISAM 表可以和其他字段一起建立联合索引	InnoDB中必须包含只有该字段的索引
SELECT	MyISAM 更优
INSERT		InnoDB 更优
UPDATE		InnoDB 更优
DELETE		InnoDB 更优
COUNT without WHERE	MyISAM 更优，因为 MyISAM 保存了表的具体行数	InnoDB 没有保存表的具体行数，需要逐行扫描统计，就很慢了
COUNT with WHERE	一样，都会锁住整张表	一样，都会锁住整张表
FULLTEXT全文索引	支持	不支持（5.6.4以上支持英文全文索引） 可以通过使用 Sphinx 从 InnoDB 中获得全文索引，会慢一点

相关面试题

• Q：MyISAM 和 InnoDB 的区别有哪些？

  1. InnoDB 支持事务，而 MyISAM 是不支持事务的。这也是 MySQL 把数据库默认引擎变为 InnoDB 的原因之一
  2. InnoDB 最小粒度的锁是行级锁，而 MyISAM 最小粒度的锁是表级索，执行一条更新语句会锁住整张表，导致其他查询和更新都会被阻塞，不适合高并发的场景。这也是 MySQL 把数据库默认引擎变为 InnoDB 的原因之一
  3. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败
  4. InnoDB 是聚簇索引，MyISAM 是非聚簇索引 (后文索引分类中讲解)
  5. InnoDB 不保存表的具体行数，执行 select count( * ) from table 时需要全表扫描，而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快

• Q：一张表里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，再把数据库重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15？

  • 如果表类型是 MyISAM，则是 18。因为 MyISAM 表会把自增主键的最大ID记录到数据文件中，重启数据库，自增主键的最大 ID 也不会丢失
  • 如果表类型是 InnoDB，则是 15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行 OPTION 操作，都会导致最大ID丢失

• Q：哪个存储引擎执行 select count( * ) 更快，为什么？

  • MyISAM 更快，因为 MyISAM 内部维护了一个计数器，把表的总行数存储在磁盘上，当执行 select count( * ) from t 时，直接返回总数据
  • 在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count( * ) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量

• Q：为什么 MyISAM 的读操作比 InnoDB 更快，写操作比 InnoDB 更慢？

  • 读操作更快是因为 MyISAM 是非聚簇索引，InnoDB 是聚簇索引。INNODB 在做 SELECT 的时候，要维护的东西比 MYISAM 引擎多很多
  • 写操作更慢是因为 MyISAM 是表级锁，更新操作会锁住整张表，而 InnoDB 是行级锁

• Q：MyISAM 和 InnoDB 的主键支持有什么不同？

  • MyISAM 数据表允许没有主键和其他索引，而 InnoDB 数据表如果没有主键的话，而会生成一个用户不可见 6 字节的主键

数据类型

• 主要包括五大类
  • 整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
  • 浮点数类型：FLOAT、DOUBLE、DECIMAL
  • 字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
  • 日期类型：Date、DateTime、TimeStamp、Time、Year
  • 其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection 等

相关面试题

• Q：CHAR 和 VARCHAR 的区别
  • 相同点
    • CHAR(n)，VARCHAR(n) 中的 n 都代表字符的个数
    • CHAR，VARCHAR 最大长度 n 的限制后，字符串会被截断
  • 不同点
    • CHAR 不论实际存储的字符数都会占用 n 个字符的空间，而 VARCHAR 只会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255）
      

      • 原因：VARCHAR 保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于255则使用两个字节来保存长度）

    • 能存储的最大空间限制不一样：CHAR 的存储上限为 255 字节
    • CHAR 在存储时会截断尾部的空格，而 VARCHAR 不会
  • 使用策略
    • 对于经常变更的数据来说，CHAR 比 VARCHAR 更好，因为 CHAR 不容易产生碎片。
    • 对于非常短的列，CHAR 比 VARCHAR 在存储空间上更有效率
• Q：BLOB和TEXT有什么区别？
  • BLOB 保存二进制数据，TEXT 保存字符数据

索引

索引是什么

• 索引(Index)是一种用于提升数据库查询效率的数据结构

索引的语法

• 创建索引

  CREATE INDEX nameIndex ON test(name)
  
  CREATE UNIQUE INDEX idIndex ON test(id)
  复制代码

  

  CREATE TABLE mytable ( 
      ID INT NOT NULL, 
      username VARCHAR ( 16 ) NOT NULL, 
      INDEX [ indexName ] ( username ( length )) 
  );
  复制代码

• 删除索引

  -- DROP INDEX [indexName] ON mytable
  DROP INDEX idIndex ON test
  复制代码

• 显示索引

  SHOW INDEX FROM test
  复制代码

  

索引的优缺点

• 优点
  • 提高数据检索效率，降低数据库 IO 成本
  • 降低数据排序的成本，降低 CPU 的消耗
• 缺点
  • 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息

索引的分类

• Hash 索引

  • Hash 索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。Memory 引擎默认使用的是此种索引

  • 存储引擎对所有的索隐列计算出一个 HashCode，将 HashCode 存储在索引中，同时 HashCode 中保存每个数据行的指针。这样对于此种索引查找速度是非常快的。出现哈希值碰撞的话，索引会以链表的形式存放多个记录指针到同一个哈希条目中

  • 检索算法

    • 在检索查询时，就再次对待查关键字再次执行相同的 Hash 算法，得到 HashCode，到对应 Hash 表对应位置取出数据即可，如果发生 Hash 碰撞，则需要在取值时进行筛选

  • 举例

    name	age
    Jane	28
    Peter	20
    Lily	30

    • 假设使用假想的哈希函数f()，生成对应的设想值：
      1. f(‘Jane’) = 2323
      2. f(‘Peter’) = 2456
      3. f(‘David’) = 2400
    • 则 Hash 索引的数据结构如下(Hash 表)

    槽(slot)	值(value)
    2323	指向第1行指针
    2400	指向第3行指针
    2456	指向第2行指针

    • 对于 select * from user where name = 'Jane' 那么直接先算 Jane 的 HashCode，然后根据Jane的 HashCode = 2323 去找到对应的第一行数据，查询速度相对于 B-Tree 索引是要快

  • MySQL 并没有显式支持 Hash 索引，而是作为内部的一种优化。具体在 Innodb 存储引擎里，会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，就为之建立 Hash 索引。因此，在 MySQL 的 Innodb 里，对于热点的数据会自动生成 Hash 索引。这种 Hash 索引根据其使用的场景特点，也叫自适应 Hash 索引

  • 缺点

    1. 不能避免读取行：哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显
    2. 无法用于排序：哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序
    3. 只支持等值查找：哈希索引只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同的操作）。不支持任何范围查询，例如 WHERE price>100
    4. 存在 Hash 冲突：当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行
    5. 不支持部分列匹配查找：这里面是使用索引列的全部内容来计算哈希值，例如(A,B)两列一起建索引，单纯使用 A 一列，那么就无法使用索引，B-Tree 索引的话，因为支持匹配最左前缀，所以这种情况适用性偏好

• B+Tree 索引

  • 这个是 MySQL 索引的基本实现方式。除了全文索引、Hash索引，Innodb、MyISAM 的索引都是通过 B+Tree 实现的

  • 本部分内容较多，会从以下几个方面进行讲述：
    1. 为何从二叉树变为选择 B+Tree
    2. 磁盘存取
    3. B-Tree 数据结构
    4. B+Tree 数据结构

  • 为何从二叉树变为选择 B+Tree

    • 二叉搜索树

      

      • 二叉搜索树具有以下性质：左子树的值小于根的值，右子树的值大于根的值
      • 对该二叉树的节点进行查找发现：深度为1的节点的查找次数为1，深度为2的查找次数为2，深度为n的节点的查找次数为n，因此其平均查找次数为 (1+2+2+3+3+3+3) / 7 = 2.4 次
      • 但是当这个二叉搜索树构造成以下形式

      

      • 其平均查找次数为 (1+2+3+4+5+6+6) / 7 = 4 次
      • 二叉搜索树缺点：如果树的太深（太高），那么这棵二叉树的查询效率就低了。因此若想二叉树的查询效率尽可能高，需要这棵二叉树是平衡的，从而引出新的定义——平衡二叉树，或称 AVLTree

    • 平衡二叉树

      • 平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为 1（图片来源于网络）

      

      • 如果在AVL树中进行插入或删除节点，可能导致AVL树失去平衡，这种失去平衡的二叉树可以概括为四种姿态：
        1. LL：LeftLeft，也称“左左”。插入或删除一个节点后，根节点的左孩子（Left Child）的左孩子（Left Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡
        2. RR：RightRight，也称“右右”。插入或删除一个节点后，根节点的右孩子（Right Child）的右孩子（Right Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡
        3. LR：LeftRight，也称“左右”。插入或删除一个节点后，根节点的左孩子（Left Child）的右孩子（Right Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡
        4. RL：RightLeft，也称“右左”。插入或删除一个节点后，根节点的右孩子（Right Child）的左孩子（Left Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡

      

      • AVL树失去平衡之后，可以通过旋转使其恢复平衡
      • 使用平衡二叉树作为索引的数据结构

      

      • 将每一个节点看成一块磁盘，磁盘的结构如图所示：
        1. 关键字：即我们建立索引的关键字段的对应值。
        2. 数据区：即关键字对应的数据存储磁盘位置，通过关键字所对应的磁盘位置进行IO读写操作获取数据
        3. 节点引用：即指向子节点的磁盘位置
      • 当我们需要查询 ID=8 的数据的时候，那么先会获取根节点 10 加载到内存中，比较数据大小，发现比 10 小，那么查找左节点5，发现比 5 大，查找 5 的右节点，发现命中，然后根据数据区地址去进行 IO读写操作
      • AVLTree 缺点：如果数据太深(或者说太高)，会导致频繁操作 IO，而 IO 又是一个耗时操作，因此引出了新的定义—— B-Tree

  • 磁盘存取

    • 局部性原理与磁盘预读
      • 由于存储介质的特性，磁盘本身存取就比主存慢很多，因此为了提高效率，要尽量减少磁盘 IO
      • 为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存
      • 预读可以提高I/O效率，预读的长度一般为页（page:计算机管理存储器的逻辑块-通常为 4k）的整倍数，主存和磁盘以页为单位交换数据
      • 当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中
    • 系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么
    • InnoDB 存储引擎默认每一页的大小为 16k，而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB
    • InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 IO 次数，提高查询效率，B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块

  • B-Tree 数据结构

    • 首先定义一条记录为一个二元组[key, data]，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据来描述 B-Tree

    • 一棵 m 阶的 B-Tree 有如下特性：
      1. 每个节点最多有 m 个孩子
      2. 除了根节点和叶子节点外，其它每个节点至少有 Ceil(m/2) 个孩子
      3. 若根节点不是叶子节点，则至少有 2 个孩子
      4. 所有叶子节点都在同一层，且不包含其它关键字信息
      5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
      6. 关键字的个数 n 满足：ceil(m/2)-1 <= n <= m-1
      7. ki(i=1,…n) 为关键字，且关键字升序排序。
      8. Pi(i=1,…n) 为指向子树根节点的指针，P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

    • 一个三阶的 B-Tree 结构如下图所示

    

    • 每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为 17 和 35，P1 指针指向的子树的数据范围为小于 17，P2 指针指向的子树的数据范围为 17~35，P3 指针指向的子树的数据范围为大于 35。模拟查找关键字 29 的过程：
      1. 根据根节点找到磁盘块 1，读入内存。【磁盘I/O操作第1次】
      2. 比较关键字 29 在区间（17,35），找到磁盘块1的指针P2
      3. 根据 P2 指针找到磁盘块 3，读入内存。【磁盘I/O操作第2次】
      4. 比较关键字 29 在区间（26,30），找到磁盘块3的指针P2
      5. 根据 P2 指针找到磁盘块 8，读入内存。【磁盘I/O操作第3次】
      6. 在磁盘块8中的关键字列表中找到关键字 29
    • 分析上面过程，发现需要 3 次磁盘I/O操作，和 3 次内存查找操作
    • 由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率，3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素
    • B-Tree 相对于 AVLTree 缩减了节点个数（因为 AVLTree 中一个节点只有一个关键字和两个指针），使每次磁盘 I/O 取到内存的数据都发挥了作用，从而提高了查询效率
    • B-Tree 缺点：从 B-Tree 结构图中可以看到每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，当 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率，因此引出新的定义——B+Tree

  • B+Tree

    • B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用B+Tree实现其索引结构

    • 在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度

    • B+Tree 相对于 B-Tree 有几点不同：
      1. B+Tree 节点关键字搜索采用闭合区间
      2. B+Tree 非叶节点不保存数据相关信息，只保存关键字和子节点的引用
      3. B+Tree 关键字对应的数据保存在叶子节点中
      4. B+Tree 叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系
      5. 每个节点的指针上限为 2d 而不是 2d+1（d 表示出度的意思，后文讲解）

    • 由于 B+Tree 的非叶子节点只存储 key 值信息不再存储 data 数据，所有的 data 数据均存储在叶子节点中，这样使得非叶子节点 出度d 值会变得大
    • 假设每个磁盘块能存储 4 个键值及指针信息，则变成 B+Tree 后其结构如下图所示

    

    • 通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：
      1. 一种是对于主键的范围查找和分页查找
      2. 一种是从根节点开始，进行随机查找
    • 可能上面例子中只有 22 条数据记录，看不出 B+Tree 的优点，下面做一个推算：
      • InnoDB 存储引擎中页的大小为 16KB，一般表的主键类型为 INT（占用4个字节）或 BIGINT（占用8个字节），指针类型也一般为 4 或 8 个字节，也就是说一个页（即 B+Tree 中的一个节点）中大概存储 16KB/(8B+8B)=1K 个键值（因为是估值为方便计算，这里的 K 取值为10^3）。也就是说一个深度为 3 的 B+Tree 索引可以维护 (10^3 * 10^3 * 10^3 = 10亿) 条记录
    • 实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在 2-4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作

  • B-Tree/B+Tree 索引的性能分析

    • 前置内容：
      • h：指数的度
      • 度：二叉树中节点的度指的是结点拥有的子树的数目，而整棵树的度指的是树中结点的最大的度（出度d 表示的就是整棵树的度）
      • 一般使用磁盘 I/O 次数评价索引结构的优劣
    • 先从 B-Tree 分析：根据 B-Tree 的定义，可知检索一次最多需要访问 h(高度) 个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次 I/O 就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：
      • 每次新建节点时，直接申请一个页的空间（对应上面一个页即 B+Tree 的一个节点），这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个 node 只需一次 I/O
      • B-Tree 中一次检索最多需要 h-1 次 I/O（因为根节点常驻内存），渐进复杂度为 O(h)=O(logdN)。一般实际应用中，出度d 是非常大的数字，通常超过 100，因此 h 非常小（通常不超过3）
      • 综上所述，用 B-Tree 作为索引结构效率是非常高的
    • 在分析 B+Tree：B+Tree 更适合外存索引，原因和内节点 出度d 有关。从上面分析可以看到，d 越大索引的性能越好，而出度的上限取决于节点内 key 值和 data 值的大小：
      • dmax = floor(pagesize / (keysize + datasize + pointsize)) (pagesize – dmax >= pointsize) 或
      • dmax = floor(pagesize / (keysize + datasize + pointsize)) – 1 (pagesize – dmax < pointsize)
    • 因此在非叶子节点中删除了 data 域的 B+Tree 明显有更大的 出度d 值（根据上面的粗略估算是 1K），所以更加适合索引

  • 经过上面的讲解，可以引出一个面试题：为什么 InnoDB 采用的是 B+Tree，而不是 Hash 索引、红黑树亦或者是 B-Tree 呢？

    1. 首先 B-Tree 和 B+Tree 对比：由于 B+Tree 的非叶子节点舍弃了 Data 域，所以单个节点中 B+Tree 能存储更多的指针，也就是说指向的子节点更多，这样查询到的节点也就更多，同时意味着树的高度 h 不高(一般为 2~4 层)，这样可以减少 IO 次数，最后 B+tree 所有叶子节点之间都采用单链表连接，这样适合基于范围的顺序查找
    2. 其次 B+tree 与红黑树的对比：对于有 N 个叶子结点的 B+tree，其搜索复杂度为 O(logdN)，d 值一般是大于 100 的，即使数据量达到千万级别时 B+tree 的高度依然维持在 3-4 左右，保证了 3-4 次磁盘 I/O 操作就能查询到目标数据，然而红黑树是二叉树，节点子节点个数为两个，意味着其搜索复杂度为 O(logN)，树的高度也会比 B+tree 高出不少，因此红黑树检索到目标数据所需经历的磁盘 I/O 次数更多
    3. 最后 B+Tree 和 Hash 索引对比：Hash 索引其检索效率非常高，根据索引值可以一次定位，不像 B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的 IO 访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引，但是 Hash 索引存在许多弊端，比如：Hash 冲突、只支持等值查找，不能避免读取行等等…

• 下面几种分类索引可以在看完索引的实现部分再来理解

• 主键索引和辅助索引

  • MyISAM 主键索引与辅助索引
    • MyISAM 引擎的索引文件和数据文件是分离的，其 B+Tree 结构中叶子节点的 data 域存放的是数据记录的内存地址
    • 索引文件与数据文件分离，这样的索引称为”非聚簇索引“
    • MyISAM 的主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字
  • InnoDB 主键索引与辅助索引
    • InnoDB 引起索引文件和数据文件并没有分离，其 B+Tree 结构中叶子节点的 data 域存放的就是实际的数据记录（对于主键索引存储方式实际的数据记录，对于辅助索引存储的是主键索引的值）
    • 索引文件与数据文件没有分离，这样的索引称为”聚簇索引“，一个表只能有一个聚簇索引

• 单值索引和联合索引

  • 索引既可以仅包含一个字段，也可以同时包含多个字段。单个字段组成的索引可以称为单值索引，否则称之为联合索引，也称为组合索引或多值索引
  • 假如我们现在有如下表结构：id 字段为主键索引，username 字段为辅助索引，那么(id, username) 就是联合索引

    CREATE TABLE `user_table` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `username` varchar(255) NOT NULL,
      `password` varchar(255) DEFAULT NULL,
      `age` int(11) unsigned Not NULL,
      PRIMARY KEY (`id`),
      key (`username`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8
    复制代码

    • 注意是 InnoDB 存储引擎
  • 联合索引的索引的数据顺序跟字段的顺序相关，包含多个值的索引中，如果当前面字段的值重复时，将会按照其后面的值进行排序
  • InnoDB 存储引擎的 B+Tree 遵循最左前缀原则，下面我从一个 sql 语句入手去讲解什么是 “最左前缀原则”

    select password from user_table where username = 'xdh'
    复制代码

    • 我们添加了一个联合索引 (username,password)，特别注意这个联合索引的顺序，如果我们颠倒下顺序改成 (password,username)，这样查询能使用这个索引吗？答案是不能的！
    • 这是最左前缀的第一层含义：联合索引的多个字段中，只有当查询条件为联合索引的一个字段时，查询才能使用该索引
    • 我们现在有如下三种查询场景：
      1. 查出用户名的第一个字是“张”开头的人的密码，即查询条件子句为"where username like '张%'"
      2. 查处用户名中含有“张”字的人的密码，即查询条件子句为"where username like '%张%'"
      3. 查出用户名以“张”字结尾的人的密码，即查询条件子句为"where username like '%张'"
    • 那么实际上这个联合索引只有在第一个场景中才会生效，为什么呢？
    • 这就是最左前缀的第二层含义：索引可以用于查询条件字段为索引字段，根据字段值最左若干个字符进行的模糊查询
  • 扩展
    • 维护索引是需要付出代价的，我们有时候可以利用“最左前缀原则”来减少索引数量，比如说：如果我们现在需要通过 username 来查找对应的 age 信息，有以下三种方式（一般选择第三种）：
      1. 直接通过 username 索引树查找到主键索引值，在通过回表查询(后文会讲)来获取 age 数据
      2. 通过创建联合索引 (username, age) 来查询
      3. 我们可以在 (username, password) 的基础上创建 (username, password, age) 联合索引，这样我们需要维护的索引数量不变
    • 创建索引时，我们还需要考虑空间代价，使用较少的空间来创建索引，比如说：经常需要通过 username 查询 age 或通过 age 查询 username，有以下两种方式（一般选择第一种）
      1. (username, age) 联合索引 + age 单字段索引
      2. (age, username) 联合索引 + username 单字段索引
      • 一般来说，username 字段比 age 字段大的多，所以我们应选择第一种，索引占用空间较小，这就是最少空间原则

• 覆盖索引

  • 是联和索引查询时的最优情况，不要进行回表查询

  • 假设执行如下两条 sql 语句

    select id from user_table where username = 'xdh'
    select password from user_table where username = 'xdh'
    复制代码

  • 执行第一条语句的过程是这样的：通过 username 辅助索引创建的 B+Tree 树的叶子节点存储的是 key = username 的值，data 存储的是主键索引的值(在后文索引的实现中会讲)，所以通过 username 辅助索引我们就已经找到了我们需要的数据了，不用在通过主键索引去查找另外的数据

  • 执行第二条语句的过程是这样的：通过 username 辅助索引创建的 B+Tree 树的叶子节点存储的是 key = username 的值，data 存储的是主键索引的值，然后在通过回表查询在主键索引树中找到满足条件的数据(username > id > password)

  • 当 sql 语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中，可以直接使用索引查询而不需要回表，这就是覆盖索引

  • 通过使用覆盖索引，可以减少搜索树的次数，是常用的性能优化手段，比如上面的第二条 sql 语句就可以通过建立联合索引(username, password) 来实现覆盖索引，达到优化的目的

  • 查看是否成功的使用联合索引

    EXPLAIN SELECT id FROM user_table WHERE username = 'xdh';
    EXPLAIN SELECT password FROM user_table WHERE username = 'xdh';
    复制代码

  • 可以从 Extra 这一列看出是否成功使用覆盖索引

    

    

• 索引下推

  • MySQL 数据库对联合索引的一种优化策略，在后文性能优化中讲解

• 聚簇索引

  • Innodb 的主键索引，非叶子节点存储的是索引指针，叶子节点存储的是既有索引也有数据，是典型的聚簇索引（这里可以发现，索引和数据的存储顺序是强相关的，因此是典型的聚簇索引）
  • Inndob 里的辅助索引也是非聚簇索引

• 非聚簇索引

  • MyISAM 中索引和数据文件分开存储，B+Tree 的叶子节点存储的是数据存放的地址，而不是具体的数据，是典型的非聚簇索引，换言之就是：数据可以在磁盘上随便找个地方存，索引也可以在磁盘上随便找个地方存，只要叶子节点记录上对应关系就行。（）索引存储顺序和数据存储关系毫无关联，是典型的非聚簇索引）
  • Inndob 里的辅助索引也是非聚簇索引

• 唯一索引

  • 不允许具有索引值相同的行，从而禁止重复的索引或键值，系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查， 如果有重复的值，则会操作失败，抛出异常
  • 注意的是：主键索引一定是唯一索引，而唯一索引不一定是主键索引

• 全文索引

  • 在 MySQL5.6 版本以前只有 MyISAM 存储引擎支持全文引擎
  • 在 MySQL5.6 版本中 InnoDB 存储引擎加入了对全文索引的支持，但是不支持中文全文索引
  • 在MySQL5.6 版本以后 InnoDB 存储引擎支持中文全文索引

索引的实现

• 索引的实现是由存储引擎来实现的， MYSQL 中比较主流的两大引擎是：Myisam 和 InnoDB，存储引擎是建立在表上面的，在建立表的时候可以指定所需要的存储引擎

  CREATE TABLE `user` {
      `id` int(11) NOT NULL,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY(`id`)
  } ENGINE=InnoDB DEFAULT CHARSET=UTF8
  复制代码

  CREATE TABLE `user` {
      `id` int(11) NOT NULL,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY(`id`)
  } ENGINE=MYISAM DEFAULT CHARSET=UTF8
  复制代码

• B+Tree 在 MyISM 中的实现

  • 我们在上文提到过的使用 MyISAM 存储引擎的表创建的时候会生成 3 个文件：table_name.frm(表结构文件)、table_name.MYD(数据保存文件)、table_name.MYI(索引保存文件)

  
  (图片来源于网络)

  • 例如上诉 teacher 表，两个文件分别保存了数据及索引，由 于B+Tree 中只有叶子节点保存数据区，在MyISAM 中，data 区中保存的是数据的引用地址，就比如说 ID=101 的数据信息所保存到物理磁盘地址为 0x123456,在索引中的节点数据去中所保存的就是这个磁盘地址指针。当扫描到这个指针位置，就可以通过这个磁盘指针讲数据加载出来
  • 在 MyISAM 中 B+Tree 的实现中,比如现在不用 ID 作为索引了，要用 name，那么他的一个展现形式又是怎么样的呢？其实他与 ID 作为索引是一样的，也是保存他指定的磁盘位置指针，如下图(图片来源于网络)

  

• B+Tree 在 InnoDB 中的体现

  • 会在数据目录生成 2 个文件，分别是table_name.frm(表结构文件)，table_name.idb(数据与索引保存文件)
  • 在 InnoDB中，因为设计之初就是认为主键是非常重要的。是以主键为索引来组织数据的存储，当我们没有显示的建立主键索引的时候，存储引擎会隐式的为我们建立一个 6 位数的主键索引来组织数据存储，在叶子节点上，data 区保存了数据的所有信息
  • 如果这个时候建立了name 字段的索引：会产生一个以 name 字段为索引的辅助索引，而此刻叶子节点上所保存的数据为聚集索引（ID 索引）的关键字的值，基于辅助索引找到 ID 索引的值，再通过 ID 索引区获取最终的数据，这就是所谓的 “回表查询”

  

索引策略

• 覆盖索引
• 最左前缀策略
• 索引下推

  • ICP（Index Condition Pushdown）是在 MySQL 5.6 版本上推出的查询优化策略，把本来由 Server 层做的索引条件检查下推给存储引擎层来做，以降低回表和访问存储引擎的次数，提高查询效率

  • 原理

    • 在没有使用 ICP 的情况下，联合索引的查询过程是怎么样的
      1. 存储引擎读取索引记录
      2. 根据索引中的主键值，定位并读取完整的行记录
      3. 存储引擎把记录交给 Server 层去检测该记录是否满足 WHERE 条件
    • 在使用了 ICP 的情况下，联合索引的查询过程又是怎么样的
      1. 读取索引记录（不是完整的行记录，即辅助索引的记录）
      2. 判断 WHERE 条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；、
      3. 条件满足，使用辅助索引中的主键值去定位并读取完整的行记录（就是所谓的回表）；
      4. 存储引擎把记录交给 Server 层，Server 层检测该记录是否满 WHERE 条件的其余部分

  • 案例

    • 创建一张 user 表，并插入数据

    CREATE TABLE USER (
        id INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT "主键",
        name VARCHAR ( 32 ) COMMENT "姓名",
        city VARCHAR ( 32 ) COMMENT "城市",
        age INT ( 11 ) COMMENT "年龄",
        PRIMARY KEY ( id ),
        KEY idx_name_city ( NAME, city ) 
    ) ENGINE = INNODB DEFAULT CHARSEt = UTF8;
    
    INSERT INTO USER2 (`name`, city, age )
    VALUES
        ( "ZhaoDa", "BeiJing", 20 ),
        ( "QianEr", "ShangHai", 21 ),
        ( "SunSan", "GuanZhou", 22 ),
        ( "LiSi", "ShenZhen", 24 ),
        ( "ZhouWu", "NingBo", 25 ),
        ( "WuLiu", "HangZhou", 26 ),
        ( "ZhengQi", "NanNing", 27 ),
        ( "WangBa", "YinChuan", 28 ),
        ( "LiSi", "TianJin", 29 ),
        ( "ZhangSan", "NanJing", 30 ),
        ( "CuiShi", "ZhengZhou", 65 ),
        ( "LiSi", "KunMing", 29 ),
        ( "LiSi", "ZhengZhou", 30 );
    复制代码

    • 表记录

    

    • 查看表中索引

    

    • 执行 SQL 查询语句

    SELECT * FROM user2 WHERE name = "LiSi" AND city LIKE "%Z%" AND age > 25;
    复制代码

  • 案例分析

    • 索引条件下推默认是开启的，可以使用系统参数 optimizer_switch 来控制器是否开启，通过下面的命令可以控制是否开始索引下推
      • set optimizer_switch=”index_condition_pushdown=off”;
      • set optimizer_switch=”index_condition_pushdown=on”;

  • 不使用索引下推：据联合索引“最左匹配”原则，只有 name 列能用到索引，city 列由于是模糊匹配，是不能用到索引的，此时的执行过程是这样的

    1. 存储引擎根据 (name, city) 联合索引，找到 name 值为 LiSi 的记录，共 4 条记录
    2. 然后根据这 4 条记录中的 id 值，逐一进行回表扫描，去主索引中取出完整的行记录，并把这些记录返回给 Server 层
    3. Server 层接收到这些记录，并按条件 name="LiSi" and city like "%Z%" and age > 25 进行过滤，最终留下 ("LiSi", "ZhengZhou", 30) 这条记录

    

  • 使用索引下推：此时的执行过程是这样的

    1. 存储引擎根据 (name, city) 联合索引，找到 name=’LiSi’ 的记录，共 4 条
    2. 由于联合索引中包含 city 列，存储引擎直接在联合索引中按 city like "%Z%" 进行过滤，过滤后剩下 2 条记录
    3. 根据过滤后的记录的 id 值，逐一进行回表扫描，去主索引中取出完整的行记录，并把这些记录返回给 Server 层
    4. Server 层根据 WHERE 语句的其它条件 age > 25，再次对行记录进行筛选，最终只留下 ("LiSi", "ZhengZhou", 30) 这条记录

    

  • 从执行计划上判断是否使用索引下推（Extra 里显示 Using index condition）

    

  • 使用场景

    • 只能用于 range、 ref、 eq_ref、ref_or_null 访问方法
    • 只能用于 InnoDB 和 MyISAM 存储引擎及其分区表
    • 对 InnoDB 存储引擎来说，索引下推只适用于辅助索引，因为主键索引的叶子节点包含了整个行记录，，用不到索引下推

索引的适用/不适用场景

• 适用场景
  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段
  • 查询中与其他表关联的字段，外键关系建立索引
  • 单键/组合索引的选择问题，高并发下倾向创建组合索引
  • 查询中排序的字段，排序字段通过索引访问大幅提高排序速度
  • 查询中统计或分组字段
• 不适用场景
  • 表记录太少
  • 经常增删改的表
  • 数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
  • 频繁更新的字段不适合创建索引（会加重IO负担）
  • where 条件里用不到的字段不创建索引

相关面试题

• Q：说说你对 MySQL 索引的理解？
• Q：数据库索引的原理，为什么要用 B+Tree，为什么不用二叉树，红黑树亦或者是 B-Tree 呢？
  • 首先可以回答索引是什么：索引是存储引擎中一种用于快速查找记录的一种数据结构，其本质都是通过不断缩小想要的数据的范围来筛选出最终的结果，数据库中是通过 B+Tree 来实现索引的
  • 在讲 B+Tree 前可以先讲 B-Tree，它是一种平衡二叉树，说下一个 m 阶的 B-Tree 有哪些特性，在说明 B-Tree 的缺点：如果节点中存储的 data 值过大，而一个节点的空间是有限的，那么就会导致 key 值的缩小，当数据量很大的时候就会增大 B-Tree 的树的高度，从而导致了 I/O 操作的增加，最终影响到查询效率
  • 接下来就可以引出 B+Tree，说明它与 B-Tree 的几点区别，这些区别带来的好处
  • 最后要是问为什么不用红黑树或者 Hash 索引则可以用在上文 B-Tree/B+Tree 索引的性能分析解答过的内容回答
• Q：聚集索引与非聚集索引的区别？
  • 区别就是索引的存储顺序和数据的存储顺序是否是关系的，有关就是聚簇索引，无关就是非聚簇索引
• Q：叶子结点存放的是数据还是指向数据的内存地址？
  • 如果该表的存储引擎是 MyISAM，其叶子节点的 data 区域保存的是内存地址
  • 如果该表的存储引擎是 InnoDB，并且是由主键索引创建出来的 B+Tree，其叶子节点的 data 区域保存的是数据，如果是非主键索引创建出来的 B+Tree，其叶子节点保存的是主键索引的的值
• Q：InnoDB 引擎中的索引策略，了解过吗？
  • 覆盖索引
  • 最左前缀原则
  • 索引下推
• Q：创建索引的方式有哪些？
  • 建表时创建

    CREATE TABLE `user_table` (
        `id` INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
        `username` VARCHAR ( 255 ) NOT NULL,
        `password` VARCHAR ( 255 ) DEFAULT NULL,
        `age` INT ( 11 ) UNSIGNED NOT NULL,
        PRIMARY KEY ( `id` ),
        KEY ( `username` ) 
    ) ENGINE = INNODB DEFAULT CHARSET = utf8
    复制代码

  • 建表后创建

    CREATE UNIQUE INDEX idIndex ON test(id)
    复制代码

  • 查看表索引

    SHOW INDEX FROM TABLE_NAME
    复制代码

• Q：使用索引需要注意的几个地方？
  • 用来创建的索引的字段要尽量小，因为存储引擎是通过这个字段值去创建 B+Tree 中节点的 key 值的，如果 key 值过大的话，就会导致每一个节点的叶子节点数量变少，会影响树的高度，导致 I/O 操作增加，影响查询速率
• Q：那为什么推荐使用整型自增主键而不是选择UUID？
  • UUID是字符串，比整型消耗更多的存储空间
  • 在 B+Tree 中进行查找时需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速
  • 自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续。UUID 是随机产生的，读取的上下两行数据存储是分散的，不适合执行 where id > 5 && id < 20 的条件查询语句。
  • 在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构。UUID 主键很容易出现这样的情况，B+Tree 为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间
• Q：为什么非主键索引结构叶子节点存储的是主键值？
  • 保持数据的一致性和节省存储空间
• Q：索引哪些情况会失效？
  • 查询条件包含 or，可能导致索引失效
  • 如何字段类型是字符串，where 时一定用引号括起来，否则索引失效
  • like 通配符可能导致索引失效（在最左边使用了通配符）
  • 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效
  • 在索引列上使用 mysql 的内置函数，索引失效
  • 对索引列运算（如，+、-、* 、/），索引失效
  • 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效
  • 索引字段上使用is null， is not null，可能导致索引失效
  • 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效
  • mysql 估计使用全表扫描要比使用索引快，则不使用索引

SQL 语句

• count( * ) 和 count(1) 和 count(列名) 的区别？

• count(expr) 函数

  

  • 意思就是
    1. COUNT(expr) ，返回 SELECT 语句检索的行中 expr的值不为 NULL 的数量，结果是一个 BIGINT 值
    2. 如果查询结果没有命中任何记录，则返回 0
    3. COUNT( * ) 的查询结果中会包含值为 NULL 的行数
  • count( * ) 是 SQL92 中规定的标准的统计行数的语法，与数据库无关，无是否为 NULL 值无关

• COUNT(列名)、COUNT(常量) 和 COUNT( * )

  • 根据上面的 count 函数可知，此时的 expr 对应的分别为：列名、常数、*
  • COUNT( * ) 和 COUNT(常量) 和 COUNT(列名) 在查询结果上的区别
    • COUNT(常量) 和 COUNT( * ) 表示的是直接查询符合条件的数据库表的行数
    • COUNT(列名) 表示的是查询符合条件的列的值不为 NULL 的行数
  • COUNT( * ) 和 COUNT(1) 有区别吗？下面有两种说法：
    1. 有的说 COUNT( * ) 执行时会转换成 COUNT(1)，所以 COUNT(1) 少了转换步骤，所以更快
    2. 还有的说，因为 MySQL 针对 COUNT( * ) 做了特殊优化后文会讲，所以COUNT( * ) 更快
    • 实际上，官方文档做出了如下回答：

      InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.
      复制代码

    • 对于 COUNT(1) 和 COUNT( * )，MySQL 的优化是完全一样的，根本不存在谁比谁快！
  • COUNT(列名)
    • COUNT(列名) 的查询过程就是进行全表扫描，然后判断指定字段的值是不是为 NULL，不为 NULL 则累加
    • 相比COUNT( * )，COUNT(列名) 多了一个步骤就是判断所查询的字段是否为 NULL，所以他的性能要比COUNT( * ) 慢
  • COUNT( * ) 的优化
    • MyISAM 存储引擎中会直接把表的总行数单独记录下来供 COUNT( * ) 查询
    • InnoDB 存储引擎则会在扫表的时候选择最小的索引来降低成本
    • 注意：这些优化的前提都是没有进行 where 和 group 的条件查询

• 总结

  • COUNT( * ) = COUNT(1) > COUNT(列名)

• MySQL中 in和 exists 的区别？

• 先对 EXISTS 和 IN 做一个简单的介绍

• EXIST：指定一个子查询，检测行的存在

  • exists() 后面的子查询被称做相关子查询，他是不返回列表的值的，只是返回一个 ture 或 false 的结果，其运行方式是先运行主查询一次，再去子查询里查询与其对应的结果。如果是 ture 则输出，反之则不输出
  • 再根据主查询中的每一行去子查询里去查询(一个 loop 循环结构)
  • 如下：

    select * from user where exists (select 1);
    复制代码

    • 对 user 表的记录逐条取出，由于子条件中的 select 1 永远能返回记录行，那么 user 表的所有记录都将被加入结果集，所以与 select * from user; 是一样的
  • 又如下：

    select * from user where exists (select * from user where user_id = 0);
    复制代码

    • 可以知道对 user 表进行 loop 时，检查条件语句(select * from user where user_id = 0),由于 user_id 永远不为 0，所以条件语句永远返回空集，条件永远为 false，那么 user 表的所有记录都将被丢弃

• IN：确定给定的值是否与子查询或列表中的值相匹配

  • in() 后面的子查询是返回结果集的，换句话说执行次序和 exists() 不一样。子查询先产生结果集，然后主查询再去结果集里去找符合要求的字段列表去。符合要求的输出，反之则不输出
  • 注意：in 所对应的 select 语句返回的结果一定是一列！可以为多行

• 关系

  • sql 经过改变，二者是可以达到同一个目标的

  SELECT *  FROM p_user_2 
  WHERE id IN ( 
    SELECT id FROM p_user 
  );
  
  SELECT *  FROM p_user_2 
  WHERE EXISTS (
    SELECT id  FROM p_user 
    WHERE 
      id = p_user_2.id 
  );
  复制代码

• 对比

  • in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询
  • in 其实与等于相似，比如 in(1,2) 就是 = 1 or = 2 的一种简单写法，所以一般在元素少的时候使用 in，如果多的话就用 exists
  • exists 一般都需要和子表进行关联，而且关联时则需要用索引，这样就可以加快速度
  • in 可以与子查询一起使用,也可以直接 in (a, b…)

  • exist 会针对子查询的表使用索引
  • not exist会对主子查询都会使用索引
  • in 与子查询一起使用的时候，只能针对主查询使用索引
  • not in 则不会使用任何索引

  • 注意：一直以来认为 exists 比 in 效率高的说法是不准确的

• 案例

  • 案例一

    SELECT * FROM A 
    WHERE id IN (
        SELECT id FROM B
    )
    复制代码

    • 以上查询使用了 in 语句，in() 只执行一次，它查出 B 表中的所有 id 字段并缓存起来。之后检查 A 表的 id 是否与 B 表中的 id 相等。如果相等则将 A 表的记录加入结果集中，直到遍历完 A 表的所有记录。它的查询过程类似于以下过程

    List resultSet=[];
    Array A=(select * from A);
    Array B=(select id from B);
    
    for(int i=0;i<A.length;i++) {
       for(int j=0;j<B.length;j++) {
          if(A[i].id==B[j].id) {
             resultSet.add(A[i]);
             break;
          }
       }
    }
    return resultSet;
    复制代码

  • 结论一
    • 可以看出，当 B 表数据较大时不适合使用 in()，因为它会 B 表数据全部遍历一次
      • 比如：A 表有 10000 条记录，B 表有 1000000 条记录，那么最多有可能遍历 10000 * 1000000 次，效率很差
      • A 表有 10000 条记录，B 表有 100 条记录，那么最多有可能遍历 10000 * 100 次，遍历次数大量减少
    • in() 适合 B 表比 A 表数据小的情况
  • 案例二

    SELECT a.* FROM A a
    WHERE EXISTS (
        SELECT 1 FROM B b
        WHERE a.id = b.id
    )
    复制代码

    • 以上查询使用了 exists 语句，exists() 会执行 A.length 次，它并不缓存 exists() 结果集。因为 exists() 结果集的内容并不重要，重要的是结果集中是否有记录，如果有则返回 true，没有则返回 false。它的查询过程类似于以下过程

    List resultSet=[];
    Array A=(select * from A)
    for(int i=0;i<A.length;i++) {
       if(exists(A[i].id) {    // SELECT 1 FROM B b WHERE a.id = b.id 是否有记录返回
           resultSet.add(A[i]);
       }
    }
    return resultSet;
    复制代码

  • 结论二
    • 当 B 表比 A 表数据大时适合使用 exists()，因为它没有那么遍历操作，只需要再执行一次查询就行
    • exists() 适合 B 表比 A 表数据大的情况

• 总结

  • EXISTS 与 IN 的使用效率的问题，通常情况下采用 EXISTS 要比 IN 效率高，因为 IN 不走索引，但要看实际情况具体使用：
    • IN 适合于外表大而内表小的情况
    • EXISTS 适合于外表小而内表大的情况
    • 当内表数据与外表数据一样大时，IN 与 EXISTS 效率差不多，可任选一个使用

SQL 查询语句执行顺序

• 对于查询处理，可将其分为逻辑查询处理及物理查询处理
  • 逻辑查询处理表示执行查询应该产生什么样的结果
  • 物理查询代表 MySQL 数据库是如何得到该结果的
  • 两种查询的方法可能完全不同，但是得到的结果必定是相同的

• SQL 查询案例

  • 我们先从一个查询语句去解读相关 SQL 命令的作用
  • 创建表结构并插入数据

  -- 创建 customers 表
  CREATE TABLE customers (
          customer_id VARCHAR(10),
          city VARCHAR(10) NOT NULL,
          PRIMARY KEY (customer_id)
  ) ENGINE = InnoDB;
  
  -- 向 customers 表插入数据
  INSERT INTO customers
  VALUES ('163', 'HangZhou'),
          ('9you', 'ShangHai'),
          ('TX', 'HangZhou'),
          ('baidu', 'HangZhou');
  
  -- 创建 orders 表
  CREATE TABLE orders2 (
          order_id INT AUTO_INCREMENT,
          customer_id VARCHAR(10),
          PRIMARY KEY (order_id)
  ) ENGINE = InnoDB;
  
  -- 向 orders 表插入数据
  INSERT INTO orders2
  VALUES (1, '163'),
          (2, '163'),
          (3, '9you'),
          (4, '9you'),
          (5, '9you'),
          (6, 'TX'),
          (7, NULL);
  复制代码

  • 表记录

  

  

  • 执行查询语句：【查询来自杭州且订单数少于2的客户，并且查询出他们的订单数量，查询结果按订单数从小到大排序】

  SELECT
      c.customer_id,
      count( o.order_id ) AS total_orders 
  FROM
      customers AS c
      LEFT JOIN orders2 AS o ON c.customer_id = o.customer_id 
  WHERE
      c.city = 'HangZhou' 
  GROUP BY
      c.customer_id 
  HAVING
      count( o.order_id ) < 2 
  ORDER BY
      total_orders DESC;
  复制代码

  • 查询结果

  

• 逻辑查询顺序

  • 查询语句的一般结构如下

  (8)SELECT (9)DISTINCT <select_list>
  (1)FROM <left_table>
  (3)<join_type>JOIN <right_table>
  (2)ON<join_condition>
  (4)WHERE<where_condition>
  (5)GROUP BY<group_by_list>
  (6)WITH {CUBE|ROLLUP}
  (7)HAVING<having_condition>
  (10)ORDER BY<order_by_list>
  (11)LIMIT<limit_number>
  复制代码

  • 查询语句中的序号为该查询语句的处理顺序，可以看到一共有 11 个步骤，最先执行的是 FROM 操作，最后执行的是 LIMIT 操作
  • 每个操作都会产生一张虚拟表，该虚拟表作为一个处理的输入。这些虚拟表对用户是透明的，只有最后一步生成的虚拟表才会返回给用户
  • 具体分析各个阶段
    

    1. FROM：对 FROM 子句中的左表 <left_table> 和右表 <right_table> 执行笛卡儿积（Cartesianproduct），产生虚拟表 VT1
    2. ON：对虚拟表 VT1 应用 ON 筛选，只有那些符合 <join_condition> 的行才被插入虚拟表 VT2 中
    3. JOIN：如果指定了 OUTER JOIN（如LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表 VT2 中，产生虚拟表 VT3。如果 FROM 子句包含两个以上表，则对上一个连接生成的结果表 VT3 和下一个表重复执行步骤(1)～步骤(3)，直到处理完所有的表为止
    4. WHERE：对虚拟表 VT3 应用 WHERE 过滤条件，只有符合 <where_condition> 的记录才被插入虚拟表 VT4 中
    5. GROUP BY：根据 GROUP BY 子句中的列，对 VT4 中的记录进行分组操作，产生 VT5
    6. CUBE|ROLLUP：对表 VT5 进行 CUBE 或 ROLLUP 操作，产生表 VT6
    7. HAVING：对虚拟表 VT6 应用 HAVING 过滤器，只有符合 <having_condition> 的记录才被插入虚拟表 VT7 中
    8. SELECT：第二次执行 SELECT 操作，选择指定的列，插入到虚拟表 VT8 中
    9. DISTINCT：去除重复数据，产生虚拟表 VT9
    10. ORDER BY：将虚拟表 VT9 中的记录按照 <order_by_list> 进行排序操作，产生虚拟表 VT10
    11. LIMIT：取出指定行的记录，产生虚拟表 VT11 ，并返回给查询用户

• 分析上面的查询 SQL 的执行过程

  1. 第一步：需要做的是对 FROM 子句前后的两张表进行笛卡儿积操作，也称做交叉连接（CrossJoin），生成虚拟表 VT1

     • 如果 FROM 子句前的表中包含 a 行数据，FROM 子句后的表中包含 b 行数据，那么虚拟表 VT1 中将包含 a * b 行数据，得到的结果集中前表的列在前，后表的列在后

     FROM customers as c ....... JOIN orders as o
     复制代码

     

  2. 应用 ON 过滤器：SELECT 查询一共有 3 个过滤过程，分别是 ON、WHERE、HAVING。ON 是最先执行的过滤过程。根据上一小节产生的虚拟表 VT1，过滤条件为

     ON c.customer_id = o.customer_id
     复制代码

     

  3. 添加外部行：这一步只有在连接类型为OUTER JOIN时才发生

     • 如LEFT OUTER JOIN、RIGHT OUTERJOIN、FULL OUTER JOIN，虽然在大多数时候我们可以省略 OUTER 关键字，但 OUTER 代表的就是外部行。
     • LEFT OUTER JOIN 把左表记为保留表，RIGHT OUTER JOIN 把右表记为保留表，FULL OUTER JOIN 把左右表都记为保留表
     • 添加外部行的工作就是在 VT2 表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表中的数据被赋予 NULL 值，最后生成虚拟表 VT3
     • 在这个例子中，保留表是 customers，顾客 baidu 在 VT2 表中由于没有订单而被过滤，因此 baidu 作为外部行被添加到虚拟表 VT2 中，将非保留表中的数据赋值为 NULL
     • 如果需要连接表的数量大于 2，则对虚拟表 VT3 重做本节首的步骤(1)～步骤(3)，最后产生的虚拟表作为下一个步骤的输出

     customers as c LEFT JOIN orders as o
     复制代码

     

  4. 应用 WHERE 过滤器：对上一步骤产生的虚拟表 VT3 进行 WHERE 条件过滤，只有符合 <where_condition> 的记录才会输出到虚拟表 VT4 中

     WHERE c.city='HangZhou'
     复制代码

     

     • 注意：在当前应用 WHERE 过滤器时，有两种过滤是不被允许的
       • 由于数据还没有分组，因此现在还不能在 WHERE 过滤器中使用统计过滤，如

         SELECT customer_id, count(customer_id) FROM ordersWHERE COUNT(customer_id) < 2;
         复制代码

       • 由于没有进行列的选取操作，因此在 SELECT 中使用列的别名也是不被允许的，如

         SELECT order_id as o, customer_id as cFROM ordersWHERE c = '163';
         复制代码

     • 在 WHERE 过滤器中进行的过滤和在 ON 过滤器中进行的过滤是有所不同的
       • 对于 OUTER JOIN 中的过滤，在 ON 过滤器过滤完之后还会添加保留表中被 ON 条件过滤掉的记录
       • 对于 WHERE 条件中被过滤掉的记录则是永久的过滤

  5. 分组：在本步骤中根据指定的列对上个步骤中产生的虚拟表进行分组，最后得到虚拟表 VT5

     GROUP BY c.customer_id
     SELECT * FROM customers AS c LEFT JOIN orders2 AS o ON c.customer_id = o.customer_id  WHERE c.city = 'HangZhou'  GROUP BY c.customer_id;
     复制代码

     

  6. 应用 ROLLUP 或 CUBE：如果指定了 ROLLUP 选项，那么将创建一个额外的记录添加到虚拟表 VT5 的最后，并生成虚拟表 VT6

     • 因为我们的查询并未用到 ROLLUP，所以将跳过本步骤

  7. 应用 HAVING 过滤器：这是最后一个条件过滤器了，之前已经分别应用了 ON 和 WHERE 过滤器

     • 在该步骤中对于上一步产生的虚拟表应用 HAVING 过滤器，HAVING 是对分组条件进行过滤的筛选器
     • 对于示例的查询语句，经过分组条件后，将 customer_id 为 163 的订单从虚拟表中删除，生成的虚拟表 VT6 虚拟表 VT6

     HAVING count(o.order_id < 2)
     SELECT * FROM customers AS c LEFT JOIN orders2 AS o ON c.customer_id = o.customer_id  WHERE c.city = 'HangZhou'  GROUP BY c.customer_id HAVING count(o.order_id) < 2;
     复制代码

     

  8. 处理 SELECT 列表：虽然SELECT是查询中最先被指定的部分，但是直到步骤8）时才真正进行处理。在这一步中，将SELECT中指定的列从上一步产生的虚拟表中选出 SELECT部分为：

     SELECT c.customer_id,count(o.order_id) AS total_orders
     SELECT c.customer_id, count(o.order_id) as total_orders FROM customers AS c LEFT JOIN orders2 AS o ON c.customer_id = o.customer_id  WHERE c.city = 'HangZhou'  GROUP BY c.customer_id HAVING count(o.order_id) < 2;
     复制代码

     

  9. 应用 DISTINCT 子句：如果在查询中指定了 DISTINCT 子句，则会创建一张内存临时表（如果内存中存放不下就放到磁盘上）

     • 这张内存临时表的表结构和上一步产生的虚拟表一样，不同的是对进行 DISTINCT 操作的列增加了一个唯一索引，以此来去除重复数据
     • 对于使用了GROUP BY 的查询，再使用 DISTINCT 是多余的，因为已经进行分组，不会移除任何行
     • 由于在这个 SQL 查询中未指定 DISTINCT，因此跳过本步骤

  10. 应用 ORDER BY 子句：根据 ORDER BY 子句中指定的列对上一步输出的虚拟表进行排列，返回新的虚拟表

      • 还可以在 ORDER BY 子句中指定 SELECT 列表中列的序列号，如下面的语句 2，通常情况下，并不建议采用这种方式来进行排序，因为程序员可能修改了 SELECT 列表中的列，而忘记修改 ORDER BY 中的列表

      ORDER BY total_orders DESC
      SELECT c.customer_id, count(o.order_id) as total_orders FROM customers AS c LEFT JOIN orders2 AS o ON c.customer_id = o.customer_id  WHERE c.city = 'HangZhou'  GROUP BY c.customer_id HAVING count(o.order_id) < 2 ORDER BY total_orders DESC;
      SELECT c.customer_id, count(o.order_id) as total_orders FROM customers AS c LEFT JOIN orders2 AS o ON c.customer_id = o.customer_id  WHERE c.city = 'HangZhou'  GROUP BY c.customer_id HAVING count(o.order_id) < 2 ORDER BY 2, 1;
      复制代码

      

      

      • 注意：下面有两个经常犯错的地方
        • 在MySQL数据库中，NULL值在升序过程中总是首先被选出，即 NULL 值在 ORDER BY 子句中被视为最小值
        • 很多开发人员都错误地认为在选取表中的数据时，记录会按照表中主键的大小顺序地取出，即结果像进行了 ORDER BY 一样，实际上不是这样的
      • 扩展
        

        • 关系数据库是在数学的基础上发展起来的，关系对应于数学中集合的概念。数据库中常见的查询操作其实对应的是集合的某些运算：选择、投影、连接、并、交、差、除。最终的结果虽然是以一张二维表的方式呈现在用户面前，但是从数据库内部来看是一系列的集合操作。因此对于表中的记录，用户需要以集合的思想来理解
        • 因为表中的数据是集合中的元素，而集合是无序的。因此对于没有 ORDER BY 子句的 SQL 语句，其解析结果应为：从集合中选择期望的子集合，这表明结果并不一定要有序

  11. LIMIT 子句：从上一步骤的虚拟表中选出从指定位置开始的指定行数据

      • 对于没有应用 ORDER BY 的 LIMIT 子句，结果同样可能是无序的，因此 LIMIT 子句通常和 ORDER BY子句一起使用
      • 由于本 SQL 语句没有 LIMIT 子句，将跳过

• 物理查询处理

  • 数据库也许并不会完全按照逻辑查询处理的方式来进行查询
  • 在 MySQL 数据库服务层有解析器和优化器两个组件，解析器的工作就是分析 SQL 语句，而优化器的工作就是对这个 SQL 语句进行优化，选择一条最优的路径来选取数据，但是必须保证物理查询处理的最终结果和逻辑查询处理是相等的

• 总结

JOIN 连接

• 常见的 JOIN 可以分为以下七种

• 下面通过建立两张表来解释这七种 JOIN

  CREATE TABLE customers (
      customer_id VARCHAR(10),
      city VARCHAR(10) NOT NULL,
      PRIMARY KEY (customer_id)
  ) ENGINE = InnoDB;
  
  CREATE TABLE orders2 (
      order_id INT AUTO_INCREMENT,
      customer_id VARCHAR(10),
      PRIMARY KEY (order_id)
  ) 
  复制代码

  

• LEFT JOIN

  • 返回包括左表(A)中的所有记录 + 右表(B)中联结字段相等的记录
  • 左表(A)的记录将会全部表示出来，而右表(B)只会显示符合搜索条件的记录(例子中为: customers.customer_id = orders2.customer_id) B 表记录不足的地方均为 NULL

  SELECT * FROM customers LEFT JOIN orders2 ON customers.customer_id = orders2.customer_id;
  复制代码

  

• RIGHT JOIN

  • 返回包括右表(B)中的所有记录 + 左表(A)中联结字段相等的记录
  • 右表(B)的记录将会全部表示出来，而左表(A)只会显示符合搜索条件的记录(例子中为: customers.customer_id = orders2.customer_id) A 表记录不足的地方均为 NULL

  SELECT * FROM customers RIGHT JOIN orders2 ON customers.customer_id = orders2.customer_id;
  复制代码

  

• INNER JOIN

  • 只返回两个表中联结字段相等的记录

  SELECT * FROM customers INNER JOIN orders2 ON customers.customer_id = orders2.customer_id;
  复制代码

  

• LEFT JOIN 且不含右表的内容

  • 意味着我们只返回右表为 NULL 的记录

  SELECT * FROM customers LEFT JOIN orders2 ON customers.customer_id = orders2.customer_id WHERE orders2.customer_id IS NULL;
  复制代码

  

  • RIGHT JOIN 且不含左表的内容
  • 意味着我们只返回左表为 NULL 的记录

  SELECT * FROM customers RIGHT JOIN orders2 ON customers.customer_id = orders2.customer_id WHERE customers.customer_id IS NULL;
  复制代码

  

• FULL JOIN

  • 两边的所有内容都要有，连得起的就连，连不起的就补充 NULL
  • MySQL 语法不支持 FULL OUTER JOIN，所以我们用 UNION 来实现

  SELECT * FROM customers LEFT JOIN orders2 ON customers.customer_id = orders2.customer_id
  UNION
  SELECT * FROM customers RIGHT JOIN orders2 ON customers.customer_id = orders2.customer_id WHERE customers.customer_id IS NULL;
  复制代码

  

• FULL JOIN 且不含交集

  SELECT * FROM customers LEFT JOIN orders2 ON customers.customer_id = orders2.customer_id WHERE orders2.customer_id IS NULL
  UNION
  SELECT * FROM customers RIGHT JOIN orders2 ON customers.customer_id = orders2.customer_id WHERE customers.customer_id IS NULL;
  复制代码

  

相关面试题

• Q：count( * ) 和 count(1) 和 count(列名) 的区别？
• Q：MySQL中 in和 exists 的区别？
• Q：什么是左连接、右连接，有什么区别？

事务

什么是事务

• 事务是逻辑上的一组操作要么都执行，要么都不执行

为什么需要事务

• 设想一个场景：
  • A 向 B 转账 1000 元，那么应该是 A 减少 1000 元，B 增加 1000 元，如果在这减少和增加的操作之间转账系统崩溃了，导致 A 余额减少而 B 余额没有增加，明显这是不合理的
  • 那么事务就是保证这两个操作要么都完成，要么都失败的

事务的四大特性

• 原子性(Atomicity)
  • 是事务最小的执行单元，不可再分割，原子性保证了事务要么都执行，要么都不执行
• 一致性(Consistency)
  • 在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
• 隔离性(Isolation)
  • 在并发访问数据库的时候，一个事务在执行操作不会被其他事务所打扰，相互之间是隔离独立的
• 永久性(Durability)
  • 当执行完事务后，对数据的影响是永久性的。即使数据库发生故障也不会有影响

并发事务带来的问题

• 为什么会产生问题
  • 多个事务并发执行，可能会对同一个数据进行操作而产生了下面几个问题
• 脏读
  • 当事务 A 对数据库中的某一个记录进行了更新，但是还没提交到数据库中去，此时事务 B 对该数据进行了访问，那么得到的就是 事务 A 更新后的数据。若事务 A 进行了回滚，那么事务 B 得到的数据就是一个脏数据
• 不可重复读
  • 事务 A 多次访问同一个数据，事务 B 在事务 A 访问的时候对该数据进行了更新并提交了，那么就导致事务 A 多次访问的结果不一致
• 幻读
  • 幻读和不可重复读是类似的，都是在同一个事务中两次访问数据得到的结果不一致，但是不同点在于：幻读的重点在数据(行数)的新增或删除，不可重复读的重点在数据(内部)的修改

事务的隔离级别

• 读未提交(READ_NUCOMMITTED)
  • 允许读未提交的数据，有可能造成脏读、不可重复读、幻读
• 读已提交(READ_COMMITTED)
  • 允许读已提交的数据，可以解决脏读问题，仍然存在不可重复读和幻读问题
• 可重复读(REPETABLE_READ)
  • 一个事务多次读取结果是一样的，即事务执行期间禁止其他事务对该数据进行操作，可解决脏读和不可重复读问题，仍然存在幻读问题
  • MySQL 数据库 InnoDB 存储引擎默认的隔离级别
• 序列化(SERIALIZABLE)
  • 一个事务多次从一个表中读取到相同的行，即执行期间禁止其他事务对这个表进行新增、更新和删除，可解决脏读、不可重复读、幻读问题
• 注意：
  • 事务隔离级别和数据访问的并发性是对立的，事务隔离级别越高并发性就越差
  • InnoDB 存储引擎在 REPEATABLE-READ 事务隔离级别下使用的是 Next-Key Lock(临建锁，在锁机制中会讲) 算法，可以避免幻读的产生，因此在完全保证事务的隔离性要求的同时保留了比较好的并发性能

并发问题的解决方案

• 脏读、不可重复读和幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决，有以下两种解决方案
  • 一种是加锁：在读取数据前对其加锁，阻止其他事务对数据进行修改，比如：共享锁和排它锁
  • 一种是数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库

MVCC(TODO：更深入的理解)

锁机制

• 数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则

锁的分类

• 并发控制一般采用三种方法，分别是乐观锁和悲观锁以及上面讲解过的 MVCC

乐观锁

• 乐观锁会认为自己在使用数据的时候，不会有其他线程来修改，所以不加锁，只是在更新数据之前去判断这个数据有没有被其他线程修改过。如果没有被修改，则该线程将自己修改的数据成功写入；如果被修改了，则根据不同的实现方式进行不同的操作
  

  • 再次强调一下：乐观锁只是一种思想，CAS 是它的一种实现方式，但是 CAS 同样存在问题，就引出了下面两种实现方式
  • 关于 CAS 的内容可以看我的另一篇文章CAS

• 实现方式：乐观锁有以下两种
  • 借助数据库表增加一个版本号的字段 version
    • 即为数据增加一个版本标识，一般是为数据库表增加一个数字类型的 version 字段来实现
    • 当读取数据的时候，将 version 字段值读取出来并记录下来，当我们进行提交的时候再次读取 version 字段值，和上一次读取的值进行比较，如果值相等则可以进行更新，否则不予更新
    • 数据每更新一次则 version+1
  • 借助行更新时间时间戳 timestamp
    • 与上面的检测方式类似，即更新操作执行前先获取记录当前的更新时间 A，在提交更新时，再次获取记录更新时间 B，并判断此时 B 是否与上次获取的更新时间 A 相等
• 优点
  • 乐观并发控制没有实际加锁，不通过数据库的锁机制实现，所以没有额外开销，也不错出现死锁问题，适用于读多写少的并发场景，因为没有额外开销，所以能极大提高数据库的性能
• 缺点
  • 乐观并发控制不适合于写多读少的并发场景下，因为会出现很多 version 字段的写冲突，导致数据写入要多次等待重试，在这种情况下，其开销实际上是比悲观锁更高的

悲观锁

• 对同一个数据的并发操作，悲观锁会认为自己在使用数据的时候一定会有其他线程来修改该数据，因此在获取的时候会加一把锁，确保数据不会被其他线程修改

• 实现方式：在数据库中，悲观锁的流程如下

  • 在对任意记录进行修改前，先尝试为该记录加上排他锁(后文会讲)
  • 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常(具体响应方式由开发者根据实际需要决定)
  • 如果加锁成功，那么就可以对记录做修改，事务完成后就会解锁了
  • 在这期间，如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常

• 注意

  • 在 InnoDB 存储引擎中使用悲观锁必须关闭 MySQL 数据库的自动提交属性(set autocommit = 0)
  • SQL 语句要怎么写？

    //0.开始事务
    begin;/begin work;/start transaction; (三者选一就可以)
    //1.查询出商品信息
    select status from t_goods where id=1 for update;
    //2.根据商品信息生成订单
    insert into t_orders (id,goods_id) values (null,1);
    //3.修改商品status为2
    update t_goods set status=2;
    //4.提交事务
    commit;/commit work;
    复制代码

  • select…for update 会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB 默认行级锁，行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住

• 优点

  • 悲观锁都是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。因此适合写多读少的情况

• 缺点

  • 由于需要加锁，而且可能面临锁冲突甚至死锁的问题，增加了系统的额外开销，降低了系统的效率，同时也会降低了系统的并行性

• 乐观锁和悲观锁的适用场景

  • 乐观锁
    • 适用于读多写少，因为如果出现大量的写操作，写冲突的可能性就会增大，业务层需要不断重试，这会大大降低系统性能
    • 数据一致性要求不高，但要求非常高的响应速度
    • 无法解决脏读，幻读，不可重复读，但是可以解决更新丢失问题
  • 悲观锁
    • 适用于写多读少，因为悲观锁是在读取数据的时候就加锁的，读多的场景会需要频繁的加锁和很多的的等待时间，而在写冲突严重的情况下使用悲观锁可以保证数据的一致性
    • 数据一致性要求高
    • 可以解决脏读，幻读，不可重复读，第一类更新丢失，第二类更新丢失的问题

共享锁

• 共享锁也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源
• 性质
  • 多个事务可以封锁同一个共享页
  • 任何事务都不能修改该页，如果事务对共享锁进行修改操作，很可能会造成死锁
  • 通常是该页读取完毕，共享锁就释放掉了
  • 如果事务 T 对数据 A 加上共享锁后，则其他事务只能对 A 再加共享锁，不能加排他锁
• 说明
  • 在查询语句后面增加 LOCK IN SHARE MODE ，MySQL 会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞
• 案例
  • 事务 A 在关闭自动提交的前提下进行查询，在查询语句尾部添加了 LOCK IN SHARE MODE

  -- 使用悲观锁需要先关闭自动提交
  SET AUTOCOMMIT = 0  
  SELECT * FROM customers WHERE customer_id = 163 LOCK IN SHARE MODE
  -- 注意没有执行 COMMIT 提交命令
  复制代码

  • 事务 B 现在来对事务 A 的查询结果进行 UPDATE 操作

  UPDATE customers SET city = 'HangZhou1' WHERE customer_id = 163
  复制代码

  • 执行结果如下：超时

  

  • 事务 C 现在对事务 A 的查询结果进行 SELECT 操作

  SELECT * FROM customers WHERE customer_id = 163
  复制代码

  • 执行结果如下：查询成功

  

  • 上述案例说明了：加了共享锁的数据，后面只能再加共享锁，而不能加排它锁。多个事务可以封锁同一个共享页

排它锁

• 排它锁也叫写锁，表示对数据进行写操作，如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了
• 性质
  • 仅允许一个事务封锁此页，该事务可以对该页进行读写操作
  • 其他事务可以对该页进行读，不能写
  • 排它锁会阻塞所有的排它锁和共享锁
• 说明
  • 在查询语句后面增加 FOR UPDATE ，MySQL 会对查询结果中的每行都加排它锁
• 案例
  • 事务 A 在关闭自动提交的前提下进行查询，在查询语句尾部添加了 FOR UPDATE

  SET AUTOCOMMIT = 0  
  SELECT * FROM customers WHERE customer_id = '163' FOR UPDATE
  -- 没有执行 COMMIT 提交命令
  复制代码

  • 事务 B 现在来对事务 A 的查询结果进行 UPDATE 操作

  UPDATE customers SET city = 'HangZhou' WHERE customer_id = 163
  复制代码

  • 执行结果如下：超时

  

  • 事务 C 现在对事务 A 的查询结果进行 SELECT 操作，并 添加上 LOCK IN SHARE MODE

  SELECT * FROM customers WHERE customer_id = '163' LOCK IN SHARE MODE
  复制代码

  • 执行结果如下：查询失败，超时

  

表级索、行级锁和页面锁

• 表级索
  • 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）
• 行级锁
  • 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）
• 页面锁
  • 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
• 适用场景
  • 表级锁：更适合于以查询为主，只有少量按索引条件更新数据的应用
  • 行级锁：更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用

MyISAM 表锁

• MyISAM 的表锁有两种模式
  • 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
  • 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作
• MyISAM 表的读操作与写操作之间，以及写操作之间是串行的
  • 当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作，其他线程的读、写操作都会等待，直到锁被释放为止
• 默认情况下，写锁比读锁具有更高的优先级
  • 当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求
• MyISAM 加锁解锁机制
  • 在执行查询语句（SELECT—前，会自动给涉及的所有表加读锁
  • 在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预

InnoDB 行锁

• InnoDB 实现了以下两种类型的行锁
  • 共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
  • 排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁
• 注意
  • InnoDB 存储引擎的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则都会从行锁升级为表锁
  • 行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁

InnoDB 加锁机制

• InnoDB 有三种行锁模式的算法
• 记录锁（Record Locks）
  • 单个记录上的锁。对符合条件的带有索引的行加锁，其他事务不能修改和删除加锁项

  SELECT * FROM table WHERE id = 1 FOR UPDATE;
  复制代码

  • 会在 id=1 的记录上加上记录锁，以阻止其他事务更新，删除 id=1 这一行

  -- id 列为主键列或唯一索引列
  UPDATE SET age = 50 WHERE id = 1;
  复制代码

  • 在通过主键索引与唯一索引对数据行进行 UPDATE 操作时，也会对该行数据加记录锁
• 间隙锁（Gap Locks）
  • 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做间隙（GAP)，InnoDB 也会对这个间隙加锁
  • 比如：我们总共有 100 条记录，id 从 1-100

  Select * from  emp where empid >= 100 for update;
  复制代码

  • 是一个范围条件的检索，InnoDB 不仅会对符合条件的 empid=100 的记录加锁，也会对empid>100（这些记录并不存在）的间隙加锁
  • 使用间隙锁的目的是为了解决幻读问题，以满足 REPETABLE_READ 这个隔离级别的要求，比如：
    • 对于上面的例子要是不使用间隙锁，如果其他事务插入了 empid>100 的任何记录，那么本事务如果再次执行上述语句，就会发生幻读
  • 注意
    • 如果使用相等条件请求给一个不存在的记录加锁，InnoDB 也会使用间隙锁
    • 间隙锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件
• 临键锁（Next-Key Locks）
  • 临键锁是记录锁与间隙锁组成的，它的封锁范围，既包含索引记录，又包含索引区间，可以理解为一种特殊的间隙锁，也可以理解为是一种算法
  • 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据
  • InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁
  • 临键锁的主要目的，也是为了避免幻读，如果把事务的隔离级别降级为READ-COMMITED，临键锁则也会失效

死锁(TODO：更深入的理解)

• 什么是死锁
  • 死锁是指在一组进程中的各个进程均占有不会释放的资源，但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状
• 产生死锁的四个条件
  • 互斥条件：同一个资源只能被一个进程使用
  • 请求与保持条件：一个进程因请求资源而阻塞，但是对已经占有的资源不会释放
  • 不可剥夺条件：一个进程已经占用的资源在使用完之前不会被剥夺
  • 循环等待条件：若干个进程形成环形等待资源关系
• 解除当前死锁状态
  • 总的来说就是找到造成死锁的进程ID 并 kill 进程ID
• 如何避免死锁
  • 按同一顺序访问对象
  • 避免事务中的用户交互
• 注意
  • 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁
  • 死锁会影响性能而不是会产生严重错误，因为InnoDB 会自动检测死锁状况并回滚其中一个受影响的事务

相关面试题

• Q：数据库的乐观锁和悲观锁？
  • 乐观锁是数据库认为事务在其操作过程中不会有其他事务
  • 悲观锁则认为在其操作过程中会有其他事务来干扰，则会在进行操作之前加上锁
• Q：MySQL 中有哪几种锁，列举一下？
  • 乐观锁和悲观锁
  • 共享锁和排它锁
  • 记录锁、间隙锁和临键锁
• Q：MySQL 中 InnoDB 引擎的行锁是怎么实现的？
  • InnoDB 存储引擎的行锁有三种实现机制：记录锁、间隙锁、和临键锁
  • 记录锁是对带有索引的查询记录加锁，加锁后不能对其进行更新和删除
  • 当我们的查询条件是一个范围而不是等值的时候，并且对查询记录加排它锁或者是共享锁的时候，存储引擎则会给符合的索引记录加上间隙锁
  • 临键锁是记录锁和间隙锁组成的，其作用和间隙锁一样都是避免 InnoDB 引擎在 REPETABLE_READ 的隔离级别下发生幻读
• Q：MySQL 死锁有没有了解，写一段会造成死锁的 sql 语句，死锁发生了如何解决，MySQL 有没有提供什么机制去解决死锁
  • 死锁是指多个事务在占有各自资源的情况下，又互相申请被其他事务所占用的资源而导致的一种死循环状态

  -- 执行顺序1
  BEGIN;
  DELETE FROM user_table WHERE id = 10;    --理论上此时 user_id区间 (-∞, 10] 和 (10, 20)都被锁住了
  
  -- 执行顺序3
  INSERT INTO user_table (id) VALUES (24); --事务 A 尝试插入数据，但是该区间已经被事务 B 锁住，因此阻塞等待事务 B 释放锁
  复制代码

  -- 执行顺序2
  BEGIN;
  DELETE FROM user_table WHERE id = 30;    --理论上此时 user_id 区间 [20, 30 ) 和 [30, +∞) 被事务 B 锁住了
  
  -- 执行顺序4
  INSERT INTO user_table(id) VALUES (13);  --事务 B 尝试插入数据，但是该区间已经被事务 A 锁住，因此阻塞等待事务 A 释放锁
  复制代码

  • 事务 A 和事务 B 互相等待对方释放锁，导致死锁
  • MySQL 提供了一套 InnoDB 的监控机制，用于周期性（每隔 15 秒）输出 InnoDB 的运行状态日志中，默认是关闭的
  • InnoDB 目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚

性能优化(TODO：更深入的理解)

SQL 优化

• 总体来说 SQL 语句的优化有以下几个方面
  1. 避免不走索引的场景
  2. SELECT 语句其他优化
  3. 增删改 DML 语句优化
  4. 查询条件优化
  5. 建表优化
• 参考
  • SQL优化2020最全干货总结—MySQL

分区分表分库

分区

• 概念
  • 分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，系统或是不同服务器存储介子中，实际上还是一张表
• 何时考虑分区
  • 当一张表的查询速度已经慢到影响使用的时候
  • 数据库中数据是分段的
  • 往往只是操作数据库中一部分数据
• 分区策略
  • RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区
  • LIST 分区：类似于按 RANGE 分区，每个分区必须明确定义。它们的主要区别在于，LIST 分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而 RANGE 分区是从属于一个连续区间值的集合
  • HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式
  • KEY 分区：类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数，必须有一列或多列包含整数值
• 实现方式（RANGE 分区）

  CREATE TABLE sales (
      id INT AUTO_INCREMENT,
      amount DOUBLE NOT NULL,
      order_day DATETIME NOT NULL,
      PRIMARY KEY(id, order_day)
  ) ENGINE=Innodb 
  PARTITION BY RANGE(YEAR(order_day)) (
      PARTITION p_2010 VALUES LESS THAN (2010),
      PARTITION p_2011 VALUES LESS THAN (2011),
      PARTITION p_2012 VALUES LESS THAN (2012),
  PARTITION p_catchall VALUES LESS THAN MAXVALUE);
  复制代码

• 优点
  • 相对于单个文件系统或是硬盘，分区可以存储更多的数据
  • 分区可以做到将表的数据均衡到不同的地方，提高数据检索的效率，减少数据库频繁操作 IO
• 缺点
  • 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁

分表

• 概念

  • 就是把一张表按一定的规则分解成 N 个具有独立存储空间的实体表，系统读写时需要根据定义好的规则得到对应的子表名，在对该子表进行操作

• 何时考虑分表

  • 一张表的查询速度已经慢到影响使用的时候。
  • SQL 经过优化
  • 数据量大
  • 当频繁插入或者联合查询时，速度变慢

• 分表策略

  • 水平分表：是把一个表复制成同样表结构的另一张表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能，这些相同结构的表可以放在同一个或不同的数据库

  • 垂直分表：通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表，然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中

  • 图片来源于网络，侵权请联系删除

  

• 解决的问题

  • 分表后，单表的并发能力提高了，磁盘I/O性能也提高了，写操作效率提高了
  • 查询一次的时间短了
  • 数据分布在不同的文件，磁盘 I/O 性能提高
  • 读写锁影响的数据量变小
  • 插入数据库需要重新建立索引的数据减少

• 实现方式

  • 需要业务系统配合迁移升级，工作量较大

分库

• 概念
  • 一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上
• 何时考虑分库
  • 单台数据库的存储空间不够
  • 随着查询量的增加单台数据库服务器已经没办法支撑
• 分库策略
  • 水平分库
  • 垂直分库
  • 读写分离
• 解决的问题
  • 其主要目的是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题

分表分库带来的问题

• 事务的支持，分库分表就变成了分布式事务

• JOIN 时跨库，跨表的问题

• 分库分表，读写分离使用了分布式，分布式为了保证强一致性，必然带来延迟，导致性能降低、系统的复杂度变高

• 解决方案：通常配合使用第三方数据库中间件（Atlas，Mycat，TDDL，DRDS）来解决相应的问题

主从复制

• 概念
  • MySQL 主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中
  • 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行
• 用途
  • 读写分离：在开发工作中，有时候会遇见某个 SQL 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读
  • 数据备份：当系统中某个节点数据库发生故障时，可以方便的恢复
  • 架构扩展：当单体数据库不能适应海量的数据的时候，可以使用主从复制来缓解单体 DB 的压力，降低单体磁盘 IO 操作频率，提高单个机器的性能

主从复制形式

• 一主一从
• 一主多从
  • 一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现高可用，而且还能读写分离，进而提升集群的并发能力
• 多主一从
  • 多主一从可以将多个 MySQL 数据库备份到一台存储性能比较好的服务器上
• 双主复制
  • 双主复制每个主节点既是主节点又是另外一台服务器的从节点。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中
• 级联复制
  • 部分从节点的数据同步不连接主节点，而是连接从节点
  • 因为如果主节点有太多的从节点，就会损耗一部分性能用于 replication，那么我们可以让 3~5 个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响

主从复制原理

• MySQL 主从复制涉及到三个线程，一个运行在主节点（Log Dump Thread），其余两个运行在从节点（I/O Thread、SQL Thread），如下图所示

• 主节点 Log Dump Thread

  • 当从节点连接主节点时，主节点会创建一个Log Dump Thread，用于发送 bin-log 的内容。在读取 bin-log 中的操作时，此线程会对主节点上的 bin-log 加锁，当读取完成，甚至在发动给从节点之前，锁才会被释放
  • bin-log 是主库中的更新事件类型

• 从节点 I/O Thread

  • 当从节点上执行 start slave 命令之后，从节点会创建一个 I/O Thread 用来连接主节点，请求主库中更新的 bin-log，I/O Thread 接收到主节点 Log Dump Thread 发来的更新之后，保存在本地 relay-log 中

• 从节点 SQL Thread

  • SQL Thread 负责读取 relay-log 中的内容，解析成具体的操作并执行，最终保证主从数据的一致性

• 说明

  • 对于每一个主从复制，都需要三个线程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个 Log Dump Thread，而每个从节点都有自己的 I/O Thread 和 SQL Thread
  • 要实施复制必须打开 Master 端的 binary log（bin-log）功能，否则无法实现，因为整个复制过程实际上就是 Slave 从 Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作

• 复制过程

  

  1. 从节点上的 I/O 线程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
  2. 主节点接收到来自从节点的 I/O 请求后，负责复制的 I/O 线程会根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的 bin-log file 以及 bin-log position
  3. 从节点的 I/O 进程接收到内容后，将接收到的日志内容更新到本机的 relay-log 中，并将读取到的 binary log 文件名和位置保存到 master-info 文件中，以便确定下次去主库中读取的位置
  4. 从节点的 SQL 线程检测到 relay-log 中新增加了内容后，会将 relay-log 的内容解析成在主节点上实际执行过的操作，并在本数据库中执行，达到主从一致性

主从复制策略

• 同步策略：Master 会等待所有的 Slave 都回应后才会提交，这个主从的同步的性能会严重的影响
• 半同步策略：Master 至少会等待一个 Slave 回应后提交
• 异步策略（默认）：Master 不用等待 Slave 回应就可以提交
• 延迟策略：Slave 要落后于 Master 指定的时间
  

  • 对于不同的业务需求，有不同的策略方案，但是一般都会采用最终一致性，不会要求强一致性，毕竟强一致性会严重影响性能

主从复制实现（TODO：还未实现）

数据库连接池

参考与感谢

• MySQL 三万字精华总结 + 面试100 问，和面试官扯皮绰绰有余（收藏系列）
• 100道MySQL数据库经典面试题解析（收藏版）
• 重学MySQL系列(一)：谈谈MySQL架构
• Hash索引
• Mysql索引机制(B+Tree)
• B+Tree在数据库索引上拥有独特优势的原因（为什么比红黑树更合适）
• mysql索引篇之覆盖索引、联合索引、索引下推
• MySQL索引分类，90%的开发都不知道
• count(1)比count(*)效率高？
• SQL 中 IN 和 EXISTS 用法的区别
• SQL查询语句执行顺序详解
• 面试官：InnoDB解决幻读的方案了解么？
• [数据库锁机制] 深入理解乐观锁、悲观锁以及CAS乐观锁的实现机制原理分析
• 全面了解mysql锁机制（InnoDB）与问题排查
• 一文读懂什么是MySQL索引下推（ICP）
• SQL优化2020最全干货总结—MySQL
• 深度探索MySQL主从复制原理