MySQL查询计划不使用Index

I have a nested query and I trying to see if there is any full table scan in my query.

我有一个嵌套查询，我试图查看我的查询中是否有任何全表扫描。

explain delete from ACCESS where ACCESS.MESSAGEID in (select ID from MESSAGE where MESSAGE.CID = 'xzy67sd’)\G

The sub query is hitting index but the first is not using index. Here is the query plan.

子查询是命中索引但第一个不使用索引。这是查询计划。

    *************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: ACCESS
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 18295
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: MESSAGE
         type: unique_subquery
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: func
         rows: 1
        Extra: Using where

But if I separate the query and check the query plan then it is using index. I am not able to understand why and looking for some hints

但是，如果我将查询分开并检查查询计划，那么它正在使用索引。我无法理解为什么并寻找一些提示

explain delete from ACCESS where ACCESS.MESSAGEID in (2,3)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ACCESS
         type: range
possible_keys: ACCESS_ID1
key: ACCESS_ID1
      key_len: 8
          ref: const
         rows: 2
        Extra: Using where

Subquery select statement returns constant, so rather than using select statement I type integer and the query plan start picking index

子查询select语句返回常量，因此不是使用select语句，而是输入整数和查询计划开始选择索引

select ID from MESSAGE where MESSAGE.CID = 'xzy67sd’)\G

Thanks in advance

提前致谢

1 个解决方案

#1

You don't need a subquery, here, and as a general rule, you shouldn't use one in MySQL unless you actually do need one.

在这里，您不需要子查询，作为一般规则，除非您确实需要，否则不应在MySQL中使用子查询。

DELETE a
  FROM ACCESS a
  JOIN MESSAGE m ON m.ID = a.MESSAGEID
 WHERE m.CID = 'xzy67sd’;

This will delete the rows from ACCESS while leaving MESSAGE alone because only ACCESS is listed (by its alias "a") between DELETE and FROM, which is where you specify which tables you want to delete matching rows from.

这将从ACCESS中删除行，同时仅保留MESSAGE，因为在DELETE和FROM之间仅列出了ACCESS（通过别名“a”），您可以在其中指定要从中删除匹配行的表。

The optimizer should use the indexes appropriately.

优化器应该适当地使用索引。

https://dev.mysql.com/doc/refman/5.6/en/delete.html (multi-table syntax)

https://dev.mysql.com/doc/refman/5.6/en/delete.html（多表语法）

#1