为什么Mysql explain extended中的filtered列值总是100%

为什么Mysql explain extended中的filtered列值总是100%

1. 问题

执行Mysql的explain extended的输出会比单纯的explain多一列filtered(MySQL5.7缺省就会输出filtered)，它指返回结果的行占需要读到的行(rows列的值)的百分比。按说filtered是个非常有用的值，因为对于join操作，前一个表的结果集大小直接影响了循环的次数。但是我的环境下测试的结果却是，filtered的值一直是100%，也就是说失去了意义。

参考下面mysql 5.6的代码，filtered值只对index和all的扫描有效（这可以理解，其它场合，通常rows值就等于估算的结果集大小。）。
sql/opt_explain.cc

1. bool Explain_join::explain_rows_and_filtered()
   
2. {
   
3. if (table->pos_in_table_list->schema_table)
   
4. return false;
   
5. 
   
6. double examined_rows;
   
7. if (select && select->quick)
   
8. examined_rows= rows2double(select->quick->records);
   
9. else if (tab->type == JT_INDEX_SCAN || tab->type == JT_ALL)
   
10. {
    
11. if (tab->limit)
    
12. examined_rows= rows2double(tab->limit);
    
13. else
    
14. {
    
15. table->pos_in_table_list->fetch_number_of_rows();
    
16. examined_rows= rows2double(table->file->stats.records);
    
17. }
    
18. }
    
19. else
    
20. examined_rows= tab->position->records_read;
    
21. 
    
22. fmt->entry()->col_rows.set(static_cast(examined_rows));
    
23. 
    
24. /* Add "filtered" field */
    
25. if (describe(DESCRIBE_EXTENDED))
    
26. {
    
27. float f= 0.0;
    
28. if (examined_rows)
    
29. f= 100.0 * tab->position->records_read / examined_rows;
    
30. fmt->entry()->col_filtered.set(f);
    
31. }
    
32. return false;
    
33. }

但是，我构造了一个全表扫描后，filtered的结果却不对，仍然是100%，而我期待的是0.1%。

1. mysql> desc tb2;
   +-------+--------------+------+-----+---------+-------+
   | Field | Type | Null | Key | Default | Extra |
   +-------+--------------+------+-----+---------+-------+
   | id | int(11) | NO | PRI | 0 | |
   | c1 | int(11) | YES | | NULL | |
   | c2 | varchar(100) | YES | | NULL | |
   +-------+--------------+------+-----+---------+-------+
   3 rows in set (0.00 sec)
   
   mysql> explain extended select * from tb2 where c1+----+-------------+-------+------+---------------+------+---------+------+--------+----------+-------------+
   | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
   +----+-------------+-------+------+---------------+------+---------+------+--------+----------+-------------+
   | 1 | SIMPLE | tb2 | ALL | NULL | NULL | NULL | NULL | 996355 | 100.00 | Using where |
   +----+-------------+-------+------+---------------+------+---------+------+--------+----------+-------------+
   1 row in set, 1 warning (10 min 29.96 sec)
   
   mysql> select count(*) from tb2 where c1+----------+
   | count(*) |
   +----------+
   | 1001 |
   +----------+
   1 row in set (1.99 sec)

通过gdb跟踪，发现代码走的分支是对的，但下面的值有问题。

1. (gdb) p table->file->stats.records
   
2. $18 = 996355
   
3. (gdb) p tab->position->records_read
   
4. $19 = 996355

上面的tab->position->records_read应该是估算出的返回行数，正确的值应该是1001左右，而不是全表大小996355。

2.原因

为什么会出现上面的情况呢？后来我查看了下MySQL收集的统计信息就明白了。
MySQL和其它主流数据库一样会自动需要收集统计信息以便生成更好的执行计划，也可以用analyze table手动收集，收集的统计信息存储在mysql.innodb_table_stats和mysql.innodb_index_stats里。
参考:http://dev.mysql.com/doc/refman/5.6/en/innodb-persistent-stats.html#innodb-persistent-stats-tables

但这不是重点，重点是，查看这两个表就会发现MySQL收集的统计信息非常少。

1. mysql> select * from mysql.innodb_table_stats where table_name='tb2';
   +---------------+------------+---------------------+--------+----------------------+--------------------------+
   | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
   +---------------+------------+---------------------+--------+----------------------+--------------------------+
   | test | tb2 | 2015-12-02 06:26:54 | 996355 | 3877 | 0 |
   +---------------+------------+---------------------+--------+----------------------+--------------------------+
   1 row in set (0.00 sec)
   
   mysql> select * from mysql.innodb_index_stats where table_name='tb2';
   +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
   | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
   +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
   | test | tb2 | PRIMARY | 2015-12-02 06:26:54 | n_diff_pfx01 | 996355 | 20 | id |
   | test | tb2 | PRIMARY | 2015-12-02 06:26:54 | n_leaf_pages | 3841 | NULL | Number of leaf pages in the index |
   | test | tb2 | PRIMARY | 2015-12-02 06:26:54 | size | 3877 | NULL | Number of pages in the index |
   +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
   3 rows in set (0.00 sec)
   

重要的信息也就2个，一是表的总记录数(n_rows)，二是索引中的列的唯一值数(n_diff_pfx01)。也就是说MySQL不会统计非索引列的值分布信息，在前面的查询的例子中，由于c1没有被索引，所以MySQL无法估算出"c1

3. 引申

后面我联系到MySQL匮乏的统计信息会带来什么后果？
不难想象，如果缺少索引，MySQL很可能会生成性能糟糕的执行计划，比如搞错大表和小表的join顺序,就像下面这样。

1. mysql> explain extended select count(*) from tb1,tb2 where tb1.c1=tb2.c1 and tb2.c2='xx';
   +----+-------------+-------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
   | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
   +----+-------------+-------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
   | 1 | SIMPLE | tb1 | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL |
   | 1 | SIMPLE | tb2 | ALL | NULL | NULL | NULL | NULL | 996355 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
   +----+-------------+-------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
   2 rows in set, 1 warning (0.00 sec)

虽然t1表时小表，tb2表是大表，但是tb2上加上tb2.c2='xx'的条件限制后结果集就变成0了，因此先扫描tb2表才是性能更好的选择。
相同的查询，PostgreSQL给出的执行计划是更好的，先扫描t2表再循环扫描t1表。

1. postgres=# explain select count(*) from tb1,tb2 where tb1.c1=tb2.c1 and tb2.c2='xx';
   
2. QUERY PLAN
   
3. -------------------------------------------------------------------
   
4. Aggregate (cost=20865.50..20865.51 rows=1 width=0)
   
5. -> Nested Loop (cost=0.00..20865.50 rows=1 width=0)
   
6. Join Filter: (tb1.c1 = tb2.c1)
   
7. -> Seq Scan on tb2 (cost=0.00..20834.00 rows=1 width=4)
   
8. Filter: ((c2)::text = 'xx'::text)
   
9. -> Seq Scan on tb1 (cost=0.00..19.00 rows=1000 width=4)
   
10. (6 rows)

下面实际对比一下执行时间看看。

MySQL花了0.34s

1. mysql> select count(*) from tb1,tb2 where tb1.c1=tb2.c1 and tb2.c2='xx';
   
2. +----------+
   
3. | count(*) |
   
4. +----------+
   
5. | 0 |
   
6. +----------+
   
7. 1 row in set (0.34 sec)

PostgreSQL花了0.139s

1. postgres=# select count(*) from tb1,tb2 where tb1.c1=tb2.c1 and tb2.c2='xx';
   
2. count
   
3. -------
   
4. 0
   
5. (1 row)
   
6. 
   
7. Time: 139.600 ms

上面这个例子的性能差别其实不是很大，如果去掉tb2.c2='xx'的条件，差别就非常大了。
Mysql花了1分08秒

1. mysql> explain select count(*) from tb1,tb2 where tb1.c1=tb2.c1;
   +----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
   | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
   +----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
   | 1 | SIMPLE | tb1 | ALL | NULL | NULL | NULL | NULL | 1000 | NULL |
   | 1 | SIMPLE | tb2 | ALL | NULL | NULL | NULL | NULL | 996355 | Using where; Using join buffer (Block Nested Loop) |
   +----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
   2 rows in set (0.00 sec)
   
   mysql> select count(*) from tb1,tb2 where tb1.c1=tb2.c1;
   +----------+
   | count(*) |
   +----------+
   | 9949 |
   +----------+
   1 row in set (1 min 8.26 sec)
   

PostgreSQL只用了0.163秒

1. postgres=# explain select count(*) from tb1,tb2 where tb1.c1=tb2.c1;
   
2. QUERY PLAN
   
3. -------------------------------------------------------------------------
   
4. Aggregate (cost=23502.34..23502.35 rows=1 width=0)
   
5. -> Hash Join (cost=31.50..23474.97 rows=10947 width=0)
   
6. Hash Cond: (tb2.c1 = tb1.c1)
   
7. -> Seq Scan on tb2 (cost=0.00..18334.00 rows=1000000 width=4)
   
8. -> Hash (cost=19.00..19.00 rows=1000 width=4)
   
9. -> Seq Scan on tb1 (cost=0.00..19.00 rows=1000 width=4)
   
10. (6 rows)
    
11. 
    
12. Time: 0.690 ms
    
13. postgres=# select count(*) from tb1,tb2 where tb1.c1=tb2.c1;
    
14. count
    
15. -------
    
16. 10068
    
17. (1 row)
    
18. 
    
19. Time: 163.868 ms

不过这个性能差别和统计信息无关，原因在于PG支持Nest Loop Join，Merge Join和Hash Join，而MySQL只支持Nest Loop Join，缺了索引Nest Loop Join会慢得跟龟似的。

4. 总结

1. MySQL的统计信息非常少，只有表行数和索引列的唯一值数目，这使得MySQL的优化器经常不能对数据规模有一个正确的认识而给出性能不佳的执行计划。
2.MySQL的join操作的效率非常依赖于索引(我之前两次帮人调优MySQL的SQL语句都是在加索引)。并不是说PG的join不需要索引，只是不像MySQL缺了索引的反应那么大。上面那个MySQL执行了1分多钟的例子，加上索引后，不管是MySQL还是PG的执行时间都立刻降到10毫秒以内。所以，开发人员在设计表的时候应该对可能的查询方式做个评估，把该建的索引都建上（不能少建也不宜多建）。
3.相比之下，PG不仅统计所有列的值分布，而且除了唯一值还有直方图，频繁值等等信息，支撑了PG的优化器做出正确的决策。猜测也是由于这个原因，PG社区认为PG的优化器已经足够智能，不需要把和Oracle类似的hint功能加到PG的内核里（因为hint可能会被人滥用，导致系统很难维护；不过，实在想用的话可以自己装pg_hint_plan插件）。