MySQL-2
Schema与数据类型优化
选择数据类型
- 1、更小的通常更好，即满足需求下尽可能使用小的数据类型(占用更少的磁盘，内存和CPU)。
- 2、简单就好：整型比字符型代价更低
- 3、尽量避免使用NULL，即除非字段中必须有null值，否则设计表时应该设置为not null
- 4、Date和Timestamp，优先使用TimeStamp，因为其占用的存储空间只有Date的一半，且运行时间范围要小的多，如果可以使用long时间戳，就更好。
整数类型
- 整数(whole number)和实数(real number)。如果存储整数，可以使用这几种整数类型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8，16，32，64位存储空间，它们恶意存储的范围址从-2的N-1次方到2的N-1次方-1，N为存储空间的位数。
- 整数类型有可选的UNSIGNED属性，表示不允许负值。
- 整数计算一般使用64位的BIGINT整数，即使在32位的环境下也是如此。MySQL可以为整数类型指定宽度，它对于存储和计算来说是没有区别的，只是会限制一些交互工具显示字符的个数。
实数类型
- 实数是带有小数部分的数字。MySQL即支持精确类型，也支持不精确类型。
- FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。
- DECIMAL类型用于存储精确的小数。
- 浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数，则会影响列的空间消耗。DECIMAL只是一种存储格式，在计算中DECIMAL会转换为DOUBLE类型。有很多种方法可以指定浮点列所需要的精度，这使得MySQL会选择不同的数据类型或在存储时对值进行取舍。这些精度定义是非标准的，所以建议只指定数据类型而不指定精度。
- 浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。应尽量支队小树进行精确计算时才使用DECIMAL。在数据量比较大时，可以使用BIGINT代替DECIMAL。
数据类型 | 大小 | 范围 | 说明 |
|---|---|---|---|
tinyint | 1个字节 | 有符号(-128 到127)，无符号(0到255) | 默认为有符号 |
int | 4个字节 | 有符号(- 2^23 ~ 2^23 – 1) | 默认为有符号 |
bigint | 8个字节 | 有符号(-2^63 ~ 2^63 - 1) | 默认为有符号 |
float | 4字节 | 有符号(- 2^23 ~ 2^23 – 1) | 单精度浮点 |
double | 8字节 | 双精度浮点 | |
decimal | 不确定 | 不确定 | 精确计算 |
字符串类型
- VARCHAR和CHAR是两种最主要的字符串类型。它的存储和存储引擎的具体实现相关。
- VARCHAR类型用于存储可变字符串，是最常见的字符串数据类型，它比定长类型更节省空间。若MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会定长存储。VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则用两个。由于行是变长的，所以在UPDATE时可能使行变得比原来更长，需要额外的工作。若一个行占用的空间增长，并且在页内没有更多的空间可以存储，此时，MyISAM会将行拆成不同的片段进行存储，InnoDB或者额需要分裂页来使行放进页内。当字符串的最大长度比平均长度大很多时；列的更新很少；使用了UTF-8时比较适合用VARCHAR。MySQL在存储和检索时会保留末尾空格。InnoDB会把过长的VARCHAR存储为BLOB。
- CHAR类型时定长的：MySQL总是根据定义的字符串的长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。CHAR适合存储很短的字符串，或者所有值都接近于一个长度。CHAR适合存储密码的MD5值，对于不经常变更的值CHAR也比较合适。对于比较短的列，CHAR比VARCHAR在存储空间上也更有效率。
- Memory引擎只支持定长的行，即使有变长字段也会根据最大长度分配最大空间。
- 填充和截取空格的行为在不同存储引擎都是一样的，这是在MySQL服务器层进行处理的。
- BINARY和VARBINARY存储的事二进制字符串，二进制字符串存储的是字节码而不是字符。MySQL填充BINARY采用的是\0而不是空格，在检索时也不会去掉填充值。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较，因此二进制比较比字符比较简单得多，所以更快。
BLOB和TEXT类型
- BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
- 字符类型时：TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT，LONGTEXT。
- 二进制类型：TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB。
- MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会作特殊处理。当BLOB和TEXT值太大时，InnoDB会使用外部存储区域进行存储，此时每个值在行内需要1～4个字节存储一个指针，然后在外部存储区域存储实际的值。
- BLOB和TEXT的区别是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT是字符集和排序规则。
- MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length字节而不是整个字符串进行排序。若只需要排序前面一小部分，可以减少max_sort_length的配置，或使用ORDER BY SUSTRING(column, length)。MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。
- 因为Memory引擎不支持BLOB和TEXT，所以查询使用BLOB或TEXT列并且需要使用隐式临时表，此时可以在所有用到BLOB的字段都使用SUBSTRING(column, length)将列的值转换为字符串，这样就可以使用内存临时表了。但要确保街区的子字符串足够短，不会使临时表的大小超过max_heap_table_size或tmp_table_size，超过以后MySQL会将内存临时表转换为MyISAM磁盘临时表。
数据类型 | 大小 | 说明 |
|---|---|---|
char | 0-255字节 | 定长字符串 |
varchar | 0-65535字节 | 变长字符串 |
tinyblob | 0-255字节 | 小二进制字符串 |
tinytext | 0-255字节 | 小文本字符串 |
blob | 0-65535字节 | 二进制字符串 |
text | 0-65535字节 | 文本字符串 |
mediumblob | 0-(2^24-1)字节 | 中等二进制字符串 |
mediumtext | 0-(2^24-1)字节 | 中等文本字符串 |
longblob | 0-(2^32-1)字节 | 大二进制字符串 |
longtext | 0-(2^32-1)字节 | 大文本字符串 |
FAQ
- char、varchar和text的区别？ 1、char，存定长，速度快，存在空间浪费的可能，会处理尾部空格，上限255。 2、varchar，存变长，速度慢，不存在空间浪费，不处理尾部空格，上限65535，但是有存储长度实际65532最大可用。 3、text，存变长大数据，速度慢，不存在空间浪费，不处理尾部空格，上限65535，会用额外空间存放数据长度，顾可以全部使用65535。
Enum枚举类型
值也可以是空串(“”) 或 NULL
- 枚举类型是特殊的字符串类型，定义枚举列后，真正存在表中的是整数，表的.frm文件则保存整数和枚举字符串的映射关系
- 如：CREATE TABLE enum_test(e ENUM(‘fish’,’apple’,’dog’) NOT NULL);在表中，真正存储的是，1、2、3这样的数字
- 不要使用数字作为枚举字符串常量，如ENUM(‘1’,’2’,’3’)。这样会导致混乱
- 枚举的顺序是按照背地里的数字来排序的，因此，你的Order by语句可能得不到按字符串排序的结果。解决方案就是声明的时候就把字符串排好序，枚举常量对应的数字是和声明时的顺序有关的。还有一种方案是使用如下FIELD语句：
- SELECT e FROM enum_test ORDER BY FIELD(e,'apple','dog','fish'); 1 2 >```
>- ENUM和CHAR(VARCHAR)类型关联查询，会慢一些，因此，假如预先知道某列需要与CHAR类型关联，那么就不应该将该列设置为ENUM类型 >- ENUM类型的列可有效缩小表所占的空间，书中写可缩小1/3
日期和时间类型
- MySQL可以使用许多类型来保存日期和时间值。例如YEAR和DATE。MySQL能存储的最小时间粒度为秒。但MySQL可以使用微秒级的粒度进行临时运算。MySQL提供两种日前类型；DATETIME和TIMESTAMP。
- DATETIME精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。
- TIMESTAMP只使用了4个字节的存储空间。MySQL提供了FROM_UNIXTIME()函数把Unix时间转换为日期，并提供UNIX_TIMESTAMP()函数把日期转换为Unix的时间戳。TIMESTAMP显示的值也依赖于市区。MySQL服务器，操作系统以及客户端连接都有时区的设置。默认情况下，若插入时没有指定第一个TIMESTAMP列的值，MySQL则设置这个列的值为当前时间。TIMESTAMP列默认为NOT NULL。
- 我们应该尽量使用TIMESTAMP。
数据类型 | 大小 | 格式 | 说明 |
|---|---|---|---|
date | 3字节 | YYYY-MM-DD | 日期值 |
datetime | 8字节 | YYYY-MM-DD HH:MM:SS | 时间和日期值 |
timestamp | 4字节 | YYYYMMDDHHMMSS | 时间戳 |
MySQL schema设计中的陷阱
- 太多的列：MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲功能中国年将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配，所以不需要转换。但是MyISAM和变长结构和InnoDB的行结构则总需要转换。转换的代价依赖于列的数量。
- 太多的关联：实体——属性——值(EVA)设计模式在MySQL下不能很好的工作。MySQL限制了每个关联操作最多只有61张表。单个查询最好在12个表以内做关联。
- 全能的枚举：防止过度使用枚举。
- 变相的枚举：枚举列允许在列中存储一组定义值中的单个值，集合(SET)则允许在列中存储一组定义值中的一个或多个值。
- 非此发明(Not Invent Here)的NULL：避免使用NULL，可以使用0或某个特殊值，或者空字符串来作为代替。
范式和反范式
- 1NF(第一范式)：强调原子性，即列不能够再被复制
- 2NF(第二范式)：首先是1NF，且必然有主键，没有包含在主键的列必须完全依赖于主键
- 3NF(第三范式)：首先是2NF，且非主键列必须直接依赖于主键
在范式话数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。
范式的优点:
- 范式化的更新操作通常比反范式化要快
- 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改很少的数据
- 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快
- 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句
范式的更新要比反范式的更新要快，但是这样就需要更多的关联，使查询效率降低.
缓存表，汇总表
有时挺升性能最好的办法是在同一张表中保存衍生的冗余数据，有时也需要创建一张完全独立的汇总表或缓存表。我们使用缓存表来存储哪些可以比较简单地从schema其他表获取数据的表，汇总表保存的是使用GROUP BY语句聚合数据的表。 缓存表对优化搜索和检索查询语句很有效。可以对缓存表使用不同的存储引擎。 当重建汇总表和缓存表时，通常需要保证数据在操作时依然可用。这需要使用影子表。影子表指的是一张在真实表背后创建的表。当完成建表操作后，可以通过一个原子命名操作切换影子表和原表。 CREATE TABLE t2 AS SELECT * FROM t1; //只有表结构和数据，没有索引，注释，序列等 CREATE TABLE t2 LIKE t1;//有完整的表结构，但是没有数据
物化视图
物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。MySQL并不支持物化视图。可以使用Flexviews，它由下面几个部分组成。 变更数据抓取(change data capture, CDC)功能，可以读取服务器的二进制日志并且解析相关行的变更 一系列可以帮助创建和管理试图的定义的存储过程 一些可以应用变更到数据库中国年的物化视图的工具 相比传统的维护汇总表和缓存表的方法，Flexviews通过提取对源表的更改，可以增量地重新计算物化视图的内容。
计数器表
创建一张独立的表存储技术器可以帮助避免查询缓存实效，并且可以使用本节展示的一些更高级的技巧。例如在高并发下，可以将计数器保存在多行，每次随机选择一行进行更新。
加快alter table 的操作速度
MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查处所有数据插入新表，然后删除旧表。大部分ALTER TABLE会导致MySQL服务中断。可以通过两个技巧：一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；另一种技巧是影子拷贝。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。 更改表可以通过ALTER COLUMN修改表中的列，这个命令直接修改.frm文件，因此速度很快。也可以直接修改.frm来改动表达到不新建一张表的目的；修改.frm只适用以下几种方式：
- 移除一个列的AUTO_INCREMENT属性
- 增加，移除或更改ENUM和SET常量。若移除的是已经有有行数据用到其值的常量，查询将会返回个空字符串。
基本的技术是为想要的表结构创建一个新的.frm文件，然后用它替换已经存在的那张表的.frm，步骤如下：
- 创建一张由相同结构的空表，并进行所需要的更改；(CREATE TABLE t2 LIKE t1;ALTER TABLE t2 MODIFY COLUMN rating ENUM(‘A’,’B’,’C’) DEFAULT ‘A’;)
- 执行FLUSH TABLES WITH READ LOCK。它会关闭所有正在使用的表，并且禁止任何表被打开；(FLUSH TABLES WITH READ LOCK;)
- 交换.frm文件；(mv t1.frm t1_temp.frm;mv t2.frm f1.frm;mv t1_temp.frm f2.frm)
- 执行UNLOCK TABLES来释放第二步的读锁(UNLOCK TABLES)