Блог

Главная - Вопросы по программированию - MySQL: низкая производительность соединения маленькой таблицы с большой

MySQL: низкая производительность соединения маленькой таблицы с большой

#mysql #performance #join #inner-join

#mysql #Производительность #Присоединиться #внутреннее объединение

Вопрос:

У меня есть следующие таблицы:

 CREATE TABLE smalltable (
    smalltable_id VARCHAR(64) NOT NULL,
    bigtable_id VARCHAR(64),
    ...
    PRIMARY KEY (smalltable_id)
) ENGINE=InnoDB;

CREATE TABLE bigtable (
    bigtable_id VARCHAR(64) NOT NULL,
    count BIGINT,
    PRIMARY KEY (bigtable_id)
) ENGINE=InnoDB;

smalltable имеет около 8000 строк и bigtable около 40 миллионов. Я хотел бы получить эти строки из smalltable того smalltable.bigtable_id места, где они присутствуют в bigtable . Выполнение следующего запроса заняло почти 10 часов:

 SELECT * FROM smalltable
INNER JOIN bigtable
ON smalltable.bigtable_id = bigtable.bigtable_id;

Вот результат EXPLAIN :

 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: smalltable
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8610
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: bigtable
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 38818260
     filtered: 100.00
        Extra: Using where; Using join buffer (Block Nested Loop)

Я не эксперт в интерпретации этого, но похоже, что MySQL выполняет последовательное сканирование обеих таблиц. Если я напишу небольшой скрипт на Python, который перебирает все строки в smalltable и выполняет SELECT запрос на bigtable для каждой строки в smalltable , все это завершится за 25 секунд. Я хотел бы иметь такую же производительность с одним SQL-запросом.

Комментарии:

1. Добавьте индекс в smalltable (bigtable_id)

2. Спасибо. Это действительно значительно ускоряет процесс. Запрос теперь занимает 1 минуту 30 секунд. Тем не менее, все еще медленнее, чем ручная итерация по smalltable и запуск запросов select к bigtable. Кроме того, было бы неплохо иметь решение, которое не требует изменения smalltable. В реальном варианте использования данные в smalltable являются подмножеством другой большой таблицы, и у меня нет прав на запись для этой таблицы.

3. Индексы — это то, что нужно. Альтернатива — делать то, что вы уже делаете.

4. Проверьте значение COLLATION bigtable_id в обеих таблицах — оно должно быть одинаковым.

5. Что такое настройка join_buffer_size ? Какая версия MySQL?

Ответ №1:

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

 SELECT STRAIGHT_JOIN * FROM smalltable
INNER JOIN bigtable
ON smalltable.bigtable_id = bigtable.bigtable_id;

Использование STRAIGHT_JOIN подскажет mysql следовать порядку таблиц в вашем запросе.

Ответ №2:

Если вам нужна только информация из smalltable (что подразумевает ваше описание),

 SELECT *
    FROM smalltable AS s
    WHERE EXISTS (
        SELECT *
            FROM bigtable
            WHERE bigtable_id = s.bigtable_id );