什么是索引?有什么作用?优缺点?
索引是数据库中用于提升数据检索效率的一种数据结构,可以将索引看作一本书的目录,可以利用目录查找到想要的数据而不是翻阅整本书找到想要的内容。而索引就是数据库中数据的目录。
能够提升数据检索的效率,并且索引可以用在优化器中,比如索引下推,覆盖索引,都能提升数据检索的性能。
缺点:索引可以提升检索性能,但是也会占用额外的内存空间,并且维护索引也需要额外的工作量。
索引的分类:可以根据索引使用的数据结构、存储方式、索引字段、索引的字段特性来进行分类。
数据结构: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、经常被用于连接的字段