Java数据库-7

优化LIMIT分页

当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

参考答案

MySQL中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句，EXPLAIN语句的基本语法如下：

EXPLAIN [EXTENDED] SELECT select_options

使用EXTENED关键字，EXPLAIN语句将产生附加信息。执行该语句，可以分析EXPLAIN后面SELECT语句的执行情况，并且能够分析出所查询表的一些特征。下面对查询结果进行解释：

扩展阅读

DESCRIBE语句的使用方法与EXPLAIN语句是一样的，分析结果也是一样的，并且可以缩写成DESC。。DESCRIBE语句的语法形式如下：

DESCRIBE SELECT select_options

参考答案

重点要关注如下几列：

列名	备注
type	本次查询表联接类型，从这里可以看到本次查询大概的效率。
key	最终选择的索引，如果没有索引的话，本次查询效率通常很差。
key_len	本次查询用于结果过滤的索引实际长度。
rows	预计需要扫描的记录数，预计需要扫描的记录数越小越好。
Extra	额外附加信息，主要确认是否出现 Using filesort、Using temporary 这两种情况。

其中，type包含以下几种结果，从上之下依次是最差到最好：

类型	备注
ALL	执行full table scan，这是最差的一种方式。
index	执行full index scan，并且可以通过索引完成结果扫描并且直接从索引中取的想要的结果数据，也就是可以避免回表，比ALL略好，因为索引文件通常比全部数据要来的小。
range	利用索引进行范围查询，比index略好。
index_subquery	子查询中可以用到索引。
unique_subquery	子查询中可以用到唯一索引，效率比 index_subquery 更高些。
index_merge	可以利用index merge特性用到多个索引，提高查询效率。
ref_or_null	表连接类型是ref，但进行扫描的索引列中可能包含NULL值。
fulltext	全文检索。
ref	基于索引的等值查询，或者表间等值连接。
eq_ref	表连接时基于主键或非NULL的唯一索引完成扫描，比ref略好。
const	基于主键或唯一索引唯一值查询，最多返回一条结果，比eq_ref略好。
system	查询对象表只有一行数据，这是最好的情况。

另外，Extra列需要注意以下的几种情况：

关键字	备注
Using filesort	将用外部排序而不是按照索引顺序排列结果，数据较少时从内存排序，否则需要在磁盘完成排序，代价非常高，需要添加合适的索引。
Using temporary	需要创建一个临时表来存储结果，这通常发生在对没有索引的列进行GROUP BY时，或者ORDER BY里的列不都在索引里，需要添加合适的索引。
Using index	表示MySQL使用覆盖索引避免全表扫描，不需要再到表中进行二次查找数据，这是比较好的结果之一。注意不要和type中的index类型混淆。
Using where	通常是进行了全表/全索引扫描后再