MySQL не использует индексы ( "Использование filesort" ) при использовании ORDER BY
Я сталкиваюсь с некоторыми довольно важными проблемами, связанными с использованием "ORDER BY" -statements в моем SQL-коде.
Все в порядке, пока я не использую ORDER BY-statements в SQL. Однако, как только я вводю ORDER BY: s в коде SQL, все резко замедляется из-за отсутствия правильной индексации. Можно было бы предположить, что исправление этого было бы тривиальным, но, судя по дискуссиям на форуме, и т.д., Это, кажется, довольно распространенная проблема, которую я еще не видел окончательного и краткого ответа на этот вопрос.
Вопрос: Учитывая следующую таблицу...
CREATE TABLE values_table (
id int(11) NOT NULL auto_increment,
...
value1 int(10) unsigned NOT NULL default '0',
value2 int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY value1 (value1),
KEY value2 (value2),
) ENGINE=MyISAM AUTO_INCREMENT=2364641 DEFAULT CHARSET=utf8;
... как создать индексы, которые будут использоваться при запросе таблицы для value1 -range при сортировке по значению value2?
В настоящее время выборка выполняется, когда НЕ используется предложение ORDER BY.
См. следующий вывод EXPLAIN QUERY:
OK, when NOT using ORDER BY:
EXPLAIN select ... from values_table this_ where this_.value1 between 12345678 and 12349999 limit 10;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | this_ | range | value1 | value1 | 4 | NULL | 3303 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
However, when using ORDER BY I get "Using filesort":
EXPLAIN select ... from values_table this_ where this_.value1 between 12345678 and 12349999 order by this_.value2 asc limit 10;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
| 1 | SIMPLE | this_ | range | value1 | value1 | 4 | NULL | 3303 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
Дополнительная информация о содержимом таблицы:
SELECT MIN(value1), MAX(value1) FROM values_table;
+---------------+---------------+
| MIN(value1) | MAX(value2) |
+---------------+---------------+
| 0 | 4294967295 |
+---------------+---------------+
...
SELECT MIN(value2), MAX(value2) FROM values_table;
+---------------+---------------+
| MIN(value2) | MAX(value2) |
+---------------+---------------+
| 1 | 953359 |
+---------------+---------------+
Пожалуйста, дайте мне знать, нужна ли какая-либо дополнительная информация для ответа на вопрос.
Большое спасибо!
Обновление # 1: Добавление нового составного индекса (ALTER TABLE values_table ADD INDEX (значение1, значение2);) не решает проблему. После добавления такого индекса вы все равно получите "Using filesort".
Обновление # 2: Ограничение, о котором я не упоминал в моем вопросе, состоит в том, что я бы скорее изменил структуру таблицы (например, добавив индексы и т.д.), чем изменив используемые SQL-запросы, Запросы SQL автоматически генерируются с использованием Hibernate, поэтому рассмотрите те более или менее фиксированные.
Ответы
Ответ 1
Вы не можете использовать индекс в этом случае, так как вы используете условие фильтрации RANGE.
Если вы используете что-то вроде:
SELECT *
FROM values_table this_
WHERE this_.value1 = @value
ORDER BY
value2
LIMIT 10
то создание составного индекса на (VALUE1, VALUE2) будет использоваться как для фильтрации, так и для упорядочения.
Но вы используете условие диапазона, поэтому вам все равно придется выполнять заказы.
Ваш составной индекс будет выглядеть следующим образом:
value1 value2
----- ------
1 10
1 20
1 30
1 40
1 50
1 60
2 10
2 20
2 30
3 10
3 20
3 30
3 40
и если вы выберете 1 и 2 в value1, вы все равно не получите весь отсортированный набор value2.
Если ваш индекс на value2 не очень избирателен (например, в таблице не так много DISTINCT value2), вы можете попробовать:
CREATE INDEX ix_table_value2_value1 ON mytable (value2, value1)
/* Note the order, it important */
SELECT *
FROM (
SELECT DISTINCT value2
FROM mytable
ORDER BY
value2
) q,
mytable m
WHERE m.value2 >= q.value2
AND m.value2 <= q.value2
AND m.value1 BETWEEN 13123123 AND 123123123
Это называется методом доступа SKIP SCAN. MySQL не поддерживает его напрямую, но его можно эмулировать следующим образом.
В этом случае будет использоваться доступ RANGE, но, вероятно, вы не получите каких-либо преимуществ по производительности, если DISTINCT value2 содержит меньше, чем около 1% строк.
Примечание:
m.value2 >= q.value2
AND m.value2 <= q.value2
вместо
m.value2 = q.value2
Это делает MySQL выполнение RANGE проверки каждого цикла.
Ответ 2
Мне кажется, что у вас есть два полностью независимых ключа: один для value1 и один для value2.
Поэтому, когда вы используете ключ value1 для извлечения, записи не обязательно возвращаются в порядке значения2, поэтому их нужно сортировать. Это все еще лучше, чем полное сканирование таблицы, поскольку вы сортируете записи, удовлетворяющие вашему предложению "where value1".
Я думаю (если это возможно в MySQL), сложный ключ (value1, value2) разрешил бы это.
Try:
CREATE TABLE values_table (
id int(11) NOT NULL auto_increment,
...
value1 int(10) unsigned NOT NULL default '0',
value2 int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY value1 (value1),
KEY value1and2 (value1,value2),
) ENGINE=MyISAM AUTO_INCREMENT=2364641 DEFAULT CHARSET=utf8;
(или эквивалент ALTER TABLE), предполагая, что правильный синтаксис в MySQL для составного ключа.
Во всех базах данных, которые я знаю (и я должен признать, что MySQL не является одним из них), это заставит механизм БД выбрать ключ value1and2 для извлечения строк, и они уже будут отсортированы в value2-in-value1 порядок, поэтому не потребуется сортировка файла.
Вы все равно можете сохранить ключ value2, если вам это нужно.
