MYSQL索引-下
前言
丁奇老师的 MYSQL45讲 的关于索引的的两节 深入浅出索引,做个笔记
覆盖索引
插入一个实例表
mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
我们执行查询语句
select * from T where k = 3
上一节我们知道主键索引和二级索引的关系,以及索引树。大致流程是这样的
- 在 k 二级索引树上找到 k=3 的记录,取得 ID = 300;
- 再到 ID 主键索引树查到 ID=300 对应的行记录
回到主键索引树搜索的过程,我们称为回表。我们可以看到查询普通索引一次,回表一次。由于我们查询的是整行数据记录,就必须回表。
那如果我们这样查询?
select ID from T where k = 3
这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引 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,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
有没有必要建立 以下的联合索引
KEY id_card_name
(id_card
,name
)
如果通过id_card查询name是个高频的查询,那么这个联合索引就是有必要的,可以在这个高频查询上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间。
当然,索引字段的维护总是有代价的。
最左前缀原则
这个原则应该大多数人都知道。
继续看上面的表
select * from tuser where name = 'xyz' and age = 25
select * from tuser where name = 'xyz'
select * from tuser where name like 'x%'
上面的三种类型的查询都会使用的 name_age 这个索引,这就是使用索引额最左前缀匹配。
如果执行
select * from tuser where age = 25
这条语句就没有使用索引。
只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。基于上面对最左前缀索引的说明,我们来讨论一个问题:在建立联合索引的时候,如何安排索引内的字段顺序。
那么,如果既有联合查询,又有基于 a、b 各自的查询呢?查询条件里面只有 b 的语句,是无法使用 (a,b) 这个联合索引的,这时候你不得不维护另外一个索引,也就是说你需要同时维护 (a,b)、(b) 这两个索引。这时候,我们要考虑的原则就是空间了。比如上面表的情况,name 字段是比 age 字段大的 多,那我们就建议你创建一个(name,age) 的联合索引和一个 (age) 的单字段索引。
索引下推
上一段我们说到满足最左前缀原则的时候,最左前缀可以用于在索引中定位记录。这时,你可能要问,那些不符合最左前缀的部分,会怎么样呢?
我们还是以以上表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是 10 岁的所有男孩”。那么,SQL 语句是这么写的:
select * from tuser where name like '张%' and age=10 and ismale=1;
你已经知道了前缀索引规则,所以这个语句在搜索索引树的时候,只能用 “张”,找到第一个满足条件的记录 。当然,这还不错,总比全表扫描要好。然后呢?
先说结果吧
MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
所以基于这个原则,先通过联合索引树(name, age)取到全部以 张 开头的索引结果,在通过索引结果里面的age=10剔除掉不符合条件的索引结果,再获取索引结果的ID,通过主键索引回表筛选出ismale=1的记录,减少了回表次数,提高了查询效率。
需要注意的是,MySQL 5.6之前的版本没有这个优化。