Mysql
為什麼 EXPLAIN 的行不准確?
我有下表:
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寫過這個
Jun 21, 2011
: MySQL 查詢優化器從哪裡讀取索引統計資訊?Aug 03, 2011
:我應該什麼時候重建索引?Feb 23, 2013
: mysql 如何處理涉及 myisam 和 innodb 表的查詢?也許您可以嘗試通過執行以下命令來禁用該行為:
SET GLOBAL innodb_stats_on_metadata = 0;
設置innodb_stats_on_metadata 應該會穩定近似基數,並且您應該一遍又一遍地獲得相同的基數。