SQLinfo.ru - Все о MySQL Webew.ru: теория и практика веб-технологий

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 18.09.2007 11:46:56

Magz
Гуру
Откуда: Москва
Зарегистрирован: 18.09.2007
Сообщений: 112

Использование составных индексов

Здравствуйте. Хотел бы обсудить такую тему, как использование составнях индексов. Как я понимаю, они нужны тогда, когда большинство запросов идет именно по такому сочетанию полей. А если запросы идут с разными сочетаниями, то и индексы нужно строить на каждое поле отдельно. Я прав? smile
Еще вопросик. Стоит ли включать в составной индекс primary key? ИМХО, нет. Просто я разбираю "наследство" программиста и у него практически везде primary key включен в составной индекс.
Ну, и напоследок, индексация полей типа VARCHAR. Если идет большое количество запросов на поиск по справочнику (процент операций изменений данных минимален), то есть ли смысл в построении индекса?

Неактивен

 

#2 18.09.2007 12:12:21

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Использование составных индексов

Составные индексы следует использовать, если запрос включет условие на несколько полей. Например, если запрос 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));

Неактивен

 

#3 18.09.2007 12:36:12

Magz
Гуру
Откуда: Москва
Зарегистрирован: 18.09.2007
Сообщений: 112

Re: Использование составных индексов

Ок. Спасибо!

rgbeast написал:

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

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

Неактивен

 

#4 18.09.2007 12:44:48

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Использование составных индексов

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

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

Неактивен

 

#5 18.09.2007 13:12:47

Magz
Гуру
Откуда: Москва
Зарегистрирован: 18.09.2007
Сообщений: 112

Re: Использование составных индексов

Класс! Спасибо, действительно ценная информация!

Неактивен

 

#6 20.10.2007 22:01:55

Shad
Завсегдатай
Зарегистрирован: 02.10.2007
Сообщений: 25

Re: Использование составных индексов

Можно в продолжение темы еще один вопрос?
Используются ли индексы непосредственно при чтении данных? Т.е. не только в условиях 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: речь идет о больших таблицах с сотнями миллионов записей.

Неактивен

 

#7 20.10.2007 22:09:05

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Использование составных индексов

Да, будет использоваться. Посмотрите, что выдает explain. Если присутствует "Using index", это значит весь запрос выполняется чтением только индекса (который для MyISAM может эффективно кэшироваться в key_buffer)

1. если key(word_id)
mysql> explain select page_id, weight from search_index where word_id=99999\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: search_index
         type: ref
possible_keys: word_id
          key: word_id
      key_len: 4
          ref: const
         rows: 1
        Extra:
1 row in set (0.00 sec)

2. в случае key(word_id,weight, page_id)

mysql> explain select page_id, weight from search_index where word_id=99999\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: search_index
         type: ref
possible_keys: word_id_2
          key: word_id_2
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index
1 row in set (0.01 sec)

Неактивен

 

#8 20.10.2007 22:57:57

Shad
Завсегдатай
Зарегистрирован: 02.10.2007
Сообщений: 25

Re: Использование составных индексов

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

Неактивен

 

#9 06.11.2007 14:06:48

Сугубый
Участник
Зарегистрирован: 06.11.2007
Сообщений: 7

Re: Использование составных индексов

А можно с Вами обсудить мой "больной" вопрос? Как оптимизировать _порядок_ полей в составном ключе?

Есть группа таблиц, у которых:
- похожий первичный ключ (secid varchar(12), tradedate date, boardid char(4) )
- состав данных разный - иногда distinct secid ==5, иногда ==1000, то же и по остальным полям.
- при разном обьеме данных (в среднем - сотни тысяч, до миллиона). 
- Запросы идут secid='xxx' and boardid='yyy' and  tradedate > ddd1 and tradedate < ddd2.
- Создал индексы на всех полях, пока не составные.

Оптимизатор выбирает разные ключи, в зависимости от таблицы - и это понятно: разные данные (боюсь употреблять слово селективность, хотя оно мне знакомо smile разная "плотность". Составной ключ - рулит, но мне не понятно как определять порядок полей в ключе. Пока делаю втупую - создаю все возможные вариации (6 штук) и прогоняю типовые запросы, смотрю какие индексы выбирает оптимизатор и оставляю только их (1-2). Таблиц много, данных тоже - индексы создаются долго и нудно...

А как это сделать более системно? Проанализировать данные и сразу создать нужный индекс (пусть даже и с 80-90% оптимальностью). Какой алгоритм использует оптимизатор?

Неактивен

 

#10 06.11.2007 14:16:26

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Использование составных индексов

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

Неактивен

 

#11 08.11.2007 23:58:09

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Использование составных индексов

Добавлю, что оптимизатор не всегда выбирает лучший ключ - часто он выбирает просто произвольный из доступных. Кроме того, поиск по ключу KEY(A,B) значений WHERE A='x' AND B='y' происходит очень быстро, независимо от cardinality

Неактивен

 

#12 10.11.2007 21:00:15

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Использование составных индексов

Дополню про устройство составных индексов в 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 не следует ожидать отличия в производительности. Кроме того, такой поиск по ключу - очень быстрая операция, реальное время будет занимать выборка найденных по индексу строк из таблицы.

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

Неактивен

 

#13 08.06.2009 18:39:44

Az.
Участник
Зарегистрирован: 08.06.2009
Сообщений: 2

Re: Использование составных индексов

Интересно по поводу primary key.
(исправляю на более простой пример)
Т.е. для таблицы связей

user_id (int)
friend_blog_id (int)

Неразумно делать
  PRIMARY KEY  (`user_id`,`friend_blog_id`)
  KEY (`friend_blog_id`)
как гарант уникальности, не вводя бессмысленный id (autoincrement_id)?
Речь в моём случае идёт об InnoDB, foreign keys.

Благодарю за ответ заранее.

Отредактированно Az. (08.06.2009 18:48:41)

Неактивен

 

#14 08.06.2009 19:41:20

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6617

Re: Использование составных индексов

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

Неактивен

 

#15 08.06.2009 22:10:27

Az.
Участник
Зарегистрирован: 08.06.2009
Сообщений: 2

Re: Использование составных индексов

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

Неактивен

 

#16 10.06.2009 15:29:41

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6617

Re: Использование составных индексов

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

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

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

Неактивен

 

#17 09.07.2011 10:55:45

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

Re: Использование составных индексов

В Интернете моря болтавни про индексы ... Общая теория + тысячи исключений и обсуждение подводных камней ...
Теория по большому счёту ясна, нужны конкретные техники ! :-)
То есть как уже приводился пример выше:
Есть 30 разных запросов работающих с определённой таблицей. Соответственно может быть много разных составных ключей.
Какие вы используете техники для быстрой оптимальной подборки индексов ?

Неактивен

 

#18 09.07.2011 12:14:25

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Использование составных индексов

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

Неактивен

 

#19 09.07.2011 18:24:42

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

Re: Использование составных индексов

Делаю клиенту оптимизацию БД.
Имеются более ста разных запросов, на каждую таблицу нужно ставить по несколько составных ключей.
В моем случае это ещё цветочки, в практике DB админы встречаются с обьёмами побольше.
И тут над каждым запросом нету время сидеть, тут нужна скорость и техники. Вот и хотелось чтоб вы поделились вашими секретами ! :-)

а) часть запросов требует простой индекс, его создаем. Те запросы, которые уже стали достаточно быстрыми (высокая cardinality индекса, мало строк выбирается) - вычеркиваем

То есть запускаем SHOW INDEX FROM, и смотрим на cardinality ? Если значение высокое, это обозначает что индекс не практичен ?

вычеркиваем

Снимаем индекс ?

б) для запросов, которые все еще недостаточно быстрые, создаем необходимые составные индексы

А что в вашем понимании недостаточно быстры ?
С помощью чего осуществляется сравнение ?
Так как я понял на запрос WHERE name1=100 AND name2=200 вы сначала делаете два ключа на name1 и name2, потом проверяете скорость и если скорость маленькая то только тогда ставите составной ключ ?
А почему составной ключ сразу не поставить ?

Неактивен

 

#20 09.07.2011 19:03:37

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Использование составных индексов

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

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 все делает по-другому, если ответит в этой теме, узнаете и его подход.

Неактивен

 

#21 09.07.2011 19:09:46

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Использование составных индексов

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

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

Неактивен

 

#22 09.07.2011 19:51:07

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

Re: Использование составных индексов

rgbeast написал:

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

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

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

Неактивен

 

#23 09.07.2011 23:09:47

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

Re: Использование составных индексов

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

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

indexes:  (views,member_id)
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    video_stats_2    range    NewIndex1    NewIndex1    10    \N    4    Using where


indexes:  (member_id,views)
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    video_stats_2    range    mem    mem    5    \N    67396Using where


select member_id,count(member_id) cnt from video_stats_2 group by member_id

member_id    cnt
0    3
27    4
32    4
34    640368
43    87
47    1
50    100000

select views,count(views) cnt from video_stats_2 group by views

views    cnt
1    1
7    2
8    1
10    2
12    740182
17    1
27    1
33    1
34    1
38    1
40    2
49    1
52    1
53    1
55    1
68    1
74    1
76    1
82    1
83    1
84    1
86    2
87    1
91    1
92    2
93    1
95    1
100    200
200    54


Почему это происходит ?
Также я заметил что не всегда порядок играет роль. Почему ?

Неактивен

 

#24 11.07.2011 19:44:28

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 818

Re: Использование составных индексов

Заметил некаю связь.
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 (строгое равенство) - в этом случае составной ключ будет использоваться полностью, причем в обоих вариантах.

Неактивен

 

#25 11.07.2011 19:57:36

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Использование составных индексов

Простое правило - опаерация > или < (другая range, например like 'a%') завершает использование составного индекса.

Пример из жизни - пусть индекс=телефонный справочник, то есть индекс на (`Фамилия`, `Имя`, `Отчество`).
Запрос - Власов Сергей, а отчество начинается на Я, то есть WHERE f='Власов' and i='Сергей' and o >= 'Я'. Такой запрос легко выполните - долистаете до Власова Сергей и до отчеств на Я, а затем пойдете по индексу.

Второй запрос - фамилия начинается на Я, зовут Владимир Александрович, то есть WHERE i='Владимир' and o='Александрович' and i>='Я';
Индекс вам поможет только найти все фамилии на Я, далее придется их все перебрать. То есть используется лишь первая часть составного индекса. Если был бы другой справочник, отсортированный по имени отчеству, то он бы вам помог здесь.

Кстати, у нас есть онлайн-курс, где все про индексы изложено smile

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson