第十章：第31节 MySQL进阶篇——表的拆分

MySQL的进阶篇楠神已讲解了30节，马上就要步入尾声。如果大家能把前面的30节内容消化掉，起码MySQL有那么点水平了，绝对能胜任大部分MySQL工作。但想要成为MySQL大神级的人物还是不够的。

MySQL还有两项重要的优化我们没有讲：表的拆分和读写分离。楠神不想班门弄斧，因为我也没有机会实际操作过。（好可怜，一直工作的都是小平台，根本用不到这些优化手段）对于初学者说，你们不工作几年，有一定经验和能力，也是很难接触到的。小平台流量少，数据少用不着，大平台也不可能轻易把这么重要的任务让小程序员完成。

楠神觉着可以先初步了解下，关键还是要打好（计算机）基础，真有机会来了，现查资料学习起来也不会吃力的。

表的拆分：

在讲表结构设计时，楠神说过“垂直分表”，其实就是把本可以放入一个表的字段，拆分成两个或多个表。因为字段多而去垂直分割。

“水平分表”呢，其实就是因为记录多而去水平分割。

目前很多互联网系统都存在单表数据量过大的问题，这就降低了查询速度，影响了客户体验。为了提高查询速度，我们可以优化sql语句，优化表结构和索引，不过对那些百万级千万级的数据库表，即便是优化过后，查询速度还是满足不了要求。这时候我们就可以通过分表降低单次查询数据量，从而提高查询速度，一般分表的方式有两种：水平拆分和垂直拆分，两者各有利弊，适用于不同的情况。

1）水平拆分 

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。 

通常情况下，我们使用取模的方式来进行表的拆分;比如一张有400W的用户表users，为提高其查询效率我们把其分成4张表users1，users2，users3，users4

通过用ID取模的方法把数据分散到四张表内Id%4+1 = [1,2,3,4] 
然后查询,更新,删除也是通过取模的方法来查询。

例：QQ的登录表。假设QQ的用户有100亿，如果只有一张表，每个用户登录的时候数据库都要从这100亿中查找，会很慢很慢。如果将这一张表分成100份，每张表有1亿条，就小了很多，比如qq0,qq1,qq1…qq99表。

用户登录的时候，可以将用户的id%100，那么会得到0-99的数，查询表的时候，将表名qq跟取模的数连接起来，就构建了表名。比如123456789用户，取模的89，那么就到qq89表查询，查询的时间将会大大缩短。

另外部分业务逻辑也可以通过地区，年份等字段来进行归档拆分;进行拆分后的表，只能满足部分查询的高效查询需求，这时我们就要在产品策划上，从界面上约束用户查询行为。比如我们是按年来进行归档拆分的,这个时候在页面设计上就约束用户必须要先选择年,然后才能进行查询;在做分析或者统计时，由于是自己人的需求,多点等待其实是没关系的,并且并发很低,这个时候可以用union把所有表都组合成一张视图来进行查询,然后再进行查询。

水平拆分的优点：

 
◆表关联基本能够在数据库端全部完成； 
◆不会存在某些超大型数据量和高负载的表遇到瓶颈的问题； 
◆应用程序端整体架构改动相对较少； 
◆事务处理相对简单； 
◆只要切分规则能够定义好，基本上较难遇到扩展性限制；

水平切分的缺点：

◆切分规则相对更为复杂，很难抽象出一个能够满足整个数据库的切分规则； 
◆后期数据的维护难度有所增加，人为手工定位数据更困难； 
◆应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。

额外介绍：

突然想起来，楠神也做过水平分表。那是做积分功能，目的是实现积分过期的需求。

大体思路是：假如2017年获得的积分，到2018年12月31日过期。楠神就用分表实现过期的功能。

2017年获得积分存到score2017表“now_total”字段，“now_total”记录的是某一用户2017年的积分总数，score2017表另一字段“last_total”是2016年的积分总数。两个字段之和是用户的总积分。

到了2018年1月1日，数据库里自动创建score2018表（用PHP代码写逻辑触发），然后把score2017表里的数据复制到score2018表里，其中score2017“now_total”复制到score2018“last_total”里。

同理到了2019年1月1日，score2018“now_total”复制到score2019“last_total”里，这样2017年的积分就彻底过期失效了。

2）垂直拆分 

垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。表的记录并不多，但是字段却很长，表占用空间很大，检索表的时候需要执行大量的IO，严重降低了性能。这时需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。 

通常我们按以下原则进行垂直拆分: 

1，把不常用的字段单独放在一张表;, 
2，把text，blob等大字段拆分出来放在附表中; 
3，经常组合查询的列放在一张表中;

例如学生答题表tt：有如下字段： 
Id name 分数 题目 回答 
其中题目和回答是比较大的字段，id name 分数比较小。

如果我们只想查询id为8的学生的分数：select 分数 from tt where id = 8;虽然知识查询分数，但是题目和回答这两个大字段也是要被扫描的，很消耗性能。但是我们只关心分数，并不想查询题目和回答。这就可以使用垂直分割。我们可以把题目单独放到一张表中，通过id与tt表建立一对一的关系，同样将回答单独放到一张表中。这样我们插叙tt中的分数的时候就不会扫描题目和回答了。

垂直切分的优点 
◆ 数据库的拆分简单明了，拆分规则明确； 
◆ 应用程序模块清晰明确，整合容易； 
◆ 数据维护方便易行，容易定位；

垂直切分的缺点 
◆ 部分表关联无法在数据库级别完成，需要在程序中完成； 
◆ 对于访问极其频繁且数据量超大的表仍然存在性能平静，不一定能满足要求； 
◆ 事务处理相对更为复杂； 
◆ 切分达到一定程度之后，扩展性会遇到限制； 
◆ 过读切分可能会带来系统过渡复杂而难以维护。

垂直拆分：

解决问题：表与表之间的io竞争

不解决问题：单表中数据量增长出现的压力

水平拆分：

解决问题：单表中数据量增长出现的压力

不解决问题：表与表之间的io争夺

表的数量太多的话，会不会也影响数据库的性能呢？

表多是不会影响数据库性能，实际上要看查询语句，如果是 join 查询当然ＪＯＩＮ的表越多越慢。