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