目录
4.为什么MySQL数据库要用B+树存储索引?而不用红黑树、Hash、B树?
mysql的隔离级别
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
读未提交 | 有 | 有 | 有 |
读已提交 | 无 | 有(mvcc解决) | 有 |
可重复读 | 无 | 无 | 无(mvcc解决) |
串行化 | 无 | 无 | 无 |
1.MySQL 的可重复读怎么实现的?
1.在innodb中,每一行都隐藏有三个字段,记录插入或更新该行的事务的事务ID,回滚指针,指向 undo log 记录,行ID,随着插入新行而单调递增,如果有主键,则不会包含该列。
2. RR 级别为例:每开启一个事务时,系统会给该事务会分配一个事务 Id,在该事务执行第一个 select 语句的时候,会生成一个当前时间点的事务快照 ReadView,(ReadView的内容:包含了生成ReadView时的事务id,生成ReadView时,还未执行提交的事务列表id,还未执行提交的事务列表中最小的事务id,生成readView时,系统分配给下一个事务的id)
3.将readView中的相关的事务id分别和数据记录行后的插入或更新该行的事务的事务ID进行比较判断 得到哪些数据行是对当前事务是可访问的,来达到RR级别的可重复读
4.在进行判断时,首先会拿记录的最新版本来比较,如果该版本无法被当前事务看到,则通过记录的回滚指针 找到上一个版本,重新进行比较,直到找到一个能被当前事务看到的版本。
5.RR和RC的区别是,RR 级别只在事务开始时生成一次(也就是第一个select语句时),之后一直使用该 ReadView。而 RC 级别则在每次 select 时,都会生成一个 ReadView。
2. MVCC 解决了幻读了没有?
幻读:在一个事务中使用相同的 SQL 两次读取,第二次读取到了其他事务新插入的行,则称为发生了幻读。
1.谈到幻读,首先我们要引入“当前读”和“快照读”的概念
2.快照读:生成一个事务快照(ReadView),之后都从这个快照获取数据。普通 select 语句就是快照读。
3.当前读:读取数据的最新版本。常见的 update/insert/delete、还有 select ... for update、select ... lock in share mode 都是当前读
对于快照读,MVCC 因为因为从 ReadView 读取,所以必然不会看到新插入的行,所以天然就解决了幻读的问题。
而对于当前读的幻读,MVCC 是无法解决的。需要使用 Gap Lock 或 Next-Key Lock(Gap Lock + Record Lock)来解决
SQL 标准中规定的 RR 并不能消除幻读,但是 MySQL 的 RR 可以,靠的就是 Gap 锁。在 RR 级别下,Gap 锁是默认开启的,而在 RC 级别下,Gap 锁是关闭的。
3.常见的索引类型有哪些?
常见的索引类型有:hash、b树、b+树。
hash:底层就是 hash 表。进行查找时,根据 key 调用hash 函数获得对应的 hashcode,根据 hashcode 找到对应的数据行地址,根据地址拿到对应的数据。
B树:B树是一种多路搜索树,n 路搜索树代表每个节点最多有 n 个子节点。每个节点存储 key + 指向下一层节点的指针+ 指向 key 数据记录的地址。查找时,从根结点向下进行查找,直到找到对应的key。
B+树:B+树是b树的变种,主要区别在于:B+树的非叶子节点只存储 key + 指向下一层节点的指针。另外,B+树的叶子节点之间通过指针来连接,构成一个有序链表,因此对整棵树的遍历只需要一次线性遍历叶子结点即可。
4.为什么MySQL数据库要用B+树存储索引?而不用红黑树、Hash、B树?
红黑树:如果在内存中,红黑树的查找效率比B树更高,但是涉及到磁盘操作,B树就更优了。因为红黑树是二叉树,数据量大时树的层数很高,从树的根结点向下寻找的过程,每读1个节点,都相当于一次IO操作,因此红黑树的I/O操作会比B树多的多。
hash 索引:如果只查询单个值的话,hash 索引的效率非常高。但是 hash 索引有几个问题:1)不支持范围查询;2)不支持索引值的排序操作;3)不支持联合索引的最左匹配规则。
B树索引:B树索相比于B+树,在进行范围查询时,需要做局部的中序遍历,可能要跨层访问,跨层访问代表着要进行额外的磁盘I/O操作;另外,B树的非叶子节点存放了数据记录的地址,会导致存放的节点更少,树的层数变高
5.MySQL 中的索引叶子节点存放的是什么
MyISAM和InnoDB都是采用的B+树作为索引结构,但是叶子节点的存储上有些不同。
MyISAM:主键索引和辅助索引(普通索引)的叶子节点都是存放 key 和 key 对应数据行的地址。在MyISAM 中,主键索引和辅助索引没有任何区别。
InnoDB:主键索引存放的是 key 和 key 对应的数据行。辅助索引存放的是 key 和 key 对应的主键值。因此在使用辅助索引时,通常需要检索两次索引,首先检索辅助索引获得主键值,然后用主键值到主键索引中检索获得记录。
6.说说共享锁和排他锁?
共享锁又称为读锁,简称S锁,顾名思义,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。
排他锁又称为写锁,简称X锁,顾名思义,排他锁就是不能与其他锁并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务可以对数据就行读取和修改。
常见的几种 SQL 语句的加锁情况如下:
select * from table:不加锁
update/insert/delete:排他锁
select * from table where id = 1 for update:id为索引,加排他锁
select * from table where id = 1 lock in share mode:id为索引,加共享锁
7.说说数据库的行锁和表锁?
行锁:操作时只锁某一(些)行,不对其它行有影响。开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高。
表锁:即使操作一条记录也会锁住整个表。开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突概率高,并发度最低。
页锁:操作时锁住一页数据(16kb)。开销和加锁速度介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般。
InnoDB 有行锁和表锁,MyIsam 只有表锁
8.InnoDB 的行锁是怎么实现的?
InnoDB 行锁是通过索引上的索引项来实现的。意味者:只有通过索引条件检索数据,InnoDB 才会使用行级锁,否则,InnoDB将使用表锁!
对于主键索引:直接锁住锁住主键索引即可。
对于普通索引:先锁住普通索引,接着锁住主键索引,这是因为一张表的索引可能存在多个,通过主键索引才能确保锁是唯一的,不然如果同时有2个事务对同1条数据的不同索引分别加锁,那就可能存在2个事务同时操作一条数据了。
9.InnoDB 锁的算法有哪几种?
Record lock:记录锁,单条索引记录上加锁,锁住的永远是索引,而非记录本身。
Gap lock:间隙锁,在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。
Next-key lock:Record lock 和 Gap lock 的结合,即除了锁住记录本身,也锁住索引之间的间隙。
10.MySQL 如何实现悲观锁和乐观锁?
乐观锁:更新时带上版本号(cas更新)
悲观锁:使用共享锁和排它锁,select...lock in share mode,select…for update。
11.explain 用过吗,有哪些字段分别是啥意思?
explain 字段有:
id:标识符
select_type:查询的类型
table:输出结果集的表
partitions:匹配的分区
type:表的连接类型
possible_keys:查询时,可能使用的索引
key:实际使用的索引
key_len:使用的索引字段的长度
ref:列与索引的比较
rows:估计要检查的行数
filtered:按表条件过滤的行百分比
Extra:附加信息
12.type 中有哪些常见的值?
按类型排序,从好到坏,常见的有:const > eq_ref > ref > range > index > ALL。
const:通过主键或唯一键查询,并且结果只有1行(也就是用等号查询)。因为仅有一行,所以优化器的其余部分可以将这一行中的列值视为常量。
eq_ref:通常出现于两表关联查询时,使用主键或者非空唯一键关联,并且查询条件不是主键或唯一键的等号查询。
ref:通过普通索引查询,并且使用的等号查询。
range:索引的范围查找(>=、<、in 等)。
index:全索引扫描。
All:全表扫描
13.explain 主要关注哪些字段?
主要关注 type、key、row、extra 等字段。主要是看是否使用了索引,是否扫描了过多的行数,是否出现 Using temporary、Using filesort 等一些影响性能的主要指标。
14.如何做慢 SQL 优化?
首先要搞明白慢的原因是什么:是查询条件没有命中索引?还是 load 了不需要的数据列?还是数据量太大?所以优化也是针对这三个方向来的。
首先用 explain 分析语句的执行计划,查看使用索引的情况,是不是查询没走索引,如果可以加索引解决,优先采用加索引解决。
分析语句,看看是否存在一些导致索引失效的用法,是否 load 了额外的数据,是否加载了许多结果中并不需要的列,对语句进行分析以及重写。
如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行垂直拆分或者水平拆分。