MYSQL – 索引和优化选择查询

我有一张超过500万行的表.当我执行选择查询时,大约需要20秒.

SELECT CompUID,Weburl FROM `CompanyTable` WHERE (Alias1='match1' AND Alias2='match2' )OR Alias3='match3' OR Alias4='match4'

这是表结构：

CREATE TABLE `CompanyMaster` (
  `CompUID` int(11) NOT NULL AUTO_INCREMENT,`Weburl` varchar(150) DEFAULT NULL,`CompanyName` varchar(200) DEFAULT NULL,`Alias1` varchar(150) DEFAULT NULL,`Alias2` varchar(150) DEFAULT NULL,`Alias3` varchar(150) DEFAULT NULL,`Alias4` varchar(150) DEFAULT NULL,`Created` datetime DEFAULT NULL,`LastModified` datetime DEFAULT NULL,PRIMARY KEY (`CompUID`),KEY `Alias` (`Alias1`,`Alias2`,`Alias3`,`Alias4`)
) ENGINE=InnoDB AUTO_INCREMENT=5457968 DEFAULT CHARSET=latin1

这是来自该查询的EXPLAIN：

--------+------------------------------------------------------------------------------------------------------+
| id | select_type | table        | type  | possible_keys | key    | key_len  | ref  | rows    | Extra         |
+----+-------------+----------+-------+---------------+------+---------+------+---------+----------------------+
|  1 | SIMPLE      | CompanyTable | ALL   |     Alias     | NULL   | NULL     | NULL | 5255929 |  Using where  |
+----+-------------+----------+-------+---------------+------+---------+------+---------+----------------------+

我使用了复合索引Alias(Alias1,Alias2,Alias3,Alias4).
但我相信这不是最好的.请建议我为此选择查询查找正确的索引.

最佳答案

要使查询引擎使用复合索引中的列,必须首先满足左侧的列.也就是说,必须使用列作为限制,从左到右读取候选行.

OR别名3(或别名4)条款违反了这条规则,因为它说“我不关心左边的部分(别名1或别名2(或alias3))是什么,因为我不依赖于它们”.

然后需要进行全表扫描,以查看是否存在满足条件的alias3(或alias4)值.

这种情况下潜在有用的指数是：

> INDEX(alias1,alias2)：alias1 AND alias2涵盖此复合索引
> INDEX(别名3)
> INDEX(别名4)

实际统计数据and plan selection需要进一步调查 – 但至少现在查询计划程序可以使用.

话虽如此 – 而且我不确定“别名”的作用是什么 – 将表格标准化可能是有意义的.以下确实稍微改变了语义,因为它删除了“别名位置”(可以重新添加)并且应该验证语义正确性.

CREATE TABLE `CompanyMaster` (
  `CompUID` int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (`CompUID`)
)

-- (This establishes a unique alias-per-company,which may be incorrect.)
CREATE TABLE `CompaniesAliases` (
  `CompUID` int(11) NOT NULL,`Alias` varchar(150) NOT NULL
  -- Both CompUID and Alias appear in 'first' positions:
  --   CompUID for Join,Alias for filter,PRIMARY KEY (`CompUID`,`Alias`),KEY (`Alias`)
 -- Alternative,which may change plan selection by eliminating options:
 --,PRIMARY KEY (`Alias`,`CompUID`) -- and no single KEY/index on Alias or CompUID,FOREIGN KEY(CompUID) REFERENCES CompanyMaster(CompUID)
)

然后它可以被查询大致类似于原始,不同之处在于它不关心哪个“别名”匹配哪个值：

-- AND constructed by joins (could also use GROUP BY .. HAVING COUNT)
SELECT c.CompUID FROM `CompanyTable` c
JOIN `CompaniesAliases` ac1
ON ac1.CompUID = c.CompUID AND Alias = 'match1'
JOIN `CompaniesAliases` ac2
ON ac2.CompUID = c.CompUID AND Alias = 'match2'

-- OR constructed by union(s)
UNION
SELECT c.CompUID FROM `CompanyTable` c
JOIN `CompaniesAliases` ac1
ON ac1.CompUID = c.CompUID AND (Alias = 'match3' OR Alias = 'match4')

我希望在sql Server中有效地实现这样的查询 – 使用MysqL的YMMV.

原文链接：https://www.f2er.com/mysql/432849.html

MYSQL – 索引和优化选择查询

猜你在找的MySQL相关文章