作/譯者：葉金榮（Email: 
），來源：http://，轉(zhuǎn)載請注明作/譯者和出處，并且不能用于商業(yè)用途，違者必究。
一. 前言
近日由于需要,對discuz論壇(簡稱dz)進(jìn)行優(yōu)化,當(dāng)然了,只是涉及到數(shù)據(jù)庫的優(yōu)化.
先說一下服務(wù)器及dz的數(shù)據(jù)量,2 * Intel(R) Xeon(TM) CPU 2.40GHz, 4GB mem, SCISC硬盤.
MySQL 版本為 4.0.23. 數(shù)據(jù)表情況:
cdb_attachments 2萬
cdb_members 10萬
cdb_posts 68萬
cdb_threads 7萬
二. 緩存優(yōu)化
在 my.cnf 中添加/修改以下選項(xiàng):
#取消文件系統(tǒng)的外部鎖 skip-locking #不進(jìn)行域名反解析,注意由此帶來的權(quán)限/授權(quán)問題 skip-name-resolve #索引緩存,根據(jù)內(nèi)存大小而定,如果是獨(dú)立的db服務(wù)器,可以設(shè)置高達(dá)80%的內(nèi)存總量 key_buffer = 512M #連接排隊(duì)列表總數(shù) back_log = 200 max_allowed_packet = 2M #打開表緩存總數(shù),可以避免頻繁的打開數(shù)據(jù)表產(chǎn)生的開銷 table_cache = 512 #每個(gè)線程排序所需的緩沖 sort_buffer_size = 4M #每個(gè)線程讀取索引所需的緩沖 read_buffer_size = 4M #MyISAM表發(fā)生變化時(shí)重新排序所需的緩沖 myisam_sort_buffer_size = 64M #緩存可重用的線程數(shù) thread_cache = 128 #查詢結(jié)果緩存 query_cache_size = 128M #設(shè)置超時(shí)時(shí)間,能避免長連接 set-variable = wait_timeout=60 #最大并發(fā)線程數(shù),cpu數(shù)量*2 thread_concurrency = 4 #記錄慢查詢,然后對慢查詢一一優(yōu)化 log-slow-queries = slow.log long_query_time = 1 #關(guān)閉不需要的表類型,如果你需要,就不要加上這個(gè) skip-innodb skip-bdb
以上參數(shù)根據(jù)各自服務(wù)器的配置差異進(jìn)行調(diào)整,僅作為參考.
三. 索引優(yōu)化
上面提到了,已經(jīng)開啟了慢查詢,那么接下來就要對慢查詢進(jìn)行逐個(gè)優(yōu)化了.
1. 搜索優(yōu)化
搜索的查詢SQL大致如下:
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;
用 EXPLAIN 分析的結(jié)果如下:
mysql>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; +-----------+------------+----------+--------------+-------------+-----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra +-----------+------------+----------+--------------+-------------+-----------+-------------+ | 1 | SIMPLE | t | range | PRIMARY,fid | fid | 2 | NULL | 66160 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | p | ref | tid | tid | 3 | Forum.t.tid | 10 | Using where | +----+-------------+-------+-------+---------------+------+---------+-------------+-------+ ---------
只用到了 t.fid 和 p.tid,而 p.author 則沒有索引可用,總共需要掃描66160*10 = 661600 次索引,夠夸張吧 :(
再分析 cdb_threads 和 cdb_posts 的索引情況:
mysql>show index from cdb_posts; +-----------+------------+----------+--------------+-------------+-----------+---------- ---+----------+--------+------+--+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+---- ---------+-----------+-------------+----------+--------+------+--+ | cdb_posts | 0 | PRIMARY | 1 | pid | A | 680114 | NULL | NULL | | BTREE | | | cdb_posts | 1 | fid | 1 | fid | A | 10 | NULL | NULL | | BTREE | | | cdb_posts | 1 | tid | 1 | tid | A | 68011 | NULL | NULL | | BTREE | | | cdb_posts | 1 | tid | 2 | dateline | A | 680114 | NULL | NULL | | BTREE | | | cdb_posts | 1 | dateline | 1 | dateline | A | 680114 | NULL | NULL | | BTREE | | +-----------+------------+----------+--------------+-------------+-----------+---
以及
mysql>show index from cdb_threads; +-----------+------------+----------+--------------+-------------+-----------+-------------+ ----------+--------+------+-----+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+----- --------+-----------+-------------+----------+--------+------+-----+ | cdb_threads | 0 | PRIMARY | 1 | tid | A | 68480 | NULL | NULL | | BTREE | | | cdb_threads | 1 | lastpost | 1 | topped | A | 4 | NULL | NULL | | BTREE | | | cdb_threads | 1 | lastpost | 2 | lastpost | A | 68480 | NULL | NULL | | BTREE | | | cdb_threads | 1 | lastpost | 3 | fid | A | 68480 | NULL | NULL | | BTREE | | | cdb_threads | 1 | replies | 1 | replies | A | 233 | NULL | NULL | | BTREE | | | cdb_threads | 1 | dateline | 1 | dateline | A | 68480 | NULL | NULL | | BTREE | | | cdb_threads | 1 | fid | 1 | fid | A | 10 | NULL | NULL | | BTREE | | | cdb_threads | 1 | enablehot | 1 | enablehot | A | 2 | NULL | NULL | | BTREE | | +-------------+------------+-----------+--------------+-------------+------
看到索引 fid 和 enablehot 基數(shù)太小,看來該索引完全沒必要,不過,對于fid基數(shù)較大的情況,則可能需要保留>該索引.
所做修改如下:
ALTER TABLE `cdb_threads` DROP INDEX `enablehot`, DROP INDEX `fid`, ADD INDEX (`fid`, `lastpost`); ALTER TABLE `cdb_posts` DROP INDEX `fid`, ADD INDEX (`author`(10)); OPTIMIZE TABLE `cdb_posts`; OPTIMIZE TABLE `cdb_threads`;
在這里, p.author 字段我設(shè)定的部分索引長度是 10, 是我經(jīng)過分析后得出來的結(jié)果,不同的系統(tǒng),這里的長度也不同,最好自己先取一下平均值,然后再適當(dāng)調(diào)整.
現(xiàn)在,再來執(zhí)行一次上面的慢查詢,發(fā)現(xiàn)時(shí)間已經(jīng)從 6s 變成 0.19s,提高了 30 倍.
這次先到這里,下次繼續(xù) ^_^
