MySQL高级学习笔记

MySQL高级学习笔记
注：毕笔记来源于黑马程序员 MySQL数据库入门到精通。

文章目录
第1章 MySQL的架构介绍

 1.1 MySQL简介

官网：https://www.mysql.com/

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司

MySQL 是一种关联数据库管理系统（RDBMS），将数据保存在不同的表中，而不是将所有数据放在一个大的仓库内，这样就增加了速度并提高了灵活性。

MySQL 特征
- MySQL 是开源的，所以你不需要支付额外的费用。
- MySQL 支持大型的数据库。可以处理拥有处理上千万条记录的大型数据库。
- MySQL 可以允许于多个系统上，并且支持多种语言，这些编程语言包含 C、C++、Java、Perl、PHP 、 Eiffel 、 Ruby 和 Tcl 等。
- MySQL对 PHP 有很好的支持， PHP 是暮年最流行的 WEB 开发语言之一。
- MySQL 支持大型数据库，支持 5000W 条数据记录的数据仓库， 32 为操作系统最大可支持 4GB ， 64位操作系统最大的表文件为 8TB。
- MySQL 是可以支持定制的，采用了 GPL协议，你可以修改源码来开发自己的MySQL 系统。
1.2 在Linux上安装MySQL

MySQL高级的应用都是在Linux系统上进行，是对数据库的优化。

请参考博客：在Linux中安装MySQL

1.3 MySQL配置文件

Windows系统下mysql的配置文件为my.ini文件，Linux系统下mysql的配置文件为/etc/my.cnf文件。

MySQL主要配置文件如下：
- 二进制日志log-bin：用于主从复制
- 错误日志log-error：默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等
- 查询日志log：默认关闭，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗系统资源的
- 数据文件
  - 两系统
    mysql安装目录下的data目录下可以挑选很多库
    默认路径：/var/lib/mysql
  - frm文件：存放表结构
  - myd文件：存放表数据
  - myi文件：存放表索引
1.3 MySQL逻辑架构介绍

总体架构：

总体架构说明：

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

2、业务逻辑处理层：主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的操作。所有跨操作引擎的功能也在这一层实现，如过程，函数等。在该层服务器会解析查询并创建相应的内部解析树，并对其完成相应的优A化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是SELECT语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。注：提供SQL操作的接口，对SQL脚本按一定规则进行解析，并通过SQL优化器优化执行顺序，对于查询的语句还会进入缓存区，提升系统的性能。

3、数据存储引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引[擎进行通信，不同的存储引|擎具有的功能不同，这样我们可以根据自己的实际需要进行选选取。注：存储引擎都是可插拔的，每个存储引l擎所提供的服务都有所差异，所以我们需要根据具体的业务需要，选择合适的存储引[擎，常用的只有两种MyISAM和lnnoDB。数据库中的索引是在存储引擎层实现的。

4、数据存储层：主要是将数据存储在运行于裸设备的文件系统上，并完成与存储引擎的交互。注：将数据存储到磁盘上，并协同存储引擎对数据进行读写操作。是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询
日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。

数据库逻辑结构共分为四层，分别是连接层（线程连接池)、业务逻辑处理层(SQL解析读取)、数据存储引擎层(存储擎)、数据存储层(数据存储)和其它的数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.4 MySQL存储引擎

 1.4.0 存储引擎概述

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

1.4.1 查看命令
- 建表时指定存储引擎
语法如下：
```
CREATE TABLE 表名( 
	字段1 字段1数据类型 [ COMMENT 字段1注释 ],
	...
	字段n 字段n数据类型 [ COMMENT 字段n注释 ]
) ENGINE = 引擎名 [ COMMENT 表注释 ];
1
2
3
4
5
```
引擎名可以是 InnoDB、MyISAM、Memory。例如：
```
create table my_myisam(
	id int,
	name varchar(10)
) engine = MyISAM;
1
2
3
4
```
- 查询当前数据库支持的存储引擎
可以通过如下命令查看支持的存储引擎：
```
show engines; #查看支持的存储引擎
1
```
- 查看当前默认的存储引擎
通过如下命令查看当前默认的存储引擎：
```
show variables like '%storage_engines%'; #查看当前默认的存储引擎
1
```
- 查看指定表使用的存储引擎
语法：
```
# 语法
show create table 表名;
# 示例，查看 account 表的建表语句
show create table account;
1
2
3
4
```
1.4.2 存储引擎特点

1.4.2.1 InnoDB

（1）概述

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。

（2）特点
- DML 操作遵循 ACID 模型，支持事务。
- 行级锁，提高并发访问性能。
- 支持外键 FOREIGN KEY 约束，保证数据的完整性和正确性。
（3）文件

xxx.idb：其中 xxx 表示的是表名，如果使用了 InnoDB 存储引擎，那么数据库中的每张表都会对应这样一个表空间文件，存储该表的表结构、数据和索引。在早期版本中是以 .frm 后缀结尾的。

这些文件是二进制文件，是不能直接使用记事本程序打开的，但可以使用 MySQL 提供的一个命令 ibd2sdi，通过该命令就可以从 .ibd 文件中提取 sdi 信息，而 sdi 数据字典就包含该表的表结构。命令格式如下：
```
# 语法
ibd2sdi 表名.ibd
# 示例，查看 user.ibd 表空间文件的数据字典
ibd2sdi user.ibd
1
2
3
4
```
注：在 MySQL 中，变量 innodb_file_per_table 表示是否开启对于使用了 InnoDB 存储引擎的表，每一张表都对应一个 .ibd 文件。其中 ON 表示默认开启。

（4）逻辑存储结构

注：InnoDB 存储引擎的逻辑存储结构如下图（图来源于黑马视频）：
- 表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个 Segment 段。
- 段 : 表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
- 区 : 区是表空间的单元结构，每个区的大小为1M。默认情况下， InnoDB存储引擎页大小为 16K，即一个区中一共有 64 个连续的页。
- 页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
- 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段
1.4.2.2 MyISAM

（1）概述

MyISAM 是 MySQL 早起版本的默认存储引擎。

（2）特点
- 不支持事务。
- 不支持外键。
- 支持表锁，不支持行锁。
- 访问速度快。
（3）文件

MySQL 中使用了 MyISAM 存储引擎的表，会生成如下几个文件：
- xxx.sdi：存储了表的结构信息。
- xxx.MYD：存储了表数据信息。
- xxx.MYI：存储了表索引信息。
1.4.2.3 Memory

（1）概述

Memory 引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

（2）特点
- 存储在内存中。
- 默认使用 Hash 索引。
（3）文件

使用了这种存储引擎的表会生成如下文件：
- xxx.sdi：该文件存储了表结构信息。
1.4.2 MyISAM和InnoDB

1.4.3 存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。
- InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操
  作，那么InnoDB存储引擎是比较合适的选择。
- MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
- MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
第2章索引优化分析

 2.1 概述

 2.1.1 性能下降原因

性能下降SQL慢，执行时间长，等待时间长的原因：
- 查询语句写的烂
- 索引失效：单值和复合
- 关联查询太多join（设计缺陷或不得已的需求）
- 服务器调优及各个参数设置不合理（缓冲、线程数等）
2.1.2 常用的join查询

2.1.2.1 SQL执行顺序

①手写

通常我们按照需求写的SQL查询语句是这样的：
```
SELECT DISTINCT查询列表
FROM 左表 INNER|LEFT|RIGHT
JOIN 右表 ON 连接条件
WHERE 分组前的筛选条件
GROUP BY 分组列表
HAVING 分组后的筛选条件
ORDER BY 排序列表
LIMIT 分页参数
1
2
3
4
5
6
7
8
```
我们手写的SQL中SELECT在最前面的位置。

②机读

随着 Mysql 版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：
```
FROM 左表 INNER|LEFT|RIGHT
JOIN 右表 ON 连接条件
WHERE 分组前的筛选条件
GROUP BY 分组列表
HAVING 分组后的筛选条件
SELECT DISTINCT查询列表
ORDER BY 排序列表
LIMIT 分页参数
1
2
3
4
5
6
7
8
```
而在机读中FROM是最先执行的。

③总结

2.1.2.2 join图
- 左外连接
A表独有部分+AB两表的公有部分。
```
-- SQL语句如下：
SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key# 其中A.key=B.key指的是连接条件
-- 注：
-- 左外连接中左边的是主表，右边的是从表
1
2
3
4
```
- 右外连接
```
-- SQL语句如下：
SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key# 其中A.key=B.key指的是连接条件
-- 注：
-- 右外连接中右边的是主表，左边的是从表
1
2
3
4
```
- 内连接
获取的是两张表的公有部分。
```
-- SQL语句如下：
SELECT 查询列表 FROM A INNER JOIN B ON A.key=B.key# 其中A.key=B.key指的是连接条件
-- 注：
-- 内连接求的是多张表的交集部分
1
2
3
4
```
- 左外连接去除交集
```
SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key WHERE B.key IS NULL;# 将从表B的连接条件作为NULL值判断
1
```
- 右外连接去除交集
```
SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key WHERE A.key IS NULL;# 将从表A的连接条件作为NULL值判断
1
```
- 全外连接
注意：MySQL不支持full join。
```
SELECT 查询列表 FROM A FULL JOIN B ON A.key=B.key;# 全外连接就是求并集

# 可以间接完成效果

SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key
UNION
SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key
1
2
3
4
5
6
7
```
- 全外连接去除交集
```
SELECT 查询列表 FROM A FULL JOIN B ON A.key=B.key WHERE A.key IS NULL OR B.key IS NULL;
1
```
实例如下：
```
-- 左外连接
select * from beauty left join boys on beauty.boyfriend_id=boys.id;
-- 右外连接
select * from beauty right join boys on beauty.boyfriend_id=boys.id;
-- 内连接
select * from beauty inner join boys on beauty.boyfriend_id=boys.id;
-- 左外连接去除交集
select * from beauty left join boys on beauty.boyfriend_id=boys.id where boys.id IS NULL;
-- 右外连接去除交集
select * from beauty right join boys on beauty.boyfriend_id=boys.id where beauty.boyfriend_id IS NULL;
-- 全外连接(MySQL不能运行)
select * from beauty full join boys on beauty.boyfriend_id=boys.id;
-- 全外连接去除交集(MySQL不能运行)
select * from beauty full join boys on beauty.boyfriend_id=boys.id where boys.id IS NULL OR beauty.boyfriend_id IS NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
```
2.2 索引简介

 2.2.1 索引是什么

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构（有序）。可以得到索引的本质：索引是数据结构。可以简单理解为排好序的快速查找数据结构。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hashindex)等。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

如下演示使用索引与不使用索引的区别（假如表结构和数据如下）：

假如我们要执行的 SQL 语句是：select * from user where age = 45;。
- 如果是无索引的情况，则会从第一行一直扫描到最后一行，即全表扫描，性能很低。
- 如果是有索引的情况，假设索引结构是二叉树（并不是真实的索引结构），对该表建立索引，即对 age 字段建立一个二叉树的索引结构。在进行查询时，只需要扫描三次就可以查到，极大的提高了查询效率。
2.2.2 索引的优缺点

优势：
- 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势：
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
- 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
2.2.3 索引结构

2.2.3.1 概述

MySQL 的索引在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

索引结构

描述

B+Tree索引

最常见的索引类型，大部分引擎都支持 B+ 树索引

Hash索引

底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询

R-tree(空间索引）

空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少

Full-text(全文索引)

是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持情况：

索引

InnoDB

MyISAM

Memory

B+tree索引

支持

支持

支持

Hash 索引

不支持

不支持

支持

R-tree 索引

不支持

支持

不支持

Full-text

5.6版本之后支持

支持

不支持

注：平常所说的索引，如果没有特别指明，都是指 B+ 树结构组织的索引。

2.2.3.2 二叉树

注：通过下面这几节可以了解为什么索引结构采用 B+ 树的数据结构。

假如 MySQL 的索引结构采用二叉树，比较理想的情况如图：

如果是按主键顺序插入，在比较极端的情况下，则会变成一个单链表，那么查找又会变成全表扫描。结构如下：

因此使用二叉树作为索引结构，缺点如下：
- 顺序插入时，会退化成一个链表，查询性能大大降低。
- 大数据量情况下，即使是比较理想的二叉树结构，也会有很多层，造成层次比较深，那么检索速度会变慢。
为了解决顺序插入而退化成链表的问题，可以考虑使用红黑树，红黑树是一颗自平衡二叉树，即使顺序插入数据，最终也会形成一颗平衡的二叉树，而不会退化成链表。

但即使如下，由于红黑树本身也是二叉树，所以大数据量层次比较深的问题仍然存在。使用红黑树的缺点如下：
- 大数据量情况下，即使是理想的红黑树，也会有很多层，造成层次比较深，那么检索速度会变慢。
所以，在 MySQL 的索引结构中，没有选择二叉树或红黑树，而是选择 B+ 树来作为数据结构。

2.2.3.3 B-Tree

B-Tree，B 树是一种多叉路衡查找树，相对于二叉树，B 树每个节点可以有多个分支，即多叉。

而多叉就能解决深层次的问题，将原来的高度问题变成了宽度问题（高瘦——>矮胖）。

以一颗最大度数（max-degree，度数即指一个节点的子节点个数）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针：

注：可通过 B-Trees 网站来动态演示查看 B-Tree 的执行过程。

例如插入一组数据：[100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250]。然后观察一些数据插入过程中，节点的变化情况。如图所示：

B-Tree的特点：
- 5阶的B树，每一个节点最多存储4个key，对应5个指针。
- 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
- 在B树中，非叶子节点和叶子节点都会存放数据。
为什么不使用 B-Tree 作为实现索引的数据结构呢？因为对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。

2.2.3.4 B+Tree

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一下其结构示意图：

如图，可以看到 B+Tree 只在叶子结点存储数据：
- 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
- 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。
例如插入一组数据：[100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250]。然后观察一些数据插入过程中，节点的变化情况。如图所示：

B+Tree 与 B-Tree 相比，主要有以下三点区别：
- 所有的数据都会出现在叶子节点。
- 叶子节点形成一个单向链表。
- 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
上述我们所看到的结构是标准的B+Tree的数据结构，而 MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree（实际上是循环双链表），提高区间访问的性能，利于排序。

2.2.3.5 Hash

MySQL 中除了支持 B+Tree 索引，还支持一种索引类型——Hash索引。

（1）结构

注：如果想要了解 Hash 索引更多的信息，建议先学习了解 Hash 数据结构。

哈希索引就是采用一定的 hash 算法，将键值换算成新的 hash 值（通常是一个字符串转换成一个数字，再将数字映射到对应表中位置），映射到对应的槽位上，然后存储在hash表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了 hash 冲突（也称为 hash 碰撞），可以通过链表（但如果链表中冲突的结点多了话也会变成线性遍历查找了）来解决，如在 Java 中的 HashMap 中后期版本中采用了红黑树来解决冲突。

（2）特点

Hash 索引的特点如下：
- Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）。
- 无法利用索引完成排序操作，因为元素是乱序存放的。
- 查询效率高，通常(不存在 hash 冲突的情况)只需要一次检索就可以了，效率通常要高于 B+Tree 索引。
（3）存储引擎支持

在 MySQL 中，支持 hash 索引的是 Memory 存储引擎。而 InnoDB 中具有自适应 hash 功能，hash 索引是 InnoDB 存储引擎根据 B+Tree 索引在指定条件下自动构建的。

为什么 InnoDB 存储引擎选择使用 B+tree 索引结构？
- 相对于二叉树，层级更少，搜索效率高。
- 对于 B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
- 相对 Hash 索引，B+Tree支持范围匹配及排序操作。
2.2.4 索引的分类

索引可以分为单值索引、唯一索引、主键索引、复合索引和全文索引。

分类

含义

特点

关键字

主键索引

针对表中的主键创建的索引

默认自动创建，只能有一个

PRIMARY

唯一索引

避免同一个表中某数据列的值重复

可以有多个

UNIQUE

单值索引

只对某列建立索引

可以有多个

复合索引

对多个列一起建立索引

可以有多个

全文索引

全文索引查找的是文本中的关键词，而不是比较索引中的值

可以有多个

FULLTEXT

2.2.4.0 聚集索引与二级索引

而在在 InnoDB 存储引擎中，根据索引的存储形式（物理存储），又可以分为以下两种：

分类

含义

特点

聚集索引(Clustered Index)

将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

必须有,而且只有一个

二级索引(Secondary Index)

将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键

可以存在多个

聚集索引选取规则:
- 如果存在主键，主键索引就是聚集索引。
- 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
聚集索引（如以主键 id 建立聚集索引）和二级索引（如以 name 字段建立二级索引）的具体结构如下：
- 聚集索引的叶子节点下挂的是这一行的数据。
- 二级索引的叶子节点下挂的是该字段值对应的主键值。
当我们执行如下的SQL语句（select * from user where name='Arm';）时，具体的查找过程如下：
- 1、由于是根据 name 字段进行查询，所以先根据 name='Arm' 到 name 字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
- 2、由于查询返回的数据是 *，所以此时，还需要根据主键值 10，到聚集索引中查找 10 对应的记录，最终找到 10 对应的行 row。
- 3、最终拿到这一行的数据，直接返回即可。
回表查询：这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。
思考题：以下两条SQL语句，那个执行效率高为什么
- A. select * from user where id = 10;
- B. select * from user where name = 'Arm';
  备注: id 为主键，name 字段创建的有索引；
  解答：A 语句的执行性能要高于B 语句。因为A语句直接走聚集索引，直接返回数据。而B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。
2.2.4.1 单值索引

即一个索引只包含单个列，一个表可以有多个单列索引。

创建单值索引的语法如下：
```
-- 创建情况一：在创建表时创建
CREATE TABLE 表名(
	字段名 字段类型 [约束],
	字段名 字段类型 [约束],
	...
	字段名 字段类型 [约束],
	KEY(字段名)# 这一句就是创建单列索引的语句，直接在KEY()中写字段名即可
);

-- 创建情况二：单独创建单值索引
CREATE INDEX 索引名 ON 表名(字段名);
# 注释：
# 	1.索引名通常是idx_表名_字段名这样的格式，比如idx_user_name
#   2.单独创建索引的示例：CREATE INDEX idx_user_name user(nmae);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
```
2.2.4.2 唯一索引

即索引列的值必须唯一，但允许有空值。

创建唯一索引的语法如下：
```
-- 创建情况一：在创建表时创建
CREATE TABLE 表名(
	字段名 字段类型 [约束],
	字段名 字段类型 [约束],
	...
	字段名 字段类型 [约束],
	UNIQUE(字段名)# 这一句就是创建唯一索引的语句，直接在UNIQUE()中写字段名即可
);

-- 创建情况二：单独创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
# 注释：
# 	1.索引名通常是idx_表名_字段名这样的格式，比如idx_user_name
#   2.单独创建唯一索引的示例：CREATE UNIQUE INDEX idx_user_id user(id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
```
2.2.4.3 主键索引

设定某字段为主键后，数据库会自动建立索引，innodb存储引擎的主键为聚簇索引。

创建索引的基本语法如下：
```
-- 创建情况一：在创建表时创建
CREATE TABLE 表名(
	字段名 字段类型 [约束],
	字段名 字段类型 [约束],
	...
	字段名 字段类型 [约束],
	PRIMARY KEY(字段名)# 这一句就是创建主键索引的语句，直接在PRIMARY KEY()中写字段名即可
);

-- 创建情况二：单独创建主键索引
ALTER TABLE 表名 ADD PRIMARY KEY 表名(字段名);
# 注释：
#  1.创建示例：ALTER TABLE user ADD PRIMARY KEY user(id);

-- 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;
# 注释：
#  1.删除示例：ALTER TABLE user DROP PRIMARY KEY;
#  2.如果要修改主键索引，那么必须先删除掉原索引，再新建索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
```
2.2.4.4 复合索引

即一个索引包含多个列。

创建的基本语法如下：
```
-- 创建情况一：在创建表时创建
CREATE TABLE 表名(
	字段名 字段类型 [约束],
	字段名 字段类型 [约束],
	...
	字段名 字段类型 [约束],
	KEY(字段名,字段名,..)# 这一句就是创建复合索引的语句，直接在KEY()中写多个字段名即可
);

-- 创建情况二：单独创建复合索引
CREATE INDEX 索引名 ON 表名(字段名,字段名,...);
1
2
3
4
5
6
7
8
9
10
11
```
注：在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。因为单列索引很可能由于需要查询多个字段触发回表查询，而使用联合索引可以触发覆盖索引的情况避免回表查询，效率更高点。

2.2.5 基本语法

①创建索引
```
-- 语法
CREATE [UNIQUE] INDEX 索引名 ON 表名(字段名[,字段名,字段名,..]);
1
2
```
②删除索引
```
-- 语法
DROP INDEX 索引名 ON 表名;
1
2
```
③查看索引
```
-- 语法
SHOW INDEX FROM 表名;
1
2
```
④修改索引
```
-- 语法
-- 通过修改语句添加主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);# 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为 NULL。
-- 通过修改语句添加普通索引
ALTER TABLE 表名 ADD INDEX 索引名(字段名,[字段名,..]);# 添加普通索引
-- 通过修改语句添加全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名(字段名,[字段名,..]);# 该语句指定了索引为FULLTEXT，用于全文索引
1
2
3
4
5
6
7
```
2.2.6 索引的创建时机

2.2.6.1 适合创建索引的情况
- 主键自动建立唯一索引；
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引
- 单键/组合索引的选择问题，组合索引性价比更高
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
- 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录，还会更新索引，加重了IO负担。
- where条件里用不到的字段不创建索引。
2.2.6.2 不适合创建索引的情况
- 表记录太少
- 经常增删改的表或者字段
- Where 条件里用不到的字段不创建索引（注：虽然提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件）
- 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。（注：比如国籍，全是中国人，那么没必要创建索引；又或者性别，不是’男’就是’女’也没必要创建索引）
2.3 性能分析

 2.3.0 SQL 执行频率

我们可以获取数据库中 INSERT、UPDATE、DELETE、SELECT 这几类命令的执行频率，查看哪一类的命令执行频率比较高，如果是 SELECT 查询命令执行频次比较高，则需要针对查询进行优化。

MySQL 客户端连接成功后，通过 show session status; 或 show global status; 语句可以提供服务器状态信息。

通过如下指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT 的访问频次：
```
SHOW GLOBAL STATUS LIKE 'Com_______';
1
```
主要字段说明如下：
- Com_delete：表示删除命令的执行频次。
- Com_insert：表示插入命令的执行频次。
- Com_select：表示查询命令的执行频次。
- Com_update：表示更新命令的执行频次。
注：通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。如果是以增删改为主，我们可以考虑不对其进行索引的优化。如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。那如果是以查询为主，该如何定位针对于那些查询语句进行优化呢？我们可以借助于慢查询日志来获取哪些查询语句执行比较慢，然后针对这部分语句来进行优化。

2.3.1 MySQL Query Optimizer

1、Mysql中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)

2、当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQLQuery Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有)，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

2.3.2 MySQL常见瓶颈
- CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈: top,free, iostat和vmstat来查看系统的性能状态
2.3.3 Explain

2.3.3.1 是什么

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

2.3.3.2 能干啥
- 表的读取顺序
- 数据读取操作的操作类型哪些索引可以使用
- 哪些索引被实际使用表之间的引用
- 每张表有多少行被优化器查询
2.3.3.3 怎么玩
```
-- 语法
EXPLAIN 查询语句;
-- 示例
EXPLAIN SELECT * FROM user;
1
2
3
4
```
查询出来所包含的信息如下：

示例：

2.3.3.4 字段解释

Explain 执行计划中各个字段的含义：

字段

含义

id

select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。

select_type

表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等

type

表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。

possible_key

显示可能应用在这张表上的索引，一个或多个。

key

实际使用的索引，如果为NULL，则没有使用索引。

key_len

表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

rows

MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

filtered

表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

2.3.3.4.1 id

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况：
- id相同，执行顺序由上至下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同不同，同时存在
说了表的读取顺序：先加载括号内的子查询。

2.3.3.4.2 select_type

select_type有如下类型值：
- SIMPLE：简单的select查询，查询中不包含子查询或者UNION。
- PRIMARY：查询中若包含任何复杂的子查询，则最外层的查询被标记为PRIMARY。
- SUBQUERY：在SELECT或WHERE列表中包含的子查询。
- DERIUED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中。
- UNION：若第二个SELECT出现在UNION之后，则被标记为UNION。若UNION包含在FROM子句的子查询中，则外层SELECT将被标记为DERIVED。
- UNION RESULT：从UNION表获取结果的SELECT。
查询的类型select_type主要用于区别普通查询、联合查询、子查询等复杂查询。

2.3.3.4.3 table

显示这一行的数据是关于哪张表的。

2.3.3.4.4 type

一般上百万条数据才进行优化。

type是访问类型排列，显示查询使用了何种类型，跟索引优化有很大的关系，需掌握。type的值有如下几种：
- system：表只有一行记录（等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计。
- const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中,MySQL就能将该查询转换为一个常量。
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
- ref：非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
- range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询
  这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
- index：全索引扫描，index与ALL的区别为index只遍历索引树而非全表。通常比ALL快，因为索引文件通常比数据文件小。也就是all和index都是读全表，但index从索引中读取，而all从硬盘中读取。
- all：全表扫描，将遍历全表找到匹配的行。
从最好到最差依次是：system>const>eq_ref>ref>range>index>all。

注：一般来说，得保证查询至少达到range级别，最好能达到ref。

如果百万级别或千万级别的记录查询出现的type是all，那么就需要考虑优化了。

2.3.3.4.5 possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。是列出理论上可能会被用到的索引。

2.3.3.4.6 key

列出实际上被用到的索引。如果为NULL，则没有使用索引。

通常同possible_keys来说，possible_keys是理论上可能会被用到的索引，而key是实际上用到的索引。例如请客估计应该来10人，这是possible_keys，而当天实际上来了6人，这是key。

若查询中使用了覆盖索引，则该索引和查询的select字段重叠。所谓的覆盖索引就是查询的字段正好是复合索引中的字段列表，那么就直接在索引中查找，而不是从全表中查找，如下图：

2.3.3.4.7 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分。但使用的长度越小越好。

如何计算：
- ①先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20
- ②如果是 varchar 或者 char 这种字符串字段，视字符集要乘不同的值，比如 utf-8 要乘 3,GBK 要乘 2，
- ③varchar 这种动态字符串要加 2 个字节
- ④允许为空的字段要加 1 个字节
第一组：key_len=age 的字节长度+name 的字节长度=4+1 + (20*3+2)=5+62=67

第二组：key_len=age 的字节长度=4+1=5

2.3.3.4.8 ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

如"const"表示一个常量，"mytest.emp.deptno"表示"mytest"数据库的"emp"表的"deptno"列的索引被使用了。

2.3.3.4.9 rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好！

2.3.3.4.10 extra

extra中包含不适合在其他列中显示，但又十分重要的额外信息。

它可能的值有如下几种情况（重点关于①、②、③的情况）：

①Using filesort

说明mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。

注：出现了这个值就应该对查询语句进行优化了。

出现 filesort 的情况：

优化后，不再出现 filesort 的情况：

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度（即将排序的字段添加到索引）。

②Using temporary

使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

注：出现了这个值就应该对查询语句进行优化了。

优化前：

将group by、order by后面的字段添加到索引中去，如果已有索引，那么它们后面的字段的顺序应该跟复合索引中的字段顺序一样。

优化后：

③Using index

Using index 代表表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现 using where，表明索引被用来执行索引键值的查找;如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。

利用索引进行了排序或分组。

④Using where

表明使用了 where 过滤。

⑤Using join buffer

使用了连接缓存。

⑥impossible where

where 子句的值总是 false，不能用来获取任何元组。比如查找一个人名字既为"张三"又为"李四"，不可能筛选成功。

⑦ select tables optimized away

在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

在 innodb 中：

在 Myisam 中：

⑧distinct

优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

例子练习：

2.4 索引优化

 2.4.1 索引分析

2.4.1.1 单表

①创建测试表

测试用的数据如下：
```
CREATE TABLE IF NOT EXISTS `article`(
    `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `author_id` INT (10) UNSIGNED NOT NULL,
    `category_id` INT(10) UNSIGNED NOT NULL , 
    `views` INT(10) UNSIGNED NOT NULL , 
    `comments` INT(10) UNSIGNED NOT NULL,
    `title` VARBINARY(255) NOT NULL,
    `content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');
1
2
3
4
5
6
7
8
9
10
11
12
13
```
②查询及执行情况分析

查询需求：查询category_id为1并且comments>1的情况下，观看数量最多的文章
```
explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
1
```
执行情况：
- type：ALL，表示是全表查询需要的记录，要考虑优化。
- Extra:Using filesort，表示使用了文件内排序，又要考虑优化。
③如何优化

考虑建立表索引来进行优化。两种方式建立索引都可以，先创建索引试试：
```
ALTER TABLE article ADD INDEX idx_article_ccv (category_id, comments, views); --第一种方式
CREATE INDEX idx_article_ccv ON article (category_id, comments, views) ; --第二种方式
1
2
```
可以使用show index from article;查看article表的索引情况。

再次查看执行计划：使用了索引，type也由all变成了range。

说明：type变成range这是可以的，但在extra中还是有Using filesort，这是无法接受的。我们创建了索引，但是没有被用到，这是因为按照BTree索引的工作原理，先排序category_id字段，如果遇到相同的category_id再排序comments字段，如果再遇到相同的comments字段再排序views字段。当comments字段在复合索引里处于中间位置时，因comments>1条件是一个范围值（所谓的range），MySQL无法利用索引对后面的views部分进行检索，即range类型查询字段后面的索引无效。

那么就需要删除索引，并重建有效的索引：
```
DROP INDEX idx_article_ccv ON article; -- 删除索引
CREATE INDEX idx_article_ccv ON article (category_id,views);  -- 重建索引
1
2
```
即既然范围值会使索引失效，那么就不对它建立索引即可。

解决了Using filesort问题，也使用了索引。

总结：对单表中要查询的字段添加索引，对如果条件是一个范围值的话就不添加索引。

2.4.1.2 双表

①创建测试表

测试用的数据如下：
```
CREATE TABLE IF NOT EXISTS `class`(
    `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
    `bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));

INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
```
②查询及执行情况分析

查询需求：关联class表和book表
```
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card;
1
```
发现是全表扫描，即type为ALL，那么需要进行优化。

如何优化

由于是LEFT JOIN，所以左表class表是主表，因此第一次添加索引的尝试添加在主表上。
```
CREATE INDEX idx_class_card ON class (card); -- 对class表的card字段添加索引
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card; -- 再次查看执行计划
1
2
```
发现class表添加索引并且使用索引成功。

但查看rows字段发现还是全表扫描。

那么接下来为右表book添加索引：
```
DROP INDEX idx_class_card on class; -- 删除class表索引
CREATE INDEX idx_book_card ON book (card); -- 为book表的card字段添加索引
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card; -- 查看执行计划
1
2
3
```
发现type变成了ref，效果比index好，并且rows只扫描了一行。

总结：当是两张表时，如果是LEFT JOIN左连接，由于左表数据全部都有，所以关键在于如何从右表进行搜索，故右表一定要为连接条件的字段添加索引；如果是RIGHT JOIN右连接，由于右表数据全部都有，所以关键在于如何从左表进行搜索，故左表一定要为连接条件的字段添加索引。

2.4.1.3 三表

①创建测试表

在双表的基础上新增一张表：
```
CREATE TABLE IF NOT EXISTS `phone`(
    `phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
```
使用show index from book;语句发现book表刚才添加的索引没有删除掉，那么删除干净：
```
show index from book; -- 查看book表的索引情况
drop index idx_book_card on book; -- 删除book表的索引
1
2
```
②查询及执行情况分析

查询需求：关联book、class、phone三张表。

查看查询计划情况：
```
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
1
```
发现都是全表扫描并且使用了连接缓存（Using join buffer），需要考虑优化。

如何优化

根据双表得出的结论，如果是LEFT JOIN左连接，那么在右表添加索引即可，但这里使用了三张表，那么需要在book表为card字段添加索引，在phone表为card字段添加索引。
```
CREATE INDEX idx_phone_card ON phone(card); -- 为phone表的card字段添加索引
CREATE INDEX idx_book_card ON book (card); -- 为book表的card字段添加索引
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card; -- 查看执行计划
1
2
3
```
总结：如果是三表及多表查询，按照双表的左外连接和右外连接的情况来分批次处理即可。

2.4.1.4 总结
- 语句优化应尽可能减少join语句中NestedLoop的循环总次数，即“永远用小结果集驱动大结果集*（即添加索引变为小结果集）*”。
- 优先优化NestedLoop的内层循环。
- 尽量保证join语句中被驱动表的条件字段添加了索引（即LEFT JOIN在右表上添加，反之亦然）。
- 当无法保证被驱动表的条件字段添加索引时，且内存资源充足的前提下，不妨调整join buffer以达到性能优化的目的。
2.4.2 索引失效

对于索引失效应该尽可能地避免。

2.4.2.1 创建测试表

测试表的SQL如下：
```
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(24) DEFAULT NULL COMMENT'姓名',
    `age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
    `pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
    `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES(NULL,23,'test',NOW());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`); # 创建索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
```
2.4.2.2 索引失效的各种情况

（1）全值匹配最好

查询的字段按照顺序在索引中都可以匹配，此时速度最快。

比如上面的测试用例中创建了复合索引(name, age, pos)，那么条件的参数（where name=xxx and age=xxx and pos=xxx）和索引中的字段个数相同并且顺序一致（name对应name，age对应age，pos对应age），那么情况最好，如下：

（2）最佳左前缀法制

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

注：最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

过滤条件要使用索引必须按照索引建立的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用

多列索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引。

如果是多列复合索引，那么下面这些是有效的：
```
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age = 15;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age = 15 and pos= 'dev';
# 注意，这样的情况也是正确使用索引的
EXPLAIN SELECT * FROM staffs WHERE age = 15 and pos= 'dev' and NAME = 'July';
1
2
3
4
5
```
如果是下面这些不按照顺序的，那么就是无效的：
```
EXPLAIN SELECT * FROM staffs WHERE age=25 and pos='dev';
EXPLAIN SELECT * FROM staffs WHERE pos='dev';
1
2
```
（3）不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

如下示例：
```
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME,4) = 'July'; -- 使用函数
EXPLAIN SELECT * FROM staffs WHERE NAME = '2000';
EXPLAIN SELECT * FROM staffs WHERE NAME = 2000; -- 使用类型转换
1
2
3
4
```
（4）存储引擎不能使用索引中范围条件右边的列

使用范围查询（如>、<、in等）后，如果范围内的记录过多，会导致索引失效，因为从自定义索引映射到主键索引需要耗费太多的时间，反而不如全表扫描来得快

建议：将可能做范围查询的字段的索引顺序放在最后
```
EXPLAIN SELECT * FROM staffs WHERE name='July' and age=23 and pos='dev';
EXPLAIN SELECT * FROM staffs WHERE name='July' and age>21 and pos='dev'; -- 使用范围查询
1
2
```
注：联合索引中，出现范围查询(>、<)，范围查询右侧的列索引失效。所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <。

**（5）尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select **

使用覆盖索引（Using index）会提高检索效率：只访问索引列的查询（索引列和查询列一致，尽量不使用select *）。

即如果复合索引列（下例：name,age,pos）和*的字段个数相同，那么建议使用字段名检索而不是星号（ * ）。

例如：
```
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age = 23 and pos= 'dev'; -- 应该减少select *
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = 'July' and age = 23 and pos= 'dev'; -- 尽量使用覆盖索引
1
2
```
（6）mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

如：
```
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'; -- 使用=等号
EXPLAIN SELECT * FROM staffs WHERE NAME != 'July'; -- 使用!=不等号 
EXPLAIN SELECT * FROM staffs WHERE NAME <> 'July'; -- 使用<>不等号
1
2
3
```
（7）is null，is not null也无法使用索引

如果允许字段为空，则
- IS NULL 不会导致索引失效
- IS NOT NULL 会导致索引失效
  
  EXPLAIN SELECT * FROM staffs WHERE name IS NULL; – IS NULL的情况
  EXPLAIN SELECT * FROM staffs WHERE name IS NOT NULL; – IS NOT NULL的情况
（8）like以通配符开头（‘%abc…’），mysql索引失效，变成全表扫描的操作

如下：
```
EXPLAIN SELECT * FROM staffs WHERE NAME like '%July%'; -- 左右都有通配符
EXPLAIN SELECT * FROM staffs WHERE NAME like '%July';  -- 模糊查询加左边
EXPLAIN SELECT * FROM staffs WHERE NAME like 'July%';  -- 模糊查询加右边
1
2
3
```
由上图可知，如果要使用模糊查询，那么尽可能让通配符%加在字符串的右边，这样不会让索引失效。

如果实在必须要用like '%字符串%'并且索引又不失效，那么可以使用覆盖索引（查询的字段尽量和索引字段匹配）。

即SELECT后面的字段是索引列字段。

（9）字符串不加单引号索引失效

如：
```
EXPLAIN SELECT * FROM staffs WHERE NAME = '2000';
EXPLAIN SELECT * FROM staffs WHERE NAME = 2000; -- 如果不加单引号，就会发生类型转换，导致索引失效
1
2
```
（10）少用or，用它来连接时会索引失败

如：
```
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' OR NAME = 'z3'; -- 使用OR导致索引失效
1
```
注：用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。只有当 or 连接的条件，左右两侧字段都有索引时，索引才会生效。

（11）数据分布影响

如果 MySQL 认为使用索引比全表更慢，则不使用索引。

MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

因此，is null 、is not null是否走索引，得具体情况具体分析，并不是固定的。

2.4.3 小总结

可以创建一张表进行测试：
```
CREATE TABLE abcd(
	a VARCHAR(10);
	b VARCHAR(10);
	c VARCHAR(10);
	d VARCHAR(10);
);
INSERT INTO abcd(a,b,c,d) VALUES('1','2','3','4');
INSERT INTO abcd(a,b,c,d) VALUES('1','kkkk','4','5');
1
2
3
4
5
6
7
8
```
2.4.4 优化口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

2.4.5 面试题讲解

SQL题目如下：
```
#【建表语句】
create table test03 (
    id int primary key not null auto_increment, 
    c1 varchar(10),
    c2 varchar(10),
    c3 varchar(10),
    c4 varchar(10),
    c5 varchar(10)
);

insert into test03 (c1, c2, c3, c4, c5) values ('a1', 'a2', 'a3', 'a4', 'a5');
insert into test03 (c1, c2, c3, c4, c5) values ('b1', 'b2', 'b3', 'b4', 'b5');
insert into test03 (c1, c2, c3, c4, c5) values ('c1', 'c2', 'c3', 'c4', 'c5');
insert into test03 (c1, c2, c3, c4, c5) values ('d1', 'd2', 'd3', 'd4', 'd5');
insert into test03 (c1, c2, c3, c4, c5) values ('e1', 'e2', 'e3', 'e4', 'e5');

select * from test03;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
```
题目练习：
```
#索引建立
create index idx_test03_c1234 on test03(c1, c2, c3, c4);
show index from test03;

#问题：我们创建了复合索引 idx_test03_c1234, 根据一下 SQL 分析下索引使用情况？

explain select * from test03 where c1 = 'a1';
explain select * from test03 where c1 = 'a1' and c2 = 'a2';
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3';
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';

#1) Yes
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';

#2) Yes
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';

#3) Yes(5.6+ Using index condition)
explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';

#4) Yes(5.6+ Using index condition)
explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';

#5)Yes(5.6+ Using index condition)
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
#c3 的作用在于排序而不是查找

#6）Yes(5.6+ Using index condition)
explain select * from test03 where c1='c1' and c2='c2' order by c3;

#7) Yes(5.6+ Using index condition)
explain select * from test03 where c1='c1' and c2='c2' order by c4;

#8) Yes(5.6+ Using index condition, Using where)
explain select * from test03 where c1='a1' and c5='a5' order by c2, c3;

#9) NO (Using index condition; Using where; Using filesort)
explain select * from test03 where c1='a1' and c5='a5' order by c3, c2;

#10) Yes(5.6+ Using index condition)
explain select * from test03 where c1='a1' and c2='a2' order by c2, c3;

#11) Yes(Using index condition; Using where)
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2, c3;

#12) Yes(5.6+ Using index condition)
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3, c2;
#本例有常量c2的情况，和#9对比

#13) No (Using index condition; Using where; Using filesort)
explain select * from test03 where c1='a1' and c5='a5' order by c3, c2;

#14) Yes(Using where; Using index)
explain select c2, c3 from test03 where c1='a1' and c4='a4' group by c2, c3;

#15) NO (Using where; Using index; Using temporary; Using filesort)
explain select c2, c3 from test03 where c1='a1' and c4='a4' group by c3, c2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
```
总结：
- 定值、范围还是排序，一般order by是给一个范围
- group by基本上都是需要排序的，会有临时表产生
2.4.6 一般性建议
- 对于单键索引,尽量选择针对当前query过滤性更好的索引。
- 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。
2.5 其他索引情况

 2.5.1 SQL 提示

如图，问题如下：

图上选择的使用的复合索引是 MySQL 自己选择的，如果我们希望在查询的时候，自己来指定使用哪个索引。就需要用到 SQL 提示了。

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。例如强制要求使用指定索引。
- use index：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。
语法如下：
```
# 语法
select 查询字段列表 from 表名 use index(索引名) where 条件语句;
# 示例，建议使用 idx_user_pro 索引
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
1
2
3
4
```
- ignore index：忽略指定索引。表示不使用指定的索引。
语法如下：
```
# 语法
select 查询字段列表 from 表名 ignore index(索引名) where 条件语句;
# 示例，建议使用 idx_user_pro 索引
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
1
2
3
4
```
- force index：强制使用索引，强制使用某个指定的索引。
语法如下：
```
# 语法
select 查询字段列表 from 表名 force index(索引名) where 条件语句;
# 示例，建议使用 idx_user_pro 索引
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
1
2
3
4
```
2.5.2 覆盖索引

尽量使用覆盖索引，减少使用 select *。

覆盖索引：是指select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。如对 profession、age、status 字段建立了索引，如果使用 select profession,age,status 只查询这三列而非 select * 或者 select name,profession,age,status 就是覆盖索引，因为查询的字段覆盖了索引字段。

当前 tb_user 表的索引情况如下：

看看下面这组 SQL 语句的执行计划，对其进行分析：
```
explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select id,profession,age, status from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select id,profession,age, status, name from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '软件工程' and age = 31 and status= '0';
1
2
3
4
```
上述这几条SQL的执行结果为：

从上述的执行计划我们可以看到，这四条 SQL 语句的执行计划前面所有的指标都是一样的，看不出来差异。但是此时，我们主要关注的是后面的 Extra 字段，前面两个 SQL 的结果为 Using where; Using Index; 而后面两条 SQL 的结果为: Using index condition。
- Using where; Using Index：表示查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。
- Using index condition：表示查找使用了索引，但是需要回表查询数据。
因为，在 tb_user 表中有一个联合索引 idx_user_pro_age_sta，该索引关联了三个字段 profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主键 id。所以当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引直接返回数据了。如果超出这个范围，就需要拿到主键 id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。而我们如果一直使用 select * 查询返回所有字段值，很容易就会造成回表查询（除非是根据主键查询，此时只会扫描聚集索引）。

注：在我本地环境的 MySQL 中执行结果如图，跟视频中的有所差别（但 Using index 表示使用了覆盖索引不需要回表查询，而 NULL 表示索引条件查询需要回表查数据，所以大体是相同的）：

为了大家更清楚的理解，什么是覆盖索引，什么是回表查询，我们一起再来看下面的这组 SQL 的执行过程。
- A. 表结构及索引示意图
id 是主键，是一个聚集索引。name 字段建立了普通索引，是一个二级索引（辅助索引）。
- B. 执行 SQL: select * from tb_user where id = 2;
根据 id 查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
- C. 执行 SQL：selet id,name from tb_user where name = 'Arm';
虽然是根据 name 字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在 name 的二级索引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。
- D. 执行 SQL：selet id,name,gender from tb_user where name = 'Arm';
由于在 name 的二级索引中，不包含 gender 字段，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。

2.5.3 前缀索引

2.5.3.1 概述

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘 IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

注：即如果某一列是较长文本字符串时，可以对其开头的几个字符组成的子串建立前缀索引，方便检索。

2.5.3.2 语法

建立前缀索引的语法如下：
```
# 语法
create index 索引名 on 表名(列名(前缀索引长度));
# 示例
create index idx_email_5 on tb_user(email(5));
1
2
3
4
```
2.5.3.3 前缀索引长度

我们必须得考虑什么样的前缀索引长度才是最合适的，长度大了或者小了都不太合适。

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

如下例来进行计算较为合适：
```
# 计算不重复的 email 字段个数占总数的比例
select count(distinct email)/count(*) from tb_user;
# 计算 email 字段前五个字符组成的子串不重复的个数占总数的比例
select count(distinct substring(email,1,5))/count(*) from tb_user;
1
2
3
4
```
2.5.3.4 前缀索引的查询流程

如图模拟了前缀索引的查询流程：

2.6 索引设计原则

设计索引时遵循一些原则：
1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询。
第3章查询截取分析

分析：
- 1、观察，至少跑1天，看看生产的慢SQL情况。
- 2、开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
- 3、 explain+慢SQL分析
- 4、 show profile
- 5、运维经理or DBA，进行SQL数据库服务器的参数调优。
总结：
- 1、慢查询的开启并捕获
- 2、 explain+慢SQL分析
- 3、 show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
- 4、 SQL数据库服务器的参数调优。
3.0 插入优化

 3.0.1 insert

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化：
```
insert into 表名 values(值1,值2,...);
insert into 表名 values(值1,值2,...);
insert into 表名 values(值1,值2,...);
...
1
2
3
4
```
3.0.1.1 优化方案一

多条 SQL 语句不如一条 SQL 语句批量插入数据。
```
# 语法
insert into 表名 values(值1,值2,...),(值1,值2,...),(值1,值2,...),...;
1
2
```
例如：

3.0.1.2 优化方案二

手动控制事务。先手动开启事务，插入多条记录，再提交事务。
```
# 开启事务
start transaction;
# 多条插入语句
insert into 表名 values(值1,值2,...);
insert into 表名 values(值1,值2,...);
insert into 表名 values(值1,值2,...);
...
# 提交事务
commit;
1
2
3
4
5
6
7
8
9
```
例如：

3.0.1.3 优化方案三

主键顺序插入，性能要高于乱序插入。因为主键顺序插入方便快速建立索引。如：
```
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
1
2
```
3.0.2 大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用 insert 语句插入性能较低，此时可以使用 MySQL 数据库提供的 load 指令进行插入。操作如下：

可以执行如下指令，将数据脚本文件中的数据加载到表结构中：
```
# 首先在客户端连接服务端时，加上参数 -–local-infile，然后输入密码进行登录到 MySQL
mysql --local-infile -u root -p

# 接着设置全局参数 local_infile 为 1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

# 最后执行 load 指令将准备好的数据，加载到表结构中
load data local infile '数据文件路径' into table 表名 fields terminated by '列字段分隔符' lines terminated by '行字段分隔符';
# 例如：将 /root/sql1.log 文件中的数据大批量插入到表 tb_user 中，其中每行通过换行符 '
' 进行分隔，每行中的每个字段通过逗号 ',' 进行分隔
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '
';
1
2
3
4
5
6
7
8
9
10
11
12
```
示例如下：

注：在 load 时，主键顺序插入性能高于乱序插入

 3.1 查询优化

 3.1.1 永远小表驱动大表
```
# 优化原则：小表驱动大表， 即小的数据集合驱动大的数据集合
##################### 原理 （RBO）###############

select * from A where id in (select id from B)
#等价于
for select id from B
for select id from A where A.id = B.id

# 当B表的数据集必须小于A表的数据集时，用in 优于 exists
select * from A where exists (select 1 from B where B.id = A.id)
#等价于
for select id from A
for select id from B where B.id = A.id

#当 A 表的数据集系小于表的数据集， 用 exists 优于 in
#注意： A与B表的id 字段应该建立索引


## exists
select ... from table where exists (subquery);
# 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或者 FALSE）来决定主查询数据结果是否得到保留。

## 提示
# 1，EXISTS (subquery) 只返回 True 或 False ， 因此查询的 SELET * 也可以是SELET 1 或其他， 官方说法是执行时会忽略SELECT 清单， 因此没有区别
# 2. EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解的逐条比对，如果担忧效率问题，可以进行实际检验以确定是否有效率问题。
# 3. EXISTS 子查询往往也可以使用条件表达式、其他子查询或者 JOIN 来代替，何种最优化需要具体分析。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
```
优化原则：小表驱动大表

 3.1.2 order by关键字优化

3.1.2.1 order by优化

首先创建测试表：
```
# 创建表
create table tblA(
    #id int primary key not null auto_increment,
    age int,
    birth timestamp not null
);

# 插入测试数据
insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());

# 创建索引
create index idx_A_ageBirth on tblA(age, birth);# 创建复合索引age和birth
1
2
3
4
5
6
7
8
9
10
11
12
13
14
```
查看执行计划：案例一
```
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age,birth;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth,age;
EXPLAIN SELECT * FROM tblA WHERE birth > '2021-02-19 22:45:00' ORDER BY birth;
EXPLAIN SELECT * FROM tblA WHERE birth > '2021-02-19 22:45:00' ORDER BY age;
1
2
3
4
5
6
```
查看执行计划：案例二
```
EXPLAIN SELECT * FROM tblA ORDER BY age ASC,birth DESC; #排序不一致，要么都是升序或者都是降序
1
```
最后得出结论：
- MySQL支持两种方式的排序，index和filesort。index效率高，它是指扫描索引本身完成排序，filesort效率低。
- ORDER BY子句，尽量使用Index方式排序，避免filesort方式排序。
- ORDER BY子句满足两种情况，会使用index排序，一是ORDER BY子句采用遵照最佳左前缀法则，二是where条件字段和ORDER BY子句组合起来，满足最佳左前缀法则排序分组优化
- Using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
- Using index: 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
- 对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。排序。有两种排序算法：双路排序和单路排序。
3.1.2.2.1 双路排序

MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出.
从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。

简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，IO 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。

3.1.2.2.2 单路排序

从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

3.1.2.2.3 存在的问题

单路排序存在的问题：在 sort_buffer 中，方法 B（单路排序）比方法 A（双路排序）要多占用很多空间，因为方法 B 是把所有字段都取出, 所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取取 sort_buffer 容量大小，再排……从而多次 I/O。也就是本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

3.1.2.3 优化策略
- 增大sort_buffer_size参数的设置
  
  不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M 之间调整。
- 增大max_length_for_sort_data参数的设置
  
  mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data，提高这个参数，会增加使用改进算法的概率。
  
  但是如果设的太高，数据总容量超出 sort_buffer_size 的概率反而会增大，就会出现高频磁盘 I/O 和低的处理器使用率。（1024-8192 之间调整）
- 减少 select 后面的查询的字段（少用select ）
  
  查询的字段减少，缓冲就能容纳更多的内容，也就相当于间接增大了sort_buffer_size。
  
  当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|IBLOB类型时，会用改进后的算法一—单路排序，否则用老算法——多路排序。
  
  两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
所以 order by 优化原则:
- A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- B. 尽量使用覆盖索引。
- C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
- D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。
3.1.2.4 总结
```
为排序使用索引：
（1）MySQL两种排序方式：文件排序或扫描有序索引排序
（2）MySQL能为排序与查询使用相同的索引。（因为索引有两个作用：排序和查找）
例如：key a_b_c(a,b,c) #为a,b,c三个字段创建复合索引

## 第一种情况：ORDER BY能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c #相当于ORDER BY a ASC,b ASC,c ASC
- ORDER BY a DESC,b DESC,c DESC

## 第二种情况：如果WHERE使用索引的最左前缀定义为常量（如 WHERE name='张三'），则ORDER BY 能使用索引
- WHERE a=const ORDER BY b,c #也按照了索引顺序：a,b,c
- WHERE a=const AND b=const ORDER BY c #也按照了索引顺序：a,b,c
- WHERE a=const ORDER BY b,c #也按照了索引顺序：a,b,c
- WHERE a=const AND b>const ORDER BY b,c #虽然b>const会导致后面的索引失效，但前面的常量加上ORDER BY后面的字段也符合索引顺序：a,b,c

## 第三种情况：不能使用索引进行排序的情况
- ORDER BY a ASC,b DESC,c DESC #排序不一致，既存在升序，也存在降序
- WHERE g=const ORDER BY b,c #丢失a索引，不能使用索引排序
- WHERE a=const ORDER BY c #丢死b索引，也不能使用索引排序
- WHERE a=const ORDER BY a,d #d不是索引的一部分，也不能使用索引排序
- WHERE a in (...) ORDER BY b,c #对于排序来说，多个相等条件也是范围查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
```
3.1.3 group by关键字优化

group by优化和order by优化大致相似。

group by实质是先排序后进行分组，遵照索引建的最佳左前缀。

当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。

where高于having，能写在where限定的条件就不要去having限定了。

要想在排序时使用索引，避免 Using filesort，可以采用索引覆盖。

ORDER BY /GROUP BY后面字段的顺序要和复合索引的顺序完全一致。

ORDER BY /GROUP BY后面的索引必须按照顺序出现，排在后面的可以不出现。

要进行升序或者降序时，字段的排序顺序必须一致。不能一部分升序，一部分降序，可以都升序或者都降序。

如果复合索引前面的字段作为常量出现在过滤条件中，排序字段可以为紧跟其后的字段。

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：
- A. 在分组操作时，可以通过索引来提高效率。
- B. 分组操作时，索引的使用也是满足最左前缀法则的。
3.1.4 主键优化

引入：主键顺序插入的性能是要高于乱序插入的。那么这是为什么？主键又该如何设计呢？

3.1.4.1 数据组织方式

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。如图行数据都是按照主键顺序组织存放的：

行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：

在 InnoDB 引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认 16K。那也就意味着，一个页中所存储的行也是有限的，如果插入的数据行 row 在该页存储不下，将会存储到下一个页中，页与页之间会通过指针连接。

3.1.4.2 页分裂

在 InnoDB 引擎中，每一页可以为空，也可以只填充一半，也可以填充满。每页包含了 2-N 行数据（如果一行数据过大则会溢出），根据主键排列。

主键顺序插入的效果如图：
- ①从磁盘中申请页，主键顺序插入
- ②第一个页没有满，继续往第一页插入
- ③当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接
- ④当第二页写满了，再往第三页写入
主键乱序插入的效果如图：
- ①假如 1#、2# 页都已经写满了，存放了如图所示的数据
- ②此时再插入id为50的记录，我们来看看会发生什么现象，并不会开启一个新页
- ③因为，索引结构的叶子节点是有顺序的。按照顺序，应该存储在 47 之后。
- ④但是 47 所在的 1# 页，已经写满了，存储不了 50 对应的数据了。那么此时会开辟一个新的页 3#。
- ⑤但是并不会直接将 50 存入 3# 页，而是会将 1# 页后一半的数据，移动到 3# 页，然后在 3# 页，插入 50。
- ⑥移动数据，并插入 id 为 50 的数据之后，那么此时，这三个页之间的数据顺序是有问题的。1# 的下一个页，应该是 3#，3# 的下一个页是 2#。所以，此时，需要重新设置链表指针。
- ⑦上述的这种现象，称之为 “页分裂”，是比较耗费性能的操作。
注：如果不按主键顺序插入，很容易发生页分裂，是很耗费性能的操作。

3.1.4.3 页合并

如果说在插入时可能发生页分裂，那么在删除时就可能会发生页合并。

假如表中已有数据的索引结构(叶子节点)如下：
- ①当我们对已有数据进行删除时，具体的效果如下：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
- ②当我们继续删除2#的数据记录：
- ③当页中删除的记录达到 MERGE_THRESHOLD（默认为页的 50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
- ④删除数据，并将页合并之后，再次插入新的数据 21，则直接插入 3# 页。
- ⑤这个里面所发生的合并页的这个现象，就称之为 “页合并”。
3.1.4.4 索引设计原则

所以在设计主键时可以遵循以下原则：
- 满足业务需求的情况下，尽量降低主键的长度。
- 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键。
- 尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号。
- 业务操作时，避免对主键的修改。
3.1.5 limit 优化

3.1.5.1 问题引入

MySQL 高级笔记

docx 5星超过95%的资源 14.70MB

下载

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。来看看执行limit分页查询耗时对比：

通过测试我们会看到，越往后，分页查询效率越低，这就是分页查询的问题所在。

因为，当在进行分页查询时，如果执行 limit 2000000,10，此时需要 MySQL 排序前 2000010 记录，仅仅返回 2000000-2000010 的记录，其他记录丢弃，查询排序的代价非常大。

3.1.5.2 优化思路

优化思路: 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
```
explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
1
```
3.1.6 count 优化

3.1.6.1 问题引入

在之前的测试中，我们发现，如果数据量很大，在执行 count 操作时，是非常耗时的。
```
select count(*) from tb_user ; 
1
```
关于 count(*) 在不同存储引擎中的处理：
- MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；但是如果是带条件的count，MyISAM也慢。
- InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
3.1.6.2 优化思路

如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于 redis 这样的数据库进行，但是如果是带条件的count又比较麻烦了)。

3.1.6.3 count 用法

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值。

用法：
- count(*)
- count(主键)
- count(字段)
- count(数字)
count用法

含义

count(主键)

InnoDB 引擎会遍历整张表，把每一行的主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)

count(字段)

没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

count(数字)

InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。

count(*)

InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

注：按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count()。

3.1.7 update 优化

需要注意一下update语句执行时的注意事项：
```
update course set name = 'javaEE' where id = 1 ; 
1
```
当我们在执行删除的SQL语句时，会锁定 id 为 1 这一行的数据，然后事务提交之后，行锁释放。

当我们在执行如下SQL时：
```
# name 字段没有加索引，会升级成表锁
update course set name = 'SpringBoot' where name = 'PHP' ; 
1
2
```
当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。导致该update语句的性能大大降低。

注：InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁。

3.2 慢查询日志

 3.2.1 是什么

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志，默认未开启。long_query_time 默认为10 秒，最小为 0，精度可以到微秒。
- 慢查询日志是MySQL提供的一种日志记录，用来记录响应时间超过阀值的SQL语句。
- 如果某条SQL语句运行时间超过long_query_time设定的值，就会被记录到慢查询日志中。
- long_query_time的默认值为 10（10秒）。
- 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。
3.2.2 使用

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。

如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会将SQL语句写入日志，因此或多或少带来一定的性能影响。

SQL语句

说明

SHOW VARIABLES LIKE ‘%slow_query_log%’;

查询慢查询日志是否开启，默认为OFF表示未开启

SET GLOBAL slow_query_log=1;

开启慢查询日志

SET GLOBAL slow_query_log=0;

关闭慢查询日志

SHOW VARIABLES LIKE ‘long_query_time%’;

查询慢查询设定阈值，默认为10秒

SET long_query_time=5;

设定慢查询阈值为5秒，可以设置为几秒，单位为秒

注意：
- set global slow_query_log=1 开启慢查询日志，仅对当前数据库生效，MySQL重启后失效。
- 如果需要永久生效，则需要修改 my.ini 配置文件（Windows系统下的文件）或 /etc/my.cnf 配置文件（Linux系统下的文件）。
- 下面以Linux系统配置永久慢查询日志为例：
  
  下面的配置语句是添加在[mysqld]标签下的
  
  [mysqld]
  
  配置慢查询日志
  
   开启慢查询日志，1表示开启，0表示关闭，默认关闭
  
  slow_query_log=1
  
  设置日志路径，日志路径通常由在mysql下执行SHOW VARIABLES LIKE ‘%slow_query_log%’;命令>得到的slow_query_log_file参数的值
  
  slow_query_log_file=/var/lib/mysql/localhost-slow.log
  
  设置慢查询阈值为5秒，当有查询SQL执行时间超过5秒就会被记录在慢查询日志中
  
  long_query_time=5
  log_output=FILE
退出保存后执行 service mysqld restart（CentOS 7以下版本）或者systemctl restart mysqld（CentOS 7 及以上版本）命令重启mysql服务，再次进入mysql中查看已经设置成功了

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用 log_slow_admin_statements 和 log_queries_not_using_indexes 更改此行为，如下所述：
```
#记录执行较慢的管理语句
log_slow_admin_statements =1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1
1
2
3
4
```
注：在慢查询日志中，只会记录执行时间超多我们预设时间的 SQL，执行较快的 SQL 是不会记录的。通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。

3.2.3 日志分析工具mysqldumpslow

生产环境中手工查找，分析日志，非常的耗费时间，因此MySQL提供了日志分析工具mysqldumpslow。

通过mysqldumpslow --help命令可以查看该工具的帮助：

帮助信息的各参数说明：
- -s：是表示按照何种方式排序。
- -c：访问次数。
- -l：锁定时间。
- -r：返回记录。
- -t：查询时间。
- -al：平均锁定时间。
- -ar：平均返回记录数。
- -at：平均查询时间。
- -t：即为返回前面多少条的数据。
- -g：后面搭配一个正则表达式，大小写不敏感。
工作常用参考案例：
```
-- 获取返回集最多的10条SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
-- 获取访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
-- 获取按时间排序的前10条含有LEFT JOIN的SQL语句
mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/localhost-slow.log
-- 结合|more使用，否则有可能会爆屏
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log |more
1
2
3
4
5
6
7
8
```
3.3 批处理数据脚本

批处理数据脚本是为了插入百万、千万条数据的脚本。

第一步：创建测试表：
```
create database big_data;
use big_data;

# dept
create table dept(
    id int primary key auto_increment,
    deptno mediumint not null default 0,
    dname varchar(20) not null default '',
    loc varchar(13) not null default ''
) engine = innodb default charset = utf8;

# emp
create table emp(
    id int primary key auto_increment,
    empno mediumint not null default 0,
    ename varchar(20) not null default '',
    job varchar(9) not null default '' comment '工作',
    mgr mediumint not null default 0 comment '上级编号',
    hirdate date not null comment '入职时间',
    sal decimal(18,2) not null comment '薪水',
    comm decimal(18,2) not null comment '红利',
    deptno mediumint not null default 0 comment '部门编号'
) engine = innodb default charset = utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
```
第二步：创建函数，保证每条数据都不相同，产生随机字符串和随机部门编号：
```
#随机字符串函数
delimiter $$
create function rand_str(n int) returns varchar(255)
begin 
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    declare return_str varchar(100) default '';
    declare i int default 0;
    while i < n do 
        set return_str = concat(return_str, substring(chars_str, floor(1 + rand() * 52) ,1));
        set i = i+1;
    end while;
    return return_str;
end $$
DELIMITER ;
#删除函数
#drop function if exists rand_str;
#使用函数
select rand_str(5);


#用于随机产生多少到多少的编号
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
    RETURN i;
END$$
DELIMITER ;
#删除函数
#drop function if exists rand_num;
#使用函数
select rand_num(100,100000);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
```
第三步：创建存储过程
```
-- 为dept创建存储过程
delimiter $$
create procedure insert_dept(in start int(10), in max_num int(10))
begin 
    declare i int default 0;
    set autocommit = 0;#设置不让一次插入后就自动提交，批量插入的话如果每次都自动提交那么会连接无数次，耗费性能
    repeat
    set i = i+1;
    insert into dept(deptno, dname, loc) values ((start+i), rand_num() , rand_str(6));
    until i = max_num
    end repeat;# 结束循环
    commit;# 最后批量插入后再插入
end $$
delimiter ;

-- 为emp创建存储过程
delimiter $$
create procedure insert_emp(in start int(10), in max_num int(10))
begin 
    declare i int default 0;
    set autocommit = 0;
    repeat 
    set i = i+1;
    insert into emp (empno, ename, job, mgr, hirdate, sal, comm, deptno) values ((start+i), rand_str(6), 'SALESMAN', 001, curdate(), 2000, 400, rand_num(100,50000));
    until i = max_num
    end repeat;
commit;
end $$
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
```
第四步：调用存储过程批量插入数据
```
# 添加数据到部门表
#执行存储过程，往 dept 表添加 1 万条数据
CALL insert_dept(0,10000);

# 添加数据到员工表
CALL insert_emp(10,50000);
1
2
3
4
5
6
```
3.4 Show Profile

3.4.1 是什么

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。即 profile 可以帮助我们一条 SQL 语句到底在哪些方面耗费了时间。

Show Profiles是MySQL提供，可以分析SQL语句执行的资源消耗情况，可用于SQL调优。

通过配置profiling参数启用SQL剖析，该参数可以在全局和session级别来设置。

全局级别作用于整个MySQL实例，而session级别只影响当前回话。

该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等

Show profiles是5.0.37之后添加的，要想使用此功能，要确保MySQL版本 > 5.0.37。

官网：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

3.4.2 使用

默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

第一步：查看是否支持，即查看当前的mysql版本是否支持。

执行 show variables like 'profiling' 或者 select @@have_profiling; 命令查询是否启用，为OFF表示未启用

第二步：由于默认是关闭的，使用前需要开启。

执行 set profiling=on 或者 set profiling = 1; 命令开启profile。

第三步：运行SQL
```
# 随便写条查询SQL查看效果
SELECT id%10 from emp GROUP BY id%10 LIMIT 10000;
1
2
```
第四步：执行show profiles;

第五步：诊断SQL
```
# 语法
show profile [type] for query 上一步前面问题的SQL数字号码;
# type参数说明
# all 表示显示全部开销信息
# block io 表示显示块IO相关开销
# contexxtswitchaes 表示显示上下文切换相关开销
# cpu 表示显示CPU相关的开销信息
# ipc 表示显示发生和接收相关的信息
# memory 表示显示内存相关的信息
# page faults 表示显示页面错误相关的开销信息
# source 表示显示和source_function,source_file,source_line相关的开销信息
# swaps 表示显示交换次数相关的开销的信息
# 示例
show profile cpu,block io for query 35;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
```
第六步：日常开放需要注意的事项
- ①converting HEAP to MyISAM：出现了这个表示查询结果太大，内存都不够用了，往磁盘上搬了。
- ②Create tmp table：出现这个表示创建了临时表。
- ③Copying to tmp table on disk：把内存中的临时表复制到磁盘，需要优化了。
- ④locked：表示锁。
出现上面这四种情况，就需要考虑优化该条SQL语句了。

3.5 全局查询日志

注意：永远不要在生产环境中开启此功能。

注意：永远不要在生产环境中开启此功能。

注意：永远不要在生产环境中开启此功能。

只能在测试环境中使用，一旦开启，会记录每条查询SQL。有如下两种启用方式：

3.5.1 配置文件来启用

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是未开启的。

在mysql的 /etc/my.cnf 配置文件中的 [mysqld] 标签下添加如下内容：
```
# 开启，默认是关闭的
general_log=1
# 记录日志文件的路径
general_log_file=/var/lib/mysql/localhost.log
# 输出格式
log_output=FILE
1
2
3
4
5
6
```
开启了查询日志之后，在MySQL的数据存放目录，也就是 /var/lib/mysql/ 目录下就会出现 localhost.log 文件。之后所有的客户端的增删改查操作都会记录在该日志文件之中，长时间运行后，该日志文件将会非常大。

3.5.2 编码来启用

在mysql环境下执行如下语句来启用全局查询日志：
```
set global general_log=1; #开启配置文件
set global log_output='TABLE'; #设置后，所以执行的SQL语句，都会被记录在mysql库的general_log表中，可以用下面的语句查看
select * from mysql.general_log;
1
2
3
```
3.6 错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log。可以通过如下 SQL 语句来查看错误日志文件的位置：
```
show variables like '%log_error%';
1
```
我们来不输入密码登录，查看它的日志情况：

3.7 二进制日志

 3.7.1 概述

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

该日志文件的作用：
- ①灾难时的数据恢复。
- ②MySQL的主从复制。
在 MySQL8 版本中，默认二进制日志是开启着的。可以通过如下 SQL 语句查看与二进制日志文件相关的参数：
```
show variables like '%log_bin%';
1
```
如果没有开启，则可以执行 set global log_bin=1; 语句进行开启。参考：
在 MySQL 中执行如下命令启动二进制日志文件报错“ERROR 1238 (HY000): Variable ‘log_bin‘ is a read only variable“
。

注意，关于 log_bin 的参数说明：
- log_bin_basename：当前数据库服务器的 binlog 日志的基础名称(前缀)，具体的binlog文件名需要再该basename的基础上加上编号(编号从000001开始)。
- log_bin_index：binlog的索引文件，里面记录了当前服务器关联的binlog文件有哪些
3.7.2 格式

我们可以对二进制日志文件中输出内容的日志格式进行设置，即是显示对数据进行修改的 SQL 语句还是显示每一行的实际数据变化。

我们可以通过 binlog-format 变量来查看二进制日志的格式：
```
show variables like '%binlog_format%';
1
```
binlog_format 变量可以设置的值有如下几个：

日志格式

含义

STATEMENT

基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。

ROW

基于行的日志记录，记录的是每一行的数据变更。（默认）

MIXED

混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。

如果我们要配置二进制日志的格式，需要在配置文件 /etc/my.cnf 中配置 binlog_format 参数。例如：
```
# 修改二进制日志文件输出格式为 ROW
binlog_format=ROW
1
2
```
注意，修改后要记得重启 MySQL 服务。

3.7.3 查看

由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看。

该命令的具体语法如下：
```
# 语法
mysqlbinlog [ 参数选项 ] logfilename
	参数选项：
		-d 指定数据库名称，只列出指定的数据库相关操作。
		-o 忽略掉日志中的前n行命令。
		-v 将行事件(数据变更)重构为SQL语句
		-vv 将行事件(数据变更)重构为SQL语句，并输出注释信息

# 示例，其中 /var/lib/mysql/mysql-bin　.000001 是二进制日志文件的路径
mysqlbinlog -vv /var/lib/mysql/mysql-bin　.000001
1
2
3
4
5
6
7
8
9
10
```
注：如果要进行测试，需要执行修改数据的 SQL 语句才会被记录在二进制日志文件中。

3.7.4 删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志（在登录 MySQL 后执行如下命令）：

指令

含义

reset master

删除全部 binlog 日志，删除之后，日志编号，将从 binlog.000001重新开始

purge master logs to 'binlog.*'

删除 * 编号之前的所有日志

purge master logs before 'yyyy-mm-dd hh24:mi:ss'

删除日志为 “yyyy-mm-dd hh24:mi:ss” 之前产生的所有日志

也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。
```
# 通过如下语句查看设置的二进制日志文件过期时间
show variables like '%binlog_expire_logs_seconds%';
1
2
```
第4章 MySQL的锁机制

 4.1 概述

锁是计算机协调多个进程或线程并发访问某个资源的机制。
- 在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种共享资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题。
- 锁冲突是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言显得尤为重要，而且也更加复杂。
生活中的案例：比如在淘宝上买一件商品，商品库存只有一件，这个时候，如果有其他的卖家，那么如何解决是你买到还是别人买到的问题。

这里肯定要用到事务，我们先从库存中取出物品的数量，然后插入订单。付款后插入付款信息。然后更新商品数量，这个过程使用锁,可以对有限的资源进行保护，解决隔离和并发的矛盾。

从对数据的操作类型分为：读锁（共享锁）和写锁（排他锁）
- 读锁：针对同一份数据，对该数据的读操作可以同时进行且不受影响。
- 写锁：写操作未完成前，会阻断其他的读操作和写操作。
MySQL中的锁，按照锁的粒度分，分为以下三类：
- 全局锁：锁定数据库中的所有表。
- 表级锁：每次操作锁住整张表。
- 行级锁：每次操作锁住对应的行数据。
4.2 三锁

在数据库中有三种锁：表锁（偏读）、行锁（偏写）和页锁。

4.2.0 全局锁

4.2.0.1 介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

即使用全局锁后，只能进行读操作，不能进行任何的其他操作（修改、删除、创建等）。

典型的应用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

为什么进行全库逻辑备份，需要加全局锁？

如果不加锁，可能存在的问题：

假设在数据库中存在这样三张表: tb_stock 库存表，tb_order 订单表，tb_orderlog 订单日志表。
- 在进行数据备份时，先备份了tb_stock库存表。
- 然后接下来，在业务系统中，执行了下单操作，扣减库存，生成订单（更新tb_stock表，插入tb_order表）。
- 然后再执行备份 tb_order表的逻辑。
- 业务中执行插入订单日志操作。
- 最后，又备份了tb_orderlog表。
此时备份出来的数据，是存在问题的。因为备份出来的数据，tb_stock表与tb_order表的数据不一致(有最新操作的订单信息,但是库存数没减)。那如何来规避这种问题呢此时就可以借助于MySQL的全局锁来解决。

如果加锁，情况如下：

对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的DDL、DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性和完整性。

4.2.0.2 语法

加全局锁的语法，在登录 MySQL 后执行：
```
flush tables with read lock;
1
```
可以在加了全局锁之后进行逻辑备份。例如：
```
# 备份数据库 test 到 test.sql 文件中
mysqldump -uroot -proot test > test.sql
1
2
```
释放锁的语法：
```
unlock tables;
1
```
4.2.0.3 特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：
- 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
- 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。
在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。
```
# 备份数据库 test 到 test.sql 文件中，但不加锁
mysqldump --single-transaction -uroot -proot test > test.sql
1
2
```
4.2.1 表级锁

4.2.1.1 介绍

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类：
- 表锁
- 元数据锁（meta data lock，MDL）
- 意向锁
4.2.1.2 表锁（偏读）

4.2.1.2.1 特点

表锁的特点： MylSAM引擎使用表锁，开销小，加锁快，无死锁，锁定力度大，发生锁冲突的概率最高。并发度最低。不支持事务。

对于表锁，分为两类：
- 表共享读锁（read lock）
- 表独占写锁（write lock）
4.2.1.2.2 案例分析
- 建表SQL
插入测试要用到的表：
```
use big_data;

create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

select * from mylock;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
```
- 加读锁示例
需要开启两个会话来测试加读锁后的情况。

加读锁的语法如下：
```
# 加锁语法
lock table 表名 read|write; # read表示为该表添加读锁；write表示为该表添加写锁
# 释放锁语法
unlock tables;
1
2
3
4
```
①打开两个会话session-1和session-2，然后在session-1会话中为mylock表添加读锁。

②在两个会话中都能读取当前已经添加了读锁的mylock表。

③在session-1会话（已经加了读锁）中不能查询没有加锁的表，而其他会话如session-2能够查询或更新其他表。

④当前session-1（已经加了读锁）会话中插入或更新锁定了的表都会提示错误，其他会话中插入或更新锁定了的话会进入阻塞状态一直等待获得解锁。

⑤当前session-1（已经加了读锁）会话中释放锁，那么其他会话中陷入阻塞状态的操作也会继续完成。
- 加写锁示例
①为session-1会话中的mylock表添加写锁。

②在session-1会话（已经加了写锁）中查询其他表失败，而其他会话能够查询其他表成功。

③在session-1会话（已经加了写锁）可以更新和插入数据，而其他会话不能插入数据。

④在session-1会话（已经加了写锁）查询mylock表，而其他会话中查询mylock表会陷入阻塞状态。

⑤当前session-1（已经加了写锁）会话中释放锁，那么其他会话中陷入阻塞状态的操作也会继续完成。

4.2.1.2.3 案例结论

MylSAM在执行查询语句(select)前，会自动给涉及到的表加读锁，在执行增删改操作之前，会自动给涉及到的表加写锁。

MySQL的表级锁有两种模式：
- 表共享读锁(Table Read Lock)
- 表独占写锁(Table Write Lock)
锁类型

可否兼容

读锁

写锁

读锁

是

是

否

写锁

是

否

否

结合上表，所以对 MylSAM表进行操作，会有一下的情况:
- 1.对MyISAM表的读操作（加读锁)，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进行的写操作。
- 2.对MylSAM操作的写锁(加写锁)，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。
结论：简而言之、就是读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都阻塞

4.2.1.2.4 表锁分析

查询被锁定的表的语法：
```
# 语法
show open tables;
1
2
```
如果要分析表锁，可以通过下面的方法来对表锁进行分析：

通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。table_locks_waited变量和table_locks_immediate变量可以通过下面的SQL语句来进行查看：
```
show status like 'table%'; #查看表锁信息
1
```
在查询出来的结果集中需要关注Table_locks_immediate变量和Table_locks_waited变量，它们的说明如下：
- Table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。
- Table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况。
此外，Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作大量的更新会使查询很难得到锁，从而造成永远阻塞。

4.2.1.3 元数据锁

meta data lock , 元数据锁，简写MDL。

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

这里的元数据，大家可以简单理解为就是一张表的表结构。也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。

常见的SQL操作时，所添加的元数据锁：

对应SQL

锁类型

说明

lock tables xxx read / write

SHARED_READ_ONLY / SHARED_NO_READ_WRITE

select 、select ... lock in share mode

SHARED_READ

与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥

insert 、update、delete、select … for update

SHARED_WRITE

与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥

alter table ...

EXCLUSIVE

与其他的MDL都互斥

演示：
- 当执行SELECT、INSERT、UPDATE、DELETE等语句时，添加的是元数据共享锁（SHARED_READ / SHARED_WRITE），之间是兼容的。
- 当执行SELECT语句时，添加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁（EXCLUSIVE），之间是互斥的。
- 我们可以通过下面的SQL，来查看数据库中的元数据锁的情况：
  
  select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;
4.2.1.4 意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简单分析一下：
- 首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁。
- 当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低。
有了意向锁之后：
- 客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。
- 而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。
关于意向锁的分类如下：
- 意向共享锁(IS): 由语句 select ... lock in share mode 添加。与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
- 意向排他锁(IX): 由 insert、update、delete、select...for update 添加。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。
注：一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

可以通过以下SQL，查看意向锁及行锁的加锁情况：
```
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
1
```
演示：
- A.意向共享锁与表读锁是兼容的
- B.意向排他锁与表读锁、写锁都是互斥的
4.2.2 行锁（偏写）

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：
- 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
- 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
- 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
InnoDB实现了以下两种类型的行锁：
- 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相数据集的排它锁。
- 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
两种行锁的兼容情况如下:

常见的SQL语句，在执行时，所加的行锁如下：

SQL

行锁类型

说明

INSERT ...

排他锁

自动加锁

UPDATE ...

排他锁

自动加锁

DELETE ...

排他锁

自动加锁

SELECT（正常）

不加任何锁

SELECT ... LOCK IN SHARE MODE

共享锁

需要手动在SELECT之后加LOCK IN SHARE MODE

SELECT ... FOR UPDATE

排他锁

需要手动在SELECT之后加FOR UPDATE

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。
- 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
- InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。
可以通过以下SQL，查看意向锁及行锁的加锁情况：
```
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
1
```
4.2.2.1 特点

偏向InnoDB存储引擎，开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

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

4.2.2.2 行锁支持事务

所以可以复习下事务的知识：
- 事务及其ACID特性
- 并发事务带来的问题
- 事务隔离级别
4.2.2.3 案例分析
- 建表SQL
插入测试要用到的表：

尚硅谷MySQL高级_笔记

pdf 0星超过10%的资源 20.69MB

下载
```
-- 创建表
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;
-- 插入数据
INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');
-- 创建索引
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
-- InnnDB事务自动提交，如果需要演示行锁，需要关闭自动提交
SET autocommit=0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
```
- 行锁示例
①开启事务，即关闭自动提交。

②在session-1会话中更新但不提交事务，即没有手写commit;语句执行，其他会话如session-2会陷入阻塞，只能等待。

③在session-1会话中提交事务，而其他会话中堵塞的操作解除，继续执行。

④如果对不同的记录进行修改，那么也就不会堵塞
- 无索引行锁升级为表锁
索引失效，行锁变表锁（通过varchar类型不加单引号让索引失效）。

当索引失效后，即使多个客户端操作的不是同一条记录，如果未提交，其他客户端也会进入阻塞状态，所以要避免索引失效。

为甚么索引失效行锁会变表锁：InnoDB 行级锁是通过给索引上的索引项加锁来实现的，InnoDB行级锁只有通过索引条件检索数据，才使用行级锁。否则，InnoDB使用表锁，在不通过索引(主键)条件查询的时候，InnoDB是表锁而不是行锁。
- 间隙锁危害
①什么是间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁）。

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。
索引上的等值- 查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁。
索引上的等值- 查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。
索引上的范围- 查询(唯一索引)–会访问到不满足条件的第一个值为止。

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

比如一共有1、2、4、5、6、7、8、9共八条记录，表中没有3号记录，但是在使用范围条件（id>=2 and id<=6）进行查询检索的时候，那么也会给3号记录添加锁，如果这时候其他会话插入一条id为3的记录就会陷入阻塞，也就是所谓的间隙锁发生了。给不存在的记录加锁时, 优化为间隙锁。

②间隙锁演示

间隙锁会带来插入问题，当产生阻塞时就暂时不能插入，只有阻塞解除后才能完成插入。

③间隙锁的危害

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
- 面试题：常考如何锁定一行
select xxx from xxx where key=xxx for update锁定某一行后，其他的操作会被阻塞，直到指定行的会话提交commit。

4.2.2.4 案例结论

如果两个客户端对同一条记录进行修改
- 客户端A修改后，未提交（未commit），此时客户端B修改，则会阻塞
- 客户端A修改后，提交后，客户端B再修改，则不会阻塞
- 如果两个客户端分别对不同的记录行进行修改，则不会被阻塞
4.2.2.5 行锁分析

可以通过innodb_row_lock这个变量来分析行锁的竞争情况：
```
show status like 'innodb_row_lock%';
1
```
各字段解释说明：

字段

说明

Innodb_row_lock_current_waits

当前正在等待锁定的数量

Innodb_row_lock_time（重要）

从系统启动至今，总锁定了多长时间

Innodb_row_lock_time_avg（重要）

每次锁定的平均时间

Innodb_row_lock_time_max

最长的一次锁定时间

Innodb_row_lock_waits（重要）

从系统启动至今，一共锁定了多少次

4.2.2.6 优化建议
- 尽可能让数据检索通过索引完成，避免无索引，让行锁升级为表锁
- 合理设计索引，缩小锁的范围
- 尽可能减少检索条件，避免间隙锁
- 尽可能控制事务的大小，减少锁定资源量和时间长度
- 尽可能采用低级别的事务隔离级别
4.2.3 页锁

对于页锁了解即可。
- 开销和加锁时间介于表锁和行锁之间，会出现死锁
- 锁定粒度介于表锁和行锁之间，并发度一般
第5章主从复制

 5.1 复制的基本原理

slave会从master 读取binlog来进行数据同步。

原理：

MySQL复制过程分为三步:
- 1、master（主服务器）将改变记录到二进制日志(binary log).这些记录过程叫做二进制日志事件binary log events;
- 2、slave（从服务器）将 master的 binary log events拷贝到它的中继日志(relay log) ;
- 3、slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是一步的且串行化的。
主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。
- binlog 线程：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
- I/O 线程：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
- SQL 线程：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。
5.2 复制的基本原则
- 一个slave（从服务器）只能有一个master（主服务器）
- 一个master可以有多个slave
- 一个slave只能有一个唯一的服务器ID
5.3 复制的最大问题

延时。

5.4 一主一从配置

以最常见的一主一从配置为例说明该如何配置。

第一步：mysql版本一致并且在后台以服务运行。
- 检查Windows系统下的数据库版本及服务运行情况
执行mysql -V命令可以查看版本，执行net start mysql命令可以查看服务启动情况，如果没有启动则启动。
- 检查Linux系统下的数据库版本及其服务运行情况
执行mysql -V命令可以查看版本，执行service mysqld status命令可以查看服务启动情况，如果没有启动则执行service mysqld start命令启动。

第二步：主从配置都在配置文件的[mysqld]节点下，都是小写。

在这里作为主服务器的Windows系统下的配置文件是my.ini。

在这里作为从服务器的Linux系统下的配置文件是my.cnf。

第三步：主服务器修改my.ini配置文件

可以在my.ini配置文件的[mysqld]节点下作如下配置：
```
# 1.[必须]主服务器唯一ID
server-id=1

# 2.[必须]启用二进制日志
log-bin=本地MySQL的安装目录下的data目录的路径/mysqlbin #语法
log-bin=E:/MySQL/InstallationFiles/data/mysqlbin #示例

# 3.[可选]启用错误日志
log-err=本地MySQL的安装目录下的data目录的路径/mysqlerr #语法
log-err=E:/MySQL/InstallationFiles/data/mysqlerr

# 4.[可选]根目录
basedir="本地MySQL的安装目录的路径" #语法
basedir="E:/MySQL/InstallationFiles/" #示例

# 5.[可选]临时目录
tempdir="本地MySQL的安装目录的路径" #语法
tempdir="E:/MySQL/InstallationFiles/" #示例

# 6.[可选]数据目录
datadir="本地MySQL的安装目录下的data目录的路径" #语法
datadir="E:/MySQL/InstallationFiles/data" #示例

# 7.主机读写都可以
read-only=0

# 8.[可选]设置不要复制的数据库
binlog-ignore-db=不要复制的数据库名 #语法
binlog-ignore-db=mysql #示例

# 9.[可选]设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字 #语法
binlog-do-db=test #示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
```
而我选择的配置是：
```
[mysqld] 
# 设置主服务器唯一ID[必须]
server-id=1
# 启用二进制日志[必须] log-bin=数据库路径mysqlbin
log-bin=E:/MySQL/InstallationFiles/data/mysqlbin
# 启用错误日志[可选] 
log-err=E:/MySQL/InstallationFiles/data/mysqlerr
# 设置mysql的安装目录 
basedir=E:/MySQL/InstallationFiles
# 设置临时目录[可选]
tmpdir=E:/MySQL/InstallationFiles
# 设置主机读写均可以
read-only=0
# 设置不需要备份的数据库[可选]
binlog-ignore-db=mysql
# 设置需要备份的数据库[可选]
# binlog-do-db=layman
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
```
第四步：修改从服务器的my.conf配置文件
```
[mysqld]
# 配置从机服务器
# 设置主服务器唯一ID[必须]
server-id=2
# 启用二进制日志[可选]，Linux环境下采用默认值即可
log-bin=mysql-bin
1
2
3
4
5
6
```
保存并退出。

第五步：重启主服务器和从服务器的mysql服务

因为修改过配置文件，要重启mysql服务才会生效。
- 重启Windows下的mysql服务，先关闭再打开
- 重启Linux系统下的mysql服务，执行service mysqld restart命令
第六步：主服务器和从服务器都关闭防火墙
- Windows系统手动关闭防火墙
- 关闭Linux系统的防火墙，执行service iptables stop命令
第七步：在主服务器Windows上建立账户并授权给从服务器

登录进入mysql下

输入如下内容：
```
# grant replication slave on *.* to '从机数据库用户名'@'从机IP地址' identified by '从机数据库密码'; #语法
grant replication slave on *.* to 'root'@'192.168.0.117' identified by 'root';
flush privileges; #刷新
1
2
3
```
可以执行show master status命令查看主机状态

注意：记录下File和Position，会用到。

第八步：在Linux上配置需要复制的主机

在Linux中进入mysql下

输入如下内容进行配置：
```
# 语法
CHANGE MASTER TO MASTER_HOST='主机IP地址', #这里我使用的是Windows系统作为主机，所以为Windows系统的IP地址
MASTER_USER='主机数据库的用户名',
MASTER_PASSWORD='主机数据库的密码',
MASTER_LOG_FILE='具体数字', #通过上面在主机查询出来的File值
MASTER_LOG_POS=具体值; #通过上面在主机查询出来的Position值

------------------------- 分割线 ------------------------

# 示例
CHANGE MASTER TO MASTER_HOST='192.168.0.105',
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysqlbin.000001',
MASTER_LOG_POS=107;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
```
第九步：启动从机服务的复制功能

在从服务器Linux系统的mysql下执行start slave;命令启动复制功能。

第十步：查看是否配置成功

在从服务器Linux系统的mysql下执行show slave statusG命令查看是否配置成功

配置成功后在主机上新建库，表，插入数据都会同步至从机。

使用stop slave命令可以停止从机的复制。

5.5 主从复制的优势
1. 主服务器处理写操作以及实时性要求比较高的读操作
2. 从服务器处理读操作
读写分离能够提高系统性能：
- 主从服务器负责各自的读和写，极大程度缓解锁竞争
- 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销
- 增加冗余，提高可用性
第6章 MySQL管理

注：下面的 uroot 表示 MySQL 的用户名是 root；-proot 表示 MySQL 的登录密码是 root。

6.1 系统数据库

Mysql数据库安装完成后，自带了一下四个数据库，具体作用如下：

数据库

含义

mysql

存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等）

information_schema

提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等

performance_schema

为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数

sys

包含了一系列方便 DBA 和开发人员利用 performance_schema性能数据库进行性能调优和诊断的视图

 6.2 常用工具

 6.2.1 mysql

该 mysql 不是指 MySQL 服务，而是指 MySQL 的客户端工具。语法如下：
```
语法 ：
	mysql [options] [database]
选项 ：
	-u, --user=name 		#指定用户名
	-p, --password[=name] 	#指定密码
	-h, --host=name 		#指定服务器IP或域名
	-P, --port=port 		#指定连接端口
	-e, --execute=name 		#执行SQL语句并退出
1
2
3
4
5
6
7
8
```
-e 选项可以在 MySQL 客户端执行 SQL 语句，而不用连接到 MySQL 数据库再执行，对于一些批处理 shell 脚本，这种方式尤其方便。例如：
```
# 语法
mysql -u 用户名 -p 密码 数据库名 -e "SQL语句";
# 示例
mysql -uroot -proot test -e "select * from tb_user limit 10";
1
2
3
4
```
6.2.2 mysqladmin

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。语法如下：
```
语法:
	mysqladmin [options] command ...
选项:
	-u, --user=name #指定用户名
	-p, --password[=name] #指定密码
	-h, --host=name #指定服务器IP或域名
	-P, --port=port #指定连接端口
1
2
3
4
5
6
7
```
该命令中 command 支持的有：
```
  create databasename	#创建一个新数据库
  debug			Instruct server to write debug information to log
  drop databasename		#删除指定数据库和所有的表
  extended-status       Gives an extended status message from the server
  flush-hosts           Flush all cached hosts
  flush-logs            Flush all logs
  flush-status		Clear status variables
  flush-tables          Flush all tables
  flush-threads         Flush the thread cache
  flush-privileges      Reload grant tables (same as reload)
  kill id,id,...	Kill mysql threads
  password [new-password] Change old password to new-password in current format
  ping			Check if mysqld is alive
  processlist		Show list of active threads in server
  reload		Reload grant tables
  refresh		Flush all tables and close and open logfiles
  shutdown		Take server down
  status		Gives a short status message from the server
  start-slave		Start slave
  stop-slave		Stop slave
  variables             Prints variables available
  version		#查看版本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
```
例如：
```
# 删除名为 test 的数据库
mysqladmin -uroot -proot drop 'test';
1
2
```
6.2.3 mysqlbinlog

由于服务器生成的二进制日志文件（可以通过 show variables like '%log_bin%'; 语句在 MySQL 中查看二进制日志文件的路径）以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到 mysqlbinlog 日志管理工具。语法如下：
```
语法 ：
	mysqlbinlog [options] log-files1 log-files2 ...
选项 ：
	-d, --database=name 指定数据库名称，只列出指定的数据库相关操作。
	-o, --offset=# 忽略掉日志中的前n行命令。
	-r,--result-file=name 将输出的文本格式日志输出到指定文件。
	-s, --short-form 显示简单格式， 省略掉一些信息。
	--start-datatime=date1 --stop-datetime=date2 指定日期间隔内的所有日志。
	--start-position=pos1 --stop-position=pos2 指定位置间隔内的所有日志。
1
2
3
4
5
6
7
8
9
```
6.2.4 mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。
```
语法 ：
	mysqlshow [options] [db_name [table_name [col_name]]]
选项 ：
	--count 显示数据库及表的统计信息（数据库，表 均可以不指定）
	-i 显示指定数据库或者指定表的状态信息
1
2
3
4
5
```
例如：
```
# 查看数据库 test 中每个表中的字段数和行数
mysqlshow -uroot -proot test --count
# 查看每个数据库中表的数量和表中记录数
mysqlshow -uroot -proot --count 
# 查看数据库 test 中 tb_user 表的统计信息
mysqlshow -uroot -proot test tb_user --count 
# 查看数据库 test 中 tb_user 表的 id 字段的统计信息
mysqlshow -uroot -proot test tb_user id --count 
1
2
3
4
5
6
7
8
```
6.2.5 mysqldump

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。
```
语法 ：
	mysqldump [options] db_name [tables]
	mysqldump [options] --database/-B db1 [db2 db3...]
	mysqldump [options] --all-databases/-A
连接选项 ：
	-u, --user=name 指定用户名
	-p, --password[=name] 指定密码
	-h, --host=name 指定服务器ip或域名
	-P, --port=# 指定连接端口
输出选项：
	--add-drop-database 在每个数据库创建语句前加上 drop database 语句
	--add-drop-table 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)
	-n, --no-create-db 不包含数据库的创建语句
	-t, --no-create-info 不包含数据表的创建语句
	-d --no-data 不包含数据
	-T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
```
示例如下：
```
# 备份数据库 test，并将备份内容写入到 test.sql 文件中，包括删表、建表、数据插入语句
mysqldump -uroot -proot test > test.sql
# 只备份 test 数据库的表数据，不备份表结构，需要使用 -t 选项，只有 insert 语句没有建表语句
mysqldump -uroot -proot -t test > test.sql 
# 将 test 数据库的表结构与表数据分开备份，需要使用 -T 选项。其中 /root/ 表示备份文件的存储路径；test 表示待备份的数据库；tb_user 表示待备份的数据库表名
# 注意，实际上不能指定备份文件的存储路径为 /root/，需要使用 MySQL 信任的目录，通过 secure_file_priv 变量查看该路径（即 `show variables like '%secure_file_priv%';`）。备份后会生成一个 sql 文件是表结构，txt 文件是表数据。
mysqldump -uroot -proot -T /root/ test tb_user
1
2
3
4
5
6
7
```
6.2.6 mysqlimport

mysqlimport 是客户端数据导入工具，用来导入 mysqldump -T 命令后导出的 txt 文本文件。该命令的语法如下：
```
# 语法
mysqlimport [options] db_name textfile1 [textfile2...]
# 示例，将 /tmp/tb_user.txt 文件中的数据导入到 test 数据库中
mysqlimport -uroot -proot test /tmp/tb_user.txt
1
2
3
4
```
6.2.7 source

source 命令是用来导入 sql 文件的，而非 txt 文件。该命令必须在登录 MySQL 后执行。
```
# 语法
source /root/test.sql
1
2
```
第7章 InnoDB引擎

 7.1 逻辑存储结构

InnoDB引擎的逻辑存储结构如图：

7.1.1 表空间

表空间是InnoDB存储引擎逻辑结构的最高层，如果用户启用了参数 innodb_file_per_table (在 8.0 版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

7.1.2 段

段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent（区）。

7.1.3 区

区，表空间的单元结构，每个区的大小为1M。默认情况下， InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。

7.1.4 页

页，是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

7.1.5 行

行，InnoDB 存储引擎数据是按行进行存放的。在行中，默认有两个隐藏字段：
- Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
- Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
7.2 架构

 7.2.1 概述

MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构

 7.2.2 内存结构

在左侧的内存结构中，主要分为这么四大块儿： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。接下来介绍一下这四个部分。

（1）Buffer Pool

InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。

在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。

缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：
- free page：空闲page，未被使用。
- clean page：被使用page，数据没有被修改过。
- dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。
在专用服务器上，通常将多达80％的物理内存分配给缓冲池。参数设置：show variables like 'innodb_buffer_pool_size';。

（2）Change Buffer

Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。

Change Buffer的意义是什么呢先来看一幅图，这个是二级索引的结构图：

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。

（3）Adaptive Hash Index

自适应hash索引，用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持hash索引，但是给我们提供了一个功能就是这个自适应hash索引。因为前面我们讲到过，hash索引在进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。

InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。

自适应哈希索引，无需人工干预，是系统根据情况自动完成。可以通过如下参数进行查看：

（4）Log Buffer

Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。

相关的参数有：innodb_log_buffer_size 和 innodb_flush_log_at_trx_commit。

innodb_log_buffer_size 表示缓冲区大小。

innodb_flush_log_at_trx_commit 表示日志刷新到磁盘时机，取值主要包含以下三个：
- 1: 日志在每次事务提交时写入并刷新到磁盘，默认值。
- 0: 每秒将日志写入并刷新到磁盘一次。
- 2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。
7.2.3 磁盘结构

看看InnoDB体系结构的右边部分，也就是磁盘结构：

（1）System Tablespace

系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)。相关的参数有 innodb_data_file_path，其中系统表空间默认的文件名是 ibdata1。

（2）File-Per-Table Tablespaces

如果开启了 innodb_file_per_table 开关，则每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中。涉及的参数是 innodb_file_per_table，默认是开启的。即每创建一个表，都会产生一个表空间文件，即 .ibd 文件。

（3）General Tablespaces

通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。

创建表空间的语法如下：
```
# 语法
CREATE TABLESPACE 表空间名 ADD DATAFILE 'ibd文件名' ENGINE = 存储引擎名;
# 示例
create tablespace ts_user add datafile 'my_user.ibd' engine = innodb; 
1
2
3
4
```
创建表时指定表空间的语法如下：
```
# 语法
CREATE TABLE 建表语句 engine = 存储引擎名 tablespace 表空间名;
# 示例
create table tb_account(id int primary key auto_increment, name varchar(10)) engine=innodb tablespace ts_account;
1
2
3
4
```
（4）Undo Tablespaces

撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志。

（5）Temporary Tablespaces

InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

（6）Doublewrite Buffer Files

双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。即 .dblwr 文件。

（7）Redo Log

重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时，发生错误时, 进行数据恢复使用。

以循环方式写入重做日志文件，涉及两个文件：ib_logfile0 和 ib_logfile1。

6.2.4 后台线程

那么内存中我们所更新的数据，又是如何到磁盘中的呢？此时，就涉及到一组后台线程。

在InnoDB的后台线程中，分为4类，分别是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread。

（1）Master Thread

核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收。

（2）IO Thread

在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调。

我们可以通过以下的这条指令 show engine innodb status G;，查看到InnoDB的状态信息，其中就包含IO Thread信息。

（3）Purge Thread

主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。

（4）Page Cleaner Thread

协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

7.3 事务原理

 7.3.1 事务基础

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。事务有如下四个特性：
- 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
研究事务的原理，就是研究MySQL的InnoDB引擎是如何保证事务的这四大特性的。而对于这四大特性，实际上分为两个部分。其中的原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。而持久性是通过数据库的锁，加上MVCC来保证的。

7.3.2 redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo logfile）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

如果没有redolog，可能会存在什么问题的？我们一起来分析一下。

我们知道，在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页我们称为脏页。而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性。

那么，如何解决上述的问题呢？在InnoDB中提供了一份日志 redo log，接下来我们再来分析一下，通过redolog如何解决这个问题。

有了redolog之后，当对缓冲区的数据进行增删改之后，会首先将操作的数据页的变化，记录在redo log buffer中。在事务提交时，会将redo log buffer中的数据刷新到redo log磁盘文件中。过一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性。而如果脏页成功刷新到磁盘或或者涉及到的数据已经落盘，此时redolog就没有作用了，就可以删除了，所以存在的两个redolog文件是循环写的。

那为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢

因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。这种先写日志的方式，称之为 WAL（Write-Ahead Logging）。

7.3.3 undo log

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和MVCC(多版本并发控制) 。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment回滚段中，内部包含1024个undo log segment。

7.4 MVCC

7.4.1 基本概念

（1）当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode(共享锁)，select ...for update、update、insert、delete(排他锁)都是一种当前读。

在测试中我们可以看到，即使是在默认的RR隔离级别下，事务A中依然可以读取到事务B最新提交的内容，因为在查询语句后面加上了 lock in share mode 共享锁，此时是当前读操作。当然，当我们加排他锁的时候，也是当前读操作。

（2）快照读

简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
- Read Committed：每次select，都生成一个快照读。
- Repeatable Read：开启事务后第一个select语句才是快照读的地方。
- Serializable：快照读会退化为当前读。
在测试中,我们看到即使事务B提交了数据,事务A中也查询不到。原因就是因为普通的select是快照读，而在当前默认的RR隔离级别下，开启事务后第一个select语句才是快照读的地方，后面执行相同的select语句都是从快照中获取数据，可能不是当前的最新数据，这样也就保证了可重复读。

（3）MVCC

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

7.4.2 隐藏字段

7.4.2.1 介绍

当我们创建了上面的这张表，我们在查看表结构的时候，就可以显式的看到这三个字段。实际上除了这三个字段以外，InnoDB还会自动的给我们添加三个隐藏字段及其含义分别是：

隐藏字段

含义

DB_TRX_ID

最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。

DB_ROLL_PTR

回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。

DB_ROW_ID

隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

而上述的前两个字段是肯定会添加的，是否添加最后一个字段DB_ROW_ID，得看当前表有没有主键，如果有主键，则不会添加该隐藏字段。

例如查看有主键的表 tb_user 的情况，进入服务器的 /var/lib/mysql/ 目录下的 test 数据库（test 数据库中有 tb_user 表），查看 tb_user 表的结构信息，通过如下命令 idb2sdi tb_user.ibd 。查看到的表结构信息中，有一栏 columns，在其中我们会看到处理我们建表时指定的字段以外，还有额外的两个字段分别是：DB_TRX_ID 、 DB_ROLL_PTR ，因为该表有主键，所以没有DB_ROW_ID隐藏字段。

7.4.3 undolog

7.4.3.1 介绍

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

7.4.3.2 版本链

有一张表原始数据为：
- DB_TRX_ID : 代表最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID，是
  自增的。
- DB_ROLL_PTR ：由于这条数据是才插入的，没有被更新过，所以该字段值为null。
假如，有四个并发事务同时在访问这张表：

A. 第一步

当事务2执行第一条修改语句时，会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本。

B. 第二步

当事务3执行第一条修改语句时，也会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本。

C. 第三步

当事务4执行第一条修改语句时，也会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本。

最终我们发现，不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

7.4.4 readview

ReadView（读视图）是快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。ReadView中包含了四个核心字段：

字段

含义

m_ids

当前活跃的事务ID集合

min_trx_id

最小活跃事务ID

max_trx_id

预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）

creator_trx_id

ReadView创建者的事务ID

而在readview中就规定了版本链数据的访问规则：trx_id 代表当前undolog版本链对应事务ID。

条件

是否可以访问

说明

trx_id ==creator_trx_id

可以访问该版本

成立，说明数据是当前这个事务更改的。

trx_id < min_trx_id

可以访问该版本

成立，说明数据已经提交了。

trx_id > max_trx_id

不可以访问该版本

成立，说明该事务是在ReadView生成后才开启。

min_trx_id <= trx_id <= max_trx_id

如果trx_id不在m_ids中，是可以访问该版本的

成立，说明数据已经提交。

不同的隔离级别，生成ReadView的时机不同：
- READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
- REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。
7.4.5 原理分析

7.4.5.1 RC隔离级别

RC隔离级别下，在事务中每一次执行快照读时生成ReadView。

我们就来分析事务5中，两次快照读读取数据，是如何获取数据的

在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读都会生成一个ReadView，那么两次生成的ReadView如下。

那么这两次快照读在获取数据时，就需要根据所生成的ReadView以及ReadView的版本链访问规则，到undolog版本链中匹配数据，最终决定此次快照读返回的数据。

A. 先来看第一次快照读具体的读取过程：

在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：
- 先匹配这条记录，这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中。 ①不满足 ②不满足 ③不满足 ④也不满足，都不满足，则继续匹配undo log版本链的下一条。
- 再匹配第二条，这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也不满足，都不满足，则继续匹配undo log版本链的下一条。
- 再匹配第三条，这条记录对应的trx_id为2，也就是将2带入右侧的匹配规则中。①不满足 ②满足终止匹配，此次快照读，返回的数据就是版本链中记录的这条数据。
B. 再来看第二次快照读具体的读取过程：

在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：
- 先匹配这条记录，这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中。 ①不满足 ②不满足 ③不满足 ④也不满足，都不满足，则继续匹配undo log版本链的下一条。
- 再匹配第二条，这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②满足。终止匹配，此次快照读，返回的数据就是版本链中记录的这条数据。
7.4.5.3 RR 隔离级别

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。而RR 是可重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的。

那MySQL是如何做到可重复读的呢我们简单分析一下就知道了

我们看到，在RR隔离级别下，只是在事务中第一次快照读时生成ReadView，后续都是复用该ReadView，那么既然ReadView都一样， ReadView的版本链匹配规则也一样，那么最终快照读返回的结果也是一样的。

所以呢，MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。而MVCC + 锁，则实现了事务的隔离性。而一致性则是由redolog 与 undolog保证。

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦
相关阅读:
docker 构建镜像和容器的流程，以及常用命令
 安装 eslint 配置指南及遇到的一些问题记录
 如何将 Python 运用到实际的测试工作中
 企业级优化
 动态规划学习
 亚马逊测评自养号成号率的关键因素及风控策略探讨
 中国移动集采120万部，助推国产5G赶超iPhone15
nginx
【数据集NO.4】遥感图像数据集汇总
 JAVA面试题进阶
原文地址：https://blog.csdn.net/m0_67392811/article/details/126062540

文章目录

第1章 MySQL的架构介绍

1.1 MySQL简介

1.2 在Linux上安装MySQL

1.3 MySQL配置文件

1.3 MySQL逻辑架构介绍

1.4 MySQL存储引擎

1.4.0 存储引擎概述

1.4.1 查看命令

1.4.2 存储引擎特点

1.4.2.1 InnoDB

1.4.2.2 MyISAM

1.4.2.3 Memory

1.4.2 MyISAM和InnoDB

1.4.3 存储引擎选择

第2章 索引优化分析

2.1 概述

2.1.1 性能下降原因

2.1.2 常用的join查询

2.1.2.1 SQL执行顺序

2.1.2.2 join图

2.2 索引简介

2.2.1 索引是什么

2.2.2 索引的优缺点

2.2.3 索引结构

2.2.3.1 概述

2.2.3.2 二叉树

2.2.3.3 B-Tree

2.2.3.4 B+Tree

2.2.3.5 Hash

2.2.4 索引的分类

2.2.4.0 聚集索引与二级索引

2.2.4.1 单值索引

2.2.4.2 唯一索引

2.2.4.3 主键索引

2.2.4.4 复合索引

2.2.5 基本语法

2.2.6 索引的创建时机

2.2.6.1 适合创建索引的情况

2.2.6.2 不适合创建索引的情况

2.3 性能分析

2.3.0 SQL 执行频率

2.3.1 MySQL Query Optimizer

2.3.2 MySQL常见瓶颈

2.3.3 Explain

2.3.3.1 是什么

2.3.3.2 能干啥

2.3.3.3 怎么玩

2.3.3.4 字段解释

2.3.3.4.1 id

2.3.3.4.2 select_type

2.3.3.4.3 table

2.3.3.4.4 type

2.3.3.4.5 possible_keys

2.3.3.4.6 key

2.3.3.4.7 key_len

2.3.3.4.8 ref

2.3.3.4.9 rows

2.3.3.4.10 extra

2.4 索引优化

2.4.1 索引分析

2.4.1.1 单表

2.4.1.2 双表

2.4.1.3 三表

2.4.1.4 总结

2.4.2 索引失效

2.4.2.1 创建测试表

2.4.2.2 索引失效的各种情况

2.4.3 小总结

2.4.4 优化口诀

2.4.5 面试题讲解

2.4.6 一般性建议

2.5 其他索引情况

2.5.1 SQL 提示

2.5.2 覆盖索引

2.5.3 前缀索引

2.5.3.1 概述

2.5.3.2 语法

2.5.3.3 前缀索引长度

第2章索引优化分析

第3章查询截取分析

3.1.6.3 `count` 用法