我有大表"放置"，有12 558 392条记录;
当我尝试使用此表获取数据时，我遇到了性能问题(加载时间为5 秒)。 当我解释这个查询时，一切看起来都不错，但查询时间太长。
我的查询示例：
SELECT SQL_NO_CACHE om.*
FROM order_materials om
INNER JOIN material m ON om.material_id = m.id AND om.deleted = FALSE
INNER JOIN placement p ON m.id = p.material_id AND m.deleted = FALSE AND p.deleted = FALSE
INNER JOIN block b ON p.block_id = b.id AND b.deleted = FALSE
INNER JOIN orders o ON om.order_id = o.id AND o.deleted = FALSE
INNER JOIN product pr ON pr.mediaPlan_id = p.mediaplan_id
WHERE
b.advTable_id = 139
AND p.date >= '2018-03-01 00:00:00' AND p.date <= '2018-04-01 00:00:00'
GROUP BY om.material_id;
解释：
+----+-------------+-------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+---------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+---------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | b | NULL | ref | PRIMARY,FK597C48D47B04A3 | FK597C48D47B04A3 | 5 | const | 455 | 50.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | NULL | ref | FK6ADE12E521DC3251,FK6ADE12E59B1CA2F1,FK6ADE12E5AFA9B543,date_ind,placement_b,placement_material_id_mediaplan_id_index,placement_material_id,placement_material_id_mediaplan_id_order_id_block_id_index,block_id_date_ind | FK6ADE12E521DC3251 | 5 | openmarket.b.id | 135 | 0.82 | Using where |
| 1 | SIMPLE | pr | NULL | ref | FKED8DCCEF9B1CA2F1 | FKED8DCCEF9B1CA2F1 | 5 | openmarket.p.mediaplan_id | 1 | 100.00 | Using index |
| 1 | SIMPLE | m | NULL | eq_ref | PRIMARY | PRIMARY | 4 | openmarket.p.material_id | 1 | 50.00 | Using where |
| 1 | SIMPLE | om | NULL | ref | FK_order_materials_1,FK_order_materials_2 | FK_order_materials_2 | 4 | openmarket.p.material_id | 2 | 50.00 | Using where |
| 1 | SIMPLE | o | NULL | eq_ref | PRIMARY | PRIMARY | 4 | openmarket.om.order_id | 1 | 50.00 | Using where |
+----+-------------+-------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+---------------------------+------+----------+----------------------------------------------+
显示创建表order_materials：
CREATE TABLE `order_materials` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`material_id` int(11) NOT NULL,
`deleted` bit(1) NOT NULL DEFAULT b'0',
`created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_mod_user_id` int(11) DEFAULT NULL,
`placements_count` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `FK_order_materials_1` (`order_id`),
KEY `FK_order_materials_2` (`material_id`),
CONSTRAINT `FK_order_materials_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
CONSTRAINT `FK_order_materials_2` FOREIGN KEY (`material_id`) REFERENCES `material` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=251369 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
显示创建表放置;
CREATE TABLE `placement` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`changeDate` datetime DEFAULT NULL,
`date` datetime NOT NULL,
`plannedPosition` tinyint(4) DEFAULT NULL,
`realPosition` tinyint(4) DEFAULT NULL,
`positionWithPolitics` tinyint(4) DEFAULT NULL,
`material_id` int(11) DEFAULT NULL,
`mediaplan_id` int(11) NOT NULL,
`block_id` int(11) DEFAULT NULL,
`visible` bit(1) NOT NULL,
`blockStartTime` datetime DEFAULT NULL,
`price` float DEFAULT NULL,
`pricedPrice` float DEFAULT NULL,
`actualStartTime` datetime DEFAULT NULL,
`playedPosition` tinyint(4) DEFAULT NULL,
`status` int(11) DEFAULT NULL,
`played_material_id` int(11) DEFAULT NULL,
`deleted` bit(1) NOT NULL DEFAULT b'0',
`created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_mod_user_id` int(11) DEFAULT NULL,
`conflict_status_lid` int(11) DEFAULT NULL COMMENT 'lookup category placement_conflict',
`conflict_by_type_in_block` bit(1) NOT NULL DEFAULT b'0',
`conflict_by_type_near` bit(1) NOT NULL DEFAULT b'0',
`conflict_by_time_overflow` bit(1) NOT NULL DEFAULT b'0',
`conflict_by_position` bit(1) NOT NULL DEFAULT b'0',
`order_id` int(11) DEFAULT NULL,
`order_status_lid` int(11) DEFAULT NULL,
`play_type_lid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK6ADE12E521DC3251` (`block_id`),
KEY `FK6ADE12E59B1CA2F1` (`mediaplan_id`),
KEY `FK6ADE12E5AFA9B543` (`material_id`),
KEY `blockstarttime` (`blockStartTime`),
KEY `date_ind` (`date`),
KEY `bst_rp_ind` (`blockStartTime`,`realPosition`),
KEY `status_ind` (`status`),
KEY `FK_played_material` (`played_material_id`),
KEY `FK_placement_1` (`conflict_status_lid`),
KEY `FK_placement_2` (`order_id`),
KEY `FK_placement_3` (`order_status_lid`),
KEY `FK_placement_4` (`play_type_lid`),
KEY `placement_b` (`date`,`blockStartTime`,`block_id`,`plannedPosition`),
KEY `placement_material_id_mediaplan_id_index` (`material_id`,`mediaplan_id`),
KEY `placement_material_id` (`material_id`,`mediaplan_id`,`order_id`),
KEY `placement_material_id_mediaplan_id_order_id_block_id_index` (`material_id`,`mediaplan_id`,`order_id`,`block_id`),
KEY `block_id_date_ind` (`block_id`,`date`),
CONSTRAINT `FK6ADE12E521DC3251` FOREIGN KEY (`block_id`) REFERENCES `block` (`id`),
CONSTRAINT `FK6ADE12E59B1CA2F1` FOREIGN KEY (`mediaplan_id`) REFERENCES `mediaplan` (`id`),
CONSTRAINT `FK6ADE12E5AFA9B543` FOREIGN KEY (`material_id`) REFERENCES `material` (`id`),
CONSTRAINT `FK_placement_1` FOREIGN KEY (`conflict_status_lid`) REFERENCES `lookups` (`id`),
CONSTRAINT `FK_placement_2` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
CONSTRAINT `FK_placement_3` FOREIGN KEY (`order_status_lid`) REFERENCES `lookups` (`id`),
CONSTRAINT `FK_placement_4` FOREIGN KEY (`play_type_lid`) REFERENCES `lookups` (`id`),
CONSTRAINT `FK_played_material` FOREIGN KEY (`played_material_id`) REFERENCES `played_material` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12578822 DEFAULT CHARSET=utf8 COMMENT='Розміщення рекламного матеріалу')
) ENGINE=InnoDB AUTO_INCREMENT=251369 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
使用存在重写查询后，我有下一个解释
+----+--------------------+-------+------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+---------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+---------------------------+--------+----------+-------------+
| 1 | PRIMARY | om | NULL | ALL | NULL | NULL | NULL | NULL | 243300 | 50.00 | Using where |
| 6 | DEPENDENT SUBQUERY | o | NULL | eq_ref | PRIMARY | PRIMARY | 4 | openmarket.om.order_id | 1 | 50.00 | Using where |
| 2 | DEPENDENT SUBQUERY | m | NULL | eq_ref | PRIMARY | PRIMARY | 4 | openmarket.om.material_id | 1 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | p | NULL | ref | FK6ADE12E5AFA9B543,date_ind,placement_b,placement_material_id_mediaplan_id_index,placement_material_id,placement_material_id_mediaplan_id_order_id_block_id_index | placement_material_id | 5 | openmarket.m.id | 101 | 0.82 | Using where |
| 5 | DEPENDENT SUBQUERY | pr | NULL | ref | FKED8DCCEF9B1CA2F1 | FKED8DCCEF9B1CA2F1 | 5 | openmarket.p.mediaplan_id | 1 | 100.00 | Using index |
| 4 | DEPENDENT SUBQUERY | b | NULL | eq_ref | PRIMARY,FK597C48D47B04A3 | PRIMARY | 4 | openmarket.p.block_id | 1 | 5.00 | Using where |
+----+--------------------+-------+------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+---------------------------+--------+----------+-------------+
据我从您的架构中了解，您正在寻找一个不同的"order_materials"列表，以根据其他列出的表进行过滤。 根据我的经验，您无法使用联接过滤/分组/区分结果而不会遭受"使用临时 - 使用文件排序"的困扰
无论如何，在您的情况下，由于您看起来不需要其他表中的值，我相信可以重写您的查询，删除所有连接并仅使用 EXISTS 子句。
我会尝试这样的事情：
SELECT SQL_NO_CACHE om.*
FROM order_materials om
where
om.deleted = false
and
exists (
select 1
from material m
where exists
(select 1 from placement p
where m.id = p.material_id
AND m.deleted = FALSE
AND p.deleted = FALSE
and p.date >= '2018-03-01 00:00:00' AND p.date <= '2018-04-01 00:00:00'
and exists (select 1 from block b
where p.block_id = b.id
AND b.deleted = FALSE
and b.advTable_id = 139
)
and exists (select 1 from product pr
where pr.mediaPlan_id = p.mediaplan_id)
)
and m.id=om.material_id
)
and exists (select 1 from orders o
where om.order_id = o.id
AND o.deleted = FALSE)
此类查询的解释计划是：
+------+--------------------+-------------+--------+-----------------------------------+--------------+---------+--------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+-------------+--------+-----------------------------------+--------------+---------+--------------------+------+-------------+
| 1 | PRIMARY | om | ALL | order_id,material_id | NULL | NULL | NULL | 1 | Using where |
| 1 | PRIMARY | m | eq_ref | PRIMARY | PRIMARY | 4 | abc.om.material_id | 1 | Using where |
| 1 | PRIMARY | o | eq_ref | PRIMARY | PRIMARY | 4 | abc.om.order_id | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | p | ALL | material_id,block_id,mediaplan_id | NULL | NULL | NULL | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | <subquery5> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | |
| 3 | DEPENDENT SUBQUERY | b | eq_ref | PRIMARY,advTable_id | PRIMARY | 4 | abc.p.block_id | 1 | Using where |
| 5 | MATERIALIZED | pr | index | mediaPlan_id | mediaPlan_id | 5 | NULL | 1 | Using index |
+------+--------------------+-------------+--------+-----------------------------------+--------------+---------+--------------------+------+-------------+
如您所见，没有临时的，没有文件排序。因此，由于 exists 是一个布尔运算符，因此您不会像加入时那样收到重复项。
最后，请非常小心：我写了这个答案，你需要检查嵌套的存在是否与你的预期结果一致，这不是最终的解决方案，而只是对我目前所学到的知识的共享提示。
order_materials似乎是一个多：多映射表。 架构效率低下。 摆脱id并进行此处建议的其他更改。
block需要INDEX(advTable_id, deleted, id)
placement需要INDEX(material_id, deleted, date)
有几个冗余索引。 遵循以下规则：如果您有INDEX(a, b)，则不需要INDEX(a)。
key_len = 5通常指的是INT NULL- 检查它们是否应该INT NOT NULL。