Mysql下单表大数据量的调优案例

遇到问题：某个信息化系统的资产管理统计分析模块，非常慢，10多分钟过去了，还没见到应该有的统计表。（具体什么时候能出来，没人等到它出来过。。。）
业务背景：资产管理分析模块初始化需要统计所有单位的所有资产数据，加压环境数据量为360万。在统计分析的过程中，需要对资产数据大小，对单位进行排序，取前20家，同时对资产按照不同类别进行分类统计。
数据量来源：一共121家单位，每家平均3万数据。
服务器：MySQL数据库，数据库配置使用的是安装时指定的 server 模式，服务器为windows server 2008，内存8G，4个逻辑核心。

解决问题思路和步骤：
1.首先查看后台代码，看是否存在多余的数据库查询，是否可以合并数据库查询。
   经分析，发现可以通过一条数据库语句获取各个单位各个类别的资产总和，然后在此基础之上，在内存中进行排序，计算，统计。于是，原来的四个数据库交互变为一个数据库交互。

2.此时，统计分析的查询在10分钟过去后，还是没有结果，不过此时就剩下一个数据库交互，问题就在这个SQL上。此时的SQL为：
select zcjb.C_SSDW,zcjb.C_ZCLB,sum(bdxx.N_RZYZ) as rzyz  from T_ZCJBXX zcjb,T_ZCBDXX bdxx where zcjb.C_ID_BD=bdxx.C_ID group by zcjb.C_SSDW,bdxx.C_ID_ZCLB;

3.由于T_ZCJBXX与T_ZCBDXX为一对多的关系，所以当T_ZCJBXX中有360万数据时，T_ZCBDXX只会存在多于360万数据。那么这两个表做关联，而且是360万对360万+的数据关联，速度肯定是上不来。
  既然关联会拖慢速度，那么做冗余，将表关联去掉。于是在T_ZCJBXX表上添加冗余字段，同时在业务逻辑中对冗余字段进行维护，解除T_ZCJBXX和T_ZCBDXX的关联。此时的SQL为：
select C_RY_SSDW,C_RY_ZCLB,sum(N_RY_RZYZ) as rzyz  from T_ZCJBXX  group by C_RY_SSDW,C_RY_ZCLB;
既然存在group by，那么给C_RY_SSDW和C_RY_ZCLB加一个联合索引。

4.此时，统计分析查询终于能出结果了，嗯，，，，用时440秒，还得继续优化。不过此时遇到问题：手上知道的优化方案已经没有了，关联已经去掉，索引也加了，而且索引的的确确也走了。于是到网上查找MySQL的进一步优化方案。
  终于找到一个关键词：分区。
  MySQl支持表分区，经过表分区后，原来在一个大文件中存储的单表数据会被拆分为多个文件，每个文件对应一个分区。

5.于是，对T_ZCJBXX 做分区。由于单位数量固定，而且统计维度中单位是其中一个主要维度，正好以单位来做分区，划分121个分区（121家单位，每个单位一个分区）。

6.来试试结果，此时的统计分析用时在50秒左右。如果只对一家单位查询，那么用时是0.7秒左右。看来真的到瓶颈了，单个单位0.7秒，121家单位用时50秒，看来一起查还能好那么一点点。

7.但是我们可以多线程并发啊，一个线程负责几家单位甚至一个线程只负责一家单位，最大程度压榨数据库服务器的运算能力。于是配置线程池，不断调整线程池大小和单个线程查询单位数量，争取达到最优解。
  此时的查询速度到了39秒。

8.到这里，基本所有办法都用光了，剩下就是提升硬件水平了，首先试试固态硬盘吧。换了 同事的固态硬盘，220万（没有导入360万，因为固态硬盘满了）数据可以用7秒左右出结果，固态硬盘真是好东西。

9.之后换到性能测试环境，用固态硬盘进行性能测试，360万数据用时9秒。但是奇怪的事情发生了，服务在运行25分钟后，响应时间上去了，CPU持续处于90%的使用水平，服务器整个都变慢了。
  经过排查，是单次统计分析用的线程池配置过大，过多的线程以及线程的切换挤占了CPU的绝大部分资源，造成了并发时的CPU资源等待。降低线程池并发数量，CPU终于正常，查询时间也稳定在了9秒。如果使用更高配置的CPU，可以加大线程   池，加强IO处理，让查询时间进一步的缩减。
10.如果说进一步提升，那么就是窄表，因为现在表加了冗余后，表太宽，不利于数据检索和获取。

优化总结：
1.优化代码，删除不必要的数据库查询。
2.做冗余，减少表关联。
3.加索引。
4.大数据量的表，可以进行分区或者分表，不过分表需要业务逻辑做额外的处理。
5.提升服务器的硬件配置，包括使用固态硬盘，提升CPU配置等等。

建议最后采取提升服务器硬件配置的方法，不然会失去很多解决问题的好机会。