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）

1 conn = DriverManager.getConnection("jdbc:mysql://localhost/?useCursorFetch=true", "user", "password");
2 stmt = conn.createStatement();
3 stmt.setFetchSize(100);

View Code

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

1 --分页查询语句示例
2 select * from tablename order by col limit offset, pagesize;

View Code

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

1 --1
2 select * from tablename where col1 > (select col1 from tablename order by col1 limit (&page-1)*&pagesize,1) order by col1 limit &pagesize;
3 
4 --2
5 select t1.* from tablename as t1 join (select col1 from tablename order by col1 limit (&page-1)*&pagesize,1) as t2 where t1.col1 >= t2.col1 order by t1.col1 limit &pagesize;
6 
7 --语句2对于当表的主键是复合字段的时候比较容易扩展，可以写成
8 select t1.* from tablename as t1 join (select col1, col2 from tablename order by col1, col2 limit (&page-1)*&pagesize,1) as t2 where t1.col1 > t2.col1 or (t1.col1 = t2.col1 and t1.col2 >= t2.col2) order by t1.col1, t1.col2 limit &pagesize;

View Code

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

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

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

 1     String sqltext = "select col1, col2 from tablename where col1 > ? or (col1 = ? and col2 > ?) order by col1, col2 limit &pagesize";
 2 
 3     PreparedStatement prepStmt = null;
 4     ResultSet rs = null;
 5     prepStmt = conn.prepareStatement(sqltext);
 6 
 7     String iCol1 = "";
 8     String iCol2 = "";
 9 
10     while(true)
11     {
12          prepStmt.setString(1,iCol1);
13          prepStmt.setString(2,iCol1);
14          prepStmt.setString(3,iCol2);
15          rs = prepStmt.executeQuery();
16          int rsCnt = 0;
17          while(rs.next())
18          {
19             rsCnt++;
20             if(rsCnt == PAGESIZE) 
21             {
22                 iCol1 = rs.getString("col1");
23                 iCol2 = rs.getString("col2");
24             }
25           }
26           if(rsCnt == PAGESIZE) break;
27     }

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，长度不一致，将两者修改一致后问题解决；

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有