Почему SQL-запрос медленнее на MacBook M2 с MySQL 9.5

На MacBook M2 с MySQL 9.5 запрос выполняется медленнее, чем на виртуальном хостинге с MySQL 8.0, в основном из-за различий в оптимизаторе MySQL, операционной системе и отсутствии правильной конфигурации MySQL.

Содержание

• Различия между MySQL 8.0 и 9.5
• Влияние операционной системы на производительность
• Проблемы с временными таблицами и вложенными циклами
• Настройка MySQL my.cnf для MacBook M2
• Оптимизация конкретного запроса
• Практические рекомендации

Различия между MySQL 8.0 и 9.5

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

Как отмечено в Percona блоге, MySQL 8.0 требует больше аппаратных ресурсов для выполнения тех же операций, особенно операций записи на диск. Интересно, что одна из статей утверждает, что MySQL 9.0 почти на 40% быстрее в операциях чтения, записи и обновления, но это может зависеть от типа рабочей нагрузки.

В вашем случае проблема может заключаться в том, что оптимизатор MySQL 9.5 выбирает менее эффективный план выполнения для вашего запроса по сравнению с MySQL 8.0. В EXPLAIN плане видно использование временной таблицы и вложенных циклов, что часто является признаком неоптимального выбора плана выполнения.

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

Операционная система также играет важную роль в производительности MySQL. Несмотря на ожидания, что MacBook M2 будет работать быстрее, исследования показывают, что производительность может значительно варьироваться между ОС.

Согласно Database Administrators Stack Exchange, в одном случае производительность Mac была превосходнее более чем в 50 раз по сравнению с Linux. Однако это не всегда правило - производительность MySQL на macOS может быть ниже из-за:

1. Различий в файловых системах (APFS vs ext4)
2. Разной реализации многопоточности и синхронизации
3. Разных настроек буферизации и кэширования на уровне ОС
4. Отсутствия оптимизаций, специфичных для Linux

Как упоминается в документации MySQL, поведение выделения пространства в InnoDB на Linux может отличаться от macOS, что влияет на производительность операций с таблицами.

Проблемы с временными таблицами и вложенными циклами

Из вашего EXPLAIN плана видно несколько потенциальных проблем:

-> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Nested loop inner join  (cost=1768 rows=5498)

Использование временных таблиц и вложенных циклов является частой причиной снижения производительности. Как показывают исследования, существуют известные проблемы с алгоритмом Block Nested-Loops Join:

1. Баг #115679 на bugs.mysql.com: “Block Nested-Loops Join can sometimes slow down the query”
2. Баг #69721: “Block Nested Loop making things slower”
3. Проблемы с использованием Using join buffer (Block Nested Loop)

Как объясняется в документации MySQL 8.0, Block Nested-Loop использует буферизацию строк из внешних циклов, чтобы уменьшить количество чтений внутренних таблиц, но это может быть неэффективным для определенных типов запросов.

В вашем случае:

• Запрос использует GROUP BY pc2.category_id, что требует агрегации
• Есть множественные соединения с фильтрацией по IN списку
• Оптимизатор выбирает создание временной таблицы для агрегации

Настройка MySQL my.cnf для MacBook M2

Отсутствие файла my.cnf является серьезной проблемой. Без правильной конфигурации MySQL использует стандартные настройки, которые могут быть неоптимальными для MacBook M2.

Ключевые параметры для настройки:

[mysqld]
# Размер буфера соединений
join_buffer_size = 256M

# Размер буфера для сортировки
sort_buffer_size = 256M

# Размер буфера для чтения ключей
read_buffer_size = 64M
read_rnd_buffer_size = 256M

# Настройки InnoDB
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2

# Отключение проверки внешних ключей для ускорения
foreign_key_checks = 0

# Оптимизация для временных таблиц
tmp_table_size = 512M
max_heap_table_size = 512M

Как упоминается в Stack Overflow, увеличение join_buffer_size может значительно ускорить запросы с вложенными циклами.

Оптимизация конкретного запроса

Ваш запрос можно оптимизировать несколькими способами:

1. Изменение порядка таблиц в запросе

SELECT pc2.category_id as id, COUNT(pc2.product_id) as total 
FROM oc_product p
INNER JOIN oc_product_to_category pc ON p.product_id = pc.product_id 
INNER JOIN oc_product_to_category pc2 ON pc.product_id = pc2.product_id 
WHERE p.status = 1 
AND pc.category_id = 907 
AND pc2.category_id IN (866,879,880,881,882,883,886,889,890,891,892,893,896,378,385,297,115,119,118,149,148,154,121,163,122,83,171,71,45,175,81,298,59,26,62,921)
GROUP BY pc2.category_id;

2. Использование подзапроса для предварительной фильтрации

SELECT pc2.category_id as id, COUNT(pc2.product_id) as total 
FROM (
    SELECT product_id 
    FROM oc_product_to_category 
    WHERE category_id = 907
) pc
JOIN oc_product_to_category pc2 ON pc.product_id = pc2.product_id 
JOIN oc_product p ON pc.product_id = p.product_id 
WHERE pc2.category_id IN (866,879,880,881,882,883,886,889,890,891,892,893,896,378,385,297,115,119,118,149,148,154,121,163,122,83,171,71,45,175,81,298,59,26,62,921) 
AND p.status = 1 
GROUP BY pc2.category_id;

3. Создание составного индекса

ALTER TABLE oc_product_to_category ADD INDEX idx_product_category (product_id, category_id);

4. Замена IN на JOIN с временной таблицей

SELECT pc2.category_id as id, COUNT(pc2.product_id) as total 
FROM oc_product_to_category pc 
JOIN oc_product_to_category pc2 ON pc.product_id = pc2.product_id 
JOIN oc_product p ON pc.product_id = p.product_id 
JOIN (
    SELECT 866 AS category_id UNION SELECT 879 UNION SELECT 880 UNION SELECT 881
    UNION SELECT 882 UNION SELECT 883 UNION SELECT 886 UNION SELECT 889
    UNION SELECT 890 UNION SELECT 891 UNION SELECT 892 UNION SELECT 893
    UNION SELECT 896 UNION SELECT 378 UNION SELECT 385 UNION SELECT 297
    UNION SELECT 115 UNION SELECT 119 UNION SELECT 118 UNION SELECT 149
    UNION SELECT 148 UNION SELECT 154 UNION SELECT 121 UNION SELECT 163
    UNION SELECT 122 UNION SELECT 83 UNION SELECT 171 UNION SELECT 71
    UNION SELECT 45 UNION SELECT 175 UNION SELECT 81 UNION SELECT 298
    UNION SELECT 59 UNION SELECT 26 UNION SELECT 62 UNION SELECT 921
) target_categories ON pc2.category_id = target_categories.category_id
WHERE pc.category_id = 907 
AND p.status = 1 
GROUP BY pc2.category_id;

Практические рекомендации

1. Создайте файл my.cnf для MacBook M2 с оптимальными настройками, особенно увеличьте join_buffer_size, sort_buffer_size и innodb_buffer_pool_size.

2. Проверьте версии MySQL - возможно, downgrade с 9.5 до 8.0 или обновление до последней версии 9.x улучшит производительность.

3. Анализируйте планы выполнения - сравнивайте EXPLAIN планы на разных средах, чтобы понять, почему оптимизатор выбирает разные стратегии.

4. Оптимизируйте индексы - убедитесь, что у вас есть составные индексы для часто используемых полей соединений.

5. Изучите системные переменные - выполните SHOW VARIABLES LIKE '%buffer%'; и SHOW VARIABLES LIKE '%join%'; для текущих настроек.

6. Рассмотрите возможность виртуализации - иногда запуск MySQL в Docker или виртуальной машине на MacBook может дать более предсказуемую производительность.

7. Мониторьте ресурсы - проверяйте использование CPU, RAM и диска на обеих системах во время выполнения запросов.

Источники

1. Database Administrators Stack Exchange - MySQL performance on Mac vs Linux
2. Rajat Jain - MySQL 8 Vs 9 Performance Benchmark
3. Percona Blog - MySQL 8.4.3 and 9.1.0: Major Performance Gains Revealed
4. Stack Overflow - MySQL performance 5.7 vs 8.0
5. MySQL 8.0 Reference Manual - Nested-Loop Join Algorithms
6. MySQL Bugs - Block Nested-Loops Join can sometimes slow down the query
7. Stack Overflow - How can I improve query performances using Using temporary; Using filesort
8. MySQL 8.0 Reference Manual - What Is New in MySQL 8.0

Заключение

Запрос выполняется медленнее на MacBook M2 с MySQL 9.5 из-за комбинации факторов: различий в оптимизаторе версий MySQL, особенностей работы macOS по сравнению с Linux, отсутствия правильной конфигурации MySQL и потенциальных проблем с алгоритмами соединений.

Для улучшения производительности рекомендуется:

• Настроить my.cnf с увеличенными буферами
• Оптимизировать запрос и индексы
• Рассмотреть возможность изменения версии MySQL
• Сравнить планы выполнения и системные настройки между средами

Понимание того, как оптимизатор MySQL выбирает планы выполнения, и правильная настройка параметров могут значительно улучшить производительность запросов на MacBook M2.