• • 1 影響效能的幾個方面
  • 2 MySQL體系結構
  • 3 InnoDB儲存引擎
  • 4 InnoDB儲存引擎的特性
  • 5 什麼是鎖
  • 6 如何選擇正確的儲存引擎
  • 7 配置引數
  • 8 效能最佳化順序

 

---

1 影響效能的幾個方面

1. 伺服器硬體。
2. 伺服器系統（系統引數最佳化）。
3. 儲存引擎。
   MyISAM： 不支援事務，表級鎖。
   InnoDB: 支援事務，支援行級鎖，事務ACID。
4. 資料庫引數配置。
5. 資料庫結構設計和SQL陳述句。（重點最佳化）

2 MySQL體系結構

 分三層：客戶端->服務層->儲存引擎

1. MySQL是外掛式的儲存引擎，其中儲存引擎分很多種。只要實現符合mysql儲存引擎的介面，可以開發自己的儲存引擎!
2. 所有跨儲存引擎的功能都是在服務層實現的。
3. MySQL的儲存引擎是針對錶的，不是針對庫的。也就是說在一個資料庫中可以使用不同的儲存引擎。但是不建議這樣做。

3 InnoDB儲存引擎

 MySQL5.5及之後版本預設的儲存引擎：InnoDB。

3.1 InnoDB使用表空間進行資料儲存。

show variables like 'innodb_file_per_table

如果innodb_file_per_table 為 ON 將建立獨立的表空間，檔案為tablename.ibd；

如果innodb_file_per_table 為 OFF 將資料儲存到系統的共享表空間，檔案為ibdataX（X為從1開始的整數）；

.frm ：是伺服器層面產生的檔案，類似伺服器層的資料字典，記錄表結構。

3.2 (MySQL5.5預設)系統表空間與(`MySQL5.6`及以後預設)獨立表空間

1.1 系統表空間無法簡單的收縮檔案大小，造成空間浪費，並會產生大量的磁碟碎片。

1.2 獨立表空間可以透過optimeze table 收縮系統檔案，不需要重啟伺服器也不會影響對錶的正常訪問。

2.1 如果對多個表進行掃清時，實際上是順序進行的，會產生IO瓶頸。

2.2 獨立表空間可以同時向多個檔案掃清資料。

強烈建立對Innodb 使用獨立表空間，最佳化什麼的更方便，可控。

3.3 系統表空間的表轉移到獨立表空間中的方法

1、使用mysqldump 匯出所有資料庫資料（儲存過程、觸發器、計劃任務一起都要匯出 ）可以在從伺服器上操作。

2、停止MYsql 伺服器，修改引數（my.cnf加入innodb_file_per_table），並刪除Inoodb相關檔案（可以重建Data目錄）。

3、重啟MYSQL，並重建Innodb系統表空間。

4、 重新匯入資料。

 或者 Alter table 同樣可以的轉移，但是無法回收系統表空間中佔用的空間。

4 InnoDB儲存引擎的特性

4.1 特性一：事務性儲存引擎及兩個特殊日誌型別：Redo Log 和 Undo Log

1. Innodb 是一種事務性儲存引擎。
2. 完全支援事務的ACID特性。
3. 支援事務所需要的兩個特殊日誌型別：Redo Log 和Undo Log

 Redo Log：實現事務的永續性(已提交的事務)。
Undo Log：未提交的事務，獨立於表空間，需要隨機訪問，可以儲存在高效能io裝置上。

Undo日誌記錄某資料被修改前的值，可以用來在事務失敗時進行rollback；Redo日誌記錄某資料塊被修改後的值，可以用來恢復未寫入data file的已成功事務更新的資料。

4.2 特性二：支援行級鎖

1. InnoDB支援行級鎖。
2. 行級鎖可以最大程度地支援併發。
3. 行級鎖是由儲存引擎層實現的。

5 什麼是鎖

5.1 鎖

5.2 鎖型別

5.3 鎖的粒度

MySQL的事務支援不是系結在MySQL伺服器本身，而是與儲存引擎相關

將table_name加表級鎖命令：lock table table_name write; 寫鎖會阻塞其它使用者對該表的‘讀寫’操作，直到寫鎖被釋放：unlock tables；

1. 鎖的開銷越大，粒度越小，併發度越高。
2. 表級鎖通常是在伺服器層實現的。
3. 行級鎖是儲存引擎層實現的。innodb的鎖機制，伺服器層是不知道的

5.4 阻塞和死鎖

（1）阻塞是由於資源不足引起的排隊等待現象。
（2）死鎖是由於兩個物件在擁有一份資源的情況下申請另一份資源，而另一份資源恰好又是這兩物件正持有的，導致兩物件無法完成操作，且所持資源無法釋放。

6 如何選擇正確的儲存引擎

 參考條件：

1. 事務
2. 備份(Innobd免費線上備份)
3. 崩潰恢復
4. 儲存引擎的特有特性

 總結:Innodb大法好。
註意:儘量別使用混合儲存引擎，比如回滾會出問題線上熱備問題。

7 配置引數

7.1 記憶體配置相關引數

確定可以使用的記憶體上限。

記憶體的使用上限不能超過物理記憶體，否則容易造成記憶體上限溢位；（對於32位作業系統，MySQL只能試用3G以下的記憶體。）

確定MySQL的每個連線單獨使用的記憶體。

sort_buffer_size #定義了每個執行緒排序快取區的大小，MySQL在有查詢、需要做排序操作時才會為每個緩衝區分配記憶體（直接分配該引數的全部記憶體）；
join_buffer_size #定義了每個執行緒所使用的連線緩衝區的大小，如果一個查詢關聯了多張表，MySQL會為每張表分配一個連線緩衝，導致一個查詢產生了多個連線緩衝；
read_buffer_size #定義了當對一張MyISAM進行全表掃描時所分配讀緩衝池大小，MySQL有查詢需要時會為其分配記憶體，其必須是4k的倍數；
read_rnd_buffer_size #索引緩衝區大小，MySQL有查詢需要時會為其分配記憶體，只會分配需要的大小。

 註意：以上四個引數是為一個執行緒分配的，如果有100個連線，那麼需要×100。

MySQL資料庫實體：

　①MySQL是單行程多執行緒（而oracle是多行程），也就是說MySQL實體在系統上表現就是一個服務行程，即行程；

　②MySQL實體是執行緒和記憶體組成，實體才是真正用於運算元據庫檔案的；

一般情況下一個實體操作一個或多個資料庫；叢集情況下多個實體操作一個或多個資料庫。

 如何為快取池分配記憶體：
Innodb_buffer_pool_size，定義了Innodb所使用快取池的大小，對其效能十分重要，必須足夠大，但是過大時，使得Innodb 關閉時候需要更多時間把臟頁從緩衝池中掃清到磁碟中；

總記憶體-（每個執行緒所需要的記憶體*連線數）-系統保留記憶體

 key_buffer_size，定義了MyISAM所使用的快取池的大小，由於資料是依賴儲存作業系統快取的，所以要為作業系統預留更大的記憶體空間；

select sum(index_length) from information_schema.talbes where engine='myisam'

 註意：即使開發使用的表全部是Innodb表，也要為MyISAM預留記憶體，因為MySQL系統使用的表仍然是MyISAM表。

 max_connections 控制允許的最大連線數， 一般2000更大。
不要使用外來鍵約束保證資料的完整性。

8 效能最佳化順序

 從上到下：