Подтвердить что ты не робот

Можно ли использовать MySQL для индекса ORDER на 1 DESC, 2 ASC?

У меня есть материализованная доска объявлений с управляемым движением. Для получения сообщений по порядку используется следующий запрос:

SELECT * FROM Board ORDER by root DESC, path ASC LIMIT 0,100

где root - это id корневого сообщения для потока, а path - материализованный путь.

Однако ни одно из моих попыток сделать этот запрос для использования индексов не имело успеха.

mysql> explain extended select path from Board order by root desc, path asc limit 100;
+-------+---------------+----------+---------+------+-------+----------+----------------------------+
| type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra
+-------+---------------+----------+---------+------+-------+----------+-----------------------------
| index | NULL          | rootpath | 261     | NULL | 21998 |   100.00 | Using index; Using filesort

В настоящий момент он показывает число всех строк в таблице в столбце rows. Мне интересно, есть ли способ уменьшить это число или оптимизировать запрос любым другим способом?

CREATE TABLE `Board` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `path` varchar(255) NOT NULL DEFAULT '0',
  `root` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `root` (`root`),
  KEY `path` (`path`),
  KEY `rootpath` (`root`,`path`)
)

Основная проблема с запросом - разбиение на страницы - мне нужно запустить вторую страницу прямо из сообщения рядом с последним на предыдущей странице. Вот почему я хочу это прямолинейно - без сублектов и прочее.
Текущая настройка не совсем приятна, поскольку она запускает вторую страницу из середины потока, но это, по крайней мере, логично.

4b9b3361

ОТВЕТЫ

Ответ 1

Ваш исходный запрос

SELECT * FROM Board ORDER by root DESC, path ASC LIMIT 0,100;

Создайте таблицу для хранения отрицательного значения root, называемого BoardDisplayOrder, где вы добавляете новый столбец с именем rootinv.

Сначала приведены примеры данных и ваш исходный запрос:

mysql> drop database if exists YourCommonSense;
Query OK, 2 rows affected (0.06 sec)

mysql> create database YourCommonSense;
Query OK, 1 row affected (0.00 sec)

mysql> use YourCommonSense
Database changed
mysql> CREATE TABLE `Board` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `path` varchar(255) NOT NULL DEFAULT '0',
    ->   `root` int(11) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`),
    ->   KEY `root` (`root`),
    ->   KEY `path` (`path`),
    ->   KEY `rootpath` (`root`,`path`)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO Board (path,root) VALUES
    -> ('Rolando Edwards',30),
    -> ('Daniel Edwards',30),
    -> ('Pamela Edwards',30),
    -> ('Dominiuqe Edwards',40),
    -> ('Diamond Edwards',40),
    -> ('Richard Washington',50),
    -> ('George Washington',50),
    -> ('Synora Washington',50);
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM Board;
+----+--------------------+------+
| id | path               | root |
+----+--------------------+------+
|  2 | Daniel Edwards     |   30 |
|  3 | Pamela Edwards     |   30 |
|  1 | Rolando Edwards    |   30 |
|  5 | Diamond Edwards    |   40 |
|  4 | Dominiuqe Edwards  |   40 |
|  7 | George Washington  |   50 |
|  6 | Richard Washington |   50 |
|  8 | Synora Washington  |   50 |
+----+--------------------+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM Board ORDER by root DESC, path ASC LIMIT 0,100;
+----+--------------------+------+
| id | path               | root |
+----+--------------------+------+
|  7 | George Washington  |   50 |
|  6 | Richard Washington |   50 |
|  8 | Synora Washington  |   50 |
|  5 | Diamond Edwards    |   40 |
|  4 | Dominiuqe Edwards  |   40 |
|  2 | Daniel Edwards     |   30 |
|  3 | Pamela Edwards     |   30 |
|  1 | Rolando Edwards    |   30 |
+----+--------------------+------+
8 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM Board ORDER by root DESC, path ASC LIMIT 0,100;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | Board | index | NULL          | rootpath | 261     | NULL |    8 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql>

Затем создайте таблицу BoardDisplayOrder с помощью rootinv и индекса с участием rootinv:

mysql> CREATE TABLE BoardDisplayOrder LIKE Board;
Query OK, 0 rows affected (0.09 sec)

mysql> ALTER TABLE BoardDisplayOrder DROP INDEX root;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE BoardDisplayOrder DROP INDEX path;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE BoardDisplayOrder DROP INDEX rootpath;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE BoardDisplayOrder ADD COLUMN rootinv int(11) NOT NULL;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE BoardDisplayOrder ADD INDEX rootpathid (rootinv,path,id,root);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE BoardDisplayOrder \G
*************************** 1. row ***************************
       Table: BoardDisplayOrder
Create Table: CREATE TABLE `boarddisplayorder` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `path` varchar(255) NOT NULL DEFAULT '0',
  `root` int(11) NOT NULL DEFAULT '0',
  `rootinv` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rootpathid` (`rootinv`,`path`,`id`,`root`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

Затем запустим BoardDisplayOrder:

mysql> INSERT INTO BoardDisplayOrder (id,path,root,rootinv)
    -> SELECT id,path,root,-root FROM Board;
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM BoardDisplayOrder;
+----+--------------------+------+---------+
| id | path               | root | rootinv |
+----+--------------------+------+---------+
|  7 | George Washington  |   50 |     -50 |
|  6 | Richard Washington |   50 |     -50 |
|  8 | Synora Washington  |   50 |     -50 |
|  5 | Diamond Edwards    |   40 |     -40 |
|  4 | Dominiuqe Edwards  |   40 |     -40 |
|  2 | Daniel Edwards     |   30 |     -30 |
|  3 | Pamela Edwards     |   30 |     -30 |
|  1 | Rolando Edwards    |   30 |     -30 |
+----+--------------------+------+---------+
8 rows in set (0.00 sec)

mysql>

Теперь запустите свой запрос с BoardDisplayOrder, но без DESC на rootinv:

mysql> SELECT id,path,root FROM BoardDisplayOrder ORDER by rootinv, path LIMIT 0,100;
+----+--------------------+------+
| id | path               | root |
+----+--------------------+------+
|  7 | George Washington  |   50 |
|  6 | Richard Washington |   50 |
|  8 | Synora Washington  |   50 |
|  5 | Diamond Edwards    |   40 |
|  4 | Dominiuqe Edwards  |   40 |
|  2 | Daniel Edwards     |   30 |
|  3 | Pamela Edwards     |   30 |
|  1 | Rolando Edwards    |   30 |
+----+--------------------+------+
8 rows in set (0.00 sec)

mysql> EXPLAIN SELECT id,path,root FROM BoardDisplayOrder ORDER by rootinv, path LIMIT 0,100;
+----+-------------+-------------------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table             | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+-------------------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | BoardDisplayOrder | index | NULL          | rootpathid | 269     | NULL |    8 | Using index |
+----+-------------+-------------------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

Дайте ему попробовать!!!

CAVEAT

Это было легко сделать, потому что root был INT.

Если root был VARCHAR, rootinv должен был бы быть красным символом. Другими словами,

  • A → Z
  • B → Y
  • ...
  • M → N
  • N → M
  • ...
  • Y → B
  • Z → A

Это будет работать в любом поле, в котором вам нужно выполнить DESC. Проблема связана с тем, что MySQL не упорядочивает ключи внутри внутри индекса как ASC или DESC. Все в индексе возрастает. Вот почему, когда вы видите статистику обработчика в SHOW GLOBAL STATUS LIKE 'handler%';, вы увидите следующее:

и т.д.

Согласно текущей документации MySQL

Спецификация index_col_name может заканчиваться ASC или DESC. Эти ключевые слова разрешены для будущих расширений для указания возрастания или нисходящего индекса. В настоящее время они анализируются, но игнорируются; значения индекса всегда сохраняются в порядке возрастания.

Дайте ему попробовать!!!

ОБНОВЛЕНИЕ 2012-05-04 06:54 EDT

@frail комментарий о моем ответе

ALTER TABLE BoardDisplayOrder ADD INDEX rootpathid (rootinv, path, id, root) кажется мне совершенно ненужным, ALTER TABLE BoardDisplayOrder ADD INDEX rootpathid (rootinv, path) должно быть достаточно

Причиной моего решения было ALTER TABLE BoardDisplayOrder ADD INDEX rootpathid (rootinv,path,id,root) - предоставить индекс покрытия. Индекс покрытия в этом случае будет:

  • всегда есть необходимые столбцы для извлечения
  • улучшит качество плана объяснения, поскольку
    • запрос никогда не будет читать из таблицы для извлечения данных
    • запрос будет считываться только из индекса для извлечения данных
    • приведет к сканированию диапазона индексов

Подумайте о первоначальном запросе,

SELECT * FROM Board ORDER by root DESC, path ASC LIMIT 0,100;

Для этого требуется получить три столбца пути, id и root. Таким образом, они должны быть в индексе. Конечно, увеличение размера индекса было бы компромиссом. Если таблица Board была очень большой, некоторые не беспокоились бы о пространстве, если бы поиск мог быть сделан быстрее. Если индекс корневых путей был справедливым (rootinv, path), то каждое сканирование диапазона индекса сопровождалось бы поиском ref в таблице для остальных столбцов. Именно по этой причине я выбрал ALTER TABLE BoardDisplayOrder ADD INDEX rootpathid (rootinv,path,id,root);

Ответ 2

Проблема, с которой вы сталкиваетесь, хорошо объясняется в этой статье. И важная часть:

Наиболее типичным случаем является то, когда вы хотите заказать по два столбца в разных направления:... ЗАКАЗАТЬ по цене ASC, дата DESC LIMIT 10 Если у вас есть индексируется (цена, дата) в порядке возрастания, вы не сможете оптимизируйте этот запрос - внешний вид ( "filesort" ) будет необходим. Если вы сможете построить индекс по цене ASC, дайте DESC то же самое запрос может извлекать данные в отсортированном порядке в порядке.

Также в статье упоминается допустимое обходное решение для проблемы: с заменой второго предложения "порядок":

Это, однако, то, что вы можете решить, имея что-то вроде столбец "reverse_date" и использовать его для сортировки. С MySQL 5.0 вы даже может использовать триггеры, чтобы обновить его как настоящие обновления даты, чтобы он стал меньше некрасиво. На самом деле это, например, почему вы увидите "reverse_timestamp" в структуре таблицы Википедии.

Также из официальной документации MySQL:

В некоторых случаях MySQL не может использовать индексы для решения ORDER BY, хотя он по-прежнему использует индексы для поиска строк, соответствующих WHERE пункт. Эти случаи включают следующее:
 
.....
 
Вы смешиваете ASC и DESC:
 
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

В качестве предложения вам лучше иметь столбец reversed_root, который Integer.MAX_VALUE - root И имеет индекс на (reverseedroro, путь). Тогда вы можете задать запрос как:

SELECT * FROM Board ORDER by reversed_root ASC,path ASC LIMIT 0,100

Ответ 3

В этой ситуации, когда сами данные не поддаются поиску так, как вам нужно, может быть целесообразно создать дополнительный столбец с необходимой информацией - это позволит вам получить требуемый заказ,

Это было бы особенно уместно в этом случае, так как кажется, что сами данные не обновляются после его сохранения. После того, как ваши сообщения опубликованы, они не обновляются (или, кажется, из моего первоначального чтения).

Шаги, которые я рекомендую, если вы возьмете этот путь, будут:

  • Добавить новый столбец root_path в таблицу.
  • Выполните этот оператор обновления update Board set root_path = root + path. (Возможно, вам придется адаптировать это на основе типов данных существующих столбцов.)
  • Всякий раз, когда вы добавляете новую строку в таблицу, также добавляйте этот новый столбец. (Это можно было бы обработать с помощью триггера, хотя я бы опасался срабатывания триггеров, поскольку их можно игнорировать, когда люди меняют другие части кода.)

Затем вы должны иметь возможность установить индекс в этом новом столбце и написать свои выборки по этому столбцу - нажав ваши индексы, как вам угодно.

Я считаю, что это сработает, хотя один из ключей нужно сортировать в противоположном порядке.

CREATE TABLE foo
(
  id serial NOT NULL,
  int_field integer DEFAULT 0,
  varchar_field character varying(255),
  composite_field character varying(255),
  CONSTRAINT foo_pkey PRIMARY KEY (id )
);

CREATE INDEX composite_field_idx ON foo (composite_field);

INSERT INTO foo (int_field, varchar_field, composite_field) VALUES 
(1,'t','t1'),
(2,'z','z2'),
(2,'w','w2'),
(4,'u','u4'),
(5,'u','u5'),
(5,'x','x5'),
(7,'v','v7');

explain select * from foo order by composite_field desc;

Запустите вышеуказанный код, и инструкция объяснения должна показать ссылку на составной_компьют_файл_ид.

Результат запроса:

select * from foo order by composite_field desc;

 id | int_field | varchar_field | composite_field 
----+-----------+---------------+-----------------
  2 |         2 | z             | z2
  6 |         5 | x             | x5
  3 |         2 | w             | w2
  7 |         7 | v             | v7
  5 |         5 | u             | u5
  4 |         4 | u             | u4
  1 |         1 | t             | t1