我有下表(删除了我的示例中未使用的列):

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `name` varchar(1024) NOT NULL,
  `sortname` varchar(1024) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sortname` (`sortname`(255)),
  KEY `name` (`name`(255))
);

CREATE TABLE `personalias` (
  `id` int(11) NOT NULL,
  `person` int(11) NOT NULL,
  `name` varchar(1024) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `person` (`person`),
  KEY `name` (`name`(255))
)

目前，我正在使用这个查询，它工作得很好:

select p.* from person p where name = 'John Mayer' or sortname = 'John Mayer';

mysql> explain select p.* from person p where name = 'John Mayer' or sortname = 'John Mayer';
+----+-------------+-------+-------------+---------------+---------------+---------+------+------+----------------------------------------------+
| id | select_type | table | type        | possible_keys | key           | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+-------------+---------------+---------------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | p     | index_merge | name,sortname | name,sortname | 767,767 | NULL |    3 | Using sort_union(name,sortname); Using where | 
+----+-------------+-------+-------------+---------------+---------------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)

现在我想扩展此查询以考虑别名。

首先，我尝试使用连接:

select p.* from person p join personalias a on p.id = a.person where p.name = 'John Mayer' or p.sortname = 'John Mayer' or a.name = 'John Mayer';

mysql> explain select p.* from person p join personalias a on p.id = a.person where p.name = 'John Mayer' or p.sortname = 'John Mayer' or a.name = 'John Mayer';
+----+-------------+-------+--------+-----------------------+---------+---------+-------------------+-------+-----------------+
| id | select_type | table | type   | possible_keys         | key     | key_len | ref               | rows  | Extra           |
+----+-------------+-------+--------+-----------------------+---------+---------+-------------------+-------+-----------------+
|  1 | SIMPLE      | a     | ALL    | ref,name              | NULL    | NULL    | NULL              | 87401 | Using temporary | 
|  1 | SIMPLE      | p     | eq_ref | PRIMARY,name,sortname | PRIMARY | 4       | musicbrainz.a.ref |     1 | Using where     | 
+----+-------------+-------+--------+-----------------------+---------+---------+-------------------+-------+-----------------+
2 rows in set (0.00 sec)

这看起来很糟糕:没有索引，87401 行，使用临时索引。只有在我使用 distinct 时才会出现 Using temporary，但由于别名可能与名称相同，所以我真的无法摆脱它。

接下来，我尝试用子查询替换连接:

select p.* from person p where p.name = 'John Mayer' or p.sortname = 'John Mayer' or p.id in (select person from personalias a where a.name = 'John Mayer');

mysql> explain select p.* from person p where p.name = 'John Mayer' or p.sortname = 'John Mayer' or p.id in (select id from personalias a where a.name = 'John Mayer');
+----+--------------------+-------+----------------+------------------+--------+---------+------+--------+-------------+
| id | select_type        | table | type           | possible_keys    | key    | key_len | ref  | rows   | Extra       |
+----+--------------------+-------+----------------+------------------+--------+---------+------+--------+-------------+
|  1 | PRIMARY            | p     | ALL            | name,sortname    | NULL   | NULL    | NULL | 540309 | Using where | 
|  2 | DEPENDENT SUBQUERY | a     | index_subquery | person,name      | person | 4       | func |      1 | Using where | 
+----+--------------------+-------+----------------+------------------+--------+---------+------+--------+-------------+
2 rows in set (0.00 sec)

同样，这看起来很糟糕:没有索引，540309 行。有趣的是，这两个查询 (select p.* from person ... or p.id in (4711,12345) 和 select id from personalias a where a.name = 'John Mayer') 工作得非常好。

为什么 MySQL 不为我的两个查询使用任何索引？我还能做什么？目前，最好为别名获取 person.ids 并将它们作为 in(...) 静态添加到第二个查询中。肯定有另一种方法可以通过单个查询来做到这一点。我目前没有想法。我可以以某种方式强制 MySQL 使用另一个(更好的)查询计划吗？

最佳答案

在第一个查询中，只有一个表。

MySQL 使用索引合并:它从两个索引中获取行指针并将它们合并。

您的第二个查询引入了另一个表。 MySQL 无法合并来自另一个表的索引，因为记录指针不同。

你需要模仿这个:

SELECT  p.*
FROM    (
        SELECT  id
        FROM    person p
        WHERE   p.name = 'John Mayer'
                OR p.sortname = 'John Mayer'
        UNION
        SELECT  person
        FROM    personalias a
        WHERE   a.name = 'John Mayer'
        ) q
JOIN    person p
ON      p.id = q.id

如果您的表是 MyISAM，请将 id 作为尾随列添加到索引中:

CREATE INDEX ix_person_name_id ON (name, id);
CREATE INDEX ix_person_sortname_id ON (sortname, id);
CREATE INDEX ix_personalias_name_person (name, person);

另请注意，对于这样的查询，最好使用 FULLTEXT 索引:

CREATE FULLTEXT INDEX fx_person_name_sortname ON person (name, sortname);

SELECT  p.*
FROM    (
        SELECT  id
        FROM    person p
        WHERE   MATCH (name, sortname) AGAINST ('"John Mayer"' IN BOOLEAN MODE)
        UNION
        SELECT  person
        FROM    personalias a
        WHERE   a.name = 'John Mayer'
        ) q
JOIN    person p
ON      p.id = q.id

关于mysql - 为什么我的查询在使用子查询时不使用任何索引？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/2683524/