MySQL之SQL优化(四)

“良好的逻辑设计和物理设计是高性能的基石，应根据系统将要执行的查询语句来设计Schema，这需要权衡各种因素”

 

数据类型：

1.整数类型：

a) tinyint（8），smallint（16），mediumint（24），int（32），bigint（64）

值的范围从-2(n-1) ~ 2(n-1) 

b) 整数类型有unsigned属性，表示无符号整型，表示不允许负值，使正数的上限提高一倍。

制定整型的宽度，如int（11）不会限制整型的值的合法范围，只是规定mysql的某些交互工具用来显示的字符的个数。对于存储和计算来说，int（1）和int（28）没有差别

 

2.实数类型：

实数，带有小数部分的数字

Decimal类型用于存储精确的小数，mysql5.0+的版本中，decimal类型支持精确计算。因cpu不支持对decimal的直接计算，故在mysql5.0+版本中，mysql服务器实现了decimal的高精度计算。相比较，因cpu直接支持原生浮点运算，故浮点运算明显更快。float（4），double（8）

a) 浮点和decimal类型都可指定精度，decimal（18，9）精度18位，保留9位小数点。因需要额外的空间和计算开销，所以尽量只在对小数进行精确计算的时候才使用decimal。在数据量较大时，可以考虑使用bigint代替decimal（将小数位数×相应的倍数），这样做可以同时避免浮点存储计算不精确和decimal精确计算代价高的问题

 

3. 字符串类型：

varchar和char类型，字符串在磁盘和内存中的存储于存储引擎的实现相关，以InnoDB和MyISAM存储引擎为例：

a) varchar，可变长字符串，比定长的字符串节省空间。varchar需要使用1or2个额外的字节记录字符串的长度（当长度小于等于255字节时使用1个字节表示，否则使用2个字节）

varchar节省存储空间，所以对性能有帮助，当时由于变长，当update时可能使行变得比原来长，会导致额外的工作。例如一个行占用的空间增长，且页内没有更多的空间时，myisam会将行拆分成不同的片段存储，innodb则需要分裂页来使行可以放入页内，InnoDB会把过长的varchar存储为Blob

*当字符串列的最大长度比平均长度大很多时；裂的更新很少（此时碎片不是问题）；使用了像utf8这种复杂的字符集，每个字符都使用不同的字节数进行存储，这几种情况下使用varchar较好。

 

b) char，定长字符串，mysql会根据定义的字符串长度分配足够的空间。当存储char值时，mysql会删除末尾的空格。char值会根据需要采用空格进行填充以方便比较。

char适合存储很短的字符串，或者所有值的长度都接近同一个长度的。

例如：

char适合存储密码的MD5值，因为是定长的值

对于经常变更的数据，char也比varchar好，因为定长的char类型不容易产生碎片

对于非常短的列，char也比varchar在存储空间上更有效率

*对char截取和填充空格的行为在不同的存储引擎是相同的，因为该行为是在mysql服务器层进行处理的。

*binary与varbinary类似，但存储的是二进制字符串，即字节码，会填充\0而不是空格，并且在检索的时候也不会去掉填充的\0

*按需分配真正需要的空间，如字段长度等

 

c) blob和text类型

blob采用二进制方式存储，text采用字符方式存储

对应两个不同的数据类型组：

tinyblob，smallblob，blob，mediumblob，longblob。其中smallblob和blob一样

tinytext，smalltext，text，mediumtext，longtext。其中smalltext和text一样

MySQL将blob和text当作独立的对象处理，存储引擎在存储时通常会做特殊的处理，当blob和text值太大时，InnoDB会使用专门外部存储区域来进行存储。此时在每个值的行内要1～4个字节存储一个指针，指向外部实际存储值的区域。

 

blob和text的区别：blob存储的是二进制数据，没有排序规则或字符集。而text类型有字符集和排序规则

 

MySQL对blob和text进行排序与其他类型不同。他只对每个列的前max_sort_length字节而不是整个字符串做排序。可以减小max_sort_length的配置，或使用order by sustring(column, length)来对字符串前面一小部分字符排序

 

MySQL不能将blob和text列全部长度的字符串进行索引，也不能对这些索引消除排序

 

在实际使用中要尽量避免使用blob和text，如果实在无法避免，则在所有用到blob字段的地方都是用subString(column, length)将列值转换为字符串（在order by子句中也适用），此时就可以使用内存临时表了。但要确保截取的子串足够短，不会使临时表的大小超过max_heap_table_size或tmp_table_size，超过后MySQL会将内存临时表转换为MyISAM磁盘临时表。这时最坏的情况下的长度分配对于排序的时候也是一样的。所以这个技巧对于内存中创建大临时表和文件排序，以及在磁盘上创建大临时表和文件排序两种情况都很有帮助。

 

 4. 日期和时间类型，

MySQL能存储的最小时间粒度为秒，但是可以使用微秒级的粒度进行临时运算

a) Datetime，能保存大范围的值，从1001到9999年，精度为秒，将日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节的存储空间，默认情况下MySQL以"2008-01-16 11:30:21"的ANSI标准定义的日期和时间表示法保存时间

 b) Timestamp，保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，和unix时间戳相同。timestamp只使用4个字节的存储空间，范围比datetime小很多。只能表示从1970-2038，且与时区相关。timestamp显示的值也依赖时区，MySQL服务器，操作系统以及客户端连接都有时区设置。