外键与分区

Question

由于分区 mySQL 数据库目前不支持外键，因此我想听听对于每个表将处理大约 1-400 000 行的读取密集型应用程序的一些优点和缺点。不幸的是，我在这方面还没有足够的经验来自己得出结论......

非常感谢！

参考文献：

如何在分区时处理外键

对具有外键的 mySQL 表进行分区？

Answer 1

好吧，如果您需要对小至 400.000 行的表进行分区，请使用 MySQL 以外的另一个数据库。严重地。按照现代标准，任何低于 1.000.000 行的表的大小通常可以忽略不计（甚至不算小），除非您也没有任何索引等。而现代标准在这方面已有大约 10 年的历史。

Answer 2

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

例如，
audittransaction<-auditentry

每个audittransaction有0到n个auditentry。表auditentry包含事务的外键。两个表都必须具有创建日期列，因为它用于对两个表进行分区。

------ 创建一个触发器，在触发器内插入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

----- 然后，为autientry 创建一个触发器

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();