我知道对主键使用 INTEGER 字段通常是最佳实践,但不幸的是,由于我正在使用的 API,我只能拥有格式为:CHAR_INT(例如:ABC_12345)的主键。
我将有大量数据(+10 亿条记录)并且查询和插入速度是优先考虑的,使用 CHAR_INT 主键对速度有很大影响吗?还是相对可以忽略不计?
另外,为字符串的 CHAR 部分创建数字 ID 会更有效吗?所以使用前面的例子:ABC_12345 会变成类似于 1_12345 的东西。我知道它们都是字符串,只是想知道仅使用数字是否有效率。
我正在使用 SQLite。
谢谢!
除了一个例外,类型(类型关联)没有真正的区别。
例外是特别the_column_name INTEGER PRIMARY KEY
(有或没有 AUTOINCREMENT),它将列定义为rowid列的别名。INT PRIMARY KEY 没有。
so the_column_name CHAR_INT PRIMARY KEY
,the_column_name INT CHAR PRIMARY KEY
甚至the_column_name INT PRIMARY KEY
实际上是相同的,the_column_name RUMPLESTILTSKIN PRIMARY KEY
甚至可以使用(尽管后者具有不同的类型关联)。
确定类型关联的是规则。有 5 个。具有最高优先级的规则是,如果类型具有 INT,则类型关联为 INTEGER。RUMPLESTILTSKIN 作为一种类型会通过除最后一条规则之外的所有规则,如果前面的规则均不适用,则类型关联为 NUMERIC。
3.1. 色谱柱亲和性的测定
列的亲和性由列的声明类型决定,按照以下规则显示的顺序:
如果声明的类型包含字符串“INT”,则为它分配 INTEGER 亲和性。
如果列的声明类型包含任何字符串“CHAR”、“CLOB”或“TEXT”,则该列具有 TEXT 亲和性。请注意,类型 VARCHAR 包含字符串“CHAR”,因此被分配了 TEXT 亲和性。
如果列的声明类型包含字符串“BLOB”或未指定类型,则该列具有亲缘关系 BLOB。
如果列的声明类型包含任何字符串“REAL”、“FLOA”或“DOUB”,则该列具有 REAL 亲和性。
否则,亲和力为 NUMERIC。
请注意,确定列亲和性的规则顺序很重要。声明类型为“CHARINT”的列将同时匹配规则 1 和规则 2,但第一个规则优先,因此列亲缘关系将为 INTEGER。
说类型亲和并不能决定数据的存储方式。每一列都根据依赖于所存储数据的存储类进行存储。
Null 存储为空值,数字字符串(包含或不作为字符串)作为整数存储。简而言之,数据将按照 SQLite 确定的方式存储,SQlite 将尝试尽可能有效地存储数据,并在尽可能小的空间内存储到字节作为存储的最小单位。
考虑以下 :-
DROP TABLE IF EXISTS mytable1;
DROP TABLE IF EXISTS mytable2;
DROP TABLE IF EXISTS mytable3;
CREATE TABLE IF NOT EXISTS mytable1 (c1 CHAR_INT PRIMARY KEY);
CREATE TABLE IF NOT EXISTS mytable2 (c1 INT PRIMARY KEY);
CREATE TABLE IF NOT EXISTS mytable3 (c1 RUMPLEstiltSkin PRIMARY KEY);
-- INSERT INTO mytable1 VALUES(12345),('12345'),('a_12345'),('1_12345'),(x'0102030405'); -- fails due to unique constraint 12345 and '12345' are the same
-- INSERT INTO mytable2 VALUES(12345),('12345'),('a_12345'),('1_12345'),(x'0102030405'); -- fails due to unique constraint 12345 and '12345' are the same
-- INSERT INTO mytable3 VALUES(12345),('12345'),('a_12345'),('1_12345'),(x'0102030405'); -- fails due to unique constraint 12345 and '12345' are the same
INSERT INTO mytable1 VALUES(12345),('54321'),('a_12345'),('1_12345'),(x'0102030405');
INSERT INTO mytable2 VALUES(12345),('54321'),('a_12345'),('1_12345'),(x'0102030405');
INSERT INTO mytable3 VALUES(12345),('54321'),('a_12345'),('1_12345'),(x'0102030405');
SELECT c1, typeof(c1) FROM mytable1;
SELECT c1, typeof(c1) FROM mytable2;
SELECT c1, typeof(c1) FROM mytable3;
所述的typeof函数返回柱(存储型NOT柱亲和力)的类型
结果如下:-
使用除 INTEGER PRIMARY KEY 以外的任何东西(有一些派生),因此 rowid 的别名是
有两个索引,rowid(除非 TABLE 被定义为 WITHOUT ROWID)和 PRIMARY KEY。
搜索具有特定 rowid 的记录或具有指定范围内的 rowid 的所有记录的速度大约是通过指定任何其他 PRIMARY KEY 或索引值进行的类似搜索的两倍。ROWID 和整数主键
处理数字而不是字符串会消耗更多空间,因此会减少缓冲区中可以保存的数据,因此会产生一些影响。
搜索一个索引,是比较快的,相对于数据本身,数据比较少,数据本身只是读取。
也许考虑以下几点:-
DROP TABLE IF EXISTS mytable1;
DROP TABLE IF EXISTS mytable2;
DROP TABLE IF EXISTS mytable3;
CREATE TABLE IF NOT EXISTS mytable1 (pk INT PRIMARY KEY, name TEXT);
CREATE TABLE IF NOT EXISTS mytable2 (pk CHAR_INT PRIMARY KEY, name TEXT);
CREATE TABLE IF NOT EXISTS mytable3 (pk INT PRIMARY KEY, name TEXT) WITHOUT ROWID;
INSERT INTO mytable1
WITH RECURSIVE cte1(a,b) AS (
SELECT 'ABC_'||CAST(abs(random()) AS TEXT),'some data' UNION ALL
SELECT DISTINCT (substr(a,1,4))||CAST(abs(random()) AS TEXT),'some data' FROM cte1 LIMIT 1000000
)
SELECT * FROM cte1
;
INSERT INTO mytable2
WITH RECURSIVE cte1(a,b) AS (
SELECT '1_'||CAST(abs(random()) AS TEXT),'some data' UNION ALL
SELECT DISTINCT (abs(random()) % 100)||'_'||CAST(abs(random()) AS TEXT),'some data' FROM cte1 LIMIT 1000000
)
SELECT * FROM cte1
;
INSERT INTO mytable3 SELECT * FROM mytable1;
SELECT * FROM mytable1 WHERE name LIKE('%me data%');
SELECT * FROM mytable2 WHERE name LIKE('%me data%');
SELECT * FROM mytable3 WHERE name LIKE('%me data%');
SELECT * FROM mytable3 WHERE name LIKE('%me data%');
SELECT * FROM mytable1 WHERE name LIKE('%me data%');
SELECT * FROM mytable2 WHERE name LIKE('%me data%');
SELECT * FROM mytable2 WHERE name LIKE('%me data%');
SELECT * FROM mytable3 WHERE name LIKE('%me data%');
SELECT * FROM mytable1 WHERE name LIKE('%me data%');
这将创建 3 个排列,所有排列都有 1,000,000 行
时间安排
所有 3 个的 SELECTS 都非常接近(完成多个选择并以不同的顺序均匀缓存)。包括时间的消息是:-
SELECT * FROM mytable1 WHERE name LIKE('%me data%')
> OK
> Time: 0.672s
SELECT * FROM mytable2 WHERE name LIKE('%me data%')
> OK
> Time: 0.667s
SELECT * FROM mytable3 WHERE name LIKE('%me data%')
> OK
> Time: 0.702s
SELECT * FROM mytable3 WHERE name LIKE('%me data%')
> OK
> Time: 0.7s
SELECT * FROM mytable1 WHERE name LIKE('%me data%')
> OK
> Time: 0.675s
SELECT * FROM mytable2 WHERE name LIKE('%me data%')
> OK
> Time: 0.673s
SELECT * FROM mytable2 WHERE name LIKE('%me data%')
> OK
> Time: 0.676s
SELECT * FROM mytable3 WHERE name LIKE('%me data%')
> OK
> Time: 0.709s
SELECT * FROM mytable1 WHERE name LIKE('%me data%')
> OK
> Time: 0.676s
除了前面的链接,您可能还希望查看:-
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句