home > DB > mysql >

再談mysql索引—索引建立、存儲結構、存儲格式、存儲模型

author:zhoulujun    hits:

為啥使用索引數據庫的檢索速度加快,插入、刪除、修改等維護任務的速度降低?索引最左匹配原則是啥?聚集索引、非聚集索引,Hash索引、B+樹索引有什么區別?索引的存儲結構、存儲格式、存儲模式、優化手段說一下?

對于 MySQL 數據庫而言,數據是存儲在文件里的,而為了能夠快速定位到某張表里的某條記錄進行查詢和修改,我們需要將這些數據以一定的數據結構進行存儲,這個數據結構就是我們說的索引。

什么是索引

索引就像一本書的目錄。而當用戶通過索引查找數據時,就好比用戶通過目錄查詢某章節的某個知識點。這樣就幫助用戶有效地提高了查找速度。所以,使用索引可以有效地提高數據庫系統的整體性能。

索引,類似書籍的目錄,可以根據目錄的某個頁碼立即找到對應的內容。

  • 索引的優點:1. 天生排序。2. 快速查找。

  • 索引的缺點:1. 占用空間。2. 降低更新表的速度。

注意點:小表使用全表掃描更快,中大表才使用索引。超級大表索引基本無效。

索引從實現上說,分成 2 種:聚集索引和輔助索引(也叫二級索引或者非聚集索引)

從功能上說,分為 6 種:普通索引,唯一索引,主鍵索引,復合索引,外鍵索引,全文索引。

  • 普通索引:最基本的索引,沒有任何約束。

  • 唯一索引:與普通索引類似,但具有唯一性約束。

  • 主鍵索引:特殊的唯一索引,不允許有空值。

  • 復合索引:將多個列組合在一起創建索引,可以覆蓋多個列。

  • 外鍵索引:只有InnoDB類型的表才可以使用外鍵索引,保證數據的一致性、完整性和實現級聯操作。

  • 全文索引:MySQL 自帶的全文索引只能用于 InnoDB、MyISAM ,并且只能對英文進行全文檢索,一般使用全文索引引擎(ES,Solr)。

注意:主鍵就是唯一索引,但是唯一索引不一定是主鍵,唯一索引可以為空,但是空值只能有一個,主鍵不能為空。

InnoDB 通過主鍵聚簇數據,如果沒有定義主鍵且沒有定義聚集索引, MySql 會選擇一個唯一的非空索引代替,如果沒有這樣的索引,會隱式定義個 6 字節的主鍵作為聚簇索引,用戶不能查看或訪問

怎么建立索引

支持快速查找的數據結構有:順序數組、哈希、搜索樹。

順序數組、哈希、搜索樹

數組要求插入的時候保證有序,這樣查找的時候可以利用二分查找法達到 O(log(N)) 的時間復雜度,對范圍查詢支持也很好,但是插入的時候如果不是在數組尾部,就需要摞動后面所有的數據,時間復雜度為 O(N) 。所以有序數組只適合存儲靜態數據,例如幾乎很少變動的配置數據,或者是歷史數據。這里應該會有人有疑問:我用另外一種線性數據結構鏈表來替代數組不就可以解決數組插入因為要移動數據導致太慢的問題了么,要回答這個問題我們需要了解操作系統讀取文件的流程,磁盤 IO 是一個相對很慢的操作,為了提高讀取速度,我們應該盡量減少磁盤 IO 操作,而操作系統一般以 4kb 為一個數據頁讀取數據,而 MySQL 一般為 16kb 作為一個數據塊,已經讀取的數據塊會在內存進行緩存,如果多次數據讀取在同一個數據塊,則只需要一次磁盤 IO ,而如果順序一致的記錄在文件中也是順序存儲的,就可以一次讀取多個數據塊,這樣范圍查詢的速度也可以大大提升,顯然鏈表沒有這方面的優勢。


類似于 jdk 中的 hashmap ,哈希表通過一個特定的哈希函數將 key 值轉換為一個固定的地址,然后將對應的 value 放到這個位置,如果發生哈希碰撞就在這個位置拉出一個鏈表,由于哈希函數的離散特性,所以經過哈希函數處理后的 key 將失去原有的順序,所以哈希結構的索引無法滿足范圍查詢,只適合等值查詢的情況例如一些緩存的場景。


二叉樹在極端情況下會變成線性結構,也就是每個節點都只有左子節點或者只有右子節點,這樣就無法利用二分查找只能從第一個節點開始向后遍歷了,所以為了維持 O(log(N)) 的時間復雜度,我們需要在插入節點的時候對節點進行調整以保證樹的平衡,所以平衡二叉樹插入的時間復雜度也是 O(log(N)) ,二叉樹只有兩個子節點,如果數據量很大則樹就很高,樹的每一層一般不在同一個數據塊中存儲,為了盡量的減少磁盤讀寫次數,我們用N叉樹來代替二叉樹,在 MySQL 中這個N一般為 1200 ,這樣樹高是 4 的話也可以存儲億級別的數據,而且樹的前面兩層一般都在內存中, MySQL 中用到的 B+ 樹,一般用非葉子節點構建索引,而葉子節點用來存儲具體的值。

MySql 的索引使用 B+ 樹結構。在說 B+ 樹之前,先說說 B 樹,B 樹是一個多路平衡查找樹,相較于普通的二叉樹,不會發生極度不平衡的狀況,同時也是多路的。

B 樹的特點是:他會將數據也保存在非頁子節點。

B 樹的特點是

而這個特點會導致非頁子節點不能存儲大量的索引。

而 B+ Tree 就是針對這個對 B tree 做了優化。如下圖所示:

B+ Tree 優化

我們看到,B+ Tree 將所有的 data 數據都保存到了葉子節點中,非也子節點只保存索引和指針。

我們假設一個非頁子節點是 16kb,每個索引,即主鍵是 bigint,即 8b,指針為 8b。那么每頁能存儲大約 1000 個索引(16kb/ 8b + 8b).

而一顆 3 層的 B+樹能夠存儲多少索引呢?

 B+樹能存儲索引結構圖

通常 B+ 樹的高度在 2-4 層,由于 MySql 在運行時,根節點是常駐內存的,因此每次查找只需要大約 2 -3 次 IO。可以說,B+ 樹的設計,就是根據機械磁盤的特性來進行設計的。

知道了索引的設計,我們能夠知道另外一些信息:

  1. MySql 的主鍵不能太大,如果使用 UUID 這種,將會浪費 B+ 樹的非葉子節點。

  2. MySql 的主鍵最好是自增的,如果使用 UUID 這種,每次插入都會調整 B+樹,從而導致頁分裂,嚴重影響性能。

那么,如果項目中使用了分庫分表,我們通常都會需要一個主鍵進行 sharding,那怎么辦呢?

在實現上,我們可以保留自增主鍵,而邏輯主鍵用來作為唯一索引即可

Innodb中的索引數據結構是 B+ 樹,數據是有序排列的,從根節點到葉子節點一層層找到對應的數據

普通索引,也叫做輔助索引,葉子節點存放的是主鍵值。主鍵上的索引叫做聚集索引,表里的每一條記錄都存放在主鍵的葉子節點上。

當通過輔助索引select 查詢數據的時候,會先在輔助索引中找到對應的主鍵值,然后用主鍵值在聚集索引中找到該條記錄。舉個例子,用name=Alice來查詢的時候,會先找到對應的主鍵值是18 ,然后用18在下面的聚集索引中找到name=Alice的記錄內容是 77 和 Alice。


輔助索引聚集索引

表中每一行的數據,是組織存放在聚集索引中的,所以叫做索引組織表

InnoDB 中,有聚簇索引和普通索引之分

  • 聚簇索引根據主鍵來構建,葉子節點存放的是該主鍵對應的這一行記錄

  • 普通索引根據申明這個索引時候的列來構建,葉子節點存放的是這一行記錄對應的主鍵的值

而普通索引中還有唯一索引和聯合索引兩個特例,

  • 唯一索引在插入和修改的時候會校驗該索引對應的列的值是否已經存在

  • 聯合索引將兩個列的值按照申明時候的順序進行拼接后在構建索引

mysql索引存儲結構

數據是以行為單位存儲在聚簇索引里的,根據主鍵查詢可以直接利用聚簇索引定位到所在記錄,根據普通索引查詢需要先在普通索引上找到對應的主鍵的值,然后根據主鍵值去聚簇索引上查找記錄,俗稱回表

普通索引上存儲的值是主鍵的值,如果主鍵是一個很長的字符串并且建了很多普通索引,將造成普通索引占有很大的物理空間,這也是為什么建議使用 自增ID 來替代訂單號作為主鍵,另一個原因是 自增ID 在插入的時候可以保證相鄰的兩條記錄可能在同一個數據塊,而訂單號的連續性在設計上可能沒有自增ID好,導致連續插入可能在多個數據塊,增加了磁盤讀寫次數。

如果我們查詢一整行記錄的話,一定要去聚簇索引上查找,而如果我們只需要根據普通索引查詢主鍵的值,由于這些值在普通索引上已經存在,所以并不需要回表,這個稱為索引覆蓋,在一定程度上可以提高查詢效率,由于聯合索引上通過多個列構建索引,有時候我們可以將需要頻繁查詢的字段加到聯合索引里面,例如如果經常需要根據 name 查找 age 我們可以建一個 name 和 age 的聯合索引。

查詢的時候如果在索引上用了函數,將導致無法用到根據之前列上的值構建的索引,索引遵循最左匹配原則,所以如果需要查詢某個列的值中間是否包含某個字符串,將無法利用索引,如果有這種需求可以利用全文索引,而如果查詢是否以某個字符串開頭就可以,聯合索引根據第一個列查詢可以用到索引,僅僅根據第二個列將無法用到索引,查詢的時候用 IN 的效率高于 NOT = 。另外建議將索引的列設置為非空,這個和 NULL 字段的存儲有關,下文在分析。

索引查詢操作


select * from table where pId='11'

如上圖所示,從根開始,經過3次查找,就可以找到真實數據。如果不使用索引,那就要在磁盤上,進行逐行掃描,直到找到數據位置。顯然,使用索引速度會快。但是在寫入數據的時候,需要維護這顆B+樹的結構,因此寫入性能會下降! OK,接下來引入非聚簇索引!我們執行下面的語句

create index index_name on table(name);

大家注意看,會根據你的索引字段生成一顆新的B+樹。因此, 我們每加一個索引,就會增加表的體積, 占用磁盤存儲空間。然而,注意看葉子節點,非聚簇索引的葉子節點并不是真實數據,它的葉子節點依然是索引節點,存放的是該索引字段的值以及對應的主鍵索引(聚簇索引)。 如果我們執行下列語句

select * from table where name='lisi'

通過上圖紅線可以看出,先從非聚簇索引樹開始查找,然后找到聚簇索引后。根據聚簇索引,在聚簇索引的B+樹上,找到完整的數據! 那

什么情況不去聚簇索引樹上查詢呢?

還記得我們的非聚簇索引樹上存著該索引字段的值么。如果,此時我們執行下面的語句

select name from table where name='lisi'

此時結構圖如下


如上圖紅線所示,如果在非聚簇索引樹上找到了想要的值,就不會去聚簇索引樹上查詢。還記得,博主在《select的正確姿勢》提到的索引問題么:

當執行select col from table where col = ?,col上有索引的時候,效率比執行select * from table where col = ? 速度快好幾倍!

看完上面的圖,你應該對這句話有更深層的理解了。

那么這個時候,我們執行了下述語句,又會發生什么呢?

create index index_birthday on table(birthday);

看到了么,多加一個索引,就會多生成一顆非聚簇索引樹。因此,很多文章才說,索引不能亂加。因為,有幾個索引,就有幾顆非聚簇索引樹!你在做插入操作的時候,需要同時維護這幾顆樹的變化!因此,如果索引太多,插入性能就會下降!

索引存儲格式

有了以上的索引知識我們在來分析數據是怎么存儲的,InnoDB 存儲引擎的邏輯存儲結構從大到小依次可以分為:表空間、段、區、頁、行。

MySql 將數據按照頁來存儲,默認一頁為 16kb,當你在查詢時,不會只加載某一條數據,而是將這個數據所在的頁都加載到 pageCache 中,這個其實和 OS 的就近訪問原理類似

表空間作為存儲結構的最高層,所有數據都存放在表空間中默認情況下用一個共享表空間 ibdata1 ,如果開啟了 innodb_file_per_table 則每張表的數據將存儲在單獨的表空間中,也就是每張表都會有一個文件

表空間由各個段構成,InnoDB存儲引擎由索引組織的,而索引中的葉子節點用來記錄數據,存儲在數據段,而非葉子節點用來構建索引,存儲在索引段,而回滾段我們在后面分析鎖的時候在聊。區是由連續的頁組成,任何情況下一個區都是 1MB

一個區中可以有多個頁,每個頁默認為 16KB ,所以默認情況下一個區中可以包含64個連續的頁,頁的大小是可以通過 innodb_page_size 設置,頁中存儲的是具體的行記錄。一行記錄最終以二進制的方式存儲在文件里,我們要能夠解析出一行記錄中每個列的值,存儲的時候就需要有固定的格式,至少需要知道每個列占多少空間,而 MySQL 中定義了一些固定長度的數據類型,例如 int、tinyint、bigint、char數組、float、double、date、datetime、timestamp 等,這些字段我們只需要讀取對應長度的字節,然后根據類型進行解析即可,對于變長字段,例如 varchar、varbinary 等,需要有一個位置來單獨存儲字段實際用到的長度,當然還需要頭信息來存儲元數據,例如記錄類型,下一條記錄的位置等。下面我們以 Compact 行格式分析一行數據在 InnoDB 中是怎么存儲的。

  • 變長字段長度列表,該位置用來存儲所申明的變長字段中非空字段實際占有的長度列表,例如有3個非空字段,其中第一個字段長度為3,第二個字段為空,第三個字段長度為1,則將用 01 03 表示,為空字段將在下一個位置進行標記。變長字段長度不能超過 2 個字節,所以 varchar 的長度最大為 65535。

  • NULL 標志位,占 1 個字節,如果對應的列為空則在對應的位上置為 1 ,否則為 0 ,由于該標志位占一個字節,所以列的數量不能超過 255。如果某字段為空,在后面具體的列數據中將不會在記錄。這種方式也導致了在處理索引字段為空的時候需要進行額外的操作。

  • 記錄頭信息,固定占 5 字節,包含下一條記錄的位置,該行記錄總長度,記錄類型,是否被刪除,對應的 slot 信息等

  • 列數據 包含具體的列對應的值,加上兩個隱藏列,事務 ID 列和回滾指針列。如果沒有申明主鍵,還會增加一列記錄內部 ID。

InnoDB 的數據頁由以下 7 個部分組成:

  • 文件頭(File Header) 固定 38 個字節 (頁的位置,上一頁下一頁位置,checksum , LSN)

  • 數據頁頭( Page Header)固定 56 個字節 包含slot數目,可重用空間起始地址,第一個記錄地址,記錄數,最大事務ID等

  • 虛擬的最大最小記錄 (Infimum + Supremum Record)

  • 用戶記錄 (User Records) 包含已經刪除的記錄以鏈表的形式構成可重用空間

  • 待分配空間 (Free spaces) 未分配的空間

  • 頁目錄 (Page Directory) slot 信息,下面單獨介紹

  • 文件尾 (File Trailer) 固定8個字節,用來保證頁的完整性


索引的基礎知識 頁結構

頁目錄里維護多個 slot ,一個 slot 包含多個行記錄。每個 slot 占 2 個字節,記錄這個 slot 里的行記錄相對頁初始位置的偏移量。由于索引只能定位到數據頁,而定位到數據頁內的行記錄還需要在內存中進行二分查找,而這個二分查找就需要借助 slot 信息,先找到對應的 slot ,然后在 slot 內部通過數據行中記錄頭里的下一個記錄地址進行遍歷。每一個 slot 可以包含 4 到 8 個數據行。如果沒有 slot 輔助,鏈表本身是無法進行二分查找的。

排序

排序有好多種算法來實現,在 MySQL 中經常會帶上一個 limit ,表示從排序后的結果集中取前 100 條,或者取第 n 條到第 m 條,要實現排序,我們需要先根據查詢條件獲取結果集,然后在內存中對這個結果集進行排序,如果結果集數量特別大,還需要將結果集寫入到多個文件里,然后單獨對每個文件里的數據進行排序,然后在文件之間進行歸并,排序完成后在進行 limit 操作。沒錯,這個就是 MySQL 實現排序的方式,前提是排序的字段沒有索引。

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

select city,name,age from person where city='武漢' order by name limit 100  ;

使用 explain 發現該語句會使用 city 索引,并且會有 filesort . 我們分析下該語句的執行流程

    1.初始化 sortbuffer ,用來存放結果集

    2.找到 city 索引,定位到 city 等于武漢的第一條記錄,獲取主鍵索引ID

    3.根據 ID 去主鍵索引上找到對應記錄,取出 city,name,age 字段放入 sortbuffer

    4.在 city 索引取下一個 city 等于武漢的記錄的主鍵ID

    5.重復上面的步驟,直到所有 city 等于武漢的記錄都放入 sortbuffer

    6.對 sortbuffer 里的數據根據 name 做快速排序

    7.根據排序結果取前面 1000 條返回

這里是查詢 city,name,age 3個字段,比較少,如果查詢的字段較多,則多個列如果都放入 sortbuffer 將占有大量內存空間,另一個方案是只區出待排序的字段和主鍵放入 sortbuffer 這里是 name 和 id ,排序完成后在根據 id 取出需要查詢的字段返回,其實就是時間換取空間的做法,這里通過 max_length_for_sort_data 參數控制,是否采用后面的方案進行排序。

另外如果 sortbuffer 里的條數很多,同樣會占有大量的內存空間,可以通過參數 sort_buffer_size 來控制是否需要借助文件進行排序,這里會把 sortbuffer 里的數據放入多個文件里,用歸并排序的思路最終輸出一個大的文件。

以上方案主要是 name 字段沒有加上索引,如果 name 字段上有索引,由于索引在構建的時候已經是有序的了,所以就不需要進行額外的排序流程只需要在查詢的時候查出指定的條數就可以了,這將大大提升查詢速度。我們現在加一個 city 和 name 的聯合索引。

alter table person add index city_user(city, name);

這樣查詢過程如下:

1.根據 city,name 聯合索引定位到 city 等于武漢的第一條記錄,獲取主鍵索引ID

2.根據 ID 去主鍵索引上找到對應記錄,取出 city,name,age 字段作為結果集返回

3.繼續重復以上步驟直到 city 不等于武漢,或者條數大于 1000

由于聯合所以在構建索引的時候,在 city 等于武漢的索引節點中的數據已經是根據 name 進行排序了的,所以這里只需要直接查詢就可,另外這里如果加上 city, name, age 的聯合索引,則可以用到索引覆蓋,不行到主鍵索引上進行回表。

總結一下,我們在有排序操作的時候,最好能夠讓排序字段上建有索引,另外由于查詢第一百萬條開始的一百條記錄,需要過濾掉前面一百萬條記錄,即使用到索引也很慢,所以可以根據 ID 來進行區分,分頁遍歷的時候每次緩存上一次查詢結果最后一條記錄的 id , 下一次查詢加上 id > xxxx limit 0,1000 這樣可以避免前期掃描到的結果被過濾掉的情況。

InnoDB 存儲模型

InnoDB 通過一些列后臺線程將相關操作進行異步處理,如下圖所示,同時借助緩沖池來減小 CPU 和磁盤速度上的差異。

當查詢的時候會先通過索引定位到對應的數據頁,然后檢測數據頁是否在緩沖池內,如果在就直接返回,如果不在就去聚簇索引中通過磁盤 IO 讀取對應的數據頁并放入緩沖池。

一個數據頁會包含多個數據行。

緩存池通過 LRU 算法對數據頁進行管理,也就是最頻繁使用的數據頁排在列表前面,不經常使用的排在隊尾,當緩沖池滿了的時候會淘汰掉隊尾的數據頁。

從磁盤新讀取到的數據頁并不會放在隊列頭部而是放在中間位置,這個中間位置可以通過參數進行修。

緩沖池也可以設置多個實例,數據頁根據哈希算法決定放在哪個緩沖池。

InnoDB 在更新數據的時候會采用 WAL 技術,也就是 Write Ahead Logging ,這個日志就是 redolog 用來保證數據庫宕機后可以通過該文件進行恢復。這個文件一般只會順序寫,只有在數據庫啟動的時候才會讀取 redolog 文件看是否需要進行恢復。該文件記錄了對某個數據頁的物理操作,例如某個 sql 把某一行的某個列的值改為 10 ,對應的 redolog 文件格式可能為:把第5個數據頁中偏移量為99的位置寫入一個值 10 。

redolog 不是無限大的,他的大小是可以配置的,并且是循環使用的,例如配置大小為 4G ,一共 4 個文件,每個文件 1G 。首先從第一個文件開始順序寫,寫到第四個文件后在從第一個文件開始寫,類似一個環,用一個后臺線程把 redolog 里的數據同步到聚簇索引上的數據頁上。寫入 redolog 的時候不能將沒有同步到數據頁上的記錄覆蓋,如果碰到這種情況會停下來先進行數據頁同步然后在繼續寫入 redolog 。另外執行更新操作的時候,會先更新緩沖池里的數據頁,然后寫入 redolog , 這個時候真正存儲數據的地方還沒有更新,也就是說這時候緩沖池中的數據頁和磁盤不一致,這種數據頁稱為臟頁,當臟頁由于內存不足或者其他原因需要丟棄的時候,一定要先將該臟頁對應的redolog 刷新到磁盤里的真實數據頁,不然下次查詢的時候由于 redolog 沒有同步到磁盤,而查詢直接通過索引定位到數據頁就會查詢出臟數據。


更新的時候先從磁盤或者緩沖池中讀取對應的數據頁,然后對數據頁里的數據進行更改并生成 redolog 到對應的緩沖池(redolog buffer)進行緩存,當事務提交的時候將緩存寫入到 redolog 的物理磁盤文件上。這里由于操作系統的文件寫入 InnoDB 并沒有使用 O_DIRECT 直接寫入到文件,為了保證性能而是先寫入操作系統的緩存,之后在進行 flush ,所以事務提交的時候 InnoDB 需要在調用一次 fsync 的系統調用來確保數據落盤。為了提高性能 InnoDB 可以通過參數 innodb_flush_log_at_trx_commit 來控制事務提交時是否強制刷盤。

  • 1(默認值) ,事務每次提交都需要調用 fsync 進行刷盤,

  • 0 表示事務提交的時候不會調用 redolog 的文件寫入,通過后臺線程每秒同步一次,

  • 2 表示事務提交的時候會寫入文件但是只保證寫入操作系統緩存,不進行 fsync 操作。

redolog 文件只會順序寫,所以磁盤操作性能不會太慢,所以建議生產環境都設置為1,以防止數據庫宕機導致數據丟失。


在執行更新邏輯的時候還會寫入另外一個日志:undolog 。這個文件存儲在共享表空間中,也就是即使打開了 innodb_file_per_table 參數,所有的表的 undolog 都存儲在同一個文件里。該文件主要用來做事務回滾和 MVCC 。undolog 是邏輯日志,也就是他不是記錄的將物理的數據頁恢復到之前的狀態,而是記錄的和原 sql 相反的 sql , 例如 insert 對應 delete , delete 對應 insert ,update 對應另外一個 update 。事務回滾很好理解,執行相反的操作回滾到之前的狀態,而 MVCC 是指鏡像讀,當一個事務需要查詢某條記錄,而該記錄已經被其他事務修改,但該事務還沒提交,而當前事務可以通過 undolog 計算到之前的值。這里我們只需要知道和 redolog 一樣, undolog 也是需要在執行 update 語句的時候在事務提交前需要寫入到文件的。另外 undolog 的寫入也會有對應的 redolog ,因為 undolog 也需要持久化,通過 WAL 可以提高效率。這里可以總結下,在事務提交的時候要保證 redolog 寫入到文件里,而這個 redolog 包含 主鍵索引上的數據頁的修改,以及共享表空間的回滾段中 undolog 的插入。另外 undolog 的清理通過一個后臺線程定時處理,清理的時候需要判斷該 undolog 是否所有的事務都不會用到。

熟悉 MySQL 的都知道,他通過 binlog 來進行高可用,也就是通過 binlog 來將數據同步到集群內其他的 MySQL 實例。

binlog 和 redolog 的區別是

  • binlog是在存儲引擎上層 Server 層寫入的,他記錄的是邏輯操作,也就是對應的 sql 

  • redolog 記錄的底層某個數據頁的物理操作

  • redolog 是循環寫的

  • binlog 是追加寫的,不會覆蓋以前寫的數據。而binlog 也需要在事務提交前寫入文件。binlog 的寫入頁需要通過 fsync 來保證落盤,為了提高 tps ,MySQL 可以通過參數 sync_binlog 來控制是否需要同步刷盤,該策略會影響當主庫宕機后備庫數據可能并沒有完全同步到主庫數據。

由于事務的原子性,需要保證事務提交的時候 redolog 和 binlog 都寫入成功,所以 MySQL 執行層采用了兩階段提交來保證 redolog 和 binlog 都寫入成功后才 commit,如果一方失敗則會進行回滾。

下面我們理一下一條 update 語句的執行過程:

update person set age = 30 where id = 1;

1.分配事務 ID ,開啟事務,獲取鎖,沒有獲取到鎖則等待。

2.執行器先通過存儲引擎找到 id = 1 的數據頁,如果緩沖池有則直接取出,沒有則去主鍵索引上取出對應的數據頁放入緩沖池。

3.在數據頁內找到 id = 1 這行記錄,取出,將 age 改為 30 然后寫入內存

4.生成 redolog undolog 到內存,redolog 狀態為 prepare

5.將 redolog undolog 寫入文件并調用 fsync

6.server 層生成 binlog 并寫入文件調用 fsync

7.事務提交,將 redolog 的狀態改為 commited 釋放鎖


MySQL數據庫建立索引的事項及提高性能的手段

  1. 對查詢進行優化,應盡量避免全表掃描,首先應考慮在 where 及 order by 涉及的列上建立索引。

  2. 應盡量避免在 where 子句中對字段進行 null 值判斷,否則將導致引擎放棄使用索引而進行全表掃描,如:select id from t where num is null可以在num上設置默認值0,確保表中num列沒有null值,然后這樣查詢:select id from t where num=0

  3. 應盡量避免在 where 子句中使用!=或<>操作符,否則引擎將放棄使用索引而進行全表掃描。

  4. 應盡量避免在 where 子句中使用or 來連接條件,否則將導致引擎放棄使用索引而進行全表掃描,如:select id from t where num=10 or num=20可以這樣查詢:select id from t where num=10 union all select id from t where num=20

  5. in 和 not in 也要慎用,否則會導致全表掃描,如:select id from t where num in(1,2,3) 對于連續的數值,能用 between 就不要用 in 了:select id from t where num between 1 and 3

  6. 避免使用通配符。下面的查詢也將導致全表掃描:select id from t where name like ‘李%’若要提高效率,可以考慮全文檢索。

  7. 如果在 where 子句中使用參數,也會導致全表掃描。因為SQL只有在運行時才會解析局部變量,但優化程序不能將訪問計劃的選擇推遲到運行時;它必須在編譯時進行選擇。然而,如果在編譯時建立訪問計劃,變量的值還是未知的,因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描:select id from t where [email protected]可以改為強制查詢使用索引:select id from t with(index(索引名)) where num=@num

  8. 應盡量避免在 where 子句中對字段進行表達式操作,這將導致引擎放棄使用索引而進行全表掃描。如:select id from t where num/2=100應改為:select id from t where num=100*2

  9. 應盡量避免在where子句中對字段進行函數操作,這將導致引擎放棄使用索引而進行全表掃描。如:select id from t where substring(name,1,3)=’abc’ ,name以abc開頭的id應改為:select id from t where name like ‘abc%’

  10. 不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算,否則系統將可能無法正確使用索引。

  11. 在使用索引字段作為條件時,如果該索引是復合索引,那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引,否則該索引將不會被使用,并且應盡可能的讓字段順序與索引順序相一致。

  12. 不要寫一些沒有意義的查詢,如需要生成一個空表結構:select col1,col2 into #t from t where 1=0 這類代碼不會返回任何結果集,但是會消耗系統資源的,應改成這樣:create table #t(…)

  13. 很多時候用 exists 代替 in 是一個好的選擇:select num from a where num in(select num from b)用下面的語句替換:select num from a where exists(select 1 from b where num=a.num)

  14. 并不是所有索引對查詢都有效,SQL是根據表中數據來進行查詢優化的,當索引列有大量數據重復時,SQL查詢可能不會去利用索引,如一表中有字段sex,male、female幾乎各一半,那么即使在sex上建了索引也對查詢效率起不了作用。

  15. 索引并不是越多越好,索引固然可以提高相應的 select 的效率,但同時也降低了insert 及 update 的 效率,因為 insert 或 update 時有可能會重建索引,所以怎樣建索引需要慎重考慮,視具體情況而定。一個表的索引數最好不要超過6個,若太多則應考慮一些不常使用到的列上建的索引是否有 必要。

  16. 應盡可能的避免更新 clustered 索引數據列,因為 clustered 索引數據列的順序就是表記錄的物理存儲 順序,一旦該列值改變將導致整個表記錄的順序的調整,會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列,那么需要考慮是否應將該索引建為 clustered 索引。

  17. 盡量使用數字型字段,若只含數值信息的字段盡量不要設計為字符型,這會降低查詢和連接的性能,并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符,而對于數字型而言只需要比較一次就夠了。

  18. 盡可能的使用 varchar/nvarchar 代替 char/nchar ,因為首先變長字段存儲空間小,可以節省存儲空間,其次對于查詢來說,在一個相對較小的字段內搜索效率顯然要高些。

  19. 任何地方都不要使用 select * from t ,用具體的字段列表代替“*”,不要返回用不到的任何字段。

  20. 盡量使用表變量來代替臨時表。如果表變量包含大量數據,請注意索引非常有限(只有主鍵索引)。

  21. 避免頻繁創建和刪除臨時表,以減少系統表資源的消耗。

  22. 臨時表并不是不可使用,適當地使用它們可以使某些例程更有效,例如,當需要重復引用大型表或常用表中的某個數據集時。但是,對于一次性事件,最好使用導出表。

  23. 在新建臨時表時,如果一次性插入數據量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果數據量不大,為了緩和系統表的資源,應先create table,然后insert。

  24. 如果使用到了臨時表,在存儲過程的最后務必將所有的臨時表顯式刪除,先 truncate table ,然后 drop table ,這樣可以避免系統表的較長時間鎖定。

  25. 盡量避免使用游標,因為游標的效率較差,如果游標操作的數據超過1萬行,那么就應該考慮改寫。

  26. 使用基于游標的方法或臨時表方法之前,應先尋找基于集的解決方案來解決問題,基于集的方法通常更有效。

  27. 與臨時表一樣,游標并不是不可使用。對小型數據集使用 FAST_FORWARD 游標通常要優于其他逐行處理方法,尤其是在必須引用幾個表才能獲得所需的數據時。在結果集中包括“合計”的例程通常要比使用游標執行的速度快。如果開發時間允許,基于游標的方法和基于集的方法都可以嘗試一下,看哪一種方法的效果更好。

  28. 在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON ,在結束時設置 SET NOCOUNT OFF。無需在執行存儲過程和觸發器的每個語句后向客戶端發送DONE_IN_PROC 消息。

  29. 盡量避免大事務操作,提高系統并發能力。

  30. 盡量避免向客戶端返回大數據量,若數據量過大,應該考慮相應需求是否合理。


參考文章:

MySql 三大知識點——索引、鎖、事務 https://zhuanlan.zhihu.com/p/59764376

千萬級MySQL數據庫建立索引,提高性能的秘訣 https://zhuanlan.zhihu.com/p/80600549



轉載本站文章《再談mysql索引—索引建立、存儲結構、存儲格式、存儲模型》, 請注明出處:http://www.qsexmk.tw/html/DB/mysql/2019_1014_8172.html