MySQL数据库设计优化.pptxVIP

MySQL数据库设计、优化

叶金荣

微博：@yejinrong

微信公众号：MySQL中文网

QQ群:125572178、272675472

2013.08.20

提纲

规范

基础规范

命名规范

库表规范

字段规范

索引规范

开发环境

优化

2

可整理ppt

规范

基础规范

全部使用InnoDB引擎，MyISAM适用场景非常少

字符集：latin1=utf8=gbk

用数据库来持久化存储以及保证事务一致性，不是运算器

读写分离，主库只写和少量实时读取请求

采用队列方式合并多次写请求，持续写入，避免瞬间压力

超长text/blob进行垂直拆分，并先行压缩

冷热数据进行水平拆分，LRU原则

快速更新大数据表禁止直接运行count(*)统计

3

可整理ppt

规范

基础规范

单表行记录数控制在1000万以内，行平均长度控制在16KB以内，单表20GB以内

单实例下数据表数量不超过2000个，单库下数据表数量不超过500个

禁止开发环境直连线上生产环境

最少授权，只授予最基础权限需求

压力分散，在线表和归档表（日志表）分开存储

线上数据库和测试数据库尽可能保持一致

禁止明文存储机密数据，需至少两次加密（部分数据可逆运算）

4

可整理ppt

规范

命名规范

涉及系统目录、文件、数据库、表、字段名

强烈建议只用小写字符、数字、下划线组合

命名长度不超过32个字符

不使用select、show、update等保留字

全英文或全中文，言之有意，不要半洋半中

临时用加上tmp/temp前缀/后缀

统计表加上stat/statistic前缀/后缀

历史归档加上完整日期，例如mkdir-p/backup/user_log/2013/08

createtableuser_detail

createtablexxx_1234

createtableaccess_log5

可整理ppt

规范

库表规范

少用分区表等未完善的新特性

不对InnoDB引擎表做在线实时count(*)统计

分库、分表策略

以用户ID=123456为例，取N/100%10=4，取N%10=6

最大10个分库，10个分表，共100个分表

则分配到DB_04库下，分表TABLE_06中

采用预存映射关系动态分配更灵活，不受分表算法变化而影响，但数据库开销大

6

可整理ppt

规范

字段规范

用timestamp（4字节intunsigned，且效率非常高）记录时间，而非使用date/datetime/char/varchar

IPV4地址采用4字节intunsigned，内置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

0Int

-2147483648~2147483647

0~4294967295

Bigint

-9223372036854775808~9223372036854775807

0~18446744073709551615

7

可整理ppt

规范

字段规范

char(10)VSvarchar(10)

尽可能不使用text/blob类型

存储字符型数据时，尽可能先压缩或者序列化

注意字符集问题，server=database(trigger、storedprocedure、eventscheduler)=table=column

不要同时指定字符集（characterset）和校验集（collectset），避免出现和默认对应关系不一致

Value

CHAR(4)

StorageRequired

VARCHAR(4)

StorageRequired

4bytes

1byte

ab

ab

4bytes

ab

3bytes

abcd

abcd

4bytes

abcd

5bytes

abcdefgh

abcd

4bytes

abcd

5bytes

8

可整理ppt

规范

字段规范

显式指定自增int/bigintunsignednotnull作为主键

杜绝使用UUID/HASH/MD5类型作为主键

无须预留，越短越好，此处无须18cmO(∩_∩)O哈哈~

MySQL5.5以上，OnlineDDL越来越方便

显式约束：NOTNULL

9

可整理ppt

规范

SQL规范

简化每一条SQL