Производительность MySQL - строка против целого

ProgramBox

Производительность MySQL — строка против целого

Post author:admin
Запись опубликована:30 марта, 2023
Post category:Вопросы по программированию

#mysql #sql #database #performance #mysql-workbench

#mysql #sql #База данных #Производительность #mysql-workbench

Вопрос:

Я осознал очень странное поведение MySQL, которому у меня нет объяснения.

Это не слишком сложный запрос:

 SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = 2 
    OR enr.Enricher4State = 2 
    OR enr.Enricher5State = 2 
    OR enr.Enricher9State = 2
  );

Столбцы Enrichher3state, Enrichher4state, enrichher5state, enrichher9state действительно имеют индекс и относятся к типу данных int(11).

Теперь я попытался изменить это состояние Enricher [x] на строку:

 SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = '2' 
    OR enr.Enricher4State = '2' 
    OR enr.Enricher5State = '2' 
    OR enr.Enricher9State = '2'
  );

Любой здравый смысл сказал бы, что строковый вариант должен работать так же или медленнее, потому что тип данных столбца — integer!

Но, по-видимому, это не так!

Запрос с целочисленной нотацией (первый): 7.23048825с

Запрос со строковой нотацией (последний): 5.22188450с

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

Я абсолютно не представляю, как может произойти эта разница — и если это означает, что я должен изменить все запросы в моем проекте, используя строковую нотацию…

Я использую MySQL версии 5.7.10

Согласно вашим комментариям, я деактивировал все службы, которые записывают или считывают данные в базу данных, и повторил эксперимент.

А) Целочисленная запись:

 SET profiling=0;
SET profiling=1;

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = 2 
    OR enr.Enricher4State = 2 
    OR enr.Enricher5State = 2 
    OR enr.Enricher9State = 2
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = 2 
    OR enr.Enricher4State = 2 
    OR enr.Enricher5State = 2 
    OR enr.Enricher9State = 2
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = 2 
    OR enr.Enricher4State = 2 
    OR enr.Enricher5State = 2 
    OR enr.Enricher9State = 2
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = 2 
    OR enr.Enricher4State = 2 
    OR enr.Enricher5State = 2 
    OR enr.Enricher9State = 2
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = 2 
    OR enr.Enricher4State = 2 
    OR enr.Enricher5State = 2 
    OR enr.Enricher9State = 2
  );

  SHOW PROFILES;

Время выполнения каждого запроса:

6.42429325
5.95059900
6.34392825
6.53041775
6.69593450

Б) Строковая нотация:

 SET profiling=0;
SET profiling=1;

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = '2' 
    OR enr.Enricher4State = '2' 
    OR enr.Enricher5State = '2' 
    OR enr.Enricher9State = '2'
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = '2' 
    OR enr.Enricher4State = '2' 
    OR enr.Enricher5State = '2' 
    OR enr.Enricher9State = '2'
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = '2' 
    OR enr.Enricher4State = '2' 
    OR enr.Enricher5State = '2' 
    OR enr.Enricher9State = '2'
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = '2' 
    OR enr.Enricher4State = '2' 
    OR enr.Enricher5State = '2' 
    OR enr.Enricher9State = '2'
  );

SELECT 
  COUNT(*) 
FROM 
  incidents.incidents AS inc, 
  incidents.enrichment AS enr 
WHERE 
  inc.Id <= 606734 
  AND inc.Id >= 1 
  AND inc.Id = enr.ParentTableId 
  AND (
    enr.Enricher3State = '2' 
    OR enr.Enricher4State = '2' 
    OR enr.Enricher5State = '2' 
    OR enr.Enricher9State = '2'
  );

  SHOW PROFILES;

Время выполнения:

5.07188875
4.90356250
4.86164300
4.48403375
5.06533725

Как вы можете ясно видеть, строковая нотация все еще быстрее!

Такое же поведение было обнаружено и другими разработчиками моей команды, поэтому я мог исключить временную глупость себя…

1. Вероятно, работает буферный кэш InnoDB. Попробуйте еще раз, на этот раз выполнив каждый запрос дважды подряд. Отбросьте первый результат, сообщите второй результат каждого запроса. То есть, запустите целочисленный запрос, целочисленный запрос (сообщите об этом), строковый запрос, строковый запрос (сообщите об этом).) Кроме того, если результаты все еще различаются, вплоть до 4 каждый, отбросьте первый еще, а затем усредните оставшиеся 3 для каждого запроса.

2. Также не должно быть никакой другой активности в базе данных, чтобы действительно сравнивать яблоки с яблоками.

3. Я изменил тест в соответствии с вашими данными, но результат все тот же.

4. Что это говорит, когда вы вводите ОБЪЯСНЕНИЕ перед вашим запросом?

Ответ №1:

Поскольку поля проиндексированы, и у вас есть условие OR, а запрос имеет целочисленную константу в качестве условия, MySQL может потратить время на вычисление перекрестного объединения индексов, а затем выполнить сканирование таблицы, а со строковой константой MySQL не учитывает индексацию, а просто выполняет сканирование таблицы.

Это тот случай, когда наличие индексов во многих полях, которые используются в OR condition, является не преимуществом, а дополнительной работой для MySQL.

Условие ИЛИ не предоставляет требуемую индексацию участвующих полей, довольно часто наличие индексов в полях «1,2,3,4» плохо сказывается на таблице. Эти поля следует вынести в отдельную таблицу.

Добавлено: запустите EXPLAIN, и если вы увидите поля индексов «1,2,3,4», перечисленные для рассматриваемых ключей, это то, на что MySQL тратит время.

Ответ №2:

Учитывая ответ Сергея Титаренко, я удалил индексы в столбцах состояния Enricher [x].

Время выполнения для целочисленной записи:

4.93739900
5.01461550
5.05932075
5.02891175
5.02525075

Время выполнения для строковой записи:

5.04365650
5.07545950
5.12358825
5.14665200
5.15426525

Время выполнения сейчас примерно такое же.

Действительно, при наличии нескольких индексов в столбцах, связанных с ИЛИ, следует соблюдать осторожность.

Кажется, я случайно обнаружил хороший обходной путь (кроме удаления индекса), создав строку из целого числа…

1. Да, это хороший обходной путь, но вы должны полагаться на то, что разработчики будут следовать ему, а ваша платформа позволит использовать кавычки при подготовке запросов. В других случаях (например, одно условие или условия с И) использование кавычек для целочисленной константы будет большой проблемой. Учитывая общие причины для полей «1,2,3,4» в таблице, их не следует индексировать (и часто вы не можете просто переделать таблицу, чтобы избежать их использования).

2. Лично я бы попробовал написать запрос по-другому, избегая OR ‘ввода предикатов, потому что оптимизатор MySQL, как правило, не генерирует оптимальные планы выполнения для запросов, которые имеют OR ‘общие предикаты.

Ответ №3:

Учитывая, что вы выполнили каждый из запросов несколько раз и выдали результаты первого выполнения, мы видим значительную разницу в среднем времени выполнения.

Разница в производительности, вероятно, связана с различием в плане выполнения.

Я бы внимательно посмотрел на вывод из EXPLAIN EXTENDED по обоим запросам. Очень вероятно, что планы выполнения каким-то образом отличаются (какие индексы используются, порядок операций и т.д.).

Мои наблюдения… Оптимизатор запросов MySQL и запросы с OR условиями… планы запросов не являются оптимальными. Чтобы повысить производительность, я обычно прибегаю к разбиению запроса и использованию операций UNION ALL set.

Для получения «count» я был бы склонен написать запрос следующим образом:

   SELECT SUM(2 IN (enr.enricher3state,
                   enr.enricher4state,
                   enr.enricher5state,
                   enr.enricher9state))
    FROM incidents.incidents inc
    JOIN incidents.enrichment enr 
      ON enr.parenttableid = inc.id
   WHERE inc.id <= 606734 
     AND inc.id >= 1

Я был бы уверен, что у меня есть покрывающий индекс, например

 ON enrichment (parenttableid, enricher3state, enricher4state,
                              enricher5state, enricher9state)

(или любой индекс с parenttableid в качестве ведущего столбца, который также включает остальные четыре столбца)

Затем я бы проверил РАСШИРЕННЫЙ вывод и производительность EXPLAIN.

Ответ №4:

Сравнение с числами

 char = 123   -- slow because it converts the char to numeric; can't use index
char = '123' -- fine
int = 123    -- fine
int = '123'  -- fine - because '123' is converted to numeric up front

Итог: всегда безопасно заключать константы в кавычки.

или

OR по сути, не поддается оптимизации. Однако следующее может иметь тот же эффект, но быстрее…

Общее правило при проектировании схемы: «Не распределяйте массив объектов по столбцам». Вместо этого создайте другую таблицу и установите между ними соотношение 1: много. Это может быть лучшим решением для повышения производительности.

Пожалуйста, используйте JOIN ... ON ... синтаксис, а не «соединение запятыми».

Профилирование

В 5.6.7 говорится: «Инструкции SHOW PROFILE и SHOW PROFILES. Вместо этого используйте схему производительности; см. Схему производительности MySQL.»

Индексирование

Редко бывает, чтобы индекс был полезен в столбце с низкой мощностью, например, как я ожидаю, enrichher3state .

В vs ИЛИ

2 IN (...) по сравнению с ..=2 OR ..=2 OR... — это, вероятно, не имеет большого значения. Индекс не может быть использован; оба варианта сопряжены с некоторой сложностью.

Подробная информация

Нужно посмотреть SHOW CREATE TABLE для обеих таблиц.
Нужно посмотреть EXPLAIN SELECT ... .