数据库的索引

数据库的索引

引入

B树和B+树（B树，B+树都是平衡树）这两种实现索引方式最常见

Mysql的innodb用B+树做索引

Mysql的myisam的索引有两种：主索引和辅助索引，主索引使用具有唯一性的键值，辅助索引键值可以重复。和innodb不同的是，最后的叶子节点存的是地址，而innodb最后的叶子节点村的是完整的数据。

InnoDB

聚簇索引

平时建表的时候都会为表加上主键， 在某些关系数据库中， 如果建表时不指定主键，数据库会拒绝建表的语句执行。一个没加主键的表，它的数据是无序的放置在磁盘存储上，一行一行的排列很整齐，类似于我们平常见到的“表”。但如果给一个表加上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构（平衡树结构），那么整个表就变成了一个索引，我们称之为聚簇索引（clustered index），这也是为什么一个表只能有一个主键，一个表只能有一个聚簇索引，**因为主键的作用就是把表的数据格式转换成“索引（平衡树）”的格式来放置。

如上图所示，其中树的所有节点（叶子节点除外）的数据都说是由主键字段中的数据（比如主键ID）构成。叶子节点中储存的是真正表中的数据（具体看下图）。

若我们执行

select * from table where id = 125;

首先根据索引定位到125这个值所在的叶结点，然后再通过叶结点取到id等于125的数据行。这棵树几层，就只需要几次查找。加入一张表有1亿条数据，需要找到其中某一条数据，按照常规做法，就需要一条一条去匹配，最坏的情况下就需要匹配1亿次才能找到结果。但是把这张表转换成平衡树的结构，想象一下这棵树非常茂盛，节点非常多。假设这棵树有10层，那么只需要10次IO开销就能查找到所需要的数据，速度以指数级别提升。

因此，索引能让数据库查询数据的速度上升，但让写入数据的速度下降。

原因：由于平衡树这个树的结构必须维持在一个正确的状态，增删改数据都会改变平衡树各节点中的索引内容，破环树结构，因此，在每次数据改变时，DBMS必须重新梳理树的结构来确保它的正确，这样就会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。

非聚簇索引

非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。 如下图

因此，每次给字段建一个新索引，索引字段中的数据就会被复制一份出来（只复制索引字段，不复制其他的），用于生成索引。因此，给表添加索引，会增加表的体积，占用磁盘存储空间。非聚簇索引之间不存在关联。

创建非聚簇索引

create index index_birthday on user_info(birthday);

查询生日在2021年1月1日出生的小孩的名字

select user_name from user_info where birthday = '2021-01-01';

这句SQL语句的执行过程如下

首先，通过非聚集索引index_birthday查找birthday等于2021-01-01的所有记录的主键id值。然后，通过得到的主键id值执行聚集索引查找，找到主键id值对应的真实数据（数据行）存储的位置。最后，从得到的真实数据中取得user_name字段的值返回，取得最终的结果。

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ，再使用主键的值通过聚集索引查找到需要的数据。

不建议使用索引的场景

1 数据唯一性差的字段不要使用索引：比如我们所说的性别，有两种可能，去查找无异于全表扫描。

2 频繁更新的字段不要使用索引：比如点赞次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率。

3 字段不在where语句出现时不要使用索引：只有在where语句出现，mysql才会使用索引。

4 数据量少的表不要使用索引。

3 字段不在where语句出现时不要使用索引：只有在where语句出现，mysql才会使用索引。

4 数据量少的表不要使用索引。

5 若MySQL使用全表扫描要比使用索引快，则不会使用索引。