277_DBA_執行計劃成本概述_統計信息收集

查詢成本估算

查詢成本 = I/0 成本 + CPU 成本 ??（讀取一個頁面花費成本是1.0， 檢索一條記錄是否符合條件成本是0.2）

測試SQL

Select * from s1 where key1 in (“a”,”b”,”c”) and key2 > 10 and key2 < 100 and key3 > key2 and key_part1 like “%bob%” and commen_field= “json”

1 單表成本

全表掃描的代價

對于s1單表 成本來說 ?12075008字節 / 16/1024 = 1,965個頁

show table status like "s1";?? #data_length 12075008?? index_length 20021248? rows 99385

IO = 1,965 * 1.0 + 1.1 =1966.43 (1.1 是微調值) # 1965代表數據頁數量（數據字節/16KB = page_numbers） Cpu = 99385 * 0.1 + 1.0 = 9939.5 (1.0是微調值) # 99385 代表rows 行數 成本 = IO + CPU = 1966.43 + 9939.5 = 11,905.93

使用索引的代價

二級索引代價 (先算二級唯一索引 再算普通二級索引)

Select * from s1 where key1 in (“a”,”b”,”c”) and key2 > 10 and key2 < 100 and key3 > key2 and key_part1 like “%bob%” and commen_field= “json”

使用唯一二級索引 key2

對于二級索引+回表方式執行的查詢: 成本= 掃描區間數量和需要回表的記錄數

掃描區間數量：無論查詢掃描區間的二級索引占多少頁, 成本與掃描一個頁的I/O成本相同， key2 > 10 and key2 < 100區間為 (10,100) 就一個區間, 意味著一個I/O成本 1 * 1.0 =1

需要回表的記錄數: 找到 key=10 和 key=100的頁號, 然后根據page Header的PAGE_N_RECS屬性 可以大概分析出行數, 例 有100行數據，CPU 成本= 100 * 0.2 + 0.01 =20.01 （0.2是讀取記錄成本常數， 0.01是微調值）

根據需要回表的記錄（主鍵值）執行回表操作：mysql定義每次回表相當于訪問一個數據頁, 100個記錄意味著100個頁 固IO成本 100*1? = 10 （1讀取一個頁的I/O成本常數）

回表操作后取到結果集再判斷 是否符合其它條件：100行數據的 cpu 成本 = 100 * 0.2 = 20 （0.2是 檢測一條記錄的成本常數）

總成本 = IO （掃描區間的數量+ 預估二級索引記錄數）+ cpu （讀取二級索引記錄成本+讀取后檢測是否符合其它條件成本）

總成本 = IO (1 + 100 *1 )? + CPU (100 * 0.2 + 0.01 + 100 * 0.2) = 101 + 40.01=141.01

其它二級索引方式以此類推，最終結合綜合成本 選出最優的，但可見 走了索引的成本 和全表掃描的成本差異是巨大的

2 基于索引統計數據的成本計算

前文說道 掃描索引形成掃描區間，如果類似這類SQL select * from xxx where xxx in (a,b,c,d,e) 會有多個掃描區間，MySQL把通過索引訪問索引樹來計算某個掃描區間內索引記錄數的方式稱為 index_dive;

但是如果掃描區間非常多, in 里面有 2000個字段, 那么性能可能還不如直接做全表掃描

MySQL為了優化這類訪問方式 做了閾值限制，訪問的區間數 大于 @@eq_range_index_dive_limit (默認值200) 就使用索引統計數據(index statistics)來進行估算

show index from s1;

屬性名

描述

Table

索引所在的表名

Non_unique

是否是唯一二級索引/主鍵 ?0 代表是 1 代表否/普通索引

Key_name

索引名字

Seq_in_index

該列索引的位置，從1開始計數 例聯合索引 1/2/3不同位置對應不同字段

Column_name

列名

Collation

排序方式 A代表升序

Cardinality

基數/不重復值的數量, 對于聯合索引來說 key_part3代表 key_part1 & key_part2 & key_part3的組合不重復值

Sub_part

有些索引為前綴索引,前n個字符/字節建立索引

Packed

如何被壓縮, null 代表未被壓縮

NULL

是否允許存null值

Index_type

C常見 BTREE

Comment

注釋信息

結合 show table status like “s1” 中的rows, 99385 / Cardinality ?拿key2為例 代表 key2列的單個值重復次數，此處為1，意味著沒有重復值，需要回表99385次

如果有 2000個掃描區間 + 每個單點回表 2000 * 1 =2000次回表 （此處特殊key2是唯一索引，如果普通索引可能是 10個重復值，2000*10 =20000）

這樣通過索引統計數據來估算出該表不同索引的查詢成本

3 連接查詢的成本

對于join查詢 成本 = 單次查詢驅動表的成本 + 多次查詢被驅動表的成本（具體次數等于驅動表的扇出）

例

Select * from s1 join s2 where s1.key2 > 10 and s1.key2 < 1000; #s1是驅動表，扇出就是 10

explain Select * from s1 join s2 where s1.key2 > 10 and s1.key2 < 1000 and s1.key1 in (1,9,100) and s1.commen_field > "xxx"

這情況 用到了 key2的唯一索引，但是在這之后 還有多少條記錄滿足 s1.key1 in (1,9,100) and s1.commen_field > "xxx" 條件呢？這是優化器需要判斷的（靠哥德巴赫猜想 XJBM）MySQL把這種猜的過程稱為 condition Filtering（條件過濾）可能是index_dive 也可能 index_static

兩表的連接成本分析

連接查詢的總成本 = 單次訪問驅動表的成本 + 扇出值 ?*? 單次訪問被驅動表的成本

對于左/右外連接 驅動表是固定的, 只需要分別為驅動/被驅動選擇成本最低的訪問方法即可

對于內連接，驅動表和被驅動位置可以互換, 要考慮最優的連接順序以及最優成本

explain Select * from s1 join s2 on s1.id = s2.id where s1.key2 > 10 and s1.key2 < 1000 and s1.key1 in (1,9,100) and s1.commen_field > "xxx"

核心思想：1 盡量減少驅動表的扇出 ?2 訪問被驅動表的成本要低

執行計劃顯示 s1 是驅動表， s2是被驅動表 分析相關成本如下

驅動表：用key2的唯一索引充當條件的成本最低

被驅動表：可以用到主鍵id充當檢索條件

固總的成本 = 用unique_key2 訪問 S1 的成本 + s1的扇出 *? 訪問 S2(primary key) 的成本

調節成本常數

一條SQL執行時候, 其實在server 和 innodb引擎層都有執行，server層負責 連接管理，查詢緩存，語法解析和優化，引擎層負責具體的存/取

在server層操作的成本常數存在 mysql.server_cost中， 在引擎層的成本常數存在 mysql.engine_cost中

select * from mysql.server_cost;

Cost_name

default

備注

disk_temptable_create_cost

20

創建基于磁盤的臨時表成本,增大這個值會讓優化器少創基于磁盤臨時表

disk_temptable_row_cost

0.5

向基于磁盤臨時表寫入或讀取一條記錄的成本,增大該值 會讓優化器少創基于磁盤臨時表

key_compare_cost

0.05

比較排序成本，增大該值提升filesort成本, 讓優化器盡可能走索引而不是 filesort

memory_temptable_create_cost

1

創建內存臨時表成本，增大會減少優化器在內存的臨時表

memory_temptable_row_cost

0.1

向基于內存臨時表寫入或讀取一條記錄的成本,增大該值 會讓優化器少創基于磁盤臨時表

row_evaluate_cost

0.1

讀取一條記錄檢索是否符合搜索條件,增大該值,讓優化器更傾向使用索引

Select * from mysql.engine_cost

Cost_name

default

備注

io_block_read_cost

1

從磁盤上讀取一個塊對應的成本, innodb默認一個塊就是一個頁, MyISAM 默認4096 一個塊

memory_block_read_cost

0.25

從內存中讀取一個塊對應的成本

補充 查看庫下面表的大小和行數估算統計

自動統計相關信息

innodb_stats_auto_recalc | ON

innodb_stats_method | nulls_equal? # 代表所有null值都相等(nulls_equal, nulls_unequal, nulls_ignored)

手動收集統計信息

Analyze table table_name? #立即收集相關統計信息

Flush? table table_name # 讓優化器重新加載新的統計信息

庫下表的排序

select table_schema as '數據庫', table_name as '表名', table_rows as '記錄數', truncate(data_length/1024/1024, 2) as '數據容量(MB)', truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables where table_schema='test' order by table_rows desc, index_length desc;

select * from mysql.innodb_table_stats where table_name = "sbtest1_97";

字段名

描述

n_rows

表中記錄的條數

Clustered_index_size

表的聚簇索引占用頁面的數量

Sum_of_other_index_sizes

表的其它索引占用的頁面數量

N_rows 統計原理：從聚簇索引中取N個頁， 統計每個頁 有多少行數據，再用平均值*全部葉子節點數量，相關統計參數 innodb_stats_persistent ON innodb_stats_persistent_sample_pages 20 # 統計數據時 采樣的頁面數量

索引統計信息

Innodb_Index_stats

select * from innodb_index_stats where table_name="sbtest1_97";

stat_name 明細

n_leaf_pages

索引的葉子節點占多少頁

Size

該索引共占多少頁

n_diff_pfx01

表示某列不重復值有多少

k_1 n_diff_pfx01 代表 k列不重復值, 02 代表 k,id 組合不重復值

MySQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。