MySQL 请选择合适的列！

淡淡的烟草味﹌ 2022-08-20 05:21 202阅读 0赞

思维导图

点击图片，可查看大图。

[![2012052316474473.png][]][2012052316474473.png 1]

介绍

情况：如果你的表结构设计不良或你的索引设计不佳，那么请你优化你的表结构设计和给予合适的索引，这样你的查询性能就能提高几个数量级。——数据越大，索引的价值越能体现出来。

我们要提高性能，需要考虑的因素：

1、设计架构

2、设计索引

3、评估查询性能

![2012052315371511.png][]

今天要讲的是表列的设计，暂不谈索引设计。我会在下一章讲索引设计。

选择数据类型

选择正确的数据类型，对于提高性能至关重要。

下面给出几种 **原则**，有利于帮助你选择何种类型。

1、更小通常更好。

使用最小的数据类型。——更少的磁盘空间，内存和CPU缓存。而且需要的CPU的周期也更少。

2、简单就好。

整数代价小于字符。——因为字符集和排序规则使字符比较更复杂。

1>mysql内建类型（如timestamp,date)优于使用字符串保存。

2>使用整数保存ip地址。

3、尽量避免NULL——如果计划对列进行索引，尽量避免把列设置为NULL

尽可能把字段定义为NOT NULL。——可以放置一个默认值，如‘’，0，特殊字符串。

原因：

（1）MYSQL难以优化NULL列。NULL列会使索引，索引统计和值更加复杂。

（2）NULL列需要更多的存储空间，还需要在MYSQL内部进行特殊处理。

（3）NULL列加索引，每条记录都需要一个额外的字节，还导致MyISAM中固定大小的索引变成可变大小的索引。

![2012052315555940.png][]

决定列的数据类型，我们应该遵循下面两步。

第一步、大致决定数据类型。——判断是数字，字符串还是时间等。这通常很直观。

第二步、确定特定的类型。

很多数据类型能够保存同类型的数据，但是我们要发现，他们在**存储**的范围，**精度和物理**空间之间的差别（磁盘或内存空间）。如：datetime和timestamp能保存同样类型的数据：日期和时间。——timestamp使用datetime一半的空间;能保存时区;拥有特殊的自动更新能力。

![2012052316070949.png][]

品味数据类型

整数

1、存储类型——数据范围为-2^(n-1)到2^(n-1)-1，这里的n是所需存储空间的位数。

<table style="border:1px solid silver; border-collapse:collapse; word-break:break-word"> 
 <tbody> 
  <tr> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　类型名称</td> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">占用位数　　&nbsp;</td> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　数据范围</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">TINYINT</td> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　　　8</td> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　-2^7 ~ 2^7-1</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　SMALLINT</td> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　　　16</td> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　-2^15~2^15-1</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　MEDIUMINT</td> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　　　24</td> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　-2^23~2^23-1</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　INT</td> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　　　32</td> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　-2^31~2^23-1</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　BIGINT</td> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;64&nbsp;</td> 
   <td style="border:1px solid silver; border-collapse:collapse; padding:3px">&nbsp;　　-2^63~2^63-1</td> 
  </tr> 
 </tbody> 
</table>

2、unsigned属性表示不允许负数，并大致把正上限提高了一倍。如TINYINT UNSIGNED保存的范围为0到255而不是-127到128

3、MYSQL对整数类型定义宽度，比如int（1）和int（22）对于存储和计算是一样的。只规定了MYSQL的交互工具（如命令行客户端）用来显示字符的个数。

![2012052316133653.png][]

实数

实数有分数部分（小数部分）。

存储类型：FLOAT和DOUBLE，DECIMAL。

占用大小：FLOAT 4个字节，DOUBLE 8个字节。DECIMAL受到MYSQL版本影响，早期版本254个数字，5.0以上65个数字。

区别：1、FLOAT和DOUBLE支持标准浮点运算进行近似计算。

2、DECIMAL进行DECIMAL运算，CPU并不支持对它进行直接计算。浮点运算会快一点，因为计算直接在CPU上进行。

3、DECIMAL只是一个存储格式，在计算时会被转换为DOUBLE类型。

4、DECIMAL(18,9)使用9个字节，小数点前4个字节，小数点1个字节，小数点后4个字节。

5、DECIMAL只有对小数进行精确计算的时候才使用它，如保存金融数据。

![2012052316252049.png][]

字符串类型

1、varchar

（1）保存可变长字符串。

理解：比固定长度占用更少的存储空间，因为它只占用自己需要的空间。例外情况：使用ROW\_FORMAT=FIXED创建的MyISAM表，它为每行使用固定长度的空间，可能会造成浪费。

（2）存储长度信息。如果定义的列小于或等于255，则使用1个字节存储长度值，假设使用latin1字符集，如varchar（10）将占用11个字节的存储空间。反过来，varchar（1000），则占用1002个字节的存储空间。

（3）节约空间，对性能有帮助。

（4）5.0版本以上，无论是取值还是保存，MySQL都会保留字符串末尾的空格。

> **只分配真正需要的空间**  
> 使用varchar(5)和varchar(200)保存'hello'占用空间是一样的。——这里应该指的是磁盘上的空间。  
> 那么使用较短列有何优势？——巨大的优势  
> 　　较大的列会使用更多的内存，因为MySQL通常会分配固定大小的内存块（如varchar(200)会用200个字符大小的内存空间）来保存值(然后对值进行trim操作，最后放入磁盘)或取值。——这对排序或使用基于内存的临时表尤其不好。

![2012052316305478.png][]

2、char

（1）固定长度。

（2）保存值时，去掉末尾的空格。

![2012052307300611.png][]

![2012052217075512.png][]  咱们再看看varchar

![2012052307354193.png][]

（3）char常用于很短字符串或长度近似相同的字符串的时候很有用。如存储用户密码的MD5哈希值，它的长度总是一样的。

char优于varchar的地方？

1>> 对于经常改变的值，char优于varchar，因为固定长度行不容易产生碎片。——当最长长度远大于平均长度，并且很少发生更新的时候，通常适合使用varchar。

2>>对于很短的列，char的效率也是高于varchar的。如对于单字节字符集（如latin1)，char(1)只会占用1个字节，而varchar(2)会占用2个字节（有一个字节用来存储长度的信息）。

![2012052316371261.png][]