Mysql 高級

1 MySQL 的架構介紹

1.1 sql_mode

sql_mode 是一個容易忽視的變量，默認情況下為空，可以忍耐一些非法操作，在生產環境中，必須將其設置為嚴格模式，在開發測試環境中配該變量也是很有必要的，因為這樣可以在生產之前發現問題。

sql_mode 常用值如下：

• ONLY_FULL_GROUP_BY：對于 GROUP BY 聚合操作，如果在 SELECT 中的列沒有在 GROUP BY 中出現，那么這個 sql 是不合法的
• NO_AUTO_VALUE_ON_ZERO：該值影響自增列的插入，默認情況下，插入 0 或 NULL 代表生成下一個增長值，如果用戶希望插入的主鍵 ID 為0，就可以配置這個值
• STRICT_TRANS_TABLES：如果一個值不能插入到一個事務表中，則中斷當前的操作，非事務表不限制
• NO_ZERO_IN_DATE：在嚴格模式下，不允許日期和月份為零
• NO_ZERO_DATE：不允許插入零日期，否則拋出異常
• ERROR_FOR_DIVISION_BY_ZERO：在插入或更新過程中，如果數據被零除，則產生錯誤，如果未配置該參數，那么數據被零除時返回 NULL
• NO_AUTO_CREATE_USER：禁止創建密碼為空的用戶
• NO_ENGINE_SUBSTITUTION：如果需要的存儲引擎未編譯或被禁用，則拋出錯誤
• PIPES_AS_CONCAT：將 || 視為字符串連接操作符
• ANSI_QUOTES：配置該參數后，不能用 “” 引用字符串

1.2 MySQL 邏輯架構

和其它數據庫相比，MySQL 有點與眾不同，它的架構可以在多種不同場景中應用并發揮良好作用，主要體現在存儲引擎的架構上，插件式的存儲引擎架構將查詢處理和其它的系統任務以及數據的存儲提取相分離，這種架構可以根據業務的需求和實際需要選擇合適的存儲引擎。

連接層

最上層是客戶端和連接服務，包含本地 socket 通信和 tcp/ip 通信，主要完成連接處理、授權認證及相關的安全方案，該層引入了線程池，為授權用戶提供線程，還實現了 ssl 安全鏈接。

服務層

• Management Serveices & Utilities：系統管理和控制工具
• SQL Interface：SQL 接口，接收 SQL 命令，并返回查詢結果
• Parser：解析器，對 SQL 命令進行驗證和解析
• Optimizer：查詢優化器，在查詢之前對語句進行優化
• Cache 和 Buffer：查詢緩存，如果查詢緩存有命中的查詢結果，查詢語句就可以直接去查詢緩存中取數據，這個緩存機制是由一系列小緩存組成的，比如表緩存，記錄緩存，key緩存，權限緩存等

引擎層

存儲引擎層，負責了數據的存儲和提取，服務器通過 API 與存儲引擎進行通信。

存儲層

數據存儲層，主要是將數據存儲在運行于裸設備的文件系統之上，并完成與存儲引擎的交互。

1.2.1 SQL 的執行周期

開啟診斷分析工具

set profiling=1;

顯示最近的幾條查詢

show profiles;

查看 SQL 的執行步驟

show profile cpu,block io for query 1;

1.2.2 查詢流程

• 客戶端和 MySQL 建立連接，發送查詢語句，先查詢緩存，如果緩存有命中，直接返回結果，否則進行語句解析
• 也就是說，解析之前先訪問緩存，解析器將對語句進行語法規則校驗和解析查詢，然后會生成一顆解析樹
• 之后由優化器將解析器轉換成執行計劃，最后執行計劃，返回結果

1.2.3 SQL 執行順序

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where__condition>
GROUP BY<group_by_list>
HAVING <having__condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by__condition>
LIMIT <limit_number>

1.3 MySQL 存儲引擎

查看支持的存儲引擎

show engines;

查看當前默認的存儲引擎

show variables like '%storage_engine%';

1.3.1 各個引擎簡介

InnoDB

InnoDB 是 MySQL 默認的事務型引擎，用來處理大量的短期事務，除非有特別的原因需要用到其他存儲引擎，否則優先考慮 InnoDB。

MyISAM

MyISAM 提供了大量的特性，包括全文檢索、壓縮、空間函數等，但 MyISAM 不支持事務和行級鎖，缺點是崩潰后無法安全恢復。

Archive

Archive 檔案存儲引擎只支持 INSERT 和 SELECT 操作，在 MySQL5.1 之前不支持索引；

Archive 表適合日志和數據采集類應用；

根據英文的測試結論來看，Archive 表比 MyISAM 表要小大約 75%，比支持事務處理的 InnoDB 表小大約 83%。

Blackhole

Blackhole 引擎沒有實現任何存儲機制，它會丟棄所有插入的數據，不做任何保存。但服務器會記錄Blackhole表的日志，所以可以用于復制數據到備庫，或者簡單地記錄到日志。但這種應用方式會碰到很多問題，因此并不推薦。

CSV

CSV 引擎可以將普通的 CSV 文件作為 MySQL 表來處理，但不支持索引， CSV 可以作為一種數據交換的機制，CSV 引擎存儲的數據可以被文本編輯器、execl 讀取。

Memory

如果需要快速地訪問數據，并且這些數據不會被修改，重啟以后丟失也沒有關系，那么使用Memory表是非常有用，Memory 表至少比 MyISAM 表要快一個數量級。

Federated

Federated 引擎是訪問其他 MySQL 服務器的一個代理，盡管該引擎看起來提供了一種很好的跨服務器的靈活性，但也經常帶來問題，因此默認是禁用的。

1.3.2 InnoDB 和 MyISAM

2 索引優化分析

2.1 優化步驟

分庫分表

SQL 優化

建立索引

調整 my.cnf 優化服務器及配置參數

2.2 索引簡介

2.2.1 什么是索引？

數據本身之外，數據庫還維護著一個滿足特定查找算法的數據結構，這些數據結構以某種方式指向數據，這樣就可以在這些數據結構的基礎上實現高級查找算法，這種數據結構就是索引；

一般來說索引本身也很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲的磁盤上；

雖然索引提高了查詢的效率，但是也降低了更新的效率，因為更新表時，不僅要插入數據，同時還要保存一下索引文件每次更新添加了的索引列的字段，都會調整因為更新所帶來的鍵值變化后的索引信息；

實際上索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄，所以索引列也是要占用空間的。

2.2.2 MySQL 索引結構

2.2.2.1 BTree 索引

如圖所示，磁盤塊 1 包含數據項 17 和 35，包含指針 P1、P2、P3

P1 表示小于 17 的磁盤塊，P2 表示介于 17 和 35 之間的磁盤塊，35 表示大于 35 的磁盤塊

查找過程

如果要查找數據項 29，首先將磁盤塊 1 加載到內存，此時發生一次 IO，利用二分查找確定 29 在 17 和 35 之間，鎖定磁盤塊 1 的 P2 指針，通過磁盤塊 1 的 P2 指針的磁盤地址把磁盤塊 3 加載到內存，此時發生一次 IO，利用二分查找確定 29 在26 和 30 之間，鎖定磁盤塊 3 的 P2 指針，通過磁盤塊 3 的 P2 指針的磁盤地址把磁盤塊 8 加載到內存，此時發生一次 IO，同時利用二分查找到 29，查詢結束。

2.2.2.2 B+Tree 索引

B+ 樹的非葉子節點只是存儲 key，占用空間非常小，因此每一層的節點能索引到的數據范圍更加的廣，換句話說，每次 IO 操作可以觀看更多的數據；

葉子節點兩兩相連，符合磁盤的預讀特性。如圖存儲 5、8 、9 的葉子節點，它有個指針指向了 10、15、18 這個葉子節點，那么當我們從磁盤讀取5、8、9 對應的數據的時候，由于磁盤的預讀特性，會順便把 10、15、18 對應的數據讀取出來，這個時候屬于順序讀取，而不是磁盤尋道了，加快了速度；

支持范圍查詢，而且部分范圍查詢非常高效，原因是數據都是存儲在葉子節點這一層，并且有指針指向其他葉子節點，這樣范圍查詢只需要遍歷葉子節點這一層，無需整棵樹遍歷。

2.2.2.3 聚簇索引與非聚簇索引

聚簇索引并不是一種單獨的索引類型，而是一種數據存儲方式，聚簇表示數據行和相鄰的鍵值聚簇的存儲在一起；

按照聚簇索引排列順序，查詢顯示一定范圍數據的時候，由于數據都是緊密相連，數據庫不不用從多個數據塊中提取數據，所以節省了大量的 IO 操作；

對于 MySQL 數據庫目前只有 InnoDB 數據引擎支持聚簇索引，而 MyISAM 并不支持聚簇索引；

由于數據物理存儲排序方式只能有一種，所以每個 MySQL 的表只能有一個聚簇索引，一般情況下就是該表的主鍵；

為了充分利用聚簇索引的聚簇的特性，所以 InnoDB 表的主鍵列盡量選用有序的順序 ID，而不建議用無序的 ID，比如 UUID這種。

2.2.3 MySQL 索引分類

2.2.2.3.1 單值索引

即一個索引只包含單個列，一個表可以有多個單列索引

隨表一起建索引： 
CREATE TABLE customer (
    id INT (10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR (200),
    customer_name VARCHAR (200),
    PRIMARY KEY (id),
    KEY (customer_name)
);

單獨建單值索引： 
CREATE INDEX idx_customer_name ON customer (customer_name);

刪除索引： 
DROP INDEX idx_customer_name ON customer;

2.2.3.2 唯一索引

索引列的值必須唯一，但可以為空

隨表一起建索引： 
CREATE TABLE customer (
    id INT (10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR (200),
    customer_name VARCHAR (200),
    PRIMARY KEY (id),
    KEY (customer_name),
    UNIQUE (customer_no)
);

單獨建唯一索引： 
CREATE UNIQUE INDEX idx_customer_no ON customer (customer_no);

刪除索引： 
DROP INDEX idx_customer_no ON customer;

2.2.3.3 主鍵索引

設為主鍵后自動創建主鍵索引

隨表一起建索引： 
CREATE TABLE customer (
    id INT (10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR (200),
    customer_name VARCHAR (200),
    PRIMARY KEY (id)
);

單獨建主鍵索引： 
ALTER TABLE customer ADD PRIMARY KEY customer (customer_no);

刪除建主鍵索引： 
ALTER TABLE customer DROP PRIMARY KEY;

修改建主鍵索引： 必須先刪除掉 (DROP) 原索引，再新建 (ADD) 索引

2.2.3.4 復合索引

一個索引包含單個列

隨表一起建索引： 
CREATE TABLE customer (
    id INT (10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR (200),
    customer_name VARCHAR (200),
    PRIMARY KEY (id),
    KEY (customer_name),
    UNIQUE (customer_name),
    KEY (customer_no, customer_name)
);

單獨建索引： 
CREATE INDEX idx_no_name ON customer (customer_no, customer_name);

刪除索引： 
DROP INDEX idx_no_name ON customer;

2.2.4 創建索引的時機

哪些情況需要創建索引？

• 主鍵自動創建唯一索引
• 頻繁作為查詢條件的字段應該創建索引
• 查詢中與其他表關聯的字段，外鍵關系建立索引
• 組合索引比單值索引性價比更高
• 查詢排序的字段，排序字段若通過索引去訪問將大大提高排序速度
• 查詢中統計或分組字段

哪些情況不需要創建索引？

• 表記錄太少
• 經常更新的表
• where 條件里用不到的字段不需要創建索引
• 過濾性不好的字段不需要創建索引

2.3 性能分析

2.3.1 EXPLAN

使用 EXPLAIN 關鍵字可以模擬優化器執行 SQL 查詢語句，從而知道 MySQL 是如何處理 SQL 語句的，分析查詢語句或是表結構的性能瓶頸。

EXPLAN 的作用：

查看表的讀取順序

查看哪些索引可以被使用

數據讀取操作的操作類型

哪些索引被實際使用

表之間的引用

使用方式：

Explain + SQL

Explain SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
    -> UNION
    -> SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;

2.3.2 各字段解釋

• id：

  SELECT 查詢的序列號,包含一組數字，表示查詢中執行 SELECT 子句或操作表的順序

  • id 相同：執行順序由上至下

  • id 不同：如果是子查詢，id 的序號會遞增，id 值越大優先級越高，越先被執行

  • id 相同不同，同時存在：id如果相同，可以認為是一組，從上往下順序執行；在所有組中，id值越大，優先級越高，越先執行

  每個 id 表示一趟獨立的查詢，一個 SQL 的查詢趟數越少越好

• select_type

  查詢的類型，主要是用于區別普通查詢、聯合查詢、子查詢等的復雜查詢

  • SIMPLE：最簡單的查詢，不包含 UNION 和子查詢

  • PRIMARY：查詢中若包含復雜的子部分，最外層查詢被標記為 PRIMARY

  • DERIVED：在 FROM 列表中包含的子查詢被標記為 DERIVED，MySQL 會遞歸執行這些子查詢, 把結果放在臨時表里

  • SUBQUERY：在 SELECT 或 WHERE 列表中包含子查詢

  • DEPENDENT SUB：在 SELECT 或 WHERE 列表中包含子查詢，子查詢基于外層

  • UNCACHEABLE SUBQUREY：結果集不能被緩存的子查詢，必須重新為外層查詢的每一行進行評估

  • UNION：若第二個 SELECT 出現在 UNION 之后，則被標記為 UNION

  • UNION RESULT：從 UNION 表獲取結果的 SELECT

• table

  顯示這一行的數據是關于哪張表的

• type

  顯示連接使用的類型，按最優到最差的類型排序

  • system：表只有一行記錄

  • const：表示通過索引一次就找到了，const 用于比較 primary key 或者 unique 索引，因為只匹配一行數據，所以很快
    如將主鍵置于 where 列表中，MySQL 就能將該查詢轉換為一個常量

  • eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配，常見于主鍵或唯一索引掃描

  • ref：非唯一性索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以它應該屬于查找和掃描的混合體

  • range：只檢索給定范圍的行，使用一個索引來選擇行，key 列顯示使用了哪個索引，一般就是在 where 語句中出現了 between、<、>、in 等的查詢，這種范圍掃描索引掃描比全表掃描要好，因為它只需要開始于索引的某一點，而結束語另一點，不用掃描全部索引

  • index：出現 index 是 SQL 使用了索引但是沒用通過索引進行過濾，一般是使用了覆蓋索引或者是利用索引進行了排序分組

  • all：Full Table Scan，將遍歷全表以找到匹配的行

  • index_merge：在查詢過程中需要多個索引組合使用，通常出現在有 or 的關鍵字的 SQL 中

  • ref_or_null：對于某個字段既需要關聯條件，也需要 null 值的情況下，查詢優化器會選擇用 ref_or_null 連接查詢

  • index_subquery：利用索引來關聯子查詢，不再全表掃描

  • unique_subquery：該聯接類型類似于 index_subquery，子查詢中的唯一索引

  一般來說，得保證查詢至少達到 range 級別，最好能達到 ref

• possible_keys

  顯示可能應用在這張表中的索引，一個或多個，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用

• key

  實際使用的索引，如果為NULL，則沒有使用索引，查詢中若使用了覆蓋索引，則該索引和查詢的 select 字段重疊

• key_len

  表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度， key_len 字段能夠檢查是否充分的利用上了索引

• ref

  顯示索引的哪一列被使用了，如果可能的話，是一個常數，哪些列或常量被用于查找索引列上的值

• rows

  rows 列顯示 MySQL 認為它執行查詢時必須檢查的行數

• Extra

  包含不適合在其他列中顯示但十分重要的額外信息

  • Using filesort：說明 mysql 會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取，MySQL 中無法利用索引完成的排序操作稱為文件排序
  • Using temporary：使了用臨時表保存中間結果，MySQL 在對查詢結果排序時使用臨時表，常見于排序 order by 和分組查詢 group by
  • USING index：表示相應的 select 操作中使用了覆蓋索引(Covering Index)，避免訪問了表的數據行，效率不錯，如果同時出現 using where，表明索引被用來執行索引鍵值的查找，如果沒有同時出現 using where，表明索引只是用來讀取數據而非利用索引執行查找
  • Using where：表明使用了 where 過濾
  • using join buffer：使用了連接緩存：
  • impossible where：where 子句的值總是 false，不能用來獲取任何元組
  • select tables optimized away：在沒有 GROUP BY 子句的情況下，基于索引優化 MIN/MAX 操作或者對于MyISAM 存儲引擎優化 COUNT(*) 操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化。

2.4 查詢優化

2.4.1 單表使用索引失效問題

index(a,b,c)

創建索引的建議：

對于單值索引，盡量選擇針對當前查詢過濾性更高的字段

選擇組合索引，當前查詢過濾性最高的字段在索引的位置越靠前越好

選擇組合索引，盡量選擇可以能夠包含當前查詢中的 where 字句中更多字段的索引

在選擇組合索引的時候，如果某個字段可能出現范圍查詢時，盡量把這個字段放在索引次序的最后面

2.4.2 關聯查詢優化

保證被驅動表的 join 字段已經被索引

left join 時，選擇小表作為驅動表，大表作為被驅動表

inner join 時，MySQL 會自己把小結果集的表選為驅動表

子查詢盡量不要放在被驅動表，有可能使用不到索引

能夠直接多表關聯的盡量直接關聯，不用子查詢

2.4.3 子查詢優化

盡量不要使用not in 或者 not exists，用 left join on xxx is null 替代

2.4.4 排序分組優化

ORDER BY子句，盡量使用Index方式排序,避免使用FileSort方式排序

如果不在索引列上，filesort 有兩種算法：

雙路排序

• MySQL 4.1 之前是使用雙路排序,字面意思就是兩次掃描磁盤，最終得到數據，讀取行指針和 order by 列，對他們進行排序，然后掃描已經排序好的列表，按照列表中的值重新從列表中讀取對應的數據輸出
• 從磁盤取排序字段，在 buffer 進行排序，再從磁盤取其他字段
• 取一批數據，要對磁盤進行了兩次掃描，眾所周知，IO 是很耗時的，所以在 MySQL 4.1 之后，出現了第二種改進的算法，就是單路排序

單路排序：

• 從磁盤讀取查詢需要的所有列，按照 order by 列在 buffer 對它們進行排序，然后掃描排序后的列表進行輸出，它的效率更快一些，避免了第二次讀取數據，并且把隨機 IO 變成了順序 IO，但是它會使用更多的空間，因為它把每一行都保存在內存中了
• 單路排序是把所有的字段都取出，所以有可能取出的數據的總大小超過了 sort_buffer 的容量，導致每次只能取 sort_buffer 容量大小的數據進行排序，排序完再取一部分，反而增大了 IO
• 優化策略
  • 增大sort_buffer_size參數的設置
  • 增大max_length_for_sort_data參數的設置
  • 減少 select 后面的查詢的字段
  • 禁止使用 select *

group by 使用索引的原則幾乎跟 order by 一致 ，唯一區別是 group by 即使沒有過濾條件用到索引，也可以直接使用索引

3 查詢截取分析

什么是慢查詢日志？

慢查詢日志是 MySQL 提供的一種日志記錄，它用來記錄在 MySQL 中響應時間超過閾值的語句，具體指運行時間超過long_query_time 值的 SQL，則會被記錄到慢查詢日志中；

long_query_time 的默認值為10，意思是運行10秒以上的語句。

默認慢查詢日志是關閉的，需要手動開啟

查看慢查詢日志是否開啟
SHOW VARIABLES LIKE '%slow_query_log%';
開啟慢查詢日志
set global slow_query_log=1;

查看并配置 long_query_time

查看long_query_time
SHOW VARIABLES LIKE 'long_query_time%';
set  long_query_time=1

日志分析工具 mysqldumpslow

常用參考：

hadoop100得到返回記錄集最多的10個SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop100-slow.log

得到訪問次數最多的10個SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hadoop100-slow.log

得到按照時間排序的前10條里面含有左連接的查詢語句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hadoop100-slow.log

另外建議在使用這些命令時結合 | 和more 使用 ，否則有可能出現爆屏情況
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop100-slow.log | more

4 主從復制

復制的基本原理

master 將改變記錄到二進制日志（binary log），這些記錄過程叫做二進制日志事件，binary log events；

slave 將 master 的 binary log events 拷貝到它的中繼日志（relay log）；

slave 重做中繼日志中的事件，將改變應用到自己的數據庫中，MySQL 復制是異步的且串行化的。

復制的基本原則

每個 slave 只有一個 master

每個 slave 只能有一個唯一的服務器 ID

每個 master 可以有多個salve

4.1 配置主從復制

1、配置主數據庫

vim /etc/my.cnf

server-id=1
log-bin=mysql-bin
binlog_format=mixed

為從服務分配賬號

查看主服務器 BIN 日志的信息

show master status;

重啟主數據庫

systemctl restart mariadb

2、配置從數據庫

連接主數據庫

CHANGE MASTER TO 
    -> MASTER_HOST="192.168.10.100",
    -> MASTER_USER="slave",
    -> MASTER_PASSWORD="123456",
    -> MASTER_LOG_FILE="mysql-bin.000001",
    -> MASTER_LOG_POS=388;

啟動從數據庫

start slave;