MySql（二）MySql事务与锁
一、事务
1.引言
什么是事务？
在数据库中，事务（Transaction）是指一系列数据库操作序列，这些操作要么全部执行成功，要么全部执行失败回滚，是保证数据库一致性、完整性和隔离性的基本单位。
为什么要有事务？
事务是为了保证数据的一致性、可靠性和隔离性而引入的概念。
在一个复杂的业务逻辑中，可能包含多个操作，如果这些操作不能保证同时成功或同时失败，就会导致数据的不一致性和不可靠性，甚至会损害数据的完整性。而通过引入事务机制，可以将多个操作视为一个整体，要么全部成功，要么全部失败，保证了数据的一致性和可靠性。
经典的转账问题，扣款成功了，但是另一个账户因为宕机没转成功，怎么办？
事务适用场景有哪些？
- 要求数据的完整性和一致性的场景，例如银行转账、股票交易等，这些操作都需要在一个事务内完成，保证数据的完整性和一致性。
- 高并发场景，例如电商网站的订单处理，会有大量的订单并发写入数据库，使用事务可以保证并发操作的正确性和效率。
- 多表操作场景，例如多表关联查询、插入、更新和删除操作，这些操作涉及到多个表的数据，使用事务可以保证数据的一致性，避免出现脏数据。
2.哪些存储引擎支持事务？
在MySql中，除了做集群的NDB之外，只有InnoDB支持事务，这也是为什么它能成为默认的存储引擎
3.事务特性
- 原子性（Atomicity）：事务是一个原子操作，要么全部执行成功，要么全部失败回滚
- 使用undo log记录事务开始前的状态，如果事务失败则根据日志回滚
- 一致性（Consistency）：事务执行前后，数据从一个合法状态变为另一个合法状态，不会破坏数据库的完整性和一致性约束
- 其他三个特性最终就是要完成一致性
- 隔离性（Isolation）：多个事务之间相互隔离，每个事务都应该感知不到其他事务对数据库的修改。防止多个事务并发执行时发生脏读、不可重复读、幻读等问题
- 加锁
- 持久性（Durability）：事务执行成功后，对数据库所作的修改将永久保存在数据库中，即使发生系统故障也不会丢失
- 事务提交成功后的修改是永久性的，不会因为掉电、宕机、意外重启等变成原来状态
考虑下之前说过的，InnoDB崩溃恢复是通过什么手段来实现的呢？
InnoDB的写是先写入到缓冲池Buffer Pool之中的，这个时候出现异常依赖redo log恢复
而刷盘过程中出现异常，则主要依赖双写缓冲来恢复现场
4.数据库事务出现时机
在使用MyBatis的时候，一般是通过在方法上增加注解@Transaction来标明事务，那么其他情况下MySql就不执行事务了吗？Mysql的事务究竟是什么条件下能够触发呢？
4.1 自动触发
在MySql中，默认情况下，单独的增删改Sql语句都会触发事务，并且自动提交落到磁盘
需要注意的是，在InnoDB中，每个事务都有专属于自己的编号，这个编号是递增的
4.2 手动触发
那如果想要将多条语句放到一个事务里面执行，要怎么开启呢？
在MySql里面，手动开启事务有两种方式
- begin
- start transaction
那开启后如何结束呢？
- rollback：回滚
- commit：正常提交
- 客户端连接断开
事务的自动提交可以根据参数设置，比如mybatis在实现事务时，就将事务的自动提交给关闭了
5.事务并发
在java中，多线程并发可能会导致一些预料之外的事情出现，那么在MySql中，事务并发会出现什么问题呢？
5.1 脏读
假设在一个银行系统中，用户A账户余额为1000元，同时有两个事务T1和T2。
事务T1: A账户余额减去100元，余额变为900元。
事务T2: A账户余额增加200元，余额变为1200元。
假设T1先执行，然后在T1执行的过程中，T2也开始执行但是还未提交。此时，如果有一个查询操作读取了账户A的余额，它将读取到的是未提交的T2的数据，即A账户的余额为1200元。如果T2在后面执行失败或者回滚，A账户的余额应该是原来的1000元，但是因为脏读，查询到的余额为1200元，导致数据不一致
脏读是指在数据库中，一个事务读取到了另一个事务未提交的数据。如果该事务后续回滚，那么读取到的数据就是无效的，称为“脏数据”
脏读是一种可能会导致数据不一致性的问题，因为读取到的数据实际上是未经过确认的数据
5.2 不可重复读
假设有一个学生表，包含学生姓名和年龄字段。事务T1想要查询该表中年龄在20岁以下的所有学生，并将他们的年龄加1。同时，另一个事务T2在T1查询数据期间将一个20岁以下的学生的年龄从19岁修改为21岁。
- T1查询该表中年龄在20岁以下的所有学生，并将他们的年龄加1，得到一个名为A的学生，年龄由19岁变为了20岁。
- 在T1的事务中，再次查询该表中年龄在20岁以下的所有学生，发现A学生已经不符合条件了。
这个例子中，同一个事务内进行了两次查询，但是得到的结果却不一样，因为另一个事务在T1查询期间修改了数据。这就是不可重复读
不可重复读（Non-repeatable read）是指在同一个事务中，多次读取同一条记录，但是得到的结果不一致
这是因为在这个事务中，其他事务可能已经修改或删除了这条记录，导致当前事务读取的结果与之前不同，因此称为不可重复读
5.3 幻读
假设有一个银行账户表，其中有一个字段是账户余额。现在有两个事务同时执行：
事务A：查询账户表中账户余额在1000到2000之间的所有记录。
事务B：插入一条记录，账户余额为1500。
在事务A中，由于B事务插入的数据满足查询条件，所以查询结果中会包含这条新插入的记录，导致出现幻读的现象
幻读指在一个事务执行过程中，由于并发的其他事务新增数据行，导致当前事务查询时出现了与之前不同数量的数据行
那么不可重复读和幻读区别在哪呢？
- 查询条件：不可重复读是读取某一行数据前后结果不一；而幻读是读取某一范围内数据行前后不一致的情况
- 执行操作：不可重复读可能由并发事务更新与删除造成，而幻读是由并发事务插入造成的问题
6.隔离级别
事务隔离级别有很多行业标准，最常见的是SQL92标准，在SQL92标准中，关于数据库隔离级别的定义如下
- 读未提交（read uncommitted）：允许脏读，事务可以读取到其他未提交事务修改的数据
- 读已提交（read committed）：禁止脏读，事务只能读取到已经提交的数据。一个事务开始时，会获取一个可重复读的快照，每次查询都会使用这个快照，因此会出现在一个事务内多次查询到不同的结果的情况
- 可重复读（repeatable read）：禁止脏读和不可重复读，保证事务执行过程中读取到的数据不变，即多次读取同一数据时结果相同。与读已提交不同的是，在一个事务中多次查询同一数据时，每次查询都会使用最初的快照，而不会使用当前已提交的数据
- 串行化（serializable）：禁止脏读、不可重复读和幻读，是最高的隔离级别。在串行化级别下，事务串行执行，确保每个事务的操作都不会被其他事务干扰
InnoDB默认采用RR，即可重复读的隔离级别，在这个隔离级别下，InnoDB已解决了幻读问题，后面详细介绍如何解决的
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交（Read Uncommited） | 可能 | 可能 | 可能 |
| 读已提交（Read Commited） | 不可能 | 可能 | 可能 |
| 可重复读（Repeatable Read） | 不可能 | 不可能 | 对InnoDB不可能 |
| 串行化（Serializable） | 不可能 | 不可能 | 不可能 |
7.隔离实现
如果要解决读一致性的问题，保证一个事务中前后两次读取数据结果一致，实现事务隔离，应该怎么做呢？
这里有两类解决方案，加锁或者快照
7.1 LBCC
很容易想到，如果要保持数据前后一致，那么加锁就行了
在读取数据的时候，锁定要操作的数据，不允许其他数据修改
这种方案称为基于锁的并发控制Lock Based Concurrency Control（LBCC）
但是如果读的时候不允许其他修改，那就是不支持并发的读写操作，而大多数应用都是读多写少的，因此这样会极大影响操作数据的效率
7.2 MVCC
那么加锁不行，还有什么办法呢？
在MySql的InnoDB引擎中，采用快照的策略，在修改数据之前建立一个备份或者叫快照，后面读的话就读这个快照就行了
这个方案被称为多版本并发控制Multi Version Concurrency Control（MVCC）
MVCC原则如下
一个事务能够看到的数据版本：
- 第一次查询之前已经提交的事务的修改
- 本事务的修改
一个事务不能够看见的数据版本：
- 在本事务第一次查询之后创建的事务（即事务ID比自己的事务ID大）
- 活跃的（未提交的）事务的修改
MVCC效果如下
一个事务，可以查到在自己开始之前就已经存在的数据，即使后面这个数据被删除或者修改了——因为查的是比自己早的快照
而在这个事务之后新增的数据，该事务无法查到——还是因为查的都是比自己早的快照
注意是第一次查询之后才创建了本事务的ID
7.3 MVCC快照
上面提到了MVCC快照，那么这个快照是如何实现的呢？
| id | name | DB_TRX_ID | DB_ROLL_PTR |
|---|---|---|---|
| 1 | zhangsan | 01 | NULL |
InnoDB为每个事务都分配了一个递增的编号，此外，它为每行记录都实现了两个隐藏字段
DB_TRX_ID：6字节，表示事务ID
数据是在哪个事务插入或者修改为新数据的，就记录为当前事务ID
DB_ROLL_PTR：7字节，回滚指针
数据被删除或记录为旧数据时，记录操作的事务ID，没这个操作就是NULL
由于MVCC保证一个事务只能读取比自己小的事务ID所标记的数据行，因此可以保证在同一个事务过程中，读取到的数据保持一致
但是如果数据被修改，事务ID变化，那当前事务如何保证能够读到这个数据修改之前的记录呢？
答案是undo log
在undo log之中，存放有数据的旧版本信息，同一条数据多次修改会形成一条数据链
而DB_ROLL_PTR就是指向undo log的指针
合理猜想，应该有一个数据结构，能够把当前事务ID，活跃事务ID，当前系统最大事务ID存储起来，这样才能进行判断
这个数据结构确实存在，被称为Read View（可视性视图），每个事务都会维护一个自己的Read View
| m_ids{} | min_trx_id | max_trx_id | creator_trx_id |
|---|---|---|---|
| 列表，当前系统活跃的事务id | m_ids的最小值 | 系统分配给下一个事务的id | 生成read view事务的事务id |
依靠这个数据结构，事务判断的可见性规则就如下所示：
- 从数据的最早版本开始判断（依靠undo log）
- 数据版本的trx_id = creator_trx_id，本事务修改，可改
- 数据版本的trx_id < creator_trx_id（未提交事务的最小ID），说明这个版本在生成RedaView之前已经提交，可以访问
- 数据版本的trx_id > creator_trx_id（下一个事务ID），这个版本是在生成ReadView之后开启事务建立的，不能访问
- 数据版本的trx_id在min_trx_id和max_trx_id之间，看看是否在m_ids之中
- 如果在，不可以—未提交
- 不过不在，可以—已提交
- 如果当前版本不可见，就找undo log链中的下一个版本
需要注意的是，在RR隔离级别下，Read View只会在第一次查询时创建一次
而在RC隔离级别下，每次查询的时候都会创建一个Read View
MVCC保证了一个事务在读取过程中，读取到的快照都是同一个，因此它其实算是在一定程度上解决了脏读，不可重复读与幻读问题，但是并不是完全解决
脏读：可以被MVCC完全解决
不可重复读：不一定
- RC：事务每次查询都会创建快照，因此可能读到其他事务提交修改的数据
- RR：全局只有一个事务第一次查询创建的快照
幻读：不一定
RC：事务每次查询都会创建快照，因此可能读到其他事务提交修改的数据
RR：全局只有一个事务第一次查询创建的快照
不确定，后面再改
二、锁
1.锁的粒度
已知MyISAM仅支持表锁，InnoDB支持行锁，那行锁和表锁表现有什么区别呢？
- 锁定粒度：表锁 > 行锁
- 加锁效率：表锁 > 行锁
- 冲突概率：表锁 > 行锁
- 并发性能：表锁 < 行锁
事实上，在MySql官网把锁分为了8类
- 共享锁/互斥锁—行锁
- 基本模式的锁
- 意向共享锁/意向互斥锁—表锁
- Record Locks—行锁
- 锁的算法
- Gap Locks—间隙锁
- 锁的算法
- next-Key Locks—结合的行锁+间隙锁
- 锁的算法
- 插入意向锁
- 特殊的间隙锁，允许多个事务同时插入数据到同一范围
- 自增锁：特殊的表锁，用来防止自增字段重复，数据插入后就会释放，不需要事务提交
- 0:traditional 每次自增都会产生表锁
- 1:consecutive 会产生一个轻量级锁，simple insert会获得批量的锁，保证连续插入，默认是这个
- Predicate Locks for Spatial Index
- 5.7版本之后新增的一种数据类型的索引锁
2.共享锁
也称为读锁，行级锁，允许多个事务同时读取该行数据
共享锁应用：阻塞其他事务的修改，可用在不允许其他事务修改的情况下
如何加共享锁？
1 | Select ...... Lock in share mode; |
如何释放共享锁？
- 事务结束
- 使用命令显示释放
3.排他锁
也称为写锁，行级锁，只要获取排他锁，就不允许其他事务再获取该行数据的读锁和写锁
排他锁应用：独占数据行，阻塞其他事务
如何加排他锁？
- 操作数据时，增删改默认会加上排他锁
- 手动在后面使用for update加排他锁
4.意向锁
数据库自己维护，包括意向共享锁与意向排他锁
意向锁是由Innodb自动维护的，为了提升加表锁的效率，那为何会出现呢？
思考下，如果加表锁之前，需要确定表中没有行锁，那如何确定？遍历吗？明显不靠谱
因此加行锁时，InnoDB会自动加上对应的意向锁，从而保证想要加表锁时能够快速的判断当前表中有没有行锁
意向共享锁（表锁）：表中加行级别共享锁时加上
意向排他锁（表锁）：表中加行级别排他锁时加上
事务结束自动释放
5.行锁原理
5.1 无索引表的行锁
无索引即只能锁住记录
此时假设InnoDB锁住的是一行数据或者一条记录
设有一张t1表，有两字字段，id和name，有1，2，3，4共4条记录
以下两个事务，先开启事务1
1 | begin; |
事务2
1 | select * from t1 where id = 3 for update; // blocked |
明明只是给id=1加了锁，为什么事务2都会被阻塞呢？
事实上，这里整张表都被锁住了，即InnoDB的行锁，锁住的并不是Record
具体原因后面解释，这里看下一个案例
5.2 有主键索引的表
创建一个t2的表，与t1区别是在主键上加了主键索引
同样操作，事务1开启
1 | begin; |
事务2
1 | select * from t1 where id = 1 for update; // blocked |
可见在这里，使用不同的id加锁是可以加锁成功的
上面已经证实锁住的不是Record，那是不是锁住的id字段呢？
继续看下一案例
5.3 唯一索引
t3相比t2，是在name字段上加了唯一索引
同样操作，事务1开启
1 | begin; |
事务2
1 | select * from t1 where name = '4' for update; // blocked |
这里发现，使用id插入也被阻塞了，所以上面的猜测也是错的，并不是锁住了id这个字段
从上面案例可以看到，锁住的并不是record，也不是column，那么锁住的到底是什么呢？
事实上，InnoDB的行锁，是通过锁住索引来实现的
这里回顾一下上面的几个问题
1）为什么案例1里面，锁住一行数据会导致整张表都被锁住呢？
在索引章节可以知道，如果一张表没有主键，也没有不包含NULL值的唯一索引列，那么就会选择6字节长的ROWID作为隐藏的聚集索引
在这种情况下，id=1加锁，由于没有索引，会导致全表扫描，从而就把每一个隐藏索引都给锁住了
2）案例2中，为什么通过给唯一索引加锁，也能锁住主键索引呢？
因为回表查询，所以使用唯一索引加锁时，锁住的也是主键索引
6.锁的算法
上面介绍的都是基本的锁，本节讨论点不一样的
还是上面的t2表，有主键索引，假如插入了主键为1，4，7，10的记录，那么在
Record Locks，Gap Locks，next-Key Locks三种锁中，锁的范围都是怎样的呢？
这里面的主键都称为Record，所以这里有4个Record
主键之间不存在的区间就称为Gap，是一个左开右开的区间
而加上主键与Gap，构成的左开右闭的范围就称为Next-Key
6.1 记录锁
当使用唯一性的索引来查询数据，精准匹配到一条记录的时候，使用的就是记录锁，比如
1 | select * from t2 where id = 4 for update; |
锁住的就是对应的id=4的索引
6.2 间隙锁
当查询的记录不存在时，没有命中任何一个record，这时候无论是用的等值查询还是范围查询，都是使用的间隙锁
首先回顾下，t2当中只有1，4，7，10这几条数据
1 | select * from t2 where id > 4 and id < 7 for update; // 锁住(4,7) |
需要注意，假如键值最大是10，那如果查询大于10的范围，锁住的范围也是从10开始的
6.3 临键锁
当使用范围查询，不仅命中了Record，还包含了Gap间隙，这时候加的就是临键锁
临键锁是MySql里面默认的行锁算法，相当于记录锁+间隙锁
因此，当使用唯一索引，匹配到的只有一条数据时，退化成记录锁
匹配不到的时候，就退化成间隙锁
还是t2表，如下
1 | select * from t2 where id > 5 and id < 9 for update; |
此时命中了7，所以锁住了(4,7]+7+(7,10]
注意，不会锁一半间隙，所以这里并不是5和9作为边界
这也是为什么InnoDB能够在RR级别下解决幻读问题了，因为它这时候采用的就是临键锁
直接把两边间隙都锁住了，其他事务自然无法插入数据造成幻读了
6.4 隔离级别实现
在这里总结下四个隔离级别的实现
- 读未提交 Read Uncommited：不加锁
- 串行化 Serializable：所有select语句都会转化为共享锁执行，和update、delete互斥
- 读已提交 Read Commited：
- 普通的select使用快照读，MVCC实现，每个事务查询生成一个视图
- 加锁的select使用记录锁
- 会出现幻读，因为没有锁住周边间隙
- 可重复读 Repeatable Read：
- 普通的select使用快照读，MVCC实现，但是全局用一个视图
- 加锁的select以及更新操作使用当前读，底层使用记录锁/间隙锁/临键锁
- 可解决幻读
此外，外键约束检查以及重复键检查会用间隙锁封锁区间
一般来说，使用默认的RR即可，但是也有用RC的，使用RC相比RR有哪些优点呢？
- RR间隙锁会导致锁定范围扩大
- 条件列未用到索引时，RR锁表，RC锁行
- RC的“半一致性”读可以增加update操作的并发性
在RC中，一个update语句，如果读到了一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySql上层判断此版本是否满足update的where条件
若满足，则MySql会重新发起一次读操作，读取行的最新版本(并加锁)
7.死锁
锁具有互斥的特性，在并发编程中要避免死锁的出现，那在MySql中会不会出现死锁的情况呢？
在MySql中也是有死锁现象发生的，一个事务持有锁不释放，等待其他事务锁的释放，构成环就会导致死锁
7.1 锁的释放与阻塞
前面说过，锁的释放有以下两种情况：
- 事务结束：rollback/commit
- 客户端断开连接
那么，如果有一个锁持有锁并一直不释放，会永久等待下去吗？
MySql有参数控制这种情况，默认50s后会释放锁
事实上，MySql不会等待50s再去释放，它一般通过算法（wait-for graph）是能够检测到死锁的发生的
死锁发生条件：
- 同一时刻只能有一个事务持有锁
- 其他事务需要在这个事务释放之后才能获取锁，不可强行剥夺
- 多个事务形成等待环路
因为死锁发生情况虽然复杂，但是都会满足以上三个条件，所以可通过算法检测到
7.2 锁日志
在MySql中，可以通过命令查看锁信息
1 | show status like 'innodb_row_lock_%' |
得到：
- Innodb_row_lock_current_waits：当前正在等待锁的数量
- Innodb_row_lock_time：系统启动到现在锁定的总时间长度，ms
- Innodb_row_lock_time_avg：每次等待所花平均时间
- Innodb_row_lock__time_max：从系统启动到现在等待时机最长所花时间
- Innodb_row_lock_waits：从系统启动到现在总共等待的次数
此外，InnoDB还提供了三张表来分析事务与锁的情况
- 查询当前运行的所有事务，和具体的语句
- 当前出现的锁
- 锁等待的对应关系
7.3 死锁避免
类似并发编程，MySql中也可以采用一些策略避免死锁
- 在程序中，操作多张表时，尽量以相同的顺序访问（避免形成等待环路）
- 批量操作单张表数据的时候，先对数据进行排序（避免形成等待环路）
- 申请足够级别的锁，如果要操作数据，就申请排他锁
- 尽量使用索引访问数据，避免没有where条件的操作，避免锁表
- 如果可以，尽量把大事务化为小事务
- 使用等值查询而不是范围查询，减少间隙锁对并发影响
三、性能优化
性能优化是经常问到的问题，说到数据库性能优化，最常见的思路是什么呢？
或者说慢SQL如何去优化？
数据库性能优化，需要从每个环节下手考虑，每个环节都有可以进行优化的地方
1.连接——配置优化
连接可能有什么性能问题呢？
因为连接数是有上限的，所以达到上限后，新的请求获取不到连接，就会报错
如何优化呢？
从两个方面，分别是服务端与客户端
1.1 服务端
- 增加可用连接数—可通过修改参数max_connextions实现
- 释放不活动的连接—把默认释放时机调小
1.2 客户端
不要每一个请求都新建连接，使用连接池技术
Druid默认连接池大小是8，Hikari是10
Hikari给出的文档说PostgreSQL建议设置：机器核数*2+1
为什么不是连接数越多越好呢？
因为cpu核数有限，而每一个连接都需要一个线程去处理，过多的连接就会产生过多的线程，从而频繁的上下文切换，导致效率降低
2.架构优化
2.1 缓存
很常用的技术，对于一些慢查询，比如涉及的数据量太大，关联的表过多，反正就是导致查询缓慢的操作，如果变更频率不高，就可以使用redis缓存技术加速访问
从数据一致性角度考虑，缓存的数据不能是频繁更新的，此外要设置过期时间，保证数据的最终一致性
这里属于架构层面的优化
2.2 集群——主从复制
这主要是提高系统可用性，保证不会一台机器挂了整个服务都挂了
此外使用读写分离，主写从读也可以提升系统整体效率
那么使用集群就必然要考虑集群间的数据同步了，MySql的主从复制如何实现的呢？
前面说过，MySql的所有更新语句都会使用binLog日志进行记载，从数据库根据master传过来的binlog即可同步数据与master一致
那么binlog是如何同步的呢？
在master上有一个log dump线程，专门用来把binlog发送给slave
而从库的sql线程，也会读取relay log，把数据写入数据库
之后就可以愉快的读写分离了，把写的任务交给master，读的任务交给slave
2.3 分库分表
分库分表也是常用的优化手段，包括垂直分库与水平分库分表
垂直分库：把一个水库按照业务拆分成不同的数据库，即拆列
水平分库分表：把一张数据量很大的大表，按行拆分成很多个小表
3.优化器——SQL语句分析与优化
除了从配置与架构上优化，最常见的就是sql语句的优化了
优化sql语句的前提是找到慢sql，如何找到呢？
3.1 慢查询日志
默认是关闭慢查询日志的，因为保持开启需要一定的代价，可以配置开启
设置超过多久的语句为慢sql语句，默认10s
获得慢查询日志后，可以通过工具mysqldumpslow来分析
还有一些其他系统命令可以监控mysql，此处不再赘述
因为在企业中，一般都有专门的工具与告警来监控慢查询语句
3.2 慢查询优化
通常来说，慢sql优化需要分析该sql的执行计划，如何查看呢？
使用explain关键字，在sql前面加上该关键字即可查看sql的执行计划
在MySql5.6.3之前只能使用explain分析select，在之后就可以分析update、delete和insert命令了
explain会查出来很多列，如下表，那么这些列都代表什么意思呢？下面逐个分析
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | t2 | NULL | ref | fk_t2_t1 | fk_t2_t1 | 5 | test_db.t1.id | 2 | 100.00 | Using index |
①id
id是查询序列编号，一个select就有一个序号，可表示查询顺序
id值不同：先查询id值大的
id值相同：根据结果从上往下执行
②select_type
不同字段值含义不同，下面是常见的值
SIMPLE：表示这是一个简单的SELECT查询，不包含子查询或者联合查询PRIMARY：表示这是一个包含多个表的查询语句中最外层的SELECT查询SUBQUERY：表示这是一个子查询，MySQL 会在查询执行前执行子查询，然后将其结果传递给外层查询DERIVED：表示这是一个子查询，MySQL 会将其结果存储在一个临时表中，然后执行外层查询，查询中使用的子查询都被标记为DERIVEDUNION：表示这是一个联合查询，包含多个SELECT语句，并且使用UNION连接UNION RESULT：表示这是一个联合查询中的结果集
除了上述几个常见的取值之外，select_type 还可能出现一些其他取值，例如 DEPENDENT UNION、UNION DISTINCT、DEPENDENT SUBQUERY 等等，这些取值的含义需要根据具体的查询语句来理解
③type
- system：表只有一行记录（system表），这是const类型的特例，平常不会出现，优化器不会选择这个访问类型
- const：使用常数值（如主键或unique索引等）进行查询。这种情况下MySQL在查询之初就可以确定结果，因此查询只会执行一次，是最优的访问类型
- eq_ref：对于每个索引键，表中只有一条记录与之匹配。常见于使用主键或唯一索引进行等值查询的情况。此类型比ref访问更加优秀，因为索引是唯一的，所以无需执行扫描
- ref：使用非唯一索引或者唯一索引的非前导列进行等值查询，会返回匹配到的所有行。ref查询通常会使用索引进行访问，但可能会执行全表扫描，例如IN操作符中的查询列表中不同的值超过了索引中的键数时
- range：对索引进行范围查询，常见于使用BETWEEN、IN等操作符的查询
- index：Full Index Scan，扫描整个索引，和All访问类型类似，只不过只扫描索引而不扫描数据
- all：Full Table Scan，遍历全表，效率最低，应尽量避免
根据以上取值优化顺序从最优到最差为：system、const、eq_ref、ref、range、index、all
而在实际开发中，system，const，eq_ref都是可遇不可求的，很难优化到这个状态
比较常见的是ref及之后的这些
尽量优化到ref/range，而all和index都是需要被优化的
④possible_key、key
分别表示可能用到的索引与实际用到的索引，如果NULL就是没用到索引
所以如果分析后发现没有用到索引，就要检查SQL或者创建索引
⑤key_len
索引的长度，一般不怎么关注
⑥rows
估算所需扫描的行数
⑦filtered
表示此查询条件所过滤掉的数据百分比
如果比例很低，说明存储引擎返回的数据要经过大量过滤，是比较消耗性能的，需要关注
⑧ref
表示上述索引的哪个列被使用了，常见的取值为表的列名或常量
⑨Extra
Extra是一列用于提供关于查询优化器使用的额外信息的字段。它可以告诉我们很多关于查询执行的细节信息，例如使用了哪些索引、使用了哪些算法、数据来源等等
- Using index: 表示查询使用了覆盖索引，所有需要的数据都可以从索引中获取，而不需要回到数据表中读取行。
- Using temporary: 表示MySQL需要创建临时表来处理查询，例如在GROUP BY或ORDER BY中使用了不在索引列中的列，或者使用了不同的存储引擎等。
- Using filesort: 表示MySQL需要对结果集进行排序，但是无法使用索引完成排序，因此需要创建临时表并进行文件排序。
- Using join buffer: 表示MySQL使用了连接缓存，连接缓存是MySQL用于优化连接查询的一种内存结构。
- Range checked for each record: 表示MySQL在执行查询时使用了索引范围扫描，对于每一行记录都需要进行索引范围判断，这可能会导致性能问题。
- Using where: 表示MySQL使用了WHERE子句过滤数据。
- Using index condition: 表示MySQL使用了索引条件过滤数据，例如使用了WHERE子句中的等值条件；也就是上面提到过的索引下推
- Using sort_union: 表示MySQL使用了多个排序结果集的合并排序，通常是通过UNION或UNION ALL实现的。
- Using intersect: 表示MySQL使用了多个结果集的交集，通常是通过INTERSECT实现的。
- Using index for group-by: 表示MySQL使用了索引来执行GROUP BY操作，这通常可以减少排序操作的成本。
上述字段中，最常见的是是 Using where 和 Using index
Using where：表示 MySQL 需要在存储引擎层面获取更多的数据行，然后在执行过滤操作来返回查询结果，这个操作可能会非常耗时，因此需要优化查询条件，减少使用 where 子句的情况
Using index：表示 MySQL 通过索引直接获取了数据行，而无需进行全表扫描，这通常是一种很好的情况，表示查询性能比较好，不需要过多的优化
而以下字段需要关注，并且尽量优化掉
Using filesort：表示 MySQL 在对结果进行排序时需要使用到一个外部的文件排序，这个操作非常耗时，需要优化排序方式
Using temporary：表示 MySQL 在对结果进行查询时需要使用到一个临时表，也会对查询性能造成影响，需要尽量减少使用
3.3 SQL与索引优化
获得了执行计划之后，如何对sql进行优化呢？
以下是一些常见的sql优化策略
- 索引优化：添加、修改、删除索引来提高查询效率（加索引）
- SQL 重构：使用更优雅、更高效的 SQL 写法来减少数据库的工作量（优化sql）
- 分库分表：对于单表数据量过大的情况，可以将数据分散到多个库或多个表中，减轻单一表的压力（大表拆分）
- 数据库服务器优化：例如增加缓存、优化配置参数等，提高整个数据库服务器的性能
- 应用程序优化：例如使用缓存来减轻数据库的负担，或者尽可能减少数据库的访问次数等
- 小表驱动大表：如果在联接查询时，一个表非常小，而另一个表非常大，可以考虑使用小表驱动大表的方式，即先查询小表，再将结果与大表联接。这样可以减少查询大表的数据量
- 利用分区表：对于需要经常查询的字段，可以将表按照这些字段的值进行分区，从而加快查询速度（分片）
- 数据库优化器调整：例如更改统计信息、更改优化器的启发式算法等，优化 SQL 执行计划
4.存储引擎
4.1 选择
在实际工作中，不同的业务场景需要选择不同的存储引擎来进行工作，下面是一些场景与选择
- 数据库引擎的类型：MySQL有多种类型的数据库引擎，包括InnoDB、MyISAM、Memory等。不同的存储引擎有不同的特点和适用场景，根据不同的应用场景和需求选择合适的存储引擎可以提高数据库的性能和可靠性。
- 数据库的读写比例：如果数据库的读操作比较多，可以选择InnoDB等支持行级锁定和事务的存储引擎；如果数据库的写操作比较多，可以选择MyISAM等支持表级锁定和非事务的存储引擎。
- 数据库的并发连接数：如果并发连接数较高，可以选择支持多线程操作和缓存技术的存储引擎，比如InnoDB等。
- 数据库的数据大小：如果数据库的数据量较小，可以选择Memory等内存型存储引擎；如果数据库的数据量较大，可以选择支持分区和压缩技术的存储引擎，比如InnoDB等。
- 数据库的可靠性要求：如果数据库的可靠性要求较高，可以选择支持ACID事务和崩溃恢复技术的存储引擎，比如InnoDB等。
- 数据库的查询类型：如果数据库的查询类型是常规的数据检索和聚合查询，可以选择InnoDB等支持行级锁定和索引技术的存储引擎；如果数据库的查询类型是大量的全文检索和复杂的空间查询，可以选择支持全文索引和空间索引的存储引擎，比如MyISAM等。
- 数据库的数据访问模式：如果数据库的数据访问模式是以主键为主的随机访问，可以选择支持哈希索引和B+树索引的存储引擎，比如Memory等；如果数据库的数据访问模式是基于范围的扫描访问，可以选择支持B+树索引和全文索引的存储引擎，比如InnoDB等
4.2 分表或者分区
分区或者分表是一种常见的数据库水平拆分技术，它将一个大表按照一定规则拆分成多个小表或分区，每个小表或分区之间互相独立，可以分散在不同的物理存储设备上，从而提高数据库的扩展性和性能
- 应对海量数据：当单表数据量很大时，查询性能会变慢，甚至会超出单机存储容量限制，此时可以通过分区或分表将数据拆分到多个物理存储设备上，从而提高查询性能和存储容量。
- 提高查询性能：通过将大表拆分成多个小表或分区，查询时只需在有限的小表或分区中进行，可以减少扫描的数据量，从而提高查询性能。
- 提高并发性能：当多个用户同时访问同一个表时，可能会出现锁竞争和阻塞等问题，而分区或分表可以将数据分散到不同的物理存储设备上，从而减少锁竞争和阻塞等问题，提高并发性能。
- 易于维护：当表的结构比较复杂或者数据量很大时，对其进行维护和管理比较困难，而通过分区或分表可以将表结构和数据分散到多个小表或分区中，从而方便维护和管理。
4.3 字段定义
原则：使用可以正确存储数据的最小数据类型
这样可以减小空间消耗
4.4 整数类型
在MySql中，INT有6种类型，不同类型的最大储值范围不一样，占用存储空间也不一样，要合理选择
4.5 字符类型
变长情况下选择varchar
固定长度用char
4.6 非空
非空的字段尽量定义成NOT NULL，别忘了提供默认值
4.7 不要用外键、触发器、视图
降低可读性
4.8 大文件存储
不要把图片，视频等资源放到数据库中存储，放到NAS上，数据库存储URL即可
4.9 表拆分或者字段冗余
不常用字段拆分出去
为了方便，可以对一些字段冗余存储，防止连表查询
5.优化总结
在实际工作中，不仅要能够从技术角度对系统进行优化，还要能够从业务角度进行考虑
5.1 支付方式
比如双11，为什么提倡使用花呗或者余额宝支付呢？因为调用自己的接口肯定更快
5.2 降级措施
为什么双十一那几天，会停掉一些平常不太用的业务？
为了把资源都交给最核心的业务
5.3 分散流量
为什么双11周期变长了？一方面考虑就是把流量进行了分散
此外，银行的交易记录只允许按照月份查询也是减少流量的方式
此外，还有限流，削峰等诸多措施来保证系统的并发性能
6.优化步骤
对于具体的慢sql，如何解决呢？下面是一些步骤
①分析查询基本情况
涉及到表的结构，字段的索引，表的数据量，业务含义等，分析后也许这个SQL根本不需要这样写呢？
②找出原因
根据执行计划，分析SQL慢的具体原因
③对症下药
找到原因之后，进行问题解决，比如
- 创建索引/联合索引
- 改写sql
- 小表驱动大表
- 使用join代替子查询
- not exist转换为left join is null
- or改为union
- 允许重复的话，使用union all代替union
- 大偏移的limit，先过滤再排序
- 结构优化
- 表结构优化：冗余、拆分、not null等
- 架构优化：缓存、读写分离、分库分表
- 业务优化
- 技术手段行不通，可以考虑在业务层面进行优化
