How To Optimize Symbian Sql Server 1.1 (japanese)
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View How To Optimize Symbian Sql Server 1.1 (japanese) as PDF for free.
More details
- Words: 4,775
- Pages: 33
Symbian SQL Server 最適化の手順 セキュリティ区分: 参考文書:
External - Symbian
状態: バージョン: 最終改定日:
発行 1.1
SGL.GT0334.067
2008/02/22
チーム/部門 : 作成者: 所有者:
SWE - System Libraries
承認者:
George Sewell
Symbian Persistent Data Services Team Symbian Persistent Data Services Team
配布:
外部
USE FILE > PROPERTIES TO EDIT DOCUMENT INFORMATION
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED. THIS DOCUMENT MAY NOT BE REPRODUCED IN ANY FORM, IN WHOLE OR IN PART, BY ANY MEANS WHATSOEVER, WITHOUT THE WRITTEN PERMISSION OF THE COPYRIGHT HOLDER.
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
CONTENTS 1
はじめに ............................................................................................................................................ 4 1.1 1.2 1.3
2
目的、適用範囲、対象読者.......................................................................................................... 4 確認事項 ..................................................................................................................................... 4 重要な注意事項 ........................................................................................................................... 4
SQL SERVER の概要......................................................................................................................... 5 2.1 SQL SERVER アーキテクチャの概要 ............................................................................................ 5 2.2 SQLITE アーキテクチャの概要..................................................................................................... 6 データベース ファイルのフォーマット ............................................................................... 6 2.2.1 2.2.2 SQLite サブシステム ........................................................................................................... 6 2.2.3 ロールバック ジャーナル ファイル ..................................................................................... 7 2.3 SQL SERVER の制限..................................................................................................................... 8
3
データベース設計の最適化................................................................................................................. 9 3.1 正規化......................................................................................................................................... 9 3.2 大容量のデータ型........................................................................................................................ 9 3.3 列の順序 ..................................................................................................................................... 9 3.4 索引 .......................................................................................................................................... 10 索引のサイズ..................................................................................................................... 10 3.4.1 3.4.2 一般的な索引の問題点を回避する ..................................................................................... 10 3.4.3 正しい索引タイプを選択する ............................................................................................ 11 3.4.4 ROWID を特殊な索引として使用する ............................................................................... 11 3.4.5 複数の索引を文で使用する................................................................................................ 11 3.4.6 索引の選択を最適化する ................................................................................................... 12 3.5 トリガ....................................................................................................................................... 12 3.6 レコード数の影響...................................................................................................................... 12
4
データベース処理の最適化............................................................................................................... 13 4.1 データベース処理を実行するオプション................................................................................... 13 4.2 処理の最適化 – 一般的なヒント................................................................................................. 13 4.2.1 単一 SQL クエリの複数の処理を最適化する ..................................................................... 13 4.2.2 トランザクションを使用して複数の処理を最適化する ...................................................... 13 4.2.3 Prepare 文をキャッシュおよび再利用する ........................................................................ 13 4.2.4 集計関数と単関数を使用する ............................................................................................ 14 4.2.5 EXPLAIN キーワードを使用する ....................................................................................... 14 4.3 SELECT 処理の最適化方法 ....................................................................................................... 14 4.3.1 索引を使用して WHERE 句の評価を高速化する................................................................ 14 4.3.2 索引を使用して ORDER BY の評価を高速化する .............................................................. 16 4.3.3 LONG テキストまたは BLOB データを取得する ............................................................... 16 4.3.4 単一値の取得に最適のインターフェース ........................................................................... 17 4.3.5 RSqlStatement API の最適化 ......................................................................................... 17 4.3.6 サブクエリの中に定数部分式を配置する ........................................................................... 19 4.3.7 LIMIT および OFFSET を使用する .................................................................................... 19 4.4 INSERT 処理の最適化方法 ........................................................................................................ 19 単一レコードの挿入を最適化する ..................................................................................... 20 4.4.1 4.4.2 LONG テキストまたは BLOB データの挿入を最適化する ................................................. 20 複数のレコードの挿入を最適化する .................................................................................. 20 4.4.3 4.5 UPDATE 処理の最適化方法....................................................................................................... 21 4.5.1 単一レコードの更新を最適化する ..................................................................................... 21 4.5.2 LONG テキストまたは BLOB データの更新を最適化する ................................................. 21 4.5.3 複数のレコードの更新を最適化する .................................................................................. 22
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 2 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
4.6 5
ISSUED V1.1
DELETE 処理の最適化方法 ....................................................................................................... 22
RAM 使用率の最適化 ....................................................................................................................... 23 5.1 5.2 5.3 5.4 5.5
6
EXTERNAL - SYMBIAN
データベース接続...................................................................................................................... 23 事前作成された文...................................................................................................................... 23 複数のレコードの削除............................................................................................................... 23 大容量データの操作 .................................................................................................................. 23 一時テーブル............................................................................................................................. 23
データベース構成の最適化............................................................................................................... 25 6.1 6.2 6.3 6.4 6.5
自動バキューム ......................................................................................................................... 25 ページ サイズ............................................................................................................................ 25 キャッシュ サイズ..................................................................................................................... 26 データベースのエンコーディング ............................................................................................. 26 クエリのエンコーディング........................................................................................................ 26
7
マルチユーザー データベースの考慮事項......................................................................................... 27
8
マルチテーブル データベースの考慮事項......................................................................................... 28 8.1 トリガを使用した参照整合性 .................................................................................................... 28 トランザクションでのプログラム チェック ...................................................................... 29 8.1.1 8.1.2 トリガの使用..................................................................................................................... 29 8.1.3 パフォーマンスの考慮事項................................................................................................ 30 8.1.4 結論 .................................................................................................................................. 31 8.2 多対多関係の非正規化............................................................................................................... 31
9
その他の情報 ................................................................................................................................... 33 9.1 9.2
参考資料 ................................................................................................................................... 33 用語集....................................................................................................................................... 33
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 3 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
1 はじめに Symbian のデータベース製品は、DBMS と SQL Server の 2 つのコンポーネントで構成されます。この 2 つのコンポーネントは、内部アーキテクチャおよび外部 API が大きく異なり、機能セットとシステム特性 も違います。 特定のアプリケーションに SQL Server を使用するのが最適であると判断された場合、アプリケーション 開発者は SQL Server を最大限活用するために、そのアプリケーションの高度な開発が可能であることを 望みます。本書は、そうした開発者の活動を支援するために提供されています。 本書では、読者が十分な情報に基づき、DBMS ではなく SQL Server の使用が各自のアプリケーションに 最も適していると判断していることを前提としています。[R2] の指針や情報に基づいて、このような判断 に至る場合もあります。本書の情報を利用して、読者は対象とするアプリケーションの各種要件と使用パ ターンに基づき、SQL Server を最大限活用できる方法でアプリケーションを開発することができます。
1.1
目的、適用範囲、対象読者
本書の主な対象読者は、ライセンシーのアプリケーション開発者です。本書は、それらの開発者が各アプ リケーションにおいてのSQL Server の最適な使用方法を理解できるよう支援することを目的としていま す。本書では、特にデータベースの処理速度に重点を置いて、最大のパフォーマンス ゲインを達成する方 法と、RAM の使用率を最小限に抑えるためのオプションについて解説します。また、読者が各自のアプ リケーションをできる限り効率よく最適化できるよう、一般的なヒントと防止策も提示します。
1.2
確認事項
本書の注意事項には、独自または公共の各種情報源から引用または応用された内容が含まれています。 [R1] ～ [R7] を参照してください。
1.3
重要な注意事項
本書は、Symbian OS v9.3 に同梱される Symbian SQL Server のパフォーマンスを最適化する方法につい て助言するものです。このバージョンの SQL は、SQLite version 3.3.13 を基盤にしています。 読者は、本書の指針や情報が他のSymbian OS リリース関しても絶対的に正しい、あるいは関連性があ ると見なすことはできません。他の Symbian OS リリースの詳細については、各リリースのマニュアルを 参照してください。 読者は、SQLite の公式マニュアルを参照することもできます。 Symbian OS の今後のリリースは、バージョンの異なる SQLite を基盤にします。 ·
Symbian は現在、Symbian OS v9.4 で SQLite version 3.3.17 にアップグレードすることを計画してい ます。このリリースでは、特定のユースケース向けに I/O の最適化機能が提供される予定です。
·
Symbian は現在、将来のリリースに SQLite version 3.4.x を導入することを計画しています。これに は、増分 BLOB I/O のサポートも含まれる予定です。
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 4 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
2 SQL Server の概要 この章では、Symbian の SQL Server の概要を示します。ここでは、最適化に関連する背景情報と、後述 する推奨事項について一定の見通しを示す背景情報を提供することを目的としています。
2.1
SQL Server アーキテクチャの概要
SQL Server は、SQLite [R1] を使用します。SQLite は、オープン ソースのリレーショナル データベース ライブラリで、ACID に対応しています。SQL Server では、標準的な Symbian OS クライアント / サーバ アーキテクチャを介してデータベースにアクセスできます。
クライアント アプリケーション
sql.dl sql.dll l
SQ
Event
SQL クライアント API SQ L
クライアント プロセス サーバ プロセス
sqlserver.e sqlserver.exe
Server
IPC
SQL Server
xe
SQL Server
SQ
Confi
SQLite SQLit
Exten
e
Symbian OS Adaptor
OS サービス
E32
F32
上の図に示すように、クライアント アプリケーションは SQL DLL とリンクします。SQL DLL は、IPC の 境界を越えて SQL Server にデータベース要求を送信するための API を提供します。要求は、データベー ス、テーブル、データ、索引等の作成、削除、変更を目的に行うことができます。 一般に使用されるクライアント API のクラスは次のとおりです。 RSqlDatabase – これは、サーバを介して SQL データベースへのハンドルを提供するクラスで、データ ベースの作成、オープン、クローズ、アタッチ、またはデータベースでの SQL 文の実行に使用できます。 RSqlStatement – このクラスは SQL 文を表し、あらゆるタイプの SQL 文の作成および実行に使用でき ます。 SQL Server は、SQLite API の周辺に薄い API ラッパーを保持し、クライアント要求の処理に使用します。 SQL Server は、複数のクライアント接続を処理でき、Symbian OS プラットフォーム セキュリティ モデ ルを使用してデータベースへのアクセスを制御します。 データベース システムとの下位のファイル インターフェースは、Symbian OS File Server（F32）コンポ ーネントによって提供されます。上の図の Symbian OS Adaptor 層は、SQLite の下位移植層 API と独自 の File Server API 間の適合層です。これにより、SQLite を Symbian OS で実行できます。 SQL Server アーキテクチャの詳細については、[R7] を参照してください。
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 5 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
2.2
EXTERNAL - SYMBIAN
ISSUED V1.1
SQLite アーキテクチャの概要
以下の項では、SQLite アーキテクチャの主な機能を取り上げます。
2.2.1
データベース ファイルのフォーマット
SQLite データベース ファイルは、サイズが固定された 1 つ以上の ページで構成されます。ページとは、 データベースがディスクへの読み書きに使用する単位です。先頭ページには 100 バイトのヘッダが含まれ ます。このヘッダは、ファイルを SQLite データベースとして識別し、ファイル フォーマットのバージョ ン番号、ページ サイズ、自動バキュームの有効 / 無効などの処理パラメータを格納します。 各 SQLite デ ー タ ベ ー ス に は 、 SQLITE_MASTER と い う 特 殊 な テ ー ブ ル も 含 ま れ て い ま す 。 SQLITE_MASTER テーブルには、データベース スキーマと、データベース内に存在するすべてのテーブ ルおよび索引に関する情報（具体的には、テーブルおよび索引を作成する CREATE TABLE 文と CREATE INDEX 文）が格納されます。
2.2.2
SQLite サブシステム
SQLite の主要サブシステムを簡単なブロック図で示します。各サブシステムについて、以下で簡単に説 明します。 2.2.2.1
SQL コンパイラ
SQL コンパイラ
処理対象の SQL 文は、SQL コンパイラ モジュールに送信され ます。このモジュールは、SQL 文の構文解析と意味解析を行い、 各文を実行するバイトコードを生成します。 2.2.2.2
仮想マシン 生成されたバイトコードは、仮想マシンに渡されて評価されま す。仮想マシンは、データベースを B-Tree [R4] のセットと見な します。各テーブルおよび索引に対し、1 つの B-Tree がありま す。B-Tree の作成、読み取り、書き込み、更新、バランス、お よび破棄の論理は、B-Tree モジュールに格納されます。
2.2.2.3
仮想マシン
B-Tree
ページャー
B-Tree モジュール B-Tree モジュールは、データベースで使用されるページのリス トを管理し、ページャー モジュールを使用してデータベース フ ァイルの各ページにアクセスします。
OS インターフェース
各テーブルは個別の B+Tree [R5] であり、テーブルの各レコードは、B+Tree の該当するノード内 の個別のエントリです。B+Tree キーは、レコードの ROWID または INTEGER PRIMARY KEY であり、B+Tree エントリの先頭に格納されます。レコード内のそれ以外のすべての列は、エント リのデータ部に格納されます。レコードにデータ セグメントの容量を超えるバイト数が含まれる 場合は、「オーバフロー」ページが作成されます。オーバフロー ページの作成後、データの大部 分はオーバフロー ページのノードに格納されますが、必ず最小限のデータが元のノード エントリ に格納されます。そのレコードに対応するオーバフロー ページのリンク付きリストを示すポイン タは、元のノード エントリに格納されます。 索引は、索引付けするテーブルの各レコードに対応する B-Tree の該当するノードに、個別の BTree として 1 つのエントリと共に格納されます。B-Tree キーは、索引が付けられた列の値で構 成され、B-Tree エントリの先頭に格納されます。その後ろに、テーブル内の関連するレコードの ROWID が格納されます。索引 B-Tree では、B-Tree エントリのデータ部は使用されません。 2.2.2.4
ページャー モジュール ページャー モジュール（単に「ページャー」とも呼ばれる）は、データベース ファイルからの生 データの読み取りおよび書き込みを管理します。たとえば、読み取りは常に一貫性があって独立 しており、書き込みはアトミック性を確保するよう管理されます。ページャーは、データベース
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 6 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
ファイルを連続した 0 個以上の固定サイズのページと見なします。各ページのデフォルト サイズ は 1,024 バイトです。 ページャーは、最近アクセスされたデータベース ページのキャッシュも保存し、ページ要求への 応答でディスク I/O が必要になる頻度を最小限に抑えます。 2.2.2.5
OS インターフェース 基本ファイル システムとオペレーティング システムとのすべての対話は、OS インターフェース モジュールを介して行われます。したがって、OS インターフェース モジュールのカスタム バー ジョンを実装するだけで、SQLite を新しいプラットフォームに簡単に移植できます。
2.2.3
ロールバック ジャーナル ファイル ロールバック ジャーナルは、データベース ファイルとは別のファイルで、データベースと同じ名 前に接尾辞の「–journal」が付きます。1 つのデータベースにつき、ジャーナル ファイルは 1 つだ け存在します。ロールバック ジャーナルによって、データベースを安全な方法で更新することがで きます。なぜなら、更新が正常に完了しなかった場合、ロールバック ジャーナルはデータベースを 元の状態に「ロールバック」できるからです。データベースを更新する場合、ページャーはまず、 更新するページの現在のコンテンツをジャーナル ファイルに書き込む必要があります。その後、デ ータベース ファイルのページを更新できます。データベースが正常に更新され、ディスクにコミッ トされると、ジャーナル ファイルは削除されます。たとえば、データベースの書き込み処理で停電 が発生した場合は、起動時にジャーナル ファイルの存在が検出されます。これは、更新が正常に完 了しなかったため、ジャーナル ファイルに保存されたデータを使用して、データベースが前の状態 に自動的にロールバックされたことを意味します。 複数のデータベースがアタッチされている（ATTACH コマンドを使用）場合は、各データベースに それぞれ独自のジャーナル ファイルがあり、マスタ ジャーナル ファイルによって個々のジャーナ ル ファイルへの参照が維持されます。
2.2.3.1
ロールバック トランザクション
ロールバックには、次に示す 2 つのシナリオがあります。 単一の暗黙的トランザクション。以下に例を示します。 UPDATE table1 SET i1=2 WHERE i1==1 先述したように、トランザクションが失敗すると、ジャーナル ファイルの存在が検出されるので、 SQLite は自動的にトランザクションのロールバックを処理します。 一括トランザクション、すなわち「BEGIN」から「COMMIT」までのブロックで連続して実行され る単一処理。ロールバックのメカニズムは開発者が提供する必要があります。以下に例を示します。 static void DoRollback(void* aDbHandle) { } RSqlDatabase db; CleanupClosePushL(db); TInt err = db.Open(); // err should be KErrNone CleanupStack::PushL(TCleanupItem(&DoRollback, &db)); db.Exec(_L(“BEGIN”));
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
// start of bulk transaction
EXTERNAL - SYMBIAN
PAGE 7 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
<some code that could leave> db.Exec(_L(“INSERT INTO table1 (i1, i2) values(1, 18)”)); <some code that could leave> db.Exec(_L(“INSERT INTO table1 (i1, i2) values(2, 56)”)); <some code that could leave> db.Exec(_L(“INSERT INTO table1 (i1, i2) values(3, 32)”)); db.Exec(_L(“COMMIT”)); // end of bulk transaction CleanupStack::Pop(); // TCleanupItem CleanupStack::PopAndDestroy(); // db 上記のコードでは、クリーンアップ アイテムがクリーンアップ スタックにプッシュされます。このクリ ーンアップ アイテムは、クリーンアップ関数 DoRollback() とその関数が実行されるオブジェクト、すな わちデータベース接続をカプセル化します。「BEGIN」と「COMMIT」の間で途中終了が発生すると、こ の関数が呼び出され、関数内部でデータベース接続の「ROLLBACK」処理が実行されます。この処理に より、途中終了が発生する前に正常に実行された各 INSERT 処理がロールバックされるため、データベー スは「BEGIN」～「COMMIT」ブロックが実行される前と同じ状態で残されます。
2.3
SQL Server の制限
SQL Server には、次の制限が適用されます。制限の大半はネイティブの SQLite の制限によるもので、そ れ以外は Symbian OS の制限に起因します。たとえば、特定のファイル システムとメディアに対し、 Symbian OS のファイル サイズは 2GB、ファイル バッファ サイズは 256MB に制限されています。 ·
データベース ファイルの最大サイズは 2GB（Symbian OS の制限）
·
最大レコード数は 2^64 -1（SQLite の制限）
·
アタッチデータベースの最大数は 10（SQLite の制限）
·
最大ページ サイズは 32,768 バイト（SQL Server のデフォルト ページ サイズは変更可能、6.1 を参 照）
·
1 つのデータベースにおける最大ページ数は 1,073,741,823（SQLite の制限）
·
SQL クエリで使用するワイルドカード演算子「?」の最大数は 999（SQLite の制限）
·
テーブル、列、索引、トリガ、またはビューの名前にサイズの制限はありません。
·
複合索引の列数に制限はありませんが、列数が 30 を超える索引は、クエリの最適化（たとえば、 SELECT 文の WHERE 句に索引を使用するなど）に使用できません（SQLite の制限）。
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 8 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
3 データベース設計の最適化 この章では、データベースのパフォーマンスを強化するためにデータベース スキーマ レベルで実行でき る各種の最適化について説明します。
3.1
正規化
データベースの正規化は、データの重複を低減するためにリレーショナル データベースで利用されている 手法で、これによってデータの整合性の問題を引き起こす挿入、削除、更新の異常が発生する可能性を除 去します。冗長データを維持する方が適切な場合もあるため、ケースバイケースの原則で判断する必要が あります。 テーブル データに適用できる正規化にはいくつかの段階があり、この段階のことを「正規形」という用語 で表します。「第 1 正規形」（「1NF」）は、最も初期の段階の形式です。「テーブルの各値はアトミッ ク性を確保し、レコードは一意に識別できなければならない」と規定することで、テーブル「セル」内の グループを繰り返す可能性を除去します。「第 2 正規形」（「2NF」）に適合する基準は、1NF の基準の 上位集合です。一般に、必要なテーブル数が多いほど、データを正規化する必要性が高くなります。 正規化と非正規化は、マルチテーブル データベース環境で一般的に利用されるコンセプトです（8 章を参 照）。
3.2
大容量のデータ型
LONG テキストや BLOB（Binary Large Object）データなど大容量のデータ型をテーブルに含める必要が あるかどうかを考慮することは重要です。たとえば、検索中に多数の BLOB を確認する必要がある場合、 大容量の BLOB があると検索時間に大きく影響します。 BLOB が含まれたレコードを格納するためにオーバフロー ページが必要になると、状況はさらに悪化しま す。オーバフロー ページは、レコード サイズがページ サイズが超えると必要になり、これは大容量の BLOB がレコードに含まれている場合にはよくあることです。その場合、オーバフロー ページのデータも 取得して確認する必要があるため、検索速度はさらに遅くなります。 さらに、レコードのオーバフロー ページを作成または取得する必要がある場合、そのレコードを挿入また は更新する時間が長くなります。また、そのようなレコードを取得することは望ましくありません。なぜ なら、レコードを再構築するために、そのレコードに対応するページのリンク付きリストをすべて確認す る必要があり、これは効率的な処理ではないからです。 上記の問題を踏まえて、BLOB データが必要な場合は、次のいずれかのアプローチを検討する必要があり ます。
1. BLOB データを、共通キーによって元のテーブルにリンクされた個別のテーブルに分離する。 2. BLOB 列をテーブル スキーマの最終列として配置する。 上記の推奨事項は、レコードの取得中、それほど頻繁に BLOB 列を選択する必要がない場合に有益です。 大容量 BLOB をデータベースの外部（別個のファイル）に格納し、ファイルの場所の参照をテーブルの値 として保持しておくと、最適化をさらに進めることができます。SQLite では、BLOB の増分書き込みまた は増分読み取りができず、BLOB 全体を一度に読み取りまたは書き込みする必要があるため、BOLB を外 部に分離するこの方法は便利です。たとえば、10MB の BLOB の最後に 100 バイトを付加するには、 SQL Server は BLOB 全体を読み取り、100 バイトを付加し、もう一度ディスクに 新しい BLOB を書き戻 す必要があります。別のファイルに BLOB を配置しておくと、10 MB の BLOB にアクセスしなくても 100 バイトをすばやく効率的に付加できます。
3.3
列の順序
スペースの効率上の理由から、各レコードは圧縮形式で格納されます。これにより、各ページにより多く のレコードを割り当てられるので、ディスク I/O も最小限に抑えられます。
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 9 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
ただし、データベースがテーブルの n 番目の列の値を取得するには、まずその前にあるすべての列を解凍 しなければならないという欠点があります。したがって、テーブル内のアクセス頻度が最も高い列が、テ ーブル スキーマの先頭にあるのが最適の状態です。 これは、整数列などのように容量のごく小さいデータを格納しているテーブルの列にも当てはまります。 このような列は、テーブルの先頭近くに配置するのが適しています。先述したように、BLOB 列などの大 容量のデータ列は、できる限りテーブルの末尾に配置します。
3.4
索引
索引は、データベースからのレコードのルックアップ速度を大幅に高める上で極めて重要です。なぜなら、 索引を使用したルックアップでは、線形のフル テーブル スキャンではなく、バイナリ検索技術が使用さ れるからです。 アプリケーションを開発する場合、データ ルックアップの速度を高める必要性が明確になるまでは、索引 を作成しないことをお勧めします。なぜなら、適切にあるいは実用本位で使用されないと、索引はデータ ベースのパフォーマンスを全体的に低下させるからです。索引を追加すると、ルックアップ速度は大幅に 向上しますが、レコードの挿入または削除にかかる時間が増大する可能性があります。また、更新する列 に索引が付いていると、レコードの更新にかかる時間も増大する場合があります。
3.4.1
索引のサイズ
SQLite では、索引は SQLite 自身の B-Tree に格納され、テーブル データから切り離されています。BTree の葉 1 枚につき、最低 4 つのエントリがあります。B-Tree のエントリの最大サイズは約 230 バイト です。サイズの大きいデータが格納されている列に索引が付いていると、索引 B-Tree がオーバフロー ペ ージを使用する原因となります。この状態は、先述した理由により、できる限り避ける必要があります。
3.4.2
一般的な索引の問題点を回避する
3.4.2.1
明示的および暗黙的索引
通常、索引は「CREATE INDEX」SQL コマンドを使用して明示的に作成されます。以下に例を示します。 ‘CREATE INDEX idx1 on Table1(i3)’ ここで i3 は、テーブル Table1 の 3 番目の列です。 代わりに、テーブル スキーマ内の特定タイプの列を指定すると、索引は暗黙的に作成されます。この方法 は、列が UNIQUE または PRIMARY KEY として指定されている場合に使用されます。列が INTEGER PRIMARY KEY として宣言されている場合、新しい索引は作成されませんが、指定された列は組み込み ROWID 索引の別名として扱われます（3.4.3 を参照）。 明示的索引が暗黙的索引と重複しないよう注意してください。 3.4.2.2
索引によるリソースへの影響
新しい索引の作成にはコストが伴うことを認識しておくのは重要です。 ·
新しい索引は、データベース ファイルにそれぞれの B-Tree を格納できる追加スペースを必要 とします。作成される索引が多いほど、テーブル データは付加されずにデータベース ファイ ルが大きくなります。重複や冗長な索引を回避するよう、十分注意する必要があります。以 下に例を示します。 o 冗長な索引 CREATE TABLE Table1(i1 INTEGER, i2 INTEGER) CREATE INDEX idx1 on Table1(i1) CREATE INDEX idx2 on Table1(i1,i2) o 重複した索引 CREATE TABLE Table1(ID INTEGER UNIQUE); CREATE INDEX idx1 on Table1(ID)
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 10 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
3.4.3
EXTERNAL - SYMBIAN
ISSUED V1.1
·
BLOB 列などの大容量データが含まれた列には索引を作成しないようにします。これにより、 索引 B-Tree でオーバフロー ページの使用が必要になるのを防ぎます。
·
すべての挿入および削除処理は、データ テーブルの B-Tree だけでなく、そのテーブルの各索 引の B-Tree も変更します。したがって、索引の数が増えるほど、挿入および削除処理のパフ ォーマンスは低下します。これは、索引付きの列を更新する場合にも当てはまります。
·
索引が重複していると、オプティマイザが使用する索引の選択を誤る可能性が高くなります。 たとえば、上記の冗長な索引で、クエリで使用する列として i1 が指定されている場合、オプ ティマイザは idx2 を選択する可能性があります。
正しい索引タイプを選択する
索引にはさまざまなタイプがあり、索引を選択する前に、各タイプの長所と短所を考慮することが重要で す。使用できる各種の索引と、それらに最も適した環境を次に示します。 ·
標準索引 – 標準索引は、順序付けられた値のリストで、重複する値が許可されています。文 の WHERE 句の検索フィールドで使用される索引として、デフォルトで選択されます。
·
一意索引 - 一意索引は、順序付けられた値のリストで、重複する値は許可されません。一意 索引は、データの整合性を維持するために、テーブルの列または列のセットに制約を適用す る場合に使用できます。テーブル内には、プライマリ キー索引は 1 つしか含めることができ ませんが、一意索引は複数含めることができます。一意性に関する制約を設定すると、一意 索引に影響する処理は、標準索引の場合よりも若干遅くなります。これは、一意性の制約が 守られていることを確認する必要があるためです。
·
プライマリ キー索引 – データの正規化では、テーブルにプライマリ キーが含まれていること が重要です。プライマリ キーの値は、各レコードに対して一意の値です。対象とするテーブ ルによっては、複数の列で一意のプライマリ キーを構成する方が適している場合もあります が、通常は単一の整数プライマリ キーを使用するのが最良の方法です。単一の整数プライマ リ キーは、組み込み ROWID 索引の別名として指定されるので、索引のフットプリントが最 も小さいからです。さらに、索引キーの値は個別の B-Tree ではなくテーブルの B+Tree のエ ントリに格納されるため、最高のパフォーマンスを得ることができます。プライマリ キーの 短所は、値が一意であり null でないことを確実にするために必要な確認があるため、プライ マリ キーに関する処理の速度が低下することです。
·
マルチキー索引 – この索引は複合索引とも呼ばれ、複数の列で構成されます。マルチキー索 引では、列の順序を指定する際に注意が必要です。たとえば、列のいずれかが一意の列であ る場合、その索引では、一意の列の後ろに一意でない列を配置することはできません。
3.4.4 ROWID を特殊な索引として使用する 各テーブルの各レコードは、一意の 64 ビット整数型の値 ROWID を持ちます。ROWID は、テーブル デ ータを格納する B+Tree のキーであり、同じテーブルのその他のすべてのレコードにおいて一意です。一 般に、ROWID を使用するデータ検索は、他の検索方法の約 2 倍高速です。これは、ROWID 索引キーと 共にレコード データが格納されているからです。 ROWID には、特殊な列名である ROWID、_ROWID_ または OID のいずれかを使用してアクセスできま す。あるいは、テーブルの作成時に整数列のキーワード INTEGER PRIMARY KEY を使用して、そのテー ブルの整数列を ROWID の別名として宣言することもできます。
3.4.5
複数の索引を文で使用する
オプティマイザは、文を評価する際、1 つのテーブルに対して 1 つの索引だけを使用します。したがって、 複数の列を含む WHERE 句では、マルチキー索引を使用すると便利な場合があります。それ以外の場合、 索引は 1 つしか使用されません。 たとえば、次のような文があります。
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 11 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
SELECT * FROM Table1 WHERE a = 5 and b < 10; 列 a および b に、以下の索引がそれぞれ作成されます。 CREATE INDEX idx1 ON Table1(a); CREATE INDEX idx2 ON Table1(b); オプティマイザによって索引が 1 つだけ使用され、他の索引は不要になります。 以下のように、a と b 両方の列を含むマルチキー索引を使用する方法もあります。 CREATE INDEX idx3 ON Table1(a,b); この場合、idx3（a と b の複合列に作成されたマルチキー索引）が使用され、列 a および b を最適の方法 で評価に使用することができます。マルチキー索引を使用する場合、WHERE 句で指定されたすべての索 引列は = または IN 演算子を付けて使用する必要があります。ただし、不等演算子を付けて使用できると 指定されている右端の列は除きます。
3.4.6
索引の選択を最適化する
先述のように、文の実行に複数の索引を使用できる場合、使用する索引はオプティマイザによって選択さ れます。この選択は、必ずしも明確、または、予想通りであるわけではありません。 オプティマイザを支援するために、ANALYZE コマンドを使用して、存在する索引に関する統計情報を格 納するテーブル「sqlite_stat1」を作成できます。この情報は、より適切な索引を選択するために、クエリ オプティマイザによって使用されます。
3.5
トリガ
トリガとは、事前に指定されたイベントが発生すると自動的に起動されるデータベース処理です。たとえ ば、テーブル X で削除イベントが発生するとテーブル Y からレコードを削除するトリガを作成できます。 トリガは、更新、削除、または挿入イベントで作成されます。トリガを使用して、外部キーの制約を適用 することができます。8.1.2 を参照してください。
3.6
レコード数の影響
索引を使わずにデータベースでレコードの場所を特定するには、フル テーブル スキャンを実行する必要 があります。当然、この処理の完了に要する時間は、テーブルのレコード数に比例して増大します。 検索列に索引が付いていると、ルックアップ時間は大幅に向上します。大量のレコードを含むテーブルで は、索引の使用はアプリケーションのパフォーマンスにとってきわめて重要です。 一般に、テーブルのレコード数が少ないほど、処理の実行時間は短くなります。したがって、不要になっ たレコードをただちにテーブルから削除することで、データ処理における最適のパフォーマンスを実現で きます。
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 12 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
4 データベース処理の最適化 4.1
データベース処理を実行するオプション
データベース処理の最も簡単な実行方法は、RSqlDatabase::Exec() を使用して SQL クエリを実行す ることです。これは、ほとんどの単一 SQL クエリ処理に当てはまりますが、複数の SQL クエリ処理など その他の環境下では、別の API を使用してパフォーマンスを高めることが推奨されます。以下の項では、 さまざまな処理を最適化する方法について説明します。 ここに示すコード例で、疑似コード「」は、RSqlDatabase のインスタンスを表しています。
4.2
処理の最適化 – 一般的なヒント
この項では、処理を最適化するための指針とヒントを示します。
4.2.1
単一 SQL クエリの複数の処理を最適化する
ここでは、1 つの処理を複数回実行し、そのたびに異なるデータ値を使用する場合を対象としています。 複数の文を直接実行するのではなく、バインド変数を含む 1 つの文を使用します。次に例を示します。 RSqlStatement stmt; CleanupClosePushL(stmt); CleanupStack::PushL(TCleanupItem(&DoRollback, &)); .Exec(_L(“BEGIN”)); stmt.PrepareL(, _L(“INSERT INTO values(?,?,… )”)); for (TUint i=0; i < <no_of_rows_to_insert>; ++i) {
External - Symbian
状態: バージョン: 最終改定日:
発行 1.1
SGL.GT0334.067
2008/02/22
チーム/部門 : 作成者: 所有者:
SWE - System Libraries
承認者:
George Sewell
Symbian Persistent Data Services Team Symbian Persistent Data Services Team
配布:
外部
USE FILE > PROPERTIES TO EDIT DOCUMENT INFORMATION
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED. THIS DOCUMENT MAY NOT BE REPRODUCED IN ANY FORM, IN WHOLE OR IN PART, BY ANY MEANS WHATSOEVER, WITHOUT THE WRITTEN PERMISSION OF THE COPYRIGHT HOLDER.
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
CONTENTS 1
はじめに ............................................................................................................................................ 4 1.1 1.2 1.3
2
目的、適用範囲、対象読者.......................................................................................................... 4 確認事項 ..................................................................................................................................... 4 重要な注意事項 ........................................................................................................................... 4
SQL SERVER の概要......................................................................................................................... 5 2.1 SQL SERVER アーキテクチャの概要 ............................................................................................ 5 2.2 SQLITE アーキテクチャの概要..................................................................................................... 6 データベース ファイルのフォーマット ............................................................................... 6 2.2.1 2.2.2 SQLite サブシステム ........................................................................................................... 6 2.2.3 ロールバック ジャーナル ファイル ..................................................................................... 7 2.3 SQL SERVER の制限..................................................................................................................... 8
3
データベース設計の最適化................................................................................................................. 9 3.1 正規化......................................................................................................................................... 9 3.2 大容量のデータ型........................................................................................................................ 9 3.3 列の順序 ..................................................................................................................................... 9 3.4 索引 .......................................................................................................................................... 10 索引のサイズ..................................................................................................................... 10 3.4.1 3.4.2 一般的な索引の問題点を回避する ..................................................................................... 10 3.4.3 正しい索引タイプを選択する ............................................................................................ 11 3.4.4 ROWID を特殊な索引として使用する ............................................................................... 11 3.4.5 複数の索引を文で使用する................................................................................................ 11 3.4.6 索引の選択を最適化する ................................................................................................... 12 3.5 トリガ....................................................................................................................................... 12 3.6 レコード数の影響...................................................................................................................... 12
4
データベース処理の最適化............................................................................................................... 13 4.1 データベース処理を実行するオプション................................................................................... 13 4.2 処理の最適化 – 一般的なヒント................................................................................................. 13 4.2.1 単一 SQL クエリの複数の処理を最適化する ..................................................................... 13 4.2.2 トランザクションを使用して複数の処理を最適化する ...................................................... 13 4.2.3 Prepare 文をキャッシュおよび再利用する ........................................................................ 13 4.2.4 集計関数と単関数を使用する ............................................................................................ 14 4.2.5 EXPLAIN キーワードを使用する ....................................................................................... 14 4.3 SELECT 処理の最適化方法 ....................................................................................................... 14 4.3.1 索引を使用して WHERE 句の評価を高速化する................................................................ 14 4.3.2 索引を使用して ORDER BY の評価を高速化する .............................................................. 16 4.3.3 LONG テキストまたは BLOB データを取得する ............................................................... 16 4.3.4 単一値の取得に最適のインターフェース ........................................................................... 17 4.3.5 RSqlStatement API の最適化 ......................................................................................... 17 4.3.6 サブクエリの中に定数部分式を配置する ........................................................................... 19 4.3.7 LIMIT および OFFSET を使用する .................................................................................... 19 4.4 INSERT 処理の最適化方法 ........................................................................................................ 19 単一レコードの挿入を最適化する ..................................................................................... 20 4.4.1 4.4.2 LONG テキストまたは BLOB データの挿入を最適化する ................................................. 20 複数のレコードの挿入を最適化する .................................................................................. 20 4.4.3 4.5 UPDATE 処理の最適化方法....................................................................................................... 21 4.5.1 単一レコードの更新を最適化する ..................................................................................... 21 4.5.2 LONG テキストまたは BLOB データの更新を最適化する ................................................. 21 4.5.3 複数のレコードの更新を最適化する .................................................................................. 22
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 2 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
4.6 5
ISSUED V1.1
DELETE 処理の最適化方法 ....................................................................................................... 22
RAM 使用率の最適化 ....................................................................................................................... 23 5.1 5.2 5.3 5.4 5.5
6
EXTERNAL - SYMBIAN
データベース接続...................................................................................................................... 23 事前作成された文...................................................................................................................... 23 複数のレコードの削除............................................................................................................... 23 大容量データの操作 .................................................................................................................. 23 一時テーブル............................................................................................................................. 23
データベース構成の最適化............................................................................................................... 25 6.1 6.2 6.3 6.4 6.5
自動バキューム ......................................................................................................................... 25 ページ サイズ............................................................................................................................ 25 キャッシュ サイズ..................................................................................................................... 26 データベースのエンコーディング ............................................................................................. 26 クエリのエンコーディング........................................................................................................ 26
7
マルチユーザー データベースの考慮事項......................................................................................... 27
8
マルチテーブル データベースの考慮事項......................................................................................... 28 8.1 トリガを使用した参照整合性 .................................................................................................... 28 トランザクションでのプログラム チェック ...................................................................... 29 8.1.1 8.1.2 トリガの使用..................................................................................................................... 29 8.1.3 パフォーマンスの考慮事項................................................................................................ 30 8.1.4 結論 .................................................................................................................................. 31 8.2 多対多関係の非正規化............................................................................................................... 31
9
その他の情報 ................................................................................................................................... 33 9.1 9.2
参考資料 ................................................................................................................................... 33 用語集....................................................................................................................................... 33
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 3 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
1 はじめに Symbian のデータベース製品は、DBMS と SQL Server の 2 つのコンポーネントで構成されます。この 2 つのコンポーネントは、内部アーキテクチャおよび外部 API が大きく異なり、機能セットとシステム特性 も違います。 特定のアプリケーションに SQL Server を使用するのが最適であると判断された場合、アプリケーション 開発者は SQL Server を最大限活用するために、そのアプリケーションの高度な開発が可能であることを 望みます。本書は、そうした開発者の活動を支援するために提供されています。 本書では、読者が十分な情報に基づき、DBMS ではなく SQL Server の使用が各自のアプリケーションに 最も適していると判断していることを前提としています。[R2] の指針や情報に基づいて、このような判断 に至る場合もあります。本書の情報を利用して、読者は対象とするアプリケーションの各種要件と使用パ ターンに基づき、SQL Server を最大限活用できる方法でアプリケーションを開発することができます。
1.1
目的、適用範囲、対象読者
本書の主な対象読者は、ライセンシーのアプリケーション開発者です。本書は、それらの開発者が各アプ リケーションにおいてのSQL Server の最適な使用方法を理解できるよう支援することを目的としていま す。本書では、特にデータベースの処理速度に重点を置いて、最大のパフォーマンス ゲインを達成する方 法と、RAM の使用率を最小限に抑えるためのオプションについて解説します。また、読者が各自のアプ リケーションをできる限り効率よく最適化できるよう、一般的なヒントと防止策も提示します。
1.2
確認事項
本書の注意事項には、独自または公共の各種情報源から引用または応用された内容が含まれています。 [R1] ～ [R7] を参照してください。
1.3
重要な注意事項
本書は、Symbian OS v9.3 に同梱される Symbian SQL Server のパフォーマンスを最適化する方法につい て助言するものです。このバージョンの SQL は、SQLite version 3.3.13 を基盤にしています。 読者は、本書の指針や情報が他のSymbian OS リリース関しても絶対的に正しい、あるいは関連性があ ると見なすことはできません。他の Symbian OS リリースの詳細については、各リリースのマニュアルを 参照してください。 読者は、SQLite の公式マニュアルを参照することもできます。 Symbian OS の今後のリリースは、バージョンの異なる SQLite を基盤にします。 ·
Symbian は現在、Symbian OS v9.4 で SQLite version 3.3.17 にアップグレードすることを計画してい ます。このリリースでは、特定のユースケース向けに I/O の最適化機能が提供される予定です。
·
Symbian は現在、将来のリリースに SQLite version 3.4.x を導入することを計画しています。これに は、増分 BLOB I/O のサポートも含まれる予定です。
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 4 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
2 SQL Server の概要 この章では、Symbian の SQL Server の概要を示します。ここでは、最適化に関連する背景情報と、後述 する推奨事項について一定の見通しを示す背景情報を提供することを目的としています。
2.1
SQL Server アーキテクチャの概要
SQL Server は、SQLite [R1] を使用します。SQLite は、オープン ソースのリレーショナル データベース ライブラリで、ACID に対応しています。SQL Server では、標準的な Symbian OS クライアント / サーバ アーキテクチャを介してデータベースにアクセスできます。
クライアント アプリケーション
sql.dl sql.dll l
SQ
Event
SQL クライアント API SQ L
クライアント プロセス サーバ プロセス
sqlserver.e sqlserver.exe
Server
IPC
SQL Server
xe
SQL Server
SQ
Confi
SQLite SQLit
Exten
e
Symbian OS Adaptor
OS サービス
E32
F32
上の図に示すように、クライアント アプリケーションは SQL DLL とリンクします。SQL DLL は、IPC の 境界を越えて SQL Server にデータベース要求を送信するための API を提供します。要求は、データベー ス、テーブル、データ、索引等の作成、削除、変更を目的に行うことができます。 一般に使用されるクライアント API のクラスは次のとおりです。 RSqlDatabase – これは、サーバを介して SQL データベースへのハンドルを提供するクラスで、データ ベースの作成、オープン、クローズ、アタッチ、またはデータベースでの SQL 文の実行に使用できます。 RSqlStatement – このクラスは SQL 文を表し、あらゆるタイプの SQL 文の作成および実行に使用でき ます。 SQL Server は、SQLite API の周辺に薄い API ラッパーを保持し、クライアント要求の処理に使用します。 SQL Server は、複数のクライアント接続を処理でき、Symbian OS プラットフォーム セキュリティ モデ ルを使用してデータベースへのアクセスを制御します。 データベース システムとの下位のファイル インターフェースは、Symbian OS File Server（F32）コンポ ーネントによって提供されます。上の図の Symbian OS Adaptor 層は、SQLite の下位移植層 API と独自 の File Server API 間の適合層です。これにより、SQLite を Symbian OS で実行できます。 SQL Server アーキテクチャの詳細については、[R7] を参照してください。
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 5 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
2.2
EXTERNAL - SYMBIAN
ISSUED V1.1
SQLite アーキテクチャの概要
以下の項では、SQLite アーキテクチャの主な機能を取り上げます。
2.2.1
データベース ファイルのフォーマット
SQLite データベース ファイルは、サイズが固定された 1 つ以上の ページで構成されます。ページとは、 データベースがディスクへの読み書きに使用する単位です。先頭ページには 100 バイトのヘッダが含まれ ます。このヘッダは、ファイルを SQLite データベースとして識別し、ファイル フォーマットのバージョ ン番号、ページ サイズ、自動バキュームの有効 / 無効などの処理パラメータを格納します。 各 SQLite デ ー タ ベ ー ス に は 、 SQLITE_MASTER と い う 特 殊 な テ ー ブ ル も 含 ま れ て い ま す 。 SQLITE_MASTER テーブルには、データベース スキーマと、データベース内に存在するすべてのテーブ ルおよび索引に関する情報（具体的には、テーブルおよび索引を作成する CREATE TABLE 文と CREATE INDEX 文）が格納されます。
2.2.2
SQLite サブシステム
SQLite の主要サブシステムを簡単なブロック図で示します。各サブシステムについて、以下で簡単に説 明します。 2.2.2.1
SQL コンパイラ
SQL コンパイラ
処理対象の SQL 文は、SQL コンパイラ モジュールに送信され ます。このモジュールは、SQL 文の構文解析と意味解析を行い、 各文を実行するバイトコードを生成します。 2.2.2.2
仮想マシン 生成されたバイトコードは、仮想マシンに渡されて評価されま す。仮想マシンは、データベースを B-Tree [R4] のセットと見な します。各テーブルおよび索引に対し、1 つの B-Tree がありま す。B-Tree の作成、読み取り、書き込み、更新、バランス、お よび破棄の論理は、B-Tree モジュールに格納されます。
2.2.2.3
仮想マシン
B-Tree
ページャー
B-Tree モジュール B-Tree モジュールは、データベースで使用されるページのリス トを管理し、ページャー モジュールを使用してデータベース フ ァイルの各ページにアクセスします。
OS インターフェース
各テーブルは個別の B+Tree [R5] であり、テーブルの各レコードは、B+Tree の該当するノード内 の個別のエントリです。B+Tree キーは、レコードの ROWID または INTEGER PRIMARY KEY であり、B+Tree エントリの先頭に格納されます。レコード内のそれ以外のすべての列は、エント リのデータ部に格納されます。レコードにデータ セグメントの容量を超えるバイト数が含まれる 場合は、「オーバフロー」ページが作成されます。オーバフロー ページの作成後、データの大部 分はオーバフロー ページのノードに格納されますが、必ず最小限のデータが元のノード エントリ に格納されます。そのレコードに対応するオーバフロー ページのリンク付きリストを示すポイン タは、元のノード エントリに格納されます。 索引は、索引付けするテーブルの各レコードに対応する B-Tree の該当するノードに、個別の BTree として 1 つのエントリと共に格納されます。B-Tree キーは、索引が付けられた列の値で構 成され、B-Tree エントリの先頭に格納されます。その後ろに、テーブル内の関連するレコードの ROWID が格納されます。索引 B-Tree では、B-Tree エントリのデータ部は使用されません。 2.2.2.4
ページャー モジュール ページャー モジュール（単に「ページャー」とも呼ばれる）は、データベース ファイルからの生 データの読み取りおよび書き込みを管理します。たとえば、読み取りは常に一貫性があって独立 しており、書き込みはアトミック性を確保するよう管理されます。ページャーは、データベース
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 6 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
ファイルを連続した 0 個以上の固定サイズのページと見なします。各ページのデフォルト サイズ は 1,024 バイトです。 ページャーは、最近アクセスされたデータベース ページのキャッシュも保存し、ページ要求への 応答でディスク I/O が必要になる頻度を最小限に抑えます。 2.2.2.5
OS インターフェース 基本ファイル システムとオペレーティング システムとのすべての対話は、OS インターフェース モジュールを介して行われます。したがって、OS インターフェース モジュールのカスタム バー ジョンを実装するだけで、SQLite を新しいプラットフォームに簡単に移植できます。
2.2.3
ロールバック ジャーナル ファイル ロールバック ジャーナルは、データベース ファイルとは別のファイルで、データベースと同じ名 前に接尾辞の「–journal」が付きます。1 つのデータベースにつき、ジャーナル ファイルは 1 つだ け存在します。ロールバック ジャーナルによって、データベースを安全な方法で更新することがで きます。なぜなら、更新が正常に完了しなかった場合、ロールバック ジャーナルはデータベースを 元の状態に「ロールバック」できるからです。データベースを更新する場合、ページャーはまず、 更新するページの現在のコンテンツをジャーナル ファイルに書き込む必要があります。その後、デ ータベース ファイルのページを更新できます。データベースが正常に更新され、ディスクにコミッ トされると、ジャーナル ファイルは削除されます。たとえば、データベースの書き込み処理で停電 が発生した場合は、起動時にジャーナル ファイルの存在が検出されます。これは、更新が正常に完 了しなかったため、ジャーナル ファイルに保存されたデータを使用して、データベースが前の状態 に自動的にロールバックされたことを意味します。 複数のデータベースがアタッチされている（ATTACH コマンドを使用）場合は、各データベースに それぞれ独自のジャーナル ファイルがあり、マスタ ジャーナル ファイルによって個々のジャーナ ル ファイルへの参照が維持されます。
2.2.3.1
ロールバック トランザクション
ロールバックには、次に示す 2 つのシナリオがあります。 単一の暗黙的トランザクション。以下に例を示します。 UPDATE table1 SET i1=2 WHERE i1==1 先述したように、トランザクションが失敗すると、ジャーナル ファイルの存在が検出されるので、 SQLite は自動的にトランザクションのロールバックを処理します。 一括トランザクション、すなわち「BEGIN」から「COMMIT」までのブロックで連続して実行され る単一処理。ロールバックのメカニズムは開発者が提供する必要があります。以下に例を示します。 static void DoRollback(void* aDbHandle) {
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
// start of bulk transaction
EXTERNAL - SYMBIAN
PAGE 7 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
<some code that could leave> db.Exec(_L(“INSERT INTO table1 (i1, i2) values(1, 18)”)); <some code that could leave> db.Exec(_L(“INSERT INTO table1 (i1, i2) values(2, 56)”)); <some code that could leave> db.Exec(_L(“INSERT INTO table1 (i1, i2) values(3, 32)”)); db.Exec(_L(“COMMIT”)); // end of bulk transaction CleanupStack::Pop(); // TCleanupItem CleanupStack::PopAndDestroy(); // db 上記のコードでは、クリーンアップ アイテムがクリーンアップ スタックにプッシュされます。このクリ ーンアップ アイテムは、クリーンアップ関数 DoRollback() とその関数が実行されるオブジェクト、すな わちデータベース接続をカプセル化します。「BEGIN」と「COMMIT」の間で途中終了が発生すると、こ の関数が呼び出され、関数内部でデータベース接続の「ROLLBACK」処理が実行されます。この処理に より、途中終了が発生する前に正常に実行された各 INSERT 処理がロールバックされるため、データベー スは「BEGIN」～「COMMIT」ブロックが実行される前と同じ状態で残されます。
2.3
SQL Server の制限
SQL Server には、次の制限が適用されます。制限の大半はネイティブの SQLite の制限によるもので、そ れ以外は Symbian OS の制限に起因します。たとえば、特定のファイル システムとメディアに対し、 Symbian OS のファイル サイズは 2GB、ファイル バッファ サイズは 256MB に制限されています。 ·
データベース ファイルの最大サイズは 2GB（Symbian OS の制限）
·
最大レコード数は 2^64 -1（SQLite の制限）
·
アタッチデータベースの最大数は 10（SQLite の制限）
·
最大ページ サイズは 32,768 バイト（SQL Server のデフォルト ページ サイズは変更可能、6.1 を参 照）
·
1 つのデータベースにおける最大ページ数は 1,073,741,823（SQLite の制限）
·
SQL クエリで使用するワイルドカード演算子「?」の最大数は 999（SQLite の制限）
·
テーブル、列、索引、トリガ、またはビューの名前にサイズの制限はありません。
·
複合索引の列数に制限はありませんが、列数が 30 を超える索引は、クエリの最適化（たとえば、 SELECT 文の WHERE 句に索引を使用するなど）に使用できません（SQLite の制限）。
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 8 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
3 データベース設計の最適化 この章では、データベースのパフォーマンスを強化するためにデータベース スキーマ レベルで実行でき る各種の最適化について説明します。
3.1
正規化
データベースの正規化は、データの重複を低減するためにリレーショナル データベースで利用されている 手法で、これによってデータの整合性の問題を引き起こす挿入、削除、更新の異常が発生する可能性を除 去します。冗長データを維持する方が適切な場合もあるため、ケースバイケースの原則で判断する必要が あります。 テーブル データに適用できる正規化にはいくつかの段階があり、この段階のことを「正規形」という用語 で表します。「第 1 正規形」（「1NF」）は、最も初期の段階の形式です。「テーブルの各値はアトミッ ク性を確保し、レコードは一意に識別できなければならない」と規定することで、テーブル「セル」内の グループを繰り返す可能性を除去します。「第 2 正規形」（「2NF」）に適合する基準は、1NF の基準の 上位集合です。一般に、必要なテーブル数が多いほど、データを正規化する必要性が高くなります。 正規化と非正規化は、マルチテーブル データベース環境で一般的に利用されるコンセプトです（8 章を参 照）。
3.2
大容量のデータ型
LONG テキストや BLOB（Binary Large Object）データなど大容量のデータ型をテーブルに含める必要が あるかどうかを考慮することは重要です。たとえば、検索中に多数の BLOB を確認する必要がある場合、 大容量の BLOB があると検索時間に大きく影響します。 BLOB が含まれたレコードを格納するためにオーバフロー ページが必要になると、状況はさらに悪化しま す。オーバフロー ページは、レコード サイズがページ サイズが超えると必要になり、これは大容量の BLOB がレコードに含まれている場合にはよくあることです。その場合、オーバフロー ページのデータも 取得して確認する必要があるため、検索速度はさらに遅くなります。 さらに、レコードのオーバフロー ページを作成または取得する必要がある場合、そのレコードを挿入また は更新する時間が長くなります。また、そのようなレコードを取得することは望ましくありません。なぜ なら、レコードを再構築するために、そのレコードに対応するページのリンク付きリストをすべて確認す る必要があり、これは効率的な処理ではないからです。 上記の問題を踏まえて、BLOB データが必要な場合は、次のいずれかのアプローチを検討する必要があり ます。
1. BLOB データを、共通キーによって元のテーブルにリンクされた個別のテーブルに分離する。 2. BLOB 列をテーブル スキーマの最終列として配置する。 上記の推奨事項は、レコードの取得中、それほど頻繁に BLOB 列を選択する必要がない場合に有益です。 大容量 BLOB をデータベースの外部（別個のファイル）に格納し、ファイルの場所の参照をテーブルの値 として保持しておくと、最適化をさらに進めることができます。SQLite では、BLOB の増分書き込みまた は増分読み取りができず、BLOB 全体を一度に読み取りまたは書き込みする必要があるため、BOLB を外 部に分離するこの方法は便利です。たとえば、10MB の BLOB の最後に 100 バイトを付加するには、 SQL Server は BLOB 全体を読み取り、100 バイトを付加し、もう一度ディスクに 新しい BLOB を書き戻 す必要があります。別のファイルに BLOB を配置しておくと、10 MB の BLOB にアクセスしなくても 100 バイトをすばやく効率的に付加できます。
3.3
列の順序
スペースの効率上の理由から、各レコードは圧縮形式で格納されます。これにより、各ページにより多く のレコードを割り当てられるので、ディスク I/O も最小限に抑えられます。
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 9 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
ただし、データベースがテーブルの n 番目の列の値を取得するには、まずその前にあるすべての列を解凍 しなければならないという欠点があります。したがって、テーブル内のアクセス頻度が最も高い列が、テ ーブル スキーマの先頭にあるのが最適の状態です。 これは、整数列などのように容量のごく小さいデータを格納しているテーブルの列にも当てはまります。 このような列は、テーブルの先頭近くに配置するのが適しています。先述したように、BLOB 列などの大 容量のデータ列は、できる限りテーブルの末尾に配置します。
3.4
索引
索引は、データベースからのレコードのルックアップ速度を大幅に高める上で極めて重要です。なぜなら、 索引を使用したルックアップでは、線形のフル テーブル スキャンではなく、バイナリ検索技術が使用さ れるからです。 アプリケーションを開発する場合、データ ルックアップの速度を高める必要性が明確になるまでは、索引 を作成しないことをお勧めします。なぜなら、適切にあるいは実用本位で使用されないと、索引はデータ ベースのパフォーマンスを全体的に低下させるからです。索引を追加すると、ルックアップ速度は大幅に 向上しますが、レコードの挿入または削除にかかる時間が増大する可能性があります。また、更新する列 に索引が付いていると、レコードの更新にかかる時間も増大する場合があります。
3.4.1
索引のサイズ
SQLite では、索引は SQLite 自身の B-Tree に格納され、テーブル データから切り離されています。BTree の葉 1 枚につき、最低 4 つのエントリがあります。B-Tree のエントリの最大サイズは約 230 バイト です。サイズの大きいデータが格納されている列に索引が付いていると、索引 B-Tree がオーバフロー ペ ージを使用する原因となります。この状態は、先述した理由により、できる限り避ける必要があります。
3.4.2
一般的な索引の問題点を回避する
3.4.2.1
明示的および暗黙的索引
通常、索引は「CREATE INDEX」SQL コマンドを使用して明示的に作成されます。以下に例を示します。 ‘CREATE INDEX idx1 on Table1(i3)’ ここで i3 は、テーブル Table1 の 3 番目の列です。 代わりに、テーブル スキーマ内の特定タイプの列を指定すると、索引は暗黙的に作成されます。この方法 は、列が UNIQUE または PRIMARY KEY として指定されている場合に使用されます。列が INTEGER PRIMARY KEY として宣言されている場合、新しい索引は作成されませんが、指定された列は組み込み ROWID 索引の別名として扱われます（3.4.3 を参照）。 明示的索引が暗黙的索引と重複しないよう注意してください。 3.4.2.2
索引によるリソースへの影響
新しい索引の作成にはコストが伴うことを認識しておくのは重要です。 ·
新しい索引は、データベース ファイルにそれぞれの B-Tree を格納できる追加スペースを必要 とします。作成される索引が多いほど、テーブル データは付加されずにデータベース ファイ ルが大きくなります。重複や冗長な索引を回避するよう、十分注意する必要があります。以 下に例を示します。 o 冗長な索引 CREATE TABLE Table1(i1 INTEGER, i2 INTEGER) CREATE INDEX idx1 on Table1(i1) CREATE INDEX idx2 on Table1(i1,i2) o 重複した索引 CREATE TABLE Table1(ID INTEGER UNIQUE); CREATE INDEX idx1 on Table1(ID)
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 10 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
3.4.3
EXTERNAL - SYMBIAN
ISSUED V1.1
·
BLOB 列などの大容量データが含まれた列には索引を作成しないようにします。これにより、 索引 B-Tree でオーバフロー ページの使用が必要になるのを防ぎます。
·
すべての挿入および削除処理は、データ テーブルの B-Tree だけでなく、そのテーブルの各索 引の B-Tree も変更します。したがって、索引の数が増えるほど、挿入および削除処理のパフ ォーマンスは低下します。これは、索引付きの列を更新する場合にも当てはまります。
·
索引が重複していると、オプティマイザが使用する索引の選択を誤る可能性が高くなります。 たとえば、上記の冗長な索引で、クエリで使用する列として i1 が指定されている場合、オプ ティマイザは idx2 を選択する可能性があります。
正しい索引タイプを選択する
索引にはさまざまなタイプがあり、索引を選択する前に、各タイプの長所と短所を考慮することが重要で す。使用できる各種の索引と、それらに最も適した環境を次に示します。 ·
標準索引 – 標準索引は、順序付けられた値のリストで、重複する値が許可されています。文 の WHERE 句の検索フィールドで使用される索引として、デフォルトで選択されます。
·
一意索引 - 一意索引は、順序付けられた値のリストで、重複する値は許可されません。一意 索引は、データの整合性を維持するために、テーブルの列または列のセットに制約を適用す る場合に使用できます。テーブル内には、プライマリ キー索引は 1 つしか含めることができ ませんが、一意索引は複数含めることができます。一意性に関する制約を設定すると、一意 索引に影響する処理は、標準索引の場合よりも若干遅くなります。これは、一意性の制約が 守られていることを確認する必要があるためです。
·
プライマリ キー索引 – データの正規化では、テーブルにプライマリ キーが含まれていること が重要です。プライマリ キーの値は、各レコードに対して一意の値です。対象とするテーブ ルによっては、複数の列で一意のプライマリ キーを構成する方が適している場合もあります が、通常は単一の整数プライマリ キーを使用するのが最良の方法です。単一の整数プライマ リ キーは、組み込み ROWID 索引の別名として指定されるので、索引のフットプリントが最 も小さいからです。さらに、索引キーの値は個別の B-Tree ではなくテーブルの B+Tree のエ ントリに格納されるため、最高のパフォーマンスを得ることができます。プライマリ キーの 短所は、値が一意であり null でないことを確実にするために必要な確認があるため、プライ マリ キーに関する処理の速度が低下することです。
·
マルチキー索引 – この索引は複合索引とも呼ばれ、複数の列で構成されます。マルチキー索 引では、列の順序を指定する際に注意が必要です。たとえば、列のいずれかが一意の列であ る場合、その索引では、一意の列の後ろに一意でない列を配置することはできません。
3.4.4 ROWID を特殊な索引として使用する 各テーブルの各レコードは、一意の 64 ビット整数型の値 ROWID を持ちます。ROWID は、テーブル デ ータを格納する B+Tree のキーであり、同じテーブルのその他のすべてのレコードにおいて一意です。一 般に、ROWID を使用するデータ検索は、他の検索方法の約 2 倍高速です。これは、ROWID 索引キーと 共にレコード データが格納されているからです。 ROWID には、特殊な列名である ROWID、_ROWID_ または OID のいずれかを使用してアクセスできま す。あるいは、テーブルの作成時に整数列のキーワード INTEGER PRIMARY KEY を使用して、そのテー ブルの整数列を ROWID の別名として宣言することもできます。
3.4.5
複数の索引を文で使用する
オプティマイザは、文を評価する際、1 つのテーブルに対して 1 つの索引だけを使用します。したがって、 複数の列を含む WHERE 句では、マルチキー索引を使用すると便利な場合があります。それ以外の場合、 索引は 1 つしか使用されません。 たとえば、次のような文があります。
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 11 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
SELECT * FROM Table1 WHERE a = 5 and b < 10; 列 a および b に、以下の索引がそれぞれ作成されます。 CREATE INDEX idx1 ON Table1(a); CREATE INDEX idx2 ON Table1(b); オプティマイザによって索引が 1 つだけ使用され、他の索引は不要になります。 以下のように、a と b 両方の列を含むマルチキー索引を使用する方法もあります。 CREATE INDEX idx3 ON Table1(a,b); この場合、idx3（a と b の複合列に作成されたマルチキー索引）が使用され、列 a および b を最適の方法 で評価に使用することができます。マルチキー索引を使用する場合、WHERE 句で指定されたすべての索 引列は = または IN 演算子を付けて使用する必要があります。ただし、不等演算子を付けて使用できると 指定されている右端の列は除きます。
3.4.6
索引の選択を最適化する
先述のように、文の実行に複数の索引を使用できる場合、使用する索引はオプティマイザによって選択さ れます。この選択は、必ずしも明確、または、予想通りであるわけではありません。 オプティマイザを支援するために、ANALYZE コマンドを使用して、存在する索引に関する統計情報を格 納するテーブル「sqlite_stat1」を作成できます。この情報は、より適切な索引を選択するために、クエリ オプティマイザによって使用されます。
3.5
トリガ
トリガとは、事前に指定されたイベントが発生すると自動的に起動されるデータベース処理です。たとえ ば、テーブル X で削除イベントが発生するとテーブル Y からレコードを削除するトリガを作成できます。 トリガは、更新、削除、または挿入イベントで作成されます。トリガを使用して、外部キーの制約を適用 することができます。8.1.2 を参照してください。
3.6
レコード数の影響
索引を使わずにデータベースでレコードの場所を特定するには、フル テーブル スキャンを実行する必要 があります。当然、この処理の完了に要する時間は、テーブルのレコード数に比例して増大します。 検索列に索引が付いていると、ルックアップ時間は大幅に向上します。大量のレコードを含むテーブルで は、索引の使用はアプリケーションのパフォーマンスにとってきわめて重要です。 一般に、テーブルのレコード数が少ないほど、処理の実行時間は短くなります。したがって、不要になっ たレコードをただちにテーブルから削除することで、データ処理における最適のパフォーマンスを実現で きます。
COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.
EXTERNAL - SYMBIAN
PAGE 12 OF 33
HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067
EXTERNAL - SYMBIAN
ISSUED V1.1
4 データベース処理の最適化 4.1
データベース処理を実行するオプション
データベース処理の最も簡単な実行方法は、RSqlDatabase::Exec() を使用して SQL クエリを実行す ることです。これは、ほとんどの単一 SQL クエリ処理に当てはまりますが、複数の SQL クエリ処理など その他の環境下では、別の API を使用してパフォーマンスを高めることが推奨されます。以下の項では、 さまざまな処理を最適化する方法について説明します。 ここに示すコード例で、疑似コード「
4.2
処理の最適化 – 一般的なヒント
この項では、処理を最適化するための指針とヒントを示します。
4.2.1
単一 SQL クエリの複数の処理を最適化する
ここでは、1 つの処理を複数回実行し、そのたびに異なるデータ値を使用する場合を対象としています。 複数の文を直接実行するのではなく、バインド変数を含む 1 つの文を使用します。次に例を示します。 RSqlStatement stmt; CleanupClosePushL(stmt); CleanupStack::PushL(TCleanupItem(&DoRollback, &
