SQLite 表磁盘使用情况

Question

如何找出 SQLite 数据库中单个表的磁盘使用情况，而不将其复制到新的空数据库中？

Answer 1

您可以使用 sqlite3_analyzer www.sqlite.org/download.html" rel="noreferrer">https://www.sqlite.org/download.html。

这是一个非常酷的工具。它显示每个带索引和不带索引的表使用的页数（默认情况下，每个页为 1024 字节）。

这是 Northwind 数据库的 sqlite3_analyzer 输出示例：

*** Page counts for all tables with their indices ********************

EMPLOYEES............................. 200         34.4% 
ORDERS................................ 152         26.2% 
CATEGORIES............................ 90          15.5% 
ORDER DETAILS......................... 81          13.9% 
CUSTOMERS............................. 17           2.9% 
SQLITE_MASTER......................... 11           1.9% 
PRODUCTS.............................. 7            1.2% 
SUPPLIERS............................. 7            1.2% 
TERRITORIES........................... 6            1.0% 
CUSTOMERCUSTOMERDEMO.................. 2            0.34% 
CUSTOMERDEMOGRAPHICS.................. 2            0.34% 
EMPLOYEETERRITORIES................... 2            0.34% 
REGION................................ 2            0.34% 
SHIPPERS.............................. 2            0.34% 

它还生成 SQL 语句，可用于创建包含使用情况统计信息的数据库，然后您可以对其进行分析。

Answer 2

可以从 DBSTAT 虚拟中获取每个表或索引使用的所有页面的详细信息表，也可以聚合它来获取每个表或索引的磁盘使用情况。

例如，运行此查询以获取使用最多磁盘空间的前 10 个表：

SELECT name, sum(pgsize) AS size FROM dbstat GROUP BY name
  ORDER BY size DESC LIMIT 10;

Answer 3

如果您使用的是 Linux 或 OSX，或者有 unix 实用程序 awk（以及可选的排序）可用，您可以执行以下操作以通过转储分析获取计数和估计大小：

# substitute '.dump' for '.dump mytable' if you want to limit to specific table
sqlite3 db.sqlite3 '.dump' | awk -f sqlite3_size.awk

返回：

table            count   est. size
my_biggest_table 1090    60733958
my_table2        26919   7796902
my_table3        10390   2732068

并使用 awk 脚本：

/INSERT INTO/ {                              # parse INSERT commands
    split($0, values, "VALUES");             # extract everything after VALUES
    split(values[1], name, "INSERT INTO");   # get tablename
    tablename = name[2];                     #
    gsub(/[\047\042]/, "", tablename);         # remove single and double quotes from name
    gsub(/[\047,]/, "", values[2]);          # remove single-quotes and commas
    sizes[tablename] += length(values[2]) - 3; # subtract 3 for parens and semicolon
    counts[tablename] += 1;
}

END {
    print "table\tcount\test. size"
    for(k in sizes) {
        # print and sort in descending order:
        print k "\t" counts[k] "\t" sizes[k] | "sort -k3 -n -r";

        # or, if you don't have the sort command:
        print k "\t" counts[k] "\t" sizes[k];
    }
}

估计大小为基于“INSERT INTO”命令的字符串长度，因此不会等于磁盘上的实际大小，但对我来说，计数加上估计大小比其他替代方案（例如页计数）更有用。

Answer 4

我意识到这个答案完全违反了问题的精神，但它确实可以让您在不复制文件的情况下获得大小......

$ ls -lh db.sqlite
-rw-r--r-- 1 dude bros 44M Jan 11 18:44 db.sqlite
$ sqlite3 db.sqlite
sqlite> drop table my_table;
sqlite> vacuum;
sqlite> ^D
$ ls -lh db.sqlite
-rw-r--r-- 1 dude bros 23M Jan 11 18:44 db.sqlite

Answer 5

我在这里遇到了其他答案的问题（即 sqlite_analyzer 不适用于 Linux）。 '最终创建了以下 Bash 函数来（临时）将每个表写入磁盘，作为评估磁盘大小的一种方式。从技术上讲，这是复制数据库，这不符合OP问题的精神，但它给了我我想要的信息。

function sqlite_size() {
  TMPFILE="/tmp/__sqlite_size_tmp"
  DB=$1
  IFS=" " TABLES=`sqlite3 $DB .tables`
  for i in $TABLES; do
    \rm -f "$TMPFILE"
    sqlite3 $DB ".dump $i" | sqlite3 $TMPFILE
    echo $i `cat $TMPFILE | wc -c`
    \rm -f "$TMPFILE"
  done
}

例子：

$ sqlite_size sidekick.sqlite
SequelizeMeta 12288
events 16384
histograms 20480
programs 20480