MySQL索引-In与等于索引编制问题 [英] MySQL Indexing - In vs. Equals indexing issues

问题描述

以下查询可在mysql服务器上快速且即时地运行:

Following queries run quite fast and instantaneously on mysql server:

SELECT  table_name.id 
FROM table_name 
WHERE table_name.id in (10000)

SELECT table_name.id 
from table_name 
where table_name.id = (SELECT table_name.id 
                       FROM table_name 
                       WHERE table_name.id in (10000)
                      );

但是，如果我将第二个查询更改为以下内容，则将花费20秒钟以上的时间:

But if I change the second query to as following, then it takes more than 20 seconds:

SELECT table_name.id 
from table_name 
where table_name.id in (SELECT table_name.id 
                        FROM table_name 
                        WHERE table_name.id in (10000)
                        );

在做解释时，我得到以下输出.显然，关于MySQL如何为数据建立索引以及如何在关键字中使用这些问题.

On doing explain, I get the following output. It is clear that there are some issues regarding how MySQL indexes the data, and use in keyword.

对于第一个查询:

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | table_name    | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+

第二个查询:

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | table_name    | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
|  2 | SUBQUERY    | table_name    | const | PRIMARY       | PRIMARY | 4       |       |    1 | Using index |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+

第三个查询:

+----+--------------------+------------+-------+---------------+---------+---------+-------+---------+--------------------------+
| id | select_type        | table_name | type  | possible_keys | key     | key_len | ref   | rows    | Extra                    |
+----+--------------------+------------+-------+---------------+---------+---------+-------+---------+--------------------------+
|  1 | PRIMARY            | table_name | index | NULL          | sentTo  | 5       | NULL  | 6250751 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | table_name | const | PRIMARY       | PRIMARY | 4       | const |       1 | Using index              |
+----+--------------------+------------+-------+---------------+---------+---------+-------+---------+--------------------------+

我正在使用InnoDB，并尝试更改第三个查询以强制使用以下类别所指示的索引.

I am using InnoDB and have tried changing the third query to forcibly use the index as indicated by the following category.

推荐答案

在第一种情况下，您只有子查询中的第一条记录(它运行一次，因为equals仅用于第一个值)

In first case you have only first record from subquery (It runs once, because equals is only for first value)

在第二个查询中，您获得了笛卡尔乘法(每个)，因为IN为每一行运行子查询.哪个不利于性能

In second query you got Cartesian multiplication (each per each) because IN runs subquery for each row. Which is not good for performance

尝试在这些情况下使用联接.

Try to use joins for these cases.

这篇关于MySQL索引-In与等于索引编制问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！