注意：如果文中的 SQL 无法执行，请使用编辑器重新编辑后再粘贴复制，可能 web 浏览器对空格或者特殊符号转码导致。
查询优化
数据初始化
# DDL 语句 CREATE TABLE `employees` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名' COLLATE 'utf8_general_ci', `age` INT(10) NOT NULL DEFAULT '0' COMMENT '年龄', `position` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位' COLLATE 'utf8_general_ci', `hire_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_name_age_position` (`name`, `age`, `position`) USING BTREE, INDEX `idx_age` (`age`) USING BTREE ) COMMENT='员工记录表'; # 随机数（随机生成员工年龄） drop function if exists `rand_num`; delimiter ;; create function `rand_num`( `start_num` integer, `end_num` integer ) returns int comment '' begin return floor(start_num + rand() * (end_num - start_num + 1)); end ;; # 随机字符串函数 drop function if exists `rand_str`; delimiter ;; create definer = `root`@`localhost` function `rand_str`( `n` int ) returns varchar(255) begin declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'; declare return_str varchar(100) default ''; declare i int default 0; while i < n do set return_str = concat(return_str, substring(chars_str, floor(1 + rand() * 52), 1)); set i = i + 1; end while; return return_str; end ;; # 生成测试数据 drop procedure if exists `insert_emp`; delimiter ;; create procedure `insert_emp`( in `max_num` int(10) ) begin declare i int default 0; set autocommit = 0; repeat set i = i+1; insert into `employees` (`name`, `age`, `position`, `hire_time`) values (rand_str(6), rand_num(20, 40), 'dev', now()); until i = max_num end repeat; commit; end ;; delimiter ; call insert_emp(10000);
很多时候我们业务系统实现分页可能会用到如下的 sql 实现：
select * from employees limit 50000, 10;
表示表 employees 中去除 50001 行开始的 10 行记录。看似只查询 10 条记录，实际这条 SQL 是优先读取 10010 条记录，然后抛弃前 10000 条记录，然后读后面想要的 10 条数据，因此要查询一张大表比较靠后的数据，执行效率是非常低的。
分页 Limit 优化
1、 更具自增且连续的主见爱心的分页查询
首先来看一个根据自增且连续主键排序的分页查询的例子：
mysql> select * from employees limit 90000,5;
该 SQL 表示查询从第 90001开始的五行数据，没添加单独 order by，表示通过主键排序。我们再看表 employees ，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第 90001开始的五行数据，如下：
select * from employees where id > 90000 limit 5;
查询的结果是一致的。我们再对比一下执行计划：
EXPLAIN select * from employees limit 90000,5;
EXPLAIN select * from employees where id > 90000 limit 5;
显然改写后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。 但是，这条改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，如下图试验所示（先删除一条前面的记录，然后再测试原 SQL 和优化后的 SQL）：直接 limit :
**id > x limit **
两条 SQL 的结果并不一样，因此，如果主键不连续，不能使用上面描述的优化方法。 另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满足以下两个条件：
- 主键自增且连续
- 结果是按照主键排序的
