MYSQL - 索引和优化查询

Question

我有一张超过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)，但我认为这不是最好的选择。请为这个查询建议正确的索引。

- Sandy

---

OR Alias3='match3' OR Alias4='match4' 这个语句强制进行全表扫描。这个子句本质上是未索引的。为了优化这个查询，您需要在Alias3和Alias4上添加索引。 - drew010

您可以通过以下方式为查询提供提示来检查其性能：SELECT CompUID,Weburl FROM CompanyTable use index(Alias)。这会对速度产生影响吗？ - zedfoxus

查询以下语句的计数： "select count() from CompanyTable where Alias1='match1' AND Alias2='match2';" 和 "select count() from CompanyTable where Alias1='match3'" 和 "select count(*) from CompanyTable where Alias1='match4". - Zafar Malik

Answer 1

查询引擎要使用复合索引中的列，必须先满足左侧的列。也就是说，列必须被用作限制条件，从而从左到右减少候选行。

OR alias3（或alias4）子句违反了这个规则，因为它表示“我不关心左侧部分（alias1或alias2（或alias3））是什么，因为我不依赖它们”。

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

在这种情况下，可能有用的索引是：

- INDEX（alias1，alias2）：alias1和alias2覆盖了这个复合索引 - INDEX（alias3） - INDEX（alias4）

实际统计和计划选择需要进一步调查 - 但至少现在查询计划器有了可用的东西。

---

话虽如此 - 我不确定 "alias" 的作用是什么 - 规范化表可能是有意义的。以下操作会稍微改变语义，因为它删除了 "别名位置"（可以添加回来），应验证其语义正确性。

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中可能会有所不同。

- user2864740

Answer 2

我建议采用以下解决方案，创建一个带有复杂别名字段的表。这会稍微增加一些数据量，并且您的数据现在是冗余的，但我认为这是一个简单直接的解决方案。 1. 创建表格

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,
  `ComplexAliasQuery` BOOLEAN DEFAULT FALSE,
  PRIMARY KEY (`CompUID`),
  KEY `Alias` (`Alias1`,`Alias2`,`Alias3`,`Alias4`),
  KEY `AliasQuery` (`ComplexAliasQuery`)
) ENGINE=InnoDB AUTO_INCREMENT=5457968 DEFAULT CHARSET=latin1;

2. 填写您的新字段 ComplexAliasQuery

UPDATE CompanyMaster set ComplexAliasQuery = TRUE WHERE (Alias1='match1' AND Alias2='match2' )OR Alias3='match3' OR Alias4='match4';

3. 更新字段Alias1、Alias2、Alias3、Alias4中的一个

如果要更新，请同时填写ComplexAliasQuery。如果您无法使用触发器，因为您正在运行集群，则可以使用触发器http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html或在代码中完成此操作。

4. 您的简单查询在最后

SELECT CompUID,Weburl FROM `CompanyMaster` WHERE ComplexAliasQuery IS TRUE;

通过索引进行操作

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

另一种解决方案

如果你不喜欢你的表格CompanyMaster中的字段，你可以将其外包到一个新的表格中，并称之为IndexAliasCompanyMaster，然后只需与该表格进行连接。

- Kordi

Answer 3

以上都不对。重新设计模式。

如果这4个别名只是公司的同义词，不要在表中展示它们的数组，将它们移到另一个表中。（user2864740已经走了一半的路；我建议你走到底。）

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

CREATE TABLE `CompaniesAliases` (
  `CompUID` int(11) NOT NULL,
  `Alias` varchar(150) NOT NULL,
  PRIMARY KEY (Alias)  -- Assuming no two companies can have the same Alias
  KEY (CompUID)
) ENGINE=InnoDB;

你真的应该将所有表格转换为InnoDB。

现在，你的原始查询变成了

SELECT  CompUID, Weburl
    FROM  `CompanyTable`
    JOIN  CompaniesAliases  USING(CompUID)
    WHERE  Alias IN ('match1', 'match2', 'match3', 'match4');

这样做会使程序运行速度更快。

如果您需要显示公司名称及其别名，请考虑

SELECT CompanyName,
       GROUP_CONCAT(Alias) AS 'Also known as'
    FROM  `CompanyTable`
    JOIN  CompaniesAliases  USING(CompUID)
    WHERE ...
    GROUP BY CompUID;

- Rick James