mysql-Optimization Two

Table Structure Optimization

The table uses columns of the short field, number type as much as possible.

For example, IP can be converted to int by Inet_aton function, conversion rule, a.b.c.d IP number is: A * 256 of the 3 square + b * 256 of the 2 square + c * 256 1 Times Square + d * 256 0;

Mysql> SelectInet_aton ('255.255.255.255');+------------------------------+|Inet_aton ('255.255.255.255')|+------------------------------+|                   4294967295 |+------------------------------+MySQL> SelectInet_ntoa (4294967295);+-----------------------+|Inet_ntoa (4294967295)|+-----------------------+| 255.255.255.255       |+-----------------------+

Vertical sub-table, the original table of the column according to the business split into multiple tables, the benefit is that the different business queries scattered in different tables, the disadvantage is that when there is a business need for multiple tables of data need to join;

Horizontal sub-table, the data in the table according to some of the rules of a field (such as the ID trailing character take 16 of the first 4 bits, 0-9,a-f to divide the table) into multiple tables, the same business in the processing of different customer requests, it may be assigned to different tables, in favor of reducing the DB composite;

InnoDB Cache Pool

Mysql>Show variables like 'innodb%pool%';+-------------------------------------+----------------+|Variable_name|Value|+-------------------------------------+----------------+|Innodb_additional_mem_pool_size| 8388608        ||Innodb_buffer_pool_dump_at_shutdown| OFF            ||Innodb_buffer_pool_dump_now| OFF            ||Innodb_buffer_pool_filename|Ib_buffer_pool||Innodb_buffer_pool_instances| 8              ||Innodb_buffer_pool_load_abort| OFF            ||Innodb_buffer_pool_load_at_startup| OFF            ||Innodb_buffer_pool_load_now| OFF            ||Innodb_buffer_pool_size| 134217728      |+-------------------------------------+----------------+

Can be set via set GLOBAL innodb_buffer_pool_size=xxx;

Innodb_buffer_pool_dump_at_shutdown is used to save hot data in the cache when MySQL is off, innodb_buffer_pool_load_at_ Startup is used to restore the saved hot data to the cache when MySQL is turned on.

char, varchar, text, blob

Char is stored with a fixed length (1-255, that is, one byte), more than the set length of the string will be truncated, less than the length of the set will be filled, the advantage of fixed length is that the insertion speed is fast (must be the whole line is fixed length);

VarChar is stored with a variable length (1-65535,64k, two bytes full 1, which represents bytes instead of characters, 65535 includes two bytes representing length, and varchar (86) Stores Chinese is 3x85 + 1 = 256 bytes, Requires two bytes of record length), more than the set length of the string will be truncated, less than the length of the set is not filled, the advantage of longer is to store an indeterminate length string, you can save space.

Text is similar to varchar and is also variable in length, and it is divided into,,, and TINYTEXT TEXT MEDIUMTEXTLONGTEXT，依次使用1、2、3、4个字节来表示长度

BLOBs are similar to text and are divided into,,, and TINYBLOB BLOB MEDIUMBLOBLONGBLOB

char, varchar, text can choose whether to ignore the case:

Mysql> Select 'ABC'='ABC'COLLATE utf8_general_ci;+--------------+| 'ABC'='ABC' |+--------------+|            1 |+--------------+MySQL> Select 'ABC'='ABC'COLLATE Utf8_bin;+--------------+| 'ABC'='ABC' |+--------------+|            0 |+--------------+

The maximum length of a row in MySQL is 65535 (that is, 2 of 16 square-1) bytes, the maximum length of the column is limited, the contents of the text and blob are not stored in the row, but the address is stored, and the index prefix length must be specified when indexing text and blobs. Note that when all columns in a table are fixed lengths, char is shown for the speed advantage of varchar, otherwise char does not have to be more efficient than varchar.

If there is a space at the end of the inserted string, char erases the space, and varchar preserves the space.

char, varchar, text in the comparison operation (such as =), will be the end of the space (like belongs to the pattern matching operation):

Mysql> Select 'ABC'='ABC';+--------------+| 'ABC'='ABC' |+--------------+|            1 |+--------------+MySQL> Select 'ABC'  like 'ABC';+--------------+| 'ABC'  like 'ABC' |+--------------+|            0 |+--------------+

Batch Update

Jdbc

1 Try{2Class.forName ("com. MySQL.jdbc.Driver "); 3conn =drivermanager.getconnection (O_url, Username,password); 4Conn.setautocommit (false); 5String sql = "INSERT user (user_name, password) VALUES (?,?)"; 6PreparedStatement Prest =conn.preparestatement (sql,resultset.type_scroll_sensitive,resultset.concur_read_only); 7       for(User user:users) {8Prest.setstring (1, User.getusername); 9Prest.setstring (2, User.getpassword); Ten Prest.addbatch ();  One      }    A Prest.executebatch ();  - Conn.commit ();  -  the}Catch(SQLException ex) { -Logger.getlogger (MyLogger.class. GetName ()). log (Level.severe,NULL, ex);  -}Catch(ClassNotFoundException ex) { -     +Logger.getlogger (MyLogger.class. GetName ()). log (Level.severe,NULL, ex);  -}finally{ + conn.close (); A}

Mapper.xml

1 <!--Bulk Increase Operations -  2     <InsertID= "Batchinsert">  3 INSERT INTO User (User_name,password) values4 <!--@Param list<user> Users -5         <foreachCollection= "Users"Item= "Item"Index= "Index"Separator=",">  6 (#{item.username},#{item.password})7         </foreach>  8     </Insert>  

Update, delete is similar.

Reference:

Https://dev.mysql.com/doc/refman/5.7/en/blob.html

Https://dev.mysql.com/doc/refman/5.7/en/char.html

Http://www.cnblogs.com/roverliang/p/6501768.html

mysql-Optimization Two