主页 / dba / 问题 / 188091
Accepted
Yunus UYANIK
Asked: 2017-10-11 02:35:39 +0800 CST

使用索引的 MySQL 查询性能

  • 772

Campaign Code 表中有 7700 万条记录。

指数声明：

索引 IDX_CampaignCode_Id_CodeDateId_CodeId_CustomerId

(CampaignId, CampaignCodeDateId, PKCampaignCodeId, CustomerId)

第一次查询，执行时间：0:00:0.546

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 18
AND cc.CampaignCodeDateId = 19325
ORDER BY cc.PKCampaignCodeId LIMIT 20000 OFFSET 0;

在此处输入图像描述

第二个查询，执行时间：0:01:11.597

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 30
AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

在此处输入图像描述

第二个问题不需要更快吗？我应该怎么办？

编辑 ：

我为第二个查询创建了索引。

INDEX IDX_Test_Index

(CampaignId, CustomerId,PKCampaignCodeId)

创建索引后，第二个查询更快，但第一个查询更慢

第一个查询执行计划已更改：执行时间：0:01:17.236 在此处输入图像描述

CREATE TABLE CampaignCode (
  PKCampaignCodeId int(11) NOT NULL AUTO_INCREMENT,
  CampaignId int(11) NOT NULL,
  Code varchar(255) NOT NULL,
  CreatedBy int(11) NOT NULL,
  CreatedOn datetime NOT NULL,
  CustomerId int(11) DEFAULT NULL,
  IsActive bit(1) NOT NULL,
  IsUsed bit(1) DEFAULT NULL,
  ModifiedBy int(11) DEFAULT NULL,
  ModifiedOn datetime DEFAULT NULL,
  OrderNumber varchar(255) DEFAULT NULL,
  CampaignCodeDateId int(11) NOT NULL,
  PRIMARY KEY (PKCampaignCodeId),
  INDEX IDX_CampaignCode_CampaignId (CampaignId),
  INDEX IDX_CampaignCode_CampaignId_CodeDateId_CodeId_CampaignCodeId
            (CampaignId, CampaignCodeDateId, PKCampaignCodeId),
  INDEX IDX_CampaignCode_Code (Code),
  INDEX IDX_Test_Index (CampaignId, CustomerId, PKCampaignCodeId)
)
ENGINE = INNODB
AUTO_INCREMENT = 114306664
AVG_ROW_LENGTH = 61
CHARACTER SET latin5
COLLATE latin5_turkish_ci
ROW_FORMAT = DYNAMIC;
mysql index

3 个回答

  1. Best Answer

    没有完美的索引：

    WHERE cc.CampaignId = 30
  AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId

    这是因为索引无法通过IS NOT NULL以到达ORDER BY.

    一般来说，

    WHERE a=1 AND b=2 ORDER BY c

    可以充分利用INDEX(a,b,c)或(b,a,c)

    但是，这不能：

    WHERE a=1 AND b!=2 ORDER BY c

    （IS NOT NULL类似于!= 2它是一个“范围”，而不是单个值。）

    要对此进行优化：

    WHERE cc.CampaignId = 30
  AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

    有两种可能性。优化器会选择一个，有时是不太理想的一个：

    INDEX(CampaignId, CustomerId)
INDEX(CampaignId, PKCampaignCodeId)

    第一个会做过滤，但仍然需要排序和限制。
    第二个将进行一些过滤，避免排序，但可能需要查看超过 25 行的内容。

    索引中包含所有 3 列没有任何好处；第三列将不会被使用。（而且，显然，它会给您的第一个SELECT.

    有关创建索引的更多信息：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql （尽管它没有完全解决您的所有问题）

    • 2

  2. 第二个问题不需要更快吗？我应该怎么办？

    没有第二个查询不会使用索引，因为在复合非聚集索引中条件很重要的排序和列。

    因此，为了在第二个查询中使用索引，您应该使用另一个复合非聚集索引。

    INDEX IDX_CampaignCode_Id_CodeDateId_CodeId_CustomerId1

(CampaignId, CustomerId,PKCampaignCodeId)

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 30
AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

    还要避免使用 *，改用列名。

    • 0

  3. 第二个查询甚至不使用索引，而是使用 PRIMARY KEY。如果您在表上没有太多写入，您可以保留两个索引，因此 INDEX IDX_CampaignCode_CampaignId_CodeDateId_CodeId_CampaignCodeId（CampaignId、CampaignCodeDateId、PKCampaignCodeId）和 INDEX IDX_Test_Index（CampaignId、CustomerId、PKCampaignCodeId）。

    如果您需要其他建议，请参考此处的索引：INDEX IDX_CampaignCode_CampaignId (CampaignId) 是多余的。它不会被使用，您可以将其丢弃。

    • 0

相关问题