[大数据]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中

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

覆盖索引 : 使用非主键索引，不管是单列索引还是联合索引，如果select的数据列只能从索引中取得，不必从数据区读取，这时候使用的索引就是覆盖索引，避免了回表。

索引下推：

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

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

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

回表：

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

索引失效的场景

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

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

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

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

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