mysql 执行计划优化 - MySQL教程

写道

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就执行完了， 快了很多倍！

查看本栏目更多精彩内容：http://www.bianceng.cn/database/MySQL/

（编辑：应用网_镇江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!