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