mysql - 如何保存由其他两个表组成的临时表的位置？

Question

我正在构建审核引擎，其中审核者应该能够保存最后看到的消息的位置，假设总消息实体应该从 100k 到数百万条记录。

消息数据库结构由两个表question和reply组成，表示如下:

问题表:

+-------------------------+------------------+------+-----+---------------------+----------------+
| Field                   | Type             | Null | Key | Default             | Extra          |
+-------------------------+------------------+------+-----+---------------------+----------------+
| entity_id               | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| text                    | mediumtext       | NO   |     | NULL                |                |
| customer_id             | int(10) unsigned | YES  | MUL | NULL                |                |
+-------------------------+------------------+------+-----+---------------------+----------------+

和reply表，parent_id是FK到question.entity_id:

+-------------+------------------+------+-----+-------------------+----------------+
| Field       | Type             | Null | Key | Default           | Extra          |
+-------------+------------------+------+-----+-------------------+----------------+
| entity_id   | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| text        | mediumtext       | NO   |     | NULL              |                |
| customer_id | int(10) unsigned | YES  | MUL | NULL              |                |
| parent_id   | int(10) unsigned | NO   | MUL | NULL              |                |
+-------------+------------------+------+-----+-------------------+----------------+

SQL fiddle :http://sqlfiddle.com/#!9/a06077

因为这是两个具有父子关系的独立表，所以我唯一的想法就是创建一个临时复合表messages，如下所示:

question1->reply1.1->reply1.2->reply1.3->question2->reply2.1->reply2.2... etc

并从主持人查看的最后保存的位置开始对其进行切片，假设最后保存的位置是此临时表中最后查看的消息的 id。

所以，问题是:

1. 如果结构如下，如何正确编写 SQL 以在临时表中插入问题回复:

   CREATE TEMPORARY TABLE tmp (
           id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
           text  varchar(16),
           is_question boolean
       );
   

2. 假设消息和回复会不断增长，如何正确构建这个临时表？

3. 它会影响大量条目的性能(我们使用 MySQL)吗？
4. 是否有更好的方法来实现此类功能？

我在大型数据库构建方面没有太多经验，并且希望从更有经验的工程师那里获得一些建议或批评或关于此任务的任何想法。

Answer 1

如果有人说花 20 分钟展示一下，这是我的建议。

create database QRMod123xyz; -- create a new safe sandbox away from your tables
use QRMod123xyz; -- use this db

drop table if exists reply; -- reverse order on drop (at least for the FK ones at the moment)
drop table if exists question; -- this comes second
drop table if exists moderator_qr_seen_history;

create table question 
(   question_id int auto_increment key primary key, 
    theText mediumtext, -- don't use reserved or keywords for column names
    customer_id int  
    -- dont forget useful indexes and FK to customer table 
)engine=INNODB; 

create table reply 
(   reply_id int auto_increment key, 
    theText mediumtext, 
    question_id int not null, 
    sequence_id int not null, -- sequence of read (1..n) per question_id. See notes at bottom.
    customer_id int not null, -- could be more than 1 customer for a given question id in theory 
    theDT datetime not null, 
    unique key (question_id,sequence_id), 
    constraint fk_question foreign key (`question_id`) references question(`question_id`) 
    -- dont forget useful indexes and FK to customer table 
)engine=INNODB;

create table moderator_qr_seen_history 
(   -- modify this to your heart's content depending on your intentions
    id int auto_increment primary key,
    question_id int not null,
    reply_id int not null,
    moderator_id int not null,
    theText mediumtext, 
    customer_id int not null,
    theDT datetime not null,    -- when seen (note, a moderator can have several rows for a "Q R" combo
    key(question_id,moderator_id)   -- NOTE Aboy this will narrow things down (big time filter)
   -- dont forget useful indexes and all FK's
)engine=INNODB;

... do other stuff (save your scripts !)


drop database QRMod123xyz; -- cleanup when complete

注意 A(以上引用):该键将通过大时间过滤器缩小范围。但它不会是一个覆盖索引。这只是一个想法。只有您在充分测试后才知道您的索引策略应该是什么。

另请参阅Keywords and Reserved Words 。不要在列名称中使用它们。

我不会使用临时表。

我将通过包含 FOR UPDATE 的打包交易获取 sequence_id锁定 question 表作为获取该问题的下一个可用序列的网关(通过 ifnull(max(sequence_id),0))。插入带有下一个可用数字的回复递增。然后COMMIT。

下面是一个简单案例中 ifnull max 的快速可视化。但您可以通过适当的 where 子句针对 reply 表使用它。请注意，这支持规范化和交易安全性。

create table a (i int not null,sequence_id int not null);
insert a(i,sequence_id) values (1,1),(1,2);
select ifnull(max(sequence_id),0) as prevMax from a where i=1;
-- answer is 2
select ifnull(max(sequence_id),0) as prevMax from a where i=7;
-- answer is 0

这是我在 20 分钟内为您提供的最好信息。