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基数较大的情形,则大概需求保存>该索引.
　　以上是“MySQL优化 之 Discuz论坛优化[网站美工]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：