我正在调查mysql5.7生产数据库中的一些长时间运行的查询。一个特定的查询需要超过60秒。
我通常的方法是从prod中转储数据，将其导入dev数据库，再现问题，然后分析并尝试对查询进行一些调整。
然而，dev中完全相同的查询只花了不到一秒钟的时间。
显然，mysql的配置、表结构、记录编号等都与prod中的相同。
查询本身是一个select，它跨3个表进行连接，每个表上都有一个where子句；其中两个表中有大约15万条记录。我最初的怀疑是在查询的列上缺少索引，但在dev中它运行得非常快的事实似乎证明了这一点。
我能做些什么来阐明这一点？
解释我的查询结果：
生产

EXPLAIN select this_.id as y0_ from event this_ inner join member m1_ on this_.member_id=m1_.id inner join event_type et2_ on this_.type_id=et2_.id where m1_.submission_id=40646 and this_.status in ('SUPPRESSED') and et2_.name in ('Salary') order by m1_.ni_number asc, m1_.ident1 asc, m1_.ident2 asc, m1_.ident3 asc, m1_.id asc, et2_.name asc limit 15;
+----+-------------+-------+------------+--------+-------------------------------------+-------------------+---------+--------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                       | key               | key_len | ref                      | rows | filtered | Extra                                        |
+----+-------------+-------+------------+--------+-------------------------------------+-------------------+---------+--------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | et2_  | NULL       | ALL    | PRIMARY                             | NULL              | NULL    | NULL                     |   17 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | this_ | NULL       | ref    | FK5C6729A2434DA80,FK5C6729AE4E22C6E | FK5C6729AE4E22C6E | 8       | iconnect.et2_.id         | 4166 |    10.00 | Using where                                  |
|  1 | SIMPLE      | m1_   | NULL       | eq_ref | PRIMARY,IND_submission_id           | PRIMARY           | 8       | iconnect.this_.member_id |    1 |     5.00 | Using where                                  |
+----+-------------+-------+------------+--------+-------------------------------------+-------------------+---------+--------------------------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

开发

EXPLAIN select this_.id as y0_ from event this_ inner join member m1_ on this_.member_id=m1_.id inner join event_type et2_ on this_.type_id=et2_.id where m1_.submission_id=40646 and this_.status in ('SUPPRESSED') and et2_.name in ('Salary') order by m1_.ni_number asc, m1_.ident1 asc, m1_.ident2 asc, m1_.ident3 asc, m1_.id asc, et2_.name asc limit 15;
+----+-------------+-------+------------+------+-------------------------------------+-------------------+---------+-----------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys                       | key               | key_len | ref             | rows  | filtered | Extra                                        |
+----+-------------+-------+------------+------+-------------------------------------+-------------------+---------+-----------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | et2_  | NULL       | ALL  | PRIMARY                             | NULL              | NULL    | NULL            |    17 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | m1_   | NULL       | ref  | PRIMARY,IND_submission_id           | IND_submission_id | 8       | const           | 26644 |   100.00 | NULL                                         |
|  1 | SIMPLE      | this_ | NULL       | ref  | FK5C6729A2434DA80,FK5C6729AE4E22C6E | FK5C6729A2434DA80 | 8       | iconnect.m1_.id |     2 |     1.86 | Using where                                  |
+----+-------------+-------+------------+------+-------------------------------------+-------------------+---------+-----------------+-------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.03 sec)

我们还发现，此查询访问的某些索引的基数在dev和prod之间有很大的不同：
fk5c6729ae4e22c6e:开发=9，生产=3792
ind\u submission\u id:dev=2490，prod=74220
这会影响产品的性能吗？