面试总结（二）--mysql篇

一、mysql Innodb锁机制：Next-Key Lock:

1.1 Next-Key Locks
Next-Key Locks 是在存储引擎innodb、事务级别在可重复读的情况下使用的数据库锁，官网上有介绍，Next-Key Locks是行锁和gap锁的组合

2.1 GAP锁
gap锁，又称为间隙锁。存在的主要目的就是为了防止在可重复读的事务级别下，出现幻读问题。

在可重复读的事务级别下面，普通的select读的是快照，不存在幻读情况，但是如果加上for update的话，读取的是已提交事务数据，gap锁保证for update情况下，不出现幻读。

2.2 那么gap锁到底是如何加锁的呢？
假如是for update级别操作，先看看几条总结的何时加锁的规则。

• 唯一索引
  • 精确等值检索，Next-Key Locks就退化为记录锁，不会加gap锁
  • 范围检索，会锁住where条件中相应的范围，范围中的记录以及间隙，换言之就是加上记录锁和gap 锁（至于区间是多大稍后讨论）。
  • 不走索引检索，全表间隙加gap锁、全表记录加记录锁
• 非唯一索引
  • 精确等值检索，Next-Key Locks会对间隙加gap锁（会锁住该值左右两边的间隙），以及对应检索到的记录加记录锁。
  • 范围检索，会锁住where条件中相应的范围，范围中的记录以及间隙，换言之就是加上记录锁和gap 锁（至于区间是多大稍后讨论）。(左开右闭)
• 非索引检索，全表间隙gap lock，全表记录record lock

思考
假如条件是一个非索引列，那么如何处理？
假如是非索引咧，那么将会全表间隙加上gap锁。

条件是唯一索引等值检索且记录不存在的情况，会使用gap lock？
我们要考虑，gap lock是防止幻读，那么尝试思考，使用唯一索引所谓条件查找数据for update，如果对应的记录不存在的话，是无法使用行锁的。这时候，会使用gap lock来锁住区间，保证记录不会插入，防止出现幻读。

https://blog.youkuaiyun.com/weixin_43947156/article/details/117257634

二、可重复读隔离界别-幻读
https://blog.youkuaiyun.com/weixin_43947156/article/details/117732074

三、页面分页的显示，在大数据量的情况下，怎么解决count(*)全表扫描的问题

1、高性能mysql：覆盖索引或者创建汇总表或者调整业务架构
2、对于大表的后台，都有默认条件，默认7天之内。但如果查七天之前的就比较头痛
3、用explain来获取总行数。explain select * from table 返回的rows行数，该数值是估算值，如果对数据的总数精确度不是很高就可以用这个，速度快

四、大数据量的情况下，怎么优化limit 10000,20这种扫描大量数据的情况

1、优化

select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;

https://zhuanlan.zhihu.com/p/214377808

2、limit 10000,20前面几万条数据都不是我们要的，为啥也要回表
https://segmentfault.com/q/1010000038665420?utm_source=tag-newest

五、explain的常用字段解析

explain的常用字段解析

Type字段

null > system > const（主键索引扫描） > eq_ref(表中只有一条记录与之匹配，常见于主键索引和唯一索引) > ref(非唯一索引扫描) > ref_or_null > index > all

const 通过索引一次就找到了，const用于primary key或unique索引的比较，因为只匹配一行数据，所以很快，如主键至于where中，mysql会将查询置换成一个常量

eq_ref 常见于主键索引和唯一索引 对于每个索引键，表中只有一条记录与之匹配 应该多在链表的时候出现
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id;

ref 非唯一索引扫描 返回匹配某个单独值的所有行

ref_or_null 类似于ref，一般存在于非唯一索引的or查询，如select * from invest where num=2 or num is null;

index_merge 索引合并时会出现 useing intersect using sort_uion , 索引合并之后还需要进行一些计算工作生成最后的结果，例如下面这条sql，key1、key2、key3有创建独立的索引，mysql会单独扫描每个索引，共扫描三次，最后把三次的结果合并，但这个是可以通过组合索引来优化的，即创建（key1,key2,key3）组合索引，这样mysql就会优先使用组合索引，因此只要扫描一次索引就可以了
SELECT * FROM t1 WHERE key1=1 AND key2=2 AND key3=3;

range 出现了范围查找

index full index scan index和All的区别，index虽然也是全表扫，但它扫的是索引树，而All扫的是硬盘，总的来说索引文件的大小要比数据文件小

All full table scan 全表扫描

Extra字段

using where

使用单列索引后再对查找到的数据进行剩下的where的条件的过滤
高性能MYSQL第三版对它的解释：
using where 代表MYSQL服务器层将在存储引擎层返回行以后再应用WHERE过滤条件；

Using filesort
当不能使用索引树排序的时候，mysql会通过相应的算法将取得的数据在内存中进行排序
解决办法有二：一、获取数据后在程序中进行排序 二、在where条件列和order by列的字段中建立联合索引

Using temporary

以下这几种情况会产生内存临时表

使用了临时表保存中间结果 ， mysql对结果排序时使用了临时表，常见于order by排序和分组查询group by
1、当连表时，如left join时，order by后面是第二张表的字段就会出现using temporary。例
explain select * from film left join film_actor p on film.film_id=p.film_id order by p.actor_id;

2、如果语句中出现了distinct关键字也会(distinct非常耗时)。distinct：一旦mysql找到了与行相联合匹配的行，就不再搜索了
explain select distinct * from film left join film_actor p on film.film_id=p.film_id order by film.title;

3、UNION查询；

4、用到TEMPTABLE算法或者是UNION查询中的视图；

5、ORDER BY和GROUP BY的子句不一样时；

6、表连接中，ORDER BY的列不是驱动表中的；

7、DISTINCT查询并且加上ORDER BY时；

8、SQL中用到SQL_SMALL_RESULT选项时；

9、FROM中的子查询；

10、子查询或者semi-join时创建的表；

在以下几种情况下，会创建磁盘临时表：
1、数据表中包含BLOB/TEXT列；

2、在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列（或者超过 512字节的 二进制类型列，在5.6.15之前只管是否超过512字节）；

3、在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列（对于字符串类型是512个字符，对于二进制类型则是512字节）；