数据库--第3篇（深入MySQL篇二）

一，深入MySQL

1，索引的常见模型

索引的出现是为了提高查询效率，但是实现索引的方式却有很多种，所以这里也就引入了索引模型的概念。三种常见、也比较简单的数据结构分别是哈希表、有序数组和二叉搜索树。
一个主键列为ID的表，表中有字段k，并且在k上有索引，表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示例示意图如下。

1. InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。
2. 主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。主键查询方式，则只需要搜索ID这棵B+树；
3. 非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。非主键索引树回到主键索引树搜索的过程过程称为回表。
4. 基于非主键索引的查询需要多扫描一棵索引树，尽量使用主键查询。
5. 每个非主键索引的叶子节点上都是主键的值。

2，覆盖索引

在查询里面，如果非主键索引已经“覆盖了”我们的查询需求，则称为覆盖索引 。覆盖索引减可少回表，提升查询性能，可做性能优化手段；

-- 由于非主键k的叶子节点是主键的值，查询结果已经返回了我们需要的结果id，称为覆盖索引
select id from table where k between 3 and 5;

3，联合索引（最左前缀原则）

创建联合索引时，优先调整字段顺序支持最左前缀，尽量少维护一个索引。如果既有联合查询，又有基于各自的查询，就要虑空间问题，如：name字段是比age字段大的 ，建议创建一个（name,age)的联合索引和一个(age)的单字段索引。

4，索引下推

满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录，不符合最左前缀的部分，MySQL 5.6 引入的索引下推 优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。