MySQL 忽略索引问题

Question

在最近对我们网站的 MySQL (v5.5) 监控中，我们发现它经常生成如此繁重的查询:

SELECT b.PRODUCT_ID, b1.PRODUCT_ID, 1 FROM b_sale_basket b, b_sale_basket b1 
WHERE b.ORDER_ID = b1.ORDER_ID AND b.ORDER_ID = 15500 AND b.ID <> b1.ID AND
NOT EXISTS (SELECT 1 FROM b_sale_product2product d 
     WHERE d.PRODUCT_ID = b.PRODUCT_ID AND d.PARENT_PRODUCT_ID = b1.PRODUCT_ID);

它运行超过 60 秒。并返回 12K 行。

b_sale_product2product 有 5100 万条记录，b_sale_basket 有 640K 条记录。

所有使用的字段都有受尊重的索引。就是找不到为什么这么长的痛苦。

请帮忙。

表结构:

CREATE TABLE `b_sale_product2product` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PRODUCT_ID` int(11) NOT NULL,
  `PARENT_PRODUCT_ID` int(11) NOT NULL,
  `CNT` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `IXS_PRODUCT2PRODUCT_PRODUCT_ID` (`PRODUCT_ID`),
  KEY `IXS_PRODUCT2PRODUCT_PARENT_AND_PRODUCT_ID` (`PRODUCT_ID`,`PARENT_PRODUCT_ID`),
  KEY `IXS_PRODUCT2PRODUCT_PARENT_PRODUCT_ID` (`PARENT_PRODUCT_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=52036712 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `b_sale_basket` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `FUSER_ID` int(11) NOT NULL,
  `ORDER_ID` int(11) DEFAULT NULL,
  `PRODUCT_ID` int(11) NOT NULL,
  `PRODUCT_PRICE_ID` int(11) DEFAULT NULL,
  `PRICE` decimal(18,2) NOT NULL,
  `CURRENCY` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `DATE_INSERT` datetime NOT NULL,
  `DATE_UPDATE` datetime NOT NULL,
  `WEIGHT` double(18,2) DEFAULT NULL,
   /* ... skipped ... */
  PRIMARY KEY (`ID`),
  KEY `IXS_BASKET_LID` (`LID`),
  KEY `IXS_BASKET_USER_ID` (`FUSER_ID`),
  KEY `IXS_BASKET_ORDER_ID` (`ORDER_ID`),
  KEY `IXS_BASKET_PRODUCT_ID` (`PRODUCT_ID`),
  KEY `IXS_BASKET_PRODUCT_PRICE_ID` (`PRODUCT_PRICE_ID`),
  KEY `IXS_SBAS_XML_ID` (`PRODUCT_XML_ID`,`CATALOG_XML_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=962527 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

解释结果:

+----+--------------------+-------+------+----------------------------------------------------------------------------------------------------------------+--------------------------------+---------+-------------------------+------+-------------+
| id | select_type        | table | type | possible_keys                                                                                                  | key                            | key_len | ref                     | rows | Extra       |
+----+--------------------+-------+------+----------------------------------------------------------------------------------------------------------------+--------------------------------+---------+-------------------------+------+-------------+
|  1 | PRIMARY            | b     | ref  | IXS_BASKET_ORDER_ID                                                                                            | IXS_BASKET_ORDER_ID            | 5       | const                   |  179 | Using where |
|  1 | PRIMARY            | b1    | ref  | IXS_BASKET_ORDER_ID                                                                                            | IXS_BASKET_ORDER_ID            | 5       | const                   |  179 | Using where |
|  2 | DEPENDENT SUBQUERY | d     | ref  | IXS_PRODUCT2PRODUCT_PRODUCT_ID,IXS_PRODUCT2PRODUCT_PARENT_AND_PRODUCT_ID,IXS_PRODUCT2PRODUCT_PARENT_PRODUCT_ID | IXS_PRODUCT2PRODUCT_PRODUCT_ID | 4       | b.PRODUCT_ID |    1 | Using where |
+----+--------------------+-------+------+----------------------------------------------------------------------------------------------------------------+--------------------------------+---------+-------------------------+------+-------------+

Answer 1

请尝试添加 -

Force index   (IXS_PRODUCT2PRODUCT_PARENT_AND_PRODUCT_ID)

---

SELECT b.PRODUCT_ID, b1.PRODUCT_ID, 1 FROM b_sale_basket b, b_sale_basket b1 
WHERE b.ORDER_ID = b1.ORDER_ID AND b.ORDER_ID = 15500 AND b.ID <> b1.ID AND
NOT EXISTS (SELECT 1 
             FROM b_sale_product2product d 
                      Force index (IXS_PRODUCT2PRODUCT_PARENT_AND_PRODUCT_ID)
             WHERE d.PRODUCT_ID = b.PRODUCT_ID AND d.PARENT_PRODUCT_ID = b1.PRODUCT_ID )