为什么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
- bool Explain_join::explain_rows_and_filtered()
- {
- if (table->pos_in_table_list->schema_table)
- return false;
- double examined_rows;
- if (select && select->quick)
- examined_rows= rows2double(select->quick->records);
- else if (tab->type == JT_INDEX_SCAN || tab->type == JT_ALL)
- {
- if (tab->limit)
- examined_rows= rows2double(tab->limit);
- else
- {
- table->pos_in_table_list->fetch_number_of_rows();
- examined_rows= rows2double(table->file->stats.records);
- }
- }
- else
- examined_rows= tab->position->records_read;
- fmt->entry()->col_rows.set(static_cast
(examined_rows)); - /* Add "filtered" field */
- if (describe(DESCRIBE_EXTENDED))
- {
- float f= 0.0;
- if (examined_rows)
- f= 100.0 * tab->position->records_read / examined_rows;
- fmt->entry()->col_filtered.set(f);
- }
- return false;
- }
但是,我构造了一个全表扫描后,filtered的结果却不对,仍然是100%,而我期待的是0.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跟踪,发现代码走的分支是对的,但下面的值有问题。
- (gdb) p table->file->stats.records
- $18 = 996355
- (gdb) p tab->position->records_read
- $19 = 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收集的统计信息非常少。
- 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)
3. 引申
后面我联系到MySQL匮乏的统计信息会带来什么后果?不难想象,如果缺少索引,MySQL很可能会生成性能糟糕的执行计划,比如搞错大表和小表的join顺序,就像下面这样。
- 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)
相同的查询,PostgreSQL给出的执行计划是更好的,先扫描t2表再循环扫描t1表。
- postgres=# explain select count(*) from tb1,tb2 where tb1.c1=tb2.c1 and tb2.c2='xx';
- QUERY PLAN
- -------------------------------------------------------------------
- Aggregate (cost=20865.50..20865.51 rows=1 width=0)
- -> Nested Loop (cost=0.00..20865.50 rows=1 width=0)
- Join Filter: (tb1.c1 = tb2.c1)
- -> Seq Scan on tb2 (cost=0.00..20834.00 rows=1 width=4)
- Filter: ((c2)::text = 'xx'::text)
- -> Seq Scan on tb1 (cost=0.00..19.00 rows=1000 width=4)
- (6 rows)
MySQL花了0.34s
- mysql> select count(*) from tb1,tb2 where tb1.c1=tb2.c1 and tb2.c2='xx';
- +----------+
- | count(*) |
- +----------+
- | 0 |
- +----------+
- 1 row in set (0.34 sec)
PostgreSQL花了0.139s
- postgres=# select count(*) from tb1,tb2 where tb1.c1=tb2.c1 and tb2.c2='xx';
- count
- -------
- 0
- (1 row)
- Time: 139.600 ms
上面这个例子的性能差别其实不是很大,如果去掉tb2.c2='xx'的条件,差别就非常大了。
Mysql花了1分08秒
- 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秒
- postgres=# explain select count(*) from tb1,tb2 where tb1.c1=tb2.c1;
- QUERY PLAN
- -------------------------------------------------------------------------
- Aggregate (cost=23502.34..23502.35 rows=1 width=0)
- -> Hash Join (cost=31.50..23474.97 rows=10947 width=0)
- Hash Cond: (tb2.c1 = tb1.c1)
- -> Seq Scan on tb2 (cost=0.00..18334.00 rows=1000000 width=4)
- -> Hash (cost=19.00..19.00 rows=1000 width=4)
- -> Seq Scan on tb1 (cost=0.00..19.00 rows=1000 width=4)
- (6 rows)
- Time: 0.690 ms
- postgres=# select count(*) from tb1,tb2 where tb1.c1=tb2.c1;
- count
- -------
- 10068
- (1 row)
- 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插件)。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



In recent days, Ice Universe has been steadily revealing details about the Galaxy S25 Ultra, which is widely believed to be Samsung's next flagship smartphone. Among other things, the leaker claimed that Samsung only plans to bring one camera upgrade

OnLeaks has now partnered with Android Headlines to provide a first look at the Galaxy S25 Ultra, a few days after a failed attempt to generate upwards of $4,000 from his X (formerly Twitter) followers. For context, the render images embedded below h

Alongside announcing two new smartphones, TCL has also announced a new Android tablet called the NXTPAPER 14, and its massive screen size is one of its selling points. The NXTPAPER 14 features version 3.0 of TCL's signature brand of matte LCD panels

In recent days, Ice Universe has been steadily revealing details about the Galaxy S25 Ultra, which is widely believed to be Samsung's next flagship smartphone. Among other things, the leaker claimed that Samsung only plans to bring one camera upgrade

The Vivo Y300 Pro just got fully revealed, and it's one of the slimmest mid-range Android phones with a large battery. To be exact, the smartphone is only 7.69 mm thick but features a 6,500 mAh battery. This is the same capacity as the recently launc

Samsung has not offered any hints yet about when it will update its Fan Edition (FE) smartphone series. As it stands, the Galaxy S23 FE remains the company's most recent edition, having been presented at the start of October 2023. However, plenty of

The Redmi Note 14 Pro Plus is now official as a direct successor to last year'sRedmi Note 13 Pro Plus(curr. $375 on Amazon). As expected, the Redmi Note 14 Pro Plus heads up the Redmi Note 14 series alongside theRedmi Note 14and Redmi Note 14 Pro. Li

Motorola has released countless devices this year, although only two of them are foldables. For context, while most of the world has received the pair as the Razr 50 and Razr 50 Ultra, Motorola offers them in North America as the Razr 2024 and Razr 2
