Композитный указатель

Я новичок в MySQL, и мне нужно добавить индексы в существующую таблицу (которая содержит примерно 200 тыс. Строк).

Table mytable: (id:integer, created_time:timestamp, deleted_time:timestamp)

У меня есть 2 запроса, которые должны извлечь выгоду из индекса:

select s.id from mytable s
where s.completed_time is not null
and s.completed_time < ?
and ( s.deleted_time is null
    or s.deleted_time >= ? );

а также:

select s.id from mytable s 
where
   s.completed_time is not null 
and (
   ( s.deleted_time is not null 
   and s.deleted_time >= ? 
   and s.deleted_time < ? )
 or ( s.completed_time >= ? 
   and s.completed_time < ? ) ) ;

Я подумываю о введении многостолбцового индекса (для завершения_ времени и удаленного времени). Однако я не уверен, соответствует ли условие "s.completed_time не равно нулю" критериям, чтобы эти запросы использовали составной индекс.

  • Есть ли у вас мысли о том, что лучше (составной индекс или 2 индекса)? Я пытаюсь использовать "объяснить", чтобы выяснить, что лучше, но я не уверен, как интерпретировать результаты.

  • И в целом: с таблицей, имеющей составной индекс (column1, column2), я понимаю, что фильтрация только для column2 не будет использовать индекс. Но что, если я введу фиктивное условие, например (column1 > MIN_VALUE) или (column1 не является нулевым), когда это правильно сделать?

Спасибо!

Источник

2 ответа

Решение

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

Как вы уже знаете, при поиске запроса по двум столбцам иногда могут использоваться два отдельных индекса путем (грубо) объединения этих двух индексов в один. Но это неоптимально и имеет стоимость с точки зрения производительности.

И наоборот, составной индекс можно использовать только в том случае, если в условие поиска включены самые левые столбцы или, как указано в руководстве:

MySQL может использовать индексы с несколькими столбцами для запросов, которые проверяют все столбцы в индексе, или запросов, которые проверяют только первый столбец, первые два столбца, первые три столбца и т. Д.

Что касается предложенного вами хака (введение фиктивных условий для возможности использования индекса), это может сработать, но я бы посоветовал создать второй индекс для column2 только (кроме двухколоночного индекса на (column1, column2)). Это происходит за (небольшую) стоимость, но гораздо более элегантно и многократно используется.

Что касается предложения избавиться от NULL ценности, я категорически не согласен. Семантически некорректно использовать 0, 0 означает "ноль", NULL означает "нет значения". Все ваши тесты должны учитывать это особое значение, тогда как IS NULL стандартно и везде понятно. Это также просто нецелесообразно в некоторых ситуациях (попробуйте вставить 0 с SQL_MODE='TRADITIONAL').

С другой стороны, выигрыш в производительности сомнителен (я полагаю, что это в основном основано на ложном предположении, что NULL значения не индексируются). Легко проверить, что запрос, как s.completed_time IS NOT NULL попадет в индекс, если такой индекс существует.

Источник

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

Но второй запрос индекса может не работать. - Поскольку у вас есть ИЛИ между вашими индексированными столбцами.

Обычно он идет по левым столбцам в индексе и в таком порядке.

Я предлагаю создать отдельные индексы. Несмотря на то, что он содержит служебные данные (может быть) http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html

Источник
Другие вопросы по тегам