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(表名)；