4.1 选择优化的数据类型
小 -----> 更小的通常更好
简------> 简单就好(整型比字符型简单)
非null------> 设计时尽量避免null(除非真的需要才存储null),但是调优时没有必要null变成非null,性能提升非常小
列选择数据类型步骤
第一步:确定合适的大类型 数字/字符串/时间
第二部:选择具体的类型
数据类型 | 存储空间(字节) | 存储范围 | 特性 | 整体特性 | ||
datetime | 8 | 1001年---9999年 | (YYYYMMDDHHMMSS)与时区无关 | from_inixtime()/ unix_timestamp() timestamp比datetime效率高 | ||
timestamp | 4 | 1970---2038 | 19700101午夜(unix时间戳相同)以来的秒数 依赖于时区 默认not null,如果在插入时没有指定第一个timestamp列的值,mysql则设置这个列的值为当前时间 | |||
number | whole number | tinyint | 8 | -2(n-1)到2(n-1)-1 | 有可选unsigned,表示不允许负值,大致可以使正数的上限提高一倍 | |
smallint | 16 | |||||
mediumint | 24 | |||||
int | 32 | |||||
bigint | 64 | 存储财务数据时,可以使用bigint缩小倍数来代替decimal/存储时间戳 | ||||
real number | float | 4 | ||||
double | 8 | mysql使用doudble作为内部浮点计算的类型 | ||||
decimal | 最多65个数字 | |||||
字符串 | varchar | 变长字符串,当长度>255时,用2个额外字节来记录长度;当长度<255时,用1个额外字节来记录长度; 适合存储那些字符串的最大长度比平均长度大很多;列的更新很少,即碎片不是问题的情况 | ||||
char | 定长字符串,会剔除末尾空格 适合存储很短的字符串或所有值都接近一个长度 | |||||
2进制字符串 | binary | |||||
varbinary | ||||||
大字符串 | 2进制 | blob | 独立的对象,当值太大时,InnoDB会使用专门的外部存储区域做特殊处理,此时每个值在行内仅需要1-4个字节来存储一个指针 | |||
字符串 | text | |||||
特殊数据类型 | ip地址 | 无符号数存储 | inet_aton()/inet_ntoa()转换 | |||
4.2 mysqlschema设计中的陷阱
1 列多
2 关联多(单个查询最好在12个表以内做关联)
3 全能的枚举
4 变相的枚举
4.3 范式/反范式
范式中减少distinct和group by的操作
反范式中减少关联表的次数,避免随机i/o
混用(道的境界)
4.4 缓存和汇总表
4.5 加快 alter table操作的速度
常规方法:用新的结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表
出现的问题:花费时间长,如果内存不足而表很大,而且还有很多索引的情况下尤其如此
一般而言大部分的alter table操作都将导致mysql服务中断。对常见的场景能使用的场景只有2种:
a 先在一台不提供服务的机器上执行alter table操作,然后和提供服务的主库进行切换
b “影子拷贝”
4.5.1 知修改 .frm文件
4.5.2 快速创建MyISAM索引