Оптимизация производительности MySQL: порядок по дате времени

У меня есть таблица с примерно 100 000 сообщений блога, связанная с таблицей с 50 фидами через отношения 1: n. Когда я запрашиваю обе таблицы с помощью оператора select, упорядоченного по полю datetime таблицы проводок, MySQL всегда использует filesort, что приводит к очень медленным запросам ( > 1 секунда). Здесь схема таблицы postings (упрощенная):

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
| feed_id             | int(11)      | NO   | MUL | NULL    |                |
| crawl_date          | datetime     | NO   |     | NULL    |                |
| is_active           | tinyint(1)   | NO   | MUL | 0       |                |
| link                | varchar(255) | NO   | MUL | NULL    |                |
| author              | varchar(255) | NO   |     | NULL    |                |
| title               | varchar(255) | NO   |     | NULL    |                |
| excerpt             | text         | NO   |     | NULL    |                |
| long_excerpt        | text         | NO   |     | NULL    |                |
| user_offtopic_count | int(11)      | NO   | MUL | 0       |                |
+---------------------+--------------+------+-----+---------+----------------+

И вот таблица feed:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| type        | int(11)      | NO   | MUL | 0       |                |
| title       | varchar(255) | NO   |     | NULL    |                |
| website     | varchar(255) | NO   |     | NULL    |                |
| url         | varchar(255) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

И вот запрос, который занимает > 1 секунду для выполнения. Обратите внимание, что поле post_date имеет индекс, но MySQL не использует его для сортировки таблицы проводок:

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    (`postings`)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

Результат команды explain extended в этом запросе показывает, что MySQL использует filesort:

+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
| id | select_type | table    | type   | possible_keys                         | key       | key_len | ref                      | rows  | Extra                       |
+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
|  1 | SIMPLE      | postings | ref    | feed_id,is_active,user_offtopic_count | is_active | 1       | const                    | 30996 | Using where; Using filesort |
|  1 | SIMPLE      | feeds    | eq_ref | PRIMARY,type                          | PRIMARY   | 4       | feedian.postings.feed_id |     1 | Using where                 |
+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+

Когда я удаляю часть order by, MySQL перестает использовать filesort. Пожалуйста, дайте мне знать, если у вас есть идеи по оптимизации этого запроса, чтобы заставить MySQL сортировать и выбирать данные с помощью индексов. Я уже пробовал несколько вещей, таких как создание объединенного индекса по всем полям где/по полю, как это было предложено несколькими блогами, но это тоже не сработало.

Ответ 1

Создайте составной индекс либо на postings (is_active, post_date) (в этом порядке).

Он будет использоваться как для фильтрации на is_active, так и для упорядочения на post_date.

MySQL должен показать REF метод доступа по этому индексу в EXPLAIN EXTENDED.

Обратите внимание, что у вас есть условие фильтрации RANGE по сравнению с user_offtopic_count, поэтому вы не можете использовать индекс над этим полем как при фильтрации, так и при сортировке по другому полю.

В зависимости от того, насколько выборочным является ваш user_offtopic_count (то есть сколько строк удовлетворяют user_offtopic_count < 10), может оказаться более полезным создать индекс на user_offtopic_count и отсортировать сортировку post_dates.

Чтобы сделать это, создайте составной индекс на postings (is_active, user_offtopic_count) и убедитесь, что используется метод доступа RANGE по этому индексу.

Какой индекс будет быстрее, зависит от вашего распределения данных. Создайте оба индекса, FORCE их и посмотрите, что быстрее:

CREATE INDEX ix_active_offtopic ON postings (is_active, user_offtopic_count);
CREATE INDEX ix_active_date ON postings (is_active, post_date);

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    `postings` FORCE INDEX (ix_active_offtopic)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

/* This should show RANGE access with few rows and keep the FILESORT */

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    `postings` FORCE INDEX (ix_active_date)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

/* This should show REF access with lots of rows and no FILESORT */

Ответ 2

MySQL имеет два алгоритма filesort: более старый файловый порт, который сортирует записи на диске и новую версию, которая работает в памяти.

Если он не может использовать индекс в первой таблице в соединении для сортировки запроса, ему придется делать файловый порт. Если результат перед сортировкой, преобразованной в формат фиксированной ширины, больше, чем буфер сортировки ИЛИ, если он содержит любые текстовые поля, ему придется использовать медленный алгоритм файлового управления на диске (второе условие выполняется, так как ваш запрос имеет текстовое поле).

MySQL предпочитает использовать столбец is_active, якобы потому, что считает, что столбец наиболее избирателен в устранении строк до того, как он продолжит с другими объединениями и где условия. Первое, что я хотел бы предложить, - попытаться создать составные индексы с post_date, feed_id и столбцами в условии where, например. (is_active, user_offtopic_count, post_date, feed_id).

Ответ 3

Также важно помнить, что MySQL не будет использовать индекс, если столбец, который вы заказываете, имеет функцию, примененную к нему.

Вы также должны попробовать aliasing postings.post_date как что-то еще. Это позволит MySQL заказывать по неизменному столбцу, и вы по-прежнему будете выбирать временную метку unix.