第1章 MySQL體系結構和存儲引擎

1.1數據庫和實例

數據庫：物理操作系統文件或其他形式文件類型的集合。實例：MySQL數據庫由后臺線程以及一個共享內存區組成。共享內存可以被運行 的后臺線程所共享。數據庫實例才是真正用于操作數據庫文件的。

啟動MySQL數據庫實例，并通過命令ps觀察MySQL數據庫啟動后的進程情況：

./mysqld_safe&ps -ef I grep mysqld

讀取配置文件順序，以讀取到的最后一個配置文件中的參數為準

mysql --help | grep my.cnf #/etc/my.cnf?/etc/mysql/my.cnf?/usr/etc/my.cnf?~/.my.cnf

1.2 MySQL體系結構

從概念上來說數據庫是文件的集合，是依照某種數據模型組織起來并存放于二級存儲器中的數據集合；

數據庫實例是程序，是位 于用戶與操作系統之間的一層數據管理軟件，用戶對數據庫數據的任何操作，包括數據 庫定義、數據査詢、數據維護、數據庫運行控制等都是在數據庫實例下進行的，應用程序只有通過數據庫實例才能和數據庫打交道。

MySQL數據庫的體系結構。

MySQL由以下幾部分組成:

• 連接池組件

• 管理服務和工具組件

• SQL接口組件

• 査詢分析器組件

• 優化器組件

• 緩沖(Cache)組件

• 插件式存儲引擎

• 物理文件

MySQL數據庫區別于其他數據庫的最重要的一個特點就是其插件式的表存儲引擎。存儲引擎是基于表的，而不是數據庫。

1.3 MySQL存儲引擎

MySQL數據庫獨有的插件式體系結構。存儲引擎的好處是，每個存儲引擎都有各自的特點，能夠根據具體的應用建立不同存儲引擎表。

InnoDB存儲引擎

• InnoDB存儲引擎支持事務，其設計目標主要面向在線事務處理(OLTP)的應用。其特點是行鎖設計、支持外鍵，并支持類似于Oracle的非鎖定讀，即默認讀取操作不會產生鎖。

• InnoDB通過使用多版本并發控制(MVCC)來獲得高并發性，并且實現了 SQL 標準的4種隔離級別，默認為REPEATABLE級別。

• 同時，使用一種被稱為next-key locking的策略來避免幻讀(phantom)現象的產生。

• 除此之外，InnoDB儲存引擎還提供了插入緩沖(insert buffer)>二次寫(double write)、自適應哈希索引(adaptive hash index).預讀(read ahead)等高性能和高可用的功能。

• 對于表中數據的存儲，InnoDB存儲引擎采用了聚集(clustered)的方式，因此每張表的存儲都是按主鍵的順序進行存放。如果沒有顯式地在表定義時指定主鍵，InnoDB存 儲引擎會為每一行生成一個6字節的ROWID,并以此作為主鍵。

MylSAM存儲引擎

MylSAM存儲引擎不支持事務、表鎖設計，支持全文索引，主要面向一些OLAP 數據庫應用。在MySQL 5.5.8版本之前MylSAM存儲引擎是默認的存儲引擎(除 Windows版本外)。MylSAM存儲引擎的緩沖池只緩存(cache)索引文件，而不緩沖數據文件。

MylSAM存儲引擎表由MYD和MYI組成，MYD用來存放數據文件，MYI用來存 放索引文件。

1.4各存儲引擎之間的比較

常用MySQL存儲引擎之間的不同之處，包括存儲容量的限制、事務支持、鎖的粒度、MVCC支持、支持的索引、備份和復制等。

查看當前使用的MySQL數據庫所支持的存儲引擎

SHOW ENGINES\G

示例數據庫來簡單顯示各存儲引擎之間的不同

CREATE TABLE mytest Engine=MyISAM AS SELECT * FROM A;ALTER TABLE mytest Engine=InnoDB;ALTER TABLE mytest Engine=ARCHIVE;

從表的大小方面簡單地揭示了各存儲引擎的不同：InnoDB>MyISAM>ARCHIVE

第2章InnoDB存儲引擎

InnoDB是事務安全的MySQL存儲引擎。

2.1 InnoDB存儲引擎概述

MySQL 5.5版本開始是默認的表存儲引擎。支持ACID事務，其特點是行鎖設計、支持MVCC、支持外鍵、提供一致性非鎖定讀，同時被設計用來最有效地利用以及使用內存和CPU。

2.3 InnoDB體系架構

InnoDB存儲引擎有多個內存塊，可以認為這些內存塊組成了一個大的內存池，負責 如下工作：維護所有進程/線程需要訪問的多個內部數據結構。緩存磁盤上的數據，方便快速地讀取，同時在對磁盤文件的數據修改之前在這里 緩存。重做日志(redo log)緩沖...

后臺線程的主要作用是負責刷新內存池中的數據，保證緩沖池中的內存緩存的是最近的數據。此外將已修改的數據文件刷新到磁盤文件，同時保證在數據庫發生異常的情 況下InnoDB能恢復到正常運行狀態。

2.3.1后臺線程

InnoDB存儲引擎是多線程的模型，因此其后臺有多個不同的后臺線程，負責處理不

Master ThreadMaster Thread是一個非常核心的后臺線程，主要負責將緩沖池中的數據異步刷新到磁盤，保證數據的一致性，包括臟頁的刷新、合并插入緩沖(INSERT BUFFER UNDO頁的回收等

IO Thread在InnoDB存儲引擎中大量使用了 AIO (Async IO)來處理寫IO請求，這樣可以極大提高數據庫的性能。而IO Thread的工作主要是負責這些IO請求的回調(callback) 處理。分別是 write、read、insert buffer 和 log 10 thread，分別使用 innodb_read_io_threads 和 innodb_write_io_threads 參數進行設置

SHOW VARIABLES LIKE 'innodb_%io_threads'\G

觀察 InnoDB 中的 IO Thread

SHOW ENGINE INNODB STATUS\G

IO Thread 0 為 insert buffer thread，IO Thread 1 為 log thread之后就是根據參數innodb_read_io_threads及innodb_write_io_threads來設置的讀寫線程，并且讀線 程的ID總是小于寫線程。

Purge ThreadPurgeThread來回收已經使用并分配的undo頁，purge操作可以獨立到單獨的線程中進行，以此來減輕Master Thread的工作。

從InnoDB 1.2版本開始，InnoDB支持多個Purge Thread,可以加快undo頁的回收。同時由于Purge Thread需要離散地讀取undo頁，這樣也褪更進 一步利用磁盤的隨機讀取性能。如用戶可以設置4個Purge Thread：

SHOW VARIABLES LIKE 'innodb_purge_threads'\G

Page Cleaner Thread作用是將之前版本中臟頁的刷新操作都放入到單獨的線程中來完成。而其目的是為了減輕原Master Thread的工作 及對于用戶査詢線程的阻塞，進一步提高InnoDB存儲引擎的性能。

2.3.2內存

緩沖池

InnoDB存儲引擎是基于磁盤存儲的，并將其中的記錄按照頁的方式進行管理。基于磁盤的數據庫系統通常使用緩沖池技術來提高數據庫的整體性能。緩沖池簡單來說就是一塊內存區域，通過內存的速度來彌補磁盤速度較慢對數據庫性能的影響。在數據庫中進行讀取頁的操作，首先將從磁盤讀到的頁存放在緩沖池中， 這個過程稱為將頁"FIX”在緩沖池中。下一次再讀相同的頁時，首先判斷該頁是否在緩沖池中。若在緩沖池中，稱該頁在緩沖池中被命中，直接讀取該頁。否則，讀取磁盤 上的頁。對于數據庫中頁的修改操作，則首先修改在緩沖池中的頁，然后再以一定的頻率刷新到磁盤上。這里需要注意的是，頁從緩沖池刷新回磁盤的操作并不是在每次頁發生變更時觸發，而是通過一種稱為Checkpoint的機制刷新回磁盤。緩沖池的配置通過參數innodb_buffer_pool_size來設置。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size' \G

緩沖池中緩存的數據頁類型有：索引頁、數據頁、undo頁、插入緩沖 (insert buffer)、自適應哈希索引(adaptive hash index)、InnoDB 存儲的鎖信息(lock info)、數據字典信息(data dictionary)等。不能簡單地認為，緩沖池只是緩存索引頁和 數據頁，它們只是占緩沖池很大的一部分而已。InnoDB存儲引擎中內存的結構情況。

配置多個緩沖池實例。每個頁根據哈希值平均分配到 不同緩沖池實例中。這樣做的好處是減少數據庫內部的資源競爭，增加數據庫的并發處理能力。

SHOW VARIABLES LIKE 'innodb_buffer_pool_instances'\G

觀察到每個緩沖池實例對象運行的狀態

SHOW ENGINE INNODB STATUS\GSELECT POOL_ID,POOL_SIZE,FREE_BUFFERS,DATABASE_PAGES FROM information_schema.INNODB_BUFFER_POOL_STATS\G

LRU List、Free List 和 Flush List

數據庫中的緩沖池是通過LRU (Latest Recent Used)算法來進行管理的。即最頻繁使用的頁在LRU列表的前端，而最少使用的頁在LRU列表的尾端。當緩沖池不能存放新讀取到的頁時，將首先釋放LRU列表中尾端的頁。在InnoDB存儲引擎中，緩沖池中頁的大小默認為16KB,同樣使用LRU算法對緩沖池進行管理。

InnoDB的存儲引擎中，LRU列表中還加入了 midpoint位置。新讀取到的頁，雖然是最新訪問的頁，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint 位置。這個算法在InnoDB存儲引擎下稱為midpoint insertion strategy。在默認配置下，該位置在LRU列表長度的5/8處。

midpoint位置可由參數innodb_old_blocks_pct控制，如：

SHOW VARIABLES LIKE 'innodb_old_blocks_pct'\G

默認值為37,表示新讀取的頁插入到LRU列表尾端的37%的位置(差不多3/8的位置)。在InnoDB存儲引擎中，把 midpoint之后的列表稱為old列表，之前的列表稱為new列表。可以簡單地理解為new 列表中的頁都是最為活躍的熱點數據。那為什么不采用樸素的LRU算法，直接將讀取的頁放入到LRU列表的首部呢？因為某些SQL操作可能會使緩沖池中的頁被刷新出，從而影響緩沖池的效率。常見掃描操作，并不是活躍的熱點數據。如果頁被放入LRU列表的首部，那么非常可能將所需要的熱點數據頁從LRU列表中移除。

InnoDB存儲引擎引入了另一個參數來進一步管理LRU列表，表示頁讀取到mid位置后需要等待多久才會被加入到LRU列表的熱端。可以通過下面的方法盡可能使LRU列表中熱點數據不被刷出。

SHOW VARIABLES LIKE 'innodb_old_blocks_time'\GSET GLOBAL innodb_old_blocks_time=1000;####data or index scan operationSET GLOBAL innodb_old_blocks_time=1000;

如果用戶預估自己活躍的熱點數據不止63%。

SHOW VARIABLES LIKE 'innodb_old_blocks_pct'\GSET?GLOBAL?innodb_old_blocks_pct=20;

LRU列表用來管理已經讀取的頁，但當數據庫剛啟動時，LRU列表是空的，即沒有任何的頁。這時頁都存放在Free列表中。當需要從緩沖池中分頁時，首先從Free列表中查找是否有可用的空閑頁，若有則將該頁從Free列表中刪除，放入到LRU列表中。否則，根據LRU算法，淘汰LRU列表末尾的頁，將該內存空間分配給新的頁。當頁從 LRU列表的old部分加入到new部分時，稱此時發生的操作為page made young,而因為innodb_old_blocks_time的設置而導致頁沒有從old部分移動到new部分的操作稱為 page not made young

通過命令 ?來觀察 LRU 列 表及Free列表的使用情況和運行狀態。

SHOW ENGINE INNODB STATUS\G

當前Buffer 8191個頁。Free buffers表示當前Free列表中頁的數量，Database pages表示LRU列表中頁的數量。可能的情況是Free buffers與 Database pages的數量之和不等于Buffer pool size，因為緩沖池 中的頁還可能會被分配給自適應哈希索引、Lock信息、Insert Buffer等頁，而這部分頁 不需要LRU算法進行維護，因此不存在于LRU列表中。pages made young顯示 LRU列表中頁移到前端的次數，因為該服務器在運行階段沒改innodb_old_blocks_time 的值，因此 not young 為 0。youngs/s，non-youngs/s 表示每秒這兩類操作的次數。

Buffer pool hit rate,表示緩沖池的命中率，100%說明緩沖池運行狀態非常良好。通常該值不應該小于95%。若發生Buffer pool hit rate的值小于95%這種情況，需要觀察是否是由于全表掃描引起的LRU列表被污染的問題。

還可以通過表INNODB_BUFFER_POOL_STATS來觀察緩沖池的運行狀態

SELECT POOL_ID, HIT_RATE,PAGES_MADE_YOUNG, PAGES_NOT_MADE_YOUNGFROM information_schema.INNODB_BUFFER_POOL_STATS\G

InnoDB存儲引擎從1.0.x版本開始支持壓縮頁的功能，即將原本16KB的頁壓縮 為1KB、2KB、4KB和8KB。而由于頁的大小發生了變化，LRU列表也有了些許的改變。對于非16KB的頁，是通過unzip_LRU列表進行管理的。LRU中的頁包含了 unzip_LRU列表中的頁。

在LRU列表中的頁被修改后，稱該頁為臟頁(dirty page),即緩沖池中的頁和磁盤 上的頁的數據產生了不一致。這時數據庫會通過CHECKPOINT機制將臟頁刷新回磁盤, 而Flush列表中的頁即為臟頁列表。需要注意的是，臟頁既存在于LRU列表中，也存在 于Flush列表中。LRU列表用來管理緩沖池中頁的可用性，Flush列表用來管理將頁刷新 回磁盤，二者互不影響。

前面例子中Modified db pages 就顯示了臟頁的數量。臟頁同樣存在于LRU列表中

SELECT TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE FROM INNODB_BUFFER_PAGE_LRU WHERE OLDEST_MODIFICATION> 0;

重做日志緩沖

內存區域除了有緩沖池外，還有重做日志緩沖(redo log buffer)。InnoDB存儲引撃首先將重做日志信息先放入到這個緩沖區，然后按一定頻率將其刷新到重做日志文件。重做日志緩沖一般不需要設置得很大，因為一般情況下每一秒鐘會將重做日志緩沖刷新到日志文件，因此用戶只需要保證每秒產生的事務量在這個緩沖大小之內即可。

SHOW VARIABLES LIKE 'innodb_log_buffer_size'\G

重做日志在下列三種情況下會將重做日志緩沖中的內容刷新到外部磁盤的重做日志文件中。

• Master Thread每一秒將重做日志緩沖刷新到重做日志文件；

• 每個事務提交時會將重做日志緩沖刷新到重做日志文件；

• 當重做日志緩沖池剩余空間小于1/2時，重做日志緩沖刷新到重做日志文件。

額外的內存池

在InnoDB存儲引擎中，對內存的管理是通過一種稱為內存堆(heap) 的方式進行的。在對一些數據結構本身的內存進行分配時，需要從額外的內存池中進行申請

2.4 Checkpoint 技術

緩沖池的設計目的為了協調CPU速度與磁盤速度的鴻溝。將臟頁從緩沖池刷新到磁盤。

若每次一個頁發生變化，就刷新到磁盤，開銷非常大。

若熱點數據集中在某幾個頁中，那么數據庫的性能將變得非常差。

若在從緩沖池將頁的新版本刷新到磁盤時發生了宕機，發生數據丟失的問題。

當前事務數據庫系統普遍都采用了 Write Ahead Log策略，即當事務提交時，先寫重做日志，再修改頁。當由于發生宕機而導致數據丟失時，通過重做日志來 完成數據的恢復。這也是事務ACID中D (持久性)的要求。Checkpoint的目的：

縮短數據庫的恢復時間；

緩沖池不夠用時，將臟頁刷新到磁盤；

重做日志不可用時，刷新臟頁。

兩種CheckpointShaip Checkpoint發生在數據庫關閉時將所有的臟頁都刷新回磁盤，這是默認的工作方式

Fuzzy Checkpoint進行頁的刷新，即只刷新一 部分臟頁，而不是刷新所有的臟頁回磁盤。

臟頁的數量太多，當緩沖池中臟頁的數量占據75%時，強制進行Checkpoint。

SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct'\G

2.6 InnoDB關鍵特性

InnoDB存儲引擎的關鍵特性包括：插入緩沖(Insert Buffer)兩次寫(DoubleWrite)自適應哈希索引(Adaptive Hash Index)異步 IO (Async IO)刷新鄰接頁(Flush Neighbor Page)2.6.1插入緩沖

Insert BufferInsert Buffer和數據頁一樣，也是物理頁的一個組成部分。在InnoDB存儲引擎中，主鍵是行唯一的標識符。通常應用程序中行記錄的插入順序是按照主鍵遞增的順序進行插入的。因此，插入聚集索引(Primary Key) 一般是順序的，不需要隨機讀取另一個頁中的記錄。因此，速度是非常快的。若主鍵類是UUID這樣的類，在進行插入操作時，數據頁的存放還是按主鍵進行順序存放的，但是對于非聚集索引葉子節點的插入不再是順序的了，這時就離散地訪問非聚集索引頁，由于隨機讀取的存在而導致了插入操作 性能下降。因為B+樹的特性決定了非聚集索引插入的離散性。

Insert Buffer,對于非聚集索引的插入或更新操作， 不是每一次直接插入到索引頁中，而是先判斷插入的非聚集索引頁是否在緩沖池中，若在，則直接插入；若不在，則先放入到一個Insert Buffer對象中，好似欺騙。數據庫這 個非聚集的索引已經插到葉子節點，而實際并沒有，只是存放在另一個位置。然后再以 一定的頻率和情況進行Insert Buffer和輔助索引頁子節點的merge (合并)操作，這時通 常能將多個插入合并到一個操作中(因為在一個索引頁中)，這就大大提高了對于非聚 集索引插入的性能。然而Insert Buffer的使用需要同時滿足以下兩個條件：索引是輔助索引(secondary index)；索引不是唯一(unique)的。當滿足以上兩個條件時，InnoDB存儲引擎會使用Insert Buffer,這樣就能提高插入操作的性能了。

Change BufferInnoDB存儲引擎可以對DML操作——INSERT, DELETE, UPDATE都進行緩沖

將記錄標記為已刪除;

真正將記錄刪除。

Insert Buffer的內部實現Insert Buffer是一棵B+樹，因此其也由葉節點和非葉節點組成。非葉節點存放的是査詢的search key (鍵值)，由space-marker-offset組成，space表示待插入記錄所在表的表空間id,每個表有一個唯一的space id,可以通過space id査詢得知是哪張表。space占用4字節。marker占用1字節，用來兼容老版本的Insert Buffer，offset 表示頁所在的偏移量，占用4字節。當一個輔助索引要插入到頁(space, offset)時，將這條記錄插入到Insert Buffer B+樹的葉子節點中。由space-marker-offset-matadata組成,前面一樣，matadata會記錄進入 Insert Buffer的順序。第5列開始，就是實際插入記錄的各個字段了。因此較之原插入記錄，Insert Buffer B+樹的葉子節點記錄需要額外13字節的開銷。

Merge Insert BufferInsert/Change Buffer是一棵B+樹。合并(merge)Insert Buffer中的記錄到真正的輔助索引中

2.6.2兩次寫

doublewrite (兩次寫)帶給InnoDB存儲引擎的是數據頁的可靠性。當發生數據庫宕機時，可能InnoDB存儲引擎正在寫入某個頁到表中，而這個頁只寫了一部分，發生了宕機，導致數據丟失的情況。可以通過重做日志進行恢復。這是一 個辦法。但是必須清楚地認識到，重做日志中記錄的是對頁的物理操作，如偏移量800, 寫-aaaa-記錄。如果這個頁本身已經發生了損壞，再對其進行重做是沒有意義的。這 就是說，在應用(apply)重做日志前，用戶需要一個頁的副本，當寫入失效發生時，先 通過頁的副本來還原該頁，再進行重做，這就是doublewrite。doublewrite由兩部分組成，一部分是內存中的doublewrite buffer,大小為2MB,另一部分是物理磁盤上共享表空間中連續的128個頁，即2個區(extent),大小同樣為 2M，在對緩沖池的臟頁進行刷新時，并不直接寫磁盤，而是會通過memcpy函數將 臟頁先復制到內存中的doublewrite buffer,之后通過doublewrite buffer再分兩次，每次 1MB順序地寫入共享表空間的物理磁盤上，然后馬上調用fsync函數，同步磁盤，避免 緩沖寫帶來的問題。在這個過程中，因為doublewrite頁是連續的，因此這個過程是順序

2.6.3自適應哈希索引

而B+樹的查找次數，取決于B+樹的高度，在生產環境中，B+樹的高度一般為3?4層，故需要3~4次的査詢。InnoDB存儲引擎會監控對表上各索引頁的査詢。如果觀察到建立哈希索引可以帶 來速度提升，則建立哈希索引，稱之為自適應哈希索引(Adaptive Hash Index, AHI)。

2.6.4 異步

為了提高磁盤操作性能，當前的數據庫系統都采用異步IO (Asynchronous IO, AI0)的方式來處理磁盤操作。

2.6.5刷新鄰接頁

InnoDB存儲引擎還提供了 Flush Neighbor Page (刷新鄰接頁)的特性。其工作原理 為：當刷新一個臟頁時，InnoDB存儲引擎會檢測該頁所在區(extent)的所有頁，如果是臟頁，那么一起進行刷新。

2.7啟動、關閉與恢復

在關閉時，參數innodb_fast_shutdown影響著InnoDB的行為

• 0表示在MySQL數據庫關閉時，InnoDB需要完成所有的fiill purge和merge insert buffer,并且將所有的臟頁刷新回磁盤。這需要一些時間，有時甚至需要幾 個小時來完成。如果在進行InnoDB升級時，必須將這個參數調為0,然后再關閉數據庫。

• 1是參數innodb fast shutdown的默認值，表示不需要完成上述的fiill purge和 merge insert buffer操作，但是在緩沖池中的一些數據臟頁還是會刷新回磁盤。

• 2表示不完成full purge和merge insert buffer操作，也不將緩沖池中的數據臟頁寫回磁盤，而是將日志都寫入日志文件。這樣不會有任何事務的丟失，但是下次 MySQL數據庫啟動時，會進行恢復操作(recovery)。

第3章 文件

• 參數文件：告訴MySQL實例啟動時在哪里可以找到數據庫文件，并且指定某些初始化參數

• 日志文件：用來記錄MySQL實例對某種條件做出響應時寫入的文件，如錯誤日志文件、二進制日志文件、慢査詢日志文件、查詢日志文件等。

• socket文件：當用UNIX域套接字方式進行連接時需要的文件。

• pid文件：MySQL實例的進程ID文件。

• MySQL表結構文件：用來存放MySQL表結構定義文件。

• 存儲引擎文件：因為MySQL表存儲引擎的關系，每個存儲引擎都會有自己的文 件來保存各種數據。這些存儲引擎真正存儲了記錄和索引等數據。

3.1參數文件

啟動時，數據庫會先去讀一個配置參數文件，用來尋找數據庫的各種文件所在位置以及指定某些初始化參數

mysql --help | grep my.cnf

3.1.2參數類型MySQL數據庫中的參數可以分為兩類：動態(dynamic)參數靜態(static)參數

全局/當前會話

SELECT @@session.read_buffer_size\GSELECT @@global.read_buffer_size\G

3.2日志文件

常見的日志文件有：

• 錯誤日志(error log)

• 二進制日志(binlog)

• 慢査詢日志(slow query log)

• 査詢日志(log)

對運行狀態進行診斷，從而更好地進行數據庫層面的優化。

3.2.1錯誤日志

錯誤日志文件對MySQL的啟動、運行、關閉過程進行了記錄。不僅記錄了所有的錯誤信息，也記錄一些警告信息或正確的信息。

SHOW VARIABLES LIKE 'log_error'\Gtail -n 50 /var/lib/mysql/node3.err

3.2.2慢查詢日志

慢査詢日志定位可能存在問題的SQL語句，從而進行SQL語句層面的優化。可以在MySQL啟動時設一個閾值，將運行時間超過該值的所有SQL語句都記錄到慢查詢日志文件中,默認關閉

SHOW VARIABLES LIKE 'long_query_time'\GSHOW VARIABLES LIKE 'log_slow_queries'\G

如果運行的SQL 語句沒有使用索引，則MySQL數據庫同樣會將這條SQL語句記錄到慢査詢日志文件。

SHOW VARIABLES LIKE 'log_queries_not_using_indexes'\G

DBA可以通過慢查詢日志來找出有問題的SQL語句，對其進行優化。

提供的mysqldumpslow命令，分析該文件：

mysqldumpslow xxx-190-slow.log

如果用戶希望得到執行時間最長的10條SQL語句，可以運行如下命令：

mysqldumpslow -s al -n 10 david.log

將慢査詢的日志記錄放入一張表中，査詢更加方便和直觀。

select * from mysql.slow_log;

參數long_query_io將超過指定邏輯10次數的SQL語句記錄到 slow log中。表示對于邏輯讀取次數大于100的SQL語句，記錄到 slow log中。

3.2.3查詢日志

査詢日志記錄了所有對MySQL數據庫請求的信息，無論這些請求是否得到了正確的執行。默認文件名為：主機名.log,如査看一個査詢日志：

3.2.4二進制日志

二進制日志(binary log)記錄了對MySQL數據庫執行更改的所有操作，但是不包括SELECT和SHOW這類操作

SHOW MASTER STATUS\GSHOW BINLOG EVENTS IN 'mysql-bin.000002'\G

二進制日志主要有以下幾種作用。恢復、復制、審計

show variables like 'datadir';

mysql-bin.000001即為二進制日志文件，mysql-bin.index為二進制的索引文件，來存過往產生的二進制日志序號

以下配置文件的參數影響著二進制日志記錄的信息和行為：

max_binlog_size指定了單個二進制日志文件的最大值，如果超過該值，則產生 新的二進制日志文件，后綴名+1，并記錄到.index文件。當使用事務的表存儲引擎時，所有未提交的二進制日志會被記錄到一個緩存中去，等該事務提交時直接將緩沖中的二進制日志寫入二進制日志文件，而該緩沖的大小由binlog_cache_size決定，默認大小為32K。

在默認情況下，二進制日志并不是在每次寫的時候同步到磁盤。sync_binlog= [N]表示每寫緩沖 多少次就同步到磁盤。如果將N設為1,即sync_binlog= 1表示采用同步寫磁盤的方式來 寫二進制日志參數binlog-do-db和binlog-ignore-db表示需要寫入或忽略寫入哪些庫的日志。默認為空，表示需要同步所有庫的日志到二進制日志。binlog_format參數十分重要，它影響了記錄二進制日志的格式。

要査看二進制日志文件的內容，通過MySQL提供的工具mysqlbinlogo對于STATEMENT格式的二進制日志文件，在 使用mysqlbinlog后，看到的就是執行的邏輯SQL語句

mysqlbinlog --start-position=203 test.000004

3.3套接字文件

在UNIX系統下本地連接MySQL可以采用UNIX域套接字方式，這種方式需要一個套接字(socket)文件。套接字文件可由參數socket控制。一般在/tmp 目錄下，名為mysql.sock

3.4 pid文件

當MySQL實例啟動時，會將自己的進程ID寫入一個文件中該文件即為pid文 件。該文件可由參數pid_file控制，默認位于數據庫目錄下，文件名為主機名.pid：

show variables like 'pid_file'\G

3.5表結構定義文件

MySQL數據的存儲是根據表進行 的，每個表都會有與之對應的文件。但不論表采用何種存儲引擎，MySQL都有一個以 frm為后綴名的文件，這個文件記錄了該表的表結構定義。frm還用來存放視圖的定義，如用戶創建了一個v_a視圖，那么對應地會產生v_a.frm文件

3.6 InnoDB存儲引擎文件

與InnoDB存儲引擎密切相關的文件，這些文件包括重做日志文件、表空間文件。

表空間文件

InnoDB采用將存儲的數據按表空間(tablespace)進行存放的設計。在默認配置 下會有一個初始大小為10MB,名為ibdatal的文件。該文件就是默認的表空間文件 (tablespace file)

設置innodb_data_file_path參數后，所有基于InnoDB存儲引擎的表的數據都會記錄到該共享表空間中。若設置了參數innodb_Ele_per_table,則用戶可以將每個基于 InnoDB存儲引擎的表產生一個獨立表空間。獨立表空間的命名規則為：表名.ibd。通過 這樣的方式，用戶不用將所有數據都存放于默認的表空間中。下面這臺MySQL數據庫 服務器設置了 innodb_file_per_table,故可以觀察到：

SHOW VARIABLES LIKE 'innodb_file_per_table'\G

這些單獨的表空間文件僅存儲該表的數據、索引和插入緩沖BITMAP等信息，其余信息還是存放在默認的表空冋中。

3.6.2重做日志文件

在默認情況下，在InnoDB存儲引擎的數據目錄下會有兩個名為ib_logfile0和ib_ logfile1的文件。它們記錄了對于InnoDB存儲引擎的事務日志。主機掉電導致實例失敗，InnoDB存儲引擎會使用重做日志恢復到掉電前的時刻，以此來保證數據的完整性。每個InnoDB存儲引擎至少有1個重做日志文件組(group),每個文件組下至少有 2個重做日志文件，如默認的ibJogfile0和ibJogfile1為了得到更高的可靠性，

innodb_log_file_size指定每個重做日志文件的大小。1.2.x版本將該限制擴大為了 512G

innodb_log_files_in_group指定了日志文件組中重做日志文件的數量，默認為 2參數

innodb_mirrored_log_groups指定了日志鏡像文件組的數量，默認為1,表示只 有一個日志文件組，沒有鏡像

innodb_log_group_home_dir指定了日志文 件組所在路徑，默認為./,表示在MySQL數據庫的數據目錄下

innodb_flush_log_at_trx_commit設置為1，因此為了保證事務的ACID中的持久性，也就是每當有事務提交時，就必須確保事務都已經寫入重做日志文件

第4章 表

4.1索引組織表

在InnoDB存儲引擎中，表都是根據主鍵順序組織存放的，這種存儲方式的表稱為索引組織表(index organized tabl，每張表都有個主鍵 (Primary Key),如果在創建表時沒有顯式地定義主鍵，則InnoDB存儲引擎會按如下方 式選擇或創建主鍵：首先判斷表中是否有非空的唯一索引(Unique NOT NULL),如果有，則該列即為主鍵。如果不符合上述條件，InnoDB存儲引擎自動創建一個6字節大小的指針。當表中有多個非空唯一索引時，InnoDB存儲引擎將選擇建表時第一個定義的非空唯 一索引為主鍵

通過下面的SQL語句判斷表的主鍵值

SELECT a,b,c,_rowid FROM z;

rowid可以顯示表的主鍵，rowid R能用于查看單個列為主鍵的情況如：

4.2 InnoDB邏輯存儲結構

從InnoDB存儲引擎的邏輯存儲結構看，所有數據都被邏輯地存放在一個空間中，稱 之為表空間(tablespace)。表空間又由段(segment)＞區(extent)、頁(page)組成。頁在 一些文檔中有時也稱為塊(block)

4.2.1 表空間

默認情況下InnoDB存儲引擎有一個共享表空間ibdatal, 即所有數據都存放在這個表空間內。如果用戶啟用了參數innodb_file_per_table,則每張表內的數據可以單獨放到一個表空間內。每張表的表空間內存放的只是數據、索引和插入緩沖Bitmap頁，其他類的數據，如回滾(undo)信息，插入緩沖 索引頁、系統事務信息，二次寫緩沖(Double write buffer)等還是存放在原來的共享表空間內。?

4.2.2 段

表空間是由各個段組成的，常見的段有數據段、索引段、回滾段等。因為前面已經介紹過了 InnoDB存儲引擎表是索引組織的(index organized),因此數據即索引，索引即數據。那么數據段即為B+樹的葉子節點(圖4-1的Leaf node segment), 索引段即為B+樹的非索引節點(圖4-1的Non-leaf node segment),回滾段較為特殊,后面單獨介紹。

4.2.3 區

區是由連續頁組成的空間，在任何情況下每個區的大小都為1MB。為了保證區中頁 的連續性，InnoDB存儲引擎一次從磁盤申請4~5個區。在默認情況下，InnoDB存儲引擎頁的大小為16KB,即一個區中一共有64個連續的頁。InnoDB 1.0.x版本開始引入壓縮頁，即每個頁的大小可以通過參數KEY_BLOCK_ SIZE設置為2K、4K、8K,因此每個區對應頁的數量就應該為512、256、128。InnoDB 1.2.x版本新增了參數innodb_page_size.通過該參數可以將默認頁的大小設置為4K、8K,但是頁中的數據不是壓縮。這時區中頁的數量同樣也為256、128。總之， 不論頁的大小怎么變化，區的大小總是為IM.在每個段開始時，先用32個頁大小的碎片頁(fragment page)來存放數據， 在使用完這些頁之后才是64個連續頁的申請。這樣做的目的是，對于一些小表，在開始時申請較少的空間，節省磁盤容量的開銷。

4.2.4 頁

頁是InnoDB 磁盤管理的最小單位。在InnoDB存儲引擎中，默認每個頁的大小為16KB。而從 InnoDB 1.2.x版本開始，可以通過參數innodb_page_size將頁的大小設置為4K、8K、 16K。若設置完成，則所有表中頁的大小都為innodb_page_size,不可以對其再次進行修改。常見的頁類型有：

• 數據頁(B-treeNode)

• undo 頁(undo Log Page)

• 系統頁(System Page)

• 事務數據頁(Transaction system Page)

• 插入緩沖位圖頁(Insert Buffer Bitmap )

• 插入緩沖空閑列表頁(Insert Buffer Free List)

• 未壓縮的二進制大對象頁(Uncompressed BLOB Page)

• 壓縮的二進制大對象頁(compressed BLOB Page)

4.2.5 行

InnoDB存儲引擎是面向列的(row-oriented),也就說數據是按行進行存放的。

4.3 InnoDB行記錄格式

InnoDB存儲引擎是以行的形式存儲的。這意味著頁中保存著表中一行行的數據。在InnoDB 1.0.x版本之前，提供了 Compact和Redundant兩種格式來存放行記錄數據，在MySQL 5.1版本中，默認設置為Compact行格式。查看當前表使用的行格式

SHOW TABLE STATUS like 'user'\G

Compressed 和 Dynamic 行記錄格式

InnoDB 1.0.x版本開始引入了新的文件格式稱為Barracuda 文件格式。Barracuda文件格式下擁有兩種新的行記錄格式：Compressed和Dynamic。

4.4 InnoDB數據頁結構

頁是InnoDB存儲引擎管理數據庫的最小磁盤單位。頁類型為B-treeNode的頁存放的即是表中行的實際數據了。在這一節中，我們將從底層具體地介紹InnoDB數據頁的內部存儲結構。

4.4.1 File Header

記錄頁的一些頭信息，共占用38字節。

4.4.2 Page Header

該部分用來記錄數據頁的狀態信息，由14個部分組成，共占用56字節

4.4.3 Infimum 和 Supremum Record

每個數據頁中有兩個虛擬的行記錄，用來限定記錄的邊界。Infimum記錄是比該頁中任何主鍵值都要小的值，Supremum指比任何可能大的值還要大的值。

4.4.4 User Record 和 Free Space

實際存儲行記錄的內容。再次強調，InnoDB 存儲引擎表總是B+樹索引組織的。Free Space很明顯指的就是空閑空間，同樣也是個鏈表數據結構。

4.4.5 Page Directory

存放了記錄的相對位置(存放的是頁相對位置，而不是偏移量)由于在InnoDB存儲引擎中Page Direcotry是稀疏目錄，二叉査找的結果只是一個粗略的結果，因此InnoDB存儲引擎必須通過recorder header中的next_record來繼續査找相關記錄。

需要牢記的是，B+樹索引本身并不能找到具體的一條記錄，能找到只是該記錄所在的頁。數據庫把頁載入到內存，然后通過Page Directory再進行二叉査找。只不過二 叉查找的時間復雜度很低，同時在內存中的査找很快

4.4.6 File Trailer

為了檢測頁是否已經完整地寫入磁盤(如可能發生的寫入過程中磁盤損壞、機器關 機等)，InnoDB存儲引擎的頁中設置了 File Trailer部分。保證頁的完整性(not corrupted)。

4.7視圖

在MySQL數據庫中，視圖(View)是一個命名的虛表，視圖中的數據沒有實際的物理存儲。

4.8分區表

4.8.1分區概述分區功能并不是在存儲引擎層完成的,分區的過程是將一個表或索引分解為多個更小、更可管理的部分。MySQL 數據庫的分區是局部分區索引，一個分區中既存放了數據又存放了索引。而全局分區是指，數據存放在各個分區中，但是所有數據的索引放在一個對象中。目前，MySQL數據不支持全局分區。查看當前數據庫是否啟用了分區功能：

SHOW VARIABLES LIKE '%partition%'\G

幾種類型的分區。

• RANGE分區：行數據基于屬于一個給定連續區間的列值被放入分區。5.5 開始支持RANGE COLUMNS的分區。

• LIST分區：和RANGE分區類型，只是LIST分區面向的是離散的值。5.5 開始支持LIST COLUMNS的分區。

• HASH分區：根據用戶自定義的表達式的返回值來進行分區，返回值不能為負數。

• KEY分區：根據MySQL數據庫提供的哈希函數來進行分區。

不論創建何種類型的分區，如果表中存在主鍵或唯一索引時，分區列必須是唯一索 引的一個組成部分

4.8.2分區類型

PARTITION BY RANGE (id)(PARTITION p0 VALUES LESS THAN (10),PARTITION p1 VALUES LESS THAN (20))；PARTITION BY LIST(b)(PARTITION p0 VALUES IN (1,3,5,7,9),PARTITION p1 VALUES IN (0,2,4,6,8));PARTITION BY HASH (YEAR(b))PARTITIONS 4;PARTITION BY KEY (b)PARTITIONS 4;

第5章索引與算法

5.1 InnoDB存儲引擎索引概述

幾種常見的索引：

• B+樹索引

• 全文索引

• 哈希索引

InnoDB存儲引擎支持的哈希索引是自適應的，InnoDB存儲引擎會根據表的使用情況自動為表生成哈希索引B+樹索引就是傳統意義上的索引，這是目前關系型數據庫系統中最有效的索引。

B+樹索引并不能找到一個給定鍵值的具體行。B+樹索引能找到的只是被査找數據行所在的頁。然后數據庫通過把頁讀入到內存，再在內存中進行查找，最后得到要查找的數據。

5.4 B+樹索引

B+樹索引的本質就是B+樹在數據庫中的實現。但B+索引在數據庫中有一個特點是高扇出性，因此在數據庫中，B+ 樹的高度一般都在2-4層數據庫中的B+樹索引可以分為聚集索引(clustered inex)和輔助索引(secondary index)。其內部都是B+樹的，即高度平衡的，葉子 節點存放著所有的數據。聚集索引與輔助索引不同的是，葉子節點存放的是否是一整行的信息。

5.4.1聚集索引

InnoDB存儲引擎表是索引組織表，即表中數據按照主鍵順序存 放。而聚集索引(clustered index)就是按照每張表的主鍵構造一棵B+樹，同時葉子節點中存放的即為整張表的行記錄數據，也將聚集索引的葉子節點稱為數據頁。聚集索引 的這個特性決定了索引組織表中數據也是索引的一部分。同B+樹數據結構一樣，每個數據頁都通過一個雙向鏈表來進行鏈接。由于實際的數據頁只能按照一棵B+樹進行排序，因此每張表只能擁有一個聚集索引。

在多數情況下，查詢優化器傾向于采用聚集索引。因為聚集索引能夠在B+樹索引 的葉子節點上直接找到數據。能夠特別快 地訪問針對范圍值的查詢。

數據頁上存放的是完整的每行的記錄， 而在非數據頁的索引頁中，存放的僅僅是鍵值及指向數據頁的偏移量，而不是一個完整的行記錄。

聚集索引的存儲并不是物理上連續的，而是邏輯上連續的。這其中有兩點：一是頁通過雙向鏈表鏈接，頁按照主鍵的順序排序；另一點是每個頁中的記錄也是通過雙向鏈表進行維護的，物理存儲上可以同樣不按照主鍵存儲。

5.4.2 輔助索引

對于輔助索引(Secondary Index,也稱非聚集索引)，葉子節點并不包含行記錄的全部數據。葉子節點除了包含鍵值以外，每個葉子節點中的索引行中還包含了一個書簽 (bookmark),輔助索引的書簽就是相應行數據的聚集索引鍵。

輔助索引的存在并不影響數據在聚集索引中的組織，因此每張表上可以有多個輔助索引。

當通過輔助索引來尋找數據時，InnoDB存儲引擎會遍歷輔助索引并通過頁級別的指針，獲得指向主鍵索引的主鍵，然后再通過主鍵索引來找到一個完整的行記錄。

5.4.4 B+樹索引的管理

1 .索引管理索引的創建和刪除可以通過兩種方法，一種是ALTER TABLE,另一種是CREATE/ DROP INDEX.

ALTER TABLE user ADD KEY idx_id (id); SHOW INDEX FROM user;ALTER TABLE user DROP INDEX idx_id ;create index idx_id on user(id);drop index idx_id on user;#聯合索引ALTER TABLE user ADD KEY idx_id_bc (id,bc);mysql> show index from user\G*************************** 1. row *************************** Table: user Non_unique: 1 Key_name: idx_id Seq_in_index: 1 Column_name: id Collation: A #A或者null，B+樹總是A，即排序的 Cardinality: 1 #表示索引中唯一值的數目的估值，應盡可能接近1 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment:?

優化器會根據Cardinality值來判斷是否使用這個索引， 但他不是實時更新的,更新

analyze table user\G

5.5 Cardinality 值

什么時候添加B+樹索 取值的范圍很小，稱為低選擇性，這時添加B+樹索引是完全沒有必要的。

取值范圍很廣，幾乎沒有重復，即屬于高選擇性，使用B+樹索引是最適合的

Cardinality值非常關鍵，表示索引中不重復記錄數量的預估值。是一個預估值，而不是一個準確值，應盡可能地接近1。

怎樣來統計Cardinality信息的呢？

數據庫對于Cardinality的統計都是通過采樣(Sample)的方法來完成的。Cardinality統計信息的更新發生在兩個操作中：INSERT 和UPDATE。更新Cardinality 的策略為：

表中1/16的數據已發生過變化。

stat_modified_counter > 2000000000

怎樣來進行Cardinality信息的統計和更新操作？

同樣是通過采樣的方法。默認InnoDB存儲引擎對8個葉子節點(Leaf Page)進 行采用。采樣的過程如下：

取得B+樹索引中葉子節點的數量，記為A。

統計每個頁不同記錄的個數，即為P1,…P8。

給出 Cardinality 的預估值：Cardinality= (P1+P2+...+P8) *A/8。

5.6 B+樹索引的使用

5.6.1不同應用中B+樹索引的使用

B+樹索引建立后，對該索引的使用應該只是通過該索引取得表中少部分的數據。這時建立B+樹索引才是有意義的，否則即使建立了，優化器也可能選擇不使用索引。

5.6.2聯合索引

聯合索引是指對表上的多個列進行索引。

CREATE TABLE t (a INT,b INT,PRIMARY KEY (a),KEY idx_a_b (a,b))ENGINE=INNODB

聯合索引也是一棵 B+樹，不同的是聯合索引的鍵值的數量不是1,而是大于等于2。

和單個鍵值的B+樹 并沒有什么不同，鍵值都是排序的，通過葉子節點可以邏輯上順序地讀出所有數據

數據按(a, b)的順序進行了存放。對于b列的査詢使用不到(a, b)的索引。聯合索引的第二個好處是已經對第二個鍵值進行了排序處理。索引本身在葉子節點已經排序了。

5.6.3覆蓋索引

InnoDB存儲引擎支持覆蓋索引，即從輔助索引中就可以得到査詢的記錄，而不需要査詢聚集索引中的記錄。好處是輔助索引不包含整行記錄的所有信息，故其大小要遠小于聚集索引，因此可以減少大量 的IO操作。

對于InnoDB存儲引擎的輔助索引而言，由于其包含了主鍵信息

SELECT COUNT (*) FROM buy_logInnoDB存儲引擎并不會選擇通過查詢聚集索引來進行統計。由于buy_log表上還有輔助索引，而輔助索引遠小于聚集索引，選擇輔助索引可以減少IO操作，故優化器的選擇為userid索引，而列Extra列的Using index就是代表了優化器進行了覆蓋索引操作。

5.6.4優化器選擇不使用索引的情況

優化器并沒有選擇索引去査找數據，而是通過掃描聚集索引，也就是直接進行全表的掃描來得到數據。這種情況多發生于范圍查找、JOIN鏈接操作等情況下。

用戶要選取的數據是整行信息，而輔助索引不能覆蓋到我們要査詢的信息，因此在對OrderlD索引查詢到指定數據后，還需要一次書簽訪問來査找整行數據的信息。雖然OrderlD索引中數據是順序存放的，但是再一次進行書簽查找的數據則是無序的，因此變為了磁盤上的離散讀操作。

如果要求訪問的數據量很小，則優化器還是會選擇輔助索引，但是當訪問的數據占整個表中數據的蠻大一部分時 (一般是20%左右)，優化器會選擇通過聚集索引來査找數據。因為順序讀要遠遠快于離散讀。

因此對于不能進行索引覆蓋的情況，優化器選擇輔助索引的情況是，通過輔助索引 查找的數據是少量的。也可以強制使用輔助索引

select * from t force index(id) where id >10000 nad id <10200;

5.6.5索引提示

SELECT * FROM t USE INDEX(a) WHERE a=l AND b = 2;

5.6.6 Multi-Range Read 優化

目的就是為了減少磁盤的隨機訪問，并且將隨機訪問轉化為較為順序的數據訪問，適用于range, ref, eq_ref類型的查詢。好處：

• MRR使數據訪問變得較為順序

• 減少緩沖池中頁被替換的次數

• 批量處理對鍵值的査詢操作

MRR的工作方式如下：

• 將査詢得到的輔助索引鍵值存放于一個緩存中，這時緩存中的數據是根據輔助索 引鍵值排序的。

• 將緩存中的鍵值根據RowID進行排序。

• 根據RowID的排序順序來訪問實際的數據文件。

在列Extra會看見Using MRR選項。

若啟用了 Multi-RangeRead優化，優化器會先將査詢條件進行拆分，然后再進行數據査詢。優化器會將查詢條件拆分為(1000, 1000), (1001, 1000),...最后再根據這些拆分出的條件進行數據的査詢。

5.6.7 Index Condition Pushdown (ICP)優化

根據索引進行査詢的優化方式。之前的MySQL進行索引査詢時，首先根據索引來查找記錄，然后再根據WHERE條件來過濾記錄。在支持Index Condition Pushdown后，MySQL數據庫會在取出索引的同時， 判斷是否可以進行WHERE條件的過濾，也就是將WHERE的部分過濾操作放在了存儲引擎層。在某些查詢下，可以大大減少上層SQL層對記錄的索取(fetch),從而提高數 據庫的整體性能。

當優化器選擇Index Condition Pushdown優化時， 在Extra看到Using index condition提示。

5.7哈希算法

5.7.1 哈希表

哈希表技術很好地解決了直接尋址遇到的問題，但是會碰撞，在數據庫中一般采用最簡單的碰撞解決技術，這種技術被稱為鏈接法 (chaining).

在鏈接法中，把散列到同一槽中的所有元素都放在一個鏈表中，槽i中有一個指針，它指向由所有散列到i的元素構成的鏈表的頭；如果不存在這樣的元素， 則i中為NULL

5.7.2 InnoDB存儲引擎中的哈希算法

InnoDB存儲引擎使用哈希算法來對字典進行查找，其沖突機制采用鏈表方式，哈希函數采用除法散列方式。

對于緩沖池頁的哈希表來說，在緩沖池中的頁都有一個chain指針，它指向相同哈希函數值的頁。而對于除法散列，m的取值為略大于2倍的緩沖池頁數量的質數。例如：當前參數innodb_buffer_pool_size的大小為10M,則共有 640個16KB的頁。對于緩沖池頁內存的哈希表來說，需要分配640X2=1280個槽，但 是由于1280不是質數，需要取比1280略大的一個質數，應該是1399,所以在啟動時會 分配1399個槽的哈希表，用來哈希查詢所在緩沖池中的頁。那么InnoDB存儲引擎的緩沖池對于其中的頁是怎么進行査找的呢？上面只是給出 了一般的算法，怎么將要查找的頁轉換成自然數呢？其實也很簡單，InnoDB存儲引擎的表空間都有一個space_id,用戶所要査詢的應該 是某個表空間的某個連續16KB的頁，即偏移量offset。InnoDB存儲引擎將space_id左 移 20 位，然后加上這個 space_id 和 offset,即關鍵字 K=space_i<<20+space_id+offset, 然后通過除法散列到各個槽中去。

5.7.3自適應哈希索引

自適應哈希索引是數據庫自身創建并使用的，自適應哈希索引經哈希函數映射到一個哈希表中，因此對于字典類型的査找非常快速，但是對于范圍査找就無能為力了。

哈希索引只能用來搜索等值的査詢

innodb_adaptive_hash_index來禁用或啟動此特性，默認為開啟。

5.8全文檢索

5.8.1概述

B+樹索引可以通過索引字段的前綴 (prefix)進行查找。比如 a like 'xxx%'，只要a添加了 B+ 樹索引，就能利用索引進行快速査詢。但是 a like '%xxx%'就不行了根據B+樹索引的特性，上述SQL語句即便添加了 B+樹索引也是需要進行索引的掃描來得到結果。

從InnoDB 1.2.x版本開始，InnoDB存儲引擎開始支持全文檢索，其支持MylSAM存儲引擎的全部功能

SELECT * FROM fts_a WHERE body LIKE '%Pease%';SELECT * FROM fts_a WHERE MATCH(body) AGAINST ('Pease'):

type這列顯示了 fulltext,即表示使用全文檢索的倒排索引，而key這 列顯示了 idx_Rs,表示索引的名字。

第六章 鎖

開發多用戶、數據庫驅動的應用時，最大的一個難點是：一方面要最大程度地利用數據庫的并發訪問，另外一方面還要確保每個用戶能以一致的方式讀取和修改數據。為 此就有了鎖(locking)的機制

6.1什么是鎖

鎖是數據庫系統區別于文件系統的一個關鍵特性。鎖機制用于管理對共享資源的并發訪問，InnoDB存儲引擎會在行級別上對表數據上鎖，數據庫系統使用鎖是為了支持對共享資源進行并發訪問， 提供數據的完整性和一致性。

對于MylSAM引擎，其鎖是表鎖設計。并發情況下的讀沒有問題，但是并發插入時的性能就要差一些了InnoDB存儲引擎鎖的實現提供一致性的非鎖定讀、行級鎖支持。行級鎖沒有相關額外的開銷，并可以同時得到并發性和一致性。

6.2 lock 與 latch

latch一般稱為閂鎖(輕量級的鎖)，因為其要求鎖定的時間必須非常短。若持續的時間長，則應用的性能會非常差。在InnoDB存儲引擎中，latch又可以分為mutex (互斥量)和rwlock (讀寫鎖)。其目的是用來保證并發線程操作臨界資源的正確性，并且通常沒有死鎖檢測的機制。

lock的對象是事務，用來鎖定的是數據庫中的對象，如表、頁、行。并且一般lock 的對象僅在事務commit或rollback后進行釋放(不同事務隔離級別釋放的時間可能不同)。此外，lock是有死鎖機制的

show engine innodb mutex;#latch

6.3 InnoDB存儲引擎中的鎖

6.3.1鎖的類型

InnoDB存儲引擎實現了如下兩種標準的行級鎖：

• 共享鎖(SLock),允許事務讀一行數據。

• 排他鎖(XLock),允許事務刪除或更新一行數據。

如果一個事務T1已經獲得了行r的共享鎖，那么另外的事務T2可以立即獲得行r 的共享鎖，因為讀取并沒有改變行r的數據，稱這種情況為鎖兼容(Lock Compatible)。但若有其他的事務T3想獲得行r的排他鎖，則其必須等待事務Tl、T2釋放行r上的共享鎖——這種情況稱為鎖不兼容。S和X鎖都是行鎖，兼容是指對同一記錄(row)鎖的兼容性情況。

此外，InnoDB存儲引擎支持多粒度(granular)鎖定，這種鎖定允許事務在行級上的鎖和表級上的鎖同時存在。

為了支持在不同粒度上進行加鎖操作，InnoDB存儲引擎支持 一種額外的鎖方式，稱之為意向鎖(Intention Lock)。

意向鎖是將鎖定的對象分為多個層次，意向鎖意味著事務希望在更細粒度(fine granularity)上進行加鎖

6.3.2 一致性非鎖定讀

一致性的非鎖定讀是指InnoDB存儲引擎通過行多版本控制(multi versioning)的方式來讀取當前執行時間數據庫中 行的數據。如果讀取的行正在執行DELETE或UPDATE操作，這時讀取操作不會因此去等待行上鎖的釋放。而是會去讀取行的一個快照數據。快照數據是指該行的之前版本的數據，該實現是通過undo段來完成。非鎖定讀機制極大地提高了數據庫的并發性。這是默認的讀取方式，即讀取不會占用和等待表上的鎖。

但是在不同事務隔離級別下，讀取的方式不同，并不是在每個事務隔離級別下都是采用非鎖定的一致性讀。

快照數據其實就是當前行數據之前的歷史版本，每行記錄可能有多個版本。一個行記錄可能有不止一個快照數據，一般稱這種技術為行多版本技術。由此帶來的并發控制，稱之為多版本并發控制(Multi Version Concurrency Control. MVCC)。

在事務隔離級別READ COMMITTED和REPEATABLE READ (默認事務隔離級別)下，InnoDB存儲引擎使用非鎖定的一致性讀。然而，對于快照數據的定義卻不相同。在READ COMMITTED事務隔離級別下，對于快照數據，非一致 性讀總是讀取被鎖定行的最新一份快照數據。而在REPEATABLE READ事務隔離級別 下，對于快照數據，非一致性讀總是讀取事務開始時的行數據版本。

6.3.3 一致性鎖定讀

即事務的隔離級別為REPEATABLE READ模 式下，InnoDB存儲引擎的SELECT操作使用一致性非鎖定讀。但是在某些情況下，用戶需要顯式地對數據庫讀取操作進行加鎖以保證數據邏輯的一致性。而這要求數據庫支持加鎖語句，即使是對于SELECT的只讀操作。持兩種一致性的鎖定讀(locking read)操作：

SELECT...FOR UPDATESELECT...LOCK IN SHARE MODE

SELECT...FOR UPDATE對讀取的行記錄加一個X鎖，其他事務不能對已鎖定的行加上任何鎖。

SELECT...LOCK IN SHARE MODE對讀取的行記錄加一個S鎖，其他事務可以向被鎖定的行加S鎖，但是如果加X鎖，則會被阻塞。

6.3.4自増長與鎖

在InnoDB存儲引擎的內存結構中，對每個含有自增長值的表都有一個自增長計數器(auto-increment counter),當對含有自增長的計數器的表進行插入操作時，這個計數器會被初始化，執行如下的語句來得到計數器的值：

SELECT MAX(auto_inc_col) FROM t FOR UPDATE;

插入操作會依據這個自增長的計數器值加1賦予自增長列。這個實現方式稱做 AUTO-INC Locking.這種鎖其實是采用一種特殊的表鎖機制，為了提高插入的性能，鎖不是在一個事務完成后才釋放，而是在完成對自增長值插入的SQL語句后立即釋放。

6.5鎖問題

6.5.1臟讀

臟頁指的是在緩沖池中已經被修改的頁，但是還沒有刷新到磁盤中，即數據庫實例內存中的頁和磁盤中的頁的數據是不一致的，當然在刷新到磁盤之前，日志都已經被寫入到了重做日志文件中。

臟數據是指事務對緩沖池中行記錄的修改，并且還沒有被提交。

對于臟頁的讀取，是非常正常的。臟頁是因為數據庫實例內存和磁盤的異步造成的, 這并不影響數據的一致性(或者說兩者最終會達到一致性，即當臟頁都刷回到磁盤)。并且因為臟頁的刷新是異步的，不影響數據庫的可用性，因此可以帶來性能的提高。

臟數據是指未提交的數據，如果讀到了臟數據，即一個事務可臟讀指的就是在不同的事務下，當前事務可以讀到另外事務未提交的數據，簡單來說就是可以讀到臟數據。違反了事務的隔離性。

6.5.2不可重復讀

不可重復讀是指在一個事務內多次讀取同一數據集合。在這個事務還沒有結束時, 另外一個事務也訪問該同一數據集合，并做了一些DML操作。在第一個事務中的兩次讀數據之間，由于第二個事務的修改，發生了在一個事務內兩次讀到的數據是不一樣的情況，這種情況稱為不可重復讀。

不可重復讀和臟讀的區別是：臟讀是讀到未提交的數據，而不可重復讀讀到的卻是已經提交的數據，但是其違反了數據庫事務一致性的要求。InnoDB存儲引擎的默認事務隔離級別是READ REPEATABLE,采用Next-Key Lock算法，避免了不可重復讀的現象。

6.5.3丟失更新

丟失更新是另一個鎖導致的問題，簡單來說其就是一個事務的更新操作會被另一個事務的更新操作所覆蓋，從而導致數據的不一致。

6.6阻塞

因為不同鎖之間的兼容性關系，在有些時刻一個事務中的鎖需要等待另一個事務中的鎖釋放它所占用的資源，這就是阻塞。阻塞并不是一件壞事，其是為了確保事務可以 并發且正常地運行。innodb_lock_wait_timeout用來控制等待的時間(默認 是50秒)

innodb_rollback_on_timeout用來設定是否在等待超時時對進行中的事務進行回滾操作(默認是OFF,代表不回滾)。

6.7死鎖

6.7.1死鎖的概念

死鎖是指兩個或兩個以上的事務在執行過程中，因爭奪鎖資源而造成的一種互相等待的現象。

解決死鎖問題最簡單的一種方法是超時，即當兩個事務互相等待時，當一個等待時間超過設置的某一閾值時，其中一個事務進行回滾，另一個等待的事務就能繼續進行。參數innodb_lock_wait_timeout用來設置超時的時間。

除了超時機制，當前數據庫還都普遍采用wait-for graph (等待圖)的方式來進行死鎖檢測。較之超時的解決方案，這是一種更為主動的死鎖檢測方式。

6.8鎖升級

鎖升級(Lock Escalation)是指將當前鎖的粒度降低。舉例來說，數據庫可以把一 個表的1000個行鎖升級為一個頁鎖，或者將頁鎖升級為表鎖。如果在數據庫的設計中認為鎖是一種稀有資源，而且想避免鎖的開銷，那數據庫中會頻繁出現鎖升級現象。

InnoDB存儲引擎不存在鎖升級的問題。因為其不是根據每個記錄來產生行鎖的，相反，其根據每個事務訪問的每個頁對鎖進行管理的，采用的是位圖的方式。因此不管一 個事務鎖住頁中一個記錄還是多個記錄，其開銷通常都是一致的。

第7章 事務

數據庫系統引入事務的主要目的：事務會把數據庫從一種一致狀態轉換為另 一種一致狀態。在數據庫提交工作時，要么所有修改都已經保存了，要么所有修改都不保存。InnoDB存儲引擎中的事務完全符合ACID的特性：

• 原子性(atomicity)

• 一致性(consistency)

• 隔離性(isolation )

• 持久性(durability)

7.1認識事務

7.1.1概述

事務是訪問并更新數據庫中各種數據項的一個程序執行單元。在事務中的操作，要么都做修改，要么都不做，這就是事務的目的，也是事務模型區別與文件系統的重要特 征之一。

原子性。只有使事務中所有的數據庫操作都執行成功，才算整個事務成功。事務中任何一個SQL語句執行失敗，已經執行成功的 SQL語句也必須撤銷，數據庫狀態應該退回到執行事務前的狀態。一致性。一致性指事務將數據庫從一種狀態轉變為下一種一致的狀態。在事務開始之前和事務結束以后，數據庫的完整性約束沒有被破壞，事務是一致性的單位，如果事務中某個動作失敗了，系統可以自動撤銷事務——返 回初始化的狀態。隔離性。隔離性還有其他的稱呼，如并發控制(concurrency control)、 可串行化(serializability)、鎖(locking)等。事務的隔離性要求每個讀寫事務的對象對其他事務的操作對象能相互分離，即該事務提交前對其他事務都不可見，通常這使用鎖來實現。當前數據庫系統中都提供了一種粒度鎖(granular lock)的策略，允許事務僅鎖住一個實體對象的子集，以此來提高事務之間的并發度。持久性。事務一旦提交，其結果就是永久性的。即使發生宕機等故障，數據庫也能將數據恢復。

7.1.2分類

從事務理論的角度來說，可以把事務分為以下兒種類型：扁平事務(Flat Transaction)，所有操作都處于同一層次，其由BEGIN WORK開始，由COMMIT WORK或ROLLBACK WORK結束，其間的操作是原子的，要么都執行，要么都回滾。因此扁平事務是應用程序成為原子操作的基本組成模塊。

扁平事務的主要限制是不能提交或者回滾事務的某一部分，或分幾個步驟提交。

帶有保存點的扁平事務(Flat Transactions with Savepoint),除了支持扁平事務支 持的操作外，允許在事務執行過程中回滾到同一事務中較早的一個狀態。保存點(Savepoint)用來通知系統應該記住事務當前的狀態，以便 當之后發生錯誤時，事務能回到保存點當時的狀態。

鏈事務(Chained Transaction)可視為保存點模式的一種變種。鏈事務的思想是：在提交一個事務時，釋放不需要的數據對象，將必要的處理上下 文隱式地傳給下一個要開始的事務。注意，提交事務操作和開始下一個事務操作將合并為一個原子操作。鏈事務與帶有保存點的扁平事務不同的是，帶有保存點的扁平事務能回滾到任意正 確的保存點。而鏈事務中的回滾僅限于當前事務，即只能恢復到最近一個的保存點。

嵌套事務(Nested Transaction)是一個層次結構框架。由一個頂層事務(top- level transaction)控制著各個層次的事務。頂層事務之下嵌套的事務被稱為子事務 (subtransaction),其控制每一個局部的變換。

分布式事務(Distributed Transactions)通常是一個在分布式環境下運行的扁平事務，因此需要根據數據所在位置訪問網絡中的不同節點。對于分布式事務，其同樣需要滿足ACID特性，要么都發生，要么都失效。

7.2事務的實現

事務隔離性鎖來實現。

原子性、一致性、持久性通過數據庫的redo log和undo log來完成。redo log稱為重做日志，用來保證事務的原子性和持久性。undo log用來保證事務的一致性。redo恢復提交事務修改的頁操作，而undo回滾行記錄到某個特定版本。因此兩者記錄的內容不同，redo通常是物理日志，記錄的是頁的物理修改操作。undo是邏輯日志，根據每行記錄進行記錄。

7.2.1 redo

1.基本概念重做日志用來實現事務的持久性，由兩部分組成：一是內存中的重做日志緩沖(redo log buffer),其是易失的；二是重做日志文件(redo log file), 其是持久的。

InnoDB是事務的存儲引擎，其通過Force Log at Commit機制實現事務的持久性，即當事務提交(COMMIT)時，必須先將該事務的所有日志寫入到重做日志文件進行持久化，待事務的COMMIT操作完成才算完成。這里的日志是指重做日志，在 InnoDB存儲引擎中，由兩部分組成，即redo log和undo log。redo log用來保證事務的持久性，undo log用來幫助事務回滾及MVCC的功能。redo log基本上都是順序寫的， 在數據庫運行時不需要對redo log的文件進行讀取操作。而undo log是需要進行隨機讀寫的。

為了確保每次日志都寫入重做日志文件，在每次將重做日志緩沖寫入重做日志文件后，InnoDB存儲引擎都需要調用一次fsync操作。由于重做日志文件打開并沒有使用 O_DIRECT選項，因此重做日志緩沖先寫入文件系統緩存。為了確保重做日志寫入磁盤，必須進行一次fsync操作。由于fsync的效率取決于磁盤的性能，因此磁盤的性能確定了事務提交的性能，也就是數據庫的性能。

InnoDB存儲引擎允許用戶手工設置非持久性的情況發生，以此提高數據庫的性能。即當事務提交時，日志不寫入重做日志文件，而是等待一個時間周期后再執行fsync操 作。由于并非強制在事務提交時進行一次fsync操作，顯然這可以顯著提高數據庫的性能。但是當數據庫發生宕機時，由于部分日志未刷新到磁盤，因此會丟失最后一段時間的事務。參數innodb_flush_log_at_trx_commit用來控制重做日志刷新到磁盤的策略。該參數 的默認值為1,表示事務提交時必須調用一次fsync操作。還可以設置該參數的值為0和 2。表示事務提交時不進行寫入重做日志操作，這個操作僅在master thread中完成，而 在master thread中每1秒會進行一次重做日志文件的fsync操作。2表示事務提交時將 重做日志寫入重做日志文件，但僅寫入文件系統的緩存中，不進行fsync操作。在這個 設置下，當MySQL數據庫發生宕機而操作系統不發生宕機時，并不會導致事務的丟失。而當操作系統宕機時，重啟數據庫后會丟失未從文件系統緩存刷新到重做日志文件那部 分事務。

在MySQL數據庫中還有一種二進制日志(binlog),其用來進行PONIT-IN-TIME (PIT)的恢復及主從復制(Replication)環境的建立。從表面上看其和重做日志罪常相似，都是記錄了對于數據庫操作的日志。首先，重做日志是在InnoDB存儲引擎層產生，而二進制日志是在MySQL數據庫的上層產生的，并且二進制日志不僅僅針對于InnoDB存儲引擎，MySQL數據庫中的任何存儲引擎對于數據庫的更改都會產生二進制日志。其次，兩種日志記錄的內容形式不同。MySQL數據庫上層的二進制日志是一種邏輯日志，其記錄的是對應的SQL語句。而InnoDB存儲引擎層面的重做日志是物理格式日志，其記錄的是對于每個頁的修改。此外，兩種日志記錄寫入磁盤的時間點不同。二進制日志只在事務提交完成后進行一次寫入。而InnoDB存儲引擎的重做日志在事務進行中不斷地被寫入， 這表現為日志并不是隨事務提交的順序進行寫入的。

7.2.2 undo

1 .基本概念重做日志記錄了事務的行為，可以很好地通過其對頁進行“重做”操作。但是事務有時還需要進行回滾操作，這時就需要undo.因此在對數據庫進行修改時，InnoDB存儲引擎不但會產生redo,還會產生一定量的undo。這樣如果用戶執行的事務或語句由于某種原因失敗了，又或者用戶用一條ROLLBACK語句請求回滾，就可以利用這些undo信息將數據回滾到修改之前的樣子。

redo存放在重做日志文件中，與redo不同，undo存放在數據庫內部的一個特殊段 (segment)中，這個段稱為undo段，undo段位于共享表空間內。undo是邏輯日志，因此只是將數據庫邏輯地恢復到原來的樣子。所有修改都被邏輯地取消了，但是數據結構和頁本身在回滾之后可能大不相同。不能將一個頁回滾到事務開始的樣子，因為這樣會影響其他事務正在進行的工作。

除了回滾操作，undo的另一個作用是MVCC,即在InnoDB存儲引擎中MVCC的 實現是通過undo來完成。當用戶讀取一行記錄時，若該記錄已經被其他事務占用，當前事務可以通過undo讀取之前的行版本信息，以此實現非鎖定讀取。最后也是最為重要的一點是，undo log會產生redo log,也就是undo log的產生會伴隨著redo log的產生，這是因為undo log也需要持久性的保護。

7.6事務的隔離級別

SQL標準定義的四個隔離級別為：

• READ UNCOMMITTED

• READ COMMITTED

• REPEATABLE READ

• SERIALIZABLE

READ UNCOMMITTED稱為瀏覽訪問(browse access),僅僅針對事務而言， READ COMMITTED 稱為游標穩定， REPEATABLE READ 是 2.9999° 的隔離，沒有幻讀的保護。SERIALIZABLE稱為隔離，或3°的隔離。SQL和SQL2標 準的默認事務隔離級別是SERIALIZABLEInnoDB存儲引擎默認支持的隔離級別是REPEATABLE READ,但是與標準SQL不同的是，InnoDB存儲引擎在REPEATABLE READ事務隔離級別下，使用Next-Key Lock鎖的算法，因此避免幻讀的產生。

InnoDB存儲引擎在默認的REPEATABLE READ的事務隔離級別下已經能完全保證事務的隔離性要求，即達到SQL標準的SERIALIZABLE隔離級別。隔離級別越低，事務請求的鎖越少或保持鎖的時間就越短。這也是為什么大多數數據庫系統默認的事務隔離級別是READ COMMITTED在InnoDB存儲引擎中，可以使用以下命令來設置當前會話或全局的事務隔離級別：如果想在MySQL數據庫啟動時就設置事務的默認隔離級別，那就需要修改MySQL 的配置文件，在[mysqld]中添加如下行：

[mysqld]transaction-isolation = READ-COMMITTED

查看當前會話的事務隔離級別

SELECT @@tx_isolation\G

査看全局的事務隔離級別

SELECT @@global.tx_isolation\G

7.7分布式事務

7.7.1 MySQL數據庫分布式事務

InnoDB存儲引擎提供了對XA事務的支持，并通過XA事務來支持分布式事務的實現。分布式事務指的是允許多個獨立的事務資源(transactional resources)參與到一個全局的事務中。事務資源通常是關系型數據庫系統，但也可以是其他類型的資源。全局事務要求在其中的所有參與的事務要么都提交，要么都回滾，這對于事務原有的ACID要 求又有了提高。另外，在使用分布式事務時，InnoDB存儲引擎的事務隔離級別必須設置 為SERIALIZABLE，XA事務允許不同數據庫之間的分布式事務，如一臺服務器是MySQL數據庫的，另 一臺是Oracle數據庫的，又可能還有一臺服務器是SQL Server數據庫的，只要參與在 全局事務中的每個節點都支持XA事務。

分布式事務使用兩段式提交(two-phase commit)的方式。在第一階段，所有參與全局事務的節點都開始準備(PREPARE),告訴事務管理器它們準備好提交了。在第二階段，事務管理器告訴資源管理器執行ROLLBACK還是COMMIT。如果任何一個節點顯示不能提交，則所有的節點都被告知需要回滾。

第8章備份與恢復

8.1備份與恢復概述

可以根據不同的類型來劃分備份的方法。根據備份的方法不同可以將備份分為：

• Hot Backup (熱備)

• Cold Backup (冷備)

• Warm Backup (溫備)

Hot Backup是指數據庫運行中直接備份，對正在運行的數據庫操作沒有任何的影響。這種方式在MySQL官方手冊中稱為Online Backup (在線備份)。Cold Backup是指備份操作是在數據庫停止的情況下，這種備份最為簡單，一般只需要復制相關的數據庫物理文件即訶。這種方式在MySQL官方手冊中稱為Offline Backup (離線備份)。Warm Backup備份同樣是在數據庫運行中進行的，但是會對當前數據庫的操作有所影響，如加 一個全局讀鎖以保證備份數據的一致性。

按照備份后文件的內容，備份又可以分為：

• 邏輯備份

• 裸文件備份

邏輯備份是指備份出的文件內容是可讀的，一般是文本 文件。內容一般是由一條條SQL語句，或者是表內實際數據組成。如mysqldump和SELECT*INTO OUTFILE的方法。這類方法的好處是可以觀察導出文件的內容，一般適 用于數據庫的升級、遷移等工作。但其缺點是恢復所需要的時間往往較長。

裸文件備份是指復制數據庫的物理文件，既可以是在數據庫運行中的復制(如 ibbackup. xtrabackup這類工具)，也可以是在數據庫停止運行時直接的數據文件復制。這類備份的恢復時間往往較邏輯備份短很多。

若按照備份數據庫的內容來分，備份又可以分為：

• 完全備份

• 增量備份

• 日志備份

完全備份是指對數據庫進行一個完整的備份。

增量備份是指在上次完全備份的基礎上，對于更改的數據進行備份。

日志備份主要是指對MySQL數據庫二進制日志的備份， 通過對一個完全備份進行二進制日志的重做(replay)來完成數據庫的point-in-time的恢復工作。

MySQL數據庫復制(replication)的原理就是異步實時地將二進制日志重做傳送并應用到從(slave/standby)數據庫。

8.2冷備

對于InnoDB存儲引擎的冷備非常簡單，只需要備份MySQL數據庫的fan文件，共享表空間文件，獨立表空間文件(*.ibd),重做日志文件。另外建議定期備份MySQL數據庫的配置文件my.cnf,這樣有利于恢復的操作

冷備的優點是：

• 備份簡單，只要復制相關文件即可。

• 備份文件易于在不同操作系統，不同MySQL版本上進行恢復。

• 恢復相當簡單，只需要把文件恢復到指定位置即可。

• 恢復速度快，不需要執行任何SQL語句，也不需要重建索引。

冷備的缺點是：

• InnoDB存儲引擎冷備的文件通常比邏輯文件大很多，因為表空間中存放著很多其他數據

• 冷備也不總是可以輕易地跨平臺。操作系統、MySQL的版本、文件大小寫敏感 和浮點數格式都會成為問題。

8.3邏輯備份

8.3.1 mysqldump

通常用來完成轉存(dump) 數據庫的備份及不同數據庫之間的移植，如從MySQL低版本數據庫升級到MySQL高版本數據庫,又或者從MySQL數據庫移植到Oracle、Microsoft SQL Server數據庫等。mysqldump的語法如下：

shell>mysqldump (arguments] >file_naine備份所有的數據庫，可以使用-all-databases選項：mysqldump --all-databases >dump.sql備份指定的數據庫，可以使用-databases選項：mysqldump --databases dbl db2 db3 >dump.sql如果想要對test這個架構進行備份，可以使用如下語句：mysqldump --single-transaction test >test_backup.sql

8.3.2 SELECT...INTO OUTFILE

SELECT...INTO語句也是一種邏輯備份的方法，更準確地說是導出一張表中的數據。

#默認導出的文件是以TAB進行列分割的 select * into outfile '/home/mysql/a.txt' from a; cat /home/mysql/a.txt #使用其他分割符?mysql?test?-e?"select?*?into?outfile?'/home/mysql/a.txt'?fields?terminated?by?*,?*?from?a";

8.3.3邏輯備份的恢復

mysqldump的恢復操作比較簡單，因為備份的文件就是導出的SQL語句，一般只需要執行這個文件就可以了

mysql -uroot -p source /home/mysql/test_backup.sql

8.3.4 LOAD DATA INFILE

若通過mysqldump-tab,或者通過SELECT INTO OUTFILE導出的數據需要恢復,可以通過命令LOAD DATA INFILE來進行導入。

load data infile '/home/mysql/a.txt' into table a;

8.4二進制日志備份與恢復

二進制日志非常關鍵，用戶可以通過它完成point-in-time的恢復工作。MySQL數據庫的replication同樣需要二進制日志。在默認情況下并不啟用二進制日志，要使用二進制日志首先必須啟用它。如在配置文件中進行設置：

[mysqld]log-bin=mysql-bin

只簡單啟用二進制日志是不夠的， 還需要啟用一些其他參數來保證最為安全和正確地記錄二進制日志,推薦的二進制日志的服務器配置應該是：

[mysqld]log-bin = mysql-binsync_binlog = 1innodb_support_xa = 1

在備份二進制日志文件前，可以通過FLUSH LOGS命令來生成一個新的二進制日志文件，然后備份之前的二進制日志。要恢復二進制日志也是非常簡單的，通過mysqlbinlog即可。mysqlbinlog的使用方法如下：

#恢復二進制日志mysqlbinlog binlog. 0000001 I mysql-uroot -p test#恢復多個二進制日志文件mysqlbinlog binlog.[0-10]* I mysql -u root -p test

也可以先通過mysqlbinlog命令導出到一個文件，然后再通過SOURCE命令來導入

mysqlbinlog binlog.000001 > /tmp/statements.sqlmysqlbinlog binlog.000002 >> /tmp/statements.sqlmysql -u root -p -e "source /tmp/statements.sql"

8.5熱備

8.5.1 ibbackup

ibbackup是InnoDB存儲引擎官方提供的熱備工具，可以同時備份MylSAM存儲引擎和InnoDB存儲引擎表

8.5.2 XtraBackup

XtraBackup備份工具是由Percona公司開發的開源熱備工具。支持MySQL5.0以上的版本。

8.5.3 XtraBackup實現增量備份

MySQL數據庫本身提供的工具并不支持真正的增量備份，更準確地說，二進制日志的恢復應該是point-in-time的恢復而不是增量備份。而XtraBackup工具支持對于 InnoDB存儲引擎的增量備份

第9章性能調優

9.1選擇合適的CPU

InnoDB存儲引擎一般都應用于OLTP的數據庫應用，這種應用的特點如下：

• 用戶操作的并發量大

• 事務處理的時間一般比較短

• 查詢的語句較為簡單，一般都走索引

• 復雜的査詢較少

本身對CPU的要求并不是很高，可以說OLAP是CPU密集型的操作，而OLTP是IO密集型的操作。在采購設備時，將更多的注意力放在提高IO的配置上。

此外，為了獲得更多內存的支持，用戶采購的CPU必須支持64位，否則無法支持64位操作系統的安裝。因此，為新的應用選擇64位的CPU是必要的前提。如果CPU是多核的，可以通過修改參數innodb_read_io_ threads和innodb_write_io_threads來增大IO的線程，充分有效地利用CPU 的多核性能。

9.2內存的重要性

內存的大小是最能直接反映數據庫的性能。InnoDB存儲引擎既緩存數據，又緩存索引，并且將它們緩存于一個很大的緩沖池中，即 InnoDB Buffer PooL因此，內存的大小直接影響了數據庫的性能。

應該在開發應用前預估“活躍”數據庫的大小是多少，并以此確定數據庫服 務器內存的大小。如何判斷當前數據庫的內存是否已經達到瓶頸了呢？比較物理磁盤的讀取和內存讀取的比例來判斷緩沖池的命中率，通常InnoDB存儲引擎的緩沖池的命中率不應該小于99%

SHOW GLOBAL STATUS LIKE 'innodb%read%'\G; 緩沖池的命中率=Innodb_buffer_pool_read_requests/Innodb_buffer_pool_read_ahead_evicted+Innodb_buffer_pool_read_requests+Innodb_buffer_pool_reads

9.3硬盤對數據庫性能的影響

9.3.1傳統機械硬盤

機械硬盤有兩個重要的指標：一個是尋道時間，另一個是轉速。

傳統機械硬盤最大的問題在于讀寫磁頭，讀寫磁頭的設計使硬盤可以不再像磁帶一樣，只能進行順序訪問，而是可以隨機訪問。但是，機械硬盤的訪問需要耗費長時間的磁頭旋轉和定位來查找，因此順序訪問的速度要遠高于隨機訪問。

9.3.2固態硬盤

固態硬盤，更準確地說是基于閃存的固態硬盤，

9.4合理地設置RAID

9.4.1 RAID 類型

RAID (Redundant Array of Independent Disks,獨立磁盤冗余數組)的基本思想就是把多個相對便宜的硬盤組合起來，成為一個磁盤數組，使性能達到甚至超過一個價格昂貴、容量巨大的硬盤。由于將多個硬盤組合成為一個邏輯扇區，RAID看起來就像一個單獨的硬盤或邏輯存儲單元，因此操作系統只會把它當作一個硬盤。RAID的作用是：

• 增強數據集成度?

• 增強容錯功能

• 增加處理量或容量

9.7選擇合適的基準測試工具

基準測試工具可以用來對數據庫或操作系統調優后的性能進行對比。MySQL數據 庫本身提供了一些比較優秀的工具，這里將介紹另外兩款更為優秀和常用的基準測試工 具：sysbench 和 mysql-tpcco

9.7.1 sysbench

sysbench是一個模塊化的、跨平臺的多線程基準測試工具，主要用于測試各種不同 系統參數下的數據庫負載情況。它主要包括以下幾種測試方式：CPU性能、磁盤IO性能、調度程序性能、內存分配及傳輸速度、POSIX線程性能、數據庫OLTP基準測試

9.7.2 mysql-tpcc

TPC是一個用來評價大型數據庫系統軟硬件性能的非盈利組織。TPC-C是TPC協會制定的，目前在學術界和工業界普遍采用TPC-C 來評價OLTP應用的性能。

tpcc-mysql由以下兩個工具組成。

• tpcc_load：根據倉庫數量，生成9張表中的數據。

• tpcc_start：根據不同選項進行TPC-C測試。

總結

以上是生活随笔為你收集整理的不同存储结构的文件磁盘io操作次数_MySQL InnoDB存储引擎的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。