VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > MySQL >
  • MySQL教程之Mysql 学习笔记(一)

   最近从在学习MySQL数据库,遇到一些问题,有些解决了,有些还未找到答案,本篇作为学习笔记,未解决的问题等后续有答案再补充,也请走过路过的大牛们指点一二;

 

    问题一:Java程序查询MySQL表数据,由于MySQL默认将查询结果全部加载到内存中,数据量比较大时,会报OOM,以下是解决这个问题过程中在网上找到的三种常见解决方案:

方案1)

1 String sqlText = "select * from bigTable";
2 PreparedStatement ps = null;
3 
4 ps = con.prepareStatement(sqlText , ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);  
5 ps.setFetchSize(Integer.MIN_VALUE);  
6 ps.setFetchDirection(ResultSet.FETCH_REVERSE); 

方案2)

 View Code

方案3)分页查询,由于某些比较囧的原因,我最终选取了这个方案;

 View Code

当offset比较大的时候,查询效率很低,以下是网上查到的两种解决办法

 View Code

用来排序的col1, col2字段是查询的表的主键字段,一般来说,使用分页查询,表最好是有一个自增的数值型的主键会比较好,查询效率比较高,如果主键是多个字段,可以看出来查询的SQL会写得非常复杂,效率也很低。

我的测试数据是500w,pagesize是50,当表里面的主键是两个字段时,翻第二页的时间用了50+秒,可见效率有多低……只能看看还有没有优化办法,其实我的需求是扫全表,因此只要每次翻页的时候把上一页查到的最后一条记录

主键值传给下一个查询语句就可以优化不少时间,最终的方案如下:

 View Code

--------------------------------------------------------------------------------------我是分页查询有效率问题的分割线---------------------------------------------------------------------------------------------------------

     最近一个处理流程中,还是继续使用分页查询,每次从表中读5000条数据,A表做全表扫描取出字段col1,用col1关联查询B表记录取出字段col2(查询可以匹配索引),然后用col2关联查询C表取出col3(查询可以匹配索引),用col3的值更新A表的字段col4;

     其中A/B/C三个表的数据量均为500w,测试过几次,整个流程跑完一共花了9-13个小时的时间,效率极其低;查看了MySQL的慢查询日志可以看到最外层分页查询部分的查询语句效率很低,我另外写了个小程序测试,用分页查询扫完A表,读每一条记录的col值,每页(5000条记录)花费的时间约在90s-130s之间;而采用方案一每读5000条记录花费的时间在50ms-100ms之间;所以说决定采取什么方案的时候最好还是做好充足的测试,以免多走弯路;最终采取方案一的查询大数据模式,跑完整个流程花了2个小时20分钟!

    抽空又做了下方案2的效率测试,同一张表,一样是读5000条的时间花费,方案2每读5000条记录花费的时间为150s-180s之间;

    对比下方案1和方案2,方案1采取的是流数据接收方式,每次从服务器接收部分数据,直到所有数据处理完毕,期间数据库的Connection不能再执行其他sql语句,如果还需要做其他查询或更新操作需要另外建新的connection,考虑到数据库的连接资源是有限的,我们在选择这个方案的时候应该具体情况具体分析;方案2设置连接属性useCursorFetch=true,再设置fetch size参数,表示采用服务器端游标,每次从服务器取fetch_size条数据。 在我的测试例子中,同样的测试环境下,方案1比起方案2时间效率略高.

 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

     问题二(未解决),MySQL 存储过程,使用insert ignore 语句新增表记录,程序中断重提没有新增成功(实际表里面没有该记录),去掉ignore就成功新增了,不清楚中间发生了什么事?单独调研存储过程insert ignore没问题。在Java程序中调用出现这种情况。

     问题三(未解决),向MySQL中新增10G左右的数据(执行好几次),MySQL生成150G左右的二进制日志,我需要继续学下MySQL二进制日志文件的相关内容(这个问题后来经分析这么多日志应该是正常现象,二进制日志记录模式有三种:row, statement, mixed,可以了解下)

     问题四:replace into table1 select * from table2 (table1,table2刚开始以为表结构一摸一样)语句报replace data truncate for column, 查出原因是由于table1主键字段 filename长度为20, table2的主键字段filename长度为30,长度不一致,将两者修改一致后问题解决;

 


相关教程