mysql优化（一）-数据类型优化

为什么要进行数据类型优化？

mysql支持的数据类型非常多，选择正确的类型对获取高性能至关重要。

选取数据类型的的原则

1. 更小的通常更好
   一般情况下使用正确存储数据的最小数据类型，因为它们占取更小的磁盘，内存和CPU缓存
2. 简单就好
   例如整型比字符操作代价更低，如采用MySQL内建类型（date，time，datatime）存储时间和日期而不是字符串，另一个是用整型存储ip地址。
3. 尽量避免NULL
   通常情况下最好使用 NOT NULL 存储，除非真的需要 NULL：
   • 可为 NULL 的列会占用更多存储空间
   • 可为 NULL 的列使索引，索引统计，和值比较都更加复杂
   • 当可为 NULL 的列被索引时，每个索引记录都需要额外的空间。
4. 不同子数据类型的特殊行为
   例如，DATETIME 和 TIMESAMP 可以存储相同类型的数据，时间和日期精确到秒，然而 TIMESTAMP 只使用 DATETIME 一半的存储空间，并且会根据时区变化拥有自动更新能力。

各种数据类型的优化

1. 整数类型

1. 数字的类型有两种：整数，实数。整数有下列几种类型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间，它们可以存储的值的范围是 -2 的 N-1 次方到 2 的 N-1 次方减 1 。其中 N 是 存储空间位数。

2. 整数类型有可选的 UNSIGNED 属性，表示不允许为负值，同时整数上线提高一倍。例如 TINYINT 的存储范围是 -128 ~ 127 ，而 TINYINT UNSIGNED 的存储范围是 0 ~ 255。有无符号都使用相同的存储空间，有相同的性能。

3. MySQL可以指定整数宽度，如 INT(20) ，但它实际上是没有用的，它不会限定取值的范围，只是规定了 mysql 的交互工具（如 mysql命令行等）显示字符的个数。对于存储和计算 INT(1) 和 INT(20) 是没有区别的。

2.实数类型

1. 实数是即带有小数的数字，分为不精确类型 FLOAT 和 DOUBLE 进行近似运算，精确类型 DECIAML 进行精确运算。

2. DECIAML 可以指定精度：DECIAML(a,b) 中 a代表该数字最多允许的位数，位数是小数部分和整数部分的位数之和，b表示小数部分的最大位数，b<=a。例如 DECIAML(5,4) 代表最多只能有5位，整数部分最对只能有1位，小数部分最多只能有4位。mysql5.0及更高版本将数字打包存储到一个二进制字符串里（每4个字节存9个数字）。

3. MySQL 5.0 及更高版本 DECIAML 类型最多允许 65个数字。

3.字符串类型

1. 从 mysql4.1 开始，每个字符串可以自定义字符集和排列顺序，或者校对规则。将会很大程度影响性能。

2. VARCHAR

   • varchar 存储可变长字符串，比定长字符串更节省空间，越短的字符串使用越少的空间。
   • varchar 使用 1 或 2个额外字节记录字符串长度，如果列长度小于或等于 255 用 1 个字节，否则用 2 个字节。
   • 适合使用 varchar 的情况
     1. 字符串列的最大长度比平均长度大得多
     2. 列更新很少

3. CHAR

   • char 是定长的：MySQL 总是根据定义的字符串长度分配足够空间
   • 适合使用 char 的场景
     1. 所有值接近同一长度，如 MD5 。
     2. 很短字符串，如 CHAR(1) 存储 Y 和 N的值只需要 1 个字节，而 VARCHAR(1) 却需要两个字节，还有一个需要记录长度的字节。

4. 注意
   使用 VARCHAR(5) 和 VARCHAR(200) 存储 'hello’的空间开销是一样的，但是更长的列或消耗更多的内存。

4.BLOB 和 TEXT 类型

1. 为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。BLOB 采用二进制数据没有排序规则和字符集，而 TEXT 有。

2. mysql 不能将 BLOB 和 TEXT 的全部长度字符串作为索引，只能使用前面一小部分。