Explain优化实战

-- 示例表
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(20) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=136326 DEFAULT CHARSET=utf8 COMMENT='员工表'

--创建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('xiaoqiang',i),i,'coder');
        SET i=i+1;
    end WHILE;
end;;
delimiter ;
call insert_emp();

select * from  employees WHERE id > 9999 limit 5;

SQL语句的返回结果和上面一样，我们利用explain输出该SQL语句的执行信息。

可以看到，type字段的值为ALL，表明进行了全表扫描。由于limit语句前没有加 order by，因此是默认按照主键进行排序的，虽然我们只想要5行数据，但5行数据是从第10000行开始的，因此mysql必须读取10005行数据，然后抛弃前10000行数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

分页查询的一个可能优化就是将limit查询转换为已知位置的查询。因为我们表的id列是主键自增的，因此查询从第10001开始的五行数据可改为如下SQL。

select * from  employees WHERE id > 9999 limit 5;

SQL语句的返回结果和上面一样，我们利用explain输出该SQL语句的执行信息。

改写后的SQL利用了主键索引，rows值比前面少了，执行效果会好很多。

如果我