mysql面试中的常见问题

1. 先说下mysql的隔离级别吧
2. mysql的innodb引擎是如何实现可重复读的？
3. mvcc是如何实现的？
4. redolog和binlog的区别都有哪些？
5. mysql如何解决幻读的？
6. 来聊聊索引吧，innodb用的什么数据结构的索引？
7. b+tree和b-tree的区别
8. b+tree的叶子节点可以存些什么
9. 3层b+tree能存多少行数据
10. 覆盖索引是什么？
11. 查询请求都有哪些情况不走索引？
12. explain有哪些关键字段，含义是什么？
13. mysql你都知道哪些优化方式？

以下是这些问题的答案，其实每一个问题都可以写一个单独的文章介绍，所以答案会比较简短，只写总结性的东西

1.先说下mysql的隔离级别吧

老生常谈，这个问题基本都知道

除了知道这4个隔离级别，还应该知道具体是什么现象

读未提交 read uncommitted ：两个事务，b事务修改数据还未提交，a事务已经可以读到修改后的数据

不可重复读 read committed ：两个事务，a事务读取数据结果，b事务修改数据并提交，这时候再读取会发现数据已改变

可重复读 repeatable read ：mysql默认级别，事务开启多次读取的数据一致，不受其他其他事务影响，使用mvcc解决

串行化：会执行悲观锁表，并发度极低，基本不会使用

2.mysql的innodb引擎是如何实现可重复读的？

mysql使用mvcc来实现可重复读，一般这么回答完会紧接着问mvcc的实现原理。

mvcc全称多版本并发控制，要知道他的概念是维护一个数据的多个版本，来实现无锁的并发读写功能。

mvcc实现依赖3个隐式字段，undolog，read view

隐式字段不用记具体的名，只需要知道一个是最后一次修改记录的事务id，一个是回滚指针，一个是隐含的自增id。

undolog分两种，insert操作时产生的insert undo log和update或delete时产生的update undo log，mvcc主要使用update undo log。

read view是事务进行快照读时生产的读视图，在事务执行select的时刻会生成一个当前的快照，此时read view还有三个关键属性，up_limit_id（当前活跃的最小事务id），trx_list（当前活跃的事务id列表），low_limit_id（出现过的事务id的最大值+1），经过可见性算法比较，决定当前事务能看到哪个版本的数据，具体可看下 正确的理解MySQL的MVCC及实现原理。

4.redolog和binlog的区别都有哪些？

undolog主要是为mvcc使用对于数据历史版本的查看，还有就是事务回滚时使用。

redolog是重做日志文件，是记录数据修改之后的值，主要用处可以在数据库宕机时还原数据。

1. redolog是在InnoDB存储引擎层产生，而binlog是MySQL数据库的上层服务层产生的。
   
2. 两种日志记录的内容形式不同。MySQL的binlog是逻辑日志，其记录是对应的SQL语句，对应的事务。而innodb存储引擎层面的重做日志是物理日志，是关于每个页（Page）的更改的物理情况。
   
3. 两种日志与记录写入磁盘的时间点不同，binlog日志只在事务提交完成后进行一次写入。而innodb存储引擎的重做日志在事务进行中不断地被写入，并日志不是随事务提交的顺序进行写入的。
   
4. binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redolog是循环使用。
   
5. binlog可以作为恢复数据使用，主从复制搭建，redolog作为异常宕机或者介质故障后的数据恢复使用。
   

5.mysql如何解决幻读的？

innodb使用next-key lock解决幻读问题。

insert into test(xid) values (1), (3), (5), (8), (11);

由于xid上是有索引的，该算法总是会去锁住索引记录。现在，该索引可能被锁住的范围如下：(-∞, 1], (1, 3], (3, 5], (5, 8], (8, 11], (11, +∞)。

现在开启事务

select * from test where id = 8 for update

执行后会锁住的范围：(5, 8], (8, 11]。除了锁住8所在的范围，还会锁住下一个范围，所谓Next-Key。

6.innodb用的什么数据结构的索引？

b+tree结构。其实myisam也是用的b+tree

7.b+tree和b-tree的区别

1. b-tree每个节点都存储了key和数据，b+tree非叶子节点只存储键值信息，以及指针
2. b+tree的叶子节点使用指针连接成链表，可以做高效的范围查询

8.b+tree的叶子节点可以存些什么

b+tree的叶子节点可以存数据，也可以存主键的值，聚簇索引的叶子节点保存了整行数据，也叫主键索引或聚簇索引，每个表有且只有一个聚簇索引，叶子节点保存的是主键值的为非聚簇索引，都是由其他非主键字段创建的索引，这种索引由于叶子节点没有数据，所以需要再去主键索引找到对应的行数据返回，这个操作叫做回表

可见 MySQL聚簇索引和非聚簇索引的理解

9.3层b+tree能存多少行数据

机械硬盘在读取数据时，比较耗时操作是机械臂读取的过程，如果索引构建的树越高，寻址的次数就越多，耗时也就越多。

3层b+tree索引树能存多少行数据，大概2千万左右，其实你就算说上亿也可以只要知道怎么算就行，首先mysql的存储单元是page，一个page默认是16k，然后对于非叶子节点不用保存数据，只保存主键、指针和record header即可，所以对于int类型的主键非叶子节点大概能保存1100 ~ 1200个数据，叶子节点保存的行数据加入是1k的，那一个page就是16行数据，所以计算1203*1203*16，大概是2000多万的数据，如果你行数据只有4个字节，就保存一个int字段，那3层树能表示1203*1203*676，大概9.7亿数据

10.覆盖索引是什么？

指一条sql查询的内容只通过非聚簇索引既可获取，不用在进行回表查询，称作覆盖索引

11.查询请求都有哪些情况不走索引？

1. 模糊查询 like 以%开头
   
2. 索引列参与计算,使用了函数
   
3. 非最左前缀顺序
   
4. where单列索引对null判断
   
5. where使用not，<>，!=
   
6. or操作有至少一个字段没有索引
   
7. 需要回表的查询结果集过大（超过配置的范围）
   
8. 隐式转换
   

12.explain有哪些关键字段？

explain命令概要

1. id:select选择标识符
   
2. select_type:表示查询的类型。
   
3. table:输出结果集的表
   
4. partitions:匹配的分区
   
5. type:表示表的连接类型
   
6. possible_keys:表示查询时，可能使用的索引
   
7. key:表示实际使用的索引
   
8. key_len:索引字段的长度
   
9. ref:列与索引的比较
   
10. rows:扫描出的行数(估算的行数)
    
11. filtered:按表条件过滤的行百分比
    
12. Extra:执行情况的描述和说明
    

explain 中的 select_type（查询的类型）

1. SIMPLE(简单SELECT，不使用UNION或子查询等)
   
2. PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
   
3. UNION(UNION中的第二个或后面的SELECT语句)
   
4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
   
5. UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
   
6. SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
   
7. DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
   
8. DERIVED(派生表的SELECT, FROM子句的子查询)
   
9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
   

explain 中的 type（表的连接类型）

1. system：最快，主键或唯一索引查找常量值，只有一条记录，很少能出现
   
2. const：PK或者unique上的等值查询
   
3. eq_ref：PK或者unique上的join查询，等值匹配，对于前表的每一行(row)，后表只有一行命中
   
4. ref：非唯一索引，等值匹配，可能有多行命中
   
5. range：索引上的范围扫描，例如：between/in
   
6. index：索引上的全集扫描，例如：InnoDB的count
   
7. ALL：最慢，全表扫描(full table scan)
   

explain 中的 Extra（执行情况的描述和说明）

1. Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
   
2. Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
   
3. Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
   
4. Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
   
5. Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
   
6. Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
   
7. No tables used：Query语句中使用from dual 或不含任何from子句
   

13.mysql你都知道哪些优化方式？

首先对于表的优化

1. 设置合理的字段长度，行数据的大小直接决定了树高度，如果一行数据过多会导致b+树的高度变高
2. 字段尽量不设置null，使用默认值代替，null会占用额外的存储空间，并且where条件里对is null的判断也不会走索引，not in的查询会返回永远为null的值
3. 索引要建在区分度高的字段上，并且这个字段不需要经常更新，索引字段的经常更新会导致重建索引树

对于sql的优化

1. 创建合理的索引，符合最左前缀匹配原则
2. 优化子查询，使用join代替子查询，因为嵌套查询会创建临时表，需要较大的系统开销
3. 使用explain查看sql是否用上索引
4. 很多时候使用exists 代替 in 是一个好的选择，select num from a where exists(select 1 from b where num=a.num)
5. 不要使用select *