MySQL索引问答

什么是索引？有什么作用？优缺点？

索引是数据库中用于提升数据检索效率的一种数据结构，可以将索引看作一本书的目录，可以利用目录查找到想要的数据而不是翻阅整本书找到想要的内容。而索引就是数据库中数据的目录。

能够提升数据检索的效率，并且索引可以用在优化器中，比如索引下推，覆盖索引，都能提升数据检索的性能。

缺点：索引可以提升检索性能，但是也会占用额外的内存空间，并且维护索引也需要额外的工作量。

索引的分类：可以根据索引使用的数据结构、存储方式、索引字段、索引的字段特性来进行分类。

数据结构：B树索引、hash索引、

数据存储方式：聚簇索引和非聚簇索引

字段特性：主键索引、唯一索引（索引列的值必须唯一）、普通索引（普通字段上的索引，既不要求是主键，也不要求值唯一）、前缀索引（仅用字段的前一部分作为索引，减少索引使用的空间）

字段个数：单列索引和联合索引

创建索引的命令？

答：create INDEX indexName on 表名(column1,column2)

什么时候不需要索引？

答：在where、order by 、group by条件中用不到的列可以不用索引，因为索引的作用是快速定位，如果不需要快速定位，没必要浪费多余的内存去存储索引数据；数据量太少时也不需要建立索引；大量数据重复时也没必要创建索引，比如gender的男和女；需要经常更新的字段也可以不建索引，因为维护会花费很多时间。

索引可以怎样优化？

答：使用前缀索引、覆盖索引、防止索引失效、主键最好使用自增。

如果主键不是自增而是随机的，每次新增一个数据可能就不是直接加在最后，而是得插在一些数据中间，可能需要复制数据，甚至产生内存碎片。

索引失效的情况及原因：

1、使用左或者左右模糊匹配。B+树按照索引值有序排列，只能根据前缀进行比较。

2、对索引字段进行类型转换、计算、函数等操作。索引字段保存的是原始值，而不是做出转换计算之后的值。

3、联合索引中不符合最左匹配原则。联合索引的情况下，数据首先按索引第一列排序，第一列数据相同的再按第二列排序。所以查询条件中的各个列必须是联合索引中最左边开始连续的列。

4、where子句中，如果在or之前的条件列是索引列，or之后的不是索引列，也会导致索引失效。or的含义是只要满足一个条件就行，所以只有一个条件是索引列是没有意义的，只要有条件列不是索引列，就会走全表扫描，导致索引失效。

innodb是如何存储数据的？

答：innodb采用B+树作为索引结构，按照数据页进行读写数据，一个页的大小是16k，也就是说每次读数据并不是只查询一条记录，而是从磁盘中读取一个数据页的信息到内存中。数据页之间通过双向链表连接，便于范围查询。

B+树是如何进行查询的？

答：磁盘的IO次数对索引的效率至关重要，所以更倾向于矮胖型的数据结构。 
B+树的结构是每个节点都是一个数据页，但是只有叶子节点才会存放数据，非叶子节点存放的是数据页的目录项作为索引，非叶子节点通过分层来降低每一层的搜索量，所有节点按照索引的顺序，构成双向链表，便于范围查询。

如果叶子节点放的是具体的数据，那么就是聚簇索引（主键索引），如果存放的是主键值，就是二级索引。一个表中只能有一个主键索引，但是可以有多个二级索引。

为什么用B+树作为索引结构？

答：磁盘IO的速度比内存读取数据的速度慢很多，所以希望能够尽可能减少磁盘IO的次数，以提升数据查询的效率。

既要高效地查询某一条记录，也要能够进行高效的范围查询。

二分查找：提升查找效率，但新插入一个数据需要把后面所有数据都后移，效率太低。

二分查找树：左子树的节点都小于这个节点，右子树的节点都大于这个节点。但如果每次插入都是最大的元素，会导致二叉树变成一个链表，查找的时间复杂度变成O(n)。

自平衡二叉树：保证左右子树的高度差不超过1，通过旋转保持平衡。但也正是因为这样，进行大量复制旋转降低了数据库性能。且只有两个子节点，所以高度仍然会较高，需要多次IO。

B树：多路平衡查找树，它允许每个节点有M个子节点，M称为B树的阶。

B+树：跟B树差不多，但是B+树只会在叶子节点存放数据，非叶子节点存放的是目录页作为索引，而B树的非叶子节点也存放数据，这样可能会导致树的高度更高。并且B+树作为索引只把数据存储在叶子节点，并且非叶子节点会有冗余，数据在进行删除和插入的时候也会更简洁。

MySQL中的B+树，叶子节点之间通过双向链表进行连接，这样既能向左遍历，也能向右遍历，能够进行范围查询。

(B+树使用了多个子节点，这样能够在一定程度上缓解树的高度，减少查询时的IO次数；此外，只有在叶子节点才会存放数据，非叶子节点存放的是目录项，这样对于B树来说，也能够减少树的高度，对于数据的增删也更便捷；B+树的叶子节点和非叶子节点之间双向链接，便于范围查询。总的来说有利于快速查找数据，减少磁盘IO的次数。)

B+树是三层的时候，数据量刚好是2000万左右，如果增加到4层，IO次数会增加，且数据量也会非常庞大，不现实。而且这个也只是最大值，可能会影响查询性能。

正确使用索引的建议：

1、不为null的字段

2、被频繁查询的字段

3、需要被排序的字段

4、经常被用于连接的字段