MySQL Как оптимизировать запрос по дате в таблице с большими данными?

Question

[Денис]

Таблица request (запросы), в ней 3 000 000 записей с 2019-01-11 по 2019-08-07
В таблице по столбцу date есть индекс key_date.
+------------+------------+
| id (INT) | date (DATE)|
+------------+------------+
| 1 | 2019-01-19 |
+------------+------------+
| 2 | 2019-01-20 |
+------------+------------+
| 3 | 2019-01-20 |
+------------+------------+
| 4 | 2019-01-22 |
+------------+------------+
| 5 | 2019-01-23 |
+------------+------------+
| ... | ... |
+------------+------------+
| 2999999 | 2019-08-07 |
+------------+------------+
| 3000000 | 2019-08-07 |
+------------+------------+

Если использовать конструкцию ниже, то запрос будет долгим:
Вариант №1

SELECT 
    `request`.`id`, `request`.`date`
FROM
    `request`
WHERE
    `request`.`date` BETWEEN '2019-01-18' AND '2019-01-19'
ORDER BY `request`.`id` DESC
LIMIT 10;

Время выполнения: 2.574 sec / 0.000 sec (жестко!)
# id, date
'127362', '2019-01-19'
'127361', '2019-01-19'
'127360', '2019-01-19'
'127359', '2019-01-19'
'127358', '2019-01-19'
'127357', '2019-01-19'
'127356', '2019-01-19'
'127355', '2019-01-19'
'127354', '2019-01-19'
'127353', '2019-01-19'

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "21453.81"
    },
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "request",
        "access_type": "index",
        "possible_keys": [
          "fk_date"
        ],
        "key": "PRIMARY",
        "used_key_parts": [
          "id",
          "id_area",
          "id_transport"
        ],
        "key_length": "12",
        "rows_examined_per_scan": 561,
        "rows_produced_per_join": 53388,
        "filtered": "1.78",
        "cost_info": {
          "read_cost": "10776.21",
          "eval_cost": "10677.60",
          "prefix_cost": "21453.81",
          "data_read_per_join": "27M"
        },
        "used_columns": [
          "id",
          "date",
          "id_area",
          "id_transport"
        ],
        "attached_condition": "(`host`.`request`.`date` between '2019-01-18' and '2019-01-19')"
      }
    }
  }
}

Так как записи в таблицу ведутся постоянно и дата каждого дня индексируется, то есть ли смысл использовать вложенные запросы, которые бы находили id по MIN() & MAX(), а дальше можно было бы определять интервал значений по идентификаторам?

Вариант №2

SELECT 
    `request`.`id`, `request`.`date`
FROM
    `request`
WHERE
    `request`.`id` BETWEEN (SELECT 
    MIN(`request`.`id`)
FROM
    `request`
WHERE
    `request`.`date` = '2019-01-18'
ORDER BY `request`.`id`) AND (SELECT 
    MAX(`request`.`id`)
FROM
    `request`
WHERE
    `request`.`date` = '2019-01-19'
ORDER BY `request`.`id`)
ORDER BY `request`.`id` DESC
LIMIT 10;

# id, date
'127362', '2019-01-19'
'127361', '2019-01-19'
'127360', '2019-01-19'
'127359', '2019-01-19'
'127358', '2019-01-19'
'127357', '2019-01-19'
'127356', '2019-01-19'
'127355', '2019-01-19'
'127354', '2019-01-19'
'127353', '2019-01-19'

ОГО! Время запроса сократилось и составило 0.078 sec / 0.000 sec

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "22927.31"
    },
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "request",
        "access_type": "range",
        "possible_keys": [
          "PRIMARY"
        ],
        "key": "PRIMARY",
        "used_key_parts": [
          "id"
        ],
        "key_length": "4",
        "rows_examined_per_scan": 57088,
        "rows_produced_per_join": 57088,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "11509.72",
          "eval_cost": "11417.60",
          "prefix_cost": "22927.32",
          "data_read_per_join": "29M"
        },
        "used_columns": [
          "id",
          "date",
          "id_area",
          "id_transport"
        ],
        "attached_condition": "(`host`.`request`.`id` between ( select#2 ... and  select#3 ... ))",
        "attached_subqueries": [
          {
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 3,
              "message": "Select tables optimized away"
            }
          },
          {
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 2,
              "message": "Select tables optimized away"
            }
          }
        ]
      }
    }
  }
}

Пробовал извращать формат даты с DATE на INT в виде UNIX_TIMESTAMP - работает хуже:
Вариант №3

SELECT 
    `request`.`id`, `request`.`date`
FROM
    `request`
WHERE
    `request`.`id` BETWEEN (SELECT 
    MIN(`request`.`id`)
FROM
    `request`
WHERE
    `request`.`date` > UNIX_TIMESTAMP('2019-01-18')
ORDER BY `request`.`id`) AND (SELECT 
    MAX(`request`.`id`)
FROM
    `request`
WHERE
    `request`.`date` < UNIX_TIMESTAMP('2019-01-19')
ORDER BY `request`.`id`)
ORDER BY `request`.`id` DESC
LIMIT 10;

1.326 sec / 0.000 sec

В данном случае индекс ПОЧТИ уникальный, селективность высокая. Большая вероятность не попасть в индекс, поэтому только операторы < и >, а это большие затраты!

Самый главный вопрос: КАК МОЖНО ОПТИМИЗИРОВАТЬ ЗАПРОС ПО ДАТЕ?

Comments

[Siverius]

Partitioning
Как вариант

[Adamos]

Если вам часто нужна выборка за год - проще сделать колонку "год" с индексом на ней. И запрос будет летать...

Answer 1

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

ADD INDEX `t` (`date` ASC, `id` DESC) VISIBLE; или даже ADD INDEX `t` (`date` DESC, `id` DESC) VISIBLE; если и даты и id неубывают и мускуль достаточно умный