【MySQL】システム変数の設定と値の確認方法

MySQL Serverには、その構成方法を指示する多くのシステム変数があります。ここでは、SETステートメントを使ってシステム変数を変更する方法や、現在のシステム変数の値を確認する方法を説明します。

一般的にシステム変数の変更は、コマンドラインのオプションに指定したり、オプションファイルを使って行うことが多いと思いますが、ここではシステム変数の理解を目的としているため、これらについての説明はまた機会があればしたいと思います。

システム変数の概要

そのシステム変数にはグローバル変数とセッション変数があります。

グローバル変数はサーバー全体の操作に影響します。グローバル変数にはデフォルト値があり、すべてのグローバル変数は、サーバーの起動時にデフォルト値に初期化されます。

セッション変数はクライアント接続ごとに存在し、クライアントとの接続に影響を与えます。セッション変数はクライアントとの接続時に、対応するグローバル変数の現在の値で初期化されます。

いくつかのシステム変数は、グローバル値とセッション値の両方を持つことができます。

システム変数は、コマンドラインのオプションを使用するか、オプションファイルでサーバー起動時に設定できます。ほとんどのシステム変数は、SET ステートメントを使用してサーバーの実行中にも変更できます。これによるサーバーの再起動は必要なく、サーバーの動作を動的に変更できます。

システム変数の変更

SETステートメントでグローバル変数を変更するには、変数名の前に「GLOBAL」または「@@GLOBAL.」を付けて指定します。グローバル変数を設定するには SUPER 権限が必要です。

SETステートメントでセッション変数を変更するには、変数名の前に「SESSION」、「@@SESSION.」、「@@」を明示的に指定するか、修飾子を指定しないで変数名だけ指定します。セッション変数を設定するのに特別な権限は必要ありませんが、クライアントは自分自身のセッション変数のみ変更できます。

SETステートメントで複数の変数の変更を一度に行うには、カンマで区切って指定します。 GLOBAL または SESSION 修飾子が指定された場合、後続の修飾子を持たない変数はその修飾子が指定されたものとして扱われます。

SET sort_buffer_size=50000;
SET @@sort_buffer_size=500000;
SET @@GLOBAL.sort_buffer_size=1000000, @@SESSION.sort_buffer_size=1000000;
SET GLOBAL sort_buffer_size=2000000, read_buffer_size=262144, SESSION sort_buffer_size=1000000;

最初の2つの例はセッション変数を変更します。 3つ目の例はグローバル変数とセッション変数を変更します。 4つ目はグローバル変数のsort_buffer_size、read_buffer_sizeとセッション変数のsort_buffer_sizeを変更します。

セッション変数の変更は、セッションが終了するまで、または変数を異なる値に変更されるまで有効です。セッション変数の変更は別のクライアントからは見ることができません。

グローバル変数を変更すると、その値はサーバーの終了まで、または変数を異なる値に変更するまで有効です。グローバルシステム変数を永続的に設定するには、オプションファイルを使います。

システム変数の参照

特定のシステム変数の値を参照するには、SELECTに@@修飾子を付けて変数を指定します。

グローバル変数を参照するには、変数名に「@@GLOBAL.」を付けてSELECTに指定します。

SELECT @@GLOBAL.sort_buffer_size;

セッション変数を参照するには、「@@SESSION.」を付けた変数名を指定します。

SELECT @@SESSION.sort_buffer_size, @@sort_buffer_size;

「@@」を付けた変数名は、その変数名のセッション変数が存在すればそれを参照し、セッション変数が存在しなければグローバル変数を参照します。（これは、常にセッション変数を参照する SET @@var_name = value とは異なります。）

全てのシステム変数の名前と値を表示するには、SHOW VARIABLES ステートメントを使います。 SHOW VARIABLES で GLOBAL および SESSION をいずれも指定しない場合は、セッション変数を返します。

mysql> SHOW VARIABLES;
mysql> SHOW SESSION VARIABLES;
mysql> SHOW GLOBAL VARIABLES;

LIKE 句を使うと、パターンに一致する変数のみを表示します。特定の変数名だけを表示するには、次のように指定します。

SHOW VARIABLES LIKE 'max_join_size';
SHOW SESSION VARIABLES LIKE 'max_join_size';

SHOW VARIABLES LIKE 'sort_buffer_size';

SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';

LIKE 句の中ではワイルドカードが使えます。を使用します。「%」は任意の文字列と、「_」は任意の 1 文字と一致します。

SHOW VARIABLES LIKE '%size%';
SHOW GLOBAL VARIABLES LIKE '%size%';