最近一直忙於開發業務系統，數據庫從原來的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占用過高。現在查詢時間比較短，現象比較難重現了。
