索引查询过程分析

索引查询过程分析

索引结构

1、索引的最小存储单位是innodb定义的数据页
2、对于单个数据页，数据是按顺序存储的，并且数据页头记录了当前页数据的最大和最小值，维护最大和最小值是为了更方便的够成b+树，数据顺序存储，且内存是连续分配的，在查找的时候可二分查找
3、数据页记录FIL_PAGE_PREV和FIL_PAGE_NEXT，b+数叶子节点构成双向链表，在无法使用索引时，可已很方便的进行全表扫描。

聚簇索引

聚簇索引，叶子节点存储的是实际的数据，一般聚簇索引行程的b+树在三层以内，三层满了，数据量1170117016已经可以存储几千万数据。

聚簇索引查找流程

查找单个数据。首先根据b+数据一页数据上的区间确定查找方向，找到子节点然后同样的方式递归寻找，直到子节点。找到叶子节点还可以二分查找。这样的io次数在三次以内就可以查找到

范围查找 如果查找的数据是一个范围，在innodb中最小的io单位是数据页，如果三层数据页的情况下，可能进行成百上千次io。十分影响速度，所以在范围查找时如果允许，加上limit是十分有必要的，对于大表，使用范围查找，已经变得无法接收，即使是聚簇索引

聚簇索引查找流程

查找单个数据。首先根据b+数据一页数据上的区间确定查找方向，找到子节点然后同样的方式递归寻找，直到子节点。找到叶子节点还可以二分查找。这样的io次数在三次以内就可以查找到

范围查找 如果查找的数据是一个范围，在innodb中最小的io单位是数据页，如果三层数据页的情况下，可能进行成百上千次io。十分影响速度，所以在范围查找时如果允许，加上limit是十分有必要的，对于大表，使用范围查找，已经变得无法接收，即使是聚簇索引

非聚簇索引查询流程

非聚簇索引，叶子节点存的是，主键，也就是说，非聚簇索引，再根据索引找到指定数据后，还要再根据主键在聚簇索引查找一遍

单个数据查找，假设千万数据，三层b+树，查完非聚簇索引再查聚簇索引，也只需要几次io，速度还是十分可观的。

范围查询，非聚簇索引范围查询，还是假设有千万级数据，那么在非聚簇索引上查到全部的范围数据，就可能有成千上百次io,如果索引数据不满足条件，需要回表继续查询，那么查询到的全部结果还要再走一遍聚簇索引，显然效率十分低下，通常情况下，对非聚簇索引的查询，数据库会直接优化成全表扫描，即完全不走索引。

联合索引

1、联合索引是如何存储的

为了更好的理解，先看一下字符串大小比较.字符串2>字符串199999。联合索引，按照创建索引的顺序，比如A，B,C列依次创建索引
A,B，C三列的值就会被拼接起来。比如，A列是1，B列是2，C列是3，那就拼接成1 2 3
也因此，innodb索引只支持左前缀匹配，因为最左的数据对响拼接值影响最大。因此虽然是按照三列整体排序的，但是前缀列也保持有序
例如用1查找。那，就只根据第一列的值匹配，如果用 1 2匹配，那么就使用a,b列的组合进行匹配。

因此联合索引，根据，联合索引的顺序拼接值的大小创建一个b+树，但是对于前缀有序，因此支持左前缀匹配。并且前缀顺序优先级更高。
比如我们先查组合查 a,b，如果，用的条件是a = xxx, b =xxx，那么联合索引的b+树根据a,b两列拼接的值，与条件的拼接值进行比较很容易确定范围。

如果是 a > xxx ,b > xxx，那么就只能，根据a列查找到范围，再根据b列在查到的范围中查，那这个时候联合索引真正起作用的实际上只有a列了

explain  select * from article where user_id >1111 and topic > 'topic_10758'

1	SIMPLE	article	range	user_id	user_id	4		387298	Using index condition; Using MRR

kenLen为4说明只有userId在查询中起作用了

如果是 a < xxx ,b > xxx，那么就只能，根据a列查找到范围，再根据b列在查到的范围中查，那这个时候联合索引真正起作用的实际上只有a列了

explain  select * from article where user_id =1111 and topic > 'topic_10758'

1	SIMPLE	article	range	user_id	user_id	97		184	Using index condition

a,b两个索引都起作用了
此时由于a的范围比较小，确定a的范围后对于只看b依然是有序的。可能会使用索引

2、联合索引查询过程

1、大致跟非聚簇索引类似，不再分析