Mysql Query中的索引表现得无差别-Java 学习之路

我有一个问题：

select SQL_NO_CACHE id from users
 where id>1 and id <1000
   and id in  ( select owner_id from comments and content_type='Some_string');

（请注意，它缺少用于我的sphinx索引的实际大型查询，表示问题）此查询占用 3.5 seconds （修改范围从id = 1..5000使其约为 15 secs ） .

users表有大约35000个条目，而comments表有大约8000个条目 .

解释以上查询：

explain select SQL_NO_CACHE id from users
        where id>1 and id <1000
          and id in  ( select distinct owner_id from d360_core_comments);

| id | select_type |表|类型| possible_keys |关键| key_len | ref |行|额外的| | 1 |主要|用户|范围|主要|主要| 4 | NULL | 1992年|用在哪里;使用索引| | 2 |相关的子信息| d360_core_comments |所有| NULL | NULL | NULL | NULL | 6901 |用在哪里;使用临时|

这里的单个子查询（ select owner_id from d360_core_comments where content_type='Community20::Topic'; ）占用差不多0.0秒 .

但是如果我 add index on owner_id,content_type ，（请注意这里的订单）

create index tmp_user on d360_core_comments (owner_id,content_type);

我的 subquery 在~0.0秒内运行，使用NO索引：

mysql>解释从d360_core_comments中选择owner_id，其中content_type ='Community20 :: Topic'; | id | select_type |表|类型| possible_keys |关键| key_len | ref |行|额外的| | 1 |简单| d360_core_comments |所有| NULL | NULL | NULL | NULL | 6901 |使用何处|

但是现在我的主要查询（ select SQL_NO_CACHE id from users where id>1 and id <1000 and id in ( select owner_id from d360_core_comments where content_type='Community20::Topic'); ）现在在~0秒内运行，并提供以下说明：

mysql>解释从id> 1且id <1000且id在的用户中选择SQL_NO_CACHE id（从d360_core_comments中选择owner_id，其中content_type ='Community20 :: Topic'）; | id | select_type |表|类型| possible_keys |关键| key_len | ref |行|额外的| | 1 |主要|用户|范围|主要|主要| 4 | NULL | 1992年|用在哪里;使用索引| | 2 |相关的子信息| d360_core_comments | index_subquery | tmp_user | tmp_user | 5 | func | 34 |使用何处|

So the main questions I have are:

如果在我的子查询中使用的表上定义的索引没有在我的实际子查询中使用，那么它在这里如何优化查询？
为什么在第一个地方第一个查询花了这么多时间，实际的子查询和主查询独立得多？

4 回答

3
如果您阅读了MySQL参考手册的这一部分：Optimizing Subqueries with EXISTS Strategy，您将看到查询优化器从以下位置转换子查询条件：
```
id in ( select distinct owner_id
          from d360_core_comments
         where content_type='Community20::Topic')
```
成：
```
exists ( select 1
           from d360_core_comments
          where content_type='Community20::Topic'
            and owner_id = users.id )
```
这就是当子查询作为独立查询进行测试时， (owner_id, content_type) 上的索引无用的原因，但在考虑转换后的子查询时它很有用 .
回复于 2024-04-24T17:40:03+08:00
2
在没有索引的完整查询中似乎发生的事情是MySQL将构建（某种）子查询生成的所有owner_id的临时表 . 然后，对于与user约束匹配的users表中的每一行，将执行此临时构造中的查找 . 目前还不清楚开销是否正在创建临时构造，或者查询是否是次优地实现的（以便所有元素与外部查询的每一行线性匹配 .

在owner_id上创建索引时，如果只运行子查询，则不会更改任何内容，因为它在owner_id上没有条件，索引也不包含content_type列 .

但是，当您使用索引运行完整查询时，可以获得更多信息，因为我们现在具有来自外部查询的值，该值应与owner_id匹配，后者由索引覆盖 . 所以现在执行似乎是运行外部查询的第一部分，并且对于每个匹配的行，执行owner_id的索引查找 . 换句话说，可能的执行计划是：
```
From Index-Users-Id Get all id matching id>1 and id <1000
For Each Row
    Include Row If Index-Comment-OwnerId Contains row.Id
                   And Row Matches content_type='Some_string'
```
所以在这种情况下，运行1000（我假设）索引查找的工作比构建8000可能的owner_id的临时构造要快 . 但这只是一个假设，因为我不太了解MySQL .
回复于 2024-04-24T17:40:03+08:00
0
你应该知道的第一件事是MySQL不能优化依赖子查询，它是一个长期以来众所周知的MySQL缺陷，它将在MySQL 6.x中修复（只需google for "mysql dependent subquery"，你会看到） . 也就是说，子查询基本上是为 users 表中的每个匹配行执行的 . 由于您有其他条件，因此总体执行时间取决于该条件 . 解决方案是使用连接替换子查询（您期望从MySQL获得的优化） .

其次，子查询中存在语法错误，我认为owner_id存在一个条件 . 因此，当您在 owner_id 上添加索引时，它会被使用，但对于第二个条件（因此没有 using index ）是不够的，但为什么在 EXPLAIN 中没有提到一切都是一个问题（我认为因为 users.id 的条件）

第三，我不知道为什么你需要这个条件，但你应该明白这些是两个范围条件，需要非常准确，有时非显而易见和数据依赖的索引方法（而不是相等的比较条件），如果你实际上并不需要它们，仅用于解释为什么查询需要这么长时间，然后这是一个坏主意，它们将不会消失 .

如果条件是必需的并且 owner_id 上的索引仍然存在，我会按如下方式重写查询：
```
SELECT id 
FROM (
  SELECT owner_id as id
  FROM comments
  WHERE owner_id < 5000 AND content_type = 'some_string'
) as ids
JOIN users ON (id)
WHERE id > 1;
```
附： (content_type, owner_id) 上的复合索引甚至可以更好地用于查询 .
回复于 2024-04-24T17:40:03+08:00
1
第1步：使用 id BETWEEN x AND y 而不是 id >= x AND id <= y . 您可能会发现一些令人惊讶的收益，因为它的索引更好

第2步：调整您的子 SELECT 进行过滤，这样就不必进行两次：
```
SELECT SQL_NO_CACHE id 
  FROM users
 WHERE id IN (SELECT owner_id 
                FROM comments
               WHERE content_type='Some_string' 
                 AND owner_id BETWEEN 1 AND 1000);
```
你的陈述中似乎有几处错误 . 例如，您选择2到999，可能两端都是一个，并且子选择无效 .
回复于 2024-04-24T17:40:03+08:00

Mysql Query中的索引表现得无差别

4 回答

相关问题