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