MySQL知識彙總

橡皮筋兒發表於2021-11-08
MySql

一、mysql事務

innodb事務日誌包括redo log和undo log。

redo log是重做日誌，提供前滾操作，undo log是回滾日誌，提供回滾操作。

前滾：

​ 未完全提交的事務，即該事務已經被執行commit命令了，只是現在該事務修改所對應的髒資料塊中只有一部分被寫到磁碟上的資料檔案中，還有一部分已經被置為提交標記的髒塊還在記憶體上，如果此時資料庫例項崩潰了，則當資料庫例項恢復時，就需要用前滾（這個機制）來完成事務的完全提交，即將先前那部分已經被置為提交標記且還在記憶體上的髒塊寫入到磁碟上的資料檔案中。

回滾：

​ 未提交的事務，即該事務未被執行commit命令。但是此時，該事務修改的髒塊中也有可能一部分髒塊寫入到資料檔案中了。如果此時資料庫例項崩潰了，則當資料庫例項恢復時，就需要用回滾（這個機制）來將先前那部分已經寫入到資料檔案的髒塊從資料檔案上撤銷掉。

redo log

redo log包括兩部分：

一是記憶體中的日誌緩衝(redo log buffer)，該部分日誌是易失性的；

二是磁碟上的重做日誌檔案(redo log file)，該部分日誌是持久的。

通常是物理日誌，記錄的是資料頁的物理修改，而不是某一行或某幾行修改成怎樣怎樣，它用來恢復提交後的物理資料頁(恢復資料頁，且只能恢復到最後一次提交的位置)。

undo log

一般是邏輯日誌，undo用來回滾行記錄到某個版本。，根據每行記錄進行記錄。

undo log和redo log記錄物理日誌不一樣，它是邏輯日誌。

可以認為當delete一條記錄時，undo log中會記錄一條對應的insert記錄，反之亦然，當update一條記錄時，它記錄一條對應相反的update記錄。

當執行rollback時，就可以從undo log中的邏輯記錄讀取到相應的內容並進行回滾

另外，undo log也會產生redo log，因為undo log也要實現永續性保護。

實質

通過undo log記錄delete和update操作的結果發現：(insert操作無需分析，就是插入行而已)

  • delete操作實際上不會直接刪除，而是將delete物件打上delete flag，標記為刪除，最終的刪除操作是purge執行緒完成的。
  • update分為兩種情況：update的列是否是主鍵列。
    • 如果不是主鍵列，在undo log中直接反向記錄是如何update的。即update是直接進行的。
    • 如果是主鍵列，update分兩部執行：先刪除該行，再插入一行目標行。

恢復

在啟動innodb的時候，不管上次是正常關閉還是異常關閉，總是會進行恢復操作。

因為redo log記錄的是資料頁的物理變化，因此恢復的時候速度比邏輯日誌(如二進位制日誌)要快很多。而且，innodb自身也做了一定程度的優化，讓恢復速度變得更快。

與Redis事務區別

mysql:

  1. 寫方面： 事務開啟時，事務中的操作，都會先寫入儲存引擎的日誌緩衝中

  2. 提交： 當事務提交之後，在Buffer Pool中對映的資料檔案才會慢慢重新整理到磁碟。

  3. 回滾：此時如果資料庫崩潰或者當機，那麼當系統重啟進行恢復時，就可以根據redo log中記錄的日誌，把資料庫恢復到崩潰前的一個狀態。未完成的事務，可以繼續提交，也可以選擇回滾，這基於恢復的策略而定。

redis:

  1. 寫：所有命令都會被序列化

  2. 提交：在事務提交過程，會按照順序序列化執行佇列中的命令

  3. 回滾：沒有回滾。事務中任意命令執行失敗，其餘的命令仍會被執行

二、Redis事務

Redis事務的概念：

　　Redis 事務的本質是一組命令的集合。事務支援一次執行多個命令，一個事務中所有命令都會被序列化。在事務執行過程，會按照順序序列化執行佇列中的命令，其他客戶端提交的命令請求不會插入到事務執行命令序列中。

　　總結說：redis事務就是一次性、順序性、排他性的執行一個佇列中的一系列命令。　　

Redis事務沒有隔離級別的概念：

　　批量操作在傳送 EXEC 命令前被放入佇列快取，並不會被實際執行，也就不存在事務內的查詢要看到事務裡的更新，事務外查詢不能看到。

Redis不保證原子性：

　　Redis中，單條命令是原子性執行的，但事務不保證原子性，且沒有回滾。事務中任意命令執行失敗，其餘的命令仍會被執行。

Redis事務的三個階段：

  • 開始事務
  • 命令入隊
  • 執行事務

三、MySql鎖

MyISAM和MEMORY採用表級鎖(table-level locking)

BDB採用頁面鎖(page-level locking)或表級鎖，預設為頁面鎖

InnoDB支援行級鎖(row-level locking)和表級鎖,預設為行級鎖

3.1按照對資料操作的鎖粒度來分

1 行級鎖（加在索引上的鎖）

行級鎖分為共享鎖和排他鎖

(1) 描述

行級鎖是mysql中鎖定粒度最細的一種鎖。表示只針對當前操作的行進行加鎖。行級鎖能大大減少資料庫操作的衝突，其加鎖粒度最小，但加鎖的開銷也最大。行級鎖分為共享鎖和排他鎖

(2) 特點

開銷大，加鎖慢，會出現死鎖。發生鎖衝突的概率最低，併發度也最高。

InnoDB有三種行鎖的演算法（排他鎖）：

1，Record Lock（記錄鎖）：單個行記錄上的鎖。這個也是我們日常認為的行鎖。

2，Gap Lock（間隙鎖）：間隙鎖，鎖定一個範圍，但不包括記錄本身（只不過它的鎖粒度比記錄鎖的鎖整行更大一些，他是鎖住了某個範圍內的多個行，包括根本不存在的資料）。

GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況。

該鎖只會在隔離級別是RR或者以上的級別記憶體在。

間隙鎖的目的是為了讓其他事務無法在間隙中新增資料。

3，Next-Key Lock（臨鍵鎖）：它是記錄鎖和間隙鎖的結合，鎖定一個範圍，並且鎖定記錄本身。對於行的查詢，都是採用該方法，主要目的是解決幻讀的問題。next-key鎖是InnoDB預設的鎖

record lock、gap lock、next-key lock，都是加在索引上的。假設有記錄1,3,5,7，

則5上的記錄鎖會鎖住5，

5上的gap lock會鎖住(3,5)，

5上的next-key lock會鎖住(3,5]。

2 表級鎖

(1) 描述

表級鎖是mysql中鎖定粒度最大的一種鎖，表示對當前操作的整張表加鎖，它實現簡單，資源消耗較少，被大部分mysql引擎支援。最常使用的MyISAM與InnoDB都支援表級鎖定。表級鎖定分為表共享讀鎖（共享鎖）與表獨佔寫鎖（排他鎖）

(2) 特點

開銷小，加鎖快，不會出現死鎖。發生鎖衝突的概率最高，併發度也最低。

  • LOCK TABLE my_table_name READ; 用讀鎖鎖表，會阻塞其他事務修改表資料。
  • LOCK TABLE my_table_name WRITE; 用寫鎖鎖表，會阻塞其他事務讀和寫。

MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，
在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程並不需要使用者干預，

因此，使用者一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。

但是在InnoDB中如果需要表鎖就需要顯式地宣告瞭

3 頁級鎖

(1) 描述

頁級鎖是 MySQL 中鎖定粒度介於行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但衝突多，行級衝突少，但速度慢。
因此，採取了折中的頁級鎖，一次鎖定相鄰的一組記錄。BDB 支援頁級鎖。

(2) 特點

開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。

3.2按照鎖的共享策略來分

共享鎖和排他鎖在MySQL中具體的實現就是讀鎖和寫鎖：

  • 讀鎖（共享鎖）：Shared Locks（S鎖），針對同一份資料，多個讀操作可以同時進行而不會互相影響
  • 寫鎖（排它鎖）：Exclusive Locks（X鎖），當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖
  • IS鎖：意向共享鎖、Intention Shared Lock。當事務準備在某條記錄上加S鎖時，需要先在表級別加一個IS鎖。
  • IX鎖：意向排他鎖、Intention Exclusive Lock。當事務準備在某條記錄上加X鎖時，需要先在表級別加一個IX鎖。

它們的提出僅僅為了在之後加表級別的S鎖和X鎖時可以快速判斷表中的記錄是否被上鎖，

以避免用遍歷的方式來檢視表中有沒有上鎖的記錄。

注意：如果一個表中有多個行鎖，他們都會給表加上意向鎖，意向鎖和意向鎖之間是不會衝突的。

3.3從加鎖策略上分

樂觀鎖

樂觀鎖在運算元據時非常樂觀，認為別人不會同時修改資料。

因此樂觀鎖不會上鎖，只是在執行更新的時候判斷一下在此期間別人是否修改了資料：如果別人修改了資料則放棄操作，否則執行操作。

樂觀鎖的實現方式主要有兩種：CAS機制和版本號機制

CAS操作邏輯如下：

如果記憶體位置V的值等於預期的A值，則將該位置更新為新值B，否則不進行任何操作。

許多CAS的操作是自旋的：如果操作不成功，會一直重試，直到操作成功為止。

版本號機制：

的基本思路是在資料中增加一個欄位version，表示該資料的版本號，每當資料被修改，版本號加1。

  • 當某個執行緒查詢資料時，將該資料的版本號一起查出來；
  • 當該執行緒更新資料時，判斷當前版本號與之前讀取的版本號是否一致，如果一致才進行操作

悲觀鎖

悲觀鎖在運算元據時比較悲觀，認為別人會同時修改資料。

因此運算元據時直接把資料鎖住，直到操作完成後才會釋放鎖；上鎖期間其他人不能修改資料。

3.4自增鎖

自增鎖（AUTO-INC鎖）
自增鎖是一種特殊的表級鎖，主要用於事務中插入自增欄位，也就是我們最常用的自增主鍵id。
通過innodb_autoinc_lock_mode引數可以設定自增主鍵的生成策略。防止併發插入資料的時候自增id出現異常。

當一張表的某個欄位是自增列時，innodb會在該索引的末位加一個排它鎖。
為了訪問這個自增的數值，需要加一個表級鎖，不過這個表級鎖的持續時間只有當前sql，而不是整個事務，即當前sql執行完，該表級鎖就釋放了。其他session無法在這個表級鎖持有時插入任何記錄。

四、MySQL InnoDB儲存引擎

InnoDB優勢

1）支援事務

InnoDB 最重要的一點就是支援事務，可以說這是 InnoDB 成為 MySQL 中最流行的儲存引擎的一個非常重要的原因

InnoDB 還實現了 SQL92 標準所定義的 4 個隔離級別

2）災難恢復性好

commit、rollback、crash-recovery 來保障資料的安全

3）使用行級鎖

InnoDB 的行鎖機制是通過索引來完成的，但畢竟在資料庫中 99%的 SQL 語句都要使用索引來檢索資料。行鎖定機制也為 InnoDB 在承受高併發壓力的環境下增強了不小的競爭力。

4）實現了緩衝處理

InnoDB 提供了專門的快取池，實現了緩衝管理，不僅能緩衝索引也能緩衝資料，常用的資料可以直接從記憶體中處理，比從磁碟獲取資料處理速度要快。

當一條 SQL 執行的時候，如果是讀操作，要查詢的資料所在的資料頁在記憶體中時，則將結果返回。否則會把對應的資料頁載入到記憶體中，然後再返回結果。

同樣對於寫操作來說。如果要修改的行所在的資料頁在記憶體中，則修改後返回對應的結果（當然還有後續操作）。如果不在的話，則會從磁碟裡將該行所對應的資料頁讀到記憶體中再進行修改。

1.緩衝池的預讀機制

InnoDB使用兩種預讀演算法來提高I/O效能：線性預讀（linear read-ahead）和隨機預讀

會產生兩個問題

緩衝池汙染問題
還有一種情況是當執行一條 SQL 語句時，如果掃描了大量資料或是進行了全表掃描，此時緩衝池中就會載入大量的資料頁，從而將緩衝池中已存在的所有頁替換出去，這種情況同樣是不合理的。這就是緩衝池汙染，並且還會導致 MySQL 效能急劇下降。

預讀失效問題

被預先載入進緩衝池的頁，並沒有被訪問到

2.緩衝重新整理策略

通常來說，緩衝池是通過LRU（Latest Recent Used，最近最少使用）

5）檔案大小不受限制

InnoDB 的表和索引在一個邏輯表空間中，表空間可以包含數個檔案（或原始磁碟分割槽）。

這與 MyISAM 表不同，比如在 MyISAM 表中每個表被儲存在分離的檔案中。

InnoDB 表可以是任何尺寸，即使在檔案尺寸被限制為 2GB 的作業系統上。

6）支援外來鍵

InnoDB 支援外來鍵約束，檢查外來鍵、插入、更新和刪除，以確保資料的完整性。

在儲存表中資料時每張表的儲存都按主鍵順序存放，如果沒有顯式地在定義表時指定主鍵，InnoDB 會為每一行生成一個 6 位元組的 ROWID ，並以此作為主鍵。

物理儲存

1. 資料檔案（表資料和索引資料）

資料檔案用來存放資料表中的資料和所有的索引資料，包括主鍵和其他普通索引。

InnoDB 儲存的資料採用表空間（Tablepace）進行存放設計。表空間是用來存放 MySQL 系統相關資訊的一個特殊共享表空間。

InnoDB 的表空間分為以下兩種形式：

  1. 共享表空間，表資料和索引都存放在同一個表空間。預設的表空間檔案就是上面所提到的 MySQL 初始化路徑下的 ibdata1 檔案。
  2. 獨立表空間，每個表的資料和索引被存放在一個單獨的 .ibd 檔案中。

InnoDB 的表空間分為以下兩種形式：

  1. 共享表空間，表資料和索引都存放在同一個表空間。預設的表空間檔案就是上面所提到的 MySQL 初始化路徑下的 ibdata1 檔案。
  2. 獨立表空間，每個表的資料和索引被存放在一個單獨的 .ibd 檔案中。

2. 日誌檔案

預設情況下，InnoDB 儲存引擎的資料目錄下會有兩個名為 ib_logfile0 和 ib_logfile1 的檔案。在 MySQL 官方手冊中將其稱為 InnoDB 儲存引擎的重做日誌檔案（redo log file）。

每個 InnoDB 儲存引擎至少有 1 個重做日誌檔案組（group），

每個檔案組下至少有 2 個重做日誌檔案，如預設的 ib_logfile0 和 ib_logfile1。

五、MySql相關小知識

1.自增主鍵用完了該怎麼辦

旦自增id達到最大值，此時資料繼續插入是會報一個主鍵衝突異常如下所示

//Duplicate entry '4294967295' for key 'PRIMARY'

解決方法：將Int型別改為BigInt型別

1.1線上怎麼修改列的資料型別的

方式一:使用mysql5.6+提供的線上修改功能

對於修改資料型別這種操作，是不支援併發的DML操作！也就是說，如果你直接使用ALTER這樣的語句線上修改表資料結構，會導致這張表無法進行更新類操作(DELETE、UPDATE、DELETE)。 因此，直接ALTER是不行滴！

方式二:藉助第三方工具

1、pt-online-schema-change，簡稱pt-osc - 2、GitHub正式宣佈以開源的方式釋出的工具，名為gh-ost

如果你的表裡有觸發器和外來鍵，這兩個工具是不行

方式三:改從庫表結構，然後主從切換

mysql架構一般是讀寫分離架構，從機是用來讀的。我們直接在從庫上進行表結構修改，不會阻塞從庫的讀操作。改完之後，進行主從切換即可。

可能會有資料丟失的情況

但是：

一般達不到最大值，我們就分庫分表了，所以不曾遇見過！"

2.char和varchar的區別

  1. char型別的長度是固定的，varchar的長度是可變的。

    這就表示，儲存字串'abc'，使用char(10)，表示儲存的字元將佔10個位元組，如果不足10位元組，將使用空格佔位，所以檢索CHAR值時需刪除尾隨空格

    使用varchar2(10),，則表示只佔3個位元組，10是最大值，當儲存的字元小於10時，按照實際的長儲。

  2. 2.char型別的效率比varchar的效率稍高

3.set欄位型別

SET是一個字串物件，可以有零或多個值，其值來自表建立時規定的允許的一列值。

指定包括多個SET成員的SET列值時各成員之間用逗號(‘,’)間隔開。這樣SET成員值本身不能包含逗號。

SET最多可以有64個不同的成員。

當建立表時，SET成員值的尾部空格將自動被刪除。

4.BLOB 和 TEXT 有什麼區別？

TEXT與BLOB的主要差別就是BLOB儲存二進位制資料，TEXT儲存字元資料。。

主要差別

TEXT與BLOB的主要差別就是BLOB儲存二進位制資料，TEXT儲存字元資料。

目前幾乎所有部落格內容裡的圖片都不是以二進位制儲存在資料庫的，而是把圖片上傳到伺服器然後正文裡使用MySQL知識彙總標籤引用，這樣的部落格就可以使用TEXT型別。而BLOB就可以把圖片換算成二進位制儲存到資料庫中。

5.MySQL資料庫預計運維三年,怎麼優化？

1、設計良好的資料庫結構，允許部分資料冗餘，儘量避免 join 查詢，提高效率。

2、選擇合適的表欄位資料型別和儲存引擎，適當的新增索引。

3、MySQL 庫主從讀寫分離。

4、找規律分表，減少單表中的資料量提高查詢速度。

5、新增快取機制，比如 memcached，apc 等。

6、不經常改動的頁面，生成靜態頁面。

7、書寫高效率的 SQL。比如 SELECT ***** FROM TABEL 改為 SELECT field_1, field_2, field_3 FROM TABLE

6.什麼是儲存過程？用什麼來呼叫？

儲存過程是一個預編譯的 SQL 語句，優點是允許模組化的設計，就是說只需建立一次，以後在該程式中就可以呼叫多次。

如果某次操作需要執行多次 SQL， 使用儲存過程比單純 SQL 語句執行要快。可以用一個命令物件來呼叫儲存過程

7.什麼是觸發器，可以用來做什麼

觸發器是與 MySQL 資料表有關的資料庫物件，在滿足定義條件時觸發，並執行觸發器中定義的語句集合

觸發器的這種特性可以協助應用在資料庫端確保資料的完整性。

六、MySQL索引底層實現原理

索引是資料結構。

資料庫系統的設計者會從查詢演算法的角度進行優化。最基本的查詢演算法當然是順序查詢（linear search），這種複雜度為O(n)的演算法在資料量很大時顯然是糟糕的。

電腦科學的發展提供了很多更優秀的查詢演算法，例如二分查詢（binary search）、二叉樹查詢（binary tree search）等

但是資料本身的組織結構不可能完全滿足各種資料結構（例如，理論上不可能同時將兩列都按順序進行組織），所以，資料庫系統還維護著滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實現高階查詢演算法。這種資料結構，就是索引。

6.1B樹

B樹事實上是一種平衡的多叉查詢樹，也就是說最多可以開m個叉（m>=2）

二、五階B樹

B樹的查詢過程和二叉排序樹比較類似，從根節點依次比較每個結點，因為每個節點中的關鍵字和左右子樹都是有序的，所以只要比較節點中的關鍵字，或者沿著指標就能很快地找到指定的關鍵字，如果查詢失敗，則會返回葉子節點，即空指標。

6.2Plus版 — B+樹

作為B樹的加強版，B+樹與B樹的差異在於

  • 所有的關鍵字全部儲存在葉子節點上，且葉子節點本身根據關鍵字自小而大順序連線。
  • 非葉子節點可以看成索引部分，節點中僅含有其子樹（根節點）中的最大（或最小）關鍵字。
  • 有n棵子樹的節點含有n個關鍵字（也有認為是n-1個關鍵字）。

查詢：

B+樹的查詢過程，與B樹類似，只不過查詢時，如果在非葉子節點上的關鍵字等於給定值，並不終止，而是繼續沿著指標直到葉子節點位置。因此在B+樹，不管查詢成功與否，每次查詢都是走了一條從根到葉子節點的路徑。

特性

  • 所有關鍵字都儲存在葉子節上，且連結串列中的關鍵字恰好是有序的。
  • 不可能非葉子節點命中返回。
  • 非葉子節點相當於葉子節點的索引，葉子節點相當於是儲存（關鍵字）資料的資料層。
  • 更適合檔案索引系統。

6.3為什麼使用B樹（B+樹）

一般來說，索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存在磁碟上。

這樣的話，索引查詢過程中就要產生磁碟I/O消耗，相對於記憶體存取，I/O存取的消耗要高几個數量級，所以評價一個資料結構作為索引的優劣最重要的指標就是在查詢過程中磁碟I/O操作次數的漸進複雜度。

換句話說，索引的結構組織要儘量減少查詢過程中磁碟I/O的存取次數。下面先介紹記憶體和磁碟存取原理，然後再結合這些原理分析B-/+Tree作為索引的效率

主存存取原理

當系統需要讀取主存時，則將地址訊號放到地址匯流排上傳給主存，主存讀到地址訊號後，解析訊號並定位到指定儲存單元，然後將此儲存單後設資料放到資料匯流排上，供其它部件讀取。

主存存取的時間僅與存取次數呈線性關係，因為不存在機械操作

磁碟存取原理

當需要從磁碟讀取資料時，系統會將資料邏輯地址傳給磁碟，

磁碟的控制電路按照定址邏輯將邏輯地址翻譯成實體地址，即確定要讀的資料在哪個磁軌，哪個扇區。

為了讀取這個扇區的資料，需要將磁頭放到這個扇區上方，為了實現這一點，磁頭需要移動對準相應磁軌，這個過程叫做尋道，所耗費時間叫做尋道時間，然後磁碟旋轉將目標扇區旋轉到磁頭下，這個過程耗費的時間叫做旋轉時間。

區域性性原理與磁碟預讀

區域性性原理：當一個資料被用到時，其附近的資料也通常會馬上被使用。從這個位置開始，順序向後讀取一定長度的資料放入記憶體

磁碟預讀：預讀的長度一般為頁（page）的整倍數。

​ 頁是計算機管理儲存器的邏輯塊，硬體及作業系統往往將主存和磁碟儲存區分割為連續的大小相等的塊，每個儲存塊稱為一頁（在許多 作業系統中，頁得大小通常為4k）

​ 主存和磁碟以頁為單位交換資料。當程式要讀取的資料不在主存中時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤訊號，磁碟會 找到資料的起始位置並向後連續讀取一頁或幾頁載入記憶體中，然後異常返回，程式繼續執行。

​ 此時如果快取空間滿了，就會觸發快取過期策略

快取過期策略：

FIFO：First In First Out，先進先出

LRU：Least Recently Used，最近最少使用

LFU：Least Frequently Used，最不經常使用

效能分析總結

資料庫系統的設計者巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入。

B-Tree中一次檢索最多需要h-1次I/O（根節點常駐記憶體），漸進複雜度為O(h)=O(logdN)O(h)=O(logdN)。

一般實際應用中，出度d是非常大的數字，通常超過100，因此h非常小（通常不超過3）。

（h表示樹的高度 & 出度d表示的是樹的度，即樹中各個節點的度的最大值）

而紅黑樹這種結構，h明顯要深的多。由於邏輯上很近的節點（父子）物理上可能很遠，無法利用區域性性，所以紅黑樹效率明顯比B-Tree差很多。

綜上所述，用B-Tree作為索引結構效率是非常高的。

此外：因為資料都是儲存在葉子節點上的，所以非葉子節點上沒有資料域，因此可以擁有更大的出度，從而擁有更好的效能。

6.4MySQL索引實現

MYISAM索引實現

可以看出MyISAM的索引檔案僅僅儲存資料記錄的地址。

在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重複。

因此，MyISAM中索引檢索的演算法為首先按照B+Tree搜尋演算法搜尋索引，如果指定的Key存在，則取出其data域的值，然後以data域的值為地址，讀取相應資料記錄。

MyISAM的索引方式也叫做“非聚集”的，之所以這麼稱呼是為了與InnoDB的聚集索引區分。

InnoDB索引實現


在InnoDB中，表資料檔案本身就是按B+Tree組織的一個索引結構，這棵樹的葉節點data域儲存了完整的資料記錄。

這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主索引。

與myisam不同點

  1. 葉節點包含了完整的資料記錄。這種索引叫做聚集索引。因為InnoDB的資料檔案本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有）

  2. 第二個與MyISAM索引的不同是InnoDB的輔助索引data域儲存相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。

聚集索引這種實現方式使得按主鍵的搜尋十分高效，但是輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。

使用聚簇索引的優勢

  1. 輔助索引的葉子節點，儲存主鍵值，而不是資料的存放地址。好處是當行資料放生變化時，索引樹的節點也需要分裂變化；

    如果儲存完整記錄，則需要對輔助索引維護，只存主鍵值，那麼只需要維護聚簇索引樹就可以了。

  2. 因為輔助索引存放的是主鍵值，減少了輔助索引佔用的儲存空間大小。

  3. 聚簇索引的資料的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那麼對應的資料一定也是相鄰地存放在磁碟上的

    非聚簇索引，那麼他的資料的實體地址必然是凌亂的，為了拿到資料，需要不停的尋道不停的旋轉。

    而聚簇索引則只需一次I/O就可以獲得一頁的資料。（強烈的對比）

6.5總結

1.索引是利用空間換取時間，將資料的引用按照合適的資料結構儲存，就是索引
2.索引使用b+樹，因為b+樹很適合檔案系統查詢
3.瞭解索引的實現，在建立索引的時候，可以提升表的效率

七、Mysql優化

Mysql的優化，大體可以分為三部分：索引的優化，sql語句的優化，表的優化

1.索引優化

一般的應用系統，讀寫比例在10：1左右，在生產環境中，我們遇到最多的也是最容易出現問題的，還是一些複雜的查詢操作，

因此對查詢語句的優化是重中之重，加速查詢最好的方法就是索引。

索引型別

普通索引：是最基本的索引，它沒有任何限制。

唯一索引：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

組合索引：指多個欄位上建立的索引，只有在查詢條件中使用了建立索引時的第一個欄位，索引才會被使用。

主鍵索引：是一種特殊的唯一索引，一個表只能有一個主鍵，不允許有空值。一般是在建表的時候同時建立主鍵索引

全文索引：主要用來查詢文字中的關鍵字，而不是直接與索引中的值相比較。它更像是一個搜尋引擎

索引優化

  • 只要列中含有NULL值，就最好不要在此例設定索引，複合索引如果有NULL值，此列在使用時也不會使用索引
  • 儘量使用短索引，如果可以，應該制定一個字首長度
  • 對於經常在where子句使用的列，最好設定索引，這樣會加快查詢速度
  • 對於有多個列where或者order by子句的，應該建立複合索引
  • 對於like語句，以%或者‘-’開頭的不會使用索引，以%結尾會使用索引
  • 儘量不要在列上進行運算（函式操作和表示式操作）
  • 儘量不要使用not in和<>操作

2.sql慢查詢優化

操作

  1. 如何捕獲低效sql

    slow_query_log=ON/OFF;

  2. 執行時間超過此數值時，就會被記錄到日誌中

    long_query_time=n;

  3. 設定日誌名

    slow_query_log_file

步驟

  1. 先執行看看是否真的很慢，注意設定SQL_NO_CACHE（在查詢時，不使用快取）
  2. where鎖定最小返回記錄表（單表每個欄位分別查詢，看哪個欄位的區分度最高）
  3. explain檢視執行計劃
  4. 按照查詢，加入索引，參照建索引的幾大原則
  5. 觀察結果

優化的原則

  • 查詢時，能不要*就不用，儘量寫全欄位名
  • 大部分情況連線效率遠大於子查詢
  • 多使用explain和profile分析查詢語句
  • 檢視慢查詢日誌，找出執行時間長的sql語句優化
  • 多表連線時，儘量小表驅動大表，即小表 join 大表
  • 在千萬級分頁時使用limit
  • 對於經常使用的查詢，可以開啟快取

開啟快取

1、修改配置檔案my.ini

windows下是my.ini，linux下是my.cnf;

在配置檔案的最後追加上：

query_cache_type = 1
query_cache_size = 600000

需要重啟mysql生效；

2.命令方式
set global query_cache_type = 1;  
set global query_cache_size = 600000;

3.資料庫表優化

  • 表的欄位儘可能用NOT NULL
  • 欄位長度固定的表查詢會更快
  • 把資料庫的大表按時間或一些標誌分成小表
  • 將表拆分

資料表拆分

主要就是垂直拆分和水平拆分。

水平切分:將記錄雜湊到不同的表中，各表的結構完全相同，每次從分表中查詢, 提高效率。

垂直切分:將表中大欄位單獨拆分到另外一張表, 形成一對一的關係（穀粒學院中的course表就拆分為了course和course_discription）

相關文章