MySQL优化 之 Discuz论坛优化

一. 前言

近日由于需要,对discuz论坛(简称dz)进行优化,当然了,只是涉及到数据库的优化.

先说一下服务器及dz的数据量,2 * Intel(R) Xeon(TM) CPU 2.40GHz, 4GB mem, SCISC硬盘.

MySQL 版本为 4.0.23. 数据表情况:

cdb_attachments 2万

cdb_members 10万

cdb_posts 68万

cdb_threads 7万

二. 缓存优化

在 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

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

三. 索引优化

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

1. 搜索优化

搜索的查询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 倍.

这次先到这里,下次继续 ^_^

来自：http://imysql.cn/node/181