mysql索引的概述_《Mysql 索引 - 概述》

一：索引的目的

- 索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

二：InnoDB 索引模型

- InnoDB 采用 B+树 的数据结构进行存储。

- 例如，我们建立一张表，分析他的数据建立

mysql>create table T(

idintprimary key,

kint not null,

name varchar(16),

index (k)

) engine=InnoDB;

插入对应的表数据，表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)

- 则，对应的两颗树建立如下

-

- 从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

- 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引(clustered index)。

- 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引(secondary index)。

- 也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

三：基于存储结构谈为什么要建立自增主键？

-  B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

- 以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。

- 如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

- 而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。

- 在这种情况下，性能自然会受影响。

- 在这种情况下，性能自然会受影响。

- 除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。

- 自增主键

- 也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。

- 每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

- 显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

- 所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

- 适合业务做主键的使用场景

- 只有一个索引；

- 该索引必须是唯一索引。

- 典型的 KV 场景。(由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。)

四：联合索引技巧

- 覆盖索引

- 如果查询条件使用的是普通索引(或是联合索引的最左原则字段)，查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据

- 最左前缀(前缀索引)

- 联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符(前缀索引)

- 联合索引

- 根据创建联合索引的顺序，以最左原则进行where检索。

- 比如(age，name)以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引。

- 考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。

- 索引下推 (Mysql 底层优化)

- like 'hello%’and age >10 检索。

- MySQL5.6版本之前，会对匹配的数据进行回表查询。

- 5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度

四：索引重建

- 为什么要重索引？

- 文章里面有提到，索引可能因为删除，或者页分裂等原因，导致数据页有空洞。

- 重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

- 如何重建索引

- 注意：不论是删除主键还是创建主键，都会将整个表重建。

重建索引请使用- alter table T engine=InnoDB