Как правильно выбрать индексы для таблицы базы данных по приведённому примеру?
Есть некоторая таблица, в ней Id с авто инкрементом, несколько различных текстовых полей, а главное есть 2 поля с int данными в которых хранится некоторый числовой диапазон, который в других записях этой таблицы не пересекается(это базой контролировать не требуется, данные так загружены что диапазоны не пересекаются). То есть первое поле хранит число начала диапазона а второе его конечное. Давайте для условности пусть будут поля id,min,max.
Меня интересует запрос вида SELECT id FROM table WHERE (min < N) AND (max > N); где N некоторое подставляемое число.
Вопрос не только как правильно выбрать индекс для min и max поля, а так же почему? Исходя из того что я прочитал в книгах, видимо правильнее сделать один индекс по обоим полям min и max, вот только я не понял почему. На мой взгляд если сделать отдельные по этим полям то вроде бы тоже понятно что должно нормально работать. Я просто представляю что ищется по одному индексу по другому а затем берётся их объединение? А как в случаи с объединёным индексом работает выборка, можно более детально как то расписать саму теорию или даже физический смысл как работает объединённый индекс? Если можно как то распишите на примере каких нибудь 10 записей, то есть как для них будет выглядеть индекс и чтобы было наглядно видно что он действительно быстрее чем 2 индекса по двум полям.
Ответы (1 шт):
Меня интересует запрос вида
SELECT id FROM table WHERE (`min` < N) AND (`max` > N);где N некоторое подставляемое число.Вопрос не только как правильно выбрать индекс для min и max поля, а так же почему?
Правильно с точки зрения производительности - изменить схему хранения. Использовать GEOMETRY и соответственно Spatial Index.
Если это невозможно, то следует использовать покрывающий индекс - по (`min`, `max`, id) либо (`max`, `min`, id).
Исходя из того что я прочитал в книгах, видимо правильнее сделать один индекс по обоим полям min и max
Плохо читал. У тебя неравенство - соответственно индекс будет использован только для одного условия, того, для которого поле в выражении индекса первое.
На мой взгляд если сделать отдельные по этим полям то вроде бы тоже понятно что должно нормально работать.
Неверно. Одна копия таблицы в источнике запроса может использовать только один индекс.
С точки зрения максимальной эффективности, с учётом гарантии, что диапазоны не пересекаются (то есть запрос возвращает либо одну запись, либо пустой набор данных), и при нежелании использовать spatial datatypes, запрос следует переписать так:
SELECT id
FROM ( SELECT id
FROM table
WHERE `min` < N
ORDER BY `min` LIMIT 1 ) t1
JOIN ( SELECT id
FROM table t2
WHERE `max` > N
ORDER BY `max` DESC LIMIT 1 ) t2 USING (id)
и создать два индекса - (`min`, id) и (`max`, id). Но это мероприятие имеет смысл только в случае, если таблица реально ну очень большая (размером в гигабайты и записей под миллиард).