文章/答案/技术大牛

发布

社区首页 >问答首页 >即使实现了索引，Mysql连接查询也要花费太多的时间。

问即使实现了索引，Mysql连接查询也要花费太多的时间。
EN

Database Administration用户

提问于 2017-02-13 07:43:28

回答 1查看 61关注 0票数 0

嗨，我有一个mysql查询

SELECT 
    `locationMapping`.`keyId` AS `caseId`,
        `locationMapping`.`id`
FROM
    `locationMapping`
INNER JOIN `locationSubtype` ON (`locationMapping`.`locationSubtypeId` = `locationSubtype`.`id`
    AND `locationMapping`.`locationTypeId` = `locationSubtype`.`locationTypeId`
    AND `locationMapping`.`govtId` = `locationSubtype`.`govtId`)
    AND ((`locationSubtype`.`govtId` = '90')
    AND (`locationMapping`.`keyTable` = 'xercase'))
WHERE
    locationMapping.govtId = 90

GROUP BY `keyId`

需要8秒才能执行。

为locationMapping创建表命令是:显示Create；

CREATE TABLE `locationMapping` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`govtId` int(11) NOT NULL DEFAULT '0',
`locationSubtypeId` int(11) NOT NULL DEFAULT '0',
`keyId` int(11) NOT NULL DEFAULT '0',
`keyTable` varchar(100) NOT NULL DEFAULT '',
`locationTypeId` int(11) DEFAULT '0',
`gisCode` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `locationmapping_instance` (`keyId`,`keyTable`),
KEY `locationmapping_subtype` (`locationSubtypeId`),
KEY `locationmapping_govtid` (`govtId`)
) ENGINE=InnoDB AUTO_INCREMENT=3994710 DEFAULT CHARSET=latin1

创建locationSubType的表命令是

CREATE TABLE `locationSubtype` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`govtId` int(11) NOT NULL DEFAULT '0',
`locationTypeId` int(11) NOT NULL DEFAULT '0',
`name` varchar(100) DEFAULT NULL,
`description` varchar(100) DEFAULT NULL,
`code` varchar(30) DEFAULT NULL,
`gisCode` varchar(100) DEFAULT NULL,
`sortorder` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `locationsubtype_locationtype` (`locationTypeId`),
KEY `locationsubtype_govtid` (`govtId`),
KEY `locationsubtype_giscode` (`gisCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4319 DEFAULT CHARSET=latin1

查询的解释语句是：

id,   select_type, table,             type,  possible_keys,                                                             key,                      key_len, ref,                              rows, Extra
'1',  'SIMPLE',    'locationSubtype', 'ref', 'PRIMARY,locationsubtype_locationtype,locationsubtype_govtid',             'locationsubtype_govtid', '4',     'const',                          '756', 'Using index condition; Using temporary; Using filesort'
'1', 'SIMPLE',     'locationMapping', 'ref', 'locationmapping_instance,locationmapping_subtype,locationmapping_govtid', 'locationmapping_subtype','4',     'comcate_dev6x.locationSubtype.id','333', 'Using where'

query-performance

mysql

performance

optimization

回答 1

Database Administration用户

发布于 2017-02-13 16:20:24

试试这个重写：

SELECT  m.`keyId` AS `caseId`,
        m.`id`
    FROM  `locationMapping` AS m
    WHERE  m.`keyTable` = 'xercase'
      AND  m.govtId = 90
      AND  EXISTS (
        SELECT  *
            FROM  locationSubtype
            WHERE  m.`locationSubtypeId` = `id`
              AND  m.`locationTypeId` = `locationTypeId`
              AND  m.`govtId` = `govtId`
                  )
    GROUP BY  m.`keyId`

在许多情况下，EXISTS()比INNER JOIN快。

这个“综合”指数很重要：

locationMapping: INDEX(govtId, keyTable)  -- in either order

优化器足够聪明，可以注意到govtId=90的传递特性，因此我删除了对它的提及。

AND   `locationMapping`.`govtId` = `locationSubtype`.`govtId`)
AND ((`locationSubtype`.`govtId` = '90')
...        locationMapping.govtId = 90

如果给出了其中的第一个，再加上另外两个中的一个，优化器将推导出最后的一个。你已经提供了所有的3，这是好的。但这是“过度杀戮”。

第一行很重要，因为它告诉优化器两个表是如何关联的。

票数 0

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/164145

复制

相似问题

问即使实现了索引，Mysql连接查询也要花费太多的时间。
EN

回答 1

Database Administration用户

More

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问即使实现了索引，Mysql连接查询也要花费太多的时间。EN

回答 1

Database Administration用户

More

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问即使实现了索引，Mysql连接查询也要花费太多的时间。
EN