Введение
Когда-то тому назад, я писал про невозможность задания query plan в MySQL. Посыпаю главу пеплом, был неправ. В ряде случаев, ограниченные функции планирования запроса, могут позволить значительно ускорить его выполнение.
В этой заметке я расскажу немного о планировщике запроса, и о двух способах задания собственного плана выполнения запроса.
Что такое query plan
Query plan, или план выполнения запроса, это описание порядка нескольких микродействий, составляющих сложный (а иногда и простой) запрос. Давайте рассмотрим самый простой пример. Для этого и всех других примеров, нам потребуется база данных. Для ее создания я использовал следующие команды:
CREATE DATABASE opt; USE opt; CREATE USER 'opt'@'localhost'; GRANT ALL PRIVILEGES ON opt.* TO 'opt'@'localhost';
Далее создадим таблицы test1 и test2 с одинаковой структурой, но в одной из них создадим индекс, а в другой - нет. Допустим, таблицы будут содержать 10000 записей - номер и md5 сумма строки с номером.
CREATE TABLE TEST1 ( id INT, hash VARCHAR(32) ); CREATE TABLE TEST2 ( id INT PRIMARY KEY, hash VARCHAR(32) );
<?php
$mysqli = new mysqli('localhost', 'opt', '', 'opt');
for ($i = 1; $i < 10000; $i++) {
$mysqli->query("INSERT INTO test1(id, hash) VALUES ($i, MD5($i))");
$mysqli->query("INSERT INTO test2(id, hash) VALUES ($i, MD5($i))");
}
?>
Теперь попробуем посмотреть планы запроса для выборки из середины этих идентичных таблиц:
mysql> EXPLAIN SELECT hash FROM test1 WHERE id = 5000; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test1 | ALL | NULL | NULL | NULL | NULL | 9999 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.01 sec) mysql> EXPLAIN SELECT hash FROM test2 WHERE id = 5000; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | test2 | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.02 sec)
В первом случае, движок базы данных проверяет все 9999 записей в таблице, - вдруг поле id каждой записи равно 5000, и нужно вернуть все 9999 хешей. Во втором случае, за счет уникального индекса, база знает, что значение 5000 в базе встречается лишь один раз, и для его поиска использует основной индекс таблицы, что не замедляет сказаться на результате (даже на такой небольшой базе в 10000 - точнее, 9999 записей):
mysql> SELECT hash FROM test2 WHERE id = 5000; +----------------------------------+ | hash | +----------------------------------+ | a35fe7f7fe8217b4369a0af4244d1fca | +----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT hash FROM test1 WHERE id = 5000; +----------------------------------+ | hash | +----------------------------------+ | a35fe7f7fe8217b4369a0af4244d1fca | +----------------------------------+ 1 row in set (0.03 sec)
Запрос из второй, индексированной таблицы, выполняется мгновенно. В то же самое время неоптимизированный запрос выполняется 0.03 секунды. Это немного, - но не стоит забывать, что в реальных условиях, к базе данных подлючается сразу несколько клиентов, а объем базы может значительно превышать эти 9999 записей. Поэтому самый первый шаг к планированию обработки запросов - создание правильных индексов.
Следует по возможности избегать планов "Using where" и "Using filesort" (Using where страшно только без использования ключа), хотя из любых правил бывают исключения. Например, если по индексу не удается отсеять порядка 70%-85% записей, MySQL, как правило, считает предпочтительным полное сканирование (и это в большинстве случаев очень эффективно).
Оптимизация JOIN
В большинстве случаев, планировщик верно определяет план запроса. Но случаются и исключения - когда программист точно знает, что нужно начать JOIN с конкретной таблицы или в конкретном порядке, а MySQL уверен в обратном. Тогда на выручку приходит оператор STRAIGHT_JOIN. Здесь проще привести обратный пример, как можно указать худший план запроса (т.е. когда планировщик запросов MySQL работает правильно). Для этого создадим таблицу test3 с небольшим числом записей.
mysql> CREATE TABLE test3 ( id INT PRIMARY KEY ); Query OK, 0 rows affected (0.39 sec) mysql> INSERT INTO test3 VALUES (13), (113), (1113), (1313); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
Если мы начинаем выборку с таблицы test3, нам потребуется просканировать лишь 4 записи в таблице test1 или test2. В противном случае, приходится сканировать все 9999 записей таблицы test1 или test2, а уже затем сканировать таблицу test3. Конечно, первый способ намного быстрее - и это планировщик понимает.
mysql> EXPLAIN SELECT * FROM test2 JOIN test3 ON test2.id = test3.id; +----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+ | 1 | SIMPLE | test3 | index | PRIMARY | PRIMARY | 4 | NULL | 4 | Using index | | 1 | SIMPLE | test2 | eq_ref | PRIMARY | PRIMARY | 4 | opt.test3.id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT * FROM test2 STRAIGHT_JOIN test3 ON test2.id = test3.id; +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | test2 | ALL | PRIMARY | NULL | NULL | NULL | 9999 | | | 1 | SIMPLE | test3 | index | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where; Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ 2 rows in set (0.01 sec)
Итак, мы добились, чего хотели - может быть, порядок JOINов и неоптимальный, зато тот, который требуется нам.
Оптимизация использования индексов
Как я уже говорил, MySQL может не использовать индексы, даже когда они присутствуют! Для примера возьмем ту же таблицу test2, и извлечем из нее все значения, у которых id > 1 (это 9998 записей), а затем все значения, у которых id > 123456 (это 0 записей):
mysql> EXPLAIN SELECT * FROM test2 WHERE id > 1; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test2 | ALL | PRIMARY | NULL | NULL | NULL | 9999 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ mysql> EXPLAIN SELECT * FROM test2 WHERE id > 123456; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | test2 | range | PRIMARY | PRIMARY | 4 | NULL | 13 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
В первом случае, индекс по полю id не используется. Во втором, MySQL заранее знает, что таких значений не более 13, и потому использует индекс (см. поле key). Но мы можем замедлить запрос, вызвав поиндексное сканирование таблицы при помощи инструкции FORCE INDEX(PRIMARY). Если в таблице имеется несколько индексов, можно указать любой из них. Для использования основного индекса, применяется служебное слово PRIMARY.
mysql> SELECT * FROM test2 WHERE id > 1; 9998 rows in set (0.08 sec) mysql> SELECT * FROM test2 FORCE INDEX(PRIMARY) WHERE id > 1; 9998 rows in set (0.23 sec)
А вот планы выполнения обоих запросов:
mysql> EXPLAIN SELECT * FROM test2 FORCE INDEX(PRIMARY) WHERE id > 1; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | test2 | range | PRIMARY | PRIMARY | 4 | NULL | 9998 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM test2 WHERE id > 1; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test2 | ALL | PRIMARY | NULL | NULL | NULL | 9999 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Также необходимо иметь ввиду, что база данных не будет использовать более одного индекса, и при помощи FORCE INDEX можно заставить ее использовать именно тот индекс, который нужен (если он определен неправильно). Для определения нужного индекса, используется максимально "уникальный" индекс. Узнать, какой из индексов максимально привлекателен, можно при помощи запроса SHOW KEYS FROM test2. Уникальность индекса характеризует столбец Cardinality.