执行效率差异:一个SQL。双分裂SQL

Question

我使用MySQL，上下文如下：

mysql> show create table letter_index\G
*************************** 1. row ***************************
       Table: letter_index
Create Table: CREATE TABLE `letter_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner` int(11) NOT NULL DEFAULT '0',
  `target` int(11) NOT NULL DEFAULT '0',
  `type` tinyint(4) unsigned NOT NULL DEFAULT '0',
  `data_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `target_idx` (`target`,`type`)
) ENGINE=InnoDB 


mysql> show create table letter_data\G
*************************** 1. row ***************************
       Table: letter_data
Create Table: CREATE TABLE `letter_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `topic` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

表letter_index定义了字母关系:是谁写的，谁要接收，而表letter_data则存储信件内容消息。

表letter_index列data_id引用表letter_data主键id。

我想得到别人都收到的信息。当我使用一个嵌套SQL来获得结果时，它通常比两个拆分SQL花费更多的时间。演示如下：

一个SQL：

mysql> select * from letter_data where id in (select data_id from letter_index where target=10718)
  ...
  ...  # query result
  ...
78 rows in set (52.01 sec)

分裂：

mysql> select data_id from letter_index where target=10718;
  ...
  ...  # query result
  ...
78 rows in set (0.25 sec)


mysql> select * from letter_data where id in (1,2,..`data_id result`..);
  ...
  ...  # query result
  ...
78 rows in set (2.04 sec) 

效率差别很大！

解释了相关SQL，并给出了一些提示：

mysql> explain select * from letter_data where id in (select data_id from letter_index where target=10718);
+----+--------------------+--------------+------+---------------+------------+---------+-------+---------+-------------+
| id | select_type        | table        | type | possible_keys | key        | key_len | ref   | rows    | Extra       |
+----+--------------------+--------------+------+---------------+------------+---------+-------+---------+-------------+
|  1 | PRIMARY            | letter_data  | ALL  | NULL          | NULL       | NULL    | NULL  | 1103585 | Using where | 
|  2 | DEPENDENT SUBQUERY | letter_index | ref  | target_idx    | target_idx | 4       | const |      78 | Using where | 
+----+--------------------+--------------+------+---------------+------------+---------+-------+---------+-------------+


mysql> explain select data_id from letter_index where target=10718;
+----+-------------+--------------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys | key        | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+---------------+------------+---------+-------+------+-------+
|  1 | SIMPLE      | letter_index | ref  | target_idx    | target_idx | 4       | const |   78 |       | 
+----+-------------+--------------+------+---------------+------------+---------+-------+------+-------+


mysql> explain select * from letter_data where id in (1,2,..`data_id result`..);
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | letter_data | range | PRIMARY       | PRIMARY | 4       | NULL |   78 | Using where | 
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

我的问题是，为什么嵌套的SQL表letter_data possible_keys是NULL而单个表是PRIMARY？

耽误您时间，实在对不起!

Answer 1

不确定我是否遗漏了什么，但为什么不使用INNER JOIN：

SELECT
    ld.*
FROM
    letter_data ld
INNER JOIN letter_index li ON li.data_id = ld.id
WHERE li.target = 10718

结果是相同的，最大的区别是MySQL不会实现任何子查询。

Answer 2

表中的数据类型不同，这将导致类型转换，从而防止MySQL在第一个查询中使用索引。(letter_index.data_id是无符号的，而letter_data.id是签名的。)这就是为什么MySQL对letter_data表使用全表扫描的原因。

使用相同的数据类型，在data_id列上创建索引，创建外键。

要提高查询的性能，可以使用EXISTS而不是IN。

要解释第二种方法:这两个查询都是简单的范围或常量搜索，没有困难，特别是在筛选字段上有索引时。