MYSQL之高性能的mysql（四）--Schema与数据类型优化 - joker1034

Data: 2015-07-30 07:04:21Form: JournalClick: 10

joker1034

MYSQL之高性能的mysql（四）--Schema与数据类型优化 - joker1034 - 博客园

Schema与数据类型优化

更小的通常更好

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

简单就好

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

尽量避免NULL

　　如果计划在列上建索引，就应该尽量避免设计成可为NULL 的列。InnoDB使用单独的位（bit）存储NULL 值，所以对于稀疏数据有很好的空间效率。

整数类型

　　TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT 。分别使用8，16，24，32，64位存储空间。它们可以存储的值的范围从−2^（N−1）到2^（N−1）−1，有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。

实数类型

　　因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL ——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT 代替DECIMAL ，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

字符串类型

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

　　使用CHAR 适合的: 存储很短的字符串; 所有值都接近同一个长度(密码的MD5 值); 经常变更的数据。

　　慷慨是不明智的，比如使用VARCHAR（5）和VARCHAR（200）存储’hello’ 的空间开销是一样的，但是更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。所以最好的策略是只分配真正需要的空间。

BLOB和TEXT类型

　　最好的解决方案是尽量避免使用BLOB 和TEXT 类型。

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

DATETIME和TIMESTAMP

　　DATETIME：保存大范围的值，从1001年到9999年，精度为秒，显示的值不依赖于时区。

　　TIMESTAMP：保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，显示的值依赖于时区。

　　除了特殊行为之外，通常也应该尽量使用TIMESTAMP ，因为它比DATETIME 空间效率更高。有时候人们会将Unix时间截存储为整数值，但这不会带来任何收益。用整数保存时间截的格式通常不方便处理，所以我们不推荐这样做。

　　如果需要存储比秒更小粒度的日期和时间值，可以使用BIGINT 类型存储微秒级别的时间截，或者使用DOUBLE 存储秒之后的小数部分。这两种方式都可以，或者也可以使用MariaDB替代MySQL。

选择标识符（identifier）

　　整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT 。如果可能，应该避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。EMUM 和SET 类型通常是一个糟糕的选择。

MySQL schema设计中的陷阱

太多的列

太多的关联

　　所谓的“实体-属性-值”（EAV）设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱地工作。MySQL限制了每个关联操作最多只能有61张表，但是EAV数据库需要许多自关联。我们见过不少EAV数据库最后超过了这个限制。事实上在许多关联少于61张表的情况下，解析和优化查询的代价也会成为MySQL的问题。一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。

全能的枚举

　　CREATE TABLE ... ( country enum('','0','1','2',...,'31')

变相的枚举

　　CREATE TABLE ... ( is_default set ('Y','N') NOT NULL default 'N'

　　枚举（ENUM）列允许在列中存储一组定义值中的单个值，集合（SET）列则允许在列中存储一组定义值中的一个或多个值。有时候这可能比较容易导致混乱。

非此发明（Not Invent Here）的NULL

　　CREATE TABLE ...(dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' 处理NULL确实不容易，但有时候会比它的替代方案更好。

范式和反范式

范式的优点和缺点

　　当为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景。这通常是个好建议。因为下面这些原因，范式化通常能够带来好处：

　　范式化的更新操作通常比反范式化要快。

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

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

　　很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。

　　范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

反范式的优点和缺点

　　反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机 I/O 。

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

混用范式化和反范式化

　　事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西：在真实世界中很少会这么极端地使用。在实际应用中经常需要混用，可能使用部分范式化的schema、缓存表，以及其他技巧。

总结

　　尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计（很多的意思是介于有点多和非常多之间）。

　　使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL 值。

　　尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。

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

　　尽量使用整型定义标识列。

　　避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。

　　小心使用ENUM 和SET 。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用BIT 。