MySQL 请选择合适的列 |
|
本文标签：MySQL,合适的列 思维导图 点击图片，可查看大图 。
情况：如果你的表结构设计不良或你的索引设计不佳，那么请你优化你的表结构设计和给予合适的索引，这样你的查询性能就能提高几个数量级 。——数据越大，索引的价值越能体现出来 。
我们要提高性能，需要考虑的因素：
1、设计架构
2、设计索引
3、评估查询性能
今天要讲的是表列的设计，暂不谈索引设计 。我会在下一章讲索引设计 。
选择数据类型
选择正确的数据类型，对于提高性能至关重要 。
下面给出几种原则，有利于帮助你选择何种类型 。
1、更小通常更好 。
使用最小的数据类型 。——更少的磁盘空间，内存和CPU缓存 。而且需要的CPU的周期也更少 。
2、简单就好 。
整数代价小于字符 。——因为字符集和排序规则使字符比较更复杂 。
1>mysql内建类型（如timestamp,date)优于使用字符串保存 。
2>使用整数保存ip地址 。
3、尽量避免NULL——如果计划对列进行索引，尽量避免把列设置为NULL
尽可能把字段定义为NOT NULL 。——可以放置一个默认值，如‘，0，特殊字符串 。
原因：
（1）MYSQL难以优化NULL列 。NULL列会使索引，索引统计和值更加复杂 。
（2）NULL列需要更多的存储空间，还需要在MYSQL内部进行特殊处理 。
（3）NULL列加索引，每条记录都需要一个额外的字节，还导致MyISAM中固定大小的索引变成可变大小的索引 。
决定列的数据类型，我们应该遵循下面两步 。 第一步、大致决定数据类型 。——判断是数字，字符串还是时间等 。这通常很直观 。 第二步、确定特定的类型 。 很多数据类型能够保存同类型的数据，但是我们要发现，他们在存储的范围，精度和物理空间之间的差别（磁盘或内存空间） 。如：datetime和timestamp能保存同样类型的数据：日期和时间 。——timestamp使用datetime一半的空间;能保存时区;拥有特殊的自动更新能力 。
品味数据类型
整数
1、存储类型——数据范围为-2^(n-1)到2^(n-1)-1，这里的n是所需存储空间的位数 。
类型名称 占用位数 数据范围 TINYINT 8 -2^7 ' 2^7-1 SMALLINT 16 -2^15'2^15-1 MEDIUMINT 24 -2^23'2^23-1 INT 32 -2^31'2^23-1 BIGINT 64 -2^63'2^63-1
2、unsigned属性表示不允许负数，并大致把正上限提高了一倍 。如TINYINT UNSIGNED保存的范围为0到255而不是-127到128
3、MYSQL对整数类型定义宽度，比如int（1）和int（22）对于存储和计算是一样的 。只规定了MYSQL的交互工具（如命令行客户端）用来显示字符的个数 。
实数
实数有分数部分（小数部分） 。
存储类型：FLOAT和DOUBLE，DECIMAL 。
占用大小：FLOAT 4个字节，DOUBLE 8个字节 。DECIMAL受到MYSQL版本影响，早期版本254个数字，5.0以上65个数字 。
区别：1、FLOAT和DOUBLE支持标准浮点运算进行近似计算 。
2、DECIMAL进行DECIMAL运算，CPU并不支持对它进行直接计算 。浮点运算会快一点，因为计算直接在CPU上进行 。
3、DECIMAL只是一个存储格式，在计算时会被转换为DOUBLE类型 。
4、DECIMAL(18,9)使用9个字节，小数点前4个字节，小数点1个字节，小数点后4个字节 。
5、DECIMAL只有对小数进行精确计算的时候才使用它，如保存金融数据 。
字符串类型
1、varchar
（1）保存可变长字符串 。
理解：比固定长度占用更少的存储空间，因为它只占用自己需要的空间 。例外情况：使用ROW_FORMAT=FIXED创建的MyISAM表，它为每行使用固定长度的空间，可能会造成浪费 。
（2）存储长度信息 。如果定义的列小于或等于255，则使用1个字节存储长度值，假设使用latin1字符集，如varchar（10）将占用11个字节的存储空间 。反过来，varchar（1000），则占用1002个字节的存储空间 。
（3）节约空间，对性能有帮助 。
（4）5.0版本以上，无论是取值还是保存，MySQL都会保留字符串末尾的空格 。
只分配真正需要的空间
2、char
（1）固定长度 。
（2）保存值时，去掉末尾的空格 。
（3）char常用于很短字符串或长度近似相同的字符串的时候很有用 。如存储用户密码的MD5哈希值，它的长度总是一样的 。
char优于varchar的地方？
1>> 对于经常改变的值，char优于varchar，因为固定长度行不容易产生碎片 。——当最长长度远大于平均长度，并且很少发生更新的时候，通常适合使用varchar 。
2>>对于很短的列，char的效率也是高于varchar的 。如对于单字节字符集（如latin1)，char(1)只会占用1个字节，而varchar(2)会占用2个字节（有一个字节用来存储长度的信息） 。
3、text
用于保存大量数据 。
（1）InnoDB在它们较大的时候会使用“外部”存储区域来进行保存 。——所以需要足够的外部存储空间来保存实际的值 。
（2）排序方式不同于其他字符类型，不会按照完整长度进行排序，而只是按照max_sort_length规定的前若干个字节进行排序 。
4、使用ENUM代替字符串类型
（1）ENUM列可以存储65 535个不同的字符串 。
（2）以紧凑方式保存 。根据列表中值的数量，把它们压缩到1到2个字节中 。
（3）MySQL在内部把每个值都保存为整数，以表示值在列表中的位置 。
（4）保留了一份“查找表”，来表示整数和字符串在表的.frm文件中的映射关系 。
（5）ENUM字符列是固定的，添加、删除字符串须使用ALTER TABLE 。
（6）使用案例：权限表中使用ENUM来保存Y值和N值 。
使用方法：
在对enum列使用order by的时候，是按数字排序的，而不是字符串排序 。
日期和时间类型 DATETIME：保存大范围的值 。封装格式：YYYYMMDDHHMMSS 。——与时区无关，使用8字节存储空间 。
TIMESTAMP：保存自1970年1月1日午夜（格林尼治标准时间）以来的秒数 。——使用4字节存储空间 。
经验交谈
1、我们在为列选择数据类型的时候，不仅要考虑存储类型大小，还要考虑MySQL如何对它们进行计算和比较 。例如：MySQL在内部把ENUM和SET类型保存为整数，但是在比较的时候把它们转换为字符串 。
2、我们要在相关表中使用同样的类型，类型之间要精确匹配，包括诸如UNSIGNED这样的属性 。
3、混合不同的数据类型会导致性能问题，即使没有性能问题，隐式的类型转换也能导致难以察觉的错误 。
4、选择最小的数据类型要考虑将来留出的增长空间 。如，中国的省份，我们知道不会有成千上万个，因此不必用INT 。TINYINT就足够了，它比INT小3字节 。
5、整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT 。
6、要尽可能避免字符串做为列的数据类型，因为它们占用了很多空间并且通常必整数类型要慢 。MyISAM默认情况下为字符串使用了压缩索引，这使查找更为缓慢 。
总结
若有错误，望请大侠指教一、二，不胜感激！
参考文献:《高性能MYSQL》 |
咱们再看看varchar
