Блог

Обзор

Я использую MySQL 5.7.30-33, и я сталкиваюсь с проблемой, которая, похоже, MySQL использует неправильный индекс при выполнении запроса. Я получаю 3-секундное время запроса, используя мой существующий запрос. Однако, просто изменив ORDER BY, удалив LIMIT или принудительно используя ИНДЕКС, я могу получить время запроса 0,01 секунды. К сожалению, мне нужно придерживаться моего исходного запроса (он встроен в приложение), поэтому было бы здорово, если бы это несоответствие можно было устранить в схеме / индексации.

Настройка / проблема

Моя структура таблицы выглядит следующим образом:

 CREATE TABLE `referrals` (
  `__id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `systemcreated` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referrerid` mediumtext COLLATE utf8mb4_unicode_ci,
  `referrersiteid` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  ... lots more mediumtext fields ...
  PRIMARY KEY (`__id`),
  KEY `systemcreated` (`systemcreated`,`referrersiteid`,`__id`)
) ENGINE=InnoDB AUTO_INCREMENT=53368 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED
 

В таблице всего ~ 55 тыс. строк, но она очень широкая, так как некоторые поля содержат огромные двоичные объекты:

 mysql> show table status like 'referrals'G;
*************************** 1. row ***************************
           Name: referrals
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
           Rows: 45641
 Avg_row_length: 767640
    Data_length: 35035897856
Max_data_length: 0
   Index_length: 3653632
      Data_free: 3670016
 Auto_increment: 54008
    Create_time: 2020-12-12 12:46:14
    Update_time: 2020-12-12 17:50:28
     Check_time: NULL
      Collation: utf8mb4_unicode_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED
        Comment: 
1 row in set (0.00 sec)
 

Приложение моего клиента запрашивает таблицу, используя это, и, к сожалению, это нелегко изменить:

 SELECT  *
    FROM  referrals
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id desc
    limit  16;
 

Это приводит к времени запроса около 3 секунд.

ОБЪЯСНЕНИЕ выглядит следующим образом:

  ---- ------------- ------------- ------------ ------- --------------- --------- --------- ------ ------ ---------- ------------- 
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------------- ------------ ------- --------------- --------- --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | referrals   | NULL       | index | systemcreated | PRIMARY | 4       | NULL |   32 |     5.56 | Using where |
 ---- ------------- ------------- ------------ ------- --------------- --------- --------- ------ ------ ---------- ------------- 
 

Обратите внимание, что для запроса используется ПЕРВИЧНЫЙ ключ, а не systemcreated индекс.

Эксперимент 1

Если я изменю запрос на использование ASC, а не DESC:

 SELECT  *
    FROM  referrals
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id asc
    limit  16;
 

then it takes 0.01 seconds, and the EXPLAIN looks to be the same:

  ---- ------------- ------------- ------------ ------- --------------- --------- --------- ------ ------ ---------- ------------- 
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------------- ------------ ------- --------------- --------- --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | referrals   | NULL       | index | systemcreated | PRIMARY | 4       | NULL |   32 |     5.56 | Using where |
 ---- ------------- ------------- ------------ ------- --------------- --------- --------- ------ ------ ---------- ------------- 
 

Experimentation 2

If I change the query to stick with ORDER BY __id DESC, but remove the LIMIT:

 SELECT  *
    FROM  referrals
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id desc;
 

тогда это также занимает 0,01 секунды, с ОБЪЯСНЕНИЕМ, подобным этому:

  ---- ------------- ------------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- --------------------------------------- 
| id | select_type | table       | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                 |
 ---- ------------- ------------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- --------------------------------------- 
|  1 | SIMPLE      | referrals   | NULL       | range | systemcreated | systemcreated | 406     | NULL | 2086 |    11.11 | Using index condition; Using filesort |
 ---- ------------- ------------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- --------------------------------------- 
 

Эксперимент 3

В качестве альтернативы, если я заставлю исходный запрос использовать systemcreated индекс, это также даст время запроса 0,01 секунды. Вот ОБЪЯСНЕНИЕ:

 mysql> explain     SELECT  *
    FROM  referrals USE INDEX (systemcreated)
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id desc
    limit  16;

 ---- ------------- -------------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- --------------------------------------- 
| id | select_type | table        | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                 |
 ---- ------------- -------------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- --------------------------------------- 
|  1 | SIMPLE      | referrals    | NULL       | range | systemcreated | systemcreated | 406     | NULL | 2086 |    11.11 | Using index condition; Using filesort |
 ---- ------------- -------------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- --------------------------------------- 
 

Эксперимент 4

Наконец, если я использую исходный ORDER BY __id DESC LIMIT 16, но выбираю меньшее количество полей, тогда он также возвращается через 0,01 секунды! Вот объяснение:

 mysql> explain     SELECT  field1, field2, field3, field4, field5
    FROM  referrals
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id desc
    limit  16;

 ---- ------------- ------------- ------------ ------- --------------- --------- --------- ------ ------ ---------- ------------- 
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------------- ------------ ------- --------------- --------- --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | referrals   | NULL       | index | systemcreated | PRIMARY | 4       | NULL |   32 |     5.56 | Using where |
 ---- ------------- ------------- ------------ ------- --------------- --------- --------- ------ ------ ---------- ------------- 
 

Краткие сведения

Таким образом, единственная комбинация, которая, похоже, работает плохо, это ORDER BY __id DESC LIMIT 16 .

Я думаю, что у меня правильно настроены индексы. Я запрашиваю через поля systemcreated and и referrersiteid упорядочиваю по __id , поэтому у меня есть индекс, определенный как (systemcreated, referrersiteid, __id), но MySQL, похоже, все еще использует ПЕРВИЧНЫЙ ключ.

Есть предложения?

• «Avg_row_length: 767640»; много MEDIUMTEXT . Строка ограничена примерно 8 КБ; переполнение переходит в блоки «вне записи». Чтение этих блоков требует дополнительных обращений к диску.
• SELECT * будет доступно для всех этих толстых столбцов. Общее количество операций чтения составит около 50 (по 16 КБ каждое). Это требует времени.
• (Пример 4) SELECT a,b,c,d выполнялся быстрее, потому что ему не нужно было извлекать все ~ 50 блоков в строке.
• Ваш вторичный индекс, ( systemcreated , referrersiteid , __id ), — полезен только первый столбец. Это происходит из — за systemcreated LIKE 'xxx%' . Это «диапазон». Как только диапазон достигнут, остальная часть индекса становится неэффективной. За исключением…
• «Индексные подсказки» ( USE INDEX(...) ) могут помочь сегодня, но могут ухудшить ситуацию завтра, когда изменится распределение данных.
• Если вы не можете избавиться от подстановочных LIKE знаков, я рекомендую эти два индекса:

     INDEX(systemcreated)
    INDEX(referrersiteid)
   

• Реальное ускорение может произойти, если вывернуть запрос наизнанку. То есть сначала найдите 16 идентификаторов, а затем найдите все эти громоздкие столбцы:

     SELECT  r2...   -- whatever you want
        FROM  
        (
            SELECT  __id
                FROM  referrals
                WHERE  `systemcreated` LIKE 'XXXXXX%'
                  AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
                order by  __id desc
                limit  16 
        ) AS r1
        JOIN  referrals r2 USING(__id)
        ORDER BY  __id DESC   -- yes, this needs repeating 
   

И сохраните имеющийся у вас вторичный индекс из 3 столбцов. Даже если он должен сканировать намного больше, чем 16 строк, чтобы найти нужные 16, он намного менее громоздкий. Это означает, что подзапрос («производная таблица») будет умеренно быстрым. Тогда во внешнем запросе все равно будет 16 запросов — возможно, 16 * 50 блоков для чтения. Общее количество прочитанных блоков все равно будет намного меньше.

Между ASC и on редко бывает заметная разница DESC ORDER BY .

Почему оптимизатор выбирает PK вместо, казалось бы, лучшего вторичного индекса? PK может быть лучшим, особенно если 16 строк находятся в «конце» (DESC) таблицы. Но это был бы ужасный выбор, если бы ему пришлось сканировать всю таблицу, не найдя 16 строк.

Между тем, проверка подстановочных знаков делает вторичный индекс полезным лишь частично. Оптимизатор принимает решение на основе неадекватной статистики. Иногда это похоже на подбрасывание монеты.

Если вы используете мою переформулировку наизнанку, я рекомендую следующие два составных индекса — оптимизатор может сделать полуинтеллектуальный, полу-правильный выбор между ними для производной таблицы:

 INDEX(systemcreated, referrersiteid, __id),
INDEX(referrersiteid, systemcreated, __id)
 

Он по-прежнему будет указывать «сортировка файлов», но не волнуйтесь; он сортирует только 16 строк.

И, помните, это снижает производительность. SELECT * (Хотя, возможно, вы не можете это исправить.)