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