作为一名后端开发人员,了解数据库及设计是十分重要的,对于大数据量的表尤其如此,数据库设计的首要是对表结构及字段类型进行设计,下面从数据字段设计原则开始,对mysql不同数据类型进行讨论。

一般情况下,尽量使用可以正确存储数据的最小数据类型,更小的数据类型通常更快,因为它们占用更少的磁盘、内存和CPU缓存,处理时需要的CPU周期也更少。但是要确保没有低估需要存储的值的范围,因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作

简单的数据类型的操作通常需要更少的CPU周期。比如整型比字符操作代价更低,因为字符集和校对规则使字符串比整型比较更复杂(这在各种语言的设计中基本如此,Java的字符串比较就要比整型效率更低)。应该使用mysql内建的类型而不是字符串来存储日期和时间

在设计数据表时很可能包含null值,如果不指定列的not null属性,列的值就可能为null。null 列的出现使索引、索引统计器和值比较都更复杂。可为null的列会使用更多的存储空间。但并不是可为null的列改为not null并不会带来性能上明显的提升,因此对于已经可为null的列不必刻意去修改。但在设计表时,尽可能指定not null是一个较好的设计原则,除非null是一个必须表示的状态,否则可以用特殊的数据来表示,比如默认为空字符串表示nul。

上述是设计表时的基本原则,下面从不同数据类型的设计分别说明应该注意到的地方。

实数类型可以用 浮点(FLOAT,DOUBLE)及DECIMAL表示。FLOAT与DOUBLE用来表示不精确的小数,DECIMAL用来表示精确的小数。如果要在SQL语句中进行计算,原生的不精确浮点类型比精确的DECIMAL数据类型更高效。浮点和DECIMAL另一个区别是DECIMAL所需的存储空间更多,因此除非需要精确的小数表式,才考虑DECIMAL。如果需要精确表示的小数数据量很多,可以用BIGINT表代替DECIMAL,比如两位小数可以将数据乘100后存储在BIGINT中。(当然,大多数应用中不必如此)

字符串类型包括VARCHAR和CHAR,VARCHAR是变长字符串类型,CHAR则是定长字符串类型。通常VARCHAR比CHAR更佳,因为它仅需要必要的空间,越短的字符串,使用的空间越少(但当mysql表使用ROW_FORMAT=FIXED创建,每一行都会定长存储,会浪费空间)。VARCHAR在使用时指定长度是十分必要的,但VARCHAR的最大长度(指定长度)小于等于255字节,需要1个字节的额外空间记录长度,否则用2个字节。VARCHAR(10)需要11个字节的存储空间,VARCHAR(1000)则需要1002个字节。VARCHAR适合字符串列的最大长度比平均长度大很多的情况,因为不定长对于更新操作存在一定影响。

VARCHAR和CHAR另一个不同是,CHAR会将字符串尾部的空格去除,VARCHAR则不会。

对于只有特定几种类型的字符串,mysql提供枚举类型,枚举比字符串类型更紧凑也更加高效,但使用起来也有很多限制。枚举类型在设计时就必须指定所有枚举字段,比如

1CREATE TABLE enum_test(23e ENUM(‘fish’,’apple’,’dog’) NOT NULL45);67INSERT INTO enum_test(e) VALUES(‘fish’),(‘apple’),(‘dog’);  枚举可以根据上下文表示为不同的类型,比如

1SELECT e+0 FROM enum_test;

1SELECT e FROM enum_test;

mysql的时间数据类型包括DATETIME和TIMESTAMP。DATETIME的时间范围是1001到9999年,精度为秒。时间和日期被封装为YYYYMMDDHHMMSS的整数中,与时区无关,占用8个字节。TIMESTAMP保存从1970年1月1日午夜以来的秒数,只占用4个字节的存储空间,比DATETIME(用字符串表示则要用10几个字节)所需空间小,时间范围从1970到2038年。

占用空间越小,数据类型就越简单,因此能使用TIMESTAMP的场景尽量使用TIMESTAMP

根据业务的需要选择合适的数据类型对于高效SQL意义重大,能用数字类型的地方不要用字符串类型

除了特定需要精确数字类型的情况,浮点型比DECIMAL类型存储空间更小,计算也更加高效。

精确小数表示可以用DECIMAL数据类型,但如果数据量非常大的情况,将小数转换为整数(乘小数点位数的倍数即可)用BIGINT表示能节省空间,更高效

VARCHAR适合大多数字符串使用的场景,但如果确实都是定常的类型,比如只有一个字节,CHAR(1)的空间则比VARCHAR(1)所需空间更小,因为VARCHAR需要额外一个字节存储长度信息

整型类型指定长度信息不影响空间,字符串类型指定长度信息则影响存储空间

总的原则就是简单的类型比复杂的类型更加高效