• 1. MySQL数据库设计、优化叶金荣 微博: @yejinrong 微信公众号: MySQL中文网 QQ群: 125572178、272675472 2013.08.20
  • 2. 提纲规范 基础规范 命名规范 库表规范 字段规范 索引规范 开发环境 优化
  • 3. 规范基础规范 全部使用InnoDB引擎,MyISAM适用场景非常少 字符集:latin1 => utf8 => gbk 用数据库来持久化存储以及保证事务一致性,不是运算器 读写分离,主库只写和少量实时读取请求 采用队列方式合并多次写请求,持续写入,避免瞬间压力 超长text/blob进行垂直拆分,并先行压缩 冷热数据进行水平拆分,LRU原则 快速更新大数据表禁止直接运行count(*)统计
  • 4. 规范基础规范 单表行记录数控制在1000万以内,行平均长度控制在16KB以内,单表20GB以内 单实例下数据表数量不超过2000个,单库下数据表数量不超过500个 禁止开发环境直连线上生产环境 最少授权,只授予最基础权限需求 压力分散,在线表和归档表(日志表)分开存储 线上数据库和测试数据库尽可能保持一致 禁止明文存储机密数据,需至少两次加密(部分数据可逆运算)
  • 5. 规范命名规范 涉及系统目录、文件、数据库、表、字段名 强烈建议只用小写字符、数字、下划线组合 命名长度不超过32个字符 不使用select、show、update等保留字 全英文或全中文,言之有意,不要半洋半中 临时用加上 tmp/temp 前缀/后缀 统计表加上 stat/statistic 前缀/后缀 历史归档加上完整日期,例如:20130802 mkdir -p /backup/user_log/2013/08 create table user_detail create table xxx_1234 create table access_log_20130820
  • 6. 规范库表规范 少用分区表等未完善的新特性 不对InnoDB引擎表做在线实时count(*)统计 分库、分表策略 以用户ID=123456为例,取N/100%10=4,取N%10=6 最大10个分库,10个分表,共100个分表 则分配到DB_04库下,分表TABLE_06中 采用预存映射关系动态分配更灵活,不受分表算法变化而影响,但数据库开销大
  • 7. 规范字段规范 用timestamp(4字节int unsigned,且效率非常高)记录时间,而非使用date/datetime/char/varchar IPV4地址采用4字节int unsigned,内置INET_ATON/INET_NTOA快速转换,采用char至少15字节 性别、状态、是否、小范围枚举使用tinyint(0 ~ 255,或 -128 ~ 127)SignedUnsignedTinyint-128~1270~255Samllint-32768~327670~65535Mediumint-8388608~83886070~16777215Int-2147483648~21474836470~4294967295Bigint-9223372036854775808~92233720368547758070~18446744073709551615
  • 8. 规范字段规范 char(10) VS varchar(10) 尽可能不使用text/blob类型 存储字符型数据时,尽可能先压缩或者序列化 注意字符集问题,server=>database(trigger、stored procedure、event scheduler)=>table=>column 不要同时指定字符集(character set)和校验集(collect set),避免出现和默认对应关系不一致ValueCHAR(4)Storage RequiredVARCHAR(4)Storage Required'''    '4 bytes''1 byte'ab''ab  '4 bytes'ab'3 bytes'abcd''abcd'4 bytes'abcd'5 bytes'abcdefgh''abcd'4 bytes'abcd'5 bytes
  • 9. 规范字段规范 显式指定自增 int/bigint unsigned not null 作为主键 杜绝使用UUID/HASH/MD5类型作为主键 无须预留,越短越好,此处无须18cm O(∩_∩)O哈哈~ MySQL 5.5以上,Online DDL越来越方便 显式约束:NOT NULL
  • 10. 规范SQL规范 简化每一条SQL,短事务、快速执行、无阻塞 固定模式业务逻辑封装成存储过程 用括号显式确定AND、OR的先后顺序,避免混淆 注意引号问题会导致类型转换(where id = ‘1234’) 所有查询想尽一切办法使用索引:主键=>唯一索引=>索引 有些查询只需要扫描索引,无需扫描数据(SELECT id,user FROM table WHERE id = 1234)
  • 11. 规范SQL规范 过滤用户提交SQL,防止注入 杜绝 like ‘%xxx%’,不用/少用 like ‘xxx%’ 不用/少用子查询,改造成连接(JOIN) 不用/少用FOR UPDATE、LOCK IN SHARE MODE,防止锁范围扩大化 SQL中不用/少用函数,可能造成额外开销或者导致无法使用索引 分页SQL采用内连接(INNER JOIN)实现,更高效
  • 12. 规范索引用途 快速定位 避免排序 覆盖索引可直接返回结果,无需扫描数据 唯一索引可实现唯一约束
  • 13. 规范索引类型 B+ Tree Clustered index(InonDB vs MyISAM) Hash index
  • 14. 规范索引类型 B+ Tree
  • 15. 规范索引类型 Clustered index InnoDB vs MyISAM
  • 16. 规范索引类型 Hash index
  • 17. 规范索引规范 显式指定自增 int/bigint unsigned not null 作为主键 不使用外键 合理利用覆盖索引,但字段尽量不超过5个 合理利用最左索引(前缀索引/部分索引) 及时删除冗余索引 选择适当的索引顺序,选择性高条件靠前
  • 18. 规范索引规范 基数( Cardinality )很低的字段不创建索引(MySQL还不支持 bitmap 索引) 采用第三方系统实现text/blob全文检索 常用排序(ORDER BY)、分组(GROUP BY)、取唯一(DISTINCT)字段上创建索引 单表索引数量不超过5个 索引字段条件不使用函数
  • 19. 规范开发环境 启用log_queries_not_using_indexes 设置long_query_time为最小值 定期检查分析slow log 授权和生产环境一致 关闭Query Cache 设置较小InnoDB Buffer Pool、key buffer size 数据量不能太少,否则有些性能问题无法提前规避
  • 20. 规范行为规范 批量导入、导出数据须提前通知DBA,请求协助观察 推广活动或上线新功能须提前通知DBA,请求压力评估 不使用SUPER权限连接数据库 单表多次ALTER操作必须合并为一次操作 数据库DDL及重要SQL及早提交DBA评审 重要业务库须告知DBA重要等级、数据备份及时性要求 不在业务高峰期批量更新、查询数据库 提交线上DDL需求,所有SQL语句须有备注说明
  • 21. 优化硬件 NUMA新架构,CPU直接存取内存,更高效 CPU一般不是瓶颈,但MySQL多核支持仍不佳 设备越来越廉价,大内存解决很多问题 SSD应用越来越广泛,未来是主力 RAID卡可有效提升IOPS及数据安全(RAID 10 vs RAID 5) RAID卡必须配备BBU,设置FORCE WB FushionIO很NB,但还是贵族
  • 22. 优化系统 升级到64位 /tmp使用/dev/shm的tmpfs 内核 IO调度:deadline,noop,反正不要cfq VM管理:vm.swappiness=0 文件系统:xfs/zfs 全B+树,高效 分配组,提高并发度 延迟分配,减少IO mount:nobarrier、data=ordered,writeback
  • 23. 优化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
  • 24. 优化MySQL配置 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