Mysql索引的常见问题

1.Mysql索引方面常见问题

1.索引的优点和缺点

优点：

• 可以大大加快数据的检索速度

缺点：

• 时间方面：创建索引和维护索引需要消耗时间
• 空间方面：索引需要占物理空间

2.哪些情况下需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段
3. 多表关联查询中的关联字段
4. 排序的字段
5. 频繁查找的字段，需要覆盖索引
6. 查询中统计或者分组字段

3.哪些情况下不需要创建索引

1. 表记录太少
2. 经常进行增删改查操作的字段
3. where条件里使用频率不高的字段

4.创建索引的几种方式

1. 创建表时添加索引
2. 使用alter table命令
3. 使用create index命令

5. mysql有几种索引类型

1. fulltext 全文索引
2. hash 哈希索引
3. btree b树索引
4. rtree r树索引

6.为什么索引结构默认使用b+tree，而不是btree，二叉树，红黑树？

1. btree：因为btree不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少，指针少的情况下要保存大量的数据，只能增加数的高度，导致IO操作变多，查询性能贬低
2. 二叉树：树的高度不均匀，不能自平衡，查找效率和树的高度有关，IO代价高
3. 红黑树：树的高度随着数据量的增加而增加，IO代价高
4. 总的来说，影响mysql查找性能的主要还是磁盘IO的次数，大部分是刺头移动到指定磁道的时间花费，树越高，IO代价越高，查询时间越长

7.hash索引和btree索引的优缺点

优点

1. 单条数据查询时，hash索引查询时间复杂度为O(1)，b数索引时间复杂度为O(logN)。

缺点

1. hash索引只适合等值查询，但是无法进行范围查询
2. 哈希索引没有办法利用索引完成排序
3. 哈希索引不支持联合索引的最左匹配规则
4. 如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞

8.索引种类

1. 普通索引：提高查询速度
2. 唯一索引：提高查询速度 + 列值唯一 + 可以为null
3. 主键索引：提高查询速度 + 列值唯一 + 不可以为null
4. 组合索引：多个字段组合的索引，效率大于索引合并
5. 全文索引：对文本内容分词搜索

9.为什么建议使用主键自增索引？

由于b+树是有序的，如果不是自增，那么需要将下面的叶子节点进行移动，腾出位置来插入数据，这样就会比较耗时间，如果刚好数据满了，还需要进行页分裂操作。
但是如果我们的主键是自增的，每次插入的ID都会比前面的大，那么我们每次只需要在后面插入就行，不需要移动位置，分裂等操作，这样就可以提高性能

10.b+tree的叶子节点可以存哪些东西？两者的区别是什么？

innodb的b+tree可能存储的是整行数据，也有可能是主键的值。
如果索引的叶子节点存储了整行数据的主键索引，也被称为聚簇索引。
如果索引的叶子节点存储了主键的值的非主键索引，也被称之为非聚簇索引。

11.那么聚簇索引（主键索引）和非聚簇索引（非主键索引），在查询数据的时候有区别吗？为什么？

聚簇索引查询更快，因为聚簇索引的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值之后，还需再通过主键的值进行一次查询。

12.非主键索引是否一定需要回表查询？

如果是覆盖索引就不需要回表。覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取，也可以称之为实现了索引覆盖。

13.何时使用聚簇索引和非聚簇索引？

动作	使用聚簇索引	使用非聚簇索引
列经常被分组排序	Y	Y
返回某范围内的数据	Y	N
一个或极少不同值	N	N
小数目的不同值	Y	N
大数目的不同值	N	Y
频繁更新的列	N	Y
外键列	Y	Y
主键列	Y	Y

14.创建联合索引的时候，联合索引多个字段顺序是如何选择的呢？为什么？

按照识别度由高到低一次排序，为了最左前缀匹配。
当创建一个key1、key2、key3的联合索引时，相当于创建了key1、key1-key2、key1-key2-key3三个索引。

15.mysql5.6对索引做什么优化？

index Condition Pushdown 索引下推

16.如何知道有没有走索引查询？

可以通过explain查询sql语句的执行计划，通过执行计划来分析索引使用情况。

17.explain执行计划中你关注哪些字段？

1. type：表的链接类型（const > eq_ref > ref > range > index > all）
2. key： 表实际使用的索引
3. key_len：索引字段的字节数，字符长度*3 ，变长类型+2，可null+1
4. rows：扫描出来的行数（估算的行数）
5. extra：执行情况和描述说明

18.什么情况下明明创建了索引，但是执行的时候并没有通过索引？

1. like通配符在前面
2. or左右两边有非索引列
3. 使用了not、not in 、not like等负向查询
4. 索引字段可以为null，查询使用了is null 或者is not null
5. 隐式类型转换
6. 索引列使用了内置函数
7. 对索引列直接运算
8. 违背联合索引最左匹配原则
9. mysql优化器的最终选择

2.mysql四种索引类型的区别以及适用场景

1.fulltext

即全文索引，目前只有MyISAM引擎支持。其可以在ceate table、alter table、create index使用，不过目前只有char、varchar、text列上可以创建全文索引。值得一提的是，在数据量较大的时候，先将数据放入一个没有全局索引的表中，然后再用create index 创建fulltext索引，要比先为一张表建立fulltext索引然后再将数据写入的速度快很多。

2.hash

hash是一种key->value形式的键值对，如数学中的函数映射，允许多个key对应相同的value，但不允许一个key对应多个value。正式由于这个特性，hash很适合做索引，为某一列或几列建立hash索引，就会利用这一列或几列的值通过一定的算法计算出一个hash值，对应一行或几行的数据。在java语言中，每个类都有自己的hashcode方法，没有显示定义的都继承自object类。该方法使得每一个对象都是唯一的，在进行对象间equals比较，和序列化传输中起到了很重要的作用。hash的生成方法有很多种，足可以保证hash码的唯一性。
由于hash索引可以一次定位，不需要像树形索引那样逐层查找，因此具有极高的效率。那为什么还需要其他的树形索引呢？
hash索引有以下问题

1. hash索引仅仅能满足 = ，in , <=>等精确查询，不能使用范围查询。
   由于hash索引比较的是进行hash运算之后的hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的hash算法处理之后的hash值的大小关系，并不能保证和hash运算前完全一样。
2. hash索引无法被用来避免数据的排序操作。
   由于hash索引中存放的是经过hash计算之后的hash值，而且hash值的大小关系并不一定和hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算
3. hash索引不能利用部分索引键查询
   对于组合索引，hash索引在计算hash值的时候是组合索引键合并后再一起计算hash值，而不是单独计算hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，hash所以也无法被利用。
4. hash索引在任何时候都不能避免表扫描
   hash索引是将索引键通过hash运算之后，将hash运算结果的hash值和所对应的行指针信息存放于一个hash表中，由于不同索引键存在相同的hash值，所以即使取满足某个hash键值的数据的记录条数，也无法从hash索引中直接完成查询。还是要通过访问表中的实际数据进行相应的比较，并得到响应的结果。
5. hash索引遇到大量hash值相等的情况后性能并不一定就会比btree索引高。
   对于选择性比较低的索引键，如果创建hash索引，那么将会存在大量记录指针信息存于同一个hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能地下。

3.btree

btree索引就是一种将索引值按一定的算法，存入一个树形的数据结构中。每次查询都是从数的入口root开始，依次遍历node，获取叶节点。
btree在myisam里的形式和innodb稍有不同。
在innodb里，有两种形态：一是primary key形态，其叶节点里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其叶节点和普通的btree差不多，只是还存放了指向主键的信息。
在myisam里，主键和其他的并没有太大的区别。不过叶节点里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息

4.rtree

rtree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有mysiam、bdb、innodb、ndb、Archive几种。
相对于btree、rtree的优势在于范围查找。

5.各种索引的使用情况

1. 对于btree这种mysql默认的索引类型，具有普遍的适用性
2. 由于fulltest对中文支持不是很好，在没有插件的情况下，最好不要使用。其实，一些小的博客应用，只需要在数据采集时，为其建立关键字列表，通过关键字索引，也是一个不错的方法。
3. 对于一些搜索引擎级别的应用来说，fulltext同样不是一个好的处理方法，mysql的全文索引建立的文件还是比较大的，而且效率不是很高，即便是使用了中文分词插件，对中文分词支持也只是一般。真要碰到这种问题，Apache的Lucene或许是好的选择。
4. 正式因为hash表在处理较小数据量时具有无可比拟的优势，所以hash索引很适合做缓存。

3.索引下推（Index Condition Pushdown）

不使用索引条件下推优化时的查询过程：
获取下一行，首先读取索引信息，然后根据索引将整行数据读取出来。然后通过where条件判断当前数据是否符合条件，符合返回数据
使用索引下推优化时的查询过程
获取下一行的索引信息。 检查索引中存储的列信息是否符合索引条件，如果符合将整行数据读取出来，如果不符合跳过读取下一行。 用剩余的判断条件，判断此行数据是否符合要求，符合返回数据。
使用场景

1. 非聚簇索引，聚簇索引已经有整行数据，不需要回表
2. 减少回表次数，特别对于like通配符来说，减少效果明显

如何判断
使用explain进行分析时，如果Extra显示Using index condition，说明使用了索引下推

4.图解为什么建议使用自增长主键作为索引

结合b+tree的特点，自增主键时连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少的一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

插入连续的数据：

插入不连续的数据

5.hash索引、btree索引、b+tree索引存储示例图

1.hash索引

2.btree索引

3.b+tree索引

6.思维导图