MySQL針對(duì)Discuz論壇程序的基本優(yōu)化教程

更新時(shí)間：2015年11月18日 11:58:16 作者：葉金榮

這篇文章主要介紹了MySQL針對(duì)Discuz論壇程序的基本優(yōu)化教程,包括在緩存和索引等方面的優(yōu)化方法,需要的朋友可以參考下

過(guò)了這么久，discuz論壇的問(wèn)題還是困擾著很多網(wǎng)友，其實(shí)從各論壇里看到的問(wèn)題總結(jié)出來(lái)，很關(guān)鍵的一點(diǎn)都是因?yàn)闆](méi)有將數(shù)據(jù)表引擎轉(zhuǎn)成InnoDB導(dǎo)致的，discuz在并發(fā)稍微高一點(diǎn)的環(huán)境下就表現(xiàn)的非常糟糕，產(chǎn)生大量的鎖等待，這時(shí)候如果把數(shù)據(jù)表引擎改成InnoDB的話，我相信會(huì)好很多。這次就寫(xiě)個(gè)掃盲貼吧。

1. 啟用innodb引擎，并配置相關(guān)參數(shù)

#skip-innodb

innodb_additional_mem_pool_size = 16M #一般16M也夠了，可以適當(dāng)調(diào)整下
innodb_buffer_pool_size = 6G #如果是專(zhuān)用db的話，一般是內(nèi)存總量的80%
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 20
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 120
innodb_file_per_table

修改表引擎為innodb：

mysql> alter table cdb_access engine = innodb;

其他表類(lèi)似上面，把表名換一下即可...
將表存儲(chǔ)引擎改成innodb后，不僅可以避免大量的鎖等待，還可以提升查詢(xún)的效率，因?yàn)閕nnodb會(huì)把data和index都放在buffer pool中，效率更高。

2.緩存優(yōu)化
在 my.cnf 中添加/修改以下選項(xiàng):

 #取消文件系統(tǒng)的外部鎖
skip-locking
#不進(jìn)行域名反解析,注意由此帶來(lái)的權(quán)限/授權(quán)問(wè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
#打開(kāi)表緩存總數(shù),可以避免頻繁的打開(kāi)數(shù)據(jù)表產(chǎn)生的開(kāi)銷(xiāo)
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
#查詢(xún)結(jié)果緩存
query_cache_size = 128M
#設(shè)置超時(shí)時(shí)間,能避免長(zhǎng)連接
set-variable = wait_timeout=60
#最大并發(fā)線程數(shù),cpu數(shù)量*2
thread_concurrency = 4
#記錄慢查詢(xún),然后對(duì)慢查詢(xún)一一優(yōu)化
log-slow-queries = slow.log
long_query_time = 1
#關(guān)閉不需要的表類(lèi)型,如果你需要,就不要加上這個(gè)
skip-bdb

以上參數(shù)根據(jù)各自服務(wù)器的配置差異進(jìn)行調(diào)整,僅作為參考.

3.索引優(yōu)化
上面提到了,已經(jīng)開(kāi)啟了慢查詢(xún),那么接下來(lái)就要對(duì)慢查詢(xún)進(jìn)行逐個(gè)優(yōu)化了.

搜索的查詢(xún)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 則沒(méi)有索引可用,總共需要掃描
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ù)太小,看來(lái)該索引完全沒(méi)必要,不過(guò),對(duì)于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è)定的部分索引長(zhǎng)度是 10, 是我經(jīng)過(guò)分析后得出來(lái)的結(jié)果,不同的系統(tǒng),這里的長(zhǎng)度也不同,最好自己先取一下平均值,然后再適當(dāng)調(diào)整.
現(xiàn)在,再來(lái)執(zhí)行一次上面的慢查詢(xún),發(fā)現(xiàn)時(shí)間已經(jīng)從 6s 變成 0.19s,提高了 30 倍.

您可能感興趣的文章: