代码之家 › 专栏 › 技术社区 › sfussenegger

为什么我的查询在使用子查询时没有使用任何索引?

mysql

sfussenegger · 技术社区 · 15 年前

我有以下表格(删除了不用于示例的列):

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 ,但由于别名可能与名称相同,我无法真正摆脱它。

接下来,我尝试用子查询替换联接:

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使用另一个(更好的)查询计划吗?

2 回复 | 直到 15 年前

DVK 15 年前

SELECT p.* from person p 
WHERE  p.name = 'John Mayer' or p.sortname = 'John Mayer' 

UNION

SELECT p.* from person p, personalias a 
WHERE  p.id =a.person and a.name = 'John Mayer'

工会会注意清楚的。

Quassnoi 15 年前

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

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

第二个查询引入另一个表。 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