mysql覆盖索引优化记录一下

749 阅读3分钟

开局准备,先创建一张表和写数据

CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
 PRIMARY KEY (`id`),
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

‐‐ 插入100000示例数据
drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();
  • 示例一:组合索引中第一个字段用范围查询会导致不走索引,mysql优化器认为查询结果范围很大回表效率低,所以就走全表扫描。
EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

在这里插入图片描述

  • 示例二:这里使用强制执行索引,但是效率不一定比上面高,还是因为这里回表效率低。
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';

在这里插入图片描述

-- 测试小例子:
-- 先关闭mysql的查询缓存,得到真实的查询效率
set global query_cache_size=0;
set global query_cache_type=0;
-- 执行时间7.541s
SELECT * FROM employees WHERE name > 'LiLei';
-- 执行时间7.769s
SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';
  • 示例三:覆盖索引优化。
EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

在这里插入图片描述

  • 示例四:in和or在MySQL表数据量比较大的时候会走索引,在表数据不多的时候会选择全表扫描。
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';

第一个sql语句解释信息 第二个sql语句解释信息

-- 测试小例子:employees_copy1表只有少量的数据,所以没有走索引
EXPLAIN SELECT * FROM employees_copy WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees_copy WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';

第一个sql语句解释信息 第二个sql语句解释信息

  • 示例五:like % 一般情况都会走索引。
EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees_copy1 WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

第一个sql语句解释信息 第二个sql语句解释信息

  • 解释一下索引下推原则:在使用组合索引时比如:where name like 'LiLei%' and age = 22 and position ='manager'
    • 在mysql5.6之前版本里索引只会执行匹配到name字段,age和position字段不会走索引,先拿到name匹配到的索引对应的ID,然后在回表拿到所有数据,然后在筛选age和position字段。
    • 在mysql5.6版本后引入索引下推优化,可以对组合索引字段全都进行匹配判断,剔除掉不匹配的数据后再进行回表操作,有效的减少了回表的次数和数据。
  • 在innodb存储引擎中,索引下推只能用于二级索引,因为在聚餐索引(主键索引)中,最下方的叶子节点保存了整行数据,所以使用索引下推也没有效率的提升
  • 至于范围查询索引没有使用索引下推:因为范围查询索引的结果集一般比较大,而like %查询结果集大多数比较小,所以mysql给like%使用索引下推而范围查询没有,当然like%也不一定都会走索引下推;

最后

  • 虚心学习,共同进步 -_-