那位先生

个人站

前世五百次回眸,才能换得今生的一次擦肩而过。


MySQL-02-Schema与数据类型优化

1.选择优化的数据类型

1.概述

选择的原则:
更小的通常更好:尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为他们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少,但是注意 要确保没有低估需要存储的值的范围。
简单就好:简单数据类型的操作通常需要更少的CPU周期。比如:整型比字符操作代价更低。
尽量避免NULL:通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。如果查询中包含有NULL的列,对MySQL来说更难优化,因为可为NULL的列使得做阴、索引统计和值都更难查询, 而且可为null的列会使用更多的存储空间。注意:把可为NULL改为NOT NULL带来的性能提升比较小,所以优化可以不优先考虑。但如果计划在列上建索引就要避免。
选择的流程:
第一步确定合适的大类型:数字、字符串、时间等。
第二步选择具体的类型:很多MySQL的数据类型可以存储相同类型的数据,只是存储的长度和范围不一样、允许的精度不同,或者需要的物理空间(磁盘和内存空间)不同。

2.整数类型

有两种类型的数字:整型和实数。如果存储整数可以使用这几种类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间。 他们可以存储的范围从-2的N-1次方到2的N-1次方-1,其中N是存储空间的位数。
整数类型有可选的UNSINGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。例如,TINYINT UNSIGNED 可以存储的范围是0-255,而TINYINT的存储范围是-128~127。 有符号和无符号类型使用相同的存储空间,并具有相同的性能。
MySQL可以为整数类型指定宽度,例如INT (11),对于大多数应用没什么意义:它不会限制值的合法范围。对于存储和计算来说,INT(11)和INT(20)是相同的。

3.实数类型

实数是带有小数部分的数字。然而,他们不只是为了存储小数部分;也可以使用DECIMAL存储比BIGINT还大的整数。FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数。
浮点和DECIMAL类型都可以指定精度。对于DECIMAL列,可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。DECIMAL(18,9)小数点两边将各存储9个数字,一共使用9个字节:小数点前的数字用4个字节, 小数点后的数使用4个字节,小数点本身占一个字节。
浮点类型在存储同样范围的值时,通常比DECIMAL使用更少的空间和计算开销。因此只在对小数进行精度计算时才使用DECIMAL–例如存储财务数据。但在数据量比较大的时候,可以考虑使用BIGINT替代DECIMAL, 将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

4.字符串类型

VARCHAR:用来存储可变长字符串。它比定长类型更节省空间,因为它仅使用必要的空间(除非指定ROW_FORMAT=FIXED)。VARCHAR使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或等于255字节,则使用1个 字节表示,否则使用2个字节。比如VARCHAR(100)需要11个字节的存储空间,VARCHAR(1000)需要1002个字节。
使用VARCHAR最合适的情况:字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题;使用了像UTF-8这样复杂的字符集。
注意:慷慨是不明智的。虽然VARCHAR(5)和VARCHAR(200)在存储’hello’的空间开销是一样的,但是更长的列消耗更多的内存。因为MySQL通常会分配固定大小的内存来保存内部值。
CHAR:存储的类型是定长的,根据定义的字符串长度分配足够的空间。当存储CHAR值时,MySQL会删除所有的末尾空格。
使用CHAR最最适合的情况:存储的字符串很短,或者所有的值都接近同一个长度。比如:密码的MD5值,CHAR(1)存储只有Y和N的值。
VARCHAR和CHAR删除末尾空格的情况:当’ string1’时,存储结果一样,但当插入’string ‘时,CHAR变成’string’,VARCHAR则不变。
BLOB和TEXT类型:都是为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。与其它类型不同,MySQL把每个BLOB和TEXT值当作一个独立的对象处理。当值太大时,InnoDB会使用专门的外部存储区域进行存储, 此时每个值在行内需要1~4个字节存储指针,然后在外部存储实际的值。
BLOB和TEXT的区别:BLOB类型存储的是二进制数据,没有排序规则或字符集,而TEXT有排序规则和字符集。它的排序是对每列的最前max_sort_length排序而不是整个字符串,也可以通过ORDER BY SUBSTRING(column,length) 指定。

5.日期和时间类型

MySQL提供了两种相似的日期类型:DATETIME和TIMESTAMP。MySQL能存储的最小粒度为秒。
DATETIME:能保存最大范围的值为:1001年~9999年,精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关,使用8个字节的存储空间。 TIMESTAMP:保存了从1970年1月1日午夜(格林尼治时间)以来的秒数,它和UNIX时间戳相同,使用4个字节的存储空间。因为它的范围比DATETIME小:1970年~2038年。
TIMESTAMP提供的值和时区有关,而DATETIME则保留文本表示的日期和时间。除特殊情况外,通常尽量使用TIMESTAMP,因为相比DATETIME更节省空间。

6.选择标识符(identitier)

要跟关联表对应的列类型要一样,而且要精确匹配,包括像UNSIGNED这样的属性。保证未来增长空间的前提下,应该选择最小的数据类型。最好选择整数类型,因为快,而且可以使用AUTO_INCREMENT。不要使用ENUM和SET类型。 尽量避免使用字符串,因为耗空间。UUID不如递增的整数好用。

2.MySQL schema设计中的缺陷

避免使用太多的列;避免使用太多的关联(最好12以内);避免过度使用枚举或者SET。

3.范式和反范式化

1.范式的优点和缺点

优点:更新操作比反范式化要快;很少或没有重复列,所以修改的数据会更少;范式化的表小,可以更好的放在内存,所以执行操作会更快;很少多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。
缺点:通常需要关联。

2.反范式的优点和缺点

优点:很好的避免关联。

3.混用范式化和反范式化

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码支持
你说多少就多少

比五毛钱特效专业哦