第4章 Schema 与数据类型优化

2017-02-09 13:25:47.0

4.1 选择优化的数据类型

更小的通常是更好的
一般情况下,应尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快。因为它们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少
简单就好
简单的数据类型的操作通常需要更少的CPU周期。
尽量避免NULL
如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较更复杂。可为NULL的列会使更多的存储空间,在MySQL里也需要特殊处理。当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至可能导致固定大小的变成可变大小的索引。
通常把可为NULL的列改为NOT NULL带来的性能提升比较小,所以调优时没有必要首先在现有schema中查找并修改掉这种情况。但是如果计划在列上建索引,就应尽量避免设计成可NULL的列。
当然也有例外,InnoDB使用单独的位(bit)存储NULL值,所以对于稀疏数据有很好的空间效率,但这一点不适用于MyISAM。
很多MySQL的数据类型可以存储相同的数据,只是存储的长度和范围不一样、允许的精度不同,或者需要的物理空间不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。

例如DATETIME和TIMESAMP列都可以存储相同类型的数据:日期和时间,精确到秒。然而TIMESAMP只使用DATATIME一半的存储空间,并且会根据时区变化,具有特殊的自动更新能力。另一方面TIMESAMP允许时间范围要小得多,有时它的特殊能力会成为障碍。
本章只讨论基本的数据类型。MySQL为了兼容性支持很多别名,如INTEGER,BOOL以及NUMERIC。

4.1.1 整数类型

有两种类型的数字:整数和实数。如果存储整数,可以使用:TINYINT(8)、SMALLINT(16)、MEDIUMINT(24)、INT(32)、BIGINT(64)。整数类型有可选的UNSIGNED属性,表示无符号。有无符号都使用相同的存储空间,并具有相同的性能。
MySQL可以为整数类型指定宽度,如int(11),对大多数应用这是没有意义的,它不会限制值的合法范围,只是规定了MySqL的一些交互工具用来显示字符的个数,对于存储和计算来说,INT(1)和INT(20)是一样的。下面是一段网络解释:
int(10)与int(11)后的括号中的字符表示显示宽度,整数列的显示宽度与mysql需要用多少个字符来显示该列数值,与该整数需要的存储空间的大小都没有关系,int类型的字段能存储的数据上限还是2147483647(有符号型)和4294967295(无符号型)。
CREATE TABLE `mytable` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `age` int(5) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
insert into mytable (age) values(8);
insert into mytable (age) values(100);
insert into mytable (age) values(10000);
此时查询:
mysql> select * from mytable;
+----+-------+
| id | age   |
+----+-------+
|  1 |     8 |
|  2 |   100 |
|  3 | 10000 |
+----+-------+
看不出什么,如果结合列的可选属性补0:
 alter table mytable modify column age int(5) ZEROFILL not null;
则:
mysql> select * from mytable;
+----+-------+
| id | age   |
+----+-------+
|  1 | 00008 |
|  2 | 00100 |
|  3 | 10000 |
+----+-------+
3 rows in set (0.00 sec)
就可以看出区别来了。

4.1.2 实数类型

实数是带有小数部分的数字。MySQL即支持精确类型,也支持不精确类型。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。
DECIMAL类型用于存储精确的小数。因为CPU不支持对DECIMAL的精确计算,因此MySQL5.0及以上版本自身实现了对DECIMAL的高精度计算。相对而言,CPU直接支持原生符点运算,因此浮点运算会更快一些。
DECIMAL只是一种存储类型,而不是计算类型,MySQL在内部会将其转化为DOUBLE类型进行运算。事实上DECIMAL会打包成一个字符串来存储,一个数字站一个字节,点本身也站一个字节。
浮点类型在存储相同范围的值时,比DECIMAL占用更少的空间。FLOAT是4个字节,DOUBLE是8个字节。

4.1.3 字符串类型

VARCHAR和CHAR类型
VARCHAR和CHAR是两种主要的字符串类型。由于存储引擎的不同,其存储方式也不尽相同,以下以InnoDB和MyISAM引擎为例讲解这两种数据类型。
VARCHAR用于存储变长的字符串,它比定长的类型更少空间,因为它只存储必要的值。有一个例外是当表使用ROW_FORMART=FIXED创建的话,每一行都是定长的。
VARCHAR类型使用1或2个额外的字节来存储字符串的长度。当字符串小于255时只使用一个字节存储长度,否则使用两个字节。例如:varchar(10)需要11个字节存储空间,varchar(1000)需要1002个字节存储空间。
因为是变长的节省了空间所以对性能有帮助,但是同样因为是变长的,在update时,需要做额外的工作。
当行的最大长度比平均长度大很多时适合使用VARCHAR类型。在InnoDB中,当varchar非常大时,可以自动转为BLOB存储。

CHAR是定长的,MySQL总是按照定义的长度分配足够的空间。当存储CHAR值时,MySQL会删除所有的末尾空格来存储。CHAR类型适合存储长度相近的行。比如密码的md5值。对于经常变更的数据,CHAR比VARCHAR更好,因为CHAR会产生更少的碎片。对于非常短的列更适合,因为CHAR(1)只需要一个字节,而VARCHAR(1)要用两个字节。
CREATE TABLE `mytable` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `age` int(5) unsigned zerofill NOT NULL,
  `name` char(10) DEFAULT NULL,
  `remark` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 
update mytable set name = 'zhangsan' where id = 1;
update mytable set name = ' zhangsan' where id = 2;
update mytable set name = 'zhangsan ' where id = 3;
mysql> select concat("'",name,"'") from mytable;
+----------------------+
| concat("'",name,"'") |
+----------------------+
| 'zhangsan'           |
| ' zhangsan'          |
| 'zhangsan'           |
+----------------------+
可以发现第三个的末尾空格被删除了。
然后char在查询时,也同样移除了后面的空格,而且后面有几个空格和没有空格,都会被查询出来,如下:
mysql> select * from mytable where name = 'zhangsan  ';
+----+-------+----------+-----------+
| id | age   | name     | remark    |
+----+-------+----------+-----------+
|  1 | 00008 | zhangsan | zhangsan  |
|  3 | 10000 | zhangsan | zhangsan  |
+----+-------+----------+-----------+
在varchar字段后面的空格保留了。
update mytable set name = 'zhangsan' where id = 1;
update mytable set name = ' zhangsan' where id = 2;
update mytable set name = 'zhangsan ' where id = 3;
mysql> select concat("'",remark,"'") from mytable;
+------------------------+
| concat("'",remark,"'") |
+------------------------+
| 'zhangsan'             |
| ' zhangsan'            |
| 'zhangsan '            |
+------------------------+
而使用varchar时却没有删除后面的空格。
但查询时,同样忽略后面的空格
mysql> select * from mytable where remark = 'zhangsan          ';
+----+-------+----------+-----------+
| id | age   | name     | remark    |
+----+-------+----------+-----------+
|  1 | 00008 | zhangsan | zhangsan  |
|  3 | 10000 | zhangsan | zhangsan  |
+----+-------+----------+-----------+
mysql> select length(name),length(remark) from mytable where remark = 'zhangsan          ';
+--------------+----------------+
| length(name) | length(remark) |
+--------------+----------------+
|            8 |              8 |
|            8 |              9 |
+--------------+----------------+


数据如何存储取决于存储引擎,并非所有的存储引擎都会按照相同的方式处理定长和变长的字符串。Memory引擎只支持定长字符串,即使使用变长类型,也村根据最大长度分配空间。而它们的填充和截取空格的行为却是一样的,因为这是在服务层做的。

与VARCHAR和CHAR相似的是VARBINARY和BINARY。后者用于存储二进制字符串,前者用户存储字符字符串。二进制字符串存储的是字节码而不是字符,填充也不一样。BINARY填充值是\0字节而不是空格,检索时也不会去掉填充。如下再加两个字段
`bname` binary(10) DEFAULT NULL,
  `bvname` varbinary(10) DEFAULT NULL,
mysql> select bname,length(bname) as "length(bname)",bvname,length(bvname) as "length(bvname)" from mytable;
+------------+---------------+------------+----------------+
| bname      | length(bname) | bvname     | length(bvname) |
+------------+---------------+------------+----------------+
| bzhangsan  |            10 | bzhangsan  |              9 |
|  bzhangsan |            10 |  bzhangsan |             10 |
| bzhangsan  |            10 | bzhangsan  |             10 |
+------------+---------------+------------+----------------+
查询遇到的问题,在查询时,bname 前后有空格时,bvname所有情况可以准确查询;而bname在没有前后空格时查询不出来
mysql> select * from mytable where bname ='bzhangsan';
Empty set (0.00 sec)

能短就短
使用varchar(5)和varchar(200)存储hello的空间开销是一样的。性能呢?事实证明,使用长的会消耗更多的内存。因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别差。

BLOB和TEXT类型
这两个都是为存储很大的数据而设计的字符串数据类型。分别采用二进制和字符存储方式。
它们分别属于两种不同的数据类型家族:
TINYTEXT、SMALLTEXT(同BLOB)、TEXT、MEDIUMTEXT、LONGTEXT
TINYBLOB、SMALLBLOB(同BLOB)、BLOB、MEDIUMBLOB、LONGBLOB
与其他类型不同,MySQL把这两种类型当成一个对象来处理,当数据过大时,InnoDB会使用专门的“外部”存储区来进行存储,而此时每个值在行内需要一个1~4字节存储一个指针,在外部区域存储实际的值。
BLOB与TEXT的不同:BLOB存储的是二进制,没有字符集和排序规则。
在排序方面这两种类型与其他的也不同。它们只对数据的最前max_sort_length字节而不是整个字符串建立索引
MySQL不能将BLOB和TEXT列全部长度建立索引。

因为MySQL的Memory引擎不支持BLOB和TEXT类型,因此如果查询这样的数据时,且需要临时表时,将不得不使用MyISAM磁盘临时表,这会导致严重的性能开销。


使用枚举代替字符串
枚举类型范围是1~2个字节,存储值为0~65535
修改下表名
alter table mytable rename to datatypetest;
添加两个枚举字段
alter table datatypetest add column enumc enum('fish','apple','dog') not null;
此时查看一下建表sql
CREATE TABLE `datatypetest` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `age` int(5) unsigned zerofill NOT NULL,
  `name` char(10) DEFAULT NULL,
  `remark` varchar(10) DEFAULT NULL,
  `bname` binary(10) DEFAULT NULL,
  `bvname` varbinary(10) DEFAULT NULL,
  `enumc` enum('fish','apple','dog') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 
插入几条记录,查询之
mysql> select enumc from datatypetest;
+-------+
| enumc |
+-------+
| fish  |
| apple |
| dog   |
+-------+

1)但其实存储的是整数:

mysql> select enumc+0 from datatypetest;
+---------+
| enumc+0 |
+---------+
|       1 |
|       2 |
|       3 |

2)不要在枚举中存储数值常量,因为这样容易与实际存储值相混淆。

3)排序是按照数据库中枚举存储的整数值排序的。
mysql> select enumc from datatypetest order by enumc;
+-------+
| enumc |
+-------+
| fish  |
| apple |
| dog   |
+-------+
如果想用字符顺序来排序,则最好使用想要排序的方式定义枚举。如上定义成apple dog fish

枚举最不好的地方就是扩展性。因为它的字符串列表值是固定的,如果想加就必须用alter修改表定义。预计将来可能要修改的字段不要这样做。
好处还有,使用枚举数据类型,会使表列体积大大减少。

4.1.4 时期和时间类型

MySQL提供两种相似的时间数据类型:DATETIME和TIMESTAMP。很多情况下它们都能正常工作,但在某些情况下,一个比另一个工作得好。
DATETIME
     数据保存的范围是1001到9999年,精度为秒。它将时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节存储空间。
TIMESTAMP
     数据存储范围是1970年1月1日夜(格林尼治标准时间)以来的秒数,它和UNIX时间戳相同。TIMESTAMP只使用4个字节的长度。因此它的存储空间比DATETIME小的多。是1970年到2038年。
这个范围的是怎么算出来的?
现添加两个字段:
alter table datatypetest add column datetimec datetime not null,add column timestampc timestamp not null;
但是想插入时间'1970-01-01 00:00:00'是失败的
mysql> update datatypetest set timestampc = '1970-01-01 00:00:00' where id = 1;
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:00' for column 'timestampc' at row 1
正是因为上面所说,TIMESTAMP数据类型,是时区相关的,北京是东八区,因此最小应为8点
mysql> update datatypetest set timestampc = '1970-01-01 08:00:01' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

数据类型DATETIMETIMESTMAP
存储大小8字节(精确到秒YYYYMMDDHHMMSS整数存储)格林尼治时间,从1970-01-01 00:00:00开始的秒数
显示显示与时区无关显示和存储与时区有关
默认值0当前时间,将会把第一列的TIMESTAMP设置为当前时间
非空默认不非空默认非空 NOT NULL

MySQL只能精确到秒级别。如果想处理比秒更小的日期,可以使用BIGINT存储秒级别的时间戳。

4.1.5   位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型,不管底层存储格式和处理方式如何,从技术上来说都是字符串类型。
BIT
在MySQL5.0之前,BIT即TINYINT。但之后却不同了。它最大长度是64。应谨慎使用BIT类型,对于大部分应用,最好避免使用这种类型。
SET

4.1.6 选择标识符(即ID列,主键列)

整数类型:
标识列中最好的选择。它们很快且可以使用AUTO_INCREMENT
字符串类型:
避免使用。因为它很消耗空间,而且查询很慢。尤其在MyISAM引擎中更要小心,因为MyISAM默认对字符串使用压缩索引,会导致查询慢很多。
对于完全随机的字符串也要注意,因为函数生成的新值任意分分布在很大的空间,导致INSERT和SELECT很慢。
· 因为插入新值会随机的写入到索引的不同位置,所以使得INSERT语句很慢。会导致页分裂、磁盘随机访问,以及对于聚簇存储引擎产生的碎片。
· SELECT语句变慢。因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
· 随机值导致缓存对所有类型的查询语句效果变得很差。

4.1.7 特殊类型数据
IPv4地址使用点分十进制表示法,但其真正的值是32位的无符号整数,在数据库中可以保存为整数,MySQL提供了INET_ATON()和INET_NTOA()函数进行转换
mysql> select inet_aton('127.0.0.1');
+------------------------+
| inet_aton('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(2130706433);
+-----------------------+
| inet_ntoa(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set (0.00 sec)

4.2 MySQL schema 设计中的陷阱


太多的列
MySQL的存储引擎API工作时,需要在服务器层和存储引擎层之间通过缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据的操作代价是非常高的。MyISAM的定长结构实际上与服务kdknfc的行结构正好匹配,所以不需要转换。然后MyISAM的变长行和InnoDB的行结构则总是需要转换,转换的代价依赖于列的数量。

太多的关联
所谓的“实体-属性 -值”(EAV)设置模式是一个常见的糟糕的设计模式,尤其在MySQL下不能靠谱的工作。MySQL限制了每个关联操作最多只能有61张表。

全能的枚举
防止过多的使用枚举,尤其是枚举元素非常多时。因为枚举一旦需要修改,则意味着必须alert table。而早期的数据库在修改表时,必须锁表是阻塞操作,即便是5.1以后,如果不是在表末尾同样也需要锁表。

变相的枚举
如把SET类型用作枚举 set('Y','N'),此时应改为枚举

非此发明的NULL
不一定必须避免使用NULL,因为不使用NULL需要使用一些特殊的值,如0 -1等替换NULL,会造成代码的复杂度而且容易造成bug

4.3 范式和反范式

第一范式(1NF):关系中的每个属性都不能再分。
第二范式(2NF):满足1NF,表中的字段必须完全依赖于全部主键而非部分主键
第三范式(3NF):满足2NF,非主键外的所有字段必须互不依赖

4.3.1 范式的优点和缺点

· 范式化的更新操作通常比反范式化要快
· 当数据较好地范式化时,就只有很少或者没有重复数据,所以只要修改更少的数据
· 范式化的表通常更小,可以更好的放在内存里,所以执行操作会更快
· 很少有多余的数据意味着检索列数据时更少需要DISTINCT或者GROUP BY语句
范式化设计的schema的缺点是通常需要关联。关联查询可能会导致一些索引策略失效。

4.3.2 反范式的优点和缺点

优点:避免关联查询,单独的有也能使用更有效的索引策略。

4.3.3 混用范式化和反范式化



4.4 缓存表和汇总表

用于统计使用的数据,一般不能提供实时的汇总数据,因此可以1小时甚至一天,执行一次数据统计汇总到一个单独的表中,如用户的活跃度,或者订单金额统计。

4.4.1 物化视图

4.4.2 计数器表

在web系统中经常会有计数操作,如统计用户的点击次数。
create table hit_counter(
    cnt int unsigned not null
)negine=innodb;
这样每次用户点击时可以做update操作更新这个计数器的值。
问题在于,对于任何想更新这一行的事务来说,这条记录上都有一个全局的互斥锁,会使得这些事务只能串行更新。因此做如下修改:
create table hit_counter(
    slot tinyint unsigned not null primary key,
    cnt int unsigned not null
)negine=innodb;
预先在表中添加100条记录数据,更新时选择一个随机槽进行更新
update hit_counter set cnt = cnt + 1 where slot = rand()*100;
要获取统计结果只需sum即可。

4.5 加快alter table的速度

MySQL的ALTER TABLE的操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表,从旧表中查出所有数据插到新表,然后删除旧表。
这样的操作可能花费很长的时间,如果内存不足,表又很大,索引也很多更是如此。
一般而言,大部分alter table都会使MySQL中断服务。但不是所有的,如:
alter table mytable modify column retal_duration not null set default 5;会创建新表然后执行数据拷贝
alter table mytable alter column retal_duration not null set default 3;直接修改.frm文件

4.5.1 只修改.frm文件

下面这些操作不需要重建表
` 移除(不是增加)一个列的AUTO_INCREMENT属性
` 增加、移除,或更改ENUM和SET常量
底层细节:
1 创建一张相同结构的空表,并进行所需要的修改
2 执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表,并且禁止任何表被打开
3 交换.frm文件
4 执行UNLOCK TABLES来释放第2步的读锁

4.5.2 快速创建MyISAM索引

有一常用的技巧是先禁用索引、载入数据,然后重新启用索引
alter table mytable disable keys;
--- load data
alter table mytable enable keys;
这个技巧能发挥作用是因为构建索引的工作被延迟到数据完全载入以后,这时已经可以通过排序来构建索引了。但对于唯一索引是无效的。

数据类型
数值型-整型
TypeStorageMinimum ValueMaximum Value
 (Bytes)(Signed/Unsigned)(Signed/Unsigned)
TINYINT1-128127
  0255
SMALLINT2-3276832767
  065535
MEDIUMINT3-83886088388607
  016777215
INT4-21474836482147483647
  04294967295
BIGINT8-92233720368547758089223372036854775807
  018446744073709551615
数值型-符点型








字符型
类型大小用途
CHAR0-255字节定长字符串
VARCHAR0-255字节变长字符串
TINYBLOB0-255字节255 个字符的二进制字符串 
TINYTEXT0-255字节短文本字符串
BLOB0-65535字节二进制形式的长文本数据
TEXT0-65535字节长文本数据
MEDIUMBLOB0-16777215字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16777215字节 中等长度文本数据
LOGNGBLOB0-4294967295字节 二进制形式的极大文本数据
LONGTEXT0-4294967295字节极大文本数据
日期类型
类型


datetime


timestamp