mysql 子查询添加索引_mysql – 当我使用子查询时,为什么我的查询不使用任何索引？...

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

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行.有趣的是,两个查询(从人…中选择p.*或在(4711,12345)中选择p.id并从个人a中选择id,其中a.name =’John Mayer’)工作得非常好.

为什么MySQL不为我的两个查询使用任何索引？我还能做什么？目前,最好为别名获取person.ids,并将其作为in(…)静态添加到第二个查询中.当然,必须有另一种方法来执行单个查询.我目前没有想法.我可以以某种方式强迫MySQL使用另一个(更好的)查询计划吗？

解决方法:

尝试：

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'

UNION将照顾清晰度.

标签：mysql

来源： https://codeday.me/bug/20190621/1258601.html