mysql笔记01 Schema与数据类型优化

Schema与数据类型优化

1. 选择优化的数据类型

    1). 更小的通常更好：更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理需要的CPU周期也更少。

    2). 简单就好：简单的数据类型的操作通常需要更少的CPU周期。例如：整型比字符串操作的代价更低，因为字符集和校对规则(排序规则)是字符串比较比整型比较更复杂。这里有两个例子：

                       一个是应该使用MySQL内建的类型而不是字符串来存储日期和时间，另外一个是应该使用整型存储IP地址。

    3). 避免使用null：通常情况下最好指定列为not null，除非真的需要存储null。因为null列使得索引、索引统计和值比较都更复杂。可为null的列会使用更多的存储空间，在MySQL中也需要特殊处理。

2. MySQL兼容性支持很多别名，例如INTEGER、BOOL等。他们都是别名，这些别名可能令人不解，但不会影响性能。如果建表时采用数据烈性的别名，然后用show create table检查，会发现MySQL

    报告的是基本类型，而不是别名。

3. 整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别使用8，16,24,32,64位存储空间。它们的存储值的范围从-2的n-1次幂，到2的n-1次幂减一。

    1). 整数类型有可选的额UNSIGNED属性，表示不允许负值，这大致可以使正数的上线提高一倍。

    2). 有符号和无符号类型使用相同的存储空间，并且有相同的性能，因此可以更具实际情况选择合适的类型。

    3). MySQL可以为正数类型指定宽度，例如INT(11)，但大多数应用这是没有意义的。对于存储和计算来说，INT(1)和INT(20)是相同的。

4. 实数类型：实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。

    1). FLOAT和DOUBLE类型支持使用标准的浮点运算进行近视计算。DECIMAL类型用于存储精确的小数。

    2). 浮点类型在存储同样类型的范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储.DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。

    3). 因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将对应的值扩大N倍。

5. 字符串类型：

    1). VARCHAR:它比定长类型更节省空间，因为它仅使用必要的空间。VARCHAR节省了空间，所以对性能也有帮助。但是由于行是边长的，在UPDATE时可能是行变得比原来更长，这就导致需要做额外的工作。

         下面的情况使用VARCHAR是合适的：字符串最大长度比平均长度大很多；列的更新少，所以碎片不死问题；使用了像UTF-8这样复杂的字符集，每个字符使用不同的字节数。

         在5.0或更高的版本中，MySQL在存储和检索时会保留末尾空格。InnoDB则更灵活，它可以把长的VARCHAR存储为BLOB。

    2). CHAR: 定长，当存储CHAR值时，MySQL会删除所有的末尾空格。定长的CHAR类型不容易产生碎片，对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率，VACHAR还有一个记录长度的额外字节。

    3). 记住字符串的长度定义不是字节数，是字符数。多字节字符集会需要更多的空间存储单个字符。

    4). 与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串。二进制字符串中存储的是字节码和不是字符。

         二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较BINARY字符串是，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比字符比较简单的多，所以也就更快。

    5). BLOB和TEXT类型：BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。当BLOB和TEXT值太大时，InnoDB会使用专门的"外部"存储区域来进行存储。

6. 日期和时间类型：MySQL能存储的最小时间粒度为秒。

    1). DATETIME : 这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中。默认显示格式为"2008-02-16 22:37:08"

    2). TIMESTAMP：保存从1970年1月1日午夜依赖的秒数，它和UNIX时间戳相同。只能表示从1970年到2038年。TIMESTAMP因为空间占用小，所以效率更高。

    3). 可以使用BIGINT类型存储微秒级别的时间戳。

7. 位运算：BIT , SET 

8. 选择标识符(identifier，主键) 

    1). 当选择标识列的类型时，不仅仅需要考虑存储类型，还需要考虑MySQL这种类型怎么执行计算和比较

    2). 一旦选择了一种类型，要确保在所有关联表中都使用同样的类型。会用不同数据类型可能导致性能问题，即使没有性能影响，在比较操作时隐式类型转换也可能导致很难发现错误。

    3). 在可以满足值的范围要求，并且预留未来增长空间的前提下，应该选择最小的数据类型。

    4). 整数类型通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT

    5). 如果可能，应该避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。

    6). 对于完全"随机"的字符串也需要多加注意。例如：MD5(),SHAI()或者UUID()产生的字符串。这些函数生成的新值也任意分布在很大空间内，这会导致INSERT和一些SELECT语句很缓慢

    7).如果存储UUID值，则应该移除"-"符号，或者更好的做法是，用UNHEX()函数转换UUID值为16字节的数字，并存储在一个BINARY(16)的列中。检索时再转换回来。

9. 当心自动生成的schema(表)

10. 人们通常使用VARCHAR(15)来存储IP地址。然而，它们实际是32位无符号整数，不是字符串。用小数点将字段分割成四段是为了阅读方便。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()

      和INET_NTOA()函数在这两种表示方法之间转换。

11. MySQL schema 设计中的陷阱：

      1). 太多的列

      2). 太多的关联

      3). 全能的枚举

      4). 变相的枚举

      5). 非此发明的NULL：不要因为不适用NULL值，而走极端，根据实际情况也可以使用NULL

12. 范式的优点和缺点

      优点：

      1). 范式化的更新操作通常比反范式化要快

      2). 修改更少的数据

      3). 范式化的表通常表更小，可以更好地放在内存中，所以执行操作会更快

      缺点：通常需要关联查询，不仅代价昂贵，也可能使一些索引策略无效

13. 反范式的优点和缺点：

     优点：很好的避免关联，更有效的使用索引策略

     缺点：范式的优点，就是反范式的缺点

14. 缓存表和汇总表：

     1). 缓存表：存储那些可以比较容易的从schema其他表获取(但每次获取速度缓慢)数据的表

     2). 汇总表：保存的是使用GROUP BY语句聚合数据的表。实时计算统计值是很昂贵的操作。

     4). 在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，可以保持表不会有很多碎片，以及完全顺序组织的索引。

15. 影子表：指的是在一张真实表"背后" 创建的表。当完成了建表操作后，可以通过一个原子的重命名操作切换影子表和原表。原表尽量保留备份，防止新表出问题。

16. 物化视图：物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。MySQL并不原声支持物化视图，可以使用开源工具Flexviews。

                   对比传统的维护汇总表和缓存表的方法，Flexviews通过提起对源表的更改，可以增量地重新计算物化视图的内容。

 17. 计数器表：创建一张独立的表存储计数器通常是一个好主意。使用计数器表的一些技巧

       1). 为了防止互斥锁影响效率，可以添加多条记录，随机更新一条记录。统计总数时，将所有数据相加。

       2). 对于需要根据时间更新计数器，也可以用上述方法，不过多加一步操作。每天定时将前一天的总数合计起来，插入到计数器表，删除那些零散的统计记录。

18. 加快ALTER TABLE操作的速度：MySQL的ALTER TABLE操作的性能对大表来说是一个问题。MySQL执行大部分修改表结构的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。

      可以通过ALTER COLUMN操作来改变列的默认值。这个语句会直接修改.frm文件而不涉及表数据。所以，这个操作非常快。