MySQL知识总结
2022-08-19 19:36:00 0 举报AI智能生成
MySQL知识索引，事务，锁，MVCC多版本控制机制
作者其他创作
大纲/内容
1.索引数据结构详解
1.什么是索引
索引是一种排好序的数据结构
2.索引的数据结构--B+树
基础数据结构了解
二叉树
二叉搜索树
红黑树
B树
特点
结构图
B+树
子主题
3.索引分类
聚集索引
非聚集索引
2.Explain详解与索引实践
1.使用
在select语句之前增加explain关键字，MySQL会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL注意：如果from中包含子查询，仍会执行该子查询，将结果放入临时表中
2.详解
explain两个变种
explain extended
会在explain的基础上额外提供一些查询优化的信息。紧随其后通过showwarnings命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有filtered列，是一个半分比的值，rows*filtered/100可以估算出将要和explain中前一个表进行连接的行数（前一个表指explain中的id值比当前表id值小的表）
explain partitions
相比explain多了个partitions字段，如果查询是基于分区表的话，会显示查询将访问的分区。
explain中的列
1.id列
id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行
2.select_type列
simple：简单查询。查询不包含子查询和union
primary：复杂查询中最外层的select
subquery：包含在select中的子查询（不在from子句中）
derived：包含在from子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义
union：在union中的第二个和随后的select
3.table列
这一列表示explain的一行正在访问哪个表。当from子句中有子查询时，table列是<derivenN>格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询。当有union时，UNIONRESULT的table列的值为<union1,2>，1和2表示参与union的select行id。
4.type列
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。依次从最优到最差分别为：system>const>eq_ref>ref>range>index>ALL一般来说，得保证查询达到range级别，最好达到ref
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
const,system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看showwarnings的结果）。用于primarykey或uniquekey的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system
eq_ref：primarykey或uniquekey索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在const之外最好的联接类型了，简单的select查询不会出现这种type
ef：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
range：范围扫描通常出现在in(),between,>,<,>=等操作中。使用一个索引来检索给定范围的行
index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些
ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了
5.possible_keys列
查询可能使用哪些索引来查
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果
7.key列
mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是NULL
8.key_len列
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。举例来说，film_actor的联合索引idx_film_actor_id由film_id和actor_id两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找
key_len计算规则如下
9.ref列
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
10.Extra列
这一列展示的是额外信息。常见的重要值如下
using where：使用where语句来处理结果，并且查询的列未被索引覆盖
Usingindexcondition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围
Usingtemporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化
Usingfilesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的
Selecttablesoptimizedaway：使用某些聚合函数（比如max、min）来访问存在索引的某个字段是
3.实战
1.全值匹配
2.最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列
5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select*语句
6.mysql在使用不等于（！=或者<>），notin，notexists的时候无法使用索引会导致全表扫描<小于、>大于、<=、>=这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
7.isnull,isnotnull一般情况下也无法使用索引
8.like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作
使用覆盖索引，查询字段必须是建立覆盖索引字段
如果不能使用覆盖索引则可能需要借助搜索引擎
9.字符串不加单引号索引失效
10.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化
11.范围查询优化
4是否使用索引
子主题
likeKK%相当于=常量，%KK和%KK%相当于范围
3.SQL语句的执行过程详解
1.内部组件结构结构
1.Server层
连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等
2.存储引擎层
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎
结构模型图
2.组件详解
1.连接器
连接器负责跟客户端建立连接、获取权限、维持和管理连接
2.查询缓存
3、分析器
1、词法分析2、语法分析3、语义分析4、构造执行树5、生成执行计划6、计划的执行
4.优化器
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
5.执行器
1,判断权限
1.调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
2.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端
bin-log归档
1、Binlog在MySQL的Server层实现（引擎共用）2、Binlog为逻辑日志,记录的是一条语句的原始逻辑3、Binlog不限大小,追加写入,不会覆盖以前的日志
4.索引优化案例分析
5.事务隔离级别和锁
1.概述
数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题
2.事务及其ACID属性
1.事务是由一组SQL语句组成的逻辑处理单元
2.特性
原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行
一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性
隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然
持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
3.并发事务处理带来的问题
更新丢失(Lost Update)或脏写
最后的更新覆盖了由其他事务所做的更新
脏读（Dirty Reads）
事务A读取到了事务B已经修改但尚未提交的数据
不可重读（Non-Repeatable Reads）
事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
幻读（Phantom Reads）
事务A读取到了事务B提交的新增数据，不符合隔离性
4.事务隔离级别
子主题
常看当前数据库的事务隔离级别: show variables like 'tx_isolation';
设置事务隔离级别：set tx_isolation='REPEATABLE-READ';
Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别
3.锁详解
1.锁分类
性能
乐观锁(用版本对比来实现)
悲观锁
操作
读锁（共享锁，S锁(Shared)）
写锁（排它锁，X锁(eXclusive)）
数据操作
表锁
行锁
2.基本操作
表锁
每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景
手动增加表锁 lock table 表名称 read(write),表名称2 read(write);
查看表上加过的锁 show open tables;
删除表锁 unlock tables;
行锁
每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
InnoDB与MYISAM的最大不同有两点：
InnoDB支持事务（TRANSACTION）
InnoDB支持行级锁
InnoDB支持行级锁
总结
1.MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。
2.InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
3.读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。
3.其他
间隙锁(Gap Lock)
锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，有办法解决幻读问题吗？间隙锁在某些情况下可以解决幻读问题
临键锁(Next-key Locks)
子主题
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。
nnodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。
4.锁分析
行锁分析
show status like 'innodb_row_lock%';
查看INFORMATION_SCHEMA系统库锁相关数据表
死锁
查看近期死锁日志信息
show engine innodb status\G;
5.锁优化建议
1.尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
2.合理设计索引，尽量缩小锁的范围
3.尽可能减少检索条件范围，避免间隙锁
4.尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
5.尽可能低级别事务隔离
6.MVCC详解
多版本并发控制机制
Innodb引擎SQL执行的BufferPool缓存机制
链接
MVCC多版本并发控制
链接
0 条评论
下一页
