Ivaylo's questions

Meu entendimento é que ao projetar um índice composto, uma coluna que será usada para testes de intervalo deve ser colocada no final desse índice, porque o uso das colunas em índices compostos para após a primeira coluna de intervalo.

O que EXPLAINacontece é que também uma coluna após uma coluna de intervalo é usada pelo otimizador. É por isso que estou confuso e não tenho certeza de como projetar corretamente meus índices compostos.

Abaixo está um exemplo simplificado da minha tabela. Para cada data DatePhá um número de objetos Object. Cada um Objecttem 3 subobjetos subjacentes subObject. subObjecté um nome exclusivo para cada um DatePe é derivado Objectda adição de um número. subObjectpossui um identificador inteiro subObjectId, que geralmente é 0, 1 ou 2 e é único dentro do mesmo Object.

CREATE TABLEo código está abaixo. O mecanismo de tabela é MyISAM e o servidor é MySQL 8.

CREATE TABLE `testTab` (
  `DateP` date NOT NULL,
  `Object` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `subObject` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `subObjectId` int NOT NULL,
  `counter1` int DEFAULT NULL,
  ......
  PRIMARY KEY (`subObject`,`DateP`,`subObjectId`) USING BTREE,
  KEY `Object` (`Object`,`DateP`,`subObjectId`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC 

A coluna mais exclusiva de é KEY, Objectseguida por DateP. O menos exclusivo é subObjectId. Pelo que eu sei, a exclusividade das colunas também é importante para sua ordem em um índice composto - a mais exclusiva deve estar na posição mais à esquerda.

A seguir você pode ver diferentes versões EXPLAINdependendo das condições da WHEREcláusula.

1. Todas as três colunas do índice composto são apresentadas. No meio está o teste de intervalo de DateP. No entanto, subObjectIda coluna também é usada pelo Optimizer após o teste de alcance. key_lené 31 .

EXPLAIN SELECT DateP,Object,SUM(counter1)
FROM testTab
WHERE Object='aaa' AND DateP>='2019-06-01' AND DateP<='2019-06-10' AND (subObjectId=0 OR subObjectId=1)
GROUP BY Object,DateP

+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | testTab | NULL       | range | Object        | Object | 31      | NULL |   28 |    19.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+

2. A primeira e a segunda colunas são apresentadas na WHEREcláusula, finalizando com o teste de intervalo. key_lené 27 .

EXPLAIN SELECT DateP,Object,SUM(counter1)
FROM testTab
WHERE Object='aaa' AND DateP>='2019-06-01' AND DateP<='2019-06-10'
GROUP BY Object,DateP

+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | testTab | NULL       | range | Object        | Object | 27      | NULL |   29 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+

3. Apenas a primeira coluna é usada em WHERE. key_lené 24 .

EXPLAIN SELECT DateP,Object,SUM(counter1)
FROM testTab
WHERE Object='aaa' 
GROUP BY Object,DateP

+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | testTab | NULL       | ref  | Object        | Object | 24      | const |  417 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+

Minha conclusão é que todas as três colunas do índice composto podem ser usadas, apesar de no meio haver uma coluna para testes de intervalo. Mas posso esperar que o otimizador sempre respeite a terceira coluna?

No meu design atual KEYé desenhado com DatePcoluna no final.

KEY `Object` (`Object`,`subObjectId`,`DateP`) USING BTREE

Mas esta ordem não é adequada para GROUP BYcláusula, que é:

GROUP BY Object,DateP

Neste caso, para agrupamento, acho que apenas Objecta coluna é usada, porque pulo a coluna do meio DateP. A execução da consulta termina com Using temporary.

+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | testTab | NULL       | range | Object        | Object | 31      | NULL |   20 |   100.00 | Using index condition; Using temporary |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------------+