外键 vs 分区

Question

由于分区的MySQL数据库目前不支持外键，因此我希望听到一些针对阅读量较大的应用程序的优缺点，每个表格将处理约1-400,000行。不幸的是，我在这个领域还没有足够的经验来做出自己的结论...

非常感谢！

参考资料：

如何在分区时处理外键

具有外键的MySQL分区表？

- Industrial

---

现在是否可以在12年后使用外键对MySQL表进行分区？ - Kundan

Answer 1

如果您需要对包含400,000行的表进行分区，请选择除MySQL之外的其他数据库。说真的，按照现代标准，任何不到1,000,000行的表都可以忽略不计（甚至不算小），除非您也没有任何索引等。在这方面，现代标准已经有大约10年的历史了。

- TomTom

---

好的，我没有找到任何关于是否分区的建议。我认为即使对于小表来说，这也会产生明显的差异。 - Industrial

不是的。分区基本上是当你在使用一个表时遇到问题时的解决方案。例如，因为你按年份记录销售情况，删除某一年的所有销售记录会非常麻烦，但是通过分区，你只需要删除该年份的表即可。有些小事情看起来毫无意义，但实际上却非常重要。 - TomTom

非常感谢TomTom。感谢你的回答。与分区一起工作的删除部分是显而易见的，但根据我之前听到的，分区表始终比非分区表效果更好，即使数据集较小。然而，我不知道外键与分区的性能增加比较。似乎这是一个信息很少的主题。也许我将不得不通过基准测试和样本数据来找出答案... - Industrial

Answer 2

分区对于复杂的数据模型并不是一个好的解决方案。如果你只有2到3张互相依赖的表，你可能可以这样做，但这并不美观。每张表都必须有一个确定分区的列。然后，每张表还必须有一个触发器来创建新表，并设置外键和唯一约束。

例如， audittransaction <- auditentry

每个audittransaction可以有0到n个auditentry。表auditentry包含了transaction的外键。这两个表都必须有creationDate列，因为它用于对这两个表进行分区。

------ 在触发器中创建插入audittransaction的触发器

create or replace function audittransaction_insert_function() 
returns trigger as $$ 
DECLARE

    tablepartition varchar;
    tablename varchar;
    startbounds timestamp;
    endbounds timestamp;                


BEGIN
    tablepartition :=  to_char(date_trunc('month', NEW.whendone), 'YYYYMMDD');  
    tablename := 'audittransaction_' || tablepartition ;        

    if not exists(select * from information_schema.tables where table_name = tablename) then
        startbounds := date_trunc('month', NEW.whendone);
        endbounds := startbounds + cast('1 months' as interval);
        execute 'create table ' || tablename || ' ( CHECK (whendone >= ' || quote_literal(startbounds) || ' and whendone < ' || quote_literal(endbounds)|| ') ) inherits (audittransaction)';
        execute 'ALTER TABLE '||  tablename ||' ADD CONSTRAINT '||tablename||'_unique_id UNIQUE (id)';          
    end if;     
    execute 'insert into ' || tablename || ' (id, operationid, whendone, "comment", ticketid ,transactionid, userid )  values (' || quote_literal(NEW.id) || ',' || quote_literal(NEW.operationid) || ',' || quote_literal(NEW.whendone) || ')';                
    return null; 
END; $$ 
LANGUAGE plpgsql;

create trigger insert_audittrans

----- 然后，为自动输入创建触发器

create or replace function auditentry_insert_function() 
returns trigger as $$ 
DECLARE
    tablepartition varchar;
    tablename varchar;
    startbounds timestamp;
    endbounds timestamp;                


BEGIN
    tablepartition :=  to_char(date_trunc('month', NEW.transactiontimestampgmt), 'YYYYMMDD');   
    tablename := 'auditentry_' || tablepartition ;

    if not exists(select * from information_schema.tables where table_name = tablename) then
        startbounds := date_trunc('month', NEW.transactiontimestampgmt);
        endbounds := startbounds + cast('1 months' as interval);
        execute 'create table ' || tablename || ' ( CHECK (transactiontimestampgmt >= ' || quote_literal(startbounds) || ' and transactiontimestampgmt < ' || quote_literal(endbounds)|| ') ) inherits (auditentry)';
        execute 'ALTER TABLE '||  tablename ||' ADD CONSTRAINT '||tablename||'_unique_id UNIQUE (id)';  
        execute 'ALTER TABLE ' || tablename ||' ADD CONSTRAINT auditentry FOREIGN KEY (audit_transaction_id) REFERENCES audittransaction_'||tablepartition ||'(id)';                
    end if;     
    execute 'insert into ' || tablename || ' (id, audit_transaction_id, eventid, transactiontimestampgmt,timestampgmt, acknowledged, resolved, acknowledgedbyusername, acknowledgeddate,  notificationlevel, resolvedbyusername, resolveddate, severity,  parentauditentry_id )  values (' || quote_literal(NEW.id) || ',' || quote_literal(NEW.audit_transaction_id) || ',' || quote_literal(NEW.eventid) || ','||quote_literal(NEW.transactiontimestampgmt)||')';             
    return null; 
END; $$ 
LANGUAGE plpgsql;

create trigger insert_auditentry before insert on auditentry for each row execute procedure auditentry_insert_function();

- binaryatwork