[MySQL] 基础数据类型优化

电玩女神 2022-06-12 00:07 226阅读 0赞

1.  整数类型  
    存储整数可使用TINYINT \\ SMALLINT \\ MEDIUMINT \\ INT \\ BIGINT，分别对应8、16、24、32、64位存储空间，可选用需求范围内最小的数据类型。INT UNSIGNED等表示对应的无符号类型。  
    MySQL可以为整数指定宽度，如INT(11)，但这样对存储不会有影响 ，只是指定了交互工具显示的字符个数（如命令行等工具）
2.  实数类型  
    FLOAT & DOUBLE类型使用了浮点运算进行的近似计算，分别占用4个和8个字节。相比之下DECIMAL类型可指定精确的位数，但在计算中还是会转为DOUBLE类型进行浮点运算的。  
    在DECIMAL中指定的位数并没有标准实现方式，因此不推荐指定。只有必要时（如财务计算）才会使用到DECIMAL，但实际上可以采用BIGINT类型来代替存储（如需要精确到万分之一，就将数据乘以一百万存入数据库），以避免DECIMAL的浮点计算不精确、开销大问题。
3.  字符串类型
    
    1.  VARCHAR和CHAR类型：不同存储引擎可能有自己的实现方式。  
        VARCHAR可以存储变长的字符串，需要额外的1~2个字节存储长度信息（长度<=255时使用1个字节，否则2个）。若在MySQL表中使用ROW\_FORMAT=FIXED创建表，每行的存储长度就变成了定长，若又使用到VARCHAR，会非常浪费空间。 相比于CHAR类型，由于其不定长，更新效率会更长。  
        CHAR类型会剃除末尾空格（总会在后面填充空格以实现定长字符串），VARCHAR在4.1以及更老版本时也会这样而新版不会。  
        **VARCHAR适合的使用场景有**：字符串的最大长度比平均长度大很多；列的更新较少；使用了UTF8这样的复杂字符集使得长度不确定。**CHAR适合的使用场景有**：所有值长度接近如密码MD5值、Y或N值等等。  
        另外还有两个类似的类型BINARY与VARBINARY分别对应二进制字串与变长二字制字串。
    2.  BLOB和TEXT类型  
        对应于整数，TEXT与BLOB也有TINYTEXT \\ SMALLTEXT \\ TINYBLOB等等。其中TEXT是普通字符串，而BLOB是二进制字符串。  
        与其它类型不同，**MySQL会将TEXT与BLOB当作一个独立的对象处理**，通常会做特殊处理。如InooDB会在此类型的值过大时，使用外部、专门的存储区域存储这个值，并在原行处存一个1~4字节的指针。  
        另一个不同点在于，对此类型排序时只比较前max\_sort\_length个字符，若有特殊需求可配置此值或使用`ORDER BY SUBSTRING(column, length)`。
    3.  ENUM（枚举）代替字符串  
        把一些不重复的字符串存储为预定义的集合，可将此类值压缩成一到两个字节中。SQL语句如下：
        
            CREATE TABLE enum_test( e ENUM('fish', 'apple', 'dog') NOT NULL ) INSERT INTO enum_test(e) VALUES('apple'), ('dog')
        
        若`SELECT e+0 FROM enum_test`将返回1至3的数字，值得一提的是，其内部的排序规则不是字母序，而是按内部存储的整数（1~3）排序。
4.  日期和时间类型  
    MySQL中的存储只能精确到秒，大体来说有DATETIME与TIMESTAMP两种日期类型。  
    其中DATETIME能保护1001年至9999年，范围较大。其被存储至格式为YYYYMMDDHHMMSS格式的整数中，并且与时区无关。  
    而TIMESTAMP，保存的是自1970年1月1日（格林威治标准时间）以来的秒数，与UNIX时间戳相同，只需要4个字节存储，因此其范围比DATETIME小得多，只能表示1970年至2038年。MySQL提供了`FROM_UNIXTIME()`函数将时间戳转为日期，也提供了`UNIX_TIMESTAMP()`函数将日期转为时间戳。  
    为了效率快，应使用效率更高的TIMESTAMP。若需要比秒更精确的单位，只能手动使用BIGINT等整数类型存储时间。
5.  位数据类型
    
    1.  BIT  
        BIT在旧版的MySQL（5.0之前）中，与TINYINT是同义词。在新版中成为一个独特的新类型，使用BIT(n)存储n个0/1位，n最大为64。  
        但其在各个存储引擎中的表现不同，如MyISAM只需要17个位就可以存储BIT(17)，而InnoDB \\ Memory则需要使用足够的最小整数类型来存放（在此处即32位整数）。BIT类型可以理解为一个01的二进制值，如BIT(8)值为’00111001’时，即可存储为数字57，展示出来也就是对应ASCII码57号字符’9’。这种方式非常令人费解，因此应该谨慎使用（最好避免使用）。  
        如果希望使用一个位存储0或1，也不一定要使用BIT(1)，可以使用CHAR(0)来代替，其值可以为NULL或空串”“。
    2.  SET  
        如果需要保存很多个true/false值，可以使用SET，并且MySQL中提供了FIND\_IN\_SET()等函数来方便操作。但是此类型有一个劣势在于改变SET的定义需要ALTER此表，在大表中的修改代价昂贵。一个较好的方法是使用整型的01位来表示true或false，并在应用中定义各个位的意义。
6.  选择标识符（IDENTIFIER）  
    举例来说，比如需要一个state\_id字段来标识此物品所在的美国某州，那可以使用哪些类型呢？通常来说有整数、ENUM\\SET、字符串三种。  
    ENUM\\SET只适用于极固定的东西，扩展效率低，而字符串的读写和操作效率都很低。因此推荐使用整数，并且尽可能使用最小的整数类型即可（如TINYINT等）  
    比如存储UUID时，推荐去除-，或使用UNHEX()函数转为16字节数字存入BINARY(16)类型中，取出时可使用HEX()格式化回普通的16进制UUID值。
7.  特殊类型  
    比较典型的一个特殊数据是IPv4地址，人们常用VARCHAR(15)来存储。然而它实际上是32位无符号整数，只是平时用小数点将其分为四段，方便人们阅读而已。MySQL中提供了`INET_ATON()`和`INET_NTOA()`两个函数为IPV4和32位无符号整数提供相互转换。