我有以下查询与多个多对多联结表的联接：
profile_language
profile_industry
profile_contract_type
profile_contract_hour
profile_qualification
执行大约需要 3-4 秒。当我尝试排除多对多联结表时，查询在 0.4 秒内执行。
select distinct `profiles`.*
, `locations`.`name` as `location_name`
, `candidate_view`.`last_viewed`
, CASE WHEN candidate_shortlist.profile_id IS NOT NULL THEN true ELSE false END AS shortlisted
, CASE WHEN unlocked_profiles.profile_id IS NOT NULL THEN true ELSE false END AS unlocked
from `profiles`
inner join `jobseekers` on `jobseekers`.`id` = `profiles`.`jobseeker_id`
inner join `locations` on `locations`.`id` = `profiles`.`location_id`
inner join `profile_language` on `profile_language`.`profile_id` = `profiles`.`id`
inner join `profile_industry` on `profile_industry`.`profile_id` = `profiles`.`id`
left join `profile_contract_type` on `profile_contract_type`.`profile_id` = `profiles`.`id`
left join `profile_contract_hour` on `profile_contract_hour`.`profile_id` = `profiles`.`id`
left join `profile_qualification` on `profile_qualification`.`profile_id` = `profiles`.`id`
left join (SELECT MAX(created_at) AS last_viewed
, profile_id
FROM candidate_views
WHERE recruiter_id = 43
GROUP BY profile_id ) AS candidate_view on `candidate_view`.`profile_id` = `profiles`.`id`
left JOIN (SELECT order_items.purchaseable_id as profile_id
FROM orders
INNER JOIN order_items on order_items.order_id = orders.id
INNER JOIN recruiters on recruiters.id = orders.recruiter_id
WHERE recruiters.company_id = 37
AND order_items.purchaseable_type = "App\\Profile" ) AS unlocked_profiles on `unlocked_profiles`.`profile_id` = `profiles`.`id`
left join `candidate_shortlist` on `candidate_shortlist`.`profile_id` = `profiles`.`id` and `candidate_shortlist`.`recruiter_id` = 43
where `profiles`.`searchable` = 1
and `profiles`.`deleted_at` is NULL
order by `profiles`.`id` desc limit 25 offset 0
这是解释信息：
+----+-------------+-----------------------+------------+--------+-------------------------------------------------------------------+-------------+---------+----------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+------------+--------+-------------------------------------------------------------------+-------------+---------+----------------------------------+------+----------+----------------------------------------------+
| 1 | PRIMARY | profiles | NULL | ALL | PRIMARY,profiles_jobseeker_id_unique,profiles_location_id_foreign | NULL | NULL | NULL | 2826 | 1.00 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | jobseekers | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testjobsdb.profiles.jobseeker_id | 1 | 100.00 | Using index |
| 1 | PRIMARY | locations | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testjobsdb.profiles.location_id | 1 | 100.00 | NULL |
| 1 | PRIMARY | profile_contract_type | NULL | ref | PRIMARY | PRIMARY | 4 | testjobsdb.profiles.id | 1 | 100.00 | Using index |
| 1 | PRIMARY | profile_contract_hour | NULL | ref | PRIMARY | PRIMARY | 4 | testjobsdb.profiles.id | 1 | 100.00 | Using index |
| 1 | PRIMARY | profile_qualification | NULL | ref | PRIMARY | PRIMARY | 4 | testjobsdb.profiles.id | 1 | 100.00 | Using index |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | testjobsdb.profiles.id | 2 | 100.00 | NULL |
| 1 | PRIMARY | profile_language | NULL | ref | PRIMARY | PRIMARY | 4 | testjobsdb.profiles.id | 2 | 100.00 | Using index |
| 1 | PRIMARY | order_items | NULL | ALL | order_items_order_id_foreign | NULL | NULL | NULL | 9 | 100.00 | Using where |
| 1 | PRIMARY | orders | NULL | eq_ref | PRIMARY,orders_recruiter_id_foreign | PRIMARY | 4 | testjobsdb.order_items.order_id | 1 | 100.00 | NULL |
| 1 | PRIMARY | recruiters | NULL | eq_ref | PRIMARY,recruiters_company_id_foreign | PRIMARY | 4 | testjobsdb.orders.recruiter_id | 1 | 100.00 | Using where |
| 1 | PRIMARY | candidate_shortlist | NULL | eq_ref | PRIMARY,candidate_shortlist_profile_id_foreign | PRIMARY | 8 | const,testjobsdb.profiles.id | 1 | 100.00 | Using index |
| 1 | PRIMARY | profile_industry | NULL | ref | PRIMARY | PRIMARY | 4 | testjobsdb.profiles.id | 4 | 100.00 | Using index; Distinct |
| 2 | DERIVED | candidate_views | NULL | ref | candidate_views_profile_id_foreign,Index 4 | Index 4 | 4 | const | 21 | 100.00 | Using where; Using index |
+----+-------------+-----------------------+------------+--------+-------------------------------------------------------------------+-------------+---------+----------------------------------+------+----------+----------------------------------------------+
请注意，联结表是在 php 中构建动态搜索查询所必需的，并且在上面的示例中没有显示，但是如果输入了搜索参数，它们将被添加到 where 子句中，例如：
and `profile_contract_type`.`contract_type_id` in (1,2,3,4)
此外，当我更改查询以进行计数时，它甚至需要更长的时间，大约 4-5 秒，例如
select count(distinct `profiles`.`id`) as aggregate from `profiles`...
如何优化此查询。任何帮助表示赞赏。
过度标准化。我的意思是模式具有在不需要时被规范化的数据。这通常会导致冗长的查询和较差的性能。
与其为类型、小时、资格、行业、语言等设置单独的表，不如为每个表设置一列。
也有
可能会有更多建议；修复归一化后回来。
更多的
假设表是 InnoDB 并且
id是PRIMARY KEY, ...效率低下有两个原因。
DISTINCT是不必要的，因为id值无论如何都是不同的。它所能做的就是计算行数。但这最好通过SELECT COUNT(*) FROM profiles. 此时，优化器选择最小的索引并遍历该 BTree。在一个复杂的查询中，正是这样
WHERE .. ORDER BY .. LIMIT：那么优化器会考虑使用我建议的索引。但是，它将检查可能涉及多少行。如果超过大约 20% 的表需要被访问，它会在索引上踢出并简单地进行表扫描。
LEFT JOIN与子查询...旧版本的 MySQL 在优化方面做得很糟糕
在您的情况下，这些似乎每个都返回 1 行（或者可能
NULL）。这是对所有版本都有帮助的优化。代替将该派生表移动到主
SELECT列表中：和
INDEX(recruiter_id, profile_id, created_at)您会得到相同的结果，包括
NULL案例，但它通常运行得更快。也这样做unlocked_profiles。尝试先选择所需的配置文件 ID，然后加入其他列：