比来在学习MySQL优化方面的见识。Mysql教程栏目就数据类型和schema方面的优化进行介绍。

1. 选中优化的数据类型

MySQL支撑的数据类型有许多，而怎样选中出准确的数据类型，关于机能是至关重要的。下列几个准则能够帮忙肯定数据类型：

1. 更小的平常更好

   应尽可能运用可以准确存储数据的最小数据类型，够用就好。这样将占用更少的磁盘、内存和缓存，而在处置时也会耗时更少。

2. 简略就好

   当两种数据类型都能胜任一个字段的存储工作时，选中简略的那一方，往往是最佳的选中。例如整型和字符串，因为整型的操纵代价要小于字符，所以当在两者之间选中时，选中整型平常能够获得更好的机能。

3. 尽量以免NULL

   当列可为NULL时，关于MySQL来说，在索引和值比拼等方面需要做更多的工作，虽然对机能的影响不是很大，但也应尽量以免设计为可为NULL。

除了以上准则，在选中数据类型时，需遵循的步骤：第一肯定合适的大类型，例如数据、字符串、工夫等；然后再选中具体的类型。下面将计议大类型下的一些具体类型，第一是数字，有两品种型：整数和实数。

1.1 整数类型

整数类型和所占用的空间如下：

整数类型所能存储的范畴和空间大小有关：-2^(N-1)至2^(N-1)-1，其中N为空间大小的位数。

整数类型拥有UNSIGNED的可选属性，当声明时，表示不允许负数，则存储范畴变为：0至2^(N)-1，扩充了一倍。

在MySQL中，还可认为整数类型指定宽度，例如INT(1)，但这样的意义并不大，并不会限定值的合法范畴，仍能存储-2^31至2^31-1的值，所影响的是与MySQL的交互工具显示字符的个数。

1.2 实数类型

实数类型的对照如下：

从上面可以看出，FLOAT和DOUBLE都有牢固的空间大小，但同时因为是运用规范的浮点运算，所以只能近似盘算。而DECIMAL则可以实现精准盘算，与此同时占用的空间会相较更大，所耗费的盘算开销也更多。

DECIMAL所占空间大小与指定的精度有关，例如DECIMAL(M,D)：

• M为整个数字的最大长度，取值范畴为[1, 65]，默许值为10；
• D为小数点后的长度，取值范畴为[0, 30]，且D <= M，默认值为0。

MySQL在存储DECIMAL类型时会作为二进制字符串存储，每4个字节存9个数字，当不够9位时，数字的占用空间如下：

小数点先后将离别存储，同时小数点也要占1个字节。下面举两个盘算的例子：

1. DECIMAL(18, 9)：整数局部长度为9，占用4个字节。小数局部长度为9，占用4个字节。同时加上小数点1个字节，则总共占用9个字节。
2. 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的适用处景：

1. 最大长度比均匀长度大许多；
2. 列的更新少，以免碎片；
3. 运用复杂的字符集，如UTF-8，每个字符能运用不一样的字节存储。

CHAR则为定长字符串，依据定义的字符串长度分配脚够的空间，适用处景：

1. 长度短；
2. 长度附近，例如MD5；
3. 时常更新。

除了VARCHAR和CHAR，针对存储大字符串，可以运用BLOB和TEXT类型。BLOB和TEXT的区别在于，BLOB是以二进制方式存储，而TEXT是以字符方式存储。这也致使，BLOB类型的数据没有字符集的概念，没法按字符排序，而TEXT类型则有字符集的概念，可以按字符排序。两者的运用场景，也由存储格局决议了，当存储二进制数据时，例如图片，应运用BLOB，而存储文本时，例如文章，则应运用TEXT类型。

1.4 日期和工夫类型

MySQL中所能存储的最小工夫粒度为秒，常用的日期类型有DATETIME和TIMESTAMP。

TIMESTAMP显示的值将依赖于时区，意味在不一样时区查询到的值将不同。除了以上列出的不一样，TIMESTAMP还拥有一个特别属性，在插入和更新时，要是没有指定首先个TIMESTAMP列的值，将会设定这个列的值为目前工夫。

我们在开发历程中，应尽量运用TIMESTAMP，主如果由于其空间大小仅需DATETIME的一半，空间效率更高。

要是我们想存储的日期和工夫精准到秒之后，怎么办？因为MySQL并未供给，所以我们可以运用BIGINT存储奥妙级另外工夫戳，或者运用DOUBLE存储秒之后的小数局部。

1.5 选中标识符

平常来说整数是标识符的最佳选中，主如果由于其简略，盘算快，且可运用AUTO_INCREMENT。

2. 范式和反范式

简略来说，范式就是一张数据表的表构造所相符的某种设计规范的级别。首先范式，属性不成分割，此刻的RDBMS系统建成的表都是相符首先范式的。而第二范式，则是消弭非主属性对码（可以了解为主键）的局部依赖。第三范式消弭非主属性对码的通报依赖。具体的介绍，可以读读知乎上的这个答复（https://www.zhihu.com/question/24696366/answer/29189700）

严厉范式化的数据库中，每个事实数据会涌现且只涌现一次，不会涌现数据冗余，这样所能带能带来的益处有：

1. 更新操纵更快；
2. 修改更少的数据；
3. 表更小，更好地放内存中，施行操纵更快；
4. 更少需要DISTINCT或GROUP BY。

但也因为数据分散存在各张表中，查询时需要对表进行关联。而反范式的长处则是不消进行关联，将数据冗余存储。

在现实利用中，不会涌现完全的范式化或完全的反范式化，经常需要混用范式和反范式，运用局部范式化的schema，往往是最佳的选中。对于数据库设计，在网上看到这样一段话，大家可以感想下。

数据库设计应当分为三个境地：

首先境地：刚入门数据库设计，范式的重要性还未深刻了解。这时候涌现的反范式设计，个别会出题目。

第二境地：随着碰到题目解决题目，慢慢理解到范式的真正益处，从而能迅速设计出低冗余、高效率的数据库。

第三境地：再经过N年的熬炼，是一定会觉察范式的局限性的。此时再去打破范式，设计更合理的反范式局部。

范式就像武侠里面的招数，初学者贪图不按招数来，只能死的很为难。究竟招数都是高手总结归纳的英华。而随着武功提高，招数熟练之后，必定是发明招数的局限性，要末忘掉招数，要末自创招数。

只有努力，加上多熬几年，总能达到第二个境地，总会觉得范式是经典。此时能不外分依赖范式，迅速冲破范式局限性的人，天然是高手。

4. 缓存表和汇总表

除了上陈说到的反范式，在表中存储冗余数据，我们还可以新建一张完全独立的汇总表或缓存表，来知足检索的需要。

缓存表，指的是存储可以从schema其他表中猎取数据的表，也就是逻辑上冗余的数据。而汇总表，则指的是存储运用GROUP BY等语句聚合数据，盘算出的不冗余的数据。

缓存表，可用于优化搜寻和检索查询语句，这里可以运用的技巧有对缓存表运用不一样的存储引擎，例如主表运用InnoDB，而缓存表则可运用MyISAM，获得更小的索引占用空间。甚至可以将缓存表放到专门的搜寻系统中，例如Lucene。

汇总表，则是为了以免实时盘算统计值所带来的昂扬代价，代价来自两方面，一是需要扫描表中的大局部数据，二是创立特定的索引，会对UPDATE操纵有影响。例如，查询微信已往24小时的伴侣圈数目，则可牢固每1小时扫描全表，统计后写一笔记录到汇总表，当查询时，只需查询汇总表上最新的24笔记录，而无须每次查询时都去扫描全表进行统计。

在运用缓存表和汇总表时，必需决议是实时保护数据还是按期重建，这取决于我们的需求。按期重建比拟实时保护，能节俭更多的资源，表的碎片更少。而在重建时，我们仍需保障数据在操纵时可用，需要通过“影子表”来实现。在真实表后新建一张影子表，当添补好数据后，通过原子的重命名操纵来切换影子表和原表。

5. 加速ALTER TABLE操纵的速度

当MySQL在施行ALTER TABLE操纵时，往往是创建一张表，然后把数据从旧表查出并插入到新表中，再删除旧表，要是表很大，这样需要破费很长工夫，且会致使MySQL的办事中止。为了不办事中止，平常可以运用两种技巧：

1. 在一台不供给办事的机器上施行ALTER TABLE操纵，然后再与供给办事的主库进行切换；
2. “影子拷贝”，创立一张与原表无关的新表，在数据迁移完成后，通过重命名操纵进行切换。

但也不是所有的ALTER TABLE操纵会引起表重建，例如在修改字段的默许值时，运用MODIFY COLUMN会进行表重建，而运用ALTER COLUMN则不会进行表重建，操纵速度很快。这是由于ALTER COLUMN在修改默许值时，会直接修改了存在表的.frm文件（存储字段的默许值），而并未重建表。

更多相干免费学习举荐：mysql教程(视频)

以上就是MySQL中的数据类型和schema优化的细致内容，更多请关注 百分百源码网 其它相干文章！