MySQLdiscuz!X 帖子表过大导致查询过慢有什么解决方法？
pre_forum_post 表 1.5GB
pre_forum_thread 表 455MB
下面是使用了show full processlist的结果。
mysql> show full processlist;
+------+------+---------------------+--------------+---------+------+------------+----------------------------- --------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+---------------------+--------------+---------+------+------------+----------------------------- --------------------------------+
| 580 | root | 10.132.xx.xx:42314 | lcbdata2 | Query | 0 | statistics | SELECT * FROM pre_forum_post _3 WHERE tid=189595 AND first=1 |
| 1606 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
+------+------+---------------------+--------------+---------+------+------------+----------------------------- --------------------------------+
2 rows in set (0.01 sec)
mysql> show full processlist;
+------+------+---------------------+--------------+---------+------+----------------+------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+---------------------+--------------+---------+------+----------------+------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------+
| 580 | root | 10.132.xx.xx:42314 | lcbdata2 | Query | 31 | Sending data | SELECT * FROM pre_forum_thread WHERE fid=13 AND displayorder>=0 ORDER BY `tid` DESC LIMIT 20 |
| 1606 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
| 1818 | root | 10.241.xx.xx:51084 | lcbdata2 | Query | 26 | Locked | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('203395') |
| 1820 | root | 10.241.xx.xx:51085 | lcbdata2 | Query | 25 | Locked | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('384661') |
| 1830 | root | 10.241.xx.xx:51092 | lcbdata2 | Query | 22 | Locked | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('91359') |
| 1831 | root | 10.241.xx.xx:51093 | lcbdata2 | Query | 22 | Locked | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('9125') |
| 1832 | root | 10.132.38.129:42654 | lcbdata2 | Query | 21 | Locked | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('101747') |
| 1847 | root | 10.241.xx.xx:51101 | lcbdata2 | Query | 14 | Locked | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('98942') |
| 1848 | root | 10.241.xx.xx:51102 | lcbdata2 | Query | 13 | Locked | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('376834') |
| 1849 | root | 10.132.38.129:42671 | lcbdata2 | Query | 12 | Locked | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('29271') |
| 1850 | root | 10.241.xx.xx:51103 | lcbdata2 | Query | 12 | Locked | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('841670') |
| 1857 | root | 10.132.38.129:42677 | lcbdata2 | Query | 11 | Locked | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('141532') |
| 1868 | root | 10.241.xx.xx:51115 | lcbdata2 | Query | 7 | Sorting result | SELECT * FROM pre_forum_thread WHERE `fid` IN('223','192','40','14','4') AND `displayorder` IN('0','1') ORDER BY displayorder DESC, l astpost DESC LIMIT 50 |
| 1870 | root | 10.132.38.129:42692 | lcbdata2 | Query | 5 | Sorting result | SELECT * FROM pre_forum_thread WHERE `fid` IN('223','192','40','14','4') AND `displayorder` IN('0','1') ORDER BY displayorder DESC, l astpost DESC LIMIT 50 |
| 1873 | root | 10.241.xx.xx:51118 | lcbdata2 | Query | 5 | Sending data | SELECT COUNT(*) FROM pre _forum_thread WHERE fid=624 AND displayorder=-2 AND authorid=1554427 |
| 1877 | root | 10.241.xx.xx:51120 | lcbdata2 | Query | 2 | Sorting result | SELECT * FROM pre_forum_thread WHERE `fid` IN('457','378','377','433','370','363','372','371','361','369','362','364','365','366','367 ','368','373','376','375','374','8') AND `displayorder` IN('0','1') ORDER BY displayorder DESC, lastpost DESC LIMIT 50 |
+------+------+---------------------+--------------+---------+------+----------------+------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------+
这个是My.cnf 的配置文件：
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 3M
table_open_cache = 512
sort_buffer_size = 1M
net_buffer_length = 16K
read_buffer_size = 1M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 16M
thread_cache_size = 8
datadir = /data/mysql
server-id = 1
innodb_data_home_dir = /usr/local/mysql/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
引用来自“leo108”的答案
@mark35 把dz的mysql换成了postgre，据说效果挺好
http://www.discuz.net/thread-2990167-1-1.html
效果嘛，那个帖子里面从理论到结果都写得很清楚。那个自从换了数据库一了百了论坛性能刚刚的。
楼主的问题有几个办法:
1、最简单的是升级服务器（主要是磁盘系统），用硬件堆砌来填补数据库性能窟窿。随着论坛流量增加投入资金也要增加
2、麻烦点的是分析数据库屏蔽，针对性添加索引来缓解表锁，或者到dz后台管理中看看关于性能设置参数。比如
UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('91359')这个看看是否在后台设置中打开了帖子浏览/附件下载延迟更新
3、工作量最大的是彻底替换mysql这货，从此一了百了
@mark35 把dz的mysql换成了postgre，据说效果挺好
http://www.discuz.net/thread-2990167-1-1.html
引用来自“mark35”的答案
引用来自“leo108”的答案
@mark35 把dz的mysql换成了postgre，据说效果挺好
http://www.discuz.net/thread-2990167-1-1.html
效果嘛，那个帖子里面从理论到结果都写得很清楚。那个自从换了数据库一了百了论坛性能刚刚的。
楼主的问题有几个办法:
1、最简单的是升级服务器（主要是磁盘系统），用硬件堆砌来填补数据库性能窟窿。随着论坛流量增加投入资金也要增加
2、麻烦点的是分析数据库屏蔽，针对性添加索引来缓解表锁，或者到dz后台管理中看看关于性能设置参数。比如
UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('91359')这个看看是否在后台设置中打开了帖子浏览/附件下载延迟更新
3、工作量最大的是彻底替换mysql这货，从此一了百了
楼主，
还有第四个方法
付钱给楼上， 只要给够钱， 肯定能搞定