全部索引扫描
现象:
执行SQL:
结果:虽然走了索引,但还是进行了全表扫描
产生的原因:
1.与B+树的结构有关,即叶子节点形成有序链表
2.本条SQL是范围查询
对于范围查询,会从索引的最左边叶子节点,向右扫描B+树的最后一层叶子节点
索引的过滤性要足够好
假设现在维护了一个表,在它的age字段上建立了索引,这个表记录了中国14亿人的基本信息,现在要查出所有年龄在10~15岁之间的姓名和基本信息,那么你的语句会这么写,select * from t_people where age between 10 and 15。
执行上述SQL语句还是会比较慢,原因是:满足以上条件的数据量可能仍然很大,比如:超过1亿。
上面的索引不是主键索引,还需要回表查询,也会消耗大量时间。
对于大表,在设计表结构的时候,要让他的过滤性足够好,也就是区分度要高。
回表的代价
t_people表上有一个索引是姓名和年龄的联合索引,执行select * from t_people where name like ‘张%’ and age=8;
这个过程跟上面的差别,是在遍历联合索引的过程中,将年龄等于8的条件下推到所有遍历的过程中,减少了回表的次数,假设全国名字第1个字是张的人里面,有100万个是8岁的小朋友,那么这个查询过程中在联合索引里要遍历8000万次,而回表只需要100万次。
尽量减少回表的次数
虚拟列
针对回表代价中的问题,可以建立虚拟列。
首先他在people上创建一个字段叫name_first的虚拟列,然后给name_first和age上创建一个联合索引,并且,让这个虚拟列的值总是等于name字段的前两个字节,虚拟列在插入数据的时候不能指定值,在更新的时候也不能主动修改,它的值会根据定义自动生成,在name字段修改的时候也会自动修改。
有了这个新的联合索引,我们在找名字的第1个字是张,并且年龄为8的小朋友的时候,这个SQL语句就可以这么写:select * from t_people where name_first=‘张’ and age=8。
这样这个语句的执行过程,就只需要扫描联合索引的100万行,并回表100万次,这个优化的本质是我们创建了一个更紧凑的索引,来加速了查询的过程。