假设,我们有一个主键列为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;