mysql执行计划
mysql执行计划就是一条sql语句的执行过程。使用关键字expain。expain关键字返回信息(部分列):
Column | Meaning |
---|---|
id | 查询序列号 |
type(最重要) | 查询时的连接类型 |
key | 所用到的索引 |
extra(也重要) | 其他信息,如是否使用filesort等 |
Mysql索引
索引是帮助mysql高效获取数据的数据结构。
memory的存储引擎支持的hash索引,innodb支持自适应hash索引。innodb使用的是B+树索引.
B树与B+树
- B树每个节点包含指针,键值(表中记录的主键),和数据,而B+树的非叶子节点只有键值和指针,数据全放在叶子节点中。而数据是很占内存的。因此相同内存B+树能存放更多数据,相同高度的B+树和B树,B+树存放的记录能高很多。
- B+树叶子节点有两两指针相互连接,顺序查询性能更高。
聚簇索引与非聚簇索引
数据与索引存储在一起的叫做聚簇索引,没有在一起叫非聚簇索引.
innodb存储引擎在进行数据插入的时候,数据必须要和某一个索引列存储在一起,这个索引可以是主键,如果没有主键,选择唯一键,其次再选择6字节的row-id来进行存储.数据必定是和一个索引绑定再一起的,榜单数据的索引叫做聚簇索引。其他索引的叶子节点中存储的数据不再是整行记录,而是聚簇索引的id值. 因此innodb中既有聚簇索引,也有非聚簇索引。而myisam中只有非聚簇索引。
回表
什么是回表:如对于一个id为主键,name为普通索引的表,select * from table where name = xxx ,查询时会先根据name的B+树找到对应的叶子节点,查询到对应行记录的id值,再根据id去检索整行记录,这个过程就叫回表,回表效率低,要尽量避免回表操作。
索引覆盖
还是上一个列子,将查询语句改为 select id,name from table where name = xxx。此时根据name的值的B+树索引去检索对应的记录,就能获得id的属性值,索引的叶子节点就包含了查询的所有列,此时不需要回表。这个过程叫索引覆盖,using index,因此 在某些场景中,可以考虑将要查询的所有列都变成组合索引,此时就会使用索引覆盖,加快查询效率。
最左匹配
创建索引的时候可以选择多个列来共同组成索引,此时叫做组合索引或者联合索引,要遵循最左匹配原则。如 id为主键,name,age为组合索引。在查询时select * from table where age = xx and name =xx 此时就不是最左匹配原则,mysql内部会自行t调整为where name = xxx and age = xx,就会使用组合索引,而当查询语句为select * from table where age = xx 时就不会走组合索引。(带头大哥不能少)
索引下推
select * from table where name = xxx and age = xx;在没有索引下推之前,先根据name从存储引擎中拉去数据到server层,然后再server层中对应age进行数据过滤
有了索引下推之后 : 根据name和age两个条件进行数据筛选,将筛选之后的结果返回给server层,就节约了从server层到存储引擎拉取数据的时间。
MVCC
基础知识:
- 当前读: 读取的是数据的最新版本,总是读取到最新的数据
- select …lock in share mode
- select … for update
- update
- delete
- insert
- 快照读:读取的是历史的版本
-select … - 事务隔离级别
- 读未提交
- 读已提交
- 可重复读(默认)
- 串行化
第一部分:隐藏字段
- 每一行记录上都会包含几个用户不可见的字段:
-DB_TRX_ID: 创建或者最后一次修改该记录的事务id
-DB_ROW_ID: 隐藏主键
-DB_ROW_PTR: 回滚指针 (与undolog相关)
当不同事务对同一条记录进行修改的时候,会导致该记录的undolog形成一个线性表,也就是一个链表,链首就是最新的历史记录,而链尾是最早的历史记录。
第二部分:readview
-
readview是指事务在进行快照读的时候产生的读视图,其中有以下组件
-trx_list :当前系统活跃的事务id
-up_limit_id: 列表中事务最小的id
-low_limit_id:系统尚未分配的下一个事务的id -
实际场景
事务1 | 事务2 | 事务3 | 事务4 |
---|---|---|---|
开启 | 开启 | 开启 | 开启 |
修改某一个值 并commit; | |||
快照读 |
此时能读到事务4修改的值吗?
具体的可见性规则如下:
- 首先比较DB_TRX_ID < up_limit_id,如果小于,则当前事务能看到DB_TRX_ID所在的记录,如果大于等于进入下一个判断阶段
- 接下来判断DB_TRX_ID >= low_limit_id,如果大于等于则代表DB_TRX_ID所在的记录在ReadView生成后才出现的,那么对于当前事务肯定不可见,如果小于,则进入下一步
- 判断DB_TRX_ID是否在活跃事务中,如果在,则代表在ReadView生成时刻,这个事务还是活跃的,还没有commit,修改的数据,当前事务看不到,如果不在,说明这个事务在ReadView生成之前就开始commit,那么修稿的结果时可见的
对于不同的隔离级别:
- RC: 每次在进行快照读的时候都会生成新的readview
- RR 只有在第一次进行快照读的时候才会生成readview,之后的读操作都会用第一次生成的readview
事务的四大特性
- 原子性 :通过undolog日志实现
- 一致性 : 由其他三个特性提供支持
- 隔离性: 通过MVCC实现
- 持久性 : redolog实现 redolog详解