优化MySQL数据类型——《深究MySQL》

太过爱你忘了你带给我的痛 2022-06-02 03:12 258阅读 0赞

### 1. 选择数据类型总体原则 ###

1.1 操作整型比字符简单  
  因为字符的字符集和校对规则（排序规则）使得字符的比较比整型的比较更为复杂。  
  如我们应该用datetime、date或time存储日期类型，而不是字符串。  
  我们也应该用整型来存储IP地址。  
1.2 尽量避免Null  
  NULL的列可使得索引、索引统计和值的比较都更复杂。  
  可为NULL的列会占用更多的存储空间，因为此时，每个索引记录需要一个额外的字节  
  通常情况下最好指定列为NOT NULL，除非真的需要NULL的值，一般改为NOT NULL带来的性能提升比较小  
  InnoDB使用单独的位(bit)存储NULL值，所以对于稀疏数据（大部分数据为NULL，只有少部分非空数据）有很好的空间效率。

### 2. 整数类型 ###

2.1 整数类型有tinyint、smallint、mediumint、int、bigint  
  它们分别使用8，16，24，32，64位存储空间，它们的存储范围是−2(N−1)到2(N−1)−1，其中幂里的N是位数。  
  unsigned(无符号)属性不充许有负值，这可以使正数存储范围扩大一倍，如tinyint的范围就会从-128~127变成0~255  
  整数计算一般使用有64位的bigint整数，即使在32位的环境中即是如此  
  对存储和计算来讲，指定了长度的int(1)和int(12)是相同的。因为这个指定的长度只是规定了交互工具（如MySQL命令客户端）显示字符的个数。而不是指定的存储大小。这个规则只适用于像tinyInt这种数字类型而不适用于varchar和char类型。

### 3. 实数类型（带有小数的数字） ###

3.1 Float和Double类型支持使用标准的浮点运算进行近似计算  
3.2 Decimal类型用于存储精确的小数  
  decimal需要额外的空间和计算开销，所以尽量只在需要对小数进行精确计算时使用（如与钱相关的数据）。  
  在数量比较大的时候，可考虑使用bigint代替decimal。此时只需根据小数位数乘以相应的倍数即可。

### 4. 字符串类型 ###

**Varchar**  
4.1 varchar类型需要1~2个额外字节记录字符串的长度；当列的最大长度<=255时，用1个；当>255时用2个字节。  
4.2 varchar是可变长的，所以节省存储空间；但如果在update时列变得比原来长，导致整行数据占用的空间增长，如果在页内存不下时，InnoDB引擎就会做**页分裂**这个额外的操作。  
4.3 选择varchar类型的情况

> 字符串列的最大长度比平均长度大很多  
> 列的更新频率较少  
> 使用了像UTF-8这样复要的字符集，每个字符集都使用不同的字节数进行存储

4.4 InnoDB中会把过长的varchar存储为BLOB  
4.5 Varchar(100)后面的长度100指定是存储长度，虽然它是变长的，但是也不能随意往大了设置，因为在内存中会以这个长度大小的内存块来存储数据。  
**Char**  
4.6 当存储Char值时，MySQL会删除末尾的所有空格  
4.7 Char适合存储像MD5这种定长的字符串  
4.8 利用Char(1)在存储像只有Y和N的值时，比Varchar(1)节约空间，因为varchar还要一个字节记录长度  
4.9 Memeory引擎只支持定长的行，即使有变长字段，也会根据最大长度分配空间

**Binary和VarBinary**  
这是二进制字符串  
4.10 MySQL在填充Binary时用的是\\0(零字节)而不是空格，在检索时也不会去掉  
4.11 MySQL在比较Binary字符串时，每次按一个字节，并且是根据该字节的数值进行比较；因此二进制比较比字符串的比较快。

**Blob和Text**  
4.12 Blob和Text都是为存储很大的数据而设计的，它们分别采用二进制和字符方式存储  
4.13 字符类型有TinyText、SmallText、Text、MediumText、LongText；对应的二进制类型：TinyBlob、SmallBlob、Blob、MediumBlob、LongBlob  
4.14 与其他类型不同，每个Blob和Text值会被当成独立的对象对象。当数据很大时，InnoDB会在外部存储区域存储，此时每个值在行内需要1~4个字节存储指向外部存储区域的指针。  
4.15 Blob和Text在排序时，只对列中最前max\_sort\_length。如果只排序前面一小部分，则可以减小max\_sort\_length的值，或者使用order by substring(列名，length)  
4.16 Blob和Text只能索引前端一小部分数据，好像是前256

**Enum**  
4.17 枚举类型非常紧凑  
4.18 枚举类型的底层是将枚举的几个值保存为整数，并且在表的.fm文件中保存“数字-字符串”映射关系的查找表。  
4.19 枚举中尽量不要存数字，不然容易引起歧义。因为双重性  
4.20 枚举是按字符串值在底层对应的数字排序的，所以一般最好是按照需要的顺序来定义枚举。  
4.21 枚举值都被保存成了整数，只有在查询时才能转换为字符串，所以会有一定的开销。  
4.22 枚举的字符串列表是固定的，添加或删除时必须使用ALTER TABLE。

### 5 日期类型 ###

5.1 MySQL存储时间的最小精度为秒  
5.2 Datetime时间类型存储的范围是1001年~9999年，精度为秒，占8个字节的长度。  
5.3 Timestamp的存储范围是1970年~2038年，存的是时间戳，只占4个字节的长度。  
5.4 Timestamp列会默认设置为Not null ，所以在插入数据时，若不给值就会默认为当前时间。  
5.5 实现精度比秒更高的办法：

> 1）使用Bigint存储微秒级别的时间戳  
> 2）用Double存储秒之后的小数部分

个人更偏向于使用datetime，timestamp不能像datetime那样可以使用函数，存储时间也只能到2038年。

### 6 位数据类型 ###

从技术上来说，位类型都是字符串类型  
**Bit**（慎用）  
MySQL将Bit当作字符串类型，而不是数字类型。如果检索BIT(1)的值时，结果是一个包含二进制0或1的字符串，而不是ASCII码的0或1。  
Bit的最大长度是64  
**Set**  
如果需要保存许多true/false值，可用set数据类型

### 7 其它 ###

1.    
    
2.  关联查询时，两个表中关联的字段最好是同一个数据类型
3.  如果没有负数，最好使设置unsigned；既避免了出现负数的bug，又使得存储的数据扩大一倍
4.  Enum和Set类型适合存储固定信息，如有序的状态、产品类型、性别
5.  对于完全随机的字符串（如MD5()、SHA1()、UUID()）在插入值时，会随机地写到索引的不同位置，所以插入慢，还会导致页分裂和磁盘随机访问。在查询时，也会因为逻辑上相邻的行会分布在磁盘和同存的不同的地方而变得很慢。
6.  随机值会弱化查询语句的缓存作用，因为它使得缓存赖以工作的访问局部性原理失效。
7.  在存十六进制的UUID值时，最好移除“-”号。最好的做法是用unhex()函数将其转为16字节的数字，并存在一个binary(16)列中，在检索时可通过hex()函数转为十六进制格式。
8.  IP地址时实际是32位的无符号整数，所以存储的最好方式是用无符号整数,而不是字符串类型。Inet\_ATON()将带点儿的IP转为数字，而Inet\_NTOA可将数字转为IP

> select Inet\_ATON(‘127.0.0.1’);——>2130706433  
> select Inet\_ATON(‘127.1’); ——>2130706433  
> select Inet\_NTOA(3520061480);——>209.207.224.40