MySQL中的数据类型和schema优化

---

最近在学习mysql优化方面的知识。mysql教程栏目就数据类型和schema方面的优化进行介绍。

1. 选择优化的数据类型

MySQL支持的数据类型有很多，而如何选择出正确的数据类型，对于性能是至关重要的。以下几个原则能够帮助确定数据类型：

1. 更小的通常更好

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

2. 简单就好

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

3. 尽量避免NULL

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

除了以上原则，在选择数据类型时，需遵循的步骤：首先确定合适的大类型，例如数据、字符串、时间等；然后再选择具体的类型。下面将讨论大类型下的一些具体类型，首先是数字，有两种类型：整数和实数。

1.1 整数类型

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

整数类型	空间大小（bit）
TINYINT	8
SMALLINT	16
MEDIUMINT	24
INT	32
BIGINT	64

整数类型所能存储的范围和空间大小有关：-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）	取值范围	计算精度
FLOAT	4	负数：-3.4E+38～-1.17E-38；非负数：0、1.17E-38~3.4E+38	近似计算
DOUBLE	8	负数：-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

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

数字个数	占用空间（Byte）
1、2	1
3、4	2
5、6	3
7、8	4

小数点前后将分别存储，同时小数点也要占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的适用场景：

网龙b2b仿阿里巴巴电子商务平台

本系统经过多次升级改造，系统内核经过多次优化组合，已经具备相对比较方便快捷的个性化定制的特性，用户部署完毕以后，按照自己的运营要求，可实现快速定制会费管理，支持在线缴费和退费功能财富中心，管理会员的诚信度数据单客户多用户登录管理全部信息支持审批和排名不同的会员级别有不同的信息发布权限企业站单独生成，企业自主决定更新企业站信息留言、询价、报价统一管理，分系统查看分类信息参数化管理，支持多样分类信息，

0 查看详情

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。

类型	存储内容	空间大小（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）

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

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优化的详细内容，更多请关注其它相关文章！

# 数据类型  # 新都网络seo  # 推广网站找公司  # seo培训速成班运营  # 金凤区网站推广排名  # seo链接方法  # 如何分析一个网站SEO  # 滁州360网站推广公司  # 则可  # 长度为  # 都是  # 可以使用  # 但也  # 则是  # 整型  # 更少  # 阿里巴巴  # 镜像  # schema优化  # MySQL  # 网站建设seo怎么优化  # 品牌推广微信营销策略  # 大东区电商网站建设报价 

相关栏目： 【 Google疑问12 】 【 Facebook疑问10 】 【 优化推广96088 】 【 技术知识133117 】 【 IDC资讯59369 】 【 网络运营7196 】 【 IT资讯61894 】

相关推荐： c++中的const关键字用法大全_c++ const正确使用指南  mysql中如何配置字符集和排序规则_mysql字符集排序配置  家里的小飞虫总是不断，用什么方法可以彻底根除？  Flexbox布局：实现粘性导航与底部页脚的完美结合  以下哪一个是适应长期护理制度发展而设立的新职业  《oppo商城》维修服务位置  解决jQuery多计算器输入字段冲突的教程  芒果TV官网登录入口 芒果TV官方网站登录入口  firefox火狐浏览器最新官网主页_ firefox火狐浏览器平台入口直达官方链接  Lar*el Eloquent：高效删除多对多关系中无关联子记录的父模型  Flexbox布局中Stencil组件宽度不显示问题解析与:host尺寸控制  漫蛙manwa官网浏览入口_漫蛙漫画网页版访问链接  汽水音乐网页端访问 汽水音乐官方网页直达  抖音网页版官方链接 抖音网页版官网链接入口  酷狗音乐多音轨设置教程  怎样让Windows 11的开始菜单恢复经典样式_Open-Shell工具使用指南【怀旧】  深入理解随机递归函数的确定性：内部节点、叶节点与时间复杂度分析  iSpring三分屏制作教程  2025SNH48年度青春盛典门票价格及购买方式  J*a里如何处理ArithmeticException并防止除零_算术异常防护策略解析  LINUX怎么查看显卡信息_LINUX查看GPU状态  Python类装饰器动态修改方法时的类型提示：Mypy插件实现精确静态分析  如何快速去除厨房重油污？ 2025年最好用的厨房清洁剂推荐  解决Flex容器横向滚动内容截断与偏移问题  我的世界游戏平台入口 我的世界官方官网直达链接  餐馆菜篮选购指南  奥克斯空调不制热啥毛病_奥克斯空调不制热原因分析及解决技巧  realme 10 Pro息屏方案_realme 10 Pro省电策略  《密马》发布账号方法  Python项目中的条件导入：解决跨模块依赖问题  优化响应式标题底部边框：CSS实现技巧与最佳实践  招商淘客入门指南  byrutor直接访问入口 byrutor官方游戏库  Go Template中优雅处理循环最后一项：自定义函数实践  Python对象引用与属性赋值：理解链表中的行为  全球各国上班时间表外贸邮件时间  iPhone14无法连接蓝牙设备如何解决  Mac hosts文件在哪里_Mac修改hosts文件详细教程  123网页端官方登录页 123邮箱网页版即时通讯服务  淘口令快速解析技巧  如何在mysql中使用索引提示_mysql索引提示优化方法  谷歌邮箱官方入口链接 谷歌邮箱网页版电脑端快速登录  C++ virtual析构函数作用_C++基类虚析构函数防止内存泄漏  网站体验不好=浪费钱:如何提升-用户体验效果差  重返未来：1999卡戎全方位攻略  C++ optional用法详解_C++17处理可能为空的返回值  PHP与SQL实践：高效实现数据复制与特定列值修改  批改网网页版登录 批改网电脑版学生登录入口  除了Copilot，还有哪些值得一试的VS Code AI插件？  蛙漫2(台版)正版官网 2025免费网页版分享 

 2020-10-26