MySQL面试题

目录

请描述下一条查询sql的执行流程

Mysql索引用B+树有什么优势，除了B+树还有什么数据结构

索引创建应该注意哪些点

请例举你所知道的Mysql的锁！并做简单说明

为什么innoDB的RR能解决幻读问题

MVCC原理

什么是覆盖索引，什么是索引下推，什么是回表

索引失效的场景

---

请描述下一条查询sql的执行流程

1. 建立客户端连接

2. 查询缓存

3. 通过解析器词法分析，解析语法生成解析树

4. 通过预处理器将解析树再检查下，生成新的解析树

5. 根据解析树生成不同的执行计划，选择最优的执行计划

6. 选择存储引擎，执行计划，返回结果

Mysql索引用B+树有什么优势，除了B+树还有什么数据结构

1. 执行效率高：每个节点能存更多关键字，树的深度低，访问的io次数少

2. 执行稳定性好：叶子节点存数据，每次访问的io次数相同

3. 扫库、扫表效率更高：只需遍历叶子节点，不需要遍历整棵树

4. 排序效率更高：叶子节点上有下一个数据区的指针，数据形成了有序链表

B Tree在枝节点存储键值、数据地址、节点引用。

AVL Tree 左右子数深度差绝对值不能超过1.

索引创建应该注意哪些点

• 尽量在where、order、join、group by的字段上创建索引

• 索引个数不要太多

• 不要在 离散度低的字段，频繁更新的字段、随机无序的字段， 上创建索引

• 多个字段，尽量使用复合索引，按照最左匹配原则，最常用的放左边

• 过长的字段，建立前缀索引

请例举你所知道的Mysql的锁！并做简单说明

粒度纬度： 表锁、行锁、页锁

类型：共享锁、排他锁、意向共享锁、意向排他锁

用户：乐观锁、悲观锁

算法：记录锁、临建锁、间隙锁

1、共享锁（shard locks）

获取一行数据的读锁以后，阻塞其他事务修改，可以用在不允许修改数据的情况下。也叫做读锁

2、排它锁（Exclusive Locks）

用来操作数据的，也叫写锁。只要一个事务获取了一行数据的排它锁，其他事务就不能获取这一行的共享锁和排它锁。

加锁方式： 自动排他锁、For update

3、意向锁（Intention Locks）

在给数据加 共享锁之前，数据库会自动给这张表加上意向锁，表示我预约了。

当数据量多的时候提高加锁效率

4、记录锁（锁索引列）

记录锁是对索引记录的锁定。 例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 防止任何其他事务插入，更新或删除t.c1的值为10的

5、间隙锁（锁范围）

gap锁定是锁定索引记录之间的gap，或锁定在第一个或最后一个索引记录之前的gap上。

6、临建锁

它是MySQL里面默认的行锁算法，相当于记录锁加上间隙锁。 左开右闭

为什么innoDB的RR能解决幻读问题

通过mvcc+lbcc来解决的，mvcc解决快照读，lbcc解决当前读

1、LBCC(Lock Based Concurrency Control) 基于锁的并发控制

要保证前后两次读取的数据一致，那么我读取数据时，锁定操作的数据，不允许其他事务修改。

缺点：不支持并发的读写操作，影响效率

2、MVCC(Multi Version Concurrency Control) 多版本的并发控制

在修改数据之前给他建立一个备份或快照，后面事务读的时候去读取快照

MVCC原理

概念： InnoDB为每行记录都添加了两个隐藏字段

DB_TRX_ID，6字节，事务id,数据在哪个事务插入或修改为新数据，就记录为当前事务id

DB_ROLL_PTR,7字节，回滚指针（删除版本号，数据被删除或者记录为旧数据时，记录当前事务id，没修改和删除时为null）旧数据存储在undo log中

什么是覆盖索引，什么是索引下推，什么是回表

覆盖索引指一个查询语句的执行只需要从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。

优点：只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO操作，提高了效率。

Extra 为 Using index 表示使用了覆盖索引

索引下推：

• 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。

• 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

• 索引条件下推优化可以减少回表次数，提升查询效率

回表：

非主键索引，我们会通过索引找到 主键索引的键值，在通过主键值查出索引里面没有的数据，它比基于主键索引的查询多了一颗索引树，这个过程就叫回表。

索引失效的场景

1、联合索引 中where条件必须放第一个索引，最左原则，否则索引失效

2、使用OR关键字的前后条件必须都有索引，否则索引失效

3、like 使用了%xx,前匹配是全文搜索，索引失效，可以改为全文索引

4、索引上使用了函数计算（+-*/，avg count )

5、字符串索引 没有添加引号，导致隐式转换