MySQL:在给定表时使用自然主索引或添加代理索引

我有5个文本字段，我想导入到MySQL/MariaDB数据库。但是有两个问题:

(1)文件相当大:0.5 GB到10gb
(2)所有相关键都有40个字符

(1)我不得不接受现实，我无法改变它。第二点是我关心的。网上有很多建议。例如，为varchar使用enum或使用数字代理。向表中添加代理键没有问题。但是必须将相同的代理键添加到其他表中。这就是我卡住的地方。

这里是关于文件/表的具体信息:

表发票有3列20万行:
- invoice_id(主键)与不同的值=行数
- praxis_id有4000个不同的值
- patient_id有4个不同的值所有列都是CHAR(40)，固定长度为40。
表 diagnosis 有3列25万行:
- invoice_id CHAR(40) 1.4 Mio distinct id
- diagnose_type
- diagnose_code
table patient有5列5万行:
- patient_id CHAR(40) not unique (4 Mio distinct pat_id)
- praxis_id CHAR (40)
- 出生年份、性别等

例如，我想将invoice与diagnosis和patient连接起来。索引键是有意义的。一种方法是定义invoice。invoice_id作为主键，对于表invoice中的所有其他键，我将添加一个索引。对于表diagnosis (invoice_id with INDEX)和patient (patient_id为主键)也是如此。
问题是定义发票花了很长时间。

ALTER TABLE invoice_id ADD PRIMARY KEY(invoice_id);

一个小时后，我终止了这个过程。我认为一个性能问题是由表invoice中invoice_id的数据类型引起的。一种想法是在加载文本文件时添加一个自动递增的代理键invoice_id_surr。但是，如果我想与表诊断连接，问题仍然存在，因为我必须与表诊断的invoice_id连接，而表诊断没有代理键invoice_id_surr作为外键。我可以在diagnosis上添加一个索引。但是这样我就失去了在表invoice上使用代理键的优势。

我对如何处理这个问题的策略很感兴趣:几个已经存在的表可以连接在一起，但键是CHAR(40)并且没有索引。

谢谢你的帮助。

UPDATE 1: Table specification
-按键有40个字符[0-9][A-Z]
-这些表不会再改变(没有插入)

-- invoice_id is primary key (unique)
-- patient_id and praxis id for foreign key and not unique in this table
CREATE TABLE invoice (
  invoice_id             CHAR(40) DEFAULT NULL
, praxis_id              CHAR(40) DEFAULT NULL
, patient_id             CHAR(40) DEFAULT NULL
, PRIMARY KEY (invoice_id2)
) ENGINE = InnoDB
;  
LOAD DATA LOCAL INFILE 'C:/data/invoice.txt'
INTO TABLE invoice
FIELDS TERMINATED BY 't'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES
;
-- invoice_id is not unique in this table
CREATE TABLE diagnose (
  invoice_id             CHAR(40)    DEFAULT NULL
, diagnose_katalog       VARCHAR(20) DEFAULT NULL
, diagnose_code          VARCHAR(20) DEFAULT NULL
) ENGINE = InnoDB
;
-- patient_id is not unique in this table since since patient may change praxis
CREATE TABLE patient (
  patient_id             CHAR(40)    DEFAULT NULL
, praxis_id              CHAR(40)    DEFAULT NULL
, sex                    CHAR(1)     DEFAULT NULL
, birth_year             SMALLINT UNSIGNED DEFAULT NULL
, zip_code               VARCHAR(20) DEFAULT NULL
) ENGINE = InnoDB
;

由于各种原因，您应该避免在数据库中使用自然键作为主键，通过google搜索可以很容易地找到这些原因。

还有，"tables are given"是什么意思?您不应该坚持在文本文件和数据库表之间保持一对一的对应关系。相反，您应该以最适合您需求的方式设计表。力争常态化。

例如，您说patient"表"(您的意思是文件，对吗?)中的patient_id不是唯一的。显然，你需要一个病人独一无二的桌子;因此，创建一个具有不同patient_id及其属性的表。patient_id应该是该表上的唯一键，但是生成一个数字代理(例如auto_increment字段)作为该表中的主键。然后，例如，创建一个praxis表，用praxis_id作为唯一键，用数字代替主键。然后，您可以将patient和praxis与第三个表连接起来，这适用于多对多关系。这样就可以规范化数据库:患者及其属性始终是单个patient表中的单行;而现在，您在当前的patient文件/表中多次输入相同的患者，这迟早会给您带来麻烦。

你真的有CHAR(40)，没有VARCHAR(40)吗?值总是40个字符吗?表是否为CHARACTER SET utf8 ?

CHAR(40) utf8占用120字节总是。如果将'Z'存储在这样的字段中，它仍然需要120字节，而不仅仅是1字节。即使你要导入40个字符，将其声明为VARCHAR也会在加载时修剪尾部空白。

至少，我会做

ALTER TABLE foo
    MODIFY col1 VARCHAR(40) ...,  -- the "..." is other options for the col
    MODIFY col2 VARCHAR(40) ...,
    ...;

这可能对"点(1)"有很大帮助。让一切变得更快。(注意:完成ALTER需要很长时间)

"自然"主键并不是邪恶的。但要在适当的时候使用它们。在您的案例中，出于商业原因，invoice_id必须是唯一的，对吗?它有多长?这可能是一个很好的PK。

在你尝试做ALTER之前，你桌上有什么键?在最初构建表时，至少应该准备好PRIMARY KEY。(但我想现在太晚了。)

请为每个表提供SHOW CREATE TABLE -我不得不做太多的猜测。

添加代理键(AUTO_INCREMENT)为所有查找增加了一个间接级别;这个可能会减慢一些SELECTs(除了需要更改许多 SELECTs)。

在向表中添加二级索引时，要根据SELECT语句的使用情况来决定。我在我的索引食谱中讨论了这一点。此处提供SELECTs以供进一步讨论。不要盲目地为每一列添加索引。

听起来invoice_id应该是invoice的PRIMARY KEY, diagnose的INDEX。

如果它是实际的重新加载数据，声明字段VARCHAR，而不是CHAR，并有PRIMARY KEY(invoice_id)在invoice .

我回答自己的问题。这个帖子的原因是我提交了查询，例如用ALTER TABLE ...定义一个主键，这个过程在几个小时后没有停止。正如@zgguv提到的，持续时间似乎不可信。我停止查询并重新启动(有时在第三次之后)，该过程在几分钟后完成(大约10分钟)。我不知道为什么有时查询会挂起。直到现在我才遇到这种情况，但我使用的桌子要小得多。我们得到的教训是:

长字符串键应该替换为数字键，以使选择更快。
分别替换大小为10gb的表(text-table)的字符串键，20万个行号是可行的(你只需要自己拉一次头发)。两个已索引的字符串键之间的连接大约需要10分钟。
如果查询的持续时间超过30'(挂机)，则停止查询并重试。很高兴知道为什么会发生这种情况(InnoDB, MyISAM, HeidiSQL等)，但这是另一个问题。

@zgguv感谢您的支持和耐心。

相关内容

最新更新

热门标签：