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, если вам это нужно.