MySQL优化 之 Discuz论坛优化

作/译者：叶金荣（Email: ），来源：http://imysql.cn 一. 前言 近日由于需要,对discuz论坛(简称dz)进行优化,当然了,只是涉及到数据库的优化. 先说一下服务器及dz的数据量,2 * Intel(R) Xeon(TM) CPU 2.40GHz, 4GB mem, SCISC硬盘. MySQL 版本为 4.0.23. 数

作/译者：叶金荣（Email: ），来源：http://imysql.cn

一. 前言
近日由于需要,对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 中添加/修改以下选项:

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

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

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

1. 搜索优化
搜索的查询SQL大致如下:

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

用 EXPLAIN 分析的结果如下:

 mysql>EXPLAIN  SELECT t.* FROM cdb_posts p, cdb_threads t WHERE<br>    t.fid IN ('37', '45', '4', '6', '17', '41', '28', '32', '31', '1', '42')<br>    AND p.tid=t.tid AND p.author LIKE 'JoansWin'<br>    GROUP BY t.tid ORDER BY lastpost DESC LIMIT 0, 80; <br>+-----------+------------+----------+--------------+-------------+-----------+-------------+<br>| id | select_type | table | type  | possible_keys | key  | key_len | ref         | rows  | Extra<br>+-----------+------------+----------+--------------+-------------+-----------+-------------+<br>|  1 | SIMPLE      | t     | range | PRIMARY,fid   | fid  | 2       | NULL        | 66160 | Using where; <br>Using temporary; Using filesort |<br>|  1 | SIMPLE      | p     | ref   | tid           | tid  | 3       | Forum.t.tid   |    10 | Using where<br>| +----+-------------+-------+-------+---------------+------+---------+-------------+-------+<br>---------<br> 

只用到了 t.fid 和 p.tid,而 p.author 则没有索引可用,总共需要扫描
66160*10 = 661600 次索引,够夸张吧 :(

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

 mysql>show index from cdb_posts; <br>+-----------+------------+----------+--------------+-------------+-----------+----------<br>---+----------+--------+------+--+<br>| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | <br>Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+----<br>---------+-----------+-------------+----------+--------+------+--+<br>| cdb_posts |          0 | PRIMARY  |            1 | pid         | A         |      680114 |     NULL | NULL   |<br>| BTREE      |         |<br>| cdb_posts |          1 | fid      |            1 | fid         | A         |          10 |     NULL | NULL   |<br>| BTREE      |         |<br>| cdb_posts |          1 | tid      |            1 | tid         | A         |       68011 |     NULL | NULL   |<br>| BTREE      |         |<br>| cdb_posts |          1 | tid      |            2 | dateline    | A         |      680114 |     NULL | NULL   |<br>| BTREE      |         |<br>| cdb_posts |          1 | dateline |            1 | dateline    | A         |      680114 |     NULL | NULL   |<br>| BTREE      |         | <br>+-----------+------------+----------+--------------+-------------+-----------+---<br> 

以及

 mysql>show index from cdb_threads; <br>+-----------+------------+----------+--------------+-------------+-----------+-------------+<br>----------+--------+------+-----+<br>| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |<br> Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+-----<br>--------+-----------+-------------+----------+--------+------+-----+<br>| cdb_threads |          0 | PRIMARY   |            1 | tid         | A         |       68480 |     NULL | NULL   |<br>| BTREE      |         |<br>| cdb_threads |          1 | lastpost  |            1 | topped      | A         |           4 |     NULL | NULL   |<br>| BTREE      |         |<br>| cdb_threads |          1 | lastpost  |            2 | lastpost    | A         |       68480 |     NULL | NULL   |<br>| BTREE      |         |<br>| cdb_threads |          1 | lastpost  |            3 | fid         | A         |       68480 |     NULL | NULL   |<br>| BTREE      |         |<br>| cdb_threads |          1 | replies   |            1 | replies     | A         |         233 |     NULL | NULL   |<br>| BTREE      |         |<br>| cdb_threads |          1 | dateline  |            1 | dateline    | A         |       68480 |     NULL | NULL   |<br>| BTREE      |         |<br>| cdb_threads |          1 | fid       |            1 | fid         | A         |          10 |     NULL | NULL   |<br>| BTREE      |         |<br>| cdb_threads |          1 | enablehot |            1 | enablehot   | A         |           2 |     NULL | NULL   |<br>| BTREE      |         | +-------------+------------+-----------+--------------+-------------+------<br> 

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

 ALTER TABLE `cdb_threads` DROP INDEX `enablehot`, DROP INDEX `fid`, ADD INDEX (`fid`, `lastpost`);<br> ALTER TABLE `cdb_posts` DROP INDEX `fid`, ADD INDEX (`author`(10));<br> OPTIMIZE TABLE `cdb_posts`;<br> OPTIMIZE TABLE `cdb_threads`;<br> 

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