はてなブックマーク

ソートは、非常にリソースを消費する処理です。かなりのCPU時間を必要としますが、問題の核心は、データベースが結果を 一次的にバッファしておかなくてはならないことです。ソート処理では、全ての入力を終えなければ、結果を出力することができません。ソート処理は、 パイプラインのようには実行できないのです。これは、データセットが大きい時に、問題になります。 インデックスされたデータはインデックスの順番に並んでいるという原則は、第1章1で既に説明しました。 そこから、インデックスはデータを既に並べ替えられた形で保存するためのものであるとも言えます。実際にインデックスは、order by句にインデックスの定義があるかのように並べ替えられます。 そのため、order by句を満たしつつソート処理を避けるために インデックスが使えるというのは、自然な考え方と言えるでしょう。 皮肉な事に、INDEX RANG

この節では、MicrosoftのSQL Serverにおける 最も良く現われる実行計画の処理を説明します。Microsoftの ドキュメントも参照して下さい。 インデックスとテーブルへのアクセスSQL Serverの用語はシンプルになっています。 「Scan」はインデックス全体を読み込み、「Seek」はインデックスあるいはテーブルの指定された一部のみにアクセスするためにBツリーや 物理アドレス(RID、 Oracleで言うROWID)を使う処理になっています。 Index Seek, Clustered Index SeekIndex Seekは、Bツリーの走査に 加えて、一致するエントリを探すのにリーフノードチェーンをたどります。第1章1, 「 SQLインデックスの内部構造」も参照して下さい。 Index Scan, Clustered Index Scanインデックスの全体つまり全行を

インデックスリーフノードは、任意の順序で保存され、ディスク上の 保存場所は、インデックスの順序に従った論理的な場所とは関係ありません。これは、ページの順序がバラバラの電話帳のようなものです。この場合、 「タナカ」を探そうとしてまず開いたページに「サトウ」があった時、サトウの後にタナカがあるとは限らないことになります。データベースには、 バラバラに並ぶページ間を素早く検索できる別の構造が必要になります。 それが、バランス検索木、略してBツリーです。 図1.2Bツリーの構造1113183CAFF3916FB22127272C500F1B52553435390D1E4453245D1827394043464A1B9F71A2D24653538B1CA0A10D795557579CF6B1C15029678383C46BFF9DAFE94653578384868880644C2F065B89909

テーブルあたりのインデックスの数は、insertの パフォーマンスに対して最も影響を与える要素です。テーブルにたくさんのインデックスがあれば、実行もそれだけ遅くなります。insert文は、where句を持たないことから、 インデックスの利点を直接受けられない唯一の処理です。 新しい行をテーブルに追加するのは、いくつかのステップからなっています。まず最初に、データベースは行を追加する場所を探します。 一般的なヒープテーブルでは、行の順序が決まっていないため、データベースは空き容量があるテーブルブロックであればどれでも使用可能です。 これは非常にシンプルで高速な処理で、ほとんどはメインメモリ上で行われます。その後にデータベースが行うのは、それぞれのブロックに新しいエントリを 追加するだけです。 テーブルにインデックスがある場合、データベースはインデックスを使って新しいエントリを見つけられるよう

Big News In DatabasesThere’s been a lot of big news in 2016, also when it comes to databases. Let’s have a look at the really BIG news. MySQL and MariaDB Become ModernFor a long time, MySQL and MariaDB were mostly limited to the functionality of SQL-92. Now, both of them have decided to move forward by supporting the WITH and OVER clauses in their next major release (MySQL 8.0, MariaDB 10.2). The

処理しにくい最もたるものはDATE型でしょう。 Oracleは、常に時刻までを含むDATE型の1つしかないので、特に厄介だと言えます。 時間の成分を排除するためにTRUNC関数を使うのは、 一般的な方法になっています。実際には、Oracleには純粋なDATE型は存在しないので、この関数は時間成分を削除 してしまうわけではなく、その代わり時刻を0時0分に設定します。検索時に時間成分を無視するには、比較の両辺で TRUNC関数を使用します。前日の売り上げを知りたい時は以下のようにします。 SELECT ... FROM sales WHERE TRUNC(sale_date) = TRUNC(sysdate - INTERVAL '1' DAY)これは完璧に有効で正しい文ですが、SALE_DATEに 作られたインデックスを正しく使うことはできません。この理由は、「UPPERとLOWERを使った

PostgreSQLの実行計画は、SQL文の前にexplainを置く事によって取得できます。しかし、1つ重要な制限事項があります。 バインドパラメータを使ったSQL文 ($1や$2など)においては、この方法では実行計画を取得できません。まず最初にプリペアド ステートメントとして宣言する必要があるのです。 PREPARE stmt(int) AS SELECT $1PostgreSQLでは、バインドパラメータに「$n」を使用する事に 注意しましょう。使用しているデータベース抽象化層によってはこれを隠してしまう事があるので、SQL標準で定義されているように、 クエスチョンマークを使いましょう。 プリペアドステートメントは以下のように実行します。 EXPLAIN EXECUTE stmt(1)PostgreSQL 9.2以降では、実行時まで実行計画の作成は先送りされるので、バインドパラメータに関

インデックスのみのスキャンは、 インデックスに保存された冗長なデータだけを使ってSQL文を実行することです。ヒープテーブル上にある元のデータは 必要ありません。この考え方を次のレベルに持ち込んで、全ての列をインデックスに入れることを考えると、なぜヒープテーブルが必要なのかという 疑問に行きつきます。 いくつかのデータベースにおいては、一次的なテーブルの保存先として インデックスを使用できます。Oracleではこのコンセプトを索引構成表 (IOT)と呼び、その他のデータベースではクラスタ化インデックスと言います。 この節では、どちらの単語も、必要に応じてテーブルあるいはインデックスの特徴を強調するものとして使います。 索引構成表は、ヒープテーブルを持たないBツリーインデックスのことです。これには、次の2つの利点があります。(1) ヒープ構造の分の容量が節約できます。 (2) クラスタ化インデ

TranslationsA Japanese translation of this article is available here. A Chinese translation of this article is available here. A few days ago Uber published the article “Why Uber Engineering Switched from Postgres to MySQL”. I didn’t read the article right away because my inner nerd told me to do some home improvements instead. While doing so my mailbox was filling up with questions like “Is Postg

インデックスとテーブルへのアクセスSeq ScanSeq Scanは、ディスクに保存されている通り(TABLE ACCESS FULLのように)に関係(テーブル)をスキャンします。 Index ScanIndex ScanはBツリー走査を行い、 一致する全ての値を検索するのにリーフノードをたどり、対応するテーブル上のデータを取り出します。INDEX RANGE SCANに続いてTABLE ACCESS BY INDEX ROWIDを行うのに似ています。第1章1, 「 SQLインデックスの内部構造」も参照して下さい。 いわゆるインデックスフィルタ述語は、Index Scanの パフォーマンス問題を引き起こします。次の節でその問題の 識別方法を説明しています。 Index Only Scan (PostgreSQL 9.2以降)Index Only ScanはBツリー走査を行い、一致する全ての

前節では、既にあるインデックスの列の順番を変えることで、インデックスから得られるメリットを大きくする方法について説明しました。 しかし、例に挙げたのは2つのSQL文だけでした。本来は、インデックスを変更 すると、そのインデックスが作成されたテーブルに対する全てのクエリが影響を受けます。この節では、データベースがどのようにインデックスを選ぶのかを 説明し、既存のインデックスを変更することによる副作用を見ていきます。 前節で採用したEMPLOYEES_PKインデックスは、 子会社だけで検索する全てのクエリのパフォーマンスを改善します。実際のところこのインデックスは、他にどのような検索条件が追加されたと しても、SUBSIDIARY_IDを検索に使う全てのクエリに使うことができます。つまり、where句の他の部分で 他のインデックスを使っていたクエリに対しても有効になるということです。この場合、

order by付きのSQLクエリでは、関連するインデックスが 行を既に必要な順番に並べ替えている時には、結果を明示的にソートする必要はありません。つまり、where句に使われるのと同じインデックスが、order by句もサポートしている必要があります。 例として、前日の売上を売上日と製品IDで並べ替える、以下のようなクエリを考えてみましょう。 SELECT sale_date, product_id, quantity FROM sales WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY ORDER BY sale_date, product_idwhere句で使えるインデックスは、 既にSALE_DATEに作られています。しかし、order by句を満たすように、データベースは明示的なソート処理を行う必要があります。 Db2 (L

データベースは、どちらの方向に向かってもインデックスを読むことができます。つまり、パイプライン化されたorder byは、スキャンされるインデックスの範囲がorder by句と全く反対の時にも可能になるということです。order by句の ASCやDESC修飾語がパイプライン化された処理の 邪魔をしそうに思うかもしれませんが、多くのデータベースではインデックスの順序を変える方法を用意しており、パイプライン化されたorder byにインデックスを使えるようにできます。 次の例では、インデックスを逆順で使用し、日付の逆順および PRODUCT_IDの逆順に並べて、昨日までの売上を取り出します。 SELECT sale_date, product_id, quantity FROM sales WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DA

最初のページを効率的に取り出すために、パイプライン化された最初のN件のみを選択するクエリを 実装できたら、次ページ以降を取りだすクエリも必要になるでしょう。それを実現するためのチャレンジは、前のページの行をどのようにスキップするかです。 これには2つの方法が考えられます。まず1つ目は、先頭から行に番号を付けて、必要なページよりも前の行番号のデータをフィルタする、 オフセット法です。2つ目は、私がシーク法と 呼んでいる、全ページの最後のエントリを検索し、それ以降の必要な行を読み出す方法です。 以下は、最も広く使われているオフセット法の例を表したものです。 この方法の利点は簡単に使える所にあるでしょう。専用のキーワード(offset)が用意されているデータベースでは特にそうです。このキーワードは、fetch first拡張の一部として SQL標準にも採用されています。 DB2DB2はSQL標準

SQLデータベースは、全く異なる2つのgroup byの アルゴリズムを使用します。1つ目はハッシュアルゴリズムで、入力されたレコードを一時的なハッシュテーブル上でまとめ上げます。 全てのレコードが処理されたら、ハッシュテーブルが結果として返されます。2つ目は、ソート・グループアルゴリズムで、入力されたデータをグループキーで ソートすることで、各グループを順番に処理できるようにします。その後、それらをデータベースがまとめます。一般的には、どちらのアルゴリズムも 中間結果をマテリアライズする必要があるので、パイプライン的に処理されることはありません。しかし、ソート・グループアルゴリズムでは、ソート処理を しないためにインデックスを使えるので、パイプライン化されたgroup by処理が可能になります。 次のような、PRODUCT_IDごとの昨日の利益を得るためのクエリを考えてみましょう。 SE

Use The Index, Luke! のキャラクター商品を見て！――このページでの私の仕事を支えてくれるクールなグッズです。 全部、全世界送料無料。PayPal による簡単で確実な支払が可能。 ステッカーは無料です！ 無料でUse The Index, Luke!、NoOffset、Modern SQL、No-Self-Joinのステッカーをお送りします。 コースターやマグカップが欲しいですか？下へスクロールしてください！ 1人につき1年に1回のみ有効です。金銭による払い戻しはしません。送信されたデータは商品の送付のためにのみ利用し、再度注文を受けた場合に備え1年間だけ保持されます。. お届け先の住所E-Mail 国 机をお気に入りの飲み物から守るためのビアマット・セット。ビアマットの表には Use The Index, Luke! のキャラクターが、裏には NoOffset のロゴが

PostgreSQLは、where句(述語)を適用するのに、3つの方法を使い分けます。 アクセス述語 (「Index Cond」)アクセス述語は、リーフノードの走査の開始と終了の条件を表しています。 インデックスフィルタ述語 (「Index Cond」)インデックスフィルタ述語は、リーフノード走査の時にのみ適用されます。スキャンされる範囲の開始と終了の条件を表したり、 範囲を狭めるものとしては使われません。 テーブルレベルのフィルタ述語 (「Filter」)インデックスの一部でない列に対する述語は、テーブルのレベルで評価されます。その場合データベースは、 まずヒープテーブルから行をロードしなければなりません。 注記インデックスフィルタ述語は、間違った安心感を持ってしまう原因になりかねません。 インデックスは確かに使われますが、データ量やシステムの負荷が増大すると、 急激にパフォーマンス劣化

データベースに保存されたデータの量は、そのパフォーマンスに大きな影響を与えます。データベースに追加のデータが加わることにより あるクエリが遅くなるのは、よくあることです。しかし、データ量が2倍になった時の影響はどの位でしょうか？また、その影響の比率を改善するには どうしたらよいでしょうか？これらは、データベースのスケーラビリティを考える上で重要な問いかけです。 例として、2つの異なるインデックスを使った時の以下のクエリの応答時間を調べてみましょう。インデックスの定義は、今のところは 伏せておきます。これ以降の解説の中で、明らかになるでしょう。 SELECT count(*) FROM scale_data WHERE section = ? AND id2 = ?SECTION列はこのクエリにおいて、データ量を制御するという 特別な役割があります。SECTIONの数が増えると、選択される行

この節では、大抵のSQLテキストでは扱われないトピックである、 パラメータ化クエリとバインド パラメータを取り上げます。 動的パラメータ、あるいはバインド変数とも呼ばれる、バインドパラメータとは、データベースにデータを渡すための方法です。値を SQL文に直接埋め込む代わりに、?や:name、 @nameといったプレースホルダを使い、別のAPIを呼び出して実際の値を渡します。 1度限りのSQL文に、直接値を書き込んで実行するのには何の問題もありませんが、プログラム内でのバインドパラメータには2つの利点が あります。 セキュリティバインドパラメータは、SQL インジェクションを防ぐ最適な方法です。 パフォーマンスSQL ServerやOracleのように、実行計画をキャッシュするデータ ベースでは、複数回実行される同じ文に対して同じ実行計画を再利用することができます。これにより、実行計画を作成

ここまでは、列に対してインデックスを作成する ことだけを取り上げてきました。どの行にインデックスを 作成するかも、部分インデックス(PostgreSQL) あるいはフィルター選択されたインデックス(SQL Server)という機能で実現できます。 注意Oracleでは、部分インデックスに対してユニークなアプローチを取っています。これについては、この節の内容を踏まえて、 次節で詳しく説明します。 部分インデックスは、以下の例にあるステータスコードのような定数を使った、比較的よく使われるwhere句で便利です。 SELECT message FROM messages WHERE processed = 'N' AND receiver = ?このようなクエリは、キューのシステムで非常によく使われます。クエリは、特定の受信者に向けた未処理のメッセージを全て取得します。 既に処理されたメッセージ

データベースは、プライマリキーに対して自動的にインデックスを作成しますが、キーが複数の列からなる時は、さらに手動で調整をする 余地があります。この場合、データベースはプライマリキーの全ての列にいわゆる 連結インデックス(あるいはマルチカラム インデックス、複合インデックス)を作成します。 複合インデックスの列の順番は、インデックスの使い勝手に大きな影響を及ぼすので、注意して決定する必要があります。 例として、企業が合併した場合を考えてみましょう。他の会社の社員が 加わったので、EMPLOYEESテーブルが10倍の大きさになったと しましょう。ここで問題が発生します。EMPLOYEE_IDが、 それぞれの会社で一意になっていなかったのです。子会社IDのような追加の識別子で、プライマリキーを拡張する必要があります。このため、プライマリ キーは、以前からのEMPLOYEE_IDに加えて、一意性を

SQLのLIKE演算子は、予期せぬパフォーマンス上の 問題の原因になることが非常に多くあります。これは、検索語によっては、インデックスの効率的な使用を妨げてしまうからです。つまり、 インデックスをうまく作成できる検索語と、そうでない語があるということです。ワイルドカード文字の場所が、その違いです。 以下の例では、%ワイルドカードを検索語の真ん中で使っています。 SELECT first_name, last_name, date_of_birth FROM employees WHERE UPPER(last_name) LIKE 'WIN%D' Db2 (LUW)Explain Plan ---------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 13 2

開発者向けのSQLインデックス解説サイト、管理についての間違いない知識を提供します。 インデックスは開発時には忘れられがちである一方で、非常に効果的なSQLのチューニング方法です。Use The Index, Lukeでは、HibernateなどのORMツールの解説にとどまらず、SQLのインデックスについて基礎から説明します。 Use The Index, LukeはSQLパフォーマンス詳解のWeb上の無料版です。サイトを気に入って頂けたら、ぜひ書籍も購入してみて下さい。また、このサイトの運営をサポートする様々なグッズも販売しています。 MySQL、Oracle、SQL ServerなどにおけるSQLのインデックスUse The Index, Lukeでは、ベンダにとらわれないインデックスの説明を心がけています。製品特有の事柄については、以下のような表示をしています。 Db2 (LUW)U

Oracleは、インデックス定義に含まれる列の全てがNULLの 時は、その行をインデックスに含めません。つまり、全てのインデックスがwhere句が以下のような部分インデックスであると言えます。 CREATE INDEX idx ON tbl (A, B, C, ...) WHERE A IS NOT NULL OR B IS NOT NULL OR C IS NOT NULL ...EMP_DOBという、DATE_OF_BIRTH列のみの インデックスを考えてみましょう。DATE_OF_BIRTHの値がない行は、インデックスに追加される 事はありません。 INSERT INTO employees ( subsidiary_id, employee_id , first_name , last_name , phone_number) VALUES ( ?, ?, ?, ?, ? )このi

INDEX RANGE SCANにおいて、パフォーマンスへの影響が最も大きいのは、リーフノードの走査です。インデックスをスキャンする範囲をできる限り小さく保つのは、インデックスを作る上での黄金則です。インデックスのスキャンがどこから始まってどこで終わるのか、自分に問いかけて確認しましょう。 SQL文内で、始点と終点の条件が明示的に書かれているなら、答えは簡単です。 SELECT first_name, last_name, date_of_birth FROM employees WHERE date_of_birth >= TO_DATE(?, 'YYYY-MM-DD') AND date_of_birth <= TO_DATE(?, 'YYYY-MM-DD')指定された範囲内で、DATE_OF_BIRTHのインデックスがスキャンされます。スキャンは、最初の日付から始まり、2番目の日付で

「インデックスはクエリの実行を速くする」 とは、インデックスに関する説明でも、私が見た中で最も基本的なものです。これは、インデックスの最も重要な一面をとてもうまく表していますが、残念ながら この本には不十分と言わざるを得ません。この章では、インデックスの構造について、表面的過ぎないけれど深入りもしすぎない程度に説明していきます。これで、 この本全体を通して扱われる、SQLのパフォーマンスについて理解するのに十分な基礎を知ることができるでしょう。 インデックスは、データベースの中で特有の構造を持ち、create index 文で作成されます。独自のディスク領域を必要とし、インデックスを張られた テーブルのデータを保持します。これはつまり、インデックスは純粋に冗長な構造であるといえます。 インデックスを作成しても、テーブルのデータは変更されず、テーブルを参照する 新しいデータ構造が作られるだけ