Mysql Learning Notes (i)

Recently from the MySQL database, encountered some problems, some solved, some have not found the answer, this article as a study note, unresolved issues, such as follow-up answers to add, also please pass by the Daniel pointed twos;

Problem One: Java program query MySQL table data, because MySQL default to the query results loaded into memory, the amount of data is large, will be reported Oom, the following is the solution to this problem in the online three common solutions found:

Scenario 1)

1 setfetchsize (integer.min_value);

View Code

Scenario 2)

1 conn = drivermanager.getconnection ("Jdbc:mysql://localhost/?usecursorfetch=true", "User", "password"  ); 2 stmt = conn.createstatement (); 3 stmt.setfetchsize (100);

View Code

Scenario 3) Paging query, for some of the more embarrassing reasons, I finally chose this scheme;

1 -- example of a paged query statement 2 Select *  from Order  by Col limit Offset, pagesize;

View Code

When the offset is relatively large, the query efficiency is very low, the following is the online search for two solutions

1 --12 Select *  fromTableNamewhereCol1>(SelectCol1 fromTableNameOrder  byCol1 Limit (&Page-1)*&PageSize1)Order  byCol1 Limit&pagesize;3 4 --25 SelectT1.*  fromTableName asT1Join(SelectCol1 fromTableNameOrder  byCol1 Limit (&Page-1)*&PageSize1) asT2whereT1.col1>=T2.col1Order  byT1.col1 limit&pagesize;6 7 --Statement 2 is easier to extend when the primary key of a table is a composite field and can be written as8 SelectT1.*  fromTableName asT1Join(SelectCol1, col2 fromTableNameOrder  bycol1, col2 limit (&Page-1)*&PageSize1) asT2whereT1.col1>T2.col1or(T1.col1=T2.col1 andT1.col2>=T2.COL2)Order  byT1.col1, T1.col2 limit&PageSize

View Code

To sort the col1, the Col2 field is the primary key field of the queried table, in general, using paged query, the table preferably has a self-increment numeric type of the primary key will be better, query efficiency is high, if the primary key is more than one field, you can see that the query SQL will be written very complex and inefficient.

My test data is 500w,pagesize is 50, when the table inside the primary key is two fields, turn the second page time with 50+ seconds, the visibility of how low efficiency ... Only to see if there is any optimization, in fact, my demand is to sweep the table, so as long as each page when the last check on the previous record

The primary key value is passed to the next query statement to optimize some time, the final scenario is as follows:

1String sqltext = "Select Col1, col2 from tablename where col1 >? or (col1 = col2 >?) Order by col1, col2 limit &pagesize ";2 3PreparedStatement prepstmt =NULL;4ResultSet rs =NULL;5PREPSTMT =conn.preparestatement (sqltext);6 7String iCol1 = "";8String iCol2 = "";9 Ten      while(true) One     { APrepstmt.setstring (1, iCol1); -Prepstmt.setstring (2, iCol1); -Prepstmt.setstring (3, iCol2); thers =prepstmt.executequery (); -          intrscnt = 0; -           while(Rs.next ()) -          { +rscnt++; -             if(rscnt = =PAGESIZE) +             { AICol1 = rs.getstring ("Col1"); atICol2 = rs.getstring ("col2"); -             } -           } -           if(rscnt = = PAGESIZE) Break; -}

View Code

Problem two (unresolved), MySQL stored procedures, using the Insert Ignore statement to add table records, program interrupt recall no new success (there is no record in the actual table), remove ignore on the success of the new, not clear what happened in the middle? Separate research stored procedure insert ignore no problem. This occurs when called in a Java program.

Problem three (unresolved), add about 10G of data to MySQL (several times), MySQL generates about 150G binary log, I need to continue to learn the MySQL binary log file related content, じゃ~また

Mysql Learning Notes (i)