这篇文章主要介绍了MySQL针对Discuz论坛程序的基本优化教程,包括在缓存和索引等方面的优化方法,需要的朋友可以参考下

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

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

1. #skip-innodb 

1. innodb_additional_mem_pool_size = 16M #一般16M也够了，可以适当调整下 
2. innodb_buffer_pool_size = 6G #如果是专用db的话，一般是内存总量的80% 
3. innodb_data_file_path = ibdata1:1024M:autoextend 
4. innodb_file_io_threads = 4 
5. innodb_thread_concurrency = 20 
6. innodb_flush_log_at_trx_commit = 1 
7. innodb_log_buffer_size = 16M 
8. innodb_log_file_size = 256M 
9. innodb_log_files_in_group = 3 
10. innodb_max_dirty_pages_pct = 50 
11. innodb_lock_wait_timeout = 120 
12. innodb_file_per_table 

修改表引擎为innodb：

1. MySQL> alter table cdb_access engine = innodb; 

其他表类似上面，把表名换一下即可...

将表存储引擎改成innodb后，不仅可以避免大量的锁等待，还可以提升查询的效率，因为innodb会把data和index都放在buffer pool中，效率更高。

2.缓存优化

在 my.cnf 中添加/修改以下选项:

1. #取消文件系统的外部锁 
2. skip-locking 
3. #不进行域名反解析,注意由此带来的权限/授权问题 
4. skip-name-resolve 
5. #索引缓存,根据内存大小而定,如果是独立的db服务器,可以设置高达80%的内存总量 
6. key_buffer = 512M 
7. #连接排队列表总数 
8. back_log = 200 
9. max_allowed_packet = 2M 
10. #打开表缓存总数,可以避免频繁的打开数据表产生的开销 
11. table_cache = 512 
12. #每个线程排序所需的缓冲 
13. sort_buffer_size = 4M 
14. #每个线程读取索引所需的缓冲 
15. read_buffer_size = 4M 
16. #MyISAM表发生变化时重新排序所需的缓冲 
17. myisam_sort_buffer_size = 64M 
18. #缓存可重用的线程数 
19. thread_cache = 128 
20. #查询结果缓存 
21. query_cache_size = 128M 
22. #设置超时时间,能避免长连接 
23. set-variable = wait_timeout=60 
24. #最大并发线程数,cpu数量*2 
25. thread_concurrency = 4 
26. #记录慢查询,然后对慢查询一一优化 
27. log-slow-queries = slow.log 
28. long_query_time = 1 
29. #关闭不需要的表类型,如果你需要,就不要加上这个 
30. skip-bdb 

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

3.索引优化

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

搜索的查询SQL大致如下:

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

用 EXPLAIN 分析的结果如下:

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

1. +-----------+------------+----------+--------------+-------------+-----------+-------------+ 
2. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 
3. +-----------+------------+----------+--------------+-------------+-----------+-------------+ 
4. | 1 | SIMPLE | t | range | PRIMARY,fid | fid | 2 | NULL | 66160 | Using where;  
5. Using temporary; Using filesort | 
6. | 1 | SIMPLE | p | ref | tid | tid | 3 | Forum.t.tid | 10 | Using where 
7. | +----+-------------+-------+-------+---------------+------+---------+-------------+-------+ 
8. --------- 

只用到了 t.fid 和 p.tid,而 p.author 则没有索引可用,总共需要扫描

66160*10 = 661600 次索引,够夸张吧 :(

再分析 cdb_threads 和 cdb_posts 的索引情况:

1. mysql>show index from cdb_posts;  

1. +-----------+------------+----------+--------------+-------------+-----------+---------- 
2. ---+----------+--------+------+--+ 
3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |  
4. Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+---- 
5. ---------+-----------+-------------+----------+--------+------+--+ 
6. | cdb_posts | 0 | PRIMARY | 1 | pid | A | 680114 | NULL | NULL | 
7. | BTREE | | 
8. | cdb_posts | 1 | fid | 1 | fid | A | 10 | NULL | NULL | 
9. | BTREE | | 
10. | cdb_posts | 1 | tid | 1 | tid | A | 68011 | NULL | NULL | 
11. | BTREE | | 
12. | cdb_posts | 1 | tid | 2 | dateline | A | 680114 | NULL | NULL | 
13. | BTREE | | 
14. | cdb_posts | 1 | dateline | 1 | dateline | A | 680114 | NULL | NULL | 
15. | BTREE | |  
16. +-----------+------------+----------+--------------+-------------+-----------+--- 

以及

1. mysql>show index from cdb_threads;  

1. +-----------+------------+----------+--------------+-------------+-----------+-------------+ 
2. ----------+--------+------+-----+ 
3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | 
4. Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+----- 
5. --------+-----------+-------------+----------+--------+------+-----+ 
6. | cdb_threads | 0 | PRIMARY | 1 | tid | A | 68480 | NULL | NULL | 
7. | BTREE | | 
8. | cdb_threads | 1 | lastpost | 1 | topped | A | 4 | NULL | NULL | 
9. | BTREE | | 
10. | cdb_threads | 1 | lastpost | 2 | lastpost | A | 68480 | NULL | NULL | 
11. | BTREE | | 
12. | cdb_threads | 1 | lastpost | 3 | fid | A | 68480 | NULL | NULL | 
13. | BTREE | | 
14. | cdb_threads | 1 | replies | 1 | replies | A | 233 | NULL | NULL | 
15. | BTREE | | 
16. | cdb_threads | 1 | dateline | 1 | dateline | A | 68480 | NULL | NULL | 
17. | BTREE | | 
18. | cdb_threads | 1 | fid | 1 | fid | A | 10 | NULL | NULL | 
19. | BTREE | | 
20. | cdb_threads | 1 | enablehot | 1 | enablehot | A | 2 | NULL | NULL | 
21. | BTREE | | +-------------+------------+-----------+--------------+-------------+------ 

看到索引 fid 和 enablehot 基数太小,看来该索引完全没必要,不过,对于fid基数较大的情况,则可能需要保留>该索引.

所做修改如下:

1. ALTER TABLE `cdb_threads` DROP INDEX `enablehot`, DROP INDEX `fid`, ADD INDEX (`fid`, `lastpost`); 
2. ALTER TABLE `cdb_posts` DROP INDEX `fid`, ADD INDEX (`author`(10)); 
3. OPTIMIZE TABLE `cdb_posts`; 
4. OPTIMIZE TABLE `cdb_threads`; 

在这里, p.author 字段我设定的部分索引长度是 10, 是我经过分析后得出来的结果,不同的系统,这里的长度也不同,最好自己先取一下平均值,然后再适当调整.

现在,再来执行一次上面的慢查询,发现时间已经从 6s 变成 0.19s,提高了 30 倍.