今天继续学习索引相关知识,如下面查询语句需要搜索几次树和扫描几行
select * from T where between 3 and 5
创建表T,并初始化
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');
上面查询语句执行的顺序如下
上面ID索引树进行查找记录的过程叫回表,可以看出k树索引树进行了三次查询,Id索引树进行了两次查询。查询数据过程中是否可以避免回表查询呢,
覆盖索引
当我们使用下面语句
select id from T where between 3 and 5
由于上面语句在k索引树中查询直接可以获取结果id,覆盖了我们要查询的结果,不需要再回表查询,因此这种就叫做覆盖索引,覆盖索引是我们经常使用的一种优化手段。
基于上面覆盖索引,我们看看一张城市居民信息表
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
我们是否有必要建立身份证和名称的联合索引,比如当我们要根据身份证查询居民信息时候,建立一个身份证索引就应该可以满足,但是如果有一种场景要根据身份中查询居民的名称的时候,这个时候建立联合索引就比较合理了,这就要用到了上面谈到的覆盖索引,不用回表就可以把需要的信息查询出来。
但是建立索引也是需要维护成本的,到底要不要添加一些冗余的索引,这就要根据业务场景来判断了。
最左前缀索引
如果给每一种查询都建立一个索引,是很浪费空间的,比如当我们根据身份证查询地址的需求是,虽然这种查询比较少,但是我们总不能让他全表扫描,因此我们必须采用一种手段解决这种问题,这里就要说到B+树的结构使用最左前缀索引定位记录,看下图
我要要查询张三的记录时候,可以快速定位到id2。我们也可以使用下面语句
select * from T where name like '张%'
使用最左前缀匹配数据,找到张三记录,循环遍历后面的语句直到不满足条件结束,我们看到索引的最左前缀可以理解为,索引的最左结果字段,也可以是字段的最左的字符.
那么我们如果定义联合索引的字段顺序呢,我们需要评判的标准是索引的复用能力,我们的原则就是,如果联合索引调整顺序,可以减少一个索引的建立,我们就会按照这个字段顺序建立联合索引。
当然这也不是必须,当我们有联合索引(a,b)和a,b单独的业务查询,我不得不建立联合索引(a,b)和b索引,但是这里你也要考虑空间的问题,如name的长度比age的长度大,因此你应该建立联合索引(name,age)和age索引。
索引下推
当我们使用联合索引(name,age),使用下面语句
select * from T where name like '张%' and age > 10
当然上面语句的name查询符合最左前缀查询,总比使用全表查询强吧。然后肯定就是比较age是否满足条件。
在mysql5.6之前,我们会根据最左前缀获取的id,回表查询记录数
在mysql5.6之后,我们使用索引下推,直接使用在联合索引中过滤调不符合条件的age,减少回表的性能消耗,
图1
图2
上面两种图说明的不同的策略,图1是特地的去掉age的值,他会直接到主表查询需要的数据,因此需要回表4次查询,而图2,直接在(name,age)进行过滤age的值,仅仅需要回表两次。