相关文章:
什么是最左原则、什么是索引下推?
什么是索引下推?(第二篇)
在开始介绍索引下推之前,先看下联合索引的特性
1. 联合索引
联合索引(也叫组合索引、复合索引、多列索引)是指对表上的多个列进行索引。联合索引的创建方法跟单个索引的创建方法一样,不同之处仅在于有多个索引列。
例如,创建如下表,idx_name 是联合索引,索引列为 (name,age)
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(32) NOT NULL COMMENT '姓名',
`age` tinyint(3) unsigned NOT NULL COMMENT '年龄',
`gender` tinyint(3) unsigned NOT NULL COMMENT '性别:1男,0女',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
假如表中有如下数据:
id | name | age | gender |
---|---|---|---|
1 | LiLei | 18 | 1 |
2 | HanMeimei | 17 | 0 |
3 | Lucy | 17 | 0 |
4 | Lili | 16 | 0 |
5 | WeiHua | 32 | 0 |
6 | ZhangWei | 25 | 1 |
7 | Ann | 36 | 0 |
8 | Lisa | 19 | 0 |
9 | ZhangWei | 18 | 1 |
10 | Kate | 17 | 1 |
我们来看一下这棵索引树的结构:
从图中我们可以看出,叶子节点中的键值都是按顺序存储的,即(“Ann”,36)、(“HanMeimei”,17)、(“Kate”,17)、(“LiLei”,18)、(“Lili”,16)、(“Lisa”,19)、(“Lucy”,17)、(“WeiHua”,32)、(“ZhangWei”,18)、(“ZhangWei”,25)。
索引会先根据 name 排序,如果 name 相同,再根据 age 进行排序。 因此如果问age是有序的吗,不能直接回答有序,因为name相同的情况下才是有序的。
1.1 联合索引的最左匹配原则
索引的目的其实就是为了提高数据查询的效率,联合索引也一样,使用联合索引时,一定要注意符合最左匹配原则:在通过联合索引检索数据时,从索引中最左边的列开始,一直向右匹配,如果遇到范围查询(>、<、between、like等),就停止后边的匹配。
假如对字段 (a, b, c) 建立联合索引,如下查询语句可以使用到索引
:
where a = xxx
where a = xxx and b = xxx
where a = xxx and b = xxx and c = xxx
where a like 'xxx%'
where a > xxx
where a = xxx order by b
where a = xxx and b = xxx order by c
group by a
书写顺序不影响对联合索引的使用:
where b = xxx and a = xxx
where a = xxx and c = xxx and b = xxx
这种查询条件书写顺序不影响对联合索引的使用,因为执行 sql 的时候,MySQL优化器会帮我们调整 where 后 a,b,c 的顺序,让我们用上索引。
而还有一些语句是只能用到联合索引的一部分
的:
#跳过某些
where a = xxx and c = xxx 可以用到 a 列的索引,用不到 c 列索引。
#使用%,后面的不用索引
where a like 'xxx%' and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。 '索引下推正是为了优化该场景'
#使用>号,后面的不用索引
where a > xxx and b = xxx 可以用到 a 列的索引,用不到 b 列的索引
最需要注意类似下边的这些查询,完全用不到 (a, b, c) 这个联合索引
:
where b = xxx -- 跳跃
where c = xxx -- 跳跃
where a like '%xxx' -- 不满足最左前缀
where d = xxx order by a -- 出现非排序使用到的索引列 d
where a + 1 = xxx -- 使用函数、运算表达式及类型隐式转换等
1.2 如何选择合适的索引列顺序
建立联合索引时,一般我们遵循的经验是:将选择性最高的列放在索引的最前列。这在某些场景下比较有用,但通常不如避免随机IO和排序那么重要。正确的顺序应该依赖于使用该索引的查询,并且同时需要考虑如何更好的满足排序和分组的需要。
-
where a = xxx and b = xxx and c = xxx
如果我们的查询是这样的,建索引时,就可以考虑将选择性高的列放在索引的最前列,选择性低的放后边。 -
如果是
where a > xxx and b = xxx
或where a like 'xxx%' and b = xxx
这样的语句,可以对 (b, a)
建立索引。
因为这样以来,等价于where b = xxx and a > xxx
了,符合组合索引顺序了 -
如果是
where a = xxx order by b
这样的语句,可以对(a, b)
建立索引。
2. 索引下推(索引条件下推)
什么是索引下推(Index Condition Pushdown,ICP
)呢?我们通过例子来了解下。
假设我们想从一开始创建的表中,查询 name 以 ‘L’
开头,并且 age 为 17 的人员信息。
select * from t_user where name like 'L%' and age = 17;
2.1 不使用索引下推
在不用索引下推的情况下,根据前边"最左匹配原则"描述的那样,该查询在联合索引中只有 name 列可以使用到索引,age 列是用不到索引的
。
在扫描 (‘name’, age) 索引树时,根据 name like ‘L%’ 这个条件,可以查找到 LiLei、Lili、Lisa、Lucy 四条索引数据
,接下来,再根据这四条索引数据中的 id 值,逐一进行回表
扫描,从聚簇索引中找到相应的行数据,将找到的行数据返回给 server
层。server
层中,再根据 age = 17 这个条件进行筛选,最终只留下 Lucy 用户的数据信息。
select * from t_user where name like 'L%' and age = 17;
不用索引下推的过程,如下图示:
2.2 使用索引下推
在使用索引下推的情况下,存储引擎层还是先根据 name like 'L%'
这个条件,查找到 LiLei、Lili、Lisa、Lucy
四条索引数据,不过接下来不是直接进行回表操作
,而是根据 age = 17 这个条件,对四条索引数据进行判断筛选,将符合条件的索引对应的 id 进行回表扫描,最终将找到的行数据返回给 server 层。(也就是我们把本应该在 server 层进行筛选的条件,下推到存储引擎层来进行筛选判断
了。这个下推的前提是索引中有 age 列信息,如果是其它条件,如 gender = 0,这个即使下推下来也没用)
使用索引下推的过程,如下图示:
由上比较可以看出,使用索引下推优化,可以有效减少回表次数,也可以减少 server 层从存储引擎层接收数据的次数,从而大大提升查询效率。
索引下推是 MySQL 5.6 及以上版本上推出的,用于对查询进行优化。默认情况下,索引下推处于启用状态。我们可以使用如下命令来开启或关闭。
set optimizer_switch='index_condition_pushdown=off'; -- 关闭索引下推
set optimizer_switch='index_condition_pushdown=on'; -- 开启索引下推
话说,正常情况下,我们有什么理由来关闭这么好用的功能呢?