Mysql查詢語句優化一則

 

   最近一直忙於開發業務系統,數據庫從原來的Oracle被替換成瞭Mysql,但在實際線上運行中發現有條sql執行起來非常慢,更奇怪的是這句sql還會導致整個數據庫性能下降。這個問題非常嚴重!該sql和表結構如下:

 

SELECT name

       ,COUNT(*) AS counts

       ,type

FROM  entityNameTemp

WHERE postTime > '2011-06-01 00:00:00'

GROUP BY name

ORDER BY counts DESC

LIMIT  10

 

| entityNameTemp | CREATE TABLE `entityNameTemp` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',

  `name` varchar(600) DEFAULT NULL COMMENT '人名或者機構名',

  `type` enum('personName','organizationName') DEFAULT NULL COMMENT 'personName 人名;organizationName:機構名',

  `postTime` timestamp NULL DEFAULT NULL COMMENT '發帖時間',

  `createTime` timestamp NULL DEFAULT NULL COMMENT '創建時間',

  PRIMARY KEY (`id`)

) ENGINE=Innodb AUTO_INCREMENT=1931915 DEFAULT CHARSET=utf8                        |

    這是對一個百萬級別的臨時表,目的是做一下統計取排名前十的數據。先來查看一下執行計劃:

+—-+————-+—————-+——+—————+——+———+——+———+———————————————-+

| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows    | Extra                                        |

+—-+————-+—————-+——+—————+——+———+——+———+———————————————-+

|  1 | SIMPLE      | entityNameTemp | ALL  | NULL          | NULL | NULL    | NULL | 1735829 | Using where; Using temporary; Using filesort |

+—-+————-+—————-+——+—————+——+———+——+———+———————————————-+

    無疑是用到瞭臨時表以及排序,且沒有用上索引。但mysql的執行計劃實在很難定位具體問題。之前有查過mysql對臨時文件的使用規則,主體思路是查看tmp_table_size參數,mysql會評估一下本次查詢大概會需要用到的內存大小,如果小於該參數則會使用磁盤臨時文件。但這個參數我已經改到瞭200m,但問題依舊。查看瞭一下,發現設置瞭參數但仍然使用瞭磁盤。查詢發現Created_tmp_disk_tables參數在sql語句執行前後增加瞭1:

mysql> show status like  '%tmp%';

+————————-+——-+

| Variable_name           | Value |

+————————-+——-+

| Created_tmp_disk_tables | 2     |

| Created_tmp_files       | 15    |

| Created_tmp_tables      | 7     |

+————————-+——-+

    這就非常奇怪瞭,因為我手動計算發現這些數據量絕對不會超過200m,理論上是應該要用內存臨時表的。這隻好拿出殺手鐧,查看詳細的執行計劃。在命令行下依次執行1)set profiling = 1; 2)sql語句;3)show profile;就可以看到詳細的時間消耗,另外可以用show profiles查看執行過的sql語句。當前sql語句執行情況分析如下:

mysql> show profile;

+——————————–+————+

| Status                         | Duration   |

+——————————–+————+

| starting                       |   0.000023 |

| checking query cache for query |   0.000069 |

| Opening tables                 |   0.000016 |

| System lock                    |   0.000008 |

| Table lock                     |   0.000036 |

| init                           |   0.000030 |

| optimizing                     |   0.000011 |

| statistics                     |   0.000018 |

| preparing                      |   0.000014 |

| Creating tmp table             |   0.000265 |

| executing                      |   0.000008 |

| Copying to tmp table           | 165.312749 |

| Sorting result                 |   0.258847 |

| Sending data                   |   0.000094 |

| end                            |   0.000007 |

| removing tmp table             |   0.302258 |

| end                            |   0.000026 |

| query end                      |   0.000007 |

| freeing items                  |   0.000171 |

| storing result in query cache  |   0.000017 |

| logging slow query             |   0.000007 |

| logging slow query             |   0.000006 |

| cleaning up                    |   0.000008 |

+——————————–+————+

    從上可以清楚的看到時間消耗基本都花費在臨時文件拷貝上瞭,對於排序其實還沒花費多久。那問題的關鍵就是在於解決臨時文件如何在內存中建立。

    簡單商討瞭一下,覺得還是先建立索引看看吧。針對這個查詢條件應該建立postTime和name的聯合索引。但執行時發現:

mysql> alter table entityNameTemp add key idx_postTime_name ( postTime, name );

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

    這怎麼會超過長度瞭呢?name字段應該很短才對,postTime還是一個時間字段更長不瞭。但是一檢查發現居然建表的人寫的name是varchar(600)。突然想到mysql讀取時內存開辟是根據聲明的長度來的,再一聯想,mysql估計需要讀取文件的大小就是根據字段聲明來算出來的。果斷修改name到varchar(20),一執行就幾秒瞭,再看一下詳細時間消耗:

mysql> show profile;

+——————————–+———-+

| Status                         | Duration |

+——————————–+———-+

| starting                       | 0.000036 |

| checking query cache for query | 0.000094 |

| Opening tables                 | 0.000216 |

| System lock                    | 0.000010 |

| Table lock                     | 0.000038 |

| init                           | 0.000038 |

| optimizing                     | 0.000014 |

| statistics                     | 0.000019 |

| preparing                      | 0.000018 |

| Creating tmp table             | 0.000040 |

| executing                      | 0.000008 |

| Copying to tmp table           | 3.863467 |

| Sorting result                 | 0.092263 |

| Sending data                   | 0.000061 |

| end                            | 0.000006 |

| removing tmp table             | 0.004514 |

| end                            | 0.000009 |

| query end                      | 0.000005 |

| freeing items                  | 0.000035 |

| storing result in query cache  | 0.000013 |

| logging slow query             | 0.000005 |

| cleaning up                    | 0.000005 |

+——————————–+———-+

    問題基本算解決瞭,查看臨時文件使用情況也確實使用瞭內存臨時文件。加上索引試試,查看執行計劃也用上索引瞭,但是實際執行效果來看提升效果不大。因為還是要拷貝到臨時文件表,innodb對於count操作優化確實比較難。

    另外一個問題就是對整個系統的影響,這估計是因為用到瞭磁盤會導致io占用過高。現在查詢時間比較短,現象比較難重現瞭。

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *