mysql性能问题小解 Converting HEAP to MyIsam create_myisa

安定北京

被性能测试困扰了N天，实在没想法去解决了，今天又收到上级的命令说

安定北京

要解决，无奈！把项目组唯一的DBA辞掉了，现在所以数据库的问题都得自己来处理：（ 不知道上边人怎么想的。而且更不知道怎

安定北京

么想的是居然会把应用部属在虚拟主机上！唉。。。

不唠叨了 说说处理过程吧：

1.在终端里set profiling=1;
2.输入要调整的SQL语句，比如select * from table1;
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两个参数来调整，使得大数据量的查询时不用将结果集拷贝到物理磁盘。这样时间就争取过来了

对了 MYSQL有个好用的命令可以分析一条SQL的结构，可以查到这个查询是否使用到索引等。直接explain select * from table1就行了


+--------------------------------+------------+------------+------------+-------------------+---------------------+--------------+---------------+-------+---------------------------+---------------+-------------+
| Status                         | Duration   | CPU_user   | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Swaps | Source_function           | Source_file   | Source_line |
+--------------------------------+------------+------------+------------+-------------------+---------------------+--------------+---------------+-------+---------------------------+---------------+-------------+
| checking query cache for query | 0.00001100 | 0.00000000 | 0.00000000 |                 0 |                   0 |            0 |             0 |     0 | send_result_to_client     | sql_cache.cc  |        1094 |
| Opening tables                 | 0.00023400 | 0.00000000 | 0.00000000 |                 0 |                   0 |            0 |             0 |     0 | open_tables               | sql_base.cc   |        2106 |
| System lock                    | 0.00002800 | 0.00000000 | 0.00000000 |                 0 |                   0 |            0 |             0 |     0 | mysql_lock_tables         | lock.cc       |         153 |
| Table lock                     | 0.00001300 | 0.00000000 | 0.00000000 |                 0 |                   0 |            0 |             0 |     0 | mysql_lock_tables         | lock.cc       |         162 |
| optimizing                     | 0.00022700 | 0.00099900 | 0.00000000 |                 0 |                   0 |            0 |             0 |     0 | optimize                  | sql_select.cc |         617 |
| statistics                     | 0.00002900 | 0.00000000 | 0.00000000 |                 0 |                   0 |            0 |             0 |     0 | optimize                  | sql_select.cc |         773 |
| preparing                      | 0.00012800 | 0.00000000 | 0.00000000 |                 0 |                   0 |            0 |             0 |     0 | optimize                  | sql_select.cc |         783 |
| Creating tmp table             | 0.00003400 | 0.00000000 | 0.00000000 |                 0 |                   0 |            0 |             0 |     0 | optimize                  | sql_select.cc |        1206 |
| executing                      | 0.00003100 | 0.00000000 | 0.00000000 |                 0 |                   0 |            0 |             0 |     0 | exec                      | sql_select.cc |        1407 |
| Copying to tmp table           | 0.00001100 | 0.00000000 | 0.00000000 |                 0 |                   0 |            0 |             0 |     0 | exec                      | sql_select.cc |        1547 |
| converting HEAP to MyISAM      | 3.94055900 | 3.81042100 | 0.12498100 |                 6 |                   7 |            0 |             0 |     0 | create_myisam_from_heap   | sql_select.cc |        9914 |
| Copying to tmp table on disk   | 5.10490400 | 5.00623900 | 0.09798500 |                 8 |                  10 |            0 |             0 |     0 | create_myisam_from_heap   | sql_select.cc |        9968 |
| Sending data                   | 3.09531800 | 2.96954900 | 0.12698100 |                 4 |                   4 |            0 |             0 |     0 | exec                      | sql_select.cc |        1925 |
| converting HEAP to MyISAM      | 1.62242300 | 1.37279100 | 0.25096200 |                38 |                  15 |            0 |             0 |     0 | create_myisam_from_heap   | sql_select.cc |        9914 |
| Sending data                   | 5.13815600 | 5.04223300 | 0.09698500 |                13 |                  10 |            0 |             0 |     0 | create_myisam_from_heap   | sql_select.cc |        9968 |
| optimizing                     | 2.17403900 | 2.01069500 | 0.16497500 |                 5 |                   3 |            0 |             0 |     0 | optimize                  | sql_select.cc |         617 |


mysql官网论坛也有讲过这配置 但没说到第二项配置
http://forums.mysql.com/read.php?22,111012,111012#msg-111012