閱讀本文大概需要 6 分鐘。

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

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)：從資料庫的角度的持久性，磁碟損壞就不行了

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

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 likeinnodb_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的 每個連接 單獨 使用的內存。

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 e``ngine=``myisam

注意： 即使開發使用的表全部是Innodb表，也要為MyISAM預留內存，因為MySQL系統使用的表仍然是MyISAM表。

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

不要使用外鍵約束保證數據的完整性。

2.8 性能優化順序

從上到下：

作者：唐立勇

出處：segmentfault.com/a/1190

·END·

程序員的成長之路

路雖遠，行則必至

本文原發於 同名微信公眾號「程序員的成長之路」，回復「1024」你懂得，給個讚唄。微信ID：cxydczzl

往期精彩回顧程序員接私活的7大平臺利器教你一招用 IDE 編程提升效率的騷操作！大學期間的副業賺錢之道一個對話讓你明白架構師是做什麼的？作為程序員的你，一年看幾本技術相關的書5個相見恨晚的Linux命令為啥程序員下班後只關顯示器從不關電腦？送給程序員們的經典電子書大禮包面試時如何優雅地自我介紹？支撐百萬並發的資料庫架構如何設計？
推薦閱讀：
相關文章