MySQL： 索引（下）

        假设，我们有一个主键列为id的表，表中有字段k，并且在k上也有索引。即有一个主键聚集索引和一个普通索引。表中Row1~Row5的(id,k)值分别为(1,100)、(2,200)、(3,300)、(5,500)和(6,600)，两棵树的示例示意图如下。我们来分析下面的查询语句扫描了几次索引树；

select * from T where k between 300 and 500

第一步：根据k=300去普通索引树k索引树中查找主键ID=3；

第二步：根据主键ID=3 去聚簇索引树中查询得到Row3的数据；

第三步：根据下一条k=500去普通索引树k索引树中查找主键ID=5；

第四步：根据主键ID=5 去聚簇索引树中查询得到Row4的数据；

第五步：根据下一条k=600去普通索引树k索引树中未查询到对应的主键ID，结束查询。

        在这个查询过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了k索引树的3条记录（步骤1、3和5），回表了两次（步骤2和4）。由于查询结果所需要的数据只在主键索引上有，所以不得不回表。下面讨论，经过索引优化，避免回表过程。

1、覆盖索引

如果上面的查询语句不是select * 查询所有，而是：

select id from T where k between 300 and 500

        我们直接根据k值去查询id，那么就不需要回表，因为我们通过k索引树已经获取到了id的值，满足了我们的查询需求，也就是说，在这个查。询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引

举例一个公民基本信息表：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

         如果我们需要根据公民身份证号id_card查询公民的信息，那么我们只需要在id_card上添加索引，根据id_card搜索主键id，然后根据主键id回表查询公民的全部信息。

        如果有一个高频率的需求：根据身份证号id_card查询公民的姓名name，这时候创建一个（id_card、name）的覆盖索引就很有效了，查询时候不需要回表操作，直接在覆盖索引树中就能获取到我们想要的数据。但是创建覆盖索引就要考虑到索引维护带来的性能问题，这就需要根据不同的情况有所取舍了。

2、最左前缀原则

最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。 

我们使用上面表的（name，age）联合索引来说明：

        上图可以看出，索引项（下面表第一行）是按照索引定义里面出现的字段顺序排序的。当使用where name = ‘张三’条件查询是，会根据索引迅速定位到ID=4，然后向后遍历得到所需要的结果。如果你要查的是所有名字第一个字是“张”的人，查询条件是"where name like ‘张%’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是ID3，然后向后遍历得到所需要的结果。可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

最左前缀原则，就是从左到右，可以连续，但是不可以跳过，比如索引（a，b，c）；

建立联合索引的原则：

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

第二原则：空间。比如上面这个市民表的情况，name字段是比age字段大的 ，那我就建议你创建一个（name,age)的联合索引和一个(age)的单字段索引。

最左前缀注意事项：

1、范围查询
        mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。即，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引
2、like 语句的索引问题
        如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀，在 like “value%” 可以使用索引，但是 like “%value%” 不会使用索引，走的是全表扫描
3、不要在列上进行运算
        如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描例如 select * from user where YEAR(birthday) < 1990，可以改造成 select * from users where birthday <’1990-01-01′
4、索引不会包含有 NULL 值的列
        只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL
5、尽量选择区分度高的列作为索引

        区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上，即平均 1 条扫描 10 条记录
6、覆盖索引的好处
        如果一个索引包含所有需要的查询的字段的值，我们称之为覆盖索引。覆盖索引是非常有用的工具，能够极大的提高性能。因为，只需要读取索引，而无需回表，极大减少数据访问量。

3、索引下推

针对一些不符合最左前缀原则的查询，举例：查询姓张，年龄为21，并且为男性的数据

select * from tuser where name like '张%' and age=21 and ismale=1;

根据最左前缀原则，我们只能使用 ‘张’ 这个索引 ，不会用到age。

在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值，如下图，需要回表四次，查出来符合条件的两条数据

而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

mysql默认启用索引下推，我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制:

SET optimizer_switch = 'index_condition_pushdown=off';

 索引下推特别注意：

1、Innodb引擎的表，索引下推只能用于二级索引。就像之前提到的，Innodb的主键索引树叶子结点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

2、索引下推一般可用于查询字段（select列）不是或者不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。

例如 表t有联合索引（a,b）,下面语句可以使用索引下推提高效率，以为a为范围查询，索引只使用了a，根据索引下推可以过滤直接过滤b

select * from t where a > 2 and b > 10;