这一章关注的是MySQL 数据库的设计,与其他关系型数据库管理系统的区别。
1.小的通常更好(更快,占用更少的磁盘、内存和CPU 缓存,并且处理时需要的CPU 周期也更少)但是要确保没有低估需要存储的值的范围
2.简单就好,例如,整型比字符操作代价低,因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。MySQL 内建的类型date,time,datetime 而不是字符串来存储日期和时间,另外一个是应该用整型存储IP地址。
3.尽量避免NULL
更难优化,NULL 的列使得索引、索引统计和值比较更复杂。占存储空间。NULL 的列被索引时,每个索引记录需要一个额外的字节。
也有例外,InnoDB 使用单独的位(bit)存储NULL 值,对于稀疏数据(少数不为空)有好的空间效率。不适用于MyISAM。
本章只讨论基本的数据类型。MySQL 为了兼容性支持很多别名,例如INTEGER、BOOL,以及NUMERIC。建表时采用数据类型的别名,用SHOW CREATE TABLE 检查,会发现MySQL 报告的是基本类型,而不是别名。
4.1.1 整数类型
存储整数:TINYINT(8),SMALLINT(16),MEDIUMINT(24),INT(32),BIGINT(64),范围从-2^(N-1) ~ 2^(N-1)-1,N 是存储空间的位数(8,16......)
可选的UNSIGNED (不允许负值,正数的上限提高一倍),例TINYINT UNSIGNED 存储的范围是0 ~ 255,TINYINT 是.128 ~127
有符号和无符号类型存储空间、性能相同。
整数计算:64 位的BIGINT 整数,即使在32 位环境也是如此。(聚合函数[对一组值执行计算,并返回单个值]例外,它们使用DECIMAL 或DOUBLE 进行计算)。
指定宽度,INT(11)对大多数应用这是没有意义的,存储和计算来说,INT(1) 和INT(20) 是相同。
4.1.2 实数类型
带有小数部分的数字。也可使用DECIMAL 存储比BIGINT 还大的整数。
DECIMAL存储精确的小数。DECIMAL 的直接计算,CPU 不支持,MySQL服务器自带。CPU 直接支持原生浮点计算,所以浮点运算更快。
指定小数点前后所允许的最大位数,影响列的空间消耗。MySQL 5.0 将数字打包保存到一个二进制字符串中(每4 个字节存9 个数字)。DECIMAL(18,9) 12345678.87654321 ‘.’占1 个字节,前后4字节
DECIMAL 只是一种存储格式,计算中转换为DOUBLE 类型。
只在对小数进行精确计算使用DECIMAL。FLOAT 4 字节。DOUBLE 8 字节,相比FLOAT 有更高的精度和更大的范围。例如存储财务数据。但在数据量比较大的时候,可以考虑使用BIGINT 代替DECIMAL。
4.1.3 字符串类型
可自定义字符集和排序规则,但会很大程度上影响性能。
VARCHAR 和CHAR 类型:引擎存储CHAR 或者VARCHAR 值的方式在内存中和在磁盘上可能不一样
(1)VARCHAR
可变长字符串,ROW_FORMAT=FIXED 创建,定长存储,这会浪费空间。
需要使用1 或2 个额外字节记录字符串长度:列的最大长度<=255 字节1 个字节,否则使用2 个字节。VARCHAR(1000) 2 个字节存储长度信息,1001字节的存储空间。
UPDATE 时可能使行变更长,导致额外的工作:行空间增长,页内没有更多空间可存储。MyISAM :将行拆成片段存储,InnoDB分裂页来放进页内。(没懂)
用VARCHAR 合适的情况:字符串列的最大长度比平均长度大很多;列的更新很少,碎片不是问题;用了UTF-8 这样复杂字符集,每个字符都使用不同字节数存储。
存储和检索时会保留末尾空格
InnoDB 把过长的VARCHAR 存储为BLOB
(2)CHAR
定长,根据字符串长度分配足够的空间。删除末尾空格,存储很短的字符串,接近同一个长度。
例:密码MD5 值(定长的值)。
经常变更的值,CHAR 也比VARCHAR 更好,不易产生碎片。
短的列,CHAR 更有效率。CHAR(1) 存储只有Y 和N 的值,只需要一个字节,VARCHAR(1) 两个字节,还有一个记录长度的额外字节。
填充和截取空格在MySQL 服务器层处理的。
(3)BLOB 和TEXT 类型
存储很大的数据,二进制和字符方式存储。
TEXT :字符方式存储,类型是TINYTEXT,SMALLTEXT(BLOB的同义词),TEXT,MEDIUMTEXT,LONGTEXT
BLOB :二进制存储,TINYBLOB,SMALLBLOB(TEXT 同义词),BLOB,MEDIUMBLOB,LONGBLOB。
当作独立的对象处理。值太大时,InnoDB 会使用专门的“外部”存储存储,行内1 ~ 4 个字节存储一个指针。
不同:BLOB没有排序规则或字符集,TEXT 类型有。
排序:只对最前max_sort_length 字节而不是整个字符串做排序。如只需要排序前面一小部分字符,则可以减小max_sort_length 的配置,或者ORDER BY SUSTRING(column, length)。
(4)磁盘临时表和文件排序
Memory 引擎不支持BLOB 和TEXT 类型,所以使用隐式临时表,如MyISAM 磁盘临时表,这样性能开销大
尽量避免使用BLOB 和TEXT 类型。如无法避免,使用SUBSTRING(column, length) 转换为字符串(在ORDER BY 子句中也适用),就可使用内存临时表了。但是要确保截取的子字符串足够短,临时表的大小超过max_heap_table_size 或tmp_table_size将内存临时表转换为MyISAM 磁盘临时表。
例如,假设有一个1 000 万行的表,占用几个GB 的磁盘空间。其中有一个utf8字符集的VARCHAR(1000) 列。每个字符最多使用3 个字节,最坏情况下需要3 000字节的空间。如果在ORDER BY 中用到这个列,并且查询扫描整个表,为了排序就需要超过30GB的临时表。
(5)枚举代替字符串类型
这三行数据实际存储为整数,而不是字符串。可以通过在数字上下文环境检索看到这个双重属性:
缺点:与varchar关联慢得多
优点:
1.列都为enum类型时,关联变得很快:在“查找表”时采用整数主键而避免采用基于字符串的值进行关联;
2.enum列可以让表的大小缩小;
3.主键大小减小很多;(在InnoDB 表上,如表上有其他索引,减小主键大小会使非主键索引也变得更小。)
4.14日期和时间类型
例:YEAR和DATE,粒度为秒
DATETIME:1001 年到9999 ,精度为秒。YYYYMMDDHHMMSS 整数中,与时区无关。8 个字节
TIMESTAMP*(尽量使用):1970 年到2038 ,FROM_UNIXTIME() 函数把Unix 时间戳转换为日期,赖于时区。4个字节
4.15 位数据类型
bit(尽量避免使用)
定义:bit(8),tinyint同义词,值:b'00101100',一个字节表示1位,没有压缩存储
当做二进制对应的字符串类型,但在整数上下文会转换成数字
set
缺点:改变列代价高:需ALTER TABLE
以打包位的集合表示,有效利用存储空间
整数列进行按位操作:如tinyint
4.16选择标识符
1.标识列可作为外键使用
2.考虑存储类型,还需考虑怎么执行计算和比较,例如set和ENUM,做比较时转为字符串。
3.关联表中最好选用同样类型:混用会导致性能问题,隐式类型有错时难发现。
(1) 整数类型最好:快且可使用AUTO_INCREMENT
(2) enum和set只适合独立表详情展示,不适合关联查询
(3) 尽量避免字符串,因为很耗空间,且很慢:
(4)随机值,慎用
插入会写到索引不同位置,导致页分裂、磁盘随机访问以及聚簇索引碎片
查询慢,因为逻辑行随机分布的,不如递增
如果存储UUID 值,则应该移除“-”符号;或UNHEX() 函数转换UUID 值为16 字节的数字,并且存储在BINARY(16) 列中。检索时可以通过HEX()函数来格式化为十六进制格式。
4.当心自动生成的schema
会导致严重性能问题。存储任何东西都会使用很大的VARCHAR 列,比较的列使用不同的数据类型。
对象关系映射(ORM)系统是另一种常见的性能噩梦。没优化。为每个对象的每个属性使用单独的行,甚至使用基于时间戳的版本控制,导致单个属性会有多个版本存在。
面向对象的方式工作,不需要考虑数据是怎么存储。“对开发者隐藏复杂性”的应用通常不能很好地扩展。
4.1.7 特殊类型数据
例IPv4 地址。经常使用VARCHAR(15) 存储,实际上是32 位无符号整数,不是字符串。用小数点为了阅读容易。应用无符号整数存储IP 地址。MySQL 提供INET_ATON()和INET_NTOA() 函数在这两种表示方法之间转换。