MySQL調優

# MySQL調優

## 資料庫優化常見方案

1. 優化shema,sql陳述句+索引
2. 加快取，memcached,redis
3. 主從復制，讀寫分離
4. 垂直拆分
5. 水平拆分

為了知道怎么優化SQL,必須先清楚SQL的生命周期

## SQL生命周期

1. 應用服務器連接資料庫服務器，建立一個TCP/IP連接，發送SQL請求給MySQL服務器
2. 查詢快取，有快取則直接回傳資料到應用服務器，沒有則進入到SQL決議器
3. SQL決議器：匹配SQL陳述句，主要是決議語法是否正確,查詢中的表，列名是否存在，檢查表名，列名是否有歧義
4. 查詢優化器:MySQL服務器自己對SQL做優化找到SQL的最佳執行方案，生成執行計劃，優化的方面有索引優化（利用索引和列是否為空來優化count,min，max等聚合函式），順序優化（重新定義表的關聯關系），將外連接轉換為內連接，使用等價變換，比如（1=1 and a>1）將被優化為a>1,如果索引列包含查詢的所有列，則使用索引回傳需要的資料，把子查詢轉換成關聯查詢，減少表的查詢次數，
5. SQL執行器：判斷用戶權限，根據執行計劃呼叫存盤引擎介面獲取資料
6. 將處理結果通過連接回傳到應用服務器


## 慢查詢日志

在優化SQL前需要先找到需要優化的SQL,一般是通過慢查詢日志來查詢

### 查詢是否開啟慢查詢日志

```sql
SHOW VARIABLES LIKE 'slow_query_log';
```


### 開啟慢查詢日志

```sql
SET GLOBAL slow_query_log = 'ON';
```

### 查詢慢查詢日志的路徑

默認和資料檔案放一起

```sql
show VARIABLES like '%slow_query_log_file%';
```


### 慢查詢記錄時間的閾值

默認十秒

```sql
show VARIABLES like '%long_query_time%';
```


### 查詢是否開啟記錄未使用索引的SQL

```sql
show VARIABLES like '%log_queries_not_using_indexes%';
```


找到了需要優化的SQL,下面開始分析SQL的組成

## SQL執行計劃

MySQL使用explain關鍵字來分析SQL，只要在SQL陳述句前加上一個explain關鍵字，就可以得到一個SQL的執行計劃

```sql
explain select * from sys_user where id = 2979;
```


### 執行計劃欄位詳解

#### ID

執行順序的標識，值越大的越優先執行，相同的值由上往下執行

#### select_type

查詢陳述句的型別，下面是各個值

1. SIMPLE：簡單的select查詢，不包含任何子查詢和聯合查詢
2. PRIMARY:主查詢，如果有子查詢的話，最外層的查詢會被標記為PRIMARY
3. SUBQUERY:在select或where串列中包含了子查詢，表示該陳述句屬于子查詢陳述句
4. DERIVED:生成的臨時表的查詢陳述句，也就是子查詢from的一部分
5. DEPENDENT SUBQUERY:子查詢中的第一個SELECT,子查詢依賴于外出查詢的結果
6. UNION:表示union中的第二個或后面的select 陳述句
7. UNION RESULT:從UNION陳述句中獲取結果

#### table

顯示這一行的資料來源于那張表

#### type

定位SQL性能因素最重要的指標，值包括system,const,eq_ref,ref,Range,index,All,性能從高到低

- System: 表只有一行記錄，基本不會出現
- Const:通過索引一次就找到了資料，一般出現在使用了primary key或者unique索引匹配到了資料，匹配的條件常量(字串，數字)
- eq_ref:使用主鍵索引或者非空唯一索引，在表中只有一條記錄與索引鍵匹配，匹配條件是某個表的列(需要轉義替換才能拿到的值，簡單理解為關聯查詢)
- ref:非唯一性索引掃描，和eq_ref不同的是eq_ref匹配的是唯一索引，ref它回傳所有匹配某個單獨值的行，它可能會找到多個符合條件的行
- range:范圍資料掃描
- index:全索引掃描,通過掃描整棵索引樹來獲取到的結果
- All:全表掃描

#### possible_keys

可能會用到的索引

#### Key

實際使用的索引，如果為空，表示沒有使用索引

#### key_len

使用到的索引key長度，如果為聯合索引則顯示已命中的聯合索引長度之和(如：聯合索引為a+b+c,如果索引命中了a+b,那么長度就為a+b的索引長度，通過可以通過key_len來分析聯合索引所命中的情況)

關于possible_keys和key的三種關系場景

possible_keys != null && key != null：正常使用到了索引的情況

possible_keys != null && key==null,這種情況說明通過索引并不能提升多少效率，一般在表的資料量很少，或者是索引的欄位離散性不高，執行計劃發現用索引和掃描差不多

possible_keys == null && key!= null：這種情況一般為where條件沒有命中索引，但是查詢的列是索引欄位，也就是查詢的列命中了覆寫索引的情況

#### ref

實際用到的索引是哪個表的列，const代表常量

#### row

掃描的資料行數，不是準確的值，只是估算，一般來說掃描的資料行數越少，性能越好

#### filtered

回傳結果的行數占需讀取行數的百分比，值越大越好

#### rows

查詢的結果集大小

#### Extra

對整個SQL做概括性總結，包含使用了什么索引，排序方式

- using where:使用了where條件.
- using index:使用了覆寫索引（通常是一種好現象，意味著查詢的資料直接在二級索引回傳了，從而減少了回表的程序），
- using filesort:檔案排序，使用了非索引的欄位進行排序(通常這種情況需要優化)，
- using index sort:使用了索引排序,通常這是一種好現象，索引天然有序，避免了通過sort buffer來排序的流程
- using temporary:使用了臨時表（常見于group by,order by）
- using join buffer:使用 了join buffer快取(這種情況關注一下查詢的欄位是不是沒有建立索引)
- using index condition:索引下推

## SQL優化

### 優化原則

正確使用索引

### 優化查詢列

盡量避免select *，改使用select 列名，避免回傳多余的列，

```sql
優化前：select * from sys_suer
優化后:select id,username,nickname,mobile from sys_user
```

### 優化where子句

優化方案：避免索引失效，可能導致全表掃描的情況

1. 避免對欄位進行null判斷，用特殊值代替，如0

```sql
優化前：select * from sys_user where id = null
優化后：select * from sys_user where id = 0
```

2. 避免使用!=或<>運算子

```sql
優化前:select * from sys_user where dept_id <> 2;
優化后:explain select * from sys_user where dept_id > 2 union all select * from sys_user where dept_id < 2;
```

3. 避免使用or連接條件

```sql
優化前: select * from sys_user where id = 3 or id = 4;
優化后：select * from sys_user where id = 3 union all select * from sys_user where id = 2;
```

4. 避免使用引數，運算式，函式，操作

在應用層將引數轉換成常量

5. 避免在where子句中的“=”左邊進行函式,算術運算或者其他運算式運算

### 優化長難陳述句

優化方案：分解關聯查詢，執行單個查詢，減少鎖的競爭，減少冗余記錄的查詢

### 優化關聯查詢

優化方案：確定ON或者USING子句中是否有索引，確保GROUP BY和ORDER BY只有一個表中的列

### 優化子查詢

優化方案：使用關聯查詢，優化GROUP BY和DISTINCT,這兩種可以根據索引來優化，使用索引列分組效率更高，如果不需要ORDER BY進行GROUP BY時加ORDER BY NULL,mysql不會再進行檔案排序

### 優化LIMIT分頁

優化方案：記錄上次查詢的大ID,下次查詢時直接根據該ID來查詢因為LIMIT偏移量越大，查詢效率越低，因為MySQL不是跳過偏移量，而是先把偏移量+要取出的出來，然后拋棄偏移量后再回傳

```
優化前:select * from sys_user order by id desc limit 1,20
優化后:select * from sys_user where id > 1 order by id desc limit 20
```

### 優化 UNION查詢

優化方案： UNION ALL的效率高于UNION

### like陳述句優化

```
優化前：select * from sys_user where username like "%ws%"
優化后：select * from sys_user where username like "ws%"
```

優化后符合最左前綴原則，會走索引，第一種會索引失效

## 索引優化

### 分類

- 功能上分類：普通索引(NORMAL)，唯一索引(UNIQUE)，主鍵索引（PRIMARY KEY），全文索引（FULLTEXT）
- 實作方式分類：聚簇索引(主鍵屬于聚簇索引)，非聚簇索引
- 欄位個數分類：單列索引，多列索引（聯合索引，覆寫索引）

### 原則

1. 左前綴原則，mysql會一直向右匹配直到遇到范圍查詢（>,<,between,like）就停止匹配
2. 頻繁作為查詢條件的欄位適合創建索引
3. 頻繁更新的欄位不適合創建索引
4. 盡量擴展索引，不要新建索引，一個聯合索引比多個單個索引效率更高

### 建議

1. 盡量使用自增主鍵
2. 索引欄位越小越好，因為查詢索引的時候需要把索引列轉換成一個關鍵字來查詢，欄位越小，轉換的時間越短
3. 索引不要超過6個
4. 洗掉冗余和無效的索引
5. 盡量使用數字型欄位
6. 非空欄位應該指定列為NOTNULL，在mysql中，含有空值的列很難進行查詢優化，因為他使得索引的統計資訊變得更加復雜，應該用0或者一個特殊的值來代替空值
7. 將離散大的欄位（變數各個取值之間的差異程度）的列放到聯合索引的前面，可以通過count()函式查詢欄位的差異值，回傳值越大說明欄位的離散程度越高

### 索引案例

```sql
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` int NOT NULL AUTO_INCREMENT,
`username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用戶名',
`nickname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '昵稱',
`gender` tinyint(1) NULL DEFAULT 1 COMMENT '性別((1:男;2:女))',
`password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密碼',
`dept_id` int NULL DEFAULT NULL COMMENT '部門ID',
`avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '用戶頭像',
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '聯系方式',
`status` tinyint(1) NULL DEFAULT 1 COMMENT '用戶狀態((1:正常;0:禁用))',
`email` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用戶郵箱',
`deleted` tinyint(1) NULL DEFAULT 0 COMMENT '邏輯洗掉標識(0:未洗掉;1:已洗掉)',
`create_time` datetime NULL DEFAULT NULL COMMENT '創建時間',
`update_time` datetime NULL DEFAULT NULL COMMENT '更新時間',
PRIMARY KEY (`id`) USING BTREE,
INDEX `sys`(`username`, `dept_id`, `nickname`, `deleted`) USING BTREE,
INDEX `dept_id`(`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1021664 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶資訊表' ROW_FORMAT = DYNAMIC;
```

#### 有效的索引

1. 符合最左匹配原則，where后面的欄位順序和索引順序一致

```sql
explain select * from sys_user where username = '有來技術' and dept_id = 2 and nickname = "test用戶" and deleted = 1
```


2. 覆寫索引，雖然不符合最左匹配原則，但是查詢的列都在索引中

```
explain select username,nickname,deleted from sys_user where nickname = "test用戶" and deleted = 1
```

3.索引下推，雖然username是索引欄位，但是后面加上了模糊查詢，并且是以%開頭，應該是用不了索引的，但是MYSQL在5.6引入了(index Condition Pushdown)簡稱ICP特性，在存盤引擎層優化了這種情況，也能使用索引
```
explain select * from sys_user where username = '有來技術' and email like '%youlai'
```


#### 無效的索引

1. 不符合最左匹配原則

```
explain select * from sys_user where dept_id = 2 and nickname = "test用戶" and deleted = 1
```


2. 模糊查詢以%開頭

```
explain select * from sys_user where username like '%有來技術'
```


3. 使用!=導致索引失效，雖然username是索引欄位，但是因為使用了！=，需要回表根據值來過濾資料，所以索引失效了

```
explain select * from sys_user where username != '有來技術'
```


4.使用了計算運算式

```
explain select * from sys_user where dept_id - 1 = 1
```


5.使用索引自身型別不同的值

```
explain select * from sys_user where username = 1
```


標籤：其他

上一篇：云小課｜MRS資料分析-通過Spark Streaming作業消費Kafka資料

下一篇：云小課｜MRS基礎原理之Hue組件介紹