MySQL数据类型、选择与优化

MySQL数据类型：数值、日期/时间、字符串(字符)类型、复合类型。

 

一 选择原则

       1.最小的通常更好：

        一般情况下选择可以正确存储数据的最小数据类型。越小的数据类型通常更快，占用磁盘，内存和CPU缓存更小，大大减少IO开销。

       2.简单就好：

        简单的数据类型的操作通常需要更少的CPU周期。例如：整型比字符操作代价要小得多，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。比如应该使用MySQL内建的类型而不是使用字符型来存储日期和时间。

       3.尽量避免NULL:

        尽量制定列为NOT NULL，除非真的需要NULL类型的值。因为可能为NULL列使得索引，索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL列做索引时，每个索引需要一个额外的字节，在MyISAM更有可能导致固定大小的索引变成可变大小索引，在InnoDB中使用单独的位(bit)存储NULL值。

         所以当一个数据类型可以有多种选择多种类型的时候，应该优先考虑数字类型，其次是日期或二进制类型，最后应该是字符类型。尽量使用INT而非BIGINT，如果非负则加上UNSIGNED（这样数值容量会扩大一倍），当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。使用枚举或整数代替字符串类型。尽量使用TIMESTAMP而非DATETIME。使用枚举或整数代替字符串类型。用整型来存IP。对于长度相对固定的字段使用char而不是varchar（例如，手机号，MD5，身份证号码）。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。
 
原理：在对数据进行比较（查询条件，JOIN条件及排序）操作时：同样的数据，字符处理往往比数字处理慢，而且在数据库中，数据的处理是以页为单位，列的长度越小，数据类型占用的空间越小，利于性能的提升。

 

二数据类型

    1.数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL(M,D)	M字节(D+2 , 如果M < D)	依赖与M，D	依赖与M，D	小数值（准确精确度）

 

• INT
  在 MySQL 中支持的 5 个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。
  MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展(如 INT(6),6即是其宽度指示器,该宽度指示器并不会影响int列存储字段的大小,也就是说,超过6位它不会自动截取,依然会存储,只有超过它本身的存储范围才会截取;此处宽度指示器的作用在于该字段是否有zerofill,如果有就未满足6位的部分就会用0来填充)，这样当从数据库检索一个值时，可以把这个值加长到指定的长度。例如，指定一个字段的类型为 INT(6)，就可以保证所包含数字少于 6 个的值从数据库中检索出来时能够自动地用空格填充。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。
  万一我们需要对一个字段存储一个超出许可范围的数字，MySQL 会根据允许范围最接近它的一端截短后再进行存储。还有一个比较特别的地方是，MySQL 会在不合规定的值插入表前自动修改为 0。

• unsigned 和 zerofill
  UNSIGNED 修饰符规定字段只保存正值,即无符号,而mysql字段默认是有符号的。因为不需要保存数字的正、负符号，可以在储时节约一个"位"的空间(即翻一倍)。从而增大这个字段可以存储的值的范围。注意这个修饰符要紧跟在数值类型后面;
  ZEROFILL 修饰符规定 0（不是空格）可以用来真补输出的值。使用这个修饰符可以阻止 MySQL 数据库存储负值,如果某列设置为zerofill,那它自动就unsigned。这个值要配合int,tinyint,smallint,midiumint等字段的宽度指示器来用;XXint(M),如果没有zerofill,这个M的宽度指示器是没有意义的

2字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

 MySQL支持多种字符串类型，每个字符串列可以定义自己的字符集和排序规则，或者说校对规则，这些东西很大程度上影响性能。

char和varchar：

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
3.char类型的字符串检索速度要比varchar类型的快。
4.char是定长的，每个列无论字符串长度大小都会分配固定存储空间，char适合存储字符很短，或者所有值接近一个长度时，如存储MD5码、手机号等，对于经常变更的数据char也比varchar好，再一个是对于非常短的列，char比varchar更节省空间，例如使用char(1)和char(1)来存储Y或N时，char需要一个字符，而varchar需要两个，因为还有一个长度字节
5.varchar类型用于存储变长字符串，它会删除末尾的所有空格，它比定长字符串更省空间，因为它仅使用必要的空间（越短的字符串占用越少），varchar会用1或2个额外字节记录字符串长度，varchar，因此对性能是有帮助的，但是因为行是变长的，所以每次UPDATE时会重新计算字符串长度，会比定长多做额外工作。因此不是说varchar就一定能提升性能，同样要分使用场景，如果字符串的最大长度比平均长度大很多，且列的更新很少时则适合用varchar。注意InnoDB会把过长的varchar存储为BLOB，通常人们喜欢将IP地址存储为varchar型，其实IP地址是32位二进制表示的,你可以转为十进制表示，因而不是字符串，用小数是将它分为四段便于阅读。
         

varchar和text：

1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值。
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

      使用枚举代替字符串类型，枚举可以把一些重复的字符串存储成一个预定义的集合，MySQL在存储枚举时非常紧凑，MySQL在列中保存值为枚举中的位置整数。枚举最不好的是字符串是固定的，添加或删除必须使用ALTER TABLE。因此对于未来会改变的字符串，使用枚举不是一个好主意，除非能接受在枚举末尾添加元素，由于枚举有一个映射转换过程，所以枚举虽然能减少存储空间，但是也会增加一些额外开销。

 

3日期时间类型

类型	大小	范围	格式	用途
DATE	3字节	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3字节	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1字节	1901/2155	YYYY	年份值
DATETIME	8字节	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4字节	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

 

DATETIME

能保存1001到9999年，精度为秒。格式为YYYY-MM-DD HH:MM:SS与时区无关，使用八个字节的存储空间。

TIMESTAMP

时间戳，正如名字一样。它能保存从1970年1月1号午夜（格林尼治标准时间）。它只使用四个字节的存储空间只能表示1970到2038年。

TIMESTAMP显示的值依赖于时区。MYSQL服务器，操作系统，以及客户端连接都有时区设置。因此存储值为0时在不同的时区显示值会有差别。

注：通常情况下应尽量使用TIMESTAMP，因为它比DATETIME效率更高。如果需要存储更小粒度的时间，可以用BIGINGT或者转换成DOUBLE类型来进行存储。

4复合类型

MySQL 还支持两种复合数据类型 ENUM 和 SET，它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。

• ENUM 类型
  ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null 值，除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值大小写转换成与集合中大小写一致的值。
  ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息，这个错误值用索引 0 或者一个空字符串表示。
  MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。

• SET 类型
  SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。
  一个 SET 类型最多可以包含 64 项元素。还去除了重复的元素，所以 SET 类型中不可能包含两个相同的元素。
  希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。

复合类型我们一般用tinyint,更快的时间更省的空间以及更容易扩展