MySQL知识点总结

1 索引

1.1 什么是索引？

什么是索引

• 索引（index）是帮助MySQL高效获取数据的数据结构（有序） ，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。
• 我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

优势与劣势

优势：

• 提高数据检索的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本。

劣势：

• 时间方面：创建和维护索引要耗费时间，进行增/改/删表数据时，还要动态维护，会降低增/改/删的执行效率；
• 空间方面：需要占物理空间。

1.2 如何创建索引（三种方式）

1.2.1 CREATE TABLE 创建索引

CREATE TABLE table_name [col_name data_type ]
[ UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length])[ASC | DESc]
1
2

• UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引，若不选则表示创建的是普通索引。
• INDEX与KEY为同义词，两者的作用相同，用来指定创建索引;
• index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名;
• col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择;
• length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度;
• ASC 或 DESC 指定升序或者降序的索引值存储。

-- 创建普通索引
create table book(
    book_id int,
    book_name varchar(50),
    authors varchar(50),
    info varchar(50),
    comment varchar(50),
    year_publication year,
    index idx_bname(book_name) -- 声明索引，字段为book_name
);
1
2
3
4
5
6
7
8
9
10

1.2.2 ALTER TABLE 创建索引

ALTER TABLE table_name ADD 
[UNIQUE | FULLTEXT | SPATIAL][INDEX | KEY] [index_name] (col_name [length] ,...)[ASC | DESC]
1
2

-- 创建普通索引
alter table book2 add index idx_cmt(comment);

-- 创建唯一索引
alter table book2 add unique uk_idx_bname(book_name);

-- 创建联合索引
alter table book2 add index mul_bid_bname_info(book_id,book_name,info);

1
2
3
4
5
6
7
8
9

1.2.3 CREATE INDEX 创建索引

create index idx_cmt on book3(comment);-- 创建普通索引

create unique index uk_idx_bname on book3(book_name);-- 创建唯一索引

create index mul_bid_bname_info on book3(book_id,book_name,info);-- 创建联合索引

1
2
3
4
5
6

1.3 如何删除索引？

1.3.1 ALTER TABLE 删除索引

ALTER TABLE table_name DROP INDEX index_name;

-- 示例
alter table book3 drop index idx_cmt;
alter table book3 drop index mul_bid_bname_info;

1
2
3
4
5
6

1.3.2 DROP INDEX 删除索引

DROP INDEX index_name on table_name;
--示例
drop index idx_cmt on book2;
drop index mul_bid_bname_info on book2;
1
2
3
4

1.4 索引的分类

• 从存储结构上来划分：BTree索引（B-Tree或B+Tree），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式。
• 从应用层次来分：普通索引、唯一索引、复合索引。
  • 主键索引：索引列中的值必须是唯一的，不允许有空值。
  • 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
  • 唯一索引：索引列的值必须唯一，但允许有空值 。
  • 复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
• 根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引、非聚集索引。
  • 聚簇索引(聚集索引)：聚集索引的顺序就是数据的物理存储顺序。并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
  • 非聚簇索引： 不是聚簇索引，就是非聚簇索引

1.5 索引的底层实现结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。MySQL目前提供了以下4种索引：

• BTREE 索引： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

1.6 Hash索引

• Hash索引：基于哈希表实现 ，只有精确匹配索引所有列的查询才有效， 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code） ，并且Hash索引将所有的哈希码存储在索引中， 同时在索引表中保存指向每个数据行的指针。
  

1.7 B树

1.7.1 BTree的特点

BTree又叫 多路平衡搜索树 ，一颗m叉的BTree特性如下：

• 树中每个节点最多包含m个孩子。
• 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。ceil为向上取整
• 若根节点不是叶子节点，则至少有两个孩子。
• 所有的叶子节点都在同一层。
• 每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1

1.7.2 BTree的作用

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。因此在讲B-Tree之前先了解下磁盘的相关知识。

系统从磁盘读取数据到内存时是以 磁盘块(block) 为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有 页(Page)的概念， 页是其磁盘管理的最小单位 。InnoDB存储引擎中 默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：

mysql> show variables like 'innodb_page_size'
1

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

1.7.3 BTree查询数据的过程

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个 3阶的B-Tree ：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域 对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

• 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
• 比较关键字29在区间(17,35)，找到磁盘块1的指针P2。
• 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
• 比较关键字29在区间(26,30)，找到磁盘块3的指针P2。
• 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
• 在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现 需要3次磁盘I/O操作，和3次内存查找操作 。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而 3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

1.8 B+树

1.8.1 B+Tree的特点

• n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
• B+Tree的叶子节点保存所有的key信息，依key大小顺序排列 。
• 所有的非叶子节点都可以看作是key的索引部分。

MySQL中B+Tree相对于BTree的特点

• 非叶子节点只存储键值信息。
• 所有叶子节点之间都有一个链指针。
• 数据记录都存放在叶子节点中。

由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

1.8.2 B+Tree的查询流程

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

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点 （啥意思？？？？？？？？），而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：

• 一种是对于主键的范围查找和分页查找；
• 另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：InnoDB存储引擎中页的大小为16KB

• B+Tree：一般表的主键类型为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亿 条记录（啥意思，为啥是相乘？？？？？？？？）。
• BTree：假设指针不占用空间，一条数据1KB，每个磁盘块16条数据，三层B树能存储16 * 16 * 16=4096条数据，一个磁盘块代表一次IO，很明显数据量多的情况下，IO次数也会多，会影响查询性能，于是在B树的基础上衍生出了B+树。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。mysql的InnoDB存储引擎在设计时 是将根节点常驻内存的，也就是说查找某一键值的行记录时 最多只需要1 - 3次磁盘I/O操作 。

1.8.3 为什么用B+树而不用B树

B+ 树是基于 B 树和叶子节点顺序访问指针进行实现，它具有 B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能，数据都在叶子节点上。

• 从BTree结构图中可以看到 每个节点中 不仅包含数据的 key值 ，还有data值。而每一个页的存储空间是有限的：

  • 如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小，
  • 当存储的数据量很大时同样会导致B-Tree的 深度较大 ，增大查询时的磁盘I/O次数，进而影响查询效率。

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

• 用 B+ 树不用 B 树考虑的是 IO 对性能的影响，B 树的每个节点都存储数据，而 B+ 树只有叶子节点才存储数据。

• 由于B树的分支结点存储着数据，我们要找到具体的数据，需要 进行一次中序遍历 按序来扫。而由于B+树的数据都 存储在叶子结点中 ，叶子结点均为索引，方便扫库， 只需要扫一遍叶子结点即可 。

• 所以B+树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁的，所以B+树更适合用于数据库索引。

1.8.4 B树（B-树）和B+树的区别?

• 在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
• B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

1.8.5 什么时候可以使用B树（B-树）？

B树可以在内部节点同时存储键和值，因此把频繁访问的数据放 在靠近根节点的地方 将会大大提高热点数据的查询效率。 这种特性使得B树在特定数据重复多次查询的场景中更加高效 。

1.8.6 为什么用B+树，而不是用二叉树、红黑树？

• B+树有个特点，就是够矮够胖，能有效地减少访问节点次数从而提高性能。
• 虽然二叉树也有很好的查找性能log2N，但是当N比较大的时候，树的深度比较高。二叉树深度越大，查找的次数越多，性能越差。最坏的情况会退化成链表。所以，B+树更适合作为MySQL索引结构。
• 红黑树： 树的高度随着数据量增加而增加，IO代价高。

1.9 聚簇索引 & 非聚簇索引（二级索引）

1.9.1 什么是聚簇索引？什么是非聚簇索引（二级索引）？（重要）

这先要从InnoDB的索引实现说起，InnoDB有两大类索引：

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据。
• 非聚簇索引：将数据和索引分开存储，索引结构的叶子节点指向了数据的对应行。

聚集索引严格来说并不是索引类型，而是一种数据存储方式，具体细节依赖于其实现方式。如innodb聚集索引的叶子节点存放了整张表的行记录（B+Tree）。将数据存储与索引放到了一块，找到索引也就找到了数据。

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

• 如果表定义了主键，则主键就是聚集索引；
• 如果表没有定义主键，则第一个不为空的唯一列是聚集索引；
• 否则，InnoDB会创建一个隐藏的 Row ID 作为聚集索引。

1.9.2 聚簇索引与非聚簇索引的区别是什么？

• 非聚集索引与聚集索引的区别在于 将数据与索引分开存储，索引结构的叶子节点指向了数据的对应行 。
• 对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。 第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描 。
• 通常情况下， 主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。
• 如果是覆盖索引的话，查一次即可。
• 注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

1.9.3 什么是回表查询？

上在InnoDB引擎中，非主键索引查找数据时需要先找到主键，再根据主键查找具体行数据，这种现象叫回表查询。

假设表中有四条记录：1, shenjian, m, A；3, zhangsan, m, A；5, lisi, m, A；9, wangwu, f, B

两个B+树索引分别如上图：

• id 为 主键，聚集索引，叶子节点存储行记录；
• name为KEY，普通索引，叶子节点存储主键值，即id；

查询：select * from t where name=‘lisi’;　

如粉红色路径，需要扫码两遍索引树：

• 先通过普通索引定位到主键值id=5；
• 在通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录 ，它的性能较扫一遍索引树更低。

1.9.4 为什么B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据？非聚簇索引一定会回表查询吗？

当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

如果涉及到查询语句所要求的字段全部命中了索引，那么就不必再进行回表查询。

一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"。

1.9.5 聚簇索引相比非聚簇索引有什么优点？

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
• 聚集索引叶子节点的存储是逻辑上连续的，所以对于主键的排序查找和范围查找速度会更快。

1.10谈谈你对覆盖索引的认识？

一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"。具有以下优点：

• 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
• 一些存储引擎（例如：MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
• 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

1.11 最左前缀索引

创建复合索引:

	CREATE INDEX idx_name_email_status ON tb_seller(a,b,c);

就相当于
	对a创建索引 ;
	对a, b创建了索引 ;
	对a, b, c创建了索引 ;
只要查询条件包含了name，就会利用索引查询
1
2
3
4
5
6
7
8
9

b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的。

比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；

但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。

比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

1.12 什么是前缀索引？（也是种优化）使用前缀索引的流程？

因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引 ，就会产生超级加倍的效果。但是，我们需要注意，order by不支持前缀索引 。

流程是：

1. 先计算完整列的选择性 : select count(distinct col_1)/count(1) from table_1
2. 再计算不同前缀长度的选择性 : select count(distinct left(col_1,4))/count(1) from table_1
3. 找到最优长度之后，创建前缀索引 : create index idx_front on table_1 (col_1(4))

1.13 怎么查看MySQL语句有没有用到索引？或者说怎么才可以知道这条语句运行很慢的原因?

通过explain关键字，如以下例子：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='SeniorEngineer' AND from_date='1986-06-26';
1

• type： type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等。
  • 不同的 type 类型的性能关系如下: ALL < index < range ~ index_merge < ref < eq_ref < const < system
  • 如const(主键索引或者唯一二级索引进行等值匹配的情况下)
  • ref(普通的⼆级索引列与常量进⾏等值匹配)
  • index(扫描全表索引的覆盖索引) 。
  • ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
  • index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
• possible_key：查询中可能用到的索引(可以把用不到的删掉，降低优化器的优化时间) 。
• key：此字段是 MySQL 在当前查询时所真正使用到的索引。
• rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。
• extra：表示额外信息，如Using where,Start temporary,End temporary,Using temporary等。

1.14 说说索引设计的原则

• 出现在where或者连接子句中指定的列
• 基数要够大的列
• 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度节省空间
• 不要过度索引，只保持需要的索引有利于查询即可

1.15 说说创建索引的原则

• 最左前缀匹配原则，组合索引非常重要的原则，
• 对查询频次较高，且数据量比较大的表建立索引。
• 更新频繁字段不适合创建索引
• 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。不能有效区分数据的列不适合做索引列。
• 使用不等于（<,>,!=）的时候无法使用索引，会导致索引失效。
• 尽量扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
• 查询中很少涉及的列，重复值比较多的列不要建立索引。
• 定义为text、image和bit的数据类型的列不要建立索引。
• 索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
• 使用短索引（前缀索引）。在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

1.16 索引失效情况有哪些？

1、全值匹配 ，对索引中所有列都指定具体值。
2、最左前缀法则，**如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
3、不要在索引列上进行运算操作， 索引将失效。
4、字符串不加单引号，造成索引失效。
5、尽量使用覆盖索引，避免select * ，如果查询列，超出索引列，也会降低性能。
6、用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
7、以 %开头的Like模糊查询，索引失效。解决方案 ：通过覆盖索引来解决，这样即使是模糊查询也会走索引。
8、如果MySQL评估使用索引比全表更慢，则不使用索引。
9、is NULL ， is NOT NULL 有时 索引失效。比如：一个字段都不为空，那么使用NOT NULL还不如走全表扫描，mysql会自动判断。
10、in 走索引， not in 索引失效。
11、单列索引和复合索引。尽量使用复合索引，而少使用单列索引 。针对单列索引，数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。

1.17 索引下推

• 索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

• 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时， 存储引擎通过索引检索到数据，然后返回给MySQL服务器，由服务器然后判断数据是否符合条件 。

• 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

2 存储引擎

2.1 MySQL体系结构

1） 连接层：最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2） 服务层：第二层架构主要完成大多数的核心服务功能，如 SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。 所有跨存储引擎的功能也在这一层实现，如 过程、函数等。 在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。 如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3） 引擎层：存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

4）存储层：数据存储层， 主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

2.2 MySQL 中一条查询 SQL 是如何执行的

1. 取得链接，使用使用到 MySQL 中的连接器。
2. 查询缓存，key 为 SQL 语句，value 为查询结果，如果查到就直接返回。不建议使用次缓存，在 MySQL 8.0 版本已经将查询缓存删除，也就是说 MySQL 8.0 版本后不存在此功能。
3. 分析器，分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错误在此阶段。
4. 优化器，是在表里有多个索引的时候，决定使用哪个索引；或者一个语句中存在多表关联的时候（join），决定各个表的连接顺序。
5. 执行器，，将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。

2.3 MySQL数据库引擎有哪些？

如何查看mysql提供的所有存储引擎

mysql> show engines;
1

mysql常用引擎包括：MYISAM、Innodb、Memory、MERGE

• MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎
• Innodb:行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些
• Memory:全表锁，存储在内存中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引。检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表
• MERGE：是一组MYISAM表的组合

2.4 InnoDB 与 MyISAM的区别

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

在 MySQL 5.1 及之前的版本中，MyISAM 是默认的存储引擎，而在 MySQL 5.5 版本以后，默认使用 InnoDB 存储引擎。

• InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
• InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
• InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
• InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
• Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

MEMORY 与 MERGE

• Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。MEMORY 类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引 ， 但是服务一旦关闭，表中的数据就会丢失。
• MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。
  • 对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。
  • 可以对MERGE表进行DROP操作，但是这个操作只是删除MERGE表的定义，对内部的表是没有任何影响的。

2.5 存储引擎的选择

• InnoDB : 是Mysql的默认存储引擎，支持事务，支持外键。
  • 如果对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎能有效的降低由于删除和更新导致的锁定，
  • InnoDB还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
• MyISAM ： 如果应用是 以读操作和插入操作为主 ，只有很少的更新和删除操作，并且 对事务的完整性、并发性要求不是很高 ，那么选择这个存储引擎是非常合适的。
• MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。**MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。**MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
• MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

3 MySQL 事务

3.1 什么是数据库事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，它是逻辑上的一组操作，要么都执行，要么都不执行。

3.2 事务的四个特征

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元。

事务具有以下4个特性，简称为事务ACID属性。

3.3 并发事务处理会带来什么问题

3.4 四种事务隔离级别

数据库的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read（默认）、Serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

隔离级别并不是越高越好，需要进行权衡

备注 ： √ 代表可能出现 ， × 代表不会出现 。

Repeatable read（默认）：明确数据读取出来就是为了更新用的，所以要加一把锁，防止别人修改它。

3.5 隔离级别与锁的关系（ 重要 重要 重要 重要 ）

在事物中存在以下几种隔离级别：

• 读未提交(Read Uncommitted)：解决更新丢失问题。如果一个事务已经开始写操作，那么其他事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现，即事物需要对某些数据进行修改必须对这些数据加 X 锁，读数据不需要加 S 锁。

• 读已提交(Read Committed)：解决了脏读问题。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。这可以通过“瞬间共享读锁”和“排他写锁”实现， 即事物需要对某些数据进行修改必须对这些数据加 X 锁，读数据时需要加上 S 锁，当数据读取完成后立刻释放 S 锁，不用等到事物结束。

• 可重复读取(Repeatable Read)：禁止不可重复读取和脏读取，但是有时可能出现幻读数据。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。Mysql默认使用该隔离级别。这可以通过“共享读锁”和“排他写锁”实现，即事物需要对某些数据进行修改必须对这些数据加 X 锁，读数据时需要加上 S 锁，当数据读取完成并不立刻释放 S 锁，而是等到事物结束后再释放。

• 串行化(Serializable)：解决了幻读的问题的。提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

3.6 事务日志 redo log & undo log

事务是基于重做日志(redo log)和回滚日志(undo log)实现的，这两个就是所说的事务日志。

• redo log 指事务中操作的任何数据，将最新的数据备份到一个地方。（事务重做，保证持久性）
• undo log 指事务开始之前，首先将需操作的数据备份到一个地方。（事务回滚，保证原子性）

3.6.1 redo log

InnoDB存储引擎是以页为单位来管理存储空间的。在访问真正的页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。所有的数据变更也都必须新更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘（checkPoint机制），通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降太快。

3.6.1.1 redo log 的组成

redo log 用来实现事务的持久性，即事务 ACID 中的 D。其由两部分组成：

• 一是内存中的重做缓冲日志（redo log buffer），其是易失的；
• 二是重做日志文件 (redo log file)，其是持久的。

重做缓冲日志（redo log buffer）

在服务器启动时就向操作系统申请了一大片称为redo log buffer的连续内存空间，即redo日志缓冲区。这片内存空间被划分为若干个连续的redo log block。每个redo log block占用 512字节 大小。

参数 innodb_log_buffer_size 可以来设置 redo log buffer的大小

redo log buffer 大小，默认16M ，最大值是4096M，最小值为1M。

mysql> show variables like '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1
2
3
4
5
6

3.6.1.2 redo log 的工作流程

• 第1步：先将需要操作的表的原始数据从磁盘中读入内存中来，修改数据的内存拷贝；
• 第2步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值（第二步操作是在事务的执行过程中就开始执行了，而不是等到commit时才执行）；
• 第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用 追加写 的方式；
• 第4步：定期将内存中修改的数据刷新到磁盘中（这里涉及到刷盘的略）；

undo log： undo log 用来提供回滚和多个行版本控制(MVCC，快照度)。事务未提交之前，Undo 保存了未提交之前的版本数据。是为了实现事务的 原子性 而出现的产物。

3.6.1.3 redo log 的刷盘策略

redo log的写入并不是直接写入磁盘的，InnoDB引擎会在写redo log的时候先写redo log buffer，之后以一定的频率刷入到真正的redo log file 中。

redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到文件系统缓存（page cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系统自己来决定（比如page cache足够大了）。

那么对于InnoDB来说就存在一个问题，如果交给系统来同步，同样如果系统宕机，那么数据也丢失了（虽然整个系统宕机的概率还是比较小的）。

针对这种情况，InnoDB给出innodb_flush_log_at_trx_commit 参数，该参数控制 commit提交事务时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：

• 设置为0 ：表示每次事务提交时不进行刷盘操作（都没有将redo log buffer中的内容写入 page cache中，其实就是啥也没做）。（系统默认master thread每隔1s进行一次重做日志的同步）
• 设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（默认值）
• 设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。

补充：InnoDB存储引擎有一个后台线程（master thread），每隔 1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用刷盘策略（fsync）。所以，针对设置为0的情况，就是由后台线程进行刷盘。

3.6.2 undo log

undo log是事务原子性的保证。在事务中更新数据的前置操作其实是要先写入一个undo log 。

MySQL把这些为了回滚而记录的这些内容（insert、delete、update）称之为撤销日志或者回滚日志(即undo log)。注意，由于 查询操作SELECT 并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo日志。

此外，undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。

3.6.2.1 undo log的作用

作用1：回滚数据

用户对undo日志可能有误解：undo用于将数据库物理地恢复到执行语句或事务之前的样子，但事实并非如此。undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。

这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

作用2：MVCC

undo的另一个作用是MVCC，即在innoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo log读取之前的行版本信息，以此实现非锁定读取。

3.6.2.2 undo log 类型

在lnnoDB存储引擎中，undo log分为:

• insert undo log
  • insert undo log是指在insert操作中产生的undo log。
  • 因为insert操作的记录，只对事务本身可见，对其他事务不可见(这是事务隔离性的要求)，故该undo log可以在事务提交后直接删除。不需要进行purge操作。
• update undo log
  • update undo log记录的是对 delete 和 update 操作产生的undo log。
  • 该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

3.6.2.3 undo log 生成过程

对于InnoDB引擎来说，每个行记录除了记录本身的数据之外，还有几个隐藏的列:

• DB_ROWL_ID：如果没有为表显式的定义主键，并且表中也没有定义唯一索引，那么InnoDB会自动为表添加一个row_id的隐藏列作为主键。
• DB_TRX_ID：每个事务都会分配一个事务ID，当对某条记录发生变更时，就会将这个事务的事务ID写入trx_id中。
• DB_ROLL_PTR：回滚指针，本质上就是指向undo log的指针。
  

当我们执行INSERT时：

begin;
INSERT INTO user (name) VALUES ("tom");
1
2

插入的数据都会生成一条 insert undo log，并且数据的回滚指针会指向它。undo log会记录undo log的序号、插入主键的列和值…，那么在进行rollback的时候，通过主键直接把对应的数据删除即可。

当我们执行UPDATE时：

对于更新的操作会产生 update undo log，并且会分 更新主键的和 不更新主键的，假设现在执行执行不更新主键的 UPDATE 操作

UPDATE user SET name="Sun" WHERE id=1;
1

这时会把老的记录写入新的undo log，让回滚指针指向新的undo log，它的undo no是1，并且新的undo log会指向老的undo log (undo no=0)。

当我们执行更新主键的 UPDATE 操作时

UPDATE user SET id=2 WHERE id=1;
1

对于更新主键的操作，会先把原来的数据deletemark标识打开，这时并没有真正的删除数据，真正的删除会交给清理线程去判断，然后在后面插入一条新的数据，新的数据也会产生undo log，并且undo log的序号会递增。

可以发现每次对数据的变更都会产生一个undo log，当一条记录被变更多次时，那么就会产生多条undo log，undo log记录的是变更前的日志，并且 每个undo log的序号是递增的，那么当要回滚的时候，按照序号依次向前推，就可以找到我们的原始数据了。

3.6.2.4 undo log 的删除时机

针对于insert undo log

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

针对于update undo log

• 该undo log可能需要提供 MVCC机制，因此 不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

3.6.2.5 补充：purge线程（重要）

purge线程两个主要作用是：清理undo页 和 清除page里面带有Delete_Bit标识的数据行。

在InnoDB中，事务中的Delete操作实际上并不是真正的删除掉数据行，而是一种Delete Mark操作，在记录上标识Delete_Bit，而不删除记录。是一种"假删除"，只是做了个标记，真正的删除工作需要后台purge线程去完成。

3.7 在事务中可以混合使用存储引擎吗？

尽量不要在同一个事务中使用多种存储引擎，MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。

如果在事务中混合使用了事务型和非事务型的表（例如InnoDB和MyISAM表）, 在正常提交的情况下不会有什么问题。但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态 ，这种情况很难修复，事务的最终结果将无法确定。

3.8 MySQL中是如何实现事务隔离的?

读未提交和串行化 基本上是不需要考虑的隔离级别，前者不加锁限制，后者相当于单线程执行，效率太差。

MySQL 在 可重复读 级别 解决了幻读问题 ，是 通过行锁和间隙锁的组合 Next-Key 锁实现的 。

4 多版本并发控制 MVCC

MVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的 多个版本 管理来实现数据库的 并发控制。这项技术使得在InnoDB的事务隔离级别下执行 一致性读操 作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

4.1 当前读和快照读

快照读
快照读又叫一致性读，读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞读；比如这样：

SELECT * FROM player WHERE ...
1

之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下，避免了加锁操作，降低了开销。

既然是基于多版本，那么快照读可能读到的 并不一定是数据的最新版本，而 有可能是之前的历史版本。快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

当前读

当前读 读取的是记录的 最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的 SELECT，或者对数据进行增删改都会进行当前读。比如：

SELECT * FROM student LOCK IN SHARE MODE; # 共享锁
SELECT * FROM student FOR UPDATE; # 排他锁
INSERT INTO student values ... # 排他锁
DELETE FROM student WHERE ... # 排他锁
UPDATE student SET ... # 排他锁
1
2
3
4
5

4.2 MVCC实现原理之ReadView

MVCC 的实现依赖于：隐藏字段、Undo Log、Read View。

对于 InnoDB ，聚簇索引记录中包含 3 个隐藏的列：

• DB_ROW_ID（隐藏的自增 ID）：如果表没有主键，InnoDB 会自动按 ROW ID 产生一个聚集索引树；
• DB_TRX_ID（事务ID）：记录最近更新这条行记录的事务 ID，大小为 6 个字节；
• DB_ROLL_PTR（回滚指针） ：表示指向该行回滚段（rollback segment）的指针，大小为 7 个字节。该行记录上所有旧版本，在 undo 中都通过链表的形式组织。

4.2.1 什么是ReadView

在MVCC机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在Undo Log里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢?这时就需要用到ReadView了，它帮我们解决了行的可见性问题。

ReadView就是事务在使用MVCC机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照，InnoDB为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID(“活跃"指的就是，启动了但还没提交)。

4.2.2 ReadView 的组成

使用 READ UNCOMMITTED 隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。

使用 SERIALIZABLE 隔离级别的事务，InnoDB规定使用加锁的方式来访问记录。

使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务，都必须保证读到已经提交了的事务修改过的记录。假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题。

这个ReadView中主要包含4个比较重要的内容，分别如下：

• creator_trx_id ，创建这个 Read View 的事务 ID。
  • 说明：只有在对表中的记录 做改动 时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都 默认为0。
• trx_ids ，表示在生成ReadView时当前系统中 活跃的 读写事务的事务id列表(“活跃"指的就是，启动了但还没提交)。
• up_limit_id ，活跃的 事务中 最小的事务 ID。
• low_limit_id ，表示生成ReadView时系统中应该分配给下一个事务的id 值。
  • low_limit_id 是 系统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。

4.2.3 Read View 的规则

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见。

• 如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值 相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
• 如果被访问版本的 trx_id 属性值 小于 ReadView 中的 up_limit_id 值，表明生成该版本的事务在当前事务生成ReadView前 已经提交，所以该版本可以被当前事务访问。
• 如果被访问版本的 trx_id 属性值 大于或等于 ReadView中的 low_limit_id 值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本 不可以 被当前事务访问。
• 如果被访问版本的 trx_id 属性值在 ReadView 的 up_limit_id 和 low_limit_id 之间，那就需要判断一下trx_id属性值是不是在trx_ids 列表中。
  • 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。
  • 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

4.2.4 MVCC整体操作流程

了解了这些概念之后，我们来看下当查询一条记录的时候，系统如何通过MVCC找到它：

1. 首先 获取事务自己的版本号，也就是事务 ID；
2. 获取 ReadView；
3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；
4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
5. 最后 返回符合规则的数据。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

InnoDB中，MVCC是通过Undo Log + Read View进行数据读取，Undo Log保存了历史快照，而Read View规则帮我们判断当前版本的数据是否可见。

在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次 Read View。

注意：此时同样的查询语句都会重新获取一次 Read View，这时如果 Read View 不同，就可能产生不可重复读或者幻读的情况。。

当隔离级别为可重复读(REPEATABLE READ)的时候，就避免了不可重复读，这是因为一个事务只在第一次SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View。

4.3 MVCC能解决幻读吗？

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理 读-写冲突，做到即使有读写冲突时，也能做到不加锁， 非阻塞并发读，而这个读指的就是 快照读，而非当前读。当前读 实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。

我看到有的博客说MVCC在REPEATABLE READ级别下解决了幻读，这么说是不准确的，下面我们分析一下只依靠MVCC到底能不能能解决幻读。先说结论：只依靠MVCC不能解决幻读，MVCC是为了解决 读-写 之间阻塞的问题（排他锁会阻塞读操作），写操作还是需要加锁（Next-Key Lock）。如果没有MVCC，那么修改数据的操作会加排它锁，其它的读写操作都会阻塞，这样的话效率会比较低。

在之前的文章MySQL 锁：InnoDB引擎中锁分类以及表锁、行锁、页锁详解中介绍过，解决脏读、不可重复读、幻读这些问题有两种可选的解决方案：

• 方案一：读操作 利用多版本并发控制（ MVCC），写操作 进行加锁。（重要 重要）
• 方案二：读、写操作都采用加锁的方式。
  
  

方案一分析：

• 读操作使用 MVCC ，指的是 快照读；
• 写操作 进行加锁，指的是 当前读。
  
  

方案二分析：读、写操作都采用加锁的方式，指的都是 当前读

结论：在REPEATABLE READ级别下只依靠MVCC本身是不能解决幻读的，MVCC解决的是读操作，实现了读-写不冲突，写操作依旧需要加锁（Next-Key Lock）。下面我们进行详细分析

4.4 总结

这里介绍了 MVCC 在 READ COMMITTD 、REPEATABLE READ 这两种隔离级别的事务在执行快照读操作时访问记录的版本链的过程。这样使 不同事务的读-写、写-读操作并发执行，从而提升系统性能。

核心点在于 ReadView 的原理， READ COMMITTD 、REPEATABLE READ 这两个隔离级别的一个很大不同就是生成ReadView的时机不同：

• READ COMMITTD 在 每一次 进行 普通SELECT 操作前都会生成一个 ReadView；
• REPEATABLE READ 只在第一次 进行 普通SELECT 操作前生成一个 ReadView，之后的查询操作都重复使用这个ReadView就好了。

说明：我们之前说执行DELETE语句或者更新主键的UPDATE语句并不会立即把对应的记录完全从页面中删除，而是执行一个所谓的delete mark操作，相当于只是对记录打上了一个删除标志位，这主要就是为MVCC服务的。

4 MySQL锁

按照锁的粒度分数据库锁有哪些？

在关系型数据库中，可以按照锁的粒度把数据库锁分为 行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

行级锁

• 是锁定粒度最细的一种锁，只针对当前操作的行进行加锁。
• 行级锁能大大少数据库操作的冲突。其加锁粒度最小，并发度也最高
• 开销大，加锁慢； 会出现死锁 ；
• InnoDB 行级锁分为 共享锁 和 排他锁。

表级锁

• 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
• 表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
• 开销小，加锁快； 不会出现死锁 ；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁

• 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁。
• 开销和加锁时间界于表锁和行锁之间； 会出现死锁 ；锁定粒度界于表锁和行锁之间，并发度一般

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁。

行锁、表锁的使用场景？

表级锁更适合于以 查询为主，只有少量按索引条件更新数据的应用 ，如Web 应用；

而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

数据库的乐观锁和悲观锁是什么？怎么实现的？

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制。
• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐观锁一般会使用 版本号机制或CAS算法实现。

乐观锁、悲观锁使用场景？

• 乐观锁适用于 写比较少 的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
• 但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

什么是死锁？怎么解决？

死锁是指 两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环 的现象。常见的解决死锁的方法：

1、尽量约定以相同的顺序访问表；

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度 ，通过表级锁定来减少死锁产生的概率；

4、如果业务处理不好 可以用分布式事务锁或者使用乐观锁 。

锁优化方面的意见？

• 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少索引条件，及索引范围，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可使用低级别事务隔离（但是需要业务层面满足需求，隔离级别越高，对性能影响越大）

说说MyISAM 表锁？

MyISAM 存储引擎只支持表锁

• MyISAM 在执行查询语句（SELECT）前，会 自动给涉及的所有表加读锁
• 执行更新操作（UPDATE、DELETE、INSERT 等）前，会 自动给涉及的表加写锁
• 这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。（能自动加读锁或者写锁）

显示加表锁语法（lock table table_name read(or write)）：

加读锁 ： lock table table_name read;

加写锁 ： lock table table_name write；
1
2
3

1） 读锁不会会阻塞其它事务的读请求，但会阻塞对同一表的写请求；

2） 写锁会阻塞其他用户对同一表的读和写操作；

MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

说说InnoDB 行锁？

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是 采用了行级锁。

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

• 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是 只能读不能修改 。
• 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

可以通过以下语句显示给记录集加共享锁或排他锁 。

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE
1
2
3

如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。

Next-Key 锁？Next-Key Lock 如何做到防止幻读？

InnoDB除了支持行锁(Record Lock，对索引记录加锁)外还支持间隙锁(Gap Lock)、next-key lock

• 间隙锁：当我们用范围条件，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁； 对于键值在条件范围内但并不存在的记录，叫做 “间隙（GAP）” ， InnoDB也会对这个 “间隙” 加锁，这种锁机制就是 next-key lock 。
  • next-key锁： 行锁和间隙锁组合起来。

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：

• 防止间隙内有新数据被插入
• 防止已存在的数据，更新成间隙内的数据

InnoDB使用Next-Key Lock条件？

innodb自动使用间隙锁的条件：

• 必须在 Repeatable Read 级别下
• 检索条件必须有普通索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

这里的普通索引 不包括主键索引和唯一索引 ，如果在这两个索引下因为能精确检索出结果，所以会使用Record Lock直接锁定具体的行（范围查询除外）。

总结：在可重复读隔离级别下并不能避免幻读，如果要避免的话需要使用Next-Key Lock。但是有了Next-Key Lock以后，会导致并发插入的时候产生等待，所以这时候需要进行相关的优化。

如何查看锁情况？

InnoDB 行锁争用情况：show status like 'innodb_row_lock%';

Innodb_row_lock_waits: 系统启动后到现在总共等待的次数（重点关注）。当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

说说 Mysql 并发参数有哪些？

• max_connections，控制允许连接到MySQL数据库的最大连接数，默认值是 151。
• back_log，控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。 5.6.6 版本之前默认值为 50 ， 之后的版本默认为 50 + （max_connections / 5）， 但最大不超过900。 如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back_log 的值。
• table_open_cache，该参数用来控制所有SQL语句执行线程 可打开表缓存的数量 （ 针对的是所有线程 ）
• thread_cache_size，为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用 ，默认是9。
• innodb_lock_wait_timeout，设置InnoDB 事务等待行锁的时间，默认值是50ms。

5 MySQL日志

说说 Mysql 日志有哪些？

• 错误日志：它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。
• 二进制日志（BINLOG）：记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是 不包括数据查询语句 。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。
• 查询日志：记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。需自己开启。
• 慢查询日志：记录了所有执行时间 超过 参数 long_query_time 设置值并且扫描记录数**不小于** min_examined_row_limit 的所有的SQL语句的日志。long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒。min_examined_row_limit参数表示SQL执行过程中扫描的行数阈值，如果扫描的行数小于该参数设置的值，即使SQL执行耗时大于慢查询阈值，也不会记录到慢查询日志中，该参数默认值为0。
• 事务日志：redo log、undo log

什么是MySQL的 binlog？

MySQL的 binlog 是记录所有DDL和DML语句， 不会记录 SELECT 和 SHOW 这类操作。

MySQL binlog 以事件形式记录，还包含语句所执行的消耗的时间 ，MySQL 的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

binlog 有三种格式，各有优缺点：

• STATEMENT：该日志格式在日志文件中 记录的都是SQL语句 ，每一条对数据进行修改的SQL都会记录在日志文件中。
• row ： 基于行的模式，记录的是行的变化 ，很安全。但是 binlog 会比其他两种模式大很多，在一些大表中清除大量数据时在 binlog 中会生成很多条语句，可能导致从库延迟变大。
• MIXED ：这是目前MySQL 默认 的日志格式，即混合了STATEMENT 和 ROW两种格式。 默认情况下采用STATEMENT，但是在一些特殊情况下采用ROW来进行记录。

如何开启 binlog 日志？

需要手动开启，配置文件位置 : /usr/my.cnf

#配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin

#配置二进制日志的格式
binlog_format=STATEMENT
1
2
3
4
5

查看是否开启： show variables like '%log_bin%'

什么是事务日志？

6 MySQL主从复制

什么是主从复制？

复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL主从同步的目的？为什么要做主从同步？

• 实时灾备，用于故障切换

• 读写分离，提供查询服务

• 备份，避免影响业务

主从部署必要条件：

• 主库开启binlog日志（设置log-bin参数）

• 主从server-id不同

• 从库服务器能连通主库

MySQL主从复制流程和原理？（重要）

从上层来看，复制分成三步：

• slave 服务器执行 start slave，开启主从复制开关，slave 服务器的 I/O Thread 请求从 master 服务器读取 binlog（如果该线程追赶上了主库，会进入睡眠状态）
• master 服务器创建 Log Dump Thread，把 binlog 发送给 slave 服务器。slave 服务器的 I/O Thread 将读取到的 binlog 日志内容写入中继日志 relay log（中继日志，mysql-relay-bin.xxxxxx，会记录位置信息的，以便下次继续读取）
• slave 服务器的 SQL Thread 会实时监测 relay log 新增的日志内容，把 relay log解析成 SQL 语句，并执行

MySQL主从复制模式？

MySQL的主从复制模式包括异步复制，全同步复制，半同步复制。MySQL默认为异步模式。

• 异步复制：MySQL的默认复制，主库只会通知一下Dump线程发送这些新的Binlog，然后主库就会继续处理提交操作， 不保证这些Binlog传到任何一个从库节点上 。
  • 这样带来的问题就是当主死掉了，此时主上提交的事务可能还没有传到从上。而强行将从提升为主就会导致新主上的数据不完整。
• 全同步复制：当主库提交事务之后，所有的从库节点必须收到，APPLY并且提交这些事务 ，然后主库线程才能继续做后续操作。
  • 这里面有一个很明显的缺点就是，主库完成一个事务的时间被拉长，性能降低。
• 半同步复制：介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收并写到relay log中才返回给客户端。 - 相对于异步复制，半同步复制提高了数据的安全性，同时也会造成一定程度的延迟，这个延迟为一个TCP/IP往返的时间。所以半同步复制需要在低延时的网络中使用。

MySQL怎么解决主从同步延时问题、数据可能丢失问题？

mysql主从复制存在的问题：

• 主库宕机后，数据可能丢失
• 从库只有一个sql Thread，主库写压力大，复制很可能延时

解决方法：

• 半同步复制 ==》解决数据丢失的问题

• 并行复制 ==》解决从库复制延迟的问题，mysql5.7之后。

并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

主从复制搭建步骤

• 在master 的配置文件（/usr/my.cnf）中，配置如下内容：服务id、binlog存储路径和文件名、是否只读、需要忽略的数据库

  #mysql 服务ID,保证整个集群环境中唯一
  server-id=1
  
  #mysql binlog 日志的存储路径和文件名
  log-bin=/var/lib/mysql/mysqlbin
  
  #是否只读,1 代表只读, 0 代表读写
  read-only=0
  
  #忽略的数据, 指不需要同步的数据库
  binlog-ignore-db=mysql
  
  #指定同步的数据库
  #binlog-do-db=db01
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

• 在 slave 端配置文件中，配置如下内容：

  #mysql服务端ID,唯一
  server-id=2
  
  #指定binlog日志
  log-bin=/var/lib/mysql/mysqlbin
  1
  2
  3
  4
  5

• 重启后，slave执行如下命令，指定当前从库对应的主库的IP地址，用户名，密码，从哪个日志文件开始的那个位置开始同步推送日志。

  change master to master_host= '139.224.80.141', master_user='lish', master_password='lish', master_log_file='mysqlbin.000003', master_log_pos=154;
  1

主从复制下生成binlog时主库崩溃恢复的解决方案（两阶段提交-redolog-binlog）？

参考 7.4

主从复制下从库崩溃造成主从不一致的解决方法？TODO

7 MySQL两阶段提交

7.1 什么是两阶段提交？

在讲解两阶段提交之前，需要对MySQL中的三种日志即binlog、redo log与undo log有一定的了解。

• Undo log 记录的是数据操作前的样子
• redo log 记录的是数据被操作后的样子（redo log 是 Innodb 存储引擎特有）
• bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）
  
  可以看到，InnoDB在写redo log时，并不是一次性写完的，而有两个阶段，Prepare与Commit阶段，这就是两阶段提交的含义。

7.2 为什么需要两阶段提交？

先说结论：在于崩溃恢复。

MySQL为了提升性能，引入了BufferPool缓冲池。查询数据时，先从BufferPool中查询，查询不到则从磁盘加载在BufferPool。

每次对数据的更新，也不总是实时刷新到磁盘，而是先同步到BufferPool中，涉及到的数据页就会变成脏页。同时会启动后台线程，异步地将脏页刷新到磁盘中，来完成BufferPool与磁盘的数据同步。如果在某个时间，MySQL突然崩溃，则内存中的BufferPool就会丢失，剩余未同步的数据就会直接消失。

虽然在更新BufferPool后，也写入了binlog中，但binlog并不具备crash-safe的能力。因为崩溃可能发生在写binlog后，刷脏前。在主从同步的情况下，从节点会拿到多出来的一条binlog。

所以server层的binlog是不支持崩溃恢复的，只是支持误删数据恢复。InnoDB考虑到这一点，自己实现了redo log。

7.3 为什么要写两次redo log，写一次不行吗？

如果只写一次redo log会有什么样的问题呢？

先写binlog，再写redo log

• 当前事务提交后，写入binlog成功，之后主节点崩溃。在主节点重启后，由于没有写入redo log，因此不会恢复该条数据。
• 而从节点依据binlog在本地回放后，会相对于主节点多出来一条数据，从而产生主从不一致。

先写redo log，再写binlog

• 当前事务提交后，写入redo log成功，之后主节点崩溃。在主节点重启后，主节点利用redo log进行恢复，就会相对于从节点多出来一条数据，造成主从数据不一致。
• 因此，只写一次redo log与binlog，无法保证这两种日志在事务提交后的一致性。也就是无法保证主节点崩溃恢复与从节点本地回放数据的一致性。

7.4 在两阶段提交的情况下，是怎么实现崩溃恢复的呢？

首先比较重要的一点是，在写入redo log时，会顺便记录XID，即当前事务id。在写入binlog时，也会写入XID。

如果在写入redo log之前崩溃，那么此时redo log与binlog中都没有，是一致的情况，崩溃也无所谓。

如果在写入redo log prepare阶段后立马崩溃，之后会在崩恢复时，由于redo log没有被标记为commit。于是拿着redo log中的XID去binlog中查找，此时肯定是找不到的，那么执行回滚操作。

如果在写入binlog后立马崩溃，在恢复时，由redo log中的XID可以找到对应的binlog，这个时候直接提交即可。

总的来说，在崩溃恢复后，只要redo log不是处于commit阶段，那么就拿着redo log中的XID去binlog中寻找，找得到就提交，否则就回滚。在这样的机制下，两阶段提交能在崩溃恢复时，能够对提交中断的事务进行补偿，来确保redo log与binlog的数据一致性。

8 分库分表

为什么要分库分表？

• 分表：分表就是把一个表的数据放到多个表中，然后查询的时候你就查一个表。比如按照用户 id 来分表，将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内，比如每个表就固定在 200 万以内。，单表数据量太大，会极大影响你的 sql执行的性能，到了后面你的 sql 可能就跑的很慢了。一般来说，就以我的经验来看，单表到几百万的时候，性能就会相对差一些了，你就得分表了。
• 分库：分库就是你一个库一般我们经验而言，最多支撑到并发 2000，一定要扩容了，而且一个健康的单库并发值你最好保持在每秒 1000 左右，不要太大。那么你可以将一个库的数据拆分到多个库中，访问的时候就访问一个库好了。

如何对数据库如何进行垂直拆分或水平拆分的？

• 水平拆分的意思，就是把一个表的数据给弄到多个库的多个表里去，但是每个库的表结构都一样，只不过每个库表放的数据是不同的，所有库表的数据加起来就是全部数据。水平拆分的意义，就是将数据均匀放更多的库里，然后用多个库来抗更高的并发，还有就是用多个库的存储容量来进行扩容。
• 垂直拆分的意思，就是把 一个有很多字段的表给拆分成多个表，或者是多个库上去 。一般来说， 会将较少的访问频率很高的字段放到一个表里去，然后将较多的访问频率很低的字段放到另外一个表里去 。因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。这个一般在表层面做的较多一些。

两种分库分表的方式：

• 一种是按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了。
• 或者是按照某个字段hash一下均匀分散，这个较为常用。

range 来分，好处在于说，扩容的时候很简单，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了；缺点，但是大部分的请求，都是访问最新的数据。实际生产用 range，要看场景。

hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表

9 优化相关

如何定位及优化SQL语句的性能问题？如何定位并优化慢查询SQL？（重要）

一般有3个思考方向：

• 根据慢日志定位慢查询sql
• 使用explain等工具分析sql执行计划
• 修改sql或者尽量让sql走索引

具体说说：

• 1、查看SQL执行频率

  show status like 'Com_______';
  show status like 'Innodb_rows_%';
  1
  2

• 2、定位低效率执行SQL

  • 慢查询日志
  • show processlist : 慢查询日志在查询结束以后才纪录，show processlist可以 查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况。

• 3、explain分析执行计划

  explain  select * from tb_item where id = 1;
  1

  

• 4、show profiles 指令， 来查看SQL语句执行的耗时 。通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间。
• 5、trace分析优化器执行计划。MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。

针对SQL语句可以进行哪些优化？重要

• 插入大批量数据
  • 主键顺序插入 ，因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。
  • 关闭唯一性校验。
  • 手动提交事务，如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
• 优化insert语句
  • 能合并就合并，多个insert合并为一个。
  • 在事务中进行数据插入。
  • 数据有序插入
• 优化Order by (优化排序) ，见下面。
• 优化group by 语句
  • 与ORDER BY 相比，GROUP BY 只是多了排序之后的分组操作。
  • 如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序
• 优化嵌套查询（子查询）
  • 子查询是可以被更高效的多表连接查询（JOIN）替代。
• 优化OR条件（防止索引失效）
  • OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引（只能使用单列索引）； 如果没有索引，则应该考虑增加索引。
• 优化分页查询
• 使用SQL提示，SQL语句中加入一些人为的提示来达到优化操作的目的。
  • USE INDEX、IGNORE INDEX
  • FORCE INDEX，强制使用某个索引

Order by 排序优化详解

• 排序方式
  • 第一种是 filesort 排序（对返回数据进行排序）， 不是通过索引直接返回排序结果的排序都叫 FileSort 排序 。
  • 第二种 覆盖索引，这种情况即为 using index，不需要额外排序，操作效率高。
• 优化目标
  • 尽量减少额外的排序，通过索引直接返回有序数据。
  • 不出现FileSort的条件比较苛刻，必须满足下面三个条件，否则肯定需要额外的操作：
    • where 条件和Order by 使用相同的索引；
    • Order By 的顺序和索引顺序相同；
    • Order by 的字段都是升序，或者都是降序。
• Filesort 的优化
  • 两次扫描算法 ：MySQL4.1 之前，使用该方式排序，该操作可能会导致大量随机I/O操作。
    • 首先根据条件取出 排序字段 和行指针信息；
    • 然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。
    • 完成排序之后，再根据行指针回表读取记录。
  • 一次扫描算法：排序时内存开销较大，但是排序效率比两次扫描算法要高。
    • 一次性取出满足条件的所有字段；
    • 然后在排序区 sort buffer 中排序后直接输出结果集。
  • MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。
  • 可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。

一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

• 优化思路
  • 优化思路一：在索引上完成分页的排序操作，然后根据主键关联回原表查询所需要的其他列内容。
  • 优化思路二：该方案适用于 主键自增（并且主键不能出现断层） 的表，可以把Limit 查询转换成某个位置的查询 。

mysql可以进行哪些应用优化？

• 使用数据库连接池，频繁的创建关闭连接，是比较耗费资源的，我们有必要建立 数据库连接池，以提高访问的性能。
• 减少对MySQL的访问
  • 避免对数据进行重复检索，能够一次连接就获取到结果的，就不用两次连接，这样可以大大减少对数据库无用的重复请求。
  • 增加cache层，多种实现方式。可以将部分数据从数据库中抽取出来放到应用端以文本方式存储， 或者使用框架(Mybatis, Hibernate)提供的一级缓存/二级缓存，或者使用redis数据库来缓存数据 。
• 负载均衡
  • 利用MySQL复制分流查询，通过MySQL的主从复制，实现读写分离，使增删改操作走主节点（master），查询操作走从节点（slaver），从而可以降低单台服务器的读写压力。

什么是查询缓存？

• 开启Mysql的查询缓存，当执 行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。
• 默认占用1M，需要再在 /usr/my.cnf 配置中配置
• 可以查询缓存命中数 Qcache_hits等

查询缓存失效的情况？

• SQL 语句不一致的情况：要想命中查询缓存，查询的SQL语句必须一致。
• 当查询语句中有一些不确定的时，则不会缓存。如 ： now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。
• 查询语句不使用任何表。
• 查询系统数据库中的表（ mysql， information_schema 或 performance_schema ）时，不会走查询缓存。
• 在存储的函数，触发器或事件的主体内执行的查询。
• 如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。

内存优化原则

1） 将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序预留足够内存。

2） MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。（需要预留更多的内存给操作系统）

3） 排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽。（不能设置过大，要根据最大连接数合理分配）

MyISAM 内存优化

• MyISAM 存储引擎使用 key_buffer 缓存索引块，加速MyISAM 索引的读写速度。对于MyISAM 表的数据块，mysql没有特别的缓存机制，完全依赖于操作系统的IO缓存。
• key_buffer_size决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率。建议至少将1/4可用内存分配给key_buffer_size。
• read_rnd_buffer_size是MySQL读入缓冲区大小，对于需要做排序的myisam表的查询，如带有order by子句的sql，适当增加 read_rnd_buffer_size 的值，可以改善此类的sql性能。但需要注意的是 read_rnd_buffer_size 是每个session独占的，如果默认值设置太大，就会造成内存浪费。（如果并发连接数过高就会造成内存浪费）

InnoDB 内存优化

• innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块。
• innodb_buffer_pool_size，该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。innodb_buffer_pool_size 的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高。从而减少对磁盘I/O操作。

10 基础知识

SQL执行顺序

编写顺序

SELECT DISTINCT
	<select list>
FROM
	<left_table> <join_type>
JOIN
	<right_table> ON <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_params>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

注意:

• 执行顺序：FROM - WHERE(行筛选) - GROUP BY(对筛选后结果分组) - HAVING(对分组后的结果筛选) - SELECT - ORDER BY；
• HAVING在是GROUP BY基础之上,没有GROUP BY就没有HAVING；
• WHERE是对行的筛选，里面可以用列，单行函数等，但不能直接使用组函数；
• HAVING是对组的筛选，里面可以使用分组的列或者组函数筛选，直接使用单行函数，没有分组的列等不可以。它是一个条件查询，一般是跟着分组以后，比如select title， count(title) as t from titles group by title having t>=2;

什么是内联接、左外联接、右外联接？

• 内联接（Inner Join）：匹配2张表中相关联的记录 。
• 左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示 。
• 右外联接（Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示。在判定左表和右表时，要根据表名出现在Outer Join的左右位置关系。

如何进行多表查询

1. 使用SELECT子句进行查询（表可以起别名）；
2. 通过内连接进行查询；
3. 通过嵌套进行查询；
4. 通过多表联合进行查询。

使用SELECT子句进行查询（表可以起别名）

SELECT 字段名 FROM 表1，表2 … WHERE 表1.字段 = 表2.字段 AND 其它查询条件
# 示例
SELECT a.id,a.name,a.address,a.date,b.math,b.english,b.chinese 
FROM table1 AS b,table2 AS a 
WHERE a.id=b.id
1
2
3
4
5

注：在上面的的代码中，以两张表的id字段信息相同作为条件建立两表关联，但在实际开发中不应该这样使用，最好用主外键约束来实现。

简单嵌套查询（子查询、内连接）

子查询：子查询是一个SELECT查询，返回单个值且嵌套在SELECT、INSERT、UPDATE和DELETE语句或其它查询语句中，任何可以使用表达式的地方都可以使用子查询。

SELECT id,name,sex,date FROM tb_demo068 WHERE id in(SELECT id FROM tb_demo068 WHERE id='$_POST[test]')
1

内连接：把查询结果作为WHERE子句的查询条件即称为内连接。

SELECT filedlist FROM table1 [INNER] JOIN table2 ON table1.column1 = table2.column1
1

其中，filedlist是要显示的字段,INNER表示表之间的连接方式为内连接，table1.column1=table2.column1用于指明两表间的连接条件，如：

SELECT a.name,a.address,a.date,b.chinese,b.math,b.english 
FROM table1 AS a INNER JOIN table2 AS b on a.id=b.id
1
2

复杂的嵌套查询

多表之间的嵌套查询可以通过谓词IN实现，语法格式如下：

test_expression[NOT] IN{
	subquery
}
1
2
3

参数说明：test_expression指SQL表达式，subquery包含某结果集的子查询

多表嵌套查询的原理：无论是多少张表进行嵌套，表与表之间一定存在某种关联，通过WHERE子句建立此种关联实现查询。

多表联合查询

利用SQL语句中的UNION，可以将不同表中符合条件的数据信息显示在同一列中。例：

SELECT * FROM table1 UNION SELECT * FROM table2
1

使用UNION时应注意以下两点：

• 使用UNION运算符组合的语句中，所有选择列表的表达式数目必须相同，如列名、算术表达式及聚合函数等；
• 在每个查询表中，对应列的数据结构必须一样。

复杂内连接查询

复杂的内连接查询是在基本的内连接查询的基础上再附加一些查询条件，如：

SELECT a.name,a.address,a.date,b.chinese,b.math,b.english 
FROM table1 AS a INNER JOIN table2 AS b on a.id=b.id 
WHERE b.id=(SELECT id FROM table2 WHERE table2.name='$_POST[text]')
1
2
3

总之，实现表与表之间的关联的本质是两表之间存在共同的数据项或者相同的数据项，通过WHERE子句或内连接INNER JOIN … ON语句将两表连接起来，实现查询。

什么是外键

参考：https://m.php.cn/article/488927.html

数据库的三范式是什么?

• 第一范式：列不可再分
• 第二范式：行可以唯一区分，主键约束
• 第三范式：表的非主属性不能依赖与其他表的非主属性，外键约束

且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

mysql语句分类

DDL（Data Definition Languages）语句：即数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等，常用的语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。增删改表的结构

DML（Data Manipulation Language）语句：即数据操纵语句，用来查询、添加、更新、删除等，常用的语句关键字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增删改查。增删改表的数据

DCL（Data Control Language）语句：即数据控制语句，用于授权/撤销数据库及其字段的权限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。常用的语句关键字有：GRANT,REVOKE。

TCL（Transaction Control Language）语句：事务控制语句，用于控制事务，常用的语句关键字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。

drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别：

• delete和truncate只删除表的数据不删除表的结构
• 速度一般来说: drop> truncate >delete
• delete语句是DML,这个操作会放到rollback segement中，事务提交之后才生效，如果有相应的trigger，执行的时候将被触发。truncate,drop是DDL，操作立即生效，原数据不放到rollback segment中，不能回滚，操作不触发trigger。

MySQL分页查询方式

limit使用说明

SELECT * FROM table LIMIT 5,10;//检索记录行 6-15
SELECT * FROM table LIMIT 95,-1; //检索记录行 96-last
SELECT * FROM table LIMIT 5;//检索前 5 个记录行
1
2
3

分页写法

• 方式一：select * from table order by id limit m, n

  • 该语句的意思就是查询m+n条记录，去掉前m条，返回后n条。但m越大，查询性能就越低，因为MySQL需要扫描全部m+n条记录。

• 方式二：select * from table where id > #max_id# order by id limit n

  • 该查询同样会返回后n条记录，却无需像方式1扫描前m条记录，但必须在每次查询时拿到上一次查询（上一页）的最大id（或最小id），是比较常用的方式。
  • 当然该查询的问题也在于我们不一定能拿到这个id，比如当前在第3页，需要查询第5页的数据，就不行了。

• 语法格式： SELECT TOP n FROM [查询条件]，n为要返回结果集中的记录条数。

SQL练习

https://blog.csdn.net/weixin_38192427/article/details/108587776

11 补充知识

什么是视图？有哪些优点？

什么是视图

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲， 视图就是一条SELECT语句执行后返回的结果集。 所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

优点

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，原表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

什么事存储过程？什么事函数？

存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

​ 存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

​ 函数 ： 是一个有返回值的过程 ；

​ 过程 ： 是一个没有返回值的函数 ；

什么是触发器？

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

使用别名 OLD 和 NEW （OLD和NEW也称为行记录变量）来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。