我唯一知道的就是我一無所知——蘇格拉底

1、Linux安裝MySQL

1.1、安裝參考資料

?

MySQL的刪除：https://blog.csdn.net/weixin_34462581/article/details/113010258

MySQL的安裝：參考官網https://dev.mysql.com/doc/refman/8.0/en/linux-installation-yum-repo.html

MySQL的資源：https://dev.mysql.com/downloads/file/?id=484922

MySQL密碼忘記（異常失效）：https://www.jb51.net/article/127281.htm

1.2、注意事項

• 檢查端口3306是否被占用
• 如果已經安裝過MySQL，并且需要重新安裝，需要前之前安裝的完全卸載。包含mysql相關的文件夾、文件，尤其注意其配置文件/etc/my.cnf必須要刪掉
• 按照官網安裝即可

netstat -tunlp | grep 3306?

• 安裝完成之后，記得把用戶角色的遠程訪問權限改一下（localhost->%）

?

1.3、常見問題記錄

1.3.1、密碼意外丟失

?

2、MySQL執行流程與架構

2.1、本節問題：

• 描述一條查詢語句的執行流程，什么模塊做了什么事情
• 記錄redo log和bin log，為什么要要用XA兩階段提交？如果redo log直接寫入成功，bin log寫入失敗，會出現什么問題？

?

2.2、MySQL基礎

2.2.1、MySQL服務的基本概念

我們的數據是存儲在服務端，我們使用的工具（eg: navicat）是客戶端。如果要讀取數據，首先要建立客戶端與服務端的連接， 服務端運行3306的端口，客戶端會連接到3306。其次兩端必須要規范協議，消息報文格式等。

2.2.1.1、連接方式 ：

• 長連接（連接一直保持打開，也就是說可以被其他會話、客戶端復用。這樣可以減少創建和釋放連接的消耗，但會消耗服務端的性能），一般這是種通用方式。
• 短連接（客戶端操作完后，連接馬上close）

?

查看連接數：

? ?SHOW GLOBAL STATUS LIKE 'Thread%';

客戶端跟服務端建立一個連接，對服務端來講就是創建一個線程來處理連接。所以查Thread就是查連接數。這個命令可以差大已經緩存的連接數、已經建立的連接數、已創建的連接數、運行中的連接數

2.2.1.2、如何在客戶端查詢相關配置

• 舉例：長連接過多久不活動會被釋放掉？

-- 連接的超時時間 SHOW GLOBAL VARIABLES LIKE 'wait_timeout'; -- 非交互式超時時間（沒有界面的），如JDBC程序 SHOW GLOBAL VARIABLES LIKE 'interactive_timeout'; -- 交互式超時時間，如數據庫工具（單位秒，一共8個小時，如果需要加快回收速度，可以調小這個值） -- 允許的最大連接數（并發線程數） SHOW GLOBAL VARIABLES LIKE 'max_connections';

效果：

• 問題：這些變量在哪找？

https://dev.mysql.com/ ?官網下文檔里系統預設變量可查到。

?

?

2.2.1.3、如何更改相關配置

系統中的變量有兩個級別session、global：

• • session是會話級別，作用域只在當前會話，如果會話的生命周期結束，那么當前session中的配置也會失效。舉例：SHOW VARIABLES LIKE 'max_connections' 默認的是當前會話的級別。
  • global是全局級別

?

修改有兩種方式 ：

• • 動態修改
  • 配置文件永久修改 /etc/my.cnf

?

此處以動態配置舉例：

• 第一步： SHOW VARIABLES LIKE 'autocommit'; 看到是ON

• 第二步： SET autocommit = off; 在當前會話將ON改為OFF
• 第三步：SHOW VARIABLES LIKE 'autocommit'; 在當前會話再查一次，看到是OFF。

• 第四步：SHOW VARIABLES LIKE 'autocommit'; 新開一個會話再查一次，看到還是ON。因為這里的修改是session級別，修改只作用于當前會話，所以再打開一個頁面查詢還是ON。

• 第五步：總結，當前到操作是session級別，只在當前會話生效。如果要在所有的會話都生效，要在語句中加上global

`SHOW GLOBAL VARIABLES LIKE 'autocommit';`

?

2.2.2、補充說明

• 通信協議

TCP/IP ? Unix Socket

MySQL的通訊協議文件：/var/lib/mysql.socket

?

• MySQL用的是半雙工

由于是半雙工的模式，客戶端和服務器的數據接收和發送不能同時進行，必須要等待一個操作全部執行完畢之后才可以下一個操作。因此，一條sql做大量數據的查詢，會帶來網絡和服務的消耗，所以單條sql盡量小粒度的查詢，比如帶上查詢條件（limit，或者業務層代碼寫一個循環進行分批的查詢）。

?

?

2.3、MySQL服務層的主要模塊

2.3.1、查詢緩存

MySQL8之前，mysql服務提供數據的緩存（改配置默認是關閉的），目的是將查詢到的結果以key-value的形式緩存起來，如果下一次遇到同樣的查詢，便可以省去從磁盤獲取數據導致的開銷，以提升吞吐量。

MySQL8已經取消了查詢緩存：

一種說法是不建議使用查詢緩存，因為查詢緩存往往弊大于利。緩存的級別是針對于表的，而不是針對于語句的，如果表中有任意一行數據變化，都會把整個表的緩存清空。所以不建議在數據庫的服務端開啟緩存。這樣，查詢緩存的失效會非常頻繁，只要有對一個表的更新，這個表上的所有的查詢緩存都會被清空。因此很可能你費勁地把結果存起來，還沒使用呢，就被一個更新全清空了。對于更新壓力大的數據庫來說，查詢緩存的命中率會非常低。除非你的業務有一張靜態表，很長時間更新一次，比如系統配置表，那么這張表的查詢才適合做查詢緩存。

有觀點認為，大多數應用都把緩存做到了應用邏輯層，簡單的如一個map的mybatis，復雜的可以用redis或者memcache，直接操作內存遠遠比走網絡訪問快，所以mysql直接拋棄了查詢緩存。

?

2.3.2、解析環節

2.3.2.1、解析器

解析器分為：

• • 詞法解析，把sql語句打碎成一個一個的單詞，包含關鍵字、變量、符號
  • 語法解析，分析語法是否符合規則，比如左邊有括號，右邊一定要有對稱的括號。

（注：詞法和語法解析是非常基礎且常見的功能，比如jvm、json等有會有用到。世面上也已經有比較好的開源的工具）

?

2.3.2.1、預處理器（得到解析樹）

試想一下：

select * from 不存在的表; 由于表不存在會報錯，又或者 select b.* from user a; 由于別名不存在報錯。

那么是在解析時報錯，還是執行的時候報錯呢？

上一節我們知道解析器解析的是詞法和語法，如果要解析校驗數據庫里有哪些表，表里有哪些字段，就要把表的信息存儲起來。那么預處理器就是干這個的。

?

預處理器的作用：

• • 權限

• • • 操作的權限，不允許的ip的訪問
    • 用戶的權限，只讀的權限不能寫

• • 語義的解析

• • • 表名、字段名、別名錯誤的識別

?

2.3.3、優化器

對語句進行優化，優化查詢效率

2.3.3.1、概念

解析樹的本質：一種可以被執行器執行的數據結構。

那么在解析環節生成的解析樹是否可以直接交給執行器執行了呢？或者說客戶端發送的一條SQL語句是不是一定是服務端執行的SQL語句呢？

事實上，一條SQL語句是有多種執行方式（路徑）的。MySQL服務層有一個基于成本（cost）的模型算法進行計算，找到代價最小的路徑，執行器最終執行的路徑便是進過優化器篩選后的路徑。

?

2.3.3.2、查看執行成本： SHOW STATUS LIKE 'last_query_cost';

?

2.3.3.3、優化器可以做什么事情

舉例：

• • 當a join b，在數據庫的底層沒有辦法同時讀取幾張表，一定是先訪問一張表拿到一些數據，再基于這些數據訪問另一張表達到過濾數據的目的。所以關聯查詢時，必然要決定先查詢哪張表，這張表就是基準表。基準表的決定需要優化器判斷。
  • 條件查詢where a = ? and b < ? and c > ?，每一個字段都有索引的時候，既有單列的索引，又有復合索引，它需要決定要不要使用索引，使用哪幾個索引。
  • 當出現1 = 1恒等式，或者((()))多余的括號，優化器可以自動去掉。

?

但優化器的優化作用是有限的，不能全部依賴優化器。

經過優化器的選擇，最終會生成一條執行計劃。

?

2.3.4、執行計劃（優化器的執行結果，或者叫數據結構）

執行計劃的工具，查看執行路徑

EXPLAIN SELECT * FROM people;

EXPLAIN FORMAT = JSON SELECT * FROM people;

OPTIMIZE trace; -- 執行計劃的開關，信息量非常的大

?

舉例：sql語句最終選定的執行計劃

{"query_block": {"select_id": 1,"cost_info": {"query_cost": "0.65" -- 總開銷},"table": {"table_name": "people", -- 查哪張表"access_type": "ALL", -- 查詢的范圍"rows_examined_per_scan": 4, -- 預估要查多少行"rows_produced_per_join": 4, -- 實際查詢多少行"filtered": "100.00","cost_info": { -- 開銷明細"read_cost": "0.25","eval_cost": "0.40","prefix_cost": "0.65","data_read_per_join": "448"},"used_columns": ["id","name"]}} }

2.3.5、執行器

執行器按照查詢計劃調用存儲引擎，執行查詢

?

2.3.6、存儲引擎

2.3.6.1、概念

我們表面上看到的是數據放在了一個二維表結構里（類比excel），但我們用用navicat等工具查詢對象信息時看到存儲的表類型是InnoDB，表類型就是存儲引擎。并且存儲引擎的使用單位是表，而不是以數據庫為單位，這意味著表的存儲引擎還可以更改。

2.3.6.2、sql查數據庫中所有的表都使用了什么存儲引擎，SHOW TABLE STATUS FROM hc_goods;

（注：沒有指定存儲引擎的表都會默認使用InnoDB。）

2.3.6.3、表類型是存儲引擎，存儲引擎是一種存儲方式，那么在服務器上是怎么體現的呢？

查看數據庫在服務器上位置： SHOW VARIABLES LIKE 'datadir';

來到服務器，可以看到表在服務上是一個文件，且存儲引擎，其文件類型（后綴）不同，此處僅體現了InnoDB的idb文件

?

2.3.6.4、數據庫支持哪些存儲引擎， SHOW ENGINES;

2.3.7、總結

MySQL分為服務層和存儲引擎層。客戶端以各種方式建立連接，在服務層又分為解析環節、優化器、緩存，在存儲引擎層有各種可用的存儲引擎，最終數據在硬件的文件系統。

?

2.4、存儲引擎

InnoDB最初是第三方公司針對MySQL開發的，其自身的存儲引擎是ISAM。

2.4.1、官方網站列舉的存儲引擎

?

2.4.2、MySQL 8.0支持的存儲引擎

• InnoDB：MySQL 8.0中的默認存儲引擎。 InnoDB是用于MySQL的事務安全（兼容ACID）的存儲引擎，具有提交，回滾和崩潰恢復功能來保護用戶數據。 InnoDB行級鎖（行鎖是更小粒度的鎖，無需升級為更粗粒度的鎖）和Oracle風格一致支持讀寫的并發的操作，可提高并發性和性能。InnoDB將用戶數據存儲在聚集索引中，以減少基于主鍵查詢的I / O。為了保持數據完整性， InnoDB還支持FOREIGN KEY完整性約束。有關更多信息InnoDB，請參見 第15章，InnoDB存儲引擎。
• MyISAM：這些表占用的空間很小。 表級鎖定 限制了讀/寫運行負載中的性能，因此表鎖通常用于Web和數據倉庫配置中的只讀或主要工作負載中。
• Memory：將所有數據存儲在RAM中，以便在需要快速查找非關鍵數據的環境中進行快速訪問。該引擎以前稱為HEAP引擎。它的使用場景正在減少；InnoDB使用其緩沖池內存區域，提供了一種通用且持久的方式來將大多數或所有數據保留在內存中，并 NDBCLUSTER為大型分布式數據集提供了快速的鍵值查找。
• CSV：其表實際上是帶有逗號分隔值的文本文件。CSV表使您可以導入或轉儲CSV格式的數據，以便與讀取和寫入相同格式的腳本和應用程序交換數據。由于CSV表未建立索引，因此通常InnoDB在正常操作期間將數據保留在表中，并且僅在導入或導出階段使用CSV表。
• Archive：存檔的，沒有索引的表旨在用于存儲和檢索大量很少使用的歷史數據，歸檔或安全審核信息。
• Blackhole：Blackhole存儲引擎可以接受但不存儲數據，類似于Unix/dev/null設備。查詢總是返回一個空集。這些表可用于將DML語句發送到副本服務器的復制配置中，但源服務器不保留其自己的數據副本。
• NDB（也稱為 NDBCLUSTER）：此集群數據庫引擎特別適合于需要盡可能高的正常運行時間和可用性的應用程序。
• Merge：使MySQL DBA或開發人員能夠在邏輯上對一系列相同的MyISAM表進行分組并將它們作為一個對象引用。適用于VLDB環境，例如數據倉庫。
• Federated：提供了鏈接單獨的MySQL服務器以從許多物理服務器創建一個邏輯數據庫的能力。非常適合于分布式或數據集市環境。
• Example：此引擎作為MySQL源代碼中的示例，說明了如何開始編寫新的存儲引擎。它主要是開發人員感興趣的。存儲引擎是什么都不做的 “存根”。您可以使用此引擎創建表，但是不能在其中存儲數據或從中檢索數據。

?

2.4.3、為什么要設計這么多存儲引擎？

如上節所述，因為有不同的業務場景，對數據的管理和需求是不一樣的。所以誕生了各種各樣的存儲引擎。

如果上述存儲引擎沒有一個能滿足需求，怎么辦？MySQL提供自定義存儲引擎的接口。可以自定義一套存儲引擎。

?

2.5、一條查詢語句是如何執行的？

2.5.1、執行過程

服務層：

1、client的查詢請求來查詢緩存，如果緩存有，則直接返回。

2、解析器進行詞法、語法解析。

3、預處理器判定權限、表、字段、別名是否有錯。

4、優化器，選擇開銷最小的路徑

5、執行計劃，選擇后的執行路徑

6、執行器執行執行計劃調用存儲引擎，做查詢

存儲引擎層：

1、引擎讀取磁盤的數據文件，將數據交給執行器。執行器把數據寫入緩存，并且返回給客戶端（如果有緩存放到緩存，如果沒有緩存直接返回給客戶端）。 ? ?

2.6、一條更新語句是如何執行？

Mybatis源碼的執行器Executor只有doQuery和doUpdate。MySQL也是一樣的，update就包含了增刪改。與查詢不同的是，當從存儲引擎拿到數據之后，將數據加載到內存（因為數據在磁盤里，只有將數據加載到內存中才能修改操作）

?

問題：

是不是需要操作多大的數據，就要一次性從磁盤加載多少數據到內存？比如需要操作5kb的數據，就一定要加載5kb的數據到內存？

我們知道磁盤的IO讀寫相對內存是非常慢，在加上這些數據可能在磁盤上分布在不同的位置，意味著很多次尋址、很多次IO操作，這將非常的耗時。

?

2.6.1、page

預讀取（為解決上述問題），當訪問到磁盤的一塊位置，就認為周圍的數據也會被讀取，所以會讀取一個固定大小的范圍（意味著，可能要比想讀取的數據大一些），單位page（頁，邏輯單位），在InnoDB默認是16kb（注意區別，與操作系統中從磁盤加載到內存的大小4kb是兩碼事），如果想修改，只能修改源碼，重新編譯部署。

?

每一次對于頁的操作也需要從磁盤上加載到內存中，會不會也有IO很慢的問題，那么能不能緩存起來呢，下一次就不用從磁盤中讀了？當然可以，InnoDB里有內存的緩存池（buffer pool），從磁盤加載數據到內存中時，會將數據記錄在buffer pool中，下次再讀時可以直接從buffer pool拿到數據，提升數據的讀寫效率。如果需要修改（先讀再寫）的數據以及在內存的buffer pool中存在了，會先修改內存中的數據page。如果內存中的page與磁盤的page不一樣，則內存中的page叫dirty page（臟頁，產生本質是刷臟時間差和事務沒有提交）。一旦后臺線程開始空閑，就將臟頁數據刷到磁盤文件中，又叫刷臟。

?

2.6.2、redo log

因為刷臟不是實時的，如果buffer pool的內容還沒有刷到磁盤，這個時候數據庫重啟了或者宕機了，那么怎么保證緩沖池中數據頁的安全性？沒有好的辦法，必須要考慮持久化，InnoDB內存數據頁持久化的機制是redo log（重做log），當數據加載到內存中時，也會記錄在redo log中。當數據庫宕機重啟后，會依據redo log里的臟頁恢復到磁盤文件中，在InnoDB中叫做crash safe（崩潰恢復），并且事務中ACID中的D（持久性，durability）就是靠redo log實現的。

?

redo log為了提升日志的讀寫效率，也分為兩塊，內存和磁盤中各有一塊。

2.6.2.1、redo log在磁盤上體現為什么樣的文件，在安裝目錄下/var/lib/mysql

查詢redo log SHOW VARIABLES LIKE '%innodb_log%'; 當然，這些都是可修改的。

為什么客戶的更新現在內存緩沖池和redo log寫一遍，再寫到磁盤數據文件。為什么不直接寫到磁盤？

記錄redo log文件和磁盤數據文件有區別，在于既可以保證數據的安全性，還可以減少磁盤刷盤尋址的時間以降低開銷，提升了吞吐量。

順序IO，寫入redo log是追加的過程，不需要尋址

隨機IO，每一次都有尋址的時間

?

redo log的特點：（注意redo log的存在不是為了向數據文件寫入數據，只是作為崩潰恢復時使用）

1、redo log是InnoDB獨有的

2、redo log是物理日志，記錄的是數據頁里面做的修改，是絕對的

3、redo log的大小是固定的，不斷覆蓋，寫入方式如下

2.6.2、undo log（撤銷日志、回滾日志。redo log是物理日志，undo log是邏輯日志）

記錄的是事務（增刪改）發生之前數據狀態，如果在增刪改過程中發生了異常，則會基于undo log回滾事務，保證了原子性（全部成功，，全部失敗）

undo log相關的參數： SHOW VARIABLES LIKE '%undo%';?

?

2.6.3、一條更新語句是如何執行的

redo log + undo log配合使用

1、存儲引擎層負責從RAM或DISK拿到數據，返回給服務Server層

2、修改這一行數據

3、記錄undo log、redo log

4、調用存儲引擎的接口，在內存buffer pool中修改數據page

5、commit

?

redo log兩階段的記錄prepare、commit（跟XA的兩階段提交沒有任何關系）

?

2.7、InnoDB架構

?

?

?

2.7.1、MySQL sever層也有一個日志文件binlog（邏輯日志---記錄的是相對的操作）

• 記錄的是DDL，DML語句，在哪個數據頁的哪條數據做個什么樣的改動。
• 沒有固定大小限制，內容可追加（binlog用于數據恢復，而redo log只能做崩潰恢復，就是因為，內容不可追加，之前的數據會被覆蓋）
• Server層實現，可以被所有存儲引擎使用
• 用于數據恢復和主從復制

作用：

1、主從同步

2、實現數據的恢復。既然記錄的是增刪改語句的邏輯日志，從最晚的一次全量備份的時間，解析binlog，把drop的語句剔除掉后，將其他的DDL、DML重新執行一遍

?

?

redolog是prepare，binlog無記錄。回滾，因為數據的事務是以binlog為準的。

redolog是prepare，binlog有記錄。而數據本身的事務沒有commit，則會利用binlog的記錄恢復事務，將事務提交。

?

2.7.2、主從同步的原理（擴展）

1、從節點的I/O Thread請求讀取主節點的binlog

2、主節點的Log Dump Thread把數據發給從節點

3、從節點解析后寫入中繼日志

4、SQL Thread讀取中繼日志的數據

5、將解析后的SQL在從節點的DB中重放（重新執行）一遍

?

?

?

?

3、MySQL索引原理與使用原則

3.1、數據庫索引的本質

數據庫索引，是數據庫管理系統（DBMS）中一個排序的數據結構，以協助快熟查詢、更新數據庫表中數據。

沒有數據庫時，要查找一條數據，需要進行全表的掃描，類似從一本沒有目錄的書里找到指定的章節。而索引就相當于給書加了一份目錄。

3.1.1、索引類型

normal，沒有任何限制

unique，要求字段的值不重復

主鍵索引，是unique的一種特例，在unique的基礎上要求不能為空

Full text，全文索引。

如果我們在表設計時用text存放大文本，日后需要用關鍵字模糊查詢匹配的記錄，如何解決？ ? ?

• • like '%關鍵字%'
  • 建立全文索引，語法：select * from fulltext_test where match(列名) against ('關鍵字' )

• • • MATCH (col1, col2,...) AGAINST (expr [search_modifier]) ?-- where條件后使用
    • 但這不是合適的辦法，用ES更優

?

3.1.2、索引存儲結構

InnoDB顯示支持的索引結構只有B+Tree（雖然有hash類型的索引結構，但只是在InnoDB buffer pool隱式的使用，由于hash key-vlaue的本質和hash沖突的先天短板，因此不能做范圍查詢和大批量數據的命中，其作用僅限為小批量數據的快速查找——O(1)）。

?

3.1.2.1、各數據結構如果作為索引選型的區別

舊金山大學數據結構可視化網站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

?

分析一下，其他數據結構為什么不合適。

• 有序數組

沒錯有序數組檢索很快，但是索引的增刪改

• 鏈表

鏈表增刪改很快，但檢索復雜

• BST——Binary Search Tree，二叉查找樹

沒錯BST的確可以快速查找和快速變更。但為什么不選擇BST呢？假如，按照大小順序，有序的構建二叉查找樹數，我們會發現所謂的BST變成了一個單鏈表，以這種特殊場景為例，極端情況下，BST檢索的復雜度是O(n)。所以BST不適合做索引。

• AVL Tree——Balance Binary Search Tree（平衡二叉查找樹。發明人名字的簡寫是AVL，所以又叫AVL Tree）

AVL的特點是：左右子樹的深度差的絕對值不能超過1。所以AVL避免了BST變成單鏈表的問題。

看來似乎已經能滿足索引的基本要求了，那么我們假定使用AVL做索引來分析可能會出現什么問題。

設定每個節點(node)，包含三塊數據：鍵值、數據的物理地址、指向左右子節點的指針，構建索引的數據如下圖：

問題：

• • 索引的內容本質上是通過存儲引擎放到磁盤上，比對時要把各節點（Node=Page 16KB）的內容拿到server層比較，也就是說需要比對多少節點就要發生多少次IO。那么我們來看AVL，一個數據就是一個Node，如果對大量數據做索引，會導致AVL的深度很高，這也意味著要比對的節點很多，因此IO的次數也多，導致浪費性能。
  • MySQL的數據讀取單位是page，一個page是16KB，顯然在當前Node的設定（鍵值、物理地址、左右子樹指針）是遠遠達不到16KB，所以會很浪費空間，且導致樹的深度過高。

結論：

AVL不是索引數據結構的最好選擇。

?

• B Tree——Balanced Tree（B 樹，多路平衡查找樹）

如果page多放點節點的內容（放多個鍵值；放更多的指針。或者說把AVL變成分更多岔的樹），是不是就解決了AVL的2個問題：1、page浪費；2、深度過高。

B Tree的特點：度=關鍵字數+1；執行分裂、合并保證B樹的平衡（索引中也是Page的分裂和合并）

舉例：

比如有關鍵字a、b，且a<b，則子樹指針指向關鍵字的區間為(-∞,a),(a,b),(b,+∞)

B Tree比AVL更適合做索引的數據結構，那么我們假定使用B Tree做索引來分析可能會出現什么問題。

設定每個節點(node)，包含三塊數據：鍵值、數據的物理地址、指向各子節點的指針，構建索引的數據如下圖：

還不夠完美，問題：

• • 范圍查找時，需要反復遍歷樹，效率低下。
  • 每次檢索需要比較的Node次數不同（IO次數不同），效率不穩定。【注，假如我們知道每次查詢都會比較的次數為N，且某一次查詢比較的次數是n，如果n=N，可以確定的是一定成功】

?

?

• B+ Tree——B Plus Tree（加強版多路平衡查找樹）

B+ Tree的特點：度=關鍵字數；（B樹是：度=關鍵字數+1）。

舉例：

比如有關鍵字a、b、c，且a<b<c，則子樹指針指向關鍵字的區間為[a,b),[b,c),[c,+∞)

?

跟B樹不同的地方：B樹的度=關鍵字數+1，B+樹的度=關鍵字數，因此同樣數量的數據構建出的索引結構，相比B+樹的深度更低，深度低意味著更少的比較次數，等于進一步降低IO的次數。

B+樹在索引數據結構的使用中，只有葉子節點才放物理地址（永遠在葉子節點，這樣每次IO的次數是固定的，且是可預知的）。并且葉子節點的page和page之間有雙向指針（讓葉子節點形成了有序的鏈表結構），便于范圍查詢（排序）。如果沒有這個雙向指針，將會多次遍歷才能找到一個范圍。

?

3.1.2.2、為什么選擇B+Tree

• B Tree能解決的問題，B+Tree都能解決

B Tree解決了什么問題：AVL（平衡二叉查找樹）對page容量的浪費、深度過高。減少了比對次數（比對時，需要存儲引擎層將數據page加載到內存，然后由MySQL服務層執行比對），同樣減少了IO操作。增加了性能。

• 掃庫、掃表能力更強

葉子節點page頁的雙向鏈表，在范圍查找或全量查詢時，不用對數做多余的遍歷。

• 磁盤讀寫能力更強
• 排序能力更強
• 效率更加穩定

• • 每次的比較次數都是可預期
  • 由于物理地址都在葉子節點，不會出現AVL、B Tree時，有的在深度為1時完成查詢，有的在深度為n時結束查詢的問題。所以效率穩定。

?

3.1.3、特殊的索引方法——Hash

3.1.3.1、hash索引簡介

特點：

k-v形式（如下圖），時間復雜度=O(1)，也因此我們在表中看起來順序排列的數據，經過散列后分布在磁盤上是亂序的，所以不支持排序和范圍查詢。只能做k=？的查詢

hash天生的缺陷：

hash沖突，需要用額外的方式解決，但也增加了復雜度，如再hash法、鏈地址法。

?

3.1.3.2、hash索引在InnoDB中的使用

在InnoDB中是不能使用hash建立索引的（存儲引擎選用MEMORY時可以支持hash索引），親手驗證一下：

• 保存前，注意索引方法是HASH

• 保存后，索引方法會自動變為BTREE

但InnoDB的確隱式的使用了hash做索引，在哪里用的呢？回到InnoDB的架構圖（紅框數據緩沖池），為提升內存緩存池的熱點數據頁的訪問速度，InnoDB自動建立的hash索引，是無法人為干涉的。

3.1.4、補充

為什么索引不考慮紅黑樹？

這么多定義約束，目的是：最長的路徑不超過最短路徑的2。

如果選為數據庫索引的數據結構，檢索的次數是不固定的，同理導致IO次數不固定，因此也可以說效率不穩定。

?

3.2、索引在不同存儲引擎中的實現

3.2.1、不同存儲引擎的存儲文件

3.2.1.1、MyISAM

主鍵索引

?

輔助索引

主鍵索引輔助索引都在索引的文件

?

3.2.1.2、InnoDB，索引即數據

聚簇索引：索引鍵值邏輯順序與表數據行的物理順序一致。因此，主鍵索引一定是聚簇索引，其他的索引都叫二級索引英文字典，第一個字母是第一個索引

一張表沒有主鍵

?

3.2.1.3、聚集索引和二級索引

每個InnoDB表都有一個特殊的索引，稱為聚簇索引 ，用于存儲行數據。通常，聚簇索引與主鍵同義 。為了從查詢，插入和其他數據庫操作中獲得最佳性能，您必須了解InnoDB如何使用聚簇索引為每個表優化最常見的查找和DML操作。

• 在表上定義一個PRIMARY KEY時，InnoDB將其用作聚簇索引。你要為創建的每個表定義一個主鍵。如果沒有邏輯唯一且非空的列或列集，請添加一個新的 自動遞增 列，其值設為自動填充。
• 如果您沒有為表定義PRIMARY KEY，當所有列都是NOT NULL 時，MySQL會在所有鍵列所在的位置找到第一個UNIQUE的索引，并將其用作 InnoDB的聚集索引。
• 如果表即沒有索引PRIMARY KEY也沒有合適的 UNIQUE索引，則InnoDB 在內部生成一個隱藏的聚集索引GEN_CLUST_INDEX，該索引是包含行ID值的合成列 。這些行按照InnoDB分配給該表中各行的ID排序 。row_id是一個6字節的字段，隨著插入新行而單調增加。因此，按行ID排序的行實際上是按物理順序排序。如下圖所示（如果有主鍵，則以主鍵作為rowId，下圖便是這種情況）

InnoDB的表里是不可能沒有索引的（索引即數據，數據即索引）。

聚集索引如何加快查詢

通過聚集索引可以快速訪問行，因為檢索索引可以直接到達該行的page數據頁。如果表很大， 當與索引記錄中使用不同page頁保存數據的其他存儲結構比較時，聚集索引體系結構通常可以節省磁盤I / O操作 。

二級索引如何與聚簇索引建立聯系

除聚集索引之外的所有索引都稱為 輔助索引。在InnoDB中，輔助索引中的每個記錄都包含該行的主鍵列，以及為輔助索引指定的列。 InnoDB使用此主鍵在聚集索引中搜索行。

如果主鍵很長，則輔助索引將使用更多空間，因此設置較短的主鍵是有利的。

只有在主鍵索引的葉子節點才上存儲完整的數據，輔助索引的葉子節點存的是主鍵的值。

要使用輔助索引查找數據，要先在輔助索引的B+Tree檢索一遍得到主鍵索引的值，然后再主鍵索引的B+Tree再檢索一遍才能得到最終的數據。所以，輔助索引的效率要比主鍵索引的低。

InnoDB只有一個文件（xxx.ibd），所以主鍵索引的B+Tree、其他所有輔助索引的B+Tree都在這個文件上。

?

問題：輔助索引的葉子節點，為什么放的是主鍵索引的值，而不是數據的物理地址？

原因：上文索引的數據結構有說過，B+Tree和B Tree一樣是依靠page的分裂、合并保證樹的平衡，Page頁的變化，即等于物理地址的變化，顯然不可行。

?

3.3、創建和使用索引的原則

選擇合適字段：頻繁被where、order等做為條件的字段。

3.3.1、列的離散度

離散度公式：count(distinct(column_name)) : count(*)，即某一列不重復的值除總行數，簡而言之，選擇值重復低的列。

如下圖所示，如果索引值的重復率太高（離散度低），再檢索索引時，與根節點比較后，因為子節點都一樣，無法抉擇下一輪是與哪個子節點比較，這樣的代價非常大。極端情況下，還不如把整個表都比較一遍，此時存儲引擎甚至會放棄使用索引。

?

3.3.2、聯合索引

3.3.2.1、概念

創建name+phone聯合索引：ALTER TABLE people ADD INDEX `comidx_name_phone` (`name`, `phone`);

構建所得索引的數據結構如下

?

順序建立包含a、b、c三個列的聯合索引，功能上相當與建立了a、ab、abc三個索引。因此，使用聯合索引要求最左匹配原則，參考下圖。如果不是按照創建字段的順序使用，則達不到使用聯合索引的目的。

?

3.3.2.2、問題：如下sql，哪些能用到索引：

• EXPLAIN SELECT * FROM people WHERE `name` = '張三' AND phone = '100'; ? 顯然可以，查詢條件完全符合聯合索引的字段，且滿足最左匹配原則。

• EXPLAIN SELECT * FROM people WHERE phone = '100' AND `name` = '張三'; ?可以。雖然查詢條件符合聯合索引的字段，但不滿足最左匹配原則。但是優化器會自動調整條件的順序，達到使用聯合索引的目的。

• EXPLAIN SELECT * FROM people WHERE `name` = '張三'; ?可以，聯合索引的最左匹配原則，相當于建立了一個name的索引。

• EXPLAIN SELECT * FROM people WHERE phone = '100'; ? 不滿足最左匹配原則。

3.3.2.3、假如有如下場景，應該如何創建索引

第2個就夠了，由于聯合索引的最左匹配原則，相當于額外獲得了name的索引，因此沒必要創建多余的name索引

?

3.3.3、覆蓋索引

3.3.3.1、什么是回表？

上文提過，在使用輔助索引時：

第一步、查輔助索引得到主鍵索引的值。

第二步、利用主鍵索引的值去查主鍵索引得到數據。

其中第二步便是“回表”。

3.3.3.2、什么是覆蓋索引？

要查詢的數據列，已經包含在所用到的索引里。

比如，創建了name的索引，然后select name from people where name = ?，這個查詢操作在輔助索引的Tree上就能實現，不需要回表，這就叫覆蓋索引。

舉例，已創建了name+phone的聯合索引，下列查詢哪些是覆蓋索引？

• EXPLAIN SELECT NAME FROM people WHERE `name` = '張三'; ?

-- 可以，輔助索引的Tree能滿足查詢

• EXPLAIN SELECT NAME FROM people WHERE `name` = '張三' AND phone = '100';

-- 可以

• EXPLAIN SELECT NAME FROM people WHERE phone = '100';

-- 可以，優化器的操作，的確不符合最左匹配原則，但如果不用聯合索引，就要從主鍵索引里查，既然兩個索引的樹都能查到name，從概率上講，聯合索引的tree的開銷更小

• EXPLAIN SELECT * FROM people WHERE `name` = '張三';

-- 不可以，雖然聯合索引上存在name=張三，但沒有其他數據，還是要回到主鍵索引的樹去查

?

3.3.4、sql索引使用常識

• 表的索引越全越好，因為不管什么情況都能用到索引，對嗎？

不對

1、占用磁盤。索引也是數據結構，需要使用存儲空間保存。

2、更新數據變慢，會發生大量的索引結構的調整。

• 為什么不要在性別字段上建索引？

離散度低，做索引時開銷大

• 模糊查詢like abc%，like %abc%，like %abc都用不到索引？

• • like abc%，可以，索引的最左前綴。
  • like %abc%，like %abc，不一定，當索引條件下推時會使用（ICP--Index Condition Push Down）。

我們知道獲取數據是在存儲引擎層，過濾、排序、比較等邏輯操作在server層。

當用不到索引的條件（比如like條件）先在存儲引擎層過濾時開銷更小時，會把這條件先在存儲引擎過濾，叫做索引條件下推。

舉例：

• 不要使用select *，寫明具體的查詢字段。

• • 用不到覆蓋索引

• 負向的查詢!=、<>、NOT IN、NOT LIKE能否用到索引？

• • NOT LIKE，不能

• • !=、<>、NOT IN，可以

用不用索引，是由基于成本的優化器（Cost Based Optimizer）決定的。

?

3.3.5、長字段如何建索引

長字段建立索引，1、page頁的容量更小，大量數據時比較的次數多，IO多。2、索引占用大量磁盤空間。

• 可以截取定長字段建立索引
• 也可對該字段做hash運算，再用hash字段做索引

?

3.3.6、為嘛推薦使用遞增的id作為主鍵索引？雪花算法？

InnoDB使用的是聚集索引，數據記錄在主鍵索引的葉子節點上，且葉子節點是彼此鏈接且順序排列。如果使用遞增主鍵，對于主鍵索引的B+Tree，每新增一個主鍵，對于B+Tree的構建，其page頁的分裂與合并對于包含數據的子節點，只需在最后一個page后續上當前需要插入的頁，在物理結構上是相對連續的，有益于磁盤的使用效率。如果是非自增的隨機主鍵，主鍵索引葉子節點page頁和非葉子節點的page的分裂與合并也是隨機的，造成更大的IO開銷。

1、主鍵自增時，會按照順序寫滿每一個頁。而主鍵隨機時，必然會發生插入已經寫滿的page頁，將導致大量的page的分裂合并的開銷

2、為什么是主鍵索引

3、相對其它互斥的主鍵索引占用的空間小

4、MySQL事務與鎖詳解

• 問題1：事務的特性原子性，隔離性，持久性是通過什么技術實現的？

原子性，借助undo log，實現回滾，達到全部失敗的目的。沒有異常就全部成功。

隔離性，利用事務的隔離機制，解決數據并發讀取的問題。為了保證數據讀取的一致性，使用LBCC和MVCC（僅有RR和RC使用）技術對數據庫加鎖。

持久性，數據庫崩潰時利用redo log和雙寫緩沖，保證事務能夠恢復，

• 問題2：三種事務并發帶來的問題，與四種事務隔離級別的關系。

三種問題分別是臟讀、不可重復讀、幻讀。

四種隔離級別是未提交讀、已提交讀、可重復度、序列化。

RU：事務未提交的數據對其他事務是可見的，未解決任何并發問題。

RC：事務開始后能看見其他事務已提交的的修改，解決了臟讀，但會出現不可重復讀。

RR：在同一事務中多次讀取同樣的數據結果是一樣的，解決了臟讀和不可重復讀。在InnoDB中由于RR的MVCC技術中使用了間隙鎖，該鎖本身就對插入阻塞，所以不存在幻讀的情況。

Serializable：事務中最高的隔離級別，強制事務串行執行。雖然有效抑制了并發問題的發生，但極大影響并發度。所以，一般不使用。

4.1、什么是數據庫事務

數據庫事務的場景，增刪改，@Transaction、xml、AOP

維基百科的定義：事務是數據庫管理系統（DBMS）執行過程中的一個邏輯單位（前文說過，page也是邏輯單位，是不可拆分的），由一個有限的數據庫操作序列構成。

兩個關鍵點：

• 它是數據庫最小的工作單元，是不可以再分的（所有的 CUD 操作都是事務，即使單條 SQL）
• 它可能包含了一個或者一系列的DML語句，包括 insert delete update。（單條DDL（create drop）和 DCL（grant revoke）也會有事務）

?

哪些存儲引擎支持事務，InnoDB,NDB(一個集群的存儲引擎)，也是5.5之后放棄MyISAM的誘因

4.2、數據庫事務的四大特性

4.2.1、事務的四大特性：

• 原子性，Atomicity 最小的邏輯單位，事務的多個DML作為一個整體，不可分隔執行。對數據庫的一系列的DML操作，要么都成功，要么都失敗。

實現原理，在InnoDB中依賴 undo log（記錄的是事務（增刪改）發生之前數據狀態）回滾，將數據恢復到原來的版本。

?

• 一致性 Consistency，指的是數據完整性的約束不會被破壞，事務執行的前后都是合法的數據狀態。

完整性約束包含：數據庫自身的完整性約束（比如主鍵必須是唯一的，字段長度符合要求），用戶自定義的完整性約束（通常在代碼中控制）。（賬戶A-100,B+50，轉賬A0-50，B0+50）

?

• 隔離性 Isolation，各事務是個邏輯單位，包含多個dml，事務之間，的dml語句并發執行，互相不干擾。在數據庫里面會有很多的事務同時去操作同一張表或者同一行數據，必然會產生一些并發或者干擾的操作，那么我們對隔離性的定義，就是這些很多個的事務，對表或者行的并發操作，應該是透明的，互相不干擾的。

?

• 持久性 durability，事務提交成功，寫入到磁盤后狀態應該是永久的。即使數據庫中途崩潰（崩潰恢復redo log）、重啟后恢復到之前的狀態

雙寫緩沖，重要特性。頁的備份

操作系統的page是4k，數據庫的page是16k。當數據從內存向磁盤寫時，需要寫4個操作系統的page才能寫完。假設寫完第一個page時數據庫崩潰，那么16k的數據庫頁也僅被寫入了4k，如果崩潰，再恢復由于page已經不完整，所以不能再寫入。所以要把數據庫頁做個備份，叫雙寫緩沖。

?

原子性、隔離性、持久性都是為了實現一致性。

?

4.2.2、數據庫如何開啟一個事務

SELECT VERSION(); -- 查看數據庫版本SHOW VARIABLES LIKE '%ENGINE%'; -- 查看存儲引擎SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; -- 查看事務隔離級別

?

如何開啟事務？

• 單條增刪改，自動開啟，自動提交。如果結果不提交，是個在內存里臟數據。

SHOW VARIABLES LIKE 'autocommit'; ?-- 查看是否開啟自動啟用事務?

• 手動開啟 ?

• • BEGIN;
  • START TRANSACTION;

-- BEGIN; -- 開啟事務 START TRANSACTION; -- 開啟事務，這兩種都可以用，但start命令可用更多的參數 UPDATE people SET `name` = '張三123' WHERE id = 1; ROLLBACK; -- 回滾 -- COMMIT; -- 提交

?

如果事務執行一半，會話斷開，事務回滾。

事務結束的時候，事務所持有的鎖就會釋放。

?

?

4.2.3、事務并發問題

只要涉及增刪改都會有事務，時時刻刻會有很多事務在執行。事務并發操作同一行數據，就會導致并發問題，隔離性怎么實現？（不要跟事務隔離級別混淆）

事務并發問題：

• 臟讀，讀到了另一事物未提交的結果，重點是未提交被讀到。

事務A有兩次讀操作，先讀到age=16，事務B執行更新（事務尚未結束，數據還在內存中），事務A再次讀取到age=18。即事務A讀到事務B尚未提交的數據。

P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-transaction T2 then reads that row before T1 performs a COMMIT.

If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.

• 不可重復度，在同一時間內，一樣的查詢語句先后讀到是另一事物未提交和提交后的結果。重點是已提交導致兩次讀結果不一致。

事務A有兩次讀操作，先讀到age=16，事務B執行更新并且事務提交，事務A再次讀取到age=18。即事務A讀到事務B提交后的數據。

P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL- transaction T2 then modifies or deletes that row and performs a COMMIT.

If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.

• 幻讀，范圍查詢時，在其他事務插入的數據的前后，讀的結果不同。

事務A先讀到1條數據，事務B執行插入并且事務提交，事務A再次讀取到2條數據。即事務A先后兩次讀到結果數不同。

P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some . SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the ?used by SQL-transaction T1.

If SQL-transaction T1 then repeats the initial read with the same, it obtains a different collection of rows.

怎么區分臟讀、不可重復讀、幻讀：

臟讀：讀到另一事物未提交的數據

不可重復讀：在另一事物做delete、update且提交時，先后讀到的數據不一樣，影響的是精確查詢的結果。

幻讀：在另一事物做insert且提交時，先后讀到的數量不一樣，影響的是范圍查詢的數量。

?

4.2.4、事務隔離級別

事務并發的三大問題其實都是讀一致性問題（明明是相同的查詢操作，但是讀到了不同的結果）。作為服務提供方，必須由數據庫提供一定得事務隔離機制來解決，保證數據一致性的問題。所以，有數據庫的專家達成共識聯合制定了一個標準，建議數據庫廠商一定程度上實現數據一致性的問題。

SQL92 ANSI/ISO標準：http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

__Table_9-SQL-transaction_isolation_levels_and_the_three_phenomena_

?

_Level__________________P1______P2_______P3________________________| READ UNCOMMITTED | Possib|e Possib|e Possible || | | | || READ COMMITTED | Not | Possibl| Possible |Possible| REPEATABLE READ | Not | Not | Possible || | Possib|e Possib|e || | | | || SERIALIZABLE | Not | Not | Not Possible ||______________________|_Possib|e_Possib|e_________________________|| | | | |

串行化，所有的事務都要排隊執行，所以就不會再有事務隔離性可能出現的3種現象。缺點，極大降低數據庫并發度。

?

在InnoDB默認的數據庫事務隔離級別RR中，已經解決了幻讀的問題，所以不需要用Serializable這么高的隔離級別，而且會帶來較高的并發度。

怎么解決幻讀的？使用了間隙鎖，阻塞插入，所以避免幻讀。

?

如果要解決讀一致性問題，保證一個事務中前后兩次讀取的數據結果一致，實現事務隔離，應該怎么做？

方案：

• 在讀取數據前對其加鎖，阻止其他事務對數據進行修改（LBCC--Lock Based Concurrency Control，基于并發控制的鎖）。問題：為了讀而阻塞了寫，導致并發度降低。適合需要加鎖的讀（當前讀），比如要求讀取最新的數據。
• 快照，生成一個數據請求時間點的一致性數據快照（SnapShot），并用這個快照來提供一定級別（語句級或事務級）的一致性讀取（MVCC--Multi Version Concurrency Control，多版本的并發控制）

MVCC的思想，只能查找創建時間小于等于當前事務ID的（已提交的）數據，和刪除時間大于都去哪干事務ID的行（或未刪除）。

MVCC用在已提交讀RC和可重復讀RR，為什么在已提交讀RC還會出現不可重復讀幻讀，：

RR的快照建立的時間是第一次查詢的時候，所以未提交的事務和其后的事務都是不能看見的，

RC的快照建立的時間是當前select，所以能看見其他事務已提交的數據。具體的機制是兩種隔離級別下的ReadView的差異。

?

簡單單一的select是快照讀

存在加鎖的for update或DML的事務中select是當前讀

4.2、MySQL中InnoDB鎖的基本類型

官網InnoDB鎖的介紹：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

4.2.1、行鎖與表鎖

4.2.1.1、行鎖

• 共享鎖，又稱讀鎖，簡稱S鎖

指多個事務對于同一數據可以共享一把鎖，都能訪問到數據，但只能讀不能改（如果修改，可能會發生死鎖）。

加鎖方式： SELECT * FROM people WHERE id = 1 LOCK IN SHARE MODE;?

釋放鎖方式：事務結束

驗證：分別在兩個會話里對同一行數據加鎖，都能加鎖成功

• 排他鎖，又稱寫鎖，簡稱X鎖

排他鎖不能與其他鎖并存，如果一個事務獲取了一個數據行的排他鎖，其他事務就不能再獲取該行的鎖（共享鎖，排他鎖），只有獲取到排他鎖的事務能對數據行進行讀取和修改。

加鎖方式：

• • 自動：delete、update、insert默認加上X鎖
  • 手動：SELECT * FROM people WHERE id = 1 FOR UPDATE;

釋放鎖方式：事務結束

驗證：在會話1中，自動給改行數據加一個排他鎖，在會話2中分別嘗試加共享鎖和手動排他鎖都被阻塞，無法獲取鎖，需要手動停止。

• 官網里的其他行級別的鎖：意向共享鎖（IS）/意向排他鎖（IX）

意向鎖是數據庫存儲引擎自己維護的，用戶無法手動操作意向鎖。

• • 意向共享鎖（IS--Intention Shared Lock）：指事務準備給數據行加共享鎖，也就是說給數據行加共享鎖前，必須先獲得該表的IS鎖（表級別的意向的共享鎖）
  • 意向排他鎖（IX--Intention Exclusive Lock）：指事務準備給數據行加排他鎖，也就是說給數據行加排他鎖前，必須先獲得該表的IX鎖（表級別的意向的共享鎖）

干嘛要鎖定一張表呢？

事務要成功的給一張表加上表鎖，前提必須是：沒有其他任何事務已經鎖定了該表的任意一行數據。

如何確認數據沒有被加鎖？比如全表掃描，一行一行確認沒被加鎖，但是并發場景下不可避免一行數據在掃描中途被加鎖，顯然是不可行的。意向鎖就是解決這個問題的辦法，相當于表是否被加行鎖的標志，類比火車衛生間的燈，只要標識未被加鎖便可以后續操作，提升了加鎖的效率。

驗證：會話1給數據行加排他鎖， 存儲引擎會自動鎖表。會話2手動鎖表被阻塞。

4.3、InnoDB行鎖到底鎖住了什么

鎖的作用：解決資源競爭的問題。

鎖住了什么：一行數據？一個字段？

4.3.1、鎖的原理

• 沒有索引，兩個會話，select ... where name = 'a' for update和select ... where name = 'b' for update先后執行后，發現會話2失敗，證明是鎖表。
• 主鍵索引，兩個會話，select ... where id= 1 for update和select ... where id= 2 for update先后執行后，發現都可以查詢，證明是鎖行。
• 主鍵索引+唯一索引，兩個會話，select ... where id= 1 for update和select ... where name = 'b' for update先后執行后，發現都可以查詢，證明是鎖行。

鎖的是聚簇索引

1、沒有索引為什么會鎖表？

聚簇索引：主鍵索引、第一個唯一索引、rowId。當沒有索引時加排他鎖查詢，需要全表掃描，因此會鎖住所有的rowId。

2、通過唯一索引加鎖，為什么會阻塞主鍵索引的加鎖？

二級索引加鎖，先會把name字段鎖住，接著把id鎖住

4.4、行鎖的算法：鎖住了什么范圍

假設表里已有id=1、4、7、10的記錄。即索引記錄有1、4、7、10。由這四個存在的索引記錄隔開的區間，叫間隙。由間隙組成的左開右閉的區間叫臨鍵。

無論用的哪種查詢（范圍查詢或者等值查詢），只要沒有命中任何一個索引，就會鎖住兩個記錄之間的區間。查詢的相同間隙鎖（select ... for update）不排斥，因為本來就沒有數據，排斥也沒有愿意。 但是排斥插入

?

4.5、總結

• Read Uncommited，不加鎖。生產中不會用到。
• Serializable，所有的select語句都會被隱式的轉化為select ... in share mode，會和update、delete互斥（因為是排他鎖，排他鎖存在時不允許有任何鎖）。生產中不會用到。

?

?

?

?

5、MySQL性能優化總結

優化從何入手？

硬件的優化，操作系統的配置：基于硬件的實際情況調優。

操作系統、數據庫配置

架構層次，緩存，集群，讀寫分離，分庫分表

5.1、連接問題優化

客戶端與服務端簡歷連接，可能有什么問題？

服務端：連接數不夠，

回收連接時間太長，導致連接沒有及時釋放。（默認8個小時）

客戶端：怎么減少從服務端獲取的連接數？

不要每次執行一條sql語句時就去和服務端建立一個新的TCP連接。

連接池，用完之后將連接歸還到連接池（不是close），便于其他業務使用，這種方法在并發程度不高的情況下，可以大大減少頻繁創建和關閉連接的開銷。（比如ali的Druid，Spring Boot的Hikari）

緩存，將查詢到的數據，暫存在緩存中，減少對數據庫的讀。

集群，這種情況下要保證數據一致性的問題（重放bin log中的DML）。

基于集群，實現讀寫分離

分庫分表，數據量多大，數據庫扛不住并發壓力。

垂直分庫，基于業務角度劃分不同的庫

水平分庫，基于數據劃分，一張表數據量過大，將一張表水平拆分成多個表。

5.2、基于優化器

5.2.1、哪些SQL語句慢？

5.2.1.1、slow query log，慢查詢日志

（默認關閉，原因是在DML執行過程中要多記錄一個日志，會消耗性能）

結果中/var/lib/mysql/mine-slow.log就是慢查詢記錄的文件。

執行時間多久的sql會被定義為慢查詢語句記錄到日志中（默認10秒）

動態修改set語句，重啟就會失效，可以從服務器上修改配置文件/etc/my.cnf。

SHOW GLOBAL VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query%';SET long_query_time = 0; -- 臨時設置慢查詢的條件是0秒 SET GLOBAL slow_query_log = ON; -- 臨時開啟慢查詢日志SELECT * FROM people;

慢查詢日志里能看到具體的sql、執行時間、操作的用戶、IP、消耗時間、獲取鎖的時間、篩選了多少行、預計篩選多少行。

統計慢查詢結果，系統自帶了分析工具——mysqldumpslow

比如查詢用時最多的2條語句： mysqldumpslow -s t -t 2 -g 'select' mine-slow.log

?

5.2.1.2、PROFILE

谷歌工程師提供的工具，默認是不開啟的。

• SHOW PROFILES; 看到的所有執行的時間。

• SHOW PROFILE; 與DML連用，查看該DML語句在每一個環節所消耗的時間。

5.2.2、查看server層的運行信息

5.2.2.1、服務器狀態信息SHOW STATUS;

也可以使用通配符，比如查詢select一共執行多少次：（記錄的是數據庫每一次啟動之后的情況，如果數據庫重啟則會被重置）

5.2.2.2、查看服務端線程的信息（對應的是客戶端的連接）SHOW PROCESSLIST;

如果想殺死某個線程，可以使用KILL + ID;

其數據來源是MySQL的表，如下。所以也可以直接使用sql查表

5.2.2.3、查看存儲引擎層的運行信息

SHOW ENGINE INNODB STATUS \G;

mysql>show engine innodb status +----------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Type | Name | Status | +----------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | InnoDB | | ===================================== 2016-05-12 08:52:28 2b6142bc7700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 50 seconds //最近50秒內每2秒的平均值 ----------------- BACKGROUND THREAD //backgroup 線程 ----------------- srv_master_thread loops: 1332198 srv_active, 0 srv_shutdown, 64396 srv_idle srv_master_thread log flush and writes: 1396594 ---------- SEMAPHORES // 信號量 ---------- OS WAIT ARRAY INFO: reservation count 118146 //os wait 的信息 ，reservation count 表示InnoDB產生了多少次OS WAIT OS WAIT ARRAY INFO: signal count 186714 // 進行OS WAIT線程，接收到多少次信號(single)被喚醒，如果這個single數值越大，幾十萬或者幾百萬，可能是很多I/0等待或者是InnoDB爭用問題(關于爭用問題可能與OS調度有關，可以嘗試減少innodb_thread_concurrency參數) Mutex spin waits 1664035, rounds 4276317, OS waits 20348 // Mutex spin線程無法獲取鎖而進入Spin wait ,rounds是spin wait 進行輪詢檢查mutextes的次數，os wait 線程放棄spin-wait 進入掛起狀態 RW-shared spins 302454, rounds 11667281, OS waits 69050 //RW-shared 共享鎖， RW-excl spins 83942, rounds 4021896, OS waits 28377 // RW-excl 排他鎖 Spin rounds per wait: 2.57 mutex, 38.58 RW-shared, 47.91 RW-excl // 備注：要明白Innodb如何處理互斥量(Mutexes)，以及什么是兩步獲得鎖(two-step approach)。首先進程， 試圖獲得一個鎖，如果此鎖被它人占用。它就會執行所謂的spin wait,即所謂循環的查詢”鎖被釋放了嗎？”。 如果在循環過程中，一直未得到鎖釋放的信息，則其轉入OS WAIT，即所謂線程進入掛起(suspended)狀態。 直到鎖被釋放后，通過信號(singal)喚醒線程 Spin wait的消耗遠小于OS waits。Spinwait利用cpu的空閑時間，檢查鎖的狀態， OS Wait會有所謂content switch，從CPU內核中換出當前執行線程以供其它線程使用。 你可以通過innodb_sync_spin_loops參數來平衡spin wait和os wait------------------------ LATEST DETECTED DEADLOCK ------------------------ 2016-05-11 18:52:09 2b6677e07700 //死鎖發生的時間 *** (1) TRANSACTION: TRANSACTION 495116414, ACTIVE 0.092 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 14 lock struct(s), heap size 6544, 20 row lock(s), undo log entries 6 LOCK BLOCKING MySQL thread id: 870003 block 876753 MySQL thread id 876753, OS thread handle 0x2b6685903700, query id 315677415 10.168.152.132 dsc Searching rows for update update aaaaset xxx=xxx+(-1)where id=412 and xxx+(-1)>=0 //顯示第一個死鎖的的第一個事務 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 558 page no 5 n bits 144 index `idx_aaaa_unique` of table `test`.`aaaa` trx id 495116414 lock_mode X locks rec but not gap waiting Record lock, heap no 17 PHYSICAL RECORD: n_fields 23; compact format; info bits 0 // 以上表示死鎖發生時事務1等待的鎖，事務想獲得aaaa表的idx_aaaa_unique索引對應的X排他鎖(Innodb的鎖是與索引相關)0: len 8; hex 8000000000000001; asc ;;1: len 2; hex 5748; asc WH;;2: len 8; hex 800000000000004b; asc K;;3: len 8; hex 8000000000000002; asc ;;4: len 8; hex 8000000000002725; asc '%;;5: len 8; hex 8000000000000215; asc ;;6: len 2; hex 5a50; asc ZP;;7: len 8; hex 4231363033313441; asc B160314A;;8: len 6; hex 00001d82e06a; asc j;;9: len 7; hex 1d00000235151a; asc 5 ;;10: len 8; hex 800000000000019c; asc ;;11: len 8; hex 8000000000000000; asc ;;12: len 5; hex 9998da0000; asc ;;13: len 5; hex 999f5a0000; asc Z ;;14: len 10; hex 5a303230323032303031; asc Z020202001;;15: len 12; hex e5b9bfe4b89ce5b9bfe5b79e; asc ;;16: len 4; hex 80001b2f; asc /;;17: len 7; hex 80000000000000; asc ;;18: len 23; hex 50555243484153455f4f524445525f494e5f53544f434b; asc PURCHASE_ORDER_IN_STOCK;;19: len 22; hex 53493230313630343136303030303136333531313735; asc SI20160416000016351175;;20: len 5; hex 99992b1384; asc + ;;21: SQL NULL;22: len 5; hex 99994d7c8d; asc M| ;; *** (2) TRANSACTION: // 事務2的狀態 TRANSACTION 495116394, ACTIVE 0.246 sec fetching rows mysql tables in use 1, locked 1 17 lock struct(s), heap size 2936, 18 row lock(s), undo log entries 21 MySQL thread id 870003, OS thread handle 0x2b6677e07700, query id 315677426 10.168.152.132 dsc Searching rows for update update aaaaset xxx=xxx+(-2)where id=430 and xxx+(-2)>=0 *** (2) HOLDS THE LOCK(S): // 表示事務2獲得的鎖 RECORD LOCKS space id 558 page no 5 n bits 144 index `idx_aaaa_unique` of table `test`.`aaaa` trx id 495116394 lock_mode X locks rec but not gap Record lock, heap no 17 PHYSICAL RECORD: n_fields 23; compact format; info bits 00: len 8; hex 8000000000000001; asc ;;1: len 2; hex 5748; asc WH;;2: len 8; hex 800000000000004b; asc K;;3: len 8; hex 8000000000000002; asc ;;4: len 8; hex 8000000000002725; asc '%;;5: len 8; hex 8000000000000215; asc ;;6: len 2; hex 5a50; asc ZP;;7: len 8; hex 4231363033313441; asc B160314A;;8: len 6; hex 00001d82e06a; asc j;;9: len 7; hex 1d00000235151a; asc 5 ;;10: len 8; hex 800000000000019c; asc ;;11: len 8; hex 8000000000000000; asc ;;12: len 5; hex 9998da0000; asc ;;13: len 5; hex 999f5a0000; asc Z ;;14: len 10; hex 5a303230323032303031; asc Z020202001;;15: len 12; hex e5b9bfe4b89ce5b9bfe5b79e; asc ;;16: len 4; hex 80001b2f; asc /;;17: len 7; hex 80000000000000; asc ;;18: len 23; hex 50555243484153455f4f524445525f494e5f53544f434b; asc PURCHASE_ORDER_IN_STOCK;;19: len 22; hex 53493230313630343136303030303136333531313735; asc SI20160416000016351175;;20: len 5; hex 99992b1384; asc + ;;21: SQL NULL;22: len 5; hex 99994d7c8d; asc M| ;;Record lock, heap no 59 PHYSICAL RECORD: n_fields 23; compact format; info bits 00: len 8; hex 8000000000000001; asc ;;1: len 2; hex 5748; asc WH;;2: len 8; hex 800000000000004b; asc K;;3: len 8; hex 8000000000000002; asc ;;4: len 8; hex 800000000000276a; asc 'j;;5: len 8; hex 80000000000002c2; asc ;;6: len 2; hex 5a50; asc ZP;;7: len 9; hex 423136303231374341; asc B160217CA;;8: len 6; hex 00001d82e06a; asc j;;9: len 7; hex 1d00000235169f; asc 5 ;;10: len 8; hex 80000000000001db; asc ;;11: len 8; hex 8000000000000000; asc ;;12: len 5; hex 9998a20000; asc ;;13: len 5; hex 99a2600000; asc ` ;;14: len 10; hex 5a303230323032303031; asc Z020202001;;15: len 12; hex e5b9bfe4b89ce5b9bfe5b79e; asc ;;16: len 4; hex 80000772; asc r;;17: len 7; hex 80000000000000; asc ;;18: len 23; hex 50555243484153455f4f524445525f494e5f53544f434b; asc PURCHASE_ORDER_IN_STOCK;;19: len 22; hex 53493230313630343136303030303137333630353531; asc SI20160416000017360551;;20: len 5; hex 99992b1385; asc + ;;21: SQL NULL;22: len 5; hex 99994d7c8d; asc M| ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED: // 表示事務2等待的鎖 RECORD LOCKS space id 558 page no 4 n bits 152 index `idx_aaaa_unique` of table `test`.`aaaa` trx id 495116394 lock_mode X locks rec but not gap waiting Record lock, heap no 63 PHYSICAL RECORD: n_fields 23; compact format; info bits 00: len 8; hex 8000000000000001; asc ;;1: len 2; hex 5748; asc WH;;2: len 8; hex 800000000000004b; asc K;;3: len 8; hex 8000000000000002; asc ;;4: len 8; hex 8000000000000065; asc e;;5: len 8; hex 80000000000000a8; asc ;;6: len 2; hex 5a50; asc ZP;;7: len 9; hex 423136303232314b41; asc B160221KA;;8: len 6; hex 00001d82e07e; asc ~;;9: len 7; hex 2b000001d920ad; asc + ;;10: len 8; hex 80000000000001c8; asc ;;11: len 8; hex 8000000000000000; asc ;;12: len 5; hex 9998aa0000; asc ;;13: len 5; hex 99a2680000; asc h ;;14: len 10; hex 5a303230323032303031; asc Z020202001;;15: len 12; hex e5b9bfe4b89ce5b9bfe5b79e; asc ;;16: len 4; hex 80000b14; asc ;;17: len 7; hex 80000000000000; asc ;;18: len 23; hex 50555243484153455f4f524445525f494e5f53544f434b; asc PURCHASE_ORDER_IN_STOCK;;19: len 22; hex 53493230313630343136303030303137333630353531; asc SI20160416000017360551;;20: len 5; hex 99992b1385; asc + ;;21: SQL NULL;22: len 5; hex 99994d7c8d; asc M| ;;*** WE ROLL BACK TRANSACTION (1) // 表示選擇了哪個事務回滾，避免無限期死鎖等待 // innodb有一個內在的死鎖檢測工具，當死鎖超過一定時間后，會回滾其中一個事務,innodb_lock_wait_timeout 可配置死鎖等待超時時間------------ TRANSACTIONS // 包含了InnoDB事務(transaction)的統計信息 ------------ Trx id counter 495910498 // 當前的transaction id ,這是個系統變量，隨著每次新的transaction產生而增加 Purge done for trx's n:o < 495910389 undo n:o < 0 state: running but idle //正在進行清空的操作操作的transaction ID History list length 2606 // 記錄了undo spaces 內unpurged 的事務個數 //Purge的原則就是記錄沒有被其它事務繼續使用了LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 329193748744296, not started MySQL thread id 909825, OS thread handle 0x2b6142bc7700, query id 325773092 10.143.34.172 dsc init show engine innodb status ---TRANSACTION 329193658413160, not started MySQL thread id 909832, OS thread handle 0x2b667d881700, query id 325773024 10.168.108.146 dsc cleaning up.....此處省略...---TRANSACTION 329194102134888, not started MySQL thread id 886232, OS thread handle 0x2b6686c40700, query id 325769275 10.252.160.92 dsc cleaning up -------- FILE I/O // 顯示了I/O Helper thread d的狀態，包含一些統計信息 -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) // 以上顯示了I/O Helper thread的狀態 Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 // 顯示各個I/O Helper thread的pending operations,pending的log和buffer pool thread的fsync()調用 2531032 OS file reads, 61115257 OS file writes, 51279005 OS fsyncs //顯示了reads writes fsync() 調用次數 0.00 reads/s, 0 avg bytes/read, 46.64 writes/s, 39.30 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 3606, seg size 3608, 38466 merges // seg size 表示當前插入緩沖的大小為3608*16KB，大約為57728KB。free list len代表了空閑列表的長度，merges 表示合并次數 merged operations:insert 34642, delete mark 1008134, delete 0 //insert 插入的記錄數，delete mark 打上的標記,delete 刪除的次數 discarded operations:insert 0, delete mark 0, delete 0 AHI PARTITION 1: Hash table size 4980539, node heap has 161 buffer(s) AHI PARTITION 2: Hash table size 4980539, node heap has 90 buffer(s) AHI PARTITION 3: Hash table size 4980539, node heap has 225 buffer(s) AHI PARTITION 4: Hash table size 4980539, node heap has 352 buffer(s) AHI PARTITION 5: Hash table size 4980539, node heap has 3556 buffer(s) AHI PARTITION 6: Hash table size 4980539, node heap has 4393 buffer(s) AHI PARTITION 7: Hash table size 4980539, node heap has 3052 buffer(s) AHI PARTITION 8: Hash table size 4980539, node heap has 145 buffer(s) 26.62 hash searches/s, 51.78 non-hash searches/s --- LOG // 記錄了transaction log 子系統的信息 --- Log sequence number 264509449071 //顯示當前log sequence number表示有多少字節寫入到log文件內 Log flushed up to 264509449064 //顯示已經被flushed(寫入磁盤)的logs Pages flushed up to 264509446093 Last checkpoint at 264509412298 //顯示最后一個checkpoint 的logs 0 pending log flushes, 0 pending chkp writes 27332545 log i/o's done, 16.22 log i/o's/second // 顯示pending log 的統計信息 ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 20653670400; in additional pool allocated 0 // 顯示分配給innodb 的內存大小，以及additional pool 使用的大小 (0表示沒有使用) Dictionary memory allocated 1905658 Buffer pool size 1228800 // buffer pool size > database pages 因為buffer pool size 還會存放lock index hash index 等一些其他系統信息 Free buffers 8192 Database pages 1208634 Old database pages 445992 Modified db pages 8 Pending reads 0 //顯示了pending的reads 和writes Pending writes: LRU 0, flush list 0, single page 0 // 顯示InnoDB讀寫和創建的頁面(pages) Pages made young 842882, not young 127112054 0.00 youngs/s, 0.00 non-youngs/s Pages read 2637230, created 729565, written 30484675 0.00 reads/s, 0.34 creates/s, 24.06 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 //顯示buffer pool 的命中率 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 1208634, unzip_LRU len: 0 I/O sum[9624]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 153600 Free buffers 1024 Database pages 151095 Old database pages 55755 Modified db pages 2 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 32021, not young 15238551 0.00 youngs/s, 0.00 non-youngs/s Pages read 326672, created 90881, written 6387155 0.00 reads/s, 0.00 creates/s, 4.80 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 151095, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 153600 Free buffers 1024 Database pages 151061 Old database pages 55742 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 32063, not young 15503760 0.00 youngs/s, 0.00 non-youngs/s Pages read 327353, created 91471, written 978265 0.00 reads/s, 0.00 creates/s, 0.24 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 151061, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 153600 Free buffers 1024 Database pages 151107 Old database pages 55759 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 32746, not young 14789866 0.00 youngs/s, 0.00 non-youngs/s Pages read 328273, created 91339, written 4147582 0.00 reads/s, 0.00 creates/s, 3.50 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 151107, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 153600 Free buffers 1024 Database pages 151088 Old database pages 55752 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 31950, not young 15539726 0.00 youngs/s, 0.00 non-youngs/s Pages read 329629, created 91566, written 2998238 0.00 reads/s, 0.00 creates/s, 1.68 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 151088, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 153600 Free buffers 1024 Database pages 151063 Old database pages 55743 Modified db pages 1 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 323393, not young 17803631 0.00 youngs/s, 0.00 non-youngs/s Pages read 337003, created 90967, written 3974291 0.00 reads/s, 0.08 creates/s, 4.38 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 151063, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 153600 Free buffers 1024 Database pages 151066 Old database pages 55744 Modified db pages 3 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 323468, not young 18135650 0.00 youngs/s, 0.00 non-youngs/s Pages read 335789, created 90992, written 3382034 0.00 reads/s, 0.26 creates/s, 3.04 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 151066, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 153600 Free buffers 1024 Database pages 151073 Old database pages 55747 Modified db pages 2 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 34315, not young 15008240 0.00 youngs/s, 0.00 non-youngs/s Pages read 324769, created 91064, written 5580181 0.00 reads/s, 0.00 creates/s, 4.66 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 151073, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 153600 Free buffers 1024 Database pages 151081 Old database pages 55750 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 32926, not young 15092630 0.00 youngs/s, 0.00 non-youngs/s Pages read 327742, created 91285, written 3036929 0.00 reads/s, 0.00 creates/s, 1.76 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 151081, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS //顯示了row operations 及其他一些統計信息 -------------- 0 queries inside InnoDB, 0 queries in queue //顯示了有多少個線程在InnoDB內核 52 read views open inside InnoDB // 有多少個read view 被打開，一個read view 是一致性保證MVCC "snapshot" Main thread process no. 46196, id 47719070582528, state: sleeping //顯示內核main thread的狀態信息。 Number of rows inserted 35803330, updated 3181469, deleted 14015545, read 7740416065 12.48 inserts/s, 0.68 updates/s, 0.30 deletes/s, 3005.82 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================

5.2.3、SQL慢的原因

• EXPLAIN Output Columns

Column	JSON Name	Meaning
id	select_id	The SELECT identifier——優化器中各表的查詢順序
select_type	None	The SELECT type——查詢的類型
table	table_name	The table for the output row——訪問的表
partitions	partitions	The matching partitions——分區
type	access_type	The join type——訪問的類型，對于優化非常關鍵
possible_keys	possible_keys	The possible indexes to choose——可能用到的索引
key	key	The index actually chosen——實際用到的索引
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index——索引列做等值查詢時，與索引列比對的值得信息（如，這個值是個常數，或者另一個列）
rows	rows	Estimate of rows to be examined——預估要掃描多少數據
filtered	filtered	Percentage of rows filtered by table condition——交付客戶端/存儲引擎篩選的。（由于有server層的過濾，索引server過濾的越少越好）
Extra	None	Additional information

?

• select type查詢類型

select_type Value	JSON Name	Meaning
SIMPLE	None	Simple SELECT (not using UNION or subqueries)——簡單查詢，單表查詢
PRIMARY	None	Outermost SELECT——主查詢，有關聯查詢時，最外面那層查詢
UNION	None	Second or later SELECT statement in a UNION
DEPENDENT UNION	dependent (true)	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	union_result	Result of a UNION.
SUBQUERY	None	First SELECT in subquery——子查詢，有關聯查詢時，第一層子查詢（不管這個子查詢里又有多少子查詢）
DEPENDENT SUBQUERY	dependent (true)	First SELECT in subquery, dependent on outer query
DERIVED	None	Derived table——在得到最終結果之前，用到的臨時表
DEPENDENT DERIVED	dependent (true)	Derived table dependent on another table
MATERIALIZED	materialized_from_subquery	Materialized subquery
UNCACHEABLE SUBQUERY	cacheable (false)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	cacheable (false)	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

?

• type 針對單表的訪問方法

從上到下=從優到劣

• • const，唯一性索引和常數（常數不用計算，如子查詢等等）的匹配，id=1
  • system，訪問系統表，且表里只有一行數據
  • eq_ref，多表的關聯查詢時，與被驅動表比較時用到了被驅動表唯一性索引
  • ref，用了普通的索引（不是唯一性索引）
  • range，對索引的范圍掃描，><，between and
  • index，對索引全量掃描，比如（id是索引）select id from teacher;
  • all，沒有用到任何索引，就要對表全量掃描

?

• Extra，額外的信息

• • using index（覆蓋索引）
  • using where 存儲引擎返回的數據，并不都滿足客戶端的篩選條件時，需要server層再過濾
  • using index Condition（索引下推）
  • using filesort 排序的列不是索引
  • using temporary 過濾或排序時，需要暫存數據到臨時表，（比如，distinct、group by時）。可以對這些列創建索引，以消除using temporary

5.2.4、優化的參考

數據庫優化的目標，就是基于Explain的信息盡量的使用索引。當然在業務上也要考慮優化，盡量減少的數據庫訪問負擔。

• 表結構的優化：

• • 字段的定義，類型和長度。選擇滿足需要的最小數據類型（int，tinyint），字符char，verchar，能用定長就不要用變長。
  • 不建議用外鍵、觸發器、視圖 因為不可見，降低可讀性。外鍵，數據一致性的約束放在代碼層。
  • 大文件的存儲，圖片、文本、視頻最好不要base64編碼后存在數據庫，會占用大量空間。可以上傳到ftp服務器。
  • 字段適當冗余，解決重關聯的問題，避免太多表聯合查詢。
  • 表的拆分，盡可能滿足第三范式，拒絕表字段大而全。
  • 關系型數據庫如果不滿足，可以用nosql，大數據 。

?

• sql的優化（簡單寫寫 ）

• • 調整表查詢的順序，用小表驅動大表
  • join 代替 in
  • union 代替or
  • union all（不需要去重） 效率大于 union
  • limit 大偏移量時，比如要從100萬開始，可以先把數據過濾，再limit

?

?

?

?

?

附錄：

1、取消MySQL密碼檢驗程序：在mysql客戶端執行UNINSTALL COMPONENT 'file://component_validate_password';https://www.yuque.com/u8021948/zt89r2/kf7qxxhttps://www.yuque.com/u8021948/zt89r2/kf7qxx

參考：https://dev.mysql.com/doc/refman/8.0/en/validate-password-installation.html

2、服務器上的localhost：用戶root，密碼1qazxsw2!

遠程連接：用戶customer，密碼123qwe

來源：https://www.yuque.com/u8021948/zt89r2/kf7qxx

?

總結

以上是生活随笔為你收集整理的MySQL宏观架构与原理的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。