mysql-最佳化二

標籤：ring   字元   class   dump   https   rest   global   比較   values   

表結構最佳化

表中盡量使用短欄位、數字類型的列，

比如ip可以通過inet_aton函數轉換成int型，轉換規則，a.b.c.d 的ip number是：a * 256的3次方 + b * 256的2次方 + c * 256的1次方 + d * 256的0次方；

mysql> select inet_aton(‘255.255.255.255‘);+------------------------------+| inet_aton(‘255.255.255.255‘) |+------------------------------+|                   4294967295 |+------------------------------+mysql> select inet_ntoa(4294967295);+-----------------------+| inet_ntoa(4294967295) |+-----------------------+| 255.255.255.255       |+-----------------------+

 

垂直分表，即將原表中的列根據業務拆分為多個表，這樣帶來的好處是將不同業務的查詢分散在不同的表中，壞處是當有業務需要多個表的資料時需要join；

水平分表，即將表中資料按照某欄位的某些規律（如將id尾字元取16進位的後4位，0-9，a-f進行分表）拆分為多個表，相同的業務在處理不同的客戶請求時，就可能會被分配至不同的表中，有利於減輕db複合；

 

InnoDB緩衝池

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      |+-------------------------------------+----------------+

可以通過SET GLOBAL innodb_buffer_pool_size=xxx;進行設定。

innodb_buffer_pool_dump_at_shutdown用於在mysql關閉時儲存緩衝中的熱資料，innodb_buffer_pool_load_at_startup用於在mysql開啟時恢複儲存的熱資料至緩衝。

 

char、varchar、text、blob

char在儲存時有固定的長度（1-255，即一個位元組），多於設定長度的字串會被截取，少於設定的長度會被填充，固定長度的優勢在於插入速度快（必須整行都是固定長度）；

varchar在儲存時間長度度可變（1-65535，64k，即兩個位元組全1的情況，該大小表示位元組數而不是字元數，65535包括表示長度的兩個位元組，VARCHAR(86)儲存中文就是3 × 85 + 1 = 256個位元組，需要兩個位元組記錄長度），多於設定長度的字串會被截取，少於設定的長度不會被填充，變長的優勢在於儲存不確定長度字串時，可以節省空間的。

text與varchar類似，也是變長的，其又分為TINYTEXT, TEXT,MEDIUMTEXT, and LONGTEXT，依次使用1、2、3、4個位元組來表示長度

blob與text類似，又分為TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB

char、varchar、text可以選擇是否忽略大小寫：

mysql> select ‘abc‘=‘Abc‘ COLLATE utf8_general_ci;+--------------+| ‘abc‘=‘Abc‘ |+--------------+|            1 |+--------------+mysql> select ‘abc‘=‘Abc‘ COLLATE utf8_bin;+--------------+| ‘abc‘=‘Abc‘ |+--------------+|            0 |+--------------+

 

mysql中行的最大長度為65535（即2的16次方-1）個位元組，列的最大長度受此限制，行中並不儲存text、blob的內容，只是儲存了其地址，在對text、blob建立索引時，必須指定索引前置長度。注意，只有在表中的所有列都是固定長度時，char對於varchar的速度優勢才會體現，否則char並不必varchar更高效。

如果插入的字串的末尾有空格，char會抹去空格，varchar會保留空格。

char、varchar、text在進行比較操作時（比如=），都會捨去末尾的空格（LIKE屬於模式比對操作）：

mysql> select ‘abc‘=‘abc    ‘;+--------------+| ‘abc‘=‘abc    ‘ |+--------------+|            1 |+--------------+mysql> select ‘abc‘ LIKE ‘abc    ‘;+--------------+| ‘abc‘ LIKE ‘abc    ‘ |+--------------+|            0 |+--------------+

 

批次更新

jdbc

 1 try{ 2      Class.forName("com.MySQL.jdbc.Driver");    3      conn = DriverManager.getConnection(o_url, userName,password);    4      conn.setAutoCommit(false);    5      String sql = "INSERT user(user_name, password) VALUES (?, ?)";    6      PreparedStatement prest =conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);    7      for(User user: users){    8         prest.setString(1,user.getUserName);    9         prest.setString(2,user.getPassword);   10         prest.addBatch();   11      }   12      prest.executeBatch();   13      conn.commit();   14 15 } catch (SQLException ex){   16   Logger.getLogger(MyLogger.class.getName()).log(Level.SEVERE, null,ex);   17 } catch (ClassNotFoundException ex){   18    19   Logger.getLogger(MyLogger.class.getName()).log(Level.SEVERE, null,ex);   20 }finally{21     conn.close();22 }

mapper.xml

1 <!-- 批量增加操作 -->  2     <insert id="batchInsert">  3         insert into user(user_name,password) values  4 <!--@Param List<User> users-->5         <foreach collection="users" item="item" index="index" separator=",">  6             (#{item.userName},#{item.password})  7         </foreach>  8     </insert>  

 update、delete也類似。

 

參考：

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-最佳化二