Java性能调优--SQL篇：优化"分页查询"
在日常的业务需求开发中，数据库分页通常被架构封装到方法中直接调用，
但在大数据量的情况下，分页查询是否存在效率问题？怎样分析SQL效率？如何优化分页查询效率？
本篇就针对该部分做探讨。
1
如何分析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,不能用来获取任何元祖 |
2
优化大数据量下的分页查询
在使用分页查询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条数据，然后执行主语句，虽然扫描的数据量并未减少很多，但效率有所提升。
以上就是本篇的内容，希望能对读者有所帮助，浩说编程帮你学到更多。
腾讯云开发者
