mysql数据类型整理记录和优化

数据类型

1. 整数类型
   有两种类型的数字， 整数和实数。
   整数可选：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别使用8，16，24，32，64位存储空间。他们可以存储值的范围 从 -2 （n-1）到2（n-1）-1,n是存储空间的位数。

• 整数类型可选 UNSIGNED 属性，大致可使正数上限提高一倍。
• 我们的选择决定mysql是怎么在内存和磁盘中保存数据。 然而，整数计算一般使用64位BIGINT整数，即使在32位环境也是如此。（一些聚合函数例外，他们使用decimal或double进行计算）
• 指定宽度意义不大，mysql可以为整数类型指定宽度 例如INT（11），对大多数应用这是没有意义的，他不会限制合法范围，只是规定了mysql的一些交互工具（如mysql命令行客户端）用来显示字符的个数，对于存储和计算来说，INT（1）和INT（20）是相同的。

2. 实数类型
   实数是带有小数部分的数字。然而，他们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数。mysql既支持精确类型，也支持不精确类型。
   float和double支持使用标准的浮点数运算进行近似计算。
   decimal 用于存储精确的小数。mysql5.0及以上，decimal支持精确计算。
   float使用4个字节存储、double8个字节，decimal最多存储65个数字。
   MySQL分别为整数和小数部分分配存储空间。 MySQL使用二进制格式存储DECIMAL值。它将9位数字包装成4个字节。
   对于每个部分，需要4个字节来存储9位数的每个倍数。剩余数字所需的存储如下表所示
   剩余数字 位

剩余数字	位
1–2	1
3–4	2
5–6	3
7-9	4

3. 字符串类型
   varchar
   varchar 类型用于存储可变字符串，是最常见的字符串数据类型。比定长更节省空间，因为他仅使用必要的空间。例外:如果mysql表使用ROW_FORMAT=FIXED创建的话，每行都会使用定长。

• varchar 需要使用1或两个额外字节记录字符串长度。如果列的最大长度<=255字节 则使用1个字节表示，否则使用两个字节。例如：varchar（10）11个字节 varchar（1000）1002个字节
• varchar节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在update时可能使行变得比原来更长，这就需要额外的工作。如果一个行占用的空间增长率，并且当页内没有更多存储空间，这种情况下不同引擎处理方式不同，Myisam拆分成不同片段存储，InnoDB则需要分裂页来使行可以放进页内
• 5.0或更高版本，保留末尾空格，但在4.1或更早版本会剔除末尾空格。
• varchar适用情况：

1. 字符串列的最大长度比平均长度大很多
   2)列更新很少，所以碎片不是问题
   3)使用了想UTF-8这样复杂的字符集，每个字符使用不同的字节进行存储

• 关于varchar 最多能存多少值
  mysql的记录行长度是有限制的，不是无限长的，这个长度是64K，即65535个字节，对所有的表都是一样的。

    MySQL对于变长类型的字段会有1-2个字节来保存字符长度。
    当字符数小于等于255时，MySQL只用1个字节来记录，因为2的8次方减1只能存到255。
    当字符数多余255时，就得用2个字节来存长度了。
    在utf-8状态下的varchar，最大只能到 (65535 - 2) / 3 = 21844 余 1。
    在gbk状态下的varchar, 最大只能到 (65535 - 2) / 2 = 32766 余 1
  

char
char 是定长的，mysql总是根据定义的字符串长度分配足够的空间。当存储char值时，会删除所有末尾空格。适合存储很短的字符串，或者所有字符串都接近同一个长度

• char 比varchar 更好，因为定长的char类型不易产生碎片。

• 非常短的列，char比varchar在存储空间上更有效率

    varchar（5）和varchar（200） 存储 hello 的空间开销是一样的，那么使用短列意义何在？
    事实证明有很大的优势。更长的列会消耗更多内存，因为mysql通常分配固定大小的内存来保存内部值。
    尤其是使用内存临时表进行排序或操作时候回特别糟糕。在利用磁盘临时表的时候也同样糟糕。
    所以最好只分配真正需要的空间。
  

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

   • blob 存储的是二进制，没有排序规则或字符集

   • text 有字符集和排序规则

       Mysql把每个blob和text值当做一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当blob和text太大
       时，InnoDB会使用专门的外部存储区域来存储，此时每个值在行内需要1-4个字节存储一个指针。
     

5. 日期和时间类型
   mysq 最小时间粒度为秒
   DATETIME
   范围大，1001~9999，精度为秒。与时区无关，使用8个字节存储。
   TIMESTAMP
   范围小，1970~2038，精度秒。显示依赖时区，可使用 FROM_UNIXtIME()转换为日期，UNIX_TIMESTAMP() 把日期转换为Unix时间戳。
   使用推荐
   除了特殊行为外，通常应该尽量使用TIMESTAMP，它比DATETIME空间效率更高。

6. 位数据类型（不推荐使用）
   mysql 有少数几种存储类型使用紧凑的位存储数据。所有这些类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。
   BIT
   mysql5.0之前bit是tinyint的同义词。5.0及其以后版本，完全不同。可以使用BIT存储一个或多个true/false值。BIT（n）定义包含n位，最大64

7. SET
   如果需要保存很多true/false 值，可以考虑合并这些列到一个set数据类型。

补充知识

irow_format Innodb 对应 Compact ，MyISAM 一般对应静态与动态

mysql中若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话，那么这个表其实也叫动态表，即该表的 row_format是dynamic，就是说每条记录所占用的字节是动态的。其优点节省空间，缺点增加读取的时间开销。反之，这张表叫静态表，该表 row_format为fixed,即每条记录占用字节一样。优点读取快，缺点浪费部分空间

所以，做搜索查询量大的表一般都以空间来换取时间，设计成静态表。
row_format还有其他一些值：

DEFAULT
FIXED
DYNAMIC
COMPRESSED
REDUNDANT
COMPACT

修改行格式

ALTER TABLE table_name ROW_FORMAT = DEFAULT

修改过程导致：

fixed—>dynamic: 这会导致CHAR变成VARCHAR
dynamic—>fixed: 这会导致VARCHAR变成CHAR

待补充 varchar 究竟能存几个汉字

参考高性能mysql 第四章
https://www.cnblogs.com/owenma/p/7097602.html