Mysql 學習筆記（一）

標籤：body   沒有   jdb   左右   調用   cti   exe   create   and   

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

 

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

方案1）

1 setFetchSize(Integer.MIN_VALUE);

View Code

方案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 --12 select * from tablename where col1 > (select col1 from tablename order by col1 limit (&page-1)*&pagesize,1) order by col1 limit &pagesize;3 4 --25 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

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

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

 

Mysql 學習筆記（一）