MySQL针对Discuz论坛程序的基本优化教程

#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

mysql> alter table cdb_access engine = innodb;

 #取消文件系统的外部锁
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

 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;

 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
| +----+-------------+-------+-------+---------------+------+---------+-------------+-------+
---------

 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  |   | +-------------+------------+-----------+--------------+-------------+------

 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`;