歡迎光臨
每天分享高質量文章

什麼影響了 MySQL 性能?

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

 


1 影響性能的幾個方面

  1. 服務器硬體。
  2. 服務器系統(系統引數優化)。
  3. 儲存引擎
    MyISAM: 不支持事務,表級鎖。
    InnoDB: 支持事務,支持行級鎖,事務ACID
  4. 資料庫引數配置。
  5. 資料庫結構設計和SQL陳述句。(重點優化)

2 MySQL體系結構

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

clipboard.png
  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日誌記錄某資料被修改前的值,可以用來在事務失敗時進行rollbackRedo日誌記錄某資料塊被修改後的值,可以用來恢復未寫入data file的已成功事務更新的資料。

4.2 特性二:支持行級鎖

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

5 什麼是鎖

5.1 鎖

clipboard.png

5.2 鎖型別

clipboard.png

5.3 鎖的粒度

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

clipboard.png

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 性能優化順序

 從上到下:

赞(0)

分享創造快樂