主页 / dba / 问题 / 106650
Accepted
ivan
Asked: 2015-07-11 15:52:36 +0800 CST

“SELECT my_table.id”比“SELECT my_table.*”慢

  • 772

使用 InnoDB 运行 MySQL：

我有一个SELECT wide_table.*查询，我想对其进行细化，SELECT wide_table.id因为这就是所有调用代码所需要的。对其进行测试，我发现使用的执行时间*比使用的执行时间更快id（尽管使用“优化”版本通过网络传输结果的时间更快）。

为什么会这样？

查询（更改名称以保护无辜者）是：

SELECT
    `things`.*
FROM
    `things`
WHERE
    `things`.`active` = 1
        AND (owner_id IS NOT NULL
        AND owner_id > 0)
        AND ((`things`.`status` IN (0 , 1)
        OR `things`.`status` IS NULL))
        AND (date < '2015-07-11 00:00:00');

和上有一个复合索引active，date两个版本都在使用它。

作为参考，输出SHOW CREATE TABLE（省略了不相关的列）：

CREATE TABLE `things` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) DEFAULT '0',
  `date` datetime DEFAULT NULL,
  `owner_id` int(11) DEFAULT '0',
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_things_on_active_and_date` (`active`,`date`),
  KEY `index_things_on_date` (`date`),
  KEY `index_things_on_owner_id` (`owner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1862 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

限制

玩了一会儿之后，我发现比较受查询限制的影响。对于较大的限制，>200，该*版本报告执行时间更快。随着限制减少<200，id版本获得优势。仍然不确定该怎么做...

解释

在查询的两个版本上运行EXPLAIN会产生相同的输出，带有 select_type:SIMPLE和 key: index_things_on_active_and_date。

mysql innodb

1 个回答

  1. Best Answer

    SELECT *您所说的和的 EXPLAIN 计划SELECT id是相同的，将由访问的行数分隔。如何访问行？通过index_things_on_active_and_date索引。EXPLAIN 中的SIMPLE表示这是一次扫描。在这两种情况下，它都是基于active=1和的索引扫描date < '2015-07-11 00:00:00'

    索引扫描是如何发生的？

    • 由于所选索引具有active和date，因此范围扫描将从两列开始。
    • 看WHERE条款。您正在检索owner_id和status检查值。这需要您访问整行。
      • SELECT *意味着您将整行作为结果集的一部分
      • SELECT id意味着您将 id 作为结果集的一部分

    这是通向哪里？

    • 在这两种情况下，您都在读取整行以检查不在索引中的另外两个列。
    • 每个查询的结果集是整行或单个列 (id)。
    • 创建较小的结果集必须花费一些额外的时间。为什么 ？有一点，更长的id值结果将花费更长的构建时间，而与您阅读的整行一起进行。您自己凭经验对此进行了测试，并发现了以下内容：
      • < 200 rows- >SELECT id更快
      • > 200 rows- >SELECT *更快
      • = 200 rows->SELECT id并且SELECT *大致相同

    关于 InnoDB，您还需要了解其他一些事情

    这告诉您索引index_things_on_active_and_date实际上有三列：1) active、2) date、3) id。

    您可能会说：为什么SELECT *跑步会更好SELECT id？（这是最初的问题）这又回到了我所说的：WHERE 子句导致查询优化器检查非索引列status和owner_id. 您正在创建额外的工作检查索引条目和索引行中的某些内容。

    如果你创建这个索引

    ALTER TABLE things
    ADD INDEX index_everything_and_kitchen_sink
    (active,date,owner_id,status)
;

    SELECT id并运行这两个查询，那么无论您访问多少行，您都会看到优势。为什么 ？因为WHERE 子句中的所有列都是从索引中检查的。这种类型的索引称为覆盖索引。

    这里有一些关于覆盖索引的好链接

    我在一些回答中提到了这些链接：

    • 2

相关问题