Mysql Study Notes (1), mysql Study Notes

Mysql Study Notes (1), mysql Study Notes

I have recently encountered some problems in learning MySQL databases, some of which have been solved, and some have not yet found the answer. This article will be used as a study note, and some of the unsolved questions will be supplemented with answers later, please give me some advice when you pass;

 

Problem 1: the Java program queries MySQL table data. because MySQL loads all query results to the memory by default, OOM is reported when the data volume is large, the following are three common solutions found on the Internet to solve this problem:

Solution 1)

1 setFetchSize(Integer.MIN_VALUE);

View Code

Solution 2)

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

View Code

Solution 3) Paging query: I finally selected this solution for some reason that it is relatively slow;

1 -- paging query statement Example 2 select * from tablename order by col limit offset, pagesize;

View Code

When the offset value is relatively large, the query efficiency is very low. The following two solutions are available on the Internet:

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 -- Statement 2 it is easier to expand when the table's primary key is a composite field, you can write 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

The col1 and col2 fields used for sorting are the primary key fields of the queried table. Generally, when querying by page, it is better for the table to have an auto-incrementing numeric primary key for better query efficiency, if the primary key has multiple fields, it can be seen that the query SQL statements are very complex and inefficient.

My test data is 500 w, and the pagesize is 50. When the primary key in the table is two fields, it takes more than 50 seconds to flip the second page ...... I can only check whether there is any optimization method. In fact, my need is to scan the entire table, so every time I flip the page, I will check the last record on the previous page.

It takes a lot of time to pass the primary key value to the next query statement. The final solution is as follows:

 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

Problem 2 (unsolved): MySQL stored procedure, using the insert ignore statement to add a table record. The program was interrupted and was not added successfully (this record is not found in the actual table ), after ignore is removed, it is successfully added. What happened in the middle? No problem with insert ignore. This situation occurs when calling in a Java program.

Problem 3 (unsolved): Add about 10 Gb of data to MySQL (executed several times). MySQL generates about GB of binary logs, I need to continue to learn about MySQL binary log files ~ Zookeeper