从一张大表读取数据，如何解决性能问题（数据库相关八）

1.项目背景

当数据库单表数据量达到一定程度时，数据查询变得很慢很慢，建立索引已经无法提高查询速度时，该如何对查询速度进行优化呢？

以单表的数据量达到八千万数据，

由于之前的架构设计，数据库设计的原因，直接导致数据库服务器负载过高，cpu 使用率接近百分百，

后端迟迟无法返回数据给前端或返回数据时间高达20-30s，前端不停的请求数据，进一步导致数据库负载增高，差点死亡。

 

2.临时方案

先备份数据，然后提供最近一段时间的数据查询，满足用户查询近期数据的需求，

而较久远的历史数据，由产品或分析师手动提供查询，当然这只是临时方案，不可长期存在

 

3.方案比较

方案	优势	不足	性能分析
接入spark	1.       该方案操作简单，只要将数据导入到hive表，然后通过spark jdbc的方式连接即可 2.       可扩展性好，可存储上T的数据	1. 对资源的依赖相对较重，目前大数据这边有10台服务器，1台master,9台slave,对于当前的调度任务来说，资源略显紧张，数据分析师和大数据这表均存在使用，而接入spark大概占据百分之二十的集群资源，导致分析师，调度任务无法再规定时间完成，若需要强行接入spark需要申请新的机器   2.spark优化较难	1.         经过测试查询两亿数据量，查询速度在10s左右，由于数据对时间有分区，业务查询除了比较极端的需求之外，不会全表扫描 2.         查询最近一段时间的数据，查询时间维持在1s以下 3.         经过调试查询速度可维持到100ms左右
数据库分库分表	1．  将大表分割为多个小表，大幅度查询的行数，从而提高查询效率， 2．  相比于分区，对于单个小表可建立索引，进一步提高查询速度	1.       数据量若大幅增长，分表表数不够，需要重新分表，移数据略显麻烦 2.       将数据导入多个表中，对于查询该表所有数据的统计不大好统 3.       数据表建的太多，看起来凌乱，而且导入历史数据略显麻烦 4.       增加列不大方便 浪费存储空间	1.         6千万数据分给为16个表，每个表数据量大概在40w数据左右,查询时间可达200ms以内
数据库分区	1.         和数据库分库分表的思想接近,属于物理存储层面的分库分表，数据量过大（索引查过cpu内存比如4G）时，删除索引查询速度可显著提高 2.         数量若增大，查询速度减慢时，可直接通过语句增加分区个数，提高查询速度	1.   单表数据量过大，对于分区建立索引会降低查询速度 2.    数据库迁移数据困难 3. 多表连接查询效率明显降低 4.    数据插入较慢，不适用于插入频繁操作 5.   浪费存储空间 6.最新版mysql数据库分区有限制8192,	1.       单个分区数据量大概在40w,查询速度可在200ms左右 2.       若分区数据量小，查询速度可更快
接入hbase 接口	1.         列的可以动态增加，并且列为空就不存储数据,节省存储空间. 2.          Hbase自动切分数据，使得数据存储自动具有水平scalability. 3.         Hbase可以提供高并发读写操作的支持	1.       不支持条件查询,只能通过row key来查询	查询速度可在100ms左右

 

   1. 一开始从方案困难程度上进行考量，选择了 接入spark方案进行了尝试，

然而，由于其需要占用集群大概百分之二十的资源，导致数据分析师和任务调度这边资源不足，导致任务无法正常执行（延时或mr调度资源不足）

 

   2.数据库分区连表（三个或三个以上）查询的弊端 

   和 SHOW VARIABLES LIKE '%partition%'; 返回的是

    而不是返回 have_partitioning   YES,

   表示该版本的mysql不支持分区

 

    3.接入hbase 不支持条件查询

 

所以仔细考虑了之后选择了数据库的分库分表，后来做的时候，由于分库分表实行起来，对于数据迁移，实在过于麻烦，所以，

使用语句SHOW PLUGINS; 显示插件，返回下方存在partition ACTIVE STORAGE ENGINE GPL 插件则表明支持分区

所以建立表，然后倒入六千万的数据，同时建立了分区表，并倒入大概六千万的数据进行测试explain查看查询数据条数和查询时间

 

4.最终结果

 经过总和考量之后，使用了数据库分区,最终单表倒入接近上亿的数据量，

 单表查询时间维持在200ms以内，某些接口查询时间在50-80ms,多表连接查询页基本在400ms以内。

 

 

ps:数据库的优化进程

1. 首先刚开始，人不多，压力也不大,搞一台数据库服务器就搞定了，此时所有的东东都塞进一个Server里，包括web server,app server,db server,但是随着人越来越多，

   系统压力越来越多，这个时候可能你把web server,app server和db server分离了，好歹这样可以应付一阵子，

2.但是随着用户量的不断增加，你会发现，数据库这哥们不行了，速度老慢了，有时候还会宕掉，所以这个时候，你得给数据库这哥们找几个伴，这个时候Master-Salve就出现了，

  这个时候有一个Master Server专门负责接收写操作，另外的几个Salve Server专门进行读取，这样Master这哥们终于不抱怨了，总算读写分离了，

  压力总算轻点了,这个时候其实主要是对读取操作进行了水平扩张，通过增加多个Salve来克服查询时CPU瓶颈。一般这样下来，你的系统可以应付一定的压力，

3.但是随着用户数量的增多，压力的不断增加，会发现Master server这哥们的写压力还是变的太大，没办法，这个时候怎么办呢？你就得切分啊，俗话说“只有切分了，才会有伸缩性嘛”，

  所以啊，这个时候只能分库了，这也是我们常说的数据库“垂直切分”，比如将一些不关联的数据存放到不同的库中，分开部署，这样终于可以带走一部分的读取和写入压力了，Master又可以轻松一点了，

  但是随着数据的不断增多，你的数据库表中的数据又变的非常的大，这样查询效率非常低，这个时候就需要进行“水平分区”了，比如通过将User表中的数据按照10W来划分，这样每张表不会超过10W了。

4.搞一些大数据相关的接口，hbase，es等。