我应该在 MySQL 表中放置索引

Question

我有以下三个 WHERE 子句：

WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight = $maxImageHeight

WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight != $maxImageHeight

WHERE primaryId = $imgId AND imgWidth != $maxImageWidth AND imgHeight = $maxImageHeight"

它们作用于通过 UNION ALL 连接到查询中的两个 MySQL InnoDB 表。

我不确定应该如何在这两个表中设置索引；我是否应该有一个带有 imgWidth 和 imgHeight 的多列索引，或者是否还应该包含 primaryId ？

一个查询真的只能使用一个索引吗？如果没有，我可以将每一列设置为索引吗？

或者在这种情况下多列索引不起作用？

---

以下是第一个 WHERE 子句的整个查询示例。其他都是相同的，具有各自的子句：

SELECT 'allEqual' AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight = $maxImageHeight

这是 primary_images 表的架构：

CREATE  TABLE IF NOT EXISTS `new_arrivals_images`.`primary_images` (
  `imgId` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `imgTitle` VARCHAR(255) NULL DEFAULT NULL ,
  `view` VARCHAR(45) NULL DEFAULT NULL ,
  `secondary` ENUM('true','false') NOT NULL DEFAULT false ,
  `imgURL` VARCHAR(255) NULL DEFAULT NULL ,
  `imgWidth` SMALLINT(6) UNSIGNED NULL DEFAULT NULL ,
  `imgHeight` SMALLINT(6) UNSIGNED NULL DEFAULT NULL ,
  `imgDate` DATETIME NULL DEFAULT NULL ,
  `imgClass` ENUM('Jeans','T-Shirts','Shoes','Dress Shirts','Trackwear & Sweatwear') NULL DEFAULT NULL ,
  `imgFamily` ENUM('Hugo Boss','Lacoste','True Religion','7 For All Mankind','Robin\'s Jeans','Robert Graham') NULL DEFAULT NULL ,
  `imgGender` ENUM('Men\'s','Women\'s') NOT NULL DEFAULT Mens ,
  PRIMARY KEY (`imgId`) ,
  UNIQUE INDEX `imgDate_UNIQUE` (`imgDate` DESC) )
ENGINE = InnoDB;

以及 secondary_images 的架构表：

CREATE  TABLE IF NOT EXISTS `new_arrivals_images`.`secondary_images` (
  `imgId` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `primaryId` SMALLINT(6) UNSIGNED NOT NULL ,
  `view` VARCHAR(45) NULL DEFAULT NULL ,
  `imgURL` VARCHAR(255) NULL DEFAULT NULL ,
  `imgWidth` SMALLINT(6) UNSIGNED NULL DEFAULT NULL ,
  `imgHeight` SMALLINT(6) UNSIGNED NULL DEFAULT NULL ,
  `imgDate` DATETIME NULL DEFAULT NULL ,
  PRIMARY KEY (`imgId`, `primaryId`) ,
  INDEX `fk_secondary_images_primary_images` (`primaryId` ASC) ,
  UNIQUE INDEX `imgDate_UNIQUE` (`imgDate` DESC) ,
  CONSTRAINT `fk_secondary_images_primary_images`
    FOREIGN KEY (`primaryId` )
    REFERENCES `new_arrivals_images`.`primary_images` (`imgId` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Answer 1

一次查询真的只能使用一个索引吗？

不，那太愚蠢了。

如果没有，我可以将每一列设置为索引吗？

是的，这是一个选项，但前提是您彼此独立地使用该列。
如果您总是组合字段（就像您在这里所做的那样），那么使用复合索引会更有效。

我不知道应该如何在这两个表中设置索引；我是否应该有一个带有 imgWidth 和 imgHeight 的多列索引，或者是否也应该包括primaryId？

如果您想可以使用组合索引 (imgWidth, imgHeight)
但您必须记住，如果不在 where 子句中使用 imgWidth，则无法访问 imgHeight 上的索引。
您必须始终使用复合索引的最左边部分（或全部）。

在 InnoDB 上，主键始终包含在每个二级索引中，因此包含它会适得其反。

增加了 InnoDB 的奖励
如果只选择索引字段，InnoDB将永远不会真正读取表数据，因为所需的所有数据都在索引中。这会大大加快速度。

您有一个 SQL 注入漏洞
你的代码似乎有一个 SQL 注入漏洞。请将所有 $var 用单引号引起来： where field1 = '$var' ... 并且不要忘记使用 $var = mysql_real_escape_string($var);在将它们注入查询之前。 参阅：“Bobby Tables”XKCD 漫画中的 SQL 注入是如何工作的？

请 查询应读取的速度和安全性：

SELECT 'allEqual' AS COL1, COUNT(*) AS imgCount FROM (
    SELECT imgId AS primaryId FROM primary_images pi
    WHERE pi.ImgId = '$imgId' 
      AND pi.imgWidth = '$maxImageWidth' 
      AND pi.imgHeight = '$maxImageHeight'
    UNION ALL 
    SELECT primaryId FROM secondary_images si
    WHERE si.primaryId = '$imgId' 
      AND si.imgWidth = '$maxImageWidth'    
      AND si.imgHeight = '$maxImageHeight'  
) AS union_table                      

这样将使用正确的索引并且不会检索不需要的数据。
MySQL 无法对联合数据使用索引，因为它是两个不同表的合并。这就是为什么您需要在内部选择中执行 where 的原因。

Answer 2

您的 PrimaryId 列是否有重复项？或者它是主键吗？如果它是主键，那么它也将充当精细索引。在InnoDB中，如果它是主键，它可能已经是索引了。

换句话说，您的 WHERE 子句 primaryId = $imgId 的区分度如何？如果它通常不匹配任何行，或者恰好匹配一行，或者只匹配几行，那么另一个索引不会有太大帮助。如果它匹配数百或数千行，另一个索引可能会有所帮助。

查询肯定可以使用多个索引。

在这种情况下，最大的问题是“你想做什么？”您似乎正在尝试选择其中一个或两个尺寸与您的输入相匹配的图像。

考虑通过重做逻辑并删除 UNION ALL 子句（变成三个查询）来提高效率。

    WHERE primaryId = $imgId 
      AND (imgWidth = $maxImageWidth OR imgHeight = $maxImageHeight)