Почему этот запрос выводит из строя mysql?

Question

[psiklop]

Версия mysql = 8
Не то, чтобы я опытный в mysql, но для меня полтергейст.

Итак занимаюсь, чем умею на практике долблю в одиночку mysql, таблица довольно большая на 10 миллионов строк.
Делаю такой запрос:

SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE
time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR;

Срабатывает норм, в 5-10 секунд укладывается.

Далее добавляю это:

SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE
time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE n <=4;

Тоже нормально.

А теперь просто трындец, пишу так и mysql виснет (не помогает systemctl restart mysql)
И не помогает reboot серверу (только с физической кнопки)
Пробовал на двух разных серверах.

SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE
time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE n=4;

Видимо я что-то делаю не так и не понимаю, но логически мне кажется ничего особенного.

Comments

[Everything_is_bad]

А теперь просто трындец, пишу так и mysql виснет (не помогает systemctl restart mysql)
И не помогает reboot серверу (только с физической кнопки)

не верю

[psiklop]

Everything_is_bad, как доказать ?

[psiklop]

Everything_is_bad, зуб даю я специально таблицу скопировал на домашний сервер и результат точь в точь такой же

[Akina]

Планы всех запросов и DDL таблицы в студию. А также планы запросов, где подзапрос преобразован в CTE. И точно версию MySQL.

[psiklop]

Akina, я разве видео могу записать

[psiklop]

Akina, https://rutube.ru/video/17a27a19a2a599869073715846...

[Rsa97]

psiklop, А на кой нужно видео? Вы не в состоянии EXPLAIN запроса сделать и текст (не скриншот) результата скопировать? Или у вас SHOW CREATE TABLE не работает?

[psiklop]

Rsa97, explain не делал не разу. Команду таблицы могу прислать, могу хоть всю с данными.

table

CREATE TABLE `sms` (
`_id` int unsigned NOT NULL AUTO_INCREMENT,
`time` datetime NOT NULL,
`sid` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`id` varchar(34) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`phone` bigint DEFAULT NULL,
`status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`source` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`main` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`utm_source` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`utm_medium` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`utm_inside` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`alfa` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`short` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`price` decimal(8,5) NOT NULL,
`ip` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT '',
PRIMARY KEY (`_id`,`time`),
KEY `time` (`time`),
KEY `vid` (`id`),
KEY `phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=20866306 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50500 PARTITION BY RANGE COLUMNS(`time`)
(PARTITION d221 VALUES LESS THAN ('2022-03-01') ENGINE = InnoDB,
PARTITION d222 VALUES LESS THAN ('2022-06-01') ENGINE = InnoDB,
PARTITION d223 VALUES LESS THAN ('2022-09-01') ENGINE = InnoDB,
PARTITION d224 VALUES LESS THAN ('2023-01-01') ENGINE = InnoDB,
PARTITION d231 VALUES LESS THAN ('2023-03-01') ENGINE = InnoDB,
PARTITION d232 VALUES LESS THAN ('2023-06-01') ENGINE = InnoDB,
PARTITION d233 VALUES LESS THAN ('2023-09-01') ENGINE = InnoDB,
PARTITION d234 VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB,
PARTITION d241 VALUES LESS THAN ('2024-03-01') ENGINE = InnoDB,
PARTITION d242 VALUES LESS THAN ('2024-06-01') ENGINE = InnoDB,
PARTITION d243 VALUES LESS THAN ('2024-09-01') ENGINE = InnoDB,
PARTITION d244 VALUES LESS THAN ('2025-01-01') ENGINE = InnoDB,
PARTITION d251 VALUES LESS THAN ('2025-03-01') ENGINE = InnoDB,
PARTITION d252 VALUES LESS THAN ('2025-06-01') ENGINE = InnoDB,
PARTITION d253 VALUES LESS THAN ('2025-09-01') ENGINE = InnoDB,
PARTITION d254 VALUES LESS THAN ('2026-01-01') ENGINE = InnoDB,
PARTITION d261 VALUES LESS THAN ('2026-03-01') ENGINE = InnoDB,
PARTITION d262 VALUES LESS THAN ('2026-06-01') ENGINE = InnoDB,
PARTITION d263 VALUES LESS THAN ('2026-09-01') ENGINE = InnoDB,
PARTITION d264 VALUES LESS THAN ('2027-01-01') ENGINE = InnoDB,
PARTITION d271 VALUES LESS THAN ('2027-03-01') ENGINE = InnoDB,
PARTITION d272 VALUES LESS THAN ('2027-06-01') ENGINE = InnoDB,
PARTITION d273 VALUES LESS THAN ('2027-09-01') ENGINE = InnoDB,
PARTITION d274 VALUES LESS THAN ('2028-01-01') ENGINE = InnoDB,
PARTITION dmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;

[psiklop]

Rsa97,
mysql> EXPLAIN SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE
n=4;
+----+-------------+------------+----------------+------+---------------+-------------+---------+-------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+----------------+------+---------------+-------------+---------+-------+---------+----------+-----------------------------+
| 1 | PRIMARY | | NULL | ref | | | 8 | const | 10 | 100.00 | NULL |
| 2 | DERIVED | sms | d243,d244,d251 | ALL | time | NULL | NULL | NULL | 7408789 | 50.00 | Using where; Using filesort |
+----+-------------+------------+----------------+------+---------------+-------------+---------+-------+---------+----------+-----------------------------+
2 rows in set, 2 warnings (0,00 sec)

[Rsa97]

psiklop, Ещё EXPLAIN двух других вариантов запросов, которые работают быстро.

[psiklop]

Rsa97, вот первый
mysql> EXPLAIN SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR;
+----+-------------+-------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| 1 | SIMPLE | sms | d243,d244,d251 | ALL | time | NULL | NULL | NULL | 7408789 | 50.00 | Using where; Using filesort |
+----+-------------+-------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
1 row in set, 2 warnings (0,00 sec)

[psiklop]

и Второй
mysql> EXPLAIN SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE
n<=4;
+----+-------------+------------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 3704394 | 33.33 | Using where |
| 2 | DERIVED | sms | d243,d244,d251 | ALL | time | NULL | NULL | NULL | 7408789 | 50.00 | Using where; Using filesort |
+----+-------------+------------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
2 rows in set, 2 warnings (0,00 sec)

[Антон Антон]

Этот запрос должен найти четвертую смс каждого номера? или что? Я не понимаю (

[Akina]

А где DDL таблицы?

И ещё - после запуска на выполнение "долгого" запроса как выглядят расход памяти и активность диска? Не начинается ли материализация подзапроса, особенно с утыканием в недостаток дискового пространства?

И как себя поведёт

WITH cte AS (
    SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n 
    FROM sms 
    WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR
    )
SELECT * 
FROM  cte 
WHERE n=4;

?

[Антон Антон]

В любом случае я бы сортировку для начала вынес из окна в соответствующий select

[Akina]

PS. В мой последний запрос добавьте /*+ MAX_EXECUTION_TIME(60000) */ - чтобы не ребутать при слишком долгом выполнении. Ну или другой предел времени (он в миллисекундах)

[Akina]

Антон Антон,

я бы сортировку для начала вынес из окна в соответствующий select

??? это как? покажите кодом, что вы имеете в виду.

[psiklop]

Антон Антон, типа этого 4 смс, это начало работы далее что-то изменится, но получился завал на начальном этапе.

[psiklop]

Akina, попробую запрос, про DDL мне надо сначала прогуглить. С дисками все в порядке, 200gb free там точно имеется.

[Антон Антон]

Кажется, не хватает индекса по (phone, time desc) но может быть он не такой умный и не сможет сообразить, что сначала надо пронумеровать и в процессе нумеровки отсеять ненужное. Еще вопрос в том, какая часть таблицы падает в time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR и есть ли там индекс по time

[Антон Антон]

Using where уже указывает на промах с индексами, а Using filesort - это вообще жопа (свопается на диск в процессе сортировки ибо в память всё не влезает).

[psiklop]

Антон Антон, я человек простой, куда mysql свопает ему виднее, не я его писал, я им пользуюсь, я делал индексы по time и по phone, таблица даже разделена на партиции по кварталу. Тут вопрос почему n<=4 работает, а n=4 не работает.

[Антон Антон]

psiklop,

Тут вопрос почему n<=4 работает, а n=4 не работает.

потому что оптимизатор еще чуть чуть сходит с ума, то перышко, которое ломает спину верблюду. Но explain на всех запросах говорит, что индекс не используется. и я говорю про один составной индекс а не про два отдельных ибо окно такое, что ему нужно два и при этом отрезание по time. очень плохое условие.

[Ипатьев]

я бы по привычке попросил автора вывести SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
зуб даю на отсечение, что там 134217728, как минимум на локалке.
Ну и в целом посмотреть SHOW ENGINE INNODB STATUS\G

[psiklop]

Антон Антон, я не знал, что должен добавлять индексы под каждый запрос, за 4 года написано тысячи запросов и прекрасно жили до этого. Мне как программисту на php не понятно это, я так понимаю всю основную работу делает вложенный select и он завершается нормально (когда отдельно). Почему я должен делать индексы под внешний select который и должен всего лишь выбрать n=4 из уже готовых данных?

[psiklop]

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

[Ипатьев]

psiklop, не на "каждый", а на каждый тяжёлый. Если все данные берутся из индекса, а не по индексу мгновенно достаются из каждой записи, то запрос очевидно работает гораздо быстрее.
Но в любом случае самим индексам нужно где-то храниться. А "программисты на пхп", которые "не писали mysql" обычно об этом не задумываются.

[psiklop]

Ипатьев, есть и потяжелее, 5 секунд это не страшно, я повторюсь в сотый раз индексы есть и есть партиции. Да SHOW VARIABLES LIKE 'innodb_buffer_pool_size' равен 134217728

[Ипатьев]

кстати, после эксплейна надо написать SHOW WARNINGS;
там будет полезная информация, в частности запрос, как его переписал оптимизатор.

[Ипатьев]

'innodb_buffer_pool_size' равен 134217728

Ну вот и ответ.
Все ваши "мощные серверы" просто работают калориферами. Их память- сколько бы её не было - не используется.
Сделайте хотя бы пару гигов в пределах возможного, и сразу увидите разницу.

[psiklop]

Ипатьев, спасибо разницу наверняка увижу. Но только врядли в случае с n<=4 и n=4

[psiklop]

Ипатьев, прямо калориферами, а теперь будут Эпики ?