Schema与数据类型优化
良好的逻辑设计和物理设计是高性能的基石，应根据系统要执行的查询语句来设计schema。
- MySQL支持的数据类型非常多，选择正确的数据类型是获得高性能的前提。
- 选择数据类型的原则
- 更小的通常更好
- 更小的数据类型通常更快，占用更少磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
- 注意没有低估值的取值范围，因为后期扩大数据类型是一个非常耗时和痛苦的操作。
- 简单就好
- 简单数据类型的操作通常需要更少的CPU周期。
- 值尽量避免NULL
- 查询中包含可为NULL的列，对MySQL来说更难优化，它使索引、索引统计和值值比较都更复杂。
- 在优化时把可为NULL的列改为非NULL代理的性能提升很小，所以调优时没有必要优先执行此操作，除非确定这会导致问题。
- 建立索引的列，避免存储NULL值。（NULL在索引中需要一个额外的字节）。在MyISAM里甚至可能导致固定大小的索引变成可变大小的索引。
- 更小的通常更好
- 选择数据类型的步骤
- 确定合适的大类型
- 数字
- 字符串
- 时间
- 选择大类型下的具体类型
- 确定合适的大类型
| 大类型 | 具体类型 | 存储空间 | 存储值的范围 | 备注 |
|---|---|---|---|---|
| 整数类型 | TINYINT | 8位 | -2^(N-1) ~ 2^(N-1) - 1 | |
| - | SMALLINT | 16位 | -2^(N-1) ~ 2^(N-1) - 1 | |
| - | MEDIUMINT | 24位 | -2^(N-1) ~ 2^(N-1) - 1 | |
| - | INT | 32位 | -2^(N-1) ~ 2^(N-1) - 1 | |
| - | BIGINT | 64位 | -2^(N-1) ~ 2^(N-1) - 1 | MySQL默认的整数计算类型 |
| 实数类型 | DECIMAL | 每4字节存储9个数字，小数点占1个字节 | ||
| - | FLOAT | 4字节 | ||
| - | DOUBLE | 8字节 | MySQL默认的实数计算类型 | |
| 字符串 | varchar | 变长字符串（需额外1或2字节记录长度） | ||
| - | char | 定长字符串 | ||
| - | VARBINARY | 可变长二进制字符串 | ||
| - | BINARY | 定长二进制字符串 | ||
| - | TEXT | 字符方式存储 | ||
| - | BLOB | 二进制方式存储 | ||
| 日期和时间 | DATETIME | 8字节存储空间，YYYYMMDDHHMMSS格式的整数 | 1001年-9999年 精度秒 | 与时区无关 |
| - | TIMESTAMP | 4字节存储空间 | 1970 - 2038 | 与时区有关 |
数据类型
整数
- TINYINT
- 8位存储空间
- SMALLINT
- 16位存储空间
- MEDIUMINT
- 24位存储空间
- INT
- 32位存储空间
- BIGINT
- 64位存储空间
- 可选 UNSIGNED属性，表示不允许负值，这大致可使正数的上限提高一倍。
- 有符合和无符号类型使用相同的存储空间，并具有相同的性能。
- 类型决定MySQL怎样在内存和磁盘中保存数据，计算时一般使用BIGINT类型，即使32位环境也是如此。
- MySQL可先为整型设置宽度，如INT(11)，对大多数应用是没有意义的。
- 不会限制值得合法范围，只规定了MySQL交互工具用来显示字符得个数。
- 对于存储和计算来说，INT(1)和INT(20)没有差别。
- 当结合可选扩展属性ZEROFILL使用时， 缺少的长度用0代替。
- 请注意如果在整数列保存超过显示宽度的一个值，当MySQL为复杂联接生成临时表时会遇到问题，因为在这些情况下MySQL相信数据适合原列宽度。
实数
-
实数
- 带有小数部分的数字。
-
MySQL即支持精确类型，也支持非精确类型
- 精确类型（MySQL服务器自身实现了高精度计算）
- DECIMAL
- 非精确类型（支持使用标准的浮点运行进行近似计算）
- FLOAT
- DOUBLE
- 精确类型（MySQL服务器自身实现了高精度计算）
-
浮点型在存储同样范围的值时，DECIMAL占用更少空间。
-
精确类型
- DECIMAL
- 存储精确的小数和支持精确的计算。
- MySQL4.1及以前版本以浮点运算来实现DECIAML计算，精度不足，在这些版本中，只作为存储类型。
- 允许存储254个数字，并且保存为未压缩的字符串（每个数字一个字节）。
- 并不能实际计算，只用作存储。
- CPU不支持对DECIMAL的直接计算，MySQL5.0及以上版本中，MySQL服务器自身实现了DECIMAL的高精度计算。
- CPU支持原生浮点运行，所以原生浮点运行更快。
- 允许最多65个数字
- MySQL4.1及以前版本以浮点运算来实现DECIAML计算，精度不足，在这些版本中，只作为存储类型。
- 可指定精度，即小数点前后所允许的最大位数,这会影响列的空间消耗。
- MySQL5.0及以上版本中将数字打包到一个二进制字符串中（每4个字节存9个数字）,DECIMAL(18,9) 小数点两边各存储9个数字，：小数点前用4个字节，小数点后用4个字节，小数点本身用一个字节，共9字节
- 因需要额外的空间和计算开销，所以只有要求精度时使用该类型，如财务计算。
- 数据量较大时，可考虑使用BIGINT替代DECIMAL，可避免浮点计算不精确和DECIMAL精确计算代价高的问题
- 存储精确的小数和支持精确的计算。
- DECIMAL
-
FLOAT
- 4个字节存储空间
-
DOUBLE
- 8个字节存储空间
- MySQL使用DOUBLE作为内部浮点计算的类型
字符串
-
不同存储引擎下，字符串在硬盘中的存储不一定是相同的
-
存储引擎存储VARCHAR和CHAR的方式在内存中和磁盘上可能不一样，所以MySQL服务器从存储引擎读取的值可能需要转换成另一种存储格式。
-
VARCHAR
- 可边长字符串，最常见的字符串类型。
- 比定长类型更省空间，仅使用必要的空间（字符串越短适用空间越少）。
- 如果表使用 ROW_FORMAT=FIXED创建，则会使用定长存储，浪费空间
- 使用额外1或2个字节记录字符串长度
- 最大长度小于等于255字节，则用1个字节表示
- VARCHAR(10) 使用11个字节
- 最大长度大于255，则用2个字节表示
- VARCHAR(1000) 使用1002个字节
- 最大长度小于等于255字节，则用1个字节表示
- 节省了存储空间，所以对性能也有帮助，但UPDATE时可能使行变长，导致需要额外的工作
- 如果行变成，且页内没有更多可用的空间
- MyISAM：将行拆成不同片段存储
- InnoDB：分裂页来使行可以放进页内
- 如果行变成，且页内没有更多可用的空间
- 适用
- 字符串列的最大长度比平均长度大很多
- 列的更新很少，所以碎片不是问题
- 使用了UTF-8一样的复杂字符集（每个字符使用不同的字节进行存储）
- 定义范围时越大越好嘛？
- VARCHAR(10)和VARCHAR(1000)在存储hello时，空间开销一样。
- 更长的列会消耗更大的内存，MySQL通常分配固定大小的内存来保存磁盘中取出的值。
- 最好的策略使只分配真正需要的空间
- 末尾空格的处理
- 5.0及以上版，MySQL在存储和检索时会保留末尾空格。
- 4.1及以前版本，MySQL会剔除末尾空格。
-
CHAR
- 定长的。根据定义的长度分配空间。
- 会删除值末尾的所有空格，因为CHAR值根据需要采用空格对缺失位进行填充。
- 适用
- 存储很短的字符串，所有值都接近同一长度。
- 存储密码的MD5，因为是一个定长值
- 经常变更的数据。因为定长所以不易产生碎片。
- 非常短的列，也更有效率
- CHAR(1)需要1个字节
- VARCHAR(1)需要两个字节（1个字节存储字符串长度）
- 存储很短的字符串，所有值都接近同一长度。
-
VARBINARY
- 存储二进制字符串，存储的是字节码而不是字符。
- 二进制字符串比较的优势不仅体现在大小写敏感上，每次按一个字节的数值比较，所以更快。
-
BINARY
- 存储二进制字符串，存储的是字节码而不是字符。
- 二进制字符串比较的优势不仅体现在大小写敏感上，每次按一个字节的数值比较，所以更快。
- 采用\0填充而不是空格，检索时也不会去掉空格。
-
TEXT
- 为存储很大的数据而设计
- 字符方式存储
- 当作一个独立对象处理。存储引擎在存储时作特殊处理。
- 值太大时，会使用专门的外部存储区域存储，此时每个值在行内需要1~4个字节存储指针，然后在外部存储区域存储实际的值。
- 有字符集和排序规则（与BLOB的唯一不同点，因为BLOB适用二进制格式存储）
- 只对前max_sort_length字节作排序
- TINYTEXT
- SMALLTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
-
BLOB
- 为存储很大的数据而设计
- 二进制方式存储
- 当作一个独立对象处理。存储引擎在存储时作特殊处理。
- 值太大时，会使用专门的外部存储区域存储，此时每个值在行内需要1~4个字节存储指针，然后在外部存储区域存储实际的值。
- 没有字符集和排序规则（与TEXT的唯一不同点）
- TINYBLOB
- SMALLBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
对于完全随机的字符串的注意点
MD5()、SHA1()、UUID()产生的字符串会任意分布在很大的空间中，这会导致INSERT以及SELECT变得很慢
使用枚举代替字符串类型
- 存储枚举时非常紧凑，会根据列表值的数量压缩到一个或两个字节中。
- MySQL内部会将每个值在列表中的位置保存为整数，并且在.frm文件中保存映射关系。
- 缺点
- 字符串列表时固定的，若要修改枚举，必须通过修改表结构。
- 因为把每个枚举保存为整数，并且必须进行查找才可转换为字符串，所以有一些性能开销。
- 特点条件下 char/varchar 与枚举值关联可能比直接关联char/varchar慢
- 应使用ENUM和ENUM进行关联。
- 基于数字关联比基于字符串关联性能高得多。
- 适合存储固定信息
- 如性别，产品类型
CREATE TEBLE enum_test(
e ENUM('fish','apple','dog') NOT NULL
);
INSERT INTO enum_test(e) VALUES('fish'),('apple'),('dog')
SELECT e + 0 FROM enum_test;
1
3
2
-- 不建议使用数组作枚举常量容易混淆，如ENUM('1','2','3')
-- 并且时按照内部存储的整数排序的。
SELECT e FROM enum_test ORDER BY e;
fish
apple
dog
日期和时间类型
- MySQL可存储的最小时间粒度为秒（MariaDB支持微妙级别的时间类型）。
- MySQL可以适用微秒级别的力度进行临时计算。
- DATETIME
- 保存大范围的值：1001年 - 9999年，精度为秒。
- 将日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。
- 使用8字节存储空间。
- 默认情况下 MySQL以一种可排序的，无歧义的格式显示DATATIME值
- 2008-01-01 11：21：21 ANSI标准定义的时间日期表示法
- TIMESTAMP
- 保存了格林威治时间以来的秒数，和UNIX时间戳相同。
- 使用4字节存储空间。范围小的多：1970-2038
- 显示的值依赖于时区。
- MySQL服务器、操作系统、客户端都有时区设置。
- 默认值为当前时间。
- 应尽量使用该类型，因为占用空间更少。
- MySQL函数
- FROM_UNIXTIME()：unix时间戳转换为日期
- UNIX_TIMESTAMP()：日期转换为时间戳
- 对微秒的处理
- 可使用BIGINT类型存储微秒级别的时间戳
- 或使用DOUBLE存储秒之后的小数部分
- MariaDB支持微妙级别的时间类型，使用MariaDB代替MySQL
505
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
