mysql如何通过条件筛选符合条件的项_MYSQL进阶——索引

索引模型

hash索引

hash索引主要适用于等值查询的场景，排序，模糊搜索等场景并不适用

有序数组

有序数组可用于非等值查询，排序等场景，但是由于写数据时需要对数组中的元素进行位移，所以一般用于静态数据的场景

二叉树

二叉树模型能够很好的解决写操作多时索引的维护，并且支持模糊查询，排序等操作

常用索引模型

MYSQL中最常用的存储引擎是InnoDB,InnoDB中常用的索引模型是B+树(二叉树的一种，实际上是n叉树)。

使用n叉树的好处

树结构进行搜索的时候，是一层一层进行搜索的，也就是说树的高度越高，搜索的次数越多，如果数据量比较大，二叉树的话高度会很高，所以采用n叉树

B+树原理

n叉树

从图中可以看出，B+树的分叉上存的是一组范围的数据，且可以有多个节点，例如现在插入一条数据4，此时4的索引应该是在3和5之间的，这时有两种情况：

1. 如果子节点允许的最大节点树超过两个，则将4放到3和5之间
2. 如果子节点最多只允许放两个元素，那么此时需要将3和5所在的节点分裂成两个节点，然后再将数据放上去

对于上面说的第二种情况，就是页分裂，就是将原本放在一页的数据分裂成两页，此时的空间利用率变低了(因为有一些空位没有使用)。

有页分裂就有页合并，当我们做删除操作的时候，如果两页的数据都比较少，就可能会发生页合并。

索引类型

聚簇索引和二级索引(InnoDB)

聚簇索引即主键索引，二级索引即非主键索引，两个索引的区别可以用下面这个图来对比一下：

主键索引和非主键索引

图中ID是主键索引，k为非主键索引，从图中可以看出，它们的区别主要在叶子节点上，主键索引的叶子节点存储的是真正的数据行，但是非主键索引的叶子节点存储的是主键，在查询上它们的一个很大的区别是：

1. 如果使用主键索引进行查询，可以直接获取到最后的数据
2. 如果使用非主键索引进行查询，只能获取到id，还需要通过id再获取到数据行，这个过程称之为回表

覆盖索引

非主键索引会多一次回表查询，但是在一些场景下是可以避免回表的，就比如说查询的字段和条件字段都在索引上，那么就不需要进行回表，这种场景下对应的索引叫覆盖索引

索引原则

左侧原则

例如我们创建了一个联合索引(a,b,c)，以后我们只使用a条件的时候或者使用a，b条件的时候也是能够使用该索引的

索引下推

索引下推是MYSQL5.6版本后的一个优化，例如现在有联合索引(a,b)，有一个查询sql：

select * from T where a like 'pre%' and b = 'field';

1. 无索引下推是先通过联合索引(a,b)筛选出a like 'pre%' 数据的id，然后回表遍历符合条件的数据筛选出b='field'的数据后返回
2. 有索引下推是通过联合索引(a,b)筛选a like 'pre%'顺便看看b是否也同时满足条件，满足才回表查询