MySQL优化总结（一）：常用优化思路和临时表

一、MySQL 优化思路架构

通常情况下，MySQL的优化过程总结如下：

1、观察服务器状态, 一般用如下2个命令
Show status; 或者Show processlist;

例: mysql> show status;   #mysqladmin ext 

2、不规则的延迟现象往往是由于效率低下的语句造成的

1）查看当前所有连接的工作状态

Show processlist;//这个命令是显示当前所有连接的工作状态.

如果观察到以下状态,则需要注意

converting HEAP to MyISAM 查询结果太大时,把结果放在磁盘 (语句写的不好,取数据太多)create tmp table             创建临时表(如group时储存中间结果,说明索引建的不好)Copying to tmp table on disk   把内存临时表复制到磁盘 (索引不好,表字段选的不好)locked         被其他查询锁住 (一般在使用事务时易发生,互联网应用不常发生) logging slow query 记录慢查询 

2）抓取上述观察的效率低的语句
mysql 5.5 以后加了一个profile设置,可以观察到具体语句的执行步骤.

• 查看profile是否开启

Show  variables like ‘profiling’ +---------------+-------+| Variable_name | Value |+---------------+-------+| profiling     | OFF   |+---------------+-------+

• 开启profile

set profiling=on;+---------------+-------+| Variable_name | Value |+---------------+-------+| profiling     | On      |+---------------+-------+

• 查看具体的执行语句

show profiles;+----------+------------+----------------------------------------------------------+| Query_ID | Duration   | Query                                                    |+----------+------------+----------------------------------------------------------+|        1 | 0.00034225 | select cat_id,avg(shop_price) from goods group by cat_id |+----------+------------+----------------------------------------------------------+1 row in set (0.00 sec)mysql> show profile for query 1;+----------------------+----------+| Status               | Duration |+----------------------+----------+| starting             | 0.000058 || checking permissions | 0.000008 |......| Sorting result       | 0.000004 || Sending data         | 0.000120 || end                  | 0.000005 || query end            | 0.000006 || closing tables       | 0.000008 || freeing items        | 0.000023 || logging slow query   | 0.000003 || cleaning up          | 0.000004 |+----------------------+----------+

二、MySQL中临时表

　　在处理请求的某些场景中,服务器创建内部临时表。即表以MEMORY引擎在内存中处理,或以MyISAM引擎储存在磁盘上处理.如果表过大,服务器可能会把内存中的临时表转存在磁盘上。
　　临时表被创建几种情况　　
　　

• 如果group by 的列没有索引,必产生内部临时表　　

mysql> explain select goods_id,cat_id from goods group by cat_id \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: goods         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 20        Extra: Using temporary; Using filesort1 row in set (0.00 sec)mysql> alter table goods add index cat_id(cat_id);Query OK, 0 rows affected (0.18 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> explain select goods_id,cat_id from goods group by cat_id \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: goods         type: indexpossible_keys: NULL          key: cat_id      key_len: 2          ref: NULL         rows: 20        Extra: Using index

• 如果order by 与group by为不同列时,或多表联查时order by ,group by 包含的列不是第一张表的列,将会产生临时表

explain select goods_id,cat_id from goods group by cat_id order by 1 \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: goods         type: indexpossible_keys: NULL          key: PRIMARY      key_len: 3          ref: NULL         rows: 20        Extra: Using temporary

• distinct 与order by 一起使用可能会产生临时表

mysql> explain select distinct cat_id from goods order by 1 \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: goods         type: indexpossible_keys: NULL          key: cat_id      key_len: 2          ref: NULL         rows: 20        Extra: Using index1 row in set (0.00 sec)mysql> explain select distinct cat_id from goods order by goods_id \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: goods         type: indexpossible_keys: NULL          key: PRIMARY      key_len: 3          ref: NULL         rows: 20        Extra: Using temporary1 row in set (0.00 sec)                                               mysql> explain select distinct cat_id from goods order by click_count\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: goods         type: indexpossible_keys: NULL          key: cat_id      key_len: 2          ref: NULL         rows: 20        Extra: Using temporary; Using filesort1 row in set (0.00 sec)

备注：
1、想确定查询是否需要临时表,可以用EXPLAIN查询计划,并查看Extra列,看是否有Using temporary。
2、如果一开始在内存中产生的临时表变大,会自动转化为磁盘临时表。 内存中临时表的最大值为tmp_table_size和max_heap_size中较小值。