一、什么影響了數(shù)據(jù)庫查詢速度

1.1 影響數(shù)據(jù)庫查詢速度的四個因素

image

image.gif

?

1.2 風險分析

QPS：Queries Per Second意思是“每秒查詢率”，是一臺服務器每秒能夠相應的查詢次數(shù)，是對一個特定的查詢服務器在規(guī)定時間內(nèi)所處理流量多少的衡量標準。 TPS：是TransactionsPerSecond的縮寫，也就是事務數(shù)/秒。它是軟件測試結(jié)果的測量單位?？蛻魴C在發(fā)送請求時開始計時，收到服務器響應后結(jié)束計時，以此來計算使用的時間和完成的事務個數(shù)。

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

1. 效率低下的sql：超高的QPS與TPS。

2. 大量的并發(fā)：數(shù)據(jù)連接數(shù)被占滿（max_connection默認100，一般把連接數(shù)設(shè)置得大一些）。 并發(fā)量:同一時刻數(shù)據(jù)庫服務器處理的請求數(shù)量

3. 超高的CPU使用率：CPU資源耗盡出現(xiàn)宕機。

4. 磁盤IO：磁盤IO性能突然下降、大量消耗磁盤性能的計劃任務。解決：更快磁盤設(shè)備、調(diào)整計劃任務、做好磁盤維護。

10年架構(gòu)師領(lǐng)你架構(gòu)-成長之路-（附面試題（含答案））

（騰訊T3-T4）打造互聯(lián)網(wǎng)PHP架構(gòu)師教程目錄大全，只要你看完，薪資立馬提升2倍（持續(xù)更新）

點擊與我交流企鵝群.

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

1. 減少從服務器的數(shù)量（從服務器會從主服務器復制日志）

2. 進行分級緩存（避免前端大量緩存失效）

3. 避免使用select * 進行查詢

4. 分離業(yè)務網(wǎng)絡(luò)和服務器網(wǎng)絡(luò)

1.4 大表帶來的問題（重要）

1.4.1 大表的特點

1. 記錄行數(shù)巨大，單表超千萬

2. 表數(shù)據(jù)文件巨大，超過10個G

1.4.2 大表的危害

1.慢查詢：很難在短時間內(nèi)過濾出需要的數(shù)據(jù) 查詢字區(qū)分度低 -> 要在大數(shù)據(jù)量的表中篩選出來其中一部分數(shù)據(jù)會產(chǎn)生大量的磁盤io -> 降低磁盤效率

2.對DDL影響：

建立索引需要很長時間：

• MySQL -v<5.5 建立索引會鎖表

• MySQL -v>=5.5 建立索引會造成主從延遲（mysql建立索引，先在組上執(zhí)行，再在庫上執(zhí)行）

修改表結(jié)構(gòu)需要長時間的鎖表：會造成長時間的主從延遲('480秒延遲')

1.4.3 如何處理數(shù)據(jù)庫上的大表

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

難點：

1. 分表主鍵的選擇

2. 分表后跨分區(qū)數(shù)據(jù)的查詢和統(tǒng)計

1.5 大事務帶來的問題（重要）

1.5.1 什么是事務

image

image.gif

?

1.5.2事務的ACID屬性

1、原子性（atomicity)：全部成功，全部回滾失敗。銀行存取款。 2、一致性（consistent)：銀行轉(zhuǎn)賬的總金額不變。 3、隔離性（isolation)：

隔離性等級：

• 未提交讀(READ UNCOMMITED) 臟讀,兩個事務之間互相可見；-- 存在臟讀、不可重復讀、幻讀的問題

• 已提交讀(READ COMMITED)符合隔離性的基本概念,一個事務進行時，其它已提交的事物對于該事務是可見的，即可以獲取其它事務提交的數(shù)據(jù)（不可重復讀）。-- 解決臟讀的問題，存在不可重復讀、幻讀的問題。

• 可重復讀(REPEATABLE READ) InnoDB的默認隔離等級。事務進行時，其它所有事務對其不可見，即多次執(zhí)行讀，得到的結(jié)果是一樣的！ -- mysql 默認級別，解決臟讀、不可重復讀的問題，存在幻讀的問題。使用 MVCC（多版本并發(fā)控制，提高并發(fā)，不加鎖）機制 實現(xiàn)可重復讀。But，MySQL在可重復讀級別已經(jīng)解決幻讀，插不進數(shù)據(jù)，有間隙鎖。

• 可串行化（SERIALIZABLE） 在讀取的每一行數(shù)據(jù)上都加鎖，會造成大量的鎖超時和鎖征用，嚴格數(shù)據(jù)一致性且沒有并發(fā)是可使用。 -- 解決臟讀、不可重復讀、幻讀，可保證事務安全，但完全串行執(zhí)行，性能最低。

不可重復讀：事務A首先讀取了一條數(shù)據(jù)，然后執(zhí)行邏輯的時候，事務B將這條數(shù)據(jù)改變了，然后事務A再次讀取的時候，發(fā)現(xiàn)數(shù)據(jù)不匹配了，就是所謂的不可重復讀了。 也就是說，當前事務先進行了一次數(shù)據(jù)讀取，然后再次讀取到的數(shù)據(jù)是別的事務修改成功的數(shù)據(jù)，導致兩次讀取到的數(shù)據(jù)不匹配，也就照應了不可重復讀的語義。 幻讀：事務A首先根據(jù)條件索引得到N條數(shù)據(jù)，然后事務B改變了這N條數(shù)據(jù)之外的M條或者增添了M條符合事務A搜索條件的數(shù)據(jù)，導致事務A再次搜索發(fā)現(xiàn)有N+M條數(shù)據(jù)了，就產(chǎn)生了幻讀。 也就是說，當前事務讀第一次取到的數(shù)據(jù)比后來讀取到數(shù)據(jù)條目少。 不可重復讀和幻讀比較： 兩者有些相似，但是前者針對的是update或delete，后者針對的insert。

查看系統(tǒng)的事務隔離級別：show variables like '%iso%'; 開啟一個新事務：begin; 提交一個事務：commit; 修改事物的隔離級別：set session tx_isolation='read-committed';

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

image

image.gif

?

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

1.5.3 大事務

運行時間長，操作數(shù)據(jù)比較多的事務；

風險：鎖定數(shù)據(jù)太多，回滾時間長，執(zhí)行時間長。

1. 鎖定太多數(shù)據(jù)，造成大量阻塞和鎖超時；

2. 回滾時所需時間比較長，且數(shù)據(jù)仍然會處于鎖定；

3. 如果執(zhí)行時間長，將造成主從延遲，因為只有當主服務器全部執(zhí)行完寫入日志時，從服務器才會開始進行同步，造成延遲。

解決思路：

1. 避免一次處理太多數(shù)據(jù)，可以分批次處理；

2. 移出不必要的SELECT操作，保證事務中只有必要的寫操作。

感謝大家一直來支持，這是我準備的1000粉絲福利

【1000粉絲福利】10年架構(gòu)師分享PHP進階架構(gòu)資料，助力大家都能30K

點擊與我交流企鵝群.

二、什么影響了MySQL性能（非常重要）

2.1 影響性能的幾個方面

1. 服務器硬件。

2. 服務器系統(tǒng)（系統(tǒng)參數(shù)優(yōu)化）。

3. 存儲引擎。 MyISAM： 不支持事務，表級鎖。 InnoDB: 支持事務，支持行級鎖，事務ACID。

4. 數(shù)據(jù)庫參數(shù)配置。

5. 數(shù)據(jù)庫結(jié)構(gòu)設(shè)計和SQL語句。（重點優(yōu)化）

2.2 MySQL體系結(jié)構(gòu)

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

image

image.gif

?

1. MySQL是插件式的存儲引擎，其中存儲引擎分很多種。只要實現(xiàn)符合mysql存儲引擎的接口，可以開發(fā)自己的存儲引擎!

2. 所有跨存儲引擎的功能都是在服務層實現(xiàn)的。

3. MySQL的存儲引擎是針對表的，不是針對庫的。也就是說在一個數(shù)據(jù)庫中可以使用不同的存儲引擎。但是不建議這樣做。

2.3 InnoDB存儲引擎

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

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

show variables like 'innodb_file_per_table

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

如果innodb_file_per_table 為 OFF 將數(shù)據(jù)存儲到系統(tǒng)的共享表空間，文件為ibdataX（X為從1開始的整數(shù)）；

.frm ：是服務器層面產(chǎn)生的文件，類似服務器層的數(shù)據(jù)字典，記錄表結(jié)構(gòu)。

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

1.1 系統(tǒng)表空間無法簡單的收縮文件大小，造成空間浪費，并會產(chǎn)生大量的磁盤碎片。

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

2.1 如果對多個表進行刷新時，實際上是順序進行的，會產(chǎn)生IO瓶頸。

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

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

2.3.3 系統(tǒng)表空間的表轉(zhuǎn)移到獨立表空間中的方法

1、使用mysqldump 導出所有數(shù)據(jù)庫數(shù)據(jù)（存儲過程、觸發(fā)器、計劃任務一起都要導出 ）可以在從服務器上操作。

2、停止MYsql 服務器，修改參數(shù)（my.cnf加入innodb_file_per_table），并刪除Inoodb相關(guān)文件（可以重建Data目錄）。

3、重啟MYSQL，并重建Innodb系統(tǒng)表空間。

4、 重新導入數(shù)據(jù)。

或者 Alter table 同樣可以的轉(zhuǎn)移，但是無法回收系統(tǒng)表空間中占用的空間。

大廠2000道面試題（含答案）

PHP面試題匯總，看完這些面試題助力你面試成功，工資必有20-25K

點擊與我交流企鵝群.

2.4 InnoDB存儲引擎的特性

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

1. Innodb 是一種事務性存儲引擎。

2. 完全支持事務的ACID特性。

3. 支持事務所需要的兩個特殊日志類型：Redo Log 和Undo Log

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

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

2.4.2 特性二：支持行級鎖

1. InnoDB支持行級鎖。

2. 行級鎖可以最大程度地支持并發(fā)。

3. 行級鎖是由存儲引擎層實現(xiàn)的。

2.5 什么是鎖

2.5.1 鎖

image

image.gif

?

2.5.2 鎖類型

image

image.gif

?

S鎖（讀鎖）： select ... lock in share mode X鎖（寫鎖）：select ... for update (update、delete、)

2.5.3 鎖的粒度

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

image

image.gif

?

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

1. 鎖的開銷越大，粒度越小，并發(fā)度越高。

2. 表級鎖通常是在服務器層實現(xiàn)的。

3. 行級鎖是存儲引擎層實現(xiàn)的。innodb的鎖機制，服務器層是不知道的

2.5.4 鎖的分類

（1）悲觀鎖

總是假設(shè)最壞的情況，每次拿數(shù)據(jù)都認為別人會修改數(shù)據(jù)，所以要加鎖，別人只能等待，直到我釋放鎖才能拿到鎖；數(shù)據(jù)庫的行鎖、表鎖、讀鎖、寫鎖都是這種方式，java中的synchronized和ReentrantLock也是悲觀鎖的思想。

（2）樂觀鎖

總是假設(shè)最好的情況，每次拿數(shù)據(jù)都認為別人不會修改數(shù)據(jù)，所以不會加鎖，但是更新的時候，會判斷在此期間有沒有人修改過；一般基于版本號機制實現(xiàn)。

（3）使用場景

樂觀鎖適用于讀多寫少的情況，即沖突很少發(fā)生；如果是多寫的情況，應用會不斷重試，反而會降低系統(tǒng)性能，這種情況最好用悲觀鎖，因為等待到鎖被釋放后，可以立即獲得鎖進行操作。

拓展： (1)圖解悲觀鎖和樂觀鎖 (2)什么是樂觀鎖與悲觀鎖？

2.5.4 阻塞和死鎖

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

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

參考條件：

1. 事務

2. 備份(Innobd免費在線備份)

3. 崩潰恢復

4. 存儲引擎的特有特性

總結(jié):Innodb大法好。 注意:盡量別使用混合存儲引擎，比如回滾會出問題在線熱備問題。

2.7 配置參數(shù)

2.7.1 內(nèi)存配置相關(guān)參數(shù)

確定可以使用的內(nèi)存上限。

<pre>內(nèi)存的使用上限不能超過物理內(nèi)存，否則容易造成內(nèi)存溢出；（對于32位操作系統(tǒng)，MySQL只能試用3G以下的內(nèi)存。）</pre>

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

<pre>sort_buffer_size #定義了每個線程排序緩存區(qū)的大小，MySQL在有查詢、需要做排序操作時才會為每個緩沖區(qū)分配內(nèi)存（直接分配該參數(shù)的全部內(nèi)存）； join_buffer_size #定義了每個線程所使用的連接緩沖區(qū)的大小，如果一個查詢關(guān)聯(lián)了多張表，MySQL會為每張表分配一個連接緩沖，導致一個查詢產(chǎn)生了多個連接緩沖； read_buffer_size #定義了當對一張MyISAM進行全表掃描時所分配讀緩沖池大小，MySQL有查詢需要時會為其分配內(nèi)存，其必須是4k的倍數(shù)； read_rnd_buffer_size #索引緩沖區(qū)大小，MySQL有查詢需要時會為其分配內(nèi)存，只會分配需要的大小。</pre>

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

MySQL數(shù)據(jù)庫實例：?、費ySQL是單進程多線程（而oracle是多進程），也就是說MySQL實例在系統(tǒng)上表現(xiàn)就是一個服務進程，即進程； ?、贛ySQL實例是線程和內(nèi)存組成，實例才是真正用于操作數(shù)據(jù)庫文件的； 一般情況下一個實例操作一個或多個數(shù)據(jù)庫；集群情況下多個實例操作一個或多個數(shù)據(jù)庫。

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

<pre>總內(nèi)存-（每個線程所需要的內(nèi)存*連接數(shù)）-系統(tǒng)保留內(nèi)存</pre>

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

<pre>select sum(index_length) from information_schema.talbes where engine='myisam'</pre>

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

max_connections 控制允許的最大連接數(shù)， 一般2000更大。 不要使用外鍵約束保證數(shù)據(jù)的完整性。

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

從上到下：

image

image.gif

?

喜歡我的文章就關(guān)注我吧，持續(xù)更新中.....

以上內(nèi)容希望幫助到大家，很多PHPer在進階的時候總會遇到一些問題和瓶頸，業(yè)務代碼寫多了沒有方向感，不知道該從那里入手去提升，對此我整理了一些資料，包括但不限于：分布式架構(gòu)、高可擴展、高性能、高并發(fā)、服務器性能調(diào)優(yōu)、TP6，laravel，YII2，Redis，Swoole、Swoft、Kafka、Mysql優(yōu)化、shell腳本、Docker、微服務、Nginx等多個知識點高級進階干貨需要的可以免費分享給大家，需要的可以點擊進入暗號：知乎。

推薦下一篇：

MySQL性能管理及架構(gòu)設(shè)計（二）：數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化、高可用架構(gòu)設(shè)計、數(shù)據(jù)庫索引優(yōu)化