Подтвердить что ты не робот

MySQL не отвечает на простые запросы при использовании INNER JOIN. особенно "Отправка данных" занимает слишком много времени

У меня есть две таблицы; songs с 48 959 281 строками и popular_songs с 5 721 117 строками.

Это структура этих двух таблиц:

CREATE TABLE songs (
  songId       BIGINT(20)    NOT NULL,
  songName     VARCHAR(1000) NOT NULL,
  songDuration BIGINT(20)    NOT NULL,
  songPreview  VARCHAR(1000) NOT NULL,
  PRIMARY KEY (songId),
  INDEX (songDuration)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  DEFAULT COLLATE utf8_general_ci;

CREATE TABLE popular_songs (
  storeFrontId BIGINT(20) NOT NULL,
  genreId      BIGINT(20) NOT NULL,
  songId       BIGINT(20) NOT NULL,
  songRank     INT(11)    NOT NULL,
  INDEX (storeFrontId),
  INDEX (genreId),
  INDEX (songId),
  INDEX (songRank),
  CONSTRAINT popular_song UNIQUE (storeFrontId, genreId, songId),
  FOREIGN KEY (storeFrontId) REFERENCES storefront (storeFrontId),
  FOREIGN KEY (genreId) REFERENCES genre (genreId),
  FOREIGN KEY (songId) REFERENCES songs (songId)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  DEFAULT COLLATE utf8_general_ci;

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

mysql> SELECT count(*) FROM songs;
+----------+
| count(*) |
+----------+
| 48959281 |
+----------+
1 row in set (9.10 sec)

mysql> SHOW PROFILE;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000008 |
| Waiting for query cache lock   | 0.000002 |
| checking query cache for query | 0.000019 |
| checking permissions           | 0.000003 |
| Opening tables                 | 0.000011 |
| System lock                    | 0.000004 |
| Waiting for query cache lock   | 0.000017 |
| init                           | 0.000006 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000004 |
| preparing                      | 0.000004 |
| executing                      | 0.000003 |
| Sending data                   | 9.100444 |
| end                            | 0.000012 |
| query end                      | 0.000005 |
| closing tables                 | 0.000008 |
| freeing items                  | 0.000007 |
| Waiting for query cache lock   | 0.000002 |
| freeing items                  | 0.000012 |
| Waiting for query cache lock   | 0.000002 |
| freeing items                  | 0.000001 |
| storing result in query cache  | 0.000002 |
| logging slow query             | 0.000001 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
24 rows in set (0.00 sec)

mysql> SELECT count(*) FROM popular_songs;
+----------+
| count(*) |
+----------+
|  5721117 |
+----------+
1 row in set (1.34 sec)

mysql> SHOW PROFILE;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000012 |
| Waiting for query cache lock   | 0.000004 |
| checking query cache for query | 0.000037 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000017 |
| System lock                    | 0.000007 |
| Waiting for query cache lock   | 0.000031 |
| init                           | 0.000010 |
| optimizing                     | 0.000017 |
| statistics                     | 0.000004 |
| preparing                      | 0.000004 |
| executing                      | 0.000003 |
| Sending data                   | 1.343991 |
| end                            | 0.000010 |
| query end                      | 0.000005 |
| closing tables                 | 0.000007 |
| freeing items                  | 0.000007 |
| Waiting for query cache lock   | 0.000002 |
| freeing items                  | 0.000011 |
| Waiting for query cache lock   | 0.000001 |
| freeing items                  | 0.000001 |
| storing result in query cache  | 0.000002 |
| logging slow query             | 0.000001 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
24 rows in set (0.00 sec)

Когда я запускаю следующие ниже запросы, MySQL не отвечает. Я не понимаю, в чем причина.

SELECT COUNT(*)
FROM songs
INNER JOIN popular_songs ON popular_songs.songId = songs.songId

SELECT COUNT(*)
FROM songs
INNER JOIN popular_songs ON popular_songs.songId = songs.songId
WHERE songs.songDuration > 0

Update:

MySQL ответил двумя предыдущими запросами. Но это займет 250-300 секунд на Sending data. Как оптимизировать это.

EXPLAIN EXTENDED report:

mysql> EXPLAIN EXTENDED
    -> SELECT COUNT(*)
    -> FROM songs
    -> INNER JOIN popular_songs ON popular_songs.songId = songs.songId;
+----+-------------+---------------+--------+---------------+----------+---------+-----------------------------+---------+----------+-------------+
| id | select_type | table         | type   | possible_keys | key      | key_len | ref                         | rows    | filtered | Extra       |
+----+-------------+---------------+--------+---------------+----------+---------+-----------------------------+---------+----------+-------------+
|  1 | SIMPLE      | popular_songs | index  | songId        | songRank | 4       | NULL                        | 6449163 |   100.00 | Using index |
|  1 | SIMPLE      | songs         | eq_ref | PRIMARY       | PRIMARY  | 8       | itunes.popular_songs.songId |       1 |   100.00 | Using index |
+----+-------------+---------------+--------+---------------+----------+---------+-----------------------------+---------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

mysql> EXPLAIN EXTENDED
    -> SELECT COUNT(*)
    -> FROM songs
    -> INNER JOIN popular_songs ON popular_songs.songId = songs.songId
    -> WHERE songs.songDuration > 0;
+----+-------------+---------------+--------+----------------------+----------+---------+-----------------------------+---------+----------+-------------+
| id | select_type | table         | type   | possible_keys        | key      | key_len | ref                         | rows    | filtered | Extra       |
+----+-------------+---------------+--------+----------------------+----------+---------+-----------------------------+---------+----------+-------------+
|  1 | SIMPLE      | popular_songs | index  | songId               | songRank | 4       | NULL                        | 6449163 |   100.00 | Using index |
|  1 | SIMPLE      | songs         | eq_ref | PRIMARY,songDuration | PRIMARY  | 8       | itunes.popular_songs.songId |       1 |   100.00 | Using where |
+----+-------------+---------------+--------+----------------------+----------+---------+-----------------------------+---------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

При использовании INDEX songId для объединения двух таблиц:

mysql> SELECT COUNT(*) FROM songs INNER JOIN popular_songs USE INDEX FOR JOIN (songId) ON popular_songs.songId = songs.songId;
+----------+
| COUNT(*) |
+----------+
|  5721117 |
+----------+
1 row in set (25.35 sec)

mysql> SHOW PROFILE;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000011 |
| Waiting for query cache lock   |  0.000004 |
| checking query cache for query |  0.000045 |
| checking permissions           |  0.000004 |
| checking permissions           |  0.000004 |
| Opening tables                 |  0.000023 |
| System lock                    |  0.000007 |
| Waiting for query cache lock   |  0.000030 |
| init                           |  0.000021 |
| optimizing                     |  0.000011 |
| statistics                     |  0.000020 |
| preparing                      |  0.000012 |
| executing                      |  0.000005 |
| Sending data                   | 25.350160 |
| end                            |  0.000019 |
| query end                      |  0.000005 |
| closing tables                 |  0.000024 |
| freeing items                  |  0.000022 |
| Waiting for query cache lock   |  0.000002 |
| freeing items                  |  0.000016 |
| Waiting for query cache lock   |  0.000001 |
| freeing items                  |  0.000001 |
| storing result in query cache  |  0.000002 |
| logging slow query             |  0.000001 |
| logging slow query             |  0.000005 |
| cleaning up                    |  0.000003 |
+--------------------------------+-----------+
26 rows in set (0.00 sec)

Это довольно хорошо. Но все-таки это займет 25 секунд на Sending data.

Объяснение расширено для запроса:

mysql> EXPLAIN EXTENDED SELECT COUNT(*) FROM songs INNER JOIN popular_songs USE INDEX FOR JOIN (songId) ON popular_songs.songId = songs.songId;
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+---------+----------+-------------+
| id | select_type | table         | type   | possible_keys | key     | key_len | ref                         | rows    | filtered | Extra       |
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+---------+----------+-------------+
|  1 | SIMPLE      | popular_songs | index  | songId        | songId  | 8       | NULL                        | 2684407 |   100.00 | Using index |
|  1 | SIMPLE      | songs         | eq_ref | PRIMARY       | PRIMARY | 8       | itunes.popular_songs.songId |       1 |   100.00 | Using index |
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+---------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
4b9b3361

ОТВЕТЫ

Ответ 1

Я предполагаю, что индекс на songDuration путает оптимизатор. Вы можете попробовать сформулировать запрос следующим образом:

SELECT COUNT(*)
FROM songs s
WHERE EXISTS (SELECT 1
              FROM popular_songs ps
              WHERE ps.songId = s.songId
             ) AND
      s.songDuration > 0;

Индекс на songDuration был бы лучше включить songId: songs(songDuration, songId).

Ответ 2

Он выбрал songRank, потому что это был наименьший индекс (INT, а не BIGINT).

"Отправка данных" - одна из двух бесполезных вещей, которые исходят от этого инструмента. Поскольку он редко предоставляет что-либо полезное, я его не использую.

Часто время запутывает из-за кеширования. Кажется, что кеш запросов отключен (хорошо). Если данные (или индекс) в настоящее время не кэшируются в ОЗУ (InnoDB buffer_pool), тайминги накачиваются из-за ввода-вывода. Время, когда вы выглядите так, будто они связаны с процессором. Запуск запроса дважды исключает вопрос ввода/вывода.

Существует несколько способов уменьшить размер таблиц; это приводит к тому, что они работают быстрее.

popular_songs не имеет явного PRIMARY KEY, поэтому был изобретен 6-байтовый ПК. Поскольку у вас CONSTRAINT popular_song UNIQUE (storeFrontId, genreId, songId), это "естественный" ПК. Избавьтесь от ограничения и добавьте это как PK. (Нам может понадобиться изменить порядок столбцов, мы увидим.)

Используйте INT UNSIGNED, а не BIGINT для всех идентификаторов; который сокращает размер пополам.

songRank позволяет от 2 до 2 миллиардов и занимает 4 байта. Вероятно, вы можете найти меньшее поле и можете использовать UNSIGNED. Например, SMALLINT UNSIGNED составляет 2 байта и допускает значения 0..65535.

Как уже упоминалось, songDuration использует слишком большое поле (если оно не измерено в наносекундах).

INDEX (storeFrontId) избыточен с индексом UNIQUE, поэтому его можно удалить.

Но реальная проблема в том, что таблицы большие, а JOINs стоит что-то. Я не думаю, что вы можете заставить запросы работать намного быстрее, чем то, что вы видите.

Это приводит к другому вопросу - почему эти вопросы имеют значение? Они звучат как одноразовые запросы, а не то, что пользователь, сидящий в пользовательском интерфейсе, ждет каждый день. При работе с длинными запросами на большие таблицы часто лучше переосмыслить требования.

Если запросы неоднократно выполняются, их нельзя запускать один раз в день и "кэшировать"? Я подозреваю, что таблицы не растут более чем на 1% в день, подразумевая, что результирующие наборы будут корректными примерно до 1%; не так ли "достаточно близко"?

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

Ответ 3

Обратите внимание, что select count (*) из таблицы фактически не читает таблицу вообще. Он выбирает любой доступный уникальный индекс (например, первичный ключ) и возвращает количество строк в этом индексе (что фактически равно числу строк таблицы).

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

  • public_songs.songid также должен быть первичным ключом

Это помогает в SELECT COUNT (*) ОТ песен INNER JOIN popular_songs ON popular_songs.songId = songs.songId

  • song.songDuration должен иметь не уникальный индекс, иначе вы прочтете все 48 миллионов записей, чтобы просто оценить критерии. (это у вас уже есть)

Это помогает в SELECT COUNT (*) ОТ песен INNER JOIN popular_songs ON popular_songs.songId = songs.songId WHERE songs.songDuration > 0

  • избегать, когда критерии с низкой мощностью

songDuration - это в основном некоторое число, для очень немногих песен оно равно нулю. Поэтому использование этого параметра в качестве критерия не будет использовать индекс (вам нужно было заставить сам индекс). Лучше идея иметь логическое поле, указывающее, имеет ли песня нулевую длительность (нулевая длина - допустимый прецедент?) Я предполагаю, что нет? SongDuration может быть полем с нулевым значением и содержать null, если duratiuon неизвестно.) Но если вы переходите на логическое поле или в поле 0/1, вы можете использовать растровые индексы, если ваша база данных поддерживает это.

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

  • предпочитает уникальные индексы по неидеальным индексам
  • Если вы присоединитесь к двум таблицам, столбец в обеих таблицах должен иметь индекс. Если вы можете иметь уникальные индексы, которые лучше, если столбец на самом деле является первичный ключ - лучший.
  • все столбцы в , где критерии должны быть индексированы. В противном случае, чтобы оценить критерии, все строки должны быть прочитаны с диска.
  • используйте составные индексы, если это возможно. Например. если вы хотите ускорить запрос, содержащий , где songId = xxx и genre = yyy, затем создайте один индекс, который содержит как жанр songid AND, так и индексирование столбцов отдельно. Этот конкретный индекс может быть уникальным индексом, поскольку songId уникален - быстрее считывать данные из уникальных индексов
  • индексирование столбцов заранее, а индексирование отдельных столбцов обычно не имеет смысла, это преждевременная оптимизация. Индексы предназначены для ускорения ваших запросов, и у вас есть запросы, когда у вас есть приложение. Затем вы можете увидеть, какой запрос медленный, и ускорить его, используя приведенные выше методы.
  • избегать добавления всех возможных индексов только "потому что в какой-то момент это может быть хорошо". Индексы делают выбор быстро, но замедляют обновление, вставку и удаление операторов.

Ответ 4

Это зависит от ваших критериев поиска. Если вы хотите выполнить поиск по song.songDuration, попробуйте создать составной индекс:

ALTER TABLE song ADD INDEX songDuration (songId, songDuration);

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

Также вы можете попытаться установить уровень изоляции READ UNCOMMITED, если вы думаете, что ваш результирующий набор может жить с этим. Это ускорит ваш запрос!

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

ИЗМЕНИТЬ

Я пропустил, что вы храните огромный массив VARCHAR внутри таблицы. В настоящее время я не работаю над MySQL, но я помню, что MySQL хранит весь VARCHAR внутри таблицы независимо от того, какой размер он имеет. Вы можете улучшить производительность, если вы измените тип songPreview на TEXT, например.

Поля TEXT, BLOB и CBLOB хранятся вне таблицы, поэтому объединение и поиск по другим полям должны быть лучше.

Если вы действительно думаете, что есть много песен с названием около 1000 символов, вы также можете создать тип songName TEXT и использовать FULLTEXT index для поиска. Если названия ваших песен не очень большие, вы можете использовать CHAR, чтобы сохранить фиксированный размер строки и улучшить производительность таблицы.

Вы можете узнать больше о том, почему использовать TEXT против большого VARCHAR здесь Высокая производительность MySQL

О FULLTEXT index search - я использовал его в реальном проекте над полем ТЕКСТ, поэтому я мог подтвердить его работает лучше, чем VARCHAR с LIKE.

Ваши таблицы действительно огромны, поэтому, если вы хотите изменить типы из VARCHAR (1000) в TEXT, не изменяйте таблицу. Он будет работать лучше, если вы создадите новые таблицы и скопируете данные в новые таблицы. Изменение старого будет длиться вечно. Вы можете ускорить процесс, если вы не создаете индексы и ограничения в определении таблицы, а после копирования старых данных внутри таблицы.

Ответ 5

popular_songs таблица не имеет PRIMARY KEY. Я предлагаю изменить CONSTRAINT popular_song UNIQUE (storeFrontId, genreId, songId) на PRIMARY KEY (songId, genreId, storeFrontId). Также обратите внимание на изменение порядка полей.

Оптимизатор должен позаботиться об этом, но я все равно поставил бы меньшую таблицу в начале Join, как предложил Милан Томеш:

SELECT COUNT(*)
FROM popular_songs
INNER JOIN songs ON songs.songId = popular_songs.songId
                AND songs.songDuration > 0

Кроме того, я не знаю, какова цель, но для меня это попрошайка для Count Distinct:

SELECT COUNT(DISTINCT popular_songs.songId)
FROM popular_songs
INNER JOIN songs ON songs.songId = popular_songs.songId
                AND songs.songDuration > 0

Ответ 6

count(*) выполняется быстрее, если в запросе нет предложений join и where.

COUNT (*) оптимизирован для возврата очень быстро, если SELECT получает из одной таблицы никакие другие столбцы не извлекаются, и нет ГДЕ пункт.

Источник.

Поскольку у вас нет возможности избавиться от join и where от вашего запроса, ответ Gordon Linoff кажется хорошей оптимизацией. Однако, если количество строк практически бесконечно, то даже с этим запросом вам придется много ждать, особенно из-за того, что в выборке и подзапросе есть предложение where.

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

Ответ 7

Поскольку у нас есть внешний ключ между songs и popular_songs, зачем нам нужно внутреннее соединение и подсчет?, он переполняет, если вы хотите подсчитать количество уникальных популярных песен, этого запроса достаточно:

SELECT COUNT(distinct(songId))
FROM popular_songs

С более продолжительным условием

SELECT count(*)
FROM (SELECT distinct(songId) as songId FROM popular_songs) as t
    JOIN songs on t.songId = songs.songId
WHERE songs.songDuration > 0