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

分分鐘解決 MySQL 查詢速度慢與性能差

作者:唐立勇

出處:https://segmentfault.com/a/1190000013672421


一、什麼影響了資料庫查詢速度

1.1 影響資料庫查詢速度的四個因素

1.2 風險分析

QPS: QueriesPerSecond意思是“每秒查詢率”,是一臺服務器每秒能夠相應的查詢次數,是對一個特定的查詢服務器在規定時間內所處理流量多少的衡量標準。

TPS: 是 TransactionsPerSecond的縮寫,也就是事務數/秒。它是軟體測試結果的測量單位。客戶機在發送請求時開始計時,收到服務器響應後結束計時,以此來計算使用的時間和完成的事務個數。

Tips: 最好不要在主庫上資料庫備份,大型活動前取消這樣的計劃。

  1. 效率低下的 sql:超高的 QPS與 TPS
  2. 大量的併發:資料連接數被占滿( max_connection預設 100,一般把連接數設置得大一些)。 併發量:同一時刻資料庫服務器處理的請求數量
  3. 超高的 CPU使用率: CPU資源耗盡出現宕機。
  4. 磁盤 IO:磁盤 IO性能突然下降、大量消耗磁盤性能的計劃任務。解決:更快磁盤設備、調整計劃任務、做好磁盤維護。

1.3 網卡流量:如何避免無法連接資料庫的情況

  • 減少從服務器的數量(從服務器會從主服務器複製日誌)
  • 進行分級快取(避免前端大量快取失效)
  • 避免使用 select* 進行查詢
  • 分離業務網絡和服務器網絡

1.4 大錶帶來的問題( 重要

1.4.1 大表的特點

  • 記錄行數巨大,單表超千萬
  • 表資料檔案巨大,超過 10個 G

1.4.2 大表的危害

1.慢查詢:很難在短時間內過濾出需要的資料 查詢字區分度低 -> 要在大資料量的表中篩選出來其中一部分資料會產生大量的磁盤 io -> 降低磁盤效率

2.對 DDL影響:

建立索引需要很長時間:

  • MySQL-v<5.5 建立索引會鎖表
  • MySQL-v>=5.5 建立索引會造成主從延遲( mysql建立索引,先在組上執行,再在庫上執行)

修改表結構需要長時間的鎖表:會造成長時間的主從延遲(‘480秒延遲’)

1.4.3 如何處理資料庫上的大表

分庫分表把一張大表分成多個小表

難點:

  1. 分表主鍵的選擇
  2. 分表後跨分割槽資料的查詢和統計

1.5 大事務帶來的問題( 重要*)*

1.5.1 什麼是事務

1.5.2事務的 ACID屬性

1、原子性( atomicity):全部成功,全部回滾失敗。銀行存取款。

2、一致性(consistent):銀行轉賬的總金額不變。

3、隔離性(isolation):

隔離性等級:

  • 未提交讀( READ UNCOMMITED臟讀,兩個事務之間互相可見;
  • 已提交讀( READ COMMITED)符合隔離性的基本概念,一個事務進行時,其它已提交的事物對於該事務是可見的,即可以獲取其它事務提交的資料。
  • 可重覆讀( REPEATABLE READ InnoDB的預設隔離等級。事務進行時,其它所有事務對其不可見,即多次執行讀,得到的結果是一樣的!
  • 可串行化( SERIALIZABLE) 在讀取的每一行資料上都加鎖,會造成大量的鎖超時和鎖徵用,嚴格資料一致性且沒有併發是可使用。

查看系統的事務隔離級別: show variables like'%iso%';

開啟一個新事務: begin;

提交一個事務: commit;

修改事物的隔離級別: setsession tx_isolation='read-committed';

4、持久性( DURABILITY):從資料庫的角度的持久性,磁盤損壞就不行了

redolog機制保證事務更新的一致性持久性

1.5.3 大事務

運行時間長,運算元據比較多的事務;

風險:鎖定資料太多,回滾時間長,執行時間長。

  • 鎖定太多資料,造成大量阻塞和鎖超時;
  • 回滾時所需時間比較長,且資料仍然會處於鎖定;
  • 如果執行時間長,將造成主從延遲,因為只有當主服務器全部執行完寫入日誌時,從服務器才會開始進行同步,造成延遲。

解決思路:

  • 避免一次處理太多資料,可以分批次處理;
  • 移出不必要的 SELECT操作,保證事務中只有必要的寫操作。

二、什麼影響了MySQL性能( 非常重要)

2.1 影響性能的幾個方面

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

2.2 MySQL體系結構

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

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

2.3 InnoDB儲存引擎

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

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

show variables like'innodb_file_per_table

如果innodbfileper_table 為 ON 將建立獨立的表空間,檔案為tablename.ibd;

如果innodbfileper_table 為 OFF 將資料儲存到系統的共享表空間,檔案為ibdataX(X為從1開始的整數);

.frm :是服務器層面產生的檔案,類似服務器層的資料字典,記錄表結構

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

  • 1.1 系統表空間無法簡單的收縮檔案大小,造成空間浪費,並會產生大量的磁盤碎片。
  • 1.2 獨立表空間可以通過 optimeze table 收縮系統檔案,不需要重啟服務器也不會影響對錶的正常訪問。
  • 2.1 如果對多個表進行掃清時,實際上是順序進行的,會產生IO瓶頸。
  • 2.2 獨立表空間可以同時向多個檔案掃清資料。

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

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

  • 1、使用mysqldump 匯出所有資料庫資料(儲存過程、觸發器、計劃任務一起都要匯出 )可以在從服務器上操作。
  • 2、停止MYsql 服務器,修改引數(my.cnf加入innodbfileper_table),並刪除Inoodb相關檔案(可以重建Data目錄)。
  • 3、重啟MYSQL,並重建Innodb系統表空間。
  • 4、 重新匯入資料。

或者 Altertable 同樣可以的轉移,但是無法回收系統表空間中占用的空間。

2.4 InnoDB儲存引擎的特性

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

  1. Innodb 是一種事務性儲存引擎
  2. 完全支持事務的 ACID特性。
  3. 支持事務所需要的兩個特殊日誌型別: RedoLog 和 UndoLog

Redo Log: 實現事務的持久性(已提交的事務)。 Undo Log: 未提交的事務,獨立於表空間,需要隨機訪問,可以儲存在高性能io設備上。

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

2.4.2 特性二:支持行級鎖

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

2.5 什麼是鎖

2.5.1 鎖

2.5.2 鎖型別

2.5.3 鎖的粒度

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

table_name加表級鎖命令: locktable table_name write 寫鎖會阻塞其它用戶對該表的‘讀寫’操作,直到寫鎖被釋放: unlock tables

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

2.5.4 阻塞和死鎖

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

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

參考條件:

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

總結: Innodb 大法好。

註意: 儘量別使用混合儲存引擎,比如回滾會出問題在線熱備問題。

2.7 配置引數

2.7.1 記憶體配置相關引數

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

記憶體的使用上限不能超過物理記憶體,否則容易造成記憶體上限溢位;(對於32位操作系統,MySQL只能試用3G以下的記憶體。)

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

  1. 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 關閉時候需要更多時間把臟頁從緩衝池中掃清到磁盤中;

  1. 總記憶體-(每個執行緒所需要的記憶體*連接數)-系統保留記憶體

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

  1. select sum(index_length) from information_schema.talbes where e``ngine=``'myisam'

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

max_connections 控制允許的最大連接數, 一般2000更大。

不要使用外鍵約束保證資料的完整性。

2.8 性能優化順序

從上到下:

赞(0)

分享創造快樂