欧美性猛交XXXX免费看蜜桃,成人网18免费韩国,亚洲国产成人精品区综合,欧美日韩一区二区三区高清不卡,亚洲综合一区二区精品久久

作/譯者：葉金榮（Email:

一. 前言
近日由于需要,對discuz論壇(簡(jiǎn)稱(chēng)dz)進(jìn)行優(yōu)化,當然了,只是涉及到數據庫的優(yōu)化.
先說(shuō)一下服務(wù)器及dz的數據量,2 * Intel(R) Xeon(TM) CPU 2.40GHz, 4GB mem, SCISC硬盤(pán).
MySQL 版本為 4.0.23. 數據表情況:
cdb_attachments 2萬(wàn)
cdb_members 10萬(wàn)
cdb_posts 68萬(wàn)
cdb_threads 7萬(wàn)

二. 緩存優(yōu)化
在 my.cnf 中添加/修改以下選項:

 #取消文件系統的外部鎖skip-locking#不進(jìn)行域名反解析,注意由此帶來(lái)的權限/授權問(wèn)題skip-name-resolve#索引緩存,根據內存大小而定,如果是獨立的db服務(wù)器,可以設置高達80%的內存總量key_buffer = 512M#連接排隊列表總數back_log = 200max_allowed_packet = 2M#打開(kāi)表緩存總數,可以避免頻繁的打開(kāi)數據表產(chǎn)生的開(kāi)銷(xiāo)table_cache = 512#每個(gè)線(xiàn)程排序所需的緩沖sort_buffer_size = 4M#每個(gè)線(xiàn)程讀取索引所需的緩沖read_buffer_size = 4M#MyISAM表發(fā)生變化時(shí)重新排序所需的緩沖myisam_sort_buffer_size = 64M#緩存可重用的線(xiàn)程數thread_cache = 128#查詢(xún)結果緩存query_cache_size = 128M#設置超時(shí)時(shí)間,能避免長(cháng)連接set-variable = wait_timeout=60#最大并發(fā)線(xiàn)程數,cpu數量*2thread_concurrency = 4#記錄慢查詢(xún),然后對慢查詢(xún)一一優(yōu)化log-slow-queries = slow.loglong_query_time = 1#關(guān)閉不需要的表類(lèi)型,如果你需要,就不要加上這個(gè)skip-innodbskip-bdb

以上參數根據各自服務(wù)器的配置差異進(jìn)行調整,僅作為參考.

三. 索引優(yōu)化
上面提到了,已經(jīng)開(kāi)啟了慢查詢(xún),那么接下來(lái)就要對慢查詢(xún)進(jìn)行逐個(gè)優(yōu)化了.

1. 搜索優(yōu)化
搜索的查詢(xún)SQL大致如下:

 SELECT t.* FROM cdb_posts p, cdb_threads t WHEREt.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 WHEREt.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 則沒(méi)有索引可用,總共需要掃描
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 基數太小,看來(lái)該索引完全沒(méi)必要,不過(guò),對于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 字段我設定的部分索引長(cháng)度是 10, 是我經(jīng)過(guò)分析后得出來(lái)的結果,不同的系統,這里的長(cháng)度也不同,最好自己先取一下平均值,然后再適當調整.
現在,再來(lái)執行一次上面的慢查詢(xún),發(fā)現時(shí)間已經(jīng)從 6s 變成 0.19s,提高了 30 倍.
這次先到這里,下次繼續 ^_^