MySQL針對Discuz服裝論壇t.vhao.net法式的根本優化教程。本站提示廣大學習愛好者：（MySQL針對Discuz服裝論壇t.vhao.net法式的根本優化教程）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL針對Discuz服裝論壇t.vhao.net法式的根本優化教程正文
過了這麼久，discuz服裝論壇t.vhao.net的成績照樣困擾著許多網友，其實從各服裝論壇t.vhao.net裡看到的成績總結出來，很症結的一點都是由於未將數據表引擎轉成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> alter table cdb_access engine = innodb;
其他表相似下面，把表名換一下便可...
將表存儲引擎改成innodb後，不只可以免年夜量的鎖期待，還可以晉升查詢的效力，由於innodb會把data和index都放在buffer pool中，效力更高。
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年夜致以下:
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基數較年夜的情形,則能夠須要保存>該索引.
所做修正以下:
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`;
在這裡, p.author 字段我設定的部門索引長度是 10, 是我經由剖析後得出來的成果,分歧的體系,這裡的長度也分歧,最好本身先取一下均勻值,然後再恰當調劑.
如今,再來履行一次下面的慢查詢,發明時光曾經從 6s 釀成 0.19s,進步了 30 倍.
