Monty 談 MySQL 優化（七）：事務處理、REPLACE 用法、通用技巧與 3.23 新特性

三十一、事務處理範例

MyISAM 表鎖定模擬事務

LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer SET total_value=sum_from_previous_statement WHERE customer_id=some_id;
UNLOCK TABLES;

BDB 表原生事務

BEGIN WORK;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer SET total_value=sum_from_previous_statement WHERE customer_id=some_id;
COMMIT;

避開事務的單句替代方案

UPDATE customer SET value=value+new_value WHERE customer_id=some_id;

三十二、REPLACE 用法

REPLACE 功能近似 INSERT，差異：若唯一索引衝突，舊記錄在新記錄插入前被刪除。可替代「查詢→鎖表→刪除→插入→解鎖」的複雜流程：

REPLACE INTO t1 VALUES (...);

三十三、通用效能技巧

• 使用短主鍵，聯結表用數字而非字串
• 多部分鍵值第一部分應為最常用部分
• 適當增加重複列以獲得更好鍵值壓縮
• 同機器客戶端/服務端連線用 Socket 而非 TCP/IP（效能提升 7.5%），連線時不指定主機名或用 localhost
• 可能時用 --skip-locking（部分 OS 預設），關閉外部鎖定提升效能
• 應用層哈希值替代長鍵值：WHERE hash=MD5(CONCAT(col1,col2)) AND col1='const' AND col2='const'
• BLOB 檔案存檔案系統，資料庫只存檔名
• 刪除全表比刪除大部分行更快
• SQL 不夠快時，研究更底層的資料存取介面

三十四、MySQL 3.23 版本優勢

• MyISAM：可移植大表格式
• HEAP：記憶體表
• Berkeley DB：事務安全表
• 眾多限制提升
• 動態字符集
• 更多 STATUS 變數
• CHECK/REPAIR TABLE
• 更快 GROUP BY 和 DISTINCT
• LEFT JOIN ... IF NULL 優化
• CREATE TABLE ... SELECT
• CREATE TEMPORARY TABLE
• 臨時 HEAP 表自動轉 MyISAM
• 複製功能
• mysqlhotcopy 腳本

三十五、正在積極開發的重要功能

• 改進事務處理
• 失效安全的複製
• 全文搜尋
• 多表刪除（隨後多表更新）
• 更好的鍵值快取
• 原子 RENAME (RENAME TABLE foo AS foo_old, foo_new AS foo)
• 查詢快取
• MERGE TABLES
• 更好的 GUI 客戶端