1. MySQL的索引

1.1 概述

索引是通過某種算法，構建出一個數(shù)據模型，用于快速找出在某個列中有以特定值的行，不使用索引，MySQL必須從一條記錄開始讀完整個表，直到找出相關的行，表越大查詢數(shù)據所花的時間越多，如果表中查詢的列有一個索引，MySQL能夠快速達到一個位置去搜索數(shù)據文件，而不必查看所有數(shù)據，那么將會節(jié)省很大一部分時間。

1.2 分類

索引是存儲引擎用來快速查找記錄的一種數(shù)據結構：

• 按照實現(xiàn)的方式類分，主要有Hash索引和B+Tree索引。
• 按照功能劃分，單列索引（普通索引、唯一索引、主鍵索引），組合索引，全文索引，空間索引

1.3 特點

優(yōu)點

• 大大加快數(shù)據查詢的速度；
• 使用分組和排序進行數(shù)據查詢時，可以顯著減少查詢時分組和排序的時間
• 創(chuàng)建唯一索引，能夠保證數(shù)據庫表中每一行數(shù)據的唯一性
• 在實現(xiàn)數(shù)據的參考完整性方面，可以加速表和表之間的連接。

缺點

• 創(chuàng)建索引和維護索引需要消耗時間，并且隨著數(shù)據量的增加，時間也會增加
• 索引需要占據磁盤空間
• 對數(shù)據表中的數(shù)據進行增加、修改、刪除時，索引也要動態(tài)的維護，降低了維護的速度

創(chuàng)建索引的原則

• 更新頻繁的列不應該設置為索引
• 數(shù)據量小的表不要使用索引
• 重復數(shù)據多的字段不應設置為索引（一般來說，重復的數(shù)量超過15%就不該建索引）
• 首先應考慮對WHERE和ORDER BY涉及的列上建立索引

2. 存儲引擎——MySQL的核心

存儲引擎就是存儲數(shù)據、建立索引、更新、查詢數(shù)據等技術的實現(xiàn)方式。存儲引擎是基于表的，而不是基于庫的，所以存儲引擎也可以被稱為表類型。

2.1 分類

• MyISAM：MySQL5.5之前的默認數(shù)據庫引擎，最為常用。擁有較高的插入，查詢速度，但不支持事務。
• InnoDB：事務型速記的首選引擎，支持ACID事務，支持行級鎖定，MySQL5.5成為默認數(shù)據庫引擎。
• Memory：所有數(shù)據置于內存的存儲引擎，擁有極高的插入，更新和查詢效率。但是會占用和數(shù)據量成正比的內存空間，并且其內容會在MySQL重新啟動會丟失。
• Archive：非常適合存儲大量的獨立的、作為歷史紀錄的數(shù)據。因為它們不經常被讀取。Archive擁有高效的插入速度，但其對查詢的支持相對較差。
• Federated：將不同的MySQL服務器聯(lián)合起來，邏輯上組成一個完整的數(shù)據庫。非常適合分布式應用。
• CSV：邏輯上由都好分割數(shù)據的存儲引擎。他會在數(shù)據庫子目錄里為每個數(shù)據表創(chuàng)建一個.csv文件。這是一種普通文本文件，每個數(shù)據行占用一個文本行。CSV存儲引擎不支持索引。
• BlockHole：黑洞引擎，寫入的任何數(shù)據都會消失，一般用于記錄binlog做賦值的中繼。
• ERFORMANCE_SCHEMA：該存儲引擎主要用于收集數(shù)據庫服務器性能參數(shù)。

3. MyISAM和InnoDB

MyISAM和InnoDB都是MySQL數(shù)據庫中常見的存儲引擎，各自特點和使用場景如下：

MyISAM

特點

• 基于表格的存儲引擎，對每個表對應三個文件，.frm文件存儲表結構定義，.MYD文件存儲數(shù)據，MYI文件存儲索引。
• 不支持事務，也不支持外鍵。
• 支持全文索引（Full-Text-Indexing），適合于搜索場景
• 讀操作比寫操作效率高，適用于讀多寫少的場景。

使用場景

• 非事務性的應用，例如博客、新聞網站等；
• 對數(shù)據的讀操作頻繁，寫操作相對較少的場景；
• 對全文搜索需求較多的場景；

InnoDB

MySQL默認的事務型引擎。

特點

• 提供了對事務的支持（ACID兼容），具有提交、回滾和崩潰恢復的能力；
• 支持行級鎖定，提高了并發(fā)性能；
• 支持外鍵約束。
• 采用聚合索引（Clustered Indexing），數(shù)據按照主鍵的順序存儲，可以提高查詢性能。

使用場景

• 需要事務支持的應用，例如電子商務網站、金融系統(tǒng)等。
• 需要較高并發(fā)性能的應用，例如論壇、社交網絡等。
• 需要支持外鍵約束的應用。

在存儲引擎時，需要根據應用的具體需求來決定使用哪種引擎。如果是簡單的應用，只需要基本的增刪改查功能，可以選擇MyISAM。

如果需要支持事務、并發(fā)性能較高或者有復雜的數(shù)據關系，建議選擇InnoDB。

4. 事務

4.1 特性(ACID)

• 原子性(Atomicity)：事務開始后所有操作，要么全部完成，要么全部不做，不可能停止在中間環(huán)節(jié)。事務執(zhí)行過程中出錯，會回滾到事務開始前的狀態(tài)。即事務是一個整體。
• 一致性(Consistecy)：事務開始前和結束后，數(shù)據庫的完整性約束沒有被破壞。
• 隔離性(Isolation)：同一時間，只允許一個事務請求同一數(shù)據，不同的事務之間彼此沒有任何干擾。
• 持久性(Durability)：事務完成后，事務對數(shù)據的所有更新將被保存到數(shù)據庫，不能回滾。

4.2 事務靠什么保證

• 原子性：由undo log日志保證，它記錄了需要回滾的日志信息，回滾時撤銷一致性的SQL；
• 一致性：由其他三大特性共同保證，是事務的目的；
• 隔離性：由MVCC保證。
• 持久性：由redo log日志和內存保證，MYSQL修改數(shù)據時內存和redo log會記錄操作，宕機時可恢復。

4.3 undo log和redo log的區(qū)別

緩沖池（buffer pool）：主內存中的一個區(qū)域，里面可以緩存磁盤上經常操作的真實數(shù)據，在執(zhí)行增刪改操作時，先操作緩沖池中的數(shù)據（若緩沖池沒有數(shù)據，則從主磁盤加載并緩存），以一定頻率刷新到磁盤，從而減少磁盤IO，加快處理速度。

數(shù)據頁（page）：是InnoDB存儲引擎磁盤管理的最小單元，每個頁的大小默認為16KB，液中存儲的是行數(shù)據。

redo log：

• 重做日志，記錄的是事務提交時數(shù)據頁的物理修改，是用來實現(xiàn)事務的持久性。
• 該日志文件由兩部分組成：重做日志緩沖（redo log buffer）以及重做日志文件（redo log file），前者是在內存中，后者在磁盤中。當事務提交之后會把所有修改信息都存到該日志文件中，用于在刷新臟頁到磁盤，發(fā)生錯誤時，進行數(shù)據恢復使用。

undo log：

• 回滾日志，用于記錄數(shù)據被修改前的信息，作用包含兩個：提供回滾和MVCC（多版本并發(fā)控制）。undo log和redo log記錄物理日志不一樣，它是邏輯日志。
• 可以認為當delete一條記錄時，undo log中會記錄一條對應的Insert記錄，反之亦然
• 當update一條記錄時，他記錄一條對應相反的update的記錄。當執(zhí)行rollback時，就可以從undo log中的邏輯記錄讀取到相應的內容并進行回滾。
• undo log可以實現(xiàn)事物的一致性和原子性
  事物的隔離性是如何保證的

鎖：排他鎖（如一個事務獲取了一個數(shù)據行的排他鎖，其他事務就不能再獲取該行的其他鎖）

MVCC：多版本并發(fā)控制

4.4 事務的隔離級別

高并發(fā)情況下，并發(fā)事務會產生臟讀、不可讀重復、幻讀等問題，這時需要用隔離級別來控制。

• 讀未提交：允許一個事務讀取另一個事務沒有提交的數(shù)據，而未提交的事務可能發(fā)生回滾，出現(xiàn)臟讀，不可重復度、幻讀。
• 讀提交：只允許事務讀取另一個事務已提交的數(shù)據可能會出現(xiàn)不可重復度，幻讀。
• 可重復讀：確保同一字段多次讀取結果一致，可能出現(xiàn)幻讀。
• 可串行化：所有事務逐次執(zhí)行，沒有并發(fā)問題。

InnoDB默認隔離級別為可重復讀級別，分為快照讀和當前讀，并且通過間隙鎖解決了幻讀問題。

4.5 事務的并發(fā)問題

臟讀：事務A讀取了事務B更新的數(shù)據，然后事務B回滾操作，那么事務A讀取到的數(shù)據是臟數(shù)據。

不可重復讀：事務A多次讀取同一數(shù)據，事務B在事務A多次讀取的過程中，對數(shù)據做了更新并提交，導致事務A多次讀取同一數(shù)據時，結果不一致。

幻讀：系統(tǒng)管理員A將數(shù)據庫中所有學生的成績從具體分數(shù)改為ABCDE等級，但是系統(tǒng)管理員B就在這時插入了一條具體分數(shù)的記錄，當系統(tǒng)管理員A改結束后發(fā)現(xiàn)還有一條記錄沒有改過來，就好像發(fā)生了幻覺一樣，就成為幻讀。

如何解決臟讀、幻讀、不可重復讀

• 臟讀：隔離級別設置為讀提交、可重復讀、串行化可解決臟讀
• 不可重復讀：隔離級別設置為可重復讀、串行化可以解決不可重復讀
• 幻讀：隔離級別為串行化可以解決幻讀、通過MVCV + 區(qū)間鎖可以解決幻讀

4.6 快照讀和當前讀

• 快照讀：讀取的是當前數(shù)據的可見版本，可能是會過期數(shù)據，不加鎖的SELECT就是快照讀。
• 當前讀：讀取的是數(shù)據的最新版本，并且當前讀返回的記錄都會上鎖，保證其他事務不會并發(fā)修改這條記錄。如UPDATE，DELETE，INSERT，SELECT FOR UNDATE（排他鎖），SELECT LOCKIN SHARE MODE（共享鎖）都是當前讀。

4.7 MVCC

MVCC是多版本并發(fā)控制，為每次事務生成一個新版本數(shù)據，每個事務都有自己的版本，從而不加鎖就解決讀寫沖突，這種讀為快照讀。只在讀已提交和可重復讀中生效。

實現(xiàn)的原理由以下四項保證：

• undo log日志：記錄數(shù)據歷史版本
• readView：事務進行快照度時動態(tài)生成產生的視圖，記錄了當前系統(tǒng)中活躍的事務id，控制哪個歷史版本對當前事務可見。
• 隱藏字段DB_TRC_ID：最近修改記錄的事務ID
• 隱藏字段DB_Roll_PTR:回滾指針，配合undolog指向數(shù)據的上一個版本

5. 鎖機制

5.1 定義

鎖是計算機協(xié)調多個進程或線程并發(fā)訪問某一資源的機制（避免競爭）。在數(shù)據庫中，除傳統(tǒng)的計算資源（如CPU，RAM，I/O等）的爭用外，數(shù)據也是一種供許多用戶共享的資源。

鎖機制能保證數(shù)據并發(fā)訪問的一致性、有效性，同時也影響數(shù)據庫并發(fā)訪問性能。

5.2 分類

根據對數(shù)據操作的粒度

• 表鎖：操作時，會鎖定整個表
• 行鎖：操作時，會鎖定當前操作行
• 表級鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據的應用，如Web應用。
• 行級鎖更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據，同時又有并發(fā)查詢的應用，如一些在線事務處理（OLTP）系統(tǒng)。

根據對數(shù)據操作的類型

• 讀鎖（共享鎖）：針對同一份數(shù)據，多個讀操作可以同時進行而不會互相影響
• 寫鎖（排它鎖）：當前操作沒有完成之前前，會阻斷其他寫鎖和讀鎖。

6. 日志

分類：錯誤日志、二進制日志、查詢日志、慢查詢日志

6.1 錯誤日志

• 錯誤日志是MySQl中最重要的日志之一，記錄了當MySQL啟動個停止時，以及服務器在運行過程中發(fā)生任何嚴重錯誤時的相關信息，當數(shù)據庫出現(xiàn)任何故障導致無法正常使用時，可以首先查看錯誤日志。
• 該日志默認是開啟的，默認存放目錄是mysql的數(shù)據目錄，默認的日志文件名為hosyname.err(hostname為主機名)。
• 查看日志位置指令

SHOW variables LIKE 'log_error%'

6.2 二進制日志

二進制日志（BINLOG）記錄了所有的DDL語句和DML語句，但不包括DQL語句。此日志對災難時的數(shù)據恢復起著極其重要的作用，MySQL的主從復制就是通過改BINLOG實現(xiàn)的。

二進制日志在MySQL8默認開啟，低版本需要通過配置文件開啟，并配置MySQL日志的格式。

Windows系統(tǒng)：my.ini Linux系統(tǒng)： my.cnf

6.3 查詢日志

查詢日志中記錄了客戶端的所有操作語句，而二進制日志不包含查詢數(shù)據的SQL語句。

默認情況下，查詢日志是未開啟的。

6.4 慢查詢日志

慢查詢日志記錄了所有執(zhí)行時間超過參數(shù)long_query_time設置值并且掃描記錄數(shù)不小于min_examined_row_limit的所有SQL語句的日志。long_query_time默認為10秒，最小為0，精度可以到微秒。

7. 索引

7.1 定義

索引（index）是幫助MySQL高效的獲取數(shù)據的數(shù)據結構（有序）。在數(shù)據之外，數(shù)據庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據結構（B+樹），這些數(shù)據結構以某種方式指向數(shù)據，這樣就可以在這些數(shù)據結構上實現(xiàn)高級查找算法，這種數(shù)據結構就是索引。這樣可以提高數(shù)據檢索的效率，降低數(shù)據庫的IO成本（不需要全表掃描）。通過索引列對數(shù)據進行排序，降低數(shù)據排序的成本，降低了CPU的消耗。

B樹與B+樹對比

• 階數(shù)更多，路徑更短
• 磁盤讀寫代價B+樹更低，非葉子節(jié)點只存儲指針，葉子階段存儲數(shù)據
• 查詢效率B+樹更加穩(wěn)定
• B+樹便于掃庫和區(qū)間查詢，非葉子節(jié)點是一個雙向鏈表

7.2 聚簇索引和非聚簇索引

7.2.1 聚簇索引

• 含義：將數(shù)據存儲與索引放到了一起，索引結構的葉子節(jié)點保存了行數(shù)據
• 特點：必須有，而且只有一個

7.2.2 二級索引（非聚簇索引）

• 含義：將數(shù)據與索引分開存儲，索引結構的葉子節(jié)點關聯(lián)的是對應的主鍵
• 特點：可以存在多個

7.2.3 聚簇索引選取規(guī)則

• 如果存在主鍵，主鍵索引就是聚簇索引
• 如果不存在主鍵，將使用第一個唯一（UNIQUE）索引作為聚簇索引。
• 如果表沒有主鍵，或沒有適合的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚簇索引。

7.2.4 回表查詢

（首先介紹聚簇索引和非聚簇索引，然后介紹回表查詢）

通過二級索引找到對應的主鍵值，到聚簇索引中查找整行數(shù)據，這個過程就是回表。

7.3 覆蓋索引

覆蓋索引是指查詢使用了索引，并且需要返回的列在該索引中已經全部能夠找到。

• 使用id查詢，直接走聚簇索引，一次索引掃描，直接返回數(shù)據，性能高；
• 如果返回的列中沒有創(chuàng)建索引，有可能會觸發(fā)回表查詢，盡量避免使用select *；

（可以使用索引覆蓋解決MySQL超大分頁）

7.4 MySQL超大分頁處理

• 在數(shù)據量比較大時，如果進行l(wèi)imit分頁查詢，在查詢時，越往后分頁查詢的效率越低。
• 優(yōu)化思路：一般分頁查詢時，通過創(chuàng)建覆蓋索引能夠比較好的提高性能，可以通過覆蓋索引加子查詢形式進行優(yōu)化。

7.5 創(chuàng)建索引的原則

• 針對數(shù)據量較大，且查詢比較頻繁的表建立索引（單表超過10萬數(shù)據，增加用戶體驗）[重要]
• 針對于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引[重要]
• 盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度越高，使用索引的效率越高
• 如果是字符串類型的字段，字段的長度較長，可以針對于字段的特點，建立前綴索引
• 盡量使用聯(lián)合索引，減少單列索引，查詢時，聯(lián)合索引很多時候可以覆蓋索引，避免回表，提高查詢效率【重要】
• 要控制索引的數(shù)量，索引并不是多多益善，索引越多，維護索引結構的代價也就越大，會影響增刪改的效率【重要】
• 如果索引列不能存儲NULL值，在創(chuàng)建表時使用NOT NULL約束。當優(yōu)化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用于查詢

7.6 什么情況下索引會失效？

• 違反最左前綴法則
• 范圍查詢右邊的列，不能使用索引
• 不要再索引列上進行運算操作，索引將會失效
• 隱式類型轉換：字符串不加單引號，造成索引失效（類型失效）
• 以%開頭的Like模糊查詢，索引失效
• 使用Select * ，索引會失效
• 使用OR操作：查詢條件使用or關鍵字，其中一個字段沒有創(chuàng)建索引，則會導致整個查詢語句索引失效； or兩邊為“>”和“<”范圍查詢時，索引失效。
• 兩列作比較：兩列數(shù)據做比較，即便兩列都創(chuàng)建了索引，索引失效
• 不等于比較：查詢條件使用不等進行比較時，需要慎重，普通索引會查詢結果集占比較大時，索引失效
• is not null：查詢條件使用is null時正常走索引，使用is not null時，索引失效。

8. 優(yōu)化

SQL優(yōu)化一般可以從設計、查詢、索引和存儲四方面進行。

8.1 如何定位慢查詢？

• 采用運維工具（Skywalking），可以檢測出是哪個接口，最終找到SQL問題
• 在數(shù)據庫中開啟慢日志查詢，設置閾值（可以設置為2秒），一旦SQL執(zhí)行超過閾值就hi記錄到日志中。

8.2 一個SQL語句執(zhí)行很慢，如何分析

可以使用MySQL自帶的分析工具EXPLAIN或者DESC

通過key和key_len檢查是否命中了索引（索引本身存在是否失效的情況）

通過type字段查看sql是否有進一步的優(yōu)化空間，是否存在全索引掃描或全盤掃描

通過extra建議判斷，是否出現(xiàn)了回表的情況，如果出現(xiàn)了，可以嘗試添加索引或修改返回字段來修復。

• possible_key：當前sql可能會使用到的索引
• key：當前sql實際命中的索引
• key_len：索引占用的大小
• Extra：額外的優(yōu)化建議

type：這條SQL的連接類型，性能由好到差依次為：NULL，system，const，eq_ref，ref，range，index，all

• system：查詢系統(tǒng)中的表
• const：根據主鍵查詢
• eq_ref：主鍵索引查詢或唯一索引查詢
• ref：索引查詢
• range：范圍查詢
• index：索引樹掃描
• all：全盤掃描

8.3 SQL優(yōu)化的經驗

表的設計優(yōu)化

參考阿里開發(fā)手冊《嵩山版》

• 比如設置合適的數(shù)值（tinyint int bigint），要根據實際情況選擇
• 比如設置合適的字符串類型（char和varchar）char定長效率高，varchar可變長度，效率稍低

索引優(yōu)化[參考優(yōu)化創(chuàng)建原則和索引失效]

SQL語句優(yōu)化

• SELECT語句無比指定字段名稱（避免直接使用SELECT *）
• SQL語句要避免造成索引失效的寫法
• 盡量用UNION ALL代替UNION UNION會多一次過濾，效率低
• 避免在where子句中對字段進行表達式操作
• Join優(yōu)化：能用INNERJOIN就不用LEFT JOIN RIGHT JOIN，如必須使用一定要以小表為驅動，內連接會對兩個表進行優(yōu)化，優(yōu)先把小表放到外面，把大表放到里面。LEFT JOIN或RIGHT JOIN，不會重新調整順序。

主從復制、讀寫分離

如果數(shù)據庫的使用場景讀的操作比較多的時候，為了避免寫的操作所造成的性能影響，可以采用讀寫分離的架構，讀寫分離解決的是數(shù)據庫的寫入，影響了查詢的效率。

分庫分表

8.4 主從同步原理

MySQL主從復制的核心是二進制bin log（記錄DDL和DML）

• 主從在事務提交時會把數(shù)據變更記錄在二進制日志文件bin log中
• 從庫讀取主庫的二進制日志文件bin log，寫入到從庫的中繼日志Relay log
• 從庫重做中繼日志中的事件，將改變反應它自己的數(shù)據

8.5 分庫分表

時機

• 前提：項目業(yè)務數(shù)據逐漸增多，或業(yè)務發(fā)展比較迅速（單表的數(shù)據量達1000W或20G以后）
• 優(yōu)化已解決不了性能問題（主從讀寫分離，查詢索引…）
• IO瓶頸（磁盤IO，網絡IO）、CPU瓶頸（聚合查詢、連接數(shù)太多）

拆分策略

垂直拆分

垂直分庫：以表為依據，根據業(yè)務將不同表拆分到不同庫中

特點

• 將業(yè)務對數(shù)據分級管理、維護、監(jiān)控、擴展
• 在高并發(fā)下，提高磁盤IO和數(shù)據量連接數(shù)

垂直分表：以字段為依據，根據字段屬性將不同字段拆分到不同表中

特點

• 冷數(shù)據分離
• 減少IO過度爭搶，兩表互不影響

水平拆分

水平分庫：將一個庫的數(shù)據拆分到多個庫中

特點

• 解決了單庫大數(shù)量，高并發(fā)的性能瓶頸問題
• 提高了系統(tǒng)的穩(wěn)定性和可用性

水平分表：將一個表的數(shù)據拆分到多個表中（可以在同一個庫內），按照記錄分表

特點

• 優(yōu)化單一表數(shù)量過大而產生的性能問題
• 避免IO爭搶并減少鎖表的幾率

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。