OR运算符中的MySQL性能

问题描述

这是我的表：

CREATE TABLE `e_relationship` (
`OID` int(11) NOT NULL AUTO_INCREMENT,
`E_E_OID` int(11) NOT NULL,
`E_E_OID2` int(11) NOT NULL,
`REL_DISPLAY` text NOT NULL,
`APP_OID` int(11) NOT NULL,
`META_OID` int(11) NOT NULL,
`STORE_DATE` datetime NOT NULL,
`UID` int(11) DEFAULT NULL,
PRIMARY KEY (`OID`),
KEY `Left_Entity` (`E_E_OID`),
KEY `Right_Entity` (`E_E_OID2`),
KEY `Meta_Left` (`META_OID`,`E_E_OID`),
KEY `Meta_Right` (`META_OID`,`E_E_OID2`)
) ENGINE=InnoDB AUTO_INCREMENT=310169 DEFAULT CHARSET=utf8;

以下查询大约需要2.5-3ms，结果集为1,290行，总数为表中的行是1,008,700：

The following query takes about 2.5-3ms, the result set is 1,290 rows, and the total number of rows in the table is 1,008,700:

SELECT * FROM e_relationship WHERE e_e_oid=@value1 OR e_e_oid2=@value1

这是 EXPLAIN 的结果：

id: 1
select_type: SIMPLE
table: e_relationship
type: index_merge
possible_keys: Left_Entity,Right_Entity
key: Left_Entity,Right_Entity
key_len: 4,4
ref: NULL
rows: 1290
Extra: Using union(Left_Entity,Right_Entity); Using where

我想加快这个查询，因为这在我的系统中非常关键，我我不确定我是否在mysql中遇到某种瓶颈，因为记录的数量已经超过了100万，并且想知道其他可能提高性能的策略。

I would like to speed up this query as this is being quite critical in my system, I'm not sure if I'm reaching some sort of bottleneck in mysql as the number of records has already passed one million, and would like to know about other possible strategies to improve performance.

推荐答案

有时MySQL在优化 OR 查询时遇到问题。在这种情况下，您可以使用 UNION 将其拆分为两个查询：

Sometimes MySQL has trouble optimizing OR queries. In this case, you can split it up into two queries using UNION:

SELECT * FROM relationship WHERE e_e_oid = @value1
UNION
SELECT * FROM relationship WHERE e_e_oid2 = @value2

每个子查询都会使用适当的索引，然后合并结果。

Each subquery will make use of the appropriate index, and then the results will be merged.

但是，在简单的情况下，MySQL可以自动执行这个转换，它在你的查询中这样做。这就是在 EXPLAIN 输出中使用union 意味着。

However, in simple cases MySQL can automatically perform this transformation, and it's doing so in your query. That's what Using union in the EXPLAIN output means.

这篇关于OR运算符中的MySQL性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！