mysql - 当 EXPLAIN 看起来很好时，为什么这个查询要检查这么多行？

Question

我有以下查询:

SELECT  mdg.id AS mdg_id, vdk.id AS vdisk_id, vdk.name AS vdisk_name, vdk.objKey AS vdisk_key,
        vdc.type AS copy_type, vde.copy_id AS copy_id, mdk.id AS mdisk_id, vde.number_extents AS extent_count,
        IFNULL(ter.name,'generic_hdd') AS mdisk_tier, mdg.snapKey, vde.objKey AS row_key
    FROM       mdisk_grp   AS mdg
    INNER JOIN vdiskcopy   AS vdc ON mdg.snapKey = vdc.snapKey AND mdg.id = vdc.mdisk_grp_id
    INNER JOIN vdisk       AS vdk ON vdc.owner = vdk.objKey
    INNER JOIN vdiskextent AS vde ON vdk.snapKey=vde.snapKey AND vdk.id = vde.vdisk_id AND vdc.copy_id = vde.copy_id
    INNER JOIN mdisk       AS mdk ON vde.id = mdk.id AND vde.snapKey = mdk.snapKey
    LEFT  JOIN tier_mdisk  AS tmk ON tmk.mdisk_objKey = mdk.objKey
    LEFT  JOIN tier        AS ter ON tmk.tier_objKey = ter.objKey
    WHERE mdg.snapKey= '333';

我经常在慢速查询日志中看到它(当然，mdg.snapKey 的值不同)；例如:

# User@Host: svcControl[svcControl] @ localhost []
# Query_time: 2577  Lock_time: 0  Rows_sent: 11469  Rows_examined: 354942843

考虑到要检查的行数，它运行时间长这一事实并不奇怪。

但是，当我EXPLAIN 查询时，我看到了

+----+-------------+-------+--------+---------------------------------------------------------+--------------------+---------+------------------------+------+---------------------+
| id | select_type | table | type   | possible_keys                                           | key                | key_len | ref                    | rows | Extra               |
+----+-------------+-------+--------+---------------------------------------------------------+--------------------+---------+------------------------+------+---------------------+
|  1 | SIMPLE      | tmk   | system | mdisk_objKey_idx                                        | NULL               | NULL    | NULL                   |    0 | const row not found |
|  1 | SIMPLE      | ter   | const  | PRIMARY                                                 | NULL               | NULL    | NULL                   |    1 |                     |
|  1 | SIMPLE      | mdg   | ref    | mdisk_grp_id_idx,snapIdx                                | snapIdx            | 2       | const                  |   11 |                     |
|  1 | SIMPLE      | vdc   | ref    | snapIdx,mdgIdIdx,ownerIdx                               | mdgIdIdx           | 5       | svcObjects.mdg.id      |    1 | Using where         |
|  1 | SIMPLE      | vdk   | eq_ref | PRIMARY,vdisk_id_idx,snapIdx                            | PRIMARY            | 3       | svcObjects.vdc.owner   |    1 |                     |
|  1 | SIMPLE      | mdk   | ref    | mdisk_id_idx,snapKey_idx                                | snapKey_idx        | 2       | svcObjects.vdk.snapKey |   16 |                     |
|  1 | SIMPLE      | vde   | ref    | vdiskextent_id_idx,vdiskextent_vdisk_id_idx,snapKey_idx | vdiskextent_id_idx | 5       | svcObjects.mdk.id      |   23 | Using where         |
+----+-------------+-------+--------+---------------------------------------------------------+--------------------+---------+------------------------+------+---------------------+

将所有这些行相乘得到 4000 多一点(忽略列中的 0)

似乎执行计划被忽略了。

有没有一种方法可以运行查询然后得到报告以及实际遵循的执行计划？还是我误解了这个输出？

更新我注意到我的某些索引的基数为 NULL，因此我对所有表进行了ANALYZE。最新的 EXPLAIN 报告:

+----+-------------+-------+--------+---------------------------------------------------------+--------------------------+---------+----------------------+------+---------------------+
| id | select_type | table | type   | possible_keys                                           | key                      | key_len | ref                  | rows | Extra               |
+----+-------------+-------+--------+---------------------------------------------------------+--------------------------+---------+----------------------+------+---------------------+
|  1 | SIMPLE      | tmk   | system | mdisk_objKey_idx                                        | NULL                     | NULL    | NULL                 |    0 | const row not found |
|  1 | SIMPLE      | ter   | const  | PRIMARY                                                 | NULL                     | NULL    | NULL                 |    1 |                     |
|  1 | SIMPLE      | mdg   | ref    | mdisk_grp_id_idx,snapIdx                                | snapIdx                  | 2       | const                |    8 |                     |
|  1 | SIMPLE      | vdc   | ref    | snapIdx,mdgIdIdx,ownerIdx                               | snapIdx                  | 2       | const                | 1580 | Using where         |
|  1 | SIMPLE      | vdk   | eq_ref | PRIMARY,vdisk_id_idx,snapIdx                            | PRIMARY                  | 3       | svcObjects.vdc.owner |    1 |                     |
|  1 | SIMPLE      | vde   | ref    | vdiskextent_id_idx,vdiskextent_vdisk_id_idx,snapKey_idx | vdiskextent_vdisk_id_idx | 5       | svcObjects.vdk.id    |  300 | Using where         |
|  1 | SIMPLE      | mdk   | ref    | mdisk_id_idx,snapKey_idx                                | mdisk_id_idx             | 5       | svcObjects.vde.id    |   14 | Using where         |
+----+-------------+-------+--------+---------------------------------------------------------+--------------------------+---------+----------------------+------+---------------------+

乘以 53,088,000，比慢查询中检查的 354,942,843 好得多。

我想我现在的问题是……我看到的行为是否可以用我所做的来解释……如果索引基数为 NULL，那么执行计划将显得过于乐观，并且还可能导致被检查的必要行数的 6-7 倍？

Answer 1

看看这篇文章是否回答了您的问题:http://www.mysqlperformanceblog.com/2011/10/13/when-explain-estimates-can-go-wrong/