MySQL：索引不用于单个或连接查询

I'm using MySQL version 5.6.19 and I have this issue. I don't know if there any very basic topic I have forgotten, or it's actually something strange.

我正在使用MySQL版本5.6.19，我有这个问题。我不知道是否有任何我已经忘记的基本话题，或者它实际上是奇怪的。

Well, I have customers, codes (related to customers) and products. The products are related directly with the customer or the code.

好吧，我有客户，代码（与客户有关）和产品。产品直接与客户或代码相关。

The example script is this:

示例脚本是这样的：

    DROP TABLE IF EXISTS `CUSTOMERS`;
CREATE TABLE `CUSTOMERS` (
  `REQ_ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `CUSTOMER_ID` VARCHAR(15) COLLATE LATIN1_SPANISH_CI NOT NULL ,
  `SEGMENT_ID` INT(10) UNSIGNED NOT NULL,
  `STATUS` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`REQ_ID`),
  KEY `IDX_CUSTOMER_ID` (`CUSTOMER_ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1 COLLATE=LATIN1_SPANISH_CI;

INSERT INTO `CUSTOMERS` (REQ_ID,CUSTOMER_ID,SEGMENT_ID,STATUS) VALUES (1,'CA.5310',1,1);
INSERT INTO `CUSTOMERS` (REQ_ID,CUSTOMER_ID,SEGMENT_ID,STATUS) VALUES (2,'KH.5880',1,1);
INSERT INTO `CUSTOMERS` (REQ_ID,CUSTOMER_ID,SEGMENT_ID,STATUS) VALUES (3,'QQ.4639',1,1);
INSERT INTO `CUSTOMERS` (REQ_ID,CUSTOMER_ID,SEGMENT_ID,STATUS) VALUES (4,'TH.7692',1,1);
INSERT INTO `CUSTOMERS` (REQ_ID,CUSTOMER_ID,SEGMENT_ID,STATUS) VALUES (5,'YU.6972',1,1);
INSERT INTO `CUSTOMERS` (REQ_ID,CUSTOMER_ID,SEGMENT_ID,STATUS) VALUES (6,'ZA.7926',1,1);

COMMIT;

DROP TABLE IF EXISTS `CUSTOMER_CODES`;
CREATE TABLE `CUSTOMER_CODES` (
  `REQ_ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `CODE_ID` VARCHAR(20) COLLATE LATIN1_SPANISH_CI NOT NULL,
  `CUSTOMER_ID` VARCHAR(15) COLLATE LATIN1_SPANISH_CI NOT NULL,
  PRIMARY KEY (`REQ_ID`),
  KEY `IDX_CODE_ID` (`CODE_ID`),
  KEY `IDX_CUSTOMER_ID` (`CUSTOMER_ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1 COLLATE=LATIN1_SPANISH_CI;

INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (1,'AW-5332-R-918806','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (2,'CA-7600-F-419496','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (3,'DJ-6557-X-562485','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (4,'DL-6266-L-449657','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (5,'DS-9853-K-509896','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (6,'EF-6540-Z-075718','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (7,'EY-4689-L-798950','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (8,'HU-9798-W-580898','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (9,'HW-2352-Q-563124','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (10,'IJ-0691-P-347580','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (11,'IY-1366-C-744557','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (12,'JX-2997-A-619073','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (13,'KW-8547-V-632604','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (14,'LD-3972-X-404768','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (15,'OH-8414-Q-681690','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (16,'QB-3065-E-961964','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (17,'RS-8531-A-605359','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (18,'RT-0093-P-934263','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (19,'SR-9752-I-936573','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (20,'TT-1675-V-069568','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (21,'UX-9967-P-917243','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (22,'VB-1578-E-917704','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (23,'XT-9346-I-369510','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (24,'YD-5031-S-685069','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (25,'ZU-5811-D-519507','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (26,'AI-8578-P-297337','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (27,'BV-8935-R-335510','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (28,'CE-5544-B-140242','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (29,'DU-4606-X-292810','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (30,'HU-9125-V-564299','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (31,'JM-2356-C-286826','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (32,'KP-2970-D-251577','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (33,'OV-8244-W-588479','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (34,'RL-4228-G-538911','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (35,'VO-3663-O-065655','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (36,'YJ-8463-N-388866','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (37,'ZV-5622-T-443263','KH.5880');
COMMIT;

DROP TABLE IF EXISTS `CODE_OR_CUSTOMER_PRODUCTS`;
CREATE TABLE `CODE_OR_CUSTOMER_PRODUCTS` (
  `REL_ID` INT(11) NOT NULL AUTO_INCREMENT,
  `CODE_OR_CUSTOMER_ID` VARCHAR(20) COLLATE LATIN1_SPANISH_CI NOT NULL,
  `PRODUCT_ID` VARCHAR(55) COLLATE LATIN1_SPANISH_CI NOT NULL,
  `START_DATE` DATETIME NOT NULL,
  `END_DATE` DATETIME DEFAULT NULL,
  PRIMARY KEY (`REL_ID`),
  KEY `IDX_ENTIDAD` (`CODE_OR_CUSTOMER_ID`),
  KEY `IDX_ENTIDAD_FECHAS` (`CODE_OR_CUSTOMER_ID`,`START_DATE`,`END_DATE`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1 COLLATE=LATIN1_SPANISH_CI;

INSERT INTO `CODE_OR_CUSTOMER_PRODUCTS` (REL_ID,CODE_OR_CUSTOMER_ID,PRODUCT_ID,START_DATE,END_DATE) VALUES (1,'AW-5332-R-918806','DISC_01','2013-12-15 00:00:00',NULL);
INSERT INTO `CODE_OR_CUSTOMER_PRODUCTS` (REL_ID,CODE_OR_CUSTOMER_ID,PRODUCT_ID,START_DATE,END_DATE) VALUES (2,'AW-5332-R-918806','DISC_02','2014-01-13 00:00:00','2014-01-29 00:00:00');
INSERT INTO `CODE_OR_CUSTOMER_PRODUCTS` (REL_ID,CODE_OR_CUSTOMER_ID,PRODUCT_ID,START_DATE,END_DATE) VALUES (3,'AW-5332-R-918806','DISC_02','2014-02-01 00:00:00',NULL);
INSERT INTO `CODE_OR_CUSTOMER_PRODUCTS` (REL_ID,CODE_OR_CUSTOMER_ID,PRODUCT_ID,START_DATE,END_DATE) VALUES (4,'CA.5310','DISC_03','2013-01-01 00:00:00',NULL);

COMMIT;

I'm trying this approach because I don't want to take customer-code relation to CODE_OR_CUSTOMERS_PRODUCTS table.

我正在尝试这种方法，因为我不想将客户代码关系带到CODE_OR_CUSTOMERS_PRODUCTS表。

When I run EXPLAIN for this query :

当我为此查询运行EXPLAIN时：

EXPLAIN SELECT 
CODE_OR_CUSTOMER_ID, 
PRODUCT_ID
FROM CODE_OR_CUSTOMER_PRODUCTS
where 
    ( 
    CODE_OR_CUSTOMER_ID = 'CA.5310'
    or
    CODE_OR_CUSTOMER_ID IN (
                SELECT CODE_ID
                FROM CUSTOMERS, CUSTOMER_CODES
                where CUSTOMERS.CUSTOMER_ID = CUSTOMER_CODES.CUSTOMER_ID
                and CUSTOMERS.CUSTOMER_ID = 'CA.5310'
            )
    )
    and START_DATE <= '2014-01-31' and (END_DATE IS NULL OR '2014-01-01' <= END_DATE)

I've got a full-table access to CODE_OR_CUSTOMERS_PRODUCTS:

我有一个对CODE_OR_CUSTOMERS_PRODUCTS的全表访问：

*'1', 'PRIMARY', 'CODE_OR_CUSTOMER_PRODUCTS', '**ALL**', 'IDX_ENTIDAD,IDX_ENTIDAD_FECHAS', NULL, NULL, NULL, '4', 'Using where'*

I think this is because of the IN clause, but I don't want to query and requery lot of times (maybe hundreds) for a single CUSTOMER_ID and its related CODE_ID.

我认为这是因为IN子句，但我不想为单个CUSTOMER_ID及其相关的CODE_ID查询和重新查询很多次（可能是数百次）。

How can I avoid this all table access? I need some fresh eyes for this, or maybe another approach. Any suggestion would be very, very helpful.

如何避免所有表访问？我需要一些新鲜的眼睛，或者可能是另一种方法。任何建议都会非常非常有用。

Thanks a lot

非常感谢

2 个解决方案

#1

It is possible to eliminate the IN clause using JOINs. But it should also be possible to remove the OR by having 2 simpler queries UNIONed together:-

可以使用JOIN消除IN子句。但是也应该可以通过将2个更简单的查询联合在一起来删除OR： -

SELECT CODE_OR_CUSTOMER_ID, 
    PRODUCT_ID
FROM CODE_OR_CUSTOMER_PRODUCTS
WHERE CODE_OR_CUSTOMER_ID = 'CA.5310'
AND START_DATE <= '2014-01-31' AND (END_DATE IS NULL OR '2014-01-01' <= END_DATE)
UNION
SELECT CODE_OR_CUSTOMER_ID, 
    PRODUCT_ID
FROM CODE_OR_CUSTOMER_PRODUCTS
INNER JOIN CUSTOMER_CODES ON CUSTOMER_CODES.CODE_ID = CODE_OR_CUSTOMER_PRODUCTS.CODE_OR_CUSTOMER_ID
INNER JOIN CUSTOMERS ON CUSTOMERS.CUSTOMER_ID = CUSTOMER_CODES.CUSTOMER_ID AND CUSTOMERS.CUSTOMER_ID = 'CA.5310'
WHERE  START_DATE <= '2014-01-31' AND (END_DATE IS NULL OR '2014-01-01' <= END_DATE)

This does seem to ignore indexes with your test data but I suspect that is down to the limited data.

这似乎忽略了测试数据的索引，但我怀疑这取决于有限的数据。

#2

The reason no index in CODE_OR_CUSTOMER_PRODUCTS is used is because you have PRODUCT_ID in the selection list and it is not contained in any index. So once a qualifying row is found, another logical IO would have to be performed to obtain the value of that field. Since the rows are small and there are very few rows, the optimizer just read in the entire table in one swell foop and saved some IO. If your actual table contains many rows and/or the rows contains many fields which you have omitted for simplicity, then the IDX_ENTIDAD_FECHAS index might be used for the search with additional logical IOs executed as needed (nominally a very small percentage of the time; that is, few rows make it through the filter).

使用CODE_OR_CUSTOMER_PRODUCTS中没有索引的原因是因为您在选择列表中有PRODUCT_ID，并且它不包含在任何索引中。因此，一旦找到符合条件的行，就必须执行另一个逻辑IO以获取该字段的值。由于行很小并且行数很少，优化器只需在整个表中读取一个swell foop并保存一些IO。如果您的实际表包含许多行和/或行包含许多您为简单起见省略的字段，那么IDX_ENTIDAD_FECHAS索引可能会用于搜索，并根据需要执行额外的逻辑IO（名义上只占很小的时间百分比;是，很少行通过过滤器）。

You could add PRODUCT_ID to the index and thus make it a complete covering index. Then the index would be used, I guarantee it.

您可以将PRODUCT_ID添加到索引中，从而使其成为完整的覆盖索引。然后将使用索引，我保证。

If I may, this is a very awkward design. But that is not your point of concern so I'll hold my comments on that. However, in working with designs that have Startxxx/Endxxx columns, typically the search is for a date that lies between the begin/end dates: search_date >= START and search_date < END. If that is the case here, then where you have START_DATE <= '2014-01-31' and '2014-01-01' <= END_DATE, you actually mean START_DATE >= '2014-01-01' and '2014-01-31' > END_DATE. Otherwise, your WHERE clause doesn't make sense.

如果可以的话，这是一个非常尴尬的设计。但这不是你的关注点，所以我会对此发表评论。但是，在处理具有Startxxx / Endxxx列的设计时，通常搜索的日期位于开始/结束日期之间：search_date> = START和search_date ='2014-01-01'和'2014- 01-31'> END_DATE。否则，您的WHERE子句没有意义。。如果是这种情况，那么您的start_date>

#1