主要RDBMS製品の比較 – インデックスの仕組みと物理構造

Oracle ACE Proの渡部です。主要なRDBMS製品を比較します。

大枠を整理することが最大の目的です。細かい例外事項や拡張機能は適宜記載を割愛しています。
2022年9月時点の最新バージョンをベースに記載していますが、記載内容にバージョン依存は少ないはずです。
時間ができた時に随時追記予定です。
もし誤りを見つけた場合は、優しく教えていただけると嬉しいです。→ https://twitter.com/wrcsus4 or ryota.watabe at cosol dot jp

「主要RDBMS製品の比較」ページ一覧

概論
https://cosol.jp/techdb/2023/12/rdbms_compare_overview/
アーキテクチャ, スキーマ, データベース, メモリ
https://cosol.jp/techdb/2022/09/rdbms_architecture_comparison/
記憶域, トランザクションログ, 物理構造
https://cosol.jp/techdb/2022/09/rdbms_compare_storage/
インデックスの仕組みと物理構造
https://cosol.jp/techdb/2023/12/rdbms_compare_index_structure/
バックアップ, 災害対策構成, 論理レプリケーション
https://cosol.jp/techdb/2022/09/rdbms_compare_bk_dr_rep/
同時実行制御, トランザクション分離レベル
https://cosol.jp/techdb/2022/09/rdbms_compare_conc_cntl_transaction/
文字コード, 文字セット, 照合順序
https://cosol.jp/techdb/2022/09/rdbms_compare_charcode/
接続, ユーザー, コマンドラインクライアント
https://cosol.jp/techdb/2022/09/rdbms_compare_conn_user/

立場の表明

コーソルはデータベース関連製品の販売およびプロフェッショナルサービス提供を行っている営利企業です。
https://cosol.jp にある全てのコンテンツは、情報提供に加えて、コーソルの認知度向上、コーソルの営利活動の促進を目的としています。

著者について

https://cosol.jp/techdb/2000/01/watabe_profile/

インデックスの必要性

テーブルに格納された行データはデータファイルに格納されますが、データファイルはデータブロックに分割した上で使用されます。
- データブロックは「ブロック」、「ページ」と呼ばれる場合があります。
データブロックは一般に固定サイズで、サイズは8KB、16KB、32KBなどが一般的です。

インデックスの必要性を理解するうえで、重要な特性は、「ある行データが、どのデータブロックに格納されるか？」は一般に不定であるということです。

よって、この特性から、テーブルから行データを探す方法が必要とされ、それに役立つのがインデックス（索引）です。

インデックスの仕組み

インデックスは検索対象となる列に対して作成し、作成すると、一般に「その列値に対応する行データ」を高速に探すことができるようになります。

以下に列idに作成したインデックスのイメージと、"列id=2"である行データを探す場合の動きを示します。

以下の点を理解してください。

ルートブロック → ブランチブロック → リーフブロックというツリー構造になっています。
- この図でツリー構造は3階層ですが、実際の階層の数はデータの量や状態によって異なります。
- この構造のインデックスを、特に「Bツリーインデックス」と呼ぶことがあります。
複数あるリーフブロックには、インデックスを作成した列のデータが、ソートされて格納されています。
あるブロックには、複数の下位ブロックがあり、下位ブロックに対応する値の範囲（値の境界／しきい値）を知っています。これにより、検索対象のデータを含むリーフブロックを、ルートブロックから順に辿ることで高速に探すことできます。

インデックスの物理構造

インデックスの基本的な考え方は上記のとおりですが、実際のインデックスの物理構造は製品により異なります。

Oracle, PostgreSQL

リーフブロックには、インデックスを作成した列のデータに加えて、行全体のデータの位置を示すポインタ相当の情報が格納されています。
テーブルの行データはインデックスとは別の領域（データブロック）に格納されます。
- 行データの格納順序は不定です。

MySQL(InnoDB), MS SQL Server

Bツリーインデックスとテーブルが一体化した構造をしています。
- リーフブロックとテーブルが一体化したイメージです。
- この構造を「クラスタ化インデックス」と呼びます。
行データの格納順は、クラスタ化インデックス列（≒主キー列）の値順担っています。

クラスタ化インデックスの特性、利点と欠点

特性

1つのテーブルに対して作成できるクラスタ化インデックスは1つのみです。
- 通常、テーブルの主キー列に対してクラスタ化インデックスを作成します。

利点

検索条件に主キー列を指定したクエリを高速に処理できる可能性があります。（等値条件、範囲条件など）

欠点

リーフブロック（最下層のブロック）に格納できるデータ件数が少ないです。
検索条件に主キー以外の列を指定したクエリの処理効率が悪い（MySQL特有）

セカンダリインデックス(MySQL InnoDB)

MySQL InnoDBでは、一般に主キー列にクラスタ化インデックスが作成され、検索条件に主キー列を指定したクエリを高速に処理できるようになります。

検索条件に主キー以外の列を指定したクエリの処理効率を高めるためには、その列に対してインデックスを作成できます。主キー以外の列に作成したインデックスは「セカンダリインデックス」と呼ばれ、クラスタ化インデックスとは異なる構造となります。

セカンダリインデックスのリーフブロックには、索引列の値と、主キーの値が格納されます。

検索条件に主キー以外の列を指定したクエリを実行すると、一般に、セカンダリインデックスとクラスタ化インデックス（主キー）の2つのインデックスを経由します。

MS SQL Serverの非クラスタ化インデックス

MS SQL Serverで主キー以外の列にインデックスを作成すると、インデックスの構造は「非クラスタ化インデックス」と呼ばれるものになります。

クラスタ化インデックスのリーフブロックには、索引列の値と、「行識別子（RID）」と呼ばれる行データの位置を示すポインタ相当の情報が格納されています。

MySQL InnoDB場合と異なり、検索条件に主キー以外の列を指定したクエリを実行しても、2つのインデックスを経由することは一般にありません。

「主要RDBMS製品の比較」ページ一覧

概論
https://cosol.jp/techdb/2023/12/rdbms_compare_overview/
アーキテクチャ, スキーマ, データベース, メモリ
https://cosol.jp/techdb/2022/09/rdbms_architecture_comparison/
記憶域, トランザクションログ, 物理構造
https://cosol.jp/techdb/2022/09/rdbms_compare_storage/
インデックスの仕組みと物理構造
https://cosol.jp/techdb/2023/12/rdbms_compare_index_structure/
バックアップ, 災害対策構成, 論理レプリケーション
https://cosol.jp/techdb/2022/09/rdbms_compare_bk_dr_rep/
同時実行制御, トランザクション分離レベル
https://cosol.jp/techdb/2022/09/rdbms_compare_conc_cntl_transaction/
文字コード, 文字セット, 照合順序
https://cosol.jp/techdb/2022/09/rdbms_compare_charcode/
接続, ユーザー, コマンドラインクライアント
https://cosol.jp/techdb/2022/09/rdbms_compare_conn_user/

[PR] オンプレミス＆クラウドのマルチDB製品に対応した性能管理ツールDPA

Database Performance Analyzer (DPA) は、オンプレミス＆クラウドに対応するデータベース性能監視／分析ツールです。

この記事で取り上げたRDBMS製品を含む、非常に多くのデータベース製品／サービスに対応しています。

Oracle Database
MS SQL Server
Sybase SAP ASE
IBM Db2
MySQL / MariaDB / Percona Server for MySQL
PostgreSQL / Enterprise DB
AWS
- Amazon RDS for Oracle Database / SQL Server / MySQL / MariaDB / PostgreSQL
- Amazon Aurora for MySQL / PostgreSQL
Azure
- Azure SQL Database
- Azure SQL Managed Instance
- Azure SQL for PostgreSQL
- Azure Database for MySQL / MariaDB
Google Cloud
- Google Cloud SQL for MySQL / PostgreSQL / SQL Server

以下の特徴があり、導入しやすく有用な製品です。

非常に低価格。課金単位は監視インスタンスの数で、1インスタンス14.7万円/年から（2023年1月時点）。
インストールが容易。DBサーバへのエージェント導入は不要。
オンラインデモサイトですぐに使用感を確認可能 → https://cosol.jp/techdb/2022/08/dpa_online_demo/
待機時間を基礎とする性能分析（近年主流の性能分析メソッド）
機械学習アルゴリズムに基づく異常検知機能(Anomaly Detection)

なぜコーソルからDatabase Performance Analyzer (DPA)を購入すべきなのか

コーソルはDatabase Performance Analyzer (DPA)の一次代理店で、Database Performance Analyzer (DPA)の製品販売を行います。 SIer様、販社様がDatabase Performance Analyzer (DPA)を販売および導入することも可能です。

Database Performance Analyzer (DPA)
https://cosol.jp/license/dpa/

コーソルはデータベースの技術力を強みとしています。なかでもOracle Database技術力は日本随一です。MySQL、PostgreSQL、MS SQL Serverの資格や実績を持つエンジニアも多数在籍しております。

独自のDPAナレッジを公開

DPAの導入や監視設定に関する手順をナレッジとして公開しています。評価版をご利用される際の参考にしていただけると幸いです。

https://cosol.jp/knowledge/dpa-tips/
https://cosol.jp/ → 「技術情報」 → 「DPA Tips」でもアクセス可能

多数のOracle関連書籍を執筆

オラクルマスター教科書 Gold DBA Oracle Database Administration II : 渡部亮太 , 舛井智行, 峯岸隆一
https://www.amazon.co.jp/dp/479817436X/ (2022年5月27日発売)
オラクルマスター教科書 Silver SQL Oracle Database SQL : 渡部亮太 , 舛井智行, 峯岸隆一
https://www.amazon.co.jp/dp/4798172367/ (2021年9月13日発売)
オラクルマスター教科書 Silver DBA Oracle Database Administration I : 渡部亮太 , 舛井智行 , 杉本篤信 , 西田幸平
https://www.amazon.co.jp/dp/4798166359/ (2021年5月28日発売)
オラクルマスター教科書 Bronze DBA Oracle Database Fundamentals : 渡部亮太 , 岡野平八郎 , 鈴木俊也
https://www.amazon.co.jp/dp/4798166367/ (2020年9月17日発売)
オラクルマスター教科書 Gold Oracle Database 12c : 渡部亮太 , 岡野平八郎
https://www.amazon.co.jp/dp/4798147958/ (2018年8月8日発売)
Oracleの基本～データベース入門から設計/運用の初歩まで : 渡部亮太 , 相川潔 , 日比野峻佑 , 岡野平八郎 , 宮川大地
https://www.amazon.co.jp/dp/4774192511/ (2017年9月22日発売)
プロとしてのOracleアーキテクチャ入門【第2版】 : 渡部亮太
http://www.amazon.co.jp/dp/4797384085/ (2015年4月25日発売)
プロとしてのOracle運用管理入門 : 渡部亮太
http://www.amazon.co.jp/dp/4797355123/ (2009年9月25日発売)
プロとしてのOracleアーキテクチャ入門 : 渡部亮太 , 森坂康人
http://www.amazon.co.jp/dp/4797349808/ (2008年8月22日発売)
プロとしてのOracle入門 : 松下雅, 舛井智行, 古賀加奈
http://www.amazon.co.jp/dp/4797349433/ (2008年10月29日発売)
Oracle Database 10g Oracle Enterprise Manager 逆引きクイックリファレンス : 舛井智行, 青木武士, 松下雅
http://www.amazon.co.jp/dp/4797349433/ (2007年11月27日発売)