MySQL中copying to tmp table事件的相关处理

已于 2025-02-04 15:46:49 修改
阅读量7.4k 收藏 4
点赞数
分类专栏： MySql 文章标签： mysql 索引 性能 优化
于 2013-01-15 20:33:10 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/db_bili/article/details/8507233
版权
本文探讨了MySQL中遇到'copying to tmp table'事件的问题，通过逐步改写SQL，从原始的查询开始，分析并优化执行计划。通过对论坛线程数据的查询，展示了如何减少group by操作的影响，提升查询效率。最后，文章还介绍了MySQL的profile参数、上下文切换概念以及清理缓存的方法。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

 

环境说明：

 版本
' 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)

mysql>  show profiles;
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 | Using where;  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 查看具体步骤的耗时
 
mysql> show profiles;
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration    | Query                                                                                                                                                                                                                                  |
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 |  1.28875000 | 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
LEFT JOIN
forum_post_8 AS b ON AA.tid = b.tid
where b.first=1 and b.invisible=0
order by AA.dateline desc ;

+----+-------------+------------+------+------------------------------+--------------+---------+--------------+--------+----------------+
| 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后查看执行耗时情况
mysql>  show profiles;
+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration    | Query                                                                                                                                                                                                                               |
+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 |  20.36569700 | 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
LEFT JOIN
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 48036.00  104.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;
+-----------+----------+
| 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 desc;
+------+----------+
| 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.dateline desc ;
+----+-------------+------------+-------+------------------------------+--------------+---------+--------------+------+----------------+
| 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后查看执行的具体时间耗时情况
mysql>  show profiles;
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                                                                                                              |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 |  2.07984400 | 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
LEFT JOIN
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 

4.结论

处理 Copying to tmp table除了调整mysql的参数tmp_table_size，（这个参数的意思具体见
MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables）更重要的是优化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 thread gives up the CPU it is running on voluntarily, either by a specialized call (sched_yield() or similar) or by starting waiting (e.g. a blocking I/O call).

Contrast with the  involuntary context switch.

An involuntary context switch means that the kernel takes away the CPU from a running process 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

Minor

If the page is loaded in memory at the time the fault is generated, but is not marked in the memory management unit as being loaded in memory, then it is called a minor or soft page fault. The page fault handler in the operating system 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 is loaded in memory; it does not need to read the page into memory. This could happen if the memory is shared 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.

Major

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.[clarification needed] 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的缓存）

service mysqld stop;
sync;
echo 3 > /proc/sys/vm/drop_caches ;
service mysqld start;
MYSQL——数据库优化设计
庄小焱
01-05 988
摘要 本博文主要是针对，mysql中高cpu、高内存、高io、高sleep等复杂场景进行常用的优化设计方案的说明介绍。 一、占用CPU100%的优化 1.1 总体思路 1、先用操作系统命令 top 观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。 2、如果是mysqld 造成的，show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。 找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是
MySQL主从不一致问题相关处理
企业实战系列集 ●●● https://ximenjianxue.blog.youkuaiyun.com
09-26 1万+
某业务采取mysql的主从架构，但因为存储的问题，导致备库一直无法存储，数据同步一致性问题一直也未恢复，某次安全检查要求完成主备倒换演练，必须限期恢复主备，但是在恢复过程中，同步显示正常一段时间后，便会出现sql线程异常，主备数据不一致导致的同步错误情况。主数据库上面设置的max_allowed_packet比从数据库大，当一个大的sql语句，能在主数据库上面执行完毕，从数据库上面设置过小，无法执行，导致的主从不一致。根据上面的NR行数，查看附近的行，定位数据不一致的地方，后续手动补全。
copy to tmp table
weixin_30323631的博客
03-14 295
+-----+--------+-----------+--------------+---------+------+----------------------+---------+ | Id | User | Host | db | Command | Time | State | Info ...
Mysql之Copying to tmp table
青青之灰的博客
08-23 2886
Copying to tmp table这种状态耗时较长，如图 如果查询超出了tmp_table_size的限制，那么用/tmp保存查询结果，tmp_table不得不拷到硬盘上，然后返回给客户端. 解决方案：放大tmp_table_size             1）查询tmp_table_size 为默认16M                   MySQL>
mysql一直copying to tmp table_mysql出现Copying to tmp table怎么处理
weixin_35888365的博客
02-02 3160
优化数据库是，出现这个Copying to tmp table，如何处理#在mysql里用输入这个查询会列出当前执行的费时的查询showfullprocesslist今天在MYSQL中偶然发现查询命令中有很多语名处在Copying to tmp table状态，接口响应也变得很慢。因为业务需要这个SQL也确实比较复杂，同时因为有union操作，也使用到了临时表。Copying to tmp...
mysql一直copying to tmp table_mysql提示Copying to tmp table on disk
weixin_42395776的博客
02-02 697
网站运行的慢了，查找原因是Copying to tmp table on disk那怎么解决这个问题呢解决一例最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/mysqladmin processlist;发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。此分析对我没有太大的作用，...
mysql一直copying to tmp table_Mysql慢查询之Copying to tmp table
weixin_39681644的博客
02-02 672
问题：网站出现卡的情况，观察堆栈发现很多Copying to tmp table这种状态耗时较长​ 产生原因：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 temp...
mysql查询总数，导致 copying to tmp table on disk
06-08
如果以上方法无法解决问题，可以考虑增加服务器内存或者调整MySQL的配置参数，例如增大tmp_table_size和max_heap_table_size参数的值，使得MySQL能够在内存中创建更大的临时表。 希望这些方法能够帮助你解决问题。
Mysql慢查询之Copying to tmp table
水墨心的专栏
08-13 1万+
问题：网站出现卡的情况，观察堆栈发现很多Copying to tmp table这种状态耗时较长       产生原因：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 tem
mysql出现Copying to tmp table耗时较长：修改配置tmp_table_size及启动mysql时导致启动很多MYSQL进程-mysql进程数一直在增加问题
最新发布
本博客记录了从2014年以来在工作学习中遇到的技术问题、解决方法以及部分个人人生经历、经验等内容。
07-28 1523
今天在MYSQL中偶然发现查询命令中有很多语名处在Copying to tmp table状态，接口响应也变得很慢。因为业务需要这个SQL也确实比较复杂，同时因为有union操作，也使用到了临时表。Copying to tmp table状态即表示如果查询超出了MYSQL中配置的tmp_table_size的限制，tmp_table_size配置是每个线程都要分配的大小。应用中实际起限制作用除了tmp_table_size配置外还有max_heap_table_size(会取两者中的最小值)。
mysql一直copying to tmp table_mysql慢查询copying to tmp table
weixin_27155667的博客
02-02 181
windows server，无论修改my.ini的tmp_table_size，max_heap_table_size到多少，情况都一样。同样的表和查询语句，在本地运行，没出现慢查询。SELECTg.goods_id,g.goods_name,g.shop_price,g.goods_thumb,SUM(og.goods_number) AS goods_numberFROM`chinaetm`...
mysql一直copying to tmp table_mysql查询慢（Copying to tmp table）解决一例
weixin_39969881的博客
02-07 395
最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/mysqladmin processlist;发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。用命令杀掉此查询 /opt/mysql/bin/mysqladmin kill进程号；后面的查询一下子都好了。ok, 找到了问题...
mysql一直copying to tmp table_mysql,mysql优化_mysql慢查询copying to tmp table，mysql,mysql优化 - phpStudy...
weixin_35728049的博客
02-02 145
mysql慢查询copying to tmp tablewindows server，无论修改my.ini的tmp_table_size，max_heap_table_size到多少，情况都一样。同样的表和查询语句，在本地运行，没出现慢查询。SELECTg.goods_id,g.goods_name,g.shop_price,g.goods_thumb,SUM(og.goods_number) A...
MySQL 优化 之 Copying to tmp table on disk
aob30631的博客
12-23 336
项目中遇到了慢查询问题 Sql语句 SELECT sum(price) AS price, `member_id` FROM `crm_upload` GROUP BY member_id ORDER BY price DESC LIMIT 10; Explain 之后的结果： MariaDB [memb...
MySql操作缓慢:copy to tmp table
热门推荐
03-17 1万+
MySql操作缓慢:copy to tmp table 在给表加字段的时候，发现执行几分钟还没完成，查了下数据量，不到200w条记录，不应该出现这种情况。  后来想了想可能是别的线程锁表了。  新建窗口，执行 show processlist，看到所有操作表的线程  发现 state状态为copy to tmp table，这是因为临时结果集大于tmp_table_size，正在将临时
mysql慢查询问题（Copying to tmp table）
crz03128的专栏
05-04 845
http://bbs.chinaunix.net/forum.php?mod=viewthread&tid=3646279
mysql优化: waiting for.. Copying to tmp table
面朝大海，春暖花开
06-07 755
mysql优化: waiting for.. Copying to tmp table 因为临时的结果集大于mysql中tmp_table_size设置的大小，默认是16M,mysql会将数据转存到磁盘 mysql&gt; show variables like '%tmp%'; +-------------------+---------------+ | Variable_name ...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值