【MYSQL】state状态converting HEAP to MyISAM

最新推荐文章于 2022-07-03 16:27:15 发布

cml2016

最新推荐文章于 2022-07-03 16:27:15 发布

阅读量2.4k

点赞数

文章标签：数据库大数据

背景：机房迁移，原报表系统在新的db上出报表非常慢

1.查看连接状态

mysql> show full processlist;
+--------+-----------------+---------------------+----------+---------+---------+-----------------------------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-----------------+---------------------+----------+---------+---------+-----------------------------------------------------------------------------+-----------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 2247681 | Waiting on empty queue | NULL |
| 121456 | system user | | NULL | Connect | 784383 | Waiting for master to send event | NULL |
| 121457 | system user | | NULL | Connect | 1 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 121468 | ecejadmin | 192.168.2.104:42497 | ecejdbv3 | converting HEAP to MyISAM | 1 | | 复杂sql |
| 121469 | ecejadmin | 192.168.2.10:54423 | ecejdbv3 | converting HEAP to MyISAM | 2 | | 复杂sql |
| 121470 | ecejadmin | 192.168.2.12:9386 | ecejdbv3 | converting HEAP to MyISAM | 1 | | 复杂sql |
| 121471 | ecejadmin | 192.168.2.104:63937 | ecejdbv3 | converting HEAP to MyISAM | 2 | | 复杂sql

报表sql状态如上

2.处理过程

1.在终端里set profiling=1;
2.输入要调整的SQL语句，比如复杂sql;
3.输入show profiles;看看刚才那条语句的ID 用于后面分析
4.show profile for query 2;
5.show profile block io,cpu for query 2;
6.show profile cpu,block io,memory,swaps,context switches,source for query 5;
7.通过上面的一些查询大体可以看出这条SQL语句执行的时候哪些地方占用的时间太大了。这次测试看到的是Copying to tmp table on disk和converting HEAP to MyISAM占有的时间太多。网上查了一下发现可以修改一下tmp_table_size和max_heap_table_size两个参数来调整，使得大数据量的查询时不用将结果集拷贝到物理磁盘。这样时间就争取过来了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29096438/viewspace-2144585/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29096438/viewspace-2144585/

关注