MySQL高级

一、在Linux系统中安装MySQL服务

1. 安装MySQL镜像服务

拉去镜像
```
sudo docker pull mysql:8.0.30
```

配置dockers-compose文件

version: '3'

services:
  mysql:
      image: mysql:8.0.30
      container_name: mysql
      ports:
          - "3306:3306"
      volumes:
          - /usr/local/mysql/conf:/etc/mysql/conf.d
          - /usr/local/mysql/data:/var/lib/mysql
      environment:
          - MYSQL_ROOT_PASSWORD=123456
      restart: always
      networks:
          - mysql-network


networks:
  mysql-network:
      driver: bridge

使用运行指令
```
sudo docker compose up -d
```
查看是否成功启动
```
sudo docker ps 
```

2. 远程连接

查看是否允许远程连接
```
select host,user from mysql.user;
```
远程连接
问题
- 如果连接时出现：错误号码 2058，Plugin caching_sha2_password could not be loaded….
- 这个是密码的加密和解密方式不一致造成的，因为MySQL5.7使用的是mysql_native_password，而MySQL8之后使用的是caching_sha2_password，所以会报错
解决方案
- 可以升级新的MySQL工具
- 可以将MySQL用户登录加密和解密方式修改成mysql_native_password
- 进入容器内部
```
sudo docker exec -it mysql /bin/bash ;
```
- 然后进入MySQL
```
mysql -u root -p 
```
- 修改默认方式
```
alter user 'root'@'%' identified with mysql_native_password by '123456'
```

3. 字符集

3.1 默认字符集

MySQL8之前的版本，默认使用的字符集是latin1(ISO-8859-1) ，不支持中文，使用前必须设置字符集为utf8。
从MySQL8开始，数据库的默认字符集为utf8，从而避免中文乱码的问题。
查看MySQL使用的字符集
```
show variables like '%char%';
```

3.2 utf8与utf8mb4

utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计MySQL的设计者偷偷的定义了两个概念：
utf8mb3 ：utf8mb3 阉割过的 utf8 字符集，只使用1～3个字节表示字符（无法存储emoji表情等特殊符号）。
utf8mb4：utf8mb4正宗的 utf8 字符集，使用1～4个字节表示字符。
注意：MySQL5.7中的utf8是utf8mb3字符集 , MySQL8.0中的utf8是utf8mb4字符集

4. SQL大小写规范

4.1 Windows

全部不区分大小写

4.2 Linux

数据库名、表名、表的别名严格区分大小写
列名、列的别名不区分大小写
关键字、函数名称不区分大小写
变量名不区分大小写

4.3 Linu下设置大小写规则

停止MySQL服务
删除数据目录，即删除 /var/lib/mysql 目录
在MySQL配置文件（/etc/my.cnf ）的 [mysqld] 中添加 lower_case_table_names=1
初始化数据目录 mysqld –initialize –user=mysql
启动MySQL服务 systemctl start mysqld
注意：不建议在开发过程中修改此参数，将会丢失所有数据

5. 严格模式和宽松模式

MySQL有两种模式：分别为宽松模式（Loose Mode）和严格模式（Strict Mode）。这些模式决定了MySQL在处理数据是的行为规则

5.1 宽松模式（Loose Mode）：

执行错误的SQL或插入不规范的数据，也会被接受，并且不报错。

5.2 严格模式（Strict Mode）：

执行错误的SQL或插入不规范的数据，会报错。MySQl5.7版本开始就将sql_mode默认值设置为了严格模式。

5.3 查看模式和设置模式，即sql_mode

查看sql_mode
```
show variables like 'sql_mode'; 
```

设置sql_mode

set sql_mode = ' ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

5.4 错误案例演示

创建数据库，创建表，插入数据

create database MyDatabase;
use MyDatabase;
create table `employee`(id int,name varchar(50),age int,dpet int);
insert into employee values(1,'李四',34,101),(2,'王五',33,101),(3,'张三',35,102),(4,'田六',36,102),(5,'钱七',32,103),(6,'赵八',34,103);

进行错误查询

select name,max(age),dept from employee group by dept;

查询之后会报错，查询语句在"ONLY_FUlL_GROUP_BY"模式下查询出错，因为select子句中的name列并没有出现在group by 子句中，也没有出现在函数中
将非"ONLY_FUlL_GROUP_BY"模式下可以正常执行，但是得到的结果为错误结果
设置sql_mode为””
```
set sql_mode = '';
```
正确查询方式

将sql_mode设置回原来的值

set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

重新设置查询语句

select e.* from employee e inner join(select dept,max(age) AS age from employee group by dept) AS d on e.dept = d.dept and e.age = d.age;

5.5 sql_mode常用值（了解）

ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，select子句中只能包含函数和GROUP BY中出现的字段
STRICT_TRANS_TABLES：
对于支持事务的表，如果发现某个值缺失或非法，MySQL将抛出错误，语句会停止运行并回滚。
对于不支持事务的表，不做限制，提高性能。
NO_ZERO_IN_DATE：不允许日期和月份为0。
NO_ZERO_DATE：MySQL数据库不允许插入0日期，插入0日期会抛出错误而不是警告
ERROR_FOR_DIVISION_BY_ZERO：在insert或update过程中，如果数据被除零，则产生错误而非警告。如果未给出该模式，那么数据被除零时MySQL放回NULL。
NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或不存在，那么抛出错误。不设置此值时，用默认的存储引擎替代。

二、逻辑架构

1. 逻辑架构图

MySQL5.7的逻辑架构图
与MySQL5.7相比，MySQL8的逻辑架构中去除了缓存部分。

2. 客户端

MySQL之外的程序，与开发语言有很大的关系，如Java则使用的是JDBC。图像化管理工具SQLyong等，其本质是通过TCP连接MySQL协议与MySQL服务进行通信。

3. 连接层

客户端在访问MySQL服务之前，需要建立TCP连接。
经过三次握手之后，MySQL对传输过来的账号密码进行身份认证和权限获取。
如果用户名或密码不正确，会收到一个Access denied for user错误，然后客户端程序结束执行。
用户验证通过之后，会从权限表中查询相关全与连接关联，之后的权限判断都依赖于查询出的权限信息。
TCP连接接收到请求之后，会分配一个线程专门与这个客户端对接，而线程是一个线程池进行维护，这样减少了创建和销毁线程的消耗。

4. SQL接口

接收用户的SQL指令，并返回用户需要的结果，比如SELECT……FROM就是Select interface
MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言。

5.解析器

SQL命令传递到解析器时会被解析器验证和解析，解析器会对SQL命令进行词法分析、语法分析、语义分析，为SQL命令创建解析树
词法分析：检测SQL语句的关键字是否正确。
语法分析：检测SQL语句是否符合MySQL语法规则，然后按照MySQL语法规则生成解析树。
语义分析：检测解析树是否合法，例如表是否存在，列是否存在。
典型的解析树如下：

6.查询优化器

SQL命令在语法解析之后，查询前会经过查询优化器对查询路径进行优化，确定SQL语句的执行路径，生成执行计划。

7. 缓存

MySQL内部维持着一些Cache（缓存）和Buffer（缓存区），比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行解析、优化、执行，直接将结果反馈给客户端。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。
这个查询缓存可以在不同客户端之间共享。
为什么MySQL8之后会去除缓存？
只有两个相同的SQL命令才会命中，两个语句如果有任何字符上不同，都会导致缓存命中不了。
在两条查询语句之间有INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或DROP DATABASE语句也会导致缓存失效。
因为缓存的命中率不高，所以MySQL8取消了缓存。

8. 存储引擎

存储引擎，负责MySQL中的数据存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎有不同的功能，管理的表也是使用不同的存储结构，使用的算法也不同，主要使用的存储引擎有：MyISAM、InnoDB等

9. 系统管理和控制工具

数据恢复
数据安全
数据副本
服务器集群
服务器管理
服务器配置
数据迁移
元数据

10. 存储层（系统文件和服务器文件以及日志）

所有的数据、数据库、表的定义、表的每一行内容、索引，都存储在文件系统上，以文件的方式存储，并与存储引擎进行交互。

11. 查询流程

查询流程说明
图解
首先，MySQL客户端通过TCP协议与MySQL服务器建立连接，通过SQL接口传递SQL语句，查询缓存查看是否命中，如果命中则直接返回，没有命中则将SQL语句传递给解析器。
其次是解析过程，解析器通过对SQL语句的词法解析和语法解析，然后生成一颗解析树，在通过对解析树进行语义解析查看是否合法，例如表是否存在，表的字段是否存在，还会解析别名，查看是否有歧义，然后生成一颗新的解析树
然后是优化过程，查询优化器会对SQL语句做一些优化，将查询的IO成本和CPU成本降到最低，生成一个最优的执行计划。
最后，查询执行引擎会按照生成的执行计划调用存储引擎提供的接口执行SQL语句，然后将结果返回给客户端
MySQL8以下的版本，如果开启的缓存，会将查询的结果进行缓存，再返回给客户端。

三、存储引擎

1. 查看存储引擎

指令
```
show engines;
```

2. 查看默认的存储引擎

指令

show variables like '%default_storage-engine%';

3. 设置存储引擎

3.1 设置临时存储引擎

指令
```
set DEFAULT_STORGE_ENGINE=MyISAM;
```

3.2 设置永久存储引擎

需要在配置文件my.cnf中设置

在文件中添加

# 添加配置
[mysqld]
# 设置默认存储引擎
default_storage_engine=MyISAM

然后重启MySQL服务
- 指令
```
docker restart mysql
```

3.3 给不同的表设置不同的存储引擎

在创建表时设置表的存储引擎

#在建表时设置存储引擎
create table 表名(建表语句) engine=储存引擎名称;

#建表之后设置存储引擎
alter table 表名 engine=存储引擎名称;

4. MyISAM和InnoDB的区别

事务支持：InnoDB是支持事务的存储引擎，而MyISAM不支持事务。
外键约束：InnoDB支持外键约束（foreign key constraints），可以保证数据的完整性和一致性。MyISAM不支持外键约束。
锁级别：InnoDB采用行级锁定（row-level locking），可以更好地支持并发操作和并发控制。而MyISAM采用表级锁定（table-level locking），这意味着在对表进行写操作时需要锁定整个表，可能导致并发性能下降。
崩溃恢复：InnoDB具有崩溃恢复的能力，可以在数据库发生异常情况或崩溃后自动恢复数据。MyISAM没有崩溃恢复机制，如果发生故障，可能会导致数据丢失或损坏。
并发性能：由于InnoDB采用了行级锁定和多版本并发控制（MVCC）技术，它在高并发环境下通常具有更好的性能。MyISAM适用于读操作较多、写操作较少的场景。

四、MySQL索引

1. 什么时索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向数据），这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
索引的本质：索引是数据结构，可以简单理解为“排好序的快速查找数据结构”

2. MySQL中的索引结构：B+树

每一页数据大小为16kB
查看每一页数据的大小

指令

show variables like 'innodb_page-size';

3.聚簇索引

索引和所有数据放在一起，即所有数据都在一颗树上

3.1 特点

索引和数据保存在同一颗树中。
页内的记录是按照主键大小顺序排列成一个单向链表。
页与页之间也是根据页中记录的主键大小顺序排成一个双向链表。
非叶子节点存储的是记录的主键+页号
叶子节点存储的是完整的数据

3.2 优点

数据访问更快，因为索引和数据保存在同一颗B+树中，因此从聚簇索引中获取数据比InnoDB非聚簇索引更快。
聚簇索引对于主键的排序查找和范围查找速度非常快。
按照聚簇索引排列顺序（聚簇索引列一定是增长的），查询显示一定范围数据的时候，由于数据都市紧密相连，数据库可以从更少的数据块中提取数据，节省了大量的IO操作。

3.3 缺点

插入速度严重依赖于插入顺序，按照主键顺序插入是最快的方式，否则很容易出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义自增的ID列为主键。
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，定义主键为不可更新。

3.4 限制

只有InnoDB引擎支持聚簇索引，MyISAM不支持聚簇索引。
由于数据的物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。
如果没有为表定义主键，InnoDB会选择非空的唯一约束索引列代替。如果没有这样的列，InnoDB会隐式的定义一个主键作为聚簇索引。
为了充分利用聚簇索引的聚簇特性，InnoDB中的表主键应选择有序的id，不建议使用无序的id，比如UUID、MD5、HASH、字符串作为主键，无法保证数据的顺序增长。

4.非聚簇索引

索引和数据分开存储，又称为二级索引、辅助索引
聚簇索引，只能在搜索条件是主键值时才发挥作用，因为B+树中的数据都是按照主键进行排序的，如果想以别的列作为搜索条件，那么需要创建非聚簇索引。
以c2列作为搜索条件，那么需要使用c2列创建一颗B+树，如小所示：

4.1 非聚簇索引和聚簇索引有什么不同：

页内的记录是按照从c2列的大小顺序排成一个单向链表。
页和页之间也是根据页中记录的c2列的大小顺序排成一个双向链表。
非叶子节点存储的是记录的c2列+页号。
叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。
例如：根据c2列的值查找c2=4的记录，查找过程如下：
根据根页面44定位到页42（因为2 <= 4 < 9）
由于c2列没有唯一性约束，所以c2=4的记录可能分布在多个数据页中，又因为 2 <= 4 <= 4，所以确实实际存储用户记录的页在页34和页35中。
在页34和页35中定位到具体的记录。
但是这个B+树的叶子节点只存储了c2和c1（主键）两列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。
概念：回表
在非聚簇索引中查询到c2对应的主键c1，然后再通过c1的值去聚簇索引中查询所有的数据，这就叫做回表查询。
一张表可以有多个非聚簇索引

5.联合索引

为c2和c3列建立联合索引
各个页中的记录按照c2列进行排序。
在记录的c2列相同的情况下，采用c3列进行排序。
B+树叶子节点处的记录由c2列、c3列和主键 c1列组成。
本质上也是个二级索引

6.覆盖索引

如果能通过读取索引就可以得到想要的数据，那就不需要读取用户记录，或者不用再做回表操作。一个索引包含了满足查询结果的数据就叫做覆盖索引。

例如

select c1,c2 from index_demo where c2 = 5;

7. MyISAM中的索引

MyISAM引擎使用B+树作为索引结构，叶子节点的data域存放的是数据记录的地址。
MyISAM是非聚簇索引，因为其索引和数据分开存储。
为c2建立一个二级索引

8. MyISAM与InnoDB对比

InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的：
InnoDB的表在磁盘上存储在以下文件中：.ibd（表结构、索引和数据都存在一起，MySQL5.7表结构放在.frm中）
MyISAM的表在磁盘上存储在以下文件中：*.sdi（描述表结构，MySQL5.7是.frm）、 *.MYD（数据）、 *.MYI（索引）
InnoDB中主键索引是聚簇索引，叶子节点中存储完整的数据记录；其它索引是非聚簇索引，存储相应记录主键的值。
MyISAM中无论是主键索引还是非主键索引都是非聚簇的，叶子节点记录的是数据的地址。
MyISAM的回表操作时十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址取访问。

10. 索引操作

10.1 创建索引

表创建时创建索引

create database MyDataBase;

use MyDataBase;

create table customer(
  id int unsigned AUTO_INCREMENT,
  customer_no varchar(200),
  customer_name varchar(255),

  primary key(id), -- 主键索引：列设定为主键后会自动建立索引，唯一且不能为空。
  unique index ui_cn(customer_no), -- 唯一索引：索引列值必须唯一，允许为NULL值，且NULL值可能会出现多次
  key i_cn (customer_no), -- 普通索引：既不是主键，列值也不需要唯一，单纯的为了提高查询速度而创建
  key i_no_name(customer_no,customer_name) -- 复合索引：即一个索引包含多个列
);

单独创建索引

create table employee(
id int unsigned,
  employee_no varchar(200),
  employee_name varchar(200)
);

alter table employee add primary key employee(id); -- 主键索引
create unique index ui_no on employee(employee_no); -- 唯一索引
create index i_no on employee(employee_no); -- 普通索引
create index i_no_name on employee(employee_no,employee_name); -- 复合索引

alter table employee modify id int unsigned AUTO_INCREMENT,add primary key employee(id) -- 创建自增的主键索引

使用ALTER指令创建指令

alter table employee add primary key (id); -- 主键索引
alter table employee add unique index ui_no(employee_no); -- 唯一索引
alter table employee add index i_no(employee_no); -- 普通索引
alter table employee add index i_no_name(employee_no,employee_name); -- 复合索引

查看索引
```
show index from 表名;
```

删除索引

-- 删除唯一索引、普通索引、复合索引
drop index 索引名称 on 表名;
-- 删除主键索引（有自增主键），先设置成非自增主键，然后再删除
alter table 表名 modify id int unsigned,drop primary key;
-- 删除主键索引（没有自增主键）
alter table 表名 drop primary key ;

10.2 索引的分类

从功能逻辑上划分，索引主要有四种，分别为：主键索引、唯一索引、普通索引、全文索引。
按照作用字段个数划分，索引可以分为单列索引和复合索引。
按照物理实现方式划分，索引可以划分为2中，分别是聚簇索引和非聚簇索引。

10.3 索引的优缺点

优点
提高数据检索的效率，降低数据库的IO成本。
加快数据排序和聚合操作，降低CPU的消耗。
加速多表连接操作。
通过在列上创建唯一索引或主键索引，可以确保数据的唯一性和完整性
缺点
创建索引和维护索引要耗费时间。
索引是存储在磁盘上的，因此需要占用磁盘空间。

五、索引优化

1. 数据库优化方案

MySQL性能调优是指通过对MySQL数据库系统进行优化，以提高其执行速度、响应时间和资源利用率的过程。针对MySQL的性能调优主要集中在以下几个方面：
SQL查询优化：通过分析和优化查语句，包括使用适合的索引、避免全表扫描、优化JOIN操作等，以提高查询性能。
索引优化：合理设计和使用索引，包括选择合适的列作为索引、创建复合索引、删除不必要的索引等，以加快数据检索速度。
配置优化：调整MySQL的配置参数，如缓冲区大小、并发连接数、线程池大小等，以适应不同的工作负载和硬件环境。
内存管理：合理配置MySQL的内存使用，包括设置合适的缓冲池大小、排序缓冲区大小、临时表空间大小等，以提高内存利用率和减少磁盘IO。
存储引擎选择：根据应用需求选择合适的存储引擎，如InnoDB、MyISAM等，并针对不同存储引擎进行相应的优化
数据库设计优化：合理设计数据库表结构、字段类型和关系，减少数据冗余和提高查询效率。

2. 性能优化

2.1 explain是什么

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

2.2 准备测试数据

创建三个表，分别为：t_role 、t_user、user_role
注意
因为是在docker容器中的原因，在MySQL客服端输入中文会不显示，所以在进入容器时，设置字符集

指令

docker exec -it mysql env LANG=C.UTF-8 /bin/bash

SQL

CREATE TABLE `t_role` (
`id` varchar(32) NOT NULL,
`role_name` varchar(255) DEFAULT NULL,
`role_code` varchar(255) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `t_user` (
`id` varchar(32) NOT NULL,
`username` varchar(45) NOT NULL,
`password` varchar(96) NOT NULL,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `user_role` (
`id` int(11) NOT NULL auto_increment ,
`user_id` varchar(32) DEFAULT NULL,
`role_id` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_ur_user_id` (`user_id`),
KEY `fk_ur_role_id` (`role_id`),
CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','superadmin');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','admin');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','zhangsan','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','张三');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','lisi','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','李四');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','wangwu','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','王五');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','zhaoliu','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','赵六');

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');

INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '10'),(NULL, '2', '9'),(NULL, '2', '8'),(NULL, '3', '5'),(NULL, '4', '7'),(NULL, '5', '5') ;

2.3 相关字段的详细解释

2.3.1 table

展示的数据是属于哪一张表的

2.3.2 id

id字段是select查询的序列号，是一组数字表示在查询中执行select子句或是操作表的顺序。
如果id相同，则表示加载表的顺序是从上到下的

指令

 explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id;

如果id不相同，id的值越大则优先被执行，反之id的值越小则越后被执行。

指令

 explain select * from t_role where id = (select role_id from user_role where user_id = (select id from t_user where username = 'super'));

2.3.3 select_type

表示select的类型，常见值如下表所示：

union和union result：
union：对于包含union或者union all的查询语句，除了最左边的查询是primary，其余的查询都是union。
union result：union会对查询结果进行去重，MySQL会使用临时表来完成union查询的去重工作，针对这个临时表的查询就是"union result”
union去重

指令

 explain SELECT * FROM t_user WHERE id = '1'
 UNION 
 SELECT * FROM t_user WHERE id in ('1','2');

union all不去重

指令

 explain SELECT * FROM t_user WHERE id = '1'
 UNION ALL
 SELECT * FROM t_user WHERE id in ('1','2');

derived
在MySQL8.0之前：
在MySQL8.0之后进行了优化，但是如果句子很复杂还是会出现derived

2.3.4 type

type显示的是访问类型，是一个比较重要的指标，常见的可取值为：

| type | 含义 |
| —— | ———————————————————— |
| system | 表只有一行记录（等于系统表），这是const类型的特例，一般不会出现 |
| const | 表示通过索引一次就找到了，const用于比较primary key获取unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询装换为一个常量。const用于将“主键”或“唯一”索引的所有部分与常量值进行比较 |
| eq_ref | 类似ref，区别在于使用的是唯一索引、主键的关联查询，关联查询出的记录只有一条 |
| ref | 非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个） |
| range | 只检索给定返回的行，使用一个索引来选择行。where之后出现between，<，>，in等操作。 |
| index | index与ALL的区别为index类型只是遍历了索引树，通常比ALL快，ALL是遍历数据文件 |
| all | 将遍历全表以找到匹配的行 |
说明：
结果值从最好到最坏依次是：
- system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all
- 比较重要的包括：system > const > eq_ref > ref > range > index > all
- SQL性能优化的目标至少要达到range级别，要求是ref级别，最好是const级别。（阿里巴巴开发手册要求）
ALL：全表扫描。Full Table Scan,将遍历全表找到匹配的行
ALL在InnoDB存储引擎中，扫描的是主键索引（聚簇索引）
而在MyISAM中不存在聚簇索引，所以在MyISAM中全表扫描扫描的是数据文件
指令
```
explain select * from t_user;
```
index：全索引扫描，当使用覆盖索引又需要扫描全部的索引记录时：
指令
```
EXPLAIN SELECT username FROM t_user;
```
指令
```
EXPLAIN SELECT id FROM t_user;
```
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

指令

EXPLAIN SELECT * FROM t_user WHERE id > '2';

ref：通过普通二级索引列与常量进行等值匹配时

指令

EXPLAIN SELECT * FROM user_role WHERE user_id = '1';

eq_ref：连接查询时通过主键或不允许NULL值的唯一二级索引列进行等值匹配时

指令

EXPLAIN SELECT * FROM t_user u, user_role ur WHERE u.id = ur.user_id;

const：根据主键或唯一二级索引列于常数进行匹配时

指令

EXPLAIN SELECT * FROM t_user WHERE id = '1';

指令

EXPLAIN SELECT * FROM t_user WHERE username = 'super';

system：MyISAM引擎中，当表中只有一条记录时。（这是所有type的值中性能最高的场景）

指令

CREATE TABLE t(i int) Engine=MyISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;

2.3.5 possible_keys和key

possible_keys表示执行查询时可能用到的索引，一般是一个或是多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
keys表示实际使用的索引，如果为NULL，则是没有使用索引。

指令

EXPLAIN SELECT * FROM t_user WHERE id = '1';

2.3.6 key_len

表示索引使用的字节数，根据这个值可以判断索引的使用情况，检查是否充分利用了索引，针对联合索引值越大越好。
如何计算：
先看索引字段的类型+长度，例如：int = 4 ; varchar(20) = 20 ; char(20) = 20
如果是varchar或者char这种字符串字段，字符集要承不同的值，比如utf8要乘3，如果是utf8mb4要乘4，GBK要乘2
varchar这种动态字符串要加2个字节
允许为空的字段要加1个字节

数据准备

-- 部门表
CREATE TABLE `t_dept` (
`id` INT NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`CEO` INT(11),
PRIMARY KEY (`id`)
);

-- 员工表
CREATE TABLE `t_emp` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT DEFAULT NULL,
`deptId` INT DEFAULT NULL,
`empno` INT NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
);

-- 插入数据
INSERT INTO t_dept(id,deptName,address, CEO) VALUES(1,'华山','华山', 2);
INSERT INTO t_dept(id,deptName,address, CEO) VALUES(2,'丐帮','洛阳', 4);
INSERT INTO t_dept(id,deptName,address, CEO) VALUES(3,'峨眉','峨眉山', 6);
INSERT INTO t_dept(id,deptName,address, CEO) VALUES(4,'武当','武当山', 8);
INSERT INTO t_dept(id,deptName,address, CEO) VALUES(5,'明教','光明顶', 9);
INSERT INTO t_dept(id,deptName,address, CEO) VALUES(6,'少林','少林寺');

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'风清扬',90,1,100001);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'乔峰',35,2,100005);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'灭绝师太',70,3,100006);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'张三丰',100,4,100008);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'张无忌',25,5,100009);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韦小宝',18,NULL,100010);

-- 创建索引
CREATE INDEX idx_age_name ON t_emp(age, `name`);

指令

-- 测试1
-- age允许为空 int = 4 总长度等于：4+1 = 5
-- name允许为空 varchar(20) = 20 默认为utf8mb4 总长度等于 20 * 4 + 2 +1 = 83
-- key_len = 83 + 5 = 88
EXPLAIN SELECT * FROM t_emp WHERE age = 30 AND `name` like 'ab%';
-- 测试2
-- age允许为空 int = 4 总长度等于：4+1 = 5
EXPLAIN SELECT * FROM t_emp WHERE age = 30;

2.3.7 ref

表示在索引查找中用作比较条件的具体值来源
常见类型
const：使用常量值进行比较
数据库名.表名.列名：使用其他表的列进行比较
func：使用函数或表达式的结果
NULL：当type为index或ALL时，没有使用ref
作用
理解查询执行方式
- 通过ref字段可以清楚地看到MySQL是用什么值来查找索引的
ref字段帮助识别查询优化机会：
- 如果ref是const，说明使用了常量，这通常很高效
- 如果ref是其他表的列，说明是表连接，可以考虑连接顺序优化
- 如果ref是func，可能存在函数计算开销
当查询性能不佳时，ref字段可以帮助确认
- 索引是否被正确使用
- 连接条件是否合理
- 是否有不必要的函数调用

2.3.8 rows和filtered

rows：MySQL认为它执行查询时实际从索引树中查找到的行数。值越小越好。
filtered：客户端查询需要的数据占实际从索引树查询到并返回到服务器的行数的百分比。

指令

-- 如果是全表扫描，rows的值就是表中数据的估计行数
EXPLAIN SELECT * FROM t_emp WHERE empno = 100001;

-- 如果是使用索引查询，rows的值就是预计扫描索引记录行数
EXPLAIN SELECT * FROM t_emp WHERE deptId = 1;

2.3.9 extra

包含不适合在其他列中显示但十分重要的额外信息。通过这些额外信息来理解MySQL到底将如何执行当前的查询语句。MySQL提供的额外信息有好几十种，这里只挑几个比较重要的介绍：
using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于union、order by和group by 效率较低
- 指令
```
explain SELECT * FROM t_user WHERE id = '1'
UNION 
SELECT * FROM t_user WHERE id in ('1','2');
```
using where：使用了where，但在where上有字段没有创建索引，也可以理解为如果数据从存储引擎返回到server层进行过滤，那么就是using where.
- 指令
```
EXPLAIN SELECT * FROM t_emp WHERE `name` = '风清扬';
```
using filesort：
- 如果查询的结果是已经排好序的，则在服务器中就不会使用排序，因为在建立索引树时，是已经排好序的。如果没有建立对应的索引则需要到服务器中进行排序。
- 指令
```
explain select * from t_user order by id;
```
- 如果查询的结果是无序的，则需要在内存中（数据少）或磁盘中（数据多）进行排序。
- 指令
```
explain select * from t_user order by name;
```
using index：使用了覆盖索引，表示直接通过查询当前索引就能获取到想要的数据，不需要再进行回表查询。
- 指令
```
explain select id,username from t_user;
```

3. 数据准备

在进行优化之前需要准备大量数据，这里准备了员工表50w条数据，部门表1w条数据。
为了保证能够快速的插入50w条数据这里使用存储过程，为了保证每一条数据不重复这里使用函数。

员工表

id：自增长
deptName：随机字符串，允许重复
address：随机字符串，允许重复
CEO：1-50w之间的任意数字

部门表

id：自增长
empno：可以使用随机数字，或者`从1开始的自增数字`，不允许重复
name：随机生成，允许姓名重复
age：区间随机数
deptId：1-1w之间随机数

3.1 创建表

指令

-- 部门表
CREATE TABLE `dept` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `deptName` VARCHAR(30) DEFAULT NULL,
    `address` VARCHAR(40) DEFAULT NULL,
    ceo INT NULL ,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

-- 员工表
CREATE TABLE `emp` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `empno` INT NOT NULL ,
    `name` VARCHAR(20) DEFAULT NULL,
    `age` INT(3) DEFAULT NULL,
    `deptId` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

3.2 创建函数

指令

# 查看是否开启了自定以函数
show variables like 'log_bin_trust_function_creators';
# 设置全局生效，所有会话都有效，如果想要设置有永久的话，需要去my.cnf文件中配置
set global log_bin_trust_function_creators=1;

指令

-- 随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) 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 $$

-- 假如要删除
-- drop function rand_string;

指令

-- 用于随机产生区间数字
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$$

-- 假如要删除
-- drop function rand_num;

3.3 创建存储过程

指令

-- 插入员工数据
DELIMITER $$
CREATE PROCEDURE  insert_emp(START INT, max_num INT)
BEGIN  
    DECLARE i INT DEFAULT 0;   
    #set autocommit =0 把autocommit设置成0  
    SET autocommit = 0;    
    REPEAT  
        SET i = i + 1;  
        INSERT INTO emp (empno, NAME, age, deptid ) VALUES ((START+i) ,rand_string(6), rand_num(30,50), rand_num(1,10000));  
        UNTIL i = max_num  
    END REPEAT;  
    COMMIT;  
END$$

-- 删除
-- DELIMITER ;
-- drop PROCEDURE insert_emp;

指令

-- 插入部门数据
DELIMITER $$
CREATE PROCEDURE insert_dept(max_num INT)
BEGIN  
    DECLARE i INT DEFAULT 0;   
    SET autocommit = 0;    
    REPEAT  
        SET i = i + 1;  
        INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
        UNTIL i = max_num  
    END REPEAT;  
    COMMIT;  
END$$

-- 删除
-- DELIMITER ;
-- drop PROCEDURE insert_dept;

3.4 调用存储过程

指令

-- 执行存储过程，往部门表添加1万条数据
CALL insert_dept(10000)$$

-- 执行存储过程，往员工表中添加50万条数据
CALL insert_emp(100000,500000)$$

3.5 开启SQL执行时间显示

指令

-- 查看是否开启profiling
show variables like 'profiling';
-- 设置开启profiling
set global profiling = 1;
-- 显示所有语句执行使用的时间
show profiles;
-- 查看某一条数据执行使用的准确时间
show profile for query 编号

说明
这个profiling只能显示15条数数据，如果超过了15条数据则会从语句执行的前后进行覆盖，先执行的会被先覆盖。

4. 单表索引失效案例

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快了查询的速度，因此索引对查询速度有着至关重要的影响。
而创建索引之后是否使用索引是由优化器决定的，优化器会基于索引的开销来选择索引，如何选择索引是根据索引的开销大小来决定，开销越小则优先选择。不是基于某种规则，也不是基于某种语义。
此外，SQL语句是否会使用索引和数据库的版本、数据量、数据过滤的效率、数据选择度（查询中选择的列数）、运行环境都有关系。
创建测试使用的索引

指令

create index index_name on emp(`name`);

4.1 计算、函数会导致索引失效

在查询中使用的计算会导致索引失效。
在查询中使用了函数也会导致索引失效。

正常指令

explain select * from emp where emp.name like 'abc%';

索引失效指令

explain select * from emp where left(emp.name,3) = 'abc';

4.2 like以%开头会导致索引失效

指令

explain select * from emp where emp.name like '%abc%';

说明
在阿里开发手中提到
- 强制：页面搜索严禁左模糊或全模糊，如果需要则走搜索引擎

4.3 不等于（!= 或 <>）会导致索引失效

正常查询指令

explain select * from emp where emp.name = 'abc';

索引失效查询指令

explain select * from emp where emp.name <> 'abc';

4.4 is not null 和 is null

is not null不一定会导致索引失效，这个需要分情况讨论：
如果大量数据都是NULL，小部分数据是非NULL，则索引会生效，此时type字段是range
如果没有NULL的情况下，则索引失效。
注意
对于是否等于NULL和不等于NULL需要注意以下几点
- 不要使用!=、<> 、=这些的结果都是NULL得不到想要的结果。
- 也不要使用NULL与其他常量进行对比，比如NULL <> 1、NULL = 1、NULL != 1 ,其返回结果皆为NULL。

NULL也是做比对的，不过它是最小的

设置一个emp.name为null，然后进行排序

-- 修改指令
update emp set name = NULL where id = '3';
-- 排序
select * from emp order by emp.name limit 10 ;

正常查询指令

explain select * from emp where emp.name is null ;

索引失效指令

explain select * from emp where emp.name is not null;

将大量的数据修改成NULL，然后进行is not null的查询

指令

-- 将id大于20000的name设置为null
update emp set name = null where id > 20000;
-- 查看是否使用索引
-- 数据为NULL的数据量没有准确的数值，需要自行测试
explain select * from emp where emp.name is not null;

测试完之后将数据改回

指令

update emp set name = rand_string(6) where id > 20000;

4.5 类型转换会导致索引失效

未使用类型转换

explain select * from emp where emp.name = '123';

使用类型转换

explain select * from emp where emp.name = 123;

4.6 全索引匹配效率最高

运行以下语句，查看语句运行效率

-- 查看语句是否使用到了索引
explain select * from emp where age = 30 and deptid = 4 and name = 'abcd';
-- 进行查询，查看结果
select * from emp  where age = 30 and deptid = 4 and name = 'abcd';
-- 通过profiles查看执行时间
show profiles;

逐一创建索引，查看哪个索引效率最高

-- 创建二级索引 index_age
create index index_age on emp(age);
-- 创建复合索引 index_age_deptid
create index index_age_deptid on emp(age,deptid);
-- 创建复合索引
create index index_age_deptid_name on emp(age,deptid,name);

二级索引 index_age
复合索引 index_age_deptid
复合索引 index_age_deptid_name
通过执行时间的比对，全索引匹配的效率最高

4.7 最左前缀法则

注意
如果索引了多列，想让索引字段全部生效，则需要满足最左前缀法则不可以跳过字段，否则后面的字段就会失效。
- 这里的最左前缀法则，不是一定要按照索引中的顺序来进行构建where部分的条件，而是where部分的条件字段要有构建复合索引的字段。复合索引中的字段顺序从左到右在where条件中要有对应的字段（没有顺序要求），不可以跳过左边的字段。可以理解为对于在where条件中的顺序没有要求，只要没有跳过左边的字段即可。

运行如下SQL语句，查看索引中字段是否全部生效

explain select * from emp where age = 30 and name = 'abcd';
-- explain 结果
-- key index_age_deptid_name
-- key_len 5 
-- 只使用了age,deptid和name失效了，为什么name没有生效，因为跳过了deptid导致name失效

explain select * from emp where  deptid = 4 and name = 'abcd' ;
-- explain 结果
-- key null
-- key_len null
-- 没有使用索引，因为跳过了复合索引中age字段，所以导致索引失效

explain select * from emp where age = 30 and deptid = 4 ;
-- explain 结果
-- key index_age_deptid_name
-- key_len 10
-- 只有age,deptid生效了，因为没有name条件

explain select * from emp where age = 30 and deptid = 4 and name = 'abcd';
-- explain 结果
-- key index_age_deptid_name
-- key_len 93
-- 复合索引字段全部生效

explain select * from emp where name = 'abcd' and deptid = 4 and age = 40;
-- explian 结果
-- key index_age_deptid_name
-- key_len 93
-- 复合索引字段全部生效，由此可以得出不是where条件中的字段顺序要和复合索引中设置的字段顺序一样，而是需要全部都有顺序没有要求，但是不可以跳过复合索引中从左到右的字段顺序，也就是where条件中不能没有左边的字段

4.8 索引中的范围条件右边的列失效

说明
就是索引中如果某个字段在where条件中使用了范围条件，其右边的字段会失效，这个的前提是满足最左前缀法。
也需要注意不是说where中的右边，而是索引中的右边字段会失效。如果将范围调换到where条件最后你会发现还是失效，所以是索引的右边字段。因此如果会使用范围的字段要放在索引的所有字段后面。

执行以下SQL语句，查看结果

-- 使用范围
explain select * from emp where age = 20 and deptid > 1000 and name = 'abcd';
-- 使用范围，将范围放在where条件的最后面
explain select * from emp where age = 20 and name = 'abcd' and deptid > 1000;
-- 不符合最左前缀法则
explain select * from emp where  deptid > 1000 and name = 'abcd';

注意：当修改deptid的范围条件的时候，例如deptid > 100 那么整个索引失效，MySQL的优化器基于成本计算后认为没必要使用索引了，因为这样比全表扫描没开小更大，所以进行了全表扫描。（因为表中的数据是随机生成的，因此实际测试中根据测试具体数据的不同测试的结果也会不同，最终是否使用索引由优化器决定）
创建一个新的索引将deptid放到索引的最后面

指令

create index index_age_name_deptid on emp(age,name,deptid);

注意：两个索引都存在时，MySQL的优化器会选择开销最小的那个索引。

5. 关联查询优化

5.1 数据准备

-- 分类
CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
-- 图书
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

-- 插入16条记录
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)));

-- 插入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)));

5.2 左外连接

两张表都没有创建索引

指令

explain select * from  class left join book on class.card = book.card;

在启动表上建立索引，即class表

指令

create index index_class_card on class(card);

执行左外连接的指令

使用到了索引，进行了全索引扫描

explain select * from  class left join book on class.card = book.card;

在被驱动表上建立索引，即book表
使用到了索引，没有使用全表扫描，type为ref

指令

-- 删除class的索引
-- 在book表上创建索引
create index index_book_card on book(card);

explain select * from  class left join book on class.card = book.card;

在驱动表和被驱动表上建立索引
使用到了索引，class使用了全索引扫描，book表没有进行全表扫描，type类型为ref

指令

create index index_class_card on class(card);

explain select * from  class left join book on class.card = book.card;

结论
通过上述测试，建立索引的尽量是被驱动表，驱动表尽量是小表。（因为驱动表不管怎么样都是需要进行全表扫描，而被驱动表则可以不进行全表扫描，所以应驱动表尽量是小表，被驱动表建立索引）

5.3 内连接

删除之前的创建的索引

指令

drop index index_class_card on class;
drop index index_book_card on book;

不是使用索引直接查询，然后交换表的位置

指令

explain select * from class inner join book on class.card = book.card ;

explain select * from book inner join class on class.card = book.card ;

结论
- 在内连接中，是将小表作为驱动表，大表作为被驱动表。而在左外连接中，是将左边的表作为驱动表，右边的表作为被启动表
- 在内连接中就没有左右之分了。
给class表建立索引，然后进行查询

创建索引指令

create index index_class_card on class(card);

执行指令，然后交换位置

explain select * from class inner join book on class.card = book.card ;

explain select * from book inner join class on class.card = book.card ;

给book表建立索引，然后删除class表的索引

索引指令

-- 删除class索引
drop index index_class_card on class;
-- 建立book的二级索引
create index index_book_card on book(card);

执行指令，然后交换位置

explain select * from class inner join book on class.card = book.card ;

explain select * from book inner join class on class.card = book.card ;

结论
- 在内连接中，建立了索引的表做被驱动表，没有建立索引的做驱动表，左外连接不变，还是左边为驱动表，右边为被驱动表
给两张表都建立索引

索引指令

create index index_class_card on class(card);

执行指令，然后交换位置

explain select * from class inner join book on class.card = book.card ;

explain select * from book inner join class on class.card = book.card ;

结论
- 在内连接中，所有表都有索引的情况下，小表做启动表，大表做被驱动表。左外连接不变，还是左边为驱动表，右边为被驱动表
总结
在内连接中，即使交换了位置MySQL优化器还是会自动优化成小表做驱动表，大表做被驱动表。
自动优化的原则为：建立了索引的表作为被驱动表，小表做驱动表。

5.4 查询方式选择

需求：查询每个人对应的CEO名字

使用左连接方式：时间较短，推荐

-- 大表 emp(员工) dept(部门)
explain select emp.name,ceo.name AS  ceoname from emp 
left join dept on dept.id = emp.deptid
left join emp AS ceo on dept.ceo = ceo.id; 

-- 小表 t_emp(员工) t_dept(部门)
explain select emp.name,ceo.name AS ceoname from t_emp As emp
left join t_dept dept on dept.id = emp.deptid
left join t_emp AS ceo on dept.ceo = ceo.id;

使用子查询的方式：时间很长，不推荐

-- 大表 emp(员工) dept(部门)
explain select emp.name,
(select emp.name from emp  where emp.id = dept.ceo)  AS ceoname
from emp 
left join dept on dept.id = emp.deptId;

-- 小表 t_emp(员工) t_dept(部门)
explain select emp.name,
(select emp.name from t_emp AS emp where emp.id = dept.ceo) AS ceoname
from t_emp AS emp
left join t_dept AS dept on emp.deptId = dept.id;

使用临时表连接方式：时间与第一种方式相差不大,推荐

-- 大表 emp(员工) dept(部门)
explain select emp_with_dept.name,emp.name AS ceoname
from
(
select emp.name,dept.ceo 
from emp 
left join dept on dept.id = emp.deptId
) AS emp_with_dept
left join emp on emp.id = emp_with_dept.ceo;

-- 小表 t_emp(员工) t_dept(部门)
explain select emp_with_dept.name,emp.name AS ceoname
from
(
select emp.name,dept.ceo
from t_emp AS emp
left join t_dept AS dept on dept.id = emp.deptId
) AS emp_with_dept
left join t_emp AS emp on emp_with_dept.ceo = emp.id;

使用临时表连接方式2：时间特别长，不推荐

-- 大表 emp(员工) dept(部门)
explain select emp.name,ceo.ceoname 
from emp 
left join 
(
select emp.deptId,emp.name AS ceoname
from emp 
inner join dept on dept.ceo = emp.id
) AS ceo
on ceo.deptId = emp.deptId;

-- 小表 t_emp(员工) t_dept(部门)
explain select emp.name,ceo.ceoname
from t_emp AS emp
left join 
(
select emp.deptId,emp.name AS ceoname
from t_emp AS emp
inner join t_dept AS dept on dept.ceo = emp.id
) AS ceo
on ceo.deptId = emp .deptId;

总结
在多表关联的情况下，能使用关联查询就尽量使用关联查询，少使用子查询（减少查询的趟数）

6. 子查询优化

需求：查询非CEO的员工
方式一：子查询方式

指令

-- 大表 emp(员工) dept(部门)
explain select * from emp where emp.id not in (
select dept.ceo from dept where dept.ceo is not null
);

-- 小表 t_emp(员工) t_dept(部门)
explain select * from t_emp where id not in (
select ceo from t_dept where ceo is not null
);

方式二：左连接的方式

指令

-- 大表 emp(员工) dept(部门)
explain select emp.* from emp 
left join dept on dept.ceo = emp.id where dept.id is null;

-- 小表 t_emp(员工) t_dept(部门)
explain select t_emp.* from t_emp 
left join t_dept on t_dept.ceo = t_emp.id where t_dept.id is null;

给ceo字段添加索引，然后再测试两种方式

指令

create index index_dept_ceo on dept(ceo);

总结
尽量不要使用 not in或者 not exists，因为它们属于函数会导致索引失效所以不建议使用它们，可以是左连接加where条件替代，如：left join xxx on xxx = xxx where xx is null;
特别说明：
不是使用连接就一定效率高的，不是使用了索引就一定会提高效率，不是使用子查询就一定效率低，而是通过查看具体的执行结果来决定是否效率高。所以对SQL语句做的优化是否真的有用和有效，需要通过具体的执行结果来定的。

7. 排序优化

7.1 索引失效的情况

以下三种情况不走索引
无过滤，不索引
顺序错，不索引
方向反，不索引

7.1.1 测试

创建索引

create index index_age_deptid_name on emp(age,deptid,name)

无过滤，不索引

-- 索引失效语句
explain select * from emp order by age,deptid;
-- 索引成功语句，使用order by想索引生效必须要使用过滤条件，如where,limit等
explain select * from emp order by age,deptid limit 10;

顺序错，不索引

-- 排序使用了索引：
-- 注意：key_len = 5说明where语句使用age索引的标记。order by语句使用索引不在key_len中体现。
-- order by语句如果没有使用索引，在extra中会出现using filesort。
-- 为什么说如果order by语句没有使用索引，在extra中会出现using filesort呢？
-- 首先，在建立索引的时候，MySQL会按照索引中的字段从左右进行排序，也就是说在索引树中的数据都是排好序的，因此可以说如果order by语句没有使用索引，在extra中会出现using filesort。
explain select * from emp where age = 45 order by deptid ;

-- 使用了索引
explain select * from emp where age = 45 order by deptid,name;

-- 没有使用索引，因为empno没有建索引
explain select * from emp where age = 45 order by deptid,empno;

-- 没有使用索引，因为order by中的字段顺序没有满足在索引中的顺序，索引中是先按照在deptid排序再按照name排序，而order by 中没有满足条件。
-- 注意，再order by中索引中的最左前缀方法使用不了
explain select * from emp where age = 45 order by name,deptid;

-- 没有使用索引，再where条件中没有满足最左前缀法则，索引失效
explain select * from emp where deptid = 45 order by age;

方向反，不索引

-- 使用了索引，排序条和索引一致，并方向相同（同为降序）
explain select * from emp where age = 45 order by deptid desc,name desc;
-- 没有使用到索引，方向不同，一个升序一个降序
explain  select * from emp where age = 45 order by deptid asc,name desc;

7.2 索引优化案例

优化目的
尽量去除extra中的using filestort（手动排序）和using where（手动过滤）。
需求：查询年龄为30岁，且员工编号小于101000的用户，按用户名称排序。
开启profiling功能查看SQL语句的执行时间

指令

-- 查看profiling 
show variables like '%profiling%';

-- 设置profiling为开启状态
set profiling = 1;

不创建索引直接进行查询，查看explain执行计划

explain select * from emp where age = 30 and empno < 101000 order by name;

解决方式一：创建复合索引，给age、empno、name字段建立索引

指令

-- 创建复合索引 index_age_empno_name
create index index_age_empno_name on emp(age,empno,name);
-- 执行查询语句
explain select * from emp where age = 30 and empno < 101000 order by name;

说明
- 为什么会出现extra中usinmg filesort ？
- 因为在索引中范围字段的右边字段会失效，索引在服务器中使用到了手动排序
解决方式二：创建复合索引，给age、name字段创建索引。

指令

-- 删除之前的索引
drop index index_age_empno_name on emp;

-- 创建复合索引 index_age_name
create index index_age_name on emp(age,name);

-- 执行查询语句
explain select * from emp where age = 30 and empno < 101000 order by name;

说明
- 因为没有对empno建立索引，索引在查询完结果之后，在服务器中进行了手动排序
解决方式三：创建复合索引，给age、empno字段创建索引。

指令

-- 删除之前的索引
drop index index_age_name on emp;

-- 创建复合索引 index_age_empno
create index index_age_empno on emp(age,empno);

-- 执行查询语句
explain select * from emp where age = 30 and empno < 101000 order by name;

说明
为什么会出现对排序字段加了索引，查询速度还是比不上只对where条件中的字段加索引的查询速度快，为什么对所有字段加了索引也比不上where条件中的字段加索引查询速度快呢？
- 首先对所有字段加索引，虽然所有字段都有索引，但是因为有范围查询。因为在索引中范围查询的右边索引字段会失效所以范围之后的索引字段失效了，所以name字段没有产生效果，又因为三个字段的索引比数比两个字段的索引数大，索引消耗的时间比两个索引的时间长一点。
- 其次，在where条件中已经过滤掉了大部分数据，所以最后在服务器中排序所消耗的时间并不是很长，如果对排序字段加索引不对范围字段加索引，就会发现排序所需要的时间就会特别长，因为在前面的where条件中已经过滤的大部分数据，如果放弃的过滤大部分数据，只选择排序的话所消耗的时间会特别长，几乎是范围字段索引使用时间的两倍。
结论
当范围条件和排序group by 、order by 做选择时，应当现查看范围条过滤之后的数据量大小，如果数据量很小的情况下建议选择给范围条件建立索引，如果数据量还是很大的情况下建议选择给排序字段建立索引。这样可以通过对数据量的小来做取舍。
也可以将所有的索引都建立起来，让MySQL的查询优化器去选择效率最高的索引，这样就能很准确的选择出最合适的索引，而不需要一直去调试获取最合适的索引。

7.3 双路排序和单路排序

尽量使用索引进行排序，避免使用filesort进行排序，遵循最左前缀法则
如果使用了filesort手动排序，则有两种算法：
双路排序
单路排序

7.3.1 双路排序

在MySQL4.1之前使用的是双路排序，顾名思义就是通过两次IO来获取到想要的数据。
首先，根据行指针从磁盘读取排序字段，在buffer中进行排序。
在按照排序字段从磁盘中读取其他字段，是随机IO
取一批数据，对磁盘进行二次扫描，因为进行多次IO对性能的消耗很大，所以在MySQL4.1之后进行了优化，使用了单路排序

7.3.2 单路排序

从磁盘中查询所需要的所有字段，按照order by排序字段在buffer进行排序
相比于双路排序，单路排序的效率更高一些，因为一次性把所有的数据都读取出来了，只进行了一次IO避免了二次IO，并且是顺序IO，不是随机IO，但是因为它一次的把所有数据都读取出来，所以其使用的空间也会比较大。

7.3.3 结论及问题

单路排序比双路排序占用的内存空间更大。
因为单路排序是把所以数据都读取出来，可能取出来的数据总量超过sort_buffer_size的容量，导致每次只能取出sort_buffer_size容量的数据，进行排序（存在在tmp文件中，然后再进行多路合并），排序完后再取出sort_buffer_size容量的数据，然后再排序，这样就出现了多次IO问题。
原本单路排序是为了解决多次IO问题，结果却导致了大量的IO操作。

7.3.4 优化策略

减少select后面的查询的字段，在order by 时使用select * 是一个过度消耗内存的行为，因为多余的字段会占用sort_buffer_size的容量。
增大sort_buffer_size参数的设置，这个需要根据系统的能力去提高，因为这个参数是针对每个进程（connection）

查看sort_buffer_size参数指令

-- 262144/1024 = 256 k
show variables like 'sort_buffer_size';

增大max_length_for_sort_data参数的设置，MySQL根据max_length_for_sort_data参数来确定使用哪种算法，默认值为4096字节，当排序的列包含的数据超过max_length_for_sort_data参数值时，使用双路排序算法，否则使用单路排序算法。适当提高max_length_for_sort_data参数，有助于提高单路的使用率，但是如果设置的太高数据总容量超出sort_buffer_size的概率就增大，此时就会出现高的磁盘IO活动和低的处理器使用率。

查看max_length_for_sort_data参数指令

-- 4096 / 1024 = 4 k
show variables like '%max_length_for_sort_data%';

8. 分组优化

group by 使用索引的原则几乎跟order by一致。但是group by即使没有过滤条件使用到索引，也可以直接使用索引。而order by必须要有过滤条件才能使用上索引。
包含了order by、group by、distinct(去重)这些查询的语句，where条件过滤出来的结果集请保持在1000行数据以内，否则SQL会很慢。

9. 覆盖索引优化

禁止使用select * ，禁止查询与业务无关的字段
尽量利用覆盖索引

六、基础日志

1. 日志分类

在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，用来帮助数据库管理员追踪数据库曾经发生过的各种事件。MySQL有多种类型的日志，用于记录数据库的操作和状态。以下是一些常见的M有SQL日志：

错误日志（Errot Log）：主要记录MySQL服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等。
查询日志（Query Log）：记录所有执行的查询语句，包括SELECT、INSERT、UPDATE、DELETE等操作。可以用于分析查询性能和调试问题，但需要注意对于高负载的系统开启查询日志可能会对性能产生影响。
慢查询日志（Slow Query Log）：记录执行时间超过指定阈值的查询语句。慢查询日志可以帮助你找出执行时间较长的查询，以便进行性能优化。
二进制日志（Binary Log）：记录所有对数据库的更改操作，包括数据修改、表结构变更等。二进制日志可以用于数据恢复、主从复制等场景。
事务日志（Transaction Log）：包括redo log和undo log，其中redo log也称为重做日志，记录正在进行的事务的更改操作。事务日志用于保证数据库的ACID特性，并支持奔溃恢复。

2. 错误日志

错误日志是MySQL中最重要的日志之一，它记录了当MySQL启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。默认是开启的
查看日志位置指令
```
show variables like 'log_error%';
```
log_error设置为stderr并且MySQL以守护进程（daemon）方式运行，那么错误日志将被重定向到系统日志文件中（例如：/var/log/mysqld.log），而不是直接输出到控制台。
可以在my.cnf中进行配置，设置错误日志的输出位置：
打开主机中与MySQL服务挂载的目录，在my.cnf文件中进行配置（没有则进行创建），该目录通常与/etc/mysql/conf.d目录进行挂载。
```
vim ./my.cnf
```

在文件中添加如下配置

# 注意这里填写的目录需要时MySQL容器中的目录，不能是主机上的目录，一般数据型文件会放在/var/lib/mysql目录中
[mysqld]
log_error=/var/lib/mysql/mysql-error.err

重启MySQL进行测试。
```
docker restart mysql
```
查看日志内容：
- 指令
```
tail -f ./mysql-error.err
```
注意：
错误日志中记录的并非全是错误信息，例如MySQL如何启动InnoDB的表空间文件、如何初始化自己的存储引擎等，这些也记录在错误日志文件中。

3. 查询日志

查询日志中记录客户端的所有操作语句[CRUD]，默认情况下查询日志是未开启的。如果需要开启查询日志，可以设置以下配置：

# 该选项用来开启查询日志，可选：0或1：0代表关闭，1代表开启
general_log = 1
# 设置日志的文件名，如果没有指定，默认的文件名为host_name.log
general_log_file = file_name

在my.cnf文件中配置查询日志
```
vim ./my.conf
```

在配置文件中添加如下配置

general_log = 1
general_log_file=/var/lib/mysql/mysql_query.log

配置完毕之后重启服务器，然后在数据库中部操作以下指令

select * from t_emp ;
update t_emp emp set emp.age = 20 where emp.id = 10 ;
insert into t_emp(name , age , deptId , empno) VALUES('张三', 18 ,null , 100011) ;
delete from t_emp where id = 11 ;

查看日志文件

-- 查看文件内容
cat mysql_query.log
-- 查看文件内容，然后监听文件输出
tail -f mysql_query.log

开启查询日志注意事项：
开启查询日志会对MySQL的性能产生一定影响，特别是再高负载的环境下。因此，在生产环境中建议谨慎使用，并根据需要进行开启和关闭。
查询日志可能会记录大量的查询语句，导致日志文件过大。可以通过定期清理或限制日志文件大小来处理这个问题
查询日志可能会包含敏感信息（如：密码），因此要确保只有授权的人员才可以访问查询日志文件。

4. 慢查询日志

慢查询日志记录所有执行时间超过参数long_query_time设置值，long_query_time默认为10秒，最小值为0精度可以到微妙。

4.1 日志参数配置

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为启动慢查询日志会带来一定的性能影响。
慢查询日志默认是关闭的，可以通过两个参数来控制慢查询日志：
查询慢查询是否开启以及日志文件位置：
```
show variables like '%slow_query_log%';
```

查询long_query_time的值

-- 默认为10秒
show variables like '%long_query_time%';

设置参数以便测试

-- 该参数用来控制慢查询日志是否开启，可取值：1和0，1表示开启，0表示关闭
set global slow_query_log = 1;
-- 该选项用来配置查询的时间限制，超过这个时间将认为是慢查询，需要进行日志记录，默认为10秒
set global long_query_time = 0.1 ;
-- 设置慢查询日志文件名称以及存储位置
set global slow_query_log_file = /var/lib/mysql/mysql-slow.log

4.2 日志内容读取

与错误日志、查询日志一样，慢查询日志记录的格式是纯文本，可以被直接读取。
执行查询语句
```
select * from emp;
```
查看日志内容

指令

-- 查看文件内容
cat mysql_slow.log
-- 查看文件内容，然后监听文件输出
tail -f mysql_slow.log

mysqdumpslow:
在生产环境中，如果要手动分析日志，查找、分析SQL显然非常繁琐，为此MySQL提供了日志分析工具mysqldumpslow。
注意：
- 在默认情况下，传统rpm方式安装的MySQL环境自带mysqldumpslow工具，直接使用即可。docker下安装的MySQL环境没有mysqldupmslow公开
- 可以将当前服务器上的文件复制到有mysqldumpslow的服务器上进行分析
```
scp -r ./mysql-slow.log 用户名@ip地址:目录
```

在传统的MySQL环境（非docker环境）下执行以下指令：

-- 查看mysqldumpslow的帮助信息
mysqldumpslow --help

-- 工作常用参考
-- 1.得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /opt/slow.log
-- 2.得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /opt/slow.log
-- 3.得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /opt/slow.log
-- 4.另外建议在使用这些命令时结合 | 和more 使用 ，否则语句过多有可能出现爆屏情况(回车移动一行，空格移动一页)
mysqldumpslow -s r -t 10 /opt/slow.log | more

指令解释
-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序；
- c: sql语句的访问次数
- l: 锁定时间
- r: 返回数据记录集的总数量
- t: 查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；

七、事务日志

1. 事务概述

概述：就是由多个操作组成的一个逻辑单元，组成这个逻辑单元的多个操作要么都成功要么都失败。
作用：保证数据的一致性
举例：转账

2. ACID四大特性

原子性（Atomicity）：
一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间的某个环节。事务在执行过程中发生错误会被回滚（Rollback）到事务开启前的状态，就像这个事务从来没有执行过一样。
一致性（Consistency）：
事务的一致性指的是在一个事务执行之前和执行之后数据库必须处于一致性状态。如果事务成功的完成，那么系统中所有的变化将正确的应用，系统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动的回滚，系统返回到原始状态。
隔离性（loslation）：
指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整的数据空间，由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。
事务查看数据更新时，数据所处于的状态要么是另一件事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。
持久性（durability）：
指的是只要事务成功结束，它对数据库所做的更新就必须保存下来。即使发生了系统崩溃，重新启动数据库系统以后，数据库还能恢复到事务成功结束时的状态。
注意：
事务的隔离性由锁机制实现
而事务的原子性、一致性和持久性由事务的redo日志和undo日志来保证。
- redo log称为重做日志，它记录了对数据库进行修改的操作，包括插入、更新和删除等。rdeo日志的主要作用是保证数据库的持久性和恢复能力。
- undo log称为回滚日志，它记录了对数据库进行修改的逆操作，用于实现事务的回滚和MVCC（多版本并发控制）

3. redo log

3.1 innodb写数据过程

innodb存储引擎是以页为单位（大小为16k）来管理存储空间的。在真正的访问页面之前，需要把磁盘中的数据页缓存到内存【Buffer Pool缓存池】，之后才可以访问，而在缓存池中的数据也称之为脏页（因为每次的修改或者访问的数据只是数据页中的一部分）。所有的变更必须先更新缓存池中的数据，然后缓存池中的脏页会以一定的频率被刷到磁盘中（checkpoint机制），刷盘是随机的但脏页占满了缓存池会进行刷盘。通过缓存池来优化CPU和磁盘之间的访问速度，这样就保证了整体的性能不会下降太快。
checkpoint机制主要的作用是将缓冲池中的脏页刷新到磁盘。

3.2 redo 日志

查看redo日志缓冲(redo log buffer)的大小，默认是16777216 / 1024 / 1024 = 16 M，最大值为4096M，最小值为1M

指令

show variables like '%innodb_log_buffer_size%';

查看redo日志文件，在docker容器中的位置为/var/lib/mysql，可以通过docker的挂载的目录查看
redo日志整体写数据的流程
流程说明
- 先将原始数据从磁盘中读取到内存中，然后修改在内存中数据拷贝
- 生成一条重做日志并写入到redo log buffer，记录修改后的数据值
- 当事务进行commit时，将redo log buffer中的数据刷新到磁盘中的redo log file 中，对redo log file采用的时追加写。
- 定期将修改的数据刷新到磁盘磁盘中，当发生宕机时有的数据未及时刷新到磁盘中，可以通过redo log来进行恢复，这样就保证事务ACID中的持久性，这就是redo log的作用

3.3 redo log 的优点和特点

优点
redo日志降低了data buffer的刷盘频率
redo日志占用的空间小，更新速度快（其本身只是记录了要修改的数据本身而不是数据页本身所以占用的内存比较小，因而刷新到磁盘的速度比较快）
特点
redo日志是顺序刷入磁盘的
在事务的执行过程中，redo log 不会断记录。

3.4 redo log 的刷盘策略

redo log的写入并不是直接写入磁盘的，InnoDB存储引擎在写redo log时是将记录先写在redo log buffer，然后再以一定的策略将redo log buffer刷新到磁盘的redo log file，这里所说的策略就是刷盘策略
注意：
这里的redo log buffer刷盘redo log file并不是真正的刷新到磁盘之中，而是刷新到文件缓冲（page cache）中去（这是当前操作系统为了提高文件写入效率做的一个优化），真正的写入是交给操作系统来决定（比如说page cache满了），这样一来对于InnoDB就存在一个问题，交给系统来决定何时同步，假如系统宕机了数据不就丢失了吗？，虽然这种机率很小但是还是存在的。
针对这种情况，InnoDB给出了innodb_flush_log_at_trx_commit参数，这个参数是用来选择在commit事务时，如何将redo log buffer中的日志刷新到redo log file中的
- 查看innodb_flush_log_at_trx_commit参数指令
```
-- 默认参数值为 1
show variables like '%innodb_flush_log_at_trx_commit%';
```
innodb_flush_log_at_trx_commit有三种参数
- 参数为0时，表示每次事务提交时不进行刷盘操作（不往文件系统中写）。系统master thread默认每隔1s做一次重做日志的同步，性能最佳，风险最高，MySQL宕机或者操作系统宕机都会影响刷盘操作，丢失1s内的数据
- 参数为1时，表示每次提交事务都将数据进行同步，刷新到磁盘中（默认值），性能稍差，数据安全性较高
- 参数为2时，表示每次事务提交时都只把redo log buffer刷新到page cache，bu进行同步。由操作系统自己决定什么时候同步到磁盘文件中，性能价高，数据安全性较高，因为从page cache刷盘到redo.file是操作系统的任务，只要操作系统不宕机，MySQL服务器宕机不影响日志刷盘。

4. undo log

4.1undo日志简介

事务需要保证原子性，即要么事务全部完成，要么事务全部是失败，可是大部分情况是做到一半就失败了或是出现一些情况：
情况一：可能会出现各种错误，如发生异常、服务器宕机、操作系统宕机等。
情况二：也有可能是对事务进行回滚rollback。
以上的情况下需要将数据恢复至原先的状态，这种行为称之为回滚，保证事务的原子性。
如何保证原子性，为此需要对（UPDATE、INSERT、DELETE）做出一些记录，保证在发生异常时能够回退至开始的状态，例如：
当插入一条数据时，应当要记录一下这条记录的主键值，这样在回滚时方便将这条记录进行删除操作，（每执行一条insert语句，innodb存储引擎都会生成与之对应的delete语句）
当删除一条记录时，应当要记录一下这条记录的所有数据，这样在回滚时方便将这些数据进行插入操作，（每执行一条delete语句，innodb存储引擎都会生成与之对应的insert语句）
当修改一条记录时，应当要记录一下这条记录被修改前的数据，这样在回滚时方便将这些数据修改回之前的状态。（每执行一条update语句，innodb存储引擎都会生成与之对应相反的update语句）
MySQL将这些回滚记录称之为撤销日志或回滚日志（undo log）
redo log是保证事务的持久性，undo log是保证事务的原子性，在事务中每个更新数据的操作之前都会记录一条回滚日志。
undo 日志的作用
回滚数据
MVCC

4.2 undo log的存储结构

InnoDB对undo log的管理方式是采用段，也就是回滚段（rollback segment），每一个回滚段记录了1024个undo log segment，而在每一个undo log segment进行undo页（也就是回滚记录）的申请，在InnoDB1.1版本之前（不包括InnoDB1.1）只有一个rollback segment，也就是说只能有1024个事务同时在线，这也能满足绝大多数应用的需求，从InnoDB1.1开始支持最大128个rollback segment，故其支持的同时在线事务数为128 * 1024 = 131072个

4.3 undo log生成过程

假设当前有两条记录，分别为A = 1 和 B = 2 ，然后将A修改成3，将B修改成4

  -- 开启事务
1 start transaction;
2 -- 记录A = 1 到 undo log
3 update A = 3 ;
4 -- 记录A = 3 到 redo log
5 -- 记录B = 2 到 undo log
6 update B = 4 ;
7 -- 记录 B = 4 到 redo log
8 -- 将redo log 刷新到磁盘
9 commit ;

异常分析
假设1~8发生了异常或者宕机，这个事务没有提交，该事务对不会数据造成任何影响。
假设8~9发生了异常或者宕机，此时已经持久化了redo log和undo log可以选择回滚数据或者继续提交事务
假设9发生了异常或者宕机，内存中的数据映射还没有来的及刷新到硬盘，那么在恢复服务和系统之后，可以通过redo log将数据恢复到磁盘中。

5. 总结

原子性：
undo log 是数据没有提交前的系统崩溃后的数据回滚能力
持久性
redo log 是数据提交之后的系统崩溃后的数据存储能力
隔离性
锁
一致性
undo log 、redo log 、锁

八、锁

1. 并发事务带来的问题

在典型的应用程序中，多个事务并发运行，可能导致以下问题：

脏读（dirty read）：
当一个事务正在访问数据并且对数据进行修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个是数据。因为这个数据是还没有提交的数据，那么另外一个事务读到这个数据是“脏数据”
丢失修改（lost to modify）：
指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（unrepeatableread）：
指在一个事务内多次读同一个数据。在这个事务还没有结束时，另一个事务修改了该数据。那么，在第一个事务中的两次读取数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这样就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重读读。
幻读（phantom read）：
幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

2. 并发事务解决方案

MySQL四种隔离级别如所示：

读未提交（READ UNCOMMITTED）：这个隔离级别下，其他事务可以看到本事务没有提交的修改。造成脏读，这个级别的性能没有足够大的优势，但是又有很多的问题，因此很少使用

数据准备

# 创建数据库表
create table goods_innodb(
    id int NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;

# 插入数据
insert into goods_innodb(name) values('华为');
insert into goods_innodb(name) values('小米');

测试

-- 会话一

-- 设置事务隔离级别为读未提交
set session transaction isolation level read uncommitted ;

-- 开启事务
start transaction;

-- 查询数据
select * from goods_innodb;

-- 会话二

-- 设置事务隔离级别为读未提交
set session transaction isolation level read uncommitted ;

-- 开启事务
start transaction ;

-- 修改数据
update goods_innodb set name = '中兴' where id  = 2 ;


-- 会话一
-- 查询数据
select * from goods_innodb;

-- 会话二
-- 提交事务
commit ;

读已提交（READ COMMITTED）：解决脏读。其他事务可以看到本事务已提交的修改。这个隔离级别有不可重复读的问题，在同一个事务内的两次读取，拿到的结果竟然不一样，因为另外一个事务对数据进行了修改。

-- 会话一
set session transaction isolation level read committed;

-- 开启事务
start transaction ;

-- 查询
select * from goods_innodb;

-- 会话二
set session transaction isolation level read committed;

-- 开启事务
start transaction ;

-- 更新字段
update goods_innodb set name = '小米' where id = 2;

-- 会话一 查询
select * from goods_innodb;

-- 会话二 提交
commit;

-- 会话一 查询
select * from goods_innodb;

可重复读（REPEATABLE READ）：解决不可重复读的问题，但是不能完全解决幻读。这是MySQL默认的事务隔离级别
查看默认的隔离级别
```
select @@global.transaction_isolation;
```

执行测试

-- 会话一
set session transaction isolation level repeatable read;

-- 开启事务
start transaction;

-- 进行查询
select * from goods_innodb;

-- 会话二
set session transaction isolation level repeatable-read;

-- 开启事务
start transaction;

-- 修改数据
update goods_innodb set name = '联通' where id = 2;


-- 会话一
select * from goods_innodb;

-- 会话二
commit;

-- 会话一
select * from goods_innodb;

测试不会出现幻读的情况

-- 会话一
set session transaction isolation level repeatable read;

-- 开启事务
start transaction;

-- 进行查询
select * from goods_innodb;

-- 会话二
set session transaction isolation level repeatable-read;

-- 开启事务
start transaction;

-- 修改数据
update goods_innodb set name = '荣耀' where id = 2;

-- 会话二
commit;

-- 会话一
select * from goods_innodb;

测试会出现幻读的情况

-- 给表中插入一列
alter table goods_innodb add version int(10) null;
-- 会话一
set session transaction isolation level repeatable read;
-- 开启事务
start transaction;
-- 进行查询
select * from goods_innodb where version = 1;


-- 会话二
set session transaction isolation level repeatable read;

-- 开启事务
start transaction;

-- 插入数据
insert into goods_innodb(name,version) values('金立',1);

-- 会话二
commit;

-- 会话一 进行修改
 update goods_innodb set name = '嘻哈' where version = 1;
-- 查询，可以查询到结果出现了幻读
select * from goods_innodb where version = 1 ;

可串行化（SERIALIZABLE）：这是最高的隔离级别，可以解决上面提到的所有问题，因为它强制将所有的操作串行执行，这会导致并发性能极速下降，因此也不是很常用。

3. 并发事务访问情况说明

并发事务访问相同记录的情况可以分为三种：读-读、读-写（或写-读）、写-写

3.1 读-读

读-读情况，即并发事务相继读取同一个记录的情况，读取操作本身不会对记录造成任何影响，所以允许

3.2读-写

读-写或写-读情况，即一个事务读取记录，另一个事务对记录进行写操作，这样容易出现脏读、不可重复读、幻读问题。

3.3 写-写

写-写情况，即并发事务相继对同一条记录进行写操作，这种情况很容易出现脏写（丢失修改）问题。

3.4 加锁过程说明

如图所示，看作成一条记录
当一个事务想对记录进行写操作时，会查看内存中是否有与该记录关联的锁结构，如果没有则会生成一个锁结构与该记录关联，如果有则也会生成一个锁结构与记录进行关联，然后进入等待。比如T1事务想对一条记录进行写操作，就需要生成一个锁结构与该记录关联。
锁结构中有存在着很多信息，这里对两个比较重要的信息举例
trx：代表这个锁结构是哪一个事务生成的
is_waiting：表示当前事务是否在等待
当事务T1改动了这条记录之后，就生成了一个锁结构与该条记录关联，因为之前没有别的事务为这条记录加锁，所以is_waiting属性就是false，我们把这个场景就称之为获取锁成功，或者加锁成功。然后就可以进行写操作了。
在事务T1提交之前，另外一个事务T2也想对该记录做更改，那么先看看有没有锁结构与该条记录关联，发现有一个锁结构与之关联，然后也生成一个锁结构与这条记录关联，不过所结构的is_waiting属性就是true，表示当前事务需要等待，这个场景就是获取锁失败，或者加锁失败。如图所示：
当事务T1提交之后，就会把该事务生成的锁结构释放掉，然后判断是否还有其他事务在等待锁，发现T2在等待获取锁，然后把事务T2对应的锁结构中is_waiting属性设置成false，再把事务T2对应的线程唤醒，让事务T2继续执行，此时事务T2就可以理解为获取到锁了，如下图所示：

4. 锁的分类

对数据操作的粒度分
表锁：锁的范围是整张表
页面锁：锁的范围是某一页数据
行锁：锁的范围是某一条记录
对数据操作的类型分
读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会相互影响。
写锁（排它锁）：针对同一份数据，多个写操作时是相互排斥的只允许一个写操作进行，在当前写操作还没有结束前，其他写操作需要等待。
想对其他数据而言，MySQL的锁机制比较简单，其显著的特点是不同的存储引擎支持不同的锁机制，下表罗列出了各种存储引擎对锁的支持：

| 存储引擎 | 表级锁 | 行级锁 | 页面锁 |
| ——– | ——– | —— | —— |
| MyISAM | 支持 | 不支持 | 不支持 |
| InnoDB | 支持 | 支持 | 不支持 |
| MEMORY | 支持 | 不支持 | 不支持 |
| BDB | 支持 | 不支持 | 支持 |
MySQl的三种锁特性如下：

| 锁类型 | 特点 |
| —— | ———————————————————— |
| 表级锁 | 偏向MyISAM存储引擎，开销小，加锁快，不会出现死锁，锁的粒度大，发生锁冲突的概率最高，并发度最低低 |
| 行级锁 | 偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁的粒度小，发生锁冲突的概率最低，并发度最高 |
| 页面锁 | 开销和加锁处于表级锁和行级锁之间，会出现死锁，锁的粒度处于表级锁和行级锁之间，并发度一般 |

5. MyISAM表锁

MyISAM存储引擎只支持表锁，也是MySQL开始的几个版本中唯一支持的锁类型

5.1 加锁特点

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

显示加锁的语法

-- 加读锁：
lock table 表名 read;
-- 加读锁：
lock table 表名 write;
-- 释放锁：
unlock tables;

5.2 读写锁案例

5.2.1 读锁案例

数据准备

CREATE TABLE `tb_book` (
`id` INT(11) auto_increment,
`name` VARCHAR(50) DEFAULT NULL,
`publish_time` DATE DEFAULT NULL,
`status` CHAR(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思想','2088-08-08','0');



CREATE TABLE `tb_user` (
`id` INT(11) auto_increment,
`name` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');

客户端一
获取tb_book表的读锁
```
lock table tb_book read;
```
执行查询操作
```
select * from tb_book;
```
正常可以查询出数据
客户端二
执行查询操作
```
select * from tb_book;
```
正常可以查询出数据
客户端一
查询未锁定的tb_user表
```
select * from tb_user;
```
查询失败
客户端二
查询锁定的tb_user表
```
select * from tb_user;
```
查询失败
客户端一：加读锁

插入一条数据

insert into tb_book(name,publish_time,`status`) values('MySQL高级','2088-08-01','1');

插入数据失败
客户端二：不加锁

插入一条数据

insert into tb_book(name,publish_time,`status`) values('MySQL高级','2088-08-01','1');

插入数据成功
- 需要等待客户端一释放锁锁，也就是执行unlock tables指令

5.2.2 写锁案例

客户端一
获取tb_book表的写锁
```
lock table tb_book write;
```
执行查询操作
```
select * from tb_book ;
```
查询成功

执行更新语句

update tb_book set `status` = 1 where id = 2;

更新成功
客户端二

加写锁和读锁

-- 写锁
lock table tb_book write;
-- 读锁
lock table tb_book read;

加锁失败
- 需要等待客户端一释放写锁
执行查询语句
```
select * from tb_book;
```
需要等客户端一释放写锁

5.3 结论

锁模式相互兼容性如下表所示
由上表可知
对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但是会阻塞对同一表的写请求
对MyISAM表的写操作，会阻塞其他用户对比同一表的读和写的请求
由此得出结果，就是读锁会阻塞写锁，不会阻塞读锁，写锁会阻塞读锁和写锁。
此外，MyISAM的读写锁调度是写锁优先，这也就是为什么MyISAM不适合做写为主的表的存储引擎的原因，因为写之后会阻塞其他线程的操作，大量的更新会让查询难以得到锁，从而造成永久阻塞。

6. InnoDB行锁

6.1 加锁特点

InnoDB与MyISAM的最大不同有两点：
支持事务
采用了行级锁
InnoDB实现了以下两种类型的行锁。
共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排它锁（X）：又称为写锁，简称X锁，排它锁就是不能与其他锁并存，如果一个事务获取了一个数据行的排它锁，其他事务就不能再获取该行的其它锁，包括共享锁和排它锁，但是获取排它锁的事务可以对数据进行读取和修改。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据加排他锁（X），对于普通的SELECT语句，InnoDB不会加任何锁。

可以显示的排它锁和共享锁

-- 排他锁
SQL语句 +  lock in share mode;
-- 共享锁
SQL语句 + for update;

6.2准备数据

指令

create table test_innodb_lock(
id int(11),
name varchar(16),
sex varchar(1)
)engine = innodb default charset=utf8;

insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');

create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

6.3 行锁的基本演示

6.4 无索引行锁升级为表锁

如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果和表锁一样。为此一定要注意索引失效问题。
只要是没有使用索引都会使行锁升级为表锁。
查看当前表的索引
指令
```
show index from 表名称；
```
案例演示：
在test_innodb_lock表中name字段是carchar类型，在语句中使用类型转换让索引失效，然后再使用没有索引的字段进行演示

| 会话一 | 会话二 |
| :———————————————————-: | :———————————————————-: |
| 关闭事务的自动提交 | 关闭事务的自动提交 |
| 更新name为888的sex为1使用类型转换 | 更新id为4的name为BB，发现被阻塞了 |
| 提交事务 | 更新成功 |
| 更新sex为0的name为AA | 更新sex为1的name为888，发现被阻塞了 |
| 提交事务 | 更新成功 |

6.5间隙锁

当使用范围条件，而不使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁，对于键值在条件范围但并不存在的记录，叫做间隙（GAP），InnoDB也会对这个间隙加锁，这种锁机制就是所谓的间隙锁（Next-key锁）。
也就是在使用范围条件检索数据，并且请求共享或排他锁，如果此时使用排他锁进行更新数据，而更新的数据正好处于范围条件中的话，就会被阻塞。这种情况称之为间隙锁。
案例演示

| 会话一 | 会话二 |
| :———————————————————-: | :———————————————————-: |
| 关闭事务的自动提交 | 关闭事务的自动提交 |
| 更新id小于4的name为888 | 插入id为2的数据，发现被阻塞，因为此时id为2正好处于范围之中 |
| 提交事务 | 数据插入成功 |
| 关闭事务的自动提交 | |
| 更新id小于4的name为999 | 查询id小于3的全部数据 |

6.6 InnoDB行锁争用情况

查看行锁争用情况指令
```
show status like 'innodb_row_lock%';
```
说明
Innodb_row_lock_current_waits：当前正在等待锁定的数量
Innodb_row_lock_time：从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg：每次等待所花平均时长
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits：系统启动后到现在总共等待的次数
当等待的次数很高，而且每次等待的时长也不小的时候，就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

6.7 总结

InnoDB存储引擎由于实现了行级锁定，整体并发处理能力方面要远远高于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。但是，InnoDB的行级锁同样有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现变差，也就是性能降低。
优化建议：
尽可能让所有数据的检索都能通过索引来完成，这样是为了避免无索引将行锁升级为表锁（注意：对于没有建立索引和索引失效都可以理解为无索引）
合理设计索引，尽量缩小锁的范围（避免间隙锁）
尽量控制事务大小，减少锁定资源量和时间长短（以防造成过多事务的阻塞）
尽可能使用低级别事务隔离（但是需要业务层满足需求，而且一般不会修改MySQL的事务隔离级别）

九、MVCC

1. MVCC概述

MVCC是一种无锁实现并发控制的解决方案。
MVCC（Multiversion Concurrency control）多版本并发控制，即MVCC通过对数据行的多版管理来实现数据库的并发控制。
数据库中主要有两种操作分别是读和写，在并发环境中可能会出现【读-读、读-写、写-写】
而在并发环境中读读是不会出现什么问题的，因为读锁是共享锁。
而写写在并发环境中则是通过加锁的方式来实现的，因为写锁是排它锁。
而读写在并发环境中则是通过MVCC机制来实现

2. 快照和当前读

MVCC在MySQL中的InnoDB存储引擎的实现主要是为了提高数据库的提高数据库的并发性能，使用更好的方式去解决读写冲突，在读写并发时也能做到不加锁实现非阻塞并发读，而这个读指的是快照读，而非当前读。
当前读采用的是加锁的方式，属于悲观锁
而MVCC的本质是采用乐观锁的方式

2.1 快照读

快照读又称一致性读，读取的是快照数据，不加锁的简单的select都属于快照读也就是不加锁的非阻塞读，如下所示：
```
select * from 表名 where 条件;
```
快照读是基于提高并发性能考虑，快照读的实现是基于MVCC，是为了避免加锁操作降低开销，因为快照读是基于多版本的，所以读取的数据不一定是最新的数据版本，有可能是之前的历史版本。

2.2 当前读

与快照读对应的是当前读，读取的数据最新的版本（即最新的数据而非历史数据），读取时还需要保证数据不会被其它并发事务修改所以需要对读取的记录进行加锁，加锁的select、对数据的增删改都会进行当前读，例如;
```
-- 显示加锁 读锁
select 字段 from 表名 lock in share mode;
-- 显示加锁 写锁
insert into 表名(字段) values(字段数) for update;
delete  from 表名 where 条件 for update;
update 表名 set 字段及修改数据 where 条件 for update;
```

3. 隐藏字段和undo log版本链

3.1 隐藏字段

对于使用InnoDB存储引擎来说，其聚簇索引记录中都包含两个必要的隐藏字段。
trx_id：每次事务对某条聚簇索引记录做出改动时，都会将该事务的事务id赋值给trx_id。
roll_pointer：每次事务对某条聚簇索引记录做出改动时，都会将其之前的数据写入到undo log中，然后将roll_pointer指向这条写入undo log的历史记录。
举例说明
student数据表如下：
假设插入记录的事务id为8，那么此时该数据在聚簇索引中的记录为：

3.2 undo log 版本链

假设事务id=100的事务A插入一条数据（id = 1，username = '张三'，age =18 ），那么这两个隐藏字段的值为trx_id=100，roll_pointer指向一个存有主键列信息的undo log，为什么是这样呢?因为id= 1的记录并没有被事务操作过。
然后事务id=200的事务B修改了age，将age从18改成了20，此时隐藏字段的值为trx_id=200，roll_pointer指向新创建的undo log也就是事务A生成的记录
最后事务id=300的事务C又修改了age，将age修改成了30，此时隐藏字段的值为trx_id=300，roll_pointer指向新创建的undo log也就是事务B生成的记录
可以看到每次修改记录，trx_id和roll_pointer两个隐藏字段都会进行更新，之前生成的undo log日志都会通过roll_pointer指针串联起来形成一个版本链。

4. MVCC之ReadView

MVCC的实现依赖于：隐藏字段、undo log版本链、ReadView

4.1 什么是ReadView

在MVCC机制中，多个事务对同一条记录进行修改会产生很多的历史版，这些历史版本保存在undo log中称之为版本链，对于一个事务要读取什么版本，这是通过ReadView来实现的，它解决行的可见性问题。
ReadView就是一个特定的事务在使用MVCC机制进行快照读时产生的读视图，当启动事务时，会生成数据库系统的当前快照。

4.2 设计思路

ReadView主要解决可见性的问题，即通过ReadView来告诉此次事务该访问哪个快照，MVCC主要是针对隔离级别是：read commited、repeatable read。
read uncommited隔离级别的事务是读未提交，只要每次读取的是最新的数据即可。
serializable隔离级别的事务是串行化，InnoDB是通过加锁来访问数据的。
read commited和repeatable read的隔离级别的事务都是要求读取的是已提交的修改记录。（即无法读取其它事务没有提交的修改记录，需要读取历史版本）。

4.3 ReadView的工作原理

案例演示，通过举例来演示Read View是如何判断当前事务查看哪个版本的，这里以可重复读为例。
假设表中已经被事务id = 100的事务A插入了一条记录，（id = 1,name = '张三',age=18），如图所示：
此时，有两个事务并发执行一个是事务id=200的事务B，一个是事务id=300的事务C，事务B是执行select查询记录，事务C是执行update更新id=1的记录，它们都执行了相应的操作但是都没有提交事务。
- 如果此时事务B开启了一个ReadView，ReadView包含以下信息
- m_id：表示当前活跃事务的id。现在包含了事务B和事务C的id，分别为[200,300]
- min_trx_id：表示m_id中最小的事务id。当前就是200事务id最小
- max_trx_id：表示下一个事务分配的id。下一个事务的id可能是301
- creator_trx_id：表示创建当前ReadView事务的id。此时为事务B创建的ReadView，creator_trx_id的值为200
现在事务B进行第一次查询，通过记录中的隐藏字段trx_id与ReadView中的min_trx_id进行判断，此时发现查询的记录中的隐藏字段中的trx_id（100）小于min_trx_id（200）。
```
trx_id < ReadView.min_trx_id
```
通过判断得知此时的事务B是在事务A之后的开启的事务，而且事务A已经提交了事务。此时事务B则可以查询到事务A修改的记录。
接着事务C对id=1的记录做出了修改，将age从18修改成了20，此时表中记录的隐藏字段trx_id的值则成了300，同时roll_poniter指向了新生成的undo log，也就是事务C的修改之前生成的undo log。
事务B再次进行了查询，此时再次判断表中记录的隐藏字段trx_id的值为300，该id存在m_id中，说明这个事务还没有进行提交，还处在活跃的状态，因此事务B查询不到age=20的修改记录
```
trx_id in ReadView.m_id[200,300]
```
- 此时因为age=20对于事务B是不见的，所以只能通过undo log链去查询自己可见的修改记录。
- 此时事务B则会顺这roll_poniter指针往下找，判断最近的undo log的trx_id，判断发现该trx_id（100）小于当前事务B的ReadView中的min_trx_id(200)，即说明这个修改记录在事务B开启创建之前就已经存在了，并且提交了事务，所以可以查询undo log对应的修改记录。
- 通过上述得出的结论为：通过undo log版本链和ReadView机制可以保证一条修改记录在事务没有提交之前，不会读取到其它并事务的更新（repeatable read）。
假设事务C提交了事务，事务B执行了修改，把age=20修改成了66。（注意：此时为什么age是20不是18呢？因为修改是修改的最新的记录，而查询时所查的是查询自己可见的版本，而修改的话则是修改的最新的记录）
此时事务B再次进行查询，与表中记录的隐藏字段trx_id进行判断，发现事务B的ReaView中的m_id中包含了trx_id，然后再与min_trx_id判断发现不大于trx_id，最后再与creator_trx_id判断发现是事务B本身，则可以查询到该记录。
```
trx_id in ReadView.m_id
trx_id 不小于 min_trx_id
trx_id == creator_trx_id
```
事务B在执行期间，又有一个事务id=500的事务D修改了age为88，并提交了，此时事务B对新的修改是不可见的
若此时事务B又去查询，要与表中记录的隐藏字段trx_id进行判断，首先判断是否存在ReadView.m_id中，然后再判断是否小于min_trx_id，再然后判断是否小于max_trx_id，发现大于max_trx_id，则发现该事务是最新开启的事务做出的修改，对事务B是不可见的。
```
trx_id in ReadView.m_id
trx_id 不小于 min_trx_id
trx_id 不小于 max_trx_id
```
- 通过上述测试：通过undo log版本链和ReadView机制，可以保证一个事务只能读到自己修改的数据和该事务开启前的数据。

4.4 MVCC的整体操作流程

当一个事务查询一条数据时，是如何通过MVCC来扎到该数据
首先获取事务本身的事务id
获取ReadView
然后就是再索引中查询到数据，然后就是与ReadView中的事务版本号进行比较
如果不符合ReadView规则，则需要从undo log中获取历史快照。
最后返回符合规则的数据，在隔离级别为读已提交（read commited）时，一个事务每次select都会重新获取一个新的ReadView
注意：此时就算是同样的查询语句都会重新获取一次ReadView，如果此时ReadView不同则会出现不可重读或者幻读的情况。
当隔离级别为可重复读（repeatable read）时，实现了可重复读，是因为在第一次select的时会获取ReadView，而后面的select都是复用的第一次的ReadView

4.5 ReadView的判断流程

如果索引当前行的trx_id小于事务中的ReadView中的min_trx_id，则说明修改该记录的事务在当前事务之前创建并且已经提交，说明该记录可见。
如果索引当前行的trx_id大于或等于事务中的ReadView中的max_trx_id，则说明修改该记录的事务在当前事务之后创建，说明该记录不可见。
如果索引当前行的trx_id，大于等于min_trx_id，小于max_trx_id时，说明修改该记录的事务与当前事务是并发的事务，有以下几种情况：
如果索引当前行的trx_id等于creator_trx_id说明是当前事务，说明该修改记录可见。
如果索引当前行的trx_id在m_id列表中，说明修改该记录的事务与当前事务并发，还没进行事务提交，说明该记录修改不可见。
如果索引当前行的trx_id不在m_id列表中，说明修改该记录的事务在当前事务创建前就已经提交，说明该记录修改可见。

Share

MySQL高级

MySQL高级

一、在Linux系统中安装MySQL服务

1. 安装MySQL镜像服务

2. 远程连接

3. 字符集

3.1 默认字符集

3.2 utf8与utf8mb4

4. SQL大小写规范

4.1 Windows

4.2 Linux

4.3 Linu下设置大小写规则

5. 严格模式和宽松模式

5.1 宽松模式（Loose Mode）：

5.2 严格模式（Strict Mode）：

5.3 查看模式和设置模式，即sql_mode

5.4 错误案例演示

5.5 sql_mode常用值（了解）

二、逻辑架构

1. 逻辑架构图

2. 客户端

3. 连接层

4. SQL接口

5.解析器

6.查询优化器

7. 缓存

8. 存储引擎

9. 系统管理和控制工具

10. 存储层（系统文件和服务器文件以及日志）

11. 查询流程

三、存储引擎

1. 查看存储引擎

2. 查看默认的存储引擎

3. 设置存储引擎

3.1 设置临时存储引擎

3.2 设置永久存储引擎

3.3 给不同的表设置不同的存储引擎

4. MyISAM和InnoDB的区别

四、MySQL索引

1. 什么时索引

2. MySQL中的索引结构：B+树

3.聚簇索引

3.1 特点

3.2 优点

3.3 缺点

3.4 限制

4.非聚簇索引

4.1 非聚簇索引和聚簇索引有什么不同：

5.联合索引

6.覆盖索引

7. MyISAM中的索引

8. MyISAM与InnoDB对比

10. 索引操作

10.1 创建索引

10.2 索引的分类

10.3 索引的优缺点

五、索引优化

1. 数据库优化方案

2. 性能优化

2.1 explain是什么

2.2 准备测试数据

2.3 相关字段的详细解释

2.3.1 table

2.3.2 id

2.3.3 select_type

2.3.4 type

2.3.5 possible_keys和key

2.3.6 key_len

2.3.7 ref

2.3.8 rows和filtered

2.3.9 extra

3. 数据准备

3.1 创建表

3.2 创建函数

3.3 创建存储过程

3.4 调用存储过程

3.5 开启SQL执行时间显示

4. 单表索引失效案例

4.1 计算、函数会导致索引失效