Как MySQL оптимизирует DISTINCT
DISTINCT преобразовывается к GROUP BY для всех столбцов, для DISTINCT в сочетании с ORDER BY, помимо этого, во многих случаях также требуется временная таблица.
Если LIMIT # указывается совместно с DISTINCT, MySQL остановится, как только найдет # уникальных строк.
Если не все столбцы и не во всех таблицах используются, MySQL прекратит сканирование неиспользуемых таблиц, как только найдет первое совпадение.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
В случае, если, предположим, таблица t1 используется перед t2 (это проверяется при помощи EXPLAIN), MySQL прекратит чтение в t2 (для каждой отдельной строки из t1), после того как найдет первую строку в t2.
Как MySQL оптимизирует LEFT JOIN и RIGHT JOIN
Выражение "A LEFT JOIN B" в MySQL реализовано следующим образом:
Таблица B устанавливается как зависимая от таблицы A и от всех таблиц, от которых зависит A.
Таблица A устанавливается как зависимая ото всех таблиц (кроме B), которые используются в условии LEFT JOIN.
Все условия LEFT JOIN перемещаются в предложение WHERE.
Выполняются все стандартные способы оптимизации соединения, за исключением того, что таблица всегда читается после всех таблиц, от которых она зависит. Если имеется циклическая зависимость, MySQL выдаст ошибку.
Выполняются все стандартные способы оптимизации WHERE.
Если в таблице A имеется строка, соответствующая выражению WHERE, но в таблице B ни одна строка не удовлетворяет условию LEFT JOIN, генерируется дополнительная строка B, в которой все значения столбцов устанавливаются в NULL.
Если LEFT JOIN используется для поиска тех строк, которые отсутствуют в некоторой таблице, и в предложении WHERE выполняется следующая проверка: column_name IS NULL, где column_name - столбец, который объявлен как NOT NULL, MySQL пререстанет искать строки (для отдельной комбинации ключа) после того, как найдет строку, соответствующую условию LEFT JOIN.
RIGHT JOIN реализован аналогично LEFT JOIN.
При указании жесткого порядка чтения таблиц в LEFT JOIN и STRAIGHT JOIN оптимизатор связей (который определяет, в каком порядке таблицы должны быть связаны) будет выполнять работу намного быстрее, так как ему потребуется проверять меньшее количество перестановок таблиц.
Обратите внимание: отсюда следует, что если выполняется запрос типа
SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key)
WHERE b.key=d.key
MySQL будет делать полный просмотр таблицы b, поскольку LEFT JOIN заставит его читать эту таблицу перед d.
В этом случае, чтобы предотвратить полный просмотр таблицы b, нужно изменить запрос таким образом:
SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key)
WHERE b.key=d.key
Как MySQL оптимизирует ORDER BY
В некоторых случаях MySQL может использовать индекс, чтобы выполнить запрос ORDER BY или GROUP BY без выполнения дополнительной сортировки.
Индекс может также использоваться и тогда, когда предложение ORDER BY не соответствует индексу в точности, если все неиспользуемые части индекса и все столбцы, не указанные в ORDER BY - константы в выражении WHERE. Следующие запросы будут использовать индекс, чтобы выполнить ORDER BY / GROUP BY.
SELECT * FROM t1 ORDER BY key_part1,key_part2,...
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2
SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC
Ниже приведены некоторые случаи, когда MySQL не может использовать индексы, чтобы выполнить ORDER BY (обратите внимание, что MySQL тем не менее будет использовать индексы, чтобы найти строки, соответствующие выражению WHERE):
Сортировка ORDER BY делается по нескольким ключам: SELECT * FROM t1 ORDER BY key1,key2
Сортировка ORDER BY делается, при использовании непоследовательных частей ключа: SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2
Смешиваются ASC и DESC. SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC
Для выборки строк и для сортировки ORDER BY используются разные ключи: SELECT * FROM t1 WHERE key2=constant ORDER BY key1
Связываются несколько таблиц, и столбцы, по которым делается сортировка ORDER BY, относятся не только к первой неконстантной
(const) таблице, используемой для выборки строк (это первая таблица в выводе EXPLAIN, в которой не используется константный, const, метод выборки строк).
Имеются различные выражения ORDER BY и GROUP BY.
Используемый индекс таблицы имеет такой тип, который не обеспечивает сортированного хранения строк (как индекс HASH в таблицах HEAP).
Столбцы индекса могут содержать значения NULL, и используется ORDER BY ... DESC. Это объясняется тем, что в SQL значения NULL всегда сортируются в первую очередь, независимо от того, используется DESC или нет.
В тех случаях, когда MySQL должен сортировать результат, он использует следующий алгоритм:
Считываются все строки согласно ключу или путем сканирования таблицы. Строки, которые не соответствует предложению WHERE, пропускаются.
Ключ сортировки сохраняется в буфере сортировки (размера sort_buffer)
Когда буфер заполняется, содержимое буфера сортируется алгоритмом qsort, результаты сохраняются во временном файле. Сохраняется
указатель на отсортированный блок (в том случае, когда все строки умещаются в буфере сортировки, временный файл не создается).
Вышеупомянутое действие повторяется, пока не будут считаны все строки.
Делается мультислияние до MERGEBUFF (7) областей в один блок в другом временном файле. Это действие повторяется, пока все блоки из первого файла не окажутся во втором файле.
Предыдущий пункт повторяется, пока не останется менее MERGEBUFF2 (15) блоков.
При последнем мультислиянии в результирующий файл записывается только указатель на строку (последняя часть ключа сортировки).
Теперь код в файле 'sql/records.cc" будет использоваться для чтения данных в отсортированном порядке, с использованием указателей на строки из результирующего файла. Чтобы оптимизировать этот процесс, мы считываем большой блок указателей на строки, сортируем их, и затем
считываем строки в отсортированном порядке в буфер строк (record_rnd_buffer).
При помощи команды EXPLAIN SELECT ... ORDER BY можно проверить, может ли MySQL использовать индексы для выполнения запроса. Если в столбце extra содержится значение Using filesort, то MySQL не может использовать индексы для выполнения сортировки ORDER BY. См. раздел Синтаксис оператора EXPLAIN (получение информации о SELECT) (получение информации о SELECT).
Чтобы сортировка ORDER BY выполнялась с большей скоростью, нужно сначала посмотреть, можно ли заставить MySQL использовать индексы взамен дополнительной фазы сортировки. Если это невозможно, то можно сделать следующее:
Увеличить значение переменной sort_buffer.
Увеличить значение переменной record_rnd_buffer.
Изменить переменную tmpdir, чтобы она указывала на выделенный диск с большим количеством свободного пространства.
1 2 3 4 5 6 7
8 8 8
| |
*
Обратная связь