Mam około 40 milionów wierszy w tabeli MySQL i chcę skopiować tę tabelę do innej tabeli w tej samej bazie danych. Jaki jest najbardziej efektywny sposób to zrobić? Ile to zajmie (około)?

Załóżmy, że masz mydb.mytb i chcesz tworzyćmydb.mytbcopy

Mam pięć (5) podejść do robienia tej kopii

PODEJŚCIE 1

W mysqlkliencie uruchom następujące polecenie

USE mydb
CREATE TABLE mytbcopy LIKE mytb;
INSERT INTO mytbcopy SELECT * FROM mytb;

PODEJŚCIE 2

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"CREATE DATABASE IF NOT EXISTS test"
mysqldump ${MYSQL_CONN} mydb mytb | mysql ${MYSQL_CONN} -Dtest
mysql ${MYSQL_CONN} -ANe"ALTER TABLE test.mytb RENAME mydb.mytbcopy"

PODEJŚCIE 3

DUMPFILE=/some/path/tabledata.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"CREATE DATABASE IF NOT EXISTS test"
mysqldump ${MYSQL_CONN} mydb mytb > ${DUMPFILE}
mysql ${MYSQL_CONN} -Dtest < ${DUMPFILE}
rm -f ${DUMPFILE}
mysql ${MYSQL_CONN} -ANe"ALTER TABLE test.mytb RENAME mydb.mytbcopy"

PODEJŚCIE 4

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} mydb mytb | sed 's/mytb/mytbcopy' | mysql ${MYSQL_CONN} -Dmydb

PODEJŚCIE 5

DUMPFILE=/some/path/tabledata.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} mydb mytb | sed 's/mytb/mytbcopy' > ${DUMPFILE}
mysql ${MYSQL_CONN} -Dmydb < ${DUMPFILE}
rm -f ${DUMPFILE}

ANALIZA

• PODEJŚCIE 1 jest najłatwiejsze pod względem etapów, ale wymaga wpychania 40 milionów wierszy w jedną transakcję. Będzie to najbardziej obciążające dla InnoDB Storage Engine.
• W przypadku innych podejść mysqldump wyśle ​​40 milionów wierszy w kawałkach tysięcy rzędów
  • PODEJŚCIE 2 i PODEJŚCIE 3 mysqldump tabelę do testowej bazy danych. Po utworzeniu tabeli w testowej bazie danych jej nazwa jest następnie zmieniana i przenoszona do oryginalnej bazy danych
  • PODEJŚCIE # 4 i PODEJŚCIE # 5 zmień nazwę tabeli za pomocą sed przeciwko strumieniowi pochodzącemu z mysqldump, ponieważ echo poleceń INSERT
  • PODEJŚCIE # 2 i PODEJŚCIE # 4 używają potoków zamiast pliku wyjściowego
  • PODEJŚCIE # 3 i PODEJŚCIE # 5 wykorzystują plik outpuit do kolejnego przeładowania

Jeśli chcesz skopiować mydb.mytbdo już istniejącej tabeli mydb.mytbcopy, a dwie tabele mają identyczne struktury:

PODEJŚCIE # 6

INSERT INTO mytbcopy SELECT * FROM mytb;

Podobnie jak #APPROACH 1 , #APPROACH 6 miałby pojedynczą transakcję o wartości 40 milionów wierszy

PODEJŚCIE # 7

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} -t mydb mytb | sed 's/mytb/mytbcopy' | mysql ${MYSQL_CONN} -Dmydb

Takie podejście nie upuszcza tabeli. Po prostu generuje WSTAWKI

EPILOG

Nie mogę podać szacunkowego czasu, ponieważ nie znam składu serwera DB, struktury tabeli, układu indeksu i podobnych rzeczy.

SPRÓBUJ !!!

Tabel InnoDB, w przeciwieństwie do MyISAM *, nie można „po prostu skopiować”, ponieważ część słownika danych (i potencjalnie innych struktur, od których zależy tabela, takich jak bufor scalania) znajduje się w pamięci (jeśli serwer jest uruchomiony) i w wspólny / główny obszar tabel, zwany także dużym plikiem o nazwie ibdata1.

Jeśli używasz Percona Server> = 5.1 lub MySQL> = 5.6, dostępna jest obsługa przenośnych obszarów tabel, która pozwala eksportować i importować tabele bezpośrednio z systemu plików. Oto metoda MySQL i Percona . W obu przypadkach wymagane jest utworzenie tabeli z innodb_file_per_tableopcją i wymaga użycia DISCARD TABLESPACE/IMPORT TABLESPACEi / lub Percona Xtrabakup (jeśli chcesz, aby eksport odbywał się online). Należy pamiętać, że Percona Server lub Xtrabakup nie są dostępne dla systemu Windows.

Mówiąc ogólnie, metoda ta będzie tak szybka jak kopiowanie pliku za pomocą poleceń systemu plików (cp, rsync).

Chociaż mogą istnieć przypadki, że może to działać w MySQL <5.6 (w hacky sposób) w przypadku przywracania, nie będzie działać w przypadku kopii tabeli. W takich przypadkach jednym ze sposobów jest użycie SQL :

CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;

Będzie to tak szybkie, jak InnoDB może wykonać Handler_read_rnd_nexti Handler_writeraz na rząd. Jeśli używasz tej metody, upewnij się, że przynajmniej tymczasowo wyłączyłeś opcje trwałości i masz dużą pulę buforów i dziennik transakcji. W takich okolicznościach może to skrócić czas importowania, ale na pewno nie zmieści się w pełni w pamięci, więc spodziewaj się dużo czasu. Ponadto próbujesz zaimportować 40 mln wierszy w jednej transakcji, co może prowadzić do problemów.

W tym drugim przypadku moim zaleceniem byłoby użycie czegoś takiego jak pt-archiwizator , ponieważ wykona on operację podobną do tej, o której właśnie wspomniałem, ale będzie to wykonane w „fragmentach”, unikając narzutu transakcyjnego (może to być nie będzie szybszy, ale w przypadku awarii nie będzie próbował przywrócić całego stołu, biorąc wieczność). W przypadku wspomnianych rozmiarów danych jest to prawdopodobnie najlepsza droga.

Ostatnią opcją byłoby eksportowanie i importowanie w formacie CSV (lub TSV) , z kombinacją SELECT INTO OUTFILE / mysqldump i LOAD DATA / mysqlimport. To była bardzo powszechna opcja, jeśli potrzebna była współbieżność w niektórych starych wersjach mysql, ponieważ użycie sql stworzyło większe blokady (nie jest to już prawdą, jeśli jest wykonane poprawnie). Ponieważ mysqldump / import działa tylko w sposób zserializowany, poleciłbym ci zbadanie opcji równoległych, bardzo przydatnych w przypadku dużych tabel.

W każdym razie staraj się unikać wielu zdań SQL, ponieważ będzie to twoje najważniejsze wąskie gardło, jeśli wykonasz wiele różnych zapytań (które muszą być wykonywane, analizowane i optymalizowane indywidualnie).

* Struktur MyISAM nie można kopiować na gorąco, ale bardzo łatwo można je tymczasowo zsynchronizować z dyskiem FTWRL.

przenieść dane z jednej tabeli do drugiej w schemacie

create table your_table_name select * from old_schema_table;