はじめに
MySQLテーブルを最適化すると、専用のストレージサーバーで情報を並べ替えて、データの入力と出力の速度を向上させることができます。ただし、各最適化関数をいつ使用するか、およびそれらを状況にどのように適用するかを知ることは、実行可能なテーブル保守の鍵となります。
この記事では、MySQLテーブルを最適化するための実用的なヒントと関数を提供します。
前提条件
- MySQLバージョン8.0がインストールおよび構成されています
- コマンドプロンプトまたはコマンドラインへのアクセス
- フラグメント化されたデータベーステーブル
MySQLテーブルを最適化する必要がある理由
最適化されていないMySQLテーブルの主な理由は、頻繁に実行される更新および削除クエリです。次に、これは断片化を引き起こし、いくつかの結果があります：
1.データベーステーブルはより多くのスペースを占有します 必要以上に。
2.データのクエリには時間がかかる 本来よりも。
MySQLテーブル最適化手法は、データベース内のデータの配置に対応します。その結果、冗長な未使用スペースのないクリーンなストレージが実現し、クエリの高速化に役立ちます。
MySQLテーブルを最適化する必要があるのはいつですか？
トランザクションデータベースなど、データベース内の情報が継続的に更新されるテーブルが、最適化の候補となる可能性が最も高くなります。
ただし、データベースのサイズによっては、最適化クエリがより大きなテーブルで完了するまでに長い時間がかかります。したがって、テーブルを長時間ロックすることは、トランザクションシステムにとって有益ではありません。
テーブルをすぐに最適化する代わりに、INNODBエンジンテーブルに対して次のトリックのいくつかを試すことを検討してください。
- インデックスを削除し、最適化して、インデックスを追加し直します 。しばらくの間、テーブルがインデックスなしで機能する場合、インデックスの削除、最適化、および追加のパフォーマンスが向上する場合があります。
- 最もコンパクト化することでどの価値が得られるかを分析します 。場合によっては、プライマリインデックスが役に立たなかったり問題がなかったりします。間違った値に最適化すると、セカンダリインデックスで断片化が発生します。
最適化のためのテーブルを見つける
テーブルを表示し、最適化のために分析する方法は複数あります。 MySQLデータベースに接続することから始めます：
use <database name>ユースケースに応じて、以下のヒントのいずれかに従って必要な情報をフィルタリングします。
ヒント1：テーブルに未使用のスペースを表示する
次のコマンドで目的のテーブルのステータスを確認します：
show table status like "<table name>" \G
出力には、テーブルに関する一般的な情報が表示されます。次の2つの数字が重要です：
- Data_length データベースが占めるスペースの量を表します。
- Data_free データベーステーブル内に割り当てられた未使用バイトを示します。この情報は、最適化が必要なテーブルと、後で解放されるスペースの量を特定するのに役立ちます。
ヒント2：すべてのテーブルの未使用スペースを表示する
情報スキーマ データベーススキーマに関するメタデータを格納します。選択したスキーマ内のすべてのテーブルに割り当てられた未使用のデータを確認するには、次のコマンドを実行します。
select table_name, data_length, data_free
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;
クエリには、テーブルの名前、合計スペース、および未使用の割り当て済みスペースが表示されます。デフォルトでは、メモリはバイト単位で出力されます。
ヒント3：データをメガバイト単位で表示する
次のコマンドでデータをメガバイト単位で印刷します：
select table_name, round(data_length/1024/1024), round(data_free/1024/1024)
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;
サンプルテーブルは大きく断片化されていません。ただし、optimize tableを使用する test_tableからスペースを解放するのに役立ちます 。
テーブルの最適化
最適化によってテーブルを最適化する方法は複数あります。ただし、実行する一般的な手順は似ています。
最初に、プロセスは最適化が行われるテーブルの一時的なコピーを作成します。次に、操作が終了すると、関数は元のテーブルを最適化されたテーブルに置き換え、元のテーブルの名前を変更します。
ヒント1：MySQLを使用してテーブルを最適化する
MySQLは、テーブルを最適化するための関数を提供します。テーブルを最適化するには、次を使用します：
OPTIMIZE TABLE <table name>;
出力には、アクションと最適化の結果に関する有益なステータスメッセージが表形式で表示されます。
ヒント2：一度に複数のテーブルを最適化する
複数のテーブルを一度に最適化するには、次を使用します。
OPTIMIZE TABLE <table 1>, <table 2>, <table 3>;
結果には、最適化された各テーブルの最適化のステータスが表示されます。
ヒント3：ターミナルを使用してテーブルを最適化する
次のコマンドを使用して、Linuxターミナルからテーブルの最適化を実行します。
sudo mysqlcheck -o <schema> <table> -u <username> -p <password>たとえば、 testという名前のスキーマでチェックを実行するには およびtest_tableという名前のテーブル ルートを使用する ユーザーの資格情報、実行：
sudo mysqlcheck -o test test_table -u root複数のテーブルで最適化を実行するには、各テーブル名をスペースで区切ります：
sudo mysqlcheck -o <schema> <table 1> <table 2> -u <username> -p <password>最適化後
最適化の結果により、 data_lengthの値が変更されます およびdata_free 最適化されたテーブルの。両方の値が低くなり、次のことを示します。
1.最適化により、割り当てられた未使用のメモリが解放されました。
2.解放されたスペースのため、データベースの全体的なメモリは少なくなります。
