Форум пользователей MySQL

Составные индексы следует использовать, если запрос включет условие на несколько полей. Например, если запрос WHERE city='Moscow' and age='33', то можно сделать составной ключ KEY(city, age). При этом, можно всегда делать where запрос по левой части составного ключа (в данном случае WHERE city='Moscow'). Если Вам нужно делать запрос отдельно по age, то данный составной ключ в этом не поможет, нужен отдельный ключ на поле age или составной ключ, в котором поле age - первое.

Первичный ключ - уникален, поэтому составной ключ может иметь смысл только есть запросы с указанием диапазона, например WHERE id > 1000 and age < 70. Такие случаи бывают нечасто. Если в приведенном в начале примере создать ключ KEY(id, city, age), то работает он не будет для запроса WHERE city='Moscow' and age='33'.

Индекс на поле VARCHAR вполне имеет смысл. Причем можно построить индекс по подстроке длиной n символов, если поле VARCHAR длинной. Наприме KEY(description(20));

А есть ли заметная разница на быстродействии (речь идет о таблице с 20 000 - 25 000 записей) - делать составной индекс или два индекса по этим полям? Если делать запросы типа WHERE city='Moscow' and age='33'

Разница должна быть значительной. Если есть составной индекс, то он один используется для выборки - это быстро. Если два отдельных индекса, то используется только индекс city (или только индекс age - оптимизатор решит на основе статистики таблицы), а потом произойдет перебор всех отвечающих city='Moscow' записей со сравнением возраста. Второй индекс использован не будет.

Можно в продолжение темы еще один вопрос?
Используются ли индексы непосредственно при чтении данных? Т.е. не только в условиях Where, но и в списке отбираемых полей. Чтобы вопрос был понятней, поясню на примере.

Например, есть таблица:
create table search_index(
page_id int not null,
word_id int not null,
weight smalint not null
);
Запрос выглядит, например, так:
select page_id, weight from search_index where word_id=99999

Так вот, будет ли ускорение при создании индекса
(word_id, page_id, weight)
или ничего не изменится по сравнению с индексом
(word_id)

PS: речь идет о больших таблицах с сотнями миллионов записей.

Спасибо большое.

Можно, например, сначала создать одинарные ключи. Затем командой SHOW INDEX FROM имя_таблицы посмотреть CARDINALITY. Оптимизатор скорее всего выберет ключ с наибольшей CARDINALITY (точный алгоритм неизвестен). Второй ключ тоже следует выбрать с наибольшей CARDINALITY. Ключи, которые используются для операций сравнения >, < должны быть в конце, так как после такой операции оптимизатор перестанет использовать дальнейшие части ключа.

Дополню про устройство составных индексов в MySQL. KEY(A,B) строится как индекс на объединении бинарных представлений A и B. То есть если A и B типа CHAR, то KEY(A,B) то же, что KEY(C), где C = CONCAT(A,B). Причем ключ всегда фиксированной длины, даже если поля переменной длины.

Для целых чисел A INT, B INT, объединение бинарных представлений дает 8-байтовое число C, у которого 4 старших байта - A, 4 младших байта - B. То есть
C = A * 2^32 + B
или, что то же самое
C = (A << 32) + B; (сдвиг влево на 32 разряда)

Тогда запрос WHERE A=ax AND A=bx это то же самое, что
WHERE C = ax*2^32 + bx - поиск по новому ключу

Если запрос просто WHERE a=ax, то это фактически RANGE-запрос по составному ключу
WHERE c > (ax << 32) AND c < ( (ax+1) >> 32)

CARDINALITY (то есть количество разных значений) для ключей KEY(A,B) и KEY(B,A) будет одинаковым, так как это число различных пар A,B. Деревья будут разными, но при запросах WHERE A=AX AND B=BX не следует ожидать отличия в производительности. Кроме того, такой поиск по ключу - очень быстрая операция, реальное время будет занимать выборка найденных по индексу строк из таблицы.

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

Честно говоря, не понял, в чем заключается «неразумность». Отношение «многие ко многим»
тяжело построить без такой таблички связей, и уникальность в ней безусловно нужна.

Если MyISAM — то однозначно лишний ключ не нужен. Если InnoDB — ситуация двоякая.

С одной стороны, лишний ключ не нужен. С другой стороны, все не-primary ключи содержат
в себе не ссылку на строку, а ссылку на primary, поэтому, если PK очень длинный, то все
остальные ключи становятся тоже очень длинными и, следовательно, начинают тормозить.
В каждой конкретной ситуации надо смотреть отдельно.

В случае двух полей примера, я бы суррогатный не делал.

Используем простую технику:
а) часть запросов требует простой индекс, его создаем. Те запросы, которые уже стали достаточно быстрыми (высокая cardinality индекса, мало строк выбирается) - вычеркиваем
б) для запросов, которые все еще недостаточно быстрые, создаем необходимые составные индексы
в) лишние (дублирующие) индексы удаляем, например, если есть KEY(a), KEY(b), KEY(a,b), удаляем KEY(a), так как он и так является начальной частью KEY(a,b)

"... Никакого секрета здесь нет. Видишь там на горе ..."

Cardinality - число различных значений, который принимает индекс. Чем больше, чем лучше. Например, если есть колонка пол - со значениями 0,1,2, то чаще всего индекс с ее использованием бессмысленный.

Вычеркиваем запрос из списка тех, которые требуют оптимизацию. Мы оптимизируем не все запросы, а только те, которые могут быть узким местом. Если запрос нормальный (в EXPLAIN небольшое количество выбираемых строк), то составные индексы для него (обычно) не нужны.

WHERE name1=100 AND name2=200

Это идеализированный случай. Если так, то конечно ключ (name1, name2). Обычно бывает WHERE age=33 and deleted=0 AND sex=2 and smoke=0 and drink=1 или запрос содержит где-то OR или операторы больше, меньше. В таких случаях cardinality у всех второстепенных колонок низкая (и они не всегда участвуют в запросе), поэтому достаточно поставить индекс на наиболее избирательную колонку (в данном случае age), а потом уже смотреть.

А что в вашем понимании недостаточно быстры ?

Если нет ответа на этот вопрос, то непонятно что оптимизировать. Запросы, которые и так хорошо работают, мы обычно не оптимизируем (клиенту редко нужно переписывать приложение только ради того, чтобы оно стало красивее). Возьмите лог апача - проиграйте его GET-ом в несколько потоков и посмотрите SHOW FULL PROCESSLIST в этот момент. Те запросы, которые там будут видны систематически - недостаточно быстрые. Можно проще - просто выполните SHOW FULL PROCESSLIST 100 раз подряд, уже составите представление о запросах (и, конечно, лог медленных запросов). Ответ будет зависеть от типа приложения, частоты и общей сложности запросов, но суть в поиске худших среди имеющихся.

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

P.S. paulus все делает по-другому, если ответит в этой теме, узнаете и его подход.

rgbeast написал:

Кстати, все шаги описаны в моем докладе на РИТ++, Оптимизация производительности MySQL: ботанический определитель.

Если суперсложные таблицы и запросы, то обычно запросы сложны настолько, что очень сильно ограничен набор индексов, которые могут быть использованы - их и создать.

Спасибо. Буду изучать :-)

Заметил некаю связь.
select * from video_stats_2 where member_id>34 and views=200;

Существует большая разница между составным индексом (member_id,views) и (views,member_id)

В случае (views, member_id) составной индекс используется на всю длину: сначала по левой части проверяется условие на views, потом - range на member_id.

Когда наоборот (member_id, view), MySQL может использовать только левую часть индекса, а дальше останавливается, т.к. встречает условие '>', и для условия views = 200 индекс уже не использует.
Другими словами, с точки зрения условия member_id > 34 индекс (member_id, views) ничем не лучше простого индекса на member_id. Именно поэтому Вы увидели разницу.

Это не касается случаев вида views = 200 AND member_id = 34 (строгое равенство) - в этом случае составной ключ будет использоваться полностью, причем в обоих вариантах.