MySQL-面试题总结

1. 为什么InnoDB存储引擎选择B+Tree索引结构？

(1) 思路，为什么不采用二叉树和红黑树 ？
普通二叉树，顺序插入，形成链表，大大影响查询效率。红黑树本质上也是二叉树，大数据量，树的层级深，影响查询效率。
相对于二叉树，层级更少，搜索效率更高。
（2）思路，为什么不是用B-Tree树？
对于B-Tree，无论叶子节点还是非叶子节点，都存放数据。这就导致一个页中保存的节点变少，指针也跟着变少（指针个数和树的阶数是相同的），要同样保存大量数据，只能增加树的高度，降低查询效率。
（3）思路，B+Tree索引的优势
所有的数据都存放叶子节点，查询数据都要在叶子节点中找，查询效率稳定。
叶子节点形成的双向链表，便于范围查询和排序，而Hash索引是不支持范围查询和排序的。

2. 以下两个SQL语句哪个查询效率高？

select * from user where id = 15;
select * from user where name = "jack";
id为主键，name字段也有对应的索引。
（1）按照id来查，主键索引就是聚集索引，聚集索引的叶子节点存储的就是行数据。
（2）而按照name来查，name字段有索引，先查询二级索引查询到对应的主键，在走聚集索引，拿到行数据。回表查询。

3.InnoDB主键索引的B+Tree索引为多高呢？

假设：
一行数据大小为1k,一页中可以存储16行这样的数据。InnoDB的指针占用6个字节，主键即使为bigint,
占用8个字节。
高度为2：
n乘以8+(n+1)乘以6=16乘以1024,算出n约为1170。树的阶数为1171。
1171乘以16=18736，该页可以存储的总行数。
高度为3：
1171乘以1171乘以16=21939856 ,该页可以存储的总行数。

4.一张表，有四个字段(id,username,password,status),由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案：

select id,username,password from tb_user where username = 'jack';
答：给username和password建立联合索引，username作为左边的索引列。这样的话，就只会查询二级索引（联合索引）就能查询到id,username,password的数据。不需要回表查询。

5.索引失效的情况：

(1)在使用联合索引时，不使用最左列会导致整个联合索引的失效，如果跳过某一列，会导致索引的部分失效。
（2）在使用联合索引时，出现范围查询时(<、>),会导致范围查询右侧列的索引失效。业务情况允许下，使用<=、>=可以保证索引不失效。
（3）索引列参与运算(普通运算和函数运算)会导致索引失效。
（4）字符串类型不加单引号，会导致该索引列失效。
（5）使用模糊查询时，头部模糊匹配会导致索引列失效。
（6）or关键字连接条件时，必须左右两边都有索引，不然会失效。
（7）MySQL会根据数据的分布情况考虑，如果使用全表扫描比索引快时，索引也会失效。