update的執行從客戶端=> ··· => 執行引擎 是一樣的流程，都要先查到這條數據，然後再去更新。要理解 UPDATE 流程我們先來看看，Innodb的架構模型。
上一張MYSQL 官方InnoDB架構圖：
連接器(JDBC 、ODBC等) =>
[MYSQL 內部
[Connection Pool] (授权、线程复用、连接限制、内存检测等) => [SQL Interface] (DML、DDL、Views等) [Parser] (Query Translation、Object privilege) [Optimizer] (Access Paths、 统计分析) [Caches & Buffers] => [Pluggable Storage Engines]复制代码
]
=> [File]
#這裡有個關鍵點，當我們去查詢資料時候會先拿著我們目前查詢的page 去buffer pool 中查詢目前page#是否在緩衝池中。如果在，則直接獲取。
而如果是update運算時，則會直接修改 Buffer中的值。這時候，buffer pool中的資料就和我們磁碟中實際儲存的資料不一致了，稱為髒頁。每隔一段時間，Innodb儲存引擎就會把髒頁資料刷入磁碟。一般來說當更新一條數據，我們需要將數據給讀取到buffer中修改，然後寫回磁碟，完成一次 落盤IO 操作。
為了提高update的操作效能，Mysql在記憶體中做了最佳化，可以看到，在架構圖的緩衝池中有一塊區域叫做：change buffer。顧名思義，給change後的數據，做buffer的，當更新一個沒有unique index 的數據時，直接將修改的數據放到change buffer#，然後透過merge 操作完成更新，從而減少了那一次落盤的IO 操作。
沒有唯一索引的資料更新時，為什麼必須要沒有唯一索引的資料更新時才能直接放入change buffer呢？如果是有唯一約束的欄位，我們在更新資料後，可能更新的資料和已經存在的資料有重複，所以只能從磁碟中把所有資料讀出來比對才能確定唯一性。 寫多讀少 的時候，就可以透過增加innodb_change_buffer_max_size 來調整change buffer在 buffer pool 中所佔的比例，預設25（即：25%）#有四種情況：
redo log寫滿的時候，merge到磁碟談到redo，就要談到innodb的crash safe，使用WAL 的方式實作（write Ahead Logging，在寫之前先記錄日誌）
這樣就可以在，當資料庫崩潰的後，直接從redo log中恢復數據，保證資料的正確性
redo log 預設儲存在兩個檔案中ib_logfile0 ib_logfile1，這兩個檔案都是固定大小的。為什麼需要固定大小？
這是因為redo log的順序讀取 的特性造成的，必須是連續的儲存空間
看一張圖
一般我們的資料都是分散在磁碟上的：
機械硬碟的讀寫順序是：
固態讀寫：
其實不管機械還是固態，我們去儲存時，都是透過檔案系統與磁碟打交道的，而他們打交道的方式就有兩個。 隨機讀寫和順序讀寫
區塊（預設1block= 8扇區=4K）一串連續的區塊中，這樣讀取速度就大大提升了
看到buffer pool中的Log Buffer，其就是用來寫redo log 之前存在的緩衝區
在這裡，redo log具體的執行策略有三種：
，只需要每秒寫redo log 磁碟資料一次，效能高，但會造成資料1s 內的一致性問題。適用於強實時性，弱一致性，例如評論區評論，同時寫入磁碟，效能最差，一致性最高。適用於弱實時性，強一致性,例如支付場景，同時寫到os buffer（其會每秒呼叫fsync 將資料刷入磁碟），效能好，安全性也高。這是即時性適中 一致性適中的，例如訂單類別。
innodb_flush_log_at_trx_commit就可以設定執行策略。預設為1
主要用於加快查詢頁。在查詢時，Innodb透過監視索引搜尋的機制來判斷目前查詢是否能走Hash索引。例如LIKE運算子和% 通配符就不能走。
ibdata1的檔案中，其中包含：
寫入資料頁時，不是直接寫入到文件，而是先寫入到這個區域。這樣做的好處的是，一但作業系統，檔案系統或是mysql掛掉，可以直接從這個Buffer中取得資料。
.ibd 的文件，儲存資料和索引。
可以使得 ALTER TABLE與 TRUNCATE TABLE 效能得到很好的提升。例如ALTER TABLE，相較於對駐留在共享表空間中的表，在修改表時，會進行表複製操作，這可能會增加表空間佔用的磁碟空間量。此類操作可能需要與表中的資料以及索引一樣多的額外空間。該空間不會像每表檔案表空間那樣釋放回作業系統。
Drop table的時候會影響效能（除非你自己管理了碎片）fsync一次刷入資料到文件中文件句柄， 以提供維持對檔案的持續存取共享表空間，他可以儲存多個表的資料每個表表空間 小
儲存在一個叫 ibtmp1 的檔案中。正常情況下Mysql啟動的時候會建立臨時表空間，停止的時候會刪除臨時表空間。並且它能夠自動擴容。
原子性，即當修改到一半，出現異常，可以透過Undo 日誌回滾。 系統表空間``撤銷表空間``臨時表空間中，如架構圖所示。 前面已經介紹過
origin，返給執行器modification
modification刷入內存，Buffer Pool的Change Buffer
#說了Undo、Redo也順便說一下Bin log.
innodb引擎沒有多大關係，我們前面說的兩種日誌，都在是innodb引擎層的。而Bin log是處於服務層的。所以他能被各引擎通用Bin log 是以事件的形式，記錄了各個 DDL DML 語句，它是一種邏輯意義上的日誌。 主從複製，從伺服器拿到主伺服器的bin log日誌，然後執行。 資料復原，拿到某個時間段的日誌，重新執行一遍。 索引試試看#華麗的分割線
要想徹底弄清楚InnoDB中的索引是個什麼東西，就必須要了解它的在檔案儲存層級
Pages, Extents, Segments, and Tablespaces
它們的關係是：
extent 大小為1M 即64個16KB的Page。平常我們檔案系統所說的頁大小是 4KB，包含 8 個 512Byte的磁區。 
所以有時候，我們被要求主鍵為什麼要有序的原因就是，如果我們在一個有序的欄位上，建立索引，然後插入資料。
在儲存的時候，innodb就會依照順序一個個儲存到 頁 上，存滿一個頁再去申請新的頁，然後接著存。
但如果我們的欄位是無序的，儲存的位置就會在不同的頁面上。當我們的資料儲存到一個已經被 存滿的頁上時，就會造成頁分裂，從而形成碎片。
B 樹圖所示，子節點上儲存行資料，而索引的排列的順序和索引鍵值順序一致的話就是叢集索引。主鍵索引就是叢集索引，除了主鍵索引，其他所以都是輔助索引
輔助索引，它的葉子節點上只儲存自己的值和主鍵索引的值。這就意味著，如果我們透過輔助索引查詢所有數據，就會先去尋找輔助索引中的主鍵鍵值，然後再去主鍵索引#裡面，查到相關資料。這個過程稱為回表
rowid 如果沒有主鍵索引怎麼辦呢？ 叢集索引。 rowid 的東西，根據這個id來創建聚簇索引
搞清楚什麼是索引，結構是什麼之後。 讓我們來看看，什麼時候我們要用到索引，理解了這些能更好的幫助我們創建正確高效的索引
離散度低不建索引，也就是資料之間相差不大的就沒必要建立索引。 （因為建立索引，在查詢的時候，innodb大多資料都是相同的，我走索引 和全表沒什麼差別就會直接全表查詢）。如 性別欄位。這樣反而浪費了大量的儲存空間。
聯合欄位索引，例如idx(name, class_name)
select * from stu where class_name = xx and name = lzw 查詢時，也能走idx 這個索引的，因為優化器將SQL最佳化為了name = lzw and class_name = xx
select ··· where name = lzw 的時候，不需要建立一個單獨的name#索引，會直接走idx這個索引覆蓋索引。如果我們這次查詢的所有資料全都包含在索引裡面了，就不需要再 回表去查詢了。例如：select class_name from stu where name =lzw
索引條件下推(index_condition_pushdown)
select * from stu where name = lzw and class_name like '%xx'
索引條件下推，因為後面是like ' %xx'的查詢條件，所以這裡先根據name 走idx聯合索引 查詢到幾個資料後，再回表查詢到全量row資料，然後在server層進行like 過濾找到資料引擎層對like也進行過濾了，相當於把server層這個過濾操作下推到引擎層了。如圖所示：
頁分裂，索引依序存儲，如果儲存頁滿了，再去插入就會造成頁分割）函數的時候不會使用索引，所以沒必要額外建select count(distinct left(name, 10))/count(*) 來看離散度，決定到底提取前幾位）優化器決定的。例如你使用了 Cost Base Optimizer 基於開銷的優化器，那種開銷小就用哪種優化。 又一個華麗的分割線
先回顧我們熟能詳的幾個基本概念：
#前提，在一個交易中：
SQL92 標準規定： (並發度從左到右，依序降低)
RR 的MVCC實作，圖中回溯id 初始值不應該是0而是NULL，這裡為了方便寫成0
RR 是同一個交易任何一條都會建立一個版本與LBCC的結合，InnoDB能解決對於不加鎖條件下的幻讀的情況。而不必像 Serializable 一樣，必須讓交易串行進行，無任何並發。 下面我們來深入研究一下
是如何實作RR 交易隔離層級的鎖定深入MVCC在Innodb的實作
最基本鎖定的類型Record Locks 記錄鎖定
高階鎖定Insert Intention Locks 插入鎖定
會使用。顯示使用在語句後面加上for update。 意向鎖都是由資料庫自己維護的。 （主要作用是給表打一個標記你建了一個Primary key， 就是聚集索引（存儲的是
完整的資料沒有主鍵，但有一個Unique key 而是都不是null的，則會根據這個key來建立叢集索引那上面兩種都沒有呢，別擔心，innodb自己維護了一個叫rowid索引
的表，進行加鎖查詢時，資料庫其實是不知道到底要查哪些資料的，整張表可能都會用到。所以索性就鎖整張表。
輔助索引加寫鎖，例如select * from where name = 'xxx' for update 最後要回表查主鍵上的信息，所以這個時候除了鎖輔助索引還要鎖主鍵索引
首先上三個概念，有這麼一組資料：主鍵是 1，3，6，9 在儲存時候有如下：x 1 x 3 x x 6 x x x 9 x···
記錄鎖，鎖的是每個記錄，也就是 1，3，6，9
間隙鎖，鎖的是記錄間隙，每個 x，(-∞,1), (1,3), (3,6), (6,9), (9, ∞)
臨鎖，鎖的是(-∞,1], (1,3], (3,6], (6,9], (9, ∞] 左開右閉的區間
首先這三種鎖都是排它鎖， 且臨鍵鎖= 記錄鎖間隙鎖
select * from xxx where id = 3 for update時，產生記錄鎖定select * from xxx where id = 5 for update 時，產生間隙鎖=> 鎖住了(3,6)，這裡要格外注意一點：間隙鎖之間是不衝突的。select * from xxx where id = 5 for update 時，產生臨鍵鎖=> 鎖住了(3,6] , mysql預設使用臨鍵鎖，如果不滿足1 ，2 情況則他的行鎖的都是臨鍵鎖Record Lock 行鎖防止別的交易修改或刪除，Gap Lock 間隙鎖防止別的交易新增，Gap Lock 和Record Lock結合形成的Next- Key鎖定共同解決RR等級在寫資料時的幻讀問題。show status like 'innodb_row_lock_%'#select * from information_schema.INNODB_TRX 可以查看到目前正在運作和被鎖定的交易show full processlist# = select * from information_ = select * from mainformation_listsche#.process#list ## 可以查詢出是哪個使用者 在哪台機器host的哪個連接埠上 連接哪個資料庫 執行什麼指令 的狀態與時間
要想獲得更好的查詢效能，可以從這張查詢執行過程
新增連線池，避免每次都新建、銷毀連線
CPU才能真正去執行執行緒。而作業系統因為用時間分片的技術，讓我們以為一個CPU核心執行了多個執行緒。 CPU在某個時間段只能執行一個執行緒，所以無論我們怎麼增加並發， CPU還是只能在這個時段處理這麼多資料。 CPU處理不了這麼多數據，又怎麼會變慢？因為時間分片，當多個執行緒看起來在"同時執行"，其實他們之間的上下文切換十分耗時I/O操作，這個時候，CPU就可以把時間，分片給其他線程，以提升處理效率和速度I/O等待時間非常短，所以我們就無法加入太多連線數執行緒數= ((核心數* 2) 有效磁碟數)。例如一台i7 4core 1hard disk的機器，就是4 * 2 1 = 9很多CPU運算與I/O的場景 例如：設定最大執行緒數等如果並發非常大，就不能讓他們全打到資料庫上，在客戶端連接資料庫查詢時，加入如Redis這種三方快取
既然我們一個資料庫承受不了巨大的並發，那為什麼不多再增加幾台機器呢？ 主從複製原理圖
從圖中我們不難看出、Mysql主從複製讀寫分離 異步複製的特性。
Binary Log寫入relay log之後，slave都會把最新讀取到的 Binary Log Position記錄到master info上，下次就直接從這個位置去取。 上面這種異步的主從複製，很明顯的一個問題就是，更新不及時的問題。當寫入一個資料後，馬上有使用者讀取，讀取的還是之前的數據，也就是存在著延遲。
要解決延時的問題，就需要引入事務
failover動作，即主節點掛掉，選舉從節點後，能快速自動避免資料遺失。 將資料分類劃分，分成不同表，減少對單一表造成過多鎖定操作影響效能
開啟show_query_log，執行時間超過變數long_query_time的SQL會被記錄下來。
可以使用mysqldumpslow /var/lib/mysql/mysql-slow.log，還有很多外掛可以提供比這個更優雅的分析，這裡就不詳細講了。
任何SQL在寫完之後都應該explain一下
left/right join導致效能低left/right join會直接指定驅動表，在MYSQL中，預設使用Nest loop join進行表格關聯（即透過驅動表的結果集作為循環基礎數據，然後透過此集合中的每個數據篩選下一個關聯表的數據，最後合併結果，得出我們常說的臨時表）。 驅動表的資料是 百萬千萬等級的，可想而知這聯表查詢得有多慢。但反過來，如果以小表作為驅動表，借助千萬級表的索引查詢就能變得很快。 驅動表，那麼請交給優化器來決定，例如：select xxx from table1, table2, table3 where ·· ·，優化器會將查詢記錄行數少的表當作驅動表。 驅動表，那麼請拿好Explain武器，在Explain的結果中，第一個就是基礎驅動表
表排序也是有很大的效能差異，我們盡量對驅動表進行排序，而不要對臨時表，也就是合併後的結果集進行排序。即執行計劃中出現了 using temporary，就需要進行最佳化。 普通查詢和複雜查詢（聯合查詢、子查詢等）SIMPLE，查詢不包含子查詢或UNIONPRIMARY，如果查詢包含複雜查詢的子結構，那麼就需要用到主鍵查詢SUBQUERY，在select或where中包含子查詢
DERIVED，在from中包含子查詢UNION RESULT，從union 表格查詢子查詢越來越快const或system 常數等級的掃描，查詢表最快的一種，system是const的特殊情況（表中只有一條資料）eq_ref 唯一性索引掃描ref 非唯一性索引掃描range 索引的範圍掃描，例如between、等範圍查詢#index（index full）掃描全部索引樹 掃描全表
，不需要存取表或索引
到了。如果沒有則為NULL
一起被使用
server層過濾再使用where來過濾結果集DISTINCT、排序、分組server層這個過濾操作下推到引擎層MyISAM存儲引擎插入、更新、查詢
等並發數很多時，可以使用#相關免費學習推薦：mysql影片教學
以上是一篇文章讓你去懂MYSQL底層原理的詳細內容。更多資訊請關注PHP中文網其他相關文章！
