1. key_buffer_size ->
係與索引塊的緩衝區大小息息相關，故將之設定為較大後，對於來自於所有用戶執行緒的讀寫處理能力，自然有所助益，但一旦設定的太大，反而會拖累系統的執行效能。
系統的默認值為8MB，可抓系統主機記憶體容量的1/5（註：假設伺服器具2GB記憶體，則以400MB為基準)。
2. back_log ->
該數值的主要意義，指MySQL暫時停止回應新請求之前的短暫時間內可允許多少個請求，被存放於堆疊之中。
期望在短暫時間內能夠連結到較多的請求數量，或在主機進程列表當中發現到大量的等待連接進程時，則可把back_log值予以增加。
但不能無限上綱地擴大，因為作業系統對於接收TCP/IP連接的listen queue大小有其上限。
系統所默認的back_log值為50，只要不把它調到10倍（亦即500）以上，通常不會有太大問題。
3. max_connections ->
若擔心系統所承受的同時上線使用人數過多，導致經常出現「Too many connections」錯誤的話，則可考量將max_connections數值從Default的100加以提高。
4. interactive_timeout ->
系統在面對1個交互連接行為上，所產生的等待時間，以秒為單位，而系統默認值為28800，此時不妨將之調低，改為14400或7200都可，旨在降低其於交互連接過程中的等待秒數。
5. sort_buffer ->
系統在面對所有需要被排序的執行緒，即可個別分配較大的緩衝區，從而提升ORDER BY或GROUP BY的行進速度。
系統默認值僅2MB，可考慮提高到8MB或16MB。
6. table_cache ->
代表所有執行緒在開啟表單時，所能支撐的檔描述符總量，而MySQL每開啟1個表，便需要搭配2個檔描述符。
系統默認值為64，可考慮調整為256或512。
7. record_buffer ->
倘若數值愈大，則每個刻正接受掃描的表單，便可分配到較大的緩衝區，從而加速順序掃描作業之運行。
而系統默認值是128KB，可將之大幅調高到8MB或16MB無妨。
二. 以JOIN取代Sub-Queries
不可諱言，透過子查詢方式，將可1次性地搞定許多邏輯上原需多步驟方能完成的SQL操作，且能一併脫離事務或表單的閉鎖效應，更難能可貴的是，撰寫的難度也不算高。
只不過，在多數情況下，用戶也不妨考慮採用效率更高的連接方式，藉以取代子查詢。系統管理員一旦運用連接後，將不難發現到，查詢速度將比以前快上許多。
三. 慎選欄位屬性
當使用者在建立表單時，基於追求高效能考量，一定得注意表單中欄位的寬度，必須儘可能把它設定得小一些。
有關整數型(INT)欄的定義，採用MEDIUMINT會比採用BIGINT來得好，而能夠以CHAR(6)定義的欄位，便犯不著將之設定為CHAR(255)甚或VARCHAR，因為後兩者都會佔據一些無謂的資料庫空間。
此外，當面對一些諸如「性別」、「縣市」等文本欄位元時，可以考慮將之定義為ENUM型態，此乃由於，在MySQL資料量的運作過程中，ENUM類型會被當做數值型資料來加以處理，而系統對於數值型資料的處理效率，絕對比處理文本類型資料的速度要來得快。
另一方面，使用者亦須培養1個觀念，亦即儘可能將欄位設定為NOT NULL，如此一來，資料庫系統在查詢作業的過程裡頭，便無須再就NULL值進行比較，執行速度也將之加快。
四. 考慮以UNION替代臨時表
自MySQL 4.0版系統開始，便支援聯合（UNION）查詢功能，亦即可將所有原本需要採用臨時表的2條或更多條的SELECT查詢，合併至單一查詢程式之中，而待用戶查詢會話結束後，臨時表將隨即被自動刪除，據此保持資料庫的整齊一致性，並有助效率的提升。
五. 採用事務語法
舉個簡單的例子，當用戶欲將1筆資料，同時插入2個相互關聯的表單時，萬一第1個表單中成功更新之後，資料庫系統出現突發性意外，導致第2個表單中的操作無法順利完成，則資料庫系統裡頭的資料，便將趨於不完整，甚至遭致破壞。
要想避免此類狀況產生，別無他法，只能採用事務語法，確保整個系列語句之中的每條語句，都能一齊「成功」或一齊「失敗」，如此一來，資料庫當中的資料，其一致性與完整性便無遭致破壞之虞。
事務語法係以BEGIN關鍵字來出發，並以COMMIT關鍵字作為結束，此行進的過程中，任1條SQL語句操作失敗的話，只消透過ROLLBACK命令，即可將資料庫回復至BEGIN開始之前的狀態。
BEGIN;
INSERT INTO ....
COMMIT;
六. 透過鎖定表 避免延遲衝擊
即便透過事務語法，堪稱維持資料庫完整性的理想途徑，然其畢竟具有「獨佔」之意涵，在有些情況下，特別是處於大型應用環境當中，便可能影響資料庫效能，
究其主因，在於事務語法執行的過程中，致使資料庫遭到鎖定，故對於其他用戶的請求，也僅能暫時等待到該事務結束才能進行。
倘若該資料庫系統僅少數使用者所採用，此一影響即不會成為大問題，但萬一有眾多使用者同時訪問資料庫的話，則延遲現象便將趨於嚴重。
為解決這個問題，系統管理員不妨採用鎖定表的方式，藉以獲致較佳效能，譬如下例，即是以此方式，來實現與前述（註：採用事務語法）的相同目的。
LOCK TABLE inventory WRITE
SELECT ....
UPDATE ....
UNLOCK TABLES
七. 藉由外鍵(FOREIGN KEY)做法 保持資料關聯性
一旦使用外鍵，便可確保customerinfo表中的 CustomerID，都能鏡射至salesinfo表中CustomerID，其間若有任1條缺乏合法CustomerID的記錄，絕對不會被插入或更新至salesinfo表單裡頭，如同下列步驟。
CREATE TABLE customerinfo
(
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID)
) TYPE=INNODB;
CREATE TABLE salesinfo
(
SalesID INT NOT NULL,
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID, SalesID),
FOREIGN KEY(CustomerID) REFERENCES customerinfo(CustomerID) ON DELETECASCADE
) TYPE=INNODB;
ON DELETE CASCADE參數的主要功能，係確保當customerinfo表單之中的任1條客戶記錄遭到刪除時，salesinfo表單裡頭所有與該客戶相關聯的記錄，也將一併被自動刪除。
欲於MySQL系統中採用外鍵，系統管理者務必得留意，在建立表單時，應將表單類型定義為InnoDB。
八. 針對適當欄位元建立索引
事實上，索引絕對可謂增進資料庫效能所慣用之方式，特別是在查詢語句當中，若含括了MAX(), MIN()或ORDERBY等命令時，效能提升的效果尤其顯著。
多數情況下，索引應被建立在一些將被套用於JOIN, WHERE判斷、以及ORDER BY排序的欄位上，而儘量不要出現在資料庫當中某個內含大量重複數值的欄位，
尤其對於ENUM型態的欄位而言，會出現大量重複值，在這般欄位元上建立索引，不僅不會產生任何助益，反倒可能降低資料庫效能。
對於大型資料庫而言，把資料裝載至缺乏FULLTEXT索引的表單中，接著透過ALTER TABLE或CREATE INDEX來建立索引，其實速度會非常快。
九. SQL語句優化
> 語句中沒有使用函數的速度會比有函數的快。
> like 語句使用就查詢方式而論，將會就表單當中的每1條記錄逐一比較，執行速度並不理想。
SELECT * FROM TEST_TABLE WHERE name LIKE "Database%"
但透過下列全不採用LIKE關鍵字或是通配符號的查詢方式，所得查詢結果亦無異於前例，但在執行速度方面，顯然就高明許多。
SELECT * FROM TEST_TABLE WHERE name>= "Database" and name < "Databasf"
> 只查詢你需要的欄位，而不是 SELECT *.
> 處理複雜的數據時，善用臨時資料表（temporary tables）.
> 越少聯合資料表查詢越好.
> 使用正確的數據類型, 不要把所有數據都加上引號, 數據庫會因為要把數據轉化為正確的類型而變慢.
> 循環查詢, 最常見到的問題。所謂循環查詢是指在迴圈中執行的查詢，這是非常消耗資源的做法，而且很多時候是絕對不需要的。
循環查詢的錯誤例子
if (isset($_GET['ids'])) {
foreach($_GET['ids'] as $id) {
$rs = mysql_query('SELECT * FROM my_table WHERE my_id = ' . (int) $id);
$row = mysql_fetch_assoc($rs);
print_r($row);
}
}
改正這個錯誤例子
if (isset($_GET['ids'])) {
$ids = array_map('intval', $_GET['ids']);
$ids = implode(',', $ids);
$rs = mysql_query('SELECT * FROM my_table WHERE my_id IN (' . $ids . ')');
while($row = mysql_fetch_assoc($rs)) {
print_r($row);
}
}
十. 資料庫設計
很多時候你會發現資料庫設計是問題的根源，一個差勁的資料模型可以使應用程式的效能和可維護性都深受影響，值得注意的是，一個越是為效能而優化的資料庫，可維護性也越低（這一點是有爭議的，它其實要視乎應用程式的大小和規模）。
建議大家閱讀「MySQL 的資料庫設計手冊」。http://dev.mysql.com/doc/refman/5.0/en/optimizing-database-structure.html
十一. 應用程式層面的 SQL 效能
應用程式層面的 SQL 效能與 SQL 查詢的效能屬於不同的課題，這是有關如何在設計上使 SQL 與應用程式互相配合讓工作做得更好
這方面的研究主要集中於如何降低查詢的次數，從而提高可擴展性和改善效能，不過，高的效能並不等於好的可擴展性，正如具有可擴展性並不等於有高的效能。
沒有留言:
張貼留言