常連

## 質問内容
単純な疑問で恐縮なのですが、SQL(SELECT)が重くなるのは「レコード数」なのでしょうか。それとも「データザイズ」なのでしょうか。

## 質問背景
[zekterraさんの質問](https://teratail.com/questions/51953)であったり、他でも見かける中で「nnn万件のデータのSELECTに時間がかかります」という内容があるかと思います。
しかし、対象テーブルが「1カラム」の場合と「10カラム」の場合では違うのでは？という単純な疑問が沸いてきました。

例えば、
「1カラム」しか保持してなく、1レコード辺りのデータサイズも小さい場合、SELECT文のレスポンスも速い印象で、
「10カラム」を保持しており、1レコード辺りのデータサイズも約10倍ある場合、SELECT文のレスポンスも併せて遅くなりそうな印象です。

しかし、
どこを調べても「レコード数」に関するチューニングの記述しか見当たらず、「データサイズ」に関する記述が見つかりません。

これは、
「『レコード数が原因であり、データサイズは関係ないのである』という事であり、あえて言及していないのか･･･?」という純粋な疑問が沸いてきました。

そこで、
SQLやデータベースに詳しい方にご教示いただきたいと思い、投稿させていただいた次第です。

## 補足情報

・PostgreSQLを採用しており、テーブルAに約400万レコード格納されている
・テーブルＡには、暗号化されたデータA(JSON)を格納していて、取得時に復号する
・データＡは、処理Aや処理Bなど複数処理に用いられるが、処理Aについては暗号化しなくていい情報しか用いられていない
　→「暗号化すべきものとそうでないものを区別してテーブルを分ければ、レコード辺りのデータサイズも削減されて、処理Aが軽くなるのでは？」という仮説を立ててみた
 （他人のシステムなのであまり手出しできないが、そもそもJSONを加工･正規化もせずに使っているので、説得材料として加工･正規化をさせたい意図。仮説が誤ってたら他を考える予定。）

SQLが重くなるのは「レコード数」なのか「データサイズ」なのか

### はじめに
下部に記したようなSQLがあります。そのままはコピペできないため、テーブル名等を加工しています。
これを2つの環境（以下、テスト環境・本番環境とする）で実行したときに、本番環境で実行した場合にのみ想定より時間がかかってしまうのですがその理屈がわからず、お力添えいただきたく存じます。

```SQL
SELECT
    SUB.SEQ_NO
   ,SUB.ID
   ,SUB.PROCESS_DATE
   ,SUB.IMAGE_KEY
FROM
    (
    SELECT
        /* 行番号を振る*/
        ROW_NUMBER() OVER (ORDER BY MAIN.ID) AS SEQ_NO 
       ,MAIN.ID AS ID
       ,MAIN.PROCESS_DATE AS PROCESS_DATE
       ,CASE WHEN MAIN.NO_IMAGE = '1' THEN ''
             WHEN MI.IMAGE_DATA IS NULL AND SI.IMAGE_DATA IS NULL AND BI.IMAGE_DATA IS NULL THEN ''
             ELSE MAIN.ID ||MAIN.SEQUENCE || PROCESS_DATE END AS IMAGE_KEY
             
    FROM
        MAIN_TABLE MAIN
        
    LEFT OUTER JOIN MAIN_IMAGE MI
        ON MAIN.ID = MI.ID
    LEFT OUTER JOIN SUB_IMAGE SI
        ON MAIN.ID = SI.ID
    LEFT OUTER JOIN BACKUP_IMAGE BI
        ON MAIN.ID = BI.ID
    )SUB
WHERE
    SUB.SEQ_NO <= 100000
```

### 調べたこと
**1. 実行計画**
両環境において、全く同じSQLの実行計画を取得しました。すると、トータルコストに大きな差が出ました。SQLを加工している関係上実行計画をそのまま貼付する訳にもいかないのですが、具体的に記載しますと本番環境の方はソート対象行数がおおよそ6000億行ぐらいにまで膨れ上がっており、そこにかなりコストおよび時間がかかっているようでした。検証環境の実行計画は、ソート対象行数は10万行となっており想定通りでした。つまり、テーブルのレコード数に起因することがここでわかるかと思います。

**2. SQL本体**
実行計画から「どの記述が影響しているか」を特定できなかったため、SQLの結合条件やSELECT句などをコメントアウトしながら本番環境で都度実行し、「どの部分がコメントアウトされた時点でコストが増えるのか」という調査をしました。すると、下部に記載したSQLのうち、15行目の以下部分をコメントアウトするとコストは下がり、実行計画上のソート対象行数も10万行になりました。
> AND BI.IMAGE_DATA IS NULL
確かに検証環境と比較すると、BACKUP_IMAGEには大量のレコードが登録されていましたので（検証環境：約100件、本番環境：約1500万件）、そこの件数が影響していることもここでわかるかと思います。

また、BACKUP_IMAGEテーブルは現状登録されているデータの件数から増減が一切ないため、統計情報の更新は行われていません。数年前に1500万件を一気に登録した時点で統計情報を取り、それ以降はデータの増減がないようです。ですのでインデックスが効いていない、というわけでもないかと思います。

### 不明点
・テーブル同士を結合しただけでは特にコストが増えないのに、CASE文の中で結合先のテーブルカラムを参照するとコストが爆増する理屈がわからない。実行計画を見るに、ソート対象行数が増えているため、たとえば結合条件が間違っていて取得行が増殖するようなSQLになっているとかなら納得はできるが、今回登場するMAINテーブルとBACKUP_IMAGEテーブルの関係は1対1なのでそれも起きえないと考えている。

### 補足情報
・MAIN_IMAGE、SUB_IMAGE、BACKUP_IMAGEのいずれも、IMAGE_DATAカラムに画像ファイルをBLOB形式で保持しています（平均100KBほど）。
・本文に記載済みですが、MAINと各種IMAGEについては1対1になります。

MAINテーブル
|ID|PROCESS_DATE|
|:-:|--:|
|001|20231123|

MAIN_IMAGE/SUB_IMAGE/BACKUPテーブル
|ID|IMAGE_DATA|
|:--|:--:|
|001|(BLOB)|

長文になりましたが、ご助力願えればと思います。

宜しくお願い致します。



【Oracle19C】記載のSQLが遅くなる理屈がわからない

###前提・実現したいこと
初投稿の為、不備があればご指摘ください。

環境情報：
OS：Windows Server 2012 x64
Database：Oracle 11g Standard Edition x64
Tool：SI Objectbrowser 13 x64

Oracle Enterprise Managerの使用許可が下りていませんので、
Oracle Enterprise Managerによる稼働状況の分析が出来ません。

上記環境でLOG_BUFFERの最適なサイズを割り出したいです。

###発生している問題・エラーメッセージ
最大で、1時間で約500MB程度のデータの取込を行っているDBがあります。
定期実行のプログラムも多く、トランザクションの量もかなり多いようで、
SI Object Browserでデータベース情報を確認すると
REDOバッファの空き待機回数が１週間で1000を超える達するケースを確認しています。
（週末に再起動しているようで翌週頭には0になっています...）

そもそも設計の問題のほうが大きいと思うのですが、
まずはOracleのチューニングでパフォーマンス改善を、という話がされ、
空き待機回数の削減を行うように指示を受けましたが、
LOG_BUFFERの調整に難航しております。

お知恵をお貸しください。

###試したこと
データ量に反してREDOログもLOG_BUFFERもデフォルトの設定値のようでしたので、
空き待機回数を減らすために
REDOログファイルのサイズ拡張とLOG_BUFFERのサイズ拡張を行いました。

REDOログファイルのサイズ拡張で大幅に減少させることはできたのですが、
LOG_BUFFERのサイズは下記のオンラインマニュアルにある上限の32MBを設定しましたが、REDOログのサイズ拡張時ほどの効果が得られていません。
https://docs.oracle.com/cd/E16338_01/server.112/b56311/initparams133.htm

###ご相談内容
１．LOG_BUFFERの最適なサイズを割り出すためのSQLは有りませんでしょうか？
２．あるいは、Oracle Enterprise Managerで得られるような解析結果を求めるSQLを組めないでしょうか？

以上、宜しくお願い致します。

OracleのLog_Bufferの調整について

### 知りたいこと
処理に大幅な時間がかかるSQLをチューニングしたのですが、処理速度が改善した理由を知りたいです。

### 処理内容

exeで作成したSQLをサーバーに投げて結果をexeで処理しています。
SQLの結果が数件～数百万件になる可能性があります。
結果の件数が少ない時は問題ないのですが、数百万件返ってくるときはデータ量が多すぎて処理落ちしてしまいます。

そのため、row_numberを使い結果を範囲指定して百万件ずつに分けて取得するようにしました。
SQLのWHERE句で以下の記述をしています。
「WHERE rownum BETWEEN 1 AND 1000000」
このSQLを実行すると結果が返ってくるまでに7分程度かかります。
百万件で取得しきれなかった場合はexeで投げるSQLを以下に変更して実行しています。
「WHERE rownum BETWEEN 1000001 AND 2000000」

実行時間短縮のため、TOPを使うことで処理時間を大幅に減らせました。
SELECT句に
「TOP 1000000」
WHERE句に下記を記述しました。
「WHERE rownum >= 1」
このSQLを実行すると1秒で結果が返ってきます。
2回目以降のSQLではWHERE句を以下のように変更しています。
「WHERE rownum >= 1000001」


### 試したこと

その他、以下のSQLを試してみました。
「WHERE rownum BETWEEN 1 AND 100」・・・処理時間50秒程度
「WHERE rownum 1 >= AND rownum <= 1000000」・・・処理時間7分程度
「WHERE rownum <= 1000000」・・・処理時間7分程度

この結果を見ると、範囲指定をすると処理に大幅に時間がかかるような気がしているのですが、ネットで検索してみてもそのような事例は見つからなかったです。
順位付けをしたカラムを範囲指定すると遅くなるようなことはあり得るのでしょうか？

SQL Serverでのパフォーマンスについて

シンプルな質問です
C++でループ内で変数宣言を行った場合
1ループごとにその変数は解放されますか？
それとも完全にループが終了するまで新たに領域を確保し続けて、最終的に全てを解放しますか？
それとも領域は使い回しですか？

出来たらそれが明記してある文書などをご存知でしたら
それも教えていただけるとありがたいです

ループ内での変数宣言について

###前提・実現したいこと
以下の状況において、LIKE の前方一致によるINDEXが効かず、性能が出なくて困っております。。。
INDEXが効かない原因と対応策を教えて頂けないでしょうか。

＜状況＞
１：CREATE TABLEテーブルは以下です。
```SQL
CREATE TABLE `tb_test_data` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `col1` bigint(20) NOT NULL DEFAULT '0',
 `col2` int(11) NOT NULL DEFAULT '0',
 `col3` float NOT NULL DEFAULT '0',
 `col4` float NOT NULL DEFAULT '0',
 `col5` int(3) NOT NULL DEFAULT '0',
 `col6` int(11) NOT NULL DEFAULT '0',
 `col7` bigint(20) NOT NULL,
 `url` text,
 `date` date NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY UNI_col1_col5( col1, col5 ),
 KEY `IX_col2` (`col7`, col2),
 KEY `IX_col3` (`col7`, col3),
 KEY `IX_col4` (`col7`, col4),
 KEY `IX_col5_col1` (`col7`, col5, col1),
 KEY `IX_col6` (`col7`, col6),
 KEY `IX_col1_col5` (`col7`, col1, col5),
 KEY `IX_date` (`col7`, date),
 KEY `IX_url` ( url(255) )
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
```

２：上記テーブルは更新型テーブル。更新頻度は毎秒１００件くらいのDELETE / INSERTを２４時間実施。
  （MyISAMのため、参照するとテーブルロックになり、更新はされません）
  登録順はcol1をキーにcol5の分だけ削除し、削除したcol1の値でcol5の分だけ登録を行います。

３：上記テーブルは現在、１．５億レコード存在します。AUTO_INCREMENTの値は４．５億です。

４：URL に INDEXを効かせたいSQLは以下のSQLです。
```SQL
SELECT	T1.*
FROM	tb_test_data T1
WHERE	T1.col7	=	123456
	AND	T1.url		like 'http://abc.jp/xxx/xxxx/xxx%'
ORDER BY col1
Limit 0, 100
```

５：実行計画
```SQL
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | T1 | range | IX_col1,IX_col2,IX_col3,IX_col5_col1,IX_col6,IX_col1_col5,IX_date,IX_url | IX_col1_col5 | 8 | NULL | 1157468 | Using index condition; Using where 
```
※上記の実行計画は「http://abc.jp/xxx/xxxx/xxx%」の部分を68文字のURLを指定し、このURLだけで１件の表示になる値で実行計画を出しています。そのため、理想の実行計画としては、
refに「IX_url」が表示され、rows項目も1となる事を想定していました。
※仮に「http://abc.jp/xxx/xxxx/xxx%」とした場合、データが存在しないため、
 ref項目に「IX_url」が表示され、rows項目も1となります。

６：その他注意事項
 ・URL項目に対して、Likeではなく、=による絞り込みはNGとなります。
 ・URLを階層別にハッシュ化して、=による絞り込みもNGとなります。
 ・URLを１文字ずつずらしてハッシュ化して、=による絞り込みはOKですが、
  INDEXが最大数（64個）を超えてしまうため、出来ないと考えてます。
 ・MATCH AGAINSTはOKですが、この更新型テーブルにFULLTEXT INDEXを貼って効果があるかは、現在調査中です。

###発生している問題・エラーメッセージ
USE INDEX ( IX_url )やUSE KEY( IX_url )を利用しても、実行計画のref項目に「IX_url」は表示されず、
実際に実行してもものすごく時間がかかる（１０分経っても終わらない）状況です。
これを数秒以内に結果が返ってくる形にしたいと考えています。

###該当のソースコード
上記SQL参照

###試したこと
上記説明参照

###補足情報(言語/FW/ツール等のバージョンなど)
MySQLのバージョンは5.6.22
phpMyAdminのバージョンは4.5.1（ちょっと古い）

実行計画や速度が遅く感じると判断した際の動作環境は
phpMyAdmin上での実行結果になります。


###追記
check table tb_test_data;
を実行した場合、２時間経っても終わらないため、実施していません。

check table tb_test_data fast quick;
にて、テーブルの状態を確認していますが、
statusは「Table is already up to date」の状態で、問題ない認識です。

show table status like 'tb_test_data%';
にて、断片化は確認をしていて、0.6%程度の断片化率です。
（該当テーブルのURL以外の指定における性能劣化は見られていません。）

###追記２
検索用URL（100文字等）を作ってINDEXを貼って検索するのは、個人的には良いのですが、
仕様決定者的にはNGでした。。

###追記３
index(col7,col1,url(200))の追加案については、
col7での絞込件数≒実際の絞込件数（urlによる絞り込みがほとんどない）
であれば、Limitが効くため、性能は良いですが、
検索結果が「１件」の場合、Limitの効果がないため、性能遅延が発生してしまいます。

index(col1,col7,url(200))の追加案については、
全体の件数≒col7での絞込件数≒実際の絞込件数（col7,urlによる絞り込みがほとんどない）
であれば、Limitが効くため、性能は良いですが、
検索結果が「１件」の場合、Limitの効果がないため、性能遅延が発生してしまいます。

なお、上記２つのINDEXでは、URLのINDEXには届かないため、１件抽出する場合、即レスポンスにならないです。

INDEXさえ効けば、検索結果が１件の場合、即終わるハズなのですが、
実行計画の時点で効かない原因を知りたいです。。

###追記４
InnoDBについて

InnoDB化した場合、３００万の登録に６０分経っても終わらなかったため、INDEXが効くかどうかは分かりません。
別の仕様（毎秒１００件の削除・登録）が不可であるため、InnoDB化はできません。
（MyISAMであれば、３００万の登録に７分で終わります）
InnoDBは統計情報がきれいなはずなので、InnoDBであれば、確かに効く気はしています。

###追記５
INDEXが効いたSQLと効かないSQLがありましたので、情報共有いたします。
”存在しないURL”を指定すると、基本的に、INDEXが効きいて、1秒以内のレスポンスになりました。
ただし、"存在しないURL"が50文字以上となると、INDEXが効かず、300秒くらいのレスポンスになってしまいました。。

MySQL5.6.22のバグなんじゃないかと思い始めてます。。

###追記６
AkiraPenguin 様の回答により、解決いたしました！
ありがとうございます！！！！！

なお、追記５の問題も、ちょうど50文字目が「_」となっておりました。
「_」を含むURLは300万件存在するため、「_」の後に存在しないURLを置いていても、
それはINDEXが効きません。。。当然ですね。。。

たくさんの閲覧・回答ありがとうございました！

MySQL　大容量データ　INDEX　前方一致のLIKEが効かない

PostgreSQLを使用したシステムのDB設計を担当しています。

1年辺り2億件ぐらいのレコードが作成される想定のテーブルがあり、このテーブルについて懸念事項と対策について調べて欲しいと言われ、困っています。

自力で調べてはみたものの、あまりまとまらず…

PostgreSQL大規模テーブルの対策について

### 一般に固定長配列の方が可変長配列よりメモリを使わないといわれている理由を教えて頂けないでしょうか。
直感的には結局同じ要素数を格納するのであれば、固定長でも可変長でも要素の格納に必要なメモリの大きさは同じの気がします。可変長の場合は格納されている要素の情報以外にも何か情報を持っているのでしょうか？


よろしくお願い致します。

