索引面试题

1.索引分类

分类角度	索引类型
数据结构	Hash，B树，B+树等
存储层面	聚簇索引，非聚簇索引
逻辑层面	主键索引，唯一索引，普通索引等

2.索引

2.1索引：Hash

可以快速的精确查询，但是不支持范围查询。

2.2索引：B树

B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素。

2.3索引：B+树

B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。

2.3.1一个B+树的节点中到底存多少个元素最合适你有了解过么？

B+树中一个节点为一页或页的倍数最为合适。

因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费。
如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费。
所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

2.3.2 页

Mysql的基本存储结构是页(记录都存在页里边)

• 各个数据页可以组成一个双向链表
• 而每个数据页中的记录又可以组成一个单向链表
  每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位指定的记录；以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

select * from user where username='丙丙’这样没有进行任何优化的sql语句。默认会这样做：

1. 需要遍历双向链表，找到所在的页
2. 从所在的页内中查找相应的记录（由于不是根据主键查询，只能遍历所在页的单链表了）

3.回表&&覆盖索引

select * from table where name = ‘丙丙’

回表过程：
执行的流程是先查询到name索引上的“丙丙”，然后找到他的id是2，最后去主键索引，找到id为2对应的值。

select id,name from table where name = ‘丙丙’

覆盖索引:
如果只查询ID那，在Name字段的索引上就已经有了，那就不需要回表了。

4.最左匹配原则

索引可以简单如一个列 (a)，也可以复杂如多个列 (a,b,c,d)，即联合索引。
如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)

假如建立联合索引（a,b,c）

1. 全值匹配查询时

select * from table_name where a = ‘1’ and b = ‘2’ and c = ‘3’
select * from table_name where b = ‘2’ and a = ‘1’ and c = ‘3’
select * from table_name where c = ‘3’ and b = ‘2’ and a = ‘1’

用到了索引。where子句几个搜索条件顺序调换不影响查询结果，因为Mysql中有查询优化器，会自动优化查询顺序

2. 匹配左边的列时

select * from table_name where a = ‘1’
select * from table_name where a = ‘1’ and b = ‘2’
select * from table_name where a = ‘1’ and b = ‘2’ and c = ‘3’

用到了索引

select * from table_name where b = ‘2’
select * from table_name where c = ‘3’
select * from table_name where b = ‘1’ and c = ‘3’

这些没有从最左边开始，最后查询没有用到索引，用的是全表扫描

select * from table_name where a = ‘1’ and c = ‘3’

如果不连续时，只用到了a列的索引，b列和c列都没有用到

3. 匹配列前缀

select * from table_name where a like ‘As%’; //前缀都是排好序的，走索引查询
select * from table_name where a like ‘%As’//全表查询
select * from table_name where a like ‘%As%’//全表查询

4. 匹配范围值

select * from table_name where a > 1 and a < 3

可以对最左边的列进行范围查询

select * from table_name where a > 1 and a < 3 and b > 1;

多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到B+树索引，也就是只有a用到索引，在1<a<3的范围内b是无序的，不能用索引，找到1<a<3的记录后，只能根据条件 b > 1继续逐条过滤

5. 精确匹配某一列并范围匹配另外一列(如果左边的列是精确查找的，右边的列可以进行范围查找)

select * from table_name where a = 1 and b > 3;

a=1的情况下b是有序的，进行范围查找走的是联合索引

6. 排序

select * from table_name order by a,b,c limit 10; //用到索引
select * from table_name order by a limit 10; //用到部分索引
select * from table_name order by a,b limit 10; //用到部分索引
select * from table_name order by a limit 10; //全表查询
select * from table_name order by a,b limit 10; //全表查询

select * from table_name where a =1 order by b,c limit 10;//联合索引左边列为常量，后边的列排序可以用到索引

来源：https://blog.csdn.net/qq_35190492/article/details/104346265
来源；https://blog.csdn.net/sinat_41917109/article/details/88944290