MySQL针对Discuz论坛程序的基本优化教程

过了这么久，discuz论坛的问题还是困扰着很多网友，其实从各论坛里看到的问题总结出来，很关键的一点都是因为没有将数据表引擎转成innodb导致的，discuz在并发稍微高一点的环境下就表现的非常糟糕，产生大量的锁等待，这时候如果把数据表引擎改成innodb的话，我相信会好很多。这次就写个扫盲贴吧。

1. 启用innodb引擎，并配置相关参数

#skip-innodb

innodb_additional_mem_pool_size = 16m #一般16m也够了，可以适当调整下
innodb_buffer_pool_size = 6g #如果是专用db的话，一般是内存总量的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;

其他表类似上面，把表名换一下即可...
将表存储引擎改成innodb后，不仅可以避免大量的锁等待，还可以提升查询的效率，因为innodb会把data和index都放在buffer pool中，效率更高。

2.缓存优化
在 my.cnf 中添加/修改以下选项:

 #取消文件系统的外部锁
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-bdb

以上参数根据各自服务器的配置差异进行调整,仅作为参考.

3.索引优化
上面提到了,已经开启了慢查询,那么接下来就要对慢查询进行逐个优化了.

搜索的查询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 分析的结果如下:

 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 基数太小,看来该索引完全没必要,不过,对于fid基数较大的情况,则可能需要保留>该索引.
所做修改如下:

 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 字段我设定的部分索引长度是 10, 是我经过分析后得出来的结果,不同的系统,这里的长度也不同,最好自己先取一下平均值,然后再适当调整.
现在,再来执行一次上面的慢查询,发现时间已经从 6s 变成 0.19s,提高了 30 倍.