Mysql 學習筆記（一）

     最近從在學習MySQL資料庫，遇到一些問題，有些解決了，有些還未找到答案，本篇作為學習筆記，未解決的問題等後續有答案再補充，也請走過路過的大牛們指點一二；

    問題一：Java程式查詢MySQL表數據，由於MySQL預設將查詢結果全部載入到記憶體中，數據量比較大時，會報OOM，以下是解決這個問題過程中在網上找到的三種常見解決方案：

方案1）

1 setFetchSize(Integer.MIN_VALUE);

方案2）

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

方案3）分頁查詢，由於某些比較囧的原因，我最終選取了這個方案；

1 --分頁查詢語句示例
2 select * from tablename order by col limit offset, pagesize;

當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;

用來排序的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     }

     問題二（未解決），MySQL 存儲過程，使用insert ignore 語句新增表記錄，程式中斷重提沒有新增成功（實際表裡面沒有該記錄），去掉ignore就成功新增了，不清楚中間發生了什麼事？單獨調研存儲過程insert ignore沒問題。在Java程式中調用出現這種情況。

     問題三（未解決），向MySQL中新增10G左右的數據（執行好幾次），MySQL生成150G左右的二進位日誌，我需要繼續學下MySQL二進位日誌文件的相關內容，じゃ～また