MYSQL– 索引和优化选择查询

概述我有一张超过500万行的表.当我执行选择查询时,大约需要20秒.SELECT CompUID,Weburl FROM `CompanyTable` WHERE (Alias1='match1' AND Alias2='match2' )OR Alias3='match3' OR Alias4='match4' 这是表结构：CREATE TABLE `Comp

我有一张超过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` cJOIN `CompanIEsAliases` ac1ON ac1.CompuID = c.CompuID AND Alias = 'match1'JOIN `CompanIEsAliases` ac2ON ac2.CompuID = c.CompuID AND Alias = 'match2'-- OR constructed by union(s)UNIONSELECT c.CompuID FROM `Companytable` cJOIN `CompanIEsAliases` ac1ON ac1.CompuID = c.CompuID AND (Alias = 'match3' OR Alias = 'match4')

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

以上是内存溢出为你收集整理的MYSQL – 索引和优化选择查询全部内容，希望文章能够帮你解决MYSQL – 索引和优化选择查询所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。