深入理解MySql——数据类型选择及优化

数据类型选择及优化

1.为什么我们要进行数据库类型优化

2.数据库类型优化原则

3.如何选择数据库字段类型

正文：

1.为什么我们要进行数据库类型优化？

在我们以往的数据库设计经验中，往往着重关注的都是数据库的业务字段而不具体关注数据类型，其实这两者是相辅相成的，我们既要关注全局，也要注重细节，此文就来介绍如何关注字段类型的细节以及数据库类型的优化原则。

2.数据库类型优化原则

（1）更小的通常更好。
尽量使用存储数据的最小数据类型。
更小的数据类型通常更快，耗用更少的cpu，磁盘。

假设我们现在有一个订单表order,我们需要设计一个订单的状态的字段ordStatus,一般我们可以采取用0~9，这样用数字来代替订单状态，比如0-已下单，1-已付款，2-交易完成，这样一条数据的状态位就可以被压缩到1位，在千万级别的数据下，就可以省下大量空间。（你问我如果0~9用完了怎么办？那就上A-Z啊！）

注意点：
确保没有低估存储值的范围，比如我要存储一个用户名，一般就用varchar(50)，如果无法确定哪个数据类型是最好的，就设置为你认为不会超过范围的最小值。如果到了后期需要将数据字段变长，比如将varchar(50)改为varchar(150)，这对于数据库来说是一个非常耗时和痛苦的操作。

（2）简单就好。
简单数据类型的操作通常需要更少的CPU周期。例如：整型比字符操作代价更低。因为字符串要校对规则比整型更复杂。

（3）尽量避免null
最好让列指定为NOT NULL。
如果查询中包含null，对mysql来说更难优化，因为可为null的列使得索引，索引统计和值比较都更复杂。可为null的列会使用更多的存储空间，在mysql里也需要特殊处理。当可为null的列被索引时，每个索引记录需要一个额外的字节。
如果在计划列上建立索引，就应该尽量避免设计成可为null的列

注意：
通常把null改为NOT NULL 之后，提升的性能比较少，所以没有必要把这条放在首位。

3.如何选择数据库字段类型

接下来是选择合适的数据类型，因为mysql的很多不同字段都可以存储相同的数据，只是存储的长度，范围，精度不同，或者需要的物理空间不同。

（1）整数类型
如果要存整数，可以选择无小数点的整数类型。
整数范围有好多种，比如tinyint，int，bigint。
整数类型有可选的unsigned属性，可以将正数的上限提高一倍。例如，tinyint可以储存的范围是0~255，而不选的话则是-127-127。
有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据情况选择。

注意：
mysql可以为整数类型指定宽度，比如int(11)，对大多数应用而言这没有意义，它不会限制值的合法范围，只是规定了mysql的一些交互工具用来显示字符的个数。对于存储和计算来说，int(1)和int(20)相同。

（2）实数类型
实数是带小数点的数字。但是它不仅仅可以存储小数，也可以存储比bignint更大的数。

decimal类型用于存储精确的小数，decimal类型支持精确计算，早期数据库版本使用浮点型来进行精确计算，但这样会损失一些精度。

因为cpu不支持对decimal的直接计算，所以mysql自己实现了对decimal的精确计算，相对而言，cpu直接支持原生浮点计算，所以浮点运算明显更快。

decimal（18,9）小数点两边将各存储9个数字，一共使用9个字节，小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身一个字节。

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float占用4个，double占8个。相比float，double有更高精度和更大的范围。

（3）字符串类型
varchar和char类型

varchar
varchar用于存储可变长字符串，是常见的字符串数据类型。比定长类型更节省空间，因为它仅仅使用必要的空间。（当MYSQL使用ROW_FORMAT = FIXED创建的时候，每一行都会使用定长存储）。
varchar需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。
varchar节省了存储空间，对性能也有帮助，但是，由于是变长的，在update时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，而且在页内没有更多的空间可以存储，mysql会将行拆成不同的片段存储。

下面这种情况使用varchar是合适的：
字符串列的最大长度比平均长度大很多
列的更新很少，所以碎片不是问题。

char
char类型是定长的，mysql总是根据定义的字符串长度分配空间。定义char的时候，mysql会删除所有的末尾空格。
char适合存储很短的字符串，比如md5，char比varchar在存储空间上也更有效率，且不容易产生碎片。

与char有关的还有binary和varbinary，存储的是二进制字符串，二进制字符串和常规字符串非常相似，但是二进制字符串存储的是字节码，二进制的比较优势并不仅仅体现在大小写，还在于速度

提问：使用varchar(200)和varchar(5)存储的'hello'的空间开销是一样的，那么使用短的列有什么优势吗？
答：明显短的列有优势。更长的列会消耗更多的存储空间。再查询的时候，尤其是使用内存临时表进行排序操作时会特别糟糕，mysql通常会分配固定大小的内存块来保存内存值。

所以最好的策略是只分配需要的空间。

（4）日期类型和时间类型
datetime
这个类型能保存大范围的值，1001-9999年，使用8个字节的存储空间。
timestamp
timestamp只能表示从1970~2038年。
保存了1970年到现在的毫秒数
只使用4个字节

（5）选择主键
主键选择合适的数据类型非常重要，主键可能在别的表中会担任外键，所以为主键选择数据类型时，应该选择和关联表中相同的数据类型（字符编码也要相同）。

注意：
（1）不仅要考虑存储类型，更要考虑mysql对这种类型怎么执行计算和比较。
（2）在可以满足值的范围要求，并且预留未来增长空间的前提下，应该选择最小的数值类型。
（3）整数类型通常是主键最好的选择，因为他们很快并且可以使用auto increament。
（4）应该尽量避免使用字符串类型作为标识，因为它们很消耗空间，并且通常比数字类型慢。mysql默认对字符串使用压缩索引，这会导致查询慢很多，在测试中，注意到最多有6倍性能的下降。
（5）对于完全随机的字符串也要注意，如md5，uuid产生的字符串，这些函数生成的新值会任意分布在很大的空间内，会导致insert以及一些select查询很慢。

以上便是笔者总结的常用数据类型选择以及优化。