【MySQL】

我会一直

已于 2022-01-19 22:23:54 修改

阅读量113

点赞数

分类专栏：数据库文章标签： mysql innodb B+Tree

于 2022-01-15 18:47:02 首次发布

本文链接：https://blog.csdn.net/weixin_37838913/article/details/105209241

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

InnoDB（B+树）

在这里插入图片描述

root记录每一page的最小值（如图的取1和5做page的最小值）
page节点的大小是固定的，一般为16K。假如索引数据用bigint整型，一个索引数据就是8bit，索引数据中间还存着下一个节点（page）的磁盘位置，一般大小为6bit，所以一页page能存16k/(8+6)bit=1170个索引数据
每个叶子节点：存储索引数据+一行数据，我们姑且估计它有1k，那么一页就能存16行数据。那么一棵B+树就能存1170117016=2千万+的数据
聚集索引:索引值已经包含数据值(主键索引包含所有主键)(索引覆盖)
- 如果一个表建了主键的话，那么主键索引就是聚集索引。如果没有建，那么就是默认帮你建一个row_id作为聚集索引
一个表只有一个聚集索引，只有一个叶子节点存数据值的索引，用来回表
那么这里就聊到了二级索引，二级索引也就是非聚集索引，比如咱们常常要查哪个字段（name），就用那个字段加索引，那么这棵B+树的叶子节点存的是主键值。咱们在二级索引中找到name的叶子节点后，找到这个叶子结点存的主键值，就会拿着主键值回表（回聚集索引）找data值。
为什么存的是主键值呢？为了一致性和节省存储空间！
一致性是指不可能每个B+树都要维护data，只要在聚集索引插入data值，再重新生成二级索引就行。
一个索引一个B+树
回表:按主键
联合索引：
最左前缀原则
不是数字的字符都转成0

Explain

explain select b from t1 order by b,c,d;

在这里插入图片描述

explain select * from t1 order by b,c,d;

这里是指全表扫描
在这里插入图片描述

explain select (select 1 form actor where id=1) from (select * from film where id=1)
der;

在这里插入图片描述

type:关联类型或者是访问类型

一般来说查询达到range级别，最好达到ref。
system>const>eq_ref>ref>range>index>ALL

select * from film where id=1
system>const：mysql能对查询的某部分进行优化并将其转化成一个常量。
select * from film_actor left join film on film_actor.film_id=film.id;
eq_ref：用primary key 或 unique key 索引的所有部分被连接使用
ref：相比eq_ref,不使用唯一索引，使用普通索引或者唯一索引的部分前缀
explain select * from actor where id>1;
range:in(),>,<,>=,between
index：扫描全索引就能拿到结果集，一般是扫描某个二级索引
ALL:全表扫描：聚集索引从头查到尾。

Extra

Using index:覆盖索引：要查找的所有数据都在索引树就能找到
Using where：使用where语句来处理结果，并且查询的列未被索引覆盖。
Using index condition：
Using temporary ： mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是要想到用索引来优化。
distinct会创建临时表。

key_len

在这里插入图片描述

Mysql底层执行原理详解

连接器

客户端丰富：navicat，mysql front，jdbc，SQLyog
主要是连接服务器和权限认证
show processlist; 可以查看当前mysql实例有多少个客户端连接过来，并且可以查看每个连接正在干的事情：Command：Query，Sleep；state：starting
如果发现有一个连接一直在阻塞状态，可以使用 kill 14; 14为id
-

查询缓存

鸡肋，在mysql8.0之后去除。

分析器

词法分析：
语法分析：比如我们在写sql时有时会提示语法错误，就是由mysql的分析器完成；

优化器

执行计划生成索引选择

执行器

调用引擎接口获取查询结果：InnoDB，MYIsam，memory

事务及其ACID属性

ACID属性

Atomicity：原子性：事务是一个原子操作单位，其对数据的修改，要么全部执行，要么全部不执行。
Consistent：一致性：所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性。
Isolation：隔离性：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的。
Durable：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

问题	detail
更新丢失或脏写	当两个或多个事务修改同一行数据，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题-最后的更新覆盖了又其他事务所做的更新
脏读	事务A读取了事务B修改但是没有提交的数据。B退票+1但是没有commit，A读了1以为有票，B这时rollback退票失败，票还在B手上，但实际库存给不了A交货
不可重读	事务A读取了事务B修改但是没有提交的数据。B退票+1但是没有commit，A读了1以为有票，B这时rollback退票失败，票还在B手上，A再次读票库为0。但也可能A一开始读了票库为0，B退票commit成功，票+1，A这时读到了又有票1。不管别的事务有没有提交，这里重点是说两次读的数据不一致，不符合隔离性。
幻读	事务A读了一共有4条数据，但是事务B插入1条提交事务，事务A再次读就变成5条。这里重点是指insert，delete数据。

为了解决上述问题才有的事务隔离机制解决，不同的隔离级别解决不同的问题。

事务隔离级别

隔离级别	脏读	不可重读	幻读
读未提交	没有解决	没有解决	没有解决
读已提交	解决	没有解决（还是会读到别人修改提交的数据）	没有解决（还是会读到别人新增提交的数据）
可重复读	解决	解决（只读自己第一次读到的数，不管B修不修改。但是修改操作还是按实时数据，拿着这个数去操作业务，那就会有线程安全问题，通过java加锁另外解决）	没有解决
可串行化	解决	解决（mysql对要读的数据都加了锁）	解决