資料庫索引效能測試

測試環境
    中央處理器：Pentium III 800
    實體記憶體：768 MB
    作業系統：Fedora Core 4
    資料庫系統：MySQL 5, PostgreSQL 8.0
    資料庫位址：localhost
    程式語言：JSP
    網站伺服器：Apache Tomcat + JDBC Drivers(MySQL, PostgreSQL)
背景資料
MySQL
    Storage Engine    Allowable Index Types
    MyISAM        BTREE
    InnoDB        BTREE
    MEMORY/HEAP    HASH, BTREE
Example:
CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);
PostgreSQL
Example
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_name ] ( column [ ops_name ] [, ...] )
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )
        acc_name
The name of the access method to be used for the index. The default access method is BTREE. Postgres provides three access methods for indexes:
    BTREE - an implementation of Lehman-Yao high-concurrency btrees.
    RTREE - implements standard rtrees using Guttman's quadratic split algorithm.
    HASH - an implementation of Litwin's linear hashing
說明
系統以預先建立好之資料表進行Insert單一欄位資料新增測試，測試前均先將資料表內容清空並移除索引，測試中欲進行加入索引後之測試時同樣清空原先Insert的資料再由程式中直接新增索引，所使用的索引型態均為預設值（BTREE），此步驟均不列入測試時間，所得結果如下：


測試結果

MySQL效能測試：索引欄位 INDEX
伺服器時間：2005 Nov 20, 01:25:33 AM
插入資料筆數：100000
未使用索引欄位
開始時間：2005 Nov 20, 01:18:33.993 AM
結束時間：2005 Nov 20, 01:19:27.249 AM
使用時間(秒)：53.256
________________________________________
已使用索引欄位
開始時間：2005 Nov 20, 01:19:27.251 AM
結束時間：2005 Nov 20, 01:20:40.306 AM
使用時間(秒)：73.055

MySQL效能測試：全文檢索欄位 FULLTEXT INDEX
伺服器時間：2005 Nov 20, 01:24:46 AM
插入資料筆數：100000
未使用全文檢索欄位
開始時間：2005 Nov 20, 01:20:50.762 AM
結束時間：2005 Nov 20, 01:21:45.025 AM
使用時間(秒)：54.263
________________________________________
已使用全文檢索欄位
開始時間：2005 Nov 20, 01:21:45.028 AM
結束時間：2005 Nov 20, 01:23:09.518 AM
使用時間(秒)：84.490

PostgreSQL效能測試：索引欄位 INDEX
伺服器時間：2005 Nov 22, 05:05:36 AM
插入資料筆數：100000
未使用索引欄位
開始時間：2005 Nov 22, 04:33:28.300 AM
結束時間：2005 Nov 22, 04:36:45.690 AM
使用時間(秒)：197.390
________________________________________已使用索引欄位
開始時間：2005 Nov 22, 04:36:45.692 AM
結束時間：2005 Nov 22, 04:40:15.113 AM
使用時間(秒)：209.421


PostgreSQL無 CREATE FULLTEXT INDEX ，故在此不做此測試，但有一個奇妙的發現！

PostgreSQL效能測試：索引欄位 INDEX
伺服器時間：2005 Nov 22, 05:20:35 AM
插入資料筆數：1000
未使用索引欄位
開始時間：2005 Nov 22, 05:20:22.294 AM
結束時間：2005 Nov 22, 05:20:28.819 AM
使用時間(秒)：6.525
________________________________________
已使用索引欄位
開始時間：2005 Nov 22, 05:20:28.820 AM
結束時間：2005 Nov 22, 05:20:30.970 AM
使用時間(秒)：2.150

在Insert筆數較少的情況下，未使用索引所執行的時間竟然比已用索引欄位後的執行時間還長，且高達三倍之多。

結論
在測試過程中，執行測試檔時順便進入終端機介面檢視MySQL資料庫實體檔案的變化，發現使用索引在新增資料的同時，*.MYI的檔案比原本*.MYD檔案容量大了一倍，因此發現使用索引時所使用檔案容量將倍增兩倍以上，因此在資料表的設計上，必須注意索引檔的使用，避免無謂的索引浪費時間與空間。在速度方面，MySQL真的很快，加入索引檔後執行所多出來的時間也不到原來的 1/2 ，隨著MySQL版本的更新，在本次測試中的版本為 5.x版中也加入了 View, Trigger與Store Procdure的重大更新，證明了MySQL在速度上的優勢不會因其版本更新與功能的加強而衰退。
相對於MySQL，PostgreSQL就慢了許多，不過Client端程式與Database連接需透過xDBC驅動程式，而驅動程式的良窳也間接決定了速度與穩定度，在本次測試中僅使用一種JDBC Driver，因此單以此結果與MySQL相比並不適當，希望日後有更多的測試數據可讓我們在做資料庫選擇決策時可供參考。


完整測試說明檔

引用通告地址: 點擊獲取引用地址