Оптимизация OFFSET в MySQL
Штука вроде очевидная, но всё же стоит её записать.
В базах данных offset работает так: выбираются первые записи, где кол-во выбираемых записей равно offset+limit, потом записи до offset отбрасываются и остаток уже возвращается в запросе. Покажу на примере.
Как всегда, любимая табличка mobile_stat_device
mysql> show create table mobile_stat_device\G
*************************** 1. row ***************************
Table: mobile_stat_device
Create Table: CREATE TABLE `mobile_stat_device` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`deviceUuid` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Unique device ID',
`deviceUuidHash` int unsigned GENERATED ALWAYS AS (crc32(`deviceUuid`)) STORED NOT NULL,
`createdAt` datetime NOT NULL COMMENT 'Inserting date',
PRIMARY KEY (`id`),
UNIQUE KEY `deviceUuid_udx` (`deviceUuidHash`,`deviceUuid`),
KEY `deviceUuidHash_idx` (`deviceUuidHash`)
) ENGINE=InnoDB AUTO_INCREMENT=2490316 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ciС большим количеством записей
mysql> select count(1) from mobile_stat_device; +----------+ | count(1) | +----------+ | 2097152 | +----------+
Запрос, который выполняется почти секунду:
mysql> select SQL_NO_CACHE * from mobile_stat_device order by id limit 10 offset 2050000; +---------+--------------------------------------+----------------+---------------------+ | id | deviceUuid | deviceUuidHash | createdAt | +---------+--------------------------------------+----------------+---------------------+ | 2377645 | 370f4d5e-519b-11eb-a85f-42010a9c0002 | 1792791173 | 2021-01-08 10:21:03 | | 2377646 | 370f4d6c-519b-11eb-a85f-42010a9c0002 | 3206227716 | 2021-01-08 10:21:03 | | 2377647 | 370f4d7b-519b-11eb-a85f-42010a9c0002 | 3766230312 | 2021-01-08 10:21:03 | | 2377648 | 370f4d89-519b-11eb-a85f-42010a9c0002 | 2525345549 | 2021-01-08 10:21:03 | | 2377649 | 370f4d98-519b-11eb-a85f-42010a9c0002 | 3387067681 | 2021-01-08 10:21:03 | | 2377650 | 370f4da6-519b-11eb-a85f-42010a9c0002 | 2785299536 | 2021-01-08 10:21:03 | | 2377651 | 370f4db5-519b-11eb-a85f-42010a9c0002 | 1202520612 | 2021-01-08 10:21:03 | | 2377652 | 370f4dc3-519b-11eb-a85f-42010a9c0002 | 3937153461 | 2021-01-08 10:21:03 | | 2377653 | 370f4dd2-519b-11eb-a85f-42010a9c0002 | 1015628201 | 2021-01-08 10:21:03 | | 2377654 | 370f4de1-519b-11eb-a85f-42010a9c0002 | 2783037901 | 2021-01-08 10:21:03 | +---------+--------------------------------------+----------------+---------------------+ 10 rows in set, 1 warning (0.50 sec)
С очень любопытным explain:
mysql> explain format=json select SQL_NO_CACHE * from mobile_stat_device order by id limit 10 offset 2050000\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "213695.60"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "mobile_stat_device",
"access_type": "index",
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"rows_examined_per_scan": 2050010,
"rows_produced_per_join": 2088550,
"filtered": "100.00",
"cost_info": {
"read_cost": "4840.61",
"eval_cost": "208855.00",
"prefix_cost": "213695.61",
"data_read_per_join": "318M"
},
"used_columns": [
"id",
"deviceUuid",
"deviceUuidHash",
"createdAt"
]
}
}
}
}Для того, что бы отдать 680 байт mysql прочитал 318 мегабайт. Кол-во затронутых записей rows_examined_per_scan = 2050010 = 2050000(offset) + 10(limit)
И что самое плохое в этом запросе - с увеличением offset производительность и дальше будет деградировать.
Как быть?
В данном конкретном случае, можно воспользоваться тем, что id идут по порядку и переписать запрос так
mysql> select SQL_NO_CACHE * from mobile_stat_device where id between 2377645 and 2377654 order by id; +---------+--------------------------------------+----------------+---------------------+ | id | deviceUuid | deviceUuidHash | createdAt | +---------+--------------------------------------+----------------+---------------------+ | 2377645 | 370f4d5e-519b-11eb-a85f-42010a9c0002 | 1792791173 | 2021-01-08 10:21:03 | | 2377646 | 370f4d6c-519b-11eb-a85f-42010a9c0002 | 3206227716 | 2021-01-08 10:21:03 | | 2377647 | 370f4d7b-519b-11eb-a85f-42010a9c0002 | 3766230312 | 2021-01-08 10:21:03 | | 2377648 | 370f4d89-519b-11eb-a85f-42010a9c0002 | 2525345549 | 2021-01-08 10:21:03 | | 2377649 | 370f4d98-519b-11eb-a85f-42010a9c0002 | 3387067681 | 2021-01-08 10:21:03 | | 2377650 | 370f4da6-519b-11eb-a85f-42010a9c0002 | 2785299536 | 2021-01-08 10:21:03 | | 2377651 | 370f4db5-519b-11eb-a85f-42010a9c0002 | 1202520612 | 2021-01-08 10:21:03 | | 2377652 | 370f4dc3-519b-11eb-a85f-42010a9c0002 | 3937153461 | 2021-01-08 10:21:03 | | 2377653 | 370f4dd2-519b-11eb-a85f-42010a9c0002 | 1015628201 | 2021-01-08 10:21:03 | | 2377654 | 370f4de1-519b-11eb-a85f-42010a9c0002 | 2783037901 | 2021-01-08 10:21:03 | +---------+--------------------------------------+----------------+---------------------+ 10 rows in set, 1 warning (0.00 sec)
Explain:
mysql> explain format=json select SQL_NO_CACHE * from mobile_stat_device where id between 2377645 and 2377654 order by id\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.57"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "mobile_stat_device",
"access_type": "range",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"rows_examined_per_scan": 10,
"rows_produced_per_join": 10,
"filtered": "100.00",
"cost_info": {
"read_cost": "1.57",
"eval_cost": "1.00",
"prefix_cost": "2.57",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"deviceUuid",
"deviceUuidHash",
"createdAt"
],
"attached_condition": "(`test`.`mobile_stat_device`.`id` between 2377645 and 2377654)"
}
}
}
}Вот, стало значительно веселее.
Если необходимо пробежаться по всем записям таблички чтобы, например, сделать экспорт, то можно использовать условие id > самого большого id прошлой выборки, но id <= самого большого id прошлой выборки + limit + 1. Условием остановки итераций будет значение id >= максимального значения id
Запросы для отображения по страницам - для чего обычно и используют лимиты и офсеты, тут смысла не имеют из-за большого количества данных. Если же наложить какие-то внешние фильтры, уменьшив там самым кол-во выбираемых данных, то и offset будет работать быстро.