Mysql索引类面试题简单汇总+简单调优实践

前言

面试题都是从各大平台论坛收集整理而来，并且包含了本人一些调试和实践，侵删。

什么是索引？

1、索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。

2、索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中的数据。索引的实现通常是B树以及其变种B+树。（MySQL的索引虽然显示的是BTREE，但实际上它是B+树实现的。）

3、用大白话说，索引就相当于目录，为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，是需要一定的物理空间的。

索引的优缺点？

优点

1、可以极大的提高数据检索速度，这也是创建索引的最主要原因。

2、通过使用索引，可以在查询的过程中，使用优化隐藏，提高系统的性能。优化隐藏指的是不使用MySQL的优化策略，而是手动指明要走哪个索引（force）

缺点

1、时间：创建索引和维护索引也需要消耗时间，具体点来说，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增删改的执行效率。

2、空间：索引需要占用物理空间。

索引的使用场景

where

// 持续时间 执行的语句
3.33992200 SELECT * FROM easygo.comment where comment_belong_area_id ="3123944778200_1000"
0.00151375 SELECT * FROM easygo.comment where comment_belong_area_id ="3123944778200_1000"

order by

顺便一提，order by 会出现不走索引的情况，使用explain分析就清楚了。

SELECT * FROM easygo.comment order by comment_good;

可以看到，明明已经建立了索引，但是MySQL却没有选择走索引。执行速度也是非常慢。

这时候可以选择强制走索引。

SELECT * FROM easygo.comment force index(good) order by comment_good

20.18633425	SELECT * FROM easygo.comment order by comment_good
12.39368875	SELECT * FROM easygo.comment force index(good) order by comment_good

可以看到性能提升非常明显。

join

对join语句匹配关系（on）涉及的字段建立索引能够提高效率。两条相同的sql，在建立索引前后性能有质的飞跃。

3.80902700	0.00773625

SELECT 
    *
FROM
    user_info AS t2
        left JOIN
    comment AS t1 ON (t1.comment_good = t2.id)

覆盖索引

覆盖索引不是一种索引。

覆盖索引指的是当你执行查询语句时，select后应该只选择需要的变量，以提高索引的覆盖率，只要有一条语句没有索引，那么就会执行全表扫描。所以一般情况下最好不要使用select * from xxx_tb；

explain SELECT comment_good FROM easygo.comment

explain SELECT * FROM easygo.comment

可以看到，select *会执行全表扫描。（type ALL）

索引有哪几种类型？

主键索引：数据列不允许重复，不允许为空，一张表只能有一个主键。需要注意，如果你制定了两个字段为PRIMARYKEY，那只说明你创建的是联合主键，而不是有两个主键。可以通过workbench去查看表的DDL——PRIMARY KEY (`id`,`create_time`)；

唯一索引：数据列不允许重复，不允许为空，可以有组合唯一索引，即UNIQUE（c1,c2）。

普通索引：基本的索引类型，没有唯一性限制，允许为空。

全文索引：目前搜索引擎使用的一种关键技术。select * from articles where match(title, body) against ('MySQL数据库');

索引的数据结构

主要有B树和Hash。InnoDB默认使用BTREE（实际上是B+树）来实现。

查询方式：主键索引：按主键查询。

                  普通索引：找到主键后再按主键查询。

B+树：

1、每个结点的关键字个数与孩子个数相等，所有非最下层的内层结点的关键字是对应子树上的最大关键字，最下层内部结点包含了全部关键字。

2、所有叶子节点中包含了全部关键字信息以及指向含这些关键字信息的指针。且叶子节点本身依关键字的大小而自小而大顺序排序链接。

3、所有的非叶子节点可以看成是索引部分，节点中仅含有其子树中的最大（或最小）关键字。

4、B+树中，数据对象的插入和删除仅在叶子节点上进行。

5、B+树有两个头指针，一个树的根节点，一个是最小关键字的叶节点。

哈希索引：

简单来说，类似于数据结构中简单实现的Hash表一样，使用hash索引时，主要是通过Hash算法将数据库字段转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置，如果发生hash碰撞，则在对应的Hash键下以链表的形式存储。

索引的基本原理

索引用来快速地找到那些具有特定值的记录，如果没有索引，一般来说执行查询时遍历整张表。

索引的原理其实很简单，就是把无序的数据变为有序。

1、把创建了索引的列的内容进行排序。

2、对排序结果生成倒排表。

3、在倒排表内容上拼接数据地址链。

4、在查询的时候，先拿到倒排表的内容，再取出数据地址链，从而拿到具体的数据。

有哪些索引算法？

BTree算法

可以用在=、>、<、>=、<=、between上，也可以用在like操作符，只要查询条件是一个不以通配符开头的常量。

'abc%' 可以；’%abc‘ 不行；

Hash算法

只能用于对等比较，由于是一次定位数据，检索效率远高于BTree索引。innodb_adaptive_hash_index 打开自适应hash索引，默认开启。

索引的设计原则

1、适合所以的列是出现在where子句中的列，或者连接子句中指定的列。

2、基数较小的列，索引效果较差，没必要建立索引。

3、使用段索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间。

4、不要过度索引。索引会消耗额外的磁盘空间，并降低写性能，索引越多，写操作的时间就会越长。

创建索引的原则

1、最左前缀匹配原则，MySQL会一直向右匹配直到遇到返回查询就停止匹配。

2、较频繁作为查询条件的字段才建立索引。

3、更新频繁的字段不适合创建索引。

4、区分度不高的字段不适合创建索引，比如性别。

5、尽量去扩展索引而不是新建索引，比如原先有A的索引，要增加（A，B），只需要修改原先的即可。

6、定义有外键的数据列一定要建立索引。

7、对于查询中很少涉及的列、重复值多的列不要建立索引。

8、对于定义为text，image，bit数据类型的列不要建立索引。

创建索引的三种方式

1、创建表时建立。

2、使用ALTER  TABLE增加索引。

3、使用CREATE INDEX建立。

删除索引

1、根据主键名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

2、删除主键索引，alter table 表名 drop primary key。如果主键自增长，则需要先取消自增长才能删除主键索引。

创建索引时应该要注意的地方

1、应该指定列为NOT NULL，除非你想储存NULL。在MySQL中，含有空值的列很难进行查询优化，因为他们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊数值、或者是一个空字符串来代替NULL。

2、取离散程度大的字段放到索引的前面，可以使用count()函数来查看字段的差异值，数值越大差异越大。

3、索引字段越小越好，数据库的数据存储以页为单位，一页存储的数据越多，一次IO操作获取到所需数据的概率越大。

使用索引一定能提高查询性能吗？

通常来说，使用索引来查询会比全表扫描更快。（当然在数据量很少的情况下，全表扫描会更快）

原因不难理解

索引需要空间来维护，也需要定期维护，每当有记录在表中进行增减或索引列被修改时，索引本身也会被修改。因此每次cud操作都要比没有索引时耗费更多的时间，一些不必要的索引可能会占用本该用来查询的资源。

百万级别或以上的数据如何删除？

MySQL的官方手册告诉我们，删除数据的时间和创建索引的数量成正比。

删除步骤如下：

1、先删除索引

2、删除其中不想要的数据

3、重新创建索引

前缀索引

为什么要有前缀索引？前面已经讲到，段索引有利于维护。

语法：index(field(10))，使用字段的前十个字符建立索引，默认使用全部的字符。

前提是前缀的辨识度高，也就是离散程度大。比如密码，一般来说，密码的前缀几乎不可能相同。

可以利用select count(*)/count(distinct left(password,preFixLen))，通过调整profixlen的值（从1开始自增），查看不同长度前缀的平均匹配度，接近1时即可。这表示密码的前prefixlen几乎可以唯一确定一条记录。

什么是最左前缀匹配原则？

顾名思义，就是最左优先匹配，在创建多列索引时，要根据业务要求，where子句中使用最频繁的一列放在最左边。

=和in可以乱序，MySQL的查询优化器会自动优化成所以可以识别的样子。

B树和B+树的区别

在B树中，可以将键和值放在内部节点和叶子节点，但是在B+树中，内部节点没有值，叶子节点同时存放键和值。

B+树的叶子节点有一条链相连，而B树叶子节点各自独立。

使用B树的好处

B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

使用B+树的好处

由于B+树内部节点只存放键，不存放值，因此，一次读取可以在内存分页中获取更多的键，有利于更快的缩小范围。B+树的叶节点有一条链相连，因此，当需要进行一次全表扫描时，B+树只需要使用O(logN)时间查找到一个最小节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这需要更多的内存置换次数，因此也就需要花费更多的时间。

hash索引和B+树有什么区别或者优劣势？

首先要直到Hash索引和B+树索引的底层实现原理：

hash所以的底层实现就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询货的实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询是从根节点出发，查找到叶子节点方可获得所查的键值，然后根据查询判断是否需要回表查询数据。

不同之处：

1、hash索引进行等值查询速度更快（一般来说），但是不能进行范围查询，原因显而易见，hash算法导致索引顺序和数据顺序毫无关系。B+树是有序的，天然支持范围。

2、Hash索引不支持使用索引进行排序，原因同上。

3、Hash索引不支持模糊查询以及多列索引的最左前缀匹配，原因也是因为hash函数的不可预测，AAAAA和AAAAB的hash值并没有相关性。

4、hash索引任何时候都不能避免回表查询数据，而B+树在符合条件（聚簇索引，覆盖索引...）的情况下可以只通过索引完成查询。

5、hash索引虽然在等值查询上速度快，但是并不稳定，性能不可预测，当某个键值大量重复时，会发生碰撞，导致hash表退化成一个链表，此时效率极差。而B+树的查询效率比较稳定。

综上所述，在大多数情况下，直接选择B+树可以获得稳定且较好的查询效率，而不需要使用hash索引。

数据库为什么使用B树而非B+树？

1、B树适合随机检索，而B+树适合顺序检索和随即检索。

2、B+树空间利用率更高，可以减少IO次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样就导致索引查找过程中要乘胜磁盘IO消耗。B+树的内部结构并没有指向关键字的具体信息的指针，只是作为索引使用，其内部节点比B树小，盘块能够容纳的节点中关键字的数量更多，一次性读入内存可以查找的关键字也就更多，相对的，IO操作的次数也就降低了，而IO操作是影响检索效率的最大因素。

3、B+树的查询效率更加稳定。B树搜索有可能会在非叶子节点结束，越靠近根节点的数据查询速度越快，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集中做一次二分查找。而在B+树中，顺序检索比较明显，随即检索时，任何一个关键字的查找都必须走一条从根节点到叶子节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。

4、B树在提高了磁盘IO性能的同时并没有解决元素遍历效率低下的问题。B+树因为叶子节点都被连接起来了，只需要遍历叶子节点就可以完成遍历。而且在数据库中基于范围的查询是非常频繁的，而B树的范围查询比B+树会有更多的IO消耗。不能理解的话可以看看这个https://zhuanlan.zhihu.com/p/54102723。

5、增删节点时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可以很好的提高增删效率。（叶子节点有指针的存在，向兄弟节点借元素时，不需要通过父节点了，而是可以直接通过兄弟节移动即可（前提是兄弟节点的元素大于m/2），然后更新父节点的索引；如果兄弟节点的元素不大于m/2（兄弟节点也没有多余的元素），则将当前节点和兄弟节点合并，并且删除父节点中的key）

B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据

B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前key值以及整行的数据，这就是聚簇索引和非聚簇索引。（聚簇索引并不是一种索引类型，只是一种存储方式。当表有了聚簇索引的时候，表的数据行都存放在索引树的叶子页中。无法把数据行放到两个不同的地方，所以一张表只允许有一个聚簇索引）在innoDB中，只有主键是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式生成一个键来建立聚簇索引。

当使用聚簇索引时，在对应的叶子节点可以获取到整行的数据，因此不用再回表查询。

什么是聚簇索引？何时使用聚簇索引与非聚簇索引？

聚簇索引：将数据和索引放到一起，找到索引也就找到了数据。

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过这个key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应的数据，这也就是为什么索引未在key_buffer命中时候速度慢的原因。

在InnoDB中，在聚簇索引之上创建的索引被称为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，比如复合索引，前缀索引，唯一索引...辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

非聚簇索引一定会回表查寻吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必在进行回表查寻。

假设我们已经在age上建立了索引，那么select age from employee where age<20的查询时，在索引的叶子节点上已经包含了age信息，那么就不会再次进行回表查寻。

联合索引是什么？为什么需要注意联合索引中的顺序？

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个引用，否则无法命中内存。

具体原因为：

MySQL使用索引时需要索引有序，假设现在建立了”name,age,school“的联合索引，那么索引的排序为：先按照name排序，如果name相同，则按照age排序，如果age值也相等，则按照school排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特里的查询或者表结构进行单独调整。