选择优化的数据类型
1.更小的通常更好
一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为它们占用更小的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。
但是要确保没有低估需要存储的值的范围2.简单就好
简单数据类型的操作通常需要更少的CPU周期。例子:a.应该用msql内建的类型而不是字符串来存储日期和时间;b.应该用整形来存储ip地址。3.尽量避免NULL
很多表都包含可为NULL(空值)的列,即使应用程序并不需要保存NULL也是如此,这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。
原因:如果查询中包含可为NULL的列,对mysql来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在Mysql里也需要特殊处理。当可为NULL的列被索引事,每个索引记录需要一个额外的字节。
5.1.1 整数类型
整数
可以使用这几种整数类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGNIT。分别使用8,16,24,32,64位存储空间。可以存储的范围从-2^(n-1) 到 2^(n-1),n是存储空间位数。
整数类型有可选的UNSIGNED属性,表示不允许负值。这大致可以使正数的上限提高一倍。例如TINITY UNSIGNED 可以存储的范围是0 ~ 255,而TINITY的存储范围是 -128 ~ 127.
Mysql可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的;它不会限制值的合法范围,只是规定了mysql的一些交互工具(例如mysql命令行客户端)用来显示字符的个数。对于存储和计算来说。INT(11)和INT(20)是相同的
5.1.2 实数类型
实数
FLOAT和DOUBLE类型使用标准的浮点运算进行近似运算。
DECIMAL类型用于存储精确的小数,并支持精确计算。
因为CPU不支持对DECIMAL的直接计算,mysql服务器自身实现了DECIMAL的高精度计算。相对而言,CPU直接支持原生浮点计算,所以浮点计算明显更快。
浮点类型在存储同样的范围的值时,通常比DEMICAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节,相比FLOAT有更高的精度和更大的范围。和整数类型一样,能选择的只是存储类型,Mysql使用DOUBLE作为内部浮点计算的类型。
因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL--例如存储财政数据。但在数据量比较大的时候,可以考虑使用BIGNIT代替DECIMAL,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。这样可以避免浮点存储计算不精确和DECIMAL**精确计算代价高的问题。
5.1.3 字符串型
VARCHAR:
VARCHAR和CHAR是两中最主要的字符串类型。
VARCHAR类型用于存储可变长字符串,是最常用的字符串数据类型。它比定长类型更节省空间,因为它仅使用必要的空间(例如,越短的字符串使用越少的空间)。在一种情况例外,如果Mysql表使用ROW_FORMAT=FIXED创建的话,每一行都会使用定长存储,这会很浪费空间。
VARVHAR需要使用1或2个额外字节记录字符串的长度;如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列需要1002个字节,因为需要2个字节存储长度信息。
VARCHAR节省了存储空间,所以对性能也有帮助。但是,由于行是变长的,在UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作。如果一个行占用的空间增长,并且在页内没有更多的空间可以存储,在这种情况下,InnoDB需要分裂页来使行可以放进页内。
使用场景:字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题;使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字节数进行存储。
CHAR:
CHAR类型是定长的:Mysql总是根据定义的字符串长度分配足够的空间。当存储CHAR值时,Mysql会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。
使用场景:CHAR适合存储很短的字符串,或者所有值都接近用一个长度。例如,CHAR非常适合存储密码的MD5值,因为这是一个定长的值。对于经常变更的数据,CHAR也比VARCHAR更好,因为定长的CHAR类型不容易产生碎片。对于非常短的列,CHAR也比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值,如果采用单字节字符集只需要一个字节,但是VARCHAE(1)却需要两个字节,因为还有一个记录长度的额外字节。
5.1.4 文本类型
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。
实际上,它们分别属于两组不同的数据类型家族:字符类型是TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT;对应的二进制类型是TINYBLOB,SMALLBLOB,BLOB*
,MEDIUMBLOB,LONGBLOB.
5.1.5 日期和时间类型
DATETIME
这个类型能保存大范围的值,从1001年到9999年,精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。
TIMESTAMP
TIMETAMP类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数,它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间,因此它的范围比DATETINE小得多;只能表示从1970年到2038年。
Mysql schema设计中的陷阱
- 1.太多的列
Mysql的存储引擎API工作时需要在服务器层和存储引擎之间通过缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。 - 2.太多的关联
所谓的“实体-属性-值(EVA)”设计模式是一个常见的槽糕设计模式,尤其是在Mysql下不能靠谱地工作。一个粗略的经验法则,如果希望查询执行得快速且并发性好,单个查询最好在12个表以内做关联。
*3.变相的枚举
枚举(enum)列允许在列中存储一组定义值中的单个值,集合(SET)列则允许在列中存储一组定义值中的一个或多个值。有时候这可能比较容易导致混乱。这是一个例子:
CREATE TABLE ...(
is_default set('Y','N') NOT NULL default 'N'
)
如果这里真和假两种情况不会同时出现,那么毫无疑问应该可以使用枚举列代替集合列。
*4.非此发明(Not Inent Here)的NULL
应该避免使用NULL。即使需要存储一个事实上的“空值”到表中时,也不一定非得使用NULL。也许可以使用0、某个特殊值,或者空字符串作为代替。
总结
- 尽量避免过度设计,例如会导致极其复杂查询的schema设计,或者有很多列的表设计;
- 使用小而简单的合适数据类型,除非真实数据模型中有确切的需要,否则应该尽可能地避免使用NULL值;
- 尽量使用相同的数据类型存储相似或相关的值,尤其是要在关联条件中使用的列。
- 注意可变字符串,其在临时表和排序时可能导致悲观的按最大长度分配内存。
- 尽量使用整型定义标识列。
- 避免使用Mysql已经遗弃的特性,例如指定浮点数的精度,或者整型的显示宽度。
- 小心使用ENUM和SET。虽然它们用起来很方便,但是不要滥用,否则有时候会变成陷阱。最好避免使用BIT。
范式是好的,但是反范式(大多情况下意味着重复数据)有时也是必需的,并且能带来好处。
ALTER TABLE是让人痛苦的操作,大部分情况下会锁表并且会重建整张表。我们可以在备机执行ALTER并在完成后把它切换为主库。