主页 / dba / 问题 / 157053
Accepted
64k
Asked: 2016-12-03 15:59:12 +0800 CST

MySQL：通过连接访问不在索引中的表数据是否会在订购时导致文件排序

  • 772

在 SELECT 中包含 category.name，导致查询执行 Using 索引；使用临时的；使用文件排序，扫描的行也不受 LIMIT 0,1 的影响。

CREATE TABLE `item` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `category` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `feature` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` INT(10) UNSIGNED DEFAULT NULL,
  `category_id` INT(10) UNSIGNED DEFAULT NULL,
  `start_date` DATE DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tbl_feature_id_item_id_foreign` (`item_id`),
  KEY `tbl_feature_id_category_id_foreign` (`category_id`),
  CONSTRAINT `tbl_feature_id_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`),
  CONSTRAINT `tbl_feature_id_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `item` (`id`) VALUES (1),(2),(3),(4),(5);

INSERT INTO `category` (`id`, `name`, `created_at`, `updated_at`)
  VALUES
    (1, 'a', '2016-12-02 22:49:46', NULL),
    (2, 'b', '2016-12-02 22:49:48', NULL)
;

INSERT INTO `feature` (`id`, `item_id`, `category_id`, `start_date`, `created_at`, `updated_at`)
VALUES
(1,  1, 1, '2016-12-01', NOW(), NOW()),
(2,  1, 2, '2016-12-02', NOW(), NOW()),
(3,  2, 1, '2016-12-01', NOW(), NOW()),
(4,  2, 2, '2016-12-02', NOW(), NOW()),
(5,  3, 1, '2016-12-01', NOW(), NOW()),
(6,  3, 2, '2016-12-02', NOW(), NOW()),
(7,  4, 1, '2016-12-01', NOW(), NOW()),
(8,  4, 2, '2016-12-02', NOW(), NOW()),
(9,  5, 1, '2016-12-01', NOW(), NOW()),
(10, 5, 2, '2016-12-02', NOW(), NOW())
;

EXPLAIN EXTENDED
SELECT 
    item.id
  , feature.id
  , category.id
  , category.name -- Is the cause of the temporary table; file-sort;
FROM 
  item 
LEFT JOIN feature ON (
  feature.item_id = item.id 
  AND feature.start_date = (
    SELECT 
      MAX(start_date) AS start_date 
    FROM 
      feature 
    WHERE 
      feature.item_id = item.id 
  )
) 
LEFT JOIN category ON (
  category.id = feature.category_id
)
ORDER BY item.id DESC -- or ASC
LIMIT 0, 1 -- Is ignored in the table scan
;

MySQL 5.7.16

在此处输入图像描述

我相信可能是因为排序完成后需要访问源表。因为文件排序是在模式 2 中执行的

http://s.petrunia.net/blog/?p=24

模式 2：对对进行排序并生成一系列 rowid，可用于按所需顺序获取源表的行（但这实际上是按随机顺序命中表，速度不是很快）

向 category.name 添加索引确实可以解决问题。这是因为 category.name 现在是索引的一部分了吗？

任何人都可以确认，并可能提供比添加索引更好的解决方案，因为实际上，它并没有解决问题，因为时间点构造是针对多个其他功能重复的，即 feature_a、feature_b。

为什么使用索引；使用临时的；使用文件排序执行？

为什么 LIMIT 0,1 不影响扫描的行？

mysql

2 个回答

  1. Best Answer

    临时表是因为您在连接中有一个依赖子查询来计算 MAX(startdate)。

    我能够通过添加索引来改进 EXPLAIN：

    ALTER TABLE feature 
  ADD INDEX item_startdate_category (item_id, start_date, category_id);

    并更改查询以避免依赖子查询：

    EXPLAIN EXTENDED SELECT i.id, f1.id, c.id   
FROM item i 
LEFT JOIN feature f1 ON (f1.item_id = i.id) 
LEFT JOIN feature f2 ON (f1.item_id=f2.item_id AND f1.start_date < f2.start_date) 
LEFT JOIN category c ON (c.id = f1.category_id ) 
WHERE f2.item_id IS NULL
ORDER BY i.id DESC LIMIT 0, 1;

    f1 和 f2 之间的这种连接旨在确保 f1 具有其各自 item_id 的最大 start_date。如果到 f2 的 LEFT JOIN 没有找到具有相同 item_id 和更大 start_date 的其他行，则 f1 必须具有最大的 start_date。

    这是我得到的解释：

    +----+-------------+-------+------------+--------+--------------------------------------------------------+-------------------------+---------+---------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys                                          | key                     | key_len | ref                 | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+--------------------------------------------------------+-------------------------+---------+---------------------+------+----------+--------------------------+
|  1 | SIMPLE      | i     | NULL       | index  | NULL                                                   | PRIMARY                 | 4       | NULL                |    1 |   100.00 | Using index              |
|  1 | SIMPLE      | f1    | NULL       | ref    | tbl_feature_id_item_id_foreign,item_startdate_category | item_startdate_category | 5       | test.i.id           |    2 |   100.00 | Using index              |
|  1 | SIMPLE      | f2    | NULL       | ref    | tbl_feature_id_item_id_foreign,item_startdate_category | item_startdate_category | 5       | test.f1.item_id     |    2 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY                                                | PRIMARY                 | 4       | test.f1.category_id |    1 |   100.00 | Using index              |
+----+-------------+-------+------------+--------+--------------------------------------------------------+-------------------------+---------+---------------------+------+----------+--------------------------+

    我在我的 Macbook 上测试了 MySQL 8.0.0-dmr。

    • 3

  2. 复合覆盖INDEX(item_id, start_date)将提高第一个子查询的性能。

    “使用索引”意味着索引是“覆盖”的。“使用索引条件”是完全不同的——它指的是 ICP（索引条件下推）。

    LIMIT 1仅在某些情况下被折叠到优化中。一种“一般”情况是优化器可以使用所有、WHERE和。几乎所有其他情况都会导致扫描多行，而我会导致一个（或多个）临时表和排序。GROUP BY ORDER BY1

    索引食谱。

    MySQL 没有“rowids”。这样PRIMARY KEY的服务。

    • 0

相关问题