选择优化的数据类型
选择数据类型主要有以下的几个原则:
- 选择更小的 他们通常更快且占用资源和空间更少
- 选择简单的 简单类型只需要更少的CPU周期,例如整形就比字符型要好
- 尽量避免NULL 如果不是真的需要,最好别用这个值,这个值的存在让MySQL的优化更难执行。
当可为NULL的列被索引时,每个索引需要额外的存储空间,在MyISAM里还可能使固定大小的索引变为可变大小。
下面来看看具体的类型:
整数类型
整数计算一般采用BIGINT(64位)
需要注意的是MySQL可以指定类型宽度,如int(11),但这只是在显示的时候给你看的,真正存储中int(1)和int(20)是一样的。
实数类型
实数是带有小数部分的数字。
Float 和Double类型支持使用标准的浮点运算进行计算
Decimal用于存储精确的小数,且支持精确计算(5.0后支持的,早期版本转为double进行运算)
浮点和Decimal都可以指定精度
浮点类型在存储时比Decimal使用更小的空间,MySQL用double进行内部浮点计算,所以我们能选择的仅仅是存储类型
建议在对小数进行精确计算时使用Decimal——比如存储财务数据,在数据量较大时,由于Decimal精确计算代价较高,可以选择用BIGINT存储,就乘以一个倍数就行了。
字符串类型
Varchar类型:
在字符串列的最大长度比平均长度大很多、列更新较少、使用复杂字符集(UTF-8)的情况下,适合使用varchar类型。
Char类型:
稍微注意一下,char类型会截断字符串尾部的空格但varchar不会
★分配类型长度时,用多少分配多少,别太慷慨
BLOB和TEXT类型:
他们都是为存储很大的数据类型而设计的字符串数据类型,分别采用二进制和字符方式存储
MySQL会把这俩当作一个独立的对象处理,当值太大时,InnoDB会专门使用外部存储区域来存储,此时每个值在行内要划出来1-4个字节存储一个指针,然后在外部存储区域存储真正的值。
BLOB存储二进制数据,没有排序规则或者1字符集,但TEXT有。
枚举类型
有时候可以使用枚举类型来代替常用的字符串,枚举可以把一些不重复的字符串存储成一个预定义的集合。
枚举是按内部存储的整数而不是字符串进行排序的
枚举最不好的地方是,字符串列表是固定的,添加或删除字符串必须使用alter table,这是开销很大的一个操作,除非只在末尾添加新元素,因为这样不用重建整张表。
日期和时间类型
例如year和date,MySQL能存储的最小时间粒度为秒,但也可以用微秒级的粒度进行运算。
Datetime:能保存大范围的值,从1001到9999年,精度为秒。并且把日期和时间封装在格式为YYYYMMDDHHMMSS的整数中,使用八个字节存储空间。与时区无关。
Timestamp:它保存了从1970年1月1日午夜以来的秒数,使用四个字节存储空间,因为范围比datetime小得多,他的显示依赖于时区。
在多个时区存储和访问数据,将有可能得到不一样的结果。
在插入数据时没有指定第一个timestamp的值,那默认为当前时间
Timestamp默认为not NULL
通常应该尽量使用timestamp,他比datetime空间效率更高。
位数据类型
BIT
Set
当需要保存很多true false值的时候,可以使用set数据类型,MySQL对于这个类型有很多函数可以用。
主要缺点是改变列的代价较高,需要alter table
为标识列选择合适的数据类型很重要
整数类型:通常是标识列最好的选择,因为可以自增。
ENUM和SET类型:
字符串类型:
也要尽量避免使用字符串类型作为标识列,他们不仅消耗空间,且速度慢。
完全随机的字符串会使插入和查询语句变得很慢。
MySQL表设计中的陷阱
- 列太多 :MySQL的存储引擎API工作时需要在服务器层和存储引擎之间用行缓冲格式拷贝数据,需要有列变行的过程,当列太多时,转换的开销将是巨大的
- 太多的关联:
- 枚举的不正确使用:
- 不正确使用set:在set中只存储了单个值(set支持多个值)
- NULL的问题:尽量避免使用NULL值,实在要用也可以用别的替代,但具体要不要用类似整数这样的的东西替代NULL,还要结合具体情况而言。
范式和反范式
我们在实际场景之中,应该追求高范式而减少冗余,还是适当的降低范式等级,活得更快的查询速度呢?这是个值得思考的问题。
就拿经典的雇员部门领导三个表来说,如果三个要素都在一个表里,那出现人事变动时,可能需要修改很多行,这是容易出现错误的(一张表)
但如果将一张表分为两张表,通过部门来连接,会变得更好一些,这样设计符合二范式。
范式化(高范式)的优点和缺点:
反范式化(低范式)的优点和缺点:
缺点也是所有东西都在一张表里,数据冗余非常大,插入删除不方便
那我们应该怎么样应用范式和反范式呢?
答案是混用,单纯的范式化或者反范式化都将带来一定的性能问题,在实际的场景下一定要结合问题来看。
缓存表和汇总表
有时为了提升性能,会在同一张表中保存衍生的数据,有时就需要创建一张单独的缓存表或者汇总表。
实时计算统计值是很昂贵的操作,所以此时汇总表显得很有必要。
物化视图:
物化视图可以不对原表进行操作,是一种效率比较高的操作。
计数器表
如果直接在表中保存计数器,维护会是一个难题,并发什么的会给数值准确性带来问题。
计数器表是一个很常见的东西,它专门用来存储计数器,这样计数器表小且快,使用独立的表还可以避免查询缓存失效。
加快alter table操作的速度
MySQL的alter table是一个很消耗性能的操作。MySQL执行大部分修改表的操作流程都是先创建一个新的空表,把旧表符合要求的数据整个复制过来,然后再删除旧表。这个过程中还要锁表。个人认为这也太离谱了。。。
下面介绍一些“非常规方法”,书上建议一般不要轻易使用。
只修改.frm文件:
通过修改.frm文件有时可以不用建新表,可以把这个文件简单理解为表的配置文件
快速创建MyISAM索引: