文档介绍：MySQL数据库设计、优化
叶金荣
微博： ***@yejinrong
微信公众号： MySQL中文网
QQ群: 125572178、272675472
提纲
规范
基础规范
命名规范
库表规范
字段规范
索引规范
开发环境
优化
2
整理课件
规范
基础规范
全部使用InnoDB引擎，MyISAM适用场景非常少
字符集：latin1 => utf8 => gbk
用数据库来持久化存储以及保证事务一致性，不是运算器
读写分离，主库只写和少量实时读取请求
采用队列方式合并多次写请求，持续写入，避免瞬间压力
超长text/blob进行垂直拆分，并先行压缩
冷热数据进行水平拆分，LRU原则
快速更新大数据表禁止直接运行count(*)统计
3
整理课件
规范
基础规范
单表行记录数控制在1000万以内，行平均长度控制在16KB以内，单表20GB以内
单实例下数据表数量不超过2000个，单库下数据表数量不超过500个
禁止开发环境直连线上生产环境
最少授权，只授予最基础权限需求
压力分散，在线表和归档表（日志表）分开存储
线上数据库和测试数据库尽可能保持一致
禁止明文存储机密数据，需至少两次加密（部分数据可逆运算）
4
整理课件
规范
命名规范
涉及系统目录、文件、数据库、表、字段名
强烈建议只用小写字符、数字、下划线组合
命名长度不超过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
5
整理课件
规范
库表规范
少用分区表等未完善的新特性
不对InnoDB引擎表做在线实时count(*)统计
分库、分表策略
以用户ID=123456为例，取N/100%10=4，取N%10=6
最大10个分库，10个分表，共100个分表
则分配到DB_04库下，分表TABLE_06中
采用预存映射关系动态分配更灵活，不受分表算法变化而影响，但数据库开销大
6
整理课件
规范
字段规范
用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
7
整理课件
规范
字段规范
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
8
整理课件
规范
字段规范
显式指定自增 int/bigint unsigned not null 作为主键
杜绝使用UUID/HASH/MD5类型