MySQL數(shù)據(jù)庫(kù)設(shè)計(jì)規(guī)范和優(yōu)化建議

2024-07-24 12:32:18

字體：大中小

供稿：網(wǎng)友

設(shè)計(jì)規(guī)范建議:
1).索引規(guī)范
顯式指定自增 int/bigint unsigned not null 作為主鍵
不使用外鍵
合理利用覆蓋索引，但字段盡量不超過(guò)5個(gè)
合理利用最左索引（前綴索引/部分索引）
及時(shí)刪除冗余索引
選擇適當(dāng)?shù)乃饕樞?，選擇性高條件靠前
基數(shù)（ Cardinality ）很低的字段不創(chuàng)建索引（MySQL還不支持 bitmap 索引）
采用第三方系統(tǒng)實(shí)現(xiàn)text/blob全文檢索
常用排序（ORDER BY）、分組（GROUP BY）、取唯一（DISTINCT）字段上創(chuàng)建索引
單表索引數(shù)量不超過(guò)5個(gè)
索引字段條件不使用函數(shù)

2).開發(fā)環(huán)境
啟用log_queries_not_using_indexes
設(shè)置long_query_time為最小值
定期檢查分析slow log
授權(quán)和生產(chǎn)環(huán)境一致
關(guān)閉Query Cache
設(shè)置較小InnoDB Buffer Pool、key buffer size
數(shù)據(jù)量不能太少，否則有些性能問題無(wú)法提前規(guī)避

3).行為規(guī)范
批量導(dǎo)入、導(dǎo)出數(shù)據(jù)須提前通知DBA，請(qǐng)求協(xié)助觀察
推廣活動(dòng)或上線新功能須提前通知DBA，請(qǐng)求壓力評(píng)估
不使用SUPER權(quán)限連接數(shù)據(jù)庫(kù)
單表多次ALTER操作必須合并為一次操作
數(shù)據(jù)庫(kù)DDL及重要SQL及早提交DBA評(píng)審
重要業(yè)務(wù)庫(kù)須告知DBA重要等級(jí)、數(shù)據(jù)備份及時(shí)性要求
不在業(yè)務(wù)高峰期批量更新、查詢數(shù)據(jù)庫(kù)
提交線上DDL需求，所有SQL語(yǔ)句須有備注說(shuō)明

4).硬件
NUMA新架構(gòu)，CPU直接存取內(nèi)存，更高效
CPU一般不是瓶頸，但MySQL多核支持仍不佳
設(shè)備越來(lái)越廉價(jià)，大內(nèi)存解決很多問題
SSD應(yīng)用越來(lái)越廣泛，未來(lái)是主力
RAID卡可有效提升IOPS及數(shù)據(jù)安全（RAID 10 vs RAID 5）
RAID卡必須配備BBU，設(shè)置FORCE WB

優(yōu)化建議:
1).系統(tǒng)
升級(jí)到64位
/tmp使用/dev/shm的tmpfs
內(nèi)核
IO調(diào)度：deadline，noop，反正不要cfq
VM管理：vm.swappiness=0

2).文件系統(tǒng)：xfs/zfs
全B+樹，高效
分配組，提高并發(fā)度
延遲分配，減少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

（編輯：武林網(wǎng)）

上一篇：在數(shù)據(jù)庫(kù)繁忙時(shí)如何快速有效的關(guān)閉MySQL服務(wù)

下一篇：MySQL分庫(kù)分表