MySQL优化
一、MySQL逻辑架构
1.1 MySQL物理分层 （分层设计、逐层解耦）
- 最上层的服务并不是Mysql独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构
- 第二层架构中，大多数的MYSQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（日期时间等），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等
- 第三层包含了存储引擎。存储引擎负责Mysql中的数据的存储和提取。Mysql支持各种不同的存储引擎，每个存储引擎都有它的优势和劣势。服务器通过API和存储引擎进行通信。这些API接口屏蔽了不同存储引擎之间的差异，使的这些差异对上层的查询过程透明。存储引擎不会解析SQL（InnoDB是个例外，它会解析外键定义），不同的存储引擎之间也不能互相通信，而只是简单的响应上层服务器的请求
1.2 存储引擎
1.2.1 什么是存储引擎？
数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。
1.2.2 MySQL支持的存储引擎
mysql支持的存储引擎有，InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE 等。 其中MyISAM是5.5之前的默认存储引擎，InnoDB是5.5以及之后的默认存储引擎
1.2.3 MyISAM vs InnoDB
1.2.4 如何选择存储引擎？
1）事务
如果应用需要事务支持，那么InnoDB是目前最稳定并且经过验证的选择。如果不需要事务，并且主要是select和insert操作，则MyISAM是个不错的选择，一般日志型应用比较符合这一个特性
2）备份
备份的需求也会影响存储引擎的选择。如果需要在线热备份，则选择InnoDB就是基本的要求。 热备份 温备份 冷备份
3）崩溃恢复
数据量比较大的时候，系统崩溃后如何快速恢复是一个需要考虑的额问题。相对而言，MyISAM崩溃后发生损坏的概率比InnoDB高的多，而且恢复也慢，因此即使不需要事务，通常也应该选择InnoDB引擎
PS
实际开发过程中，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。如果无法确定的时候，通常InnoDB是个不错的选择。
二、数据类型优化
Mysql支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。
2.1 数据类型的设计原则
1）更小的通常更好
通常情况下，应该选择可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更小的磁盘、内存和CPU缓存。但是要确保没有低估需要存储值得范围，在MYSQL中的多个地方增加数据类型范围是一个非常耗时和痛苦的操作。
2）简单就好
简单数据类型的操作通常需要更少的CPU消耗。比如，整型比字符操作代价更低
3）尽量避免null
通常情况下最好执行列为NOT NULL，除非真的需要存储NULL值。如果查询总包含可能为NULL的列，对于MySQL来说更难优化，因为可为NULL的列使的索引、索引统计等都变的更加复杂。通常把字段设置为NOT NULL带来的性能提升比较小，但是如果计划在列上创建索引，就应该尽量避免设计成可为NULL的列。
2.2 字符串类型
· varchar
varchar用于处处可变长的字符串，是最常见的字符串数据类型。它比定长更节省空间。varchar需要使用1~2个额外字节记录字符串的长度。varchar节省了存储空间，所以对性能也有帮助，但是由于长度可变，在update时可能使行变得比原来更长，这就导致需要进行额外的工作。至于如何进行空间增长取决于不同的存储引擎。当字符串列的最大长度比平均长度要大很多，并且列的更新很少时比较适合使用varchar。
· char
定长字符串，mysql总是根据定义的字符串长度分配足够的空间。char非常适合存储很短的字符串，或者值得长度都很接近的字段。例如char非常适合存放密码的md5值，因为这是一个定长的值。对于经常变更的字段，使用char也更为合适，因为定长的char类型不容易产生碎片。对于非常短的列，存储空间也更有优势，比如char(1)只会占用一个字节，而varchar(1)会用到两个字节，因为还有一个字节用来记录varchar的长度
注意
varchar(5)和varchar(200)存储'hello'的空间开销是一样的。但是varchar(5)对性能提升有很大的优势。更长的列会消耗更多的内存，因为mysql通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序等操作时会特别糟糕。所以最好的策略是只分配真正需要的空间
2.3 数值类型
· 整数型
**tinyint **8位 - 1字节 （-128 ~ 127）
**smallint **16位 - 2字节
**mediumint **24位 - 3字节
**int **32位 - 4字节 (-20亿 ~ 20亿)
**bigint **64位 - 8字节
· 浮点型
**float **32位 - 4字节
**double **64位 - 8字节
· 精准浮点类型
**decimel **需要额外的空间和计算开销，所以应该尽量只对小数进行精准计算时才使用decimel，例如存储金额
· unsigned属性
添加unsigned属性表示该字段不允许负数，正数的上限大致可以提高一倍。比如tinyint unsigned可以存储0
255的范围。而tinyint是-128127的范围。有符号和无符号占用空间大小相同，具有相同的性能。
注意
int(11)是指定整数类型的宽度，它不会限制值得合法范围，对于存储和计算而言，int(1)和int(20)没什么区别
2.4 日期类型
· datetime
能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到YYYYMMDDHHMMSS的整数中，使用8个字节的存储空间。
· timestamp
保存了从1970年1月1日以来的毫秒数，timestamp只使用了4个字节的存储空间，因此它的范围比datetime小的多；但是只能表示从1970年到2038年。另外timestamp也依赖于时区。
2.5 特殊类型
· ip地址
很多人通常使用varchar(15)存储ip地址，但是其实IP地址本质上是一个无符号4字节的整数。完全可以使用int unsigned类型存储，在读写方面性能会提升很多。而且MySQL提供了inet_aton和inet_ntoa函数进行整数和四段字符之间的转换
三、索引优化
3.1 索引概述
3.1.1 什么是索引？
索引是存储引擎用于快速找到记录的一种数据结构。 索引对于良好的性能非常关键。尤其当表中的数据量越来越大时，索引对性能的影响越发重要。索引应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级。但是索引经常被忽略，不恰当的索引对性能可能还会带来负面效果
3.1.2 为什么索引能够提升查询效率？
DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中快速定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。
添加索引的话，首先去索引列表中查询，而我们的索引列表是B类树的数据结构，查询的时间复杂度为O(log2N)，定位到特定值得行就会非常快，所以其查询速度就会非常快。
什么时候适合添加索引？
- 主键自动建立主键索引(唯一索引)
- where子句中的列，频繁作为查询字段的列
- 表连接关联的列
- 排序用到的列
- distinct所使用的列
- 列的基数越大(选择性大)，索引的效率就越高
什么时候不适合添加索引？
- 表记录太少
- 频繁修改的字段
- 数据重复且分布平均的字段
3.1.3 索引分类
- 单列索引 - 即一个索引只包含单个列，一个表可以有多个单列索引
- 复合索引（多列索引）- 即一个索引包含多个列
- 唯一性索引 - 索引列的值必须唯一，但是允许有空值
- 全文索引 - 索引的是内容中的关键词，用于全文检索
- 主键索引
3.1.4 索引相关命令
创建索引
create [unique|fulltext] index 索引名 on 表名 (属性名[长度][asc|desc]);
删除索引
drop index 索引名 on 表名;
查看索引
show index from 表名;
3.2 索引底层原理 - (B-Tree)
3.2.1 为什么要选择B-Tree？
查找类的数据结构有很多，比如哈希表、二叉搜索树、红黑树等。
- 哈希表 - 定位搜索速度极快，查询时间复杂度为O(1)，但是不支持范围查询。
- 二叉搜索树 - 支持定位查询，也支持范围搜索，但是树节点可能失衡，导致查询性能严重下降。
- 红-黑树 - 特殊的平衡二叉搜索树，查询时间复杂度为O(log2N)。但是在大规模数据存储中，会导致红-黑树结构由于树的深度过大而造成磁盘I/O读写过于频繁，进而导致查询效率低下。
磁盘的操作费时费力，如果过于频繁的多次查找势必效率低下。那么如何提高效率，即如何避免磁盘过于频繁的多次查找呢？根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，就可以有效避免频繁的磁盘I/O，而B-Tree就复合这样的条件。
B-Tree - 意为平衡（Balance）树，是专门为了磁盘存储结构而设计的一种多路平衡树，非常符合索引的各项要求。
3.2.2 磁盘结构
盘片中一圈圈灰色同心圆为一条条磁道，从圆心向外画直线，可以将磁道划分为若干个弧段，每个磁道上一个弧段被称之为一个扇区。扇区是磁盘的最小组成单元，通常是512字节。
盘片装在一个主轴上，并绕主轴高速旋转，当磁道在磁头下通过时，就可以进行数据的读 / 写了。
活动头盘的磁头是可移动的。每一个盘面上只有一个磁头(磁头是双向的，因此正反盘面都能读写)。它可以从该面的一个磁道移动到另一个磁道。所有磁头都装在同一个动臂上，因此不同盘面上的所有磁头都是同时移动的。
当盘片绕主轴旋转的时候，磁头与旋转的盘片形成一个圆柱体。各个盘面上半径相同的磁道组成了一个圆柱面，我们称为柱面 。因此，柱面的个数也就是盘面上的磁道数。
由于扇区数目众多在寻址时比较困难，所以操作系统就将相邻的扇区组合在一起，形成一个块，再对块进行整体的操作，即块是操作系统中最小的逻辑存储单元。这样可以使操作系统忽略底层物理存储结构的设计。磁盘块是操作系统自己“杜撰”的，是一个虚拟的概念。其大小由操作系统决定，通常一个块 = 单个扇区大小 * 2的n次方（通常是4KB），其中n是可修改的。
磁盘读写的原理
- 首先移动动臂根据柱面号使磁头移动到所需要的柱面上，这一过程被称为寻道。
- 所有磁头都定位到了各个盘面的对应磁道上。这时根据盘面号来确定指定盘面上的磁道。
- 盘面确定以后，盘片开始旋转，将指定块号的扇区移动至磁头下，就可以读写数据了。
磁盘读取的响应时间
- 寻道时间：磁头从开始移动到数据所在磁道所需要的时间，寻道时间越短，I/O操作越快，目前磁盘的平均寻道时间一般在3－15ms，一般都在10ms左右。
- 旋转延迟：盘片旋转将请求数据所在扇区移至读写磁头下方所需要的时间，旋转延迟取决于磁盘转速。普通硬盘一般都是7200rpm，旋转一圈大约0.0083s，慢的有5400rpm。
- 数据传输时间：完成传输所请求的数据所需要的时间，一般传输一个字节(byte)大概0.02us。
总结
- 磁盘读取数据是以盘块为基本单位的。位于同一盘块中的所有数据都能被一次性全部读取出来。而磁盘IO代价主要花费在寻道时间上。
- 应该尽量将相关信息存放在同一盘块、同一磁道中，或者至少放在同一柱面或相邻柱面上，以求在读/写信息时尽量减少磁头来回移动的次数，避免过多的寻道时间
- 当读取第一个block时,要经历寻道,旋转延迟,传输三个步骤才能读取完这个block的数据.而对于下一个block,如果它在磁盘的某个位置,访问它会同样经历寻道,旋转,延时,传输才能读取完这个block的数据, 我们把这种方式的IO叫做随机IO.但是如果这个block的起始扇区刚好在我刚才访问的block的后面,磁头就能立刻遇到.不需等待,直接传输.这种IO就叫顺序IO.
3.2.3 B-Tree
B 树是为了磁盘或其它存储设备而设计的一种多路平衡查找树。与红黑树很相似，但在降低磁盘I/0操作方面要更好一些。许多数据库系统的索引一般都采用B-Tree或者B-Tree的各种变种结构作为底层数据结构。
3.2.3 B+Tree
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，MyISAM和InnoDB存储引擎底层就是用B+Tree实现其索引结构。
B+Tree相对于B-Tree有几点不同：
- 非叶子节点只存储键值信息。
- 所有叶子节点之间都有一个链指针。
- 数据记录都存放在叶子节点中。
B+Tree 相对于 BTree的优势：
- 所有的数据最终都会出现在叶子节点上，并且叶子节点之间通过双向链表连接，极大方便了范围查询
- 所有的非叶子节点不存储数据，数据全部存在叶子节点上，极大的减少了非叶子节点的数量体积，可以在一个节点上，存放更多的键值，在磁盘块体积不变的情况下，B+Tree的层级可以更低
劣势：
- B+Tree要找一个数据必须要定位到叶子节点，可能会有更多的磁盘io
/*创建人员表*/
create table people(
id int primary key auto_increment,
last_name varchar(20) not null,
first_name varchar(20) not null,
birthday date not null,
sex int not null
);
/*创建索引*/
index(last_name, first_name, birthday)
3.3 聚簇索引（聚集索引）
- MyISAM的主索引和二级索引都指向该数据在磁盘的位置。实际上在MyISAM中主键索引和其他的二级索引没有什么区别。
- InnoDB的主索引的节点与数据放在一起，二级索引的节点存放的是主键的位置。这种主键索引方式称之为聚簇索引。
- InnoDB默认会将表的主键作为聚簇的列；如果没有主键，则会选择第一个非空的唯一索引代替；如果没有非空唯一索引，会隐式定义一个6字节的rowid主键来作为聚集索引。
- 在InnoDB中，不要使用UUID来作为聚集索引，否则性能会很糟糕，因为它使得聚集索引的插入变得完全随机，使得数据没有任何聚集特性。
3.4 覆盖索引
覆盖索引并不是一种新的索引类型，而是一个种查询工具。简单来说，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表（回表）中读取行了。一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。
覆盖索引的优点
- 索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
- 因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
- 一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。
- 覆盖索引对innodb表特别有用，innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。
3.5 哈希索引
- MySQL中只有memory存储引擎才支持哈希索引
- InnoDB或者MyISAM都不显示支持哈希索引
- InnoDB支持“自适应哈希索引”，也就是说InnoDB某些情况下会在内部通过自动创建哈希索引来提升效率
- 查看自适应哈希索引的命令：
- show variables like '%innodb_adaptive_hash_index%'
3.6 索引失效及解决方案
- 索引字段不独立（条件的左侧列在使用了表达式或者函数）
- 尽量将计算的结果提前算好再传递到sql中，或者使用其他等价的sql
- 使用了左模糊查询
- like 字段前面不要添加%，尽量使用like 'a%'的形式
- 使用ES全文检索，代替like '%a%'的场景
- 使用or查询
- 将or的条件列都添加索引，高版本mysql会采用索引合并的方式优化查询
- 字段内部采用了隐式转换
- 搜索参数和数据库字段尽量保证类型一致
- 不符合最左前缀查询
- 根据SQL的条件顺序，调整多列索引的顺序
四、SQL优化
4.1 慢查询日志
4.1.1 什么是慢查询日志？
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。 默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数， 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。 慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
4.1.2 慢查询相关操作
/*查看慢查询日志：*/
select @@slow_query_log;
/*设置慢查询日志的输入位置
FILE - 文件
TABLE - 表(mysql.slow_log)
*/
set global log_output = "FILE,TABLE"
/*开启慢查询*/
SET global slow_query_log=1;
/*查询慢查询次数*/
show status like 'slow_queries';
/*查看阈值*/
show variables like 'long%'
/*设置阈值：*/
set long_query_time=0.001;
/*打开mysql核心配置文件 - my.cnf*/
[mysqld]
slow_query_log = 1
long_query_time = 0.001
/*重启mysql服务*/
systemctl restart mysqld
4.1.3 分析慢查询日志
基于数据库记录
/*执行sql*/
select * from mysql.slow_log
从左到右分别的含义：
- **start_time **- 执行时间
- **user_host **- 主机名
- **query_time **- 执行耗时
- **lock_time **- 持有锁的时间
- **rows_sent **- 查询返回记录行的数量
- rows_examined - 查询扫描记录行的数量
- **db **- 操作的数据库
- **sql_text **- 执行的SQL语句内容
- **thread_id **- 执行线程id
4.2 执行计划 - Explain
4.2.1 什么是执行计划？
执行计划可以模拟Mysql优化器执行SQL查询语句，从而了解Mysql是如何处理被执行的SQL语句的。可以帮助程序员分析和了解这条SQL语句的性能瓶颈。
语法
/*查看sql语句的执行计划*/
explain sql语句;
4.2.2 执行计划字段详解
id - 主要是用来标识sql执行顺序
- id相同的情况，没有子查询时会出现，执行顺序也是从上到下。
- id不同的情况，有子查询时，则每个查询会有不同的id，并且依次递增。id值越大，表示优先级越高，越先被执行。
select_type - 标识每个查询的类型
- SIMPLE - 简单查询，查询中不包括子查询或者UNION
- PRIMARY - 查询中如果包含子查询，则外层查询被标记PRIMARY
- DERIVED - 在from后面包含的子查询会被标记为DERIVED，MySQL会递归执行这些子查询，将结果放到临时表中。
- SUBQUERY - 在SELECT或者WHERE后面包含的子查询
- UNION - 跟在UNION关键字后面的查询会被标记为UNION
- UNION_RESULT - 从UNION表获得结果的查询
type - 表示访问类型，换而言之就是Mysql查找表中行的方式
以下排列性能从差到好：
- **all **- 全表扫描的方式查询所有的数据行
- index - 全索引扫描的方式查找数据行，也是遍历数据，但是是走索引树的方式进行遍历
- **range **- 索引范围查询，表示一个范围有限制的索引扫描
- index_merge：表示使用了索引合并优化，一个查询中用到了多个索引
- ref_or_null - 该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL，常见于is null条件查询
- fulltext - 全文索引
- **ref **- 非唯一性索引扫描，表示一个匹配单独值域的索引扫描，只会出现在非唯一性索引的字段查询上
- eq_ref - 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。通常在唯一性索引/主键索引 + 关联查询时出现。
- **const **- 表示通过索引一次就找到数据。通常出现在唯一性索引或者主键索引中
- system - 表示表数据只有一行记录，这是const类型的特例，通常不会出现。
- **null **- 这种方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或索引，往往意义不大。
**possible_keys **- 表示可能用于这条查询的索引，不一定真的用上。 **key **- 实际上使用的索引，如果为null表示查询没有用上任何索引。 **key_len **- 使用索引的长度。 ref - 索引哪一列被使用了。 **rows **- 表示MySQL预估为了查找所需的记录而需要读取的行数，他不一定等于最终从表里读取出来的行数，这个值越小越好。 **filtered **- 表示符合查询条件的数据百分比，最大为100，越小越好。
- 使用rows * filtered可获得和下一张表连接的行数（估值）
**Extra **- 关于MYSQL如何解析查询的额外信息
- Using filesort - 说明mysql会对查询的数据结果进行一个外部的额外排序（文件排序），有可能基于内存，也有可能基于硬盘，当SQL中包含Order By操作，而且没办法通过索引完成排序时，就会出现该信息。
- Using temporary - 说明mysql使用临时表保存中间结果，常见于排序或者分组。
- Using index - 说明查询中用上了覆盖索引。
- Using where - 表示使用了where过滤条件。
4.2.3 执行计划的额外信息
/*执行计划的额外信息，其中会显示Mysql优化器优化之后执行的SQL语句*/
show warnings;
特殊标记：
- 自动生成的临时表key - 子查询被转换为了exists查询 - 使用索引查询来处理查询片段，从而找到合格的行 (condition, expr1, expr2) - 如果条件成立，取expr1，否则取expr2 - 验证表达式不为null的测试 - 使用子查询实现 - 表示创建了内部临时表而缓存中间结果
4.3 优化器追踪 - Optimizer_Trace
4.3.1 概述
- 主要用来跟踪MySQL优化器做出的各种决策
- 可以详细了解优化的执行细节
- 理解SQL的执行过程进而更好的优化SQL
4.3.2 optimizer_trace相关参数
- optimizer_trace - 控制optimizer_trace的开关，默认值enabled=off,one_line=off
- **enable **- 是否开启optimizer_trace
- one_line - 是否开启单行存储，如果开启会节省空间，如果关闭，将会按照标准json格式化存储
- **optimizer_trace_features **- 设置可以跟踪的内容
- 默认值：greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
- greedy_search：跟踪贪心搜索
- range_optimizer：跟踪范围优化器
- dynamic_range：跟踪动态范围优化
- repeated_subselect：跟踪子查询
- optimizer_trace_limit - 控制optimizer_trace展示多少条结果，默认1
- **optimizer_trace_max_mem_size **- optimizer_trace堆栈信息的最大容量，默认1048576
- **optimizer_trace_offset **- 控制要记录的optimizer_trace的偏移量，默认-1，表示记录最近的一条SQL语句
4.3.3 optimizer_trace的相关命令
set optimizer_trace = "enabled=on",end_markers_in_json=on; - 开启optimizer_trace **set global optimizer_trace = "enabled=on",end_markers_in_json=on; **- 全局开启optimizer_trace select * from information_schema.optimizer_trace; - 查看optimizer_trace记录的结果
4.3.4 optimizer_trace结果分析
{
"steps": [
//-----SQL的准备阶段------
{
"join_preparation": {
//正在跟踪分析第几个select，1表示第一个查询语句
"select#": 1,
"steps": [
{
//格式化了原SQL语句，并且补充了原语句中隐式的库、表、列名
"expanded_query": "/* select#1 */ select `d`.`dept_no` AS `dept_no`,`d`.`dept_name` AS `dept_name`,`de`.`emp_no` AS `emp_no`,`de`.`dept_no` AS `dept_no`,`de`.`from_date` AS `from_date`,`de`.`to_date` AS `to_date` from (`departments` `d` join `dept_emp` `de` on((`de`.`dept_no` = `d`.`dept_no`))) where (`d`.`dept_no` = 1) limit 0,1000"
},
{
//连接查询转换，将join的on条件语句转成whare条件语句
"transformations_to_nested_joins": {
"transformations": [
"JOIN_condition_to_WHERE",
"parenthesis_removal"
] /* transformations */,
"expanded_query": "/* select#1 */ select `d`.`dept_no` AS `dept_no`,`d`.`dept_name` AS `dept_name`,`de`.`emp_no` AS `emp_no`,`de`.`dept_no` AS `dept_no`,`de`.`from_date` AS `from_date`,`de`.`to_date` AS `to_date` from `departments` `d` join `dept_emp` `de` where ((`d`.`dept_no` = 1) and (`de`.`dept_no` = `d`.`dept_no`)) limit 0,1000"
} /* transformations_to_nested_joins */
}
] /* steps */
} /* join_preparation */
},
//-----SQL的优化阶段------
{
"join_optimization": {
//正在跟踪的第几个select，1表示第一个查询语句
"select#": 1,
"steps": [
{
//条件语句处理，该步骤对where条件语句进行优化处理
"condition_processing": {
//表示当前优化的条件对象类型，WHERE或者HAVING语句
"condition": "WHERE",
//表示优化前的条件语句
"original_condition": "((`d`.`dept_no` = 1) and (`de`.`dept_no` = `d`.`dept_no`))",
"steps": [
{
//优化步骤1：等值条件语句转换
"transformation": "equality_propagation",
"resulting_condition": "((`d`.`dept_no` = 1) and multiple equal(`de`.`dept_no`, `d`.`dept_no`))"
},
{
//优化步骤2：常量条件语句转换
"transformation": "constant_propagation",
"resulting_condition": "((`d`.`dept_no` = 1) and multiple equal(`de`.`dept_no`, `d`.`dept_no`))"
},
{
//优化步骤3：无效条件移除转换
"transformation": "trivial_condition_removal",
"resulting_condition": "((`d`.`dept_no` = 1) and multiple equal(`de`.`dept_no`, `d`.`dept_no`))"
}
] /* steps */
} /* condition_processing */
},
{
//替换虚拟生成列
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
//梳理表之间的依赖关系
"table_dependencies": [
{
//表的名称和别名
"table": "`departments` `d`",
//列是否允许为空，不是指原表中的列是否允许为空，而是指join之后的列是否允许为null，比如进行left join，则最后一张表row_may_be_null就会为true
"row_may_be_null": false,
//表的映射编号，从0开始递增
"map_bit": 0,
//依赖的映射表，这里主要是当使用left join等有顺序差别时，会在这里显示前置表的map_big值
"depends_on_map_bits": [
] /* depends_on_map_bits */
},
{
"table": "`dept_emp` `de`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
//如果优化器认为查询可以使用ref,则再这里列出可以使用的索引
"ref_optimizer_key_uses": [
{
"table": "`departments` `d`",
"field": "dept_no",
"equals": "`de`.`dept_no`",
"null_rejecting": true
},
{
"table": "`dept_emp` `de`",
"field": "dept_no",
"equals": "`d`.`dept_no`",
"null_rejecting": true
}
] /* ref_optimizer_key_uses */
},
{
//【重要】 - 估算表行数和扫描代码，如果查询中存在range，对range扫描进行计划分析及代价估算
"rows_estimation": [
{
//表名
"table": "`departments` `d`",
"range_analysis": {
//全表扫描的成本统计
"table_scan": {
//全表扫描的行数
"rows": 9,
//全表扫描的成本
"cost": 4
} /* table_scan */,
//该阶段会列出表中所有的索引并分析是否可用
"potential_range_indexes": [
{
//索引名称
"index": "PRIMARY",
//是否可用
"usable": true,
//索引中可用的列字段
"key_parts": [
"dept_no"
] /* key_parts */
},
{
"index": "dept_name",
"usable": false,
"cause": "not_applicable"
}
] /* potential_range_indexes */,
//最好的覆盖索引扫描
"best_covering_index_scan": {
//索引的名称
"index": "dept_name",
//成本
"cost": 2.07021,
//是否选择
"chosen": true
} /* best_covering_index_scan */,
"setup_range_conditions": [
] /* setup_range_conditions */,
//评估在使用过了group by或者 distinct的是否是否有合适的索引可用
"group_index_range": {
//chosen: false表示语句中没有group by或者distinct
"chosen": false,
"cause": "not_single_table"
} /* group_index_range */,
"skip_scan_range": {
"chosen": false,
"cause": "not_single_table"
} /* skip_scan_range */
} /* range_analysis */
},
{
"table": "`dept_emp` `de`",
"range_analysis": {
"table_scan": {
"rows": 331143,
"cost": 363920
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "dept_no",
"usable": true,
"key_parts": [
"dept_no",
"emp_no"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
} /* group_index_range */,
"skip_scan_range": {
"chosen": false,
"cause": "not_single_table"
} /* skip_scan_range */
} /* range_analysis */
}
] /* rows_estimation */
},
{
//对比各个可行计划的成本，选择相对最优的执行计划
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`departments` `d`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 9,
"filtering_effect": [
] /* filtering_effect */,
"final_filtering_effect": 0.111111,
"access_type": "scan",
"resulting_rows": 1,
"cost": 1.9,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 1.9,
"rest_of_plan": [
{
"plan_prefix": [
"`departments` `d`"
] /* plan_prefix */,
"table": "`dept_emp` `de`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "dept_no",
"rows": 41392.9,
"cost": 6348.02,
"chosen": true
},
{
"rows_to_scan": 331143,
"filtering_effect": [
] /* filtering_effect */,
"final_filtering_effect": 1,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 331143,
"cost": 33851,
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 41392.9,
"cost_for_plan": 6349.92,
"chosen": true
}
] /* rest_of_plan */
},
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`dept_emp` `de`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "dept_no",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 331143,
"filtering_effect": [
] /* filtering_effect */,
"final_filtering_effect": 1,
"access_type": "scan",
"resulting_rows": 331143,
"cost": 33850.5,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 331143,
"cost_for_plan": 33850.5,
"pruned_by_cost": true
}
] /* considered_execution_plans */
},
{
//添加附加条件，使得条件尽可能筛选单表数据
"attaching_conditions_to_tables": {
"original_condition": "((`de`.`dept_no` = `d`.`dept_no`) and (`d`.`dept_no` = 1))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`departments` `d`",
"attached": "(`d`.`dept_no` = 1)"
},
{
"table": "`dept_emp` `de`",
"attached": "(`de`.`dept_no` = `d`.`dept_no`)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
//确定最终表的查询条件
"finalizing_table_conditions": [
{
"table": "`departments` `d`",
"original_table_condition": "(`d`.`dept_no` = 1)",
"final_table_condition ": "(`d`.`dept_no` = 1)"
},
{
"table": "`dept_emp` `de`",
"original_table_condition": "(`de`.`dept_no` = `d`.`dept_no`)",
"final_table_condition ": null
}
] /* finalizing_table_conditions */
},
{
//优化后的执行计划
"refine_plan": [
{
"table": "`departments` `d`"
},
{
"table": "`dept_emp` `de`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
//-----SQL的执行阶段------
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
4.4 SQL的执行顺序
SQL的编写顺序
SQL的执行顺序
4.5 Join优化
4.5.1 Join的种类
- left join - 左连接
- right join - 右连接
- inner join - 内连接
- full outer join - 全外连接
4.5.2 Join算法
Simple Nested-Loop Join(SNL) - 简单嵌套循环连接
Nested-Loop Join 简单粗暴容易理解，就是通过双层循环比较数据来获得结果，但是这种算法显然太过于粗鲁，如果每个表有1万条数据，那么对数据比较的次数=1万 * 1万 =1亿次，很显然这种查询效率会非常慢
Index Nested-Loop Join(INL) - 索引嵌套循环连接
Index Nested-Loop Join其优化的思路 主要是为了减少内层表数据的匹配次数， 简单来说Index Nested-Loop Join 就是通过外层表匹配条件 直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较， 这样极大的减少了对内层表的匹配次数，从原来的匹配次数=外层表行数 * 内层表行数，变成了 外层表的行数 * 内层表索引的高度，极大的提升了 join的性能。
Block Nested-Loop Join（BNL）- 块嵌套循环连接
- 连接类型是ALL、index或者range，才能使用join buffer
- 第一个非常量表，是不会使用join buffer的
- join buffer只会缓存需要的字段（不是所有列）
- join buffer查看以及修改
- 查看：show variables like 'join_buffer_size'
- 局部修改：set join_buffer_size = xxxx
- 全局修改：set global join_buffer_size = xxxx
- 一个查询可能被分配多个join buffer
- join buffer在执行连接前会分配，在查询完成后释放
- 执行计划的Extra中出现了**Using join buffer(Block Nested Loop)**时，说明底层使用了join buffer
Batched Key Access Join（BKA）- 批量键值关联
- MySQL 5.6引入
- BKA必须作用在二级索引上，当使用二级索引关联时才可以使用BKA
- BKA是基于MRR实现优化的
- MRR本质上是将随机IO转换成类顺序IO来提高性能。比如通过一个非主键索引搜索结果，MRR会在内存中将结果通过主键排序，然后再去主键索引中获取记录行（类顺序IO）
- MRR并不一定能提高性能，因为MRR会带来额外的排序性能开销
- Extra中出现了Using MRR说明本次查询使用了MRR优化
- BKA相关命令
- 查看状态：**show variables like '%optimizer_switch%' **(默认关闭)
- 开启BKA：set optimizer_switch = 'batched_key_access=on'
- 使用BKA：当SQL语句使用了BKA，则Extra中会出现Using join buffer(Batched Key Access)
- 强制使用BKA：select /+BKA(b)/ 字段 from 表A a join 表B b on ....
Hash Join - 哈希Join
- MySQL 8.0.18引入，用来替代BNL
- join buffer缓存外部循环的hash表，内层循环遍历匹配hash表，效率极高
- join buffer中的哈希表可能溢出到磁盘
- hash join中使用join buffer是递增分配的，意味着可以将join_buffer_size设置的比较大一些
- MySQL 8.0.20开始，BNL已经被删除，用hash join替代
- Extra中出现using join buffer (hash join) 表示使用了hash join
- hash join只能用于没有索引的字段
- 可以通过8.0 hint语法**/+NO_INDEX(表名 索引名)/**禁用某个索引，达到使用hash join的目的
4.5.3 Join调优方式
- 小表驱动大表（两张表都需要有索引）
- 外表为驱动表，内表为被驱动表
- 通常无需人工干预，MySQL优化器会自动选择合适的表为驱动表
- 可以使用straight_join，强制干预左表为驱动表，或者使用8.0的hint语法 **/+JOIN_ORDER(t1,t2)/，**来控制驱动表顺序
- join关联字段需要创建索引
- 关联字段类型需要一致
- join关联表数量不要太多
- 阿里规范要求join关联表控制在3张表之内
- 可以将一个复杂的业务拆分成多个小业务控制表关联的数量
- 可以适当调大join buffer的大小增加join缓存空间
- 基于二级索引的关联，可以尝试使用BKA关联进行优化
- 基于等值关联，可以尝试禁用关联字段索引使用Hash Join进行优化
4.6 Order By优化
4.6.1 Mysql排序模式
rowid排序
1、从表中获取满足where条件的记录 2、对每条记录，将组件及排序健取出放入sort buffer（由sort_buffer_size控制大小）
- 查看：show variables like 'sort_buffer_size'
3、如果sort buffer足够存放所有排序数据，则会直接进行排序（快排）
4、如果sort buffer已满，则会将当前局部数据**进行排序并写入到临时文件，**最后，将多个临时文件中的有序数据，再统一做一次归并排序，保证最后的数据整体有序 5、扫描排好序的数据（主键和排序健），并利用主键去获取查询所需要的其他字段 6、返回结果集
全字段排序
- 在rowid排序的基础上，做出优化，将所有记录字段全部取出直接放入sort buffer中
- 减少排序查询中io读取的次数，但是会占用更多的sort buffer，更容易产生临时文件
- 当排序的sql中出现字段的总长度小于max_length_for_sort_data时，mysql将采用全字段排序，否则使用rowid排序
- 查看：show variables like 'max_length_for_sort_data'
打包字段排序
- MySQL 5.7引入
- 对全字段排序的一种优化，排序前会将所有字段数据紧密排列，减少全字段的空间大小
4.6.2 optimizer_trace分析order by
...
"filesort_summary": {
"memory_available": 262144,
"key_size": 8,
"row_size": 39,
"max_rows_per_buffer": 6721,
"num_rows_estimate": 331143,
"num_rows_found": 331603,
"num_initial_chunks_spilled_to_disk": 46,
"peak_memory_used": 262144,
"sort_algorithm": "std::stable_sort",
"sort_mode": "<fixed_sort_key, packed_additional_fields>"
} /* filesort_summary */
...
参数详解：
- memory_available：表示可用内存，也就是sort_buffer_size的大小
- num_rows_found：表示有多少条数据参与了排序，值越小越好
- num_initial_chunks_spilled_to_disk：产生了多少临时文件，值越小越好，如果为0表示完全基于内存排序的
- sort_mode：排序模式
- <fixed_sort_key, rowid> - 使用了rowid的排序模式
- <fixed_sort_key, additional_fields> - 使用了全字段的排序模式
- <fixed_sort_key, packed_additional_fields> - 使用了打包字段排序模式
4.6.2 Order By调优
- 核心思路：使用索引优化SQL的排序，因为索引本身是有序的
- 往往很多时候MySQL优化器并不会选择索引来优化排序
- 如果优化排序的是二级索引，MySQL会发现基于二级索引虽然优化的排序，但是造成了随机IO，优化器通过计算，发现随机IO造成的成本更高，因此优化器可能会直接选择全表扫描（顺序io），然后再对数据额外进行排序操作
- 很多时候我们可以选择二级索引 + 覆盖索引的方式优化排序，既避免了随机IO，又避免了额外排序，但是有一定的场景限制。
- 如果没办法使用索引优化排序，则需要对filesort进行优化：
- 适当调大sort buffer的大小，可以有效减少临时表数量
- 适当调大max_length_for_sort_data的大小，用全字段排序替换掉rowid排序，减少磁盘io
4.7 Group By优化
- 通常只需要根据分组的字段以及字段的顺序，创建复合索引即可
- 适当增加索引字段，借助覆盖索引进一步提高性能
五、调优实战
5.1 准备测试数据
MySQL官网测试数据： https://dev.mysql.com/doc/index-other.html
Github地址：
表结构
分别代表： 员工表 - 30W+数据 部门表 - 9条数据 职位表 - 40W+数据 工资表 - 280W+数据 部门领导信息表 - 24条数据 部门员工关系表 --
5.2 调优案例
5.2.1 调优前置 - 关闭查询缓存
相关命令：
- show variables like '%query_cache%' - 查看查询缓存相关的参数
- set global query_cache_type=0 - 表示关闭查询缓存
- set global query_cache_size=0 - 表示清空查询缓存数据
注意
- 以上的命令是临时关闭查询缓存，如果要永久关闭需要去设置my.cnf文件
- mysql 8.0以上的版本已经废弃了查询缓存，无需关闭
5.2.2 案例一
/*
查询各员工的平均工资，按员工编号降序排列，返回第10W页的数据（每页显示两条结果）
*/
5.2.3 案例二
/*
查询各部门的首任管理人员名称，以及该管理人员的平均工资
结果：
部门名称 首任管理人 管理人平均工资
*/
5.2.4 案例三
/*
查询Development部门所有员工的信息，最高工资，以及发放最高工资的年份（以起始年份为准）
结果：
部门名称 员工信息（各个字段） 最高工资 获得年份
*/
5.2.4 案例四
/*
查询没有担任过部门管理者的员工信息，以及当下的领导姓名
结果：
部门名称 领导姓名 员工信息（各个字段）
*/
六、锁优化 - (InnoDB存储引擎)
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。为保证数据的一致性，需要对并发操作进行控制，因此产生了锁。同时锁机制也为实现MySQL的各个隔离级别提供了保证。锁冲突也是影响数据库并发访问性能的一个重要因素。所以锁对数据库而言显得尤其重要，也更加复杂。
6.1 并发事务访问
在MySQL中，锁主要是为了解决事务之间的并发访问，而并发访问大致可以分为以下几种情况：
- 读 - 读
- 多个事务并发读取相同的记录，读本身不会对记录造成修改，因此这种情况无需任何干预
- 写 - 写
- 多个事务并发的修改相同记录，这种情况下，如果不做干预，将会发生更新丢失的问题，任何情况下都不会允许这种情况的发生，所以这时需要通过写锁来解决
- 读 - 写/写 - 读
- 一个事务读取数据，而另一个事务并发的修改数据，这种情况下，可能发生 脏读、不可重复读、幻读的问题，需要通过锁 + 事务的隔离级别来解决**，**对于隔离级别，每个数据库厂商对标准的支持都不一样，比如MySQL，在RR级别上就已经解决了幻读问题。
6.2 锁的分类
6.2.1 表级读/写锁
相关命令：
- 写/读锁：
- lock tables 表名 write;
- lock tables 表名 read;
- 解锁：
- unlock tables;
注意
- MyISAM支持表锁，InnoDB支持表锁/行锁
- 行锁的并发性能更强但开销更大
- 实际开发过程中，基本不用表锁，更多的使用行锁来解决事务的并发问题
6.2.2 行级读/写锁
相关命令：
- 读锁
- select .... lock in share mode
- 写锁
- select ....** for update**
- insert ...
- update ...
- delete ...
- 无锁
- select .....
注意
- 行级读/写锁是和事务绑定的，在一个事务中添加的锁，将会伴随着事务的提交/回滚而自动解除
- 读读共享、读写互斥、写写互斥
- innodb的行锁是通过给索引上的索引项加锁来实现的，也就是说，只有通过索引条件检索数据，innodb才会使用行级锁，否则将会锁住每一行（不是表锁）（前提是RR的隔离级别，MySQL8.0之前 默认是RR隔离级别）
- 一般情况下，新插入一条记录的操作并不加锁，通过一种称之为隐式锁的结构来保护这条新插入的记录在本事务提交前不被别的事务访问。
- 行级锁在不同的事务隔离级别之下，将表现出不同的特性
MySQL 8.0 新特性
在5.7及之前的版本，SELECT ... FOR UPDATE，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时。
在8.0版本中，SELECT ...FOR UPDATE，SELECT ...FOR SHARE 添加NOWAIT、SKIP LOCKED语法，跳过锁等待，或者跳过锁定。
通过添加NOWAIT、SKIP LOCKED语法，能够立即返回。如果查询的行已经加锁：
- NOWAIT 会立即报错返回；
- SKIP LOCKED 也会立即返回，只是返回的结果中不包含被锁定的行。
6.2.3 意向锁
意向读锁
事务打算给数据行加读锁，事务在给一个数据行加读锁前必须先取得该表的意向读锁。
意向写锁
事务打算给数据行加写锁，事务在给一个数据行加写锁前必须先取得该表的意向写锁。
为什么需要意向锁？
假设现在有这么一种情况，事务A锁住了表中的一行，让这一行只能读，不能写。之后，事务B申请整个表的写锁。如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。 数据库需要避免这种冲突，就是说要让B的表锁申请被阻塞，直到A释放了行锁。
- 如果没有意向锁，则事务B需要判断表中每一行是否已经被锁住
- 如果存在意向锁，那么事务B只需要判断表上是否存在意向锁即可
注意
- 意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁)的锁并存。
- 意向锁是一种不与行锁冲突的表级锁
- 意向锁是InnoDB自动添加，开发者无法干预
- MyISAM中没有意向锁
6.2.4 间隙锁（gap锁）
所谓的间隙，就是MySQL索引树中插入新记录的空隙，而间隙锁，就是指加在索引树的间隙上的锁。
为什么需要间隙锁？
MySQL为了在RR的隔离级别下解决幻读问题，引入了间隙锁。
- 例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
可以看到幻读就是一个事务，在其他事务操作的范围内进行了增删，而间隙锁就可以很好的解决这种问题，直接对间隙上锁，防止其他事务操作某个范围。
间隙锁前置条件
- RR级别下，SQL走了非唯一性索引
- RR级别下，SQL是一个范围的当前读操作，即使不走非唯一性索引也会加间隙锁
6.2.5 临建锁（Next-Key）
简单来讲，临建锁 = 记录锁 + 间隙锁。innodb存储引擎默认的锁其实就是临建锁
/*字段a 上添加了非唯一性索引
mysql将会在字段a的索引上，value键值上添加临键锁（value值本身的行锁 + value前后的间隙锁）
*/
select * from xxx where a = "value" for update;
/*
字段b 上添加了非唯一性索引
mysql将会在字段b的索引上，19右边的所有键值添加临键锁（本身行锁 + 前后的间隙锁）
*/
select * from xxx where b > 19 for update;
/*字段c 上没有索引
mysql将会在整个主键索引上，所有记录添加临键锁（所有记录本身的行锁 + 所有记录前后的间隙锁）
*/
select * from xxx where c > 19 for update;
/**
字段b 上有唯一性索引
mysql指挥在字段b的索引上，19这一列添加行锁（前后不会加间隙锁）
*/
select * from xxx where d = 19 for update;
/**
字段b 上有唯一性索引
mysql指挥在字段b的索引上，19以及19右边所有的记录添加临建锁（本身行锁 + 前后的间隙锁），注意19这一列也会加上前后的间隙锁
*/
select * from xxx where d >= 19 for update;
6.3 事务的隔离级别
6.3.1 什么是隔离级别？
简单来讲，隔离级别体现出了事务的隔离性，根据隔离级别的等级，会体现出不同的隔离效果。 不同的隔离级别，多个事务在进行并发读写时，将体现出不同的特性。
如果不考虑事务的隔离级别，可能会出现以下'错误'的情况：
- 脏读
- 不可重复读
- 虚读(幻读)
脏读
一个线程中的事务读到另一个线程中未提交的事务
不可重复读
不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
虚读(幻读)
幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
6.3.2 事务的隔离级别分类
- READ UNCOMMITTED (RU - 读未提交)
- READ COMMITTED (RC - 读已提交)
- REPEATABLE READ（RR - 可重复读）
- SERIALIZABLE（SER-串行化）
不同的隔离级别可以解决的事务问题
|
| 脏读 | 不可重复读 | 幻读 | | --- | --- | --- | --- | | RU-读未提交 | x | x | x | | RC-读已提交 | √ | x | x | | RR-可重复读 | √ | √ | x | | SER-串行化 | √ | √ | √ |
6.3.3 MySQL的事务隔离级别
默认隔离级别
Mysql默认为REPEATABLE READ (RR - 可重复读)
查看隔离级别
#5.x版本
select @@tx_isolation;
#8.x版本
select @@transaction_isolation;
设置隔离级别
set transaction_isolation = 'READ-UNCOMMITTED';
set transaction_isolation = 'READ-COMMITTED';
set transaction_isolation = 'REPEATABLE-READ';
set transaction_isolation = 'SERIALIZABLE';
注意：
- 设置是临时修改隔离级别，数据库服务重启后，隔离级别会回到默认级别
- 如果需要永久修改数据库隔离级别，需要修改文件C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
- MySQL中RR级别也解决了幻读的问题
6.4 MVCC - 多版本并发控制
MVCC (MultiVersion Concurrency Control)，即多版本并发控制技术，它使得大部分支持行锁的事务引擎,不再单纯的使用行锁来进行数据库的并发控制,取而代之的是,把数据库的行锁与行的多个版本结合起来,只需要很小的开销,就可以实现非锁定读,从而大大提高数据库系统的并发性能.
6.4.1 为什么需要MVCC？
简单来说，MVCC使得数据库的事务隔离级别实现起来更加的高效（不单只是依靠锁机制）。假设没有MVCC，MySQL在提供隔离级别的实现上，只能通过加锁的方式。比如，事务A查询了记录x，而事务B修改了记录x，如果没有MVCC，为了避免脏读，事务A的查询就必须加锁，事务B的修改将会阻塞，直到事务A提交为止。这种方式将极大的降低了MySQL的并发操作性能，因此MVCC就诞生了。
MVCC能带来什么？
MVCC机制使得，MySQL在并发读写时，读操作不会阻塞写，写操作不会阻塞读，提高了数据库并发读写的能力。
6.4.2 MVCC的实现原理
简单来说，MVCC的实现原理主要是依赖记录中的** 2个隐式字段**、undo log、**read view（读视图） **来实现的。
- 隐式字段
DB_TRX_ID - 记录最近一次操作（修改、新增）该条记录的事务id DB_ROLL_PTR - 回滚指针，指向该条记录的上一个版本
- undo log文件
undo log文件主要是为了记录MySQL的回滚信息，方便回滚事务时，能够找到回滚之前的记录。undo log文件可以分为三种类型：
- insert undo log - 插入一条新数据时，记录该数据的主键，回滚时只需要根据主键删除数据即可
- delete undo log - 删除一条数据时，记录该数据的全部内容，回滚时需要将该条记录重新添加到表中
- update undo log - 修改一条数据时，记录该数据修改前的旧内容，回滚时将旧内容覆盖回原表即可
undo log的记录过程
- read view
简单来说，read view就是基于当前事务版本产生的读取视图。当前事务在执行select语句时（不加锁），会基于数据库系统生成一个快照，记录并维护当前系统的活跃事务id。
6.4.3 快照读 & 当前读
什么是快照读？
事务通过read view读取数据的方式，就称之为快照读，这种方式无需加锁，也不会被其他事务的操作造成影响。
快照读：
- select .....
什么是当前读？
直接读取数据库中最新记录行的方式，称之为**当前读，**这种方式为了避免其他事务操作的影响，需要对数据进行加锁。
当前读：
- select .... lock in share mode
- select .... for update
- insert ....
- update ....
- delete ....
什么是MVCC？
MVCC是数据库提高并发读写能力的一种机制，简单来说，就是通过MVCC可以实现 读事务 和 写事务之间无需互斥上锁。拿MySQL举例子，底层的原理就是通过undo.log日志管理表数据的多个版本，结合事务id 回滚指针，在读事务开始读取记录时，生成一个Read View（读视图），当前读事务中，所有对该表的读取其实都是读取这个视图，也称之为快照读。其他的写事务，正常去写数据库的实际记录，因此读写之间并不会互相阻塞，这种技术就是MVCC
6.5 事务隔离级别 & mvcc & 锁
6.5.1 RC（读已提交） vs RR（可重复读）
在解释RC和RR的之前，先明确一点，在项目中不可能用RU（读未提交）和SER（串行化）这两个隔离级别的。
- RU - 能读取到一个随时可能回滚的数据，在任何业务中都是不可忍受的
- SER - 串行化将强制事务进行排队，使之不会发生任何并发问题，可能会导致大量的锁竞争和读超时，并且该级别下MySQL快照读失效，最基本的select....都会进行加锁操作，读写性能极差。
RR - MySQL默认隔离级别
- 支持间隙锁和临键锁（解决幻读问题）
- 开启事务后，第一次快照读，将创建一次Read View，后续都使用同一个Read View（为了保证可重复读）
RC
- 没有间隙锁和临键锁，只有行锁
- 开启事务后，每一次快照读，都将创建一次新的Read View
6.5.2 为什么MySQL默认隔离级别选择RR（可重复读）？
根本原因在于在老版本的MySQL数据库中，主从复制存在一个问题。
老版本的MySQL，主从复制的原理是基于statement模式实现的，简单来说就是将主库执行的SQL语句发给从库，从库进行回放即可（有点类似Redis的AOF）。但是这种模式存在一个问题：
- 主库上：
- 1）事务A - 执行了某个条件的删除（比如 id < 5）, 但是没有提交事务
- 2）事务B - 添加了一条数据（id 为 2），提交了事务
- 3）事务A再提交事务
此时在主库上，是存在一条id为2的数据的，因为删除比添加先执行（虽然后提交事务），但是MySQL主从复制记录SQL语句的顺序，是事务的提交顺序来决定的。也就是说，发给从库的SQL语句将变成 事务B -> 事务A的顺序。
- 从库上：
- 1）事务B - 添加一条数据（id 为 2），提交了事务
- 2）事务A - 执行了某个条件的删除（id < 5），提交了事务
此时从库上将不存在id为2的数据，因为执行的顺序为先添加后删除，导致id为2的数据被删除掉了，从而引起主从数据不一致。
MySQL为了解决这个问题，也是很干脆，将MySQL的隔离级别设置为RR（可重复读），因为RR级别下有间隙锁，主库上的事务B的添加操作将被事务A的间隙锁阻塞，从而强制了事务A一定比事务B先提交，保证了主库与从库的一致性。
6.5.3 RC的优势
1）没有间隙锁，提高了读写的并发性，并且降低了死锁出现的概率
2）在RR模式下，没有命中索引的当前读，将会锁表（确切来说是锁住每条记录以及记录之间的间隙），而在RC模式下，没有命中索引的当前读，只会锁住相应的记录行，并发性能更好。
3）在RC模式下，半一致性读的特性，增加了update的并发性
什么是半一致性读？
- 是一种用在 Update 语句中的读操作(一致性读)的优化，是在 RC 事务隔离级别下与一致性读的结合。当 Update 语句的 where 条件中匹配到的记录已经上锁，会再次去 InnoDB 引擎层读取对应的行记录，判断是否真的需要上锁(第一次需要由 InnoDB 先返回一个最新的已提交版本)。
6.6 锁优化总结
- 将MySQL默认隔离级别设置为RC，具有更好的并发性
- 在RC级别下，尽量减少快照读，避免频繁的Read View创建，同时也可以间接避免不可重复读和幻读的问题
- 使用行锁代替表锁（会有更大的开销，但是会获得更好的并发性能）
- 控制事务的范围，一个长事务将会带来更长的资源锁定时间
- 查询条件尽可能走索引，避免不必要的锁定（RR会变成表锁，RC会有一个先锁表，上层再解锁的过程）
- 可以考虑在业务代码层进行加锁处理，这样可以尽量避免数据库端的锁等待，浪费数据库连接资源
- 获取业务锁 -> 获取连接(开启事务) -> 执行业务 -> 提交事务 -> 释放锁
思考
1）隔离级别设置为RC，不可重复读与幻读的问题怎么解决？
- 绝大部分的业务不可重复度和幻读其实没有多大问题。如果业务无法忍受不可重复度和幻读，则直接采用当前读，或者直接修改当前连接的隔离级别为RR
2）隔离级别设置为RC，主从同步的bug怎么解决？
- 主从复制的模式选择row模式 - 不是记录sql语句，而是记录数据的改变
七、如何进行MySQL优化
根据我的经验，MySQL优化大致可以从几个纬度入手。 1）在数据库设计阶段，考虑好合理的表结构以及字段的类型，尽量选择合适并且占用空间更小的类型，减少数据读写时的内存和CPU使用占比。 2）在项目运行过程中，通过慢查询日志或者其他的一些工具（Druid、Skywalking），监控到运行较慢的SQL，针对这些SQL可以进行特定的优化（通过查看执行计划了解SQL的执行过程，结合optimizer_trace分析数据库优化器的优化策略，另外也可以根据show warnings查看优化器优化过后的sql结构，了解数据库真实执行的sql是什么样子的，再根据SQL的结构适当的添加设计索引，也可以调整SQL本身的结构，尽量减少SQL扫描的数据行数，在创建索引时，可以特意的使用上覆盖索引提升SQL的执行效率）。而且在连接查询是，可以通过调整连接的join算法，提升连接效率（比如 有时可以改变驱动表和被驱动表的顺序，或者通过8,0的hint语法，禁用索引，来强制使用hash join，某些情况下也可以获得很大的性能提升） 3）根据某些场景也可以适当的调整一些数据库的参数，比如（join_buffer/sort_buffer）,这种方式更多还是DBA操作。 4）从锁优化的角度，把MySQL默认隔离级别设置为RC，提高读写的并发性。另外，也可以调整业务的复杂程度（大业务拆分成小业务），减少事务的范围，减少事务锁的持续时间。尽量使用行锁，不要显示的使用表锁。也可以在业务中使用本地锁或者分布式锁来解决数据一致性的问题，避免数据库连接被长时间锁定，浪费数据库的资源。 5）如果数据量过大，可能索引等优化手段并不能很好的解决数据库的性能问题了（几千万），这时就需要从库表结构入手（分库分表 + 读写分离） 6）有些时候更换服务器硬件也能带来很好的数据库的性能提升，采用多核CPU、更大的内存、固态硬盘.... 7）业务优化的角度，通过调整业务的方式，改变SQL的结构，从而达到提升效率的目的
