Mysql資料庫優化

1.存盤引擎

什么是存盤引擎

存盤引擎:可以看作是資料表存盤資料的一種格式，不同的格式具有的特性也各不相同，
舉例說明:只有InnoDB存盤引擎支持事務、外鍵、行級鎖等特性，而MyISAM則支持壓碩訓制等特性，
存盤引擎的特點:本身是MySQL資料庫服務器的底層組件之一，最大的特點是采用“可插拔”的存盤引擎架構，
“可插拔”的理解:指的是對正在運行的MySQL服務器依然可根據實際需求使用特定陳述句加載（插入，INSTALL PLUGIN陳述句）或卸載（拔出，UNINSTALL PLUGIN陳述句）所需的存盤引擎檔案，

存盤引擎的選擇

查看當前支持的存盤引擎

SHOW ENGINES;

執行以上SQL陳述句，運行的結果中含有6個欄位，.
Engine (存盤引擎)、Support (是否支持)
Comment (注釋說明)、Transactions (是否支持事務)
XA (是否支持分布式事務)和Savepoints ( 是否支持事務的保存點設定)

1. InnoDB存盤引擎

InnoDB存盤引擎:在MySQL 5.7版本中被指定為默認的存盤引擎，

特性:是MySQL中第一個提供外鍵約束的表引擎，尤其對事務處理的能力，是MySQL其他存盤引擎無法與之比擬的，
優勢:用于完成事務、回滾、崩潰修復和多版本并發控制的事務安全處理，
缺點:讀寫效率一般，

存盤模式

• 存盤格式:默認InnoDB資料表都共用一個表空間檔案ibdata1，每張資料表都會在對應的資料庫下創建一個與表同名的結構檔案(資料庫/表名.frm)

ibdata1 的位置:通常位于data目錄下，與資料庫檔案處于同級目錄，
ibdata1 的作用:集中存盤資料和索引，

設定資料表的獨立表空間檔案:全域變數innodb_ file_ _per_ table，

#查看默認是否共用同一個表空間檔案.
SHOW VARIABLES LIKE 'innodb_file_per_table';
#開啟每個表獨立的表空間檔案
SET GLOBAL innodb_file_per_table=ON;

例：

2. MyISAM存盤引擎

MyISAM存盤引擎:在MySQL5.5以前的版本中是MySQL的默認存盤引擎，

特性:是基于ISAM存盤引擎發展起來的，不僅解決了ISAM的很多不足，還增加了很多有用的擴展，例如，資料的全文索引、壓縮與加密、支持復制與備份的恢復等，
優勢:與InnoDB相比，MyISAM的優點是處理速度快，
缺點:InnoDB相比，MyISAM的缺點是不支持事務的完整性和并發性，

MyISAM資料表的檔案擴展名分別為frm、myd和myi，檔案名與表名相同，

MyISAM表的資料移植非常方便，只需將資料庫下表中對應的3個檔案復制到另一個資料庫下即可，

3. MRG_MYISAM存盤引擎

MRG_MYISAM存盤引擎:相同MyISAM存盤引擎表的集合，也被稱為MERGE，

特性:所有合并的表必須具有相同順序的欄位與索引的應用，
優勢:可快速拆分大型只讀表，執行搜索效率更高等，
缺點:索引讀取速度較慢、只針對MyISAM存盤引擎的表進行合并、實際應用較少等，

4. MEMORY存盤引擎

MEMORY存盤引擎:在是MySQL中一種特殊的存盤引擎，

特性:在MEMORY存盤引擎的表中，所有資料都保存在記憶體中，一旦程式出錯或服務器斷電都會導致資料的丟失，
優勢:資料的處理速度快，
缺點:不適合持久保存資料，而且也不能存盤太大的資料，
對讀寫速度快，資料量小、不需要持久保存的臨時資料是理想的選擇，

5. CSV是存盤引擎

CSV是存盤引擎:是采用文本方式存盤資料的一種存盤引擎，資料在檔案中通過逗號分隔保存，

結構:資料表會分為后綴frm（存盤表結構資訊）、csv（存盤表內容）和csm(存盤表的狀態、資料量等元資料）3個檔案存盤，檔案名與資料表名相同，
一是不支持索引和磁區，二是表中所有欄位必須含有NOT NULL屬性，

6. ARCHIVE存盤引擎

ARCHIVE存盤引擎:適合保存數量龐大、長期維護但很少被訪問的資料，

特性:資料存盤時會用zlib壓縮庫進行壓縮，在記錄被請求時會實時進行解壓，
提示: ARCHIVE存盤引擎僅僅支持查詢和插入操作，并且因不支持資料索引，查詢效率較低，

7. BLACKHOLE存盤引擎

BLACKHOLE存盤引擎:被稱為“黑洞”存盤引擎，

特性:寫入的資料都會消失，就像被“黑洞”吞噬了一樣，
作用:利用此特性可以將其作為轉發器或過濾器，
舉例:將主服務器中的大量資料經過過濾后搬到從服務器，可將BL ACKHOLE的資料表作為過濾器使用，且不會保存任何資料，但是會在二進制日志中記錄下所有SQL陳述句，然后可復制并執行這些陳述句，將結果保存到從服務器中，

8. PERFORMANCE_SCHEMA存盤引擎

PERFORMANCE_SCHEMA存盤引擎: MySQL .5.7中performance_ schema資料庫中所有資料表的存盤引擎，

特性:用戶不能為資料表創建此型別的存盤引擎，
作用:主要用于收集資料庫服務器性能引數，

9. FEDERATED存盤引擎

FEDERATED存盤引擎:默認情況下在MySQL中不可用，

啟動:利用“–federated"選項，
特性:創建從遠程MySQL服務器訪問資料的表，本地的FEDERATED表只保存結構資訊(后綴為frm)，遠程服務器同時要保存結構資訊和資料檔案，所有的增刪改查操作都通過訪問遠程服務器后，才將結果回傳給本地的服務器，

2.索引

1. 索引概述

索引:是一種特殊的資料結構，可以看做是利用MySQL提供的語法將資料表中的某個或某些欄位與記錄的位置建立一一個對應的關系，并按照一定的順序排序好，
且的:就是為了快速定位指定資料的位置，

2. 索引分類

普通索引:是MySQL 中的基本索引型別，使用KEY或INDEX定義，不需要添加任何限制條件，作用是加快對資料的訪問速度，
唯一性索引:由UNIQUE INDEX定義，創建唯一性索引的欄位需要添加唯一性約束，用于防止用戶添加重復的值，
主鍵索引:由PRIMARY KEY定義的一種特殊的唯一性索引， 用于根據主鍵自身的唯一性標識每條記錄，防止添加主鍵索引的欄位值重復或為NULL，

• 若在InnoDB表中資料保存的順序與主鍵索引欄位的順序一致時，可將這種主鍵索引稱為“聚簇索引”，一般聚簇索引指的都是表的主鍵
• 一張資料表中只能有一個聚簇索引，

全文索引:由FULLTEXT INDEX定義，用于根據查詢字符提高資料量較大的欄位查詢速度，此索引在定義時欄位型別必須是CHAR、VARCHAR或TEXT中的一種，在MySQL 5.7版本中，僅MylSAM和InnoDB存盤引擎支持全文索引，
空間索引:由SPATIAL INDEX定義在空間資料型別欄位上的索引，提高系統獲取空間資料的效率，僅MyISAM和InnoDB存盤引擎支持空間索引，還要保證創建索引的欄位不能為空，

根據創建索引的欄位個數，還可以將它們分為單列索引和復合索引：

• 單列索引:指在表中單個欄位上創建的索引，可以是普通索引、唯一索引、主鍵索引或者全文索引，只要保證該索引對應表中一個欄位即可，
• 復合索引:是在表的多個欄位上創建一個索引，且只有在查詢條件中使用了這些欄位中的第一個欄位時，該索引才會被使用，

3. 索引的操作

創建索引

• CREATE TABLE方式，與資料表同時創建，

#方式1: CREATE TABLE創建資料表時添加索引
CREATE TABLE資料表名(
欄位名資料型別[約束條件]
...
PRIMARY KEY [索引型別] (欄位串列)[索引選項],
{INDEX|KEY} [索引名稱] [索引型別] (欄位串列)[索引選項],
UNIQUE [INDEXIKEY] [索引名稱] [索引型別] (欄位串列)[索引選項],
{FULLTEXT|SPATIAL} [INDEX|KEY] [索引名稱] (欄位串列)[索引選項]
)[表選項];

• ALTER TABLE方式，對已創建的資料表進行添加，

#方式2: ALTER TABLE向已創建的資料表添加索引
ALTER TABLE 資料表名
ADD PRIMARY KEY[索引型別] (欄位串列) [索引選項]
|ADD {INDEX|KEY} [索引名稱] [索引型別] (欄位串列) [索引選項]
|ADD UNIQUE [INDEX|KEY] [索引名稱] [索引型別] (欄位串列) [索引選項]
|ADD FULLTEXT [INDEX|KEY] [索引名稱] (欄位串列)[索引選項]
IADD SPATIAL [INDEX|KEY] [索引名稱] (欄位串列)[索引選項], ...;

• CREATE INDEX方式，對已創建的資料表進行添加，不能添加主鍵索引，

#方式3: CREATE INDEX向已創建的資料表添加索引.
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名稱
[索引型別] ON 資料表名(欄位串列)[索引選項][演算法選項|鎖選項]

索引可設定選項

主鍵索引不能設定索引名稱，其他索引的名稱也可以省略，默認使用建立索引的欄位表示，復合索引則使用第一個欄位的名稱作為索引名，

索引欄位資訊

所謂索引覆寫指的是查詢的欄位恰好是索引的一部分或與索引
完全一致，那么查詢只需要在索引區上進行，不需要到資料區
檢索資料的情況，
這種查詢的的特點是速度非常快，但同時也會增加索引檔案的
大小，只有此索引的使用率盡可能高的情況下，索引覆寫才有
意義，否則，在使用時應該避免此情況的發生，

洗掉索引

主鍵索引在洗掉時，需要考慮該主鍵欄位是否含有AUTO_INCREMENT屬性，若有則需在洗掉主鍵索引前洗掉該屬性，否則程式會報以下的錯誤提示資訊，

• 洗掉含有AUTO INCREMENT屬性的主鍵索引

#洗掉主鍵欄位的AUTO_ INCREMENT屬性
ALTER TABLE 資料表 MODIFY 欄位名欄位型別
#洗掉主鍵索引
ALTER TABLE 資料表 DROP PRIMARY KEY 或 DROP INDEX、'PRIMARY' ON 資料表

當使用DROP INDEX洗掉主鍵索引時，其后的PRIMARY由于是MySQL中的保留字，因此必須使用反引號(‘) 包裹，

洗掉命令：

#語法1
ALTER TABLE 資料表 DROP INDEX 索引名
#語法2
DROP INDEX 索引名 ON 資料表 [演算法選項][鎖選項]

例：

3.鎖機制

簡單的說，鎖機制就是為了保證多用戶并發操作時，能使被操作的資料資源保持一致性的設計規則，

鎖的分類

表級鎖(如MyISAM、MEMORY存盤引擎）

表級鎖:是MySQL中鎖的作用范圍( 鎖的粒度)最大的一種鎖，
鎖定范圍:是用戶操作資源所在的整個資料表，
優勢:有效的避免了死鎖的發生，且具有加鎖速度快、消耗資源小的特點，
缺陷:因其鎖定的粒度大，在并發操作時發生鎖沖突的概率也大，

行級鎖(如InnoDB存盤引擎特殊)

行級鎖:是MySQL 中鎖的作用范圍最小的一種鎖，
鎖定范圍:僅鎖定用戶操作所涉及的記錄資源，
優勢:能減少鎖定資源的競爭，較高并發處理能力，提升系統的整體性能，
缺陷:因其鎖定的粒度過小，每次加鎖和解鎖所消耗的資源也會更多，發生死鎖的可能性更高，

根據鎖在MySQL中的狀態也可將其分為“隱式”與“顯式”：

• “隱式”鎖指的是MySQL服務器本身對資料資源的爭用進行管理，它完全由服務器自動執行，
• “顯式”鎖指的是用戶根據實際需求，對操作的資料顯式的添加鎖，同樣在使用完資料資源后也需要用戶對其進行解鎖，

表級鎖

表級鎖:根據操作的不同可以分為讀鎖和寫鎖，
讀鎖:表示用戶讀取(如SELECT查詢)資料資源時添加的鎖，其他用戶不可修改或增加資料資源，但是可以讀取該資料資源，因此讀鎖也可稱為共享鎖，
寫鎖:表示用戶對資料資源執行寫(如INSERT、UPDATE、DELETE等)操作時添加的鎖，除了當前添加寫鎖的用戶外，其他用戶都不能對其進行讀/寫操作，因此寫鎖也可以稱為排他鎖或獨占鎖，

MyISAM存盤引擎表:是MySQL 資料庫中最典型的表級鎖，
“隱式”讀的表級鎖:當用戶對MyISAM存盤引擎表執行SELECT查詢操作前，服務器會“自動”地為其添加-一個表級的讀鎖，
“隱式”寫的表級鎖:執行INSERT、 UPDATE、DELETE等寫操作前，服務器會“自動”地為其添加一個表級的寫鎖，

“隱式”讀的表級鎖與“隱式”寫的表級鎖添加的優先級順序:

• 默認服務器“自動”添加“隱式”鎖時，表的更新操作優先于表的查詢操作，.
• 添加寫鎖時，若表中沒有任何鎖則添加，否則將其插入到寫鎖等待的佇列中，
• 添加讀鎖時，若表中沒有寫鎖則添加，否則將其插入到讀鎖等待的佇列中，

表級鎖語法：

LOCK TABLES 資料表名 READ [LOCAL]| WRITE,...

• LOCK TABLES可以同時鎖定多張資料表，
• READ表示表級的讀鎖，添加此鎖的用戶可讀但不能寫，其他用戶可以讀取，執行寫操作會進入等待佇列，
• WRITE表示表級的寫鎖，添加此鎖的用戶可執行讀/寫操作，在釋放鎖之前，不允許其他用戶訪問與操作，

表級鎖的問題:鎖定的粒度大，多用戶訪問會造成鎖競爭，降低并發處理能力，
從資料庫優化的角度來考慮:盡量減少表級鎖定時間，提高多用戶的并發能力，.
如何釋放“顯式”表級鎖: UNLOCK TABLES陳述句，

行級鎖

InnoDB存盤引擎的鎖機制相對于MyISAM存盤引擎的鎖復雜一些，
InnoDB存盤引擎既有表級鎖又有行級鎖，
InnoDB表級鎖的應用與MyISAM表級鎖的相同，
“隱式”行級排他鎖:當用戶對InnoDB存盤引擎表執行INSERT、UPDATE、DELETE等寫操作前，服務器會“自動”地為通過索引條件檢索的記錄添加行級排他鎖，
“隱式”行級排他鎖要如何解鎖:直到操作陳述句執行完畢，服務器再“自動”地為其解鎖，
“隱式”行級排他鎖的生命周期:陳述句的執行時間可以看作是“隱式”行級鎖的生命周期，且該生命周期的持續時間–般都比較短暫，
延長“隱式”行級排他鎖的生命周期:通常情況下，若要增加行級鎖的生命周期，最常使用的方式是事務處理，讓其在事務提交或回滾后再釋放行級鎖，使行級鎖的生命周期與事務的相同，

對于InnoDB表來說，若要保證當前事務中查詢出的資料不會被其他事務更新或洗掉，利用普通的SELECT陳述句是無法辦到的，此時需要利用MySQL提供的“鎖定讀取”的方式為查詢操作顯式的添加行級鎖，

SELECT 陳述句 FOR UPDATE|LOCK IN SHARE MODE
# FOR UPDATE:表示在查詢時添加行級排他鎖
# LOCK IN SHARE MODE:表示在查詢時添加行級共享鎖

用戶在向InnoDB表顯式添加行級鎖時，InnoDB存盤引擎首先會“自動”地向此表添加一個意向鎖，然后再添加行級鎖，

• 意向鎖是一個隱式的表級鎖，多個意向鎖之間不會產生沖突且互相兼容，
• 意向鎖是由MySQL服務器根據行級鎖是共享鎖還是排他鎖，自動添加意向共享鎖或意向排他鎖，不能人為干預，
• 意向鎖的作用:就是標識表中的某些記錄正在被鎖定或其他用戶將要鎖定表中的某些記錄，相對行級鎖，意向鎖的鎖定粒度更大，用于在行級鎖中添加表級鎖時判斷它們之間是否能夠互相兼容，
• 好處:就是大大節約了存盤引擎對鎖處理的性能，更加方便的解決了行級鎖與表級鎖之間的沖突，

默認當InnoDB處于REPEATABLE READ (可重復讀)的隔離級別時，行級鎖實際上是一個next-key鎖，它是由間隙鎖(gap lock)和記錄鎖(record lock)組成，

• 記錄鎖(record lock)就是前面講解的行鎖，
• 間隙鎖(gap lock)指的是在記錄索引之間的間隙、負無窮到第1個索引記錄之間或最后1個索引記錄到正無窮之間添加的鎖，

4.分表技術

分表技術： 將單張資料表根據不同的需求進行拆分，從而達到分散單表壓力的目的，提升資料庫的訪問性能，

分表分類

水平分表：將一張資料表中的全部記錄分別存盤到多張資料表中，因此水平分表在創建時，必須保證各資料表涉及到的欄位全部相同，
水平分表使單張表的資料能夠保持在一定的量級，

• 優勢:在操作時又因其表結構完全相同，只需增加獲取對應分表名稱的運算，就可以提高系統的穩定性和負載能力，
• 缺點:水平分表使得資料分散存盤，加大了資料維護難度，

垂直分表：將同一個業務的不同欄位分別存盤到多張資料表中，因此垂直分表在創建時，各資料表僅通過一個欄位進行連接，其他欄位都不相同，

• 垂直分表后業務邏輯更加的清晰，方便資料進行整合與擴展，還可以根據實際需求實作動靜分離，為各分表選擇不同的存盤引擎(如查詢操作多可以使用MyISAM等)，
• 缺點:需要管理冗余欄位、查詢所有資料需要進行連接，

5.磁區技術

磁區概述

磁區技術:就是在操作資料表時可以根據給定的演算法，將資料在邏輯上分到多個區域中存盤，在磁區中還可以設定子磁區，將資料存放到更加具體的區域內，

• 磁區技術可以使一張資料表中的資料存盤在不同的物理磁盤中，相比單個磁盤或檔案系統能夠存盤更多的資料，實作更高的查詢吞吐量，
• 若在WHERE子句中包含磁區條件，系統只需掃描相關的一個或多個磁區而不用全表掃描，從而提高查詢效率，
• 同一個磁區表的所有磁區必須使用相同存盤引擎，當建表時未指定存盤引擎，在創建磁區時必須設定存盤引擎，

磁區操作

CREATE TABLE 資料表名稱
[(欄位與索引串列)][表選項]
PARTITION BY 磁區演算法(磁區欄位)[PARTITIONS磁區數量]
[SUBPARTITION BY子磁區演算法(子磁區欄位)[SUBPARTITIONS子磁區數量]]
[(
PARTITION磁區名[VALUES值][其他選項][(SUBPARTITION 子磁區名 [其他選項])],
...
)];

磁區是在表選項后添加PARTITION BY實作，
一個表最多僅可以創建1024個磁區，
磁區演算法有4種，分別為LIST、RANGE、HASH和KEY，

創建list磁區

磁區創建完成后，會在資料檔案data/mydb目錄下看到對應的磁區資料檔案
p_list#p#p1.idb
p_list#p#p2.idb

創建hash磁區

使用HASH演算法為p_hash表創建了3個磁區，磁區檔案的序號默認從0開始，當有多個磁區時依次遞增加1，例如，以上創建的磁區序號依次為0、1和2，

#已創建的資料表沒有創建磁區，添加磁區的方式
ALTER TABLE資料表名稱PARTITION BY磁區演算法...;
#已創建的資料表含有磁區，添加磁區的方式
#LIST或RANGE磁區
ALTER TABLE資料表名稱ADD PARTITION (磁區選項，..;, .
#HASH或KEY磁區
ALTER TABLE資料表名稱PARTITIONS 數量;

#洗掉HASH、KEY磁區
ALTER TABLE資料表名稱COALESCE PARTITION數量;
#洗掉RANGE、LIST磁區
ALTER TABLE資料表名稱DROP PARTITION磁區名稱; .

• 洗掉HASH與KEY演算法磁區時，會將該磁區內的資料重新整合到剩余的磁區，
• 洗掉RANGE與LIST演算法磁區時，會同時洗掉磁區中保存的資料，
• 當資料表的磁區僅剩一個時，不能通過以上的方式洗掉，只能利用DROP TABLE的方式洗掉表，

若在開發中僅要清空各磁區表中的資料，不洗掉對應的磁區檔案，可以使用以下的陳述句實作，

ALTER TABLE 資料表名稱 TRUNCATE PARTITION{磁區名稱|ALL}

6.資料碎片與維護

在MySQL資料庫中，DELETE洗掉一條記錄時，僅洗掉了資料表中保存的資料，而記錄占用的存盤空間會被保留，
長期洗掉資料、添加資料的程序中，索引檔案和資料檔案都將產生“空洞”，形成很多不連續的碎片，造成資料表占用空間變大，但表中記錄數卻很少的情況發生，

修復資料表的資料及索引碎片時，會把所有的資料檔案重新整理一遍，因此，若資料表的記錄數比較大，也會消耗一定的資源，所以不能頻繁的對資料碎片進行維護，可根據實際的情況按周、月或季度等進行操作，.