引言
思考一个问题,T的建表语句如下
create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
执行select * from T where k between 3 and 5
需要执行几次树的搜索操作,会扫描多少行
这条SQL的执行流程如下:
在K索引树上找到k=3的记录,取ID=300
在ID索引树上查到ID=300对应的R3
在k索引树取下一个值k=5,取得ID=500
在回到ID索引树查到ID=500对应的R4
在k索引树上取下一个值k=6,不满足条件,循环结束
回到主键索引树搜索得过程,称之为回表。这个查询过程读了k索引树的3条记录,回表了2次。
因为要查询的数据只有主键索引上有,所以不得不回表。那么有没有可能优化索引来避免回表呢?
覆盖索引
如果执行的语句是select ID from T where k between 3 and 5
,这时候只需要查ID的值,而ID的值已经在k索引树上了,因此不需要回表。也就是说,索引k已经覆盖了查询需求,就称之为覆盖索引。
因为覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
注意:在引擎内部使用覆盖索引在索引k上其实读了三条记录,R3~R5,但是对于MySQL的Server层来说,它找引擎拿了两条记录,因此MySQL认为扫描行数是2
基于上述说明,再来讨论一个问题:在市民信息表上,是否有必要将身份证号和名字建立联合索引。
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
一般来说,身份证号是市民的唯一标识,如果有根据身份证号查询市民信息的需求,只需要在身份证号上建立索引就够了。但是如果有高频的请求是根据市民的身份证号查询姓名,这个联合索引就比较有意义了。