MariaDB· 新鮮特性·ANALYZE statement 語法 · 數據庫內核月報

MariaDB 10.1版本中新增加了一個 ANALYZE statement 命令。這個命令跟 EXPLAIN statement 命令類似，但不同的是， ANALYZE statement 命令調用優化器生成執行計劃并且會真實的去執行語句，再用 EXPLAIN 的輸出來替代結果集，并且 EXPLAIN 結果是實際語句執行中統計出來的。這個語句可以讓你檢查優化器估算的執行計劃代價和實際執行差多少。 **命令的輸出** ~~~ MariaDB> analyze select * from tbl1 where key1 between 10 and 200 and col1 like 'foo%'\G *************************** 1\. row *************************** id: 1 select_type: SIMPLE table: tbl1 type: range possible_keys: key1 key: key1 key_len: 5 ref: NULL rows: 181 r_rows: 181 filtered: 100.00 r_filtered: 10.50 Extra: Using index condition; Using where ~~~ 我們可以看到 ANALYZE 命令多了r_rows和r_filterd兩行，我們來比較一下 EXPLAIN 計算的 rows/filtered 和 ANALYZE 計算的 r_rows/r_filtered 兩列的區別。 r_rows 是基于實際觀察的 rows 列，它表示實際從表中讀取了多少行數據。 r_filtered 是基于實際觀察的 filtered 列，它表示經過應用WHERE條件之后還有百分之多少的數據剩余。 **輸出結果解析** 讓我們來看一個更復雜的SQL。 ~~~ analyze select * from orders, customer where customer.c_custkey=orders.o_custkey and customer.c_acctbal < 0 and orders.o_totalprice > 200*1000 ~~~ ~~~ +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ | 1 | SIMPLE | customer | ALL | PRIMARY,... | NULL | NULL | NULL | 149095 | 150000 | 18.08 | 9.13 | Using where | | 1 | SIMPLE | orders | ref | i_o_custkey | i_o_custkey | 5 | customer.c_custkey | 7 | 10 | 100.00 | 30.03 | Using where | +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ ~~~ 從上面的結果，我們可以獲得如下信息：對于 customer 表，customer.rows=149095, customer.r_rows=150000\. 從這兩個值來看，優化器對 customer 表的訪問估算還是很準確的。 customer.filtered=18.08, customer.r_filtered=9.13\. 優化器有點高估了`customer`?表所匹配的記錄的條數。（一般來說，當你有個全表掃描，并且 r_filtered 少于15%的時候，你得考慮為表增加相應的索引了） orders.filtered=100, orders.r_filtered=30.03\. 優化器無法預估經過條件（orders.o_totalprice > 200*1000）檢查后還剩多少比例的記錄。因此，優化器顯示了100%。事實上，這個值是30%，通常來說30%的過濾性并不值得去建一個索引。但是對于多表Join，采集和使用列統計信息也許對查詢有幫助，也可能幫助優化器選擇更好的執行計劃。（因為在關聯中，關聯條件和普通過濾條件組合以后，可能過濾性會非常好，并且有助于優化器判斷哪張表做驅動表比較好）然后我們再把前面的例子稍微修改一下 ~~~ analyze select * from orders, customer where customer.c_custkey=orders.o_custkey and customer.c_acctbal < -0 and customer.c_comment like '%foo%' orders.o_totalprice > 200*1000 +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ | 1 | SIMPLE | customer | ALL | PRIMARY,... | NULL | NULL | NULL | 149095 | 150000 | 18.08 | 0.00 | Using where | | 1 | SIMPLE | orders | ref | i_o_custkey | i_o_custkey | 5 | customer.c_custkey | 7 | NULL | 100.00 | NULL | Using where | +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ ~~~ 這里我們可以看到 orders.r_rows=NULL，以及 orders.r_filtered=NULL。這意味著 orders 表連一次都沒有被掃描到。