①. 分页查询优化
- ①. 坏境准备
表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录,实际这条 SQL 是先读取 10010 条记录,然后抛弃前 10000 条记录,然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据,执行效率是非常低的
示例表: 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 CHARSET=utf8 COMMENT='员工记录表';
②. 根据自增且连续的主键排序的分页查询
(主键自增且连续、结果是按照主键排序的)
首先来看一个根据自增且连续主键排序的分页查询的例子:
(该 SQL 表示查询从第 90001开始的五行数据,没添加单独 order by,表示通过主键排序。我们再看表 employees ,因为主键是自增并且连续的,所以可以改写成按照主键去查询从第 90001开始的五行数据,如下)
查询的结果是一致的。我们再对比一下执行计划:
(显然改写后的 SQL 走了索引,而且扫描的行数大大减少,执行效率更高)
但是,这条改写的SQL 在很多场景并不实用,因为表中可能某些记录被删后,主键空缺,导致结果不一致,如下图试验所示(先删除一条前面的记录,然后再测试原 SQL 和优化后的 SQL)
两条 SQL 的结果并不一样,因此,如果主键不连续,不能使用下面描述的优化方法
③. 根据非主键字段排序的分页查询
(发现并没有使用 name 字段的索引(key 字段对应的值为 null),具体原因:扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高,所以优化器放弃使用索引)
④. 实关键是让排序时返回的字段尽可能少,所以可以让排序和分页操作先查出主键,然后根据主键查到对应的记录,SQL改写如下:
原SQL使用的是 filesort 排序,而优化后的SQL使用的是索引排序。
