MySQLの非常に大きなテーブルの変更

プロジェクトにサイズがギガバイトで計算されたテーブルがあり、そのようなテーブルの構造を変更するには、すべてのサービスを数時間停止する必要があります-この記事はあなたのためです。



指定：数十ギガバイトのデータを測定するテーブル。 タスクは、テーブルの構造を変更することです。



この方法はトランザクションテーブルでのみ機能します。 数十ギガバイトのMyISAMテーブルがある場合は、「自分で問題に対処する」という冗談です。 InnoDBテーブルの例を示します。



テーブルの構造が次のようになっていると仮定します。

CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT '', `password_hash` char(32) NOT NULL DEFAULT '', `registration_date` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このテーブルにlast_loginフィールドを追加します。



どのようなオプションがありますか？

額

 ALTER TABLE `users` ADD COLUMN `last_login` int(11) NOT NULL DEFAULT 0;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このオプションは、テーブルのサイズが50,000レコードをめったに超えない小さなプロジェクトでうまく機能します。 このオプションは私たちには適していません。 ALTERの実行時間が長すぎるため、この間は書き込みと読み取りの両方でテーブルがロックされます。 したがって、この時間はサービスを停止する必要があります。

脳をオンにする

そのことに関してテーブルに触れることはできませんが、別の `users_lastvisits`を作成してください：

 CREATE TABLE `users_lastvisits` ( `user_id` int(11) NOT NULL, `last_login` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これで、last_loginテーブルでJOINを実行するためにlast_loginが必要なすべてのクエリで可能になりました。 もちろん、動作はもっと遅くなり、JOINを追加するリクエストでも時間がかかりすぎますが、一般にこれで十分な場合があり、この時点で停止できます。

そしてまだ-あなたはフィールドを追加する必要があります

マスタースレーブレプリケーションを起動し、スレーブサーバーでALTERを実行してから、それらをスワップできます。 正直なところ、私はこれをやったことがありません。次の方法よりも簡単かもしれませんが、複製を増やすことは常に可能とは限りません。

私の方法は次のとおりです

最終的な構造を持つ新しいテーブルを作成し、すべての変更を記録するトリガーを最初のテーブルで作成すると同時に、最初のテーブルから2番目のテーブルにデータを転送し始め、最後に変更されたデータを「注ぎ」、テーブルの名前を変更します。



そのため、2つのテーブルを準備しています。1つ目は目的の構造で、2つ目は変更のログ記録用です。

 CREATE TABLE `_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT '', `password_hash` char(32) NOT NULL DEFAULT '', `registration_date` int(11) NOT NULL DEFAULT '0', `lastvisit` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `users_updated_rows` ( `id` int(11) NOT NULL AUTO_INCREMENT, `row_id` int(11) NOT NULL DEFAULT '0', `action` enum('updated','deleted') NOT NULL DEFAULT 'updated', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

トリガーを配置します。

 DELIMITER ;; CREATE TRIGGER users_after_delete AFTER DELETE ON users FOR EACH ROW BEGIN INSERT INTO users_updated_rows VALUES (0, OLD.id, 'deleted'); END;; CREATE TRIGGER users_after_insert AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO users_updated_rows VALUES (0, NEW.id, 'updated'); END;; CREATE TRIGGER users_after_update AFTER UPDATE ON users FOR EACH ROW BEGIN IF (OLD.id != NEW.id) THEN INSERT INTO users_updated_rows VALUES (0, OLD.id, 'deleted'); END IF; INSERT INTO users_updated_rows VALUES (0, NEW.id, 'updated'); END;; DELIMITER ;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

オーバーフローを開始します。 これを行うには、データベースへの2つの接続を開きます。 1つでは、輸血が実質的に行われ、もう1つでは、書き込みのためにテーブルを一時的にロックする必要があります。

 mysql> LOCK TABLES users WRITE; Query OK, 0 rows affected (0.00 sec) mysql> --      mysql> TRUNCATE users_updated_rows; Query OK, 0 rows affected (0.17 sec) mysql> --      mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users; mysql> --     mysql> UNLOCK TABLES;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これで、テーブルがオーバーフローしている間に、オーバーフローの開始以降に変更されたデータをどのように注ぎ込むかを考える時間ができます。 複雑なことはまったくありません-スクリプトを提供しません。ユーザーが追加された順序（主キーで並べ替え）でusers_updated_rowsテーブルから1つのレコードを取得し、_usersテーブルで更新または削除するだけです。



したがって、テーブルのオーバーフローはすでに終わっているので、残りのデータを注ぐ必要があります。 スクリプトを実行します。 スクリプトは絶えず動作し、テーブルの名前を変更するために必要なすべてのデータを注ぐと、ログに追加されるすべてのエントリを更新する必要があります。

 mysql> TRUNCATE users_updated_rows; Query OK, 0 rows affected (0.16 sec) mysql> RENAME TABLE users TO __users, _users TO users; Query OK, 0 rows affected (0.11 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この時点でわずかなデータ損失が発生する可能性があることに注意してください。 要求はアトミックに実行されません。 これが重要な場合は、変更要求がないようにしばらくサービスをオフにすることをお勧めします。 たとえば、ユーザーから書き込み権限を奪い、別のユーザーの下でコマンドを実行することができます。



すべてが正しく行われていれば、データは失われず、サービスの中断はほとんどありません。 これが必要なものです。 同様に、データを別のサーバーに転送できますが、転送方法のみが変更されます。 代わりに

 mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

mysqldumpを介してオーバーフローする必要があります。

 $ mysqldump -h host1 db users --single-transaction -ecQ | pv | mysql -h host2
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このようにして、サービスを別のサーバーに停止することなく、約12時間で60Gbと4億行のテーブルを転送することができました。



ところで、自転車はすでにFacebookによって発明されており、MySQLのオンラインスキーマ変更と呼ばれています 。