数据库应用优化（2）

1. 数据库设计
   （1)范式与反范式：为规范DB设计在DB理论发展的过程中，逐渐形成了DB范式的理论，到目前为止有五大范式。到了第三范式通常已经能够满足业务需求了，表之间的关系也比较清晰容易维护。
   （2）反范式的提出：范式理论在20世纪70年代提出并在80拆借基本定型，那时候的系统的特征为：可用的存储器资源极其有限，并且网络不成熟能使用网络的人较少通常只涉及单机的计算性能，所以范式理论强调减少依赖/降低冗余；但现在内在廉价存储不再是问题同时面临高并发业务逻辑复杂低延迟的要求，所以不应一味遵循范式设计理论，应当适当降低范式，增加冗余以空间换时间是值得的，最低可降至第一范式。
   （3）通常在设计数据库时需遵循以下原则：

1）核心业务使用范式。2）弱一致性需求--反范式3）空间换时间4）避免不必要的冗余

2. 数据库分区
   所谓分区就是把一个数据表的文件和索引分散存储在不同的物理文件中，MySQL5.1以上版本才支持分区。可用SHOW VARIABLES LIKE ‘%partition%’来确认是否支持分区。MYSQL支持的分区类型包括Range/List/Hash/Key,其中Range最常用，示例:
   CREATE TABLE foo (id INT NOT NULL AUTO_INCREMENT,created DATATIME,PRIMARY KEY(id,created)) ENGINE = INNODB PARTITION BY RANGE (TO_DAYS(created)) (PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2009-01-01')),PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2010-01-01')))

MYSQL通过分区把数据保存到不同数据文件里，同时索引也是分区的，相对未分区的表来说分区后单独的数据文件和索引文件的大小明显降低，效率明显提升。一条查询/写操作只在相应分区上执行。实际应用分区时，通过DATA DIRECTORY和INDEX DIRECTORY选项把不同分区分散到不同磁盘上进一步提高系统的IO吞吐量。使用分区后最好都用EXPLAIN PARTITIONS过一遍以确认分区是否生效。

通常使用Range分区，但在主从结构中主服务器很少使用SELECT语句在其上使用Range查询并没有太大意义，此时使用Hash类型的分区相对更好：PARTITON BY HASH（id） PARTITION 10,当插入数据时会根据ID把数据平均分散到各个分区上，由于文件小/效率高，更新操作会变得更快。通常按时间字段分区，不过具体情况还是按需求而定。

分区虽然很好，但目前的实现还有很多限制如：
主键或者唯一索引必须包含分区字段，如PRIMARY KEY （id,created），不过对InnoDB来说，大主键性能不好
很多时候使用分区就不要再使用主键否则可能影响性能
只能通过INI类型的字段或者返回INI类型的表达式来分区，通常使用YEAR或TO_DAYS等函数
每个表最多1024个分区，不可能无限制扩展分区，而且过度使用分区会消耗大量系统内存
采用分区的表不支持外键，相关的约束逻辑必须在代码里实现
分区后可能会造成索引失效，需要验证分区可行性

3. 分表的应用
   分表思想和分区类似，区别是：分区是把一个逻辑表文件分成了几个物理文件后进行存储，而分表则是把原先一个表分成几个表。进行分表查询时，可以union或者做一个视图。

分表又分升起切分和水平切分，其中水平切分最常用，水平切分通常指切分到另外的数据库或表中，例如，对一个论坛的会员表，按对10的模进行切分：table=uid%10如果uid%10=0则将用户数据放置到uid_0表中，如uid%10=1则将用户数据放置到uid_1中依次类推。有个问题是uid在这里应该是按序增长的，而自增主键只能是数字的，这时就要用到序列了.对一些流量统计系统，数据量大且对过往数据的关注度不高，可按年/月/日进行分表，或者按增量进行分表如每个表100万条数据超过后放入第二个表中，还可以按HASH进行分表，但按日期裊最常见也较容易扩展。分表后可能遇到新的问题如查询/分布/统计。通用方法是在程序中进行处理，辅助视图。

场景一：
1）
$member_table = 'member' . $uid & 5;
$sql = "select * from {$member_table}";
//查询全部会员数据
$table = array('member0','member1','member2','member3','member4');
$foreach($table as $v) {$sql .= "select * from {$v} union"; }
$sql = substr($sql,0,-5);
这样就关联了所有的表，同理分页的话在这个集合上做limit即可，如分表后的数量相对固定也可以通过一个固定的视图来完成。这样看起来似乎和一个表储存全部数据再取limit一样，但其实不一样，在实际应用中，不可能查看所有的会员资料，一次查看几十个，然后分布，完全没有必要做union，仅查询一个表就够了，唯一需要的考虑的是在分页临界点的是衔接，其实这个衔接也没有那么重要偶尔的几条数据的差异也不会对业务有较大影响。
2）根据会员姓名搜索用户信息，需要搜索所有的表，并对结果进行汇总，虽然这样做产生了多次查询但并不代表效率就代，好的SQL语句执行10次也比差的SQL语句执行1次还快。

场景二：在一个流量监控系统中由于网络将是巨大统计数据很庞大，需要按天分表，现要得到任意日/周/月的数据
1）需要某一天的数据：直接查询当天的数据表即可，
2）需要几天的数据：分开查询这几天的数据然后进行汇总
3）需要查询一周的数据：对一周数据定期汇总到一个week表，从此表查询数据，这个汇总过程可由一个外部程序完成，也可以是定期执行的脚本或者一个数据库的JOB
4）查询一个月的数据：汇总本月所有数据到month表从此表查询
5）查询5个月内的详细数据：不支持。仅支持最多3个月内的详细数据，数据每3个月归档一次。
分表前要估计好规模，先确定好规则再分表。对庞大的数据无论是否进行分表都必须考虑功能和效率的平衡性，并在功能上做出让步。