什麼影響了資料庫查詢速度?
- 2019 年 10 月 29 日
- 筆記
來源：http://t.cn/RnU0h2o
- 1 影響資料庫查詢速度的四個因素
- 2 風險分析
- 3 網卡流量：如何避免無法連接資料庫的情況
- 4 大錶帶來的問題（重要）
- 5 大事務帶來的問題（重要）
1 影響資料庫查詢速度的四個因素
2 風險分析
QPS：
Queries Per Second意思是「每秒查詢率」，是一台伺服器每秒能夠相應的查詢次數，是對一個特定的查詢伺服器在規定時間內所處理流量多少的衡量標準。 TPS：是TransactionsPerSecond的縮寫，也就是事務數/秒。它是軟體測試結果的測量單位。客戶機在發送請求時開始計時，收到伺服器響應後結束計時，以此來計算使用的時間和完成的事務個數。
Tips：最好不要在主庫上資料庫備份，大型活動前取消這樣的計劃。
- 效率低下的
sql：超高的QPS與TPS。 - 大量的並發：數據連接數被佔滿（
max_connection默認100，一般把連接數設置得大一些）。 並發量:同一時刻資料庫伺服器處理的請求數量 - 超高的
CPU使用率：CPU資源耗盡出現宕機。 - 磁碟
IO：磁碟IO性能突然下降、大量消耗磁碟性能的計劃任務。解決：更快磁碟設備、調整計劃任務、做好磁碟維護。
3 網卡流量：如何避免無法連接資料庫的情況
- 減少從伺服器的數量（從伺服器會從主伺服器複製日誌）
- 進行分級快取（避免前端大量快取失效）
- 避免使用
select *進行查詢 - 分離業務網路和伺服器網路
4 大錶帶來的問題（重要）
4.1 大表的特點
- 記錄行數巨大，單表超千萬
- 表數據文件巨大，超過
10個G
4.2 大表的危害
1.慢查詢：很難在短時間內過濾出需要的數據 查詢字區分度低 -> 要在大數據量的表中篩選出來其中一部分數據會產生大量的磁碟io -> 降低磁碟效率
2.對DDL影響：
建立索引需要很長時間：
MySQL -v<5.5建立索引會鎖表MySQL -v>=5.5建立索引會造成主從延遲（mysql建立索引，先在組上執行，再在庫上執行）
修改表結構需要長時間的鎖表：會造成長時間的主從延遲('480秒延遲')
4.3 如何處理資料庫上的大表
分庫分表把一張大表分成多個小表
難點：
- 分表主鍵的選擇
- 分表後跨分區數據的查詢和統計
5 大事務帶來的問題（重要）
5.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機制保證事務更新的一致性和持久性
5.3 大事務
運行時間長，操作數據比較多的事務；
風險：鎖定數據太多，回滾時間長，執行時間長。
- 鎖定太多數據，造成大量阻塞和鎖超時；
- 回滾時所需時間比較長，且數據仍然會處於鎖定；
- 如果執行時間長，將造成主從延遲，因為只有當主伺服器全部執行完寫入日誌時，從伺服器才會開始進行同步，造成延遲。
解決思路：
- 避免一次處理太多數據，可以分批次處理；
- 移出不必要的
SELECT操作，保證事務中只有必要的寫操作。
