性能影響

拋開業務復雜度，影響程度依次是硬件配置 > MySQL配置 > 數據表設計 > 索引優化

數據庫優化方向

• 數據庫服務器內核優化
  • 由專業的數據庫開發人員去做
• my.cnf配置，搭配壓力測試進行調試
  • 由運維人員去做
• SQL調優
  • 由業務開發人員去做
• 代碼層面減少對數據庫的訪問
  • 由業務開發人員去做

注：數據庫服務器內核優化普通人一般接觸不到，能夠接觸到的就只有從運維角度去進行的my.cnf配置和從業務開發人員角度的SQL調優與代碼層面減少對數據庫的訪問

---

代碼層面

• 減少同數據庫的交互次數(即減少訪問)
  • 削峰填谷采用的限流、消息隊列等高并發場景下的性能優化
  • 使用緩存(如Redis)優化查詢，經常查到的數據放入緩存，后續獲取直接從緩存中取出
• 由應用程序來處理數據(例：數據格式化)，不推薦使用數據庫(例：數據庫函數)
• 由應用程序保證數據準確性，不推薦使用外鍵約束
• 寫多讀少場景，由應用程序保證唯一性，不推薦使用唯一索引
• 適當冗余字段，嘗試建立中間表，用應用程序計算中間結果，用空間換時間
• 不允許執行極度耗時的事務，配合應用程序拆分成更小的事務
• 預估重要數據表(比如訂單表)的負載和數據增長態勢，提前優化(分庫分表)

SQL調優

• 預編譯語句(即預先完成sql語句檢查、編譯，使用時只傳入參數)

  • 減少SQL編譯所需要的時間，還可以解決動態SQL帶來的SQL注入問題
  • 只傳參數比傳SQL語句更高效
  • 相同語句一次解析，多次使用，提高處理效率

• 永久連接(數據庫連接是一次創建永久有效的)

  • 在一些極端的環境中，Apache會不斷的發出HTTP請求，創建子進程去請求數據庫，數據庫壓力過大

• 選擇正確的數據庫引擎

  • MyISAM對大數據量查詢友好，對DELETE、UPDATE、INSERT等不夠友好
  • InnoDB對DELETE、UPDATE、INSERT等友好，對大數據量查詢不友好
  • 數據庫主從存儲引擎可以不一致

• 擅用EXPLAIN執行計劃

• SQL語句大寫

  • SQL語句在執行的時候,是先轉化為大寫字母然后執行，直接大寫可省去轉化這一步

• 數據類型使用盡可能小的

  • 硬盤上存儲占用越小速度越快，但不是越小越好，如果不能支撐業務，后續保存所需數據都保存不了，則不可取

• 選擇合適的數據類型

• 先執行的SQL語句能檢索出的數據越少越好

  • 先執行的SQL語句檢索出的數據越少，下一次檢索的計算量越小，性能耗費越小
    • 為了提高GROUP BY、JOIN等的效率，可以在執行到該語句前，用WHERE把不需要的記錄過濾掉

• 索引的使用

  • WHERE子句、JOIN子句、ORDER BY、GROUP BY、HAVING子句、DISTINCT等里出現的列需要建索引
  • 索引種類：普通索引、組合索引、唯一索引、組合唯一索引、主鍵索引、全文索引
    • 全文索引由于查詢精度以及擴展性不佳，更多企業選擇Elasticsearch
  • 索引不要建立在有大量重復數據的列上
    • 索引有助于快速訪問到符合條件的數據，該列數據大量重復，則建立索引沒有意義
  • 善用覆蓋索引
    • 即索引已囊括所查數據，無需回表查詢，僅訪問索引即可查到所需數據
      • 回表查詢即根據索引查到聚簇索引即主鍵，又根據聚簇索引查到其他所需要的列數據
  • 善用聯合索引
    • 遵循最左匹配原則，查詢條件順序如果不遵循最左匹配原則會失效
      • 當我們創建了一個聯合索引(k1,k2,k3)時，相當于創建了(k1)、(k1,k2)、(k1,k2,k3)三個索引
    • 查詢條件是多個單列索引時，會對兩個單列索引查到的結果做一個并集的操作，聯合索引的區分度(同時滿足兩個條件的記錄數量更少)與性能是高于多個單列索引的，且隨著數據量的增加，索引不能全部加載到內存，而是要從磁盤去讀，這樣索引的個數越多，讀磁盤的開銷就越大，因此聯合索引的建立是必要的
    • 區分度最高的列放在聯合索引最左處
    • 字段長度小的列放在聯合索引的最左側
      • 字段長度越小，一頁能存儲的數據量越大，IO性能越好
    • 使用最頻繁的列放在聯合索引的最左側
      • 可以較少的建立一些索引
  • 刪除冗余和重復索引
    • 如某一個索引包含在另一個聯合索引的生效范圍內，則可刪除該索引
    • 重復的索引需維護，且優化器查詢時也要逐個考慮
  • 索引不宜太多
    • 索引越多，雖查詢效率越高，但插入、修改、刪除時因維護索引的緣故效率越低
      • 索引是需要存儲的，相當于數據庫記錄的目錄，插入、修改、刪除數據時還需維護該目錄的內容
    • 索引消耗磁盤和CPU，索引越多，消耗越大，造成不必要的浪費
      • 索引需要存儲，消耗磁盤
      • 維護和讀取索引都占用很多資源(包括CPU)
    • 不建議建索引的情況
      • 頻繁增刪改的表不要建索引
      • 頻繁更新的字段不要建索引
  • 每張表都要有主鍵
    • 無論通過什么方式去查詢，最后都會通過主鍵定位到數據(因為MySQL是先走非聚集索引，然后走聚集索引，主鍵即聚集索引)
    • 主鍵對集群、分區非常重要
    • 主鍵使用BIGINT，避免使用VARCHAR
  • 避免在索引列上進行表達式操作，或對索引列使用MySQL的內置函數
    • 對索引進行運算會使索引失效
    • 可以使用索引 = 表達式或內置函數，例：可time = 10 +1，不可time + 1 = 10
  • 避免SQL語句中查詢變量與字段定義類型不匹配
    • 數據類型不匹配，MySQL會做隱式的轉換，函數作用于表字段，不僅浪費性能，如果是索引列，還會導致索引失效
    • 例如：字符串不加單引號，日期使用字符串
  • 列中盡量不要null，應用默認值代替
    • null值更新到非null值無法做到原地更新，容易發生索引分裂影響性能
      • 比如索引存儲中，某一頁剛好放滿數據，其中一條數據某一列本來為空，改成有值的，那一頁就放不下了(格式：主鍵id,索引列)
    • null值沒有索引(有些數據庫引擎有)
  • 避免用!=、<>、not in、is not null、is null等操作符
    • 因為null值沒有索引，其次帶有非的操作符也不太好命中索引，一般情況下，查詢的成本高，優化器會自動放棄索引的
  • 避免用OR
    • 使用OR可能會使索引失效，從而全表掃描
    • OR兩邊一個加了索引，一個沒加，即使第一個條件走了索引，第二個條件還是要全表掃描，也就是全表掃描+索引掃描+合并，如果它一開始就走全表掃描，直接一遍掃描就完事。MySQL自身有優化器，出于成本和效率考慮，遇到OR條件，索引失效合情合理
  • 模糊查詢避免進行左側模糊查詢
    • 字符串(如CHAR、VARCHAR、TEXT、BLOB等)使用的是前綴索引
    • 左側模糊查詢不走索引(索引遵循最左匹配原則)
    • 如果有需要左側模糊查詢的業務，可以存一個字段是該字段的顛倒值，或者考慮全文索引(不推薦)以及Elasticsearch
  • 避免修改clustered索引數據列(聚簇索引，且一個表最多只有一個)
    • 因為clustered索引數據列的順序就是表記錄的物理存儲順序，一旦調整會耗費相當大的資源

• 避免使用SELECT *，盡量使用SELECT 具體字段

  • 查得越多速度越慢
  • 消耗更多的CPU、IO、網絡帶寬資源
  • 無法使用覆蓋索引
  • 減少表結構變更帶來的影響

• 避免嵌套子查詢，盡量用JOIN

  • IN適合主表大子表小，EXIST適合主表小子表大
    • 由于查詢優化器的不斷升級，很多場景，這兩者性能差不多一樣了
    • IN的值不要超過500個

• 多表連接時，使用表的別名，并前綴于列上

  • 減少解析時間，并減少來自于那些由列歧義引起的語法錯誤

• 不要有超過5個以上的表連接

  • 連接表越多，編譯的時間和開銷也就越大，單次查詢涉及數據量也會很大
  • 把連接表拆開成較小的幾個執行，可讀性更高
  • 如果一定要連接很多表才能得到數據，那么意味著糟糕的設計了

• INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN、CROSS JOIN，優先使用INNER JOIN，如果是LEFT JOIN，左邊表結果盡量小，如果是RIGHT JOIN，右邊表結果盡量小，避免使用FULL JOIN、CROSS JOIN

  • 如果內連接是等值連接、不等值連接、自然連接(因為內連接可以沒有ON，此時相當于交叉連接，要避免)，或許返回的行數比較少，所以性能相對會好一點
  • 使用了左連接，左邊表數據結果盡量小，條件盡量放到左邊處理，意味著返回的行數可能比較少，同理，右連接則右邊表數據結果盡量小，條件盡量放到右邊處理
  • 全連接即左連接加右連接，交叉連接是兩表的笛卡爾積

• 連接表時盡量保持兩個字段一致

• 清空表數據用TRUNCATE，而不用DELETE

  • TRUNCATE會重置auto_increment的值，釋放磁盤空間；不走事務，不鎖表；不產生大量日志寫入日志文件
  • DELETE不重置auto_increment的值，但插入的數據會覆蓋在之前刪除的數據上；走事務，會鎖表，清空數據量過大會導致長時間內業務無法使用；會產生大量日志寫入日志文件
  • 不過使用TRUNCATE會有無法回滾，數據無法恢復的風險，但是它快呀，慎用

• INSERT、UPDATE數據過多，考慮批量INSERT、UPDATE

  • 批量INSERT、UPDATE性能好，更加省時間

• DELETE、UPDATE、SELECT后加LIMIT

  • 明確確定有幾條數據要DELETE、UPDATE、SELECT時，加LIMIT 數量
    • 命中后可避免繼續全表掃描，若表中數據量過大時避免因鎖表時間長將CPU打滿，以致用到相關表的業務系統長時間內無法使用
  • DELETE、UPDATE的數據量過大時進行拆分處理，不要一次LIMIT太多
    • 原因同第一條
    • 誤DELETE、UPDATE代價小
    • DELETE、UPDATE走事務，大事務會導致主從延遲
    • 拆分方案
      • 循環刪除、更新
      • 人為并發(即多線程)刪除更新(會導致死鎖且無法回滾)
  • SELECT用LIMIT進行分頁
    • 原因同第一條
    • 查詢數據越多，速度越慢，用戶等待時間長
  • SELECT 1 FROM xxx WHERE xxx = ? LIMIT 1 判斷是否存在
    • 原因同第一條
  • 僅針對第一條，可不加LIMIT的前提是搜索條件加了索引，MySQL中加鎖都是基于索引的，如果是以某個加了索引的字段為條件DELETE、UPDATE、SELECT，如果該字段沒索引就會掃描到主鍵索引上，那么就算符合搜索條件的記錄只有一條，也會鎖表(加了索引后，除了被鎖的記錄，其他記錄仍可操作)

• 優化LIMIT分頁

  • 例如：LIMIT 10000,10，雖然只取10條，且有索引，但還是要重新查詢計算偏移量，效率一樣很慢
  • 方案
    • ORDER BY + 索引字段(注：不適用于混合排序，混合排序還是會全表掃描)
    • 以上一次查詢的記錄的排序字段最大值(即最后一條記錄的排序字段)作為條件
    • 使用游標

• ORDER BY必須和LIMIT聯用，否則會被優化掉

  • LIMIT以后可根據索引只取少量數據，不加LIMIT，ORDER BY 索引沒有任何意義

  • 如果你將LIMIT row_count子句與ORDER
    BY子句組合在一起使用的話，MySQL會在找到排序結果的第一個row_count行后立即停止排序，不會對結果集的任何剩余部分進行排序。這種行為的一種表現形式是，一個ORDER BY查詢帶或者不帶LIMIT可能返回行的順序是不一樣的，甚至多次查詢的順序也可能是不一樣的

• 只更新必要字段，減少binlog日志

• 使用UNION ALL代替UNION，如果結果集允許重復的話或已知不可能出現重復

  • 不管檢索結果有沒有重復，都會嘗試合并，并在輸出之前進行排序，允許重復或已知不可能出現重復，UNION ALL比UNION效率更高

• 慎用DISTINCT

  • 查詢一個或幾個很少字段時，會帶來優化效果，但很多時，卻會大大降低查詢效率，因為使用DISTINCT，數據庫引擎會對數據進行比較，過濾掉重復數據，但這個比較、過濾的過程會占用系統資源、CPU時間

my.cnf配置

• 修改max_connections、max_used_connections
  • 并發數指同一時刻數據庫能處理多少個請求，由max_connections、max_used_connections決定
    • max_connections是指MySQL實例的最大連接數，上限值是16384
    • max_used_connections是指每個數據庫用戶的最大連接數
  • MySQL會為每個連接提供緩沖區，意味著消耗更多的內存，如果連接數設置太高硬件吃不消，太低又不能充分利用硬件，一般要求兩者比值超過10%，計算公式如：max_used_connections / max_connections * 100% = 3 / 100 * 100%
  • 查看max_connections、max_used_connections

show variables like '%max_connections%' show variables like '%max_used_connections%'

• 將單次查詢耗時控制在0.5秒內
  • 0.5秒是個經驗值，源于用戶體驗的3秒原則。如果用戶的操作3秒內沒有響應，將會厭煩甚至退出。響應時間=客戶端UI渲染耗時+網絡請求耗時+應用程序處理耗時+查詢數據庫耗時，0.5秒就是留給數據庫1/6的處理時間

總結

以上是生活随笔為你收集整理的数据库性能优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。