Комментарии 61
Как расширение индекса удивительным образом снижает производительность
Верните пожалуйста в заголовок упоминание InnoDB, как в оригинале у Зайцева. Увидев такое падение производительности я не поверил своим глазам поначалу, пока в тексте не увидел упоминание innodb. К myisam, например, этот топик никакого отношения не имеет, ну кроме, пожалуй, совета осторожно играть с индексами, это всегда надо делать с умом.
Я с 3-го раза только прочел «расширение индекса», т.к. читалось «расширение яндекса» :) Подумалось, что яндекс пишет к mysql плагины :) П.С. Спасибо за статью.
Смысл статьи в том, чтобы сделать акцент для тех кто не знает — что id (PK) уже есть как значение индекса

>> То есть когда у вас есть индекс (a) и первичный ключ id, то реальный ключ у вас (a, id).
«оптимизатор запросов рассматривает возможность использования обоих индексов и в конце концов отвергает оба»
а как понимать причину отказа от двух индексов?
я не знаю, почему Пётр так написал, но на плане запроса вполне видно, что оптимизатор не отказался от индексов совсем. KEY == PRIMARY. т.е. оптимизатор решил выбрать отсортированную коллекцию и решил пройтись по ней, ища нужное a = 100. почему он так выбрал? потому что вес у данного варианта больше, чем у другого.

особо я не понимаю Петра в этой вот части: «This looks like an optimizer glitch in this case because it estimates it will scan 2247 rows in the selected plan, while using (a) index you can get result scanning only 1 row guaranteed.»
индекс по «a» НЕУНИКАЛЬНЫЙ. откуда там взяться 1 row guaranteed — непонятно. чтобы выполнить эту операцию — нужно выбрать все записи с a=100, отсортировать их (а их может быть и 10М), и выбрать первую, с минимальным id.
Одному мне кажется очевидным, что если я создаю индекс для пары значений, то для одного значения этот индекс и не будет использоваться?
это неочевидно. более того — ваше утверждение в корне неверно.
если создан индекс a+b, то он **БУДЕТ** использоваться для a
С какого перепуга он будет использоваться? Индекс строится для пары a и b в случае если ищем только одну из частей этот индекс не будет использоваться, потому что он строится именно для пары значений. В топике explain четко указывает это.
я советую вам почитать матчасть о индексах. хотя если вы будете настаивать и продолжать выставлять себя невеждой и человеком, не способным в документации (или гугле) почитать, как работают составные индексы — я дам вам прямую ссылку на описание на dev.mysql.com
«Почему этот запрос стал медленее? Причина в том, что его план выполнения выигрывал от одной особенности InnoDB — все индексные записи всегда отсортированы по первичному ключу. То есть когда у вас есть индекс (a) и первичный ключ id, то реальный ключ у вас (a, id). Когда же мы расширяем ключ до (a, b), то фактически получаем (a, b, id). Наш запрос использовал обе части исходного ключа «a» и «id» (для сортировки). Теперь же он не сможет полностью использовать новый индекс.»

думаю, проблема как раз в «ORDER BY id», а не в матчасти :)

как это оправдывает незнание человеком работы leftmost части индексов?
mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxitest | ref | a | a | 4 | const | 126074 | USING WHERE
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
1 row IN SET (0.00 sec)

Где тут используется индекс (a,b)? Если у вас есть ссылка я с удовольствием почитаю, как он их использует.
1. как это связано с исходным твоим комментом?

2. по обсуждаемому вопросу «Индекс строится для пары a и b в случае если ищем только одну из частей этот индекс не будет использоваться, потому что он строится именно для пары значений.»

mysql> EXPLAIN SELECT indexx from doma where indexx = '453252'  AND gninmb = '0266';
+----+-------------+-------+------+---------------+---------------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref         | rows | Extra                    |
+----+-------------+-------+------+---------------+---------------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | doma  | ref  | indexx_gninmb | indexx_gninmb | 32      | const,const |   20 | Using where; Using index |
+----+-------------+-------+------+---------------+---------------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT indexx from doma where indexx = '453252';
+----+-------------+-------+------+---------------+---------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | doma  | ref  | indexx_gninmb | indexx_gninmb | 19      | const |   20 | Using where; Using index |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)


особо обращаю внимание на поля key и key_len.
как это связано с исходным твоим комментом

Выше смотрим:
В топике explain четко указывает это.

Не указывает. EXPLAIN (и комментарии Петра) указывают лишь на то, что оптимизатор mysql колбасит и он выбирает неоптимальные решения. По всем правилам индекс использоваться должен, индекс a+id. То, что он не используется — ошибка анализатора, как следствие — баг субд.
Про leftmost не знал и видимо у меня обычно индекс строился так что multiplie-column index не использовался. Буду знать и пользовать :]
Если индекс составной (a, b), то операция сравнения идёт слева направо. ТОгда индекс будет справделив для вариантов
WHERE a =?
WHERE a =? AND b =?
Но индекс не будет использоваться в случае
WHERE b =?
вы, наверное, еще и думаете, что индекс (a,b) это тоже самое что и (b,a)?
А почему бы не создать два отдельных индекса на (a) и (b) вместо (a), (a, b)?
Провел эксперимент:

индекс на полях currency_id и transaction_type

[accounting_user]>select count(id) from ledger where currency_id = 1 and transaction_type = 'invoice';
+-----------+
| count(id) |
+-----------+
| 13197 |
+-----------+
1 row in set (0.04 sec)

[accounting_user]>explain select count(id) from ledger where currency_id = 1 and transaction_type = 'invoice';
+----+-------------+--------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------------------+
| 1 | SIMPLE | ledger | index_merge | currency_id,transaction_type | transaction_type,currency_id | 1,4 | NULL | 3749 | Using intersect(transaction_type,currency_id); Using where; Using index |
+----+-------------+--------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------------------+
1 row in set (0.05 sec)



добавляем индекс на (currency_id, transaction_type)
запрос отрабатывает те же 0.04 сек

[accounting_user]>explain select count(id) from ledger where currency_id = 1 and transaction_type = 'invoice';
+----+-------------+--------+-------------+-------------------------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------------+-------------------------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------------------+
| 1 | SIMPLE | ledger | index_merge | currency_id,transaction_type,transaction_type_2 | transaction_type,currency_id | 1,4 | NULL | 3764 | Using intersect(transaction_type,currency_id); Using where; Using index |
+----+-------------+--------+-------------+-------------------------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)


а что, если над таблицей сделать OPTIMIZE + ANALYZE?

плюс всё таки схему хотелось бы увидеть.
ха, а я и не знал что в 5ой версии есть объединение индексов
мой коммент следовательно ошибка
Наконец то! :) я тут ОЧЕНЬ сильно удивился, что нужно подобные индексы делать. Провел тесты — отдельные индексы на каждое поле работают замечательно!

В примере в статье автор создал составной индекс и дропнул одиночный. А потому удивляется, куда же делась производительность.
для сортировки мердж использован не будет. поэтому в статье автор ничего неправильного не сделал.
Ну видимо не всегда они работают замечательно.

+----+------+-------+
| id | name | level |
+----+------+-------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 2 |
| 4 | 2 | 2 |
| 5 | 1 | 3 |
+----+------+-------+

На такой табличке заставить мускуль использовать index merge не получилось. Составной использовался, одиночный использовался (какой поменьше).

Server version: 5.1.45 Source distribution
Потому что это два раздельных индекса на значения a и значение b. Эти два индекса использовать для поиска пары значений не эффективно.
А если у меня 5 полей в таблице и есть необходимость искать строки по совпадениям различных пары, троек значений — это мне нужно создавать все возможные пересечения индексов? например (a,b), (a,c), (c,d), (a,c,d) — как то это очень странно. В моем примере видно, что на скорость выполнения запроса не влияет и замечательно используется index_merge
в очередной раз — ваша фраза абсолютно некорректна в той её части, что два индекса использовать для поиска пары не то что неэффективно, а НЕВОЗМОЖНО в принципе
да знаю я про мердж. другое дело — что его поведние не совсем управляется и на него расчитывать не нужно. я отвечал человеку, который не понимает принципиально, как работают индексы и не надеялся, что кто-то из _понимающих_ поправит меня :-)
охохохо, меня поправил человек, родившийся в один день со мной. вот это неожиданность )))
Можно сделать вложенные запросы через join, но вообще для поиска пары в прямом поиске использоваться не будут.
Где-то выигрываем, где-то проигрываем, в идеале надо затачивать каждый запрос и не обращать внимание на кол-во индексов, хотя тут уже появятся другие грабли.
Какое решение проблемы? Придётся создать избыточный индекс и держать одновременно (a) и (a, b)

А чем не устраивает (a) и (b)? — В отличие от вашего варианта мы получим еще и оптимизацию при поиске по ключу b и экономию места на ключах (последнее конечно спорно). И версия у вас 5.1.45 и статья в плюсах… Кучу народа в заблуждение введет! Хабр уже не торт (
index merge очень непредсказуем, более того — он работает не во всех вариантах, где работает (a,b)
Возможно, но как минимум об этом нужно написать в статье.
не нужно :-) статья не об этом была. статья была о том, что люди часто забывают, что в secondary index'ах PK добавляется последним полем.
и рассмотрен пример, где это видно очевидно — фильтрация по a и сортировка по PK
и как раз в этой ситуации index merge работать не будет
Приведите пожалуйста пример, когда нужен именно составной индекс.
WHERE `a` = const ORDER BY `b`

как самый простой пример

плюс в запросах, когда index range, составной индекс ВСЕГДА будет быстрее index merge
Давайте возьмем случай посложнее. В таблице примерно 30 млн строк
CREATE TABLE `ix` (
`a` int(11) unsigned NOT NULL,
`b` int(11) unsigned NOT NULL,
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=MyISAM

mysql> explain SELECT COUNT(*) FROM ix WHERE a BETWEEN 50000 AND 60000
AND b BETWEEN 50000 AND 60000 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ix
type: range
possible_keys: a,b
key: a
key_len: 4
ref: NULL
rows: 165334
Extra: Using where
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM ix WHERE a BETWEEN 50000 AND 60000
AND b BETWEEN 50000 AND 60000 \G
*************************** 1. row ***************************
count(*): 2624
1 row in set (0.75 sec)

Как видите mysql уже не может использовать merge. Добавляем составной индекс ab (a,b )
mysql> explain SELECT COUNT(*) FROM ix WHERE a BETWEEN 50000 AND 60000
AND b BETWEEN 50000 AND 60000 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ix
type: range
possible_keys: a,b,ab
key: ab
key_len: 8
ref: NULL
rows: 231045
Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM ix WHERE a BETWEEN 50000 AND 60000
AND b BETWEEN 50000 AND 60000 \G
*************************** 1. row ***************************
count(*): 2624
1 row in set (0.16 sec)
В целом всё зависит от данных, если таблицы небольшие и запросы просты — то и merge прокатит. Если я на этой таблице повторю исходный запрос (т.е. a=const and b=const) то быстрее все равно будет запрос с составным индексом, но разница будет незначительная. Оно и понятно — объединение индексов сама по себе операция и время какое то занимает.

До кучи к вышесказанному примеру с сортировкой еще не забывайтся, что запрос вида
SELECT a,b FROM ix WHERE ...
в случае составного индекса результат будет отдавать непосредственно из индекса, даже не обращаясь непосредственно к данным. В случае с merge этого не будет, впрочем тут я не уверен, возможно в более новых версиях mysql этому научили.
Здесь написано, в каких случаях не будут использоваться индексы при сортировках.
Вы — нет. Комментатор выше вас писал. А Ваш пример с between очень хороший.
эм, очень занятно. а как он может использовать b из индкса a+b? у нас ведь index range по a.
Ошибся, ответ не вам — хотел в основную ветку написать.
У меня и в MyISAM бывал тупняк с неправильным выбором индекса в запросе. Причем на локальной машинке 5.1 стоит и всё хорошо, а на хостинге с 5.0 внезапно начинается какой-то ужас.
Просто заюзал FORCE INDEX.
Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.