MySQL性能优化--Schema数据类型优化
优化数据类型
选择正确的数据类型对于高性能至关重要；
原则：
1.更小的通常更好
2.简单就好
整数比字符串操作代价更低（字符集和校对规则）；
使用MySQL内建的类型而不是字符串来存储日期和时间；
用整数存储IP地址；
3.尽量避免NULL
查询中包含可为NULL的列，不好优化，可为NULL的列使得索引、索引统计和值比较更为复杂；
当可为NULL的列被索引时，每个索引记录需要一个额外的字节；
如果计划在列上建索引，应尽量避免设计成可为NULL的列；
整数类型
TINYINT：8位
SMALLINT：16位
MEDIUMINT：24位
INT：32位
BIGINT：64位
可选UNSIGNED属性，表示不允许负值
可以为整数类型指定宽度，例如INT（11），不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显示字符的个数；
实数类型
实数是带有小数部分的数字；
DECIMAL不止可以存储小数部分，也可以用来存储比BIGINT还大的整数；
DECIMAL类型用来存储精确的小数；在MySQL5.0和更高版本，DECIMAL类型支持精确计算；
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算；
采用float和double是不准的:
For FLOAT, the SQL standard permits an optional specification of the precision (but not the range of the exponent) in bits following the keywordFLOAT in parentheses. MySQL also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in a 4-byte single-precision FLOAT column. A precision from 24 to 53 results in an 8-byte double-precision DOUBLEcolumn.
FLOAT和DOUBLE等非标准类型，在DB中保存的是近似值，而DECIMAL则以字符串的形式保存数值
字符串
VARCHAR和CHAR类型
VARCHAR
用于存储可变长字符串，比定长类型更加节省空间；
使用1个或者2个额外字节记录字符串的长度，VACHAR(10)需要10+1个字节的存储空间，VARCHAR(1000)的列需要1000+2个字节；
可能的弊端：
UPDATE时可能使行变得比原来长，导致需要额外的工作；MyISAM将行拆成不同的片段存储，InnoDB则需要分裂页；
什么时候使用VARCHAR：
1.字符串列的最大长度比平均长度大很多；
2.列的更新很少（碎片不是问题）；
3.使用了UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储；
CHAR
定长；
根据定义的字符串长度分配足够的空间；
当存储CHAR值时，会删除所有的末尾空格；
适合存储很短的字符串；
使用VARCHAR(5)和VARCHAR(200)存储“hello”的空间开销是一样的，那么使用短的列有什么优势？
更长的列消耗更多的内存，MySQL通常分配固定大小的内存块来保存内部值；使用内存临时表排序或操作时会有更多内存消耗
BLOB和TEXT类型
为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储；
MySQL把每个BLOB和TEXT值当作一个独立的对象处理，存储引擎在存储时会做特殊处理；
当BLOB或TEXT太大时，InnoDB会使用专门的外部存储区域来进行存储，此时每个值在行内需要1-4个字节存储一个指针， 然后在外部存储区域存储实际的值；
关于排序： 只对每个列的最前max_sort_length字节而不是整个字符串做排序；
order by substring(column, length)
关于索引： 不能将列的全部长度的字符串进行索引；
ENUM枚举
使用枚举代替常用的字符串类型
create table t(e enum("dev", "test", "admin") not null);
insert into t(e) values("dev"), ("test"), ("admin");
mysql> select e+0 from t;
+-----+
| e+0 |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
3 rows in set (0.00 sec)
枚举字段是按照内部存储的整数而不是定义的字符串进行排序的；
mysql> select * from t order by e;
+-------+
| e |
+-------+
| dev |
| test |
| admin |
+-------+
3 rows in set (0.00 sec)
按照字符的顺序来定义枚举；
日期和时间
DATETIME
YYYYMMDD HHMMSS
ANSI标准定义的日期和时间表示方法；
保存大范围的值，从1001到9999，精度为秒；
与时区无关，使用8个字节的存储空间；
TIMESTAMP
保存从1970.1.1以来的秒数，和UNIX时间戳相同；使用4字节的存储空间；只能表示从1970到2038；
显示的值依赖于时区；
除了特殊行为之外，通常应该尽量使用TIMESTAMP，空间效率更高；
位数据类型
BIT
在MySQL5.0之前，BIT是TINYINT的同义词；但是在5.0之后版本，这是一个特性完全不同的数据类型；
BIT列的最大长度是64位；
mysql> create table bit_test(a bit(8));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into bit_test values(b'00110011');
Query OK, 1 row affected (0.01 sec)
mysql> select a,a+0 from bit_test;
+------+------+
| a | a+0 |
+------+------+
| 3 | 51 |
+------+------+
1 row in set (0.00 sec)
MySQL把BIT当作字符串类型，而不是数字类型；
应该谨慎使用BIT类型；
SET
如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型； 它在MySQL内部是以一系列打包的位的集合来表示的；可以有效利用存储空间；
选择标识符–identifier
为标识列选择合适的数据类型
整数类型：通常是标识列最好的选择，很快并且可以使用AUTO_INCREMENT；
如果存储UUID值，则应该移除‘-’符号，或者使用UNHEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY(16)列中；
UUID值虽然分布不均匀，但还是有一定顺序，不同于加密散列函数生成的值；
注意：
对象关系映射（ORM）系统是一种常见的性能噩梦； 一些ORM系统会存储任意类型的数据到任意类型的后端数据存储中； 通常没有设计使用更优的数据类型来存储；
特殊类型数据
某些数据的类型并不直接与内置类型一致；
低于秒精度的时间戳；
IPv4地址
Schema设计中要注意的点
1.太多的列；
2.太多的关联
3.全能的枚举；
4.变相的枚举；
5.Not Invent Here的NULL
范式和反范式
第一范式：只要是关系型数据库的表，都满足第一范式。 性质：第一范式的数据表中的所有字段都是单一属性，不可分割。
第二范式：不可使用组合键，确保唯一主键。
第三范式：要求数据表中不存在非关键字段对任一候选关键字段的传递函数依赖，表分开。
在范式化的数据库中，每个事实数据会出现并且只出现一次；相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方；
范式的优缺点
1.范式化的更新操作通常比反范式要快；
2.修改更少；
3.范式的表更小，更好的放在内存，所以执行操作会更快；
4.非范式的结构中可能需要DISTINCT或者GROUP BY；
缺点： 复杂一点的查询通常需要关联；
反范式的优缺点
可以很好的避免关联；
混用范式化和反范式
实际应用中通常需要混用；
最常用的反范式化数据的方法是复制或者缓存；
缓存表和汇总表
加快alter table操作的速度
常见的两种技巧：
1.先在一台不提供服务的机器上执行alter table，然后和提供服务的主库切换；
2.“影子拷贝”，用要求的表结构创建一张和源表无关的新表，然后通过重命名和删除表操作交换两张表；
modify和alter column：
所有的modify操作都会导致表重建； alter table … modify column … ;
alter column会直接修改.frm文件而不涉及表数据；操作会快很多； alter table … alter column xxx ;
change column
总结
-
尽量避免过度设计
-
使用小而简单的合适数据类型，尽可能避免使用NULL
-
尽可能使用系统的数据类型存储相似或相关的值
-
注意可变长字符串
-
尽量使用整型定义标识列
-
范式是好的，但反范式有时也是必须的；
-
注意alter table