mysql - 当列是复合键的一部分时不使用索引

Question

我有两个巨大的表，我从中选择了大量的数据。

表存储采购订单详细信息和产品信息。

    PURCHASE_ORDER_DETAILS.
    CREATE TABLE `PURCHASE_ORDER_DETAILS` (
    `PURCHASE_ORDER_NUMBER_PF` INT(20) NOT NULL,
    `PRODUCT_CODE_PF` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
    `ORDER_QUANTITY` INT(8) DEFAULT NULL,
    `UNIT_PRICE` DECIMAL(12,2) DEFAULT NULL,
    `ORDER_FULLFILLMENT_DUE_DATE` DATETIME DEFAULT NULL,
    `DELIVERY_ADDRESS` VARCHAR(64) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    `DELIVERY_CITY` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
     `DELIVERY_ZIP` BIGINT(10) DEFAULT NULL,
     `other columns`
      PRIMARY KEY (`PURCHASE_ORDER_NUMBER_PF`,`PRODUCT_CODE_PF`),
      KEY `RMAPWBTX_PUCH_ORDE_DLST_INDX` (`DELIVERY_STATE_ID_FK`),
      KEY `RMAPWBTX_PUCH_ORDE_DLTY_INDX` (`DELIVERY_TYPE_FK`),
      KEY `RMAPWBTX_PUCH_ORDE_TACO_INDX` (`TAX_CODE_FK`),
      KEY `RMAPWBMS_PUOR_DETL_PDCO_FK` (`PRODUCT_CODE_PF`),
      KEY `RMAPWBTX_PUOR_DETL_TACO_FK` (`TAX_CODE_FK`),
      KEY `CREATED_DATE_INDX` (`CREATED_DATE`),
      KEY `MODIFIED_DATE_INDX` (`MODIFIED_DATE`),
      CONSTRAINT `RMAPWBMS_PUOR_DETL_PDCO_FK` FOREIGN KEY (`PRODUCT_CODE_PF`) 
      REFERENCES `PRODUCT` (`PRODUCT_CODE_PK`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `RMAPWBMS_PUOR_DETL_PONU_FK` FOREIGN KEY 
      (`PURCHASE_ORDER_NUMBER_PF`) REFERENCES `PURCHASE_ORDER` 
      (`PURCHASE_ORDER_NUMBER_PK`),
     CONSTRAINT `RMAPWBTX_PO_DETL_DSID_FK` FOREIGN KEY 
     (`DELIVERY_STATE_ID_FK`) REFERENCES `STATE` (`STATE_ID_PK`),
     CONSTRAINT `RMAPWBTX_PUOR_DETL_TACO_FK` FOREIGN KEY (`TAX_CODE_FK`) 
     REFERENCES `TAX` (`TAX_CODE_PK`) ON DELETE NO ACTION ON UPDATE NO ACTION
     ) ENGINE=INNODB DEFAULT CHARSET=latin1;


PRODUCT

    CREATE TABLE `PRODUCT` (
    `PRODUCT_CODE_PK` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
    `PRODUCT_DESC` VARCHAR(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    `FEE_BILL_CODE` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    `other columns`
     PRIMARY KEY (`PRODUCT_CODE_PK`),
     KEY `CREATED_DATE_INDX` (`CREATED_DATE`),
     KEY `MODIFIED_DATE_INDX` (`MODIFIED_DATE`),
     KEY `PRODUCT_EXCO_FK` (`EXPENSE_CODE_ID_FK`),
     KEY `FK_PRODUCT_ENTITY_TYPE` (`ENTITY_TYPE_CODE_FK`),
     CONSTRAINT `FK_PRODUCT_ENTITY_TYPE` FOREIGN KEY (`ENTITY_TYPE_CODE_FK`) REFERENCES `ENTITY_TYPE` (`ENTITY_TYPE_CODE_PK`)
     ) ENGINE=INNODB DEFAULT CHARSET=latin1

     Below query is taking ~10min to get ~1M records.

     EXPLAIN SELECT * FROM
     PURCHASE_ORDER_DETAILS POD
     JOIN PRODUCT PRD ON POD.PRODUCT_CODE_PF=PRD.PRODUCT_CODE_PK;
+----+-------------+-------+------+----------------------------+-----------------

    -----------+---------+-----------------------------------------------+-------+-------+
    | id | select_type | table | type | possible_keys              | key                        | key_len | ref                                           | rows  | Extra |
    +----+-------------+-------+------+----------------------------+----------------------------+---------+-----------------------------------------------+-------+-------+
    |  1 | SIMPLE      | PRD   | ALL  | PRIMARY                    | NULL                       | NULL    | NULL                                          | 14283 | NULL  |
    |  1 | SIMPLE      | POD   | ref  | RMAPWBMS_PUOR_DETL_PDCO_FK | RMAPWBMS_PUOR_DETL_PDCO_FK | 34      | REALREMIT_PROD_ALTISOURCE.PRD.PRODUCT_CODE_PK |    40 | NULL  |
    +----+-------------+-------+------+----------------------------+----------------------------+---------+-----------------------------------------------+-------+---

编辑1:上面的查询是一个示例，下面是我尝试获取 1M 记录的实际查询(主表 POD 有 22M 记录)。

SELECT `some columns`
FROM `REALREMIT_PPIPFC_MIG`.MIGR_ORDER_DENORM MPO
INNER JOIN PURCHASE_ORDER_DETAILS POD 
ON MPO.PURCHASE_ORDER_NUMBER_PK=POD.PURCHASE_ORDER_NUMBER_PF
INNER JOIN PRODUCT PRD 
ON POD.PRODUCT_CODE_PF=PRD.PRODUCT_CODE_PK 
INNER JOIN EXPENSE_CODE EXP
ON PRD.EXPENSE_CODE_ID_FK=EXP.EXPENSE_CODE_ID_PK 
WHERE MPO.BATCH_ID=1;

解释上述查询的输出

+----+-------------+-------+--------+-------------------------------------+----------------------------+---------+--------------------------------------------------------+-------+-------------+
| id | select_type | table | type   | possible_keys                       | key                        | key_len | ref                                                    | rows  | Extra       |
+----+-------------+-------+--------+-------------------------------------+----------------------------+---------+--------------------------------------------------------+-------+-------------+
|  1 | SIMPLE      | PRD   | ALL    | PRIMARY,PRODUCT_EXCO_FK             | NULL                       | NULL    | NULL                                                   | 14283 | NULL        |
|  1 | SIMPLE      | EXP   | eq_ref | PRIMARY                             | PRIMARY                    | 4       | REALREMIT_PROD_ALTISOURCE.PRD.EXPENSE_CODE_ID_FK       |     1 | NULL        |
|  1 | SIMPLE      | POD   | ref    | PRIMARY,RMAPWBMS_PUOR_DETL_PDCO_FK  | RMAPWBMS_PUOR_DETL_PDCO_FK | 34      | REALREMIT_PROD_ALTISOURCE.PRD.PRODUCT_CODE_PK          |    40 | NULL        |
|  1 | SIMPLE      | MPO   | ref    | MIGR_PO_NBR_INDX,MIGR_BATCH_ID_INDX | MIGR_PO_NBR_INDX           | 4       | REALREMIT_PROD_ALTISOURCE.POD.PURCHASE_ORDER_NUMBER_PF |     1 | Using where |
+----+-------------+-------+--------+-------------------------------------+----------------------------+---------+--------------------------------------------------------+-------+-------------+
4 rows in set (0.20 sec)

Both database have same charset Columns used in join have the same collate Both tables have same charset

我创建了新表，其中主键是连接列 - PURHCASE_ORDER_NUMBER_PF 和 PRODUCT_CODE_PF，然后我在 PRODUCT_CODE_PF 上添加了新索引在这种情况下是否会使用索引和/或这是在连接中使用索引的最佳方式。

谢谢

Answer 1

这些可能有帮助:

MPO:  INDEX(BATCH_ID, PURCHASE_ORDER_NUMBER_PK)   -- in this order
EXP:  INDEX(EXPENSE_CODE_ID_PK)   -- unless it is the PRIMARY KEY

但是，在不知道“某些专栏”中有什么内容的情况下，我无法预测它们会有多大帮助。对 MPO 和 EXP 进行 SHOW CREATE TABLE 会有帮助。

你有多少内存？ innodb_buffer_pool_size 的值是多少？我这么问是因为你可能会很痛苦。