《高性能MySQL》笔记——MySQL建表数据类型的选择

￡神魔★判官ぃ 2022-01-27 06:19 235阅读 0赞

ZT: [https://www.cnblogs.com/woods1815/p/9926330.html][https_www.cnblogs.com_woods1815_p_9926330.html]

前段时间看了《高性能MySQL》中的选择优化的数据类型，这里主要是做一下笔记。

## 首先数据选择有几个简单原则： ##

*  **更小的通常更好。**一般情况下，应该尽量使用可以正确存储数据的最小数据类型。例如只需要存 0~200，tinyint unsigned 更好。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更少。
 *  **简单就好。**简单数据类型的操作通常需要更少的 CPU 周期。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较 比 整型比较更复杂。这里有两个例子：一个是应该使用 MySQL 内建的类型（date, time, datatime）而不是字符串来存储日期和时间，另一个是应该用无符号整型存储 IP 地址。
 *  **尽量避免NULL。**通常情况下最好指定列为 NOT NULL，除非真的需要存储 NULL 值。如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。特别是计划在列上建索引，就应该尽量避免设计成可为 NULL 的列。

## 整数类型：（tinyint，smallint，mediumint，int，bigint） ##

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td>存储（字节）</td> 
   <td>存储（位）</td> 
   <td>有符号范围</td> 
   <td>无符号范围</td> 
  </tr> 
  <tr> 
   <td>TINYINT</td> 
   <td>1</td> 
   <td>8</td> 
   <td>-128 ~127</td> 
   <td>0~255</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td>2</td> 
   <td>16</td> 
   <td>-32768~32767</td> 
   <td>0~65535</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td>3</td> 
   <td>24</td> 
   <td>-8388608~8388607</td> 
   <td>0~16777215</td> 
  </tr> 
  <tr> 
   <td>INT</td> 
   <td>4</td> 
   <td>32</td> 
   <td>-2147483648~2147483647</td> 
   <td>0~4294967295</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td>8</td> 
   <td>64</td> 
   <td>-2^63~2^63-1</td> 
   <td>0~2^64-1</td> 
  </tr> 
 </tbody> 
</table>

*  整数类型有可选的 unsigned 属性，表示不允许负值，这大致可以使正数的上限提高一倍。主键自增 id 适合设置为 unsigned 属性的 int 类型。
 *  MySQL 可以为整数类型指定宽度，例如 int(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了 MySQL 的一些交互工具（例如 MySQL 命令行客户端）用来显示字符的个数。对于存储和计算来说，int(1) 和 int(20) 是相同的。

## 实数类型：（float，double，decimal） ##

*  float，double 属于浮点类型（近似值）。decimal 属于定点类型（精确值）。
 *  MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M 表示该值的总共长度，D 表示小数点后面的长度。
 *  float 使用 4 个字节存储；double 使用使用 8 个字节存储；decimal 则是将数字打包保存到一个二进制字符串中（每 4 个字节存 9 个数字）。例如，decimal(18, 9) 小数点两边将各存储 9 个数字，一共使用 9 个字节：小数点前的数字用 4 个字节，小数点后的数字用 4 个字节，小数点本身占 1 个字节。
 *  因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用 bigint 代替 decimal ，将需要存储的货币单位根据最小的位数乘以相应的倍数即可。

## 字符串类型： ##

### varchar 和 char 类型 ###

*  varchar 类型用于存储可变长字符串，是最常见的字符串数据类型。
 *  varchar 需要使用 1 或 2 个额外字节记录字符串长度：如果列的最大长度小于或等于 255 字节，则只使用 1 个字节表示，否则使用 2 个字节。
 *  一般情况下使用varchar(10)和varchar(100)保存'fuk'占用的空间都是一样的，但是更小的varchar(10)有着更好的性能。比如：MySQL建立索引时如果没有限制索引的大小，索引长度会默认采用的该字段的长度，也就是说varchar(100)建立的索引存储大小要比varchar(10)建立索引存储大小大的多，加载索引使用的内存也更多。
 *  char 类型是定长的：MySQL 总是根据定义的字符串长度分配足够的空间。
 *  char 存储会删除存储数据的末尾空格；varchar 不会。（准确来说是 MySQL 4.1 以后的版本 varchar 不会删除存储数据的末尾空格）

### blob 和 text 类型 ###

*  blob 和 text 都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
 *  与其它类型不同，MySQL 把每个 blob 和 text 值当作一个独立的对象处理。
 *  blob 和 text 家族之间仅有的不同是 blob 类型存储的是二进制数据，没有排序规则或字符集，而 text 类型有字符集和排序规则。
 *  memory 引擎不支持 blob 和 text 类型。

### 枚举（enum）类型 ###

*  MySQL 在内部会将每个值在列表中的位置保存为整数，并且在表的 .frm 文件中保存 “数字-字符串” 映射关系的 “查找表”。
 *  如果使用数字作为枚举常量，这种双重性很容易导致混乱，例如 enum('1', '2', '3') 。建议尽量避免这么做。
 *  枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。

## 日期和时间类型： ##

*  MySQL 能存储的最小时间粒度为秒。
 *  Date:代表xxxx年xx月xx日   只表示前面的日期     通常用于保存用户生日
    
    DateTime:代表xxxx年xx月xx日xx时xx分xx秒     精确到时分秒,用于做时间戳
 *  datetime 保存范围从 1001 年到 9999 年，精度为秒。与时区无关。使用 8 字节的存储空间。
 *  timestamp 类型保存了从 1970 年 1 月 1 日 午夜（格林尼治标准时间）以来的秒数，它和 unix 时间戳相同。使用 4 字节的存储空间，范围从 1970 年到 2038 年。与时区有关。
 *  MySQL 4.1 以及更新的版本按照 datetime 的方式格式化 timestamp 的值，这仅仅是显示格式上的区别，timestamp 的存储格式在各个版本都是一样的。
 *  除了特殊行为之外，通常也应该尽量使用 timestamp ，因为它比 datetime 空间效率更高。有时候人们会将 unix 时间戳存储为整数值，但这不会带来任何收益。用整数保存时间戳的格式通常不方便处理，所以我们不推荐这么做。（对于这点很多公司项目中是用整数存储，我也查了资料，使用整数的理由一般是日期比较，计算时整数更好处理）

[https_www.cnblogs.com_woods1815_p_9926330.html]: https://www.cnblogs.com/woods1815/p/9926330.html