mysql异步索引_MySQL 索引

作用

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

结构类型

哈希表、有序数组和搜索树

哈希表

哈希表适合做等值查询，插入数据很快，但是因为不是有序，哈希索引做区间查询的速度很慢。

有序数组

如果仅仅看查询效率(等值查询和范围查询)，有序数组是最好的数据结构，二分查找复杂度是O(log(N))，但是插入数据比较麻烦，插入一个记录就要移动后面所有的记录，成本很高。所以，有序数组索引更适用于静态存储引擎，保存不会再修改的数据(城市历史人口信息)。

搜索树

二叉树

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。

二叉树是搜索效率是最高的，但是实际上大多数的数据库存储却并不使用二叉树(Mysql 用的是 b+ 树)。其原因是，索引不止存在内存中，还要写到磁盘上。二叉树树高过高，每次查询都需要访问过多节点，即访问数据块过多，而从磁盘随机读取数据块过于耗时(在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间)。

InnoDB 的索引模型每一个索引在 InnoDB 里面对应一棵 B+ 树，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引(clustered index)。非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引(secondary index)。

主键索引： key:主键的值，value:整行数据。 普通列索引： key：索引列的值， value:主键的值。

基于非主键索引的查询需要多扫描一棵索引树(回表)。因此，我们在应用中应该尽量使用主键查询。

页分裂，页合并。

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。用整型做主键比字符串能节省空间。

KV 场景适合用业务字段直接做主键。

当对InnoDB进行修改操作时，例如删除一些行，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。 InnoDB的Purge线程会异步的来清理这些没用的索引键和行，但是依然没有把这些释放出来的空间还给操作系统重新使用，因而会导致页面中存在很多空洞。重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。可以用这个语句代替 ： alter table T engine=InnoDB

覆盖索引二级索引查询结果仅仅是主键，此时不需要回表查主键索引，称为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

最左前缀原则B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

索引下推Mysql 5.6之前联合索引只是为了起覆盖索引作用,但是如果 where中如果出了最左前缀原则匹配的字段外还有别的联合索引的字段,直接忽略,不能直接在索引上判断,必须回表获取整行数据对比

Index Condition Pushdown，简称 ICP。 是Mysql 5.6版本引入的技术优化。旨在 在“仅能利用最左前缀索的场景”下(而不是能利用全部联合索引)，对不在最左前缀索引中的其他联合索引字段加以利用——在遍历索引时，就用这些其他字段进行过滤(where条件里的匹配)。过滤会减少遍历索引查出的主键条数，从而减少回表次数，提示整体性能。