64k's questions

给定一个表格，其中包含一段时间内每一天的日期

CREATE TABLE `tbl_calendar` (
  `date` date NOT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tbl_calendar` (`date`)
    VALUES
        ('2016-12-10'),
        ('2016-12-09'),
        ('2016-12-08'),
        ('2016-12-07'),
        ('2016-12-06'),
        ('2016-12-05'),
        ('2016-12-04'),
        ('2016-12-03'),
        ('2016-12-02'),
        ('2016-12-01')
 ;

以及一个包含不同类型的值的表，在没有填充它们的随机日期缺少值。

CREATE TABLE `tbl_values` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` int(11) NOT NULL DEFAULT '0',
  `date` date DEFAULT NULL,
  `value` double(15,2) DEFAULT '0.00',
   PRIMARY KEY (`value_id`),
   KEY `type_id_date` (`type_id`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tbl_values` (`type_id`, `date`, `value`)
VALUES
    (100, '2016-12-02', 1),
    (100, '2016-12-04', 2),
    (100, '2016-12-06', 3),
    (100, '2016-12-08', 4),
    (100, '2016-12-10', 5)
;

如何使用该类型的最新记录在 SELECT 中返回缺失天数的值？这是我到目前为止所拥有的。

SELECT 
    v1.type,
    c.date,
    v1.value

FROM 
   tbl_calendar c

   LEFT JOIN tbl_values v1 ON (
            v1.type_id = 100 
        AND v1.date <= c.date 
   )

   LEFT JOIN tbl_values v2 ON (
            v2.type_id = 100
        AND v2.date < c.date
        AND v2.date > o1.date 
   )

WHERE 
       v1.date = c.date 
    OR v2.date IS NULL

此查询的问题在于，当该日期有值时，它会返回以前的最新记录值，以及具有正确值的记录。

预期产出

a_vlad 的答案在结果上是正确的，但性能不佳，这是预期的。

SELECT
    t1.date,
    (SELECT v1.type_id FROM tbl_values v1 where v1.date <= t1.date ORDER BY v1.date desc limit 1) as `type`,
    (SELECT v1.`value` FROM tbl_values v1 where v1.date <= t1.date     ORDER BY v1.date desc limit 1) as `value`

FROM tbl_calendar t1
HAVING `type` IS NOT NULL

我使用的最终解决方案是使用 a_vlad 查询来创建汇总表。然而，事实证明它并没有提高系统的性能（因为以前缺失的值是在 PHP 循环中填充的，结果速度一样快）

在 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 不影响扫描的行？