优化数据类型
MySQL支持的数据类型非常多，选择正确的数据类型对获得高性能至关重要。选择数据类型时一般遵从以下几个原则：
-
选择更小的数据类型，他们占用空间更小，速度更快，但是要确保没有低估数据需要的存储范围，因为在schema中增加数据类型的范围是一个非常耗时的工作。
-
简单的数据类型比复杂的更好，例如整型比字符操作开销要小，因为字符的字符集和校对规则（排序规则）比整形复杂；在使用时间日期时，应该用内建的类型（date,time,datetime）来代替字符串；用整形存储IP地址。
-
尽量避免NULL，很多表都包含NULL的列，通常情况下最好指定列为NOT NULL，除非真的需要存储NULL。
查询中包含NULL的列，对MySQL来说更难优化，因为NULL值会使得索引，索引统计和值比较都更复杂。可为NULL的列会占用更多的存储空间，在MySQL中需要对其进行特殊处理。当可为NULL的列被索引时，每个索引需要一个额外的字节进行记录。所以在建立索引时，应该避免在可为NULL的列上建立索引。
对于InnoDB来说，它使用单独的位(bit)存储NULL值，所以对于列中只有少数行为NULL的数据，效率并不差。但并不适用于MyISAM。
在MySQL中，很多数据类型可以存储相同类型的数据，只是存储的长度、范围、精度、存储空间（存在内存或硬盘）不一样，相同大类型的不同子类型属性也会有不同。例如DATETIME和TIMESTAMP都可以存储精确到秒的时间日期，但是TIMESTAMP只有DATETIME一半的存储空间，并且还可以根据时区进行变化，具有自动更新的能力。但是TIMESTAMP允许的时间范围要小得多。
整数类型
存储数字有两种，整数和实数。整数有5种类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别占用了8，16，24，32，64位存储空间。存储的值范围-2的(N-1)次方 - 2的(N-1)次方-1，N是存储空间的位数。整数还有可选的UNSIGNED属性，表示不允许负值，正数的范围为0 - 2的N次方-1。有符号和无符号使用相同的存储空间，性能相同。
整数计算一般使用64位的BIGINT整数（一些聚合函数除外，他们使用DECIMAL或DOUBLE），即使在32位环境也是如此。
MySQL可以为整数类型指定宽度，例如INT(9)，它不会限制值的合法范围，只是用来显示字符的个数。对于计算来说，INT(1)和INT(9)是相同的。
实数类型
MySQL支持精确类型和不精确类型两种，FLOAT和DOUBLE类型支持标准的浮点运算接近近似值，DECIMAL类型用于存储精确的小数。
浮点和DECIMAL都可以指定精度。DECIMAL列可以指定小数点前后所允许的最大位数，这会影响列的空间消耗。MySQL将数字打包保存到一个二进制字符串中，每4个字节存储9个数字，例如DECIMAL(18,9)，小数点两边各存储9个数字，整数部分和小数部分各占4各字节，小数点占1个字节，一共使用9各字节。
浮点类型在存储和DECIMAL相同范围的值时，会占用更少的空间。FLOAT占4个字节存储，DOUBLE8个字节，所以DOUBLE比FLOAT更有更高的精度和更大的范围，MySQL使用DOUBLE作为内部浮点计算的类型。
DECIMAL不仅能够用来存储小数，还能存储比BIGINT更大的整数。但是因为需要额外的存储空间和计算开销，所以应该在只有小数计算时才使用DECIMAL，在数据量比较大的时候，考虑使用BIGINT代替DECIMAL，根据小数位数乘以相应的倍数，这样避免了浮点存储运算不精确和DECIMAL计算代价高的问题。
字符串类型
MySQL支持多种字符串类型，VARCHAR和CHAR是两种最主要的数据类型
VARCHAR
VARCHAR用于存储可变长字符串，是最常见的字符串类型。它比定长类型更节省空间，因为它仅使用必要的空间。VARCHAR需要使用额外的1或者2个字节记录字符串的长度（取决于长度是否小于255）。假设采用laint1字符集，一个VARCHAR(10)的列需要11个字节的存储空间；VARCHAR(1000)需要1002个字节的存储空间。
VARCHAR节省了存储空间，所以对性能也有帮助，但是由于数据行是变长的，在进行更新操作时可能使行比原来更长，这就导致了需要额外的工作，在一个页内如果没有更多空间用来存储，InnoDB会进行页分裂使行可以放进页内，MyISAM会强行拆成不同的片段进行存储。字符串列中数据分布不均匀，列的更新较少，或者使用了UTF-8这种复杂的字符集，都适合用VARCHAR类型。
虽然VARCHAR(5)和VARCHAR(500)存储”hello“时空间开销一样，但是更长的列会消耗更多的内存，因为MySQL会分配固定大小的内存来保存内部值，尤其当使用内存临时表排序或操作时，会影响性能。
CHAR
CHAR类型是定长的，MySQL会根据定义的字符串长度分配足够的空间。CHAR适合存储很短的字符串，或者所有字符串长度接近，例如MD5密钥，对于经常变更的数据，CHAR也比VARCHAR更适合，因为定长不容易产生碎片。
BINARY和VARBINARY
这两种数据类型与CHAR和VARCHAR类似，它们存储的是二进制字符串，二进制字符串跟常规字符串相比，存储的是字节码而不是字符。
BLOB和TEXT
这两个都是为存储很大的数据设计的字符串数据类型，分别采用二进制和字符串方式存储。BLOB没有排序规则和字符串，TEXT有排序规则和字符集。MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。
日期和时间类型
时间类型有DATETIME和TIMESTAMP。
DATETIME能保存大范围的值，从1001到9999，精度为秒。它把日期和时间封装到YYYYMMDDHHMMSS的整数中，与时区无关，占8个字节的存储空间。
TIMESTAMP保存了从1970年1月1日以来的秒数，占4个字节的存储空间，范围比DATETIME小的多，只能表示1970年到2038年。
通常情况下，从空间效率考虑应该尽量使用TIMESTAMP。
位数据类型
可以使用BIT在一列中存储一个或多个true/false值。BIT(1)存储一个位，BIT(2)存储两个位，最大64个位。具体的存储空间视存储引擎而定，InnoDB使用一个足够存储的最小的整数类型，所以并不节省空间，MyISAM会打包存储所有BIT列，17个单独的BIT列只需要3个字节的空间。
整数类型
整数类型通常是最好的选择，速度快且可以AUTO_INCREMENT
范式与反范式
范式
范式要求数据表中不存在任何的函数传递依赖，比如学生，科目，科目的老师这三个字段，将学生和科目放在一张表中，科目和科目的老师放在一张表中，这就非常好的满足了范式，只不过范式设计占用了更多的空间，在查询时需要对多张表进行操作，会影响性能；进行修改操作时比反范式更加灵活。
反范式
反范式允许在一张表中存在函数传递依赖，比如将上面提到的三个字段放在一张表中。当数据量比较大时，不需要进行连接查询，效率更高，但是修改操作需要修改更多的数据，并且容易出错。
数据类型优化总结
在设计schema时，尽量保持小而简单时数据类型优化的原则，范式虽然好，但是反范式也是必要的。
网友评论