Sql请求的过程

image.png

优化器：你以为你的sql就是你的sql？

事务

原子性

一致性

持久性

隔离性

隔离性

WHY

脏读

读到了并一定最终存在的数据，这就是脏读

原因
更新在查询之后，比如：其他事务回滚导致

如：
1.事务A改变了订单表的某条数据的订单状态为“已发货”
2.事务B获取该条数据的订单状态为“已发货”
3.事务A发生错误了，进行了回滚，订单状态由“已发货”改为了“待发货”
4.事务B拿着错误的“已发货”状态，去进行后续操作

不可重复读

原因
因为其他事务的提交导致的

对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。
如：

1. 事务A先将订单状态由“待发货” 改为 “已发货”，但事务A暂未提交
2. 事务A内再次 读到订单状态，为“已发货”

可重复读

可重复读并不是什么缺点，而是不可重复读的改进
是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。
如：

1. 事务A先将订单状态由“待发货” 改为 “已发货”，但事务A暂未提交
2. 事务A内再次 读到订单状态，为“待发货”

幻读

原因
因为其他事务在本事务之后开始，又在本事务结束之前结束

1.事务A将订单状态由“待发货” 改为 “已发货”
2.事务B将订单状态改为“待发货”
3.事务B提交
4.事务A还未提交之前，检查订单状态，发现还是“待发货”，像是出现了幻觉

隔离级别

隔离级别

image.png

读未提交（READ UNCOMMIT）

读未提交，其实就是可以读到其他事务未提交的数据，但没有办法保证你读到的数据最终一定是提交后的数据，如果中间发生回滚，那就会出现脏数据问题，读未提交没办法解决脏数据问题。更别提可重复读和幻读了，想都不要想。
如：
启动两个事务，分别为事务A和事务B，在事务A中使用 update 语句，修改 age 的值为10，初始是1 ，在执行完 update 语句之后，在事务B中查询 user 表，会看到 age 的值已经是 10 了，这时候事务A还没有提交，而此时事务B有可能拿着已经修改过的 age=10 去进行其他操作了。在事务B进行操作的过程中，很有可能事务A由于某些原因，进行了事务回滚操作，那其实事务B得到的就是脏数据了，拿着脏数据去进行其他的计算，那结果肯定也是有问题的。

读未提交是性能最好，也可以说它是最野蛮的方式，因为它压根儿就不加锁，所以根本谈不上什么隔离效果，可以理解为没有隔离

读提交(READ COMMIT)

读提交每次执行语句的时候都要重新创建一次快照

同样开启事务A和事务B两个事务，在事务A中使用 update 语句将 id=1 的记录行 age 字段改为 10。此时，在事务B中使用 select 语句进行查询，我们发现在事务A提交之前，事务B中查询到的记录 age 一直是1，直到事务A提交，此时在事务B中 select 查询，发现 age 的值已经是 10 了。

这就出现了一个问题，在同一事务中(本例中的事务B)，事务的不同时刻同样的查询条件，查询出来的记录内容是不一样的，事务A的提交影响了事务B的查询结果，这就是不可重复读，也就是读提交隔离级别。
每个 select 语句都有自己的一份快照，而不是一个事务一份，所以在不同的时刻，查询出来的数据可能是不一致的。

读提交解决了脏读的问题，但是无法做到可重复读，也没办法解决幻读

可重复读（（REAPEAT READ）默认）

仅仅在事务开始是创建一次快照

可重复是对比不可重复而言的，上面说不可重复读是指同一事物不同时刻读到的数据值可能不一致。而可重复读是指，事务不会读到其他事务对已有数据的修改，及时其他事务已提交，也就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。但是，对于其他事务新插入的数据是可以读到的，这也就引发了幻读问题

MySQL 的可重复读隔离级别其实解决了幻读问题

串行化（SERIALIZE）

串行化是4种事务隔离级别中隔离效果最好的，解决了脏读、可重复读、幻读的问题，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束。

• 读的时候加共享锁，也就是其他事务可以并发读，但是不能写
• 写的时候加排它锁，其他事务不能并发写也不能并发读

如何实现事务隔离

实现可重复读（MVVC多版本并发控制）

我们在数据库表中看到的一行记录可能实际上有多个版本，每个版本的记录除了有数据本身外，还要有一个表示版本的字段，记为 row trx_id，而这个字段就是使其产生的事务的 id，事务 ID 记为 transaction id，它在事务开始的时候向事务系统申请，按时间先后顺序递增

image.png

在上面介绍读提交和可重复读的时候都提到了一个词，叫做快照，学名叫做一致性视图，这也是可重复读和不可重复读的关键，可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读提交则是每次执行语句的时候都重新生成一次快照。

对于一个快照来说，它能够读到那些版本数据，要遵循以下规则：

1. 当前事务内的更新，可以读到；
2. 版本未提交，不能读到；
3. 版本已提交，但是却在快照创建后提交的，不能读到；
4. 版本已提交，且是在快照创建前提交的，可以读到***

利用上面的规则，再返回去套用到读提交和可重复读的那两张图上就很清晰了。还是要强调，两者主要的区别就是在快照的创建上，可重复读仅在事务开始是创建一次，而读提交每次执行语句的时候都要重新创建一次

并发写问题

存在这的情况，两个事务，对同一条数据做修改。最后结果应该是哪个事务的结果呢，肯定要是时间靠后的那个对不对。并且更新之前要先读数据，这里所说的读和上面说到的读不一样，更新之前的读叫做“当前读”，总是当前版本的数据，也就是多版本中最新一次提交的那版。

假设事务A执行 update 操作， update 的时候要对所修改的行加行锁，这个行锁会在提交之后才释放。而在事务A提交之前，事务B也想 update 这行数据，于是申请行锁，但是由于已经被事务A占有，事务B是申请不到的，此时，事务B就会一直处于等待状态，直到事务A提交，事务B才能继续执行，如果事务A的时间太长，那么事务B很有可能出现超时异常。如下图所示。

加锁
mysql写是默认加锁的
如果where中的字段有索引，直接加锁；
如果where中没有索引，先把所有数据都加锁，然后再把不满足条件的数据的锁去掉，性能不好。

解决幻读问题

并发写问题的解决方式就是行锁，而解决幻读用的也是锁，叫做间隙锁，
MySQL 把行锁和间隙锁合并在一起，解决了并发写和幻读的问题，这个锁叫做 Next-Key锁

假设现在表中有两条记录，并且 age 字段已经添加了索引，两条记录 age 的值分别为 10 和 30

image.png

此时，在数据库中会为索引维护一套B+树，用来快速定位行记录。B+索引树是有序的，所以会把这张表的索引分割成几个区间。

image.png

之后，我用下面的两个事务演示一下加锁过程

image.png

在事务A提交之前，事务B的插入操作只能等待，这就是间隙锁起得作用。当事务A执行update user set name='风筝2号’ where age = 10; 的时候，由于条件 where age = 10 ，数据库不仅在 age =10 的行上添加了行锁，而且在这条记录的两边，也就是(负无穷,10]、(10,30]这两个区间加了间隙锁，从而导致事务B插入操作无法完成，只能等待事务A提交。不仅插入 age = 10 的记录需要等待事务A提交，age<10、10<age<30 的记录页无法完成，而大于等于30的记录则不受影响，这足以解决幻读问题了。

• 如果where中的字段有索引，会加间隙锁；
• 如果where中没有索引，数据库会为整个表加上间隙锁。所以，如果是没有索引的话，不管 age 是否大于等于30，都要等待事务A提交才可以成功插入。

总结

MySQL 的 InnoDB 引擎才支持事务，其中可重复读是默认的隔离级别。

读未提交和串行化基本上是不需要考虑的隔离级别，前者不加锁限制，后者相当于单线程执行，效率太差。

读提交解决了脏读问题，行锁解决了并发更新的问题。并且 MySQL 在可重复读级别解决了幻读问题，是通过行锁和间隙锁的组合 Next-Key 锁实现的。

mysql索引

帮助数据库高效获取数据的排好序的数据结构

WHO

• 主键索引：也是一种唯一性索引，必须指定为primary key，每个表只能有一个主键（并不一* * 定是一个列，主键索引也可以是有多个列组成的组合索引）
• 唯一索引：索引列的所有值只能出现一次，即必须唯一，值可以为空
• 普通索引：基本所有类型，值可以为空，没有唯一性限制
• 全文索引：索引类型为fulltext，可以在char，varchar，text类型的列上创建，一般不用，可以用ES
• 组合索引：多列组成的索引，专门用于组合搜索

几个概念

聚簇索引

或者叫聚集索引，是将索引和数据放在一起存放，如innodb的b+tree结构

非聚簇索引

或者叫非聚集索引，是将索引和数据分开存放，如myisam的b+tree结构，最后的叶子节点里存放的是索引和数据的磁盘地址，要想查询数据，还得从.MYD文件中查找

回表

先通过普通索引在b+tree下查找主键，再通过主键索引在b+tree下查找内容，这个过程，叫回表。
如：
Select * from table where name=’zhangsan’;
Select id from table where name=’zhangsan’;
其中，id是主键，第一条查询需要做回表操作；
而第二条查询，因为b+tree里的name索引里本来存的就是主键id，不需要再回表通过主键去找主键了，直接拿主键索引返回就行了，这个操作叫索引覆盖。

索引覆盖

如：
Select * from table where name=’zhangsan’;
Select id from table where name=’zhangsan’;
其中，id是主键，第一条查询需要做回表操作；
而第二条查询，因为b+tree里的name索引里本来存的就是主键id，不需要再回表通过主键去找主键了，直接拿主键索引返回就行了，这个操作叫索引覆盖。

最左匹配

因为索引是先按序的

如：
有一个组合索引(name, age)，判断下面sql是否会用到该索引
Select * from user where name=’zhangsan’; //会，第一位匹配，可以
Select * from user where age=18; //不会，直接匹配第二位，不可以
Select * from user where name=’zhangsan’ and age=18; //会，完美匹配，肯定可以
Select * from user where age=18 and name=’zhangsan’; //会，sql优化器会根据已有索引，将sql优化成 select * from user where name=’zhangsan’ and age=18;也是一个完美匹配

谓词下推

先过滤出要查询的字段，再将要执行的操作放在下一步执行，叫谓词下推。
如：
Select t1.name, t2.name from t1 inner join t2 on t1.id=t2.id;

1. 先把所有字段做表关联，然后再从关联好的表中选择需要的4个字段
2. 先取出需要的4个字段，再做表关联，这个叫谓词下推

组合索引的索引下推

如：组合索引(name, age)
直接从存储引擎拉取数据的时候直接按照name和age做判断，将符合的结果返回，这叫索引下推

正常人脑流程（mysql中不是这个流程）

1. 先根据name将所有满足条件的数据取出
2. 再根据age过滤

索引匹配方式

• 全值匹配
• 匹配最左前缀
• 匹配列前缀
• 匹配范围值
• 精确匹配某一列并范围匹配另一列
• 只访问索引的查询

数据结构

二叉树

image.png

缺点

1. 对于顺序的数据，二叉树是链式增长，没有效果
2. 当数据量比较大的时候，树的高度会比较深（有几层深度，就会至少要查几次，即需要多次读入内存，而操作系统每次读取都是读4k整数倍大小的页，比如mysql就是每次读取16k）

红黑树

又叫二叉平衡树

image.png

缺点

当数据量比较大的时候，树的高度会比较深（有几层深度，就会至少要查几次，即需要多次读入内存，而操作系统每次读取都是读4k整数倍大小的页，比如mysql就是每次读取16k）

B-Tree

B树，针对红黑树深度太深问题，可以考虑横向扩充每个节点的大小，于是，B树有了

特点

1. 叶节点具有相同的深度，叶子节点的指针为空
2. 所有索引元素不重复
3. 节点中的数据索引从左往右递增排列
4. 所有键值分布在整棵树中
5. 搜索有可能在非叶子节点结束
6. 每个节点拥有多个子节点

image.png

缺点

当data比较大时，单个节点存的索引量较少，会导致树深度加深

B+Tree

特点

1. 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引
2. 叶子节点包含所有索引字段
3. 叶子节点用指针连接，提高访问性能，特别是顺序查找的时候

image.png

B+tree相较于b-tree

因为最后的叶子节点之间有一个指针连接，当查询范围查找的时候，很方便

Hash表

基于哈希表实现，只有匹配所有列的查询才有效。对于每一行数据，存储引擎都会对所有索引列计算一个哈希码，哈希码是一个较小的值，不同键值的行计算出的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时保存指向每个数据行的指针。
如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中去。

image.png

优点

特别适合精确查找，一般用于那种不需要范围查找的字段上，如订单号

缺点

1. Hash存储需要将所有的数据添加到内存，比较消耗内存空间
2. 不适合范围查找，因为hash码是顺序的，但对应的数据行是乱序的，如：age>18。
3. 对于联合索引，不支持部分查找。因为hash是按所有索引列来计算hash的，如（name, age），只用name来查询是不支持的

mysql存储引擎

1. 表数据文件本身就是按B+Tree组织的一个索引结构文件
2. 聚集索引：叶子节点包含了完整的数据记录（而不是存放数据的磁盘地址），innodb的主键索引就是一个聚集索引（聚集：索引跟数据不是分开存储的，而是放在一个文件一起存储的，所以肯定比非聚集索引的效率高）。

InnoDB

原理

底层应用b+tree结构

image.png

存储实现

Frm文件：存储表结构
Ibd文件：存储索引和数据

image.png

默认主键索引

B+Tree

问答

问：为什么innodb必须有主键？
答：innodb设计就是按b+tree树设计的（设计表的时候没有显示声明主键，那么mysql会自动选一个字段（唯一的）作为主键；如果没有唯一键，那么会自动生成row_id作为主键（不可见））

问：为什么推荐使用整型的主键？
答：因为要比较大小，而用整型比较大小比较快，所以不推荐用uuid；另一方面，因为整型短，占用空间小

问：为什么主键要自增？
答：索引元素从左到右依次递增，保持主键自增，永远都是在最后面追加元素，而如果是插入一个中间元素，索引结构会做分裂和树平衡，而这些操作都是会影响性能的。

问：非主键索引是怎么建的？
答：非主键上建立索引，会同样建立一个b+tree，但是在叶子节点中，存的不是数据，而是主键，再通过主键去查找数据

问：联合索引的底层数据结构？
答：

问：什么是页分裂/页合并？
答：操作系统每次从磁盘里读数据都是按照4k的整数倍读取数据的，当某一页数据满了，而刚好要往这页插入一个中间数据，就会在要插入数据的地方进行页分裂，当数据插入后，再将页合并；再然后会将最后一条数据分裂出来，并合并到下一页。

MyIsam

原理

底层应用b+tree结构，非主键上建立索引，也是最后存的主键，再通过主键找磁盘地址，再通过磁盘地址找对应数据。

image.png

存储实现

一个表对应三个文件
Frm文件：存储表结构
MYD文件：存储数据
MYI文件：存储索引

image.png

默认主键索引

B+Tree

Memory

默认主键索引

Hash map

mysql优化经验

explain

show profile

一种查看sql所有执行阶段所花费的时间的命令
如：
set profiling=1;
select * from t;
show profile for query 1;

image.png