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

最新推荐文章于 2024-08-12 23:11:19 发布
最新推荐文章于 2024-08-12 23:11:19 发布
阅读量7.3k 收藏 4
点赞数
分类专栏： MySql 文章标签： mysql 索引 性能 优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/db_bili/article/details/8507233
版权
本文探讨了MySQL中遇到'copying to tmp table'事件的问题，通过逐步改写SQL，从原始的查询开始，分析并优化执行计划。通过对论坛线程数据的查询，展示了如何减少group by操作的影响，提升查询效率。最后，文章还介绍了MySQL的profile参数、上下文切换概念以及清理缓存的方法。
摘要由CSDN通过智能技术生成
 

环境说明：

 版本
' 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.0
最低0.47元/天 解锁文章
db_bili
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql一直copying to tmp table_mysql提示Copying to tmp table on disk
weixin_42395776的博客
02-02 602
网站运行的慢了，查找原因是Copying to tmp table on disk那怎么解决这个问题呢解决一例最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/mysqladmin processlist;发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。此分析对我没有太大的作用，...
查找MySQL中查询慢的SQL语句方法
09-09
其中，状态列（`State`）特别重要，因为它揭示了查询当前所处的阶段，比如`Copying to tmp table on disk`表示数据正在从内存中复制到磁盘上的临时表。理解这些状态有助于诊断查询性能问题。 MySQL列出的状态有很多...
mysql一直copying to tmp table_mysql出现Copying to tmp table怎么处理
weixin_35888365的博客
02-02 3057
优化数据库是，出现这个Copying to tmp table，如何处理#在mysql里用输入这个查询会列出当前执行的费时的查询showfullprocesslist今天在MYSQL中偶然发现查询命令中有很多语名处在Copying to tmp table状态，接口响应也变得很慢。因为业务需要这个SQL也确实比较复杂，同时因为有union操作，也使用到了临时表。Copying to tmp...
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
Code changes the world！
12-25 1158
高性能推荐的MySQL分支版本：mariaDB、percona server 对于非严格苛刻交易型的数据表，建议用 MariaDB，这个版本目前在开源界很盛行，评价很高，percona 版本也值得推荐，percona 有很多辅助的运维工具。 有意思的函数 in用group_concat()函数，@@GROUP_CONCAT_MAX_LEN表示分组的最大长度 convert()/
copy to tmp table
weixin_30323631的博客
03-14 249
+-----+--------+-----------+--------------+---------+------+----------------------+---------+ | Id | User | Host | db | Command | Time | State | Info ...
Mysql之Copying to tmp table
青青之灰的博客
08-23 2830
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_39969881的博客
02-07 344
最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/mysqladmin processlist;发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。用命令杀掉此查询 /opt/mysql/bin/mysqladmin kill进程号；后面的查询一下子都好了。ok, 找到了问题...
mysql一直copying to tmp table_Mysql慢查询之Copying to tmp table
weixin_39681644的博客
02-02 579
问题：网站出现卡的情况，观察堆栈发现很多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 show processlist 命令检查mysql锁的方法
09-11
例如，如果看到大量线程处于`Copying to tmp table on disk`状态，可能意味着需要调整`tmp_table_size`参数，避免频繁将临时表写入磁盘，从而提升性能。 通过观察`SHOW PROCESSLIST`输出，可以发现慢查询、锁定问题...
mysql show processlist 显示mysql查询进程
09-11
如果`Copying to tmp table on disk`状态持续时间过长，可能是因为内存中的临时表过大，导致数据写入磁盘，这可能会影响性能。 处理挂起的查询，你可以使用`KILL`命令，指定线程ID来终止查询。例如，`KILL 207`将...
MySQL通过show processlist命令检视性能的讲解
09-09
- `Copying to tmp table on disk`：内存中的临时表超出限制，正在转移到磁盘。 - `Creating tmp table`：创建临时表以存储查询结果。 - `deleting from main table`和`deleting from reference tables`：多表删除...
processlist命令 查看mysql 线程
09-11
例如，`Copying to tmp table`表示正在将结果集复制到临时表，`Sorting result`表示正在对结果进行排序，`Sending data`表示数据正在发送给客户端。理解这些状态对于识别性能瓶颈至关重要。 8. **Info**: 提供了...
mysql一直copying to tmp table_mysql,mysql优化_mysql慢查询copying to tmp table，mysql,mysql优化 - phpStudy...
weixin_35728049的博客
02-02 112
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_mysql慢查询copying to tmp table
weixin_27155667的博客
02-02 135
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 on disk
aob30631的博客
12-23 302
项目中遇到了慢查询问题 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 Copying to tmp table时间占用过长的终极解决方法
LufeiWa的博客
11-20 1917
最近开发任务不那么重，闲着就修修补补现在的系统。还真发现了一个问题，问题是取20条数据，我访问我本地的项目，很快，秒加载；当我访问线上的项目时，就很慢，时间长达10s之久。 连接上线上数据库之后，进行select语句查询，发现需要3秒，我本地只需要0.1s。然后我发现“概况”那里，花费时间 最多的是Copying to tmp table，占98%多，百度了一圈，基本都是这种方式 ： 一、在mys...
mysql Copying to tmp table on disk 影响性能的解决方法
黄树茂博客
12-21 6810
最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/mysqladmin processlist; 发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。 用命令杀掉此查询 /opt/mysql/bin/mysqladmin kill  进程号； 后面的查询一下子都好了。  ok,
CVE-2012-2122 mysql/mariaDB身份认证漏洞
最新发布
weixin_68177269的博客
08-12 238
当连接MariaDB/MySQL时，输入的密码会与期望的正确密码比较，不断的尝试登录连接，回导致MySQL认为两个密码是相同的。也就是说只要知道用户名，不断尝试就能够直接登入SQL数据库。影响范围#所有的Mariadb和mysql版本:5.1.61、5.0.11、5.3.5、5.5.22。可见经过大数量的暴力登录之后，直接不需要密码登录到mysql。使用docker-compose 创建靶场环境。下载安装vulhub靶场环境。二、msf漏洞scan探测。不知道为什么没跑出来🥴。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值