高性能mysql学习笔记之----第四章：Schema与数据类型优化

1、良好的逻辑设计和物理设计师高性能的基石

2、反范式的设计可以加快某些类型的查询

3、应该尽量使用可以正确存储数据的最小数据类型

4、整型字符操作代价更低

5、尽量避免用null，通常情况下最好指定列为not null，并设置默认值，因为可为null的列是得索引、索引统计和值比较都变的更复杂。特别是计划在列上建索引就更应该避免设置为null

6、timestamp只使用datetime一半的存储空间，当然允许的时间范围也要小得多，并且timestamp会根据时区变化，具有特殊的自动更新能力

7、类型（1个字节（byte）等于8位（bit），位上面存的是二进制0或1，有符号类型会用一个字节来存符号，变长类型会拿一个或多个字节来存长度）

1、整数类型：tinyint（1个字节，最大不超过255），smallint（2个字节，最大不超过65535），mediumint（3个字节，7位以下），int（4个字节，10位以下），bigint（8个字节，19位）

2、实数类型：因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal（例如存储财务数据），但在数据量比较大的时候，可以考虑用bigint代替decimal，将需要存储的货币单位根据小数的位数乘相应的倍数即可，例如微信支付以分为单位，这样可以同时避免浮点存储计算不精确和decimal精确计算代价高的问题

3、varchar和char类型：varchar存储可变长字符串，它比定长类型更节省空间，需要使用一个到两个字节记录字符串长度，所以varchar最长不超过2个字节所能存储的整数，即smallint最大值65535；char类型是定长，适合存储很短的字符串，或所有制都接近同一个长度，非常适合存储密码，对于经常变更的数据char也比varchar更好，因为定长的char类型不容易产生碎片，对非常短的列，char比varchar在存储空间上也更有效率，因为varchar要一个记录长度的额外字节

4、blob和text类型：都是存储很大的数据的，分别采用二进制和字符方式存储；mysql把每个blob和text值当做一个独立的对象处理，当值太大时，innodb会使用专门的‘外部‘存储区域来进行存储，此时每个值在行内需要1-4个字节存储一个指针；blob没有排序规则和字符集，而text有字符集和排序规则；只对每个列的最前面max_sort_length字节而不是整个字符串做排序

5、enum枚举类型：mysql在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或两个字节中

6、日期和时间类型：datetime能保存大范围的值，从1001年到9999年，精度为妙，与时区无关，使用8个字节的存储空间；timestamp保存了1970年1月1日以来的秒数，只是用了4个字节的存储空间，即int的最大存储值，只能表示1970到2038年，显示跟时区有关系

7、位数据类型：bit：对于大部分应用最好避免使用这种类型；set：集合，和枚举不一样的是枚举时单选，集合是多选，缺点是改变列的定义代价较高

8、减小主键的大小会使的非主键索引额变的更小

9、标识列（identifier colum），又称自增列，不一定要与主键搭配，一个表至多有一个标识列，只能是数字类型，可以通过set auto_increment_increment设置步长，即每次自增的大小，整数通常是标识列最好的选择

10、一旦选定了一种类型，要确保所有的关联表都使用同样的类型，混用不同的数据类型会导致性能问题

11、mysql schema设计应该注意的：不应该有太多的列；不应该有太多的关联，特别对于数据量大的表应该尽量避免关联查询，单个查询最好在12个表以内做关联（个人觉得12个太多了！！！）；注意防止过度使用枚举

12、全文搜索引擎：Lucene或者Sphinx，最近发现如果要求不高5.7内置的ngram也是可以用的

13、物化视图：我所理解的物化视图就是每次当值有改变的时候去更新统计表，而不是每次需要展示统计数据的时候用count去查询；用触发器和存储过程可以实现物化视图

14、计数器表：比如记录网站的点击次数就可以用计数器表来实现（可以预先增加100行数据，然后选择随机行来进行更新最后进行汇总，这样来解决并发问题）

15、on duplicate key update 如果存在则更新

16、modify column 操作都将导致表重建，操作会很慢，alter column 语句会直接修改.frm文件而不涉及表数据，这个操作是非常快的，alter column 只能对默认值做修改