mysql查詢效能優化總結

曹大聖發表於2020-12-23
MySql優化

目錄

 

效能監控和分析

效能定義

效能分析

schema和資料型別優化

索引優化

查詢優化

學習要用思維導圖，將知識點連結起來，形成知識圖譜，知道哪些點，細節去查

效能監控和分析

效能定義

效能如何度量，核心指標：
1.吞吐量 tps，olap資料庫偏向吞吐量
2.時延，oltp應用偏向時延低

效能分析

  1. 效能監控
    1. set profiling 1
    2. show profile看各個階段的耗時
      1. 看官方文件 https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
      2. 檢視cpu 磁碟等所有的資訊
      3. 基於session不會持久化
      4.  
    3. mysql performance schema
      1. 包含各種效能監控表，不會進行持久化
      2. performance sechma on，預設開啟
      3. 檢視mysql在執行的執行緒在幹啥
      4. 看官網使用文件 https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html
      5. 做一個監控皮膚
    4. show processlist 檢視連結數量
      1. 連線池
        1. druid
          1. 監控資訊文件
          2. PreparedStatementCache
        2. c3p0
    5. show status handler_read檢視引擎的介面狀態。引擎是通過各種handler的介面來和服務層互動的
  2. 分析步驟
      1. explain 看索引和掃描的行數
        1. explain
          1. id，執行優先順序
          2. select type 查詢型別，
            1. primary從句的最外層
          3. type
            1. all
            2. index
            3. range
            4. ref
            5. eq_ref
            6. const
      2. show waring 看優化後的sql
      3. show profiles 檢視查詢細粒度時間
      4. 看官方文件

schema和資料型別優化

  1. 資料型別最小簡單最好
  2. 儘量不要用null
  3. char和varchar
    1. char會自動去結尾空格
    2. char讀寫效率高，varchar儲存空間小
  4. date timestamp datetime
    1. 精度，儲存空間
  5. 列舉型別
    1. 使用的是整形儲存
    2. 自動轉換成字元名稱
  6. 正規化和正規化
    1. 主鍵能確定一行，每行的列不能再拆分
    2. 每列都是和主鍵有關係
    3. 只依賴主鍵，直接關係不是簡介依賴
  7. 主鍵選擇
    1. 代理主鍵，不依賴業務
    2. 自然主鍵，業務相關的屬性id
    3. 推薦使用代理主鍵：不依賴業務，通用的id生成減少維護成本
  8. 字符集選擇
    1. 純英文latin1
    2. 中文使用utf8-mb4，純utf8只有兩個位元組
    3. 儘量精確到欄位，減少儲存量，降低io操作次數，提高快取命中率
  9. 儲存引擎選擇
    1. 一般用innodb
  10. 適當榮譽
    1. 列比較多，但是用到的不多，通常用join則浪費io，可以用單獨的表來進行
    2. 類似中間表關聯關係
  11. 適當拆分
    1. 垂直水平拆分

索引優化

  1. 資料結構
    1. b+樹
      1. 每個節點數索引指標多
      2. 非葉子節點不存資料，減少io，減少io資料量
  2. 回表，用非聚集索引中的主鍵查詢
  3. 覆蓋索引，select的列都被在索引中
  4. 最左匹配，組合索引命中索引條件
  5. 索引下推，謂詞下推，組合索引在儲存引擎層將資料過濾，不用再服務層過濾，減少io量
  6. union all,in,or使用
    1. union  會對結果排序和去重複，比union all效率低
    2. 子句簡單優先
    3. 在沒有索引的情況下in優於or，or判斷多，in是二分查詢，有索引時in or union all差不多
    4. exists 必須是子查詢，使用外層限制內層，不能查詢子查詢的欄位，比in快
    5. exists和in的效率，exists是外層表沒有索引時表掃描
    6. 查詢過程
      1. in 和join類似是迴圈巢狀，特定條件優化器會將in改成join， for for
      2. exists是外層的每個元素對字句進行判斷，字句可以走索引 for if（condition(out)）
  7. 範圍列可以用索引，但是範圍列後面的列索引不能用上，只能用一個
  8. 強制型別轉換會全表掃描
    1. 欄位為strng，用整形來匹配
  9. 更新頻繁，基數低的欄位不宜建索引
  10. 索引欄位不能為null
    1. “is null” 或者 “is not null” 或者 “<=> null” 是可以走索引的
    2. 存null會導致統計出異常
    3. 存null需要額外的儲存空間
  11.  

查詢優化

查詢優化的核心在於優化索引，建立高效的索引和行數少的查詢

  1. innodb引擎是基於成本的優化
    1. mysql優化器根據資料庫的各種統計資料來進行優化，基於成本意味著掃描行數少的成本低
    2. 不是每次優化都是最優的需要根據查詢計劃來優化，同時不斷嘗試其他的索引。
  2. 鎖
    1. innodb鎖的是索引
  3. 查詢快取
    1. 8.0已經移除
  4. calcite sql解析框架
  5. 優化min max
  6. 索引覆蓋
  7. join優化
    1. join的時候不要超過3張表
      1. 迴圈巢狀的實現方式，小表放外層作為驅動表，大表做為被驅動表
      2. 小表驅動大表，left join驅動表為左表
      3. 被驅動表的關聯欄位加索引優化
      4. mysql會優化驅動表和被動表的順序
      5. 迴圈巢狀實現方式，官網
        1. 外層迴圈遍歷每行找到內層迴圈的匹配行
        2. 無索引迴圈巢狀 join，simple nested loop join
        3. 有索引使用index nested loop join
        4. 無索引時的優化方式block nested loop join ，將驅動表放到記憶體 join buffer中每次不是一行掃描被驅動表，而是批量掃描，減少被驅動表掃描次數，無索引時會採用避免simple的join，可以設定join buffer size
      6. left join and 左表都輸出，右表and 不符合就不顯示
      7. 優先用內連線，不是外連線，因為外連線會產生null
      8. straight join 禁止sql優化join順序
      9. 等值連結 == 即using
      10. 8.0 hash join
        1. 沒索引情況的優化
        2. 等值連線
        3. 比blocked join好
  8. 排序優化
    1. filesorting
      1. 有索引的列也可以能回有
    2. 排序在引擎層的優化
      1. 兩次傳輸
        1. 先排序列在查詢行
      2. 單次傳輸
        1. 直接進行整行排序
        2. 排序快取較多
    3. 藉助索引覆蓋優化排序，排序集合大時效果明顯
  9. cout優化
    1. myism 不用條件很快
    2. 使用近似值
    3. innodb不會有效率區別
  10. 子查詢優化
    1. 儘量使用join，子查詢會有臨時表
    2. 使用帶走索引的 join 或者in exists 優化不走索引的查詢
    3. 子查詢優化，使用索引覆蓋的子查詢優化外層查詢，如果外層索引沒有用索引
  11. limit優化
    1. 儘量使用limit減少輸出
    2. 索引覆蓋優化深度分頁
  12. 自定義變數
    1. set @abd:=1;
    2. select @abd:=@abd+1;用來記錄行數或者排名
    3. 可能會被優化掉
    4. 無法使用查詢快取
  13. union列轉行：https://blog.csdn.net/weter_drop/article/details/105899362?utm_medium=distribute.pc_aggpage_search_result.none-task-blog-2~all~sobaiduend~default-1-105899362.nonecase&utm_term=mysql%20union%20%E8%A1%8C%E8%BD%AC%E5%88%97&spm=1000.2123.3001.4430
    1. join 行轉列
    2. case when
  14. 分割槽表，官網，類似分表
    1. 將idb檔案進行分割槽，分多個檔案儲存，#號分割
    2. 減少inode和索引的鎖競爭
    3. 分割槽單獨管理減少維護量
    4. 檔案個數有上限，fd上限，和記憶體相關
    5. range分割槽
      1. year分割槽
    6. 列分割槽
    7. 列表分割槽，等值分割槽
    8. hash分割槽
    9. key分割槽
      1. 按照主鍵分割槽
    10. 原理
      1. 分割槽表和普通表一樣
      2. 底層表都是單獨的引擎
      3. 增刪改時，先鎖住所有底層表，然後過濾到對應的分割槽進行操作。
    11. 儘量不要修改分割槽鍵
  15. 配置優化
    1. 最大連線數量，set max_connections.
    2. show processlist; 檢視連結數
    3. 每個使用者的連結數限制
    4. back log 連結等待佇列
    5. 互動連結的超時
    6. 日誌設定
      1. logerror error檔案
      2. binlog
        1. binlog do db，那些資料庫存到binlog ，白名單，黑名單
        2. 順序寫
        3. 備份和binglog同時使用
        4. sync_binlog ，這個引數直接影響mysql的效能和完整性
          1. sync_binlog=0 ，當事務提交後，Mysql僅僅是將binlog_cache中的資料寫入Binlog檔案，但不執行fsync之類的磁碟 同步指令通知檔案系統將快取重新整理到磁碟，而讓Filesystem自行決定什麼時候來做同步，風險大
          2. sync_binlog=n，在進行n次事務提交以後，Mysql將執行一次fsync之類的磁碟同步指令，同志檔案系統將Binlog檔案快取重新整理到磁碟。
          3. 一般設定為1
        5. redo log寫入模式
          • buffer pool->log buffer -> os buffer->disk隨機寫
          • 最安全的是直接提交到disk，儘量每次都提交fsync
            1. 在 MySQL 的配置檔案中提供了 innodb_flush_log_at_trx_commit 引數，這個可以用來控制緩衝區和磁碟之間的資料如何同步，這裡有 0、1、2 三個選項，在我裝的 MySQL 下預設的是 1，簡單介紹一下這三個選項的區別：
              1. 0：表示當提交事務時，並不將緩衝區的 redo 日誌寫入磁碟的日誌檔案，而是等待主執行緒每秒重新整理。
              2. 1：在事務提交時將緩衝區的 redo 日誌同步寫入到磁碟，保證一定會寫入成功。
              3. 2：在事務提交時將緩衝區的 redo 日誌非同步寫入到磁碟，即不能完全保證 commit 時肯定會寫入 redo 日誌檔案，只是有這個動作。

        6. 寫入兩階段提交過程，prepare階段 先寫redo，進入commit階段 寫binlog，寫完，redo 進行commit

          • slow query log
            1. 即時查詢
          • general log
            1. 查詢日誌
  16. 快取
    1. key buffer size ，myism 的索引快取大小
    2. query cache，5.7以下
      1. query cache limit
      2.  
    3. sort buffer
      1. 排序用的
    4. max allow packet 最大的tcp包
    5. join buffer ，join大快取大小
    6. thread cache size
      1. 執行緒池
    7. innodb buffer pool size
    8. read buffer size 順序讀的buffer
    9. read rnd buffer size 隨機讀的buffer
    10. innodb file per table 一張表一個ibd，否則在ibdata1檔案
  17. 鎖
    1. myisam鎖
      1. 獨佔鎖
        1. lock table * write
        2.  
      2. 共享鎖
        1. lock table * read
      3. 自動會加讀寫鎖
    2. innodb
      1. 意向鎖，用來判斷是否有被鎖住
        1. 當再向一個表新增表級X鎖的時候
        2. 如果沒有意向鎖的話，則需要遍歷所有整個表判斷是否有行鎖的存在，以免發生衝突
        3. 如果有了意向鎖，只需要判斷該意向鎖與即將新增的表級鎖是否相容即可。因為意向鎖的存在代表了，有行級鎖的存在或者即將有行級鎖的存在。因而無需遍歷整個表，即可獲取結果
      2. 有索引才是行鎖，沒索引是表鎖，鎖的是索引
      3. 自增鎖，表鎖
      4. 讀共享鎖，lock for read
      5. 寫排他鎖 ，lock for update
  18. 叢集
    1. 主從複製
      1. mts binlog無延時
    2. 讀寫分離
      1. mysql-proyx
      2. mycat
      3. 變形蟲
    3. 分庫分表
      1. sharding-jdbc

 

相關文章