mysql - 相同的数据库，不同的mysql : ENORMOUS DIFFERENCE in query time execution

Question

我有两个 MySQL 安装，一个是在带有 Xampp 的 Windows 上，另一个是在 Mac 上。机器在硬件上是相似的。
数据库是相同的，从 DUMP 文件导入到每个 MySQL 中。

我在这里发布了两个EXPLAINS EXTENDED:

EXPLAIN EXTENDED (ON MAC)

+----+--------------------+--------------+------------+--------+--------------------------+----------+---------+---------------------------------------+------+----------+---------------------------------------------------------------+
| id | select_type        | table        | partitions | type   | possible_keys            | key      | key_len | ref                                   | rows | filtered | Extra                                                         |
+----+--------------------+--------------+------------+--------+--------------------------+----------+---------+---------------------------------------+------+----------+---------------------------------------------------------------+
|  1 | PRIMARY            | little_tasks | NULL       | ref    | post_id,meta_key         | meta_key | 767     | const                                 |  620 |     0.01 | Using where; Using temporary; Using filesort; Start temporary |
|  1 | PRIMARY            | exp_baby     | NULL       | ref    | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.little_tasks.post_id |   12 |     0.39 | Using where                                                   |
|  1 | PRIMARY            | certs        | NULL       | ref    | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.little_tasks.post_id |   12 |     0.39 | Using where                                                   |
|  1 | PRIMARY            | radius       | NULL       | ref    | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.little_tasks.post_id |   12 |     3.00 | Using where                                                   |
|  1 | PRIMARY            | exp_years    | NULL       | ref    | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.little_tasks.post_id |   12 |     1.00 | Using where                                                   |
|  1 | PRIMARY            | remun_max    | NULL       | ref    | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.little_tasks.post_id |   12 |     1.00 | Using where                                                   |
|  1 | PRIMARY            | edu          | NULL       | ref    | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.little_tasks.post_id |   12 |     1.00 | Using where                                                   |
|  1 | PRIMARY            | start_date   | NULL       | ref    | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.little_tasks.post_id |   12 |     3.04 | Using where                                                   |
|  1 | PRIMARY            | moment       | NULL       | ref    | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.little_tasks.post_id |   12 |     3.04 | Using where                                                   |
|  1 | PRIMARY            | lat          | NULL       | ref    | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.little_tasks.post_id |   12 |     3.53 | Using where                                                   |
|  1 | PRIMARY            | lon          | NULL       | ref    | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.little_tasks.post_id |   12 |     3.53 | Using where                                                   |
|  1 | PRIMARY            | p            | NULL       | eq_ref | PRIMARY,type_status_date | PRIMARY  | 8       | helpeeit_helpee2.little_tasks.post_id |    1 |    24.26 | Using where; End temporary                                    |
|  3 | DEPENDENT SUBQUERY | NULL         | NULL       | NULL   | NULL                     | NULL     | NULL    | NULL                                  | NULL |     NULL | Impossible WHERE                                              |
|  2 | SUBQUERY           | spoken_lang  | NULL       | ref    | post_id,meta_key         | meta_key | 767     | const                                 |  620 |     0.02 | Using where                                                   |
+----+--------------------+--------------+------------+--------+--------------------------+----------+---------+---------------------------------------+------+----------+---------------------------------------------------------------+
14 rows in set, 2 warnings (4 min 18,58 sec)

EXPLAIN EXTENDED (ON WINDOWS/XAMPP)

+----+--------------------+--------------+----------------+--------------------------+----------+---------+-------------------------------------+------+----------+----------------------------------------------+
| id | select_type        | table        | type           | possible_keys            | key      | key_len | ref                                 | rows | filtered | Extra                                        |
+----+--------------------+--------------+----------------+--------------------------+----------+---------+-------------------------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY            | radius       | ref            | post_id,meta_key         | meta_key | 767     | const                               |  619 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | p            | eq_ref         | PRIMARY,type_status_date | PRIMARY  | 8       | helpeeit_helpee2.radius.post_id     |    1 |   100.00 | Using where                                  |
|  1 | PRIMARY            | exp_years    | ref            | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.p.ID               |    5 |   100.00 | Using where                                  |
|  1 | PRIMARY            | start_date   | ref            | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.exp_years.post_id  |    5 |   100.00 | Using where                                  |
|  1 | PRIMARY            | moment       | ref            | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.start_date.post_id |    5 |   100.00 | Using where                                  |
|  1 | PRIMARY            | lat          | ref            | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.radius.post_id     |    5 |   100.00 | Using where                                  |
|  1 | PRIMARY            | lon          | ref            | post_id,meta_key         | post_id  | 8       | helpeeit_helpee2.lat.post_id        |    5 |   100.00 | Using where                                  |
|  8 | DEPENDENT SUBQUERY | edu          | index_subquery | post_id,meta_key         | post_id  | 8       | func                                |    5 |   100.00 | Using where                                  |
|  7 | DEPENDENT SUBQUERY | certs        | index_subquery | post_id,meta_key         | post_id  | 8       | func                                |    5 |   100.00 | Using where                                  |
|  6 | DEPENDENT SUBQUERY | little_tasks | index_subquery | post_id,meta_key         | post_id  | 8       | func                                |    5 |   100.00 | Using where                                  |
|  5 | DEPENDENT SUBQUERY | exp_baby     | index_subquery | post_id,meta_key         | post_id  | 8       | func                                |    5 |   100.00 | Using where                                  |
|  4 | DEPENDENT SUBQUERY | remun_max    | index_subquery | post_id,meta_key         | post_id  | 8       | func                                |    5 |   100.00 | Using where                                  |
|  3 | DEPENDENT SUBQUERY | NULL         | NULL           | NULL                     | NULL     | NULL    | NULL                                | NULL |     NULL | Impossible WHERE                             |
|  2 | DEPENDENT SUBQUERY | spoken_lang  | index_subquery | post_id,meta_key         | post_id  | 8       | func                                |    5 |   100.00 | Using where                                  |
+----+--------------------+--------------+----------------+--------------------------+----------+---------+-------------------------------------+------+----------+----------------------------------------------+
14 rows in set, 1 warning (0.03 sec)  

可以自行查看执行时间在MAC上4分钟而在WINDOWS/Xampp上只有0.03秒

这是查询:

SELECT DISTINCT p.ID,p.post_author, lat.meta_value AS lat, lon.meta_value AS lon, radius.meta_value AS radius, start_date.meta_value AS date_interval, moment.meta_value AS day_moment, exp_years.meta_value AS exp_years
FROM `hlp_posts` p

INNER JOIN hlp_postmeta AS lat ON (p.ID = lat.post_id AND lat.meta_key = "hlp_latitude")
INNER JOIN hlp_postmeta AS lon ON (p.ID = lon.post_id AND lon.meta_key = "hlp_longitude")
INNER JOIN hlp_postmeta AS radius ON (p.ID = radius.post_id AND radius.meta_key = "hlp_resume_range_distance")
INNER JOIN hlp_postmeta AS start_date ON (p.ID = start_date.post_id AND start_date.meta_key = "hlp_resume_date")
INNER JOIN hlp_postmeta AS moment ON (p.ID = moment.post_id AND moment.meta_key = "hlp_resume_availability")
INNER JOIN hlp_postmeta AS exp_years ON (p.ID = exp_years.post_id AND exp_years.meta_key = 'resume_years_of_exp' AND (exp_years.meta_value >= 4))

WHERE
p.post_type = 'babysitter'
AND p.post_status = 'publish'

AND (
p.ID IN (SELECT spoken_lang.post_id FROM hlp_postmeta AS spoken_lang WHERE spoken_lang.meta_key = 'wpjobus_resume_languages' AND (spoken_lang.meta_value LIKE '%Inglese%' AND spoken_lang.meta_value LIKE '%Francese%' AND spoken_lang.meta_value LIKE '%Spagnolo%' AND spoken_lang.meta_value LIKE '%Tedesco%'))
OR
p.ID IN (SELECT native_lang.post_id FROM hlp_postmeta AS native_lang WHERE native_lang.meta_key = 'wpjobus_resume_native_language' AND native_lang.meta_key = 'wpjobus_resume_native_language' AND (native_lang.meta_value = 'Inglese' AND native_lang.meta_value = 'Francese' AND native_lang.meta_value = 'Spagnolo' AND native_lang.meta_value = 'Tedesco'))
)

AND p.ID IN (SELECT remun_max.post_id FROM hlp_postmeta AS remun_max WHERE remun_max.meta_key = 'wpjobus_resume_remuneration' AND remun_max.meta_value <= 5)
AND p.ID IN (SELECT exp_baby.post_id FROM hlp_postmeta AS exp_baby WHERE exp_baby.meta_key = 'wpjobus_resume_skills' AND (exp_baby.meta_value LIKE '%i:0%' AND exp_baby.meta_value LIKE '%i:1%' AND exp_baby.meta_value LIKE '%i:2%' AND exp_baby.meta_value LIKE '%i:3%'))
AND p.ID IN (SELECT little_tasks.post_id FROM hlp_postmeta AS little_tasks WHERE little_tasks.meta_key = 'hlp_resume_little_tasks' AND (little_tasks.meta_value LIKE '%i:0%' AND little_tasks.meta_value LIKE '%i:1%' AND little_tasks.meta_value LIKE '%i:2%' AND little_tasks.meta_value LIKE '%i:3%' AND little_tasks.meta_value LIKE '%i:4%' AND little_tasks.meta_value LIKE '%i:5%' AND little_tasks.meta_value LIKE '%i:6%'))
AND p.ID IN (SELECT certs.post_id FROM hlp_postmeta AS certs WHERE certs.meta_key = 'hlp_resume_prof_edu' AND (certs.meta_value LIKE '%i:0%' AND certs.meta_value LIKE '%i:2%' AND certs.meta_value LIKE '%i:3%' AND certs.meta_value LIKE '%i:7%' AND certs.meta_value LIKE '%i:11%'))
AND p.ID IN (SELECT edu.post_id FROM hlp_postmeta AS edu WHERE edu.meta_key = 'hlp_resume_edu' AND (edu.meta_value >= 3))

ORDER BY `p`.`ID` DESC  

我真的无法解释这是怎么可能的。

Answer 1

我通过将问题作为错误发布来发现的here解决方案是:

SET optimizer_search_depth = 0;

在运行真正的查询之前。
从 5.6 更新到 5.7 还提高了性能。