### 引擎 **InnoDB**：適用于處理大數據，崩潰后可以利用事務日志很好地恢復；支持聚簇、B-Tree和全文索引，不支持Hash索引；支持數據緩存（訪問數據時，表和索引數據會被緩存在緩沖池）、外鍵、行鎖、事務和MVCC；可以混合查詢InnoDB引擎的表和其他引擎的表；表文件：表名.frm（表結構文件）、表名.ibd（數據和索引文件）；速度較MyISAM慢； **MyISAM**：查詢效率很高，適合大量讀場景；支持B-Tree和全文索引，不支持聚簇、Hash索引；不支持數據緩存、外鍵、事務、行鎖（支持表鎖）；表文件：表名.frm（表結構文件）、表名.myd（數據文件）、表名.myi（索引文件）； **其他引擎**：**ARCHIVE**：檔案，適用于存儲大量獨立的作為歷史數據的數據，插入快，查詢差；不支持索引，存儲大小無限制，數據壓縮較好，不支持delete和update；**PERFORMANCE\_SCHEMA**：性能，專注收集MySQL運行性能數據；數據在內存中，重啟后丟失；大多數表只讀，用戶不能創建；**MEMORY**：內存，存儲臨時不重要的數據，例如作為緩存，適合大量讀的情形；數據在內存中，重啟后丟失；**CSV**：數據存儲在CSV文件；**FEDERATED**：聯盟，適用于分布式；MRG\_MYISAM：Merge多個相同定義的MyISAM表；**BLACKHOLE**：黑洞，像黑洞一樣接受數據但不存儲，適用于轉發和過濾給Slave服務器； * * * #### 索引 **概念**：優點：減少I/O次數，加快檢索、分組和排序速度；缺點：索引也是表，會占用磁盤空間，維護需要時間成本，降低新增修改數據效率，創建索引時需要對表加鎖； **索引類型**：主鍵索引（不可重復，不可為空）、唯一索引（不可重復，可以空）、普通索引（無限制）、全文索引（對大文本text、blob的索引）、空間索引（GIS數據）、組合索引（對多列建立的索引，遵循最左前綴原則，ABC索引相當于建了A、AB、ABC3個索引）； **索引方法**：**哈希索引**：只有內存存儲引擎才支持；實現原理：根據索引列的值計算hashcode，在hashcode的位置存儲該行數據的物理位置實現；一個值對應一個hashcode，所以不支持排序和范圍查詢；**全文索引**：僅用于MyISAM和InnoDb（5.6后支持）；實現原理：對大文本字段分詞，生成一份單詞清單，索引時根據單詞清單來；使用：不能like，使用match..against查詢；5.7前只支持英文，5.7后通過ngram插件支持中文；查詢字符串不能太短，至少4字節；**BTree索引**：每個節點包含索引鍵和數據，可以使用二分查找；**B+Tree索引**：數據都在葉子節點，其他節點只有索引鍵，磁盤讀寫代價更低； **索引實現結構**：主鍵索引為主索引，其他為輔助索引；**MyISAM實現非聚簇索引**：索引順序與數據物理排列順序無關，數據表和索引表分表存儲，主索引和輔助索引葉子節點的key都存儲鍵對應的數據物理地址；**InnoDb實現聚簇索引**：聚簇索引的順序就是數據的物理存儲順序，數據在主索引中存儲，主索引葉子節點的key存儲鍵對應的數據，輔助索引葉子節點的key存儲主鍵值；**沒有主鍵：**一個表只有一個聚簇索引，沒有主鍵，會用一個唯一且不為空的索引列做為主鍵，成為此表的聚簇索引，如果沒有這樣的索引，InnoDB會隱式定義一個主鍵來作為聚簇索引； **索引使用**：**什么時候用**：主鍵自動建索引，查詢條件，排序，外鍵，關聯字段應該建索引；**什么時候不用**：經常增刪改的列，大量重復的列，記錄太少的表；**索引失效情況**：組合索引的NULL列、like %xx%、索引列有函數和表達式、使用不等于(<>!=，但主鍵和整型不會失效)、IS (NOT) NULL、字段類型不一致（包括字符串不加單引號）、OR（非每個條件都有索引時）；**索引優化**：最左前綴、模糊查詢優化、全文索引、短索引（對前N個字符建索引）； * * * #### 鎖 **分類**：**行鎖**：只能作用在索引上；**間隙鎖**：鎖索引前面的間隙，開區間如（3，5），間隙鎖是不互斥的，RU和RC隔離級別沒有間隙鎖；**臨鍵鎖**：行鎖+間隙鎖，即左開右閉的區間如（3，5\]；**共享鎖S/排他鎖X**：都是行鎖，共享鎖用于并發讀，排他鎖用于更新刪除（不包括插入），事務請求時會根據隔離級別和數據是否存在可能獲取到行鎖、間隙鎖、臨建鎖之一；**意向共享鎖IS/意向排他鎖IX**：屬于表鎖，用于判斷鎖沖突；**插入意向鎖**：特殊的間隙鎖，鎖住一行數據，用于并發插入； **加鎖分析**：不同隔離級別、查詢記錄是否存在，存在多條還是一條，加鎖情況不一樣； 2個事務可以同時獲得IX鎖、間隙鎖，但不能同時獲取X鎖；A事務獲取了間隙鎖，B事務插入的數據在間隙區間內時B獲取不到插入鎖； 查詢時若記錄不存在則獲取間隙鎖，存在則獲取行鎖；A事務加了S鎖則B事務可讀不可改；A事務加了X鎖則B事務不可讀不可改； select...where id = ?：快照讀，不加鎖；select...lock in share mode：當前讀，加S鎖（若有多條記錄還會對區間加間隙鎖）；select...for update：當前讀，加X鎖（若有多條記錄還會對區間加間隙鎖）； 隔離級別RU和RC，無論條件列上是否有索引都不會鎖表，只鎖行；RR和串行化通過行鎖+間隙鎖實現鎖表；事務獲取X/S鎖前一定會對表加上IX/IS鎖； * * * #### 事務 **事務命令**：BEGIN、COMMIT、SAVEPOINT（設置回滾點）、ROLLBACK（默認回滾到BEGIN）、ROLLBACK TO SAVEPOINT（部分回滾，回滾到指定點）、SELECT @@AUTOCOMMIT/SET AUTOCOMMIT（查看設置自動提交）、SET TRANSACTION ISOLATION LEVEL（設置隔離級別）； **事務并發問題**：**臟讀**：A讀了B更新的數據，B回滾后是臟數據；**不可重復讀**：A多次讀取同一數據，B過程中更新并提交了數據，導致讀取結果不一致；**幻讀**：類似不可重復讀，不可重復讀側重于修改，幻讀側重于新增或刪除，導致少讀或多讀了數據； **事務隔離級別**：描述事務處理之間的相互影響。**讀未提交**：A事務可讀到B事務已更新未提交的數據，存在3個問題；**讀提交**：A事務只能讀到B事務已更新已提交的數據，只解決了臟讀；**可重復讀**：默認級別，A事務不能讀到B事務已更新已提交的數據，使用MVCC加版本控制，select不更新版本號，讀的歷史版本數據是快照讀，insert/update/delete會更新版本號是當前讀，解決了臟讀和不可重復讀；**串行化**：會鎖表，3個問題都解決了，但并發低； **MVCC**：多版本并發控制協議，大多數情況下代替行鎖，通過MVCC+行鎖提高并發，降低系統開銷；**實現原理**：每行記錄增加2個隱藏列：創建版本號和刪除版本號（即事務ID）；select：查詢創建版本小于等于當前事務版本并且刪除版本未定義或大于當前版本的行數據；delete：更新當前行的刪除版本號為當前事務ID；insert：更新當前行的創建版本號為當前事務ID，刪除版本號為undefined；update：先拷貝insert，再delete舊行； **分布式事務**：允許多個數據庫實例參與一個全局事務；**XA**：分布式事務，包括應用程序AP、資源管理器RM、事務管理器TM；MySQL**命令**：采用2PC，XA START/END/PREPARE/COMMIT/ROLLBACK xid（開啟、表示事務內SQl完成、準備、提交、回滾事務）; **快照讀**：**快照讀**：簡單的select，通過mvcc實現；**當前讀**：select ... lock in share mode、select ... for update、insert、update、delete，通過行鎖和間隙鎖實現； * * * #### 日志 **分類**：**錯誤日志**：默認開啟，文件名稱為hostname.err，記錄服務器啟動關閉、運行錯誤、事件信息等；**查詢日志**：默認關閉，記錄用戶所有的增刪改查操作，耗性能和IO；**慢日志**：默認關閉，記錄執行時間超過指定時間的查詢語句，用于性能定位；**事務日志**：InnoDb特有，持久化到磁盤，提高事務效率（改表數據只改內存，記事務日志，后續通過日志慢慢刷到磁盤），崩潰恢復，采用追加寫；**二進制日志**：默認關閉，變更日志，記錄修改數據的SQL語句及時間、數據等信息； **事務日志**：InnoDb引擎實現，包括**redo log**：重做日志，提供前滾操作，物理日志，記錄數據頁的物理修改，而不是行數據的修改；**undo log**：回滾日志，提供回滾操作，邏輯日志，根據每行數據進行記錄（反著記，insert時undolog記錄delete）；刷盤：redo/undolog buffer到os buffer，調用fsync將os buffer寫到磁盤redo/undolog file，2個階段的策略可通過參數控制（每次提交時刷，每秒刷，buffer內存超過一半刷等）； **二進制日志**：數據庫實現，也可用于數據庫恢復，3種記錄方式：statement（記錄SQL）、row（記錄改的數據行）、mixed（簡單的記SQL，復雜的記行）；查看日志：show binlog events或者使用mysqlbinlog工具； * * * #### 緩存 **存儲**：Key（SQL+數據庫+客戶端協議）Value（緩存結果）hash表存儲在內存中；相同SQL直接取緩存；**緩存失效**：對數據和表結構的任何修改會清空表的緩存，表數據修改中提交前緩存失效；**不存緩存**：包含不確定查詢結果時不會存緩存（like、now()等）；**不使用緩存**：事務ID比當前數據庫最大事務ID大的才使用緩存；InnoDB中有加鎖操作的事務不使用緩存； * * * #### 備份 **分類**：完全備份、部分備份；全量備份、增量備份、差異備份；熱備、溫備、冷備；物理備份、邏輯備份；**備份策略**：全量+差異+binlogs二進制日志時間點還原、全量+增量+binlogs二進制日志時間點還原；**備份內容**：數據、二進制日志、事務日志、代碼（存儲過程、存儲函數、觸發器、事件調度器）、服務器的配置文件；**備份工具**：mysqldump、cp（拷貝文件）； * * * #### 性能優化 **參數優化**：wait-timeout（服務器關閉非交互連接之前等待活動的秒數）、interactive\_timeout（服務器關閉交互式連接前等待活動的秒數）、max\_connections（并發鏈接數）、max\_user\_connections（單用戶最大連接數）、thread\_concurrency（并發線程數CPU\*2）、skip-name-resolve（禁止DNS解析，IP連接時關閉）、innodb\_buffer\_pool\_size（緩存大小）、innodb\_log\_buffer\_size （事務日志緩存大小）、query\_cache\_size（查詢緩存大小）； **執行計劃**：主要優化type訪問類型；system：標志有一行記錄，const：通過索引一次找到，eq\_ref：唯一索引，ref：非唯一索引，可能多行，range：范圍索引，index：全索引掃描，all：全表掃描； **分庫分表**：垂直拆分（按列拆，大表拆小表）、水平拆分（按行拆，大表拆小表）；拆分策略：Hash取模、范圍分片、地理位置分片、時間分片；問題：分布式事務、跨庫Join、數據合并、橫向擴容；分庫分表中間件：mycat、Sharding-JDBC； **其他優化**：服務器硬件、數據庫配置、操作系統配置、索引優化、SQL優化等； 作者：星冉子 鏈接：https://www.jianshu.com/p/19c6e049bca3 來源：簡書 著作權歸作者所有。商業轉載請聯系作者獲得授權，非商業轉載請注明出處。