第四章、Schema与数据类型优化
前言
| 项 | 版本号 | 说明 |
|---|---|---|
| Mysql | 5.6.37 MySQL Community Server (GPL) | 5.0以上版本即可 |
这一章主要为接下来的两章《创建高性能的索引》和《查询性能优化》做铺垫，这三章是mysql性能优化的核心内容，这里会讨论逻辑设计，物理设计和查询执行以及彼此之间的相互作用。需要我们既关注全局又关注局部细节。
4.1、数据类型的选择
、常见的优化技巧
- 1、更小的通常更好，即满足需求下尽可能使用小的数据类型(占用更少的磁盘，内存和CPU)。
- 2、简单就好：整型比字符型代价更低
- 3、尽量避免使用NULL，即除非字段中必须有null值，否则设计表时应该设置为not null
- 4、Date和Timestamp，优先使用TimeStamp，因为其占用的存储空间只有Date的一半，且运行时间范围要小的多，如果可以使用long时间戳，就更好。
4.1.2、常用数据类型
- 1、数字：下面表格列举常用的数据类型
| 数据类型 | 大小 | 范围 | 说明 |
|---|---|---|---|
| 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 | 不确定 | 不确定 | 精确计算 |
-
备注：tips：
除了上面的之外，还有下面这些smallint,mediumint。 -
2、字符串类型
字符串类型，主要分为char和varchar
| 数据类型 | 大小 | 说明 |
|---|---|---|
| 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)字节 | 大文本字符串 |
除此之外，还有BINARY、VARBINARY、ENUM、SET四种类型
FAQ
- char、varchar和text的区别？
1、char，存定长，速度快，存在空间浪费的可能，会处理尾部空格，上限255。
2、varchar，存变长，速度慢，不存在空间浪费，不处理尾部空格，上限65535，但是有存储长度实际65532最大可用。
3、text，存变长大数据，速度慢，不存在空间浪费，不处理尾部空格，上限65535，会用额外空间存放数据长度，顾可以全部使用65535。
3、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');
- ENUM和CHAR(VARCHAR)类型关联查询，会慢一些，因此，假如预先知道某列需要与CHAR类型关联，那么就不应该将该列设置为ENUM类型
- ENUM类型的列可有效缩小表所占的空间，书中写可缩小1/3
4、日期类型
|数据类型|大小|格式|说明|
| -- |-- | -- |
| date |3字节 | YYYY-MM-DD |日期值|
| datetime |8字节 | YYYY-MM-DD HH:MM:SS |时间和日期值|
| timestamp |4字节 | YYYYMMDDHHMMSS |时间戳|
备注：
如果没有特殊需求，日期还是建议存储成long类型的时间戳，这样前后端处理比较统一和方便。
4.2、MySQL schema设计中的陷阱
- 太多的列：
- 太多的关联：mysql限制关联最多61个关联，根据经验，单个查询最好控制在12个表以内
- 全能的枚举：
- 变相的枚举：
4.3、范式和反范式
在数据库设计中，完全的范式化和完全的反范式化都是实验室中才会存在的，在实际开发过程中很少有这样的使用，需要经常使用。
- 范式的更新要比反范式的更新要快，但是这样就需要更多的关联，使查询效率降低
4.4、缓存表，汇总表，物化视图，计数器表
这里介绍的相对比较简单，再后期实际应用中进行介绍。
- 缓存表（汇总表）：
假如统计一个网站23小时发出的消息数，在一个比较忙碌的网站下不可能随时维护一个精准的计数器。代替方案是每小时生成一张汇总表，这样比实时计算要高效的多。简单的说就是维护一些复杂 耗时的计算 那么用汇总表是比较好的选择。 - 物化视图：物化视图是查询结果的预运算。不同于简单的视图，物化视图的结果一般存储于表中。
- 计数器表：有些需要计数的功能可以存储，并实时更新。
4.5、加快alter table 的操作速度
这是这几张第一个实际相关操作的功能。我们详细的分析一下。
&MySQL alter一个超大表低效率低的原因
- mysql中alter表的机制是：创建一个符合alter目标的表，然后把数据全部插入到新表中，然后删除老表。
- 在数据迁移时，每插入一行，需要对索引进行一次更新。效率低下且会产生索引碎片
- 当没有足够的内存，或者表上的索引非常多时，效率十分的低下。
- 优化小技巧
.frm:表结构文件 .MYD:表数据文件 .MYI:表索引
1、 有时候只是改变某个列的配置，比如默认值等等，可以使用ALTER TABLE XXX.XXX ALTER COLUMN XXXX;使用ALTER COLUMN不要使用MODIFY COLUMN，这样修改操作是非阻塞的。
mysql> ALTER TABLE sakila.film
->MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;
mysql>ALTER TABLE sakila.film
->ALTER COLUMN rental_duration SET DEFAULT 5;
对比下这两种操作，耗时是不一样的。
2、修改frm文件，这是大神才做的，甚做。
《高性能MySQL读书笔记》
准备篇-mysql安装
准备篇-Sakila数据库
第一章、MySQL架构及历史
第二章、MySQL基准测试
第三章、服务器性能剖析
