SQLite 主键、RowID关系

默认情况下，SQLite会自动存在一个RowID列，从1开始，每添加一条记录+1

当设置了主键，而且主键的类型为integer时，查询RowID等于主键

下图为主键设置为integer时，对rowid和主键的查询情况

create table aaa(id integer PRIMARY KEY,aaa ntext)

 

只有在设置为Integer时才会替代rowid，设置为int或其它都不行，下图为主键设置成int类型

create table aaa(id int PRIMARY KEY,aaa ntext)

 

 

写入效率对比：

插入500W记录，不设置主键，利用rowid，写入时间1分13秒

插入500W记录，设置Integer主键，写入时间1分19秒

 

检索效率对比：

对设置了integer主键的表，查询主键100W次，耗时1分20秒

对设置了integer主键的表，查询rowid 100W次，耗时1.22秒

对不设置主键的表，查询rowid 100W次，耗时1.23秒

(这2秒左右的差距，判断有两种可能，第一是程序运行时误差，第二种可能是因为主键是ID，比rowid长度小，所以拼接sql语句时，要占时间优势)

 

检索优化：

每次检索对command赋值，耗时约1分20秒 

            SQLiteCommand cmd = new SQLiteCommand(conn);
            for (int i = 0; i < 1000000; i++)
            {
                cmd.CommandText = "select * from aaa where id=" + (1000000 + i);
                cmd.ExecuteNonQuery();
            }

每次检索对参数赋值，耗时约58秒

            SQLiteCommand cmd = new SQLiteCommand(conn);
            cmd.CommandText = "select * from aaa where id=@id";
            cmd.Parameters.Add("id", DbType.Int32);
            for (int i = 0; i < 1000000; i++)
            {
                cmd.Parameters[0].Value = (1000000 + i);
                cmd.ExecuteNonQuery();
            }

 

SQLite文件大小：

大小基本保持一致（但我发现不设置主键，添加一个ID字段为int时，文件大小也是一样的，猜测可能是SQLite预留的B Tree的空间）

 

 结论：

不管是使用rowid还是自定义主键，查询效率基本相同，写入效率使用独立主键要损失大约8%的效率，不过好处是可以自己管理主键的内容