Я часто вижу ошибки, связанные с созданием индексов в MySQL. Многие разработчики (и не только новички в MySQL) создают множество индексов для столбцов, которые будут использоваться при выборе, и считают это оптимальной стратегией.
Например, если мне нужно выполнить запрос типа ВОЗРАСТ=18 И ШТАТ='CA' , то многие люди просто создадут два отдельных индекса для столбцов AGE и STATE. Намного лучше ( здесь и далее ок.
переводчик: и обычно единственно верный ) Стратегия заключается в создании комбинированного индекса формы (AGE,STATE).
Давайте посмотрим, почему это так.
Обычно (но не всегда) индексы в MySQL Индексы BTREE — Этот тип индекса позволяет быстро просматривать информацию, содержащуюся в его префиксах, и перебирать диапазоны отсортированных значений.
Например, когда вы спрашиваете ВОЗРАСТ = 18 лет с индексом BTREE по столбцу AGE MySQL найдет первую строку в таблице, соответствующую запросу, и продолжит поиск до тех пор, пока не найдет первую неподходящую строку — тогда он останавливает поиск, т.к.
считает, что дальше ничего подходящего не будет. Диапазоны, например запросы типа МЕЖДУ 18 ДО 20 ЛЕТ , работают аналогично — MySQL останавливается на разных значениях.
Ситуация с запросами типа ВОЗРАСТ В (18,20,30) , потому что MySQL фактически должен пройти через индекс несколько раз.
Итак, мы обсудили, как MySQL осуществляет поиск по индексу, но не определили, что он возвращает после поиска — обычно (если мы не говорим о покрывающих индексах) он получает «указатель строки», который может быть значением первичного ключа.
(при использовании движка InnoDB), физическое смещение в файле (для MyISAM) или что-то в этом роде.
Важно, чтобы внутренний движок MySQL мог использовать этот указатель для поиска полной строки со всеми необходимыми данными, соответствующей заданному значению индекса.
Какие варианты есть у MySQL, если вы создаете два отдельных индекса? Он может либо использовать только одну из них, чтобы выбрать подходящие строки (а затем отфильтровать извлеченные данные по ГДЕ — но без использования индексов ), или он может получить указатели на строки из всех соответствующих индексов и вычислить их пересечение, а затем вернуть данные.
Какой метод окажется более подходящим, зависит от избирательности и корреляции показателей.
Если после обработки WHERE в первом столбце выбирается 5% строк, а дальнейшее применение WHERE во втором столбце отфильтровывает строки до 1% от общего количества, то использование пересечений, конечно, имеет смысл.
Но если второй WHERE фильтрует только до 4,5%, то обычно гораздо выгоднее использовать только первый индекс и отфильтровывать ненужные нам строки после получения данных.
Давайте посмотрим на несколько примеров: CREATE TABLE `idxtest` (
`i1` int(10) UNSIGNED NOT NULL,
`i2` int(10) UNSIGNED NOT NULL,
`val` varchar(40) DEFAULT NULL,
KEY `i1` (`i1`),
KEY `i2` (`i2`),
KEY `combined` (`i1`,`i2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Я сделал столбцы i1 и i2 независимыми друг от друга, при этом каждый столбец выбирал около 1% строк в таблице, содержащей в общей сложности 10 миллионов записей.
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest WHERE i1=50 AND i2=50;
+----+-------------+---------+------+----------------+----------+---------+-------------+------+-------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+----------------+----------+---------+-------------+------+-------+
| 1 | SIMPLE | idxtest | ref | i1,i2,combined | combined | 8 | const,const | 665 |
+----+-------------+---------+------+----------------+----------+---------+-------------+------+-------+
1 row IN SET (0.00 sec)
Как видите, MySQL решил использовать комбинированный индекс, и запрос был выполнен менее чем за 10 мс!
Теперь предположим, что у нас есть индекс только для отдельных столбцов (прикажите оптимизатору игнорировать объединенный индекс): mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX (combined) WHERE i1=50 AND i2=50;
+----+-------------+---------+-------------+---------------+-------+---------+------+------+-------------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+-------------+---------------+-------+---------+------+------+-------------------------------------+
| 1 | SIMPLE | idxtest | index_merge | i1,i2 | i1,i2 | 4,4 | NULL | 1032 | USING intersect(i1,i2); USING WHERE
+----+-------------+---------+-------------+---------------+-------+---------+------+------+-------------------------------------+
1 row IN SET (0.00 sec)
Как вы можете видеть в этом случае, MySQL искал пересечения индексов, и выполнение запроса заняло 70 мс — В 7 раз дольше!
Теперь посмотрим, что произойдет, если мы будем использовать только один индекс и фильтровать полученные данные: mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX (combined,i2) WHERE i1=50 AND i2=50;
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxtest | ref | i1 | i1 | 4 | const | 106222 | USING WHERE
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
1 row IN SET (0.00 sec)
На этот раз MySQL пришлось пройти значительно больше строк, и запрос занял 290 мс .
Итак, мы видим, что использование пересечения индексов намного лучше, чем использование одного индекса, но гораздо лучше использовать комбинированные индексы.
Однако на этом проблемы с пересечениями индексов не заканчиваются.
В настоящее время использование этой процедуры в MySQL существенно ограничено, поэтому MySQL использует ее не всегда: mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX (combined) WHERE i1=50 AND i2 IN (49,50);
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxtest | ref | i1,i2 | i1 | 4 | const | 106222 | USING WHERE
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
1 row IN SET (0.00 sec)
Как только запрос по одному из столбцов станет перечислением, а не сравнением, MySQL больше не сможет использовать пересечение индексов, даже если в этом случае запрос i2 ИН (49,50) это было бы более чем разумно, потому что.
запрос остается весьма избирательным.
Теперь давайте проведем еще один тест. Я очистил таблицу и заново заполнил ее данными, чтобы значения в i1 и i2 были сильно коррелированы.
На самом деле теперь они в целом равны: mysql [localhost] {msandbox} (test)> UPDATE idxtest SET i2=i1;
Query OK, 10900996 rows affected (6 min 47.87 sec)
Rows matched: 11010048 Changed: 10900996 Warnings: 0
Давайте посмотрим, что произойдет в этом случае: mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest WHERE i1=50 AND i2=50;
+----+-------------+---------+-------------+----------------+-------+---------+------+------+-------------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+-------------+----------------+-------+---------+------+------+-------------------------------------+
| 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i2,i1 | 4,4 | NULL | 959 | USING intersect(i2,i1); USING WHERE
+----+-------------+---------+-------------+----------------+-------+---------+------+------+-------------------------------------+
1 row IN SET (0.00 sec)
Оптимизатор решил использовать пересечение индексов, хотя это было, пожалуй, худшее решение! Запрос принял 360 мс .
Также обратите внимание, что при оценке приблизительного количества строк существует большая погрешность.
Это связано с тем, что MySQL считает значения в столбцах i1 и i2 независимыми и поэтому выбирает пересечение индексов.
На самом деле он не может предположить обратного, потому что.
у него нет никакой статистики о соотношении значений в столбцах.
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX(i2) WHERE i1=50 AND i2=50;
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxtest | ref | i1,combined | i1 | 4 | const | 106222 | USING WHERE
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
1 row IN SET (0.00 sec)
И теперь, когда мы запретили MySQL использовать индекс по столбцу i2 (что означает, что он не может найти пересечение индексов), он использует индекс одного столбца, а не комбинированный.
Это произошло потому, что MySQL имеет статистику о приблизительном количестве совпадающих строк, и поскольку оно одинаково для обоих индексов, MySQL выбрал меньший.
Повторное выполнение запроса заняло 290 мс - точно так же, как и в прошлый раз.
Давайте заставим MySQL использовать только комбинированный индекс: mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX(i1,i2) WHERE i1=50 AND i2=50;
+----+-------------+---------+------+---------------+----------+---------+-------------+--------+-------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+----------+---------+-------------+--------+-------+
| 1 | SIMPLE | idxtest | ref | combined | combined | 8 | const,const | 121137 |
+----+-------------+---------+------+---------------+----------+---------+-------------+--------+-------+
1 row IN SET (0.00 sec)
Видно, что MySQL ошибается примерно на 20% при оценке количества итерируемых строк, что, конечно, неверно, поскольку используется тот же префикс, что и при использовании индекса только по столбцу i1. MySQL не знает этого, потому что.
просматривает статистику отдельных индексов и не пытается их согласовать.
Поскольку используемый комбинированный индекс больше, чем индекс по одному столбцу, запрос занял 300 мс .
Итак, мы видим, что MySQL может решить использовать пересечение индексов, даже если это худший сценарий, хотя с технической точки зрения это, конечно, лучший план, учитывая, что у него нет другой статистики.
Есть простые способы заставить MySQL не использовать индексы пересечений, но, к сожалению, я не знаю, как заставить его использовать пересечения, если он считает это неоптимальным.
Надеюсь, что эта функция будет добавлена в будущем.
Наконец, давайте рассмотрим ситуацию, когда процедура пересечения индексов работает значительно лучше, чем комбинированные индексы для нескольких столбцов.
Это тот случай, когда мы используем ИЛИ при выборке между столбцами.
В этом случае комбинированный индекс становится совершенно бесполезным, и у MySQL есть выбор между выполнением ПОЛНОГО СКАНИРОВАНИЯ таблицы и выполнением ОБЪЕДИНЕНИЯ значений вместо поиска пересечения данных, полученных из одной таблицы.
Я снова изменил значения в столбцах i1 и i2, чтобы они содержали независимые данные (типичная ситуация для таблиц).
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest WHERE i1=50 OR i2=50;
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+---------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+---------------------------------+
| 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i1,i2 | 4,4 | NULL | 203803 | USING union(i1,i2); USING WHERE
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+---------------------------------+
1 row IN SET (0.00 sec)
Такие запросы выполняются 660 мс .
Отключив индекс по второму столбцу, мы получим ПОЛНОЕ СКАНИРОВАНИЕ: mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX(i2) WHERE i1=50 OR i2=50;
+----+-------------+---------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | idxtest | ALL | i1,combined | NULL | NULL | NULL | 11010048 | USING WHERE
+----+-------------+---------+------+---------------+------+---------+------+----------+-------------+
1 row IN SET (0.00 sec)
Обратите внимание, что MySQL указал комбинированные ключи i1 как возможный использовать, а ведь у него есть такая возможность Нет .
Выполнение такого запроса занимает 3370 мс ! Также обратите внимание, что выполнение запроса заняло в 5 раз больше времени, несмотря на то, что при ПОЛНОМ СКАНИРОВАНИИ просматривается примерно в 50 раз больше строк.
Это показывает очень большую разницу в производительности между полным обходом таблицы и доступом по ключу, который занимает в 10 раз больше времени (с точки зрения «стоимости» доступа к строке), несмотря на то, что выполняется в памяти.
В случае с UNION оптимизатор более продвинутый и вполне способен работать с диапазонами: mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest WHERE i1=50 OR i2 IN (49,50);
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+--------------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+--------------------------------------+
| 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i1,i2 | 4,4 | NULL | 299364 | USING sort_union(i1,i2); USING WHERE
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+--------------------------------------+
1 row IN SET (0.00 sec)
Подведение итогов
В большинстве случаев использование комбинированных индексов для нескольких столбцов является лучшим решением, если вы выполняете AND между похожими столбцами в WHERE. Использование пересечения индексов в принципе повышает производительность, но все равно значительно хуже, чем использование комбинированных ключей.Если вы используете OR между столбцами, вам понадобится индекс для каждого столбца, чтобы MySQL мог находить их пересечения, а комбинированные индексы нельзя использовать в таких запросах.
Теги: #MySQL #index #indexes #index #multicolumn #multicolumn indexes #MySQL
-
Созвездия И Яркие Звезды
19 Oct, 24 -
Шутить
19 Oct, 24 -
Методы Регрессионного Анализа В Data Science
19 Oct, 24 -
Концепт Коммуникатора Скартел
19 Oct, 24 -
Распознавание Образов И Научные Знания
19 Oct, 24 -
Думать По-Другому!
19 Oct, 24
