【面试题】Mysql 索引面试题

索引作用

索引在MySQL中也叫做“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，能加快数据库的查询速度。
当数据库中数据量很大时，查找数据会变得很慢，我们就可以通过索引来提高数据库的查询效率。

索引类型

normal：表示普通索引
unique：表示唯一的，不允许重复的索引
full text: 表示 全文搜索的索引。搜索很长一篇文章的时候，效果最好。

索引方式

1. BTree
   BTree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=、>、<、>=、<=、between这些比较操作符上，还可以用于like操作符。
   2、Hash
   Hah索引只能用于对等比较，例如=、>、<、>=、<=、between。检索效率远高于BTree索引。

为什么使用BTree比Hash多呢?

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

索引哪些情况下会失效？

（1）对列进行计算或者是使用函数
（2）不匹配数据类型（比如数据库字段是varchar字符类型，但是查询写错，用了数字）
（3）where语句中使用了IS NULL或者IS NOT NULL
（4）当like语句中的%，出现在查询条件的左边时
（5）在WHERE中使用OR时，有一个列没有索引，那么其它列的索引将不起作用
注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
（6）主键字段中使用not in关键字查询数据范围，可以走索引。
而普通索引字段使用了not in关键字查询数据范围，索引会失效。
（7）列对比：user表中id字段和height字段，查询出这两个字段中值相同的记录。
（8）如果sql语句中使用not exists时，索引也会失效。

在实际操作过程中，应该选取表中哪些字段作为索引？

1．选择唯一性索引
2．为经常需要排序、分组和联合操作的字段建立索引
3．为常作为查询条件的字段建立索引
4．删除不再使用或者很少使用的索引