Mysql索引以及锁和mvvc

以你之姓@ 2023-03-14 08:22 5阅读 0赞

1 mysql为什么使用B+树作为索引结构？

**hash**  
没有范围查询，hash碰撞

**二叉树**  
深度太深，会呈现线性结构，IO查询时间长。

**平衡二叉树**

深度加大 io操作耗时大  
每块磁盘块（节点/页）保存数据量太小，浪费节点内存，  
不能很好的利用磁盘Io的交换特性  
局部性原理：当一个数据被用到时，附近的数据也通常会马上被使用。

**B树**  
多路查找，解决二叉树保存数据量小的缺点，但是每个节点保存数据，每个节点的关键字保存数量就少了，树的高度就会增加，Io时间就会变长，性能就会变差。

**B+树**

采用闭合区间，非叶子节点不存储数据，只保存叶子节点引用，可以存储更多的关键字，树的高度不会像B树那样变身，IO时间变短，查询效率稳定，减少磁盘访问次数。
    	数据存储在叶子节点，每个叶子节点尾节点指向下一个叶子节点头部，并且数据是顺序的，方便顺序遍历。

2 mysql索引存储结构

MyISAM 索引实现（非聚集）
    	MyISAM索引文件和数据文件是分离的
    	xxxx.frm 表
    	xxxx.MYD 数据文件
    	xxxx.MYI 索引文件
    
    	主键索引和非主键索引都是先查找到这个数据的磁盘地址，然后根据这个磁盘地址再去查找数据。
    
    
    
    Innodb 索引实现（聚集索引）
    	xxxx.frm 表定义文件
    	xxxxx.ibd 索引文件 +  数据文件
    	数据文件本身就是索引文件，索引文件本身就是按B+Tree的一个索引结构文件
    	InnoDb 必须有主键，并且推荐使用整型的自增主键，InnoDb没有隐士主键。
    
    	为什么非主键索引结构叶子节点存储的是主键值？ 为了一致性和节省非主键索引存储空间。
    							如果非主键索引保存数据了，如果数据更新了，需要维护两次，数据一致性。
    
    
    主键索引保存主键索引树中
    非主键索引保存在非主键索引树中，只不过叶子节点保存的是主键索引引用。
    
    
    主键索引查询直接查询索引树，非主键是查询非主键关键字索引树，只不过索引树中保存的是主键索引树的地址引用，然后根据这个引用再去查询主键索引树！

2.1 InnoDb 和 Myisam 索引查找结构区别

InnoDb
    
    	辅助索引（非主键索引）先找到满足数据的值的主键引用，然后再去主键索引查找。
    
    	主键索引直接查找主键索引树
    
    
    Myisam 
    	
    	主键索引和非主键索引都是先查找到这个数据的磁盘地址，然后根据这个磁盘地址再去查找数据。

2.2 联合索引查找结构

几个字段连接在一起，一个一个去比较。
    
    现根据第一个字段查找，然后根据第二个查找，最后找到主键值 再去查找数据（字符串根据asii码去比较）

3 如何选择索引列

联合索引
    
    name 手机号 sex性别
    
    频繁使用 
    	
    
    
    
    	1 比如 where 语句后面（使用最多的字段 ）
    
    	2 选择性最高的列有限（离散型高的原则 --- >  索引选择性 = 不重复的索引值/数据表的记录数 ，越接近1 的 离散型越好，越适合加索引。 ）
    	
    	3 空间越小原则 ： 如 名字和手机号 使用手机号
    
    	4 使用最频繁的列有限 （最左原则）
    
    
    联合索引 
    	如果出现 count(*) sum(xx) 等数据记录特别大时候，此数据接近于全表数据，没有走索引查询，而全表扫描，这种需要从业务层面进行优化，而不是从索引进行优化。
    
    
    覆盖索引
    
    	查询可通过索引节点中的关键字直接返回，成为覆盖索引。
    	
    	解决办法：可以根据相应条件通过子查询查出主键，然后通过主键关联全表。

4 mysql执行计划

执行计划是通过关键字 explain 可以模拟优化器执行sql语句，从而知道mysql是如何处理你的sql语句，分析查询语句或是结构的性能瓶颈。
    
    
    
    
    
    
    
    
    字段为null is null 不走索引  不为null is null走索引
    
    字符串不加单引号 索引实现
    
    少用or ，索引会失效， 尽量用 in
    
    %xx% 索引会失效，%xxx% 要想走索引，使用覆盖索引，select name from a where name like %xxxx% ;    xxx% 索引不会失效
    
    适用范围查询索引 后面的查询条件索引会失效

5 存储引擎介绍

插拔式的插件方式

存储引擎是指在表之上的，即一个库中的每一个表都可以使用指定的专用的存储引擎。

不管采用什么样的存储殷勤，都会在数据区，产生一个.frm文件 标结果定义描述文件。

查看存储引擎 show variables like ‘%storage\_engine%’;

存储引擎

cvs

数据以csv文件存储
      不能定义没有索引，列定义必须为not null 不能设置自增列，不能使用达标或者数据的在线处理
      存储用，隔开，可直接编辑csv文件进行数据编排 ，数据安全性低
    
    
    Memory
       
       文件存储特定，数据保存在内存中
    
    支持hash索引和Btree索引 默认hash(0(1))

6 锁

共享锁
     
          又称为读锁，简称S锁，多个事务对统一数据可以共享一把锁，都可以访问数据，但不能修改。
    
          加锁方式
          
    	select * from users where id =1 locak in share mode
    
    	commit / rollback
    
     排它锁
    
    	又称写锁 简称 X锁，排它锁不能与其他所并存，若一个事务获取另一个数据行的排它锁，其他事物就不能获取该行的锁（共享锁，没排它锁）
    	只有获取了排它锁的事务才能对数据进行读取和修改（其他书屋读取的数据来自快照）
    
    	加锁方式
    		delete / update / insert 默认加上X锁
    	
    		select * from table name where ... from update 
    
    		commit / rollback
    
    行锁
    	是通过给索引上加锁实现的。
    
    	只有通过索引条件进行数据检索吗，InnoDB才会使用行级锁，否则 InnoDB 将视同表锁（锁住所有的索引记录）
    
    
    意向共享锁
    
    	表示事物准备给数据行加入共享锁，即一个数据航加共享锁之前必须先去的该表的IS锁，意向共享锁之间是可以互相兼容的。、
    
    意向排它锁
    	
    	表示事物准备给数据行加入排它锁，即一个数据航加排它锁前必须先获取该表的IX锁，意向排它锁之间是可以互相兼容的。
    
    
    意向锁（IS IX）是InnoDB数据操作之前自动加的，不需要用户干预。
    
    
    	意义：当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可以快速返回表不能启用表锁。
    
    自增所
    	
    	针对自增列自增长的一个特殊表级别锁
    
    	show variables like 'innodb_auto_lock_mode'
    
    
    临建锁
    	 Next-key locks:锁住记录+区间(左开右闭)
    
    	当sal执行按照索引进行数据的检索时,查询条件为范围查找(between and、<、 >等)并有数据命中则此时
    	SQL语句加上的锁为Next -key locks, 锁住索引的记录+区间(左开右闭)
    
    	解决幻读问题
    
    间隙锁 Gap locks
    
    	锁住数据不存在的区间(左开右开)
    	 当sgl执行 按照索引进行数据的检索时，查询条件的数据不存在，这时SQL语句加上的锁即Gap locks,锁
    	住索引不存在的区间(左开右开)
    
    
     Record 1 ocks:
    
     锁住具体的索引项
    
     当sa1执行按照唯一性(Primary key、Unique key)索引进行数据的检索时，查询条件等值匹配且查询的数
    据是存在，这时SQL语句加上的锁即为记录锁Record locks，锁住具体的索引项
    
    
    
    死锁
    
    多个事务同时持有和请求统一资源上的锁而产生循环依赖的时候就产生了死锁。
    
    
    	解决办法
    
    		数据库实现了各种死锁探查和超时机制。当前InnoDB处理死锁的方式是回滚持有最少排他行级锁的事务。
    		尽量避免并发的执行涉及到修改数据的语句。
    		大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
    		要求每-一个事务一次就将所有要使用到的数据全部加锁，否则就不允许执行。
    		预先规定-一个加锁顺序，所有的事务都必须按照这个顺序对数据执行封锁。如不同的过程在事务内部对对象的更新
    		执行顺序应尽量保证一致。
    		相关字段建立索引。优化后的执行计划使用了索引查找，将大幅提升该查询语句的性能，降低了锁定资源的时间，
    		同时也减少了锁定资源的范围，这样就降低了锁资源循环等待事件发生的概率，对于预防死锁的发生会有--定的作
    		用，
    		降低石离级别，如果业务允许，将隔离级别调低也是较好的选择
    		如果没有别的办法了，用表级锁定优化你的事务
    		死锁是无法完全避免的

7 MVVC机制

表中会两个隐士字段，数据行版本号，删除版本号。
    
    
    插入数据
    	
    	开启事务
    		
    		数据行版本号（系统事务号），删除版本号为null
    	关闭事务
    
    删除事务
    	开启事务
    		数据行版本号（设为null），删除版本号为系统事务号
    	关闭事务
    
    更新数据
    
    	
    
    
    查询数据
    	通过版本号的控制，无论其他事物插入，修改，删除，第一个查询到的数据都没有变化。
    	在InnoDB中，mvvc和锁是协同使用的。解决脏读幻读

8 undo log

Undo Log是什么: 
    
    	undo意为取消，以撤销操作为目的，返回指定某个状态的操作
    
    	undo 1og指事务开始之前，在操作任何数据之前,首先将需操作的数据备份到一个地
    	方(Undo Log)
    
    	UndoLog是为了实现事务的原子性而出现的产物
    
    Undo Log实现事务原子性:
    
    	事务处理过程中如果出现了错误或者用户执行了ROLL.BACK语 句, Mvsql可以利用Undo Log
    	中的备份将数据恢复到事务开始之前的状态
    
    	UndoLog在Mysal innodh存储引擎中用来实现多版本并发控制
    
    Undo log实现多版本并发控制:
    
    	事务未提交之前，Undo保存了未提交之前的版本数据，Undo 中的数据可作为数据旧版本:
    	快照供其他并发事务进行快照读

Redo Log  
Redo Log是什么:

Redo, 顾名思义就是重做。的恢复操作为目的，重现操作;
    
    	Redo log指事务中操作的任何数据,将最新的数据备份到一个地方(Redo Log)
    
    Redo 1og的持久:
    
    	不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo中。具体的落盘策略可以
    	进行配置redo 1og写入成功代表你sq1执行成功。
    
    	RedoLog是 为了实现事务的持久性而出现的产物
    
    Redolog实现事 务持久性:
    
    	防止在发生故障的时间点，断电，尚有脏页未写入磁盘，在重启mysq1服务的时候，根据redo log进行重 做，
    	从而达到事务的未入磁盘数据进行持久化这--特性。

Redo Log 和 bin Log 区别

redo log是属于innoDB层面， binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性
    的要求。
    
     redo log是物理日志，记录该数据页更新的内容; binlog是逻辑 日志，记录的是这个更新语句的原始逻辑
    
     redo log是循环写，日志空间大小固定; binlog是追加写， 是指一份写到一 定大小的时候会更换下一个文件，不
    会覆盖。
    
     binlog可以作为恢复数据使用，主从复制搭建，redolog作为异常宕机或者介质故障后的数据恢复使用。