MYSQLテーブルの複数のインデックスがUPDATESとINSERTSが遅い理由ですか？

Dec 10 2020

私の（LAMPスタック）サイトのパフォーマンスは、コードの更新がないにもかかわらず、過去2日間で大幅に低下しました。特定のMySQLテーブルの挿入、更新、および削除のみが問題を引き起こしているようです。ジョブ「テーブル」のエントリを更新、挿入、または削除するページは、ロードに約10秒かかります。（EG UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324）

SELECT クエリは以前と同じように実行されているように見えますが、同時に更新が行われている場合は遅くなるようです。

テーブルには約180,000のエントリがあります。PHPMyAdminビューで、プライマリフィールドの「通常の」インデックスに加えて、「entry_date」フィールドのインデックスがあることに気付きました（画像を参照）。この場合、それが問題になる可能性がありますか？そのフィールドにインデックスが作成された理由がわかりません。

そうでない場合、他に何が問題の原因である可能性がありますか？ディスクの空き容量を確認しましたが、問題ないようです。（7 GBが利用可能）dfによると。

SHOW CREATE TABLE job\G

Create Table: CREATE TABLE `job` (
    `job_id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` int(11) NOT NULL DEFAULT '0', 
    `entry_date` date NOT NULL DEFAULT '0000-00-00', 
    `timescale` varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00', 
    `title` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `description` text COLLATE latin1_german2_ci NOT NULL, 
    `start_date` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `address_town` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `address_county` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode1` varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode2` char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `status` tinyint(4) NOT NULL DEFAULT '0', 
    `cat_id` int(4) NOT NULL DEFAULT '0', 
    `price` decimal(4,2) NOT NULL DEFAULT '1.00', 
    `emailcount` smallint(5) NOT NULL DEFAULT '-1', 
    `emailcount2` int(11) NOT NULL DEFAULT '-1', 
    `recemailcount` int(11) NOT NULL DEFAULT '-1', 
    `archive` tinyint(4) NOT NULL DEFAULT '0', 
    `post_url` varchar(100) COLLATE latin1_german2_ci NOT NULL, 
    PRIMARY KEY (`job_id`), 
    KEY `entrydatejob_id` (`entry_date`,`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=235844
           DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

更新-まず、すべての貢献者に感謝します。とても感謝しています。そのため、過去2日間で問題は停止し、問題がさらに困難になる可能性があるものを見つけようとしました。でも今はまた戻ってきたようです。まず、Google Cloudでホストされているマシンタイプg1-small（1 vCPU、1.7 GBメモリ）を提供することから始めましょう。私はこれらのコメントによって要求されたさらなる情報でこれを更新し続けます。

回答

6 ypercubeᵀᴹ Dec 10 2020 at 19:22

特定の質問に答えるには：

いいえ、(entry_date)1つまたは2つのインデックスを追加しても、別のtitle列を更新するパフォーマンスが低下することはありません。

さらに：

いいえ、MySQLのバージョン5.6は、いくつかの最新機能（ウィンドウ関数など）が欠落している場合でも、それほど古くはありません。あなたはまともなハードウェアでまともなパフォーマンスを持っている必要があります。

手元にある問題について推測することしかできませんが、何が間違っているか、それを説明することができます。

古い非効率的なハードウェア：ディスクの仕様を確認し、そのパフォーマンスを測定します。
断片化されたインデックス/テーブル。MyISAMテーブル（OPTIMIZE TABLE）を最適化する方法については、MySQLのドキュメントと古い質問/回答をここで確認してください。

最後だが大事なことは：

あなたのテーブルは古いニュースであるMyISAMエンジンを使用しています。InnODBは、数年前にMySQLのデフォルトエンジンとして置き換えられました。このエンジンの活発な開発はありません。InnoDBと比較していくつかの機能（トランザクション、外部キー制約など）と、ほとんどの作業負荷でのパフォーマンスが不足しています。InnoDBを使用するように、テーブルを変更することを強くお勧めします（もちろん、アプリとプロシージャが壊れないことをテストした後）。
重要なのは、あなたの質問に、InnoDBが実行できるSELECTとUPDATE同じ時間（通常）で。MyISAMはテーブルを完全にロックします。選択または更新を開始する前に、更新または選択を終了する必要があります。
MyISAMからInnoDBのに切り替えると、調整してくださいkey_buffer_sizeとinnodb_buffer_pool_size。

3 J.D. Dec 10 2020 at 19:20

インデックスはINSERTおよびDELETE操作のパフォーマンスにわずかに影響しますが、特にインデックスが適切に計画されていて、それぞれが30の異なるフィールドの組み合わせを持つ同じテーブルに30のインデックスを配置することでやり過ぎない場合は、通常これは無視できます。（通常、私は5 x 5のガイドラインに固執します-最大5インデックス、インデックスあたり最大5フィールド。もちろん、これは単なるガイドラインであり、厳格なルールではありません）。

そうは言っても、インデックスはUPDATEまたはSELECTのいずれかの行を見つけるために使用されるため、実際にはUPDATEおよびSELECTクエリのパフォーマンスを向上させるために使用されます。

あなたが見ている劇的な変化は、あなたが発見したentry_dateインデックスに関連しているとは疑わしいです（それが最近追加されたのか、それともパフォーマンスの変更前にすでに存在していたのか知っていますか？）。

調べる必要がある2つのことは次のとおりです。

クエリの以前のインデックスがまだ使用されている場合、特にUPDATEクエリとSELECTクエリの場合（およびそれらがシークされているか、スキャン操作が現在行われている場合）。これは、時間の経過に伴うデータのテーブル統計の変化により変化する可能性があります。ANALYZEステートメントを使用して、テーブルの統計を確認できます。
調べることができるもう1つのことは、インデックスの断片化です。これは、時間の経過とともに自然に発生します。これは、テーブルにデータが追加されると発生します。（一般的に、これはあなたのような小さなテーブルでは問題にならないはずですが、とにかくチェックする価値があります。）

私はそれらについて考えるように調査するためにもっと多くのもので私の答えを更新し続けます。クエリでEXPLAINを実行すると、問題の手がかりになる場合もあります。実行速度が遅いUPDATEステートメントとINSERTステートメントを分離し、それらが同じ根本原因の影響を受けているかどうかを理解しようとしています（ここでも、インデックスとは逆に動作するため、INSERTは無視できるほど遅くなりますが、UPDATEは通常、正しいインデックスの恩恵を受けます。より速くなるはずです。）

WilsonHauck Dec 10 2020 at 22:40

OPTIMIZETABLEジョブ; 断片化を排除し、すべてのインデックスを再作成します。

次に、UPDATEクエリのタイミングを確認します。

プロファイルの表示、パフォーマンスの調整を支援するための無料でダウンロード可能なユーティリティスクリプトのネットワークプロファイル。