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>altertablecdb_accessengine=innodb;
其他表类似上面，把表名换一下即可...
将表存储引擎改成innodb后，不仅可以避免大量的锁等待，还可以提升查询的效率，因为innodb会把data和index都放在bufferpool中，效率更高。
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大致如下:
SELECTt.*FROMcdb_postsp,cdb_threadstWHERE
t.fidIN('37','45','4','6','17','41','28','32','31','1','42')
ANDp.tid=t.tidANDp.authorLIKE'JoansWin'
GROUPBYt.tidORDERBYlastpostDESCLIMIT0,80;
用EXPLAIN分析的结果如下:
mysql>EXPLAINSELECTt.*FROMcdb_postsp,cdb_threadstWHERE
t.fidIN('37','45','4','6','17','41','28','32','31','1','42')
ANDp.tid=t.tidANDp.authorLIKE'JoansWin'
GROUPBYt.tidORDERBYlastpostDESCLIMIT0,80;
+-----------+------------+----------+--------------+-------------+-----------+-------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra +-----------+------------+----------+--------------+-------------+-----------+-------------+ |1|SIMPLE|t|range|PRIMARY,fid|fid|2|NULL|66160|Usingwhere; Usingtemporary;Usingfilesort| |1|SIMPLE|p|ref|tid|tid|3|Forum.t.tid|10|Usingwhere |+----+-------------+-------+-------+---------------+------+---------+-------------+-------+ ---------
只用到了t.fid和p.tid,而p.author则没有索引可用,总共需要扫描
66160*10=661600次索引,够夸张吧:(
再分析cdb_threads和cdb_posts的索引情况:
mysql>showindexfromcdb_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>showindexfromcdb_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基数较大的情况,则可能需要保留>该索引.
所做修改如下:
ALTERTABLE`cdb_threads`DROPINDEX`enablehot`,DROPINDEX`fid`,ADDINDEX(`fid`,`lastpost`); ALTERTABLE`cdb_posts`DROPINDEX`fid`,ADDINDEX(`author`(10)); OPTIMIZETABLE`cdb_posts`; OPTIMIZETABLE`cdb_threads`;
在这里,p.author字段我设定的部分索引长度是10,是我经过分析后得出来的结果,不同的系统,这里的长度也不同,最好自己先取一下平均值,然后再适当调整.
现在,再来执行一次上面的慢查询,发现时间已经从6s变成0.19s,提高了30倍.