Индекс в столбце TIMESTAMP не используется при сравнении в предложении WHERE с этим столбцом

#mysql #sql #database #indexing

#mysql #sql #База данных #индексирование

Вопрос:

Наличие двух таблиц, подобных:

 FOO(ID, A, B, C, D, E, G(timestamp))
BAR(ID, X, Y, Z, FK_FOO_ID, W)

и наличие index таких:

 CREATE INDEX IDX_TEST ON FOO(G)

Следующий DESCRIBE запрос не будет использовать созданный индекс:

 DESC SELECT F.A, F.B, F.C, F.D, F.E,
  SUM(CASE WHEN B.X IN (0, 12) THEN B.Y ELSE 0.00 END) AS 'Something_1',
  SUM(CASE WHEN B.X IN (0, 12) THEN B.Z ELSE 0.00 END) AS 'Something_2',
  SUM(CASE WHEN B.X = 2 THEN B.Y ELSE 0.00 END) AS 'Something_3',
  SUM(CASE WHEN B.X = 2 THEN B.Z ELSE 0.00 END) AS 'Something_4'
FROM FOO AS F
INNER JOIN BAR AS B ON F.ID = B.FK_FOO_ID
WHERE
  (F.G > '2018-03-01 23:59:59' OR F.G IS NULL) AND
  B.W <= '2018-03-01 23:59:59' AND
  B.X IN (0, 2, 12)
GROUP BY
  F.A,
  F.B,
  F.C,
  F.D,
  F.E;

но при удалении F.G > '2018-03-01 23:59:59' из WHERE предложения будет применен созданный индекс. Есть идеи, почему? Как «заставить» MYSQL использовать индекс в столбце, G который имеет тип TIMESTAMP ? Есть идеи по улучшению индекса для создания с помощью этого типа SELECT запроса?

1. Вы можете заставить его использовать индекс со следующим синтаксисом ВЫБЕРИТЕ <столбец> Из <таблицы> ИСПОЛЬЗУЙТЕ ИНДЕКС (<index>) ГДЕ …

2. да, согласен, но действительно хотел бы избежать этого, это было бы последним, если я не смогу заставить это работать другим способом

3. @ Ivan Milasevic — Принудительно индексировать — не лучшая идея. оптимизатор говорит, что индекс witch должен использоваться для самого быстрого способа. оптимизатор выполняет это для статистических данных строки. и у вас есть больше / другие данные в ваших столбцах, они могут использовать другой способ (с / без) индексирования, НО НЕ В ТОМ случае, ЕСЛИ ВЫ ПРИНУДИТЕЛЬНО ВВОДИТЕ индекс. Поэтому измените свой запрос

4. Станет ли лучше, если вы удалите OR F.G IS NULL ?

5. Также посмотрите, помогает ли добавление INDEX(X,W) в B .

Ответ №1:

Оптимизатор решает, использовать индекс или нет, в соответствии со статистикой, что означает частоту различных значений в столбце.

В вашем случае, когда вы удаляете условие F.G > ‘2018-03-01 23:59:59’, остается только проверить, является ли значение null более частым.

Более того, при проверке на null не проверяется само значение в столбце, но для каждой строки есть бит, который сообщает, является ли значение в столбце null или нет. Так что это делает его еще быстрее.

когда вы добавляете условие обратно, для него, вероятно, быстрее сначала найти соответствующий идентификатор, а затем проверить, совпадает ли G.

Он также будет использовать первичный ключ, потому что ему необходимо использовать его для получения значений для

 F.A, F.B, F.C, F.D, F.E,

Если вы действительно хотите, чтобы он использовал ваш индекс, вам, вероятно, потребуется сделать его покрывающим, то есть вам нужно добавить эти столбцы в свой индекс

1. уже пробовал с (ID, G), та же история. Я бы полностью согласился с вами, если бы индекс не использовался в случае, когда F.G > '2018-03-01 23:59:59' удаляется

2. Извините, пропустил эту часть. Исправил мой ответ.