索引

什么是索引

• 索引是一种特殊的文件，也占据着物理空间，保存着数据库表中所有记录的引用指针；
• 索引也是一种数据结构，是数据库管理系统用于排序的一种数据结构，可以协助快速查询和更新数据，常用的数据结构有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占用更多的空间，而且还会产生很多与预期不相符的情况。