mysql索引面试题

mysql索引

名词解析
首先先理清楚一个概念 什么是聚簇索引 什么是非聚簇索引？
聚簇索引

数据和索引在一棵b+树上的叫做聚簇索引，比如innodb的主键索引就是一个聚簇索引，他的叶子结点存放了整个表的记录，而mysam的则是非聚簇索引，通过文件就能看出，mysam 索引文件和数据文件是分开的，一张表最多只会有一个聚簇索引，innodb优先选择主键索引当作聚簇索引，如果没有主键，就选择唯一索引，如果没有唯一索引，就会选择默认的ROW_ID 6位随机数当作聚簇索引

非聚簇索引

数据和索引不在一棵b+树上的叫做非聚簇索引，也叫做二级索引，在innodb中，二级索引叶子节点存放的是该条数据的主键和索引列，要查找其他数据需要再去聚簇索引查找一遍，这就是回表，我们应当尽量减少回表

注：在mysam中聚簇索引和非聚簇索引其实并没有太大的区别，几乎可以说是一样

1. 回表

需要通过非聚簇索引去查询聚簇索引,叫做回表
例如：id name age 三个字段
id是主键索引 ，name是普通索引
会有2颗b+树 ：
一个是以id为索引的聚簇索引
一个是以name为索引的非聚簇索引
sql语句 select id,name,age from table where name=zhangsan
会先通过非聚簇索引查找，叶子节点只会存在一个主键id(这一步只能获取到id,name)
然后通过id 去查找聚簇索引，叶子结点有整条数据，然后取出age(而这一步就叫做回表)
但是如果sql是
select id,name from table where name=zhangsan
这样只需要去查询非聚簇索引即可获取到我们想要的数据就直接返 回给我们，也就不产生回表了
解决办法： 假如我们把name索引改成 联合索引，name+age
这样在产生的非聚族索引中我们就能找到id，name，age三个字段值而返回

2. 索引覆盖

以上回表说的解决办法 就是索引覆盖
常见的方法是：将被查询的字段，建立到联合索引里去。

3.索引下推 简称ICP

这里是引用Mysql5.6及之后版本新增的，通俗的讲就是
在不使用ICP的情况下，在使用非主键索引进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器去判断数据是否符合条件 。
在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
关闭索引下推 set optimizer_switch=‘index_condition_pushdown=off’; 不建议关