mysqlの仕組みをまとめた

mysqlmysql

広告

mysqlとは

オープンソースのリレーショナルデータベース管理システム(RDBMS)である。

リレーショナル・データベース管理システム（RDBMS）とは

リレーショナル・データベースを作ることや使うためのソフトウェアのことである。

RDBMSの特徴
  • トランザクションを使うことでデータの一貫性を保つ
  • データの参照や更新を簡単にできるための言語SQLが存在
  • データの整合性を維持
  • セキュリティやチューニングなどの機能がある

RDBMS以外のデータベース(NoSQL)

  • 高速な読み書きを行うことができる。
  • データ型の定義が必要ない。
  • データ同士の関連は表現できない

ストレージエンジン

ストレージエンジンとは

通常、RDMBSはいくつかの構成要素によって成り立っており、SQL文を受け取って結果を返すデータベースエンジン部、アプリケーションとデータベースエンジンを結びつけるクライアントモジュールや様々なツール群などから成り立っている。
その中でもサーバ側にて動作するデータベースエンジンはRDBMSの心臓部であり、非常に多くの仕事を担っている。

データベースエンジンの動作

  • データベースエンジンがクライアントからSQL文を受け取ると、そのSQL文の解釈を行う
  • どのようにデータを処理するかの決定。
    インデックスが存在するか。使ったほうが良いのかなど
  • 実データへのアクセス処理が開始。個々の部分がストレージエンジンに当たる。

ストレージエンジンの種類

  • MyISAM：テーブル単位のファイルによるデータ構造を持ち、トランザクション機能をサポートしていない
  • InnoDB：テーブルスペース内に複数のテーブルやインデックスを格納するデータ構造を持ち、トランザクション機能をサポート

MySQLの特徴としてストレージエンジンをテーブルごとに選択できる点が挙げられる。今回は、普段よく使っているInnoDBについて調べてみることにした。

InnoDB

MySQL 5.5.5移行、新しいテーブル用のデフォルトのストレージエンジンはInnoDBです。

利点
  • ハードウェアまたはソフトウェアの問題が原因でサーバーがクラッシュした場合でも、その時点でデータベースに何が発生していたのかには関係なく、データベースの再起動後に特別なことは何もする必要がない。
  • テーブル及びインデックスのデータにアクセスすると、そのデータは、InnoDBのバッファープールにキャッシュされる。頻繁に使用されるでt−あは、直接メモリーから処理される。このキャッシュは非常に数多くのタイプの情報に適用され、これにより処理速度が大幅に上がる。
  • テーブルごとに適切な主キーカラムを持つデータベースを設計すると、これらのカラムが関与する操作が自動的に最適化される。where,order by ,group byを使う時に主キーカラムへの参照が非常に高速である。

インデックスの仕様の仕組み

  • インデックスは特定の行を素早く見つけるために使われる。
  • インデックスがあることで、全てのデータを調べることなく、データファイルの途中のシークする位置を特定できる
  • ほとんどのMySQLインデックス（PRIMARY KEY UNIQUE INDES FULLTEXT）はBツリーに格納される
  • 例外：空間データ型のインデックスはRツリーを使用

B-tree

B-treeとは、1つのノードがm個 (m>=2) の子ノードを持つことができる平衡木構造のことです. ブロックの読み取り回数は木の深さになります。 AVL木の場合、log n 回なのに対して、B-treeの場合、log n / log m 回 (※計算は省略) になります。そのためmが大きくなるほど、処理時間に差が出てきます。 このことから、MySQLではインデックスのデータ構造にB-treeが採用されているようです。

ヘッダブロックでは大まかな値の範囲を保持しており、ブランチブロックではさらに細かい範囲を保持 リーフブロックでは実際の値と行への物理的な位置を保持しています。 INDEXが作成されている事で並び替えが速くなるのは、MySQLがこのINDEX順にレコードを表示すれば良いだけなので、 わざわざクイックソートで並び替えを行う事が無くなるため処理が高速になります。

インデックスが適切に効いているのか

インデックスが適切に効いているのかを確認するために、EXPLAINを使います。MySQLのチューニングの際に最も大切なことが、スキーマの最適化になります。なので、新しいテーブルを作成するときは、慎重に行いたいものです。基本的には、where句などで、頻繁に検索をかけるカラムでインデックスを張っていくようになります。実際にクエリを作った時に、インデックスが聞いているかを確認する必要があります。ここで、EXPLAIN SELECT …が登場します。
クエリの最適化をするために、出力結果が同じになるように新しいクエリを作ってEXPLAIN  を付けて確認をするようになります。

EXPLAINで表示されるもの

  • Id
  • select_type
  • table
  • type
  • possible_key
  • key
  • key_len
  • lef
  • rows
  • Extra

広告

mysqlmysql