☰

10GB以上のInnoDBテーブルのインデックスを削除するには4時間以上かかります

tag-icon • tag-icon mysql performance innodb
10GB以上のInnoDBテーブルのインデックスを削除するには4時間以上かかります

私が作業しているテーブルは次のとおりです:

CREATE TABLE IF NOT EXISTS `checklist_answer` (
  `id` varchar(36) NOT NULL,
  `created_by` varchar(36) NOT NULL,
  `date_created` datetime NOT NULL,
  `updated_by` varchar(36) NOT NULL,
  `date_updated` datetime NOT NULL,
  `deleted` int(11) NOT NULL,
  `checklistresponse_id` varchar(36) NOT NULL,
  `question_id` varchar(36) NOT NULL,
  `questionoption_id` varchar(36) DEFAULT NULL,
  `value` varchar(256) NOT NULL,
  `source` int(11) NOT NULL,
  `award_id` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `checklist_answer_1f92e550` (`question_id`),
  KEY `checklist_answer_35e0d13d` (`questionoption_id`),
  KEY `answerset` (`checklistresponse_id`,`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

現在、テーブルには約 2,000 万行あり、サイズは約 12 GB です。新しいインデックスを追加したり、インデックスを削除したりするたびに、最低でも 4 時間かかります。何か明らかに間違っているのでしょうか、それともこれが現状なのでしょうか?

MySQL 5.1.49

ありがとう！

答え1

MySQL 5.1の組み込みInnoDBを使用している場合、インデックスの作成と削除は非常に遅くなります。これは5.5で解決されました。高速インデックス可能であればMySQLを更新してください。あるいは、5.1 の組み込み InnoDB を InnoDB プラグインに置き換えます。(ただし、これはすでに実行されているはずですが、この問題が発生していることを考えると、何らかの理由で実行されていない可能性があります)。

答え2

INDEX する 3 つの 36 バイトの「ID」フィールドを確認します。

まず、これら 3 つを 8 バイトの UNSIGNED BIGINT に減らすことをお勧めします。40 億の数値が十分に大きい場合は、4 バイトの UNSIGNED INT がさらに適しています。

4 バイトの INT「削除済み」フィールドがフラグとして使用されている場合は、これを 1 バイトの TINYINT に変更できます。

以下を発行してみてください:

ALTER TABLE checklist_answerKEYS を変更します。

/*

 Make you table changes in here.

*/

ALTER TABLE checklist_answerENABLE KEYS;

これらを合わせると、「4 時間」が半分に短縮されても不思議ではありません。

その他のパフォーマンスを低下させる要因は次のとおりです。 「ENGINE=InnoDB デフォルト文字セット=utf8;」。

これを「ENGINE=MYISAM DEFAULT CHARSET=latin1;」に変更すると、時間をさらに半分に短縮できます。

BIGINT は 32 ビット マシンではパフォーマンスを低下させます。64 ビット マシンでは、32 ビット マシンよりも 2.5 倍高速に MariaDB が実行されます。

関連情報