SQLite:排序我的选择结果 [英] SQLite: Ordering my select results

问题描述

我有一个表，其中包含唯一的用户名和我正在跟踪的一堆字符串数据.每个用户将有 1000 行，当我选择它们时，我想按照添加的顺序返回它们.以下代码是否是执行此操作的必要且正确的方法:

I have a table with unique usernames and a bunch of string data I am keeping track of. Each user will have 1000 rows and when I select them I want to return them in the order they were added. Is the following code a necessary and correct way of doing this:

CREATE TABLE foo (
  username TEXT PRIMARY KEY,
  col1 TEXT,
  col2 TEXT,
  ...
  order_id INTEGER NOT NULL
);

CREATE INDEX foo_order_index ON foo(order_id);

SELECT * FROM foo where username = 'bar' ORDER BY order_id;

推荐答案

如果每个用户有 1000 行，那么 username 不应该是主键.一种选择是使用所有表都具有的 int 标识列(它优化了 I/O 读取，因为它通常按该顺序存储).

If each user will have 1000 rows, then username should not be the primary key. One option is to use the int identity column which all tables have (which optimizes I/O reads since it's typically stored in that order).

阅读RowIds 和整数主键"@http://www.sqlite.org/lang_createtable.html

Read under "RowIds and the Integer Primary Key" @ http://www.sqlite.org/lang_createtable.html

SQLite中每张表的数据都存储为B-Tree结构包含每个表行的条目，使用 rowid 值作为键.这意味着按 rowid 检索或排序记录很快.

The data for each table in SQLite is stored as a B-Tree structure containing an entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast.

因为它在 B 树结构中按该顺序存储，所以按 int 主键排序应该很快.确保它是 rowid 的别名 - 在那篇文章中有更多内容.

Because it's stored in that order in the B-tree structure, it should be fast to order by the int primary key. Make sure it's an alias for rowid though - more in that article.

此外，如果您要在 username = 'bob' 的情况下进行查询，您应该考虑在 username 列上建立索引 - 特别是会有很多用户，因为高选择性使索引有效.相比之下，在具有 1 和 0 之类的值的列上添加索引只会导致低选择性并使索引非常无效.所以，如果你有 3 个用户 :) 那就不值得了.

Also, if you're going to be doing queries where username = 'bob', you should consider an index on the username column - especially there's going to be many users which makes the index effective because of high selectivity. In contrast, adding an index on a column with values like 1 and 0 only leads to low selectivity and renders the index very ineffective. So, if you have 3 users :) it's not worth it.

这篇关于SQLite:排序我的选择结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！