MYSQL-索引和优化选择查询

Sandy 发表于 Dev

沙

我有一个超过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

这是该查询的解释：

--------+------------------------------------------------------------------------------------------------------+
| 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）。但是我认为这不是最好的。请建议我为此选择查询查找正确的索引。

用户名

为了使查询引擎在复合索引中使用列，必须首先满足左侧的列。也就是说，必须将列用作限制，以减少从左到右读取的候选行。

ORalias3（或alias4）子句违反了此规则，因为它说“我不在乎剩下的部分（alias1或alias2（或alias3）），因为我不依赖它们”。

然后需要进行全表扫描，以查看是否有任何满足条件的alias3（或alias4）值。

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

INDEX（alias1，alias2）：alias1和alias2覆盖了此复合索引
INDEX（别名3）
INDEX（别名4）

实际的统计数据和计划选择需要进一步调查-但至少现在查询计划者可以使用一些东西。

话虽如此-我不确定“别名”的作用是什么-标准化表格可能是有意义的。以下内容确实会稍微改变语义，因为它会掉落“别名位置”（可以重新添加），因此应进行语义正确性验证。

CREATE TABLE `CompanyMaster` (
  `CompUID` int(11) NOT NULL AUTO_INCREMENT
 ,`CompanyName` varchar(200) DEFAULT NULL
 ,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中有效地实现。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。