如何减少 Copyingcopy to tmp tablee 的 开销时间
点击联系发帖人
时间：2016-04-15 02:20
当前访客身份：游客 [
当前位置：
最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/my 发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。 用命令杀掉此查询 /opt/mysql/bin/mysqladmin kill&&进程号； 后面的查询一下子都好了。&&ok, 找到了问题的原因，此查询效率太低。问一下程序员，找来此查询的代码，用工具进行一下简单分析。 （说明：这里是我不喜欢mysql的原因之一，mysql我不知道从哪里能看到内存正在执行哪些sql,以及完整的sql是什么。） explain& & SELECT a.* , IF(b.`gid` IS NULL , 0, SUM( b.`mark` )) AS `score` ,
IF(c.`b_times` IS NULL ,0, c.`b_times`) AS `day_b_times`&& FROM `league_info` AS a LEFT JOIN `mark_logs` AS b ON b.`day_date` =
AND b.`gid` = a.`id` LEFT JOIN&&`visit_stat` AS c ON c.`stat_id` =
a.`id` AND c.`type` = 'league'
AND c.`day`='' WHERE a.`validate`='1' GROUP BY&&a.`id`
ORDER BY day_b_times DESC, a.`id`; +----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys& && && && && &&&| key&
&& && && &&&| key_len | ref& && && && && &&&| rows | Extra& && && && &&
&& && && && && && && && & | +----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+ |&&1 | SIMPLE& && &| a& &&&| ref&&| league_info_idx4& && && && &&&|
league_info_idx4 | 1& && & | const& && && && && &| 1441 | U
U Using filesort | |&&1 | SIMPLE& && &| b& &&&| ref&&| mark_logs_idx1,mark_logs_idx2 |
mark_logs_idx1& &| 4& && & | new5jia1.a.id& && & |& &56 |& && && && &&
&& && && && && && && && && && & | |&&1 | SIMPLE& && &| c& &&&| ref&&| visit_stat_idx1& && && && && &|
visit_stat_idx1&&| 26& && &| new5jia1.a.id,const |& &10 |& && && && &&
&& && && && && && && && && && & | +----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+
看了一下，其实此查询嵌套用得不好，作为程序员应该尽量避免用 not in ，in,&&left join&&,right&&join
等等，不过这些不归我管，我只能提一些建议。 （顺便说一声：oracle里面可以用 exist ,not exist, minus等代替in ,not in&&效率高出很多 ） 此分析对我没有太大的作用，因此用google查询了一下，发现网上一篇文章讲得很好， （ [url]/4721079.html[/url] 我给转贴了，感兴趣可以看看） Copying to tmp table on disk The temporary result set was larger than
tmp_table_size and the
thread is now changing the in memory-based temporary table to a disk
based one to save memory.
哦，原来是这样的，如果查询超出了tmp_table_size的限制，那么mysql用/tmp保存查询结果，然后返回给客户端。 set global tmp_table_size=&&(200M)
再次运行此查询,用/opt/mysql/bin/my 进行观察,发现不会出现上述问题. 至此问题解决.
调节tmp_table_size&&的时候发现另外一些参数 Qcache_queries_in_cache&&在缓存中已注册的查询数目&& Qcache_inserts&&被加入到缓存中的查询数目&& Qcache_hits&&缓存采样数数目&& Qcache_lowmem_prunes&&因为缺少内存而被从缓存中删除的查询数目&& Qcache_not_cached&&没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)&& Qcache_free_memory&&查询缓存的空闲内存总数&& Qcache_free_blocks&&查询缓存中的空闲内存块的数目&& Qcache_total_blocks&&查询缓存中的块的总数目&&
Qcache_free_memory 可以缓存一些常用的查询,如果是常用的sql会被装载到内存。那样会增加数据库访问速度。
共有1个评论
有时候不正确的字段索引也会导致问题：
/happy/thread--1.html
后来检查是因为一些字段用了索引，mysql在查询的时候自动使用了&优化&，反而导致大量数据的copy to tmp table，比取消额外的索引多用了几秒钟的时间。
更多开发者职位上
有什么技术问题吗？
小编辑的其它问题
类似的话题如何减少 Copying to tmp table 的 开销时间_百度知道
如何减少 Copying to tmp table 的 开销时间
提问者采纳
如果是temp_table_size太小的话这个可能跟你查询的SQL语句有关。这个跟temp_table_size没有关系，有些语句比如distinct,order by之类的就需要copy到临时表之后再来进行处理
其他类似问题
为您推荐：
tmp的相关知识
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁Copying to tmp table 问题求解。。。 - V2EX
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
已注册用户请 &
Great Sites on MySQL
推荐管理工具
MySQL 相关项目
Copying to tmp table 问题求解。。。
17:38:55 +08:00 · 2052 次点击
MYSQL查询语句：SELECT t_ID,t_date_last,t_content,sub_ID,sub_name,u_name,u_pic FROM t1,sub2,user3 WHERE t_type = 't' AND t_status = '0' AND t_sid = sub_ID AND u_ID = t_user ORDER BY t_date_last DESC LIMIT 30从三个表里读取数据，t1表共 100768 行32.5 MB，sub2表共 1055 行939.1 KB，user3表共 10696 行1.2 MB
查询花费 1.8137 秒 其中 Copying To Tmp Table 1.7 s尝试了tmp_table_size= 无效t_type t_status t_sid sub_ID u_ID t_user t_date_last 全部索引。。。主机是Linode 1024求解~~~
8 回复 &| &直到
23:30:17 +08:00
& & 18:05:52 +08:00
因为你使用了order by.
去掉order by 会好很多
而且,你的索引重复度会特别高,索引已经没有意义了.
& & 19:02:25 +08:00
@ 确实快了。。。。但是排序的问题没法解决啊。。。 ：（
& & 21:41:29 +08:00 via Android
用desc来找问题啊
& & 09:04:51 +08:00
@ 啊？~~ 怎么整呢。。。
& & 06:21:23 +08:00 via Android
@ 把explain贴出来
& & 11:14:12 +08:00
@ 这里。。。求帮助~~
select_type table type
possible_keys key key_len ref rows
sub2 ALL PRIMARY NULL
U Using filesort
1 SIMPLE
ref t1_sid,t1_user,t1_status,t1_user_2,t1_user_3,t1_user_4
t1_sid 8 database.sub2.sub_ID 11
Using where
1 SIMPLE
PRIMARY PRIMARY 8 database.t1.t1_user
& & 11:27:29 +08:00
explain下面的语句, 多表查询最好用join, 你那种select where是很老的用法, mysql优化器不一定能正确使用索引
SELECT t.t_ID,t.t_date_last,t.t_content,t.sub_ID,s.sub_name,u.u_name,u.u_pic FROM t1 t
LEFT JOIN sub2 s ON s.sub_ID=t.t_sid
LEFT JOIN user3 u ON u.u_ID=t.t_user
WHERE t_type = 't' AND t_status = '0'
ORDER BY t.t_date_last DESC
LIMIT 30
& & 23:30:17 +08:00
@ 貌似速度确实有提示，感谢！
& · & 1644 人在线 & 最高记录 1847 & · &
创意工作者们的社区
World is powered by solitude
VERSION: 3.9.7.3 · 59ms · UTC 08:58 · PVG 16:58 · LAX 01:58 · JFK 04:58? Do have faith in what you're doing.1428人阅读
环境说明：
' Ver 8.42 Distrib 5.5.27, for Linux on x86_64
Table 为 innodb 引擎
1.原始sql的执行计划
(这条sql导致了mysql的性能问题，当它并发多条执行的时候影响其它正常sql的执行，大量sending data以及copying to tmp table等待)
mysql&& set profiling=1;
Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)
mysql& explain
&&& -&& SELECT a.tid, a.subject,a.dateline,a.fid,b.message
&&& -& FROM forum_thread AS a
&&& -& LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
&&& -& WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
&&& -& ORDER BY a.dateline DESC
&&& -& LIMIT 0,5;
+----+-------------+-------+--------+------------------------------+-----------+---------+-------------+-------+----------------------------------------------+
| id | select_type | table | type&& | possible_keys&&&&&&&&&&&&&&& | key&&&&&& | key_len | ref&&&&&&&& | rows& | Extra&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |
+----+-------------+-------+--------+------------------------------+-----------+---------+-------------+-------+----------------------------------------------+
|& 1 | SIMPLE&&&&& | b&&&& | ref&&& | invisible,displayorder,first | invisible | 1&&&&&& | const&&&&&& | 97042 | U&Using temporary; Using filesort |
|& 1 | SIMPLE&&&&& | a&&&& | eq_ref | PRIMARY,displayorder,typeid& | PRIMARY&& | 8&&&&&& | forum.b.tid |&&&& 1 | Using where&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |
+----+-------------+-------+--------+------------------------------+-----------+---------+-------------+-------+----------------------------------------------+
2 rows in set (1.29 sec)
mysql&& SELECT a.tid, a.subject,a.dateline,a.fid,b.message
&&& -& FROM forum_thread AS a
&&& -& LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
&&& -& WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
&&& -& ORDER BY a.dateline DESC
&&& -& LIMIT 0,5;
执行这条SQL后，总耗时为 52s 查看具体步骤的耗时
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration&&& | Query&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|&&&&&&& 1 |& 1. | explain
SELECT a.tid, a.subject,a.dateline,a.fid,b.message
FROM forum_thread AS a
LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
ORDER BY a.dateline DESC
LIMIT 0,5 |
|&&&&&&& 2 | 52.027014 | SELECT a.tid, a.subject,a.dateline,a.fid,b.message
FROM forum_thread AS a
LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
ORDER BY a.dateline DESC
LIMIT 0,5&&&&&&&&&& |& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &|
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql& show profile cpu,block io,memory,swaps for query 2;
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration& | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
| starting&&&&&&&&&&&&&&&&&&&&&& |& 0.000019 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking query cache for query |& 0.000083 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000005 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Opening tables&&&&&&&&&&&&&&&& |& 0.000020 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| System lock&&&&&&&&&&&&&&&&&&& |& 0.000008 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000038 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| optimizing&&&&&&&&&&&&&&&&&&&& |& 0.000015 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& |& 0.000113 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| preparing&&&&&&&&&&&&&&&&&&&&& |& 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Creating tmp table&&&&&&&&&&&& |& 0.000162 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
|&Copying to tmp table&&&&&&&&&& | 52.027014&| 4.992241 |&&
1.324799 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sorting result&&&&&&&&&&&&&&&& |& 0.000066 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.000218 | 0.000000 |&& 0.000999 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| removing tmp table&&&&&&&&&&&& |& 0.000093 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| query end&&&&&&&&&&&&&&&&&&&&& |& 0.000005 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& |& 0.000010 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000012 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000026 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| storing result in query cache& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| cleaning up&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
31 rows in set (0.00 sec)
mysql&& show profile cpu,CONTEXT SWITCHES,PAGE
FAULTS&for query 2;
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration& | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Page_faults_major | Page_faults_minor |
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| starting&&&&&&&&&&&&&&&&&&&&&& |& 0.000019 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Waiting for query cache lock&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking query cache for query |& 0.000083 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000005 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Opening tables&&&&&&&&&&&&&&&& |& 0.000020 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| System lock&&&&&&&&&&&&&&&&&&& |& 0.000008 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Waiting for query cache lock&& |& 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000038 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| optimizing&&&&&&&&&&&&&&&&&&&& |& 0.000015 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& |& 0.000113 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| preparing&&&&&&&&&&&&&&&&&&&&& |& 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Creating tmp table&&&&&&&&&&&& |& 0.000162 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 5 |
| executing&&&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
|&Copying to tmp table&&&&&&&&&& | 52.027014 | 4.992241 |&& 1.324799 |&&&&&&&&&&&& 17969 |&&&&&&&&&&&&&&& 1828 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&& 251431 |
| Sorting result&&&&&&&&&&&&&&&& |& 0.000066 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&& 14 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.000218 | 0.000000 |&& 0.000999 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| removing tmp table&&&&&&&&&&&& |& 0.000093 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| query end&&&&&&&&&&&&&&&&&&&&& |& 0.000005 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& |& 0.000010 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000012 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Waiting for query cache lock&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000026 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 4 |
| Waiting for query cache lock&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| storing result in query cache& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| cleaning up&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
31 rows in set (0.00 sec)
同时可以用iostat看到硬盘的IO &rkB/s&非常高
# iostat -x /dev/sda8 &2 10
Linux 2.6.18-164.el5 (photo)&&& 01/15/2013
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 0.68&&& 0.00&&& 0.29&&& 0.44&& 98.59
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&& 46.27&& 0.00 344.28& 0.00 39323.38&&& 0.00 19661.69&&&& 0.00&& 114.22&&&& 2.41&&& 6.92&& 2.89& 99.45
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 1.12&&& 0.00&&& 0.94&& 11.18&& 86.76
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&& 31.84&& 1.00 327.86& 1.00 32636.82&& 15.92 16318.41&&&& 7.96&&& 99.29&&&& 2.30&&& 7.02&& 3.02& 99.45
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 1.50&&& 0.00&&& 1.06&& 10.94&& 86.50
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&& 83.92&& 0.00 348.74& 0.00 43907.54&&& 0.00 21953.77&&&& 0.00&& 125.90&&&& 2.47&&& 7.10&& 2.88 100.55
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 1.62&&& 0.00&&& 1.12&& 11.62&& 85.63
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&& 64.18&& 0.00 365.17& 0.00 49532.34&&& 0.00 24766.17&&&& 0.00&& 135.64&&&& 2.42&&& 6.63&& 2.72& 99.45
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 0.50&&& 0.00&&& 0.88&& 11.44&& 87.19
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&&& 6.03&& 1.01 345.73& 1.01 9543.72&& 16.08& 4771.86&&&& 8.04&&& 27.57&&&& 1.21&&& 3.49&& 2.86& 99.25
2.第一次改写SQL
mysql& explain&
select AA.*,b.message from (select a.tid, a.subject,a.dateline,a.fid
from forum_thread AS a
where& a.highlight!=0 AND a.fid =113)& AA
forum_post_8 AS b ON AA.tid = b.tid
where b.first=1 and b.invisible=0
order by AA.
+----+-------------+------------+------+------------------------------+--------------+---------+--------------+--------+----------------+
| id | select_type | table&&&&& | type | possible_keys&&&&&&&&&&&&&&& | key&&&&&&&&& | key_len | ref&&&&&&&&& | rows&& | Extra&&&&&&&&& |
+----+-------------+------------+------+------------------------------+--------------+---------+--------------+--------+----------------+
|& 1 | PRIMARY&&&& | &derived2& | ALL& | NULL&&&&&&&&&&&&&&&&&&&&&&&& | NULL&&&&&&&& | NULL&&& | NULL&&&&&&&& |&&&& 99 | Using filesort |
|& 1 | PRIMARY&&&& | b&&&&&&&&& | ref& | invisible,displayorder,first | displayorder | 9&&&&&& | AA.tid,const |&&&&& 1 | Using where&&& |
|& 2 | DERIVED&&&& | a&&&&&&&&& | ref& | displayorder,typeid&&&&&&&&& |&typeid&&&&&& | 8&&&&&& |&&&&&&&&&&&&& | 108440 | Using where&&& |
+----+-------------+------------+------+------------------------------+--------------+---------+--------------+--------+----------------+
typeid的索引是(fid+typeid)的组合索引
执行改写后的SQL后查看执行耗时情况
+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration&&& | Query&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|&&&&&&& 1 |&20.&|
select * from (select a.tid, a.subject,a.dateline,a.fid
from forum_thread AS a
where& a.highlight!=0 AND a.fid =113)& AA
forum_post_8 AS b ON AA.tid = b.tid
where b.first=1 and b.invisible=0
order by AA.dateline desc |
+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql&&& show profile for query 1;
mysql&& show profile cpu,block io,memory,swaps for query 1;
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration& | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
| starting&&&&&&&&&&&&&&&&&&&&&& |& 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking query cache for query |& 0.000127 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Opening tables&&&&&&&&&&&&&&&& |& 4.817902 | 0.045993 |&& 0.010998 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| System lock&&&&&&&&&&&&&&&&&&& |& 0.021866 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| optimizing&&&&&&&&&&&&&&&&&&&& |& 0.000024 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& |& 0.507939 | 0.004000 |&& 0.002000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| preparing&&&&&&&&&&&&&&&&&&&&& |& 0.000038 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 25.623515 | 1.406786 |&& 0.519921 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.000026 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000049 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| optimizing&&&&&&&&&&&&&&&&&&&& |& 0.000018 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& |& 0.198725 | 0.001000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| preparing&&&&&&&&&&&&&&&&&&&&& |& 0.000026 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sorting result&&&&&&&&&&&&&&&& |& 0.000062 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.998911 | 0.003999 |&& 0.004999 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 1.061145 | 0.001000 |&& 0.003000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000011 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.008693 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000007 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| query end&&&&&&&&&&&&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| removing tmp table&&&&&&&&&&&& |& 0.000007 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& |& 0.000010 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000019 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000007 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| storing result in query cache& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| cleaning up&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
39 rows in set (0.00 sec)
mysql&& show profile cpu,CONTEXT SWITCHES,PAGE FAULTS for query 1;
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration& | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Page_faults_major | Page_faults_minor |
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| starting&&&&&&&&&&&&&&&&&&&&&& |& 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| Waiting for query cache lock&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking query cache for query |& 0.000127 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&& 30 |
| checking permissions&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Opening tables&&&&&&&&&&&&&&&& |& 4.817902 | 0.045993 |&& 0.010998 |&&&&&&&&&&&&&& 374 |&&&&&&&&&&&&&&&&&& 3 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&& 998 |
| System lock&&&&&&&&&&&&&&&&&&& |& 0.021866 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&&& 33 |
| optimizing&&&&&&&&&&&&&&&&&&&& |& 0.000024 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&& 10 |
| statistics&&&&&&&&&&&&&&&&&&&& |& 0.507939 | 0.004000 |&& 0.002000 |&&&&&&&&&&&&&&& 37 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&& 120 |
| preparing&&&&&&&&&&&&&&&&&&&&& |& 0.000038 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&& 10 |
| executing&&&&&&&&&&&&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| Sending data&&&&&&&&&&&&&&&&&& | 25.623515 | 1.406786 |&& 0.519921 |&&&&&&&&&&&& 13625 |&&&&&&&&&&&&&&&& 158 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&& 74911 |
| Waiting for query cache lock&& |& 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.000026 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000049 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| optimizing&&&&&&&&&&&&&&&&&&&& |& 0.000018 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| statistics&&&&&&&&&&&&&&&&&&&& |& 0.198725 | 0.001000 |&& 0.000000 |&&&&&&&&&&&&&&& 11 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&& 45 |
| preparing&&&&&&&&&&&&&&&&&&&&& |& 0.000026 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sorting result&&&&&&&&&&&&&&&& |& 0.000062 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 4 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.998911 | 0.003999 |&& 0.004999 |&&&&&&&&&&&&&&& 46 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 131 |
| Waiting for query cache lock&& |& 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 1.061145 | 0.001000 |&& 0.003000 |&&&&&&&&&&&&&&& 36 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 119 |
| Waiting for query cache lock&& |& 0.000011 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.008693 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 2 |&&&&&&&&&&&&&&&&&& 3 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 8 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000007 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| query end&&&&&&&&&&&&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| removing tmp table&&&&&&&&&&&& |& 0.000007 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& |& 0.000010 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000019 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 2 |
| Waiting for query cache lock&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000007 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Waiting for query cache lock&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| storing result in query cache& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| cleaning up&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
39 rows in set (0.00 sec)
同样在iostat看到 磁盘IO read 仍然比较高，但队列长度avgrq-sz比之前小。
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&& 48.50&& 7.00 879.00& 6.00 4.00 24018.00&&& 52.00&&& 54.40&&&& 4.39&&& 4.82&& 1.13 100.05
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 0.50&&& 0.00&&& 0.94&& 11.06&& 87.50
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&& 15.00&& 0.00 524.00& 0.00 20740.00&&& 0.00 10370.00&&&& 0.00&&& 39.58&&&& 3.41&&& 6.69&& 1.91 100.05
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 0.12&&& 0.00&&& 0.75&& 11.62&& 87.51
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&&& 3.50&& 0.00 328.00& 0.00 8108.00&&& 0.00& 4054.00&&&& 0.00&&& 24.72&&&& 2.99&&& 9.18&& 3.05 100.05
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 0.25&&& 0.00&&& 0.81&& 11.44&& 87.50
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&&& 3.00&& 4.00 432.50& 1.00 13496.00&& 40.00& 6748.00&&& 20.00&&& 31.22&&&& 3.13&&& 7.22&& 2.31 100.05
3.第二次改写SQL
对sql进行进一步分析
子查询的执行时间其实还有优化的余地，首先看了下子查询中的数据分别情况
mysql& select a.highlight,count(*) from forum_thread AS a group by a.
+-----------+----------+
| highlight | count(*) |
+-----------+----------+
|&&&&&&&& 0 |& 1522463 |
|&&&&&&&& 1 |&&&& 5576 |
|&&&&&&&& 2 |&&&&&& 13 |
|&&&&&&&& 3 |&&&&&&& 1 |
|&&&&&&&& 4 |&&&&&&& 9 |
|&&&&&&&& 5 |&&&&&&& 9 |
|&&&&&&&& 6 |&&&&&&& 1 |
|&&&&&&&& 7 |&&&&&&& 7 |
|&&&&&&&& 8 |&&&&&&& 5 |
|&&&&&&& 40 |&&&&&&& 2 |
|&&&&&&& 41 |&&&&&& 55 |
|&&&&&&& 42 |&&&&&& 31 |
|&&&&&&& 43 |&&&&&&& 7 |
|&&&&&&& 44 |&&&&&& 34 |
|&&&&&&& 45 |&&&&&& 25 |
|&&&&&&& 46 |&&&&&&& 1 |
|&&&&&&& 47 |&&&&&&& 2 |
|&&&&&&& 48 |&&&&&&& 5 |
|&&&&&&& 63 |&&&&&&& 1 |
|&&&&&&& 70 |&&&&&&& 1 |
|&&&&&& 127 |&&&&&&& 6 |
+-----------+----------+
21 rows in set (18.71 sec)
mysql& select fid,count(*) from forum_thread group by fid order by 2
+------+----------+
| fid& | count(*) |
+------+----------+
| 1233 |&& 370531 |
|& 273 |&& 165592 |
|& 268 |&&& 69730 |
|& 266 |&&& 62790 |
|& 255 |&&& 49992 |
| 1118 |&&& 47293 |
|&&113 |&&& 44690 |
|& 147 |&&& 44318 |
|& 252 |&&& 43084 |
|& 253 |&&& 35864 |
|& 114 |&&& 33344 |
| 1170 |&&& 24051 |
| 1230 |&&& 23133 |
|& 269 |&&& 21613 |
|& 254 |&&& 20629 |
|& 256 |&&& 19073 |
| 1182 |&&& 17588 |
-----后面省略很多其它值
可以看到其实&highlight !=0 这个结果集是非常小的，如果有fid+highlight 的组合索引的话，子查询
select a.tid, a.subject,a.dateline,a.fid
from forum_thread AS a
where& a.highlight!=0 AND a.fid =113&
的索引范围扫描的结果集会非常小，比起原来只有fid的索引，需要回表过滤highlight=0的rows效率要高不少。
于是建立索引
ALTER TABLE `forum_thread`& & &ADD INDEX `fid_high` (`fid`, `highlight`);
这时第2步改写的sql的执行计划变为了：
mysql& explain& select AA.*,b.message from (select a.tid, a.subject,a.dateline,a.fid
&&& -& from forum_thread AS a
&&& -& where& a.highlight!=0 AND a.fid =113)& AA
&&& -& LEFT JOIN
&&& -&& forum_post_8 AS b ON AA.tid = b.tid
&&& -& where b.first=1 and b.invisible=0
&&& -& order by AA.
+----+-------------+------------+-------+------------------------------+--------------+---------+--------------+------+----------------+
| id | select_type | table&&&&& | type& | possible_keys&&&&&&&&&&&&&&& | key&&&&&&&&& | key_len | ref&&&&&&&&& | rows | Extra&&&&&&&&& |
+----+-------------+------------+-------+------------------------------+--------------+---------+--------------+------+----------------+
|& 1 | PRIMARY&&&& | &derived2& | ALL&& | NULL&&&&&&&&&&&&&&&&&&&&&&&& | NULL&&&&&&&& | NULL&&& | NULL&&&&&&&& |&& 99 | Using filesort |
|& 1 | PRIMARY&&&& | b&&&&&&&&& | ref&& | invisible,displayorder,first | displayorder | 9&&&&&& | AA.tid,const |&&& 1 | Using where&&& |
|& 2 | DERIVED&&&& | a&&&&&&&&& | range | displayorder,typeid,fid_high |&fid_high&&&& | 9&&&&&& | NULL&&&&&&&& |&&100&| Using where&&& |
+----+-------------+------------+-------+------------------------------+--------------+---------+--------------+------+----------------+
3 rows in set (0.00 sec)
--执行一次这条sql后查看执行的具体时间耗时情况
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration&& | Query&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|&&&&&&& 1 |&2.&| select * from (select a.tid, a.subject,a.dateline,a.fid
from forum_thread AS a
where& a.highlight&0 AND a.fid =113)& AA
forum_post_8 AS b ON AA.tid = b.tid
where b.first=1 and b.invisible=0
order by AA.dateline desc |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
执行时间是2s
mysql&& show profile cpu,block io,memory,swaps for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+-------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+--------------------------------+----------+----------+------------+--------------+---------------+-------+
| starting&&&&&&&&&&&&&&&&&&&&&& | 0.000022 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& | 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking query cache for query | 0.000105 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking permissions&&&&&&&&&& | 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking permissions&&&&&&&&&& | 0.000005 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Opening tables&&&&&&&&&&&&&&&& | 1.222125 | 0.027996 |&& 0.007999 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| System lock&&&&&&&&&&&&&&&&&&& | 0.000240 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| optimizing&&&&&&&&&&&&&&&&&&&& | 0.000013 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& | 0.124458 | 0.001000 |&& 0.002000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| preparing&&&&&&&&&&&&&&&&&&&&& | 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& | 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.450222 | 0.007999 |&& 0.009998 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& | 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.000034 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000220 | 0.000999 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| optimizing&&&&&&&&&&&&&&&&&&&& | 0.000013 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& | 0.025380 | 0.000000 |&& 0.001000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| preparing&&&&&&&&&&&&&&&&&&&&& | 0.000024 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sorting result&&&&&&&&&&&&&&&& | 0.000054 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.154623 | 0.003000 |&& 0.001000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& | 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.101302 | 0.003999 |&& 0.002000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& | 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.000834 | 0.001000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000063 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| query end&&&&&&&&&&&&&&&&&&&&& | 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| removing tmp table&&&&&&&&&&&& | 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& | 0.000010 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000014 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& | 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000012 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| storing result in query cache& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| logging slow query&&&&&&&&&&&& | 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| cleaning up&&&&&&&&&&&&&&&&&&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+-------+
38 rows in set (0.00 sec)
mysql&&& show profile cpu,CONTEXT SWITCHES,PAGE FAULTS for query 1;
+--------------------------------+----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Page_faults_major | Page_faults_minor |
+--------------------------------+----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| starting&&&&&&&&&&&&&&&&&&&&&& | 0.000022 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Waiting for query cache lock&& | 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking query cache for query | 0.000105 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 4 |
| checking permissions&&&&&&&&&& | 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking permissions&&&&&&&&&& | 0.000005 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Opening tables&&&&&&&&&&&&&&&& | 1.222125 | 0.027996 |&& 0.007999 |&&&&&&&&&&&&&& 378 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 986 |
| System lock&&&&&&&&&&&&&&&&&&& | 0.000240 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 4 |
| optimizing&&&&&&&&&&&&&&&&&&&& | 0.000013 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& | 0.124458 | 0.001000 |&& 0.002000 |&&&&&&&&&&&&&&& 36 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 102 |
| preparing&&&&&&&&&&&&&&&&&&&&& | 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| executing&&&&&&&&&&&&&&&&&&&&& | 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.450222 | 0.007999 |&& 0.009998 |&&&&&&&&&&&&&& 158 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 480 |
| Waiting for query cache lock&& | 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.000034 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 2 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000220 | 0.000999 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 3 |
| optimizing&&&&&&&&&&&&&&&&&&&& | 0.000013 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& | 0.025380 | 0.000000 |&& 0.001000 |&&&&&&&&&&&&&&&& 9 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&& 37 |
| preparing&&&&&&&&&&&&&&&&&&&&& | 0.000024 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sorting result&&&&&&&&&&&&&&&& | 0.000054 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 3 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.154623 | 0.003000 |&& 0.001000 |&&&&&&&&&&&&&&& 46 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 136 |
| Waiting for query cache lock&& | 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.101302 | 0.003999 |&& 0.002000 |&&&&&&&&&&&&&&& 31 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 115 |
| Waiting for query cache lock&& | 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.000834 | 0.001000 |&& 0.000000 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 8 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000063 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| query end&&&&&&&&&&&&&&&&&&&&& | 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| removing tmp table&&&&&&&&&&&& | 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& | 0.000010 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000014 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Waiting for query cache lock&& | 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000012 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| Waiting for query cache lock&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| storing result in query cache& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| logging slow query&&&&&&&&&&&& | 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| cleaning up&&&&&&&&&&&&&&&&&&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
+--------------------------------+----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
38 rows in set (0.00 sec)
最终这条sql的执行时间由最初的50s 优化到2s&
处理Copying to tmp table除了调整mysql的参数tmp_table_size，（这个参数的意思具体见
）更重要的是优化sql，减少IO读写。其实调整tmp_table_size的意义也仅仅在于调整后尽量不写on-disk
table，对于很高的read I/O 导致的性能问题帮助并不大。
根据mysql手册上面所说的
Copying to tmp table
The server is copying to a temporary table in memory.
当mysql出现Copying to tmp table的事件的时候首先mysql引擎将数据写入内存里的临时表，这个临时表的大小由参数tmp_table_size决定，
事实上另外一个参数max_heap_table_size&也会对实际的临时表的大小有影响，实际值会取这2个参数中最小的那个。
如果需要的的临时表大小超过了tmp_table_size，则写到硬盘上的&on-disk&MyISAM&table&上,这个时候iostat会出现比较高的wkB/s
当有多条sql都同时出现Copying to tmp table的时候，服务器的IO带来非常大的压力，这时基本上其它正常的sql都会由于IO的竞争变得非常慢，阻塞了其它sql执行，表现到应用层面上就是相关的页面打不开，数据查询显示不出来。大量的sending data以及copying to
tmp table等待
关于Copying to tmp table的介绍的一篇文章
备注1：show profile 参数
ALL - displays all information&
BLOCK IO - displays counts for block input and output operations&
CONTEXT SWITCHES - displays counts for voluntary and involuntary context switches
IPC - displays counts for messages sent and received&
MEMORY - is not currently implemented&
PAGE FAULTS - displays counts for major and minor page faults&
SOURCE - displays the names of functions from the source code, together with the name and line number of the file in which the function occurs&
SWAPS - displays swap counts&
mysql& show variables like 'profiling_history_size'&&&&&&&&&&&&&&&&&&&&
+------------------------+-------+
| Variable_name&&&&&&&&& | Value |
+------------------------+-------+
| profiling_history_size | 15&&& |
+------------------------+-------+
1 row in set (0.00 sec)
mysql& show variables like '%profiling%';
+------------------------+-------+
| Variable_name&&&&&&&&& | Value |
+------------------------+-------+
| have_profiling&&&&&&&& | YES&& |
| profiling&&&&&&&&&&&&& | OFF&& |
| profiling_history_size | 15&&& |
+------------------------+-------+
3 rows in set (0.00 sec)
mysql& set profiling=1; #开启
备注2.&mysql
profile 相关介绍
A&voluntary context switch&occurs when a&&gives up the CPU
it is running on voluntarily, either by a specialized call (()
or similar) or by starting waiting (e.g. a blocking I/O call).
Contrast with the&.
An&involuntary context switch&means that the kernel takes away the CPU from a running&&to
assign it to another process. This can occur because:
The process was already running for too long.A process with higher priority needs the CPU right now.
Technically this is done by a hardware interrupt that passes control to the kernel which then passes the control to another chosen process.
----------------------------------------------
page_fault
If the page is loaded in memory at the time the fault is generated, but is not marked in the&&as being loaded in memory, then it is called a minor or soft page fault. The page fault handler in the&&merely needs to make the entry for that page in the memory management unit point to the page in memory and indicate that the page it does not need to read the page into memory. This could happen if the&&by different programs and the page is already brought into memory for other programs. The page could also have been removed from a process's Working Set, but not yet written to disk or erased, such as in operating systems that use Secondary Page
Caching. For example, HP OpenVMS may remove a page that does not need to be written to disk (if it has remained unchanged since it was last read from disk, for example) and place it on a Free Page List if the working set is deemed too large. However, the page
contents are not overwritten until the page is assigned elsewhere, meaning it is still available if it is referenced by the original process before being allocated. Since these faults do not involve disk latency, they are faster and less expensive than major
page faults.
If the page is not loaded in memory at the time the fault is generated, then it is called a major or hard page fault. The page fault handler in the operating system needs to find a free page in memory, or choose another
non-free page in memory to be used for this page's data, which might be used by another process. In this latter case, the OS first needs to write out the data in that page if it hasn't already been written out since it was last modified, and mark that page
as not being loaded into memory in its process page table. Once the page has thus been made available, the OS can read the data for the new page into the physical page, and then make the entry for that page in the memory management unit point to the page in
memory and indicate that the page is loaded in memory.[]&Major faults are more expensive than minor page faults and add disk latency to the interrupted program's execution. This is the mechanism used by an operating system to increase the
amount of program memory available on demand. The operating system delays loading parts of the program from disk until the program attempts to use it and the page fault is generated.
备注3.清理mysql缓存方法
（不停库没法完全清理mysql的缓存）
echo 3 & /proc/sys/vm/drop_
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：8508次
排名：千里之外
(1)(1)(5)(2)}
当前位置：
最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/my 发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。 用命令杀掉此查询 /opt/mysql/bin/mysqladmin kill&&进程号； 后面的查询一下子都好了。&&ok, 找到了问题的原因，此查询效率太低。问一下程序员，找来此查询的代码，用工具进行一下简单分析。 （说明：这里是我不喜欢mysql的原因之一，mysql我不知道从哪里能看到内存正在执行哪些sql,以及完整的sql是什么。） explain& & SELECT a.* , IF(b.`gid` IS NULL , 0, SUM( b.`mark` )) AS `score` ,
IF(c.`b_times` IS NULL ,0, c.`b_times`) AS `day_b_times`&& FROM `league_info` AS a LEFT JOIN `mark_logs` AS b ON b.`day_date` =
AND b.`gid` = a.`id` LEFT JOIN&&`visit_stat` AS c ON c.`stat_id` =
a.`id` AND c.`type` = 'league'
AND c.`day`='' WHERE a.`validate`='1' GROUP BY&&a.`id`
ORDER BY day_b_times DESC, a.`id`; +----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys& && && && && &&&| key&
&& && && &&&| key_len | ref& && && && && &&&| rows | Extra& && && && &&
&& && && && && && && && & | +----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+ |&&1 | SIMPLE& && &| a& &&&| ref&&| league_info_idx4& && && && &&&|
league_info_idx4 | 1& && & | const& && && && && &| 1441 | U
U Using filesort | |&&1 | SIMPLE& && &| b& &&&| ref&&| mark_logs_idx1,mark_logs_idx2 |
mark_logs_idx1& &| 4& && & | new5jia1.a.id& && & |& &56 |& && && && &&
&& && && && && && && && && && & | |&&1 | SIMPLE& && &| c& &&&| ref&&| visit_stat_idx1& && && && && &|
visit_stat_idx1&&| 26& && &| new5jia1.a.id,const |& &10 |& && && && &&
&& && && && && && && && && && & | +----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+
看了一下，其实此查询嵌套用得不好，作为程序员应该尽量避免用 not in ，in,&&left join&&,right&&join
等等，不过这些不归我管，我只能提一些建议。 （顺便说一声：oracle里面可以用 exist ,not exist, minus等代替in ,not in&&效率高出很多 ） 此分析对我没有太大的作用，因此用google查询了一下，发现网上一篇文章讲得很好， （ [url]/4721079.html[/url] 我给转贴了，感兴趣可以看看） Copying to tmp table on disk The temporary result set was larger than
tmp_table_size and the
thread is now changing the in memory-based temporary table to a disk
based one to save memory.
哦，原来是这样的，如果查询超出了tmp_table_size的限制，那么mysql用/tmp保存查询结果，然后返回给客户端。 set global tmp_table_size=&&(200M)
再次运行此查询,用/opt/mysql/bin/my 进行观察,发现不会出现上述问题. 至此问题解决.
调节tmp_table_size&&的时候发现另外一些参数 Qcache_queries_in_cache&&在缓存中已注册的查询数目&& Qcache_inserts&&被加入到缓存中的查询数目&& Qcache_hits&&缓存采样数数目&& Qcache_lowmem_prunes&&因为缺少内存而被从缓存中删除的查询数目&& Qcache_not_cached&&没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)&& Qcache_free_memory&&查询缓存的空闲内存总数&& Qcache_free_blocks&&查询缓存中的空闲内存块的数目&& Qcache_total_blocks&&查询缓存中的块的总数目&&
Qcache_free_memory 可以缓存一些常用的查询,如果是常用的sql会被装载到内存。那样会增加数据库访问速度。
共有1个评论
有时候不正确的字段索引也会导致问题：
/happy/thread--1.html
后来检查是因为一些字段用了索引，mysql在查询的时候自动使用了&优化&，反而导致大量数据的copy to tmp table，比取消额外的索引多用了几秒钟的时间。
更多开发者职位上
有什么技术问题吗？
小编辑的其它问题
类似的话题如何减少 Copying to tmp table 的 开销时间_百度知道
如何减少 Copying to tmp table 的 开销时间
提问者采纳
如果是temp_table_size太小的话这个可能跟你查询的SQL语句有关。这个跟temp_table_size没有关系，有些语句比如distinct,order by之类的就需要copy到临时表之后再来进行处理
其他类似问题
为您推荐：
tmp的相关知识
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁Copying to tmp table 问题求解。。。 - V2EX
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
已注册用户请 &
Great Sites on MySQL
推荐管理工具
MySQL 相关项目
Copying to tmp table 问题求解。。。
17:38:55 +08:00 · 2052 次点击
MYSQL查询语句：SELECT t_ID,t_date_last,t_content,sub_ID,sub_name,u_name,u_pic FROM t1,sub2,user3 WHERE t_type = 't' AND t_status = '0' AND t_sid = sub_ID AND u_ID = t_user ORDER BY t_date_last DESC LIMIT 30从三个表里读取数据，t1表共 100768 行32.5 MB，sub2表共 1055 行939.1 KB，user3表共 10696 行1.2 MB
查询花费 1.8137 秒 其中 Copying To Tmp Table 1.7 s尝试了tmp_table_size= 无效t_type t_status t_sid sub_ID u_ID t_user t_date_last 全部索引。。。主机是Linode 1024求解~~~
8 回复 &| &直到
23:30:17 +08:00
& & 18:05:52 +08:00
因为你使用了order by.
去掉order by 会好很多
而且,你的索引重复度会特别高,索引已经没有意义了.
& & 19:02:25 +08:00
@ 确实快了。。。。但是排序的问题没法解决啊。。。 ：（
& & 21:41:29 +08:00 via Android
用desc来找问题啊
& & 09:04:51 +08:00
@ 啊？~~ 怎么整呢。。。
& & 06:21:23 +08:00 via Android
@ 把explain贴出来
& & 11:14:12 +08:00
@ 这里。。。求帮助~~
select_type table type
possible_keys key key_len ref rows
sub2 ALL PRIMARY NULL
U Using filesort
1 SIMPLE
ref t1_sid,t1_user,t1_status,t1_user_2,t1_user_3,t1_user_4
t1_sid 8 database.sub2.sub_ID 11
Using where
1 SIMPLE
PRIMARY PRIMARY 8 database.t1.t1_user
& & 11:27:29 +08:00
explain下面的语句, 多表查询最好用join, 你那种select where是很老的用法, mysql优化器不一定能正确使用索引
SELECT t.t_ID,t.t_date_last,t.t_content,t.sub_ID,s.sub_name,u.u_name,u.u_pic FROM t1 t
LEFT JOIN sub2 s ON s.sub_ID=t.t_sid
LEFT JOIN user3 u ON u.u_ID=t.t_user
WHERE t_type = 't' AND t_status = '0'
ORDER BY t.t_date_last DESC
LIMIT 30
& & 23:30:17 +08:00
@ 貌似速度确实有提示，感谢！
& · & 1644 人在线 & 最高记录 1847 & · &
创意工作者们的社区
World is powered by solitude
VERSION: 3.9.7.3 · 59ms · UTC 08:58 · PVG 16:58 · LAX 01:58 · JFK 04:58? Do have faith in what you're doing.1428人阅读
环境说明：
' Ver 8.42 Distrib 5.5.27, for Linux on x86_64
Table 为 innodb 引擎
1.原始sql的执行计划
(这条sql导致了mysql的性能问题，当它并发多条执行的时候影响其它正常sql的执行，大量sending data以及copying to tmp table等待)
mysql&& set profiling=1;
Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)
mysql& explain
&&& -&& SELECT a.tid, a.subject,a.dateline,a.fid,b.message
&&& -& FROM forum_thread AS a
&&& -& LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
&&& -& WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
&&& -& ORDER BY a.dateline DESC
&&& -& LIMIT 0,5;
+----+-------------+-------+--------+------------------------------+-----------+---------+-------------+-------+----------------------------------------------+
| id | select_type | table | type&& | possible_keys&&&&&&&&&&&&&&& | key&&&&&& | key_len | ref&&&&&&&& | rows& | Extra&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |
+----+-------------+-------+--------+------------------------------+-----------+---------+-------------+-------+----------------------------------------------+
|& 1 | SIMPLE&&&&& | b&&&& | ref&&& | invisible,displayorder,first | invisible | 1&&&&&& | const&&&&&& | 97042 | U&Using temporary; Using filesort |
|& 1 | SIMPLE&&&&& | a&&&& | eq_ref | PRIMARY,displayorder,typeid& | PRIMARY&& | 8&&&&&& | forum.b.tid |&&&& 1 | Using where&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |
+----+-------------+-------+--------+------------------------------+-----------+---------+-------------+-------+----------------------------------------------+
2 rows in set (1.29 sec)
mysql&& SELECT a.tid, a.subject,a.dateline,a.fid,b.message
&&& -& FROM forum_thread AS a
&&& -& LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
&&& -& WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
&&& -& ORDER BY a.dateline DESC
&&& -& LIMIT 0,5;
执行这条SQL后，总耗时为 52s 查看具体步骤的耗时
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration&&& | Query&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|&&&&&&& 1 |& 1. | explain
SELECT a.tid, a.subject,a.dateline,a.fid,b.message
FROM forum_thread AS a
LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
ORDER BY a.dateline DESC
LIMIT 0,5 |
|&&&&&&& 2 | 52.027014 | SELECT a.tid, a.subject,a.dateline,a.fid,b.message
FROM forum_thread AS a
LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
ORDER BY a.dateline DESC
LIMIT 0,5&&&&&&&&&& |& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &|
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql& show profile cpu,block io,memory,swaps for query 2;
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration& | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
| starting&&&&&&&&&&&&&&&&&&&&&& |& 0.000019 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking query cache for query |& 0.000083 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000005 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Opening tables&&&&&&&&&&&&&&&& |& 0.000020 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| System lock&&&&&&&&&&&&&&&&&&& |& 0.000008 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000038 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| optimizing&&&&&&&&&&&&&&&&&&&& |& 0.000015 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& |& 0.000113 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| preparing&&&&&&&&&&&&&&&&&&&&& |& 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Creating tmp table&&&&&&&&&&&& |& 0.000162 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
|&Copying to tmp table&&&&&&&&&& | 52.027014&| 4.992241 |&&
1.324799 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sorting result&&&&&&&&&&&&&&&& |& 0.000066 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.000218 | 0.000000 |&& 0.000999 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| removing tmp table&&&&&&&&&&&& |& 0.000093 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| query end&&&&&&&&&&&&&&&&&&&&& |& 0.000005 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& |& 0.000010 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000012 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000026 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| storing result in query cache& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| cleaning up&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
31 rows in set (0.00 sec)
mysql&& show profile cpu,CONTEXT SWITCHES,PAGE
FAULTS&for query 2;
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration& | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Page_faults_major | Page_faults_minor |
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| starting&&&&&&&&&&&&&&&&&&&&&& |& 0.000019 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Waiting for query cache lock&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking query cache for query |& 0.000083 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000005 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Opening tables&&&&&&&&&&&&&&&& |& 0.000020 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| System lock&&&&&&&&&&&&&&&&&&& |& 0.000008 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Waiting for query cache lock&& |& 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000038 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| optimizing&&&&&&&&&&&&&&&&&&&& |& 0.000015 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& |& 0.000113 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| preparing&&&&&&&&&&&&&&&&&&&&& |& 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Creating tmp table&&&&&&&&&&&& |& 0.000162 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 5 |
| executing&&&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
|&Copying to tmp table&&&&&&&&&& | 52.027014 | 4.992241 |&& 1.324799 |&&&&&&&&&&&& 17969 |&&&&&&&&&&&&&&& 1828 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&& 251431 |
| Sorting result&&&&&&&&&&&&&&&& |& 0.000066 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&& 14 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.000218 | 0.000000 |&& 0.000999 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| removing tmp table&&&&&&&&&&&& |& 0.000093 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| query end&&&&&&&&&&&&&&&&&&&&& |& 0.000005 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& |& 0.000010 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000012 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Waiting for query cache lock&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000026 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 4 |
| Waiting for query cache lock&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| storing result in query cache& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| cleaning up&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
31 rows in set (0.00 sec)
同时可以用iostat看到硬盘的IO &rkB/s&非常高
# iostat -x /dev/sda8 &2 10
Linux 2.6.18-164.el5 (photo)&&& 01/15/2013
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 0.68&&& 0.00&&& 0.29&&& 0.44&& 98.59
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&& 46.27&& 0.00 344.28& 0.00 39323.38&&& 0.00 19661.69&&&& 0.00&& 114.22&&&& 2.41&&& 6.92&& 2.89& 99.45
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 1.12&&& 0.00&&& 0.94&& 11.18&& 86.76
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&& 31.84&& 1.00 327.86& 1.00 32636.82&& 15.92 16318.41&&&& 7.96&&& 99.29&&&& 2.30&&& 7.02&& 3.02& 99.45
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 1.50&&& 0.00&&& 1.06&& 10.94&& 86.50
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&& 83.92&& 0.00 348.74& 0.00 43907.54&&& 0.00 21953.77&&&& 0.00&& 125.90&&&& 2.47&&& 7.10&& 2.88 100.55
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 1.62&&& 0.00&&& 1.12&& 11.62&& 85.63
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&& 64.18&& 0.00 365.17& 0.00 49532.34&&& 0.00 24766.17&&&& 0.00&& 135.64&&&& 2.42&&& 6.63&& 2.72& 99.45
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 0.50&&& 0.00&&& 0.88&& 11.44&& 87.19
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&&& 6.03&& 1.01 345.73& 1.01 9543.72&& 16.08& 4771.86&&&& 8.04&&& 27.57&&&& 1.21&&& 3.49&& 2.86& 99.25
2.第一次改写SQL
mysql& explain&
select AA.*,b.message from (select a.tid, a.subject,a.dateline,a.fid
from forum_thread AS a
where& a.highlight!=0 AND a.fid =113)& AA
forum_post_8 AS b ON AA.tid = b.tid
where b.first=1 and b.invisible=0
order by AA.
+----+-------------+------------+------+------------------------------+--------------+---------+--------------+--------+----------------+
| id | select_type | table&&&&& | type | possible_keys&&&&&&&&&&&&&&& | key&&&&&&&&& | key_len | ref&&&&&&&&& | rows&& | Extra&&&&&&&&& |
+----+-------------+------------+------+------------------------------+--------------+---------+--------------+--------+----------------+
|& 1 | PRIMARY&&&& | &derived2& | ALL& | NULL&&&&&&&&&&&&&&&&&&&&&&&& | NULL&&&&&&&& | NULL&&& | NULL&&&&&&&& |&&&& 99 | Using filesort |
|& 1 | PRIMARY&&&& | b&&&&&&&&& | ref& | invisible,displayorder,first | displayorder | 9&&&&&& | AA.tid,const |&&&&& 1 | Using where&&& |
|& 2 | DERIVED&&&& | a&&&&&&&&& | ref& | displayorder,typeid&&&&&&&&& |&typeid&&&&&& | 8&&&&&& |&&&&&&&&&&&&& | 108440 | Using where&&& |
+----+-------------+------------+------+------------------------------+--------------+---------+--------------+--------+----------------+
typeid的索引是(fid+typeid)的组合索引
执行改写后的SQL后查看执行耗时情况
+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration&&& | Query&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|&&&&&&& 1 |&20.&|
select * from (select a.tid, a.subject,a.dateline,a.fid
from forum_thread AS a
where& a.highlight!=0 AND a.fid =113)& AA
forum_post_8 AS b ON AA.tid = b.tid
where b.first=1 and b.invisible=0
order by AA.dateline desc |
+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql&&& show profile for query 1;
mysql&& show profile cpu,block io,memory,swaps for query 1;
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration& | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
| starting&&&&&&&&&&&&&&&&&&&&&& |& 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking query cache for query |& 0.000127 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Opening tables&&&&&&&&&&&&&&&& |& 4.817902 | 0.045993 |&& 0.010998 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| System lock&&&&&&&&&&&&&&&&&&& |& 0.021866 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| optimizing&&&&&&&&&&&&&&&&&&&& |& 0.000024 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& |& 0.507939 | 0.004000 |&& 0.002000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| preparing&&&&&&&&&&&&&&&&&&&&& |& 0.000038 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 25.623515 | 1.406786 |&& 0.519921 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.000026 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000049 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| optimizing&&&&&&&&&&&&&&&&&&&& |& 0.000018 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& |& 0.198725 | 0.001000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| preparing&&&&&&&&&&&&&&&&&&&&& |& 0.000026 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sorting result&&&&&&&&&&&&&&&& |& 0.000062 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.998911 | 0.003999 |&& 0.004999 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 1.061145 | 0.001000 |&& 0.003000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000011 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.008693 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000007 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| query end&&&&&&&&&&&&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| removing tmp table&&&&&&&&&&&& |& 0.000007 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& |& 0.000010 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000019 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000007 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| storing result in query cache& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| cleaning up&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
39 rows in set (0.00 sec)
mysql&& show profile cpu,CONTEXT SWITCHES,PAGE FAULTS for query 1;
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration& | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Page_faults_major | Page_faults_minor |
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| starting&&&&&&&&&&&&&&&&&&&&&& |& 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| Waiting for query cache lock&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking query cache for query |& 0.000127 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&& 30 |
| checking permissions&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking permissions&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Opening tables&&&&&&&&&&&&&&&& |& 4.817902 | 0.045993 |&& 0.010998 |&&&&&&&&&&&&&& 374 |&&&&&&&&&&&&&&&&&& 3 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&& 998 |
| System lock&&&&&&&&&&&&&&&&&&& |& 0.021866 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&&& 33 |
| optimizing&&&&&&&&&&&&&&&&&&&& |& 0.000024 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&& 10 |
| statistics&&&&&&&&&&&&&&&&&&&& |& 0.507939 | 0.004000 |&& 0.002000 |&&&&&&&&&&&&&&& 37 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&& 120 |
| preparing&&&&&&&&&&&&&&&&&&&&& |& 0.000038 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&& 10 |
| executing&&&&&&&&&&&&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| Sending data&&&&&&&&&&&&&&&&&& | 25.623515 | 1.406786 |&& 0.519921 |&&&&&&&&&&&& 13625 |&&&&&&&&&&&&&&&& 158 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&& 74911 |
| Waiting for query cache lock&& |& 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.000026 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000049 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| optimizing&&&&&&&&&&&&&&&&&&&& |& 0.000018 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| statistics&&&&&&&&&&&&&&&&&&&& |& 0.198725 | 0.001000 |&& 0.000000 |&&&&&&&&&&&&&&& 11 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&& 45 |
| preparing&&&&&&&&&&&&&&&&&&&&& |& 0.000026 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sorting result&&&&&&&&&&&&&&&& |& 0.000062 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 4 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.998911 | 0.003999 |&& 0.004999 |&&&&&&&&&&&&&&& 46 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 131 |
| Waiting for query cache lock&& |& 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 1.061145 | 0.001000 |&& 0.003000 |&&&&&&&&&&&&&&& 36 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 119 |
| Waiting for query cache lock&& |& 0.000011 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& |& 0.008693 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 2 |&&&&&&&&&&&&&&&&&& 3 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 8 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |& 0.000007 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| query end&&&&&&&&&&&&&&&&&&&&& |& 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| removing tmp table&&&&&&&&&&&& |& 0.000007 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& |& 0.000010 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000019 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 2 |
| Waiting for query cache lock&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000007 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Waiting for query cache lock&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| storing result in query cache& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| logging slow query&&&&&&&&&&&& |& 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| cleaning up&&&&&&&&&&&&&&&&&&& |& 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
39 rows in set (0.00 sec)
同样在iostat看到 磁盘IO read 仍然比较高，但队列长度avgrq-sz比之前小。
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&& 48.50&& 7.00 879.00& 6.00 4.00 24018.00&&& 52.00&&& 54.40&&&& 4.39&&& 4.82&& 1.13 100.05
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 0.50&&& 0.00&&& 0.94&& 11.06&& 87.50
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&& 15.00&& 0.00 524.00& 0.00 20740.00&&& 0.00 10370.00&&&& 0.00&&& 39.58&&&& 3.41&&& 6.69&& 1.91 100.05
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 0.12&&& 0.00&&& 0.75&& 11.62&& 87.51
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&&& 3.50&& 0.00 328.00& 0.00 8108.00&&& 0.00& 4054.00&&&& 0.00&&& 24.72&&&& 2.99&&& 9.18&& 3.05 100.05
avg-cpu:& %user&& %nice&&& %sys %iowait&& %idle
&&&&&&&&&& 0.25&&& 0.00&&& 0.81&& 11.44&& 87.50
Device:&&& rrqm/s wrqm/s&& r/s&& w/s& rsec/s& wsec/s&&& rkB/s&&& wkB/s avgrq-sz avgqu-sz&& await& svctm& %util
sda8&&&&&&&& 3.00&& 4.00 432.50& 1.00 13496.00&& 40.00& 6748.00&&& 20.00&&& 31.22&&&& 3.13&&& 7.22&& 2.31 100.05
3.第二次改写SQL
对sql进行进一步分析
子查询的执行时间其实还有优化的余地，首先看了下子查询中的数据分别情况
mysql& select a.highlight,count(*) from forum_thread AS a group by a.
+-----------+----------+
| highlight | count(*) |
+-----------+----------+
|&&&&&&&& 0 |& 1522463 |
|&&&&&&&& 1 |&&&& 5576 |
|&&&&&&&& 2 |&&&&&& 13 |
|&&&&&&&& 3 |&&&&&&& 1 |
|&&&&&&&& 4 |&&&&&&& 9 |
|&&&&&&&& 5 |&&&&&&& 9 |
|&&&&&&&& 6 |&&&&&&& 1 |
|&&&&&&&& 7 |&&&&&&& 7 |
|&&&&&&&& 8 |&&&&&&& 5 |
|&&&&&&& 40 |&&&&&&& 2 |
|&&&&&&& 41 |&&&&&& 55 |
|&&&&&&& 42 |&&&&&& 31 |
|&&&&&&& 43 |&&&&&&& 7 |
|&&&&&&& 44 |&&&&&& 34 |
|&&&&&&& 45 |&&&&&& 25 |
|&&&&&&& 46 |&&&&&&& 1 |
|&&&&&&& 47 |&&&&&&& 2 |
|&&&&&&& 48 |&&&&&&& 5 |
|&&&&&&& 63 |&&&&&&& 1 |
|&&&&&&& 70 |&&&&&&& 1 |
|&&&&&& 127 |&&&&&&& 6 |
+-----------+----------+
21 rows in set (18.71 sec)
mysql& select fid,count(*) from forum_thread group by fid order by 2
+------+----------+
| fid& | count(*) |
+------+----------+
| 1233 |&& 370531 |
|& 273 |&& 165592 |
|& 268 |&&& 69730 |
|& 266 |&&& 62790 |
|& 255 |&&& 49992 |
| 1118 |&&& 47293 |
|&&113 |&&& 44690 |
|& 147 |&&& 44318 |
|& 252 |&&& 43084 |
|& 253 |&&& 35864 |
|& 114 |&&& 33344 |
| 1170 |&&& 24051 |
| 1230 |&&& 23133 |
|& 269 |&&& 21613 |
|& 254 |&&& 20629 |
|& 256 |&&& 19073 |
| 1182 |&&& 17588 |
-----后面省略很多其它值
可以看到其实&highlight !=0 这个结果集是非常小的，如果有fid+highlight 的组合索引的话，子查询
select a.tid, a.subject,a.dateline,a.fid
from forum_thread AS a
where& a.highlight!=0 AND a.fid =113&
的索引范围扫描的结果集会非常小，比起原来只有fid的索引，需要回表过滤highlight=0的rows效率要高不少。
于是建立索引
ALTER TABLE `forum_thread`& & &ADD INDEX `fid_high` (`fid`, `highlight`);
这时第2步改写的sql的执行计划变为了：
mysql& explain& select AA.*,b.message from (select a.tid, a.subject,a.dateline,a.fid
&&& -& from forum_thread AS a
&&& -& where& a.highlight!=0 AND a.fid =113)& AA
&&& -& LEFT JOIN
&&& -&& forum_post_8 AS b ON AA.tid = b.tid
&&& -& where b.first=1 and b.invisible=0
&&& -& order by AA.
+----+-------------+------------+-------+------------------------------+--------------+---------+--------------+------+----------------+
| id | select_type | table&&&&& | type& | possible_keys&&&&&&&&&&&&&&& | key&&&&&&&&& | key_len | ref&&&&&&&&& | rows | Extra&&&&&&&&& |
+----+-------------+------------+-------+------------------------------+--------------+---------+--------------+------+----------------+
|& 1 | PRIMARY&&&& | &derived2& | ALL&& | NULL&&&&&&&&&&&&&&&&&&&&&&&& | NULL&&&&&&&& | NULL&&& | NULL&&&&&&&& |&& 99 | Using filesort |
|& 1 | PRIMARY&&&& | b&&&&&&&&& | ref&& | invisible,displayorder,first | displayorder | 9&&&&&& | AA.tid,const |&&& 1 | Using where&&& |
|& 2 | DERIVED&&&& | a&&&&&&&&& | range | displayorder,typeid,fid_high |&fid_high&&&& | 9&&&&&& | NULL&&&&&&&& |&&100&| Using where&&& |
+----+-------------+------------+-------+------------------------------+--------------+---------+--------------+------+----------------+
3 rows in set (0.00 sec)
--执行一次这条sql后查看执行的具体时间耗时情况
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration&& | Query&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|&&&&&&& 1 |&2.&| select * from (select a.tid, a.subject,a.dateline,a.fid
from forum_thread AS a
where& a.highlight&0 AND a.fid =113)& AA
forum_post_8 AS b ON AA.tid = b.tid
where b.first=1 and b.invisible=0
order by AA.dateline desc |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
执行时间是2s
mysql&& show profile cpu,block io,memory,swaps for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+-------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+--------------------------------+----------+----------+------------+--------------+---------------+-------+
| starting&&&&&&&&&&&&&&&&&&&&&& | 0.000022 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& | 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking query cache for query | 0.000105 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking permissions&&&&&&&&&& | 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| checking permissions&&&&&&&&&& | 0.000005 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Opening tables&&&&&&&&&&&&&&&& | 1.222125 | 0.027996 |&& 0.007999 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| System lock&&&&&&&&&&&&&&&&&&& | 0.000240 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| optimizing&&&&&&&&&&&&&&&&&&&& | 0.000013 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& | 0.124458 | 0.001000 |&& 0.002000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| preparing&&&&&&&&&&&&&&&&&&&&& | 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& | 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.450222 | 0.007999 |&& 0.009998 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& | 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.000034 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000220 | 0.000999 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| optimizing&&&&&&&&&&&&&&&&&&&& | 0.000013 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& | 0.025380 | 0.000000 |&& 0.001000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| preparing&&&&&&&&&&&&&&&&&&&&& | 0.000024 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sorting result&&&&&&&&&&&&&&&& | 0.000054 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.154623 | 0.003000 |&& 0.001000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& | 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.101302 | 0.003999 |&& 0.002000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& | 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.000834 | 0.001000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000063 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| query end&&&&&&&&&&&&&&&&&&&&& | 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| removing tmp table&&&&&&&&&&&& | 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& | 0.000010 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000014 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& | 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000012 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| Waiting for query cache lock&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| storing result in query cache& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| logging slow query&&&&&&&&&&&& | 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
| cleaning up&&&&&&&&&&&&&&&&&&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&& 0 |&&&&&&&&&&&& 0 |&&&& 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+-------+
38 rows in set (0.00 sec)
mysql&&& show profile cpu,CONTEXT SWITCHES,PAGE FAULTS for query 1;
+--------------------------------+----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Page_faults_major | Page_faults_minor |
+--------------------------------+----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| starting&&&&&&&&&&&&&&&&&&&&&& | 0.000022 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Waiting for query cache lock&& | 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking query cache for query | 0.000105 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 4 |
| checking permissions&&&&&&&&&& | 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| checking permissions&&&&&&&&&& | 0.000005 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Opening tables&&&&&&&&&&&&&&&& | 1.222125 | 0.027996 |&& 0.007999 |&&&&&&&&&&&&&& 378 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 986 |
| System lock&&&&&&&&&&&&&&&&&&& | 0.000240 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 4 |
| optimizing&&&&&&&&&&&&&&&&&&&& | 0.000013 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& | 0.124458 | 0.001000 |&& 0.002000 |&&&&&&&&&&&&&&& 36 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 102 |
| preparing&&&&&&&&&&&&&&&&&&&&& | 0.000025 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| executing&&&&&&&&&&&&&&&&&&&&& | 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.450222 | 0.007999 |&& 0.009998 |&&&&&&&&&&&&&& 158 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 480 |
| Waiting for query cache lock&& | 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.000034 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 2 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000220 | 0.000999 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 3 |
| optimizing&&&&&&&&&&&&&&&&&&&& | 0.000013 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| statistics&&&&&&&&&&&&&&&&&&&& | 0.025380 | 0.000000 |&& 0.001000 |&&&&&&&&&&&&&&&& 9 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&& 37 |
| preparing&&&&&&&&&&&&&&&&&&&&& | 0.000024 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| executing&&&&&&&&&&&&&&&&&&&&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sorting result&&&&&&&&&&&&&&&& | 0.000054 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 3 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.154623 | 0.003000 |&& 0.001000 |&&&&&&&&&&&&&&& 46 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 136 |
| Waiting for query cache lock&& | 0.000003 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.101302 | 0.003999 |&& 0.002000 |&&&&&&&&&&&&&&& 31 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&& 115 |
| Waiting for query cache lock&& | 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.000834 | 0.001000 |&& 0.000000 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 8 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000063 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| query end&&&&&&&&&&&&&&&&&&&&& | 0.000004 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| removing tmp table&&&&&&&&&&&& | 0.000006 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| closing tables&&&&&&&&&&&&&&&& | 0.000010 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 1 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000014 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| Waiting for query cache lock&& | 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000012 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 1 |
| Waiting for query cache lock&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| storing result in query cache& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| logging slow query&&&&&&&&&&&& | 0.000001 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
| cleaning up&&&&&&&&&&&&&&&&&&& | 0.000002 | 0.000000 |&& 0.000000 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&& 0 |
+--------------------------------+----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
38 rows in set (0.00 sec)
最终这条sql的执行时间由最初的50s 优化到2s&
处理Copying to tmp table除了调整mysql的参数tmp_table_size，（这个参数的意思具体见
）更重要的是优化sql，减少IO读写。其实调整tmp_table_size的意义也仅仅在于调整后尽量不写on-disk
table，对于很高的read I/O 导致的性能问题帮助并不大。
根据mysql手册上面所说的
Copying to tmp table
The server is copying to a temporary table in memory.
当mysql出现Copying to tmp table的事件的时候首先mysql引擎将数据写入内存里的临时表，这个临时表的大小由参数tmp_table_size决定，
事实上另外一个参数max_heap_table_size&也会对实际的临时表的大小有影响，实际值会取这2个参数中最小的那个。
如果需要的的临时表大小超过了tmp_table_size，则写到硬盘上的&on-disk&MyISAM&table&上,这个时候iostat会出现比较高的wkB/s
当有多条sql都同时出现Copying to tmp table的时候，服务器的IO带来非常大的压力，这时基本上其它正常的sql都会由于IO的竞争变得非常慢，阻塞了其它sql执行，表现到应用层面上就是相关的页面打不开，数据查询显示不出来。大量的sending data以及copying to
tmp table等待
关于Copying to tmp table的介绍的一篇文章
备注1：show profile 参数
ALL - displays all information&
BLOCK IO - displays counts for block input and output operations&
CONTEXT SWITCHES - displays counts for voluntary and involuntary context switches
IPC - displays counts for messages sent and received&
MEMORY - is not currently implemented&
PAGE FAULTS - displays counts for major and minor page faults&
SOURCE - displays the names of functions from the source code, together with the name and line number of the file in which the function occurs&
SWAPS - displays swap counts&
mysql& show variables like 'profiling_history_size'&&&&&&&&&&&&&&&&&&&&
+------------------------+-------+
| Variable_name&&&&&&&&& | Value |
+------------------------+-------+
| profiling_history_size | 15&&& |
+------------------------+-------+
1 row in set (0.00 sec)
mysql& show variables like '%profiling%';
+------------------------+-------+
| Variable_name&&&&&&&&& | Value |
+------------------------+-------+
| have_profiling&&&&&&&& | YES&& |
| profiling&&&&&&&&&&&&& | OFF&& |
| profiling_history_size | 15&&& |
+------------------------+-------+
3 rows in set (0.00 sec)
mysql& set profiling=1; #开启
备注2.&mysql
profile 相关介绍
A&voluntary context switch&occurs when a&&gives up the CPU
it is running on voluntarily, either by a specialized call (()
or similar) or by starting waiting (e.g. a blocking I/O call).
Contrast with the&.
An&involuntary context switch&means that the kernel takes away the CPU from a running&&to
assign it to another process. This can occur because:
The process was already running for too long.A process with higher priority needs the CPU right now.
Technically this is done by a hardware interrupt that passes control to the kernel which then passes the control to another chosen process.
----------------------------------------------
page_fault
If the page is loaded in memory at the time the fault is generated, but is not marked in the&&as being loaded in memory, then it is called a minor or soft page fault. The page fault handler in the&&merely needs to make the entry for that page in the memory management unit point to the page in memory and indicate that the page it does not need to read the page into memory. This could happen if the&&by different programs and the page is already brought into memory for other programs. The page could also have been removed from a process's Working Set, but not yet written to disk or erased, such as in operating systems that use Secondary Page
Caching. For example, HP OpenVMS may remove a page that does not need to be written to disk (if it has remained unchanged since it was last read from disk, for example) and place it on a Free Page List if the working set is deemed too large. However, the page
contents are not overwritten until the page is assigned elsewhere, meaning it is still available if it is referenced by the original process before being allocated. Since these faults do not involve disk latency, they are faster and less expensive than major
page faults.
If the page is not loaded in memory at the time the fault is generated, then it is called a major or hard page fault. The page fault handler in the operating system needs to find a free page in memory, or choose another
non-free page in memory to be used for this page's data, which might be used by another process. In this latter case, the OS first needs to write out the data in that page if it hasn't already been written out since it was last modified, and mark that page
as not being loaded into memory in its process page table. Once the page has thus been made available, the OS can read the data for the new page into the physical page, and then make the entry for that page in the memory management unit point to the page in
memory and indicate that the page is loaded in memory.[]&Major faults are more expensive than minor page faults and add disk latency to the interrupted program's execution. This is the mechanism used by an operating system to increase the
amount of program memory available on demand. The operating system delays loading parts of the program from disk until the program attempts to use it and the page fault is generated.
备注3.清理mysql缓存方法
（不停库没法完全清理mysql的缓存）
echo 3 & /proc/sys/vm/drop_
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：8508次
排名：千里之外
(1)(1)(5)(2)}
我要回帖
更多推荐
- ·台湾假期比大陆多吗有哪些节日和大陆一样
- ·低学历的工作好工作有哪些。？
- ·使用HRhr人力资源管理信息系统系统需要注意什么？
- ·人员招聘在人力资源管理中的作用管理系统有什么主要功能？
- ·杨万民思想政治教学研究科研工作简介
- ·17乘多少986乘78等于多少986
- ·a koala bear还是an koala
- ·湖南教育厅工商学院是不是省教育厅规定的
- ·求有没有数学解答详细的软件解答，数学！
- ·帮忙日文看一下这个日文是什么意思啊
- ·天津南海路到泰达集团在天津的地位一中
- ·疯狂的长河流声 吴象彬电子书之下电子书
- ·父亲身体心脏一直心脏不太好如何调理，有点担心，想学一下心肺复苏术有没有
- ·草市街树德中学小升初摇号应该摇号在哪个中学
- ·把一个小数取小数的近似数教案，文文结果是1.8，博博是1.80，谁的更精确，为什么
- ·隧道窑白红砖烧结砖隧道窑视频的压力是多少
- ·kindly 可以代替 please kindly notice么
- ·道德是什么与素养作文议论文
- ·我已46那里有学计算机我后悔了
- ·甲胎蛋白200多严重吗连读查三年都是13
- ·如何减少 Copyingcopy to tmp tablee 的 开销时间
- ·适用于1.5mw风机力矩维护的液压站力矩对照表
- ·夜班保安急急急招聘………
- ·我本来是打算先pass打算见女网友,她惊讶问为什么，没想到她给我先pass了，
- ·promotionimprovement是什么意思思
- ·长八米高二点五长10米宽14米高5米求面积二点四米求有多少立方
- ·杵球手指杵了关节变粗大有什么作用
- ·小芳抱怨自己的多付出少抱怨多理解换不来同桌的原谅.我会用什么恰当的名句勉励她
- ·占长办的英话碎尸英语怎么写写
- ·为什么欧洲造不出手机旅游造资料是否对其他人有影响
- ·葡萄糖6葡萄糖-6-磷酸脱氢酶酶可以喝豆浆吗
- ·赵一龙个人资料这个名字怎么样
- ·罗志祥巡回演唱会极限男人帮罗志祥扮女装是第几期回去吗
- ·苏DB922D的车主叫什么名字
- ·二胡演奏让二胡声音低沉沉深厚犷像什么
- ·双色球万能12码必中6算法必中6红
