Mysql

為什麼 EXPLAIN 的行不准確?

  • August 13, 2013

我有下表:

mysql> select count(*) from employees;  
+----------+  
| count(*) |  
+----------+  
|    10000 |  
+----------+  
1 row in set (0.03 sec)  

我做了一個EXPLAIN:mysql> explain select last_name from employees order by last_name;

+----+-------------+-----------+------+---------------+------+---------+------+------+----------------+  
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------------+  
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 9894 | Using filesort |  
+----+-------------+-----------+------+---------------+------+---------+------+------+----------------+  
1 row in set (0.00 sec)

這些行是 9894。我期待 10000。

我這樣做:

mysql> analyze table employees;
+-------------------------------------+---------+----------+----------+  
| Table                               | Op      | Msg_type | Msg_text |  
+-------------------------------------+---------+----------+----------+  
| sql_dummy.employees | analyze | status   | OK       |  
+-------------------------------------+---------+----------+----------+  
1 row in set (0.04 sec)  

並重新執行EXPLAIN

mysql> explain select last_name from employees order by last_name;

+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra          |  
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 10031 | Using filesort |  
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
1 row in set (0.01 sec)  

這些行現在是 10031。

有誰知道為什麼行永遠不是 10000?我在其他情況下也注意到了這一點。

你可能會覺得這很奇怪,但這就是 InnoDB 的行為。

InnoDB 儲存引擎通過在非葉 BTREE 節點下走幾級來進行基數近似,並進行有根據的猜測。

我很久以前基於mysqlperformanceblog.com寫過這個

也許您可以嘗試通過執行以下命令來禁用該行為:

SET GLOBAL innodb_stats_on_metadata = 0;

設置innodb_stats_on_metadata 應該會穩定近似基數,並且您應該一遍又一遍地獲得相同的基數。

引用自:https://dba.stackexchange.com/questions/48067