共计 4189 个字符，预计需要花费 11 分钟才能阅读完成。
自动写代码机器人，免费开通
丸趣 TV 小编给大家分享一下 MySQL 中的数据类型和 schema 优化是什么，希望大家阅读完这篇文章后大所收获，下面让我们一起去探讨吧！
1. 选择优化的数据类型
MySQL 支持的数据类型有很多，而如何选择出正确的数据类型，对于性能是至关重要的。以下几个原则能够帮助确定数据类型：
更小的通常更好
应尽可能使用可以正确存储数据的最小数据类型，够用就好。这样将占用更少的磁盘、内存和缓存，而在处理时也会耗时更少。
简单就好
当两种数据类型都能胜任一个字段的存储工作时，选择简单的那一方，往往是最好的选择。例如整型和字符串，由于整型的操作代价要小于字符，所以当在两者之间选择时，选择整型通常能够获得更好的性能。
尽量避免 NULL
当列可为 NULL 时，对于 MySQL 来说，在索引和值比较等方面需要做更多的工作，虽然对性能的影响不是很大，但也应尽量避免设计为可为 NULL。
除了以上原则，在选择数据类型时，需遵循的步骤：首先确定合适的大类型，例如数据、字符串、时间等；然后再选择具体的类型。下面将讨论大类型下的一些具体类型，首先是数字，有两种类型：整数和实数。
1.1 整数类型
整数类型和所占用的空间如下：
整数类型空间大小（bit）TINYINT8SMALLINT16MEDIUMINT24INT32BIGINT64
整数类型所能存储的范围和空间大小有关：-2^(N-1) 至 2^(N-1)-1，其中 N 为空间大小的位数。
整数类型具有 UNSIGNED 的可选属性，当声明时，表示不允许负数，则存储范围变为：0 至 2^(N)-1，扩大了一倍。
在 MySQL 中，还可以为整数类型指定宽度，例如 INT(1)，但这样的意义并不大，并不会限制值的合法范围，仍能存储 -2^31 至 2^31- 1 的值，所影响的是与 MySQL 的交互工具显示字符的个数。
1.2 实数类型
实数类型的对比如下：
实数类型空间大小（Byte）取值范围计算精度 FLOAT4 负数：-3.4E+38～-1.17E-38；非负数：0、1.17E-38~3.4E+38 近似计算 DOUBLE8 负数：-1.79E+308～-2.22E-308；非负数：0、2.22E-308~1.79E+308 近似计算 DECIMAL 与精度有关同 DOUBLE 精确计算
从上面可以看出，FLOAT 和 DOUBLE 都有固定的空间大小，但同时由于是使用标准的浮点运算，所以只能近似计算。而 DECIMAL 则可以实现精确计算，与此同时占用的空间会相较更大，所耗费的计算开销也更多。
DECIMAL 所占空间大小与指定的精度有关，例如 DECIMAL(M,D)：
M 为整个数字的最大长度，取值范围为 [1, 65]，默认值为 10；D 为小数点后的长度，取值范围为 [0, 30]，且 D = M，默认值为 0。
MySQL 在存储 DECIMAL 类型时会作为二进制字符串存储，每 4 个字节存 9 个数字，当不足 9 位时，数字的占用空间如下：
数字个数占用空间（Byte）1、213、425、637、84
小数点前后将分别存储，同时小数点也要占 1 个字节。下面举两个计算的例子：
DECIMAL(18, 9)：整数部分长度为 9，占用 4 个字节。小数部分长度为 9，占用 4 个字节。同时加上小数点 1 个字节，则总共占用 9 个字节。DECIMAL(20, 9)：整数部分长度为 14，占用 7（4+3）个字节。小数部分长度为 9，占用 4 个字节。同时加上小数点 1 个字节，则总共占用 12 个字节。
可以看出 DECIMAL 的空间占用还是很大的，因此只有当需要对小数进行精确计算时，才需要使用 DECIMAL。除此之外，我们还可以使用 BIGINT 代替 DECIMAL，例如需要保证小数点后 5 位的计算，可以将值乘上 10 的 5 次方后作为 BIGINT 存储，这样能同时避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题。
1.3 字符串类型
最常用的字符串类型当属 VARCHAR 和 CHAR。VARCHAR 作为可变长字符串，会使用 1 或 2 个额外字节记录字符串的长度，当最大长度未超过 255 时，只需 1 个字节记录长度，超过 255，则需 2 个字节。VARCHAR 的适用场景：
最大长度比平均长度大很多；列的更新少，避免碎片；使用复杂的字符集，如 UTF-8，每个字符能使用不同的字节存储。
CHAR 则为定长字符串，根据定义的字符串长度分配足够的空间，适用场景：
长度短；长度相近，例如 MD5；经常更新。
除了 VARCHAR 和 CHAR，针对存储大字符串，可以使用 BLOB 和 TEXT 类型。BLOB 和 TEXT 的区别在于，BLOB 是以二进制方式存储，而 TEXT 是以字符方式存储。这也导致，BLOB 类型的数据没有字符集的概念，无法按字符排序，而 TEXT 类型则有字符集的概念，可以按字符排序。两者的使用场景，也由存储格式决定了，当存储二进制数据时，例如图片，应使用 BLOB，而存储文本时，例如文章，则应使用 TEXT 类型。
1.4 日期和时间类型
MySQL 中所能存储的最小时间粒度为秒，常用的日期类型有 DATETIME 和 TIMESTAMP。
类型存储内容空间大小（Byte）时区概念 DATETIME 格式为 YYYYMMDDHHMMSS 的整数 8 无 TIMESTAMP 从 1970 年 1 月 1 日零点以来的秒数 4 有
TIMESTAMP 显示的值将依赖于时区，意味在不同时区查询到的值将不一样。除了以上列出的不同，TIMESTAMP 还具有一个特殊属性，在插入和更新时，如果没有指定第一个 TIMESTAMP 列的值，将会设置这个列的值为当前时间。
我们在开发过程中，应尽量使用 TIMESTAMP，主要是因为其空间大小仅需 DATETIME 的一半，空间效率更高。
如果我们想存储的日期和时间精确到秒之后，怎么办？由于 MySQL 并未提供，所以我们可以使用 BIGINT 存储微妙级别的时间戳，或者使用 DOUBLE 存储秒之后的小数部分。
1.5 选择标识符
通常来说整数是标识符的最好选择，主要是因为其简单，计算快，且可使用 AUTO_INCREMENT。
2. 范式和反范式
简单来说，范式就是一张数据表的表结构所符合的某种设计标准的级别。第一范式，属性不可分割，现在的 RDBMS 系统建成的表都是符合第一范式的。而第二范式，则是消除非主属性对码（可以理解为主键）的部分依赖。第三范式消除非主属性对码的传递依赖。具体的介绍，可以读读知乎上的这个回答（https://www.zhihu.com/question/24696366/answer/29189700）
严格范式化的数据库中，每个事实数据会出现且只出现一次，不会出现数据冗余，这样所能带能带来的好处有：
更新操作更快；修改更少的数据；表更小，更好地放内存中，执行操作更快；更少需要 DISTINCT 或 GROUP BY。
但也由于数据分散存在各张表中，查询时需要对表进行关联。而反范式的优点则是不用进行关联，将数据冗余存储。
在实际应用中，不会出现完全的范式化或完全的反范式化，时常需要混用范式和反范式，使用部分范式化的 schema，往往是最好的选择。关于数据库设计，在网上看到这样一段话，大家可以感受下。
数据库设计应该分为三个境界：
第一境界：刚入门数据库设计，范式的重要性还未深刻理解。这时候出现的反范式设计，一般会出问题。
第二境界：随着遇到问题解决问题，渐渐了解到范式的真正好处，从而能快速设计出低冗余、高效率的数据库。
第三境界：再经过 N 年的锻炼，是一定会发觉范式的局限性的。此时再去打破范式，设计更合理的反范式部分。
范式就像武侠里面的招数，初学者妄想不按招数来，只能死的很难堪。毕竟招数都是高手总结归纳的精华。而随着武功提高，招数熟练之后，必然是发现招数的局限性，要么忘掉招数，要么自创招数。
只要努力，加上多熬几年，总能达到第二个境界，总会觉得范式是经典。此时能不过分依赖范式，快速突破范式局限性的人，自然是高手。
4. 缓存表和汇总表
除了上述说到的反范式，在表中存储冗余数据，我们还可以创建一张完全独立的汇总表或缓存表，来满足检索的需要。
缓存表，指的是存储可以从 schema 其他表中获取数据的表，也就是逻辑上冗余的数据。而汇总表，则指的是存储使用 GROUP BY 等语句聚合数据，计算出的不冗余的数据。
缓存表，可用于优化搜索和检索查询语句，这里可以使用的技巧有对缓存表使用不同的存储引擎，例如主表使用 InnoDB，而缓存表则可使用 MyISAM，获得更小的索引占用空间。甚至可以将缓存表放到专门的搜索系统中，例如 Lucene。
汇总表，则是为了避免实时计算统计值所带来的高昂代价，代价来自两方面，一是需要扫描表中的大部分数据，二是建立特定的索引，会对 UPDATE 操作有影响。例如，查询微信过去 24 小时的朋友圈数量，则可固定每 1 小时扫描全表，统计后写一条记录到汇总表，当查询时，只需查询汇总表上最新的 24 条记录，而不必每次查询时都去扫描全表进行统计。
在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建，这取决于我们的需求。定期重建相比实时维护，能节省更多的资源，表的碎片更少。而在重建时，我们仍需保证数据在操作时可用，需要通过“影子表”来实现。在真实表后创建一张影子表，当填充好数据后，通过原子的重命名操作来切换影子表和原表。
5. 加快 ALTER TABLE 操作的速度
当 MySQL 在执行 ALTER TABLE 操作时，往往是新建一张表，然后把数据从旧表查出并插入到新表中，再删除旧表，如果表很大，这样需要花费很长时间，且会导致 MySQL 的服务中断。为了避免服务中断，通常可以使用两种技巧：
在一台不提供服务的机器上执行 ALTER TABLE 操作，然后再与提供服务的主库进行切换；“影子拷贝”，建立一张与原表无关的新表，在数据迁移完成后，通过重命名操作进行切换。
但也不是所有的 ALTER TABLE 操作会引起表重建，例如在修改字段的默认值时，使用 MODIFY COLUMN 会进行表重建，而使用 ALTER COLUMN 则不会进行表重建，操作速度很快。这是因为 ALTER COLUMN 在修改默认值时，会直接修改了存在表的.frm 文件（存储字段的默认值），而并未重建表。
看完了这篇文章，相信你对 MySQL 中的数据类型和 schema 优化是什么有了一定的了解，想了解更多相关知识，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
向 AI 问一下细节
丸趣 TV 网 – 提供最优质的资源集合！
