[TOC]
## 分析SQL查詢慢的方法
1. 記錄慢查詢日志
分析查詢日志,不要直接打開慢查詢日志進行分析,這樣比較浪費
時間和精力,可以使用pt- query- digest工具進行分析
2. 使用 show profile
`set profiling=1`;開啟,服務器上執行的所有語句會檢測消耗的時
間,存到臨時表中
```
set profiling=1; //開啟 profile
show profiles
show profile for query臨時表ID
```
demo
```
mysql > set profiling=1;s
mysql > select from a;
mysql> show profiles;
```

3. 使用 show status
`show status`會返回一些計數器, `show global status`查看服務器級別的所有計數有時根據這些計數,可以猜測出哪些操作代價較高或者消耗時間多
4. 使用 show processlist
觀察是否有大量線程處于不正常
5. 使用 explain
分析單條SQL語句
`mysql> explain select * from fq_order\G;`
## 優化查詢過程中的數據訪問
- 訪問數據太多導致查詢性能下降
- 確定應用程序是否在檢索大量超過需要的數據,可能是太多行或列
- 確認 MYSQL服務器是否在分析大量不必要的數據行
- 查詢不需要的記錄,使用 limit解決
- 多表關聯返回全部列,指定Aid,A.name,Bage
- 總是取出全部列, `SELECT*`會讓優化器無法完成索引覆蓋掃描的優化
- 重復查詢相同的數據,可以緩存數據,下次直接讀取緩存
## 是否在掃描額外的記錄
使用 explain來進行分析,如果發現查詢需要掃描大量的數據但只返回少數的行,可以通過如下技巧去優化:
使用索引覆蓋掃描,把所有用的列都放到索引中,這樣存儲引擎不需要回表獲取對應行就可以返回結果
## 一個復雜查詢好與多個簡單查詢
- MysαL內部每秒能掃描內存中上百萬行數據,相比之下,響應數據給客戶端就要慢得多
- 使用盡可能少的查詢是好的,但是有時將一個大的查詢分解為多個小的查詢是很有必要
## 切分查詢
將一個大的查詢分為多個小的相同的查詢一次性刪除1000萬的數據要比一次刪除1萬,暫停一會的方案更加損耗服務器開銷
## 分解關聯查詢
- 可以將一條關聯語句分解成多條SQL來執行
- 讓緩存的效率更高
- 執行單個查詢可以減少鎖的競爭
- 在應用層做關聯可以更容易對數據庫進行拆分
- 查詢效率會有大幅提升
- 較少冗余記錄的查詢
## 優化 count()查詢
- `count(*`)中的`*`會忽略所有的列,直接統計所有列數,因此不要使用 count(列名)
- MYISAM中,沒有任何 WHERE條件的` count(*)`非常快,當有 WHERE條件, MYISAM的 count統計不一定比其他表引擎快
- 可以使用 explain查詢近似值,用近似值替代 count()
- 增加匯總表
- 使用緩存
## 優化關聯查詢
- 確定ON或者 USING子句的列上有索引
- 確保 GROUP BY和 ORDER BY中只有一個表中的列,這樣 MYSQL才有可能使用索引
## 優化子查詢
盡可能使用關聯查詢來替代
## 優化 GROUP BY和 DISTINCT
- 這兩種查詢均可使用索引來優化,是最有效的優化方法
- 關聯查詢中,使用標識列進行分組的效率會更高
- 如果不需要 ORDER BY,進行 GROUP BY時使用 ORDER BY NULL, MYSQL不會再進行文件排序
- WITH ROLLUP超級聚合,可以挪到應用程序處理
> 分類聚合后的結果進行匯總 [參考用法簡書 ](https://www.jianshu.com/p/5d2f700b0a31)
## 優化UNON查詢
UNION ALL的效率高于 UNION
## 分區表的原理
對用戶而言,分區表是一個獨立的邏輯表,但是底層 MYSQL將其分成了多個物理子表,這對用戶來說是透明的,每一個分區表都會使用一個獨立的表文件 .
創建表時使用 partition by子句定義每個分區存放的數據,執行查詢時,優化器會根據分區定義過濾那些沒有我們需要數據的分區,這樣查詢只需要查詢所需數據在的分區即可
分區的主要目的是將數據按照一個較粗的粒度分在不同的表中,這樣可以將相關的數據存放在一起,而且如果想一次性刪除整個分區的數據也很方便
### 適用場景
1. 表非常大,無法全部存在內存,或者只在表的最后有熱點數據,其他都是歷史數據
2. 分區表的數據更易維護,可以對獨立的分區進行獨立的操作
3. 分區表的數據可以分布在不同的機器上,從而高效使用資源
4. 可以使用分區表來避免某些特殊的瓶頸
5. 可以備份和恢復獨立的分區
### 限制
1. 一個表最多只能有1024個分區
2. 5.1版本中,分區表表達式必須是整數,5.5可以使用列分區
3. 分區字段中如果有主鍵和唯一索引列,那么主鍵列和唯一列都必須包含進來
4. 分區表中無法使用外鍵約束
5. 需要對現有表的結構進行修改
6. 所有分區都必須使用相同的存儲引擎
7. 分區函數中可以使用的函數和表達式會有一些限制
8. 某些存儲引擎不支持分區
9. 對于 MYISAM的分區表,不能使用 load index into cache
10. 對于 MYISAM表,使用分區表時需要打開更多的文件描述符
## 分庫分表的原理
### 工作原理
通過一些HASH算法或者工具實現將一張數據表垂直或者水平進行物理切分
### 適用場景
1. 單表記錄條數達到百萬到千萬級別時
2. 解決表鎖的問題
### 分表方式
#### 水平分割
表很大,分割后可以降低在查詢時需要讀的數據和索引的頁數,同時也降低了索引的層數,提高查詢速度
使用場景
1. 表中的數據本身就有獨立性,例如表中分別記錄各個地區的數據或者不同時期的數據,特別是有些數據常用,有些不常用
2. 需要把數據存放在多個介質上
水平分表缺點
1. 給應用增加復雜度,通常查詢時需要多個表名,查詢所有數據都需 `UNION` 操作
2. 在許多數據庫應用中,這種復雜性會超過它帶來的優點,查詢時會增加讀一個索引層的磁盤次數
### 垂直分表
把主鍵和一些列放在一個表,然后把主鍵和另外的列放在另一個表中,就是多一張詳情表
使用場景
1. 如果一個表中某些列常用,而另外一些列不常用
2. 可以使數據行變小,一個數據頁能存儲更多數據,查詢時減少I/O次數
垂直分表缺點
1. 管理冗余列,查詢所有數據需要JoIN操作
## MYSQL的復制原理及負載均衡
### MYSQL主從復制工作原理
在主庫上把數據更改記錄到二進制日志
從庫將主庫的日志復制到自己的中繼日志
從庫讀取中繼日志中的事件,將其重放到從庫數據中
### MYSQL主從復制解決的問題
數據分布:隨意停止或開始復制,并在不同地理位置分布數據備份
負載均衡:降低單個服務器的壓力
高可用和故障切換:幫助應用程序避免單點失敗
升級測試:可以使用更高版本的 MYSQL作為從庫
### 解題方法
充分掌握分區和分表的工作原理和適用場景,在面試中,此類題通
常比較靈活,會給一些現有公司遇到問題的場景,大家可以根據分
區和分表以及Mysα復制、負載均衡的適用場景來根據情況進行
回答。