百万行mysql数据库优化（补充）

我上大学的那个时候喜欢研究一些数据库的技术，那时候觉得数据处理很重要，而且数据优化也是相当重要的，看了很多数据库方面的资料，虽然在实际的项目也遇到过一些数据库优化的任务，完成之后还是有些心情愉快。但是一直没有遇到过数据优化的DBA工作。个人也是从事java开发和一些采集监控系统，但是做过的项目也不算是很到，没有遇到过10万以上的监控采集点的项目，也有想过在开发工程中用下内存数据库，但是做过的项目还是没有必要使用到内存数据库。遇到的系统瓶颈大概是以下8个方面：

1 磁盘I/O：磁盘IO读写速度要比内存慢很多，内存要比磁盘快10万倍~100万倍的速度。如果程序在运行过程中，如果IO操作效率低下将会是系统的瓶颈。

2 网络操作：对网络数据进行操作时由于网络的不确定性也将成为系统的瓶颈。

3 CPU：对CPU的争夺情况也将可能成为系统的瓶颈。

4 异常：异常的处理是一个很耗费资源的过程，如果系统频繁出现异常的处理将成为系统的瓶颈。

5 数据库：对数据库的操作，如果数据库中的数据量逐渐增加，将会使数据库的操作花费的时间也逐渐增加，将成为系统的瓶颈。

6 锁竞争：对高并发程序来说，如果存在激烈的锁竞争，也会对性能带来巨大的开销。将会成为系统的瓶颈。

7 内存：如果内存分配的不合理，也将会成为系统的瓶颈（内存交换频繁将可能存在系统耗费资源多）。

8 系统处理的访问量：如果系统承受的访问量大也将成为系统的瓶颈。

前期提到的技术路线有

分析表的使用情况，频繁增删的，用链式结构；频繁查改的，用顺序结构。频繁查找字段，加入索引，索引覆盖，从索引上获取全部数据。

分析E-R，实现3个范式的数据库完整性，减少数据冗余。

对大数据表，分表处理，包括水平拆分和垂直拆分

优化字段类型，能确定varchar字段长度的尽量长度给小。

页面查询的时候，分页查询数据库内容，采用select * 尽量使用确定的字段写出来并且确定只有查询1条的数据一定要使用limit 1 ，查询尽量能使用limit就要使用limit，这样能有复杂的流程换取时间。

一次操作数据量适中，不用3重循环来处理数据，不要无限制的封装函数，特别是循环量大的函数一定要注意。

关于读写分离，如果数据库压力很大，一台机器支撑不了，用mysql复制实现多台机器同步，我觉得上百万的数据这个还是没有必要用到。

如果for循环比较多，可以尝试将改成for（int i=0;i<x;i=i+n）这样的方式来优化循环，循环是比较拖累程序的。

在for循环能一些对字符串的操作频繁的时候注意数据类型的选择，这个也是很重要的。

如果存在乘除法的语句能够用位运算代替的尽量用位运算，这样效率更高。

异常处理的过程最好不要放在for循环之中，这样会拖慢系统性能。

百万条数据量主要优化是在代码优化之中并且效果明显。而且只有在运维的时候了解实际的运行状况才能知道系统的瓶颈在哪里。

具体实施的方案1：使用数据库连接池，创建多个数据库连接放在连接池中，当用户提交数据时直接从数据库中拿到数据库的连接操作并行向数据库中插入数据，这样会节省很多时间到达优化的效果，我做了下测试在数据库表中的数据到达到了200多万时，也能达到要求但是这样占用的数据库连接比较多，资源占用比较多。基本的流程图如下：

 

具体实施的方案2：在用户请求数据时数据库先不存在响应，先缓存数据，等到达时间，1秒或者是500毫秒，也可以是缓存上线1000条数据之后，打开连接一起存入到数据库中，这样对性能的优化效果更好，基本流程如下图：

 

通过测试在数据库中数据到达200万条的时候插入数据为1000条时，能到达201毫秒的时间，优化的效果很好，并且占用资源少。

 

该系统的数据量不大建议使用第二种方式。