MySQL でテーブルとデータベースを最適化する

1. MySQL でテーブルを最適化するタイミングと理由
2. MySQL でテーブルを最適化する
3. Windows / Ubuntu 20.04OS で MySQLShell を使用して、単一または複数のテーブルを最適化する
4. Windows ターミナルを使用してテーブルを最適化する
5. Ubuntu 20.04 ターミナルを使用してテーブルを最適化する
6. 1つの MySQL データベース内のすべてのテーブルを最適化する
7. MySQL のすべてのデータベースを最適化する

このチュートリアルは、MySQL でテーブルとデータベースを最適化する方法に関する完全なガイドです。Windows と Linux（Ubuntu 20.04）の 2つのオペレーティングシステムを使用します。

また、MySQL での最適化の重要性についても説明します。

MySQL でテーブルを最適化するタイミングと理由

アプリケーションがデータベースに対して多くの DELETE および UPDATE 操作を実行している場合、MySQL のデータファイルが断片化される可能性が高くなります。その結果、未使用のスペースが発生し、パフォーマンスにも影響を与える可能性があります。

MySQL テーブルを継続的にデフラグし、未使用のスペースを再利用する必要があります。これは、MySQL でテーブルの最適化が必要な場所です。これは、専用ストレージサーバー内のデータの並べ替えをサポートし、最終的にデータの入出力のパフォーマンスと速度を向上させます。

では、どのテーブルを最適化する必要があるかをどうやって知るのでしょうか？情報（データ）が継続的に更新されるテーブルを最適化する必要があります。たとえば、トランザクションデータベースはテーブル最適化の最適な候補です。

ただし、テーブルとデータベースのサイズによっては、最適化クエリにかかる時間が長くなる可能性があります。したがって、トランザクションシステムがテーブルを長時間ロックすることは適切ではありません。

代わりに、INNODB エンジンテーブルでいくつかのトリックを試すことができます。いくつかのトリックを以下に示します。

1. 誤った値を最適化すると、セカンダリインデックスが断片化する場合があるため、特定の値を圧縮することでより多くのメリットを得る方法を分析することが重要です。これは、最適化のための正しい値の特定が非常に重要であることを意味します。
2. 別の方法は、インデックスを削除し、テーブルを最適化して、インデックスを追加し直すことです。この方法は、テーブルがインデックスなしで短時間機能できる場合にのみ適用できます。

MySQL でテーブルを最適化する

まず、最適化するテーブルを分析する必要があります。次のコマンドを使用してデータベースに接続する必要があります。

サンプルコード：

-- Syntax: Use your_database_name;
mysql> USE test;

目的のデータベースに接続したら、次のクエリを使用してテーブルのステータスを取得します。

サンプルコード：

-- Syntax: SHOW TABLE STATUS LIKE "your_table_name" \G
mysql> SHOW TABLE STATUS LIKE "test_table" \G

出力：

このテーブルを最適化する必要があるかどうかを知るための 2つの重要なプロパティがあります。

1. Data_length は、データベースが占めるスペースの量について説明します。
2. Data_free は、データベーステーブル内の割り当てられているが未使用のバイトを通知します。

この情報は、最適化が必要なテーブルと、後で再利用するスペースの量を特定するためのガイドになります。

次のクエリを使用して、特定のデータベース内のすべてのテーブルのこれら 2つの数値（Data_length と Data_free）を取得できます。現在、test データベースには test_table という名前のテーブルが 1つだけあります。

サンプルコード：

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

出力：

上記のクエリは、テーブルの名前、合計スペースをバイト単位で出力し、割り当てられた未使用スペースをバイト単位で出力します。メガバイトでの作業に慣れている場合は、次のクエリを使用してメガバイトで出力を取得できます。

サンプルコード：

mysql> SELECT TABLE_NAME,
    -> round(data_length/1024/1024) AS Data_Length_in_MBs,
    -> round(data_free/1024/1024) AS Data_Free_in_MBs
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

与えられたサンプルテーブルは大きく断片化されていませんが、OPTIMIZE TABLE コマンドを使用してスペースを再利用できます。

MySQL シェルとターミナルを使用して単一/複数のテーブルまたはデータベースを最適化する方法を学習します。

Windows / Ubuntu 20.04OS で MySQLShell を使用して、単一または複数のテーブルを最適化する

サンプルコード：

mysql> OPTIMIZE TABLE test_table;

出力：

複数のテーブルを最適化するには、次のクエリを使用します。

サンプルコード：

mysql> OPTIMIZE TABLE tableName1, tableName2;

次に、次のコマンドを使用して、目的のテーブルが最適化されていることを確認します。

サンプルコード：

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

出力：

同じクエリが Linux オペレーティングシステム（Ubuntu 20.04）の MySQL シェルで機能します。

Windows ターミナルを使用してテーブルを最適化する

サンプルコード：

-- Syntax mysqlcheck -o <schema> <table> -u <username> -p <password>
mysqlcheck -o test test_table -u root -p

上記のコマンドを記述してEnterを押すと、MySQL の root パスワードを入力するように求められます。それを入力してください。

クエリを実行するには、bin フォルダにいる必要があります（次の例を参照）。

サンプルコード：

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlcheck -o test test_table -u root -p
Enter password: *****

Ubuntu 20.04 ターミナルを使用してテーブルを最適化する

sudo su コマンドを使用して superuser としてサインインしている場合は、以下のようにクエリを実行します。MySQL の root パスワードのみを要求します。

サンプルコード：

mysqlcheck -o test test_table -u root -p

スーパーユーザーとしてサインインしていない場合は、mysqlcheck コマンドを実行します。ここでは、システムの root パスワードと MySQLroot パスワードを入力するように求められます。

サンプルコード：

sudo mysqlcheck -o test test_table -u root -p

1つの MySQL データベース内のすべてのテーブルを最適化する

Windows コマンドラインを使用してすべてのテーブルを最適化する：

-- Syntax: mysqlcheck -o your_database_name -u username -pPassword
mysqlcheck -o test -u root -p

Ubuntu ターミナルを使用してすべてのテーブルを最適化する：

-- if you are signed in as a superuser
mysqlcheck -o test -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o test -u root -p

MySQL のすべてのデータベースを最適化する

Windows コマンドラインを使用してすべてのデータベースを最適化する：

-- Syntax: mysqlcheck -o --all-databases -u username -pPassword
mysqlcheck -o --all-databases -u root -p

Ubuntu ターミナルを使用してすべてのデータベースを最適化する：

-- if you are signed in as a superuser
mysqlcheck -o --all-databases -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o --all-databases -u root -p