MySQL 查詢(xún)速度慢與性能差怎么辦

小編給大家分享一下 查詢(xún)速度慢與性能差怎么辦，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

一、什么影響了數據庫查詢(xún)速度

1.1 影響數據庫查詢(xún)速度的四個(gè)因素

1.2 風(fēng)險分析

QPS： QueriesPerSecond意思是“每秒查詢(xún)率”，是一臺每秒能夠相應的查詢(xún)次數，是對一個(gè)特定的查詢(xún)服務(wù)器在規定時(shí)間內所處理流量多少的衡量標準。

TPS： 是 TransactionsPerSecond的縮寫(xiě)，也就是事務(wù)數/秒。它是軟件測試結果的測量單位?？蛻?hù)機在發(fā)送請求時(shí)開(kāi)始計時(shí)，收到服務(wù)器響應后結束計時(shí)，以此來(lái)計算使用的時(shí)間和完成的事務(wù)個(gè)數。

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

效率低下的 sql：超高的 QPS與 TPS。大量的并發(fā)：數據連接數被占滿(mǎn)（ max_connection默認 100，一般把連接數設置得大一些）。 并發(fā)量:同一時(shí)刻數據庫服務(wù)器處理的請求數量超高的 CPU使用率： CPU資源耗盡出現宕機。磁盤(pán) IO：磁盤(pán) IO性能突然下降、大量消耗磁盤(pán)性能的計劃任務(wù)。解決：更快磁盤(pán)設備、調整計劃任務(wù)、做好磁盤(pán)維護。

1.3 網(wǎng)卡流量：如何避免無(wú)法連接數據庫的情況

減少從服務(wù)器的數量（從服務(wù)器會(huì )從主服務(wù)器復制日志）進(jìn)行分級緩存（避免前端大量緩存失效）避免使用 select* 進(jìn)行查詢(xún)分離業(yè)務(wù)網(wǎng)絡(luò )和服務(wù)器網(wǎng)絡(luò )

1.4 大表帶來(lái)的問(wèn)題（ 重要）

1.4.1 大表的特點(diǎn)

記錄行數巨大，單表超千萬(wàn)表數據文件巨大，超過(guò) 10個(gè) G

1.4.2 大表的危害

1.慢查詢(xún)：很難在短時(shí)間內過(guò)濾出需要的數據 查詢(xún)字區分度低 -> 要在大數據量的表中篩選出來(lái)其中一部分數據會(huì )產(chǎn)生大量的磁盤(pán) io -> 降低磁盤(pán)效率

2.對 DDL影響：

建立索引需要很長(cháng)時(shí)間：

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

修改表結構需要長(cháng)時(shí)間的鎖表：會(huì )造成長(cháng)時(shí)間的主從延遲('480秒延遲')

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

分庫分表把一張大表分成多個(gè)小表

難點(diǎn)：

分表主鍵的選擇分表后跨分區數據的查詢(xún)和統計

1.5 大事務(wù)帶來(lái)的問(wèn)題（ 重要*）*

1.5.1 什么是事務(wù)

1.5.2事務(wù)的 ACID屬性

1、原子性（ atomicity)：全部成功，全部回滾失敗。銀行存取款。

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

3、隔離性（isolation)：

隔離性等級：

未提交讀( READ UNCOMMITED) 臟讀,兩個(gè)事務(wù)之間互相可見(jiàn)；已提交讀( READ COMMITED)符合隔離性的基本概念,一個(gè)事務(wù)進(jìn)行時(shí)，其它已提交的事物對于該事務(wù)是可見(jiàn)的，即可以獲取其它事務(wù)提交的數據?？芍貜妥x( REPEATABLE READ) InnoDB的默認隔離等級。事務(wù)進(jìn)行時(shí)，其它所有事務(wù)對其不可見(jiàn)，即多次執行讀，得到的結果是一樣的！可串行化（ SERIALIZABLE） 在讀取的每一行數據上都加鎖，會(huì )造成大量的鎖超時(shí)和鎖征用，嚴格數據一致性且沒(méi)有并發(fā)是可使用。

查看系統的事務(wù)隔離級別： show variables like'%iso%';

開(kāi)啟一個(gè)新事務(wù)： begin;

提交一個(gè)事務(wù)： commit;

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

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

redolog機制保證事務(wù)更新的一致性和持久性

1.5.3 大事務(wù)

運行時(shí)間長(cháng)，操作數據比較多的事務(wù)；

風(fēng)險：鎖定數據太多，回滾時(shí)間長(cháng)，執行時(shí)間長(cháng)。

鎖定太多數據，造成大量阻塞和鎖超時(shí)；回滾時(shí)所需時(shí)間比較長(cháng)，且數據仍然會(huì )處于鎖定；如果執行時(shí)間長(cháng)，將造成主從延遲，因為只有當主服務(wù)器全部執行完寫(xiě)入日志時(shí)，從服務(wù)器才會(huì )開(kāi)始進(jìn)行同步，造成延遲。

解決思路：

避免一次處理太多數據，可以分批次處理；移出不必要的 SELECT操作，保證事務(wù)中只有必要的寫(xiě)操作。二、什么影響了MySQL性能（ 非常重要)

2.1 影響性能的幾個(gè)方面

服務(wù)器硬件。服務(wù)器系統（系統參數優(yōu)化）。存儲引擎。 MyISAM： 不支持事務(wù)，表級鎖。 InnoDB: 支持事務(wù)，支持行級鎖，事務(wù) ACID。數據庫參數配置。數據庫結構設計和SQL語(yǔ)句。（重點(diǎn)優(yōu)化）

2.2 MySQL體系結構

分三層：客戶(hù)端->服務(wù)層->存儲引擎

MySQL是 插件式的存儲引擎，其中存儲引擎分很多種。只要實(shí)現符合mysql存儲引擎的接口，可以開(kāi)發(fā)自己的存儲引擎!所有跨存儲引擎的功能都是在服務(wù)層實(shí)現的。MySQL的存儲引擎是針對表的，不是針對庫的。也就是說(shuō)在一個(gè)數據庫中可以使用不同的存儲引擎。但是不建議這樣做。

2.3 InnoDB存儲引擎

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

2.3.1 InnoDB使用表空間進(jìn)行數據存儲。

show variables like'innodb_file_per_table

如果innodbfileper_table 為 ON 將建立獨立的表空間，文件為tablename.ibd；

如果innodbfileper_table 為 OFF 將數據存儲到系統的共享表空間，文件為ibdataX（X為從1開(kāi)始的整數）；

.frm ：是服務(wù)器層面產(chǎn)生的文件，類(lèi)似服務(wù)器層的數據字典，記錄表結構。

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

1.1 系統表空間無(wú)法簡(jiǎn)單的收縮文件大小，造成空間浪費，并會(huì )產(chǎn)生大量的磁盤(pán)碎片。1.2 獨立表空間可以通過(guò) optimeze table 收縮系統文件，不需要重啟服務(wù)器也不會(huì )影響對表的正常訪(fǎng)問(wèn)。2.1 如果對多個(gè)表進(jìn)行刷新時(shí)，實(shí)際上是順序進(jìn)行的，會(huì )產(chǎn)生IO瓶頸。2.2 獨立表空間可以同時(shí)向多個(gè)文件刷新數據。

強烈建立對Innodb 使用獨立表空間，優(yōu)化什么的更方便，可控。

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

1、使用mysqldump 導出所有數據庫數據（存儲過(guò)程、觸發(fā)器、計劃任務(wù)一起都要導出 ）可以在從服務(wù)器上操作。2、停止MYsql 服務(wù)器，修改參數（my.cnf加入innodbfileper_table），并刪除Inoodb相關(guān)文件（可以重建Data目錄）。3、重啟MYSQL，并重建Innodb系統表空間。4、 重新導入數據。

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

2.4 InnoDB存儲引擎的特性

2.4.1 特性一：事務(wù)性存儲引擎及兩個(gè)特殊日志類(lèi)型：Redo Log 和 Undo Log

Innodb 是一種事務(wù)性存儲引擎。完全支持事務(wù)的 ACID特性。支持事務(wù)所需要的兩個(gè)特殊日志類(lèi)型： RedoLog 和 UndoLog

Redo Log： 實(shí)現事務(wù)的持久性(已提交的事務(wù))。 Undo Log： 未提交的事務(wù)，獨立于表空間，需要隨機訪(fǎng)問(wèn)，可以存儲在高性能io設備上。

Undo日志記錄某數據被修改前的值，可以用來(lái)在事務(wù)失敗時(shí)進(jìn)行 rollback； Redo日志記錄某數據塊被修改后的值，可以用來(lái)恢復未寫(xiě)入 data file的已成功事務(wù)更新的數據。

2.4.2 特性二：支持行級鎖

InnoDB支持行級鎖。行級鎖可以最大程度地支持并發(fā)。行級鎖是由存儲引擎層實(shí)現的。

2.5 什么是鎖

2.5.1 鎖

2.5.2 鎖類(lèi)型

2.5.3 鎖的粒度

MySQL的事務(wù)支持不是綁定在MySQL服務(wù)器本身， 而是與存儲引擎相關(guān)

將table_name加表級鎖命令： locktable table_name write; 寫(xiě)鎖會(huì )阻塞其它用戶(hù)對該表的‘讀寫(xiě)'操作，直到寫(xiě)鎖被釋放： unlock tables；

鎖的開(kāi)銷(xiāo)越大，粒度越小，并發(fā)度越高。表級鎖通常是在服務(wù)器層實(shí)現的。行級鎖是存儲引擎層實(shí)現的。innodb的鎖機制，服務(wù)器層是不知道的

2.5.4 阻塞和死鎖

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

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

參考條件：

事務(wù)備份( Innobd免費在線(xiàn)備份)崩潰恢復存儲引擎的特有特性

總結: Innodb 大法好。

注意: 盡量別使用混合存儲引擎，比如回滾會(huì )出問(wèn)題在線(xiàn)熱備問(wèn)題。

2.7 配置參數

2.7.1 內存配置相關(guān)參數

確定可以使用的內存上限。

內存的使用上限不能超過(guò)物理內存，否則容易造成內存溢出；（對于32位操作系統，MySQL只能試用3G以下的內存。）

確定MySQL的 每個(gè)連接 單獨 使用的內存。

sort_buffer_size #定義了每個(gè)線(xiàn)程排序緩存區的大小，MySQL在有查詢(xún)、需要做排序操作時(shí)才會(huì )為每個(gè)緩沖區分配內存（直接分配該參數的全部?jì)却妫﹋oin_buffer_size #定義了每個(gè)線(xiàn)程所使用的連接緩沖區的大小，如果一個(gè)查詢(xún)關(guān)聯(lián)了多張表，MySQL會(huì )為每張表分配一個(gè)連接緩沖，導致一個(gè)查詢(xún)產(chǎn)生了多個(gè)連接緩沖read_buffer_size #定義了當對一張MyISAM進(jìn)行全表掃描時(shí)所分配讀緩沖池大小，MySQL有查詢(xún)需要時(shí)會(huì )為其分配內存，其必須是4k的倍數；read_rnd_buffer_size #索引緩沖區大小，MySQL有查詢(xún)需要時(shí)會(huì )為其分配內存，只會(huì )分配需要的大小。

注意： 以上四個(gè)參數是為一個(gè)線(xiàn)程分配的，如果有100個(gè)連接，那么需要×100。

MySQL數據庫實(shí)例：

①MySQL是 單進(jìn)程多線(xiàn)程（而oracle是多進(jìn)程），也就是說(shuō) MySQL實(shí)例在系統上表現就是一個(gè)服務(wù)進(jìn)程，即進(jìn)程；

②MySQL實(shí)例是線(xiàn)程和內存組成，實(shí)例才是真正用于操作數據庫文件的；

一般情況下一個(gè)實(shí)例操作一個(gè)或多個(gè)數據庫；集群情況下多個(gè)實(shí)例操作一個(gè)或多個(gè)數據庫。

如何為緩存池分配內存：

Innodb_buffer_pool_size，定義了Innodb所使用緩存池的大小，對其性能十分重要，必須足夠大，但是過(guò)大時(shí)，使得Innodb 關(guān)閉時(shí)候需要更多時(shí)間把臟頁(yè)從緩沖池中刷新到磁盤(pán)中；

總內存-（每個(gè)線(xiàn)程所需要的內存*連接數）-系統保留內存

key_buffer_size，定義了MyISAM所使用的緩存池的大小，由于數據是依賴(lài)存儲操作系統緩存的，所以要為操作系統預留更大的內存空間；

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

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

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

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

2.8 性能優(yōu)化順序

從上到下：