MySQL学习day2

一、数据类型优化

选择数据原则

1、更小的通常更好

        更小的数据类型通常更快，因为它们占用更少的磁盘，内存和cpu缓存，并且处理时需要的CPU周期更少。

2、简单

        简单的数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作的代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。

3、尽量避免null

对于MySQL来说，可有null的列更难优化，因为null的列使得索引，索引统计和值比较都更复杂。可为null的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为null的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里可能还会导致固定大小的索引变成可变大小的索引。

二、常用数据类型

整数类型

1、整数类型分为：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8，16，24，32，64位存储空间。范围到，其中n是位数。

2,、可选属性UNSIGNED，表示不为负值，大概使正数上限提高了一倍。

实数类型

1、DECIMAL类型：因为CPU不支持对DECIMAL的直接计算，所以MySQL5.0以及更高版本，MySQL服务器实现了DECIMAL的高精度运算。相对而言，CPU支持原生浮点数的计算，因此浮点类型运算更快一些。

2、浮点和DECIMAL都可以指定精度。但对于DECIMAL列，可以指定小数点后边所允许的最大位数。这会影响列的空间消耗。MySQL5.0及更高版本将数字打包成一个二进制的字符串中（每4个字节存九位数字） 例如： DECIMAL(18,9)，18表示一共18位，9表示小数点后边9位，因此小数点前后各九位。一共使用9个字节。小数点前后各4个字节，小数点1个字节。

3、具体占多少字节没有找到，但是与浮点类型相比存同样的数据DECIMAL占用的空间更少。最多允许65个数字，也就是最大值为小数点前为65位，小数点后0位。

字符串类型

varchar

1、varchar类型用于存储可变长的字符串。比定长更节省空间（越短的字符串使用越少的空间）。

2、使用1个或2个字节存储字符串长度。字符串长度小于等于255个字节使用1位，大于则是用2位。若是非空字段还要1位存非空标识。 varchar的单位是字符，因此vachar（）括号里最大能写多少要看具体编码集具体公式为x*（编码集的存一个字符需要的字节）<65535-3。

3、节省了空间，所以对性能有所帮助。但是也也发了一定问题，当修改数据时使数据变得更长，这就需要额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间存储，不同引擎会有不同的处理方式。例如，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行放进页内。

4、varchar（10）和varchar（100）存‘hello’的空间开销（磁盘）是一样的。不同的是更长的列会更消耗内存，因为MySQL会分配固定长度（定义大小）的内存块保存内部值，尤其是对内部临时表进行排序或操作时特别糟糕。

char

1、定长，MySQL会根据定义的字符串长度分配足够的空间。MySQL会删除所有的末尾空格。

2、对于定长的数据char比varchar更好，因为char不容易产生碎片。例如，MD5加密的数据。

 BLOB和TEXT类型

1、BLOB和TEXT是为存储很大数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

2、当数据太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存一个指针，然后外部存储区存实际的数据。

日期和时间类型

DATATIME

1、保存大范围的值，从1001年到9999年，精度为秒。把日期封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。

2、默认情况下，MySQL以一种无排序的无歧义的格式显示例如，“2018-01-16 22:37:08”

TIMESTAMP

1、时间戳保存了从1970年一月一日午夜（格林尼治标准时间）以来的秒数，和Unix时间戳一样。时间戳只是用了四个字节，因此范围比DATIME小：只能表示从19070到2038年。MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转为日期，并提供UNIX_TIMESTAMP()函数把日期转为时间戳。