在我的MySQL数据库(v.7.31-34，使用InnoDB)中，我试图优化以下查询的性能：
SELECT DISTINCT SQL_CALC_FOUND_ROWS `story_allocation_days`.*
FROM `story_allocation_days`
INNER JOIN `workspaces`
ON `workspaces`.`id` = `story_allocation_days`.`workspace_id`
INNER JOIN `participations`
ON `participations`.`workspace_id` = `workspaces`.`id`
INNER JOIN `assignments`
ON `assignments`.`id` = `story_allocation_days`.`assignment_id`
WHERE (story_allocation_days.deleted_at is null)
AND `workspaces`.`is_budgeted` = TRUE
AND (participations.account_id = 5071)
AND (participations.access_integer >= 30)
AND (participations.type = 'MavenParticipation' OR workspaces.account_id = 5071)
AND (assignments.assignee_id IS NOT NULL);
99822 rows in set (5.35 sec)
上面的EXPLAIN：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | story_allocation_days | NULL | ALL | index_story_allocation_days_on_workspace_id,index_story_allocation_days_on_assignment_id_and_date,index_story_allocation_days_on_assignment_id | NULL | NULL | NULL | 430531 | 10.00 | Using where; Using temporary |
| 1 | SIMPLE | workspaces | NULL | eq_ref | PRIMARY,index_workspaces_on_account_id | PRIMARY | 4 | bm_rpm.story_allocation_days.workspace_id | 1 | 10.00 | Using where; Distinct |
| 1 | SIMPLE | assignments | NULL | eq_ref | PRIMARY,index_assignments_on_assignee_id | PRIMARY | 4 | bm_rpm.story_allocation_days.assignment_id | 1 | 50.00 | Using where; Distinct |
| 1 | SIMPLE | participations | NULL | ref | index_participations_on_account_id_and_workspace_id,index_participations_on_workspace_id_and_user_id,index_participations_for_sad_api_index,index_participations_on_account_id,index_participations_on_workspace_id | index_participations_for_sad_api_index | 10 | bm_rpm.story_allocation_days.workspace_id,const | 7 | 33.33 | Using where; Using index; Distinct |
这是上面的FORMAT=JSON版本：
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "153230.79"
},
"duplicates_removal": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "story_allocation_days",
"access_type": "ALL",
"possible_keys": [
"index_story_allocation_days_on_workspace_id",
"index_story_allocation_days_on_assignment_id_and_date",
"index_story_allocation_days_on_assignment_id"
],
"rows_examined_per_scan": 430268,
"rows_produced_per_join": 43026,
"filtered": "10.00",
"cost_info": {
"read_cost": "80548.24",
"eval_cost": "8605.36",
"prefix_cost": "89153.60",
"data_read_per_join": "3M"
},
"used_columns": [
"id",
"assignment_id",
"story_id",
"workspace_id",
"account_id",
"current",
"date",
"minutes",
"created_at",
"updated_at",
"deleted_at",
"cost_amount_in_cents",
"bill_amount_in_cents",
"cost_rate_in_cents",
"bill_rate_in_cents"
],
"attached_condition": "isnull(`bm_rpm`.`story_allocation_days`.`deleted_at`)"
}
},
{
"table": {
"table_name": "workspaces",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"index_workspaces_on_account_id"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"bm_rpm.story_allocation_days.workspace_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 4302,
"filtered": "10.00",
"distinct": true,
"cost_info": {
"read_cost": "43026.80",
"eval_cost": "860.54",
"prefix_cost": "140785.76",
"data_read_per_join": "21M"
},
"used_columns": [
"id",
"is_budgeted",
"account_id"
],
"attached_condition": "(`bm_rpm`.`workspaces`.`is_budgeted` = TRUE)"
}
},
{
"table": {
"table_name": "assignments",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"index_assignments_on_assignee_id"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"bm_rpm.story_allocation_days.assignment_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 2151,
"filtered": "50.00",
"distinct": true,
"cost_info": {
"read_cost": "4302.68",
"eval_cost": "430.27",
"prefix_cost": "145948.98",
"data_read_per_join": "134K"
},
"used_columns": [
"id",
"assignee_id"
],
"attached_condition": "(`bm_rpm`.`assignments`.`assignee_id` is not null)"
}
},
{
"table": {
"table_name": "participations",
"access_type": "ref",
"possible_keys": [
"index_participations_on_account_id_and_workspace_id",
"index_participations_on_workspace_id_and_user_id",
"index_participations_for_sad_api_index",
"index_participations_on_account_id",
"index_participations_on_workspace_id"
],
"key": "index_participations_for_sad_api_index",
"used_key_parts": [
"workspace_id",
"account_id"
],
"key_length": "10",
"ref": [
"bm_rpm.story_allocation_days.workspace_id",
"const"
],
"rows_examined_per_scan": 7,
"rows_produced_per_join": 5537,
"filtered": "33.33",
"using_index": true,
"distinct": true,
"cost_info": {
"read_cost": "3959.11",
"eval_cost": "1107.46",
"prefix_cost": "153230.79",
"data_read_per_join": "11M"
},
"used_columns": [
"id",
"workspace_id",
"type",
"account_id",
"access_integer"
],
"attached_condition": "((`bm_rpm`.`participations`.`access_integer` >= 30) and ((`bm_rpm`.`participations`.`type` = 'MavenParticipation') or (`bm_rpm`.`workspaces`.`account_id` = 5071)))"
}
}
]
}
}
} |
我试着找到一个索引，它将把全表扫描改为更有效的搜索类型。以下是索引：
mysql> show indexes in story_allocation_days;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
| story_allocation_days | 0 | PRIMARY | 1 | id | A | 418853 | NULL | NULL | | BTREE | | |
| story_allocation_days | 1 | index_story_allocation_days_on_workspace_id | 1 | workspace_id | A | 295 | NULL | NULL | | BTREE | | |
| story_allocation_days | 1 | index_story_allocation_days_on_assignment_id_and_date | 1 | assignment_id | A | 42533 | NULL | NULL | | BTREE | | |
| story_allocation_days | 1 | index_story_allocation_days_on_assignment_id_and_date | 2 | date | A | 418853 | NULL | NULL | | BTREE | | |
| story_allocation_days | 1 | index_story_allocation_days_on_account_id | 1 | account_id | A | 41 | NULL | NULL | | BTREE | | |
| story_allocation_days | 1 | index_story_allocation_days_on_story_id_and_date | 1 | story_id | A | 2519 | NULL | NULL | | BTREE | | |
| story_allocation_days | 1 | index_story_allocation_days_on_story_id_and_date | 2 | date | A | 83246 | NULL | NULL | | BTREE | | |
| story_allocation_days | 1 | index_story_allocation_days_on_date | 1 | date | A | 740 | NULL | NULL | | BTREE | | |
| story_allocation_days | 1 | index_story_allocation_days_on_created_at | 1 | created_at | A | 6977 | NULL | NULL | | BTREE | | |
| story_allocation_days | 1 | index_story_allocation_days_on_updated_at | 1 | updated_at | A | 7681 | NULL | NULL | | BTREE | | |
| story_allocation_days | 1 | index_story_allocation_days_on_assignment_id | 1 | assignment_id | A | 43891 | NULL | NULL | | BTREE | | |
| story_allocation_days | 1 | index_story_allocation_days_on_story_id | 1 | story_id | A | 2494 | NULL | NULL | | BTREE | | |
| story_allocation_days | 1 | sad_deleted_at | 1 | deleted_at | A | 225 | NULL | NULL | YES | BTREE | | |
我在story_allocation_days上尝试了USE INDEX(index_story_allocation_days_on_assignment_id)，但这实际上比最初的查询稍微慢一些：
99822 rows in set (5.99 sec)
根据直觉，我认为WHERE (story_allocation_days.deleted_at is null)可能会绊倒优化器，所以我尝试创建sad_deleted_at索引，并将其deleted_at列的所有具有NULL的行更新为1970-01-01 00:00:00，因为NULL值的存在可能会影响索引性能。然而，这导致了性能也比原来的差
SELECT DISTINCT SQL_CALC_FOUND_ROWS `story_allocation_days`.*
FROM `story_allocation_days`
INNER JOIN `workspaces`
ON `workspaces`.`id` = `story_allocation_days`.`workspace_id`
INNER JOIN `participations`
ON `participations`.`workspace_id` = `workspaces`.`id`
INNER JOIN `assignments`
ON `assignments`.`id` = `story_allocation_days`.`assignment_id`
WHERE (story_allocation_days.deleted_at = '1970-01-01 00:00:00')
AND `workspaces`.`is_budgeted` = TRUE
AND (participations.account_id = 5071)
AND (participations.access_integer >= 30)
AND (participations.type = 'MavenParticipation' OR workspaces.account_id = 5071)
AND (assignments.assignee_id IS NOT NULL);
99822 rows in set (6.12 sec)
以下是相应的EXPLAIN语句：
| 1 | SIMPLE | story_allocation_days | NULL | ref | index_story_allocation_days_on_workspace_id,index_story_allocation_days_on_assignment_id_and_date,index_story_allocation_days_on_assignment_id,sad_deleted_at | sad_deleted_at | 6 | const | 210391 | 100.00 | Using temporary |
| 1 | SIMPLE | workspaces | NULL | eq_ref | PRIMARY,index_workspaces_on_account_id | PRIMARY | 4 | bm_rpm.story_allocation_days.workspace_id | 1 | 10.00 | Using where; Distinct |
| 1 | SIMPLE | assignments | NULL | eq_ref | PRIMARY,index_assignments_on_assignee_id | PRIMARY | 4 | bm_rpm.story_allocation_days.assignment_id | 1 | 50.00 | Using where; Distinct |
| 1 | SIMPLE | participations | NULL | ref | index_participations_on_account_id_and_workspace_id,index_participations_on_workspace_id_and_user_id,index_participations_for_sad_api_index,index_participations_on_account_id,index_participations_on_workspace_id | index_participations_for_sad_api_index | 10 | bm_rpm.story_allocation_days.workspace_id,const | 7 | 33.33 | Using where; Using index; Distinct |
再次，上述FORMAT=JSON版本：
# NEW:
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "366895.66"
},
"duplicates_removal": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "story_allocation_days",
"access_type": "ref",
"possible_keys": [
"index_story_allocation_days_on_workspace_id",
"index_story_allocation_days_on_assignment_id_and_date",
"index_story_allocation_days_on_assignment_id",
"sad_deleted_at"
],
"key": "sad_deleted_at",
"used_key_parts": [
"deleted_at"
],
"key_length": "6",
"ref": [
"const"
],
"rows_examined_per_scan": 210552,
"rows_produced_per_join": 210552,
"filtered": "100.00",
"cost_info": {
"read_cost": "11223.00",
"eval_cost": "42110.40",
"prefix_cost": "53333.40",
"data_read_per_join": "16M"
},
"used_columns": [
"id",
"assignment_id",
"story_id",
"workspace_id",
"account_id",
"current",
"date",
"minutes",
"created_at",
"updated_at",
"deleted_at",
"cost_amount_in_cents",
"bill_amount_in_cents",
"cost_rate_in_cents",
"bill_rate_in_cents"
]
}
},
{
"table": {
"table_name": "workspaces",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"index_workspaces_on_account_id"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"bm_rpm.story_allocation_days.workspace_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 21055,
"filtered": "10.00",
"distinct": true,
"cost_info": {
"read_cost": "210552.00",
"eval_cost": "4211.04",
"prefix_cost": "305995.80",
"data_read_per_join": "105M"
},
"used_columns": [
"id",
"is_budgeted",
"account_id"
],
"attached_condition": "(`bm_rpm`.`workspaces`.`is_budgeted` = TRUE)"
}
},
{
"table": {
"table_name": "assignments",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"index_assignments_on_assignee_id"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"bm_rpm.story_allocation_days.assignment_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 10527,
"filtered": "50.00",
"distinct": true,
"cost_info": {
"read_cost": "21055.20",
"eval_cost": "2105.52",
"prefix_cost": "331262.04",
"data_read_per_join": "657K"
},
"used_columns": [
"id",
"assignee_id"
],
"attached_condition": "(`bm_rpm`.`assignments`.`assignee_id` is not null)"
}
},
{
"table": {
"table_name": "participations",
"access_type": "ref",
"possible_keys": [
"index_participations_on_account_id_and_workspace_id",
"index_participations_on_workspace_id_and_user_id",
"index_participations_for_sad_api_index",
"index_participations_on_account_id",
"index_participations_on_workspace_id"
],
"key": "index_participations_for_sad_api_index",
"used_key_parts": [
"workspace_id",
"account_id"
],
"key_length": "10",
"ref": [
"bm_rpm.story_allocation_days.workspace_id",
"const"
],
"rows_examined_per_scan": 7,
"rows_produced_per_join": 27096,
"filtered": "33.33",
"using_index": true,
"distinct": true,
"cost_info": {
"read_cost": "19373.95",
"eval_cost": "5419.35",
"prefix_cost": "366895.66",
"data_read_per_join": "55M"
},
"used_columns": [
"id",
"workspace_id",
"type",
"account_id",
"access_integer"
],
"attached_condition": "((`bm_rpm`.`participations`.`access_integer` >= 30) and ((`bm_rpm`.`participations`.`type` = 'MavenParticipation') or (`bm_rpm`.`workspaces`.`account_id` = 5071)))"
}
}
]
}
}
} |
尽管story_allocation_days的filter列从10.0变为100.0，检查的行数从400000+变为200000ish，但我很困惑为什么查询时间没有改善(事实上略有恶化)。
有人能理解为什么索引没有改善查询时间吗？
编辑：
我尝试从原始查询中删除SQL_CALC_FOUND_ROWS，但99k行仍然需要5.51秒。下面的EXPLAIN，JSON格式(因为这个问题的字符已经用完了)：
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "153230.79"
},
"duplicates_removal": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "story_allocation_days",
"access_type": "ALL",
"possible_keys": [
"index_story_allocation_days_on_workspace_id",
"index_story_allocation_days_on_assignment_id_and_date",
"index_story_allocation_days_on_assignment_id"
],
"rows_examined_per_scan": 430268,
"rows_produced_per_join": 43026,
"filtered": "10.00",
"cost_info": {
"read_cost": "80548.24",
"eval_cost": "8605.36",
"prefix_cost": "89153.60",
"data_read_per_join": "3M"
},
"used_columns": [
"id",
"assignment_id",
"story_id",
"workspace_id",
"account_id",
"current",
"date",
"minutes",
"created_at",
"updated_at",
"deleted_at",
"cost_amount_in_cents",
"bill_amount_in_cents",
"cost_rate_in_cents",
"bill_rate_in_cents"
],
"attached_condition": "isnull(`bm_rpm`.`story_allocation_days`.`deleted_at`)"
}
},
{
"table": {
"table_name": "workspaces",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"index_workspaces_on_account_id"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"bm_rpm.story_allocation_days.workspace_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 4302,
"filtered": "10.00",
"distinct": true,
"cost_info": {
"read_cost": "43026.80",
"eval_cost": "860.54",
"prefix_cost": "140785.76",
"data_read_per_join": "21M"
},
"used_columns": [
"id",
"is_budgeted",
"account_id"
],
"attached_condition": "(`bm_rpm`.`workspaces`.`is_budgeted` = TRUE)"
}
},
{
"table": {
"table_name": "assignments",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"index_assignments_on_assignee_id"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"bm_rpm.story_allocation_days.assignment_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 2151,
"filtered": "50.00",
"distinct": true,
"cost_info": {
"read_cost": "4302.68",
"eval_cost": "430.27",
"prefix_cost": "145948.98",
"data_read_per_join": "134K"
},
"used_columns": [
"id",
"assignee_id"
],
"attached_condition": "(``bm_rpm`.`assignments`.`assignee_id` is not null)"
}
},
{
"table": {
"table_name": "participations",
"access_type": "ref",
"possible_keys": [
"index_participations_on_account_id_and_workspace_id",
"index_participations_on_workspace_id_and_user_id",
"index_participations_for_sad_api_index",
"index_participations_on_account_id",
"index_participations_on_workspace_id"
],
"key": "index_participations_for_sad_api_index",
"used_key_parts": [
"workspace_id",
"account_id"
],
"key_length": "10",
"ref": [
"bm_rpm.story_allocation_days.workspace_id",
"const"
],
"rows_examined_per_scan": 7,
"rows_produced_per_join": 5537,
"filtered": "33.33",
"using_index": true,
"distinct": true,
"cost_info": {
"read_cost": "3959.11",
"eval_cost": "1107.46",
"prefix_cost": "153230.79",
"data_read_per_join": "11M"
},
"used_columns": [
"id",
"workspace_id",
"type",
"account_id",
"access_integer"
],
"attached_condition": "((`bm_rpm`.`participations`.`access_integer` >= 30) and ((`bm_rpm`.`participations`.`type` = 'MavenParticipation') or (`bm_rpm`.`workspaces`.`account_id` = 5071)))"
}
}
]
}
}
} |
编辑#2：
这是story_allocation_days:的SHOW CREATE TABLE
| story_allocation_days | CREATE TABLE `story_allocation_days` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`assignment_id` int(11) NOT NULL,
`story_id` int(11) NOT NULL,
`workspace_id` int(11) NOT NULL,
`account_id` int(11) NOT NULL,
`current` tinyint(1) NOT NULL,
`date` date NOT NULL,
`minutes` int(11) NOT NULL DEFAULT '0',
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`deleted_at` datetime DEFAULT NULL,
`cost_amount_in_cents` bigint(20) DEFAULT NULL,
`bill_amount_in_cents` bigint(20) DEFAULT NULL,
`cost_rate_in_cents` bigint(20) DEFAULT NULL,
`bill_rate_in_cents` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_story_allocation_days_on_workspace_id` (`workspace_id`),
KEY `index_story_allocation_days_on_assignment_id_and_date` (`assignment_id`,`date`),
KEY `index_story_allocation_days_on_account_id` (`account_id`),
KEY `index_story_allocation_days_on_story_id_and_date` (`story_id`,`date`),
KEY `index_story_allocation_days_on_date` (`date`),
KEY `index_story_allocation_days_on_created_at` (`created_at`),
KEY `index_story_allocation_days_on_updated_at` (`updated_at`),
KEY `index_story_allocation_days_on_assignment_id` (`assignment_id`),
KEY `index_story_allocation_days_on_story_id` (`story_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9646396 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
我将提供以下内容。重写从参与者开始的查询，因为您首先从该粒度开始；悲伤的"；条目。添加MySQL关键字"；STRIGHT_JOIN"；告诉引擎。。。不要为我着想，照我写的去做……因为你在做"；Distinct"；，做着与众不同的"*"可能正在评估所有列的唯一性。如果你只输入"；悲伤的id"；(假设每个表都有"Id"作为其自己的主键。我在下面把它作为[whateverItsUniqueKeyIdIs]，但认为它可能只是sad.id，并在下面进行相应的更改。如果这是行的唯一值，则引擎不需要去实际的数据行页面来确定其清晰度。
接下来是索引。通过在表上设置多列复合索引来覆盖WHERE的联接和条件，也将有助于引擎直接基于索引进行查询，而不是转到原始数据页。不要试图让表在单独的列上有索引，因为只有一个表不会像那些涵盖你想要的标准的表那样优化。
Table Index
Participants ( account_id, access_integer, type, workspace_id)
Workspaces ( id, is_budgeted, account_id )
Story_Allocation_Days ( workspace_id, deleted_at, assignment_id, [whateverItsUniqueKeyIdIs]
Assignments ( id, assignee_id )
最后是实际查询。
SELECT STRAIGHT_JOIN DISTINCT
SQL_CALC_FOUND_ROWS sad.[whateverItsUniqueKeyIdIs]
FROM
participations p
INNER JOIN workspaces ws
ON p.workspace_id = ws.id
AND ws.is_budgeted = TRUE
INNER JOIN story_allocation_days sad
ON p.workspace_id = sad.workspace_id
AND sad.deleted_at is null
INNER JOIN assignments a
ON sad.assignment_id = a.id
AND a.assignee_id IS NOT NULL
where
p.account_id = 5071
AND p.access_integer >= 30
AND ( p.type = 'MavenParticipation'
OR ws.account_id = 5071)
唯一可能的另一个致命拖累是"；或者ws.Account_id＝5701"；，但仍应迅速，因为主要依据是参与者限制的标准，而不是所有账户。
对评论的回应
关于查询的直觉。数据库可能充满膨胀，有时会隐藏在你搜索的杂草中。作为数据库的幕后人员，您更清楚数据中的粒度在哪里。
我试着在根"处查看查询；我想要什么"；。在您的情况下，您想要特定帐户的所有participants(带有一些次要的其他标准)。把它放在我的脑海里，它就成了我的第一张桌子。现在，看看那些帮助我只获得一个组件FIRST的索引。然后，我向外加入其他部分，比如你的workspace和story_allocation_days以及作业。我将它们的限制性标准直接应用于它们的JOIN条件；OR"；由于ws.account_id的比较上下文，但核心仍然始终是p.account_id = 5071 AND p.access_integer >= 30
现在，拥有合适/有效的索引是另一回事，而将它们按正确的顺序/位置排列可能会产生巨大的影响，不仅要有一个字段的多个索引，还要有更高效的多列单索引——这取决于您最频繁运行的查询类型。
正如我在其他帖子的答案中所描述的，以及你的数据场景。您最初是从story_allocation_days表开始的。它必须对每个帐户的所有内容进行彻底的检查，甚至在到达参与者表之前，然后再到所有工作站和任务。引擎不知道您想要什么，因此具有有效的索引。
从问题Participants中的键表开始，知道您想要一个特定的account_id和access_integer值，这就是您的起点。想象一个盒子的房间，它们保存着所有参与者的数据。房间里的每个盒子上都有第一件东西；账户ID"；在盒子的侧面，所有盒子都是按顺序排列的。你可以有1000个盒子，你可以直接运行来找到你想要的一个帐户ID。你已经消除了房间其他地方的所有盒子。现在，打开该框，在其中，它们由access_integer从1-？？进行预排序？？。所以现在，你翻过去，找到31，你就完成了。
只有到那时，你才有了一个简短的清单，可以找到其余的细节，而且你甚至没有查看原始数据页。在每个页面的顶部都有一个关于类型和工作区ID的注释，因为这就是我建议索引的方式。同样，不必进入文档翻页来查看类型和工作空间，这些内容都写在页面顶部以供快速参考。这些字段用于联接到下一级别的表。所有这些都是在没有获取基础记录的原始数据的情况下完成的。
通过使用direct_join子句，您已经从引擎中消除了查询的所有繁重工作，现在它将只对那些二级表执行具有适当索引的简单联接。HTH。
OR(通常)会扼杀性能。UNION是一种变通方法：
( SELECT sad.*
FROM `story_allocation_days` AS sad
INNER JOIN `workspaces` AS w ON w.`id` = sad.`workspace_id`
INNER JOIN `participations` AS p ON p.`workspace_id` = w.`id`
INNER JOIN `assignments` AS a ON a.`id` = sad.`assignment_id`
WHERE sad.deleted_at = '1970-01-01'
AND w.`is_budgeted` = TRUE
AND p.account_id = 5071
AND p.access_integer >= 30
AND p.type = 'MavenParticipation'
AND a.assignee_id IS NOT NULL
)
UNION DISTINCT
( SELECT sad.*
FROM `story_allocation_days` AS sad
INNER JOIN `workspaces` AS w ON w.`id` = sad.`workspace_id`
INNER JOIN `participations` AS p ON p.`workspace_id` = w.`id`
INNER JOIN `assignments` AS a ON a.`id` = sad.`assignment_id`
WHERE sad.deleted_at = '1970-01-01'
AND w.`is_budgeted` = TRUE
AND w.account_id = 5071
AND p.account_id = 5071
AND p.access_integer >= 30
AND a.assignee_id IS NOT NULL
)
这将需要这些。这些索引中列的顺序很重要。(我假设每个表都有PRIMARY KEY(id)。)
w: INDEX(account_id, is_budgeted)
p: INDEX(account_id, type, access_integer, workspace_id)
p: INDEX(workspace_id, account_id, access_integer)
sad: INDEX(workspace_id, deleted_at)
sad: INDEX(assignment_id, deleted_at)
有一些额外的索引，因为我不知道Optimizer访问表的顺序。(这取决于数据。)
我删除了SQL_CALC_FOUND_ROWS，因为没有LIMIT是不必要的。
让我知道相同的行在两个SELECTs中显示是否常见，以及story_allocation_days中是否有许多大列(例如TEXT或BLOB)。在这种情况下，我想修改查询，以避免大量运输：
SELECT sad2.*
FROM ( ( SELECT sad.id
FROM ... (1st 4-way join plus WHERE)
UNION DISTINCT
( SELECT sad.id
FROM ... (2nd 4-way join plus WHERE)
) ) AS u
JOIN `story_allocation_days` AS sad2 USING(id);
这可能会更快，因为只在id周围拖动，直到用UNION完成。