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