一、什麼影響了數據庫查詢速度 1.1 影響數據庫查詢速度的四個因素

　　1.2 風險分析

　　QPS：Queries Per Second意思是“每秒查詢率”，是一台服務器每秒能夠相應的查詢次數，是對一個特定的查詢服務器在規定時間内所處理流量多少的衡量标準。

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

　　Tips：最好不要在主庫上數據庫備份，大型活動前取消這樣的計劃。

　　效率低下的sql：超高的QPS與TPS。大量的并發：數據連接數被占滿（max_connection默認100，一般把連接數設置得大一些）。并發量:同一時刻數據庫服務器處理的請求數量超高的CPU使用率：CPU資源耗盡出現宕機。磁盤IO：磁盤IO性能突然下降、大量消耗磁盤性能的計劃任務。解決：更快磁盤設備、調整計劃任務、做好磁盤維護。 1.3 網卡流量：如何避免無法連接數據庫的情況

　　減少從服務器的數量（從服務器會從主服務器複制日志）進行分級緩存（避免前端大量緩存失效）避免使用select * 進行查詢分離業務網絡和服務器網絡 1.4 大表帶來的問題（重要）

　　1.4.1 大表的特點

　　記錄行數巨大，單表超千萬表數據文件巨大，超過10個G 1.4.2 大表的危害

　　1.慢查詢：很難在短時間内過濾出需要的數據

　　查詢字區分度低 - 要在大數據量的表中篩選出來其中一部分數據會産生大量的磁盤io - 降低磁盤效率

　　2.對DDL影響：

　　建立索引需要很長時間：

　　MySQL -v5.5 建立索引會鎖表MySQL -v=5.5 建立索引會造成主從延遲（MySQL建立索引，先在組上執行，再在庫上執行） 修改表結構需要長時間的鎖表：會造成長時間的主從延遲('480秒延遲')

　　1.4.3 如何處理數據庫上的大表

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

　　難點：

　　分表主鍵的選擇分表後跨分區數據的查詢和統計 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;

　　修改事物的隔離級别：set session tx_isolation='read-committed';

　　4、持久性(DURABILITY)：從數據庫的角度的持久性，磁盤損壞就不行了

　　redo log機制保證事務更新的一緻性和持久性

　　1.5.3 大事務

　　運行時間長，操作數據比較多的事務；

　　風險：鎖定數據太多，回滾時間長，執行時間長。

　　鎖定太多數據，造成大量阻塞和鎖超時；回滾時所需時間比較長，且數據仍然會處于鎖定；如果執行時間長，将造成主從延遲，因為隻有當主服務器全部執行完寫入日志時，從服務器才會開始進行同步，造成延遲。 解決思路：

　　避免一次處理太多數據，可以分批次處理；移出不必要的SELECT操作，保證事務中隻有必要的寫操作。二、什麼影響了MySQL性能（非常重要） 2.1 影響性能的幾個方面

　　服務器硬件。服務器系統（系統參數優化）。存儲引擎。 MyISAM： 不支持事務，表級鎖。InnoDB: 支持事務，支持行級鎖，事務ACID。數據庫參數配置。數據庫結構設計和SQL語句。（重點優化） 2.2 MySQL體系結構

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

　　MySQL是插件式的存儲引擎，其中存儲引擎分很多種。隻要實現符合mysql存儲引擎的接口，可以開發自己的存儲引擎!所有跨存儲引擎的功能都是在服務層實現的。MySQL的存儲引擎是針對表的，不是針對庫的。也就是說在一個數據庫中可以使用不同的存儲引擎。但是不建議這樣做。 2.3 InnoDB存儲引擎

　　MySQL5.5及之後版本默認的存儲引擎：innodb。

　　2.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 ：是服務器層面産生的文件，類似服務器層的數據字典，記錄表結構。

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

　　1.1 系統表空間無法簡單的收縮文件大小，造成空間浪費，并會産生大量的磁盤碎片。

　　1.2 獨立表空間可以通過optimeze table 收縮系統文件，不需要重啟服務器也不會影響對表的正常訪問。

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

　　2.2 獨立表空間可以同時向多個文件刷新數據。

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

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

　　使用mysqldump 導出所有數據庫數據（存儲過程、觸發器、計劃任務一起都要導出 ）可以在從服務器上操作。停止MYsql 服務器，修改參數（my.cnf加入innodb_file_per_table），并删除Inoodb相關文件（可以重建Data目錄）。重啟MYSQL，并重建Innodb系統表空間。重新導入數據。 或者 Alter table 同樣可以的轉移，但是無法回收系統表空間中占用的空間。

　　2.4 InnoDB存儲引擎的特性

　　2.4.1 特性一：事務性存儲引擎及兩個特殊日志類型：Redo Log 和 Undo Log

　　Innodb 是一種事務性存儲引擎。完全支持事務的ACID特性。支持事務所需要的兩個特殊日志類型：Redo Log 和Undo Log Redo Log：實現事務的持久性(已提交的事務)。

　　Undo Log：未提交的事務，獨立于表空間，需要随機訪問，可以存儲在高性能io設備上。

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

　　2.4.2 特性二：支持行級鎖

　　InnoDB支持行級鎖。行級鎖可以最大程度地支持并發。行級鎖是由存儲引擎層實現的。 2.5 什麼是鎖

　　2.5.1 鎖

　　2.5.2 鎖類型

　　2.5.3 鎖的粒度

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

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

　　鎖的開銷越大，粒度越小，并發度越高。表級鎖通常是在服務器層實現的。行級鎖是存儲引擎層實現的。innodb的鎖機制，服務器層是不知道的 2.5.4 阻塞和死鎖

　　（1）阻塞是由于資源不足引起的排隊等待現象。

　　（2）死鎖是由于兩個對象在擁有一份資源的情況下申請另一份資源，而另一份資源恰好又是這兩對象正持有的，導緻兩對象無法完成操作，且所持資源無法釋放。

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

　　參考條件：

　　事務備份(Innobd免費在線備份)崩潰恢複存儲引擎的特有特性 總結: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 engine='myisam'

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

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

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

　　2.8 性能優化順序

　　從上到下：

　　文章來源于：segmentFault

　　作者：唐成勇

　　,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!