わかりみSQL読書 Day12 「12章 インデックス」
mohira今日のまとめを書くコーナー
mohira- MVCCの話おもしろかった！ https://zenn.dev/jnuank/scraps/242bb0871703a9f39df1#comment-97f0ae9389ee868fedd9
- ロックをせずに実現する話はなるほど。しかも、仕組みは素直な感じがする(実装はしらんけど)
- しかし、ロックはどこかで使っている話があるので、それは知りたいののう。
- なんか全体的にINDEXの話は面白い
- zennのスクラップ機能いい感じ！
- 特に、ペア勉強だと相方がすくらスクラップに書いてくれてたりするので最高
- 入力変換時のTabキーでインデントされるのつらい... issueたてよう。 → zennのスクラップ機能めっちゃいいけど、Tabキーがつらい話があるので書いていこう
jnuank-
明示的なロックの話が面白かった。
- https://zenn.dev/jnuank/scraps/242bb0871703a9f39df1#comment-e62d0c1d51278827fce8
- 暗黙的に裏でロックを掛けているようにも見えていたが、そもそもMVCCモデルを採用しているから、ロックは明示的にやっていく必要がありそう？
-
部分インデックスの話は、使い慣れておくと便利そうである
-
式インデックスの使い道は、よくわからない
mohiraスクラップいい感じだ！
zenn CLI で書いたりできないのかな？
mohira今日は12.8「複合インデックス」から。
mohiraQ. 複合インデックスの場合、最初の列が含まれていないと、効かないのは何でなんだぜ？ 実装が関係してそう。というか、なぜこういうインターフェイスを採用しているんだ？
ここに書いてあるかも？
jnuank Table "public.pocketbook"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------------------------------------
id | integer | | not null | nextval('pocketbook_id_seq'::regclass)
date | date | | not null | CURRENT_DATE
amount | integer | | not null |
memo | text | | |
Indexes:
"pocketbook_pkey" PRIMARY KEY, btree (id)
"pocketbook_amount_date_idx" btree (amount, date)
"pocketbook_date_amount_idx" btree (date, amount)
(amount, date)
と
(date, amount)
というインデックスの組合せを真逆にしたのも同居は可能。
mohiraQ. 「単一インデックスを複数」と「複数インデックスを1つ」の違いをちゃんと実験したいのう。前回よりも実験計画をしっかりした感じで！
mohira大規模テーブルに対するインデックス作成が長時間にわたる可能性があります。 デフォルトでPostgreSQLはインデックス作成と並行してテーブルを読み取る（SELECT文）ことができますが、書き込み（INSERT、UPDATE、DELETE）はインデックス作成が終わるまでブロックされます。 これは多くの運用環境では受け入れられません。 インデックス作成中でも並行して書き込みできるようにすることができますが、いくつか注意しなければならないことがあります。 インデックスの同時作成の情報を参照してください。
11.1. 序文
対策
そのため行数が多いテーブルでは、ceate index 文に「concurrently」オプ ションをつけてインデックス作成をバックグラウンドで行うことで、テーブルが ロックされるのを回避します。
『わかりみSQL』 p.232
jnuankcreate index concurrently を使うことによって、テーブルをロックしなくて安全である。ただしインデックス貼るのに時間がかかる
Q. インデックスを貼る時間が長いだけがデメリット？ インデックス貼っている最中に、insert、update、delete文が実行されて、テーブルの状態が変更されたときってどうなる？
mohiraロックではなく同時実行制御のMVCCモデルを使用する主な利点は、MVCCでは問い合わせ（読み込み）ロックの獲得と、書き込みロックの獲得が競合しないことです。 したがって、読み込みは書き込みを絶対にブロックしませんし、書き込みも読み込みをブロックすることがありません。 革新的なシリアライザブルスナップショット分離 (SSI)レベルの使用を通した最も厳密なトランザクションの分離レベルを提供する場合にもPostgreSQLはこれの保証を維持します。
第13章 同時実行制御
ロックではなく、MVCCモデルを採用しているから、明示的なロックと書いてあるのかな。
jnuankロックとトランザクションの話があったので、確認。
最新版のマニュアルでは、序文でさっと書いてあるけど、過去のドキュメントでは、MVCCの説明が独立して書いてある。
→ 当時は、真新しい技術だったのだろうか。
9.1. はじめに
多くのデータベースシステムでは、同時実行制御のためにロック機構を使用していますが、PostgreSQLではデータ整合性の維持に多版方式を使用しています。つまり、データベースへの問い合わせ実行の際、各トランザクションは処理の基礎となっているデータの現在の状態を関知せず、現在から遡ったある時点におけるスナップショット（データベースバージョン）を参照する、というものです。これは、並行する（別の）トランザクションが同じ行を更新することによって引き起こる、整合性を欠いたデータの参照からトランザクションを保護し、個々のデータベースセッションに対してトランザクションの隔離を提供するものです。
多版方式とロック方式との最大の相違点は、MVCCでは問い合わせ（読み込み）ロックの獲得と、書き込みロックの獲得が競合しないことです。したがって、読み込みは書き込みを絶対にブロックしませんし、書き込みも読み込みをブロックすることがありません。
mohira
索引でも「MVCC」→「序文」となっていますね。
mohira2006年の記事にMVCCの説明が！
mohiraPostgreSQLのMVCCトランザクションのセマンティクスは、トランザクションID（XID）番号の比較が可能であることに依存しています。 現在のトランザクションのXIDよりも新しい挿入時のXIDを持ったバージョンの行は、「未来のもの」であり、現在のトランザクションから可視であってはなりません。
24.1.5. トランザクションIDの周回エラーの防止
オレ(XID=500)「君(XID=501)はオレ(XID=500)より後輩だよね？ じゃあ、いないことにするから!」
jnuank楽観排他制御とか、マスタの有効開始日、有効終了日に似ている感じがする。
jnuankここで、LOCKコマンドは、明示的ロックと呼ばれているのが理解できた。
mohiraMVCCモデルにより、ロックをしなくてもダーティリードとかを防げるのはわかった(賢い！)。
ところで、別の機会では、行ロックとかしてるんじゃないのか？ あれ？
↓
PostgreSQLは、テーブル内のデータに対する同時アクセスを制御するために様々な種類のロックモードを備えています。 これらのモードは、MVCCでは必要な動作を得られない場合、アプリケーション制御のロックに使用することができます。
MVCCにもできないことはあるってことだな。具体的な実験したいですな。
また、ほとんどのPostgreSQLコマンドでは、参照されるテーブルがそのコマンドの実行中に別の方法で削除もしくは変更されていないことを確実にするために、適切なモードのロックを自動的に獲得します。
せやな。
(例えば、TRUNCATEコマンドは、同じテーブルに対する他の操作と同時に安全に実行することはできないので、それを確実に実行するため、そのテーブルの排他ロックを獲得します。）
mohiraTRUNCATEはテーブル群から全ての行を素早く削除します。 各テーブルに対して条件指定のないDELETEコマンドの実行と同じ効果を持ちますが、実際にテーブルを走査しない分、このコマンドの方が高速です。 さらに、その後にVACUUM操作を行うことなく、このコマンドはディスク領域を即座に回収します。 このコマンドは、大きなテーブルを対象とする場合に最も有用です。
各テーブルに対して条件指定のないDELETEコマンドの実行と同じ効果を持ちますが、実際にテーブルを走査しない分、
へ〜。
jnuankDELETEは、指定したテーブルからWHERE句を満たす行を削除します。 WHEREがない場合、指定したテーブルの全ての行を削除することになります。 この結果、そのテーブルは存在するが中身が空のテーブルになります。
Question
そもそも、WHERE句なしはダメっていうインターフェースになんでしなかったんだろう。
DELETE WHERE無しでやるくらいなら、TRUNCATE を使ったほうがいいよって言うくらいなら、始めに用意しなければよかったのに。
jnuank式インデックスの話
こんなふうに使う
create index user_registrations_email_idx on user_registrations(lower(email));
使い道はどういうのがあるのだろう。
派生した式が、行が挿入、更新される度に実行されなければなりませんので、インデックス式は相対的に見て維持が高価です。 しかし、インデックス式はインデックス内にすでに格納されているため、インデックスを使用する検索の間は再計算されません。 上の両方の例では、システムは問い合わせを単なるWHERE indexedcolumn = 'constant'と理解しますので、この検索速度は他の単純なインデックス問い合わせと同じです。 したがって、式に対するインデックスは取り出し速度が挿入、更新速度より重要な場合に有用です。
しかし、インデックス式はインデックス内にすでに格納されているため、インデックスを使用する検索の間は再計算されません。
この部分がかなり罠のように思える。
mohiraUNIQUE Constraint と UNIQUE INDEX の違い
-
Unique インデックスの作成 - SQL Server | Microsoft Docs
- SQL Serverの話だけど、もしかしたらpostgresqlも同じかも！
UNIQUE 制約を作成することと、制約に依存しない一意インデックスを作成することの間に大きな違いはありません。 データ検証動作も同じ方式で行われます。また、クエリ オプティマイザーでは、制約によって作成された一意インデックスと手動で作成された一意インデックスは区別されません。 ただし、列に UNIQUE 制約を作成すると、インデックスの目的が明確になります。 UNIQUE 制約の詳細については、「 Unique Constraints and Check Constraints」を参照してください。
-
PostgreSQLにおけるUNIQUE制約とUNIQUE INDEXの違い？ - Webプログラムの技術メモ他
- テーブルに保存されている情報が違うらしい
jnuank部分インデックスの話
jnuank例11.2 必要のない値を除外するための部分インデックスの作成
請求済み注文書および未請求注文書からなる、１つのテーブルがあるとします。 そして、未請求注文書の方がテーブル全体に対する割合が小さく、かつその部分へのアクセス数が最も多かったとします。 このような場合、未請求の行のみにインデックスを作成することにより、性能を向上させることができます。 インデックスの作成には、以下のようなコマンドを使用します。
↑このような値の偏りがあるようなテーブルだったら、分けてもよさそうだなぁと思った。
- 請求済み注文書テーブル
- 未請求注文書テーブル
みたいな感じ
mohiraインデックスオンリースキャン
とっても合理的な気がする。
11.9. インデックスオンリースキャンとカバリングインデックス
https://www.postgresql.jp/document/12/html/indexes-index-only-scans.html
PostgreSQLにおけるすべてのインデックスは二次的なインデックス、つまり各インデックスはテーブルの主要なデータ領域（PostgreSQLの用語ではテーブルのヒープと呼ばれます）とは別に格納されています。
わかりみSQL p.220 図12.2 インデックススキャン
わかりみの図がわかりみ。
このことは、通常のインデックススキャンにおいて、各行の検索にはインデックスとヒープの両方からデータを取得する必要があることを意味します。
遠いかもしれないよね。それはそう。
さらに、指定のインデックス可能なWHERE条件に適合するインデックスのエントリは、通常、インデックス内の近い位置にあるのに対し、そこから参照されるテーブルの行はヒープ内のあらゆるところにあるかもしれません。 このため、インデックススキャンにおけるヒープアクセスの部分では、ヒープに対する多くのランダムアクセスがありますが、これは遅い可能性があり、特に伝統的な回転型メディアでは遅くなります。
それはそうだね。
mohiraすべてマークダウンで吐き出してみた。
title: わかりみSQL読書 Day12 「12章 インデックス」
closed: false
✍️ Posted by mohira(@mohira) on 2020/11/25
今日のまとめを書くコーナー
💬 Replied from mohira(@mohira) on 2020/11/25
- MVCCの話おもしろかった！ https://zenn.dev/jnuank/scraps/242bb0871703a9f39df1#comment-97f0ae9389ee868fedd9
- ロックをせずに実現する話はなるほど。しかも、仕組みは素直な感じがする(実装はしらんけど)
- しかし、ロックはどこかで使っている話があるので、それは知りたいののう。
- なんか全体的にINDEXの話は面白い
- zennのスクラップ機能いい感じ！
- 特に、ペア勉強だと相方がすくらスクラップに書いてくれてたりするので最高
- 入力変換時のTabキーでインデントされるのつらい... issueたてよう。 → zennのスクラップ機能めっちゃいいけど、Tabキーがつらい話があるので書いていこう
💬 Replied from jnuank(@jnuank) on 2020/11/25
-
明示的なロックの話が面白かった。
- https://zenn.dev/jnuank/scraps/242bb0871703a9f39df1#comment-e62d0c1d51278827fce8
- 暗黙的に裏でロックを掛けているようにも見えていたが、そもそもMVCCモデルを採用しているから、ロックは明示的にやっていく必要がありそう？
-
部分インデックスの話は、使い慣れておくと便利そうである
-
式インデックスの使い道は、よくわからない
✍️ Posted by mohira(@mohira) on 2020/11/25
スクラップいい感じだ！
zenn CLI で書いたりできないのかな？
✍️ Posted by jnuank(@jnuank) on 2020/11/25
zenn cliでは、スクラップ記事って書けるのだろうか。
使えれば、GitHubの草が生えて嬉しい
✍️ Posted by mohira(@mohira) on 2020/11/25
今日は12.8「複合インデックス」から。
✍️ Posted by mohira(@mohira) on 2020/11/25
Q. 複合インデックスの場合、最初の列が含まれていないと、効かないのは何でなんだぜ？ 実装が関係してそう。というか、なぜこういうインターフェイスを採用しているんだ？
ここに書いてあるかも？
✍️ Posted by jnuank(@jnuank) on 2020/11/25
Table "public.pocketbook"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------------------------------------
id | integer | | not null | nextval('pocketbook_id_seq'::regclass)
date | date | | not null | CURRENT_DATE
amount | integer | | not null |
memo | text | | |
Indexes:
"pocketbook_pkey" PRIMARY KEY, btree (id)
"pocketbook_amount_date_idx" btree (amount, date)
"pocketbook_date_amount_idx" btree (date, amount)
(amount, date)
と
(date, amount)
というインデックスの組合せを真逆にしたのも同居は可能。
✍️ Posted by mohira(@mohira) on 2020/11/25
Q. 「単一インデックスを複数」と「複数インデックスを1つ」の違いをちゃんと実験したいのう。前回よりも実験計画をしっかりした感じで！
✍️ Posted by mohira(@mohira) on 2020/11/25
大規模テーブルに対するインデックス作成が長時間にわたる可能性があります。 デフォルトでPostgreSQLはインデックス作成と並行してテーブルを読み取る（SELECT文）ことができますが、書き込み（INSERT、UPDATE、DELETE）はインデックス作成が終わるまでブロックされます。 これは多くの運用環境では受け入れられません。 インデックス作成中でも並行して書き込みできるようにすることができますが、いくつか注意しなければならないことがあります。 インデックスの同時作成の情報を参照してください。
11.1. 序文
対策
そのため行数が多いテーブルでは、ceate index 文に「concurrently」オプ ションをつけてインデックス作成をバックグラウンドで行うことで、テーブルが ロックされるのを回避します。
『わかりみSQL』 p.232
✍️ Posted by jnuank(@jnuank) on 2020/11/25
create index concurrently を使うことによって、テーブルをロックしなくて安全である。ただしインデックス貼るのに時間がかかる
Q. インデックスを貼る時間が長いだけがデメリット？ インデックス貼っている最中に、insert、update、delete文が実行されて、テーブルの状態が変更されたときってどうなる？
✍️ Posted by mohira(@mohira) on 2020/11/25
テーブルレベルロックをいろいろ試したいときは、 LOCK コマンドでいける。
✍️ Posted by mohira(@mohira) on 2020/11/25
ロックではなく同時実行制御のMVCCモデルを使用する主な利点は、MVCCでは問い合わせ（読み込み）ロックの獲得と、書き込みロックの獲得が競合しないことです。 したがって、読み込みは書き込みを絶対にブロックしませんし、書き込みも読み込みをブロックすることがありません。 革新的なシリアライザブルスナップショット分離 (SSI)レベルの使用を通した最も厳密なトランザクションの分離レベルを提供する場合にもPostgreSQLはこれの保証を維持します。
第13章 同時実行制御
ロックではなく、MVCCモデルを採用しているから、明示的なロックと書いてあるのかな。
✍️ Posted by jnuank(@jnuank) on 2020/11/25
ロックとトランザクションの話があったので、確認。
最新版のマニュアルでは、序文でさっと書いてあるけど、過去のドキュメントでは、MVCCの説明が独立して書いてある。
→ 当時は、真新しい技術だったのだろうか。
9.1. はじめに
多くのデータベースシステムでは、同時実行制御のためにロック機構を使用していますが、PostgreSQLではデータ整合性の維持に多版方式を使用しています。つまり、データベースへの問い合わせ実行の際、各トランザクションは処理の基礎となっているデータの現在の状態を関知せず、現在から遡ったある時点におけるスナップショット（データベースバージョン）を参照する、というものです。これは、並行する（別の）トランザクションが同じ行を更新することによって引き起こる、整合性を欠いたデータの参照からトランザクションを保護し、個々のデータベースセッションに対してトランザクションの隔離を提供するものです。
多版方式とロック方式との最大の相違点は、MVCCでは問い合わせ（読み込み）ロックの獲得と、書き込みロックの獲得が競合しないことです。したがって、読み込みは書き込みを絶対にブロックしませんし、書き込みも読み込みをブロックすることがありません。
💬 Replied from mohira(@mohira) on 2020/11/25
索引でも「MVCC」→「序文」となっていますね。
💬 Replied from mohira(@mohira) on 2020/11/25
2006年の記事にMVCCの説明が！
[ThinkIT] 第3回：トランザクションの比較 (3/4)
💬 Replied from mohira(@mohira) on 2020/11/25
PostgreSQLのMVCCトランザクションのセマンティクスは、トランザクションID（XID）番号の比較が可能であることに依存しています。 現在のトランザクションのXIDよりも新しい挿入時のXIDを持ったバージョンの行は、「未来のもの」であり、現在のトランザクションから可視であってはなりません。
24.1.5. トランザクションIDの周回エラーの防止
オレ(XID=500)「君(XID=501)はオレ(XID=500)より後輩だよね？ じゃあ、いないことにするから!」
💬 Replied from jnuank(@jnuank) on 2020/11/25
楽観排他制御とか、マスタの有効開始日、有効終了日に似ている感じがする。
💬 Replied from jnuank(@jnuank) on 2020/11/25
ここで、LOCKコマンドは、明示的ロックと呼ばれているのが理解できた。
💬 Replied from mohira(@mohira) on 2020/11/25
MVCCモデルにより、ロックをしなくてもダーティリードとかを防げるのはわかった(賢い！)。
ところで、別の機会では、行ロックとかしてるんじゃないのか？ あれ？
↓
PostgreSQLは、テーブル内のデータに対する同時アクセスを制御するために様々な種類のロックモードを備えています。 これらのモードは、MVCCでは必要な動作を得られない場合、アプリケーション制御のロックに使用することができます。
MVCCにもできないことはあるってことだな。具体的な実験したいですな。
また、ほとんどのPostgreSQLコマンドでは、参照されるテーブルがそのコマンドの実行中に別の方法で削除もしくは変更されていないことを確実にするために、適切なモードのロックを自動的に獲得します。
せやな。
(例えば、TRUNCATEコマンドは、同じテーブルに対する他の操作と同時に安全に実行することはできないので、それを確実に実行するため、そのテーブルの排他ロックを獲得します。）
💬 Replied from mohira(@mohira) on 2020/11/25
TRUNCATEはテーブル群から全ての行を素早く削除します。 各テーブルに対して条件指定のないDELETEコマンドの実行と同じ効果を持ちますが、実際にテーブルを走査しない分、このコマンドの方が高速です。 さらに、その後にVACUUM操作を行うことなく、このコマンドはディスク領域を即座に回収します。 このコマンドは、大きなテーブルを対象とする場合に最も有用です。
各テーブルに対して条件指定のないDELETEコマンドの実行と同じ効果を持ちますが、実際にテーブルを走査しない分、
へ〜。
💬 Replied from jnuank(@jnuank) on 2020/11/25
DELETEは、指定したテーブルからWHERE句を満たす行を削除します。 WHEREがない場合、指定したテーブルの全ての行を削除することになります。 この結果、そのテーブルは存在するが中身が空のテーブルになります。
Question
そもそも、WHERE句なしはダメっていうインターフェースになんでしなかったんだろう。
DELETE WHERE無しでやるくらいなら、TRUNCATE を使ったほうがいいよって言うくらいなら、始めに用意しなければよかったのに。
✍️ Posted by jnuank(@jnuank) on 2020/11/25
式インデックスの話
こんなふうに使う
create index user_registrations_email_idx on user_registrations(lower(email));
💬 Replied from jnuank(@jnuank) on 2020/11/25
使い道はどういうのがあるのだろう。
派生した式が、行が挿入、更新される度に実行されなければなりませんので、インデックス式は相対的に見て維持が高価です。 しかし、インデックス式はインデックス内にすでに格納されているため、インデックスを使用する検索の間は再計算されません。 上の両方の例では、システムは問い合わせを単なるWHERE indexedcolumn = 'constant'と理解しますので、この検索速度は他の単純なインデックス問い合わせと同じです。 したがって、式に対するインデックスは取り出し速度が挿入、更新速度より重要な場合に有用です。
しかし、インデックス式はインデックス内にすでに格納されているため、インデックスを使用する検索の間は再計算されません。
この部分がかなり罠のように思える。
💬 Replied from mohira(@mohira) on 2020/11/25
UNIQUE Constraint と UNIQUE INDEX の違い
-
Unique インデックスの作成 - SQL Server | Microsoft Docs
- SQL Serverの話だけど、もしかしたらpostgresqlも同じかも！
UNIQUE 制約を作成することと、制約に依存しない一意インデックスを作成することの間に大きな違いはありません。 データ検証動作も同じ方式で行われます。また、クエリ オプティマイザーでは、制約によって作成された一意インデックスと手動で作成された一意インデックスは区別されません。 ただし、列に UNIQUE 制約を作成すると、インデックスの目的が明確になります。 UNIQUE 制約の詳細については、「 Unique Constraints and Check Constraints」を参照してください。
-
PostgreSQLにおけるUNIQUE制約とUNIQUE INDEXの違い？ - Webプログラムの技術メモ他
- テーブルに保存されている情報が違うらしい
✍️ Posted by jnuank(@jnuank) on 2020/11/25
部分インデックスの話
💬 Replied from jnuank(@jnuank) on 2020/11/25
例11.2 必要のない値を除外するための部分インデックスの作成
請求済み注文書および未請求注文書からなる、１つのテーブルがあるとします。 そして、未請求注文書の方がテーブル全体に対する割合が小さく、かつその部分へのアクセス数が最も多かったとします。 このような場合、未請求の行のみにインデックスを作成することにより、性能を向上させることができます。 インデックスの作成には、以下のようなコマンドを使用します。
↑このような値の偏りがあるようなテーブルだったら、分けてもよさそうだなぁと思った。
- 請求済み注文書テーブル
- 未請求注文書テーブル
みたいな感じ
✍️ Posted by mohira(@mohira) on 2020/11/25
インデックスオンリースキャン
とっても合理的な気がする。
11.9. インデックスオンリースキャンとカバリングインデックス
https://www.postgresql.jp/document/12/html/indexes-index-only-scans.html
PostgreSQLにおけるすべてのインデックスは二次的なインデックス、つまり各インデックスはテーブルの主要なデータ領域（PostgreSQLの用語ではテーブルのヒープと呼ばれます）とは別に格納されています。
わかりみSQL p.220 図12.2 インデックススキャン
わかりみの図がわかりみ。
このことは、通常のインデックススキャンにおいて、各行の検索にはインデックスとヒープの両方からデータを取得する必要があることを意味します。
遠いかもしれないよね。それはそう。
さらに、指定のインデックス可能なWHERE条件に適合するインデックスのエントリは、通常、インデックス内の近い位置にあるのに対し、そこから参照されるテーブルの行はヒープ内のあらゆるところにあるかもしれません。 このため、インデックススキャンにおけるヒープアクセスの部分では、ヒープに対する多くのランダムアクセスがありますが、これは遅い可能性があり、特に伝統的な回転型メディアでは遅くなります。
それはそうだね。