Mysql5.1大表分區(qū)效率測(cè)試
Mysql5.1大表分區(qū)效率測(cè)試
MySQL | add at 2009-03-27 12:29:31 by PConline | view:60, comment:0
mysql5.1開始支持?jǐn)?shù)據(jù)表分區(qū)了，原來的分表可以不用了，分表的不足在于多表查詢不方便。呵呵，下面來簡(jiǎn)單測(cè)試下表分區(qū)的查詢效率。
1、用來測(cè)試的數(shù)據(jù)為discuz論壇的數(shù)據(jù)庫，表為cdb_posts表，數(shù)據(jù)量為1500多萬條
mysql> select count(*) from cdb_posts;
+-------------+
| count(*) |
+-------------+
| 15276429 |
+-------------+
1 row in set (0.04 sec)
2、為了增強(qiáng)表的擴(kuò)展性，將cdb_posts表分為10個(gè)分區(qū)，新建一個(gè)表cdb_posts1,建表語句為
CREATE TABLE `cdb_posts1` (
`pid` int(10) unsigned NOT NULL auto_increment,
`fid` smallint(6) unsigned NOT NULL default '0',
`tid` mediumint(8) unsigned NOT NULL default '0',
`first` tinyint(1) NOT NULL default '0',
`author` varchar(15) NOT NULL default '',
`authorid` mediumint(8) unsigned NOT NULL default '0',
`subject` varchar(80) NOT NULL default '',
`dateline` int(10) unsigned NOT NULL default '0',
`message` mediumtext NOT NULL,
`useip` varchar(15) NOT NULL default '',
`invisible` tinyint(1) NOT NULL default '0',
`anonymous` tinyint(1) NOT NULL default '0',
`usesig` tinyint(1) NOT NULL default '0',
`htmlon` tinyint(1) NOT NULL default '0',
`bbcodeoff` tinyint(1) NOT NULL default '0',
`smileyoff` tinyint(1) NOT NULL default '0',
`parseurloff` tinyint(1) NOT NULL default '0',
`attachment` tinyint(1) NOT NULL default '0',
`rate` smallint(6) NOT NULL default '0',
`ratetimes` tinyint(3) unsigned NOT NULL default '0',
`status` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`pid`),
KEY `fid` (`fid`),
KEY `dateline` (`dateline`),
KEY `authorid` (`authorid`),
KEY `invisible` (`invisible`),
KEY `displayorder` (`tid`,`invisible`,`dateline`),
KEY `first` (`tid`,`first`)
) ENGINE=MyISAM AUTO_INCREMENT=17715224 DEFAULT CHARSET=gbk PARTITION BY RANGE (pid) (
PARTITION p0 VALUES LESS THAN (3000000),
PARTITION p1 VALUES LESS THAN (6000000),
PARTITION p2 VALUES LESS THAN (9000000),
PARTITION p3 VALUES LESS THAN (12000000),
PARTITION p4 VALUES LESS THAN (15000000),
PARTITION p5 VALUES LESS THAN (18000000),
PARTITION p6 VALUES LESS THAN (21000000),
PARTITION p7 VALUES LESS THAN (24000000),
PARTITION p8 VALUES LESS THAN (27000000),
PARTITION p9 VALUES LESS THAN (30000000),
PARTITION p10 VALUES LESS THAN MAXVALUE
);
3、接下來將cdb_post表的數(shù)據(jù)導(dǎo)入到cdb_posts1
mysql>insert into cdb_posts1 select * from cdb_posts;
由于數(shù)據(jù)量非常大，所用的時(shí)間當(dāng)然也就很長(zhǎng)，在雙路雙核1.6G 4M L2cache CPU，2G內(nèi)存的機(jī)器上這個(gè)過程花費(fèi)了1小時(shí)3秒。
接下來測(cè)試分區(qū)后的表和未分區(qū)的表的查詢速度比較。
測(cè)試程序如下：
代碼如下
<?php
mysql_connect("localhost","root","");
mysql_select_db("discuz"); )
//cdb_posts
$start = microtime_float(); //開始時(shí)間
for($i=0;$i<100;$i++) { //查詢100次
$rand = rand(100,15000000);
$sql = "select * from cdb_posts where pid= ".$rand;
mysql_query($sql);
$i++;
}
$end = microtime_float(); //結(jié)束時(shí)間
//cdb_posts1
$posts = "select cdb_posts has total spend ".($end-$start)." secondsn";
$start = microtime_float(); //開始時(shí)間
for($i=0;$i<100;$i++) { //查詢100次
$rand = rand(100,15000000);
$sql = "select * from cdb_posts1 where pid = ".$rand;
mysql_query($sql);
$i++;
}
$end = microtime_float(); //結(jié)束時(shí)間
$posts1 = "select cdb_posts1 has total spend ".($end-$start)." secondsn";
echo "nnn";
echo $posts."n";
echo $posts1."n";
mysql_query("flush table cdb_posts"); //清除表緩存
mysql_query("flush table cdb_posts1"); //清除表緩存
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
?>
為了盡可能的避免Mysql查詢使用緩存，將pid采用rand函數(shù)取隨機(jī)數(shù)。
執(zhí)行這個(gè)php
select cdb_posts has total spend 0.48239207267761 seconds
select cdb_posts1 has total spend 1.4392290115356 seconds
接下來將查詢次數(shù)改到1000
select cdb_posts has total spend 6.6411547660828 seconds
select cdb_posts1 has total spend 13.684276103973 seconds
接下來我把查詢次數(shù)增加到10000，執(zhí)行結(jié)果為
select cdb_posts has total spend 42.948129892349 seconds
select cdb_posts1 has total spend 68.492646932602 seconds
從以上結(jié)果可以看出，大表進(jìn)行分區(qū)后，查詢效率有所降低，但是隨著查詢次數(shù)的增多，所用時(shí)間的差距不斷減小。
.Net架構(gòu)網(wǎng)站遇到大表該怎么辦？
最近做的web2.0網(wǎng)站本身遇到一個(gè)大表(千萬rows左右)，因?yàn)閷?duì)于performance，web本身可用性的考慮，必須想辦法boost perf.
這種情況應(yīng)該都用partition來搞定了，這也符合分治等算法的思想，想辦法降低問題本身的復(fù)雜度，然后在一個(gè)一個(gè)解決。
mysql中一般到100萬操作就有點(diǎn)麻煩了，index要好好的做。這里還遇到了一個(gè)文本檢索問題，MyIASM storage engine里面有個(gè)full-text index,但是不知道
它對(duì)于中文支持如何，而且不清楚它是怎么分詞的，不大清楚后臺(tái)邏輯，Mysql這種index limitation很多，很難scalable,所以基本上直接考慮用search engine那一套。直接上了lucene+solr+solrsharp.小表like還可以忽悠忽悠，大點(diǎn)就慢的如老牛....
Partition通過了解發(fā)現(xiàn)解決方案倒是不少，結(jié)合了以前做過一點(diǎn)這方面知識(shí)儲(chǔ)備。
對(duì)hivedb,hscale等都沒想過要嘗試，發(fā)現(xiàn).net在使用open source很多都不是很舒服。
最開始嘗試了mysql partition，一開始聽起來方法這種方案很Perfect!是mysql解決horizontal partioning的很好方案，等document看完了，發(fā)現(xiàn)
5.1版本的partion limitation太多了，只能適合某些特性的場(chǎng)景，例如按照用戶id做split;普通那種非unique key,primary key是很難搞定的，簡(jiǎn)單方法是給表本身
不添加任何主鍵，自己來實(shí)現(xiàn)主鍵生成機(jī)制。這樣仿佛可以了，但是通過explain partitions來做下analysis,發(fā)現(xiàn)結(jié)果定位具體parition不好，這就很難降低IO本身的高成本了。沒有通過具體測(cè)試不知道可能是explain partition本身不準(zhǔn)確原因還是。。。
mysql partition還有一個(gè)很大的弊病，就是很難跨機(jī)器，當(dāng)然如何能夠把Mysql存儲(chǔ)做成分布式，也還好，但是這個(gè)技術(shù)代價(jià)都上了不少檔次，risk過高了，只能算是下下策了，備用好了。這些不爽的地方導(dǎo)致偶們直接拋棄了這種方案，直接用手工切分來搞定這種問題，我想這也是大部分這種需求的常見solution把。
手工切分本身技術(shù)還比較簡(jiǎn)單，就是要考慮表的編碼，管理等多個(gè)方面，以及如何快速定位到可能的partition,這些在設(shè)計(jì)方面都應(yīng)該注意了。
而且對(duì)于多partitions的結(jié)果，應(yīng)該使用多線程等并發(fā)，同步技術(shù)來提高perf.
這里的partition還做到支持對(duì)某一個(gè)partition做進(jìn)一步切分，這樣切分到每一個(gè)partition塊盡量表中數(shù)據(jù)在50萬以下，這樣加上db index,速度應(yīng)該能夠滿足一定的需求的，手工切分本身很容易scale out,可以把表放在不同的機(jī)器上等等load balance方法來scale.
回想感覺最有意思還是表編碼自身的考慮有點(diǎn)意思，我很大程度的靈感來源于IP地址的劃分，因?yàn)檫@個(gè)表自身增長(zhǎng)速度會(huì)很慢，所以采用unsigned int來搞定，43億來表示2000萬還是小意思嘛。我主要是通過前綴+長(zhǎng)度來定義表的標(biāo)識(shí)。1，2，3前綴可以讓給數(shù)據(jù)比較密集的表，因?yàn)樗鼈兛梢灾С?span lang="EN-US">10位，其它就用9位來表示，可能有些不再切分范圍內(nèi)的就讓他們從0開始增長(zhǎng)把。這里partition list本身維護(hù)可以序列化到filesystem中，每次Load class時(shí)候deserialize一下，然后就是本身partition如何快速定位就需要用點(diǎn)復(fù)雜點(diǎn)的data stuctures了。
[MySQL優(yōu)化案例]系列 -- 在5.1的分區(qū)功能中混用InnoDB和MyISAM
周二, 2009/03/17 - 16:42 — yejr
MySQL 5.1中增加了分區(qū)(partition)功能，有了這個(gè)功能，以前很頭疼的分表方案，現(xiàn)在就變得不再那么麻煩了。不過，如果采用了MyISAM引擎，而且在數(shù)據(jù)量較大的情境下，并發(fā)讀寫仍然是個(gè)問題，尤其是對(duì)索引的更新。為此，可以在分區(qū)表中采用MyISAM和InnoDB引擎混用的方法，大致如下：
mysql>
mysql> CREATE TABLE test_part(
-> date DATE NOT NULL DEFAULT '0000-00-00',
-> comment VARCHAR(20) DEFAULT NULL
-> )ENGINE=MyISAM
-> PARTITION BY RANGE (to_days(date))
-> (
-> PARTITION nov08 VALUES LESS THAN(TO_DAYS('2008-12-01')),
-> PARTITION dec08 VALUES LESS THAN(TO_DAYS('2009-01-01')),
-> PARTITION jan09 VALUES LESS THAN(TO_DAYS('2009-02-01')),
-> PARTITION feb09 VALUES LESS THAN(TO_DAYS('2009-03-01')),
-> PARTITION mar09 VALUES LESS THAN(TO_DAYS('2009-04-01')) ENGINE=InnoDB,
-> PARTITION unpart VALUES LESS THAN MAXVALUE
-> );
這樣的話，就可以利用InnoDB的行鎖以及buffer pool實(shí)現(xiàn)了對(duì)索引以及行記錄的并發(fā)讀寫，大大提高效率。不幸的是，目前5.1還不支持這樣的混合引擎特性，所以，上面的想法暫時(shí)只是美好的愿望了，哈哈。
上面的創(chuàng)意來自：Venu Anuganti，原文出自：http://venublog.com/2009/03/16/m ... lers-in-partitions/
【求助】MySQL的InnoDB引擎的優(yōu)化問題
--------------------------------------------------------------------------------
magiclx
單表記錄數(shù)量級(jí)別是1000w級(jí)，將來可能是在5000w級(jí)。現(xiàn)在有四個(gè)問題：
1，對(duì)關(guān)聯(lián)表建立外鍵后，如何優(yōu)化插入或更新的速度？如何配置MySQL？
2，除了添加索引、優(yōu)化查詢語句，還有什么辦法能提高MySQL查詢的性能？
3，分表分庫的技術(shù)資料哪里可找到？請(qǐng)熟悉的人推薦書籍或講講經(jīng)驗(yàn)。
4，MySQL有沒有緩存技術(shù)？除了在架構(gòu)上，有什么辦法讓MySQL支持大數(shù)據(jù)量表的關(guān)聯(lián)查詢？（壓力到達(dá)1000萬記錄的表自關(guān)聯(lián)10次）
--------------------------------------------------------------------------------
綠茵汗將
我隨便說說幾點(diǎn)
一、像樓主這么大的數(shù)據(jù)量的話，通過優(yōu)化sql語句提升performance的空間我覺得比較小。數(shù)據(jù)量大了要分表或分區(qū)。
1）如果表里大部分?jǐn)?shù)據(jù)是以前的數(shù)據(jù)，現(xiàn)在不用了的，像論壇，2年前的帖子就很少打開了，這種可以分區(qū)；
2）經(jīng)常的數(shù)據(jù)還可以用分表的方式，把單表分成多個(gè)表。像用戶信息表，就可以按用戶名的md5值的第一個(gè)字母切分，那就可以把一個(gè)User表切出User_0 、User_1、User_2 …… User_a……User_f共16個(gè)表來，如果是按md5的前兩位字母切分表的話，那將可以分出256個(gè)表出來，這樣一來每個(gè)表的數(shù)據(jù)就少的多了。怎么查詢呢？舉個(gè)用戶登錄的例子來說：用戶通過表單提交用戶名為“a"，程序計(jì)算出a的md5為“0cc175b9c0f1b6a831c399e269772661”，取第一位：0，那么顯然，這個(gè)用戶的資料是存在了 User_0 這張表，然后 SELECT * FROM User_0 where user_name = 'a' 。如果不存在？那當(dāng)然就說明這個(gè)用戶根本沒注冊(cè)嘛。分庫的原理是一樣的。
二、上面講的是存儲(chǔ)，還有架構(gòu)上。如果有多臺(tái)數(shù)據(jù)庫服務(wù)器的話，把數(shù)據(jù)庫的讀寫分開。樓主可以找找資料看 mysql replication。
三、mysql也有query cache，我印象中不是經(jīng)常用的，一般單臺(tái)服務(wù)器跑PHP的話會(huì)用APC。多臺(tái)服務(wù)器之間共享緩存可以用memchached，這東西其它語言也有API。像很多不需要實(shí)時(shí)更新的數(shù)據(jù)都可以塞到APC/memcached里邊
--------------------------------------------------------------------------------
YinH
分區(qū)方面，我記得mysql 5.1開始支持分區(qū)表了吧，不過似乎引擎也不是innodb了，畢竟innodb被oracle收購了嘛。
感覺一般處理大數(shù)據(jù)量的方案還是分成多個(gè)表，然后就是不斷的生成中間表
--------------------------------------------------------------------------------
hshh
1. innodb使用每個(gè)表使用獨(dú)立的表空間: innodb_file_per_table
2. innodb_buffer_pool_size為物理內(nèi)存25%~50%
3. innodb_additional_mem_pool_size >= 8M
4. innodb_log_file_size 建議>=50M
5. 如果系統(tǒng)很穩(wěn)定, innodb_flush_log_at_trx_commit=0
這說明innodb需要大量的disk io, 因此你的硬盤要足夠快, 包括了傳輸快, 和尋道快
6. key_buffer與innodb_buffer_pool_size一樣大小
7. 如果cpu性能很不錯(cuò), 建議關(guān)閉query_cache, 或者query_cache_type=2, 在大量同一查詢而且更新很少量才強(qiáng)制使用query cache. cpu性能越好query cache越影響mysql的性能, 但是不建議使用8M以下的query cache
關(guān)于query cache的性能影響:
我在測(cè)試用E5310*2 (Quad Core 1.6G), 那么就是8CPU情況下面,關(guān)閉query cache可以比打開時(shí)候提高30%+的純的隨機(jī)查詢性能, 40%+的隨機(jī)select+update性能
8. 還有不少關(guān)于order, join等,建議參看mysql手冊(cè)
9. 目前最新版本mysql 5.1的innodb還不穩(wěn)定, 我在測(cè)試100并發(fā)select+update操作的時(shí)候會(huì)死鎖, 不知道mysqld在干什么.
--------------------------------------------------------------------------------
magiclx
謝謝綠茵汗將，YinH和老大的經(jīng)驗(yàn)分享，MySQL的潛力還是有的，需要優(yōu)化出來。
現(xiàn)在正在做多線程的性能測(cè)試，SuSE的服務(wù)器，現(xiàn)在有50多個(gè)MySQL連接查詢和更新，基本服務(wù)器接近掛了，可能是MySQL還要配置。
可能后一步會(huì)用到依據(jù)大表的某列來分表的方案，在開發(fā)的復(fù)雜度上也不會(huì)增加太多，數(shù)量級(jí)直接可以下降一個(gè)數(shù)量級(jí)。
據(jù)說douban的后臺(tái)只用了一臺(tái)AMD服務(wù)器和MySQL就支撐下來了，好牛。
--------------------------------------------------------------------------------
hshh
另外說句: innodb表越大性能下降越大, select count(*) 這樣的查詢是要做全表檢索的.
當(dāng)表很大的時(shí)候,query cache又有一點(diǎn)效果了.
--------------------------------------------------------------------------------
magiclx
另外說句: innodb表越大性能下降越大, select count(*) 這樣的查詢是要做全表檢索的.
當(dāng)表很大的時(shí)候,query cache又有一點(diǎn)效果了.
是的，MyISAM的select count(*)只需讀一行記錄。
一般select count(*)的時(shí)候可能是導(dǎo)出時(shí)會(huì)用到，一般都會(huì)有Where語句的，只要Where的是一列有索引的，還是不慢的
以且應(yīng)該優(yōu)化什么？
硬件
操作系統(tǒng)/軟件庫
SQL服務(wù)器(設(shè)置和查詢)
應(yīng)用編程接口(API)
應(yīng)用程序
--------------------------------------------------------------------------------
二、優(yōu)化硬件
如果你需要龐大的數(shù)據(jù)庫表(>2G)，你應(yīng)該考慮使用64位的硬件結(jié)構(gòu)，像Alpha、Sparc或即將推出的IA64。因?yàn)?span lang="EN-US">MySQL內(nèi)部使用大量64位的整數(shù)，64位的CPU將提供更好的性能。
對(duì)大數(shù)據(jù)庫，優(yōu)化的次序一般是RAM、快速硬盤、CPU能力。
更多的內(nèi)存通過將最常用的鍵碼頁面存放在內(nèi)存中可以加速鍵碼的更新。
如果不使用事務(wù)安全(transaction-safe)的表或有大表并且想避免長(zhǎng)文件檢查，一臺(tái)UPS就能夠在電源故障時(shí)讓系統(tǒng)安全關(guān)閉。
對(duì)于數(shù)據(jù)庫存放在一個(gè)專用服務(wù)器的系統(tǒng)，應(yīng)該考慮1G的以太網(wǎng)。延遲與吞吐量同樣重要。
--------------------------------------------------------------------------------
三、優(yōu)化磁盤
為系統(tǒng)、程序和臨時(shí)文件配備一個(gè)專用磁盤，如果確是進(jìn)行很多修改工作，將更新日志和事務(wù)日志放在專用磁盤上。
低尋道時(shí)間對(duì)數(shù)據(jù)庫磁盤非常重要。對(duì)與大表，你可以估計(jì)你將需要log(行數(shù))/log(索引塊長(zhǎng)度/3*2/(鍵碼長(zhǎng)度 + 數(shù)據(jù)指針長(zhǎng)度))+1次尋到才能找到一行。對(duì)于有500000行的表，索引Mediun int類型的列，需要log(500000) / log(1024/3*2/(3 + 2))+1=4次尋道。上述索引需要500000*7*3/2=5.2M的空間。實(shí)際上，大多數(shù)塊將被緩存，所以大概只需要1-2次尋道。
然而對(duì)于寫入（如上），你將需要4次尋道請(qǐng)求來找到在哪里存放新鍵碼，而且一般要2次尋道來更新索引并寫入一行。
對(duì)于非常大的數(shù)據(jù)庫，你的應(yīng)用將受到磁盤尋道速度的限制，隨著數(shù)據(jù)量的增加呈N log N數(shù)據(jù)級(jí)遞增。
將數(shù)據(jù)庫和表分在不同的磁盤上。在MySQL中，你可以為此而使用符號(hào)鏈接。
條列磁盤(RAID 0)將提高讀和寫的吞吐量。
帶鏡像的條列(RAID 0+1)將更安全并提高讀取的吞吐量。寫入的吞吐量將有所降低。
不要對(duì)臨時(shí)文件或可以很容易地重建的數(shù)據(jù)所在的磁盤使用鏡像或RAID(除了RAID 0)。
在Linux上，在引導(dǎo)時(shí)對(duì)磁盤使用命令hdparm -m16 -d1以啟用同時(shí)讀寫多個(gè)扇區(qū)和DMA功能。這可以將響應(yīng)時(shí)間提高5~50%。
在Linux上，用async (默認(rèn))和noatime掛載磁盤(mount)。
對(duì)于某些特定應(yīng)用，可以對(duì)某些特定表使用內(nèi)存磁盤，但通常不需要。
--------------------------------------------------------------------------------
四、優(yōu)化操作系統(tǒng)
不要交換區(qū)。如果內(nèi)存不足，增加更多的內(nèi)存或配置你的系統(tǒng)使用較少內(nèi)存。
不要使用NFS磁盤(會(huì)有NFS鎖定的問題)。
增加系統(tǒng)和MySQL服務(wù)器的打開文件數(shù)量。(在safe_mysqld腳本中加入ulimit -n #)。
增加系統(tǒng)的進(jìn)程和線程數(shù)量。
如果你有相對(duì)較少的大表，告訴文件系統(tǒng)不要將文件打碎在不同的磁道上(Solaris)。
使用支持大文件的文件系統(tǒng)(Solaris)。
選擇使用哪種文件系統(tǒng)。在Linux上的Reiserfs對(duì)于打開、讀寫都非常快。文件檢查只需幾秒種。
--------------------------------------------------------------------------------
五、選擇應(yīng)用編程接口
PERL
可在不同的操作系統(tǒng)和數(shù)據(jù)庫之間移植。
適宜快速原型。
應(yīng)該使用DBI/DBD接口。
PHP
比PERL易學(xué)。
使用比PERL少的資源。
通過升級(jí)到PHP4可以獲得更快的速度。
C
MySQL的原生接口。
較快并賦予更多的控制。
低層，所以必須付出更多。
C++
較高層次，給你更多的時(shí)間來編寫應(yīng)用。
仍在開發(fā)中
ODBC
運(yùn)行在Windows和Unix上。
幾乎可在不同的SQL服務(wù)器間移植。
較慢。MyODBC只是簡(jiǎn)單的直通驅(qū)動(dòng)程序，比用原生接口慢19%。
有很多方法做同樣的事。很難像很多ODBC驅(qū)動(dòng)程序那樣運(yùn)行，在不同的領(lǐng)域還有不同的錯(cuò)誤。
問題成堆。Microsoft偶爾還會(huì)改變接口。
不明朗的未來。(Microsoft更推崇OLE而非ODBC)
ODBC
運(yùn)行在Windows和Unix上。
幾乎可在不同的SQL服務(wù)器間移植。
較慢。MyODBC只是簡(jiǎn)單的直通驅(qū)動(dòng)程序，比用原生接口慢19%。
有很多方法做同樣的事。很難像很多ODBC驅(qū)動(dòng)程序那樣運(yùn)行，在不同的領(lǐng)域還有不同的錯(cuò)誤。
問題成堆。Microsoft偶爾還會(huì)改變接口。
不明朗的未來。(Microsoft更推崇OLE而非ODBC)
JDBC
理論上可在不同的操作系統(tǒng)何時(shí)據(jù)庫間移植。
可以運(yùn)行在web客戶端。
Python和其他
可能不錯(cuò)，可我們不用它們。
--------------------------------------------------------------------------------
六、優(yōu)化應(yīng)用
應(yīng)該集中精力解決問題。
在編寫應(yīng)用時(shí)，應(yīng)該決定什么是最重要的：
速度
操作系統(tǒng)間的可移植性
SQL服務(wù)器間的可移植性
使用持續(xù)的連接。.
緩存應(yīng)用中的數(shù)據(jù)以減少SQL服務(wù)器的負(fù)載。
不要查詢應(yīng)用中不需要的列。
不要使用SELECT * FROM table_name...
測(cè)試應(yīng)用的所有部分，但將大部分精力放在在可能最壞的合理的負(fù)載下的測(cè)試整體應(yīng)用。通過以一種模塊化的方式進(jìn)行，你應(yīng)該能用一個(gè)快速“啞模塊”替代找到的瓶頸，然后很容易地標(biāo)出下一個(gè)瓶頸。
如果在一個(gè)批處理中進(jìn)行大量修改，使用LOCK TABLES。例如將多個(gè)UPDATES或DELETES集中在一起。
--------------------------------------------------------------------------------
七、應(yīng)該使用可移植的應(yīng)用
Perl DBI/DBD
ODBC
JDBC
Python(或其他有普遍SQL接口的語言)
你應(yīng)該只使用存在于所有目的SQL服務(wù)器中或可以很容易地用其他構(gòu)造模擬的SQL構(gòu)造。www.mysql.com上的Crash-me頁可以幫助你。
為操作系統(tǒng)/SQL服務(wù)器編寫包裝程序來提供缺少的功能。
--------------------------------------------------------------------------------
八、如果你需要更快的速度，你應(yīng)該：
找出瓶頸(CPU、磁盤、內(nèi)存、SQL服務(wù)器、操作系統(tǒng)、API或應(yīng)用)并集中全力解決。
使用給予你更快速度/靈活性的擴(kuò)展。
逐漸了解SQL服務(wù)器以便能為你的問題使用可能最快的SQL構(gòu)造并避免瓶頸。
優(yōu)化表布局和查詢。
使用復(fù)制以獲得更快的選擇(select)速度。
如果你有一個(gè)慢速的網(wǎng)絡(luò)連接數(shù)據(jù)庫，使用壓縮客戶/服務(wù)器協(xié)議。
不要害怕時(shí)應(yīng)用的第一個(gè)版本不能完美地移植，在你解決問題時(shí)，你總是可以在以后優(yōu)化它。
--------------------------------------------------------------------------------
九、優(yōu)化MySQL
挑選編譯器和編譯選項(xiàng)。
位你的系統(tǒng)尋找最好的啟動(dòng)選項(xiàng)。
通讀MySQL參考手冊(cè)并閱讀Paul DuBios的《MySQL》一書。(已有中文版-譯注)
多用EXPLAIN SELECT、SHOW VARIABLES、SHOW STATUS和SHOW PROCESSLIST。
了解查詢優(yōu)化器的工作原理。
優(yōu)化表的格式。
維護(hù)你的表(myisamchk、CHECK TABLE、 OPTIMIZE TABLE)
使用MySQL的擴(kuò)展功能以讓一切快速完成。
如果你注意到了你將在很多場(chǎng)合需要某些函數(shù)，編寫MySQL UDF函數(shù)。
不要使用表級(jí)或列級(jí)的GRANT，除非你確實(shí)需要。
購買MySQL技術(shù)支持以幫助你解決問題憨笑
--------------------------------------------------------------------------------
十、編譯和安裝MySQL
通過位你的系統(tǒng)挑選可能最好的編譯器，你通常可以獲得10-30%的性能提高。
在Linux/Intel平臺(tái)上，用pgcc(gcc的奔騰芯片優(yōu)化版)編譯MySQL。然而，二進(jìn)制代碼將只能運(yùn)行在Intel奔騰CPU上。
對(duì)于一種特定的平臺(tái)，使用MySQL參考手冊(cè)上推薦的優(yōu)化選項(xiàng)。
一般地，對(duì)特定CPU的原生編譯器(如Sparc的Sun Workshop)應(yīng)該比gcc提供更好的性能，但不總是這樣。
用你將使用的字符集編譯MySQL。
靜態(tài)編譯生成mysqld的執(zhí)行文件(用--with-mysqld-ldflags=all-static)并用strip sql/mysqld整理最終的執(zhí)行文件。
注意，既然MySQL不使用C++擴(kuò)展，不帶擴(kuò)展支持編譯MySQL將贏得巨大的性能提高。
如果操作系統(tǒng)支持原生線程，使用原生線程(而不用mit-pthreads)。
用MySQL基準(zhǔn)測(cè)試來測(cè)試最終的二進(jìn)制代碼。
--------------------------------------------------------------------------------
十一、維護(hù)
如果可能，偶爾運(yùn)行一下OPTIMIZE table，這對(duì)大量更新的變長(zhǎng)行非常重要。
偶爾用myisamchk -a更新一下表中的鍵碼分布統(tǒng)計(jì)。記住在做之前關(guān)掉MySQL。
如果有碎片文件，可能值得將所有文件復(fù)制到另一個(gè)磁盤上，清除原來的磁盤并拷回文件。
如果遇到問題，用myisamchk或CHECK table檢查表。
用mysqladmin -i10 precesslist extended-status監(jiān)控MySQL的狀態(tài)。
用MySQL GUI客戶程序，你可以在不同的窗口內(nèi)監(jiān)控進(jìn)程列表和狀態(tài)。
使用mysqladmin debug獲得有關(guān)鎖定和性能的信息。
--------------------------------------------------------------------------------
十二、優(yōu)化SQL
揚(yáng)SQL之長(zhǎng)，其它事情交由應(yīng)用去做。使用SQL服務(wù)器來做：
找出基于WHERE子句的行。
JOIN表
GROUP BY
ORDER BY
DISTINCT
不要使用SQL來做：
檢驗(yàn)數(shù)據(jù)(如日期)
成為一只計(jì)算器
技巧：
明智地使用鍵碼。
鍵碼適合搜索，但不適合索引列的插入/更新。
保持?jǐn)?shù)據(jù)為數(shù)據(jù)庫第三范式，但不要擔(dān)心冗余信息或這如果你需要更快的速度，創(chuàng)建總結(jié)表。
在大表上不做GROUP BY，相反創(chuàng)建大表的總結(jié)表并查詢它。
UPDATE table set count=count+1 where key_column=constant非常快。
對(duì)于大表，或許最好偶爾生成總結(jié)表而不是一直保持總結(jié)表。
充分利用INSERT的默認(rèn)值。
--------------------------------------------------------------------------------
十三、不同SQL服務(wù)器的速度差別（以秒計(jì)）
+--------------------------+--------+---------+
|通過鍵碼讀取2000000行： | NT | Linux |
+--------------------------+--------+---------+
|mysql | 367 | 249 |
+--------------------------+--------+---------+
|mysql_odbc | 464 | |
+--------------------------+--------+---------+
|db2_odbc | 1206 | |
+--------------------------+--------+---------+
|informix_odbc | 121126 | |
+--------------------------+--------+---------+
|ms-sql_odbc | 1634 | |
+--------------------------+--------+---------+
|oracle_odbc | 20800 | |
+--------------------------+--------+---------+
|solid_odbc | 877 | |
+--------------------------+--------+---------+
|sybase_odbc | 17614 | |
+--------------------------+--------+---------+
+--------------------------+--------+---------+
|插入350768行： | NT | Linux |
+--------------------------+--------+---------+
|mysql | 381 | 206 |
+--------------------------+--------+---------+
|mysql_odbc | 619 | |
+--------------------------+--------+---------+
|db2_odbc | 3460 | |
+--------------------------+--------+---------+
|informix_odbc | 2692 | |
+--------------------------+--------+---------+
|ms-sql_odbc | 4012 | |
+--------------------------+--------+---------+
|oracle_odbc | 11291 | |
+--------------------------+--------+---------+
|solid_odbc | 1801 | |
+--------------------------+--------+---------+
|sybase_odbc | 4802 | |
+--------------------------+--------+---------+
在上述測(cè)試中，MySQL配置8M高速緩存運(yùn)行，其他數(shù)據(jù)庫以默認(rèn)安裝運(yùn)行。
本文來自: (www.91linux.com) 詳細(xì)出處參考：http://www.91linux.com/html/arti ... /20071213/9050.html
網(wǎng)站整體優(yōu)化其一：數(shù)據(jù)庫優(yōu)化http://news.jx163.com 發(fā)布時(shí)間：2009-05-05 來源：站長(zhǎng)網(wǎng) 作者： 點(diǎn)擊： 8
目前web2.0的程序，很大瓶頸是數(shù)據(jù)庫的吞度量。不過，如何才能確定系統(tǒng)的瓶頸是數(shù)據(jù)庫呢，因?yàn)橹挥写_定數(shù)據(jù)庫是整個(gè)系統(tǒng)的瓶頸，我們才有必要去優(yōu)化他，畢竟，還有這么多需求等待我們?nèi)プ觥?span lang="EN-US">
如何確定數(shù)據(jù)庫是瓶頸?
1 如果程序設(shè)計(jì)良好，有一個(gè)數(shù)據(jù)庫操作邏輯層，可以從這個(gè)層的統(tǒng)計(jì)數(shù)據(jù)看到每個(gè)請(qǐng)求花費(fèi)的時(shí)間，如果平均時(shí)間已經(jīng)不能讓你容忍的話，數(shù)據(jù)庫已經(jīng)是瓶頸了。
2 在數(shù)據(jù)庫的服務(wù)器上使用top命令，看看mysql服務(wù)器占用資源的情況，看看機(jī)子的平均負(fù)載。
如果服務(wù)器的平均負(fù)載已經(jīng)很高，mysql占用了塊100%的cpu資源，說明mysql服務(wù)器很忙了。
3 在數(shù)據(jù)庫服務(wù)器上使用iostat命令，看看磁盤IO，如果block住的操作比較多的話，說明數(shù)據(jù)庫操作還是過于頻繁了，磁盤都響應(yīng)不急了。
4 建議打開mysql的慢查詢?nèi)罩荆@樣grep select看一下日志中的慢查詢的數(shù)量，如果數(shù)量較多，說明慢查詢的數(shù)量很多，需要進(jìn)行調(diào)整了。
5 如果有一天數(shù)據(jù)庫無法插入了，需要檢查一下數(shù)據(jù)庫表是不是過大了。32位的操作系統(tǒng)上一個(gè)表最大的容量是2^32這么大。不過還是建議增加一個(gè)數(shù)據(jù)庫操作的邏輯層，在數(shù)據(jù)庫操作的前后記錄下操作的時(shí)間，進(jìn)行統(tǒng)計(jì)上報(bào)，利用監(jiān)控程序來報(bào)警相關(guān)負(fù)責(zé)人，這樣可以及早的知道數(shù)據(jù)庫是瓶頸，提前做出優(yōu)化。
知道數(shù)據(jù)庫是瓶頸了，如何來進(jìn)行優(yōu)化呢?
1 我們第一個(gè)想到是看看數(shù)據(jù)庫的容量是不是太大了，如果數(shù)據(jù)庫表太大的話，索引文件也會(huì)比較大，每次的更新操作就會(huì)更加的費(fèi)時(shí)。需要考慮進(jìn)行分庫和分表了。
分庫分表按照一定的規(guī)則來對(duì)數(shù)據(jù)庫中的記錄進(jìn)行分區(qū)來存儲(chǔ)，一方面可以做到一定的負(fù)載均衡，將請(qǐng)求平分下來，每個(gè)區(qū)段去獨(dú)自承受;另一方面，分庫分表可以使我們存儲(chǔ)和操作更多的數(shù)據(jù)。
不過分庫分表需要多之前基于單庫的程序進(jìn)行修改，存在一定的風(fēng)險(xiǎn)，因此，在程序設(shè)計(jì)之初就應(yīng)該考慮到分庫分表的需要，最好是將數(shù)據(jù)庫操作層獨(dú)立出來，便于擴(kuò)展和更改。
2 如果數(shù)據(jù)庫表不是很大，但是查詢慢的話，我們需要檢查一下我們的sql查詢語句，利用mysql的explain語句看看是不是使用了索引，如果沒有使用索引，那我們需要在相應(yīng)的字段上建上索引，反復(fù)的使用explain，尋找到個(gè)一個(gè)合適的索引。
在建索引時(shí)需要考慮：
1)數(shù)據(jù)庫的索引要做到越少越好。
因?yàn)槊看胃露夹枰滤饕饕^多就會(huì)降低寫入的速度。
2)最窄的字段放在鍵的左邊。
這樣提高了索引中每一個(gè)點(diǎn)的基數(shù)，帶來更好的索引讀寫性能。
3)盡量避免file sort排序、臨時(shí)表和表掃描。
對(duì)于大表，全表掃描會(huì)導(dǎo)致大量的磁盤IO的操作，會(huì)導(dǎo)致操作非常的緩慢。
4)對(duì)于大表，盡量不要將索引建在字符串類型的列上，字符串的匹配是很費(fèi)時(shí)的，需要付出很高的性能代價(jià)，如果一定有必要，建議對(duì)字符串列進(jìn)行hash后取一個(gè)整形的值來進(jìn)行索引。
3 如果更新操作有點(diǎn)慢，而讀操作的響應(yīng)要求不需要很及時(shí)的話，可以考慮利用mysql的主從熱備來分擔(dān)讀寫的壓力。
畢竟對(duì)數(shù)據(jù)庫的操作，寫少讀多。因此，我們將對(duì)數(shù)據(jù)庫的寫操作放到mysql的主服務(wù)器上，利用mysql的熱備，我們?cè)趥浞莸臄?shù)據(jù)庫服務(wù)器上進(jìn)行讀操作，由于可以有多個(gè)熱備mysql，于是可以將讀操作分布在多個(gè)熱備上面，從而將讀操作均衡開來，提高讀操作的性能。
4 緩存的使用
緩存是一切后臺(tái)程序的根本，因?yàn)?span lang="EN-US">80%的請(qǐng)求是對(duì)應(yīng)20%的數(shù)據(jù)，我們只需要少量的內(nèi)存將20%的數(shù)據(jù)緩存起來，就可以大大的滿足我們系統(tǒng)需求，何樂而不為呢。
1)mysql設(shè)置中盡量增加key cache，thread cache、查詢的cache。
2)在應(yīng)用程序?qū)釉黾右粋€(gè)memcached這樣的通用cache。
3)對(duì)于少量數(shù)據(jù)，但是操作頻繁的表使用mysql提供的內(nèi)存heap表，可以獲得極高的寫入和讀取速度。
5 數(shù)據(jù)庫的設(shè)計(jì)上進(jìn)行優(yōu)化
對(duì)于傳統(tǒng)的數(shù)據(jù)庫設(shè)計(jì)我們講究建模范式，避免數(shù)據(jù)的冗余從而導(dǎo)致臟數(shù)據(jù)。然而在我們實(shí)際的應(yīng)用中需要根據(jù)情況來使用第三范式的一些規(guī)則，對(duì)于一些頻繁需要在多個(gè)地方出現(xiàn)的數(shù)據(jù)，如同一個(gè)論壇這種用戶和主題以及回復(fù)等有關(guān)聯(lián)的應(yīng)用中，如果我們將用戶同主題和回復(fù)分開來存儲(chǔ)，每次查詢一下一篇文章或者一個(gè)回復(fù)的情況都需要對(duì)用戶表和主題表或者回復(fù)表進(jìn)行聯(lián)查，如果數(shù)據(jù)量小的話，這樣聯(lián)查的性能還是可以接受的，如果表大一點(diǎn)，上了3、4十萬以上的數(shù)據(jù)，聯(lián)查的速度就會(huì)比較慢了。
該范式化的地方需要進(jìn)行范式化，但是還是需要根據(jù)情況來設(shè)計(jì)我們的表，從而達(dá)到性能和良好設(shè)計(jì)的折中。
其它的話：
1 對(duì)于數(shù)據(jù)庫的操作建議分層處理，至少分為兩層，一層是數(shù)據(jù)庫操作的邏輯層，一層是數(shù)據(jù)庫的cache層。
從一開始就考慮如此，可以很方便在未來對(duì)數(shù)據(jù)庫進(jìn)行劃分部署、分庫分表擴(kuò)展。
2 增加mysql的監(jiān)控，監(jiān)控mysql的慢查詢?nèi)罩荆O(jiān)控mysql的請(qǐng)求情況。
3 根據(jù)自己的需要來選擇mysql的存儲(chǔ)引擎。
myisam有較高的讀寫速度，但是由于表鎖定，不能同時(shí)進(jìn)行快速的讀和寫。
innodb支持事務(wù)，提供了行級(jí)的鎖，但是為了使用事務(wù)，表空間會(huì)比較大，而且不支持全文索引。
heap將表放到內(nèi)存中，適合與表小而需要頻繁操作的情況，如用戶信息，其讀寫很快，但是不是持久的，需要自己來寫工具讓其持久。
4 mysql服務(wù)器的一些狀態(tài)檢測(cè)的命令。
show slave status：可以看到主從同步的情況。
show [full] processlist：可以看到mysql服務(wù)器的請(qǐng)求情況，如果發(fā)現(xiàn)lock情況很多，需要注意了。
show status：可以看到mysql服務(wù)器的各種請(qǐng)求情況。
我的小站 http://www.qin3.com
詳細(xì)介紹優(yōu)化mysql性能的十個(gè)參數(shù),數(shù)據(jù)庫教程,數(shù)據(jù)庫設(shè)計(jì)1)、back_log：
要求 MySQL 能有的連接數(shù)量。當(dāng)主要MySQL線程在一個(gè)很短時(shí)間內(nèi)得到非常多的連接請(qǐng)求，這就起作用，然后主線程花些時(shí)間(盡管很短)檢查連接并且啟動(dòng)一個(gè)新線程。
back_log 值指出在MySQL暫時(shí)停止回答新請(qǐng)求之前的短時(shí)間內(nèi)多少個(gè)請(qǐng)求可以被存在堆棧中。只有如果期望在一個(gè)短時(shí)間內(nèi)有很多連接，你需要增加它，換句話說，這值對(duì)到來的TCP/IP連接的偵聽隊(duì)列的大小。你的操作系統(tǒng)在這個(gè)隊(duì)列大小上有它自己的限制。試圖設(shè)定back_log高于你的操作系統(tǒng)的限制將是無效的。
當(dāng)你觀察你的主機(jī)進(jìn)程列表，發(fā)現(xiàn)大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進(jìn)程時(shí)，就要加大 back_log 的值了。默認(rèn)數(shù)值是50，我把它改為500。
(2)、interactive_timeout：
服務(wù)器在關(guān)閉它前在一個(gè)交互連接上等待行動(dòng)的秒數(shù)。一個(gè)交互的客戶被定義為對(duì) mysql_real_connect()使用 CLIENT_INTERACTIVE 選項(xiàng)的客戶。默認(rèn)數(shù)值是28800，我把它改為7200。
(3)、key_buffer_size：
索引塊是緩沖的并且被所有的線程共享。key_buffer_size是用于索引塊的緩沖區(qū)大小，增加它可得到更好處理的索引(對(duì)所有讀和多重寫)，到你能負(fù)擔(dān)得起那樣多。如果你使它太大，系統(tǒng)將開始換頁并且真的變慢了。默認(rèn)數(shù)值是8388600(8M)，我的MySQL主機(jī)有2GB內(nèi)存，所以我把它改為 402649088(400MB)。
(4)、max_connections：
允許的同時(shí)客戶的數(shù)量。增加該值增加 mysqld 要求的文件描述符的數(shù)量。這個(gè)數(shù)字應(yīng)該增加，否則，你將經(jīng)常看到 Too many connections 錯(cuò)誤。 默認(rèn)數(shù)值是100，我把它改為1024 。
(5)、record_buffer：
每個(gè)進(jìn)行一個(gè)順序掃描的線程為其掃描的每張表分配這個(gè)大小的一個(gè)緩沖區(qū)。如果你做很多順序掃描，你可能想要增加該值。默認(rèn)數(shù)值是131072(128K)，我把它改為16773120 (16M)
(6)、sort_buffer：
每個(gè)需要進(jìn)行排序的線程分配該大小的一個(gè)緩沖區(qū)。增加這值加速ORDER BY或GROUP BY操作。默認(rèn)數(shù)值是2097144(2M)，我把它改為 16777208 (16M)。
(7)、table_cache：
為所有線程打開表的數(shù)量。增加該值能增加mysqld要求的文件描述符的數(shù)量。MySQL對(duì)每個(gè)唯一打開的表需要2個(gè)文件描述符。默認(rèn)數(shù)值是64，我把它改為512。
(8)、thread_cache_size：
可以復(fù)用的保存在中的線程的數(shù)量。如果有，新的線程從緩存中取得，當(dāng)斷開連接的時(shí)候如果有空間，客戶的線置在緩存中。如果有很多新的線程，為了提高性能可以這個(gè)變量值。通過比較 Connections 和 Threads_created 狀態(tài)的變量，可以看到這個(gè)變量的作用。我把它設(shè)置為 80。
(9)mysql的搜索功能
用mysql進(jìn)行搜索，目的是能不分大小寫，又能用中文進(jìn)行搜索
只需起動(dòng)mysqld時(shí)指定 --default-character-set=gb2312
(10)、wait_timeout：
服務(wù)器在關(guān)閉它之前在一個(gè)連接上等待行動(dòng)的秒數(shù)。 默認(rèn)數(shù)值是28800，我把它改為7200。
注：參數(shù)的調(diào)整可以通過修改 /etc/my.cnf 文件并重啟 MySQL 實(shí)現(xiàn)。這是一個(gè)比較謹(jǐn)慎的工作，上面的結(jié)果也僅僅是我的一些看法，你可以根據(jù)你自己主機(jī)的硬件情況（特別是內(nèi)存大小）進(jìn)一步修改。
文章來自: 好喜愛學(xué)習(xí)網(wǎng)(www.haoxiai.net) 網(wǎng)址：http://www.haoxiai.net/wangzhanzhizuo/shujuku/59492.html