Java性能调优--SQL篇：优化“分页查询“

作者 | 浩说编程
来源 | 公众号：浩说编程
[ 大厂技术资源 | 研发必备安装包 | 限时免费获取 ]

在日常的业务需求开发中，数据库分页通常被架构封装到方法中直接调用

 
但在大数据量的情况下，分页查询是否存在效率问题？
 
怎样分析SQL效率？
 
如何优化分页查询效率？
 
本篇就针对该部分做探讨。

一、如何分析SQL性能

mysql提供了EXPLAIN命令来查看SQL语句的执行计划，其中包含了语句是否有应用索引以及遍历的数据量，举个例子：

EXPLAIN SELECT * FROM `user_`;

执行计划

以下是执行计划中每个字段的含义:

1、id：SQL查询中的序列号，id列数字越大越先执行，如果数字一样大，那么就从上往下依次执行。

2、select_type：查询类型

SIMPLE	简单SELECT(不使用UNION或子查询)
PRIMARY	最外层的SELECT
UNION	UNION中第二个或之后的SELECT语句
DEPENDENT UNION	UNION中第二个或之后的SELECT语句取决于外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT, 取决于外面的查询
DERIVED	衍生表(FROM子句中的子查询)
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	结果集无法缓存的子查询，必须重新评估外部查询的每一行
UNCACHEABLE UNION	UNION中第二个或之后的SELECT，属于无法缓存的子查询

3、table：查询的表名

4、type：查询类型，表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。

system	表中只有一行数据
const	通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据
eq_ref	使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。
ref	对于来自前面表的每一行，在此表的索引中可以匹配到多行，ref可用于使用'='或'<=>'操作符作比较的索引列。
fulltext	使用全文索引的时候是这个类型。
ref_or_null	跟ref类型类似，只是增加了null值的比较。实际用的不多。
index_merge	表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引
unique_subquery	用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。
index_subquery	该连接类型类似于unique_subquery。适用于非唯一索引，可以返回重复值。
range‍	索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。
index	索引全表扫描，把索引从头到尾扫一遍。
all	全表扫描，性能最差。

5、partitions：分区表命中的分区情况

6、possible_keys：查询可能使用到的索引都会在这里列出来，查询到的索引不一定是真正被用到的

7、key：查询真正使用到的索引

8、key_len：查询用到的索引长度（字节数）。

9、ref：如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

10、rows：mysql估算的需要扫描的行数（不是精确值）。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

11、filtered：这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。这个字段不重要

12、extra：额外信息，包含以下几种

distinct	在select部分使用了distinc关键字
Using  index	"覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
Using  filesort	当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
Using  temporary	使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于order by和分组查询group by
Using  where	表明使用了where过滤
Using  join  buffer	表明使用了连接缓存,如在查询的时候会有多次join,则可能会产生临时表
impossible  where	表示where子句的值总是false,不能用来获取任何元祖

 

二、优化大数据量下的分页查询

在使用分页查询LIMIT M(起始位置),N(偏移量)的时候，虽然我们只需要N条数据，但数据库至少会扫描M+N条数据，如果M足够大，将会产生效率问题，比如这个例子：

select * from `demo`.`order` order by order_no limit 10000, 20;

执行计划

执行效率

可以看到，mysql在帮我们获取id从10001~10020的二十条数据的时候，rows字段为10020，也就是说扫描了10020条数据，然后去掉前一万条数据进而获取了我们需要的最后二十条数据，效率问题就产生在数据的扫描量上。

那么我们是否可以优化一下，毕竟前一万条数据是我们并不需要的。

优化建议

通过子查询的方式，先获取数据起始点id，然后根据该值获取需要的偏移量数据。

优化之后的语句：

select * from `demo`.`order` where id> (select id from `demo`.`order` order by order_no limit 10000, 1)  limit 20;

执行计划

执行效率

通过上面对执行计划的说明可以了解到，id为2的子查询优先执行，并且扫描了10001条数据，然后执行主语句，虽然扫描的数据量并未减少很多，但效率有所提升。

作者 | 浩说编程
来源 | 公众号：浩说编程
[ 大厂技术资源 | 研发必备安装包 | 限时免费获取 ]