MySQL索引面试题

001什么是索引？

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据。
首先数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，要从500万行数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据。但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。就像我们从一本 500 页的书里面去找特定的一小节的内容，肯定不可能从第一页开始翻。那么这本书有专门的目录，它可能只有几页的内容，它是按页码来组织的，可以根据拼音或者偏旁部首来查找，只要确定内容对应的页码，就能很快地找到我们想要的内容。

002索引的优缺点？

优点
● 提高数据检索的效率，降低数据库IO成本。
● 通过索引对数据进行排序，降低数据的排序成本，降低CPU的消耗。
缺点
● 建立索引需要占用物理空间
● 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

003索引的应用场景？

需要建索引的情况
1. 主键自动创建唯一索引
2. 较频繁的作为查询条件的字段
3. 查询中排序的字段，查询中统计或者分组的字段
不需要建索引的情况
1. 表记录太少的字段
2. 经常增删改的字段
3. 唯一性太差的字段，不适合单独创建索引。比如性别，民族，政治面貌

004索引的数据结构？

索引的数据结构是B+树（加强版多路平衡查找树），原理：如下图，是一颗B+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

查找过程：如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
优点：保证等值和范围查询的快速查找。

005索引数据结构为什么是B+树？

先来说说二叉查找树（BST Binary Search Tree），二叉查找树在数组和链表的基础上整合出来的一个新的数据结构。
1. 二叉查找树（BST Binary Search Tree）
二叉查找树的特点是左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。

比如我们插入的数据是有序的[2、6、11、13、17、22] ，那么这个时候我们的二叉查找树变成了什么样了呢？如下图：

很明显，树变成链表了，因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。
优点：能够实现快速查找和插入。
缺点：树的深度会影响查找效率。
1. 平衡二叉树（Balanced binary search trees）
平衡二叉树又称红黑树，除了具备二叉树的特点，最主要的特征是左右子树深度差绝对值不能超过1。例如我们按顺序插入1、2、3、4、5、6，就会变成如下图：

优点：在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。
缺点：
● 时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。在表数据量大时，查询性能就会很差。
● 平衡二叉树不支持快速的范围查找，范围查找时需要从根节点多次遍历，查询效率不高。

006索引类型有哪些？

1）单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。
主键索引：主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。
唯一索引：索引列的所有值都只能出现一次，即必须唯一，值可以为空。

CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);

普通索引：基本的索可以为空，没有唯一性的限制。

CREATE INDEX account_Index ON `award`(`account`);

2）复合索引：

符合索引遵循索引最左匹配原则，举例：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。
复合索引是包含两个或两个以上字段的索引。

create index a_b_c_index on table1(a,b,c)

创建的联合索引a_b_c_index，实际上相当于建立了三个索引（a）、（a_b）、（a_b_c）。
注意：
● 查询必须从索引的最左边的列开始，否则无法使用索引。比如直接使用b或着c，此时索引会失效。 
● 查询不能跳过某一个索引。比如使用了a索引，但是跳过了b，使用了c，此时只有a索引有用，而c索引失效。 
● 查询中如果使用了范围查询，那么其右侧的索引列会失效。比如a=1 and b>2 and c=3.此时b使用了范围查询，>、like等。c索引列不会起作用。
3） 全文索引
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"我爱学编程尤其是java ..." 通过java，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。一般开发中，不贵用到全文索引，因为其占用很大的物理空间和降低了记录修改性，故较为少用。

007怎么查询SQL语句是否使用了索引查询？

在查询sql前面加一个explain，如explain select ..........

我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引，type结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。其中possible_keys：sql所用到的索引

008什么是聚簇索引？

聚簇索引就是将数据(一行一行的数据)跟索引结构放到一块，innodb存储引擎使用的就是聚簇索引；聚集索引中表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。
而非聚集索引：制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。

009使用聚簇索引的优缺点？

1.由于行数据和聚簇索引的叶子节点存储在一起，同一页（16k）中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中(读取数据是按页读取的)，再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。
3.因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

010为什么推荐使用自增主键作为索引？

主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，出现页分裂（比如之前的索引已经紧凑的排列在一起了，你此时需要在已经紧凑排列好的数据中插入数据就会导致前面已经排好序的索引出现松动和重构排序，但是使用自增id就不会出现这种情况了），导致索引树调整复杂度变大，消耗更多的时间和资源。但是使用自增主键就可以避免出现页分裂，因为自增主键后面的主键值是要比前面的大， 那后来的数据直接放在后面就行；
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但如果是自增的id，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

011什么叫回表？

如果一个查询是先走辅助索引（聚簇索引外的索引都叫辅助索引）的，那么通过这个辅助索引（innodb中的辅助索引的data存储的是主键）没有获取到我们想要的全部数据，那么MySQL就会拿着辅助索引查询出来的主键去聚簇索引中进行查询，这个过程就是叫回表；

012什么是索引覆盖？

所谓的索引覆盖是索引高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。
============
注意：id 字段是聚簇索引，age 字段是普通索引（二级索引）
select id,age from user where age = 30;
上面的这个sql是不用回表查询的，因为在非聚簇索引的叶子节点上已经有id和age的值。所以根本不需要拿着id的值再去聚簇索引定位行记录数据了。也就是在这一颗索引树上就可以完成对数据的检索，这样就实现了覆盖索引。
select id,age,name from user where age = 30;
而上面的这个sql不能实现索引覆盖了，因为name的值在age索引树上是没有的，还是需要拿着id的值再去聚簇索引定位行记录数据。但是如果我们对age和name做一个组合索引idx_age_name(age,name),那就又可以实现索引覆盖了。

013MySQL索引失效的几种情况？

1）like查询以%开头，因为会导致查询出来的结果无序；如：应尽量避免使用模糊查询， like "xxxx%" 是可以用到索引的，like "%xxxx" 则不行(like "%xxx%" 同理)。否则将导致引擎将放弃使用索引而进行全表扫描。
2）类型转换，列计算也会可能会让索引失效，因为结果可能是无序的，也可能是有序的；如：应尽量避免在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为：select id from t where num=100*2
3）在一些查询的语句中，MySQL认为走全表扫描也会导致索引失效；如：应尽量避免使用is null 和is not null 、in和not in，否则将导致引擎将放弃使用索引而进行全表扫描。
对于连续的数值用between就不要用in，如：select id from t where num in(1,2,3) 替换成：select id from t where num between 1 and 3
用exists代替in，如：select num from a where num in(select num from b) 替换成：select num from a where exists(select 1 from b where num=a.num)
4）如果条件中有or并且or连接的字段中有列没有索引，那么即使其中有条件带索引也不会使用索引 (这是因为MySQL判断即便你开始走了索引查询，但是它发现查询中有or ，也就是说or 后面的还是需要走全表扫描（因为or会导致后面的数据是无序的），所以MySQL还不如一开始就直接走全表扫描，这也是为什么尽量少用or的原因)要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引，当检索条件有or但是所有的条件都有索引时，索引不失效，可以走【两个索引】，这叫索引合并（取二者的并集）;
5)复合索引不满足最左原则就不能使用全部索引，如：注意最佳左前缀法则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)。

014谈一下你对MySQL索引的理解？

索引的b+树结构,为什么使用b+树说一下,然后再说一下聚簇索引，回表和索引覆盖；
然后再谈一下索引失效；