【MySQL】知识总结——索引的类型分类和性质

常见的基于InnoDB+树索引方面（默认都是B+树）

• 聚簇索引（主键索引）：

                非叶子节点存储主键和页号

                叶子节点存储完整数据，双向链表结构，便于范围查询

                快速定位页，非记录

• 非聚簇索引（非主键索引——辅助索引，二级索引）：

                叶子节点仅存储索引列和主键，不包含完整数据

PS：使用select * 会引起回表（先通过二级索引查到ID，再去主键索引查询非索引列数据），由于ID不连续，主键索引造成大量随机I/O操作，频繁回表效率低。

索引性质方面

• 普通索引（二级索引——由非主键列构建的索引，辅助索引）
• 主键索引
• 唯一索引
• 全文索引
• 空间索引

索引的最左前缀匹配原则

        MySQL在利用索引进行查询时，查询条件从最左边开始逐个字段匹配，直到遇到第一个不能匹配的字段为之。主要用于联合索引

1）联合索引等于查询字段——根据顺序定位索引中的相应位置，再检索记录。

2）查询字段中缺少联合索引的部分字段——也会利用索引进行查询，仅限于使用索引的前缀部分。

3）查询索引后缀字段——查询没有涉及到索引的左边部分，不能使用联合索引，可能会使用全表扫描，导致效率降低。

4）查询语句包含范围查询，列如>、<、between、like，停止使用联合索引来优化查询。

索引下推

        减少回表查询，提高查询效率的技术。将部分带有索引的条件交由存储引擎层进行过滤操作减少了I/O（本该由Server层操作，交由存储引擎层因此叫做“下推”）PS：索引下推是应用在联合索引上的。

没有使用索引下推

        1）通过索引找到主键ID数据。

        2）通过主键ID查询完整数据行，回表操作。

        3）数据交由Server层，将满足条件的数据过滤出得到最终数据行。

使用索引下推

        1）通过索引找到主键ID数据。

        2）联合索引内字段，直接在存储引擎进行索引过滤，得到最终主键ID。

        3）通过主键ID回表查询得到完整数据。

注意事项

        1）MySQL5.6版本以后支持，InnoDB和MySAM

        2）查询中引用子查询索引下推可能不会生效。

        3）使用了函数或表达式索引下推也不能生效。

        4）使用聚簇索引（主键）查询，也不会生效，因为是对于非簇聚索引来减少回表次数