1.数据库的组成

2.MySQL数据类型

    MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

2.1 数值类型

    MySQL支持所有标准SQL数值数据类型。

    这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

    关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

    BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

    作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围

2.2 日期和时间类型

    表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

    每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

    TIMESTAMP类型有专有的自动更新特性

2.3 字符串类型

    字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

注意：

    char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

    CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

    BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

    BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

    有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

3.MySQL 索引

3.1 什么是索引？

    索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

    索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

    更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

3.2 索引有哪些优缺点？

索引的优点：

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；

• 空间方面：索引需要占物理空间。

3.3 索引有哪几种类型？

主键索引: 

    数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引: 

    数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

• 可以通过 ALTER TABLE table_name ADD UNIQUE (column)；创建唯一索引

• 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2)；创建唯一组合索引

普通索引:

    基本的索引类型，没有唯一性的限制，允许为NULL值。

• 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

• 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)；创建组合索引

• 直接创建普通索引：CREATE INDEX indexName ONmytable(username(length));

全文索引：

    全文索引是目前搜索引擎使用的一种关键技术。

    可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

3.4 什么样的信息能成为索引？

• 选择一些经常要作为查询条件的字段。

• 选择区分度高的字段，能把该记录限定在一定查找范围中的字段

• 主键，唯一键以及普通键 能够让数据具备一定区分度的字段

3.5 索引的数据结构

    主流是B+Tree，还有Hash结构、bitMap索引，其中MySql不支持BitMap索引，同时基于MyISAM 或 InnoDB引擎的MySql不支持Hash

二叉查找树

    特点：    

• 跟节点有左右子树，

• 对于树中的节点值X 他的左子树的任意节点值小于X，右字数任意节点值大于X，

• 时间复杂度O(logn)

    但在插入过程中可能会产生以下问题 导致IO次数变多时间复杂度变为O(n)，比全表扫描都要慢得多

平衡二叉树（AVL Tree）

平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。下面的两张图片，左边是AVL树，它的任何节点的两个子树的高度差<=1；右边的不是AVL树，其根节点的左子树高度为3，而右子树高度为1； 

红黑树

红黑树的特点：

• 性质1：节点是红色或黑色

• 性质2：根节点是黑色的；

• 性质3：叶子节点是黑色的；

• 性质4：红色节点的子节点是黑色的；（红色节点不能相邻）

• 性质5：从根节点到任意一个叶子节点的路径上，黑色节点的个数必须相等（这个个数就称为黑高度 ）。也就是说黑高度必须相等

二叉树、平衡二叉树、红黑树作为索引的缺点：

    影响数据库运行的瓶颈在IO，如果上述索引存在磁盘中，那么索引检索深度每增加1，就多一次IO

    所以，虽然平衡二叉树或红黑树能解决极端情况下查询复杂度为O（n）的，但是随着数据的增加，因为每个存储单元只能存一个数据、最多两个指向子节点的指针，所以大数据量的表索引深度会很深，影响了查询效率

    为了提高查询效率，就只能增加每个存储单元存储的数据个数、指向子节点指针的个数

B树

一棵m阶的B-Tree有如下特性： 

• 每个节点最多有m个孩子。 

• 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。

• 若根节点不是叶子节点，则至少有2个孩子 

• 所有叶子节点都在同一层，且不包含其它关键字信息 

• 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）

• 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1

• ki(i=1,…n)为关键字，且关键字升序排序。 

• Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

    B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree： 

B+树

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

    从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

• 非叶子节点只存储键值信息。

• 所有叶子节点之间都有一个链指针。

• 数据记录都存放在叶子节点中。

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示： 

    通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

    可能上面例子中只有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索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

为什么B+Tree更适合用来做存储索引：

• B+树磁盘读写代价更低；B+树非叶子节点只存索引，所有的数据都存在叶子节点中，所以统一大小的存储块，B+树非叶子节点可以存储更多的索引信息，那么相同数据条数的数据，B+树的深度更小，IO次数更少，读写代价就更低

• B+树的查询效率更稳定；因为非叶子节点只存索引，索引关键字都需要到叶子节点中查找具体数据，所以所有关键字要走的路径相同

• B+树更有利于对数据库的扫描；B+树只要对所有叶子节点的遍历就能做到对所有数据的扫描，因为叶子节点之间用指针相连接，有利于数据库中的范围查询

哈希索引

    哈希表是一种以 键-值（key-value） 存储数据的结构，我们只要输入待查找的值即 key，就可以找到其对应的值即 value。

    哈希索引采用一定的哈希算法，对于每一行，存储引擎计算出了被索引字段的哈希码（Hash Code），把哈希码保存在索引中，并且保存了一个指向哈希表中的每一行的指针。

    这样在检索时只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

    Hash 索引结构的特殊性，其检索效率非常之高，应该是 O(1) 的时间复杂度。

虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些：

• Hash索引仅仅能满足 =，IN 和 <=> 查询，如果是范围查询检索，这时候哈希索引就毫无用武之地了。

  因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索

• Hash 索引无法利用索引完成排序，因为存放的时候是经过 Hash 计算过的，计算的 Hash 值和原始数据不一定相等，所以无法排序

• 联合索引中，Hash 索引不能利用部分索引键查询。

  Hash 索引在计算 Hash 值的时候是联合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值。

  所以对于联合索引中的多个列，Hash 是要么全部使用，要么全部不使用。通过前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

• Hash索引在任何时候都不能避免表扫描。

  前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash 运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键可能存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

• 在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

    综上，哈希表这种结构适用于只有等值查询的场景，比如 Memcached、redis 及其他一些 NoSQL 引擎。

位图索引

    存储时按照状态分开，每个数据是否是这个值，按位存储

    只适用于某个字段的值只有某种几个的情况

    锁力度较大，不适合高并发联机系统，适用于并发低但是统计多的。

3.5 密集索引和稀疏索引

数据库表的索引从数据存储方式上可以分为密集索引和稀疏索引两种。

密集索引

定义：

    该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

注意事项：

    定义密集索引时使用的列越少越好。

使用的场景：

• 包含大量非重复值的列。

• 使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。

• 被连续访问的列。

• 返回大型结果集的查询。

• 经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。

• OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。

缺点：

• 聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没有那么重要了，密集索引也没有什么优势了

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到innodb表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表

• 更新密集索引列的代价很高，因为会强制innodb将每个被更新的行移动到新的位置

• 基于密集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题，当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间

• 密集索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

不适用于：

• 频繁更改的列 。这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。

• 宽键 。来自密集索引的键值由所有稀疏索引作为查找键使用，因此存储在每个稀疏索引的叶条目内。

稀疏索引

定义：

    数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。

    非聚集索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。对于非聚集索引，可以为在表非聚集索引中查找数据时常用的每个列创建一个非聚集索引。有些书籍包含多个索引。例如，一本介绍园艺的书可能会包含一个植物通俗名称索引，和一个植物学名索引，因为这是读者查找信息的两种最常用的方法。

特点：

• 二级索引可能比想象的更大，因为在二级索引的叶子节点包含了引用行的主键列。

• 二级索引访问需要两次索引查找，而不是一次

使用场景的选择：

3.6 MySQL的Innodb和MyISAM的索引

MySQL：主要有两种存储引擎 MyISAM、InnoDB

MyISAM

• 主键索引、唯一键索引、普通索引，索引类型都属于稀疏索引

InnoDB

• 有并且仅有一个密集索引

• 如果一个主键被定义，则该主键作为密集索引

• 若没有主键定义，该表的第一个唯一非空索引则作为密集索引

• 若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引），该主键为6字节的列，随着数据的插入自增

• 为什么要一定要有主键索引呢？

• 非主键索引存储相关键位和其对应的主键值，包含两次查找

    InnoDB主键索引：将主键组织到一棵B+树中，叶子节点保存主键和这条数据的所有字段，因为主键和数据是保存在同一个文件中的，所以在查找的时候加载叶子节点的主键到内存中，同时也加载了该行的所有数据

    InnoDB辅助索引（稀疏索引）：需要两步，首先查找索引本身，然后找到索引对应的主键，然后通过主键索引查到数据

3.7 MySQL最左匹配原则

    在mysql建立联合索引时会遵守最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配.

什么是最左匹配原则

• mysql会一直向右匹配直到遇到范围查询（<、>、between、like）就停止匹配，

• 比如a = 3 and b = 4 and c>5and d=6

• 如果建立（a、b、c、d）顺序的索引，d是用不到索引的

• 如果建立（a、b、d、c）索引则都可以用到，a、b、d的顺序可以任意调整

• =和in可以乱序

• 比如a = 3 and b = 4 and c=5 建立（a、b、c）索引可以任意顺序，mysql的查询优化器会帮你优化索引可以识别的形式

MySQL官网对最左匹配原则的解释

    MySQL可以创建联合索引(即, 多列的索引). 一个索引可以包含最多16列. 对于某些数据类型, 可以索引列的前缀(这里说的是对于Blob和Text类型, 索引列的前几位就可以, 如INDEX(blob_col(10))）.

    MySQL的联合索引可以用于包含索引中所有列的查询条件的语句, 或者包含索引中的第一列的查询条件的语句, 以及索引中前两列, 索引中的前三列, 以此类推. 如果你在索引定义中以正确的顺序指定列, 那么联合索引就可以加速同一张表中的多个不同类型的查询.

作为联合索引的一个替代项, 你可以采用一个Hash值列, 这个列的Hash值来自其他的列. 如果该列简短, 合理唯一, 且被索引, 那该列就可能比一个很"宽"的由多个列构成的索引 更快. MySQL里可以很容易的使用这种列:

SELECT * FROM tbl_name WHERE hash_col=MD5(CONCAT(val1,val2)) AND col1=val1 AND col2=val2;

假设有如下表定义:

CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name));

    索引name是一个包含了last_name和first_name列的索引. 该索引可以用于为last_name 和first_name值的组合指定一个已知范围内的查询. 同样也可以用于只指定了last_name列值的查询, 因为这个列是索引的一个最左前缀(就如下一节所说). 因此, 索引name可以用于下列的查询语句:

SELECT * FROM test WHERE last_name='Jones';
SELECT * FROM test  WHERE last_name='Jones' AND first_name='John';
SELECT * FROM test WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon');
SELECT * FROM test WHERE last_name='Jones'  AND first_name >='M' AND first_name < 'N';

    然而, 索引name不能用于下列的查询:

SELECT * FROM test WHERE first_name='John';
SELECT * FROM test  WHERE last_name='Jones' OR first_name='John';

    假设存在以下select语句:

SELECT * FROM tbl_name  WHERE col1=val1 AND col2=val2;

    如果一个联合索引存在于col1和col2, 相应的列会被直接抓取. 如果是分为单独的索引 分别存在于col1和col2, 优化器会尝试利用索引联合优化, 或者尝试去寻找包含最多列, 最大限制的索引, 并利用该索引去抓取列.

    如果表拥有一个联合索引, 任何一个索引的最左前缀都会被优化器用于查找列. 比如, 如果你创建了一个三列的联合索引包含(col1, col2, col3), 你的索引会生效于(col1), (col1, col2), 以及(col1, col2, col3).

    如果查询的列不是索引的最左前缀, 那MySQL不会将索引用于执行查询. 假设你有 下列查询语句:

SELECT * FROM tbl_name WHERE col1=val1;SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE col2=val2;SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

    如果索引存在于(col1, col2, col3), 那只有头两个查询语句用到了索引. 第三个和 第四个查询包含索引的列, 但是不会用索引去执行查询. 因为(col2)和(col2, col3) 不是(col1, col2, col3)的最左前缀

    其实官方文档已经解释的非常详细了, 总结关于最左匹配的解释, 那其实只有这么 几句话:

• 按照文档, 更准确的说法应该是最左前缀原则, 即如果你创建一个联合索引, 那 这个索引的任何前缀都会用于查询, (col1, col2, col3)这个联合索引的所有前缀就是(col1), (col2, col3), (col1, col2, col3), 包含这些列的查询都会启用索 引查询.

• 其他所有不在最左前缀里的列都不会启用索引, 即使包含了联合索引里的部分列也不行. 即上述中的(col2), (col3), (col2, col3) 都不会启用索引去查询.

• 注意, (col1, col3)会用到联合索引，但是只有列(col1)的查询会用到

  
  

最左匹配原则的成因

• Mysql创建联合索引是首先会对最左边，也就是第一个索引字段进行排序

• 在第一个排序的基础上，再对第二个索引字段进行排序，其实就像是实现了Order by字段1，再Order by 字段2这样一种排序规则

• 所以第一个字段是绝对有序的，而第二个字段就是无序的了

• 因此通常情况下，直接使用第二个字段进行条件判断是用不到索引的。这就是为什么mysql要强调最左匹配原则的成因。

3.8 索引建的越多越好吗？

    不要过度索引。

    索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

• 数据量小的表不需要建立索引，建立会增加额外的索引开销

• 数据变更需要维护索引，因此更多的索引意味着更多的维护成本

• 更多的索引意味着也需要更多的空间

3.9 索引设计的原则？

• 适合索引的列是出现在where子句中的列，或者连接子句中指定的列

• 基数较小的类，索引效果较差，没有必要在此列建立索引

• 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间

• 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

3.10 创建索引的原则（重中之重）

索引虽好，但也不是无限制的使用，最好符合一下几个原则

• 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 andb = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

• 较频繁作为查询条件的字段才去创建索引

• 更新频繁字段不适合创建索引

• 若是不能有效区分数据的列不适合做索引列（如性别，男女未知，最多也就三种，区分度实在太低）

• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a，b)的索引，那么只需要修改原来的索引即可。

• 定义有外键的数据列一定要建立索引。

• 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

• 对于定义为text、image和bit的数据类型的列不要建立索引。

3.11 使用索引查询一定能提高查询的性能吗？为什么

• 通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

• 索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEXRANGE SCAN)适用于两种情况：

• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%

• 基于非唯一性索引的检索

4.如何定位并优化慢查询SQL

4.1 慢SQL查询与优化

   1.首先让咱们看一下MySQL的一些查询参数

SHOW VARIABLES LIKE '%quer%';

    其中，主要关注几个参数：

• long_query_time：慢查询认定时间，即当查询SQL执行超过这个参数设置的值时，被认定该SQL语句是慢查询，该参数单位是秒，可以用SET GLOBAL long_query_time = 1;设置参数值

• slow_query_log：是否开启慢查询日志，如果为on，则记录慢查询日志，可以SET GLOBAL slow_query_log = on;设置该参数

• slow_query_log_file：慢日志文件存储位置
  

• 注意：这里使用set global param = value设置全局参数的命名在重启数据库后失效，如果想要长期使可以在MySQL的配置文件中修改

       2. 我们找到慢日志存储位置，就可以打开慢日志文件查看具体查询时间

      3. 然后我们就可以通过explain  执行计划分析sql效率 

     这里主要注意这几个字段：

• type：表示mysql找到数据行的方式，性能从最优到最差排序如下，如果出现 index 或者 all 就需要注意优化了。

• system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

• extra：extra中出现以下两项意味着mysql不能使用索引，效率会受到重大影响，应当尽可能优化。

• Using filesort：表示mysql会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在磁盘或是在内存上排序。Mysql中无法利用索引完成的排序操作称为“文件排序”。

• Using temporary：表示mysql在对查询结果排序时使用了临时表。常见于排序和分组查询。

     4. 修改SQL或尽量让SQL走索引

4.2 SQL语句优化方法

• 避免全表扫描 where 条件中尽量少用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE 它们会引起全表扫描。

• 在where 子句条件中尽量避免null判断 最好用默认值0来代替null进行查询。

• 在where子句条件中尽量避免用or连接条件最好使用union all 连接。

• like 查询时避免使用前置%查询会导致索引失效全表扫描。

• where子句条件查询时可以使用between  and的尽量不要用in 或者用exists 代替。

• where子句查询时不要对字段进行表达式判断如：sum/2=5，会不使用索引进行全表扫描的改成 sum=2*5。

• where子句查询时不要对字段进行函数操作如：substring(xm,1,4)=wang 会不使用索引进行全表扫描的改成 like 'xm %'。

• 查询时尽量不用select *   不要查询出没有用的字段。

• 字段可以使用数字类型，尽量不要用字符类型，还有尽量用varchar/nvarchar代替char/nchar 。

• 尽量减少子查询，使用关联查询（left join,right join,inner  join）替代。

4.3 查询数据行数，为什么不用主键

    Innodb引擎，查询主键的时候把主键对应的数据一起加载到了内存中，但是如果查询索引，只需要将索引和主键加载到内存中，这样相同内存能够加载更多是数据，所以MySQL查询优化器将查询数据行数的sql语句优化为非主键索引

5.MySQL 锁模块

5.1 MyISAM与InnoDB关于锁方面的区别是什么

• MyISAM默认使用的是表级锁，不支持行级锁

• 当查询表数据的时候，MyISAM会自动给表上一个表锁，锁住这张表，并且block住其他session的写操作（增删改）

• MyISAM，当读数据时，会自动给表加上一个表级的读锁，阻塞其他session的写操作，当时允许其他session的读操作

• MyISAM，当写数据的时候，会自动给表加上一个表级的写锁，阻塞其他session的读和写操作

• 显示的加锁

• 给表加读|写锁：lock tables table_name read|write

• 解锁：unlock

• InnoDB默认用的是行级锁，也支持表级

• Innodb支持事务，默认事务是自动提交的

• Show variable like “autocommit” ; set autocommit  = 0 #关闭自动提交

• Innodb非阻塞select，优化了select同时，可以写同一条数据

• 显示加共享读锁，lock in share mode

• InnoDB的锁默认是行级的

• InnoDB  SQL语句走索引的时候用到的是行级锁，没有走索引的时候用的是表级锁

• InnoDB 表级的 共享读锁IS 和排他写锁IX

• InnoDB支持事务的同时，也相对MyISAM带来了更大的开销。InnoDB有且仅有一个密集索引，数据文件是和索引绑在一起的，必须要有主键索引，通过主键索引查询效率很高，但是其他辅助索引需要先查询主键，再通过主键查到数据；MyISAM是只有稀疏索引，主键和辅助索引一样，与数据分开存储，因此MyISAM在存检索系统中（增删改很少的系统中）效率好于InnoDB

• 使用场景

• MyISAM适合的场景

• 频繁执行全部count语句，MyISAM用一个变量保存了表的行数，InnoDB不保存表的行数，每次查询count需要全表扫描

• 对数据进行增删改的频率不高，查询非常频繁的时候

• 没有事务

• InnoDB适合的场景

• 数据增删改查都相当频繁

• 可靠性要求比较高，要求支持事务

5.2 锁的分类

• 按锁的粒度划分：表级锁、行级锁、页级锁

• InnoDB默认是行锁，也支持表级意向锁

• MyISAM只支持表级锁

• BDB引擎支持页级锁

• 按锁的级别划分：共享锁、排他锁

• 按加锁的方式划分：自动锁、显示锁

• 按操作划分：DML锁、DDL锁

• 按使用方式划分：乐观锁、悲观锁

• 悲观锁，始终认为数据会被外界修改，所以在数据操作的整个过程中都需要让数据处于锁的状态。只有用数据库层提供的锁机制才能真正保证数据的排他性。先取锁，再操作，保证数据安全；效率低、开销大、降低并行性。

• 乐观锁，始终认为数据不会被外界修改，只用在数据提交的时候才会对数据的冲突与否进行检查，如果冲突了返回用户冲突的信息，让用户选择如何做。一般乐观锁不会用到数据库的锁机制，一般用版本号或者时间戳标识数据是否被修改

写锁和读锁

• 写锁，又叫排他锁，被锁的对象不能进行另一个读或写操作

• 读锁，又叫共享锁，被锁的对象可以进行另一个读操作，但不能同时执行另一个写锁

• 对select加排他锁，Select * from table_name for update

• 对select加共享读锁，Select * from table_name lock in share mode

• 锁的力度越细，上锁的代价越高

• 表级锁在表的头部进行加锁

• 行级锁还需要扫描到行数据然后在加锁

什么是行级锁，什么是表级锁

• 加表锁的时候，只要操作到表里的数据的时候就要加表锁，所以表锁与索引没有关系

• 行级锁和索引有关系，SQL语句走索引的时候用到的是行级锁，没有走索引的时候用的是表级锁

5.3 数据库事务的四大特性

• 原子性（Atomic）

• 事务包含的所有操作，要不全部执行，要不全部失败回滚

• 实现原理：undo log

• 一致性（Consistency）

• 确保数据库的状态从一个一致的状态转换成另一个一致的状态，一致的含义是数据库中的数据应满足完整性约束。例如两个人转账，两人的总和是一样的

• 实现原理：可以说，一致性是事务追求的最终目标：前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。实现一致性的措施包括：

• 保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证

• 数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等

• 应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致

• 隔离性（Isolation）

• 多个事务并发执行时，一个事务的执行，不应该影响另外事务的执行

• 实现原理：锁机制

• 持久性（Durability）

• 一个事务一旦提交，它将永久保存在数据库中。即当一个事务提交后，如果系统故障，对该事务的更新不应丢失，即对已提交事务的更新可以恢复。一旦一个事务提交DDMS应保证提供适当的冗余，使其耐得住系统故障

• 实现原理：redo log

5.4 MySQL的RedoLog与UndoLog

5.4.1 RedoLog

重做日志(redo log)用来保证事务的持久性主要作用是用于数据库的崩溃恢复。

实际上它可以分为以下两种类型：

• 物理Redo日志

• 逻辑Redo日志

    在InnoDB存储引擎中，大部分情况下 Redo是物理日志，记录的是数据页的物理变化。而逻辑Redo日志，不是记录页面的实际修改，而是记录修改页面的一类操作，比如新建数据页时，需要记录逻辑日志。关于逻辑Redo日志涉及更加底层的内容，这里我们只需要记住绝大数情况下，Redo是物理日志即可，DML对页的修改操作，均需要记录Redo.   

Redo log可以简单分为以下两个部分：

• 一是内存中重做日志缓冲 (redo log buffer),是易失的，在内存中

• 二是重做日志文件 (redo log file)，是持久的，保存在磁盘中

Redo的整体流程

• 第一步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝

• 第二步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值

• 第三步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式

• 第四步：定期将内存中修改的数据刷新到磁盘中

  
  

    上图表示了重做日志的写入流程，每个mini-transaction对应每一条DML操作，比如一条update语句，其由一个mini-transaction来保证，对数据修改后，产生redo1，首先将其写入mini-transaction私有的Buffer中，update语句结束后，将redo1从私有Buffer拷贝到公有的Log Buffer中。当整个外部事务提交时，将redo log buffer再刷入到redo log file中。

redo如何保证 事务的持久性？

    InnoDB是事务的存储引擎，其通过Force Log at Commit 机制实现事务的持久性，即当事务提交时，先将 redo log buffer 写入到 redo log file 进行持久化，待事务的commit操作完成时才算完成。这种做法也被称为 Write-Ahead Log(预先日志持久化)，在持久化一个数据页之前，先将内存中相应的日志页持久化。

    为了保证每次日志都写入redo log file，在每次将redo buffer写入redo log file之后，默认情况下，InnoDB存储引擎都需要调用一次 fsync操作,因为重做日志打开并没有 O_DIRECT选项，所以重做日志先写入到文件系统缓存。为了确保重做日志写入到磁盘，必须进行一次 fsync操作。fsync是一种系统调用操作，其fsync的效率取决于磁盘的性能，因此磁盘的性能也影响了事务提交的性能，也就是数据库的性能。    (O_DIRECT选项是在Linux系统中的选项，使用该选项后，对文件进行直接IO操作，不经过文件系统缓存，直接写入磁盘)

    上面提到的Force Log at Commit机制就是靠InnoDB存储引擎提供的参数 innodb_flush_log_at_trx_commit来控制的，该参数可以控制 redo log刷新到磁盘的策略，设置该参数值也可以允许用户设置非持久性的情况发生，具体如下：

• 当设置参数为1时，（默认为1），表示事务提交时必须调用一次 fsync 操作，最安全的配置，保障持久性

• 当设置参数为2时，则在事务提交时只做 write 操作，只保证将redo log buffer写到系统的页面缓存中，不进行fsync操作，因此如果MySQL数据库宕机时 不会丢失事务，但操作系统宕机则可能丢失事务

• 当设置参数为0时，表示事务提交时不进行写入redo log操作，这个操作仅在master thread 中完成，而在master thread中每1秒进行一次重做日志的fsync操作，因此实例 crash 最多丢失1秒钟内的事务。（master thread是负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性）

    fsync和write操作实际上是系统调用函数，在很多持久化场景都有使用到，比如 Redis 的AOF持久化中也使用到两个函数。fsync操作 将数据提交到硬盘中，强制硬盘同步，将一直阻塞到写入硬盘完成后返回，大量进行fsync操作就有性能瓶颈，而write操作将数据写到系统的页面缓存后立即返回，后面依靠系统的调度机制将缓存数据刷到磁盘中去,其顺序是user buffer——> page cache——>disk。

5.4.1 Undo Log

undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

undo log的作用

undo是一种逻辑日志，有两个作用：

• 用于事务的回滚

• MVCC（多版本并发控制）

    undo日志，只将数据库逻辑地恢复到原来的样子，在回滚的时候，它实际上是做的相反的工作，比如一条INSERT ，对应一条 DELETE，对于每个UPDATE,对应一条相反的 UPDATE,将修改前的行放回去。undo日志用于事务的回滚操作进而保障了事务的原子性。

undo log的写入时机

• DML操作修改聚簇索引前，记录undo日志

• 二级索引记录的修改，不记录undo日志

    需要注意的是，undo页面的修改，同样需要记录redo日志。

undo的存储位置

    在InnoDB存储引擎中，undo存储在回滚段(Rollback Segment)中,每个回滚段记录了1024个undo log segment，而在每个undo log segment段中进行undo 页的申请，在5.6以前，Rollback Segment是在共享表空间里的，5.6.3之后，可通过 innodb_undo_tablespace设置undo存储的位置。

undo的类型

    在InnoDB存储引擎中，undo log分为：

• insert undo log

• update undo log

    insert undo log是指在insert 操作中产生的undo log，因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删除，不需要进行purge操作。

而update undo log记录的是对delete 和update操作产生的undo log，该undo log可能需要提供MVCC机制，因此不能再事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

    补充：purge线程两个主要作用是：清理undo页和清除page里面带有Delete_Bit标识的数据行。在InnoDB中，事务中的Delete操作实际上并不是真正的删除掉数据行，而是一种Delete Mark操作，在记录上标识Delete_Bit，而不删除记录。是一种"假删除",只是做了个标记，真正的删除工作需要后台purge线程去完成。

5.5 事务隔离级别以及各级别下的并发访问问题

事务的四种隔离级别

隔离级别

• 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

• 提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

• 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

并发访问问题

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

• 不可重复读:是指在一个事务内，多次读同一数据，结果不一致

• 同时开启两个事务，事务1和事务2

• 第一个事务select数据，值为s1

• 第二个一个事务也访问该同一数据。

• 第二个事务修改数据，

• 第一个事务select数据，值为s1

• 第二个数据提交修改的数据s2

• 第一个事务内再次读取数据值为s2

这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

• 幻读:

• 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。表里数据行数是count

• 第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。

• 第二个事务提交，表里数据为count+1

• 第一个事务的用户发现本来只需要更新count条数据，执行后却发现更新了count+1条，就好象发生了幻觉一样。

5.6 InnoDB可重复读隔离级别下如何避免幻读 

MVCC简介

    MVCC (Multiversion Concurrency Control)，即多版本并发控制技术,它使得大部分支持行锁的事务引擎，不再单纯的使用行锁来进行数据库的并发控制，取而代之的是把数据库的行锁与行的多个版本结合起来，只需要很小的开销,就可以实现非锁定读，从而大大提高数据库系统的并发性能。

• 读锁：也叫共享锁、S锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S 锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

• 写锁：又称排他锁、X锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

• 表锁：操作对象是数据表。Mysql大多数锁策略都支持(常见mysql innodb)，是系统开销最低但并发性最低的一个锁策略。事务t对整个表加读锁，则其他事务可读不可写，若加写锁，则其他事务增删改都不行。

• 行级锁：操作对象是数据表中的一行。是MVCC技术用的比较多的，但在MYISAM用不了，行级锁用mysql的储存引擎实现而不是mysql服务器。但行级锁对系统开销较大，处理高并发较好。

MVCC实现原理

    innodb MVCC主要是为Repeatable-Read事务隔离级别做的。在此隔离级别下，A、B客户端所示的数据相互隔离，互相更新不可见

    了解innodb的行结构、Read-View的结构对于理解innodb mvcc的实现由重要意义

    innodb存储的最基本row中包含一些额外的存储信息 DATA_TRX_ID，DATA_ROLL_PTR，DB_ROW_ID，DELETE BIT

• 6字节的DATA_TRX_ID 标记了最新更新这条行记录的transaction id，每处理一个事务，其值自动+1

• 7字节的DATA_ROLL_PTR 指向当前记录项的rollback segment的undo log记录，找之前版本的数据就是通过这个指针

• 6字节的DB_ROW_ID，当由innodb自动产生聚集索引时，聚集索引包括这个DB_ROW_ID的值，否则聚集索引中不包括这个值.，这个用于索引当中

• DELETE BIT位用于标识该记录是否被删除，这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在commit的时候

具体的执行过程

    begin->用排他锁锁定该行->记录redo log->记录undo log->修改当前行的值，写事务编号，回滚指针指向undo log中的修改前的行

    上述过程确切地说是描述了UPDATE的事务过程，其实undo log分insert和update undo log，因为insert时，原始的数据并不存在，所以回滚时把insert undo log丢弃即可，而update undo log则必须遵守上述过程    

    下面分别以select、delete、 insert、 update语句来说明

• SELECT

  Innodb检查每行数据，确保他们符合两个标准：

• 1、InnoDB只查找版本早于当前事务版本的数据行(也就是数据行的版本必须小于等于事务的版本)，这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行

• 2、行的删除操作的版本一定是未定义的或者大于当前事务的版本号，确定了当前事务开始之前，行没有被删除

符合了以上两点则返回查询结果。

• INSERT

• InnoDB为每个新增行记录当前系统版本号作为创建ID。

• DELETE

• InnoDB为每个删除行的记录当前系统版本号作为行的删除ID。

• UPDATE

• InnoDB复制了一行。这个新行的版本号使用了系统版本号。它也把系统版本号作为了删除行的版本。

• 说明

• insert操作时 “创建时间”=DB_ROW_ID，这时，“删除时间 ”是未定义的；

• update时，复制新增行的“创建时间”=DB_ROW_ID，删除时间未定义，旧数据行“创建时间”不变，删除时间=该事务的DB_ROW_ID；

• delete操作，相应数据行的“创建时间”不变，删除时间=该事务的DB_ROW_ID；

• select操作对两者都不修改，只读相应的数据

    从上面mvcc控制下的select操作需要满足的条件可以知道我们查询到的记录一定是当前事务开始之前就有的，而且不会被更改可见，如果更改之后事务id+1，大余当前事务id你不满足条件。因此就可以保证了可重复度，同时也会消除幻读。

5.7 next-key锁

网络上绝大多数的人都会认为幻读的解决是因为间隙锁。其实不是这样。

MySQL InnoDB支持三种行锁定方式：

• 行锁（Record Lock）：锁直接加在索引记录上面。

• 间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。

• Next-Key Lock：行锁与间隙锁组合起来用就叫做Next-Key Lock。

    默认情况下，InnoDB工作在可重复读隔离级别下，并且以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁与间隙锁的组合，这样，当InnoDB扫描索引记录的时候，会首先对选中的索引记录加上行锁（Record Lock），再对索引记录两边的间隙（向左扫描扫到第一个比给定参数小的值， 向右扫描扫描到第一个比给定参数大的值， 然后以此为界，构建一个区间）加上间隙锁（Gap Lock）。如果一个间隙被事务T1加了锁，其它事务是不能在这个间隙插入记录的。

    间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。另外，在上面的例子中，我们选择的是一个普通（非唯一）索引字段来测试的，这不是随便选的，因为如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁，仔细想想的话，这个并不难理解，大家也可以自己测试一下。

要禁止间隙锁的话，可以把隔离级别降为读已提交，或者开启参数innodb_locks_unsafe_for_binlog。

5.8 RC、RR级别下的InnoDB的非阻塞读如何实现

5.8.1 当前读和快照读

当前读：

• select…lock in share mode（共享锁）,select…for update（排他锁）

• update，delete，insert（排他锁）

    当前读就是加了锁的增删改查语句，无论是上的共享锁还是排他锁均为当前读，读取的为当前的最新版本并且读取之后还要保证其他并发事务不能修改当前记录，对读取的记录加锁RDBMS主要由两部分组成（程序实例和存储（InnoDB））。

    以update语句为例：当update SQL发给Mysql之后，MysqlSever会根据where条件，读取第一条满足条件的记录（select row 1）innoDB引擎会将第一条记录返回并加锁（return&lock）待mysqlsever接收到这条加锁的记录后会发起一个update操作去更新这条记录，一条记录更新完成了之后再读取下一条记录直至没有满足条件的记录为止，update操作就包含了一个当前读来获取数据的最新版本，就跟在readcommitted下出现的这个幻读的情况一样由于先前另外一个事务新提交了一个数据当前事务update全表的时候就莫名多了一条数据即读取到了数据的最新版本，同理DELETE操作也一样，insert操作会稍有不同，简单的来说insert操作可能会触发唯一键的冲突检查也会进行一个当前读。

快照读

    不加锁的非阻塞读，select（不加锁的条件是以在事务隔离级别不为Serializable的前提下才成立的，由于serializable是串行读，所以此时的快照读也退化为当前读，即select…lock in share mode 模式，之所以出现快照读是基于提升并发效率的考虑，快照读的实现是基于多版本的并发控制即MVCC,可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁的操作，因此开销更低，既然是基于多版本，也就意味着快照读可能读到的并不是最新版本的数据，可能是之前的历史版本）

5.8.2 InnoDB的非阻塞读是在RC/RR隔离级别下是如何实现的

实现非阻塞读的三个因子

1. 数据行里的，上面一节也介绍过

• DB_TRX_ID（标识最近一次对本行数据做修改，无论是insert，update，事务的标识符，即最后一次修改本行数据的事务ID，delete在innoDB看来也是一次update操作，更新行中的一个特殊位，将行标识为deleted,也就是说数据行中除了这三列，还有一个被称为deleted的隐藏列）

• DB_ROLL_PTR（回滚指针，指写入回滚段ROLLBACK SEGMENT的undo日志记录，如果一行记录被更新，则undolockrecalled包含从建该行记录被更新之前内容所必须的信息）

• DB_ROW_ID（行号，包含一个随着新行插入而单调递增的行ID,当由innoDB自动产生具体索引时，具体索引会包括这个行id的值，否则这个行ID不会出现在任何索引中）字段

2. undo日志，上面章节也接收过

   当我们对记录做了变更操作时，就会产生undo记录，undo记录中存储的是老版的数据，当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链找到满足其可见性的记录，undolog主要分为两种insertundolog,updateundolog,其中insertundolog表示事务对insert新记录产生的undolog,只在事务回滚时需要并且在事务提交后就可能会立即丢弃。updateundolog事务对记录进行delete或者update时产生的undolog，不仅在事务回滚时需要，快照读也需要所以不能删除，只有数据库所使用的快照中不涉及该日志记录对应的回滚日志才会被删除。

3. read view

   主要用来做可见性判断的，即当我们做快照读select的时候会针对我们所查询的数据创建出一个read view来决定当前事务能看到的是哪个版本的数据，有可能是当前最新的数据，也有可能只允许你看undolog里面某个版本的数据，遵循可见性算法。主要是将要修改的数据的DB_TRX_ID取出来，与系统其他活跃ID做对比如果大于或者等于这些ID的话，就通过DB_ROLL_PTR指针去取出undolog上一层的DB_TRX_ID直到小于这些活跃事务的ID为止，这样就保证了我们获取的数据版本是是当前最稳定的版本。

    每当我们start transaction的时候事务ID都会去递增，也就是说越新开启的事务这个ID就会越大由于生成是时机不同，造成了RC,RR两种事务隔离级别的可见性不同。

    在Repeatable read隔离级别下，session Strat transaction后的第一条快照读会创建一个快照即read view ,将当前活跃的其他事务记录起来，此后再调用快照读的时候还是用的是同一个read view。

    在 read committed级别下，事务中每条 select语句每次调用快照读的时候都会创建新的快照，这就是为什么我们在此隔离级别下能用快照读看到其他事务已提交的对表的增删改了，而在RR下如果首次使用快照读是在别的事务对数据进行增删改提交之前的，此后即便别的事务对数据做了增删改并提交，还是读不到数据变更的原因（首次select的时机很重要），

    由于以上的三个因子才使得innoDB在RR或者RC级别下支持非阻塞读，而读取数据时的非阻塞就是所谓的MVCC(Multi-Version Concurrency Control多版本控制)，而InnoDB的非阻塞读机制实现的仿制版的MVCC,并没有实现MVCC的核心的多版本共存，undolog中的内容只是串行化的结果，记录了多个事务的过程，不属于多版本共存读不加锁，读写不冲突，在读多写少的应用中读写不冲突是非常重要的，极大的增加了系统的并发性能，快照读并非是幻读现象发生的根本，只是你如果先要提交数据变更的事务，打开read view时不论别的事务的变更是否已提交，在当前事务内再次调用快照读的时候还是读的可见性版本内的数据，有一种掩耳盗铃的意思在里面。而真正防止幻读发生的原因是事务对数据加了next-key锁。

支持小猿的话请点击阅读原文或识别下方二维码关注小猿吧，更多精彩总结长文等你阅读~

参考：

MySQL：

https://baijiahao.baidu.com/s?id=1661801015248945613&wfr=spider&for=pc

https://zhuanlan.zhihu.com/p/57332938

索引：

https://blog.csdn.net/Jack__Frost/article/details/72571540

https://zhuanlan.zhihu.com/p/52124916

二分查找树：https://www.cnblogs.com/sunchong/p/9521376.html

红黑树：https://www.cnblogs.com/axing-articles/p/11402620.html

B/B+树：https://blog.csdn.net/hao65103940/article/details/89032538

ACID：https://www.cnblogs.com/kismetv/p/10331633.html

log：

https://www.jianshu.com/p/db19a1d384bc

https://juejin.im/entry/5ba0a254e51d450e735e4a1f

https://segmentfault.com/a/1190000017888478

https://www.cnblogs.com/wy123/p/8365234.html

隔离级别

https://tech.meituan.com/2014/08/20/innodb-lock.html