mysql 执行计划优化

一条简单的SQL 语句竟花了15.87 sec，

 

写道

mysql> SELECT x.loc AS loc, x.lastmod AS lastmod, x.changefreq AS changefreq, x.changecount AS changecount, x.priority AS priority, x.language AS language, x.ac cess AS access, x.status AS status FROM xmlsitemap x WHERE (x.access = '1') AND (x.status = '1') ORDER BY x.language DESC, x.loc ASC LIMIT 5 OFFSET 455000 ; +-------------+------------+------------+-------------+----------+----------+--------+--------+ | loc | lastmod | changefreq | changecount | priority | language | access | status | +-------------+------------+------------+-------------+----------+----------+--------+--------+ | node/539675 | 1361158321 | 0 | 0 | 0.8 | und | 1 | 1 | | node/539676 | 1361158321 | 0 | 0 | 0.8 | und | 1 | 1 | | node/539677 | 1361158321 | 0 | 0 | 0.8 | und | 1 | 1 | | node/539678 | 1361158321 | 0 | 0 | 0.8 | und | 1 | 1 | | node/539679 | 1361158321 | 0 | 0 | 0.8 | und | 1 | 1 | +-------------+------------+------------+-------------+----------+----------+--------+--------+ 5 rows in set (15.87 sec)

 

 

分析一下执行计划

 

写道

mysql> explain SELECT x.loc AS loc, x.lastmod AS lastmod, x.changefreq AS changefreq, x.changecount AS changecount, x.priority AS priority, x.language AS langua ge, x.access AS access, x.status AS status FROM xmlsitemap x WHERE (x.access = '1') AND (x.status = '1') ORDER BY x.language DESC, x.loc ASC LIMIT 50 OFFS ET 455000; +----+-------------+-------+------+-------------------+-------------------+---------+-------------+--------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------------+--------+-----------------------------+ | 1 | SIMPLE | x | ref | access_status_loc | access_status_loc | 2 | const,const | 266873 | Using where; Using filesort | +----+-------------+-------+------+-------------------+-------------------+---------+-------------+--------+-----------------------------+ 1 row in set (0.00 sec)

 

 

从执行计划中可以看到，用到了Using filesort

Using filsort文档中的解释：
Mysql需要额外的一次传递，以找出如何按排序顺序检索行，通过根据联接类型浏览所有行并为所有匹配where子句的行保存排序关键字和行的指针来完成排序，然后关键字被排序，并按排序顺序检索行。额外的传递是指什么？

 

简单修改去掉

ORDER BY x.language DESC,

 

分析执行计划

 

写道

mysql> explain SELECT x.loc AS loc, x.lastmod AS lastmod, x.changefreq AS changefreq, x.changecount AS changecount, x.priority AS priority, x.language AS langua ge, x.access AS access, x.status AS status FROM xmlsitemap x WHERE (x.access = '1') AND (x.status = '1') ORDER BY x.loc ASC LIMIT 5 OFFSET 455000; +----+-------------+-------+------+-------------------+-------------------+---------+-------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------------+--------+-------------+ | 1 | SIMPLE | x | ref | access_status_loc | access_status_loc | 2 | const,const | 266873 | Using where | +----+-------------+-------+------+-------------------+-------------------+---------+-------------+--------+-------------+ 1 row in set (0.00 sec)

 

 

没有用到Using filsort

 

写道

mysql> SELECT x.loc AS loc, x.lastmod AS lastmod, x.changefreq AS changefreq, x.changecount AS changecount, x.priority AS priority, x.language AS language, x.ac cess AS access, x.status AS status FROM xmlsitemap x WHERE (x.access = '1') AND (x.status = '1') ORDER BY x.loc ASC LIMIT 5 OFFSET 455000; +-------------+------------+------------+-------------+----------+----------+--------+--------+ | loc | lastmod | changefreq | changecount | priority | language | access | status | +-------------+------------+------------+-------------+----------+----------+--------+--------+ | node/539675 | 1361158321 | 0 | 0 | 0.8 | und | 1 | 1 | | node/539676 | 1361158321 | 0 | 0 | 0.8 | und | 1 | 1 | | node/539677 | 1361158321 | 0 | 0 | 0.8 | und | 1 | 1 | | node/539678 | 1361158321 | 0 | 0 | 0.8 | und | 1 | 1 | | node/539679 | 1361158321 | 0 | 0 | 0.8 | und | 1 | 1 | +-------------+------------+------------+-------------+----------+----------+--------+--------+ 5 rows in set (1.14 sec)

                                                                                                                                                     

只用1.14 sec就执行完了， 快了很多倍！