程序猿必知的mysql閃回原理,程序猿必知的mysql閃回原理有哪些

本文目錄一覽：

• 1、MySQL索引機制(詳細+原理+解析)
• 2、mysql主備同步的原理
• 3、Mysql的工作原理是什麼

MySQL索引機制(詳細+原理+解析)

MySQL 前綴索引能有效減小索引文件的大小，提高索引的速度。但是前綴索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引，也不能把它們用作覆蓋索引(Covering Index)。

集一個索引包含多個列（最左前綴匹配原則）

索引列的值必須唯一，但允許有空值

全文索引為FUllText，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重複值和空值，全文索引可以在CHAR,VARCHAR,TEXT類型列上創建

設定主鍵後數據會自動建立索引，InnoDB為聚簇索引

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

覆蓋索引是指一個查詢語句的執行只用從所有就能夠得到，不必從數據表中讀取，覆蓋索引不是索引樹，是一個結果，當一條查詢語句符合覆蓋索引條件時候，MySQL只需要通過索引就可以返回查詢所需要的數據，這樣避免了查到索引後的回表操作，減少了I/O效率

查看索引

列名解析：

刪除索引

查看：

刪除前:

刪除後：

普通的索引，沒有什麼介紹

查看:（注意和前綴索引Sub_part的區別）

當索引的列是unique的時候，會生成唯一索引，唯一索引關於null有下列兩種情況

SQLSERVER 下的唯一索引的列，允許null值，但最多允許有一個空值

MYSQL下的唯一索引的列，允許null值，並且允許多個空值

查看:

會建立兩個索引，一個非聚簇索引，一個是唯一索引

結果:

可以插入兩個空值（明人不說暗話，我喜歡MySQL）

一方面，它不會索引所有欄位所有字元，會減小索引樹的大小.

另外一方面，索引只是為了區別出值，對於某些列，可能前幾位區別很大，我們就可以使用前綴索引。

一般情況下某個前綴的選擇性也是足夠高的，足以滿足查詢性能。對於BLOB，TEXT，或者很長的VARCHAR類型的列，必須使用前綴索引，因為MySQL不允許索引這些列的完整長度。

查看：

查看：

複合索引的最左前綴匹配原則 ：

對於複合索引，查詢在一定條件才會使用該索引

減少開銷。 建一個聯合索引(col1,col2,col3)，實際相當於建了(col1),(col1,col2),(col1,col2,col3)三個索引。每多一個索引，都會增加寫操作的開銷和磁碟空間的開銷。對於大量數據的表，使用聯合索引會大大的減少開銷！

覆蓋索引。 對聯合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那麼MySQL可以直接通過遍歷索引取得數據，而無需回表，這減少了很多的隨機io操作。減少io操作，特別的隨機io其實是dba主要的優化策略。所以，在真正的實際應用中，覆蓋索引是主要的提升性能的優化手段之一。

效率高。 索引列越多，通過索引篩選出的數據越少。有1000W條數據的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設假設每個條件可以篩選出10%的數據，如果只有單值索引，那麼通過該索引能篩選出1000W10%=100w條數據，然後再回表從100w條數據中找到符合col2=2 and col3= 3的數據，然後再排序，再分頁；如果是聯合索引，通過索引篩選出1000w10% 10% *10%=1w。

在模糊搜索中很有效，搜索全文中的某一個欄位，可以參考這篇博文

:

我們先進行下面一個實驗看看InnoDB下的主鍵索引的一個現象。

查看:

我們插入進去的時候，數據的id都是亂序的，為什麼這裡最後select查詢出來的結果都是進行了排序？

這是因為InnoDB索引底層實現的是B+tree，B+tree具有下列的特點：

所以上面的排序是為了使用B+tree的結構 ，B+tree為了範圍搜索，將主鍵按照從小到大排序後，拆分成節點。後續還有新的節點進入的時候，和B-tree相同的操作，會進行分裂。

一般來說，聚簇索引的B+tree都是三層

InnoDB中主鍵索引一定是聚簇索引，聚簇索引一定是主鍵索引。

為什麼這裡輔助索引葉子結點不直接存儲數據呢？

MYISAM只有非聚簇索引，索引最終指向的都是物理地址。

Q:既然有回表的存在，那麼聚簇索引的優勢在哪裡？

Q:主鍵索引作為聚簇索引需要注意什麼

在查詢語句中使用LIke關鍵字進行查詢時，如果匹配字元串的第一個字元為”%”，索引不會使用。如果「%」不是在第一位，索引就會使用

多列索引是在表的多個欄位上創建的索引，滿足最左前綴匹配原則，索引才會被使用

查詢語句只有Or關鍵字時候，如果OR前後的兩個條件都是索引，這這次查詢將會使用索引，否則Or前後有一個條件的列不是索引，那麼查詢中將不使用索引

mysql主備同步的原理

MYSQL主從同步原理：

1） MYSQL主從同步是非同步複製的過程，整個同步需要開啟3線程，master上開啟bin-log日誌（記錄資料庫增、刪除、修改、更新操作）；

2） Slave開啟I/O線程來請求master伺服器，請求指定bin-log中position點之後的內容；

3） Master端收到請求，Master端I/O線程響應請求，bin-log、position之後內容返給salve；

4） Slave將收到的內容存入relay-log中繼日誌中，生成master.info（記錄master ip、bin-log、position、用戶名密碼）；

5） Slave端SQL實時監測relay-log日誌有更新，解析更新的sql內容，解析成sql語句，再salve庫中執行；

6） 執行完畢之後，Slave端跟master端數據保持一致！

**MYSQL bin-log用途**

1） bin-log日誌最大的功能記錄資料庫增、刪、改、插入等操作，記錄用戶操作的SQL語句；

2） bin-log日誌可以用數據增量備份、完整備份；

3） bin-log還可以主要主從複製+讀寫分離；

Mysql的工作原理是什麼

Mysql 工作原理圖

Mysql是由SQL介面，解析器，優化器，緩存，存儲引擎組成的。

mysql原理圖各個組件說明：

1. connectors

與其他編程語言中的sql 語句進行交互，如php、java等。

2. Management Serveices Utilities

系統管理和控制工具

3. Connection Pool (連接池)

管理緩衝用戶連接，線程處理等需要緩存的需求

4. SQL Interface (SQL介面)

接受用戶的SQL命令，並且返回用戶需要查詢的結果。比如select from就是調用SQL Interface

5. Parser （解析器）

SQL命令傳遞到解析器的時候會被解析器驗證和解析。

主要功能：

a . 將SQL語句分解成數據結構，並將這個結構傳遞到後續步驟，後面SQL語句的傳遞和處理就是基於這個結構的

b.  如果在分解構成中遇到錯誤，那麼就說明這個sql語句是不合理的，語句將不會繼續執行下去

6. Optimizer (查詢優化器)

SQL語句在查詢之前會使用查詢優化器對查詢進行優化(產生多種執行計劃,最終資料庫會選擇最優化的方案去執行,儘快返會結果) 他使用的是「選取-投影-聯接」策略進行查詢。

用一個例子就可以理解： select uid,name from user where gender = 1;

這個select 查詢先根據where 語句進行選取，而不是先將表全部查詢出來以後再進行gender過濾

這個select查詢先根據uid和name進行屬性投影，而不是將屬性全部取出以後再進行過濾

將這兩個查詢條件聯接起來生成最終查詢結果.

7. Cache和Buffer (查詢緩存)

如果查詢緩存有命中的查詢結果，查詢語句就可以直接去查詢緩存中取數據。

這個緩存機制是由一系列小緩存組成的。比如表緩存，記錄緩存，key緩存，許可權緩存等

8.Engine (存儲引擎)

存儲引擎是MySql中具體的與文件打交道的子系統。也是Mysql最具有特色的一個地方。

Mysql的存儲引擎是插件式的。它根據MySql AB公司提供的文件訪問層的一個抽象介面來定製一種文件訪問機制（這種訪問機制就叫存儲引擎）

SQL 語句執行過程

資料庫通常不會被直接使用，而是由其他編程語言通過SQL語句調用mysql，由mysql處理並返回執行結果。那麼Mysql接受到SQL語句後，又是如何處理

首先程序的請求會通過mysql的connectors與其進行交互，請求到處後，會暫時存放在連接池（connection pool)中並由處理器（Management Serveices Utilities）管理。當該請求從等待隊列進入到處理隊列，管理器會將該請求丟給SQL介面（SQL Interface）。SQL介面接收到請求後，它會將請求進行hash處理並與緩存中的結果進行對比，如果完全匹配則通過緩存直接返回處理結果；否則，需要完整的走一趟流程：

(1)由SQL介面丟給後面的解釋器（Parser），解釋器會判斷SQL語句正確與否，若正確則將其轉化為數據結構。

(2)解釋器處理完，便來到後面的優化器（Optimizer），它會產生多種執行計劃,最終資料庫會選擇最優化的方案去執行,儘快返會結果。

(3)確定最優執行計劃後，SQL語句此時便可以交由存儲引擎（Engine）處理，存儲引擎將會到後端的存儲設備中取得相應的數據，並原路返回給程序。

注意點

(1)如何緩存查詢數據

存儲引擎處理完數據，並將其返回給程序的同時，它還會將一份數據保留在緩存中，以便更快速的處理下一次相同的請求。具體情況是，mysql會將查詢的語句、執行結果等進行hash，並保留在cache中，等待下次查詢。

(2)buffer與cache的區別

從mysql原理圖可以看到，緩存那裡實際上有buffer和cache兩個，那它們之間的區別：簡單的說就是，buffer是寫緩存，cache是讀緩存。

(3)如何判斷緩存中是否已緩存需要的數據

這裡可能有一個誤區，覺得處理SQL語句的時候，為了判斷是否已緩存查詢結果，會將整個流程走一遍，取得執行結果後再與需要的進行對比，看看是否命中，並以此說，既然不管緩存中有沒有緩存到查詢內容，都要整個流程走一遍，那緩存的優勢在哪？

其實並不是這樣，在第一次查詢後，mysql便將查詢語句以及查詢結果進行hash處理並保留在緩存中，SQL查詢到達之後，對其進行同樣的hash處理後，將兩個hash值進行對照，如果一樣，則命中，從緩存中返回查詢結果；否則，需要整個流程走一遍。