当我选择很多列时,我有一个查询非常慢,但当我只选择查询正在使用的索引中存在的列时,速度会大大加快。这让我相信,由于键查找,查询正在变慢,但查询执行计划中未显示键查找。
我知道这存在于 SQL Server 中,但是否可以在 MySQL 5.7 的查询执行计划中显示键查找?
慢查询:
select c.*
from composite c
inner join creative_library cl on c.creative_library_id=cl.creative_library_id
inner join ad_format a on cl.ad_format_id=a.ad_format_id
where c.deleted=0 and cl.deleted=0 order by c.created_on desc limit 25;
选择类型 | 桌子 | 类型 | 钥匙 | 密钥长度 | 参考 | 行 | 过滤 | 额外的 |
---|---|---|---|---|---|---|---|---|
简单的 | 有创造力的 | 指数 | creative_library__creative_library_id__deleted__ad_format | 9 | 无效的 | 748 | 10 | 在哪里使用;使用索引;使用临时的;使用文件排序 |
简单的 | 广告格式 | eq_ref | 基本的 | 4个 | creative_library.ad_format_id | 1个 | 100 | 使用索引 |
简单的 | 合成的 | 参考 | composite__creative_library_id__deleted__created_on_desc__id | 5个 | creative_library.creative_library_id, 常量 | 2179 | 100 | 无效的 |
更快的查询:
select c.created_on, c.creative_library_id, c.deleted
from composite c
inner join creative_library cl on c.creative_library_id=cl.creative_library_id
inner join ad_format a on cl.ad_format_id=a.ad_format_id
where c.deleted=0 and cl.deleted=0 order by c.created_on desc limit 25
更快的查询具有相同的执行计划。
我在表上有一个索引composite
,它涵盖了更快查询中的所有字段。
CREATE TABLE `composite` (
`placement_cue_id` int(10) unsigned DEFAULT NULL,
`creative_library_id` int(10) unsigned NOT NULL,
`deleted` tinyint(1) NOT NULL,
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`entity_key` varchar(64) NOT NULL DEFAULT '',
`publisher_id` int(10) unsigned DEFAULT NULL,
`composite_job_id` varchar(36) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
UNIQUE KEY `entity_key` (`entity_key`),
UNIQUE KEY `placement_cue_id` (`placement_cue_id`,`creative_library_id`),
KEY `publisher_id` (`publisher_id`),
KEY `composite_job_id` (`composite_job_id`),
KEY `composite__creative_library_id__deleted__created_on_desc__id` (`creative_library_id`,`deleted`,`created_on`,`id`),
CONSTRAINT `FK__COMPOSITE_COMPOSITE_JOB` FOREIGN KEY (`composite_job_id`) REFERENCES `composite_job` (`job_id`),
CONSTRAINT `FK__COMPOSITE__CREATIVE_LIBRARY` FOREIGN KEY (`creative_library_id`) REFERENCES `creative_library` (`creative_library_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1631561 DEFAULT CHARSET=latin1
CREATE TABLE `creative_library` (
`creative_library_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ad_format_id` int(10) unsigned NOT NULL,
`deleted` tinyint(1) NOT NULL,
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`title` varchar(254) DEFAULT NULL,
PRIMARY KEY (`creative_library_id`),
UNIQUE KEY `unique_title` (`title`),
KEY `creative_library__creative_library_id__deleted__ad_format` (`creative_library_id`,`deleted`,`ad_format_id`),
KEY `creative_library__ad_format__deleted__creative_library_id` (`ad_format_id`,`deleted`,`creative_library_id`),
CONSTRAINT `FK__CREATIVE_LIBRARY__AD_FORMAT` FOREIGN KEY (`ad_format_id`) REFERENCES `ad_format` (`ad_format_id`)
) ENGINE=InnoDB AUTO_INCREMENT=751 DEFAULT CHARSET=latin1