mysql 優化

最近覺得自己的網站慢到離譜，而且用TOP指令去看的時候，系統負擔經常在6以上，因此去找了mysql 優化的相關資料，真的花很多時間去找，沒想到真的有辦法找出一些東西，也讓整個網站的速度便快了不少，所以暫時先記在這裡，以免以後忘記了。

主要找到的參考網頁是這兩個：

http://linux.chinaitlab.com/MYSQL/729100.html

http://imysql.cn/node/181

怕大陸網頁會不見，所以做個小筆記：

1.首先第一步，先查看看 mysql 裡頭有沒有哪一個動作會花很多時間的

在mysql 的設定檔中寫進以下資料（應該是my.cnf)

[mysqld]
; enable the slow query log, default 10 seconds
log-slow-queries
; log queries taking longer than 1 seconds
long_query_time = 1
; log queries that don't use indexes even if they take less than long_query_time
; MySQL 4.1 and newer only
log-queries-not-using-indexes

寫進去以後重開mysql，這樣就會在mysql 放資料的地方發現log檔（通常是/var/lib/mysql/xxx.log）

然後就可以看有哪些東西是要跑超過一秒的查詢，進而去修改他。

/usr/sbin/mysqld, Version: 5.0.32-Debian_7.b2d-log (Debian etch distribution). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 071114 18:03:21
# User@Host: root[root] @ localhost []
# Query_time: 2 Lock_time: 0 Rows_sent: 23 Rows_examined: 12381
use strikeid_forum;
SELECT c.*, COUNT(p.pic_id) AS count
FROM phpbb_album_cat AS c LEFT JOIN phpbb_album AS p ON c.cat_id = p.pic_cat_id
WHERE c.cat_id <> 0
GROUP BY c.cat_id
ORDER BY cat_order;

上面這個例子中，這個查詢要做12381次，因此很耗系統資源

舉例來說：小弟原本有一個sql查詢，應該用 = 號只要查一筆，但是用了 like，結果變成該資料表有幾筆就要查幾筆

從原先要查兩萬多筆變成只要查一筆，速度上就差非常之多了。

另一個sql指令的優化，則是使用EXPLAIN指令，放在phpmyadmin裡頭去跑看看，看看會跑幾筆

如：EXPLAIN SELECT t.* FROM cdb_posts p, cdb_threads t WHERE
t.fid IN (』37′, 『45′, 『4′, 『6′, 『17′, 『41′, 『28′, 『32′, 『31′, 『1′, 『42′)
AND p.tid=t.tid AND p.author LIKE 『JoansWin'
GROUP BY t.tid ORDER BY lastpost DESC LIMIT 0, 80;

就會顯示在資料庫中執行這個指令會跑幾次，多到很誇張當然就要研究語法有沒有辦法修改了。

2.增加mysql的設定

在大陸網頁中的伺服器有四G的RAM，他的設定是這樣的

#取消文件系統的外部鎖
skip-locking

#不進行域名反解析,注意由此帶來的權限/授權問題
skip-name-resolve

#索引緩存,根據內存大小而定,如果是獨立的db服務器,可以設置高達80%的內存總量
key_buffer = 512M

#連接排隊列表總數
back_log = 200
max_allowed_packet = 2M

#打開表緩存總數,可以避免頻繁的打開數據表產生的開銷
table_cache = 512

#每個線程排序所需的緩衝
sort_buffer_size = 4M

#每個線程讀取索引所需的緩衝
read_buffer_size = 4M

#MyISAM表發生變化時重新排序所需的緩衝
myisam_sort_buffer_size = 64M

#緩存可重用的線程數
thread_cache = 128

#查詢結果緩存
query_cache_size = 128M

#設置超時時間,能避免長連接
set-variable = wait_timeout=60

#最大並發線程數,cpu數量*2
thread_concurrency = 4

#記錄慢查詢,然後對慢查詢一一優化
log-slow-queries = slow.log
long_query_time = 1

#關閉不需要的表類型,如果你需要,就不要加上這個
skip-innodb
skip-bdb

set-variable=max_connections=500
#設定連接資料庫的上限，可以用SHOW STATUS LIKE 『max_used_connections'去查目前曾經最多一次連結多少
set-variable=wait_timeout=10
#設定每個連接等待時間的最上限，太低或太高似乎都不好
max_connect_errors = 100

貼上來以免原始網頁不見了，改了以後當然要重開mysql

其中的query_cache_size = 32M可以儘量高一點，當然是以你的實體記憶體多少為主，不過現在記憶體都很便宜了啦。

然後到phpmyadmin去查

SHOW STATUS LIKE 『qcache%』;

看看有沒有東西出來，如果有代表快取順利啟動，其中的詳細含意就不多說了大概也用不到。

大概就是如此，備忘中