数据库优化

工作和面试的时候，常常遇到需要对海量数据库数据进行优化存储，一方面是为了方便查询效率，一方面也是为了提高可维护的成本。最近我对数据库优化的一些想法进行了梳理

分库分表

• 垂直分区
  • 概括
    • 根据数据库里面的数据表相关性进行拆分
      例子：用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。
    • 换句话说就是数据表列的拆分，把一张列比较多的表拆分为多张表
  • 优缺点
    • 优点
      可是使得行数量变小，查询时减少读取Block数，减少I/O次数
      简化表的结构，易于维护
    • 缺点
      主键出现冗余，需要管理冗余列，并引起Join操作，可以通过在应用层进行Join来解决
      会让事务变得更加复杂
  • 垂直分表
    • 概括
      主键和一些列放在一个表，然后把主键和另外的列放在另一个表中
    • 适用场景
      • 如果一个表中某些列常用，另外一些列不常用
      • 可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数
    • 缺陷
      • 有些分表的策略基于应用层的逻辑算法,一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差
      • 对于应用层来说，逻辑算法增加开发成本
      • 管理冗余列，查询所有数据需要join操作
• 水平分区
  • 概括
    • 保持数据表结构不变,通过某种策略存储数据分片.这样每一片数据分散到不同的表或者库中，达到了分布式的目的.水平拆分可以支撑非常大的数据量
    • 水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放
      例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响
  • 适用场景
    • 表中的数据本身就有独立性，例如表中分表记录各个地区的数据或者不同时期的数据，特别是有些数据常用，有些不常用
    • 需要把数据存放在多个介质上
  • 水平分表
    水平拆分可以支持非常大的数据量。
    需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库 。水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨界点Join性能较差，逻辑复杂。
    《Java工程师修炼之道》一书中就推荐尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

    • 概括
      表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数

    • 缺陷

      • 给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作
      • 在许多数据库应用中，这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数

    • 补充缺陷解决方案

      • 客户端代理
        分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现
        例子：当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现；
      • 中间件代理
        在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中
        例子：Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现；

需解决的问题

• 事务

分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；
如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担

• 跨库join

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

• 跨节点的count、order by、group by以及聚合函数问题

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

• 数据迁移，容量规划，扩容等问题

来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度

• ID问题

一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由

• 跨分片的排序分页

一般来说，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。