解決したいこと
SQLite3 の DB 内に登録されているレコードの「総数」をコードで取得するシンプルな方法が知りたい。
Go 言語（以下 Golang）の v1.18 で、"database/sql" 標準パッケージと "go-sqlite3" ドライバを使い、読み込み専用の CLI 版の辞書アプリを作っています。
このアプリで、辞書用語の登録数（総レコード数）を取得したいのですが、データ数が多いと 1 分ほど時間がかかります。
現在、DB 内の総レコード数の算出に、DB 内のテーブルの一覧を取得したのち、ループで各テーブルのレコード数を COUNT で取得した総数を利用しています。
しかし、SQL 文を駆使しなくても、おそらく、もっと簡単に取得できる方法があるのではと思い質問いたしました。SQLite3 の FAQ や SQLite コマンドのヘルプなども参照してみたのですが、見つかりませんでした。
ご存知の方がいらっしゃいましたら、お教えください。m(_ _)m
【結論】（2022/08/19 自己解決）
SQLite3 自体は、仕様によりテーブルのレコード数を保持していないため（コメント参照）「レコード数を保持するカウント用のテーブルを作成」し、以下のいずれかのパターンで更新するのが現実的と思われます。
- すべてのレコードを挿入し終わった後に、カウントテーブルを更新する。
-
INSERT DELETE にトリガーを設定しておき、随時カウントテーブルを増減更新する。
読み取り専用の DB であれば、上記 1 番が良さそうです。Qiita 記事にしました。
発生している問題
DB 構成
各テーブルが rowid key value の 3 列だけで構成された DB ですが、rowid は key 列のエイリアスになっており、実質 2 列の Key-Value 型のシンプルな DB です
検索したい辞書用語をハッシュ関数にかけ、ハッシュ値の頭 8 バイトを rowid/key とし、ハッシュ値の XOR サム値をテーブル名にしています。そして value 列にはコンテンツを gob エンコードした BLOB 値が入っています。
検索用語をメソッドに渡すと gob エンコードされた用語の内容が返され、アプリはデコードした内容を表示する。これまたシンプルなものです。
試しに Wikipedia の日本語版を突っ込んでみたのですが、テーブル数は 256、用語数（データ数）は 260 万件、DB のファイルサイズは 13 GB になりました。
CLI アプリの引数に検索用語を指定して実行すると、DB を開いて用語を表示するまでの速度は 25 ミリ秒前後なので、検索速度自体は個人的に問題ありませんでした。
問題は、DB 内の総レコード数を表示する際に 1 分ほど時間がかかることです。
該当するソースコード
DB 内の総レコード数の算出に 2 ステップかけています。DB 内のテーブル名を取得し、ループで各々のテーブルのレコード数を COUNT で加算したものを返しています。
1) テーブル名一覧の取得
GetTable()
// GetTable は db のテーブル名一覧を文字列のスライスで返します。
func GetTable(db *sql.DB) ([]string, error){
result := []string{}
q := `SELECT name FROM sqlite_master WHERE type = 'table';`
res, err := db.Query(q)
if err != nil {
return nil, errors.Wrap(err, "failed to query database")
}
var table string
for res.Next() {
if err := res.Scan(&table); err != nil {
return nil, errors.Wrap(err, "failed to scan table name")
}
result = append(result, table)
}
sort.Slice(result, func(i, j int) bool {
return result[i] < result[j]
})
return result, nil
}
2) DB 内の総レコード数の取得
GetTotalRecords()
// GetTotalRecords は db 内の総レコード数を返します
func GetTotalRecords(db *sql.DB) int {
talbes, err := GetTable(db)
if err != nil {
return -1
}
const queryTPL = `SELECT COUNT(*) FROM %s;`
result := int(0)
for _, table := range talbes {
queryTMP := fmt.Sprintf(queryTPL, table)
res, err := k.db.Query(queryTMP)
if err != nil {
return -1
}
if res.Next() {
var count int
res.Scan(&count)
result += count
}
}
return result
}
自分で試したこと
- ループ前に
Prepare でステートメント化。
- 総数を保持するだけの専用テーブルを用意し、挿入時にインクリメント。
- テーブルをグループ化し、
COUNT 値を TOTAL で取得。
やはり上記の 2 が一番速かったのですが、挿入・削除時の漏れをチェックするための処理が結局「総数の算出」と同じため、いささか冗長に思いました。.table などのように、抜本的に簡単な方法がないかと思い質問いたしました。
おそらく .dbinfo の SQLite コマンドが一番近い情報を返しそうなのですが、SQLite3 のコンパイル時に -DSQLITE_ENABLE_DBPAGE_VTAB オプションを付けていないものを利用しているため取得できません。
ご存知の方、いらっしゃいましたら何卒ご教授ください。
