MySQL Создать таблицу как SELECT

Каждый раз, когда я использую MySQL CREATE TABLE AS SELECT ..., все выбранные таблицы/индексы блокируются в течение продолжительности запроса. Я действительно не понимаю, почему? Есть ли способ обойти это?

Использование: MySQL 5.1.41 и InnoDB

Добавленный пример:

Например, следующий запрос может занять до 10 минут:

CREATE TABLE temp_lots_of_data_xxx AS 
SELECT
    a.*
    b.*
    c.*
FROM a
LEFT JOIN b ON a.foo = b.foo
LEFT JOIN c ON a.foo = c.foo

Попытка обновить значения в таблицах a, b или c во время вышеуказанного запроса будет ждать завершения первого запроса. Я хочу избежать этой блокировки, так как меня не интересуют наиболее полные данные в созданной таблице temp.

p.s. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; не приводит к изменению поведения.

Ответы

Ответ 1

См. также http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/

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

Или можете выгрузить данные в файл, а затем перезагрузить данные из файла. Это также позволяет избежать блокировок.

Ответ 2

Вы пытались выполнить операцию в 2 этапа (сначала создайте таблицу, затем вставьте значения) и установив самый низкий уровень изоляции?:

CREATE TABLE temp_lots_of_data_xxx AS 
    SELECT
        a.*
        b.*
        c.*
    FROM a
        LEFT JOIN b ON a.foo = b.foo
        LEFT JOIN c ON a.foo = c.foo
    WHERE FALSE

INSERT INTO temp_lots_of_data_xxx
    SELECT
        a.*
        b.*
        c.*
    FROM a
        LEFT JOIN b ON a.foo = b.foo
        LEFT JOIN c ON a.foo = c.foo

Ответ 3

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

В общем, я не был бы слишком удивлен, если запрос CREATE TABLE блокирует все таблицы и индексы, из которых он читается.

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

(Пожалуйста, поправьте меня, если я сделал какие-либо неправильные предположения.)

Ответ 4

Все блокировки InnoDB, удерживаемые транзакцией, освобождаются, когда транзакция совершена или отменена. Таким образом, это не имеет большого смысла вызывать LOCK TABLES на таблицах InnoDB в режиме autocommit = 1, потому что приобретенные блокировки InnoDB будут немедленно выпущены.

Как прочитано здесь

EDIT, и это:

Вы не можете заблокировать дополнительные таблицы в середине транзакции потому что LOCK TABLES выполняет неявные COMMIT и UNLOCK TABLES.

Ответ 5

Я не тестировал это, но вы можете попробовать с

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
CREATE TABLE ...
COMMIT ; /*See comment by Somnath Muluk*/

Но имейте в виду:

Операторы выбора выполняются в режиме блокировки, но возможно использование более ранней версии строки. Таким образом, используя уровень изоляции, такие чтения несовместимы. Это также называется "грязное чтение".

Подробнее об этом читайте здесь:

Ручная запись MySQL SET TRANSACTION

EDIT: добавлен COMMIT ;

Ответ 6

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

Чтобы обойти эту проблему, вы могли бы SET LOW_PRIORITY_UPDATES=1, а затем вы могли бы запустить свою команду. Но это не полностью соответствует вашему делу. Таким образом, вы также можете указать более высокий приоритет в выражении SELECT. Чтобы присвоить конкретный оператор SELECT более высокий приоритет, используйте атрибут HIGH_PRIORITY.

CREATE TABLE temp_lots_of_data_xxx AS 
SELECT HIGH_PRIORITY
    a.*
    b.*
    c.*
FROM a
LEFT JOIN b ON a.foo = b.foo
LEFT JOIN c ON a.foo = c.foo

Подробнее см. на этой странице table-locking-issues на этой странице select-syntax, а также эту страницу: option_mysqld_low-priority-updates

Ответ 7

Я не так опытен в mysql, но столкнулся с той же проблемой в mssql. Решение заключалось в том, чтобы запустить "create table as select..." с нулевыми строками, чтобы он только создавал таблицу с соответствующей структурой и немедленно освобождал блокировки. Затем используйте "insert into" с тем же предложением select, чтобы заполнить таблицу, которая не будет содержать блокировки схемы во время ее запуска.