通過(guò)以前對(duì)mysql的操作經(jīng)驗(yàn)，先將mysql的配置問(wèn)題排除了，查看msyql是否運(yùn)行正常，通過(guò)查看mysql data目錄里面的*.err文件(將擴(kuò)展名改為.txt)記事本查看即可。如果過(guò)大不建議用記事本了，容易死掉，可以用editplus等工具

簡(jiǎn)單的分為下面幾個(gè)步驟來(lái)解決這個(gè)問(wèn)題：

1、mysql運(yùn)行正常，也有可能是同步設(shè)置問(wèn)題導(dǎo)致

2、如果mysql運(yùn)行正常，那就是php的一些sql語(yǔ)句導(dǎo)致問(wèn)題發(fā)現(xiàn)，用root用戶進(jìn)入mysql管理
mysql -u root -p
輸入密碼
mysql:show processlist 語(yǔ)句，查找負(fù)荷最重的 SQL 語(yǔ)句，優(yōu)化該SQL，比如適當(dāng)建立某字段的索引。

通過(guò)這個(gè)命令我看到原來(lái)是有人惡意刷搜索，因?yàn)閐edecms搜索后面調(diào)用搜索最高的詞，導(dǎo)致很多人用工具刷這個(gè)，而且是定時(shí)有間隔的，所以將這個(gè)php程序改名跳轉(zhuǎn)都方法解決了。

當(dāng)然如果你的確實(shí)是sql語(yǔ)句用了大量的group by等語(yǔ)句，union聯(lián)合查詢等肯定會(huì)將mysql的占用率提高。所以就需要優(yōu)化sql語(yǔ)句，網(wǎng)站盡量生成靜態(tài)的，一般4W ip的靜態(tài)網(wǎng)站，mysql占用率幾乎為0的。所以這對(duì)于程序員的經(jīng)驗(yàn)是個(gè)考慮。盡量提高mysql性能 (MySQL 性能優(yōu)化的最佳20多條經(jīng)驗(yàn)分享)

下面是腳本之家收集的文章，大家都可以參考下

MYSQL CPU 占用 100% 的現(xiàn)象描述

　　早上幫朋友一臺(tái)服務(wù)器解決了 Mysql cpu 占用 100% 的問(wèn)題。稍整理了一下，將經(jīng)驗(yàn)記錄在這篇文章里
　　朋友主機(jī)(Windows 2003 + IIS + PHP + MYSQL )近來(lái) MySQL 服務(wù)進(jìn)程 (mysqld-nt.exe) CPU 占用率總為 100% 高居不下。此主機(jī)有10個(gè)左右的 database, 分別給十個(gè)網(wǎng)站調(diào)用。據(jù)朋友測(cè)試，導(dǎo)致 mysqld-nt.exe cpu 占用奇高的是網(wǎng)站A，一旦在 IIS 中將此網(wǎng)站停止服務(wù)，CPU 占用就降下來(lái)了。一啟用，則馬上上升。

　MYSQL CPU 占用 100% 的解決過(guò)程

　　今天早上仔細(xì)檢查了一下。目前此網(wǎng)站的七日平均日 IP 為2000，PageView 為 3萬(wàn)左右。網(wǎng)站A 用的 database 目前有39個(gè)表，記錄數(shù) 60.1萬(wàn)條，占空間 45MB。按這個(gè)數(shù)據(jù)，MySQL 不可能占用這么高的資源。

　　于是在服務(wù)器上運(yùn)行命令，將 mysql 當(dāng)前的環(huán)境變量輸出到文件 output.txt：

d:\web\mysql> mysqld.exe --help >output.txt
　　發(fā)現(xiàn) tmp_table_size 的值是默認(rèn)的 32M，于是修改 My.ini, 將 tmp_table_size 賦值到 200M:

d:\web\mysql> notepad c:\windows\my.ini
[mysqld]
tmp_table_size=200M

　　然后重啟 MySQL 服務(wù)。CPU 占用有輕微下降，以前的CPU 占用波形圖是 100% 一根直線，現(xiàn)在則在 97%~100%之間起伏。這表明調(diào)整 tmp_table_size 參數(shù)對(duì) MYSQL 性能提升有改善作用。但問(wèn)題還沒(méi)有完全解決。

　　于是進(jìn)入 mysql 的 shell 命令行，調(diào)用 show processlist, 查看當(dāng)前 mysql 使用頻繁的 sql 語(yǔ)句：

mysql> show processlist;
　　反復(fù)調(diào)用此命令，發(fā)現(xiàn)網(wǎng)站 A 的兩個(gè) SQL 語(yǔ)句經(jīng)常在 process list 中出現(xiàn)，其語(yǔ)法如下：

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 檢查這三個(gè)表的結(jié)構(gòu) :

mysql> show columns from _myuser;
mysql> show columns from _mydata;
mysql> show columns from _mydata_body;
　　終于發(fā)現(xiàn)了問(wèn)題所在：_mydata 表，只根據(jù) pid 建立了一個(gè) primary key，但并沒(méi)有為 userid 建立索引。而在這個(gè) SQL 語(yǔ)句的第一個(gè) LEFT JOIN ON 子句中：

LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
　　_mydata 的 userid 被參與了條件比較運(yùn)算。于是我為給 _mydata 表根據(jù)字段 userid 建立了一個(gè)索引：

mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` )
　　建立此索引之后，CPU 馬上降到了 80% 左右?？吹秸业搅藛?wèn)題所在，于是檢查另一個(gè)反復(fù)出現(xiàn)在 show processlist 中的 sql 語(yǔ)句：

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, 沒(méi)有為 keywords 建立 index。_mydata_key 目前有 33 萬(wàn)條記錄，在沒(méi)有索引的情況下對(duì)33萬(wàn)條記錄進(jìn)行文本檢索匹配，不耗費(fèi)大量的 cpu 時(shí)間才怪?？磥?lái)就是針對(duì)這個(gè)表的檢索出問(wèn)題了。于是同樣為 _mydata_key 表根據(jù)字段 keywords 加上索引:

mysql> ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )
　　建立此索引之后，CPU立刻降了下來(lái)，在 50%~70%之間震蕩。

　　再次調(diào)用 show prosslist，網(wǎng)站A 的sql 調(diào)用就很少出現(xiàn)在結(jié)果列表中了。但發(fā)現(xiàn)此主機(jī)運(yùn)行了幾個(gè) Discuz 的論壇程序， Discuz 論壇的好幾個(gè)表也存在著這個(gè)問(wèn)題。于是順手一并解決，cpu占用再次降下來(lái)了。(2007.07.09 附注：關(guān)于 discuz 論壇的具體優(yōu)化過(guò)程，我后來(lái)另寫(xiě)了一篇文章，詳見(jiàn)：千萬(wàn)級(jí)記錄的 Discuz! 論壇導(dǎo)致 MySQL CPU 100% 的 優(yōu)化筆記 http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm)

　解決 MYSQL CPU 占用 100% 的經(jīng)驗(yàn)總結(jié)

增加 tmp_table_size 值。mysql 的配置文件中，tmp_table_size 的默認(rèn)大小是 32M。如果一張臨時(shí)表超出該大小，MySQL產(chǎn)生一個(gè) The table tbl_name is full 形式的錯(cuò)誤，如果你做很多高級(jí) GROUP BY 查詢，增加 tmp_table_size 值。 這是 mysql 官方關(guān)于此選項(xiàng)的解釋?zhuā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.

對(duì) WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的條件判斷中用到的字段,應(yīng)該根據(jù)其建立索引 INDEX。索引被用來(lái)快速找出在一個(gè)列上用一特定值的行。沒(méi)有索引，MySQL不得不首先以第一條記錄開(kāi)始并然后讀完整個(gè)表直到它找出相關(guān)的行。表越大，花費(fèi)時(shí)間越多。如果表對(duì)于查詢的列有一個(gè)索引，MySQL能快速到達(dá)一個(gè)位置去搜尋到數(shù)據(jù)文件的中間，沒(méi)有必要考慮所有數(shù)據(jù)。如果一個(gè)表有1000行，這比順序讀取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹(shù)中存儲(chǔ)。

根據(jù) mysql 的開(kāi)發(fā)文檔:

索引 index 用于：

快速找出匹配一個(gè)WHERE子句的行
當(dāng)執(zhí)行聯(lián)結(jié)(JOIN)時(shí)，從其他表檢索行。
對(duì)特定的索引列找出MAX()或MIN()值
如果排序或分組在一個(gè)可用鍵的最左面前綴上進(jìn)行(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個(gè)表。如果所有鍵值部分跟隨DESC，鍵以倒序被讀取。

在一些情況中，一個(gè)查詢能被優(yōu)化來(lái)檢索值，不用咨詢數(shù)據(jù)文件。如果對(duì)某些表的所有使用的列是數(shù)字型的并且構(gòu)成某些鍵的最左面前綴，為了更快，值可以從索引樹(shù)被檢索出來(lái)。

假定你發(fā)出下列SELECT語(yǔ)句：

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一個(gè)多列索引存在于col1和col2上，適當(dāng)?shù)男锌梢灾苯颖蝗〕?。如果分開(kāi)的單行列索引存在于col1和col2上，優(yōu)化器試圖通過(guò)決定哪個(gè)索引將找到更少的行并來(lái)找出更具限制性的索引并且使用該索引取行。

　　開(kāi)發(fā)人員做 SQL 數(shù)據(jù)表設(shè)計(jì)的時(shí)候，一定要通盤(pán)考慮清楚。

最新評(píng)論