Java 面试问题总结（详细） —— MySql 模块（MySQL高级）（建议收藏）

文章前言：

      此篇文章为 MySql 模块复习，亦可以当做 MySQL 高级的学习，下面列出了一些常见的面试问题，不同于简单的增删查改，面试常问的更偏向于 MySQL 的底层，所以需要具体的去了解一下其如何实现，这样比直接去背八股文好记一些，我在下面的目录没有分的很细，知识点都列在【】里了，按需查看，里面夹杂着一些面试问题，我这篇文章也是围绕面试问题展开的，所以本篇文章了解了，面试问题应该问题不大，以后看到其他面试问题也会补充进来，如果有哪里讲解不清，随时下方留言。

声明：本文参考 B 站 宋红康老师的 MySql 教程，哪里讲解不清，也可移步视频 mysql教程

---

目录：

• 1、设计数据库要注意什么？
• 2、数据库索引底层实现【B+树，索引推演，聚簇索引，非聚簇索引，联合索引】
• 3、char，nchar，varchar，nvarchar的区别
• 4、索引的类型，普通索引和主键索引查询起来有什么不同
• 5、索引什么时候该使⽤，需要注意什么【哪些条件适合创建索引，哪些条件不适合创建索引】
• 6、索引失效【索引失效的 11 种情况】
• 7、innoDB 和 myisam 的区别【MySQL存储引擎】
• 8、数据库优化【慢查询日志、性能分析工具 explain、sql 语句优化、数据结构调优、索引覆盖、索引下推、主键的设置、临时表】
• 9、sql 执行流程
• 10、innodb事务的四大特性是什么【innoDB 存储引擎的四大特性、四大隔离级别】
• 11、MySQL 日志文件【redo 日志、undo日志】
• 12、锁【读 \ 写锁、意向锁、自增锁、MDL 锁、间隙锁、记录锁、临键锁、插入意向锁、乐观锁、悲观锁、页锁、死锁、全局锁】
• 13、多版本并发问题【MVCC、超卖问题】
• 14、主从复制
• 15、其他日志文件【binlog 日志、中继日志】
• 16、数据迁移【逻辑迁移、物理迁移】

---

1、设计数据库要注意什么？

答：要符合三范式和表约束

• 总： 数据库三范式是数据库设计所需要遵循的规范
• 第一范式：（1NF）每一列都是不可再分的属性值，保证每一列的原子性，如果两列的属性相近或者相似，尽量合并为属性一样的列，避免冗余，如下图的 图 1 就不满足要求

• 第二范式：（2NF）第二范式是在第一范式的基础上更进一层，第二范式是一定满足第一范式的，第二范式规定数据表中的每一列都需要与主键关联，不能与主键的一部分关联，也就是一张表中只能存一种数据，不能把多种数据存储在一张表中，看下面这样一张表：

• 这样一张订单信息表，一个订单可能对应多种商品，所以主键为订单编号和商品编号组成的联合主键，这样就导致商品的信息和订单编号没有关联，这就是数据库中的列和主键一部分关联，就不满足第二范式，所以需要拆成单独的表

• 这样就很大程度减少了数据的冗余，如果想要获取到订单的商品信息，直接通过商品编号在商品信息表中查询即可

• 第三范式：（3NF）数据表中的每一列都与主键直接关联，不能间接关联，比如下面设计的订单数据表，需要客户编号作为外键和订单表建立联系，而不可以在订单表中添加客户表中的其他字段。如果添加其他段后，这些字段是通过外键和主键进行关联的，这样就不满足第三范式的要求了

• 这样在查询订单信息的时候，就可以通过客户编号来查询到客户表中的信息，避免了在订单表中输入客户的信息，避免数据的冗余

• 注意：

• 1、三范式的关系，先满足第一范式，再满足第二范式，满足第二范式，再满足第三范式
• 2、二三范式的区别在于：有没有分成两张表，第二范式是多种实体数据存放在一张表中，那么必须分成多张表，第三范式已经按照要求分成多张表，一张表只能有另一个表的 ID 不能有其它字段

• 范式的优缺点：

1、优点：减少了数据冗余
2、缺点：降低了查询效率，关联多张表，可能会造成一些锁策略的失效，实际在设计表时会增加少量的冗余来提高数据库的读性能，实现空间换取时间的目的

• 反范式化：
  • 为满足某种商业目标 , 数据库性能比规范化数据库更重要，在数据库规范化的同时也要综合考虑数据库的性能，通过给定表中添加额外的字段，以大量减少需要从中搜索信息的时间，看下面的例子：
    是否添加冗余字段导致的程序性能提升：

#07-数据表的设计规范

#反范式化的举例：

CREATE DATABASE atguigudb3;

USE atguigudb3;

#学生表
CREATE TABLE student(
stu_id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(25),
create_time DATETIME
);

#课程评论表
CREATE TABLE class_comment(
comment_id INT PRIMARY KEY AUTO_INCREMENT,
class_id INT,
comment_text VARCHAR(35),
comment_time DATETIME,
stu_id INT
);

###创建向学生表中添加数据的存储过程
DELIMITER //

CREATE PROCEDURE batch_insert_student(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2017-01-01 00:00:00');
DECLARE date_temp DATETIME;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = DATE_ADD(date_temp, INTERVAL RAND()*60 SECOND);
INSERT INTO student(stu_id, stu_name, create_time)
VALUES((START+i), CONCAT('stu_',i), date_temp);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //

DELIMITER ;

#调用存储过程，学生id从10001开始，添加1000000数据
CALL batch_insert_student(10000,1000000);

####创建向课程评论表中添加数据的存储过程
DELIMITER //

CREATE PROCEDURE batch_insert_class_comments(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2018-01-01 00:00:00');
DECLARE date_temp DATETIME;
DECLARE comment_text VARCHAR(25);
DECLARE stu_id INT;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = DATE_ADD(date_temp, INTERVAL RAND()*60 SECOND);
SET comment_text = SUBSTR(MD5(RAND()),1, 20);
SET stu_id = FLOOR(RAND()*1000000);
INSERT INTO class_comment(comment_id, class_id, comment_text, comment_time, stu_id)
VALUES((START+i), 10001, comment_text, date_temp, stu_id);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //

DELIMITER ;

#添加数据的存储过程的调用，一共1000000条记录
CALL batch_insert_class_comments(10000,1000000);

#########
SELECT COUNT(*) FROM student;

SELECT COUNT(*) FROM class_comment;

###需求######
SELECT p.comment_text, p.comment_time, stu.stu_name 
FROM class_comment AS p LEFT JOIN student AS stu 
ON p.stu_id = stu.stu_id 
WHERE p.class_id = 10001 
ORDER BY p.comment_id DESC 
LIMIT 10000;


#####进行反范式化的设计######
#表的复制
CREATE TABLE class_comment1
AS
SELECT * FROM class_comment;

#添加主键，保证class_comment1 与class_comment的结构相同
ALTER TABLE class_comment1
ADD PRIMARY KEY (comment_id);

SHOW INDEX FROM class_comment1;

#向课程评论表中增加stu_name字段
ALTER TABLE class_comment1
ADD stu_name VARCHAR(25);

#给新添加的字段赋值
UPDATE class_comment1 c
SET stu_name = (
SELECT stu_name
FROM student s
WHERE c.stu_id = s.stu_id
);

#查询同样的需求
SELECT comment_text, comment_time, stu_name 
FROM class_comment1 
WHERE class_id = 10001 
ORDER BY comment_id DESC 
LIMIT 10000;

• 两个查询语句对比性能：同为百万级别的数据表

• 当前只查询了 10w 条数据，查询速度得到小幅度提升，随着数据量的增大，速度的增幅也会变大

• 反范式化的问题：

• 1、存储空间变大了
• 2、一个表中的数据发生改变，另一个表中的冗余数据需要同步，否则会导致数据不一致
• 3、存储过程如果支持数据的更新和删除等额外操作，如果更新频繁，会消耗系统资源
• 4、在数据量小的情况下，反范式化优点不明显，反而可能会使数据表更复杂

• 反范式化的使用场景：

• 当冗余信息有价值或者能大幅提高查询效率的时候，就会采取反范式化的方式。

• 对于数据库中的表约束有四种：

• 1、主键约束 （primary key）
• 2、非空约束（ not null）值不能为空
• 3、唯一约束（unique）值不能重复
• 4、外键约束（foreign key）

• ①、主键约束：表中的唯一标识，被设置为主键的列不能为空，且唯一，表中只能有一个字段被设置为主键
• ②、外键约束：主表约束从表，从表中关联字段为外键，被主表中的主键约束，外键不能出现主键中不存在的值。这样做的目的是为了防止数据冗余和后期增删改的问题

---

2、数据库索引底层实现

       索引是存储引擎为快速找到数据记录的一种数据结构，好比书的目录，通过目录就可以快速找到你想要的内容，在进行数据查找时，先看查询条件是否命中索引，符合则通过索引查找，不符合则通过全表扫描进行查找（一条一条数据的查）

当数据是随机存储的时候，不确定需要查找的数据的位置，可能会需要很多次磁盘的 IO，如下图：

如果采取顺序存储，类似顺序表，假如我查找为 5 的数据依次查找 5 次即可

那么再进一步，使用二叉搜索树的方式进行存储，发现同样查找 5 只需要三次：从根结点出发，根据所需查找的数值向左或者向右进行查找

所以可以发现，通过降低存储的高度就可以减少查找的次数，以此来提高查找的性能，那么索引也是同样的目的，创建索引的主要原因就是为了降低磁盘的 IO 次数提高查询效率

• 优点：
  ①、首先最主要的就是提高数据库检索数据的速度，减少磁盘的 IO 次数，提高性能。
  ②、通过创建唯一索引可以保证数据库中每一行数据的唯一性（这里指的就是唯一约束，自动添加唯一索引）
  ③、可以加速表与表之间的连接，对于存在依赖关系的父子表可以提高查询速度（相当于主键外键，通过索引可以快速查找到数据）
  ④、在使用排序和分组进行数据查询的时候，可以显著减少查询中分组和排序的时间，因为索引本质就是 “排好序的数据结构”，因为排好序了相同的被分在临近位置，可以以此来进行分组
• 缺点：
  ①、创建索引和维护索引需要花费时间，并且随着数据量的增长，所花费的时间也会增长
  ②、索引会占用存储空间，每个索引要占用一定的物理空间，存储在硬盘上
  ③、虽然索引大幅度的提升了查询的速度，但也会降低表的更新速度，当对表中的数据进行增删改的时候，索引要动态的维护，这就降低了维护的速度

• 首先先建立一张表：使用 Compact 行格式来实际存储记录的

mysql> CREATE TABLE index_demo(
		c1 INT,
		c2 INT,
		c3 CHAR(1),
		PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

• index_demo 行格式的示意图：

• record_type：记录表头的一项属性信息，2表示最小记录，3表示最大记录，0表示普通记录
• next_record：表示下一条记录的的地址，数据在磁盘上不是顺序存储的，因为大量的数据如果使用顺序存储就需要一段很大的顺序存储空间，这很难做到，所以数据之间采用链表的方式存储，物理上是不连续的，逻辑上是连续的，这里的 next_record 就好比链表中的 next 属性，指向下一个记录的地址
• 各个列的值 ：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3
• 除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息
  我们先省去最后其他信息，最后记录格式为：

我们在数据库中存储这些记录就是通过数据页的方式来存储，一个数据页大小为 16KB，示意图：这里的页 10 就是随机分配的一个数据页

• 上面的这个数据页创建好之后，如果要查询一条数据还是需要采用顺序查找的方式，因为各个页中的记录并没有发现规律，所以还是需要一条一条记录去查找，那么想要快速查找定位到需要查找的记录，应该怎么办，需要满足下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
• 给所有的页创建一个目录项：
  
  如上图，如果查找 20 这条记录，通过二分法可以快速确定此记录应在目录3中，进入目录三就因为数据是有序的，所以依据可以通过二分法快速找到需要查找的记录，这样就可以发现数据量一下减少了 3 / 4 ，一定程度上提高了查询的性能
  再将上面各个数据页提取出来的目录项再组成一个数据页：

当一个数据页数量增多，由目录项组成的数据页也要随之增多：

当由目录项组成的数据页存在多个的时候，如果需要查找某条数据就需要先二分法去找每一个数据页去找到其主键的存在的范围，再去下层的数据页进行查找，这也会一定程度降低程序性能，所以当由目录项组成的数据页存在多个的时候，就需要再其上层继续创建目录项：

这样通过主键范围就可以快速定位到其记录的位置，相对上面的更快，它省去了一部分无用的范围
对于上面的数据结构，我们称之为：B+树

一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第 0 层，之后依次往上加。之前我们做了一个非常极端的假设：存放用户记录的页 最多存放3条记录 ，存放目录项记录的页 最多存放4条记录 。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放 100条用户记录 ，所有存放目录项记录的内节点代表的数据页可以存放 1000条目录项记录 ，那么：

• 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。
• 如果B+树有2层，最多能存放 1000×100=10,0000 条记录。
• 如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。
• 如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。相当多的记录！！！
• 所以一般我们使用的 B+ 树不会超过四层

索引按照物理实现方式，索引可以分为两种，聚簇索引和非聚簇索引。非聚簇索引也被称为二级索引或辅助索引

• 特点：

• 1、使用记录的主键值的大小进行记录和页的排序：
  • 页内的记录是按照主键值的大小排序成一个单向链表
  • 各个存放用户记录的页，也是根据用户的主键大小顺序排成一个双向链表
  • 存放目录项的页分为不同的层次，位于统一层次的页也是根据目录项中主键的值进行大小排成一个双向链表
• 2、B+ 树的叶子结点存储的是完整的用户信息，所谓的完整信息就是这个记录中的所有列的值（包括隐藏列）

• 优点：

• 1、数据库访问更快，因为聚簇索引将索引和数据都存放在同一个 B+ 树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 2、聚簇索引对于主键的排序查找和范围查找非常快
• 3、按照聚簇索引的顺序，查询显示一定范围时，数据都是紧密相连的，所以通过二分查找，很快可以确定记录的位置，节省了大量的 io 操作

• 缺点：

• 1、插入速度严重依赖于排序速度，按照主键顺序插入是最快的方式，否则会出现页分裂的现象，所以一般都会定义一个自增主键
• 2、更新主键的代价更大，更新主键后，其位置需要改变，就需要重新进行排序，所以一般定义主键为不可更新
• 3、二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据

• 非局促索引是根据非主键的其他字段（这里用 c2）进行排序，通过 B+ 树只能查找记录的主键值，如果想要通过 c2 来查询用户的完整记录，需要一次 “回表操作”，通过查找到的主键值再回到聚簇索引中查找到用户完整记录
  

• 聚簇索引的叶子结点就是数据记录，非聚簇索引的叶子结点存储的是主键的值
• 一个表只能有一个聚簇索引，只能有一组排序方式，非聚簇索引可以有多个
• 使用非聚簇索引的增删改操作效率更高，比如更新 c3 字段对于通过 c2 实现的非聚簇索引没有任何改动的地方，只需要去修改聚簇索引里面的数值。使用聚簇索引查询的效率更高（不用回表）

• 同时为多个列创建索引，比如上面的我们想要让 B+ 树按照 c2 和 c3 的大小顺序进行排序：
  • 需要遵循最左侧原则
  • 先让各个记录根据 c2 进行排序
  • 如果 c2 相同，再根据 c3 进行排序

• 联合索引示意图：
  

这里有一个常见面试问题：最左前缀匹配具体是怎么实现查找的？最左前缀匹配⽤了B+树的哪些特性？

• 最左前缀匹配，即在索引树中先按照联合索引中最左侧的字段进行匹配，如果最左侧的值相等那么再去根据右侧的列进行排序。在具体的查询语句中，全值匹配查询的顺序无所谓，优化器会将 sql 语句优化为查询性能最好的模式，但是如果查询中缺少当前字段的左侧字段，当前字段将无法使用，但是如果仅仅对一个字段进行查询，那么无论其是否满足最左侧原则都可以使用到索引，只不过使用的方式不同：从索引第一个字段进行查找，直到找到某个适合的索引

• 两种不遵循最左侧原则的例子：
  

• ①、当没有使用到 age 左侧的 name 字段，这里就不遵循最左侧原则了，所以只有 id 的索引可以使用，注意这里如果查询的是 name 和 age 没有 id 那么整个索引都用不了，只能进行全表搜索，此时索引失效

• 1、根结点的位置万年不动，我们刚看到的上面生成 B + 树的过程，其实真实情况并不是这样，而是先生成一个数据页，此数据页就作为整颗 B+ 树的根结点，随后表中插入记录，先放入到此数据页中，当此跟结点的目录页使用完后，将此数据页中的记录复制到一个新的数据页中，后进行页分裂，看插入的数据插入在哪个位置合适，原根结点的数据页就升为目录页，所以实际过程是一层层向下去生成
• 2、内节点中目录项记录的唯一性，这是对于二级索引提出的，真实二级索引的实现也是和上面的图片略有区别：看下面的图，当插入 9 1 c 的时候，下面的图怎么进行插入呢？

• 可以发现 最上面 目录页的 1 和 1 是相同的插入的 c2 也是 1 此时就不知道往哪里插入了，所以此时需要加入一个可以作为区分的数值，它是唯一的那么就是主键，加入主键以确保目录项的记录的唯一性

• 可以发现除了页号和 c2通过 c1 就可以确保数据项的唯一性
• 3、一个页面最少存储两条记录，至少得存两个构成二叉树

• B 树结构：

• B+ 树结构：

• 二者区别：
  • B树：增加 树 的杈树层数就会降低，就会大大降低磁盘的 IO 次数
  • B+ 树：是一种多路搜索树，是 B 树的改进，B+树更适合索引文件的搜索。特征：
  • 1、B+树叶子结点存储数据，非叶子结点仅用于索引，B树非叶子结点既会存储数据也会用于索引
  • 2、B+树非叶子结点的关键字同时存在叶子结点的关键字中，同时是子结点中所有关键字最大（最小）
  • 3、所有关键字都在叶子结点出现，构成一个有序的链表，而叶子结点本身按照关键字的大小进行排序为一个顺序的链表，看数据直接看叶子结点就可以，然而 B 树需要中序遍历，因为 B 树一部分数据在非叶子结点中
• 为什么采用 B + 树
  • 1、查询的效率更稳定，查询次数稳定在 3 次，B 树由于叶子结点也会存储数据，所以查询的次数不稳定，几次都有可能
  • 2、查询的速度更快，IO 次数更少

• 为了减少 IO，索引树不会一性加载，数据库索引是存储在磁盘上的，如果数据量十分巨大，必然索引的大小也会十分的大，查过几个 G，当利用索引查询的时候，不可能把几个 G 都一次都加载到内存，只能是逐一加载每一个磁盘页，每一个磁盘页对应索引树上的一个结点

• InnoDB 存储引擎中页的大小为 16 KB，主键的大小一般为 4 / 8 字节，指针的大小一般为 4 / 8 字节，所以一个页大致可以存储 16KB / (8B + 8B) = 1K 的数据，这里设 K 为 10 ^ 3，所以三层的 B+ 树就可以存储 10^3 * 10^3 * 10^3 = 1亿 ，实际情况中，每个结点可能不会填充满，因在数据库中，B+ 树一般的高度都是在 2 - 4 层，InnoDB 存储器是把根结点设置为常驻结点，所以一般查找记录最多需要 1 - 3 此磁盘 IO

• ①、hash 索引不支持范围查找，hash 索引指向数据是无序的，B+ 树的叶子结点是有序的链表
• ②、hash 索引不支持模糊查询和排序
• ③、hash 索引不支持联合查询的最左侧原则，hash 索引把联合索引的两个字段合并为一体计算 hash 值，不支持分别计算两个字段的哈希值，所以hash 索引不能向 B+ 树索引那样，可以把两个字段拆开来实现联合索引
• ④、InnoDB 不支持 hash 索引

---

3、char，nchar，varchar，nvarchar的区别

• ①、char
  • 固定长度，存储 ANSI 字符，不足的补英文半角空格
• ②、varchar
  • 可变长度，存储 ANSI 字符，根据数据长度自动变化
• ③、nchar
  • 固定长度，支持 Unicode 字符，不足的补英文半角空格
• ④、nvarchar
  • 可变长度，支持 Unicode 字符，可以根据数据的长度自动变化

• 区别：

• ①、不带 n 的：只能用于单字节来存储数据，适合英文，中文不会兼容，最大存储数值为：8000
• ②、带 n 的：可以存储两个字节的数据，可以存储中文，最大存储数值为：4000
• ③、可变长度和不可变长度：不可变长度，当分为 10 个字节大小后如果不满足 10 个字节，也会占用 10 个字节。可变长度，当分配 10 个字节后，如果不满 10 个字节会根据实际大小进行调整。如果确定大小且不包含中文，可以使用 char。不确定大小，且不含中文使用 varchar。同情况有中文就采取对应加 n 的版本

---

4、索引的类型，普通索引和主键索引查询起来有什么不同

• 按照功能逻辑上分可以分为四种：唯一性索引，主键索引，普通索引，全文索引
• 按照物理实现方式可以分为两种：聚簇索引，非聚簇索引
• 按照作用字段的个数可以分为两种：单列索引，联合索引

• 1、普通索引，就是作用在一个列上创建索引，后通过索引快速查找，提高查询的速度，可以创建在任意数据类型，其值和是否为空是否唯一都由其自己的完整性约束条件决定，可以有多个普通索引

• 2、唯一性索引：当创建唯一约束时，自动添加唯一性索引，限值索引的值唯一，可以为空，一张表中可以有多个唯一索引，通过唯一索引可以快速找到某条记录

• 3、主键索引，当创建主键时，自动添加主键索引，相当于在唯一性索引的基础上再加上非空的约束，一张表只能有一个主键索引，因为主键索引的物理存储方式，其在文件中只能按照一种顺序的方式进行存储

• 4、单列索引：在表中的单个字段创建索引，单索引可以是普通索引，唯一性索引还可以是全文索引，只需要保证一个索引对应一个字段即可，单列索引可以有多个

• 5、联合索引：使用多个字段上创建一个索引，该索引同时指向多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用到第一个字段的时候才会被使用，在此过程中遵循：最左前缀原则

• 6、全文索引：搜索引擎关键使用到的技术

• 1、声明唯一约束、主键约束、外键约束、会隐式的创建索引
• 2、声明式：在表的最后一句添加 index 索引名（具体在哪个列创建索引）
• 查看索引：show index from 表名
• 3、表已创建好：alter table …… add……
• 4、删除索引：alter table …… drop index ……（索引名）
• drop index （索引名） on （表名）

• 主键索引又被称为聚簇索引，通过主键值即可以快速定位到某条记录，普通索引存的叶子结点存储的是主键的值，所以说找到主键后，需要一次回表操作，回到聚簇索引的表中再寻找具体的记录。一个表可以有多个普通索引，并且没有任何限制，一个表只能有一个主键索引，不能为空且唯一

---

5、索引什么时候该使⽤，需要注意什么

• 1、字段的数值有唯一的限制
• 2、频繁使用 作为 where 查询条件的字段
• 3、经常 order by 或 group by，索引根据字符设计一个 B+ 树按照字段的大小进行一个排序，本身索引是排好序的，oder by 直接省去了排序的时间，排序好后相同的数据都是在临近存放的，所以 group by 也可以节省分组的时间
• 4、update delete where 条件列
• 5、distinct 需要创建索引，创建索引是记录是被排序过的，相同相邻存储，去重操作更快
• 6、多表 join 连接操作时，创建索引需要注意：连接表的数量不要超过三张，每增加一张表就相当于增加一次嵌套的循环，数量级增长非常快。对 where 条件创建索引，对于连接的字段创建索引，并且该字段在多表中数据类型一致
• 7、使用列的类型小的创建索引，添加数据表空间占用的小，创建索引占用的空间小，占用空间小，一个数据页可以存储多个数据项，整颗 B+ 树就会更加的扁平，更利于提高查询的速度，意味着更高效的 IO
• 8、 使用字符串前缀创建索引，如果字符串很长，B+ 树存储此字符串就更费时，在索引中占用的存储空间就越大，所以可以截取字符串的前面的一段字符作为索引，这个就叫做前缀索引，既节约空间，又减少了比较次数。但是由于包含的是不完全的字符串信息，索引前缀的方式不能进行索引排序，只能进行文件排序
• 9、区分度高的设置为索引，比如学生表中的学号，区分度查过 33% 就可以考虑设置为索引
• 10、使用使用度最频繁的列，放到联合索引的左侧
• 11、多个字段都要创建索引的情况下，联合索引优于单值索引

• 限制每张表的索引不要超过 6 个，索引越大需要的磁盘就越大，进行增删改维护索引的花费的时间多，多个索引比较器比较的次数会变多，效率会相对降低

• 当表中数据多到一定程度，索引的创建对于 sql 的查询速度，是有质的提升的，看下面的例子

• 在没有创建索引的时候，在一张 500000 个数据的表中使用 where 条件进行查询，执行了 360 ms，创建索引后执行时间只有 15 ms 快了将近 20 倍，所以当表中的数据越多，可以提高的倍数越大

create index sid on student_info(student_id);
explain select * from student_info where student_id = 12;
select * from student_info where student_id = 12;

• 21:49:03 select * from student_info where student_id = 12 LIMIT 0, 1000 4 row(s) returned 0.360 sec / 0.000 sec
• 21:51:21 select * from student_info where student_id = 12 LIMIT 0, 1000 4 row(s) returned 0.015 sec / 0.000 sec

• 1、在 where 中使用不到的字段不要设置索引，order by 或者是 group by
• 2、数据量小的最好不要创建索引，索引会占用物理空间
• 3、有大量重复的列上不要创建索引，比如学生表中的性别，区分度很小只有男、女
• 4、避免对频繁更新的字段创建索引
• 5、不建议使用无序的值最为索引，比如身份证 / UUID
• 6、删除不再使用或者很少使用的索引
• 7、不要定义冗余的索引

---

6、索引失效：

• MySQL 提高性能最有效的方式就是对数据表设计合理的索引，索引提高查询的访问速度，但其实用不用索引还是优化器说的算，它是基于开销去进行优化，如果存在索引的情况下还是需要进行全部扫描，那么优化器就会直接抛弃索引，直接进行全表扫描。所以 sql 能否正常使用索引，根数据库版本，数据量和数据选择度都有关系

• 1、全值匹配我最爱
• 在多条件查询时，两个字段（或者多个字段）同时存在索引，较少的索引情况就会失效

①、当一个字段使用索引时：

create index a on student (age);
explain select * from student where age = 30 and classId = 20 and name = '123';

②、两个字段使用索引

create index a_c on student (age,classId);
explain select * from student where age = 30 and classId = 20 and name = '123';

③、三个字段组成联合索引：

create index idx_age_classId_name on student (age, classId, name);
explain select * from student where age = 30 and classId = 20 and name = '123';

可以发现，当存在多个索引时，尽可能包含多的字段的索引会优先使用，较少字段的索引就会失效

• 2、最佳左前缀法则

当联合索引的左侧的字段没有被使用到，而是使用后面的字段进行查询，就会导致索引失效。这里没有遵循最左侧原则，因为当 age 相同时才会使用到右边的字段的索引进行比较，那直接使用右侧的索引就会失效

explain select * from student where age = 30 and classId = 20 and name = '123';

explain select * from student where classId = 20 and name = '123';

• 3、主键插入顺序

一般在设置主键时，使用的是自增主键，这样在聚簇索引的情况下，会保证主键的有序性，避免后面插入需要不断的维护索引，如果插入的主键是无序的，这样就需要索引动态的维护主键的有序性，就会一定程度上降低索引的性能

• 4、计算、函数、类型转换使索引失效

在 sql 中使用函数就会导致索引失效，因为使用函数，索引不知道具体的比价规则是什么，就需要根据其截取的结果一点一点的进行比较，这样就是顺序进行查找，索引就失效了

create index n on student(name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

• 5、类型转换导致索引失效

当本身这个字段是字符类型，如果判断条件给的是整型，索引就会失效：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name = 123;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name = '123';

• 6、范围条件中右边的列索引失效

联合索引使用中，如果存在是条件的查询那么其右侧的索引会失效，这里主要是看定义的联合索引中在查询中以条件查询的方式的字段的位置

• 比如，下面这个联合索引，这样定义;

create index idx_age_classId_name on student (age, classId, name);

• 再看这样一条 sql 语句:

explain select * from student where age = 30 and classId > 20 and name = '123';

可以发现在 classId 处使用到了范围条件查询，此时需要回到联合索引的定义中，classId 右侧是 name 字段，那么 name 字段的索引就会失效:

此时的索引长度为 10，前两个字段索引长度为 4 + 4 + 1 + 1 = 10 所以第三个字段的索引就失效了，正常情况下，三个字段的索引都被使用到的话，索引的长度应该为 4 + 4 + 1 + 1 + 20 * 3 + 1 + 2 = 73，索引长度的计算规则：字符串长度为 3 整型为 4 不为空 1 变长 2

• 7、不等于索引失效

等于的可以使用索引，不等于就需要一个个比对去找，索引就会失效：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != '123';

• 8、is not null 索引失效

和不等于同道理：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name is not null;

• 9、or 条件前后存在非索引的列索引失效

因为是 or 条件任意一边带有索引的按照索引查找后，另外一边也还是同样需要顺序查找，所以存在索引的一边也就没用了

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name = '123' or classId = 1;

• 10、like 以通配符 % 开头的索引失效

以%开头，就需要一一去比较其结尾，所以需要顺序查找，因为可能有很多前缀比如 dabc eabc……，% 在最后，通过索引可以比较前几个字符，因为 B+ 数是按照索引的值有序排列的，索引根据

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc';

• 11、字符集之间的转换导致索引失效

---

7、innoDB 和 myisam 的区别

• ①、innnoDB 存储引擎中，通过主键值对聚簇索引进行一次查找就可以找到对应的记录，myisam 需要一次回表的操作，这就意味着 myisam 中建立的索引相当于全部都是二级索引
• ②、innoDB 的数据文件就是索引文件，myisam 的数据文件和索引文件是分离的，索引文件中保存的是数据文件的地址
• ③、innoDB 的非聚簇索引存储的是相应记录主键的值，而 myisam 中存储的是数据的地址
• ④、myisam 的回表操作是非常迅速的，因为是拿着数据的地址去直接进行访问，innoDB 的非聚簇索引的回表操作，是获取主键后再去聚簇索引中进行查找，虽然不算慢，但是还是不如直接通过地址访问来的快
• ⑤、innoDB 要求表必须有主键，如果没有显示的指定，则 MySQL 系统会自动选择一个可以非空唯一的数据记录作为主键，如果不存在这样的列，MySQL 自动为 innoDB 生成一个隐藏列，作为主键
• ⑥、innoDB 支持外键和事务，但是 myisam 不支持

• myisam 的存储引擎采用的索引的数据结构：

---

8、数据库优化步骤

数据库调优中，目标就是响应更快，吞吐量更大

• 在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率

show status like '参数'

• 统计 sql 的执行成本

SHOW STATUS LIKE 'last_query_cost';

• 如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找

SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id = 900001;

• 运行结果（1 条记录，运行时间为 0.042s ）然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：

如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？

SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id BETWEEN 900001 AND 900100;

运行结果（100 条记录，运行时间为 0.046s ）：然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询。

能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间
基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然 页数量（last_query_cost）增加了不少 ，但是通过缓冲池的机制，并没有增加多少查询时间 。

①、开启 slow_query_log：

mysql > set global slow_query_log='ON';

②、 修改long_query_time阈值

show variables like '%long_query_time%';

③、这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：

//全局
mysql > set global long_query_time = 1;
mysql> show global variables like '%long_query_time%';


//当前会话
mysql> set long_query_time=1;
mysql> show variables like '%long_query_time%';

④、展示慢查询语句个数

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

⑤、 慢查询日志分析工具：mysqldumpslow，-s: 是表示按照何种方式排序，-t: 即为返回前面多少条的数据，5是返回前五条，慢 sql

mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log

⑥、关闭慢查询日志

slow_query_log=OFF

• 分为两步：

①、开启查看 sql 执行成本：

 show variables like 'profiling';
 set profiling = 'ON';

②、进行查看：

mysql > show profiles;

• 基本语法：

EXPLAIN SELECT select_options

• id — 选择标识符，id 越大优先级越高，越先被执行；
• select_type — 表示查询的类型；
• table — 输出结果集的表；
• partitions — 匹配的分区；
• type — 表示表的连接类型；
• possible_keys — 表示查询时，可能使用的索引；
• key — 表示实际使用的索引；
• key_len — 索引字段的长度；
• ref— 列与索引的比较；
• rows — 大概估算的行数；
• filtered — 按表条件过滤的行百分比；
• Extra — 执行情况的描述和说明。

• type 值：

• all — 扫描全表数据；
• index — 遍历索引；
• range — 索引范围查找；
• index_subquery — 在子查询中使用 ref；
• unique_subquery — 在子查询中使用 eq_ref；
• ref_or_null — 对 null 进行索引的优化的 ref；
• fulltext — 使用全文索引；
• ref — 使用非唯一索引查找数据；
• eq_ref — 在 join 查询中使用主键或唯一索引关联；
• const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点。

上面都是一些查询 sql 性能的一些工具，下面正式开始 sql 性能的优化：

• 这里选择外连接的左外连接作为代表：

• inner join / left join 可以通过优化器决定哪张表示驱动表 / 被驱动表，并且只有一个索引的情况下，设置被驱动表中某字段存在索引可以提升程序性能，因为是需要在被驱动表中进行查找操作。对于内连接来说，两个表的连接条件都存在索引的情况下，会选择小表作为驱动表，小标驱动大表（这里的大小指的是：表行数 * 每行大小）

• join 底层

• Simple Nested-Loop Join：简单嵌套循环连接，两张表 A 、B 表，A表尾驱动表，B表尾被驱动表，每次取出 A 的一条记录到 B 表中进行一次全表扫描，找到满足条件的行和 A 中的记录组成下一行，重复上述步骤，一直到 B 表扫描完成
• Index Nested-Loop Join：对于简单嵌套循环连接的优化，索引嵌套循环连接，驱动表每次取出一行数据，然后根据该数据在被驱动表进行查找，因为被驱动表中存在索引，因此每次只扫描一次即可，不用遍历整张表，如果驱动表扫描 100，在被驱动表中扫描也是 100 次，总扫描次数就是 100 次
  
• Block Nested-Loop Join：块嵌套循环连接，当出现索引失效的情况，或者本身两张表中不存在索引，此时为了优化连接，MySQL 采用块嵌套循环连接的方式进行连接，其不像简单嵌套循环连接那样一条一条的读取驱动表的数据，而是一次批量的进行读取到 join buffer 缓冲区，然后全表扫描被驱动表中的一条条数据和 join buffer 中的数据进行批量的匹配，这样就降低了被驱动表访问的频次
  

• join 小结：

• 1、整体效率比较：索引 > 块 > 普通
• 2、 永远小的结果集驱动大的结果集
• 3、为被驱动表添加索引
• 4、增减 join 缓存区的大小（一次缓存的数据越多，被驱动表扫描的次数越少）
• 5、 减少驱动表不必要的字段查询（减少不必要的字段查询，可缓存的数据就越多）

• Mysql 8.0 后新特性：hash join

• 当表中的数据量比较小的时候，Nested-Loop 是个比较好的选择
• 当大量数据连接时，优化器在相对较小的表中利用 join key 在内存中建立散列表，然后扫描较大的表并线形探测散列表，找出与 hash 表匹配的行

• 常见面试题：Inner join 和 left join 性能不同，inner join反⽽慢是为什么？

• 首先根据逻辑计算数量来看， left join 的运算量要大于 Inner join，因为左外连接是需要返回左表中的全部记录，若右表无对应记录，则置为 null 。而 Inner join 只返回两张表的交集。一般情况下， Inner join 要比 left join 性能要高，那么反而慢的情况，可能是左外连接左表为大表，右表为小表，在小表上创建了索引，此时嵌套循环走的是小表的索引，而内连接左表为小表，大表为右表，此时大表没有索引，所以性能一定程度上降低了

• 子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子、查询的执行效率不高。所以一般采用连接查询的方式来替换掉子查询

• sql 内在 order by 字句中创建索引，可以避免使用 FileSort 排序，当然有的情况下 FileSort 比索引要快，所以还是需要尽可能的避免，所以尽量使用索引完成 order by，无法使用 order by 就需要对 FileSort 进行调优

• filesort算法：双路排序和单路排序

• 双路排序：先读取 order by 的列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
• 单路排序：一次将所有列进行了扫描，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

• 调优的方法:

• 1、尝试提高 sort_buffer_size（可以使缓存多存一些数据）
• 2、尝试提高 max_length_for_sort_data（决定使用哪种排序方式）
• 3、Order by 时select * 是一个大忌。最好只Query需要的字段（减少被查询的字段，尽可能多缓存数据，提供性能）

• group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
• group by 先排序再分组，遵照索引建的最佳左前缀法则当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
• where效率高于having，能写在where限定的条件就不要写在having中了
  减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group
• by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
  包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

• 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
• 该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

• 理解：非聚簇索引的一种形式，它包括在查询里的 select、join、where 子句中用到的所有列（索引中的字段刚好覆盖查询条件中的所有字段）
• 向上面说的索引失效，如果对查询的方式进行变化，就会索引就会生效：

create index n on student(name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc';

• 此情况按照上面是索引失效的，改动这里 * 为 name 字段观察是否使用索引

EXPLAIN SELECT SQL_NO_CACHE name FROM student WHERE student.name LIKE '%abc';

• 这里的原因，就是这里只需要查询一个列，而这个列又刚好被索引覆盖，直接通过索引就可以获取到这个列，这就是覆盖索引。上面查询所有列之所以失效是因为，其通过索引查找后还需要进行回表操作，优化器会认为这样的查找方式不如直接全表扫描来的快，所以索引失效。

• 覆盖索引的利弊：

• 1、避免 innoDB 进行索引的二次查询（回表）
• 2、可以把随机 IO 变成顺序 IO 加快查询速度，在非聚簇索引中如果找到某条记录时，此时如果需要查询主键值对应的详细数据记录，需要一次回表操作，此时非聚簇索引中的主键是无序的，所以回到聚簇索引中就会出现主键的位置千差万别，这样就是随机的 IO 了，位置不确定。有了覆盖索引直接查询到需要查询的列，而且还都是顺序存储的（按照定义索引的列拍好序），这样就是顺序 IO 了，查询速度更快
• 3、缺点：索引字段的维护 总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务 DBA ，或者称为业务数据架构师的工作。

• MySQL 5.6 之后的新特性，当查询条件中包括某字段（该字段存在索引），但是索引失效了，通过索引条件下推（ICP）过滤掉一部分无效数据，减少回表操作的次数，提高查询次数，看下面这张表：

create table city1 (
id int primary key, 
it int,
name varchar(50),
password varchar(50)
);
create index i_n on city1(it,name);
insert into city1 values(1,2,'天津','123');
insert into city1 values(2,5,'背景','1223');
insert into city1 values(3,5,'南京','1233');
insert into city1 values(4,9,'的','1234');

explain select * from city1 where it = 1 and name like '%天' and password = '1%';

• 发现这里出现了索引下推，因为联合索引中的第二个字段对应的索引失效了，所以这里通过索引下推去过滤掉一些无用数据来提高查询的速度，可以比对一下开启索引条件下推和关闭情况下，查询速度有明显的差别：

• ICP 的使用条件：

• 1、只能用于二级索引
• 2、explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null
• 3、 并非全部 where 条件都可以用 ICP 筛选，如果 where 条件的字段不在索引列中，还是要读取整表的记录到 server 端做 where 过滤。
• 4、 ICP 可以用于 MyISAM 和 InnnoDB 存储引擎
• 5、MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。
• 6、当SQL使用覆盖索引时，不支持ICP优化方法

• 1、EXISTS 和 IN 的区分：
  

• 如果 A 是大表，B是小表采用 in 的方式进行相关子查询。反之采用 exists

• 2、COUNT(*)与COUNT(具体字段)效率

• 三者都是 MySQL 中用于统计数据表中的行数的，可以使用这三种方式COUNT(*) 与 COUNT(具体字段) 和 COUNT(1)
• COUNT(*) 和 COUNT(1) 可以认为二者本质上没有区别（执行效率略有偏差，但可以忽略不计）
• myisam 引擎中统一表行数只是用 O(1) 的复杂度即可，因为每张表都有一个 meta 信息存储了 row_count 值
  innoDB 存储引擎中，没有这样的变量，只能进行全表扫描
• COUNT(具体字段) 尽量采取二级索引，非聚簇索引比聚簇索引包含的数据更少，利于查询速度，对于 COUNT(*) 和 COUNT(1) 只是统计行数系统会自动采用空间较小的二级索引来进行统计
• 如果存在多个二级索引，会使用 key_len 更小的二级索引，如果不存在二级索引会使用主键索引

3、 关于SELECT(*)

• 查询中建议明确字段，尽量不要使用 * MySQL 解析过程中会将 * 修改成所有列名，这样会消耗大量时间和资源
• 无法使用覆盖索引

4、LIMIT 1 对优化的影响

• 针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上 LIMIT 1 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。
• 如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上 LIMIT 1 了。

5、多使用COMMIT

• 只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。

---

6、主键的设置

• 一般在设置主键的时候，我们采用的是自增 id 的方式进行设计，那么这样的设计方式以下缺点：
  • 可靠性不高：存在自增 id 回溯的问题
  • 安全性不高：对外暴露的接口可以非常容易猜测对应的信息。
  • 性能差：自增 id 的性能比较差，需要在数据库服务器端生成
  • 交互多：业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。
  • 局部唯一性：对于每张表的自增 id 是唯一的，但是全局上就不是了

• 如何设计主键：尽量采用和业务不相关的字段作为主键

• 非核心业务：可以采用自增 id 的方式
• 核心业务：主键设计至少应该是全局唯一且是单调递增，这里推荐使用 UUID 的方式
• UUID 的特点：数据唯一，无序，全局唯一，插入性能差
• UUID = 时间+UUID版本（16字节）- 时钟序列（4字节） - MAC地址（12字节）

• UUID 如何保证数据的唯一性：在UUID中时间部分占用60位，时间维度发生重复的概率降低到1/100ns。同时为了防止时间维度的重复，再最后加上 MAC 地址，MAC 地址用于全局唯一

此时的 UUID 已经满足全局唯一，但是无序的问题如何解决：

• 这里需要改造 UUID 让将时间的高低位进行交换，按照小时分钟秒的方式排序，此时的时间是单调有序的，Mysql 8.0 支持将时间的高低位进行交换，而且还解决了 UUID 占用空间的问题，去掉了无用的 - 并且采用二进制存储，8.0 的 UUID 占用空间为 16 字节，此时的 UUID 既全局唯一又有序，所以此时就可以将其作为主键，看下面的表可以发现此时的有序的 UUID 性能是比较高的

• 如果不是 MySQL 8.0 咋办：

• 手动赋值字段为主键，如果各个分店的会员表自己设置主键，如果每台机器产生的数据需要进行合并，此时主键就不是唯一的，所以为了防止这样的情况发生，在总部的 MySQL 数据库中，有一个管理信息表，添加一列专门维护当前最大的 id ，当每个分店再插入数据的时候，先去总部的数据库中查询当前最大的 id，再此基础上 + 1，最为当前用户的主键，这样在数据合并时就不会发生主键重复的问题了

• 参考上面的范式，和表的约束

• ①、调优的目的：尽可能节省系统资源，以便系统可以负载更多的服务（增加吞吐量），提高响应速度，减少系统瓶颈，提高 MySQL 性能：

• 如何定位调优：用户反馈（主要），分析日志（主要），服务器资源使用监控，数据库内部状况监控，其他

• ②、调优的步骤和维度：

• 1、选择合适的 DBMS
• 2、优化表设计
• 3、优化逻辑查询（sql 等价变化提升查询效率）
• 4、优化物理查询（索引的创建和使用）
• 5、使用 Redis 作为缓存
• 6、库级分离（读写分离，数据分片）

• ③、优化服务器，优化 MySQL 参数
• ④、优化数据库结构：

• 1、拆分表：冷热数据分离，将一张表中常用字段和不常用字段进行拆分，减少单张表的数据量，提高查询效率
• 2、增加中间表（临时表），需要通过 join 两张表来查询两张表中的几个字段，此时可以使用中间表，将两张表中需要查询的这几个字段拷贝出来单独放在一个临时表中，这样就减少了连接表的操作，提供了查询速度
• 3、增加冗余字段（反范式化）
• 4、优化数据类型，尽量选择数据类型小的，这样一个数据页可以多放些数据，整颗 B+ 树会更加扁平，利于提高查询效率。整型一般选取 INT 就可以，INT 有足够大的范围。既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型。避免使用TEXT、BLOB数据类型。避免使用ENUM类型。使用TIMESTAMP存储时间。用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
• 5、优化插入数据的速度，单个插入变为批量插入
• 6、使用非空约束，如果业务允许尽量使用非空约束，省去判断非空的语句，也利于一些聚合函数的使用
• 7、分析表，检查表，优化表

• ⑤、大表优化

• 限定查询范围
• 读写分离
• 垂直拆分
• 水平拆分

• ⑥、其他调优策略

• 1、服务器语句超时处理，设置超时限制，当有执行语句的时间超过设置的毫秒级数，服务器将终止查询影响不大的事务或连接
• 2、创建全局通用表空间，节省元数据方面的内存
• 3、隐藏索引对调优的帮助

常见面试题：临时表⼀般⽤来做什么的？

• 临时表，临时的表，数据库不会将其序列化到磁盘上，临时表分为两种：会话临时表，全局临时表。当两张表需要进行联合查询来查询二者的一些字段，可以使用临时表保存两张表需要查询的字段，省去表连接的操作，提高查询的效率

常见面试题： ⼀个sql查询慢，百万级别，单表，你觉得性能问题可能出在哪？

• 1、使用 select * 对所有字段进行查询，返回了一些不必要的列（查询数据量过大）
• 2、使用某些查询方法，导致索引失效，而进行了全表的扫描
• 3、IO 吞吐量小，形成了性能瓶颈
• 4、锁或者死锁
• 5、网络速度慢

---

9、sql 执行流程：

---

10、innodb事务的四大特性是什么，分别描述⼀下

• innoDB 存储引擎是支持事务的
• 事务的概念：

• 一种逻辑操作单元，从一种状态变成另一种状态

• 事务处理的原则：

• 保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种方式。当一个事物有多个操作的时候，要么所有事物都被提交，持久化到数据库中，要么数据库管理系统将放弃所有的操作，进行整个事物的回滚

• 比如 A 给 B 转账 100 ，这个过程就是一个事物，当 A 扣款 100 ，当给 B + 100 时出现了数据库宕机，此时进行整体事务的回滚。也是就是要么整体成功，要么整体失败

• 事务的 ACID 特性

• 原子性：原子性表示事物是一个不可分割的工作单位，要么全部提交，要不全部失败回滚
• 一致性：事务执行后，从一种合法状态到另外一种合法状态，是语义上的，根具体的业务无关
  • 比如：A 有 200￥ 给 B 转 300，A剩 -100 就是非法的状态，此时就不满足事务的一致性，又加入 A 有 100￥ B 有 100￥给 B 转 50 B 没到账，也是不合法的， 因为不满足 A + B 的钱数前后一致
• 隔离性：事务的隔离性是指一个事务的执行不能被其他事务干扰，即是一个事物内部操作及使用数据对并发是隔离的，并发执行的各个事务是互不干扰的
• 持久性：当事务成功提交后，就将其持久化到数据库中，它对数据库中的数据改变是永久的，接下来的数据库操作或者数据库故障对其没有影响

• 事务的状态：

• 1、活的：事务对应的数据库操作正在执行中，就称事务是活着的
• 2、部分提交的：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响就是数据没有刷新到磁盘中
• 3、失败的：事务处于活着 / 部分提交的状态时，可能发生某种错误，导致当前事务无法继续向下进行，或者人为停止当前事务
• 4、中止的：事务处于失败的状态，撤销失败事务对数据库造成的影响，也就是执行回滚操作
• 5、提交的：当事务处于部分提交的状态时，磁盘刷新操作成功后

• 如何使用事务
• 显式事务：

• 显示的开启事务：START TRANSACTION 或者 BEGIN ，作用是显式开启一个事务。START TRANSACTION 与 BEGIN 的不同之处是，START TRANSACTION 后面可以几个修饰符：READ ONLY（只读事物），READ WRITE（读写事物），START TRANSACTION（启动一致性读）
• 一系列事务的操作（主要是 DML，不含 DDL）
• 提交事务或者中止事务（即回滚事务）

//开启事务
mysql> START TRANSACTION;

//提交事务
COMMIT;

//回滚事务
ROLLBACK;

//将事务回滚到某个保存点
 ROLLBACK TO [SAVEPOINT]

• 隐式事务：

• MySQL中有一个系统变量 autocommit ：
  
• SET autocommit = OFF; 关闭自动提交功能
• 隐式提交数据的情况:
  • 1、数据定义语言（Data definition language，缩写为：DDL）
  • 2、隐式使用或修改mysql数据库中的表，alter，update，set……
  • 3、事务控制或关于锁定的语句：
    • 当 begin 开启事务，当前事务还没有提交，又使用 begin 开启事务，当前事务就会自动提交
    • 当前的 autocommit 系统变量设置为 on
    • LOCK TABLES 、 UNLOCK TABLES 等关于锁定的语句也会隐式的提交前边语句所属的事务
  • 4、加载数据的数据
  • 5、关于 MySQL 的一些复制语句
  • 6、其他一些语句

• 事务的隔离级别：

• 一个 MySQL 服务器可以同时连接多个客户端，也就是一个客户端需要处理多个事务，刚刚上面提到事务是具有隔离性的，所以安利应该是一个事物正在开启状态，其他事务应该进行排队，但是这样的性能不好，所以即保证事务的隔离性，也要保证性能，下面看看此二者是如何权衡的

• 数据并发的问题：
• 先准备数据：

• 1、脏写：两个事务A，B，事务 A 修改了未提交事务 B 修改过的数据，那就意味发生了脏写

事务 B 发生了回滚，此时的 name 字段回滚到最早的 name 属性为：小谷，那么此时 A 提交事务后明明 A 修改了 name 属性为李四，但是查询结果了小谷，这就是脏写问题

• 2、脏读问题：事务 A 读到了已经被 B 更新但还没提交的字段，之后 B 进行回滚，那么 A 读到就是一个无效的数据，这就是脏读问题

• 3、不可重复读：事务 A 读取到一个字段，之后事务 B 对此字段进行修改，事务 A 再次对同一字段进行读取，读到的内容不一样，就是不可重复读问题

注意：这里的不可重复读针对的是修改操作，下面的幻读针对是增删的操作

• 4、幻读：事务 A 读取到一个字段，此时 B 去往表中添加或者删除几行数据，事务 A 再次去读同一张表，就会多出来 / 少几行数据，这就是幻读问题

• SQL 的四种隔离级别：

• 首先声明一点，就是这四种隔离界别都解决了脏读问题
• 1、READ UNCOMMITTED：读未提交，此隔离界别解决了脏写问题，但是没有解决脏读，不可重复读，幻读的问题
• 2、READ COMMITTED：读已提交，此隔离级别解决了脏写、脏读问题，但是没有解决不可重复读和幻读的问题
• 3、REPEATABLE READ：可重复读，此隔离级别解决了脏写、脏读、不可重复读的问题，没有解决幻读的问题
• 4、SERIALIZABLE：序列化，此隔离级别所有问题都解决了，但是同样事物的并发性降到了最低，事物一个个排队进行，所以实际开发中几乎不会使用到此隔离级别，所以常使用可重复读 + 锁解决幻读问题

• MySQL 默认的隔离级别：

• 使用语句查询：show variables like ‘tx_isolation’ (5.7.2版本之前的查询语句)，之后：show variables like ‘transaction_isolation’

• 设置隔离级别：SET [GLOBAL / SESSION] TRANSATION_ISOLATION = ‘隔离级别’ （此处隔离级别中间需要加一个 - ）5.7.2 之后，（之前）SET [GLOBAL / SESSION] TRANSATION ISOLATION LEVEL + 隔离级别（这里不需要加 - ）

---

11、MySQL 日志文件

• 事务有四大特性：原子性，一致性，隔离性，持久性。这四大特性是基于怎样的机制实现的？

• 隔离性：基于锁机制
• 一致性，原子性，持久性，是基于 undo 日志和 redo 日志来保证的
  • redo 重做日志，恢复提交事务修改的页操作，用于保证事务的持久性
  • undo 回滚日志，回滚行记录到某个特定版本，用于保证事务的一致性，原子性
• 两个日志的联系和区别：
  • 都是存储引擎上生成的日志，redo 是物理级别上的页操作，记录页号偏移量和怎样的操作，undo 是逻辑级别上的页操作（sql 语句），比如插入一条数据，undo 日志就记录一条删除操作

1、为什么需要 redo 日志

• innoDB 存储引擎以页单位来管理存储空间，在正在访问页面的时候，需要把磁盘上的页缓存到内存上，对数据页进行修改之后，再将修改后的页刷新到磁盘中
• 为了保证事务的持久性，一个简单的方法就是每次事务提交之前把该事务所修改的所有页面的都刷新到磁盘中（时时进行修改），但是会造成修改量和刷新磁盘量不成正比（数据读到内存都是以页为单位的，可能修改操作只是页中的一个很小的操作），而且修改的数据的数据页存储位置可能不是连续的，所以随机 IO 比较慢
• 我们只是想要已经提交的事务对数据库中的数据所作的修改持久保存，即使后面系统崩溃，也可以通过 redo 日志进行恢复刚刚进行的修改。所以没必要在每次事务提交时就把该事务在内存中修改的全部页面都刷新到磁盘中，只需要把修改记录一下就好，比如某个事务把系统表空间中页号为 10 偏移量为 100 字节的数据从 1 改成了 2，只需要记录一下页号偏移位置和修改的操作

• redo 日志的好处：
  • redo 日志降低了刷盘的频次
  • redo 日志占用的空间非常小
• redo 日志的特点：
  • redo 日志是顺序写入磁盘的
  • 事务执行中，redo log 不断记录

• redo 日志可以分为两部分：

• 重做日志的缓冲 (redo log buffer) ，保存在内存中，是易失的
• 重做日志文件 (redo log file) ，保存在硬盘中，是持久的。

• redo 的整体流程：
  

• 1、先从磁盘中读取一条数据到内存中
• 2、生成一条重做日志，写入 redo log buffer ，写入的是修改后的数据
• 3、当事务 commit 时将 redo log buffer 刷新到 redo log file 中
• 4、定期将内存中修改的数据刷新到磁盘中

• 3、redo log 刷盘策略：

• redo log 的写入不是直接写入磁盘的，InnoDB 引擎会在写 redo log 先写入 redo log buffer 中，然后以一定的频次去写入到 redo log file 中，此处的一定频次就是 redo log 的刷盘策略
• redo log buffer 刷新到 redo log file 中不是真正的刷新到磁盘中去，只是刷新到文件系统缓存中（page cache），然后等文件系统缓存到达一定的数据量，再交给系统来决定写入磁盘，对于 innoDB存储引擎来说，此处交个系统决定，如果操作系统发送故障，这里的缓存数据就会丢失
• 针对上述情况，innoDB 给出三个参数供选择 innodb_flush_log_at_trx_commit 参数（0，1，2）三个参数
  • 设置为 0 ，每次事务提交不进行刷盘操作（系统默认master thread每隔1s进行一次重做日
    志的同步）
  • 设置为 1 ，每次事务提交都将进行同步，刷盘操作，刷新磁盘较为频繁，效率较低，但是安全性最好
  • 设置为 2，表示每次事务提交都只把 redo log buffer 内容写入到 page cache 中，而什么时候写入到 redo log file 中由系统决定，刷盘的频次相对低，效率更高一些，但是由于是交给系统决定刷盘时机，所以系统故障会导致数据丢失，但是操作系统故障的几率比较小

• 不同策略的执行过程：
  

• Undo日志：

• undo 日志用于保证事务的原子性，一致性，将事务中更新的数据保存和前置操作写入到 undo 日志中
• undo 日志的理解：事务具有原子性，那么事务中所有操作全部成功，要么全部失败。当服务器遇到异常、操作系统错误、或者程序员手动 rollback 的时候，数据回到原来的样子，这个过程就被称为回滚，好像什么事都没做一样

• undo 日志的作用：

• 回滚数据、MVCC

• undo 页重用：

• 如果每开启一个事务，执行增删改之前都记录到 undo 日志中，每个事务都创建一个 undo 页，一个页 16 k，清理不及时，空间占用会很快，所以这里考虑重用，提高空间利用率。当事务提交时，InnoDB存储引擎会做以下两件事情：将undo log放入列表中，以供之后的purge（清除）操作，判断undo log所在的页是否可以重用，若可以分配给下个事务使用

• 回滚段中的数据分类：

• 1、未提交的回滚数据：随时可能会回滚的数据
• 2、提交但未过期的回滚数据
• 3、提交已过期的回滚数据，回滚段满了后，优先覆盖
• 事务提交后并不能马上删除undo log及undo log所在的页。这是因为可能还有其他事务需要通过undo log来得到行记录之前的版本。故事务提交时将undo log放入一个链表中，是否可以最终删除undo log及undo log所在页由purge线程来判断。

• undo的类型：

• 1、insert undo log：提交后就能删除，因为其只能对当前事务可见
• 2、update undo log：支持多版本并发，其他事务可能会需要查看，所以将其放入到 undo 链中，由purge线程来判断是否删除

• undo 生命周期：

• 具体执行过程：
• 当我们执行INSERT时：

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

当我们执行UPDATE时：

UPDATE user SET id=2 WHERE id=1;

• 这里更新 id 时重新生成一条行记录，修改原来行记录的 deletemark 为 1 标记为已删除，更改新行记录 id 为 2

• 3.、undo log是如何回滚的

• 以上面的例子来说，假设执行rollback，那么对应的流程应该是这样：
• a. 通过undo no=3的日志把id=2的数据删除
• b. 通过undo no=2的日志把id=1的数据的deletemark还原成0
• c. 通过undo no=1的日志把id=1的数据的name还原成Tom
• d. 通过undo no=0的日志把id=1的数据删除

• 总结：

---

12、锁

• 事务的隔离性是由锁来实现的，并发事务访问相同记录的情况大致分为下面三种（这里的读操作指的就是 select，写指的是增删改）

• 读 - 读：并发情况下，访问同一条记录，没有对记录进行修改，不会造成影响，这种情况是允许发生的
• 写 - 写：这种情况会发生脏写的问题，多个未提交事务对相同记录进行写操作，在任何隔离级别下都是不被允许的，当 T1 事务对记录进行修改时，其他事务要进行排队等待，这里的等待就是通过锁来实现的，锁是内存中的一种结构，锁一开始和记录是不关联的。当一个事务想对当前事务做改变的时候，先去内存中看没有与这条记录关联的锁，如果没有就生成锁结构与其关联。
• 对于锁：
  • 不加锁：不在内存中生成锁结构，直接执行操作
  • 加锁成功：在内存中生成锁结构，且 is_waiting 为 false 也就是当前事务可以执行
  • 加锁失败：内存中生成了锁结构，且 is_waiting 为 true 也就是当前事务需要等待其他事务释放锁后，才可执行

• 读 - 写：多个事务对相同记录进行读写操作就会造成脏读、不可重复读、幻读的问题，各厂商对 sql 标准的支持不一样，比如 MySQL 在可重复读的隔离界别上就已经解决了幻读问题

• 并发问题的解决方案：

• 方案一：读操作使用多版本并发控制（MVCC），写操作进行加锁
  • 普通的 select 语句在 READ COMMITTED 和 REPEATABLE READ 隔离级别下会使用 MVCC 读取到记录
    • 在 READ COMMITTED 隔离级别下，一个事务在执行中每次执行 select 操作都会生成一个 readview，readview 本身保证事务不可以读取到未提交的事务所做的更改，这样就避免了脏读的问题
    • 在 REPEATABLE READ 隔离级别下，一个事务在执行过程中只有第一次 select 操作才会生成一个 readview ，之后的 select 操作都复用此 readview，这样就避免了幻读和不可重复读的问题，因为每次读到的都是第一次读取到的数据，后续更新的读不到
• 方案二：读写操作都加锁
  • 读写操作排队执行可以避免脏读和不可重复读的问题，但是对幻读的问题，如果对读操作上锁，那么对表中的记录进行加锁，之后进行插入操作的记录是没有被加锁的，所以是可以正常插入的，此时再进行读操作就会多几条记录了，这里对于幻读有这样的问题，这里解决方法是下面要介绍的间隙锁
• 两种方式的比较：
  • MVCC 方式：读写不冲突，性能高
  • 加锁 方式：读写冲突，性能相对低
  • 一般情况下采用 MVCC 的方式，但是有时业务需求也会采用加锁的方式

• 锁的不同角度分类：

• 按照对数据的操作：读锁和写锁

• 读锁：共享锁，S 锁，针对同一份数据，多个事务同时进行读操作不会相互影响，不会相互阻塞
• 写锁：排他锁，X 锁，针对同一份数据，当前写操作还没有完成之前，阻塞其他写操作和读操作
• 对于 innoDB 存储引擎，读写锁可以加在表上，也可以直接加在行上

• 对表中的记录加读锁：select …… lock in share mode
  • 演示：
    

• 可以看到两个会话，同时进行读操作对 account 表，双方都可以正常读，没有发生阻塞的情况

• 对表中的记录加写锁：select……for update（只要有一个写锁，另外一个是读锁还是写锁都会阻塞等待）
  
  

• 当第一个会话开启写锁，第二个再开启读锁，就会发生阻塞等待，直到第一个会话结束，第二个会话这边才可以继续向下进行
  
  
  

• 从数据操作的粒度划分：表级锁，页级锁，行锁

• 表级锁：

  • S锁和X锁，上面介绍过，InooDB 有更细的锁粒度，这里了解一下即可
    

• 意向锁：（两个事务至少有一个是写锁）当两个事务 T1 和 T2，T1对表加行锁，T2 对同一张表加锁，此时会 T2 对表加锁会阻塞，加入表中有 10w 条数据，那么 T2 在加锁时，会先去一行一行去找有没有行锁，如果有就阻塞，这个过程比较耗时。为了提高效率，当 T1 对行加锁，同时在其上一级（更大一级的空间）加意向锁（IX），此时 T2 再对表加锁，就会直接去看有没有意向锁，如果有阻塞等待，没有直接加锁，这样效率比较高。意向锁是为了使行锁和表锁共存。同样分为两种：意向排他锁，意向共享锁
• 意向锁是存储引擎自己维护的，无法手动添加，在数据行加共享 / 排他锁之前，InnoDB 会先获取该数据行所在数据表的对应的意向锁

• 演示：
  

• 但可以发现，如果对不同行之间的上写锁，不会产生阻塞，这是两个意向锁的关系，可以得出以下结论：
  
  
  

• 意向锁的并发性：意向锁不会影响到多个事务对不同数据行的排他锁的并发性
• 结论：

• 1、InnoDB 支持多粒度锁，特定场景下，行级锁和表级锁共存
• 2、意向锁之间不会互斥，意向锁会于共享锁 / 排他锁互斥
• 3、IX IS 是表级锁会和 X S 互斥
• 4、意向锁在保持并发性的前提下，实现了行级锁和表级锁互存且满足事务隔离性的要求

• 自增锁（知道即可）
• 元数据（MDL 锁），保证读写的正确性，如果一个事务遍历表，另一个事务修改表的结构，此时就会造成问题。当一个事务对表进行增删查改操作，加 MDL 读锁，当一个事务对表结构进行改变，加 MDL 写锁
  • 演示：
    
    

• 行级锁：

  • ①、记录锁：仅仅把一条记录锁上，S型记录锁 / X型记录锁，和表的 S / X 一致
  • ②、间隙锁：MySQL 在REPEATABLE READ 隔离级别下，MVCC 可以解决幻读的问题，也可以通过对读写操作加锁，但是此方法存在幻影记录的问题，当一个事务对表中记录加读锁，新插入进来的记录是没有被加锁的，所以其他事务时可以插入新数据的，这样还是存在幻读的问题，此时间隙锁解决了这个问题，比如下面这张表：
    

• 图中 id 为 8 的记录加了 gap 锁，意味不允许事务在 id 为 8 的记录前的间隙中插入数据，也是(3，8)区间中不能插入数据（这里只针对 insert 操作），如果其他事务向表中插入这区间中的数据，此事务就会阻塞，当前事务提交后，才能进行插入，gap 锁的提出仅仅是为了防止幻影记录而提出的。并且 gap 锁没有 X 和 S 之分所以哪种都行

  • 演示：（这里的区间如果我查询一个大于 20 的数据，右边的范围就是正无穷）
    
  • 上面的间隙锁，会产生死锁的问题，两个事务都对同一个区间加锁，插入的数据也是一样的情况下，就会产生死锁的问题
    

• 临键锁

  • 临键锁是间隙锁和记录锁的合体，有时既想锁住某条数据，又想阻止在某个区间内进行插入。事务隔离级别：可重复读下使用的数据库锁
  • 演示：
    

• 插入意向锁：

  • 上面的间隙锁，当一个事务需要插入一条数据，需要判断有没有其他事务加了 gap 锁，如果有，插入操作需要阻塞等待，但 innoDB 存储引擎会规定事务在等待时候在内存中生成一个插入意向锁，表名有事务想在某个区间内插入数据。插入意向锁不是意向锁（表锁），它是一种间隙锁（行锁）
  • 演示：
    

• 页锁：页锁锁定的粒度介于表锁和行锁之间，并发度一般，会出现死锁。每个层级的锁空间大小是有限的，当超出空间大小，会进行锁升级，占用的空间减小，并发效率降低

• 从对待锁的态度划分：乐观锁、悲观锁（不是锁，是设计思想）

  • 悲观锁：悲观锁总是假设最坏的情况，每次去拿数据都会有人对数据进行了修改，所以每次拿数据时都会上锁，这样别人拿到数据后就会阻塞直到它拿到锁
    • 超卖问题：当一件商品的卖出的数量超过库存的数量就是内存超卖问题
      
    • 悲观锁解决超卖问题：加锁，对查询库存加排他锁，当当前事务提交后，其他事务才能获取到锁进行操作（前提是这三个操作需要放在同一个事务中）
      
    • select …… for update 会在执行过程中给每一行都上锁，所以 mysql 中悲观锁必须确定使用了索引，而不是全表扫描，否则会把整个表都锁住
    • 悲观锁不合适的场景比较多，悲观锁基于锁实现，对数据库开销比较大，对于长事务，这样的开销是无法承受的，所以考虑采用乐观锁
  • 乐观锁：乐观锁认为同一时间并发操作是小概率事件，不会对数据进行上锁，但是会在更新前判断一下期间数据有没有被修改。不使用数据库的锁机制，而是程序本身实现，采用版本号或者CAS机制实现，乐观锁适合读比较多的场景，提高屯出量

    • 乐观锁的版本号机制：在表中设计一个 version 字段，第一次读的时候，会获得 version 的数值，后续对数据进行更新修改时版本号 + 1，此时如果已经有事务对数据进行了修改，修改就会不会成功

    • 乐观锁的时间戳机制：和版本号机制一样，更新提交时比对当前版本号和之前获取到的版本号是否一致，一致进行更新，不一致就不跟新

    • 乐观锁解决超卖问题：

      • 版本号机制：
        

    • 如果是读写分离的表，查询操作是在从机上，更新操作是主机上，如果没有及时的同步会出现一直更新失败的问题，此时需要强制读取主机上的数据，select 放到事务中即可

    • 如果对一条数据频繁的修改，那么会出现一种场景，每次修改只有一个事物更新成功，在业务感知上有大量的失败操作，那么可以这样修改代码：
      

• 按加锁方式划分：显示锁，隐式锁

  • 隐式锁：当事务 1 进行插入操作，还没提交事务，事务 2 此时如果对其进行读写操作会造成，脏读脏写的问题，所以这里会隐式生成一个锁，保护事务 1 插入的数据（受到其他事务影响才会进行加载，延迟加载）
  • 显示锁：通过特定的语句进行加锁

• 全局锁：

  • 对整个数据库中的实例加锁，让整个库处于只读的状态。典型使用场景：数据库逻辑备份

• 死锁：两个或者多个事务在同一资源相互占用，并请求锁定对方占用资源，从而导致恶性循环，死锁实例：
  

  • update 操作数据库会先对每条记录上加 U 锁，然后根据 where 条件为符合条件的记录转换为 X 锁，不满足条件的释放 U 锁。

  • 对于上面的例子，事务1 先更新 id 为 1 的行加 x 锁，事务 2 更新 id 为 2 的行加 x 锁，此时事务 1 再更新事务 2 也需要加 x 锁，此时 id 为 2 的行上已经有 x 锁，需要等待事务 2 的 x 锁释放，所以事务 1 会阻塞等待，事务 2 更新 id 为 1 的行，同样此时 id 为 1 有 事务 1 加的 x 锁，事务 2 阻塞等待事务 1 释放此 x 锁，两个事务都需要对方释放锁，才可以继续向下进行，这就构成死锁

  • 演示：
    

  • 死锁的解决：

    • ①、等待超时：直接等待死锁的超时时间，超时后自动释放
    • ②、发起死锁检测：发现死锁后，主动回滚持有最少行级排他锁事务回滚，让其他事务继续执行，将参数 innodb_deadlock_detect 设置为 on ，表示开启这个逻辑。

• 锁的内存结构：
  

  • 锁所在事务信息：哪个事务产生的锁结构，锁在的事务信息在内存结构中只是一个指针，通过指针可以找到内存中关于事务的其他信息
  • 索引信息：针对于行锁，需要记录一下加锁的记录属于哪个索引，也是指针
  • 表锁 / 行锁信息：
    • 表锁：记录着是对哪个表加的锁，还有一些其他信息
    • 行锁：所在表空间，所以页号，用一个比特位区分哪一条记录加锁

• type_mode ：32位数，分为 lock_mode、lock_type、rec_lock_type

  • lock_mode：IS 锁、IX 锁、S 锁、X 锁、 AUTO-INC锁
  • lock_type：表级锁、行级锁
  • rec_lock_type：（行锁的类型）间隙锁、临键锁、插入意向锁、记录锁

• 其他信息：为了更好的管理系统运行过程中生成的各种锁结构而设计了各种哈希表和链表

• 一堆 bit 位，一个比特位映射到页面一条记录

• 锁监控：

  • 通过 show status like ‘innodb_row_lock%’; 观察锁争夺的情况。information_schema.INNODB_LOCKS查看事务的锁情况，但只能看到阻塞事务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况。

---

13、 多版本并发控制

• 1、什么是 MVCC

• MVCC - 多版本并发控制，MVCC 通过数据行多个版本管理（read view）实现数据库的并发控制，这项技术是的 innoDB 在事务隔离级别下执行一致性读操作有了保证。查询一些正在被另一个事务更新的行，可以看到他们被更新之前的值，不用等到另一个事务释放锁在进行读操作

• 2、快照读：直接读，不加锁（基于 MVCC 实现，很多情况下避免加锁，降低开销）

SELECT * FROM player WHERE ...

• 3、当前读：加锁读，读取记录的最新版本

SELECT * FROM student LOCK IN SHARE MODE;

• 4、事务隔离级别（回顾）
  
  • 针对于 MySQL 事务隔离级别，还可以是如下图：
    
• 5、MVCC 实现原理：MVCC实现依赖于 read view、undo log、隐藏字段

• 聚簇索引记录中都包含两个必要的隐藏列

• 1、trx_id：某条事务对聚簇索引的记录进行修改，都会将事务的 id 赋值给 trx_id，事务的 id 当没有读写操作时 id 默认都为 0，有读写操作，事务 id 为自增序列
• 2、roll_pointer：回滚指针，每次对某条聚簇索引记录进行修改，都会把旧的版本写到 undo 日志中，然后这个列就相当于一个指针，指向这些被旧版本日志
  
  

• 假设之后两个事务id分别为 10 、 20 的事务对这条记录进行 UPDATE 操作，操作流程如下：
  
  
• 随着更新次数的增多，所有版本都会被 roll_pointer 属性连接成一个链表，称之为 undo log 版本链，头结点是版本最新数据

• read view 是什么

• read view 是用来管理历史快照的，read view 管理哪些历史快照对当前的读操作是可见的，read view 和事务是一对一的。read view 是事务使用 MVCC 机制进行快照读产生的读视图，事务启动时，会生成当前数据库系统的一个快照，innoDB 为每个事务构建一个数组，用于记录当前系统中活跃事务的 id（开启没提交）

• MVCC 是针对于 read committed 和 repeatable read 隔离级别下使用的，因为此隔离级别都是需要读取到已经提交的事务修改的记录
• read view 主要包含下面 4 个比较重要的内容：

• 1、creator_trx_id ：创建 read view 的 id
• 2、trx_ids：生成 read view 当前系统中活跃的事务 id
• 3、up_limit_id：当前活跃事务中最小的 id
• 4、low_limit_id：生成 read view 时系统分配给下一个事物的 id，low_limit_id 是系统最大的事务 id ，注意是系统中的事务 id

• read view 规则：

• 1、如果被访问版本的 trx_ids 与 read view 中creator_trx_id 相同，证明当前事务访问自己修改过的，可以访问该版本
• 2、如果被访问的版本的 trx_ids 小于read view 中的 up_limit_id，小于最小的事务 id ，那么证明该版本事务已经提交，该版本可以被访问
• 3、如果被访问的版本的 trx_id 大于或者等于 low_limit_id，证明当前版本事务在当前事务生成 read view 之后开启，该版本不能被当前事务访问
• 4、如果被访问版本的trx_id属性值在ReadView的 up_limit_id 和 low_limit_id 之间，那就需要判断一下trx_id属性值是不是在 trx_ids 列表中
  • 如果在：证明创建 read view 时该版本的事务还是活跃的，该版本不可以访问
  • 不在：可以访问

• MVCC整体操作流程：

  • 1、获取到自己事务的版本号
  • 2、获取 read view
  • 3、查询得到的数据，与 read view 的版本号比较
  • 4、如果不符合 read view 规则，就从 undo log 中获取（去版本链去找）
  • 5、返回符合规范的数据

• read committed：每次 select 都生成一个新的 read view

• repeatable read：每次 select 复用一个 read view

• 实例：

  • READ COMMITTED 隔离级别下：每次读取数据前都生成一个 ReadView。
  • 现在有两个 事务 id 分别为 10 、 20 的事务在执行：（原 id 的 name 为张三）
    
    

• 假设现在有一个使用 READ COMMITTED 隔离级别的事务开始执行执行查询操作，该事务的 id 为 0，此时活跃的事务 id 范围为 [10,20]，creator_trx_id 为 0 ，up_limit_id 为 10，low_limit_id 为 21，此时不在活跃事务 id 中只有事务编号为 8 的记录，所以这里查询到的是 “张三”

• 之后，我们把 事务id 为 10 的事务提交一下，然后再到 事务id 为 20 的事务中更新一下表 student 中 id 为 1 的记录：
  
  
  此时活跃事务 id 范围为 [20]，creator_trx_id 为 0，up_limit_id 为 20，low_limit_id 为 20，此时可以读到的就是 “王五”

• REPEATABLE READ 隔离级别下： REPEATABLE READ隔离级别下 每次读操作会复用同一个 read view

• 比如，系统里有两个 事务id 分别为 10 、 20 的事务在执行：
  
  

• 此时再开启一个事务来进行读操作，creator_trx_id 为 0，活跃事务 id 范围为 [10，20]，up_limit_id 为 10，low_limit_id 为 21，张三的 trx_id = 8 没有在活跃事务的 id 范围中，这里查询到 “张三”

• 之后，我们把 事务id 为 10 的事务提交一下，就像这样
  

• 然后再到 事务id 为 20 的事务中更新一下表 student 中 id 为 1 的记录：
  

• 由于是在 REPEATABLE READ 隔离级别下，所以 read view 只有首次读操作时生成，之后的读一直复用此 read view，再次进行读：creator_trx_id 为 0，活跃事务 id 范围为 [10，20]，up_limit_id 为 10，low_limit_id 为 21，所以此时读到的还是 “张三” 这条记录，这就避免了不可重复读的问题，两次读到的内容一致

• 如何避免幻读问题：

  • 假设表中只有一条记录：
    
  • 假设现在有事务 A 和事务 B 并发执行， 事务 A 的事务 id 为 20 ， 事务 B 的事务 id 为 30
  • 事务 A 开始第一次查询数据，查询的 SQL 语句如下
    
  • 在开始查询之前，MySQL 会为事务 A 产生一个 ReadView，此时 ReadView 的内容如下： trx_ids = [20,30] ， up_limit_id=20 ， low_limit_id=31 ， creator_trx_id= 20 , 此时事务 A 可以读到 “张三” 这条记录
  • 接着事务 B(trx_id=30)，往表 student 中新插入两条数据，并提交事务。
    
  • 此时表student 中就有三条数据了，对应的 undo 如下图所示：
    
  • 接着事务 A 开启第二次查询，根据可重复读隔离级别的规则，此时事务 A 并不会再重新生成
    ReadView。此时表 student 中的 3 条数据都满足 where id>=1 的条件，因此会先查出来。然后根据ReadView 机制，判断每条数据是不是都可以被事务 A 看到。
  • “张三” 这条记录可以读出来，后面插入的两条记录的事务 id 都在活跃事务 id 范围中，所以当 read view 生成时，id 为 30 的事务还未提交，所以不能读。所以这里事务 A 还是只能读到 “张三” 的记录，这样就避免了幻读的问题

• 总结：

  • 1、MVCC 使得不同事务的读写操作并发执行，提高效率
  • 2、避免死锁问题
  • 3、在 READ COMMITTED 和 REPEATABLE READ 两种隔离级别下，read view 生成的时机时不同的，前者每次读操作都会生成一个 read view，后者只有初次读操作会生成一个 read view，后面的读都是复用此 read view

---

14、 其他数据库日志

• MySQL 有不同的日志文件，用来存储不同类型的日志：慢查询日志，二进制日志，错误日志，通用查询日志，MySQL 8.0 新增中继日志，数据库定义日志

  • 慢查询日志：记录超过 long_query_time 的语句
  • 通用查询日志：记录所有连接的起始时间和结束时间，以及连接发送给数据库服务器的所有指令
  • 错误日志：记录数据库服务器启动，运行，停止 mysql 服务器出现的问题
  • 二进制日志：记录所有的更改数据语句，可以用于主从复制，以及服务器遇到故障无损修复
  • 中继日志：用于主从复制
  • 数据定义语句日志：记录数据定义语句执行的元数据操作

• 除二进制文件，其他文件都是文本文件，所有的日志都创建于 MySQL 数据目录中

• 日志的弊端：降低 MySQL 性能（MySQL 需要一边运行一边记录日志），日志占用大量磁盘空间

• 通用查询日志：查看通用查询日志，还原操作时的具体场景。

  • 开启通用查询
    
    
  • 经过一些列数据库操作后，再次查看通用日志，可以发现刚刚的语句都被记录在日志中
    
  • 如果数据的使用非常频繁，那么通用查询日志会占用服务器非常大的磁盘空间。数据管理员可以删除很长时间之前的查询日志，以保证MySQL服务器上的硬盘空间。
  • 停止日志：SET GLOBAL general_log=off;
    

• 错误日志：默认情况下错误日志是开启的，错误日志文件名称为：mysqld.log

  • 查看日志：SHOW VARIABLES LIKE ‘log_err%’;这里看到错误日志的名称和位置
    
  • 对于很久以前的错误日志，数据库管理员查看这些错误日志的可能性不大，可以将这些错误日志删除，以保证MySQL服务器上的 硬盘空间 。MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以直接删除 。（补充操作：install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log）

• 二进制日志(bin log)

  • 查询二进制事务的开启状态：mysql> show variables like ‘%log_bin%’;
    
  • 查看日志：
    • 当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件
    • MySQL服务 重新启动一次 ，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。
    • 通过指令：mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002“ 可以看到之前对数据表进行的更新操作
      
  • 使用日志恢复数据：
  • 通过语句：mysqlbinlog [option] filename|mysql –uuser -ppass;使用 mysqlbinlog 命令来读取filename中的内容，然后使用mysql命令将这些内容恢复到数据库中。
  • filename 是日志文件名
  • option ：比较重要的两个参数：
    

• 删除二进制日志：

PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

• 二进制日志的写入时机

• 事务执行过程中先把日志写到 binlog cache 中，然后在事务提交时，再把日志从 binlog cache 中写入到 binlog 文件中
  

• binlog 与 redolog 对比

  • 1、redolog 是物理日志，记录的内容是某个数据页上做了什么操作，会记录页号，偏移量，变更的操作，属于 innoDB 存储引擎生成的
  • 2、binlog 是逻辑日志，记录变更的 sql 语句，属于 MySQL Server 层

• 两阶段提交：

  • 在执行更新语句时，会记录 binlog 和 redolog 两块日志，以事务为基本单位，redolog 是在事务执行过程中不断写入，binlog 只是在事务提交时写入，二者写入的时机不同
  • 写入时机不同会造成的问题
    
    

• binlog 写入失败，从机中没有读到更新后的数据，主机中为最新值，会造成主从不一致的情况

• 采取两阶段提交解决问题：
  

• 中途发生异常后回滚事物：
  

• 另一个场景，redo log设置commit阶段发生异常，那会不会回滚事务呢？
  

• 并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare 阶段，但是能通过事务id找到对应的 binlog 日志，所以 MySQL 认为是完整的，就会提交事务恢复数据。

• 中继日志：

  • 中继日志只是在主从架构的从服务器上存在
    
    • 主机上进行写操作，此时主机上的数据修改了，需要进行主从复制，保证主从一致，从机要从主服务器上读取二进制日志的内容，把读取到的内容写入本地的日志文件中，这个从机的日志文件就是中继文件。然后读取从机中的中继日志，对服务器的数据进行更新，完成主从复制

---

15、主从复制：

如何提升数据库并发能力：

• 此外，一般对于数据库而言是读多写少的，所以可以考虑主从架构，读写分离的方式，提高并发能力，但并不是所有的应用都需要对数据库进行主从架构设置，其还是有成本的

• 我们目的是提高数据库的并发能力，首先要考虑的是优化 sql 和 索引，其次是缓存策略，最后才是考虑主从架构

• 主从架构的作用：

  • 1、读写分离
    
  • 2、数据备份
  • 3、具有高可用性

• 主从复制的原理：从机会从主机中的 bin log 来进行数据同步

  • 三个线程：
    
  • Master 将写数据记录到 bin log 中
  • Slave 将 Master 的 bin log events 同步到自己的中继日志中
  • Slave 重做中继日志中的事件，将改变同步到自己的数据库中，MySQL 的复制是异步的且串行化的，而且重启后从接入点开始复制

• 复制的问题：延时，从机需要 200ms 去进行同步，但是在 50 ms 处进行读操作，读到就是修改前的数据，这就造成问题，具体如何解决，下面说

• 复制的基本原则：

  • 只能有一个主机
  • 一个主机可以有多个从机
  • 一个从机对应唯一的服务器 ID

• 同步数据一致性问题

  • 主从同步的要求：
  • 读库和写库必须一致
  • 写数据必须写到写库
  • 读数据不一定到读库

• 如何降低主从延迟

  • 1、sql 调优，避免慢 sql，减少批量操作
  • 2、降低大事务并发的概率，优化逻辑结构
  • 3、提高从库配置
  • 4、尽量采用短的链路
  • 5、实时性要求的业务走主机，从机做备份、灾备

• 如何解决一致性问题：（一致性越好，并发性越差 ）

  • 1、异步复制

    • 主机将事件写入到 binlog 中，自身不知道从机是否接受

  • 2、半同步复制
    

    • 半同步复制会有一步转储并发送 binlog 到从机 然后等待 从机的一个 ACK，主机可以知道从机有没有接收到主机的 binlog，（只要保证 1 个写入成功，就可以进行后续的操作）。半同步复制是通过判断从库响应的个数来决定是否返回给客户端，5.7 之后通过一个参数，调大这个参数可以保证多个从机写入成功后，再进行后续操作，这样一致性就提高了，但是并发性就降低了

  • 3、组复制

    • 如果对一致性要求比较高，需要使用到 组复制 的方法，需要使用到 MGR 算法
    • 读读事务可以直接提交
    • 读写事务，需要通过一致性协议层同意，也就是读写事务想要提交，需要同意的结点数大于 N/2 + 1，才能进行提交，而不是发起方说的算。在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了原子消息和全局有序消息，从而保证组内数据的一致性

---

16、数据库迁移

数据库迁移，将数据从一个计算机存储系统中永久的传输到另一个计算机存储系统中的过程。迁移的方案大致分为物理迁移和逻辑迁移，通常以尽可能自动化的方式执行

• 物理迁移：适用于大数据量下的整体迁移，使用物理迁移比较快。但是需要 MySQL 的版本和配置一样，物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。不同服务器之间可以采用物理迁移，在新的服务器上安装好相同的数据库软件，创建好相同的目录，配置也需要一样然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库
• 逻辑迁移：适用的范围更广，无论是 部分迁移 还是 全量迁移 ，都可以使用逻辑迁移。逻辑迁移中使用最多的就是通过 mysqldump 等备份工具