MySQL SQL Tuning:深入理解Order By
- - CSDN博客数据库推荐文章在MySQL中ORDER BY按先后顺序有2种实现方式,先走索引无排序,如果不行,则用FILESORT. 走索引无排序需要满足2个条件:. ①排序字段和执行计划中所利用INDEX的索引键(或前面几个索引键)完全一致. ②表访问方式为index、ref或range [注释:explain输出中的Type可看出].
Profile语法:
SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS
mysql> set profiling=1; mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 1 | +-------------+ mysql> select * from employees.t order by first_name; mysql> show profiles; +----------+------------+-----------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------+ | 1 | 0.21138800 | show create table employees.t | | 2 | 8.21691600 | select * from employees.t order by first_name | +----------+------------+-----------------------------------------------+ 2 rows in set (0.00 sec) mysql> show profile cpu,block io for query 2; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000160 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000055 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000033 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000050 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000145 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000118 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | | Sorting result | 2.838465 | 1.396087 | 1.140071 | 0 | 0 | | Sending data | 0.928078 | 0.544034 | 0.056003 | 0 | 0 | | end | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 4.449672 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 17 rows in set (0.00 sec)
mysql> SELECT STATE, FORMAT(DURATION, 6) AS DURATION -> FROM INFORMATION_SCHEMA.PROFILING -> WHERE QUERY_ID = 2 ORDER BY DURATION DESC; +----------------------+----------+ | STATE | DURATION | +----------------------+----------+ | freeing items | 4.449672 | | Sorting result | 2.838465 | | Sending data | 0.928078 | | starting | 0.000160 | | statistics | 0.000145 | | preparing | 0.000118 | | Opening tables | 0.000055 | | init | 0.000050 | | System lock | 0.000033 | | end | 0.000026 | | optimizing | 0.000026 | | checking permissions | 0.000026 | | closing tables | 0.000021 | | logging slow query | 0.000014 | | query end | 0.000011 | | executing | 0.000011 | | cleaning up | 0.000005 | +----------------------+----------+ 17 rows in set (0.00 sec)
//忽略其他的数据了.