第二十二章 MySQL有哪些“飲鸩止渴”提高性能的方法？

短連接配接風暴

在想要短期提高資料庫性能的情況下，為什麼說可以對短連接配接進行一些處理 ?

因為連接配接過程中，​

​權限收集​

​​和​

​校驗​

​是比較耗性能的

在高頻短連接配接通路資料庫的情況下，想要短期業務無損地提高伺服器性能，怎麼做 ?

• 将​

  ​max_connections​

  ​ 設定為合理的參數，因為超過這個連接配接數的連接配接會被拒絕，但過大的話，連接配接了的線程又拿不到 CPU 資源去執行
• 其次，即使是空閑的線程隻要保持連接配接也會占用一個​

  ​connection​

  ​ 位置，是以 kill 掉這些空閑的連接配接也是一個思路
• 此外，啟用 –skip-grant-tables 參數，可以讓整個 MySQL 跳過所有的權限驗證階段，這樣也可以減少連接配接過程的消耗

• 但是，極力不推薦在​

  ​外網​

  ​環境下這麼做

你說 kill 掉空閑的線程，那我直接 ​

​kill​

​​ 掉 ​

​show processlist​

​​ 中的 ​

​sleep​

​ 的線程可以嗎 ? 為什麼 ?

• 不可以
• 因為這樣子可能是有損的，例如一個執行了多種操作但還沒送出的事務，此時會復原

應該怎麼幹掉一些空閑的線程 ?

• 先執行​

  ​show processlist​

  ​ 檢視哪些線程處于 sleep 中
• 再看​

  ​information_schema​

  ​​ 的​

  ​innodb_trx​

  ​​，根據裡面的​

  ​trx_mysql_thread_id​

  ​ 判斷哪些 sleep 的線程處于事務當中，幹掉事務外的 sleep 線程

kill      

從服務端幹掉線程，需要注意什麼 ?

• 用戶端下一次請求時才會收到​

  ​connection​

  ​ 斷開的報錯，有的用戶端會重新發起連接配接，而有的用戶端會一直報錯
• 是以若 DBA 要幹掉線程，應該通知到業務研發團隊

你說可以減少連接配接過程的權限驗證，會産生什麼問題呢 ? 具體的操作步驟又是怎麼樣的 ?

• 沒有權限驗證，更容易被攻擊，尤其是暴露在外網的資料庫
• 跳過權限驗證的方法：

• 重新開機資料庫，并使用​

  ​–skip-grant-tables​

  ​ 參數啟動
• 這樣，整個 MySQL 會跳過所有的權限驗證階段，包括連接配接過程和語句執行過程在内

• 注意：8.0 版本在使用該參數時，MySQL 會預設把​

  ​-skip-networking​

  ​ 參數打開，保證這時候資料庫隻能被本地的用戶端連接配接

慢查詢性能問題

在 MySQL 中，哪些情況會引發性能問題的慢查詢 ?

1. 索引沒有設計好
2. SQL 語句沒寫好
3. MySQL 選錯了索引

方式一：緊急建立索引。如果是表資料較少，直接在主庫執行即可，如果表資料量較大，此時應該分三步走

• 在備庫 B 上執行​

  ​set sql_log_bin=off​

  ​​，也就是不寫​

  ​binlog​

  ​，然後執行 alter table 語句加上索引
• 執行主備切換
• 這時候主庫是 B，備庫是 A。在 A 上執行​

  ​set sql_log_bin=off​

  ​​，然後執行​

  ​alter table​

  ​ 語句加上索引

方式二：使用 5.7 提供的 ​

​query_rewrite (查詢重寫)​

​ 功能

• 該插件在 MySQL 的 share 包中自帶，但需要執行安裝才能使用

[root@localhost share]# mysql -uroot -p < install_rewriter.sql      

• ​

  ​query_rewrite​

  ​ 可以把一個語句改寫成另外一個語句

insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");

call query_rewrite.flush_rewrite_rules();      

• 如上，如果執行的語句被​

  ​pattern​

  ​​ 比對了，會被替代為​

  ​replacement​

  ​ 中的語句執行

• 疊代的時候，上線前，測試環境中啟動慢查詢配置，模拟線上操作，觀察日志中的每類語句輸出，留意掃描行數是否與預期一緻
• 如果是新項目，最好還是進行全量回歸測試，使用開源工具​

  ​pt-query-digest​

  ​ 進行測試。

QPS 突增問題

• 可以用​

  ​查詢重寫​

  ​功能，但需要考慮到查詢重寫的模闆“誤殺”和即使重寫，重寫的結果是否會導緻後面的業務邏輯失敗的問題
• 此外，還有虛拟化、白名單機制、業務賬号分離等選項