❶ mysql 核心內容-上
1、SQL語句執行流程
MySQL大體上可分為Server層和存儲引擎層兩部分。
Server層：
連接器：TCP握手後伺服器來驗證登陸用戶身份，A用戶創建連接後，管理員對A用戶許可權修改了也不會影響到已經創建的鏈接許可權，必須重新登陸。
查詢緩存：查詢後的結果存儲位置，MySQL8.0版本以後已經取消，因為查詢緩存失效太頻繁，得不償失。
分析器：根據語法規則，判斷你輸入的這個SQL語句是否滿足MySQL語法。
優化器：多種執行策略可實現目標，系統自動選擇最優進行執行。
執行器：判斷是否有許可權，將最終任務提交到存儲引擎。
存儲引擎層
負責數據的存儲和提取。其架構模式是插件式的，支持InnoDB、MyISAM、Memory等多個存儲引擎。現在最常用的存儲引擎是InnoDB，它從MySQL 5.5.5版本開始成為了默認存儲引擎(經常用的也是這個)。
SQL執行順序
2、BinLog、RedoLog、UndoLog
BinLog
BinLog是記錄所有資料庫表結構變更（例如create、alter table）以及表數據修改(insert、update、delete)的二進制日誌，主從資料庫同步用到的都是BinLog文件。BinLog日誌文件有三種模式。
STATEMENT 模式
內容：binlog 記錄可能引起數據變更的 sql 語句
優勢：該模式下，因為沒有記錄實際的數據，所以日誌量很少 IO 都消耗很低，性能是最優的
劣勢：但有些操作並不是確定的，比如 uuid() 函數會隨機產生唯一標識，當依賴 binlog 回放時，該操作生成的數據與原數據必然是不同的，此時可能造成無法預料的後果。
ROW 模式
內容：在該模式下，binlog 會記錄每次操作的源數據與修改後的目標數據，StreamSets就要求該模式。
優勢：可以絕對精準的還原，從而保證了數據的安全與可靠，並且復制和數據恢復過程可以是並發進行的
劣勢：缺點在於 binlog 體積會非常大，同時，對於修改記錄多、欄位長度大的操作來說，記錄時性能消耗會很嚴重。閱讀的時候也需要特殊指令來進行讀取數據。
MIXED 模式
內容：是對上述STATEMENT 跟 ROW 兩種模式的混合使用。
細節：對於絕大部分操作，都是使用 STATEMENT 來進行 binlog 沒有記錄，只有以下操作使用 ROW 來實現：表的存儲引擎為 NDB，使用了uuid() 等不確定函數，使用了 insert delay 語句，使用了臨時表
主從同步流程：
1、主節點必須啟用二進制日誌，記錄任何修改了資料庫數據的事件。
2、從節點開啟一個線程（I/O Thread)把自己扮演成 mysql 的客戶端，通過 mysql 協議，請求主節點的二進制日誌文件中的事件 。
3、主節點啟動一個線程（mp Thread），檢查自己二進制日誌中的事件，跟對方請求的位置對比，如果不帶請求位置參數，則主節點就會從第一個日誌文件中的第一個事件一個一個發送給從節點。
4、從節點接收到主節點發送過來的數據把它放置到中繼日誌（Relay log）文件中。並記錄該次請求到主節點的具體哪一個二進制日誌文件內部的哪一個位置（主節點中的二進制文件會有多個）。
5、從節點啟動另外一個線程（sql Thread ），把 Relay log 中的事件讀取出來，並在本地再執行一次。
mysql默認的復制方式是非同步的，並且復制的時候是有並行復制能力的。主庫把日誌發送給從庫後不管了，這樣會產生一個問題就是假設主庫掛了，從庫處理失敗了，這時候從庫升為主庫後，日誌就丟失了。由此產生兩個概念。
全同步復制
主庫寫入binlog後強制同步日誌到從庫，所有的從庫都執行完成後才返回給客戶端，但是很顯然這個方式的話性能會受到嚴重影響。
半同步復制
半同步復制的邏輯是這樣，從庫寫入日誌成功後返回ACK確認給主庫，主庫收到至少一個從庫的確認就認為寫操作完成。
還可以延伸到由於主從配置不一樣、主庫大事務、從庫壓力過大、網路震盪等造成主備延遲，如何避免這個問題？主備切換的時候用可靠性優先原則還是可用性優先原則？如何判斷主庫Crash了？互為主備的情況下如何避免主備循環復制？被刪庫跑路了如何正確恢復？( o )… 感覺越來越扯到DBA的活兒上去了。
RedoLog
可以先通過下面demo理解：
飯點記賬可以把賬單寫在賬本上也可以寫在粉板上。有人賒賬或者還賬的話，一般有兩種做法：
1、直接把賬本翻出來，把這次賒的賬加上去或者扣除掉。
2、先在粉板上記下這次的賬，等打烊以後再把賬本翻出來核算。
生意忙時選後者，因為前者太麻煩了。得在密密麻麻的記錄中找到這個人的賒賬總額信息，找到之後再拿出算盤計算，最後再將結果寫回到賬本上。
同樣在MySQL中如果每一次的更新操作都需要寫進磁碟，然後磁碟也要找到對應的那條記錄，然後再更新，整個過程IO成本、查找成本都很高。而粉板和賬本配合的整個過程就是MySQL用到的是Write-Ahead Logging 技術，它的關鍵點就是先寫日誌，再寫磁碟。此時賬本 = BinLog，粉板 = RedoLog。
1、 記錄更新時，InnoDB引擎就會先把記錄寫到RedoLog（粉板）裡面，並更新內存。同時，InnoDB引擎會在空閑時將這個操作記錄更新到磁碟裡面。
2、 如果更新太多RedoLog處理不了的時候，需先將RedoLog部分數據寫到磁碟，然後擦除RedoLog部分數據。RedoLog類似轉盤。
RedoLog有write pos 跟checkpoint
write pos ：是當前記錄的位置，一邊寫一邊後移，寫到第3號文件末尾後就回到0號文件開頭。
check point：是當前要擦除的位置，也是往後推移並且循環的，擦除記錄前要把記錄更新到數據文件。
write pos和check point之間的是粉板上還空著的部分，可以用來記錄新的操作。如果write pos追上checkpoint，表示粉板滿了，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把checkpoint推進一下。
有了redo log，InnoDB就可以保證即使資料庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為crash-safe。 redolog兩階段提交：為了讓binlog跟redolog兩份日誌之間的邏輯一致。提交流程大致如下：
1 prepare階段 --> 2 寫binlog --> 3 commit
當在2之前崩潰時，重啟恢復後發現沒有commit，回滾。備份恢復：沒有binlog 。一致
當在3之前崩潰時，重啟恢復發現雖沒有commit，但滿足prepare和binlog完整，所以重啟後會自動commit。備份：有binlog. 一致
binlog跟redolog區別：
redo log是InnoDB引擎特有的；binlog是MySQL的Server層實現的，所有引擎都可以使用。
redo log是物理日誌，記錄的是在某個數據頁上做了什麼修改；binlog是邏輯日誌，記錄的是這個語句的原始邏輯，比如給ID=2這一行的c欄位加1。
redo log是循環寫的，空間固定會用完；binlog是可以追加寫入的。追加寫是指binlog文件寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌。
UndoLog
UndoLog 一般是邏輯日誌，主要分為兩種：
insert undo log
代表事務在insert新記錄時產生的undo log, 只在事務回滾時需要，並且在事務提交後可以被立即丟棄
update undo log
事務在進行update或delete時產生的undo log; 不僅在事務回滾時需要，在快照讀時也需要；所以不能隨便刪除，只有在快速讀或事務回滾不涉及該日誌時，對應的日誌才會被purge線程統一清除
3、MySQL中的索引
索引的常見模型有哈希表、有序數組和搜索樹。
哈希表：一種以KV存儲數據的結構，只適合等值查詢，不適合范圍查詢。
有序數組：只適用於靜態存儲引擎，涉及到插入的時候比較麻煩。可以參考Java中的ArrayList。
搜索樹：按照數據結構中的二叉樹來存儲數據，不過此時是N叉樹(B+樹)。廣泛應用在存儲引擎層中。
B+樹比B樹優勢在於：
B+ 樹非葉子節點存儲的只是索引，可以存儲的更多。B+樹比B樹更加矮胖，IO次數更少。
B+ 樹葉子節點前後管理，更加方便范圍查詢。同時結果都在葉子節點，查詢效率穩定。
B+樹中更有利於對數據掃描，可以避免B樹的回溯掃描。
索引的優點：
1、唯一索引可以保證每一行數據的唯一性
2、提高查詢速度
3、加速表與表的連接
4、顯著的減少查詢中分組和排序的時間
5、通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。
索引的缺點：
1、創建跟維護都需要耗時
2、創建索引時，需要對表加鎖，在鎖表的同時，可能會影響到其他的數據操作
3、 索引需要磁碟的空間進行存儲，磁碟佔用也很快。
4、當對表中的數據進行CRUD的時，也會觸發索引的維護，而維護索引需要時間，可能會降低數據操作性能
索引設計的原則不應該：
1、索引不是越多越好。索引太多，維護索引需要時間跟空間。
2、 頻繁更新的數據，不宜建索引。
3、數據量小的表沒必要建立索引。
應該：
1、重復率小的列建議生成索引。因為重復數據少，索引樹查詢更有效率，等價基數越大越好。
2、數據具有唯一性，建議生成唯一性索引。在資料庫的層面，保證數據正確性
3、頻繁group by、order by的列建議生成索引。可以大幅提高分組和排序效率
4、經常用於查詢條件的欄位建議生成索引。通過索引查詢，速度更快
索引失效的場景
1、模糊搜索：左模糊或全模糊都會導致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。
2、隱式類型轉換：比如select * from t where name = xxx , name是字元串類型，但是沒有加引號，所以是由MySQL隱式轉換的，所以會讓索引失效 3、當語句中帶有or的時候：比如select * from t where name=『sw』 or age=14
4、不符合聯合索引的最左前綴匹配：(A,B,C)的聯合索引，你只where了C或B或只有B,C
關於索引的知識點：
主鍵索引：主鍵索引的葉子節點存的是整行數據信息。在InnoDB里，主鍵索引也被稱為聚簇索引（clustered index）。主鍵自增是無法保證完全自增的哦，遇到唯一鍵沖突、事務回滾等都可能導致不連續。
唯一索引：以唯一列生成的索引，該列不允許有重復值，但允許有空值(NULL)
普通索引跟唯一索引查詢性能：InnoDB的數據是按數據頁為單位來讀寫的，默認每頁16KB，因此這兩種索引查詢數據性能差別微乎其微。
change buffer：普通索引用在更新過程的加速，更新的欄位如果在緩存中，如果是普通索引則直接更新即可。如果是唯一索引需要將所有數據讀入內存來確保不違背唯一性，所以盡量用普通索引。
非主鍵索引：非主鍵索引的葉子節點內容是主鍵的值。在InnoDB里，非主鍵索引也被稱為二級索引（secondary index）
回表：先通過資料庫索引掃描出數據所在的行，再通過行主鍵id取出索引中未提供的數據，即基於非主鍵索引的查詢需要多掃描一棵索引樹。
覆蓋索引：如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，我們就稱之為覆蓋索引。
聯合索引：相對單列索引，組合索引是用多個列組合構建的索引，一次性最多聯合16個。
最左前綴原則：對多個欄位同時建立的組合索引(有順序，ABC，ACB是完全不同的兩種聯合索引) 以聯合索引(a,b,c)為例，建立這樣的索引相當於建立了索引a、ab、abc三個索引。另外組合索引實際還是一個索引，並非真的創建了多個索引，只是產生的效果等價於產生多個索引。
索引下推：MySQL 5.6引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的欄位先做判斷，過濾掉不符合條件的記錄，減少回表字數。
索引維護：B+樹為了維護索引有序性涉及到頁分裂跟頁合並。增刪數據時需考慮頁空間利用率。
自增主鍵：一般會建立與業務無關的自增主鍵，不會觸發葉子節點分裂。
延遲關聯：通過使用覆蓋索引查詢返回需要的主鍵，再根據主鍵關聯原表獲得需要的數據。
InnoDB存儲: * .frm文件是一份定義文件，也就是定義資料庫表是一張怎麼樣的表。*.ibd文件則是該表的索引，數據存儲文件，既該表的所有索引樹，所有行記錄數據都存儲在該文件中。
MyISAM存儲：* .frm文件是一份定義文件，也就是定義資料庫表是一張怎麼樣的表。* .MYD文件是MyISAM存儲引擎表的所有行數據的文件。* .MYI文件存放的是MyISAM存儲引擎表的索引相關數據的文件。MyISAM引擎下，表數據和表索引數據是分開存儲的。
MyISAM查詢：在MyISAM下，主鍵索引和輔助鍵索引都屬於非聚簇索引。查詢不管是走主鍵索引，還是非主鍵索引，在葉子結點得到的都是目的數據的地址，還需要通過該地址，才能在數據文件中找到目的數據。
PS：InnoDB支持聚簇索引，MyISAM不支持聚簇索引
4、SQL事務隔離級別
ACID的四個特性
原子性（Atomicity）：把多個操作放到一個事務中，保證這些操作要麼都成功，要麼都不成功
一致性（Consistency）：理解成一串對數據進行操作的程序執行下來，不會對數據產生不好的影響，比如憑空產生，或消失
隔離性（Isolation，又稱獨立性）：隔離性的意思就是多個事務之間互相不幹擾，即使是並發事務的情況下，他們只是兩個並發執行沒有交集，互不影響的東西；當然實現中，也不一定需要這么完整隔離性，即不一定需要這么的互不幹擾，有時候還是允許有部分干擾的。所以MySQL可以支持4種事務隔離性
持久性（Durability）：當某個操作操作完畢了，那麼結果就是這樣了，並且這個操作會持久化到日誌記錄中
PS：ACID中C與CAP定理中C的區別
ACID的C著重強調單資料庫事務操作時，要保證數據的完整和正確性，數據不會憑空消失跟增加。CAP 理論中的C指的是對一個數據多個備份的讀寫一致性
事務操作可能會出現的數據問題
1、臟讀(dirty read)：B事務更改數據還未提交，A事務已經看到並且用了。B事務如果回滾，則A事務做錯了
2、 不可重復讀(non-repeatable read)：不可重復讀的重點是修改: 同樣的條件, 你讀取過的數據, 再次讀取出來發現值不一樣了，只需要鎖住滿足條件的記錄
3、 幻讀(phantom read)：事務A先修改了某個表的所有紀錄的狀態欄位為已處理，未提交；事務B也在此時新增了一條未處理的記錄，並提交了；事務A隨後查詢記錄，卻發現有一條記錄是未處理的造成幻讀現象，幻讀僅專指新插入的行。幻讀會造成語義上的問題跟數據一致性問題。
4、 在可重復讀RR隔離級別下，普通查詢是快照讀，是不會看到別的事務插入的數據的。因此，幻讀在當前讀下才會出現。要用間隙鎖解決此問題。
在說隔離級別之前，你首先要知道，你隔離得越嚴實，效率就會越低。因此很多時候，我們都要在二者之間尋找一個平衡點。SQL標準的事務隔離級別由低到高如下： 上圖從上到下的模式會導致系統的並行性能依次降低，安全性依次提高。
讀未提交：別人改數據的事務尚未提交，我在我的事務中也能讀到。
讀已提交(Oracle默認)：別人改數據的事務已經提交，我在我的事務中才能讀到。
可重復讀(MySQL默認)：別人改數據的事務已經提交，我在我的事務中也不去讀，以此保證重復讀一致性。
串列：我的事務尚未提交，別人就別想改數據。
標准跟實現：上面都是關於事務的標准，但是每一種資料庫都有不同的實現，比如MySQL InnDB 默認為RR級別，但是不會出現幻讀。因為當事務A更新了所有記錄的某個欄位，此時事務A會獲得對這個表的表鎖，因為事務A還沒有提交，所以事務A獲得的鎖沒有釋放，此時事務B在該表插入新記錄，會因為無法獲得該表的鎖，則導致插入操作被阻塞。只有事務A提交了事務後，釋放了鎖，事務B才能進行接下去的操作。所以可以說 MySQL的RR級別的隔離是已經實現解決了臟讀，不可重復讀和幻讀的。
5、MySQL中的鎖
無論是Java的並發編程還是資料庫的並發操作都會涉及到鎖，研發人員引入了悲觀鎖跟樂觀鎖這樣一種鎖的設計思想。
悲觀鎖：
優點：適合在寫多讀少的並發環境中使用，雖然無法維持非常高的性能，但是在樂觀鎖無法提更好的性能前提下，可以做到數據的安全性
缺點：加鎖會增加系統開銷，雖然能保證數據的安全，但數據處理吞吐量低，不適合在讀書寫少的場合下使用
樂觀鎖：
優點：在讀多寫少的並發場景下，可以避免資料庫加鎖的開銷，提高DAO層的響應性能，很多情況下ORM工具都有帶有樂觀鎖的實現，所以這些方法不一定需要我們人為的去實現。
缺點：在寫多讀少的並發場景下，即在寫操作競爭激烈的情況下，會導致CAS多次重試，沖突頻率過高，導致開銷比悲觀鎖更高。
實現：資料庫層面的樂觀鎖其實跟CAS思想類似， 通數據版本號或者時間戳也可以實現。
資料庫並發場景主要有三種：
讀-讀：不存在任何問題，也不需要並發控制
讀-寫：有隔離性問題，可能遇到臟讀，幻讀，不可重復讀
寫-寫：可能存更新丟失問題，比如第一類更新丟失，第二類更新丟失
兩類更新丟失問題：
第一類更新丟失：事務A的事務回滾覆蓋了事務B已提交的結果 第二類更新丟失：事務A的提交覆蓋了事務B已提交的結果
為了合理貫徹落實鎖的思想，MySQL中引入了雜七雜八的各種鎖：
鎖分類
MySQL支持三種層級的鎖定，分別為
表級鎖定
MySQL中鎖定粒度最大的一種鎖，最常使用的MYISAM與INNODB都支持表級鎖定。
頁級鎖定
是MySQL中鎖定粒度介於行級鎖和表級鎖中間的一種鎖，表級鎖速度快，但沖突多，行級沖突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。
行級鎖定
Mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖。行級鎖能大大減少資料庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大行級鎖不一定比表級鎖要好：鎖的粒度越細，代價越高，相比表級鎖在表的頭部直接加鎖，行級鎖還要掃描找到對應的行對其上鎖，這樣的代價其實是比較高的，所以表鎖和行鎖各有所長。
MyISAM中的鎖
雖然MySQL支持表，頁，行三級鎖定，但MyISAM存儲引擎只支持表鎖。所以MyISAM的加鎖相對比較開銷低，但數據操作的並發性能相對就不高。但如果寫操作都是尾插入，那還是可以支持一定程度的讀寫並發
從MyISAM所支持的鎖中也可以看出，MyISAM是一個支持讀讀並發，但不支持通用讀寫並發，寫寫並發的資料庫引擎，所以它更適合用於讀多寫少的應用場合，一般工程中也用的較少。
InnoDB中的鎖
該模式下支持的鎖實在是太多了，具體如下：
共享鎖和排他鎖 (Shared and Exclusive Locks)
意向鎖（Intention Locks）
記錄鎖（Record Locks）
間隙鎖（Gap Locks）
臨鍵鎖 （Next-Key Locks）
插入意向鎖（Insert Intention Locks）
主鍵自增鎖 (AUTO-INC Locks)
空間索引斷言鎖（Predicate Locks for Spatial Indexes）
舉個栗子，比如行鎖里的共享鎖跟排它鎖：lock in share modle 共享讀鎖：
為了確保自己查到的數據沒有被其他的事務正在修改，也就是說確保查到的數據是最新的數據，並且不允許其他人來修改數據。但是自己不一定能夠修改數據，因為有可能其他的事務也對這些數據使用了 in share mode 的方式上了S 鎖。如果不及時的commit 或者rollback 也可能會造成大量的事務等待。
for update排它寫鎖:
為了讓自己查到的數據確保是最新數據，並且查到後的數據只允許自己來修改的時候，需要用到for update。相當於一個 update 語句。在業務繁忙的情況下，如果事務沒有及時的commit或者rollback 可能會造成其他事務長時間的等待，從而影響資料庫的並發使用效率。
Gap Lock間隙鎖：
1、行鎖只能鎖住行，如果在記錄之間的間隙插入數據就無法解決了，因此MySQL引入了間隙鎖(Gap Lock)。間隙鎖是左右開區間。間隙鎖之間不會沖突。
2、間隙鎖和行鎖合稱NextKeyLock，每個NextKeyLock是前開後閉區間。
間隙鎖加鎖原則(學完忘那種)：
1、加鎖的基本單位是 NextKeyLock，是前開後閉區間。
2、查找過程中訪問到的對象才會加鎖。
3、索引上的等值查詢，給唯一索引加鎖的時候，NextKeyLock退化為行鎖。
4、索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，NextKeyLock退化為間隙鎖。
5、唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。
❷ 資料庫的事務機制是什麼
回答的有點多請耐心看完。
希望能幫助你還請及時採納謝謝
1事務的原理
事務就是將一組SQL語句放在同一批次內去執行，如果一個SQL語句出錯,則該批次內的所有SQL都將被取消執行。MySQL事務處理只支持InnoDB和BDB數據表類型。
1事務的ACID原則
** 1（Atomicity）原子性**： 事務是最小的執行單位，不允許分割。原子性確保動作要麼全部完成，要麼完全不起作用；
2（Consistency）一致性： 執行事務前後，數據保持一致；
3（Isolation）隔離性： 並發訪問資料庫時，一個事務不被其他事務所干擾。
4（Durability）持久性: 一個事務被提交之後。對資料庫中數據的改變是持久的，即使資料庫發生故障。
1緩沖池（Buffer Pool）
Buffer Pool中包含了磁碟中部分數據頁的映射。當從資料庫讀取數據時，會先從Buffer Pool中讀取數據，如果Buffer Pool中沒有，則從磁碟讀取後放入到Buffer Pool中。當向資料庫寫入數據時，會先寫入到Buffer Pool中，Buffer Pool中更新的數據會定期刷新到磁碟中（此過程稱為刷臟）。
2日誌緩沖區（Log Buffer）
當在MySQL中對InnoDB表進行更改時，這些更改命令首先存儲在InnoDB日誌緩沖區（Log Buffer）的內存中，然後寫入通常稱為重做日誌（redo logs）的InnoDB日誌文件中。
3雙寫機制緩存（DoubleWrite Buffer）
Doublewrite Buffer是共享表空間的物理文件的 buffer,其大小是2MB.是一個一分為二的2MB空間。
刷臟操作開始之時,先進行臟頁**『備份』**操作.將臟頁數據寫入 Doublewrite Buffer.
將Doublewrite Buffer(順序IO)寫入磁碟文件中(共享表空間) 進行刷臟操作.
4回滾日誌(Undo Log)
Undo Log記錄的是邏輯日誌.記錄的是事務過程中每條數據的變化版本和情況.
在Innodb 磁碟架構中Undo Log 默認是共享表空間的物理文件的Buffer.
在事務異常中斷,或者主動(Rollback)回滾的過程中 ,Innodb基於 Undo Log進行數據撤銷回滾,保證數據回歸至事務開始狀態.
5重做日誌(Redo Log)
Redo Log通常指的是物理日誌，記錄的是數據頁的物理修改.並不記錄行記錄情況。（也就是只記錄要做哪些修改，並不記錄修改的完成情況） 當資料庫宕機重啟的時候，會將重做日誌中的內容恢復到資料庫中。
1原子性
Innodb事務的原子性保證,包含事務的提交機制和事務的回滾機制.在Innodb引擎中事務的回滾機制是依託 回滾日誌(Undo Log) 進行回滾數據，保證數據回歸至事務開始狀態.
2那麼不同的隔離級別，隔離性是如何實現的，為什麼不同事物間能夠互不幹擾？ 答案是 鎖 和 MVCC。
3持久性
基於事務的提交機制流程有可能出現三種場景.
1 數據刷臟正常.一切正常提交，Redo Log 循環記錄.數據成功落盤.持久性得以保證
2數據刷臟的過程中出現的系統意外導致頁斷裂現象 (部分刷臟成功)，針對頁斷裂情況,採用Double write機制進行保證頁斷裂數據的恢復.
3數據未出現頁斷裂現象,也沒有刷臟成功，MySQL通過Redo Log 進行數據的持久化即可
4一致性
從資料庫層面，資料庫通過原子性、隔離性、持久性來保證一致性
2事務的隔離級別
Mysql 默認採用的 REPEATABLE_READ隔離級別 Oracle 默認採用的 READ_COMMITTED隔離級別
臟讀： 指一個事務讀取了另外一個事務未提交的數據。
不可重復讀： 在一個事務內讀取表中的某一行數據，多次讀取結果不同
虛讀(幻讀)： 是指在一個事務內讀取到了別的事務插入的數據，導致前後讀取不一致。
2基本語法
-- 使用set語句來改變自動提交模式
SET autocommit = 0; /*關閉*/
SET autocommit = 1; /*開啟*/
-- 注意:
--- 1.MySQL中默認是自動提交
--- 2.使用事務時應先關閉自動提交
-- 開始一個事務,標記事務的起始點
START TRANSACTION
-- 提交一個事務給資料庫
COMMIT
-- 將事務回滾,數據回到本次事務的初始狀態
ROLLBACK
-- 還原MySQL資料庫的自動提交
SET autocommit =1;
-- 保存點
SAVEPOINT 保存點名稱 -- 設置一個事務保存點
ROLLBACK TO SAVEPOINT 保存點名稱 -- 回滾到保存點
RELEASE SAVEPOINT 保存點名稱 -- 刪除保存點
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/*
課堂測試題目
A在線買一款價格為500元商品,網上銀行轉賬.
A的銀行卡余額為2000,然後給商家B支付500.
商家B一開始的銀行卡余額為10000
創建資料庫shop和創建表account並插入2條數據
*/
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 轉賬實現
SET autocommit = 0; -- 關閉自動提交
START TRANSACTION; -- 開始一個事務,標記事務的起始點
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事務
# rollback;
SET autocommit = 1; -- 恢復自動提交
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
3事務實現方式-MVCC
1什麼是MVCC
MVCC是mysql的的多版本並發控制即multi-Version Concurrency Controller，mysql的innodb引擎支持MVVC。MVCC是為了實現事務的隔離性，通過版本號，避免同一數據在不同事務間的競爭，你可以把它當成基於多版本號的一種樂觀鎖。當然，這種樂觀鎖只在事務級別為RR（可重復讀）和RC（讀提交）生效。MVCC最大的好處，相信也是耳熟能詳：讀不加鎖，讀寫不沖突，極大的增加了系統的並發性能。
2MVCC的實現機制
InnoDB在每行數據都增加兩個隱藏欄位，一個記錄創建的版本號，一個記錄刪除的版本號。
在多版本並發控制中，為了保證數據操作在多線程過程中，保證事務隔離的機制，降低鎖競爭的壓力，保證較高的並發量。在每開啟一個事務時，會生成一個事務的版本號，被操作的數據會生成一條新的數據行（臨時），但是在提交前對其他事務是不可見的；對於數據的更新（包括增刪改）操作成功，會將這個版本號更新到數據的行中；事務提交成功，新的版本號也就更新到了此數據行中。這樣保證了每個事務操作的數據，都是互不影響的，也不存在鎖的問題。
3MVCC下的CRUD
SELECT：
當隔離級別是REPEATABLE READ時select操作，InnoDB每行數據來保證它符合兩個條件：
** 1 事務的版本號 大於等於 創建行版本號**
** 2 行數據的刪除版本 未定義 或者大於 事務版本號**
【行創建版本號 事務版本號 行刪除版本號】
INSERT：
InnoDB為這個新行 記錄 當前的系統版本號。
DELETE：
InnoDB將當前的系統版本號 設置為 這一行的刪除版本號。
UPDATE：
InnoDB會寫一個這行數據的新拷貝，這個拷貝的版本為 當前的系統版本號。它同時也會將這個版本號 寫到 舊行的刪除版本里。
————————————————
版權聲明：本文為CSDN博主「@Autowire」的原創文章，遵循CC 4.0 BY-SA版權協議，轉載請附上原文出處鏈接及本聲明。
原文鏈接：https://blog.csdn.net/zs18753479279/article/details/113933252
❸ 數據倉庫的特點
1、數據倉庫是面向主題的；操作型資料庫的數據組織面向事務處理任務，而數據倉庫中的數據是按照一定的主題域進行組織。主題是指用戶使用數據倉庫進行決策時所關心的重點方面，一個主題通常與多個操作型信息系統相關。
2、數據倉庫是集成的，數據倉庫的數據有來自於分散的操作型數據，將所需數據從原來的數據中抽取出來，進行加工與集成，統一與綜合之後才能進入數據倉庫；
數據倉庫中的數據是在對原有分散的資料庫數據抽取、清理的基礎上經過系統加工、匯總和整理得到的，必須消除源數據中的不一致性，以保證數據倉庫內的信息是關於整個企業的一致的全局信息。
數據倉庫的數據主要供企業決策分析之用，所涉及的數據操作主要是數據查詢，一旦某個數據進入數據倉庫以後，一般情況下將被長期保留，也就是數據倉庫中一般有大量的查詢操作，但修改和刪除操作很少，通常只需要定期的載入、刷新。
數據倉庫中的數據通常包含歷史信息，系統記錄了企業從過去某一時點(如開始應用數據倉庫的時點)到當前的各個階段的信息，通過這些信息，可以對企業的發展歷程和未來趨勢做出定量分析和預測。
3、數據倉庫是不可更新的，數據倉庫主要是為決策分析提供數據，所涉及的操作主要是數據的查詢；
4、數據倉庫是隨時間而變化的，傳統的關系資料庫系統比較適合處理格式化的數據，能夠較好的滿足商業商務處理的需求。穩定的數據以只讀格式保存，且不隨時間改變。
5、匯總的。操作性數據映射成決策可用的格式。
6、大容量。時間序列數據集合通常都非常大。
7、非規范化的。Dw數據可以是而且經常是冗餘的。
8、元數據。將描述數據的數據保存起來。
9、數據源。數據來自內部的和外部的非集成操作系統。
數據倉庫，是在資料庫已經大量存在的情況下，為了進一步挖掘數據資源、為了決策需要而產生的，它並不是所謂的「大型資料庫」。數據倉庫的方案建設的目的，是為前端查詢和分析作為基礎，由於有較大的冗餘，所以需要的存儲也較大。為了更好地為前端應用服務，數據倉庫往往有如下幾點特點：
1.效率足夠高。數據倉庫的分析數據一般分為日、周、月、季、年等，可以看出，日為周期的數據要求的效率最高，要求24小時甚至12小時內，客戶能看到昨天的數據分析。由於有的企業每日的數據量很大，設計不好的數據倉庫經常會出問題，延遲1-3日才能給出數據，顯然不行的。
2.數據質量。數據倉庫所提供的各種信息，肯定要准確的數據，但由於數據倉庫流程通常分為多個步驟，包括數據清洗，裝載，查詢，展現等等，復雜的架構會更多層次，那麼由於數據源有臟數據或者代碼不嚴謹，都可以導致數據失真，客戶看到錯誤的信息就可能導致分析出錯誤的決策，造成損失，而不是效益。
3.擴展性。之所以有的大型數據倉庫系統架構設計復雜，是因為考慮到了未來3-5年的擴展性，這樣的話，未來不用太快花錢去重建數據倉庫系統，就能很穩定運行。主要體現在數據建模的合理性，數據倉庫方案中多出一些中間層，使海量數據流有足夠的緩沖，不至於數據量大很多，就運行不起來了。
從上面的介紹中可以看出，數據倉庫技術可以將企業多年積累的數據喚醒，不僅為企業管理好這些海量數據，而且挖掘數據潛在的價值，從而成為通信企業運營維護系統的亮點之一。正因為如此，
廣義的說，基於數據倉庫的決策支持系統由三個部件組成：數據倉庫技術，聯機分析處理技術和數據挖掘技術，其中數據倉庫技術是系統的核心，在這個系列後面的文章里，將圍繞數據倉庫技術，介紹現代數據倉庫的主要技術和數據處理的主要步驟，討論在通信運營維護系統中如何使用這些技術為運營維護帶來幫助。
4.面向主題
操作型資料庫的數據組織面向事務處理任務，各個業務系統之間各自分離，而數據倉庫中的數據是按照一定的主題域進行組織的。主題是與傳統資料庫的面向應用相對應的，是一個抽象概念，是在較高層次上將企業信息系統中的數據綜合、歸類並進行分析利用的抽象。每一個主題對應一個宏觀的分析領域。數據倉庫排除對於決策無用的數據，提供特定主題的簡明視圖。
❹ mysql 存儲過程 DDL 參數
MySQL8.0 開始支持原⼦ DDL(atomic DDL)，數據字典的更新，存儲引擎操作，寫⼆進制日誌結合成了一個事務。在沒有原⼦DDL之前，DROP TABLE test1,test2；如遇到server crash，可能會有test1被drop了，test2沒有被drop掉。下面來看下在MySQL8.0之前和MySQL8.0 數據字典的區別
在MySQL8.0 之前，Data Dictionary除了存在與.FRM, .TRG, .OPT ⽂件外，還存在於系統表中（MyISAM ⾮事務引擎表中），在MySQL8.0 ，Data Dictionary 全部存在於Data Dictionary Storage Engine（即 InnoDB表中），這使crash recovery 維持原⼦性成為了可能
存儲引擎⽀持
目前，只有InnoDB存儲引擎⽀持原子DDL，為了實現原子DDL，Innodb要寫DDL logs 到 mysql.innodb_ddl_log 表，這是⼀個隱藏在mysql.ibd 數據字典表空間⾥的數據字典表。要看mysql.innodb_ddl_log 中的內容，需要
SET GLOBALLOG_ERROR_VERBOSITY=3;（MySQL 8.0 默認為2，error log 記錄Errors and
warnings，不不記錄notes）
SET GLOBAL innodb_print_ddl_logs=1;
CREATE TABLEt1 (c1 INT)ENGINE=InnoDB;
查看error log
[Note] [MY-011066] InnoDB: DDL loginsert: [DDLrecord:DELETE SPACE,id=30,
thread_id=25, space_id=9, old_file_path=./test/t1.ibd]
[Note] [MY-011066]InnoDB:DDL logdelete:by id30
[Note] [MY-011066]InnoDB:DDL loginsert: [DDLrecord: REMOVECACHE,id=31,
thread_id=25, table_id=1066, new_file_path=test/t1]
[Note] [MY-011066]InnoDB:DDL logdelete:by id31
[Note] [MY-011066]InnoDB:DDL loginsert: [DDLrecord: FREE,id=32, thread_
id=25, space_id=9, index_id=143, page_no=4]
[Note] [MY-011066]InnoDB:DDL log delete:by id32
[Note] [MY-011066]InnoDB:DDL logpost ddl :begin for thread id: 25
[Note] [MY-011066]InnoDB:DDL logpost ddl :end for thread id: 25
原子DDL 操作步驟
准備：創建所需的對象並將DDL⽇志寫入 mysql.innodb_ddl_log表中。DDL日誌定義了如何前滾和回滾DDL操作。
執行：執⾏DDL操作。例如，為CREATE TABLE操作執⾏創建。
提交：更新數據字典並提交數據字典事務。
Post-DDL：重播並從mysql.innodb_ddl_log表格中刪除DDL⽇志。為確保回滾可以安全執⾏⽽不引⼊不⼀致性，在此最後階段執⾏⽂件操作（如重命名或刪除數據文件）。這一階段還從 mysql.innodb_dynamic_metadata的數據字典表刪除的動態元數據為了DROP TABLE，TRUNCATE和其它重建表的DDL操作。
⽆論事務是提交還是回滾，DDL日誌都會mysql.innodb_ddl_log在Post-DDL階段重播並從表中刪除 。mysql.innodb_ddl_log如果伺服器在DDL操作期間暫停，DDL⽇志應該只保留在表中。在這種情況下，DDL⽇志會在恢復後重播並刪除。
在恢復情況下，當伺服器重新啟動時，可能會提交或回退DDL事務。如果在重做⽇志和⼆進制日誌中存在DDL操作的提交階段期間執⾏的數據字典事務，則該操作被認為是成功的並且被前滾。否則，在InnoDB重放數據字典重做日誌時回滾不完整的數據字典事務 ，並且回滾DDL事務。
原⼦DDL ⽀持類型
• DROP TABLES , all tables dropped or none
• DROP SCHEMA, all entities in the schema are dropped, or none
• Note that atomic DDL statements will be rolled back or committed even in case of crash, e.g. RENAME TABLES
• CREATE TABLE would be successfully committed or rolled back (no orphan ibd left)
• TRUNCATE TABLE (including InnoDB tables with FTS AUX tables) would be successfully committed or rolled back
• RENAME TABLES, all or none
• ALTER TABLE successful or not done
示例
結論
在MySQL8.0之前，alter table 操作在server crash的情況下，會遺留.frm,.ibd文件。MySQL8.0 能實現原⼦DDL（包括 DROP TABLE, DROP SCHEMA, CREATE TABLE, TRUNCATE TABLE, ALTER TABLE），alter table 操作，在server crash的情況下，不會遺留.frm，.ibd臨時文件。讓我們⼀起期待MySQL8.0 GA的到來吧！
❺ oracle 在存儲過程中動態的建一個臨時表使用和在資料庫里寫死一個臨時表使用兩者有什麼區別
我們仍使用實驗 05中的環境，略去准備數據的過程。
我們仍然使用兩個會話，一個會話 run，用於運行主 SQL；另一個會話 ps，用於進行 performance_schema 的觀察：
主會話線程號為 29，
可以看到寫入的線程是 page_clean_thread，是一個刷臟操作，這樣就能理解數據為什麼是慢慢寫入的。
也可以看到每個 IO 操作的大小是 16K，也就是刷數據頁的操作。
結論：
我們可以看到，
1. MySQL 會基本遵守 max_heap_table_size 的設定，在內存不夠用時，直接將表轉到磁碟上存儲。
2. 由於引擎不同（內存中表引擎為 heap，磁碟中表引擎則跟隨 internal_tmp_disk_storage_engine 的配置），本次實驗寫磁碟的數據量和實驗 05中使用內存的數據量不同。
3. 如果臨時表要使用磁碟，表引擎配置為 InnoDB，那麼即使臨時表在一個時間很短的 SQL 中使用，且使用後即釋放，釋放後也會刷臟頁到磁碟中，消耗部分 IO。
❻ order by 的工作原理
CREATE TABLE `t` (
`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 t where city='杭州' order by name limit 1000 ;
MySQL 會給每個線程分配一塊內存用於排序，稱為 sort_buffer。
在全欄位排序的情況下，這個語句執行流程如下所示 ：
1）初始化 sort_buffer，確定放入 name、city、age 這三個欄位；
2） 從索引 city 找到第一個滿足 city='杭州』條件的主鍵 id
3）到主鍵 id 索引取出整行，取 name、city、age 三個欄位的值，存入 sort_buffer 中；
4）從索引 city 取下一個記錄的主鍵 id；
5） 重復步驟 3、4 直到 city 的值不滿足查詢條件為止，對應的主鍵 id
6）對 sort_buffer 中的數據按照欄位 name 做快速排序；
7）按照排序結果取前 1000 行返回給客戶端。
圖中「按 name 排序」這個動作，可能在內存中完成，也可能需要使用外部排序，這 取決
於排序所需的內存和參數 sort_buffer_size 。
sort_buffer_size，就是 MySQL 為排序開辟的內存（sort_buffer）的大小。如果要排序
的數據量小於 sort_buffer_size，排序就在內存中完成。但如果排序數據量太大，內存放
不下，則不得不利用磁碟臨時文件輔助排序。
通過查看 OPTIMIZER_TRACE 來查看排序的結果：
number_of_tmp_files 表示的是，排序過程中使用的臨時文件數。你一定奇怪，為什麼需
要 12 個文件？內存放不下時，就需要使用外部排序， 外部排序一般使用歸並排序演算法 。
可以這么簡單理解，MySQL 將需要排序的數據分成 12 份，每一份單獨排序後存在這些臨
時文件中。然後把這 12 個有序文件再合並成一個有序的大文件。
如果 sort_buffer_size 超過了需要排序的數據量的大小，number_of_tmp_files 就是 0，
表示排序可以直接在內存中完成。
否則就需要放在臨時文件中排序。sort_buffer_size 越小，需要分成的份數越多，
number_of_tmp_files 的值就越大。
examined_rows=4000，表示參與排序的行數是 4000 行。
結論：
只對原表的數據讀了一遍，剩下的操作都是在 sort_buffer 和
臨時文件中執行的。但這個演算法有一個問題，就是如果查詢要返回的欄位很多的話，那麼
sort_buffer 裡面要放的欄位數太多，這樣內存里能夠同時放下的行數很少，要分成很多個
臨時文件，排序的性能會很差。
所以如果單行很大，這個方法效率不夠好。
如果 MySQL 認為排序的單行長度太大會怎麼做呢？
SET max_length_for_sort_data = 16;
max_length_for_sort_data，是 MySQL 中專門控制用於排序的行數據的長度的一個參
數。它的意思是，如果單行的長度超過這個值，MySQL 就認為單行太大，要換一個算
法。
新的演算法（rowid）放入 sort_buffer 的欄位，只有要排序的列（即 name 欄位）和主鍵 id。
rowid排序的流程：
1. 初始化 sort_buffer，確定放入兩個欄位，即 name 和 id；
2. 從索引 city 找到第一個滿足 city='杭州』條件的主鍵 id，也就是圖中的 ID_X；
3. 到主鍵 id 索引取出整行，取 name、id 這兩個欄位，存入 sort_buffer 中；
4. 從索引 city 取下一個記錄的主鍵 id；
5. 重復步驟 3、4 直到不滿足 city='杭州』條件為止，也就是圖中的 ID_Y；
6. 對 sort_buffer 中的數據按照欄位 name 進行排序；
7. 遍歷排序結果，取前 1000 行，並按照 id 的值回到原表中取出 city、name 和 age 三
個欄位返回給客戶端。
對比全欄位排序流程圖會發現，rowid 排序多訪問了一次表 t 的主鍵索引。
需要說明的是，最後的「結果集」是一個邏輯概念，實際上 MySQL 服務端從排序後的
sort_buffer 中依次取出 id，然後到原表查到 city、name 和 age 這三個欄位的結果，不
需要在服務端再耗費內存存儲結果，是直接返回給客戶端的。
sort_mode 變成了，表示參與排序的只有 name 和 id 這兩個欄位。
number_of_tmp_files 變成 10 了，是因為這時候參與排序的行數雖然仍然是 4000
行，但是每一行都變小了，因此需要排序的總數據量就變小了，需要的臨時文件也相應
地變少了。
3. 全欄位排序 VS rowid 排序
如果 MySQL 實在是擔心排序內存太小，會影響排序效率，才會採用 rowid 排序演算法，這
樣排序過程中一次可以排序更多行，但是需要再回到原表去取數據。
如果 MySQL 認為內存足夠大，會優先選擇全欄位排序，把需要的欄位都放到 sort_buffer
中，這樣排序後就會直接從內存裡面返回查詢結果了，不用再回到原表去取數據。
這也就體現了 MySQL 的一個設計思想：如果內存夠，就要多利用內存，盡量減少磁碟訪
問。
對於 InnoDB 表來說，rowid 排序會要求回表多造成磁碟讀，因此不會被優先選擇。
如果上面的例子建立（city, name）的聯合索引，本身就是已經排好序的。
如果建立的是覆蓋索引，則效率會更高。
MySQL 的表是用什麼方法來定位「一行數據」的？
創建的表沒有主鍵，或者把一個表的主鍵刪掉了，那麼 InnoDB 會自己生成一個長度為 6 位元組的 rowid 來作為主鍵。
這也就是排序模式裡面，rowid 名字的來歷。實際上它表示的是：每個引擎用來唯一標識數據行的信息。
對於有主鍵的 InnoDB 表來說，這個 rowid 就是主鍵 ID；
對於沒有主鍵的 InnoDB 表來說，這個 rowid 就是由系統生成的；
order by rand() 使用了內存臨時表，內存臨時表排序的時候使用了 rowid 排序方法。
磁碟臨時表
tmp_table_size 這個配置限制了內存臨時表的大小，默認值是 16M。
如果臨時表大小超過了 tmp_table_size，那麼內存臨時表就會轉成磁碟臨時表。
磁碟臨時表使用的引擎默認是 InnoDB，是由參數 internal_tmp_disk_storage_engine
控制的。
MySQL 5.6 版本引入的一個新的排序演算法，即： 優先隊列排序演算法 。
什麼時候採用優先隊列排序演算法？
當返回的數據結果集小於sort_buffer_size的大小時，採用 優先隊列排序演算法，此時，不需要臨時文件，所以，number_of_tmp_files = 0
什麼時候採用用臨時文件的演算法，也就是歸並排序演算法？
比如返回的結果 limit 10000，超過了sort_buffer_size的大小，只能採用 歸並排序演算法。
說明：優先隊列排序演算法與歸並演算法是結果集的排序演算法，跟全欄位排序與rowid排序不是一回事。
❼ MySQL簡單介紹——換個角度認識MySQL
1、InnoDB存儲引擎
Mysql版本>=5.5 默認的存儲引擎，MySQL推薦使用的存儲引擎。支持事務，行級鎖定，外鍵約束。事務安全型存儲引擎。更加註重數據的完整性和安全性。
存儲格式 : 數據，索引集中存儲，存儲於同一個表空間文件中。
InnoDB的行鎖模式及其加鎖方法： InnoDB中有以下兩種類型的行鎖：共享鎖（讀鎖： 允許事務對一條行數據進行讀取）和 互斥鎖（寫鎖： 允許事務對一條行數據進行刪除或更新）， 對於update，insert，delete語句，InnoDB會自動給設計的數據集加互斥鎖，對於普通的select語句，InnoDB不會加任何鎖。
InnoDB行鎖的實現方式： InnoDB行鎖是通過給索引上的索引項加鎖來實現的，如果沒有索引，InnoDB將通過隱藏的聚簇索引來對記錄加鎖。InnoDB這種行鎖實現特點意味著：如果不通過索引條件檢索數據，那麼InnoDB將對表中的所有記錄加鎖，實際效果跟表鎖一樣。
（1）在不通過索引條件查詢時，InnoDB會鎖定表中的所有記錄。
（2）Mysql的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果使用相同的索引鍵，是會出現沖突的。
（3）當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，但都是通過行鎖來對數據加鎖。
優點：
1、支持事務處理、ACID事務特性；
2、實現了SQL標準的四種隔離級別（ 原子性（ Atomicity ）、一致性（ Consistency ）、隔離性（Isolation ）和持續性（Durability ））；
3、支持行級鎖和外鍵約束；
4、可以利用事務日誌進行數據恢復。
5、鎖級別為行鎖，行鎖優點是適用於高並發的頻繁表修改，高並發是性能優於 MyISAM。缺點是系統消耗較大。
6、索引不僅緩存自身，也緩存數據，相比 MyISAM 需要更大的內存。
缺點：
因為它沒有保存表的行數，當使用COUNT統計時會掃描全表。
使用場景：
(1)可靠性要求比較高，或者要求事務；(2)表更新和查詢都相當的頻繁，並且表鎖定的機會比較大的情況。
2、 MyISAM存儲引擎
MySQL<= 5.5 MySQL默認的存儲引擎。ISAM：Indexed Sequential Access Method(索引順序存取方法)的縮寫，是一種文件系統。擅長與處理，高速讀與寫。
功能：
（1）支持數據壓縮存儲,但壓縮後的表變成了只讀表，不可寫；如果需要更新數據，則需要先解壓後更新。
（2）支持表級鎖定，不支持高並發；
（3）支持並發插入。寫操作中的插入操作，不會阻塞讀操作（其他操作）；
優點：
1.高性能讀取；
2.因為它保存了表的行數，當使用COUNT統計時不會掃描全表；
缺點：
1、鎖級別為表鎖，表鎖優點是開銷小，加鎖快；缺點是鎖粒度大，發生鎖沖動概率較高，容納並發能力低，這個引擎適合查詢為主的業務。
2、此引擎不支持事務，也不支持外鍵。
3、INSERT和UPDATE操作需要鎖定整個表；
使用場景：
(1)做很多count 的計算；(2)插入不頻繁，查詢非常頻繁；(3)沒有事務。
InnoDB和MyISAM一些細節上的差別：
1、InnoDB不支持FULLTEXT類型的索引，MySQL5.6之後已經支持（實驗性）。
2、InnoDB中不保存表的 具體行數，也就是說，執行select count() from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數即可。注意的是，當count()語句包含 where條件時，兩種表的操作是一樣的。
3、對於AUTO_INCREMENT類型的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中，可以和其他欄位一起建立聯合索引。
4、DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。
5、LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入數據後再改成InnoDB表，但是對於使用的額外的InnoDB特性（例如外鍵）的表不適用。
6、另外，InnoDB表的行鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表。
1．索引概述
利用關鍵字，就是記錄的部分數據（某個欄位，某些欄位，某個欄位的一部分），建立與記錄位置的對應關系，就是索引。索引的關鍵字一定是排序的。索引本質上是表欄位的有序子集，它是提高查詢速度最有效的方法。一個沒有建立任何索引的表，就相當於一本沒有目錄的書，在每次查詢時就會進行全表掃描，這樣會導致查詢效率極低、速度也極慢。如果建立索引，那麼就好比一本添加的目錄，通過目錄的指引，迅速翻閱到指定的章節，提升的查詢性能，節約了查詢資源。
2．索引種類
從索引的定義方式和用途中來看：主鍵索引，唯一索引，普通索引，全文索引。
無論任何類型，都是通過建立關鍵字與位置的對應關系來實現的。索引是通過關鍵字找對應的記錄的地址。
以上類型的差異：對索引關鍵字的要求不同。
關鍵字：記錄的部分數據（某個欄位，某些欄位，某個欄位的一部分）。
普通索引,index：對關鍵字沒有要求。
唯一索引,unique index：要求關鍵字不能重復。同時增加唯一約束。
主鍵索引,primary key：要求關鍵字不能重復，也不能為NULL。同時增加主鍵約束。
全文索引,fulltext key：關鍵字的來源不是所有欄位的數據，而是從欄位中提取的特別關鍵詞。
PS：這里主鍵索引和唯一索引的區別在於：主鍵索引不能為空值，唯一索引允許空值；主鍵索引在一張表內只能創建一個，唯一索引可以創建多個。主鍵索引肯定是唯一索引，但唯一索引不一定是主鍵索引。
3.索引原則
如果索引不遵循使用原則，則可能導致索引無效。
（1）列獨立
如果需要某個欄位上使用索引，則需要在欄位參與的表達中，保證欄位獨立在一側。否則索引不會用到索引, 例如這條sql就不會用到索引:select * from A where id+1=10;
（2）左原則
Like：匹配模式必須要左邊確定不能以通配符開頭。例如:select * from A where name like '%小明%' ，不會用到索引，而select * from A where name like '小明%' 就可以用到索引（name欄位有建立索引），如果業務上需要用到'%小明%'這種方式,有兩種方法:1.可以考慮全文索引,但mysql的全文索引不支持中文；2.只查詢索引列或主鍵列,例如:select name from A where name like '%小明%' 或 select id from A where name like '%小明%' 或 select id,name from A where name like '%小明%' 這三種情況都會用到name的索引;
復合索引：一個索引關聯多個欄位，僅僅針對左邊欄位有效果，添加復合索引時，第一個欄位很重要，只有包含第一個欄位作為查詢條件的情況才會使用復合索引(必須用到建索引時選擇的第一個欄位作為查詢條件,其他欄位的順序無關),而且查詢條件只能出現and拼接,不能用or,否則則無法使用索引.
（3）OR的使用
必須要保證 OR 兩端的條件都存在可以用的索引，該查詢才可以使用索引。
（4）MySQL智能選擇
即使滿足了上面說原則，MySQL也能棄用索引，例如：select * from A where id > 1;這里棄用索引的主要原因：查詢即使使用索引，會導致出現大量的隨機IO，相對於從數據記錄的第一條遍歷到最後一條的順序IO開銷，還要大。
4.索引的使用場景
（1）索引檢索：檢索數據時使用索引。
（2）索引排序: 如果order by 排序需要的欄位上存在索引，則可能使用到索引。
（3）索引覆蓋: 索引擁有的關鍵字內容，覆蓋了查詢所需要的全部數據，此時，就不需要在數據區獲取數據，僅僅在索引區即可。覆蓋就是直接在索引區獲取內容，而不需要在數據區獲取。例如: select name from A where name like '小明%';
建立索引索引時，不能僅僅考慮where檢索，同時考慮其他的使用場景。（在所有的where欄位上增加索引，就是不合理的）
5.前綴索引
前綴索引是建立索引關鍵字一種方案。通常會使用欄位的整體作為索引關鍵字。有時，即使使用欄位前部分數據，也可以去識別某些記錄。就比如一個班級里，我要找王xx，假如姓王的只有1個人，那麼就可以建一個關鍵字為'王'的前綴索引。語法：Index `index_name` (`index_field`(N))使用index_name前N個字元建立的索引。
6.索引失效
（1） 應盡量避免在 where 子句中使用 != 或 > 操作符，否則將引擎放棄使用索引而進行全表掃描；
（2） 應盡量避免在 where 子句中使用 or 來連接條件，如果一個欄位有索引，一個欄位沒有索引，將導致引擎放棄使用索引而進行全表掃描；
（3） 應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描；
（4）應盡量避免在 where 子句中對欄位進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描；如select id from t where num/2 = 100；
（5） 應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描；如：select id from t where substring(name,1,3) = 』abc』 ；
（6）應盡量避免在where子句中對欄位進行類型轉換，這將導致引擎放棄使用索引而進行全表掃描； 如果列類型是字元串，那一定要在條件中將數據使用引號引用起來,如select id from t where id = 1；如果id欄位在表設計中是varchar類型，那麼即使id列上存的是數字，在查詢時也一定要用varchar去匹配，sql應改為select id from t where id = '1'；
（7）應盡量避免在where子句中單獨引用復合索引里非第一位置的索引；
join 的兩種演算法：BNL 和 NLJ
NLJ（Nested Loop Join）嵌套循環演算法；以如下 SQL 為例：
select * from t1 join t2 on t1.a=t2.a
SQL 執行時內部流程是這樣的：
1. 先從 t1（假設這里 t1 被選為驅動表）中取出一行數據 X；
2. 從 X 中取出關聯欄位 a 值，去 t2 中進行查找，滿足條件的行取出；
3. 重復1、2步驟，直到表 t1 最後一行循環結束。
這就是一個嵌套循環的過程，如果在被驅動表上查找數據時可以使用索引，總的對比計算次數等於驅動表滿足 where 條件的行數。假設這里 t1、t2都是1萬行，則只需要 1萬次計算，這里用到的是Index Nested-Loops Join（INLJ，基於索引的嵌套循環聯接）。
如果 t1、t2 的 a 欄位都沒有索引，還按照上述的嵌套循環流程查找數據呢？每次在被驅動表上查找數據時都是一次全表掃描，要做1萬次全表掃描，掃描行數等於 1萬+1萬*1萬，這個效率很低，如果錶行數更多，掃描行數動輒幾百億，所以優化器肯定不會使用這樣的演算法，而是選擇 BNL 演算法；
BNLJ（Block Nested Loop Join）塊嵌套循環演算法；
1. 把 t1 表（假設這里 t1 被選為驅動表）滿足條件的數據全部取出放到線程的 join buffer 中；
2. 每次取 t2 表一行數據，去 joinbuffer 中進行查找，滿足條件的行取出，直到表 t2 最後一行循環結束。
這個演算法下，執行計劃的 Extra 中會出現 Using join buffer(Block Nested Loop)，t1、t2 都做了一次全表掃描，總的掃描行數等於 1萬+1萬。但是由於 joinbuffer 維護的是一個無序數組，每次在 joinbuffer 中查找都要遍歷所有行，總的內存計算次數等於1萬*1萬。另外如果 joinbuffer 不夠大放不下驅動表的數據，則要分多次執行上面的流程，會導致被驅動表也做多次全表掃描。
BNLJ相對於NLJ的優點在於，驅動層可以先將部分數據載入進buffer，這種方法的直接影響就是將大大減少內層循環的次數，提高join的效率。
例如：
如果內層循環有100條記錄，外層循環也有100條記錄，這樣的話，每次外層循環先將10條記錄放到buffer中，內層循環的100條記錄每條與這個buffer中的10條記錄進行匹配，只需要匹配內層循環總記錄數次即可結束一次循環（在這里，即只需要匹配100次即可結束），然後將匹配成功的記錄連接後放入結果集中，接著，外層循環繼續向buffer中放入10條記錄，同理進行匹配，並將成功的記錄連接後放入結果集。後續循環以此類推，直到循環結束，將結果集發給client為止。
可以發現，若用NLJ，則需要100 * 100次才可結束，BNLJ則需要100 / block_size * 100 = 10 * 100次就可結束,大大減少了循環次數。
JOIN 按照功能大致分為如下三類：
JOIN、STRAIGHT_JOIN、INNER JOIN（內連接,或等值連接）：取得兩個表中存在連接匹配關系的記錄。
LEFT JOIN（左連接）：取得左表（table1）完全記錄，即是右表（table2）並無對應匹配記錄。
RIGHT JOIN（右連接）：與 LEFT JOIN 相反，取得右表（table2）完全記錄，即是左表（table1）並無匹配對應記錄。
注意：mysql不支持Full join,不過可以通過UNION 關鍵字來合並 LEFT JOIN 與 RIGHT JOIN來模擬FULL join。
mysql 多表連接查詢方式，因為mysql只支持NLJ演算法,所以如果是小表驅動大表則效率更高；反之則效率下降；因此mysql對內連接或等值連接的方式做了一個優化,會去判斷join表的數據行大小,然後取數據行小的表為驅動表。
INNER JOIN、JOIN、WHERE等值連接和STRAIGHT_JOIN都能表示內連接，那平時如何選擇呢？一般情況下用INNER JOIN、JOIN或者WHERE等值連接，因為MySQL 會按照"小表驅動大表的策略"進行優化。當出現需要排序時，才考慮用STRAIGHT_JOIN指定某張表為驅動表。
兩表JOIN優化
a.當無order by條件時，根據實際情況，使用left/right/inner join即可，根據explain優化 ；
b.當有order by條件時，如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解釋語句；
1）如果第一行的驅動表為a，則效率會非常高，無需優化；
2）否則，因為只能對驅動表欄位直接排序的緣故，會出現using temporary，所以此時需要使用STRAIGHT_JOIN明確a為驅動表，來達到使用a.col上index的優化目的；或者使用left join且Where條件中不含b的過濾條件，此時的結果集為a的全集，而STRAIGHT_JOIN為inner join且使用a作為驅動表。註：使用STRAIGHT_JOIN雖然不會using temporary，但也不是一定就能提高效率，如果a表數據遠遠超過b表，那麼有可能使用STRAIGHT_JOIN時比原來的sql效率更低，所以怎麼使用STRAIGHT_JOIN，還是要視情況而定。
在使用left join（或right join）時，應該清楚的知道以下幾點：
(1). on與 where的執行順序
ON 條件（「A LEFT JOIN B ON 條件表達式」中的ON）用來決定如何從 B 表中檢索數據行。如果 B 表中沒有任何一行數據匹配 ON 的條件,將會額外生成一行所有列為 NULL 的數據,在匹配階段 WHERE 子句的條件都不會被使用。僅在匹配階段完成以後，WHERE 子句條件才會被使用。它將從匹配階段產生的數據中檢索過濾。
所以我們要注意：在使用Left (right) join的時候，一定要在先給出盡可能多的匹配滿足條件，減少Where的執行。
(2).注意ON 子句和 WHERE 子句的不同
即使右表的數據不滿足ON後面的條件,也會在結果集拼接一條為NULL的數據行,但WHERE後面的條件不一樣,右表不滿足WHERE的條件,左表關聯的數據也會被過濾掉。
(3).盡量避免子查詢，而用join
往往性能這玩意兒，更多時候體現在數據量比較大的時候，此時，我們應該避免復雜的子查詢。
（1）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很多時候用 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)
（2）Update 語句，如果只更改1、2個欄位，不要Update全部欄位，否則頻繁調用會引起明顯的性能消耗，同時帶來大量日誌。
（3）join語句,MySQL裡面的join是用小表去驅動大表,而由於MySQL join實現的原理就是做循環，比如left join就是對左邊的數據進行循環去驅動右邊的表,左邊有m條記錄匹配,右邊有n條記錄那麼就是做m次循環,每次掃描n行數據,總掃面行數是m*n行數據。左邊返回的結果集的大小就決定了循環的次數,故單純的用小表去驅動大表不一定的正確的,小表的結果集可能也大於大表的結果集,所以寫join的時候盡可能的先估計兩張表的可能結果集,用小結果集去驅動大結果集.值得注意的是在使用left/right join的時候,從表的條件應寫在on之後,主表應寫在where之後.否則MySQL會當作普通的連表查詢；
（4）select count(*) from table；這樣不帶任何條件的count會引起全表掃描，並且沒有任何業務意義，是一定要杜絕的；
（5）select * from t 這種語句要盡量避免，使用具體的欄位代替*，更有實際意義，需要什麼欄位就返回什麼欄位；
（6）數據量大的情況下，limit要慎用，因為使用limit m，n方式分頁時，mysql每次都是查詢前m+n條，然後舍棄前m條，所以m越大，偏移量越大，性能就越差。比如：select * from A limit 1000000,20這鍾，查詢效率就會非常低，當分頁的頁數大於一定的數量之後，就可以換種方式來分頁：select * from A a join （select id from A limit 1000000，20） b on a.id=b.id;
❽ 為什麼要用mysql索引優化資料庫
說一下不同引擎的優化，myisam讀的效果好，寫的效率差，這和它數據存儲格式，索引的指針和鎖的策略有關的，它的數據是順序存儲的（innodb數據存儲方式是聚簇索引），他的索引btree上的節點是一個指向數據物理位置的指針，所以查找起來很快，（innodb索引節點存的則是數據的主鍵，所以需要根據主鍵二次查找）；myisam鎖是表鎖，只有讀讀之間是並發的，寫寫之間和讀寫之間（讀和插入之間是可以並發的，去設置concurrent_insert參數，定期執行表優化操作，更新操作就沒有辦法了）是串列的，所以寫起來慢，並且默認的寫優先順序比讀優先順序高，高到寫操作來了後，可以馬上插入到讀操作前面去，如果批量寫，會導致讀請求餓死，所以要設置讀寫優先順序或設置多少寫操作後執行讀操作的策略;myisam不要使用查詢時間太長的sql，如果策略使用不當，也會導致寫餓死，所以盡量去拆分查詢效率低的sql,
❾ 關於MySQL中的表鎖和行鎖
mysql行鎖和表鎖
鎖是計算機協調多個進程或純線程並發訪問某一資源的機制。在資料庫中，除傳統的計算資源（CPU、RAM、I/O）的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據並發訪問的一致性、有效性是所在有資料庫必須解決的一個問題，鎖沖突也是影響資料庫並發訪問性能的一個重要因素。從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加復雜。
概述
相對其他資料庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制。
MySQL大致可歸納為以下3種鎖：
表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，並發度最低。
行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，並發度也最高。
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，並發度一般
MySQL表級鎖的鎖模式（MyISAM)
MySQL表級鎖有兩種模式：表共享鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。
對MyISAM的讀操作，不會阻塞其他用戶對同一表請求，但會阻塞對同一表的寫請求；
對MyISAM的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；
MyISAM表的讀操作和寫操作之間，以及寫操作之間是串列的。
上面的例子在LOCK TABLES時加了『local』選項，其作用就是在滿足MyISAM表並發插入條件的情況下，允許其他用戶在表尾插入記錄
在用LOCKTABLES給表顯式加表鎖是時，必須同時取得所有涉及表的鎖，並且MySQL支持鎖升級。也就是說，在執行LOCK TABLES後，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時，如果加的是讀鎖，那麼只能執行查詢操作，而不能執行更新操作。其實，在自動加鎖的情況下也基本如此，MySQL問題一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會出現死鎖（Deadlock Free）的原因
當concurrent_insert設置為0時，不允許並發插入。
當concurrent_insert設置為1時，如果MyISAM允許在一個讀表的同時，另一個進程從表尾插入記錄。這也是MySQL的默認設置。
當concurrent_insert設置為2時，無論MyISAM表中有沒有空洞，都允許在表尾插入記錄，都允許在表尾並發插入記錄。
通過指定啟動參數low-priority-updates，使MyISAM引擎默認給予讀請求以優先的權利。
通過執行命令SET LOW_PRIORITY_UPDATES=1，使該連接發出的更新請求優先順序降低。
通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先順序。
原性性（Actomicity）：事務是一個原子操作單元，其對數據的修改，要麼全都執行，要麼全都不執行。
一致性（Consistent）：在事務開始和完成時，數據都必須保持一致狀態。這意味著所有相關的數據規則都必須應用於事務的修改，以操持完整性；事務結束時，所有的內部數據結構（如B樹索引或雙向鏈表）也都必須是正確的。
隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部並發操作影響的「獨立」環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。
持久性（Durable）：事務完成之後，它對於數據的修改是永久性的，即使出現系統故障也能夠保持。
更新丟失（Lost Update）：當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題——最後的更新覆蓋了其他事務所做的更新。例如，兩個編輯人員製作了同一文檔的電子副本。每個編輯人員獨立地更改其副本，然後保存更改後的副本，這樣就覆蓋了原始文檔。最後保存其更改保存其更改副本的編輯人員覆蓋另一個編輯人員所做的修改。如果在一個編輯人員完成並提交事務之前，另一個編輯人員不能訪問同一文件，則可避免此問題
臟讀（Dirty Reads）：一個事務正在對一條記錄做修改，在這個事務並提交前，這條記錄的數據就處於不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些「臟」的數據，並據此做進一步的處理，就會產生未提交的數據依賴關系。這種現象被形象地叫做「臟讀」。
不可重復讀（Non-Repeatable Reads）：一個事務在讀取某些數據已經發生了改變、或某些記錄已經被刪除了！這種現象叫做「不可重復讀」。
幻讀（Phantom Reads）：一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為「幻讀」。
最低級別，只能保證不讀取物理上損壞的數據 是 是 是
共享鎖（s）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。
排他鎖（X）：允許獲取排他鎖的事務更新數據，阻止其他事務取得相同的數據集共享讀鎖和排他寫鎖。
第一種情況是：事務需要更新大部分或全部數據，表又比較大，如果使用默認的行鎖，不僅這個事務執行效率低，而且可能造成其他事務長時間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高該事務的執行速度。
第二種情況是：事務涉及多個表，比較復雜，很可能引起死鎖，造成大量事務回滾。這種情況也可以考慮一次性鎖定事務涉及的表，從而避免死鎖、減少資料庫因事務回滾帶來的開銷。
盡量使用較低的隔離級別
精心設計索引，並盡量使用索引訪問數據，使加鎖更精確，從而減少鎖沖突的機會。
選擇合理的事務大小，小事務發生鎖沖突的幾率也更小。
給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改數據的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產生死鎖。
不同的程序訪問一組表時，應盡量約定以相同的順序訪問各表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大減少死鎖的機會。
盡量用相等條件訪問數據，這樣可以避免間隙鎖對並發插入的影響。
不要申請超過實際需要的鎖級別；除非必須，查詢時不要顯示加鎖。
對於一些特定的事務，可以使用表鎖來提高處理速度或減少死鎖的可能
當一個線程獲得對一個表的寫鎖後，只有持有鎖線程可以對表進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。
MySQL表級鎖的鎖模式
MySQL的表鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。鎖模式的兼容如下表
MySQL中的表鎖兼容性
當前鎖模式/是否兼容/請求鎖模式
讀鎖 是 是 否
寫鎖 是 否 否
可見，對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫請求；MyISAM表的讀和寫操作之間，以及寫和寫操作之間是串列的！（當一線程獲得對一個表的寫鎖後，只有持有鎖的線程可以對表進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。）
如何加表鎖
MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程並不需要用戶干預，因此用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。在本書的示例中，顯式加鎖基本上都是為了方便而已，並非必須如此。
給MyISAM表顯示加鎖，一般是為了一定程度模擬事務操作，實現對某一時間點多個表的一致性讀取。
要特別說明以下兩點內容。
一個session使用LOCK TABLE 命令給表film_text加了讀鎖，這個session可以查詢鎖定表中的記錄，但更新或訪問其他表都會提示錯誤；同時，另外一個session可以查詢表中的記錄，但更新就會出現鎖等待。
當使用LOCK TABLE時，不僅需要一次鎖定用到的所有表，而且，同一個表在SQL語句中出現多少次，就要通過與SQL語句中相同的別名鎖多少次，否則也會出錯！
並發鎖
在一定條件下，MyISAM也支持查詢和操作的並發進行。
MyISAM存儲引擎有一個系統變數concurrent_insert，專門用以控制其並發插入的行為，其值分別可以為0、1或2。
可以利用MyISAM存儲引擎的並發插入特性，來解決應用中對同一表查詢和插入鎖爭用。例如，將concurrent_insert系統變數為2，總是允許並發插入；同時，通過定期在系統空閑時段執行OPTIONMIZE TABLE語句來整理空間碎片，收到因刪除記錄而產生的中間空洞。
MyISAM的鎖調度
前面講過，MyISAM存儲引擎的讀和寫鎖是互斥，讀操作是串列的。那麼，一個進程請求某個MyISAM表的讀鎖，同時另一個進程也請求同一表的寫鎖，MySQL如何處理呢？答案是寫進程先獲得鎖。不僅如此，即使讀進程先請求先到鎖等待隊列，寫請求後到，寫鎖也會插到讀請求之前！這是因為MySQL認為寫請求一般比讀請求重要。這也正是MyISAM表不太適合於有大量更新操作和查詢操作應用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。這種情況有時可能會變得非常糟糕！幸好我們可以通過一些設置來調節MyISAM的調度行為。
雖然上面3種方法都是要麼更新優先，要麼查詢優先的方法，但還是可以用其來解決查詢相對重要的應用（如用戶登錄系統）中，讀鎖等待嚴重的問題。
另外，MySQL也提供了一種折中的辦法來調節讀寫沖突，即給系統參數max_write_lock_count設置一個合適的值，當一個表的讀鎖達到這個值後，MySQL變暫時將寫請求的優先順序降低，給讀進程一定獲得鎖的機會。
上面已經討論了寫優先調度機制和解決辦法。這里還要強調一點：一些需要長時間運行的查詢操作，也會使寫進程「餓死」！因此，應用中應盡量避免出現長時間運行的查詢操作，不要總想用一條SELECT語句來解決問題。因為這種看似巧妙的SQL語句，往往比較復雜，執行時間較長，在可能的情況下可以通過使用中間表等措施對SQL語句做一定的「分解」，使每一步查詢都能在較短時間完成，從而減少鎖沖突。如果復雜查詢不可避免，應盡量安排在資料庫空閑時段執行，比如一些定期統計可以安排在夜間執行。
InnoDB鎖問題
InnoDB與MyISAM的最大不同有兩點：一是支持事務（TRANSACTION）；二是採用了行級鎖。
行級鎖和表級鎖本來就有許多不同之處，另外，事務的引入也帶來了一些新問題。
1.事務（Transaction）及其ACID屬性
事務是由一組SQL語句組成的邏輯處理單元，事務具有4屬性，通常稱為事務的ACID屬性。
2.並發事務帶來的問題
相對於串列處理來說，並發事務處理能大大增加資料庫資源的利用率，提高資料庫系統的事務吞吐量，從而可以支持可以支持更多的用戶。但並發事務處理也會帶來一些問題，主要包括以下幾種情況。
3.事務隔離級別
在並發事務處理帶來的問題中，「更新丟失」通常應該是完全避免的。但防止更新丟失，並不能單靠資料庫事務控制器來解決，需要應用程序對要更新的數據加必要的鎖來解決，因此，防止更新丟失應該是應用的責任。
「臟讀」、「不可重復讀」和「幻讀」，其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決。資料庫實現事務隔離的方式，基本可以分為以下兩種。
一種是在讀取數據前，對其加鎖，阻止其他事務對數據進行修改。
另一種是不用加任何鎖，通過一定機制生成一個數據請求時間點的一致性數據快照（Snapshot），並用這個快照來提供一定級別（語句級或事務級）的一致性讀取。從用戶的角度，好像是資料庫可以提供同一數據的多個版本，因此，這種技術叫做數據多版本並發控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也經常稱為多版本資料庫。
資料庫的事務隔離級別越嚴格，並發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上「串列化」進行，這顯然與「並發」是矛盾的，同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對「不可重復讀」和「幻讀」並不敏感，可能更關心數據並發訪問的能力。
為了解決「隔離」與「並發」的矛盾，ISO/ANSI SQL92定義了4個事務隔離級別，每個級別的隔離程度不同，允許出現的副作用也不同，應用可以根據自己業務邏輯要求，通過選擇不同的隔離級別來平衡＂隔離＂與＂並發＂的矛盾
事務4種隔離級別比較
隔離級別/讀數據一致性及允許的並發副作用 讀數據一致性 臟讀 不可重復讀 幻讀
未提交讀（Read uncommitted）
已提交度（Read committed） 語句級 否 是 是
可重復讀（Repeatable read） 事務級 否 否 是
可序列化（Serializable） 最高級別，事務級 否 否 否
最後要說明的是：各具體資料庫並不一定完全實現了上述4個隔離級別，例如，Oracle只提供Read committed和Serializable兩個標准級別，另外還自己定義的Read only隔離級別：SQL Server除支持上述ISO/ANSI SQL92定義的4個級別外，還支持一個叫做＂快照＂的隔離級別，但嚴格來說它是一個用MVCC實現的Serializable隔離級別。MySQL支持全部4個隔離級別，但在具體實現時，有一些特點，比如在一些隔離級下是採用MVCC一致性讀，但某些情況又不是。
獲取InonoD行鎖爭用情況
可以通過檢查InnoDB_row_lock狀態變數來分析系統上的行鎖的爭奪情況：
如果發現爭用比較嚴重，如Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比較高，還可以通過設置InnoDB Monitors來進一步觀察發生鎖沖突的表、數據行等，並分析鎖爭用的原因。
InnoDB的行鎖模式及加鎖方法
InnoDB實現了以下兩種類型的行鎖。
另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。
意向共享鎖（IS）：事務打算給數據行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的IS鎖。
意向排他鎖（IX）：事務打算給數據行加排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的IX鎖。
InnoDB行鎖模式兼容性列表
如果一個事務請求的鎖模式與當前的鎖兼容，InnoDB就請求的鎖授予該事務；反之，如果兩者兩者不兼容，該事務就要等待鎖釋放。
意向鎖是InnoDB自動加的，不需用戶干預。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及及數據集加排他鎖（X）；對於普通SELECT語句，InnoDB會自動給涉及數據集加排他鎖（X）；對於普通SELECT語句，InnoDB不會任何鎖；事務可以通過以下語句顯示給記錄集加共享鎖或排鎖。
共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他鎖（X）：SELECT * FROM table_name WHERE ...FOR UPDATE
用SELECT .. IN SHARE MODE獲得共享鎖，主要用在需要數據依存關系時確認某行記錄是否存在，並確保沒有人對這個記錄進行UPDATE或者DELETE操作。但是如果當前事務也需要對該記錄進行更新操作，則很有可能造成死鎖，對於鎖定行記錄後需要進行更新操作的應用，應該使用SELECT ... FOR UPDATE方式獲取排他鎖。
InnoDB行鎖實現方式
InnoDB行鎖是通過索引上的索引項來實現的，這一點MySQL與Oracle不同，後者是通過在數據中對相應數據行加鎖來實現的。InnoDB這種行鎖實現特點意味者：只有通過索引條件檢索數據，InnoDB才會使用行級鎖，否則，InnoDB將使用表鎖！
在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖沖突，從而影響並發性能。
什麼時候使用表鎖
對於InnoDB表，在絕大部分情況下都應該使用行級鎖，因為事務和行鎖往往是我們之所以選擇InnoDB表的理由。但在個另特殊事務中，也可以考慮使用表級鎖。
當然，應用中這兩種事務不能太多，否則，就應該考慮使用MyISAM表。
在InnoDB下 ，使用表鎖要注意以下兩點。
（1）使用LOCK TALBES雖然可以給InnoDB加表級鎖，但必須說明的是，表鎖不是由InnoDB存儲引擎層管理的，而是由其上一層MySQL Server負責的，僅當autocommit=0、innodb_table_lock=1（默認設置）時，InnoDB層才能知道MySQL加的表鎖，MySQL Server才能感知InnoDB加的行鎖，這種情況下，InnoDB才能自動識別涉及表級鎖的死鎖；否則，InnoDB將無法自動檢測並處理這種死鎖。
（2）在用LOCAK TABLES對InnoDB鎖時要注意，要將AUTOCOMMIT設為0，否則MySQL不會給表加鎖；事務結束前，不要用UNLOCAK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務；COMMIT或ROLLBACK產不能釋放用LOCAK TABLES加的表級鎖，必須用UNLOCK TABLES釋放表鎖，正確的方式見如下語句。
關於死鎖
MyISAM表鎖是deadlock free的，這是因為MyISAM總是一次性獲得所需的全部鎖，要麼全部滿足，要麼等待，因此不會出現死鎖。但是在InnoDB中，除單個SQL組成的事務外，鎖是逐步獲得的，這就決定了InnoDB發生死鎖是可能的。
發生死鎖後，InnoDB一般都能自動檢測到，並使一個事務釋放鎖並退回，另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及鎖的情況下，InnoDB並不能完全自動檢測到死鎖，這需要通過設置鎖等待超時參數innodb_lock_wait_timeout來解決。需要說明的是，這個參數並不是只用來解決死鎖問題，在並發訪問比較高的情況下，如果大量事務因無法立即獲取所需的鎖而掛起，會佔用大量計算機資源，造成嚴重性能問題，甚至拖垮資料庫。我們通過設置合適的鎖等待超時閾值，可以避免這種情況發生。
通常來說，死鎖都是應用設計的問題，通過調整業務流程、資料庫對象設計、事務大小、以及訪問資料庫的SQL語句，絕大部分都可以避免。下面就通過實例來介紹幾種死鎖的常用方法。
（1）在應用中，如果不同的程序會並發存取多個表，應盡量約定以相同的順序為訪問表，這樣可以大大降低產生死鎖的機會。如果兩個session訪問兩個表的順序不同，發生死鎖的機會就非常高！但如果以相同的順序來訪問，死鎖就可能避免。
（2）在程序以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低死鎖的可能。
（3）在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應該先申請共享鎖，更新時再申請排他鎖，甚至死鎖。
（4）在REPEATEABLE-READ隔離級別下，如果兩個線程同時對相同條件記錄用SELECT...ROR UPDATE加排他鎖，在沒有符合該記錄情況下，兩個線程都會加鎖成功。程序發現記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這么做，就會出現死鎖。這種情況下，將隔離級別改成READ COMMITTED，就可以避免問題。
（5）當隔離級別為READ COMMITED時，如果兩個線程都先執行SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現鎖等待，當第1個線程提交後，第2個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第3個線程又來申請排他鎖，也會出現死鎖。對於這種情況，可以直接做插入操作，然後再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執行ROLLBACK釋放獲得的排他鎖。
盡管通過上面的設計和優化等措施，可以大減少死鎖，但死鎖很難完全避免。因此，在程序設計中總是捕獲並處理死鎖異常是一個很好的編程習慣。
如果出現死鎖，可以用SHOW INNODB STATUS命令來確定最後一個死鎖產生的原因和改進措施。
總結
對於MyISAM的表鎖，主要有以下幾點
（1）共享讀鎖（S）之間是兼容的，但共享讀鎖（S）和排他寫鎖（X）之間，以及排他寫鎖之間（X）是互斥的，也就是說讀和寫是串列的。
（2）在一定條件下，MyISAM允許查詢和插入並發執行，我們可以利用這一點來解決應用中對同一表和插入的鎖爭用問題。
（3）MyISAM默認的鎖調度機制是寫優先，這並不一定適合所有應用，用戶可以通過設置LOW_PRIPORITY_UPDATES參數，或在INSERT、UPDATE、DELETE語句中指定LOW_PRIORITY選項來調節讀寫鎖的爭用。
（4）由於表鎖的鎖定粒度大，讀寫之間又是串列的，因此，如果更新操作較多，MyISAM表可能會出現嚴重的鎖等待，可以考慮採用InnoDB表來減少鎖沖突。
對於InnoDB表，主要有以下幾點
（1）InnoDB的行銷是基於索引實現的，如果不通過索引訪問數據，InnoDB會使用表鎖。
（2）InnoDB間隙鎖機制，以及InnoDB使用間隙鎖的原因。
（3）在不同的隔離級別下，InnoDB的鎖機制和一致性讀策略不同。
（4）MySQL的恢復和復制對InnoDB鎖機制和一致性讀策略也有較大影響。
（5）鎖沖突甚至死鎖很難完全避免。
在了解InnoDB的鎖特性後，用戶可以通過設計和SQL調整等措施減少鎖沖突和死鎖，包括：