mysql的最佳化措施，從sql最佳化做起

標籤：

http://geeksblog.cc/2016/06/11/mysql-optimize/

最佳化sql的一般步驟

1. 通過show status瞭解各種sql的執行頻率
2. 定位執行效率低的sql語句
3. 通過explain分析效率低的sql
4. 通過show profile分析sql  ?
5. 通過trace分析最佳化器如何選擇執行計畫
6. 確定問題，採取措施最佳化

索引最佳化措施

1. mysql中使用索引的典型情境

   1. 匹配全值，條件所有列都在索引中而且是等值匹配
   2. 匹配值的範圍尋找，欄位必須在索引中
   3. 匹配最左首碼，複合索引只會根據最左列進行尋找
   4. 僅僅對索引進行查詢，即查詢的所有欄位都在索引上
   5. 匹配列首碼，比如like ‘ABC%’,如果是like ‘%aaa’就不可以
   6. 如果列名是索引，使用column is null會使用索引

2. 存在索引但不會使用索引的典型情境

   1. 以%開頭的like查詢不能使用b樹索引
   2. 資料類型出現隱式轉換不能使用索引
   3. 複合索引，查詢條件不符合最左列原則
   4. 用or分割的條件，如果前面的條件有索引，而後面的條件沒有索引

3. 查看索引使用的情況

   1	show status like ‘Handler_read%‘;

如果Handler_read_rnd_next的值比較高，說明索引不正確或者查詢沒有使用到索引

有索引:


mysql> select * from dd;+----+| a  |+----+|  1 ||  2 ||  3 ||  4 ||  5 ||  6 ||  7 ||  8 ||  9 || 10 || 11 || 12 || 13 |+----+13 rows in set (0.00 sec)mysql> show create table dd;+-------+----------------------------------------| Table | Create Table+-------+----------------------------------------| dd    | CREATE TABLE `dd` (  `a` int(11) NOT NULL,  PRIMARY KEY (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+----------------------------------------1 row in set (0.02 sec)

 

mysql> select * from dd where a=10;+----+| a  |+----+| 10 |+----+1 row in set (0.00 sec)mysql> show status like ‘Handler_read%‘;+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 0     || Handler_read_key      | 1     |    //增加的是這個值| Handler_read_last     | 0     || Handler_read_next     | 0     || Handler_read_prev     | 0     || Handler_read_rnd      | 0     || Handler_read_rnd_next | 2     |+-----------------------+-------+7 rows in set (0.00 sec)

無索引:




mysql> show create table q;+-------+------------------------------------------------------------------------------------+| Table | Create Table                                                                       |+-------+------------------------------------------------------------------------------------+| q     | CREATE TABLE `q` (  `a` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> select * from q where a=10;+------+| a    |+------+|   10 |+------+1 row in set (0.00 sec)mysql> show status like ‘Handler_read%‘;+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 1     || Handler_read_key      | 1     || Handler_read_last     | 0     || Handler_read_next     | 0     || Handler_read_prev     | 0     || Handler_read_rnd      | 0     || Handler_read_rnd_next | 56    |+-----------------------+-------+7 rows in set (0.00 sec)mysql> select * from q where a=11;+------+| a    |+------+|   11 |+------+1 row in set (0.00 sec)mysql> show status like ‘Handler_read%‘;+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 2     || Handler_read_key      | 2     || Handler_read_last     | 0     || Handler_read_next     | 0     || Handler_read_prev     | 0     || Handler_read_rnd      | 0     || Handler_read_rnd_next | 70    |+-----------------------+-------+7 rows in set (0.01 sec)

 

簡單實用的最佳化方法

1. 定期檢查表和分析表
   分析表文法：

   1	analyze table 表名；

檢查表文法：

1	check table 表名；

 

1. 定期最佳化表
   • 對於位元組大小不固定的欄位，資料更新和刪除會造成磁碟空間不釋放，這時候就行最佳化表，可以整理磁碟片段，提高效能
     文法如下：

     1	optimize table user(表名)；

mysql的最佳化措施，從sql最佳化做起