Nano Hash - криптовалюты, майнинг, программирование

Производительность MySQL, внутреннее соединение, как избежать использования временных файлов и файловой сортировки

У меня есть таблица 1 и таблица 2.

Таблица 1 PARTNUM - ID_BRAND partnum - это первичный ключ id_brand "индексируется"

Таблица 2 ID_BRAND - BRAND_NAME id_brand - это первичный ключ brand_name "проиндексировано"

Таблица 1 содержит 1 миллион записей, а таблица 2 - 1.000 записей.

Я пытаюсь оптимизировать какой-то запрос с помощью EXPLAIN, и после многих попыток я зашел в тупик.

EXPLAIN 
SELECT pm.partnum, pb.brand_name
FROM products_main AS pm 
LEFT JOIN products_brands AS pb ON pm.id_brand=pb.id_brand
ORDER BY pb.brand ASC 
LIMIT 0, 10

Запрос возвращает этот план выполнения:

ID, SELECT_TYPE, TABLE, TYPE, POSSIBLE_KEYS, KEY, KEY_LEN , REF, ROWS, EXTRA
1, SIMPLE, pm, range, PRIMARY, PRIMARY, 1, , 1000000, Using where; Using temporary; Using filesort
1, SIMPLE, pb, ref, PRIMARY, PRIMARY, 4, demo.pm.id_pbrand, 1,

Оптимизатор запросов MySQL показывает временную сортировку файлов в плане выполнения. Как мне этого избежать?

«ЗЛО» находится в ORDER BY pb.brand ASC. Упорядочивание по внешнему полю кажется узким местом.

mysql performance join inner-join
26.07.2010

  • Какие индексы у вас есть в этих таблицах? 26.07.2010
  • Предположительно, если у вас нет индекса на pb.brand, этот mysql должен будет отсортировать все 1M строк перед применением ограничения. 26.07.2010
  • ТАБЛИЦА 1: PARTNUM - это PK, а ID_BRAND - это индекс для ускорения. ТАБЛИЦА 2: ID_BRAND - это PK, а BRAND - это индекс для ускорения. 26.07.2010

Ответы:


1

Прежде всего, я сомневаюсь в использовании внешнего соединения, так как order by работает с правой стороны, и NULL, введенные левым соединением, вероятно, нанесут ему ущерб.

Тем не менее, самый простой способ ускорить этот запрос - это индекс покрытия для pb.id_brand и pb.brand. Это позволит оценивать порядок «с использованием индекса» с условием соединения. Альтернативный вариант - найти способ уменьшить размер промежуточного результата, передаваемого в функцию order-by.

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

28.07.2010

2

Попробуйте заменить соединение подзапросом. Оптимизатор MySQL - отстой; подзапросы часто дают лучшую производительность, чем соединения.

28.07.2010
  • У вас есть источник этого ...? 29.06.2011
  • Я тестировал аналогичный запрос, и он прав, это имело огромное значение. Перемещение ORDER BY x DESC LIMIT 20 в подзапрос удалило временное использование, и время запроса увеличилось с более чем 5 секунд до 0,0017 секунды. 03.05.2012
  • @ColinM как переместить ORDER BY x DESC LIMIT 20 в подзапрос? Может быть, вы можете опубликовать пример запроса в качестве дополнительного ответа на этот вопрос? 05.05.2013
  • Вы не можете сказать, что подзапросы лучше. Я обнаружил противоположность: они хуже. Итак, я хотел бы увидеть некоторые доказательства вашего тезиса ... 04.10.2013

    • 3

    Сначала попробуйте изменить индекс в таблице products_brands. Удалите существующий на brand_name и создайте новый:

    ALTER TABLE products_brands ADD INDEX newIdx (brand_name, id_brand)

    Тогда таблица уже будет иметь индекс «ordersByBrandName» с идентификаторами, необходимыми для соединения, и вы можете попробовать:

    EXPLAIN
SELECT pb.brand_name, pm.partnum
FROM products_brands AS pb 
  LEFT JOIN products_main AS pm ON pb.id_brand = pm.id_brand
LIMIT 0, 10

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

    26.07.2010
  • У меня два индекса на PRODUCTS_BRANDS. Первый находится на id_brand (это первичный ключ с неявным индексом). Второй по brand_name. 26.07.2010
  • Я изменил порядок JOIN TABLES, но ничего не изменилось. Оптимизатор запросов выбирает порядок таблицы и не соответствует порядку синтаксиса SQL. 26.07.2010
  • Вы меняли индекс? Я прочитал в вашем вопросе, что у вас есть два индекса: вам нужно изменить один на brand_name и сделать его на (brand_name, id_brand). 26.07.2010
  • Я добавил новый индекс, как вы писали. Новый индекс (1 индекс, содержащий 2 столбца) используется оптимизатором. Я вижу в выводе, что теперь он используется. Но «Использование временного; Использование файловой сортировки никуда не делось. Время исполнения остается прежним. 26.01.2011

    • 4

    Этот вопрос несколько устарел, но я нашел его, как и другие люди.

    Mysql использует временный, если ORDER BY или GROUP BY содержит столбцы из таблиц, отличных от первой таблицы в очереди присоединения.

    Таким образом, вам просто нужно изменить порядок соединения с помощью STRAIGHT_JOIN, чтобы обойти порядок, изобретенный оптимизатором:

    SELECT STRAIGHT_JOIN pm.partnum, pb.brand_name
FROM products_brands AS pb 
RIGHT JOIN products_main AS pm ON pm.id_brand=pb.id_brand
ORDER BY pb.brand ASC 
LIMIT 0, 10

    Также убедитесь, что переменные max_heap_table_size И tmp_table_size установлены на число, достаточно большое для хранения результатов:

    SET global tmp_table_size=100000000;
SET global max_heap_table_size=100000000;

    - в этом примере 100 мегабайт. Их также можно установить в конфигурационном файле my.cnf.

    08.08.2012
    Новые материалы
    Кластеризация: более глубокий взгляд
    Кластеризация — это метод обучения без учителя, в котором мы пытаемся найти группы в наборе данных на основе некоторых известных или неизвестных свойств, которые могут существовать. Независимо от..
    Как написать эффективное резюме
    Предложения по дизайну и макету, чтобы представить себя профессионально Вам не позвонили на собеседование после того, как вы несколько раз подали заявку на работу своей мечты? У вас может..
    Частный метод Python: улучшение инкапсуляции и безопасности
    Введение Python — универсальный и мощный язык программирования, известный своей простотой и удобством использования. Одной из ключевых особенностей, отличающих Python от других языков, является..
    Как я автоматизирую тестирование с помощью Jest
    Шутка для победы, когда дело касается автоматизации тестирования Одной очень важной частью разработки программного обеспечения является автоматизация тестирования, поскольку она создает..
    Работа с векторными символическими архитектурами, часть 4 (искусственный интеллект)
    Hyperseed: неконтролируемое обучение с векторными символическими архитектурами (arXiv) Автор: Евгений Осипов , Сачин Кахавала , Диланта Хапутантри , Тимал Кемпития , Дасвин Де Сильва ,..
    Понимание расстояния Вассерштейна: мощная метрика в машинном обучении
    В обширной области машинного обучения часто возникает необходимость сравнивать и измерять различия между распределениями вероятностей. Традиционные метрики расстояния, такие как евклидово..
    Обеспечение масштабируемости LLM: облачный анализ с помощью AWS Fargate и Copilot
    В динамичной области искусственного интеллекта все большее распространение получают модели больших языков (LLM). Они жизненно важны для различных приложений, таких как интеллектуальные..