引擎

InnoDB：適用于處理大數(shù)據(jù)，崩潰后可以利用事務日志很好地恢復；支持聚簇、B-Tree和全文索引，不支持Hash索引；支持數(shù)據(jù)緩存（訪問數(shù)據(jù)時，表和索引數(shù)據(jù)會被緩存在緩沖池）、外鍵、行鎖、事務和MVCC；可以混合查詢InnoDB引擎的表和其他引擎的表；表文件：表名.frm（表結構文件）、表名.ibd（數(shù)據(jù)和索引文件）；速度較MyISAM慢；

MyISAM：查詢效率很高，適合大量讀場景；支持B-Tree和全文索引，不支持聚簇、Hash索引；不支持數(shù)據(jù)緩存、外鍵、事務、行鎖（支持表鎖）；表文件：表名.frm（表結構文件）、表名.myd（數(shù)據(jù)文件）、表名.myi（索引文件）；

其他引擎：ARCHIVE：檔案，適用于存儲大量獨立的作為歷史數(shù)據(jù)的數(shù)據(jù)，插入快，查詢差；不支持索引，存儲大小無限制，數(shù)據(jù)壓縮較好，不支持delete和update；PERFORMANCE_SCHEMA：性能，專注收集MySQL運行性能數(shù)據(jù)；數(shù)據(jù)在內(nèi)存中，重啟后丟失；大多數(shù)表只讀，用戶不能創(chuàng)建；MEMORY：內(nèi)存，存儲臨時不重要的數(shù)據(jù)，例如作為緩存，適合大量讀的情形；數(shù)據(jù)在內(nèi)存中，重啟后丟失；CSV：數(shù)據(jù)存儲在CSV文件；FEDERATED：聯(lián)盟，適用于分布式；MRG_MYISAM：Merge多個相同定義的MyISAM表；BLACKHOLE：黑洞，像黑洞一樣接受數(shù)據(jù)但不存儲，適用于轉(zhuǎn)發(fā)和過濾給Slave服務器；

索引

概念：優(yōu)點：減少I/O次數(shù)，加快檢索、分組和排序速度；缺點：索引也是表，會占用磁盤空間，維護需要時間成本，降低新增修改數(shù)據(jù)效率，創(chuàng)建索引時需要對表加鎖；

索引類型：主鍵索引（不可重復，不可為空）、唯一索引（不可重復，可以空）、普通索引（無限制）、全文索引（對大文本text、blob的索引）、空間索引（GIS數(shù)據(jù)）、組合索引（對多列建立的索引，遵循最左前綴原則，ABC索引相當于建了A、AB、ABC3個索引）；

索引方法：哈希索引：只有內(nèi)存存儲引擎才支持；實現(xiàn)原理：根據(jù)索引列的值計算hashcode，在hashcode的位置存儲該行數(shù)據(jù)的物理位置實現(xiàn)；一個值對應一個hashcode，所以不支持排序和范圍查詢；全文索引：僅用于MyISAM和InnoDb（5.6后支持）；實現(xiàn)原理：對大文本字段分詞，生成一份單詞清單，索引時根據(jù)單詞清單來；使用：不能like，使用match..against查詢；5.7前只支持英文，5.7后通過ngram插件支持中文；查詢字符串不能太短，至少4字節(jié)；BTree索引：每個節(jié)點包含索引鍵和數(shù)據(jù)，可以使用二分查找；B+Tree索引：數(shù)據(jù)都在葉子節(jié)點，其他節(jié)點只有索引鍵，磁盤讀寫代價更低；

索引實現(xiàn)結構：主鍵索引為主索引，其他為輔助索引；MyISAM實現(xiàn)非聚簇索引：索引順序與數(shù)據(jù)物理排列順序無關，數(shù)據(jù)表和索引表分表存儲，主索引和輔助索引葉子節(jié)點的key都存儲鍵對應的數(shù)據(jù)物理地址；InnoDb實現(xiàn)聚簇索引：聚簇索引的順序就是數(shù)據(jù)的物理存儲順序，數(shù)據(jù)在主索引中存儲，主索引葉子節(jié)點的key存儲鍵對應的數(shù)據(jù)，輔助索引葉子節(jié)點的key存儲主鍵值；沒有主鍵：一個表只有一個聚簇索引，沒有主鍵，會用一個唯一且不為空的索引列做為主鍵，成為此表的聚簇索引，如果沒有這樣的索引，InnoDB會隱式定義一個主鍵來作為聚簇索引；

索引使用：什么時候用：主鍵自動建索引，查詢條件，排序，外鍵，關聯(lián)字段應該建索引；什么時候不用：經(jīng)常增刪改的列，大量重復的列，記錄太少的表；索引失效情況：組合索引的NULL列、like %xx%、索引列有函數(shù)和表達式、使用不等于(<>!=，但主鍵和整型不會失效)、IS (NOT) NULL、字段類型不一致（包括字符串不加單引號）、OR（非每個條件都有索引時）；索引優(yōu)化：最左前綴、模糊查詢優(yōu)化、全文索引、短索引（對前N個字符建索引）；

鎖

分類：行鎖：只能作用在索引上；間隙鎖：鎖索引前面的間隙，開區(qū)間如（3，5），間隙鎖是不互斥的，RU和RC隔離級別沒有間隙鎖；臨鍵鎖：行鎖+間隙鎖，即左開右閉的區(qū)間如（3，5]；共享鎖S/排他鎖X：都是行鎖，共享鎖用于并發(fā)讀，排他鎖用于更新刪除（不包括插入），事務請求時會根據(jù)隔離級別和數(shù)據(jù)是否存在可能獲取到行鎖、間隙鎖、臨建鎖之一；意向共享鎖IS/意向排他鎖IX：屬于表鎖，用于判斷鎖沖突；插入意向鎖：特殊的間隙鎖，鎖住一行數(shù)據(jù)，用于并發(fā)插入；

加鎖分析：不同隔離級別、查詢記錄是否存在，存在多條還是一條，加鎖情況不一樣；

2個事務可以同時獲得IX鎖、間隙鎖，但不能同時獲取X鎖；A事務獲取了間隙鎖，B事務插入的數(shù)據(jù)在間隙區(qū)間內(nèi)時B獲取不到插入鎖；

查詢時若記錄不存在則獲取間隙鎖，存在則獲取行鎖；A事務加了S鎖則B事務可讀不可改；A事務加了X鎖則B事務不可讀不可改；

select...where id = ?：快照讀，不加鎖；select...lock in share mode：當前讀，加S鎖（若有多條記錄還會對區(qū)間加間隙鎖）；select...for update：當前讀，加X鎖（若有多條記錄還會對區(qū)間加間隙鎖）；

隔離級別RU和RC，無論條件列上是否有索引都不會鎖表，只鎖行；RR和串行化通過行鎖+間隙鎖實現(xiàn)鎖表；事務獲取X/S鎖前一定會對表加上IX/IS鎖；

事務

事務命令：BEGIN、COMMIT、SAVEPOINT（設置回滾點）、ROLLBACK（默認回滾到BEGIN）、ROLLBACK TO SAVEPOINT（部分回滾，回滾到指定點）、SELECT @@AUTOCOMMIT/SET AUTOCOMMIT（查看設置自動提交）、SET TRANSACTION ISOLATION LEVEL（設置隔離級別）；

事務并發(fā)問題：臟讀：A讀了B更新的數(shù)據(jù)，B回滾后是臟數(shù)據(jù)；不可重復讀：A多次讀取同一數(shù)據(jù)，B過程中更新并提交了數(shù)據(jù)，導致讀取結果不一致；幻讀：類似不可重復讀，不可重復讀側(cè)重于修改，幻讀側(cè)重于新增或刪除，導致少讀或多讀了數(shù)據(jù)；

事務隔離級別：描述事務處理之間的相互影響。讀未提交：A事務可讀到B事務已更新未提交的數(shù)據(jù)，存在3個問題；讀提交：A事務只能讀到B事務已更新已提交的數(shù)據(jù)，只解決了臟讀；可重復讀：默認級別，A事務不能讀到B事務已更新已提交的數(shù)據(jù)，使用MVCC加版本控制，select不更新版本號，讀的歷史版本數(shù)據(jù)是快照讀，insert/update/delete會更新版本號是當前讀，解決了臟讀和不可重復讀；串行化：會鎖表，3個問題都解決了，但并發(fā)低；

MVCC：多版本并發(fā)控制協(xié)議，大多數(shù)情況下代替行鎖，通過MVCC+行鎖提高并發(fā)，降低系統(tǒng)開銷；實現(xiàn)原理：每行記錄增加2個隱藏列：創(chuàng)建版本號和刪除版本號（即事務ID）；select：查詢創(chuàng)建版本小于等于當前事務版本并且刪除版本未定義或大于當前版本的行數(shù)據(jù)；delete：更新當前行的刪除版本號為當前事務ID；insert：更新當前行的創(chuàng)建版本號為當前事務ID，刪除版本號為undefined；update：先拷貝insert，再delete舊行；

分布式事務：允許多個數(shù)據(jù)庫實例參與一個全局事務；XA：分布式事務，包括應用程序AP、資源管理器RM、事務管理器TM；MySQL命令：采用2PC，XA START/END/PREPARE/COMMIT/ROLLBACK xid（開啟、表示事務內(nèi)SQl完成、準備、提交、回滾事務）;

快照讀：快照讀：簡單的select，通過mvcc實現(xiàn)；當前讀：select ... lock in share mode、select ... for update、insert、update、delete，通過行鎖和間隙鎖實現(xiàn)；

日志

分類：錯誤日志：默認開啟，文件名稱為hostname.err，記錄服務器啟動關閉、運行錯誤、事件信息等；查詢?nèi)罩?/b>：默認關閉，記錄用戶所有的增刪改查操作，耗性能和IO；慢日志：默認關閉，記錄執(zhí)行時間超過指定時間的查詢語句，用于性能定位；事務日志：InnoDb特有，持久化到磁盤，提高事務效率（改表數(shù)據(jù)只改內(nèi)存，記事務日志，后續(xù)通過日志慢慢刷到磁盤），崩潰恢復，采用追加寫；二進制日志：默認關閉，變更日志，記錄修改數(shù)據(jù)的SQL語句及時間、數(shù)據(jù)等信息；

事務日志：InnoDb引擎實現(xiàn)，包括redo log：重做日志，提供前滾操作，物理日志，記錄數(shù)據(jù)頁的物理修改，而不是行數(shù)據(jù)的修改；undo log：回滾日志，提供回滾操作，邏輯日志，根據(jù)每行數(shù)據(jù)進行記錄（反著記，insert時undolog記錄delete）；刷盤：redo/undolog buffer到os buffer，調(diào)用fsync將os buffer寫到磁盤redo/undolog file，2個階段的策略可通過參數(shù)控制（每次提交時刷，每秒刷，buffer內(nèi)存超過一半刷等）；

二進制日志：數(shù)據(jù)庫實現(xiàn)，也可用于數(shù)據(jù)庫恢復，3種記錄方式：statement（記錄SQL）、row（記錄改的數(shù)據(jù)行）、mixed（簡單的記SQL，復雜的記行）；查看日志：show binlog events或者使用mysqlbinlog工具；

緩存

存儲：Key（SQL+數(shù)據(jù)庫+客戶端協(xié)議）Value（緩存結果）hash表存儲在內(nèi)存中；相同SQL直接取緩存；緩存失效：對數(shù)據(jù)和表結構的任何修改會清空表的緩存，表數(shù)據(jù)修改中提交前緩存失效；不存緩存：包含不確定查詢結果時不會存緩存（like、now()等）；不使用緩存：事務ID比當前數(shù)據(jù)庫最大事務ID大的才使用緩存；InnoDB中有加鎖操作的事務不使用緩存；

備份

分類：完全備份、部分備份；全量備份、增量備份、差異備份；熱備、溫備、冷備；物理備份、邏輯備份；備份策略：全量+差異+binlogs二進制日志時間點還原、全量+增量+binlogs二進制日志時間點還原；備份內(nèi)容：數(shù)據(jù)、二進制日志、事務日志、代碼（存儲過程、存儲函數(shù)、觸發(fā)器、事件調(diào)度器）、服務器的配置文件；備份工具：mysqldump、cp（拷貝文件）；

性能優(yōu)化

參數(shù)優(yōu)化：wait-timeout（服務器關閉非交互連接之前等待活動的秒數(shù)）、interactive_timeout（服務器關閉交互式連接前等待活動的秒數(shù)）、max_connections（并發(fā)鏈接數(shù)）、max_user_connections（單用戶最大連接數(shù)）、thread_concurrency（并發(fā)線程數(shù)CPU*2）、skip-name-resolve（禁止DNS解析，IP連接時關閉）、innodb_buffer_pool_size（緩存大?。?、innodb_log_buffer_size （事務日志緩存大?。?、query_cache_size（查詢緩存大?。?；

執(zhí)行計劃：主要優(yōu)化type訪問類型；system：標志有一行記錄，const：通過索引一次找到，eq_ref：唯一索引，ref：非唯一索引，可能多行，range：范圍索引，index：全索引掃描，all：全表掃描；

分庫分表：垂直拆分（按列拆，大表拆小表）、水平拆分（按行拆，大表拆小表）；拆分策略：Hash取模、范圍分片、地理位置分片、時間分片；問題：分布式事務、跨庫Join、數(shù)據(jù)合并、橫向擴容；分庫分表中間件：mycat、Sharding-JDBC；

其他優(yōu)化：服務器硬件、數(shù)據(jù)庫配置、操作系統(tǒng)配置、索引優(yōu)化、SQL優(yōu)化等；