sql - 具有多部分 WHERE 的 MySQL/InnoDB 表的最佳键

Question

给定一个(简化的)表格

CREATE TABLE  `transactions` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CREATION_DT` datetime DEFAULT NULL,
  `STATUS_IND` int(11) DEFAULT NULL,
  `COMPANY_ID` bigint(20) DEFAULT NULL,
  `AMOUNT` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `FKE7E81F1ED170D4C9` (`COMPANY_ID`),
  KEY `RPTIDX_CREATION_DT` (`CREATION_DT`),
) ENGINE=InnoDB AUTO_INCREMENT=5241784 DEFAULT CHARSET=latin1;

以及一个常见的、相对昂贵的查询

UPDATE transactions
SET STATUS_IND = 5
WHERE
COMPANY_ID = ?
 and (STATUS_IND = 3 or STATUS_IND = 7)
 and CREATION_DT >= ? and CREATION_DT <= ?

我正在尝试确定是否有更好的索引策略。阅读最常见

SELECT * FROM transactions WHERE COMPANY_ID=? ORDER BY CREATION_DT

COMPANY_ID 是相当有选择性的(我们的表中有数百家公司，并且增长迅速)。 STATUS_IND 的选择性不是很强(有 5 种常见状态和一些不太常见的状态)，并且它的值经常变化(COMPANY_ID 和 CREATION_DT 从不变化)更改给定行)。 CREATION_DT 具有相当的选择性，并且随着我们向系统添加更多交易，选择性会变得越来越强。

我最初的想法是用包含 COMPANY_ID+CREATION_DT 或 COMPANY_ID+STATUS_IND+CREATION_DT 的复合键替换现有的两个键。或者，可能是 COMPANY_ID+CREATION_DT+STATUS_IND 并更改 Update 的 WHERE 子句中的顺序？

另外，是否有一个很好的引用资料来解释 InnoDB 如何使用二级复合索引？

Answer 1

My initial thought is to replace the two existing keys with a compound key...

这是比单独 key 更好的方法。
复合/覆盖索引/键从左到右执行，这意味着列表的顺序很重要，因为它是从左到右读取的。例如，如果您将索引定义为:COMPANY_ID、CREATION_DT、STATUS_IND，则以下组合可以触发索引的使用:

1. COMPANY_ID
2. COMPANY_ID、CREATION_DT
3. COMPANY_ID、CREATION_DT、STATUS_IND

因此，如果您只有 CREATION_DT 或 CREATION_DT、STATUS_IND - 则不会使用索引。

WHERE 子句中的顺序并不重要 - 只要引用列即可。

还要记住，MyISAM 表的所有索引的总长度最多只能为 1000 字节(InnoDB 表为 767 字节)，as designed .

<小时/>

顺便说一句:我重写了你的 UPDATE 语句:

UPDATE transactions
   SET STATUS_IND = 5
 WHERE COMPANY_ID = ?
   AND STATUS_IND IN (3, 7)
   AND CREATION_DT BETWEEN ? AND ?