什么是索引
- 索引是一种特殊的文件,也占据着物理空间,保存着数据库表中所有记录的引用指针;
- 索引也是一种数据结构,是数据库管理系统用于排序的一种数据结构,可以协助快速查询和更新数据,常用的数据结构有hash和BTREE;
- 通俗的来说,索引就是目录,为了快速找到书中的内容,对书中的内容通过索引来建立目录。
索引优缺点
优点:
- 大大加快了查询的效率;
- 通过使用索引,在查询过程中,可以使用优化隐藏器,提高系统性能。
缺点:
- 索引的创建和维护都需要一定的开销,而且在对数据库表中的数据进行增加,删除,修改时,索引也需要动态的维护,也就可能导致增加,删除,修改的效率下降。
- 索引也占据物体空间。
索引的类型
- 主键索引:数据列不允许为空,不允许重复;
- 唯一索引:数据列不允许重复,但是可以为空;
- 普通索引:既可以重复也可以为空;
- 全文索引:
索引的基本原理
- 先对建立索引的列排序;
- 对排序结果生成倒排表;
- 再在倒排表中加入数据地址链;
- 在查询的时候先拿到倒排表,然后再根据数据地址链就可以查到数据。
使用索引一定能提高查询效率吗
不一定。
索引的创建和维护都需要一定的开销,而且索引还占用一定的物理空间,在对数据库表中数据插入,删除,修改时,还需要对索引进行动态的维护,这就使得在对表中数据修改时还需要修改索引,所以一些不必要的索引反而会使查询速度变慢。
数据量非常大时如何删除
如果直接删除数据,速度会非常慢,因为在删除过程中还需要动态的维护索引,这就使得每次删除操作都会带来更多的开销,数据量本来就非常大,所以不适合直接删除数据。
可以先把索引全部删除,再删除数据,然后再重新建立索引。
最左前缀匹配原则
mysql会一直向右匹配,直到遇到范围查询(> < between like)就停止匹配。
比如:a=1 and b>2 and c=3 ,在遇到b>2时就会停止,就用不到c这个索引。
hash索引
hash索引的底层结构是hash表,在hash查询时,先通过hash计算出键值,然后根据计算出来的键值去原数据表中查找数据,而且由于hash索引建立的索引顺序是无序的,这也就导致:
- hash索引只支持等值查询,不支持范围查询,而B+树是多路平衡搜索树,是有序的,然而在数据库中,范围查询频率很高;
- hash索引建立的索引不能排序;
- hash索引不支持模糊查询和多列索引的最左前缀匹配原则;
- hash索引任意时候都避免不了回表查询,而B+树则有的时候并不需要回表查询,比如索引覆盖和聚簇索引。
- 虽然hash索引等值查询的速度非常快,但是不稳定,如果要查询的键值有很多个,也就是发生了很多冲突,就会影响了hash索引的效率。
B-树
- B-树的非叶子节点既存储索引,也存储数据,所以有可能在非叶子节点就命中,但是不稳定,所以如果把经常查询的数据放在离根节点比较近的节点,就会大大加快查询效率,所以B-树适用于特定值频繁查询的场景。如果想在B-树中全遍历数据就比较麻烦,而且删除和修改跟B+树比起来也很麻烦,主要是因为B-树非叶子节点也存储数据。
B+树
- 空间利用率更高,B+树的非叶子节点只存储键值,并不存储数据,只起一个索引的作用,这就使得B+树的一个节点占用的空间更小,所以在一次IO操作中,数据页中能放更多的节点,减少了IO的操作数,减少了性能消耗,在数据库中,IO是非常耗时的;
- B+树的查询更加稳定,每次都是从根节点出发,到叶子节点才能命中;
- B+树的关键码都在叶子节点,叶子节点又是一条双向链表,所以如果想要全遍历,只需要找到最左侧的叶子节点,然后顺序遍历就可以了,而且由于关键码都在叶子节点,所以关键码的插入和删除也是很方便的。
B+树一个节点的大小为多少合适
为数据页的倍数,不管是大于还是小于数据页的倍数,都会造成空间浪费,所以最好为数据页的倍数。
数据页
- 各个数据页之间是一个双向链表;
- 数据页内部又是一个单链表;
- 数据页会对存储在里面的数据生成一个页目录,如果通过主键查询,会在页目录中用二分查找快速找到对应的槽,然后在槽对应的位置中查找需要的记录;如果是非主键查询,就只能从最小的记录开始遍历单链表。
聚簇索引和非聚簇索引
- 聚簇索引:数值跟索引都存在一块,找到索引也就等于找到数值了,不用再回表查询;InnoDB存储引擎会自动选主键建立聚簇索引,如果没有主键,就选一个唯一键,如果没有唯一键,就默认生成一个键来建立聚簇索引;
- 非聚簇索引:数值跟索引分开,没有存在一起,索引指向数据所在的数据行。
索引覆盖
如果要查询的字段全都建立了索引,就不需要再回表查询,值需要在索引表上查询就可以了。
联合索引
使用多个字段建立一个索引。
在使用时一定要注意查询的字段顺序跟建立索引的字段顺序要一致。
索引的优化
- 尽量不要对索引使用null,null处理起来很麻烦,如果有null值可以用一个特殊值来代替;
- 尽量在重复个数比较少的字段上建立索引;
- 尽量扩展索引,不要建立新的索引;
- 尽量保持索引的“干净”,不要让索引参与运算;
- 索引的名称不要 太长。
in和exists的区别
- in关键字适用于内表比较小的情况,exists适用于外表比较小的情况。
- in关键字对外表和内表建立哈希连接,先去查询内表,然后将内表的查询结果跟外表匹配,可以对外表使用索引,因为使用索引可以加快查询效率,可以用大表,由于内表需要查询,可以使用in来提高效率。
- exists关键字对外表进行loop循环,每次循环都需要查询内表,内表可以使用索引,可以使用大表,由于外表也都需要遍历,可以使用exists来提高效率。
count关键字
- count关键字是用来进行不为null的行数的统计的,有三种用法,count(常量),count(列),
count(*)
,由于常量是一个固定值,所以不会为null,*
可以看成是整行,也不为null,但是列可能为null,由于count(*)
是sql的标准语法,所以mysql对count(*)
进行了很多优化,所以还是建议用count(*)
。 - 优化:如果是MyISAM存储引擎,由于MyISAM不支持事务,只支持表级锁,对表的操作都是串行执行的,不会存在并发的修改表中记录的问题,所以查询到的数据时准确的,所以MyISAM会将行数缓存起来,等
count(*)
的时候直接返回即可,前提是不带where条件;如果是InnoDB存储引擎,由于InnoDB支持事务,也支持行级锁,所以就不能采用缓存的方式,由于我们只关心行数,不关心查到的具体的数据,所以可以选一个成本比较低的索引进行查询,InnoDB存储引擎中可分为两种索引,一种是聚簇索引,一种是非聚簇索引,由于聚簇索引的叶子节点还保存数据,所以成本比较高,就选一个非聚簇索引来进行遍历。
Mysql中为什么不允许为null
null占用更多的空间,而且还会产生很多与预期不相符的情况。