mysql 予備知識 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− #--------------------------------------------------------------------------------------------- # バージョンによる機能の違い #--------------------------------------------------------------------------------------------- InnoDB とは、外部キー、トランザクション、行レベルロックを備えたデータベースタイプのこと。 ● 3.23 ・コンパイルオプションで、テーブルタイプInnoDB の使用が可能となる ● 4.0 ・テーブルタイプInnoDB が標準でサポートされる ・クエリーキャッシュ機能を備える ・UNION をサポート ・my.cnf の内容をSQL で表示可能 ● 4.1 ・副問い合わせをサポート ・文字コードUnicode をサポート ・サーバー、クライアントごとの文字コード実装 ● 5.0 ・ストアドプロシージャをサポート ・カーソルをサポート ・アップデートログを廃止 ・トリガをサポート ・ビューをサポート #--------------------------------------------------------------------------------------------- # 日本語関連 #--------------------------------------------------------------------------------------------- mysql は4.1 以降、サーバーとクライアントの文字コードを分けて管理するようになりました。サーバー、クライアントそれぞれの文字コードは次のSQL 文で確認できます。 show variables like 'character\_set\_%'; SQL 文で確認できる変数の意味は次の通りです。 $character_set_system "indentifiers" (テーブル名とかカラム名？) を格納するのに使われる。UTF8 で固定。 $character_set_server character_set_database のデフォルト値に影響する。 $character_set_database CREATE DATABASE で文字コードが指定されなかった場合、database の文字コーはこれになる。 $character_set_client クライアントから渡されたSQL 文はこの文字コードであると解釈される。 $character_set_connection キャラクタセットイントロデューサ (例えば『_ujis'ほげ'』)が省略されたSQL 文中の文字列リテラルはこの文字コードであると解釈される。 $character_set_results サーバーがクライアントに返す結果をこの文字コードに変換する。この変数をNULL にセットすると、結果に対する文字コード変換をしないようにできる。 通常、configure 時に --with-charset オプションを設定すると、上記の変数のうち、 character_set_system 以外の変数はすべて --with-charset オプションで指定したエンコードになる。 $character_set_server、もしくは$character_set_database エンコードの変更は /etc/my.cnf の [mysqld] グループ内で行う。 $character_set_client、もしくは$character_set_connection、$character_set_resultsの変更は、下記のいずれかの方法で行う。 ・コマンドラインから mysql --default-character-set=[文字コード] を実行 ・/etc/my.cnf の [mysql] グループに default-character-set=[文字コード] を追加 ・SQL で set names [文字コード] を実行 ※ 注意 ) PERL のDBD-mysql よりmysql 4.1 に接続した場合、サーバーのmysql のバージョンが 4.1.12 以下で、かつサーバーエンコードがUTF8 ある場合、 set names utf8 によるクライアントエンコードの変更は効かない。というよりも、サーバー上ではSQL が実行されているが、日本語に余計なエスケープが挿入される。これを回避するには、mysql のバージョンを4.1.21 に上げる。 ● 使用可能な文字コードは次のSQL 文で確認方法 show character set; #--------------------------------------------------------------------------------------------- # チューニング #--------------------------------------------------------------------------------------------- ● プロセス構造 mysql サーバーの実態はmysqld なるデーモンプロセスである。クライアントからの接続要求があるたびに、mysql はスレッドを発行する。使い終わったスレッドはスレッドキャッシュに保存され、次回接続に備える。 スレッドキャッシュに保存できる最大スレッド数は thread_cache_size で指定 mysql サーバーに接続できる最大クライアント数は max_conextions で指定 ● バッファ構造 mysqld は問い合わせする際に下記のバッファ領域を使用する。 key_buffer mysqld に一つだけ存在し、インデックスデータを順次読み込んで記憶するための領域 read_buffer スレッドごとに存在し、インデックスデータが参照しているテーブルデータを順次読み込み、記憶する領域 read_rnd_buffer スレッドごとに存在し、テーブルデータを順次直接読み込んで記憶するための領域 sort_buffer スレッドごとに存在し、ORDER BY の処理など、結果の並び替えに使うための領域 join_buffer スレッドごとに存在し、複数のテーブルの結合を行うための領域 ● キャッシュ構造 table_cache mysql サーバーは多数のファイルでデータを管理しているので、多数のファイルに素早くアクセスする必要がある。通常、ファイルアクセスはfopen システムコールを使うが、これをtable_cache にキャッシュすることでファイルオープンのオーバーヘッドを減らしている。最大キャッシュサイズはtable_cache で指定する。 net_buffer スレッドごとに存在し、クライアントから受信、もしくは送信するクエリーデータの一時保存場所として使われる。 query_cache 実行されたクエリー(SELECT文) の結果を保存するキャッシュ。同様のクエリーが要求された場合はキャッシュに保存された結果を返却する。 thread_cache クライアントの要求はスレッドで対応するが、一旦使用されたスレッドはキャッシュされる。次回要求時はキャッシュ中のスレッドが利用される。 #--------------------------------------------------------------------------------------------- # 注意事項 #--------------------------------------------------------------------------------------------- mysql コマンドは起動時に /etc/my.cnf を読み込む mysql は他のRDBMS とは違い、USE 文を使うことで、一回のセッション中で異なるデータベースにアクセスできる。 データベース名、ユーザー名にアンダーバー、パーセントを使う場合は、「'foo\_bar'」のように指定します。必ずシングルクオートで囲んでください。 root ユーザーにパスワードを設定した場合、root ユーザー権限でコマンドを実行する際には必ず”-p”オプションにてパスワード認証する必要があります。 CHAR 型に関して、Create する際に指示したサイズ以上のデータを登録した場合、自動的に指示サイズにカットされて登録されます。 CHAR 型に関して、文字列以外のデータを登録した場合はNULL が登録されます。 基本的にroot ユーザーはすべてのデータベースのリソースを操作する権限があります。 mysql データベースにはMySQL で管理される全情報が記憶されています。特にユーザー管理にて重要なテーブルは下記の通です。 user テーブル ・・・追加された全ユーザーの権限を一覧表示 db テーブル ・・・全データベースのユーザーごとの権限を一覧表示 host テーブル ・・・全テーブルのユーザーごとの権限を一覧表示 ユーザー名にアンダーバーを含む場合、例えばユーザー名が「foo_bar」である時、「foo*bar」のように、アンダーバーを任意文字と差し替えたデータベースが作成できる。これは、ユーザーの権限を無視して別名のデータベースを用意に作成できるMYSQL 独自の仕組みである。