Учитывая (упрощенную) таблицу
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 использует вторичные индексы соединения?
Моя первоначальная мысль заключается в замене двух существующих ключей на составной ключ...
Это лучший подход, чем отдельные ключи.
Компонент/индекс покрытия/ключ выполняется слева направо, что означает, что порядок списка важен, поскольку он читается слева направо. Например, если вы определяете индекс как: COMPANY_ID
, CREATION_DT
, STATUS_IND
, следующие комбинации могут инициировать использование индекса:
COMPANY_ID
COMPANY_ID
, CREATION_DT
COMPANY_ID
, CREATION_DT
, STATUS_IND
Итак, если у вас только CREATION_DT
или CREATION_DT
, STATUS_IND
- индекс не будет использоваться.
Порядок не имеет значения в предложении WHERE - до тех пор, пока на столбцы ссылаются.
Имейте в виду, что все индексы таблицы MyISAM могут суммировать только до 1000 байтов (767 байт для таблиц InnoDB), как разработано.
BTW: Я переписал ваш оператор UPDATE:
UPDATE transactions
SET STATUS_IND = 5
WHERE COMPANY_ID = ?
AND STATUS_IND IN (3, 7)
AND CREATION_DT BETWEEN ? AND ?