MySQL大数据表处理的三种方案（转）

原链接：MySQL大数据表处理的三种方案，查询效率嘎嘎高！

1、场景
   当我们业务数据库表中的数据越来越多，如果你也和我遇到了以下类似场景，那让我们一起来解决这个问题
   a、数据的插入,查询时长较长
   b、后续业务需求的扩展 在表中新增字段 影响较大
   c、表中的数据并不是所有的都为有效数据  需求只查询时间区间内的

2、评估表数据体量
   我们可以从表容量/磁盘空间/实例容量三方面评估数据体量，接下来让我们分别展开来看看
   a、表容量
      表容量主要从表的记录数、平均长度、增长量、读写量、总大小量进行评估。一般对于OLTP的表，建议单表不要超过2000W行数据量，总大小15G以内。访问量：单表读写量在1600/s以内
      查询行数据的方式：我们一般查询表数据有多少数据时用到的经典sql语句如下：
      select count(*) from table
      select count(1) from table
      但是当数据量过大的时候，这样的查询就可能会超时，所以我们要换一种查询方式
      use 库名
      show table status like '表名' ; 或：show table status like '表名'\G ;
      上述方法不仅可以查询表的数据，还可以输出表的详细信息 , 加 \G 可以格式化输出。包括表名 存储引擎 版本 行数 每行的字节数等等，大家可以自行试一下。
   b、磁盘空间
      查看指定数据库容量大小
      select table_schema as '数据库',table_name as '表名',table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',
      truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables order by data_length desc, index_length desc;
      查询单个库中所有表磁盘占用大小
      select table_schema as '数据库', table_name as '表名', table_rows as '记录数',
      truncate(data_length/1024/1024, 2) as '数据容量(MB)', truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables
      where table_schema='mysql' order by data_length desc, index_length desc;
      建议数据量占磁盘使用率的70%以内。同时，对于一些数据增长较快，可以考虑使用大的慢盘进行数据归档（归档可以参考方案三）
   c、实例容量
      MySQL是基于线程的服务模型，因此在一些并发较高的场景下，单实例并不能充分利用服务器的CPU资源，吞吐量反而会卡在mysql层，可以根据业务考虑自己的实例模式

3、出现问题的原因
   上面我们已经查到我们数据表的体量了 那么为什么单表数据量越大 业务的执行效率就越慢 根本原因是什么呢？
   一个表的数据量达到好几千万或者上亿时，加索引的效果没那么明显啦。性能之所以会变差，是因为维护索引的B+树结构层级变得更高了，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。
   一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。
   如果B+树想存储更多的数据，那树结构层级就会更高，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。

4、如何解决单表数据量太大，查询变慢的问题
   知道了根本原因之后，我们就需要考虑如何优化数据库来解决问题了
   这里提供了三种解决方案，包括数据表分区，分库分表，冷热数据归档 了解完这些方案之后大家可以选取适合自己业务的方案。
   a、方案一：数据表分区
      为什么要分区：表分区可以在区间内查询对应的数据，降低查询范围 并且索引分区 也可以进一步提高命中率，提升查询效率 分区是指将一个表的数据按照条件分布到不同的文件上面，未分区前都是存放在一个文件上面的，但是它还是指向的同一张表，只是把数据分散到了不同文件而已。
      我们首先看一下分区有什么优缺点：
      表分区有什么好处？
      * 与单个磁盘或文件系统分区相比，可以存储更多的数据。
      * 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
      * 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
      * 涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
      * 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。
      表分区的限制因素
      * 一个表最多只能有1024个分区。
      * MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
      * 如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。
      * 分区表中无法使用外键约束。
      * MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。
   b、方案二：数据库分表
      为什么要分表：分表后，显而易见，单表数据量降低，树的高度变低，查询经历的磁盘io变少，则可以提高效率 mysql 分表分为两种 水平分表和垂直分表
      分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。
      水平分表
          定义：数据表行的拆分，通俗点就是把数据按照某些规则拆分成多张表或者多个库来存放。分为库内分表和分库。
          比如一个表有4000万数据，查询很慢，可以分到四个表，每个表有1000万数据。
      垂直分表
          定义：列的拆分，根据表之间的相关性进行拆分。常见的就是一个表把不常用的字段和常用的字段就行拆分，然后利用主键关联。或者一个数据库里面有订单表和用户表，数据量都很大，进行垂直拆分，用户库存用户表的数据，订单库存订单表的数据。
          缺点：垂直分隔的缺点比较明显，数据不在一张表中，会增加join 或 union之类的操作
      知道了两个知识后，我们来看一下分库分表的方案，采用hash取模和range方案结合 既可以避免热点数据的问题，也有利于将来对数据的扩容

5、方案三：冷热归档
   为什么要冷热归档：其实原因和方案二类似，都是降低单表数据量，树的高度变低，查询经历的磁盘io变少，则可以提高效率 如果大家的业务数据，有明显的冷热区分，比如：只需要展示近一周或一个月的数据。那么这种情况这一周或一个月的数据我们称之为热数据，其余数据为冷数据。那么我们可以将冷数据归档在其他的库表中，提高我们热数据的操作效率。
   接下来讲一下归档的过程
       创建归档表  创建的归档表 原则上要与原表保持一致
       归档表数据的初始化

6、以上三种方案我们如何选型

 

本文仅供参考，自己收藏！