MySQL. История одного медленного запроса

SQL запросы иногда бывают не оптимизированы, из-за неправильного понимания механизмов работы

Есть вот такая табличка:

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

Табличка с uuid устройств. Для ускорения запросов добавлено поле deviceUuidHash и создан комбинированный уникальный индекс по полям deviceUuidHash и deviceUuid.

Кол-во записей в mobile_stat_device чуть больше 2х миллионов:

mysql> select count(1) from mobile_stat_device;
+----------+
| count(1) |
+----------+
|  2097152 |
+----------+

Нам необходимо выбрать id для определенных uuid устройств. Мой первый запрос выглядел вот так:

mysql> select id, deviceUuid from mobile_stat_device where 
(deviceUuid = '36fffc8d-519b-11eb-a85f-42010a9c0002' and deviceUuidHash = crc32(deviceUuid)) or
(deviceUuid = '36fffd73-519b-11eb-a85f-42010a9c0002' and deviceUuidHash = crc32(deviceUuid));
+---------+--------------------------------------+
| id      | deviceUuid                           |
+---------+--------------------------------------+
| 2327645 | 36fffc8d-519b-11eb-a85f-42010a9c0002 |
| 2327653 | 36fffd73-519b-11eb-a85f-42010a9c0002 |
+---------+--------------------------------------+

Посмотрев на explain, я успокоился - индекс используется, ну и ладно

mysql> explain select id, deviceUuid from mobile_stat_device where (deviceUuid = '36fffc8d-519b-11eb-a85f-42010a9c0002' and deviceUuidHash=crc32(deviceUuid)) or (deviceUuid = '36fffd73-519b-11eb-a85f-42010a9c0002' and deviceUuidHash = crc32(deviceUuid));
+----+-------------+--------------------+------------+-------+---------------+----------------+---------+------+---------+----------+--------------------------+
| id | select_type | table              | partitions | type  | possible_keys | key            | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+--------------------+------------+-------+---------------+----------------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | mobile_stat_device | NULL       | index | NULL          | deviceUuid_udx | 148     | NULL | 2088550 |     1.99 | Using where; Using index |
+----+-------------+--------------------+------------+-------+---------------+----------------+---------+------+---------+----------+--------------------------+

Я не обратил внимание на количество rows в эксплейне. Точнее, когда данных мало, то и кол-во rows приемлемое. Ошибочность запроса стала понятна только когда нагрузил данными табличку и еще раз проверил скорость запроса.

Вот тот же explain в более информативном виде.

mysql> explain format=json select id, deviceUuid from mobile_stat_device where (deviceUuid = '36fffc8d-519b-11eb-a85f-42010a9c0002' and deviceUuidHash=crc32(deviceUuid)) or (deviceUuid = '36fffd73-519b-11eb-a85f-42010a9c0002' and deviceUuidHash = crc32(deviceUuid))\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "216917.41"
    },
    "table": {
      "table_name": "mobile_stat_device",
      "access_type": "index",
      "key": "deviceUuid_udx",
      "used_key_parts": [
        "deviceUuidHash",
        "deviceUuid"
      ],
      "key_length": "148",
      "rows_examined_per_scan": 2088550,
      "rows_produced_per_join": 41562,
      "filtered": "1.99",
      "using_index": true,
      "cost_info": {
        "read_cost": "212761.19",
        "eval_cost": "4156.21",
        "prefix_cost": "216917.41",
        "data_read_per_join": "6M"
      },
      "used_columns": [
        "id",
        "deviceUuid",
        "deviceUuidHash"
      ],
      "attached_condition": "(((`test`.`mobile_stat_device`.`deviceUuid` = '36fffc8d-519b-11eb-a85f-42010a9c0002') and (`test`.`mobile_stat_device`.`deviceUuidHash` = crc32(`test`.`mobile_stat_device`.`deviceUuid`))) or ((`test`.`mobile_stat_device`.`deviceUuid` = '36fffd73-519b-11eb-a85f-42010a9c0002') and (`test`.`mobile_stat_device`.`deviceUuidHash` = crc32(`test`.`mobile_stat_device`.`deviceUuid`))))"
    }
  }
}

О чём это говорит? Запрос пробегает по 2088550 записям и для ответа использует 41562. А так быть не должно. Где-то ошибка.

Внимательный читатель уже догадался где. Вот в этом условии - and deviceUuidHash = crc32(deviceUuid). Похоже, MySQL сначала вычисляет значение crc32 для всех записей, а потом уже, используя индекс, делается селект.

Переписанный запрос.

mysql> explain format=json select id, deviceUuid from mobile_stat_device
where (deviceUuid = '36fffc8d-519b-11eb-a85f-42010a9c0002' and 
deviceUuidHash=crc32('36fffc8d-519b-11eb-a85f-42010a9c0002')) or 
(deviceUuid = '36fffd73-519b-11eb-a85f-42010a9c0002' and 
deviceUuidHash = crc32('36fffd73-519b-11eb-a85f-42010a9c0002'))\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.85"
    },
    "table": {
      "table_name": "mobile_stat_device",
      "access_type": "range",
      "possible_keys": [
        "deviceUuid_udx",
        "deviceUuidHash_idx"
      ],
      "key": "deviceUuid_udx",
      "used_key_parts": [
        "deviceUuidHash",
        "deviceUuid"
      ],
      "key_length": "148",
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 2,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "0.65",
        "eval_cost": "0.20",
        "prefix_cost": "0.85",
        "data_read_per_join": "320"
      },
      "used_columns": [
        "id",
        "deviceUuid",
        "deviceUuidHash"
      ],
      "attached_condition": "(((`test`.`mobile_stat_device`.`deviceUuidHash` = <cache>(crc32('36fffc8d-519b-11eb-a85f-42010a9c0002'))) and (`test`.`mobile_stat_device`.`deviceUuid` = '36fffc8d-519b-11eb-a85f-42010a9c0002')) or ((`test`.`mobile_stat_device`.`deviceUuidHash` = <cache>(crc32('36fffd73-519b-11eb-a85f-42010a9c0002'))) and (`test`.`mobile_stat_device`.`deviceUuid` = '36fffd73-519b-11eb-a85f-42010a9c0002')))"
    }
  }
}

Обратите внимание, насколько упал query_cost и насколько меньше записей выбирается.

Вывода два:

1. Быть осторожнее с вычислительными значениями в запросах, если эти значения используются в where.
2. Нагружать БД данными и потом уже проверять насколько корректно отрабатывают запросы.