大表数据存储优化

小明是一位新鲜出炉的架构师，在业务方的施压下，为了将产品快速地迭代上线，他并没有对未来的业务量做出合理地估计，将网站服务端的数据存储方案设计成了mysql单库单表的模式，“反正大家也不太看好这个产品，单库单表足够了”，他对着开发兄弟们说，方案很快地就成功上线了。
　　不知道从哪天开始，小明开始收到了系统响应耗时长的监控告警，并且告警数量在慢慢增多。小明是一名有”匠心“的架构师，他必须查明告警的根源。通过查询服务端及数据库日志，他发现系统中的每一条sql语句都耗时较长，根源就在于当初空空如也的数据表中现在已经有将近500w多条记录了，小明此时陷入了深思，作为架构师，他必须解决现有的系统问题。
　　小明遇到地其实就是比较常见的”大表数据存储优化“问题，当数据库单表记录数过大时，增删改查性能都会急剧下降，我们可以参考以下步骤来优化：

一、单表优化

除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量：

1.数据字段

减少数据表单行记录的存储大小，这里主要是从字段类型、字段数量等方面来考虑，常见的原则如下：

• 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED，VARCHAR的长度只分配真正需要的空间；
• 使用枚举或整数代替字符串类型；
• 尽量使用TIMESTAMP而非DATETIME；
• 单表不要有太多字段，建议在20以内；
• 避免使用NULL字段，很难查询优化且占用额外索引空间；
• 用整型来存IP；

2. 索引

了解数据库索引原理与结构，有助于我们更好地为数据表建立索引，在同样地数据量下，提高数据查询地效率，可以从以下方面考虑如何通过索引来优化sql性能：

• 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE、ORDER/GROUP BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描；
• 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
  值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段；
• 字符字段只建前缀索引；
• 字符字段最好不要做主键；
• 不用外键，由程序保证约束；
• 尽量不用UNIQUE，由程序保证约束；
• 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引；
• 合理地使用覆盖索引，避免不必要地回表操作；

主键索引.jpeg 普通索引.jpeg

3.查询语句

改写不合理地查询语句，以得到更快地响应时间：

• 可通过开启慢查询日志来找出较慢的SQL；
• 不做列运算：SELECT id WHERE num + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等，查询时要尽可能将操作移至等号右边；
• sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库；
• 不用SELECT *，明确需要查询出地字段；
• OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内；
• 不用函数和触发器，在应用程序实现；
• 避免like %xxx式的模糊查询；
• 少用JOIN；
• 使用同类型进行比较，比如用'123'和'123'比，123和123比；
• 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
• 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5；
• 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大；

4.大表分区

分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。在分区表上的操作按照下面的操作逻辑进行：

• select查询：当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据；
• insert操作：当写入一条记录时，分区层打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应的底层表；
• delete操作：当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作；
• update操作：当更新一条数据时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，然后对底层表进行写入操作，并对原数据所在的底层表进行删除操作；
  　　虽然每个操作都会打开并锁住所有的底层表，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，如：innodb，则会在分区层释放对应的表锁，这个加锁和解锁过程与普通Innodb上的查询类似。

分区的优点是：

• 可以让单表存储更多的数据；
• 分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作；
• 部分查询能够从查询条件确定只落在少数分区上，速度会很快；
• 分区表的数据还可以分布在不同的物理设备上，从而高效地利用多个硬件设备；
• 可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争；
• 可以备份和恢复单个分区；

分区的限制和缺点：

• 一个表最多只能有1024个分区；
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来；
• 分区表无法使用外键约束；
• NULL值会使分区过滤无效；
• 所有分区必须使用相同的存储引擎；

下面是根据时间字段建立分区的一个例子：

CREATE TABLE sales ( id INT AUTO_INCREMENT, 
amount DOUBLE NOT NULL, 
order_day DATETIME NOT NULL, 
PRIMARY KEY(id, order_day)) ENGINE=Innodb 
PARTITION BY RANGE(YEAR(order_day)) 
( PARTITION p_2015 VALUES LESS THAN (2015), 
PARTITION p_2016 VALUES LESS THAN (2016), 
PARTITION p_2017 VALUES LESS THAN (2017), 
PARTITION p_catchall VALUES LESS THAN MAXVALUE);

分区适合的场景：

• 最适合的场景数据的时间序列性比较强，则可以按时间来分区，查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容易批量删除；
• 如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将热点数据单独放在一个分区，让这个分区的数据能够有机会都缓存在内存中，查询时只访问一个很小的分区表，能够有效使用索引和缓存；

二、分库分表

当一张单表的数据量达到几千万，并且还有不断增长趋势，在系统的访问量达到一定规模的时候，一次数据库的访问操作可能就很慢了。分库分表的目的就是要缓解单库压力，降低访问数据库的响应时间。

1.拆分方式

• 垂直拆分：是指按业务功能模块拆分，比如分为订单库、商品库、用户库，这种方式多个数据库之间的表结构不同。

垂直拆分.jpg

• 水平拆分：按照一定的分片算法将同一个表的数据进行分块保存到不同数据库的数据表中，这些数据库中的表结构完全相同。

水平拆分.jpg

在今天大型的互联网站基本上都是基于SOA或者微服务架构设计的，从某种意义上讲，我们的网站系统是按照业务功能进行垂直拆分的，不同的业务系统负责管理不同的业务数据库。在同一个业务系统中，比如订单系统，当单库单表的数据量不断增长时，往往只需要考虑水平拆分。
　　这里讲到地水平拆分其实和表分区有些类似，表分区是在单库下，在存储引擎层面实现的水平拆分。

水平拆分的优点:

• 不存在单库大数据和高并发的性能瓶颈；
• 应用端改造较少；
• 提高了系统的稳定性和负载能力；

缺点是：

• 分片事务一致性难以解决；
• 跨节点Join性能差，逻辑复杂；
• 跨节点的count,order by,group by以及聚合函数问题；

针对上面水平拆分带来的问题，目前技术上也有一些可提供的解决方案，大家在系统改造的时候，可以进行参考。

事务问题：
解决事务问题目前有两种可行的方案：

• 方案一：使用分布式事务，优点：交由数据库管理，简单有效，缺点：性能代价高，特别是shard越来越多时;
• 方案二：由应用程序和数据库共同控制，原理：将一个跨多个数据库的分布式事务分拆成多个仅处于单个数据库上面的小事务，并通过应用程序来总控各个小事务。优点：性能上有优势，缺点：需要应用程序在事务控制上做灵活设计。如果使用了spring的事务管理，改动起来会面临一定的困难。

跨节点Join的问题
　　只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

跨节点的count,order by,group by以及聚合函数问题
　　这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

2.解决方案

基于开源还是自研？
　　小明这里遇到的难题还是比较简单的，就是将大表数据分散到多库多表上存储，使用场景比较简单，而且如果要自主研发的话，时间周期长、项目风险大，小明果断地选取了市面上比较流行的分库分表解决方案，而且他还自信地对兄弟们说，即使以后我们遇到了分库分表的大难题，我们到时再在现有开源组件上基于自身业务做定制扩展嘛！

方案选取

• 客户端架构，通过修改数据访问层，如JDBC、Data Source、MyBatis，通过配置来管理多个数据源，直连数据库，并在模块内完成数据的分片整合，一般以Jar包的方式呈现；

客户端架构举例.png

客户端架构优点是： 应用直连数据库，降低外围系统依赖所带来的宕机风险，集成成本低，无需额外运维的组件。

缺点是：限于只能在数据库访问层上做文章，扩展性一般，对于比较复杂的系统可能会力不从心，将分片逻辑的压力放在应用服务器上，造成额外风险。

• 代理架构，通过独立的中间件来统一管理所有数据源和数据分片整合，后端数据库集群对前端应用程序透明，需要独立部署和运维代理组件，代理组件为了分流和防止单点，一般以集群形式存在，同时可能需要Zookeeper之类的服务组件来管理。

代理架构举例.png

代理架构的优点是：

1. 能够处理非常复杂的需求，不受数据库访问层原来实现的限制，扩展性强；
2. 对于应用服务器透明且没有增加任何额外负载；

缺点是：

1. 需部署和运维独立的代理中间件，成本高；
2. 应用需经过代理来连接数据库，网络上多了一跳，性能有损失且有额外风险；

有关客户端架构（比如tddl，sharding-jdbc等）和代理架构（mycat，heisenberg等）的具体实现方案，大家可以到internet上自行搜索学习，这里就不在累赘了。

这不产品经理又来催了，赶紧优化上线吧，这次小明明确了自己的需求：解决系统大数据量存储的问题，而且解决方案要轻量、简单易用、不增加额外的部署，所以小明选择客户端架构的实现方式，很快项目就优化上线了，告警短信消失了！