Schema与数据类型优化

Schema:是数据库对象的集合(比如用户建立了表，索引，视图，存储过程等对象，那么这些对象就构成了schema)

 

应根据系统将要执行的查询语句来设计schema，往往需要权衡各种因素。

反范式的设计可以加快某些类型的查询（同时可能使另一些类型的查询变慢）

添加计数表和汇总表可以优化查询（这些表的维护成本可能会很高）

 

选择数据类型的原则：

1.小的好 ：在可以正确存储数据的前提下，越小越好（比如能用tinyint就不用int，因为小的占用更少磁盘，内存和cpu缓存，处理时需要的CPU周期也更少）

2.简单好 ：整型比字符型操作代价更低，使用MySQL内建类型（DATETIME和TIMESAMP）而不是字符串存储时间日期，用整型存储IP（TIMESAMP只使用DATETIME一半的存储空间，并且会根据时区变化，能自动更新，前者允许的时间范围小得多，有时这些特殊能力会成为障碍）

3.避免null：若查询中包含可为null的列更难优化，因为该列使得索引，索引统计和值比较都更复杂，会使用更多存储空间，并且需要特殊处理。当可为Null的列被索引时，每个索引记录需要一个额外字节【在MyISAM里甚至还可能导致固定大小的索引变成可变大小的索引】

（通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以没有必要首先处理这种情况，除非确定会导致问题。若计划在列上建索引，就应尽量避免设计成可为NULL的列）

【INNODB使用单独的位（bit）存储NULL，所以对于稀疏数据（很多值为NULL，只有少数列为非NULL值）有很好的空间效率。但这点不适用于MYISAM】

 

各种数据类型的注意点：

1.整数类型：

（1）有无符号类型使用相同的存储空间，并具有相同性能。

（2）MYSQL可以为整数类型指定宽度，例如INT（11），不过该宽度只是规定了MYSQL一些交互工具（例如MYSQL命令行客户端）的显示字符数，对于存储和计算来说INT(1)和INT(20)并无不同

 

2.实数类型：

实数是带有小数部分的数字。但并不只是为了存储小数部分，也可用DECIMAL存储比BIGINT还大的整数

（1）FLOAT 和 DOUBLE 支持浮点运算进行近似计算。

（2）DECIMAL 用于存储精确的小数。可以指定小数点前后所允许位数（会影响空间消耗）

【MYSQL5.0和更高版本支持精确计算，由于CPU不支持对DECIMAL的精确计算，MYSQL服务器自身实现了DECIMAL的高精度计算。（CPU支持原生浮点计算，所以浮点运算明显更快）】

【浮点类型在存储同样范围的值时，通常比DECIMAL使用更少空间。MYSQL使用 DOUBLE 作为内部浮点计算类型】

建议：

（1）只在需要对小数进行精确计算时使用 DECIMAL， 避免额外空间和计算开销；

（2）在数据量比较大时，用BIGINT代替DECIMAL，在存取时乘相应倍数即可

 

3.字符串类型：

　　下面描述假设存储引擎是InnoDB / MyISAM：

（1）varchar和char：

• varchar更节省空间【仅使用必要空间，使用1到2个字节记录字符串长度（列长度≤255字节时用1个,否则2个）】
• varchar节省空间，所以对性能也友好（但是update时如果行占用空间增长并且在页面内没有更多空间可存储需要额外工作）
• mysql5.0+ 会保留末尾空格，4.1以前会剔除末尾空格；char所有版本都是剔除末尾空格

适合使用varchra：

　　1. 字符串列的最大长度比平均长度大很多

　　2. 列的更新很少

　　3. 使用类似utf-8的字符集，每个字符都使用不同字节存储

注：varchar(200)和varchar(5)存储'hello'开销是相同的，但是会耗更多内存（mysql会分配固定大小的内存块保存内部值，这样当使用内存临时表或利用磁盘临时表排序时会很糟糕）

适合使用char:

　　1. 很短的字符串（非常短的列char比varchar存储空间有优势，因为varchar存储长度还需要字节）

　　2. 同列所有值长度相近

　　3. 经常变更的数据