Как оптимизировать ORDER BY в MySQL?

Question

[MikUrrey]

Таблица:

CREATE TABLE `event` (
  `eventId` bigint unsigned NOT NULL AUTO_INCREMENT,
  `urlId` int DEFAULT NULL,
###... несколько незначимых в данном случае столбцов опущены
  `clientTime` bigint unsigned NOT NULL,
  PRIMARY KEY (`eventId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Запрос:

SELECT * FROM `event` WHERE `urlId`=432 ORDER BY `clientTime` ASC;

В таблице сейчас порядка 6 миллионов записей, и так вышло, что значения clientTime повторяются крайне редко (временные метки). Как я понял, по этой причине сортировка не цепляется за индексы (пробовал urlId+clientTime и просто clientTime) и запрос работает крайне медленно. Как быть?

Главное - сортировка по clientTime принципиально важна для правильной постобработки данных.

EXPLAIN SELECT * FROM `event` WHERE `urlId`=432 ORDER BY `clientTime` ASC;

+---+---+---+---+---+---+---+---+---+---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+---+---+---+---+---+---+---+
| 1 | SIMPLE | event | ALL | urlId_sort,select_no_time | NULL | NULL | NULL | 5263392 | Using where; Using filesort |
+---+---+---+---+---+---+---+---+---+---+

Comments

[mayton2019]

А для кого надо оптимизировать? Для человека? Или для робота?

[MikUrrey]

mayton2019, для человека. Вытащить -> обработать и почистить -> в JSON и на график.

[mayton2019]

Попробуй построить композитный индекс по urlId, clientTime

И второй вариант - если urlId - низкокардинальный - то попробуй переделать таблицу на partitioned by (urlId)

[ScriptKiddo]

Нужны все поля из таблицы?

[MikUrrey]

ScriptKiddo, да, все.
mayton2019, композитный индекс пробовал первым, не работает. UrlId низкокардинальный, но по сравнению с clientTime. По факту в нем порядка нескольких тысяч уникальных значений. Стоит пробовать partitioned?

[Дмитрий]

а explain то какой?

[Mylistryx]

Может немного не в тему, но:
Сколько же это вы ожидаете событий, что не уложитесь в unsigned int?
clientTime тоже с лихвой влезет в int даже без unsigned (проекты обычно столько не живут в современных реалиях, но путь будет unsigned int)
На моей памяти изменение PK id поля с bigint на int ускорило выборку с сортировкой на порядок. Было давно, лет так 10 назад, возможно сейчас это не имеет значения, но я бы попробовал.
Ну а далее уже партицирование накидывать и прочее...

[Vitsliputsli]

композитный индекс пробовал первым, не работает.

вот что значит не работает? не работает так быстро как хотелось бы? MySQL его не использует? использует частично?

Стоит пробовать partitioned?

Стоит показать как минимум explain запроса, т.к. начинать надо с него, а не с перехерачивания всей таблицы в надежде "вдруг заработает".

[Macaronin]

Сделай меньше записей

[MikUrrey]

Vitsliputsli, сразу так и написал - сортировка не цепляется за индексы (пробовал urlId+clientTime и просто clientTime). То есть, индекс не используется вообще, "using where, using filesort".
Дмитрий, добавил в вопрос.
Mylistryx, понял, попробую

[Дмитрий]

MikUrrey, ну он вообще индекс не использует. Никакой

show indexes from event

[Дмитрий]

MikUrrey, А force index пробовали? Если да - то быстрее работает или нет?

[mayton2019]

MikUrrey, да пробуй. Только покажи как ты это сделал. Скрипты. И покажи explain plan для выборки.

У тебя по идее должно быть нечто вроде

PARTITION BY HASH(urlId)
PARTITIONS ...;

[MikUrrey]

Дмитрий,

show indexes from `event`;

event	0	PRIMARY	1	eventId	A

event	1	clientTime_sort	1	clientTime	A

event	1	typeId_sort	1	typeId	A

event	1	urlId_sort	1	urlId	A

event	1	select_no_time	1	urlId	A
event	1	select_no_time	2	clientTime	A

на повторы прошу не обращать внимания, это экспериментальные индексы.

А force index пробовали? Если да - то быстрее работает или нет?

Попробовал - да, действительно, быстрее, и особенно с force index(clientTime_sort). А вот гибридный ворочается тяжелее. Применив так же совет Mylistryx, удалось впервые получить самый тяжелый набор данных, но все еще медленно.

[Vitsliputsli]

MikUrrey, а где у вас limit в запросе? Если у urlId действительно низкая селективность, значит вы вытягиваете из базы сотни тысяч данных, а это в любом случае будет медленно (т.к. из explain кудато исчез filtered, то число нужных строк не понятно).
Партиционирование классная штука, но я надеюсь, вы понимаете, что делаете. Индекс в партициях все равно нужен, поэтому вряд ли это будет гораздо быстрее, чем правильный индекс в неразделенной таблице. А, если вдруг окажется, что у вас не один единственный запрос на всю базу, и понадобится выбирать данные без оглядки urlId, то при большом кол-ве партиций будет весело.

[Дмитрий]

MikUrrey, ну поидее force index(select_no_time) для запроса SELECT * FROM `event` WHERE `urlId`=432 ORDER BY `clientTime` ASC; должно работать лучше.

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

[Дмитрий]

MikUrrey, полная структура таблицы? версия mysql?

И как то странно - у вас в explaine судя по всему нет ограничения по where?

[MikUrrey]

Дмитрий, есть. Полная структура такая:

CREATE TABLE `event` (
  `eventId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `urlId` int(11) DEFAULT NULL,
  `typeId` int(11) NOT NULL,
  `payload` varchar(191) NOT NULL DEFAULT '',
  `userAgent` varchar(191) NOT NULL DEFAULT '',
  `userIp` varchar(16) NOT NULL,
  `platform` varchar(30) NOT NULL DEFAULT '',
  `browser` varchar(30) NOT NULL DEFAULT '',
  `browserVersion` varchar(30) NOT NULL DEFAULT '',
  `country` varchar(191) NOT NULL DEFAULT '',
  `region` varchar(191) NOT NULL DEFAULT '',
  `city` varchar(191) NOT NULL DEFAULT '',
  `age` tinyint(3) NOT NULL DEFAULT 0,
  `isMobile` tinyint(1) NOT NULL DEFAULT 0,
  `userName` varchar(191) NOT NULL DEFAULT '',
  `userId` varchar(64) NOT NULL,
  `clientTime` bigint(20) unsigned NOT NULL,
  `time` int(11) NOT NULL,
  PRIMARY KEY (`eventId`),
  KEY `clientTime_sort` (`clientTime`),
  KEY `typeId_sort` (`typeId`),
  KEY `urlId_sort` (`urlId`),
  KEY `select_no_time` (`urlId`,`clientTime`) USING HASH,
  CONSTRAINT `event_ibfk_2` FOREIGN KEY (`typeId`) REFERENCES `type` (`typeId`) ON DELETE CASCADE,
  CONSTRAINT `fk_url_id` FOREIGN KEY (`urlId`) REFERENCES `url` (`urlId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `type` (
  `typeId` int(11) NOT NULL AUTO_INCREMENT,
  `label` varchar(30) NOT NULL,
  `title` varchar(191) NOT NULL,
  `table` varchar(30) NOT NULL,
  PRIMARY KEY (`typeId`),
  UNIQUE KEY `label` (`label`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `url` (
  `urlId` int(11) NOT NULL AUTO_INCREMENT,
  `siteId` int(11) NOT NULL,
  `url` varchar(225) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`urlId`),
  UNIQUE KEY `siteId` (`siteId`,`url`),
  CONSTRAINT `url_ibfk_1` FOREIGN KEY (`siteId`) REFERENCES `site` (`siteId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `site` (
  `siteId` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(191) NOT NULL,
  PRIMARY KEY (`siteId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

Не знаю, может сервер ограничен в ресурсах сильно, или действительно где-то ошибка в структуре? Или узкий канал в сети хоста?

[MikUrrey]

Vitsliputsli, в том и дело - лимит не нужен, нужно разом получить всё. С лимитом, конечно, было бы совсем другое дело.

[Vitsliputsli]

MikUrrey, если вы забираете из базы миллионы или сотни тысяч записей, это в принципе не будет быстро. Да и зачем?

[Дмитрий]

MikUrrey, стоп. Вы получаете все 6 миллионов? Ну а тогда зачем MySQL какие то индексы брать? Ему один хрен все эти 6 миллионов перелопачивать.

[Дмитрий]

MikUrrey, У вас всего 6 миллионов записей в таблице или по urlId 6 миллионов а в таблице миллиард?

[MikUrrey]

Дмитрий, Vitsliputsli, нет-нет, их всего 6 миллионов, получаю по urlId переменное количество. В теме указан запрос, им я пользуюсь

[Дмитрий]

MikUrrey, а explain тогда какого запроса вы сделали?

[MikUrrey]

Дмитрий, того самого, а что не так?

[Дмитрий]

MikUrrey, в том что в explain не видно что вы выбираете по какому то условию.



вот вам 2 запроса - в одном where есть, во втором нет. У вас второй вариант

[MikUrrey]

Дмитрий, а так?


Да, еще, кажется, вы выше спрашивали, про тип и версию БД. 10.6.4-MariaDB

[Дмитрий]

MikUrrey, эмммм, я честно говоря удивлен. А если сделать explain с force index?

А так же сделать explain analyze с force index и без?

[MikUrrey]

ANALYZE в веб-интерфейсе отработать сейчас не успевает (только с LIMIT)

[Дмитрий]

MikUrrey, угу. Как пишут в доке - если в запрос попадает больше 20% записей таблицы то MariaDB кладет хер на index.

Я так понимаю что если мы сделаем
select urlId, count(*) from event group by urlId;
То по urlId 432 у нас будет в районе 50% записей? А если выбрать какой нибудь url у которого будет меньше 20% чего explain даст?

[MikUrrey]

Дмитрий, urlId=432 выходит в районе 22%.
Вот explain запроса с кол-вом результатов примерно 1%:

[Дмитрий]

MikUrrey,

urlId=432 выходит в районе 22%.

Ну то есть больше 20%. Собственно работает по доке.

Вот explain запроса с кол-вом результатов примерно 1%:

А здесь индекс автоматом подключился все дела.

Слушайте - ну как бы вариант реально партицировать это дело. Разбить by range так что бы такие urlId с таким количеством записей вывались в отдельные партиции. Вопрос только как дальше будут распределяется данные - будет ли держаться такие пики записей у каких то отдельных урлов - или в дальнейшем все сгладится. Во втором случае - может и стоит гвоздями через force index прибить - или посмотреть можно ли через какой то параметр мускула сдвинуть эти 20%

[MikUrrey]

Дмитрий, в итоге, прилепил force index и добился стабильности выполнения другими средствами. Дробить не стал, итоговый результат всех устроил.

Answer 1

[Игорь]

Добавьте в таблице event сортировку по полю urlId, или даже urlId, clientTime
Не понял вашей проблемы.