1. 选择优化的数据类型：
--------遵循下面几个简单的原则--------：
1. 更小的通常更好：
一般情况下，尽量使用可以正确存储数据的最小数据类型。更小的通常更快，因为他们占用更少的的磁盘，内存和CPU缓存，处理时需要的CPU周期更短。
2. 简单就好：
简单的数据类型需要更少的CPU周期。例如整形比字符串操作代价更低，因为字符串和校对规则使字符串比整形更复杂。比如：应该是用mysql内建的类型（date， time, datatime）而不是字符串来存储日期和时间。使用整形来存储IP地址。
3. 尽量避免null
很多表都包含NULL（空值）的列，即使应用程序不需要保存null也是如此，因为列的默认值是null，通常情况下，最好指定为NOT NULL，除非真的需要存储null值。
如果查询中包含为NULL的列，对MySQL来说更难优化，因为NULL的列使得索引，索引统计和值比较都更复杂。
==============================数据类型优化================================
1.1 整数类型：
可以使用以下几种类型存储： TINYINT， SMALLINT，MEDIUMINT， INT， BIGINT。他们分别使用8，16, 24， 32, 64位存储空间。整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍，例如TINYINT UNSIGNED可以存储的范围是0 ~ 255， 而TINYINT的存储范围是-128 ~ 127。他们使用的存储空间相同，具有相同的性能。
MySQL可以为整数类型指定宽度，例如INT(11), 对大多数应用是没有意义的。它不会限制值得合法范围，只是规定了MySQL的一些交互工具（例如MySQL的命令行客户端）用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。
1.2. 实数类型：
带有小数部分的数字。然后，它们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的正数。
DECIMAL是定点长度，FLOAT（占4个字节）和DOUBLE（占8个字节）是浮点。建议只指定数据类型，不指定精度。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算的时候使用DECIMAL--例如存储财务数据。但是在数据量大的时候，可以使用考虑使用BIGINT来代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假如存储财务的数据精确到万分之一，这可以把所有金额乘以一百万，然后将计算出来的数存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。
1.3. 字符串类型：
1.3.1: VARCHAR :
VARCHAR 和 CHAR是主要的字符串类型， 不过，很难精确地解释这些值是如何存储在磁盘和内存中的，因为这跟存储引擎的具体实现有关。存储引擎存储CHAR 和 VARCHAR值得方式在内存中和磁盘中可能不一样，所以MySQL服务器从存储引擎读出的值可能需要转换为另一种存储格式。
VARCHAR类型，用于存储可变长字符串，是最常见的数据类型。比定长类型更加节省空间，当然当你的MySQL的表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，造成空间浪费。
VARCHAR需要 1 或 2 个额外字节来记录字符串的长度。如果列的最大长度小于或等于255字节，则使用1个字节来记录表示，否则使用2个。
VARCHAR虽然节省了空间，但是在UPDATE时，可能使行变的比原来更长，这就导致了额外的工作。如果一个行占用的空间增长，并且页内没有更多空间可以存储，这种情况下不同的引擎处理方式不一样。例如：MyISAM会将行拆成不同片段存储，InnoDB则需要分裂页来使行可以放进页内。
下面这些情况使用VARCHAR是合适的：
1.字符串的最大长度比平均长度大很多。 2.列的更新很少，所以碎片不是问题。 3.使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
在MySQL5.0或者更高版本中，MySQL回存储和检索时会保留末尾的空格。但在4.1之前的版本中会删除末尾的空格。
1.3.2： CHAR：
CHAR类型是定长的。存储CHAR的时候，MySQL会删除所有末尾的空间。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储上也更有效率。例如CHAR（1）来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR（1）却需要两个，因为需要记录长度的额外字节。
使用VARCHAR（5）和 VARCHAR（200）来存储‘hello’的空间开销是一样的。但是事实证明更短的列有很大的优势，更长的列会消耗更多的内存。因为MySQL通常会分配固定大小的内存块来保存内部值，最差的情况会导致MySQL分配200位来存储临时表和排序。
1.3.3： BLOB 和 TEXT：
都是用来存储很大的数据而设计的字符串类型，分别采用二进制 和 字符串方式存储。
与其他类型不同，MySQL会把每个BLOB 和 TEXT当做独立的对象进行处理。当BLOB 和 TEXT 值太大时，InnoDB会使用专门的 “外部”存储区域来进行存储，此时每个值在行内需要 1 ~ 4个字节来存储一个指针，然后在外部存储区域存储实际的值。
BLOB 和 TEXT 唯一的不同就是，BLOB存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。
MySQL对BLOB和TEXT列进行排序的时候，只对每个列的最前max_sort_length字节而不是整个字符串进行排序。如果想减少排序的字符串，可以减少max_sort_length的配置，或者使用ORDER BY SUSBTRING（column， length）。
MySQL不能对BLOB 和 TEXT列的全部长度进行索引。
1.4 使用枚举类型代替字符串类型：
很少用。
1.5 日期和时间类型：
MySQL可以使用许多类型来保存日期和时间值。例如YEAR和DATE。MySQL能存储的最小时间粒度是秒，但是MySQL也可以使用微妙级的粒度进行临时运算。
1.5.1 DATETIME类型：
这个类型能保存的值得范围，从1001年到9999年，精度为妙。他把日期封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节存储空间。默认情况下MySQL以一种可排序的，无歧义的格式显示DATETIME值，例如“2008-01-16 22:37:08”。这是ANSI标准定义的日期和时间表示方法。
1.5.2 TIMESTAMP类型：
保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，和UNIX时间戳相同。TIMESTAMP只使用了4个字节的存储空间，因此范围比DATETIME小得多。只能表示1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日起转为Unix的时间戳。
TIMESTAMP的显示和时区有关。同一个时间戳，在不同的时区显示的值不一样。TIMESTAMP默认不为NULL。不推荐把时间戳保存为整数。
timestamp只使用datetime一半的存储空间。
1.6 位数据类型：
很少使用。
1.7: 反范式的优点和缺点：
反范式化的schema都在一张表中，可以很好的避免关联。
如果不需要关联表，则对大部分查询最差的情况（即使没有使用索引，是全表扫描）。当数据比内存大时这可能要比关联要快得多，因为这样避免了随机IO。（全表扫描的话基本都是顺序IO，但也不是100%，和存储引擎有关）
1.8： 范式化的优点和缺点：
当为性能问题需求帮助时，经常会被建议使用schema进行范式化设计，尤其是写密集的场景。
范式化带来的好处：
1.范式化的更新操作通常比反范式化要快。
2.当数据较好的范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
3.范式化的表通常比较小，可以更好的放在内存里，所以执行操作也很快。
4.很少有多余的数据，意味着检索表数据的时候更少需要distinct或者group by语句。
坏处：
1.通常需关联。稍微复杂的语句可能需要关联很多张表，代价比较昂贵，而且会使一些索引策略失效。例如：范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。
MySQL设计原则总结：
1. 尽量避免过度设计，例如导致极其复杂的查询schema设计，或者很多列的表设计。
2. 使用小而简单的合适数据类型，尽量避免使用NULL值。
3. 尽量使用相同的数据类型来存储相似或者相关的数据，尤其是关联查询中使用的列。
4. 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
5. 尽量使用整形定义标识列。
6. 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
网友评论