(二)、MySQL中Schema与数据类型优化

schema为模式，指数据库对象集合，包括表、视图等对象。

一、选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。下列是几个关于选择类型的原则。

      更小的通常更好
        一般情况下，应该使用可以存储数据的最小数据类型。更小的数据类型通常更快，因为他们
        占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少。

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

        尽量避免NULL
                很多表都包含可为NULL的列，即使应用程序并不需要保存NULL也是如此，因为NULL是
        列的默认属性。通常情况下最好指定为NOT NULL。
                如果查询中包含可为NULL的列，对MySQL来说更难优化，因为NULL的列使得索引、索
        引统计和值比较更加复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处
        理。
                通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以没有必要首先在现有
        schema中查找帮修改这种情况。

1、整数类型

        有两种类型的数字：整数和实数。存储整数可以使用这些类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别使用8、16、24、32、64位存储空间。
        整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如，TINYINT开启UNSIGNED属性可以存储0~255，而没有开启则为-128~127
        有符号和无符号类型使用相同的存储空间，并具有相同的性能，可根据实际情况选择合适的类型。
        MySQL可以为整数类型指定宽度，例如int(11)，对于大多数应用是没有意义的：它不会限制值的合法范围。只是规定了交互工具显示字符的个数，对于存储和计算来说，int(1)和int(20)是相同的。

2、实数类型

        实数是带有小数部分的数字。MySQL既支持精确类型，也支持不精确类型。float和double类型支持使用标准的浮点运算进行近似计算。如果需要知道浮点运算时怎么计算的，则需要研究所使用的平台的浮点数的具体实现。
        decimal类型用于存储精确的小数。在MySQL5.0或更高版本，decimal类型支持精确计算。因为CPU不支持decimal的直接计算，所以在MySQL5.0以及跟高版本中，服务器自身实现了decimal的高精度计算。相对而言，CPU直接支持原生浮点数计算，所以浮点数运算明显更快。
        因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal。但在数据量大时，可以考虑使用bigint代替，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

3、字符串类型

        varchar
        varchar类型用于存储可变长字符串。它比定长类型更节省空间，因为它仅使用必要的空间。
        varchar需要使用1或2个额外字节记录字符串长度：如果列的最大长度小于或等于255，则只
        需要1个字节表示，否则需要2个。
        由于行是变长的，在update时可能使行变得比原来更长，这就需要做额外的工作。如果一个
        行占用的空间增长，并且在页内没有更多的空间可以存储。在这种情况下，不同存储引擎室
        不一样的。例如MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使用可以放进
        页内。

        char
        char类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储char值
        时，MySQL会删除所有的末尾空格。char值会根据需要采用空格进行填充以方便比较。

使用varchar(5)和varchar(255)存储“aaa”的空间开销是一样的。那么短的有什么优势。更长的列会消耗更多内存，因为MySQL通常会分配固定大小的内存块来保存内部值。

        BLOB和Text类型
                blob和text都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式
        存储。
                MySQL把每个Blob和text值当作一个独立的对象处理。存储引擎在存储时通常会做特殊
        处理。当Blob和Text值太大时，InnoDB会使用专门的“外部”存储区域来存储，此时每个值在行
        内需要1~4字节存储一个指针，然后在外部存储区域存储实际的值。
                MySQL对这两种类型进行排序时，只会对每个列的最前max_sort_length字节做排序。

4、日期和时间类型

                MySQL能存储的最小时间细粒度为秒。MySQL提供两种相似日期类型：DATETIME和
        TIMESTAMP。
                DATETIME保存最大范围的值，1001~9999年，精度为秒。它把日期和时间封装到格
        式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。
                TIMESTAMP：TIMESTAMP之使用4个字节的存储空间，范围为1970到2038年

5、位数据类型

                BIT：bit(1)定义一个包含单位的字段，bit(2)存储2个位。bit列最大的长度是64个位。bit
        的行为因存储引擎而异。MyISAM会打包存储所有的bit列，所以17个单独的bit列只需要17个
        位存储，MyISAM只需要3个字节就能存储这17个bit列。其他存储引擎Memory和InnoDB，为
        每个bit列使用一个足够存储的最小整数类型存放，所以不能节省存储空间。
                MySQL吧bit当作字符串类型，而不是数字类型。当检索bit(1)的值时，结果是一个包含二
        进制0或1值的字符串，而不是ASCII码的”0“或”1“。然而，在数字上下文的场景中检索时，结
        果是将位字符串转换成的数字。
                如果想在一个bit的存储空间存储一个true/false值，另一个方法是创建一个可以为空
        char(0)列。

                SET：如果需要保存很多true/false值，可以考虑合并这些列到一个set数据类型，他在
        MySQL内部是以一系列打包的位的集合来表示的。这样就有效利用了存储空间，并且MySQL
        有像find_in_set()和field()函数来查询使用。它主要缺点是改变列的定义的代价较高：需要
        alter table。

6、选择标识符

        为标识列选择合适的数据类型是非常重要的。
                1、相关属性选择相同的类型
                2、选择时需要考虑存储类型，以及这种类型在MySQL中是怎么计算的。
                3、在满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型

整数类型
通常是标识列最好的选择，因为他们很快并且可以使用auto_increment

enum和set
他们适合存储固定信息，如有序的状态、产品类型、人的性别

        字符串
                应该避免使用字符类型作为标识列，因为他们很消耗空间，并且通常比数字类型慢。尤
        其是在MyISAM表里使用，MyISAM默认对字符串使用压缩索引，这会导致查询慢。
                对于完全随机的字符串也需要注意，例如md5()、sha1()、uuid()产生的字符串。这些函
        数生成的新值分布在很大的空间内，这会导致insert以及一些select语句变慢。

因为插入值会随机的写到索引不同位置，所以使得insert语句更慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇碎片。
select语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问局部性原理失效。

7、特殊类型数据

对于IPv4地址，人们通常使用VARCHAR列来存储。然而他们实际上是32位无符号整数，不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易。所以应该使用无符号整数存储IP地址。MySQL提供了inet_aton()和inet_ntoa()函数在这两种表示方法之间转换。

select inet_aton("1.1.1.1") >> 16843009

select inet_ntoa(16843009) >> 1.1.1.1

二、MySQL schema设计中的陷阱

1、太多的列

MySQL的存储引擎API工作时需要在服务层和存储引擎层之间通过缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各列。从行缓冲中将编码过的列转换成行数据结构的操作代价非常高。转换的代价依赖列的数量。

2、太多关联

“实体-属性-值”(EAV)设计模式是一种常见的糟糕的设计模式，尤其是在MySQL下不能靠谱的工作。MySQL限制了每个关联操作最多只能有61张表关联，但是EAV数据库需要许多自关联。事实上在许多关联少于61张表的情况下，解析和优化查询的代价也会成为MySQL的问题。一般单个查询最好在12个表以内做关联。