本文主要介绍了数据库--MySQL数据表设计优化，通过具体的内容展现，希望对大家数据库MySQL的学习有所帮助。

一、选择优化的数据类型

MySQL支持数据类型非常多，为了获取高性能选对正确的数据类型非常重要，下面是优化的几个原则：

更小通常更好

更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。如果无法确定哪个数据类型最好，就选择你认为不会超过范围的最小类型。

简单即可

简单数据类型的操作通常需要更少的CPU周期。整型比字符操作代价更低，因为字符串和校对规则（排序规则）使字符比较比整形比较更复杂。

尽量避免NULL

很多表都包含可为NULL (空值)的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。因为可以为NULL值的列使得索引、索引统计和值比较都更为复杂。

具体选择数据类型，MySQL数据类型可以存储相同类型的数据，只是存储的长度和范围不同，允许的精度不同，及需要的物理空间（磁盘和内存空间）不同。例如，DATETIME和TIMESAMP列都可以存储相同类型的数据： 时间和日期，精确到秒。然而TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。另一方面，TIMESTAMP允许的时间范围要小得多，有时候它的特殊能力会成为障碍。

整数类型

N为存储空间的位数，有符号和无符号类型使用相同的存储空间，并具有相同的性能。

MySQL可以为整数类型指定宽度，例如INT(10)，对于大多数应用这是没有意义的：它不会限制值的范围，只是规定MySQL的一些交互工具用来显示字符的个数。

字符串类型

VARCHAR

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间。例如，越短的字符串使用越少的空间。

VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latin1字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000) 的列则需要1002个字节，因为需要2个字节存储长度信息。

这些情况下使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多；

UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用

的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。其他一些存储引擎也许从不在原数据位置更新数据。

CHAR

CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格。

CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR 比VARCHAR在存储空间上也更有效率。

BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

日期和时间类型

DATETIME

这个类型能保存大范围的值，从1001年到9999年，精度为秒。

TIMESTAMP

TIMETAMP 类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数，它和UNIX时间戳相同。TIMESTAMP 只使用4个字节的存储空间，因此它的范围比DATETIME小得多：只能表示从1970 年到2038年。

二、范式和反范式

2.1 范式

第一范式（1NF）：

无重复列，即表中每一列都是不可分割的，不满足第一范式的数据库不是关系型数据库。

例子：联系人表(用户名，用户信息)

第二范式（2NF）：

不存在仅依赖于关键一部分的属性（不能存在部分依赖于主键）。

例子：选课关系(学号，课程名称，成绩，学分)，学号与课程名称是主键，其不满足2NF，因为课程名称->学分

第三范式（3NF）：

属性不传递依赖于其他非主属性，非主键必须直接依赖于主键而不能传递依赖。

例子：学生表(学号，姓名，学院编号，学院名称)，学号是主键,姓名、学院编号、学院名称都完全依赖于学号,满足2NF,但不满足3NF,因为学院名称直接依赖的是学院编号 ,它是通过传递才依赖于主键。

2.2 范式优缺点

范式化的更新操作通常比反范式化要快。

当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。

范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。

很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUPBY语句。

范式化设计的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

2.3 反范式

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率。

2.3 反范式优缺点

数据都在一张表里，可以很好避免关联。

单独表可以使用更有效的索引策略。

2.4 混用范式化和反范式化

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在MySQL 5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。

三、使用缓存表和汇总表

汇总表

以网站为例，假设需要计算之前24小时内发送的消息数。在一个很繁忙的网站不可能维护一个实时精确的计数器。作为替代方案，可以每小时生成一张汇总表。这样也许一条简单的查询就可以做到，并且比实时维护计数器要高效得多。缺点是计数器并不是100%精确。

如果必须获得过去24小时准确的消息发送数量(没有遗漏)，有另外-种选择。以每小时汇总表为基础，把前23个完整的小时的统计表中的计数全部加起来，最后再加上开始阶段和结束阶段不完整的小时内的计数。

缓存表

例如，可能会需要很多不同的索引组合来加速各种类型的查询。这些矛盾的需求有时需要创建一张只包含主表中部分列的缓存表。一个有用的技巧是对缓存表使用不同的存储引擎。例如，如果主表使用InnoDB，用MyISAM作为缓存表的引擎将会得到更小的索引占用空间，并且可以做全文搜索。有时甚至想把整个表导出MySQL， 插人到专门的搜索系统中获得更高的搜索效率，例如Lucene或者Sphinx搜索引擎。

延伸的高级用法可以使用物化视图和计数器表

我是小职，记得找我

✅ 解锁高薪工作

✅ 免费获取基础课程·答疑解惑·职业测评