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