mysql schema数据混乱_高性能MySQL之:Schema与数据类型优化

一、选择优化的数据结构类型

简单的原则1、更小的通常更好

2、简单就好

3、尽量避免null

整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

整数类型有可选的unsigned属性,表示不允许负数

实数类型FLOAT、DOUBLE、DECIMAL

备注:尽量只在对小数进行精确计算时才是用DECIMAL类型.可以使用BIGINT来代替DECIMAL,在应用层换算单位(比如将元转换为分)

字符串类型CHAR & VARCHAR

char是定长存储,varchar是变长存储方式.

char适合存储定长的字符串,例如MD5摘要串、UUID等.

备注:varchar通常需要一个或两个额外字节存储字符串的长度

备注:字符串长度定义的是字符数不是字节数.BLOB & TEXT类型

BLOB和TEXT是为存储很大的数据而设计的字符串类型,分别采用二进制和字符存储方式存储.

如果对BLOB或TEXT进行排序,最好使用order by substring(cloumn,length)

备注:通常InnoDB存储较大的BLOB或TEXT时,会使用额外的存储区域来存储,在行内仅仅只存储指向这块区域的指针.

日期和时间类型DATETIME&TIMESTAMP

DATETIME存储的范围是1001到9999年,TIMESTAMP存储的范围时间是1970年到2038年

更新记录时自动更新update_time

选择合适的标识符为标识列选择合适的数据类型非常重要,通常整数类型(自增)是最优选择

对开发者隐藏复杂性的应用通常不能很好的扩展.

二、MySQL schema设计中的陷阱太多的列

太多的关联

滥用枚举

非此发明的NULL

三、范式与反范式

三大范式范式化的数据库中,每个事实数据会出现并且只出现一次.而反范式的数据库中,信息是冗余的,可能会存储在多个地方.

范式的优点与缺点1、范式化的更新操作通常比反范式化要快

2、当数据较好的范式化时,就会有很少或没有重复数据,所以只需要修改更少的数据

3、范式化的表通常更小,可以更好的放在内存里,所以执行操作会更快

范式化设计的schema的缺点是通常需要关联查询.

反范式的有点与缺点1、反范式化的schema由于所有数据都在一张表中,可以很好的避免关联.

缺点就是范式化的优点将不复存在

最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同的特定列.

实际开发中不会极端的只是用其中的一种,通常是混合使用.

四、缓存表和汇总表有些场景需要频繁查询汇总操作如果每次都是在元数据表中查询汇总,那么代价就较高.可以考虑单独独立一张汇总表来记录这些数据.

1、用户评论次数

2、商品销售量

当然是用NoSQL数据库来解决上述场景也是不错的选择

五、加快ALTER TABLE操作的速度MySQL的alter table操作的性能对达标来说通常是个大问题,因为需要锁表来完成.

如何可靠的完成修改线上大表结构？

总结1、避免过度设计

2、使用小而简单合适数据类型,尽量避免使用null

3、尽量使用相同的数据类型存储相似或相关的值,尤其是在关联条件中使用的列

4、注意可变长字符串,其在临时表和排序中可能会导致悲观的按最大长度分配内存

5、尽量使用整数定义标识列

6、小心使用enum和set

范式是好的,但是反范式有时也是必需的