close

當需要過濾的字段上既沒有索引也沒有直方圖時，優化器會根據MySQL代碼中內置的默認規則估計過濾的比率，實際很大程度上是瞎猜，部分常用的默認規則如下：

■表18.1 過濾類型的默認過濾比率

下面是SQL語句的執行計劃中默認過濾比率的幾個例子，首先設置pager，使執行計劃只顯示過濾比率：

mysql> pager grepfilteredPAGER setto'grep filtered'

等於檢索字段的默認過濾比率為10%：

mysql> explain select* fromactor wherefirst_name='lisa'\Gfiltered: 10.001row inset, 1warning (0.00sec)

大於或小於檢索字段的默認過濾比率為三分之一：

mysql> explain select* fromactor wherefirst_name>'lisa'\Gfiltered: 33.331row inset, 1warning (0.00sec)

不等於檢索字段的默認過濾比率為90%：

mysql> explain select* fromactor wherefirst_name<>'lisa'\Gfiltered: 90.001row inset, 1warning (0.00sec)

between的默認過濾比率為11.11%：

mysql> explain select* fromactor wherelast_update between '2006-02-15'and '2006-02-16'\Gfiltered: 11.111row inset, 1warning (0.00sec)

下面SQL中in的過濾比率為20%：

mysql> explain select* fromactor wherefirst_name in('lisa','THORA')\Gfiltered: 20.001 row inset, 1 warning(0.00sec)

使用默認規則估計過濾比率往往是不準的，因此很多時候會生成錯誤的執行計劃，這個時候在字段上收集直方圖統計信息可以解決這個問題。而且直方圖即使不用於改變SQL語句的執行計劃，也可以用於在執行計劃中的filt列顯示正確的過濾的比例。

編寫一個簡單的SQL語句，查詢在payment表裡面支付的金額大於10元的客戶號，生成這個SQL的執行計劃如下：

mysql> explain select customer_id from payment where amount>10\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: paymentpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 16086filtered: 33.33Extra: Using where1 row in set, 1 warning (0.00 sec)

判斷amount字段大於10的記錄，由於這個字段上沒有直方圖的統計信息，優化器根據代碼中內置的默認值估計有三分之一的記錄屬於這個範圍。再判斷amount大於100的記錄的執行計劃如下

mysql> explain select customer_id from payment where amount>100\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: paymentpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 16086filtered: 33.33Extra: Using where1 row in set, 1 warning (0.00 sec)

優化器仍然估計有三分之一的記錄屬於這個範圍，顯然優化器在瞎猜。為了解決這個問題，現在在amount字段上創建直方圖的統計信息的命令和輸出結果如下：

mysql> analyze table payment update histogram on amount with 256 buckets\G*************************** 1. row ***************************Table: sakila.paymentOp: histogramMsg_type: statusMsg_text: Histogram statistics created for column 'amount'.1 row in set (0.31 sec)

再重新生成這個SQL語句的執行計劃如下：

mysql> explain select customer_id from payment where amount>10\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: paymentpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 16086filtered: 0.71Extra: Using where1 row in set, 1 warning (0.00 sec)

優化器根據直方圖的統計信息估計符合這個條件的記錄只占總數0.71%。

刪除這個直方圖的命令如下：

mysql> analyze table payment drop histogram on amount\G*************************** 1. row ***************************Table: sakila.paymentOp: histogramMsg_type: statusMsg_text: Histogram statistics removed for column 'amount'.1 row in set (0.01 sec)

下面的SQL語句查詢單詞消費金額大於10元和在第一個店進行消費的顧客的姓名，在沒有直方圖時的生成的執行計劃如下：

mysql> explain analyze select first_name,last_name fromcustomer inner join payment using (customer_id) where amount>10andstore_id=1\G*************************** 1.row ***************************EXPLAIN: ->Nested loopinner join (cost=3100.48rows=2918) (actual time=0.443..14.853rows=68loops=1)-> Index lookup oncustomer using idx_fk_store_id (store_id=1) (cost=36.35rows=326) (actual time=0.310..0.707rows=326loops=1)-> Filter: (payment.amount > 10.00)(cost=6.72rows=9)(actual time=0.042..0.043rows=0loops=326)->Index lookup onpayment using idx_fk_customer_id (customer_id=customer.customer_id) (cost=6.72rows=27) (actual time=0.031..0.038rows=27loops=326)1row inset (0.01sec)

可以看到優化器先對符合在第一個店進行消費的條件進行過濾，然後再過濾消費金額大於10元的條件。在字段amount上有直方圖統計信息之後，再次生成這個SQL語句的執行計劃如下：

mysql> explain analyze select first_name,last_name fromcustomer inner join payment using (customer_id) where amount>10andstore_id=1\G*************************** 1.row ***************************EXPLAIN: ->Nested loopinner join (cost=1672.84rows=62) (actual time=0.328..9.507rows=68loops=1)-> Filter: (payment.amount > 10.00)(cost=1632.85rows=114)(actual time=0.224..8.421rows=114loops=1)->Table scan onpayment (cost=1632.85rows=16086) (actual time=0.191..6.482rows=16049loops=1)-> Filter: (customer.store_id = 1)(cost=0.25rows=1)(actual time=0.009..0.009rows=1loops=114)->Single-row index lookup oncustomer using PRIMARY (customer_id=payment.customer_id) (cost=0.25rows=1) (actual time=0.009..0.009rows=1loops=114)1row inset (0.02sec)

可以看到優化器將這兩個過濾條件的先後次序反轉過來了，因為藉助直方圖統計信息，優化器知道消費金額大於10元這個條件的選擇性更高。從估計成本和實際執行時間都可以看出，有直方圖的執行計劃效率要好很多！

直方圖在某些場景下可以幫助優化器生成更優的執行計劃，那麼在什麼樣的字段上考慮使用直方圖呢，這裡建議符合下面4個條件字段可以考慮建立直方圖統計信息：

（1）值分布不均勻，優化器很難估計值的分布的字段。

（2）選擇性差的字段，否則索引更適合。

（3）用於where子句中過濾的字段或用於連接的字段。

（4）字段值分布規律不隨時間變化的字段。因為直方圖統計信息不會自動收集，如果字段值分布規律發生大的變化，統計信息會失真。

實際工作中，可以使用explain analyze分析SQL語句的執行計劃，如果估算的rows和實際的rows相差過大，可以考慮在過濾字段上創建直方圖統計信息。

實例講解


MySQL 8.0運維與優化


精彩回顧



忘記root用戶密碼怎麼辦？

01

參考書籍

掃碼優惠購書

《MySQL 8.0運維與優化（微課視頻版）》

ISBN：9787302602682

姚遠 著

定價：99.8元

掃碼京東優惠購書


內容簡介



本書全面介紹MySQL數據庫的管理、監控、備份恢復和高可用等方面的知識，並在此基礎上討論如何優化MySQL的實例和SQL語句，書中還包括大量的實戰案例。

全書分五部分：第一部分（第1～4章）為管理部分，包括安裝和運行、賬號和權限、日誌和安全等內容；第二部分（第5～7章）為監控部分，介紹通過MySQL自帶的3個系統數據進行監控的方法；第三部分（第8～11章）為備份恢復部分，介紹常用的邏輯備份和物理備份工具，還介紹在沒有備份的情況下如何進行數據救援；第四部分（第12～14章）為高可用部分，包括MySQL Shell、複製和InnoDB集群；第五部分（第15～18章）為優化部分，介紹基準測試工具和優化MySQL實例和SQL方法。


作者簡介

姚遠，Oracle ACE，華為雲MVP，現任鼎甲科技首席數據庫專家，曾任IBM公司數據庫部門經理。擁有 Oracle 10g OCM和12c OCM,以及 MySQL 5.6、5.7、8.0 OCP，並在數據庫相關領域擁有20多項技術認證，兩次獲得省部級科學技術進步獎，獲兩項國家發明專利授權。


名人推薦語


This book seems very detailed with many examples on how to administrate a MySQL server. It covers MySQL 8.0 new features and Scott illustrates how to benefit from them. By reading the book, it’s obvious that the author is an experienced MySQL DBA and he shares his experience from which many junior DBA will certainly benefit. This is a very technical book I would recommend and I hope to see it translated to English too.

——Frédéric Descamps，知名MySQL布道師，MySQL社區經理

非常感謝姚遠老師為MySQL生態做出的貢獻。本書通過大量的示例，結合作者多年來的實戰經驗，全方位、多角度、系統地介紹了MySQL 數據庫。推薦MySQL DBA及IT相關技術人員仔細閱讀，相信讀者一定能夠從中獲益。

——徐軼韜，Oracle公司MySQL解決方案首席工程師 ，公眾號「MySQL解決方案工程師」的運營者及內容作者

姚遠兄是數據庫領域的資深專家、MySQL ACE榮譽獲得者和長期耕耘在一線的技術極客。本書不僅面面俱到地介紹運維的基礎知識，還分享了優化的實戰經驗，是全面學習和掌握MySQL 8.0的必備佳作。

——周彥偉，極數雲舟創始人，DTark總架構師，中國計算機行業協會數據庫專委會會長

我認真拜讀了姚遠兄的新書章節，有兩點感受：書中有不少MySQL新版本的特性，通過「穿針引線」的方式和運維場景有效結合，具有實戰意義；書中包含大量案例，這些案例源自作者多年的經驗和沉澱，不禁讚嘆姚遠兄在無數個忙碌的夜晚探索技術的苦與樂。

——楊建榮，Oracle ACE，dbaplus社群發起人,《Oracle DBA工作筆記》和《MySQL DBA工作筆記》作者

本書以MySQL 8.0為基礎，不僅全面地介紹MySQL運維的操作方法，還分享了作者從業多年的學習方法和心得，是一本非常適合MySQL愛好者的好書。

——洪斌，Oracle ACE，愛可生技術服務總監

姚遠兄對技術細節的鑽研精神讓人欽佩。本書總結了各種在一線工作中經常遇到的問題，以問題復現、分析、解決的方式，幫助讀者快速掌握實戰技能。書中介紹了很多MySQL 8.0的新特性，市場上尚未有中文版書籍對這些特性進行詳細講解，這有助於MySQL 8.0在中國的推廣。

——白瑞鈞，Oracle ACE，新炬數據庫高級專家

讀者人群


本書適合具有一定IT基礎知識的MySQL數據庫愛好者閱讀，也可以作為準備MySQL OCP考試的備考書。

目錄



向上滑動閱覽

第一部分管理

第1章安裝和運行

1．1MySQL 8.0社區版的安裝

1．2Percona Server for MySQL的安裝

1．3安裝Sakila示例數據庫

1．4檢查MySQL服務

1．5升級到MySQL 8.0

1．6Linux對MySQL的強制訪問控制

1．7啟動排錯

1．8實驗

第2章賬號和權限

2．1賬號

2．2權限

2．3訪問控制

2．4角色

2．5代理用戶

2．6無密碼登錄

2．7重置root用戶密碼

2．8實驗

第3章日誌

3．1錯誤日誌

3．2通用查詢日誌

3．3慢查詢日誌

3．4二進制日誌

3．5實驗

第4章安全

4．1密碼驗證組件

4．2連接控制插件

4．3連接加密

4．4數據加密

4．5審計插件

4．6實驗

第二部分監控

第5章information_schema數據庫

5．1數據組成

5．2MySQL 8.0中的優化

5．3權限

5．4視圖說明

5．5實驗

第6章performance_schema數據庫

6．1作用和特點

6．2配置

6．3性能計量配置

6．4消費者配置

6．5執行者配置

6．6對象配置

6．7典型用例

6．8實驗

第7章sys數據庫

7．1簡介

7．2配置參數

7．3存儲過程

7．4函數

7．5視圖

7．6實驗

第三部分備份恢復

第8章邏輯備份

8．1邏輯備份和物理備份的區別

8．2mysqldump

8．3mysqlpump

8．4mydumper

8．5MySQL Shell中的備份恢復工具

8．6四種邏輯備份工具的對比

8．7備份集的一致性

8．8提高恢復的速度

8．9實驗

第9章XtraBackup

9．1特點介紹

9．2安裝

9．3工作原理

9．4典型用例

9．5高級功能

9．6實驗

第10章MySQL Enterprise Backup

10．1簡介

10．2工作原理

10．3典型用例

10．4高級功能

10．5實驗

第11章數據救援

11．1InnoDB強制恢復

11．2遷移MyISAM表

11．3隻有表空間文件時批量恢復InnoDB表

11．4使用ibd2sdi恢復表結構

11．5TwinDB數據恢復工具

11．6實驗

第四部分高可用

第12章MySQL Shell

12．1簡介

12．2通用命令

12．3客戶化MySQL Shell

12．4全局對象

12．5報告架構

12．6實驗

第13章複製

13．1簡介

13．2克隆插件

13．3配置複製

13．4GTID

13．5排錯

13．6使用MySQL Shell的AdminAPI管理InnoDB複製

13．7實驗

第14章InnoDB集群

14．1架構

14．2組複製

14．3MySQL Router

14．4管理InnoDB集群

14．5實驗

第五部分優化

第15章基準測試工具

15．1mysqlslap

15．2Sysbench

15．3TPCCMySQL

15．4實驗

第16章實例優化

16．1數據庫優化的重要性

16．2系統參數的修改

16．3內存的分配

16．4InnoDB日誌

16．5硬盤讀寫參數

16．6資源組

16．7實驗

第17章SQL優化基礎

17．1SQL語句的執行計劃

17．2優化器

17．3索引

17．4表連接

17．5統計信息

17．6直方圖

17．7CTE

17．8實驗

第18章SQL優化實戰

18．1找出需要優化的SQL

18．2優化方法

18．3優化索引

18．4準確的統計信息

18．5直方圖的使用

18．6連接優化

18．7優化排序

18．8表空間碎片整理

18．9實驗

參考文獻


02

精彩推薦


微信小程序遊戲開發│猜數字小遊戲（附源碼+視頻）
Flink編程基礎│Scala編程初級實踐
Flink編程基礎│FlinkCEP編程實踐
Flink編程基礎│DataStream API編程實踐
Flink編程基礎│DataSet API編程實踐
數據分析實戰│客戶價值分析
數據分析實戰│價格預測挑戰
數據分析實戰│時間序列預測
數據分析實戰│KaggleTitanic生存預測


arrow
arrow
    全站熱搜
    創作者介紹
    創作者 鑽石舞台 的頭像
    鑽石舞台

    鑽石舞台

    鑽石舞台 發表在 痞客邦 留言(0) 人氣()