MySQL資料庫設計規範和優化建議

設計規範建議:
1).索引規範
顯式指定自增 int/bigint unsigned not null 作為主鍵
不使用外來鍵
合理利用覆蓋索引，但欄位儘量不超過5個
合理利用最左索引（字首索引/部分索引）
及時刪除冗餘索引
選擇適當的索引順序，選擇性高條件靠前
基數（ Cardinality ）很低的欄位不建立索引（MySQL還不支援 bitmap 索引）
採用第三方系統實現text/blob全文檢索
常用排序（ORDER BY）、分組（GROUP BY）、取唯一（DISTINCT）欄位上建立索引
單表索引數量不超過5個
索引欄位條件不使用函式


2).開發環境
啟用log_queries_not_using_indexes
設定long_query_time為最小值
定期檢查分析slow log
授權和生產環境一致
關閉Query Cache
設定較小InnoDB Buffer Pool、key buffer size
資料量不能太少，否則有些效能問題無法提前規避


3).行為規範
批量匯入、匯出資料須提前通知DBA，請求協助觀察
推廣活動或上線新功能須提前通知DBA，請求壓力評估
不使用SUPER許可權連線資料庫
單表多次ALTER操作必須合併為一次操作
資料庫DDL及重要SQL及早提交DBA評審
重要業務庫須告知DBA重要等級、資料備份及時性要求
不在業務高峰期批量更新、查詢資料庫
提交線上DDL需求，所有SQL語句須有備註說明


4).硬體
NUMA新架構，CPU直接存取記憶體，更高效
CPU一般不是瓶頸，但MySQL多核支援仍不佳
裝置越來越廉價，大記憶體解決很多問題
SSD應用越來越廣泛，未來是主力
RAID卡可有效提升IOPS及資料安全（RAID 10 vs RAID 5）
RAID卡必須配備BBU，設定FORCE WB


優化建議:
1).系統
升級到64位
/tmp使用/dev/shm的tmpfs
核心
IO排程：deadline，noop，反正不要cfq
VM管理：vm.swappiness=0


2).檔案系統：xfs/zfs
全B+樹，高效
分配組，提高併發度
延遲分配，減少IO
mount：nobarrier、data=ordered,writeback


3).MySQL配置
memlock
open_files_limit
max_connections
long_query_time
table_open_cache
key_buffer_size
query_cache_size
tmp_table_size/max_heap_table_size
innodb buffer pool
innodb_flush_log_at_trx_commit
interactive_timeout/wait_timeout
transaction_isolation
innodb_log_file_size
innodb_data_file_path
innodb_max_dirty_pages_pct

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15498/viewspace-2141669/，如需轉載，請註明出處，否則將追究法律責任。