高性能MYSQL（学习笔记）-Schema与数据类型优化1

Schema与数据类型优化

选择优化的数据类型

选择正确的数据类型对于获得高性能至关重要，下面几个原则可以帮助你做出更好的选择。

更小的通常更好

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

简单就好

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

尽量避免NULL

可以为NULL的列需要更多的存储空间，在MySQL里面也需要特殊处理，当可以为NULL的列被索引时，每个索引记录需要一个额外的字节。

在为列选择数据类型时，第一步需要确定合适的大类型：数字、字符串、时间等，下一步是选择具体类型，例如TIMESTAMP和DATETIME都可以存储相同类型的数据，但是TIMESTAMP只使用了DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。

整数类型

有两种类型的数字：整数和实数。如果存储整数，可以使用这几种整数类型：TINYINT（8位）,SMALLINT（16位）,DEDIUMINT（24位）,INT（32位）, BIGINT（64位）存储空间。

整数类型可以有UNSIGNED,表示不允许出现负值，这大致可以使正数的上限提高一倍，例如TINYINT UNSIGNED 可以存储的范围是：0-255。MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL交互工具来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是一样的。

实数类型

     实数是带有小数部分的数字,FLOAT和DOUBLE类型支持使用标准的浮点运算和近似计算。DECIMAL类型用于存储精确的小数，DECIMAL(18,9)表示小数点两边将各存储9个数字，一共使用9个字节：小数点钱的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。有多种方法可以指定浮点列所需要的精度，这会使得MySQL使用不同的数据类型，或者在存储时对值进行取舍。

字符串类型

MySQL可以对每个字符串列定义自己的字符集和排序规则，或者说校对规则。

VARCHAR 和 CHAR类型

VARCHAR类型用于存储可变长字符串，是最常见的字符串类型。它比定长类型更节省空间，因为它仅使用必要的空间（越短的字符串使用越少的空间）VARCHAR需要额外的1或者2个字节记录字符串的长度：如果列的最大长度小于或者等于255字节，则只使用1个字节表示，否则使用2个字节。例如VARCHAR(10)的列，需要11个字节的存储空间。但是在UPDATE时候需要使行变得更长，导致需要额外的工作。下面这些情况使用VARCHAR是合适的：字符串列的最长长度比平均长度大很多；列的更新很少，碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

CHAR 类型是定长的：MySQL总是根据定长的字符串长度分配足够的空间。当存储CHAR值时，CHAR值会根据需要采用空格进行填充以方便比较。CHAR适合存储很短的字符串，或者所有值都接近同一个长度，对于经常更改的数据，CHAR比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。

慷慨是不明智的~

VARCHAR(5)和VARCHAR(200)存储‘HELLO’空间开销是不一样的，更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值，尤其是使用内存临时表进行排序或操作时会特别糟糕。

BOLO和TEXT类型

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

他们属于不同的数据类型家族，

字符类型：  TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT

二进制类型：TINYBLOB,SMALLBLOB,BLOB,MEDIUMBOLOB,LONGBLOB

MySQL将BLOB和TEXT当做一个对象来处理，当BLOB和TEXT太大时，INNOBD会专门用外部存储区域来进行存储，此时每个值在行内需要1-4字节存储一个指针，然后在外部存储区域存储实际的值。

BLOB存储的是二进制数据，没有排序规则和字符集，而TEXT有字符集和排序规则。尽量不要使用TEXT和BLOB

使用枚举（ENUM）代替字符串类型

    有时候可以使用枚举列代替常用的字符串类型，枚举列可以把一些不重复的字符串存储成一个预定义的集合，MySQL在存储时候非常紧凑，会根据列表值的数量压缩到一个或两个字节中，内部存储是整数而不是字符串。

日期和时间类型

DATETIME：这个类型能保存大范围的值，从1001年到9999年，精度为妙，它把日期和时间封装格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节存储空间。

TIMESTAMP：TIMESTAMP类型保存1970年1月1日午夜以来的妙数，它和UNIX时间。使用4个字节存储空间。只能存储1970年到2038年。尽可能使用TIMESTAMP，存储空间效率更高。

位数据类型

BIT列在一列中存储一个或多个true/false值，BIT(1)定义一个包含单个位的字段，BIT(2)存储2个位，BIT最大的长度是64位，MySQL把BIT当作字符串类型而不是整数类型，检索BIT(1)结果是一个包含二进制0或者1而不是ASCII的“0”或者“1”。应该谨慎使用BIT类型！

选择标识符

标识符选择合适的数据类型非常重要，通常作为关联表的外键来使用，整数通常是标识列的最好选择，因为他们很快并且可以使用AUTO_INCREMENT

ENUM和SET类型，尽量不使用

字符串类型：避免使用，它们很消耗空间，而且比数字类型慢。对性能有较大的影响！

当使用随机的字符串也需要多加注意，例如MD5()/SHA1()/UUID()产生的字符串。

随机插入值会随机地写到索引的不同位置，所以使用INSERT语句变慢，会导致页分裂、磁盘随机访问，SELECT语句变得很慢，逻辑上相邻的行会分布到磁盘和内存的不同地方。

随机值导致缓存对所有类型的查询语句效果比较差。

特殊类型数据，IPV4地址经常使用VARCHAR(15)列来存储IP地址，然而他们实际上是32位无符号整数，不是字符串。用小数点将地址分成四段表示是为了让人们阅读容易。应该使用无符号整数存储IP地址，mysql使用INET_ATON（）和INET_NTOA()函数在这两种表示方法之间转换。

MySQL schema设计中的陷阱

太多的列：MySQL的存储引擎API工作时需要服务器层和引擎层之间通过缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。

太多的关联：所谓的实体-属性-值设计模式是一个常见的糟糕设计模式，MySQL最多只能关联操作61张表，太多的关联解析和优化查询代价很高，建议每一个查询最好在12个表内做关联。

全能的枚举：枚举设计应该是用整数作为外键关联到字典表后者查找表来查找具体值。

非此发明的NULL：需要存储一个空值到表中时候，也许可以使用0，某个特殊值、空字符串来代替。避免使用NULL！

范式和反范式

     范式化的数据库中，每个事实数据会出现并且出现一次，反范式化中，信息是冗余的，可能存储在多个地方。

范式化的优点和缺点

优点：

1、  范式的更新操作通常比反范式更快

2、  当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据

3、  范式化的表通常很小，可以更好地放在内存里，所以执行速度更快

4、  没有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句，在非范式画的结构中需要通过GROUP BY 来获得唯一的部门表。

缺点：

范式化设计schema通常需要关联，稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，可能导致一些索引策略无效.

反范式的优点和缺点：

反范式的schema因为所有数据都在一张表中，可以很好的避免关联。不需要关联的时候，最差情况是全表扫描，比关联要快很多，因为避免了随机I/O

单独的表也能使用更有效的索引策略。

混用范式话和反范式话

实际应用中经常使用混用，最常见的反范式话数据的方法是复制或者缓存，在不同的表中存储相同的特定列，使用触发器更新缓存值。当更新数据的时候，特定列需要更新两次，这时候要评估更新的频率和更新的时长，并和执行SELECT 查询的频率进行比较。

另外一个从父表中冗余数据到字表是由于排序的需要，例如在范式化的schema里面通过作者的名字对消息走排序的代价将会非常高，但是如果在message中缓存author_name字段并建立好索引，则可以非常高效的完成排序。

  总结

MySQL始终坚持简单，需要使用数据库的人应该也同样会喜欢简单的原则：

1、  尽量避免过度设计

2、  使用小而简单的合适数据类型，除非真的需要，尽可能避免使用NULL值。

3、  尽量使用相同数据类型存储相似或者相关的值，尤其是在关联表中使用的列

4、  注意可变长字符串，在临时表和排序时可能导致悲观的按最大长度分配内存

5、  尽量使用整型定义标识列

6、  避免使用MySQL丢弃的特性，例如指定浮点数精度、整数的显示宽度

7、  小心使用ENUM和SET，最好避免使用SET.

范式是好的，但是非范式也是必须的。