时间戳

首页 > 解决方案 > 覆盖索引无法优化mysql8.0中的limit查询

java - 覆盖索引无法优化mysql8.0中的limit查询

问题描述

在<High Performance Mysql>中，它使用覆盖索引来优化限制查询。我使用的是从 MySQL 官网下载的sakila数据库。查询如下：</p> 

SELECT a.film_id, a.description 
  from sakila.film a 
  inner join (
    select film_id 
      from sakila.film b 
      order by title limit 50,5
  ) as lim 
  USING(film_id)

但是我用explain来分析这个过程。它看起来像这样。在此处输入图像描述

在第三行中，它显示它仍然扫描了所有 1000 行。然后我测试了子查询select film_id from sakila.film b order by title limit 50,5，解释日志是这样的日志1。

在我看来，第一个日志中的第三行应该和第二个日志一样，我不知道如何解释第一个日志中的前 2 行，为什么行是 55 和 1，我想可能应该是 5 5. 这是MySQL官方demo。我猜是因为mysql的版本。

我将我的mysql更新到8.0.11.0，它变得像这样更正常了在此处输入图像描述

我测试了相同的数据集int mysql8.0和mysql5.6，在mysql5.6中检索数据只需要0.2s，但在mysql8.0中需要1s。他们有什么不同？而且第一行仍然是 905 而不是 5。有人可以告诉我为什么第一行是 905，第二行是 1 的原因吗？

标签： javamysqlsqlindexing

解决方案

EXPLAIN因忽视LIMIT价值观而臭名昭著。

这是了解正在发生的事情的更好方法：

 FLUSH STATUS;
 SELECT ...;
 SHOW SESSION STATUS LIKE 'Handler%';

对我拥有的一些数据使用类似的查询（在 5.6 上）...

SELECT  city, province, population
    FROM  canada AS a
    JOIN ( SELECT  id
            FROM  canada
            ORDER BY  city
            LIMIT  300,20    -- I used bigger numbers
         ) AS x USING(id);

（该表有 5484 行。）

mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 4     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 1     |
| Handler_read_key           | 21    |  -- about 20
| Handler_read_last          | 0     |
| Handler_read_next          | 319   |  -- about 300+20 (OFFSET+LIMIT)
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 21    |  -- about 20
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 20    |  -- 20  (the 'derived' table)
+----------------------------+-------+
18 rows in set (0.00 sec)

解释：

+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL |  320 | NULL        |
|  1 | PRIMARY     | a          | eq_ref | id            | id      | 4       | x.id |    1 | NULL        |
|  2 | DERIVED     | canada     | index  | NULL          | city_id | 771     | NULL | 5484 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+

所以...没有表扫描。OFFSET仅包含在派生表中。

Using index您（和我的EXPLAIN）中的 确认使用“覆盖索引”。

（我认为......）在旧版本中，EXPLAIN实际上会评估任何派生表；8.0 避免了这种情况。这可以部分解释为什么解释在你的两个输出中发生了变化。

推荐阅读