反復(fù)調(diào)用此命令(每秒刷兩次），發(fā)現(xiàn)網(wǎng)站 A 的兩個 SQL 語句經(jīng)常在 process list 中出現(xiàn)，其語法如下：
SELECT t1.pid, t2.userid, t3.count, t1.date FROM _mydata AS t1 LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid LEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pid ORDER BY t1.pid LIMIT 0,15
調(diào)用 show columns 檢查這三個表的結(jié)構(gòu) :
mysql> show columns from _myuser; mysql> show columns from _mydata; mysql> show columns from _mydata_body;
終于發(fā)現(xiàn)了問題所在：_mydata 表，只根據(jù) pid 建立了一個 primary key，但并沒有為 userid 建立索引。而在這個 SQL 語句的第一個 LEFT JOIN ON 子句中：
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
_mydata 的 userid 被參與了條件比較運(yùn)算。于是我為給 _mydata 表根據(jù)字段 userid 建立了一個索引：
mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` )
建立此索引之后，CPU 馬上降到了 80% 左右�？吹秸业搅藛栴}所在，于是檢查另一個反復(fù)出現(xiàn)在 show processlist 中的 sql 語句：
SELECT COUNT(*) FROM _mydata AS t1, _mydata_key AS t2 WHERE t1.pid=t2.pid and t2.keywords = '孔雀'
經(jīng)檢查 _mydata_key 表的結(jié)構(gòu)，發(fā)現(xiàn)它只為 pid 建了了 primary key, 沒有為 keywords 建立 index。_mydata_key 目前有 33 萬條記錄，在沒有索引的情況下對33萬條記錄進(jìn)行文本檢索匹配，不耗費(fèi)大量的 cpu 時間才怪�？磥砭褪轻槍@個表的檢索出問題了。于是同樣為 _mydata_key 表根據(jù)字段 keywords 加上索引:
mysql> ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )
建立此索引之后，CPU立刻降了下來，在 50%~70%之間震蕩。
再次調(diào)用 show prosslist，網(wǎng)站A 的sql 調(diào)用就很少出現(xiàn)在結(jié)果列表中了。但發(fā)現(xiàn)此主機(jī)運(yùn)行了幾個 Discuz 的論壇程序， Discuz論壇的好幾個表也存在著這個問題。于是順手一并解決，cpu占用再次降下來了。
至此，問題解決。
----------------------------------------------------------------------------------------
解決 MYSQL CPU 占用 100% 的經(jīng)驗總結(jié)
1. 增加 tmp_table_size 值。mysql 的配置文件中，tmp_table_size 的默認(rèn)大小是 32M。如果一張臨時表超出該大小，MySQL產(chǎn)生一個 The table tbl_name is full 形式的錯誤，如果你做很多高級 GROUP BY 查詢，增加 tmp_table_size 值。 這是 mysql 官方關(guān)于此選項的解釋：
tmp_table_size
This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.
2. 對 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的條件判斷中用到的字段,應(yīng)該根據(jù)其建立索引 INDEX。
索引被用來快速找出在一個列上用一特定值的行。沒有索引，MySQL不得不首先以第一條記錄開始并然后讀完整個表直到它找出相關(guān)的行。表越大，花費(fèi)時間越多。如果表對于查詢的列有一個索引，MySQL能快速到達(dá)一個位置去搜尋到數(shù)據(jù)文件的中間，沒有必要考慮所有數(shù)據(jù)。如果一個表有1000行，這比順序讀取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹中存儲。
根據(jù) mysql 的開發(fā)文檔:
索引 index 用于：
o 快速找出匹配一個WHERE子句的行
o 當(dāng)執(zhí)行聯(lián)結(jié)(JOIN)時，從其他表檢索行。
o 對特定的索引列找出MAX()或MIN()值
o 如果排序或分組在一個可用鍵的最左面前綴上進(jìn)行(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有鍵值部分跟隨DESC，鍵以倒序被讀取。
o 在一些情況中，一個查詢能被優(yōu)化來檢索值，不用咨詢數(shù)據(jù)文件。如果對某些表的所有使用的列是數(shù)字型的并且構(gòu)成某些鍵的最左面前綴，為了更快，值可以從索引樹被檢索出來。
假定你發(fā)出下列SELECT語句：
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一個多列索引存在于col1和col2上，適當(dāng)?shù)男锌梢灾苯颖蝗〕�。如果分開的單行列索引存在于col1和col2上，優(yōu)化器試圖通過決定哪個索引將找到更少的行并來找出更具限制性的索引并且使用該索引取行。
開發(fā)人員做 SQL 數(shù)據(jù)表設(shè)計的時候，一定要通盤考慮清楚。
您現(xiàn)在的位置： 
