我想创建一个包含 3 个主要实体的数据库,每个实体都将进一步细分(通过子类型,我的意思是泛化-专业化)。
该数据库将支持用户只能读取数据库的网站。我不知道每天有多少用户会访问该网站。正如我所看到的那样,数据库将总共有 500,000 行。
每周一次,我想用各种实体的 ALL 中的大约 10 条记录更新数据库。
我应该使用 IndoDB 吗?我可以进行哪些优化,因为这将是一个“主要读取”的数据库。停止网站5分钟,上传每周10条记录是否明智?或者可以在网上完成吗?
这是 MariaDB 中的一个示例。虽然我有 3 个实体,但为了一个简单的示例,我使用了一个具有子类型的实体 Person。
DROP DATABASE IF EXISTS Person_Database;
CREATE DATABASE Person_Database;
Use Person_Database;
人分为 2 类 - 内部人员和外部人员 每个人都是内部人员或外部人员中的一个。这是 Gen-Spec 的一个示例,因为 2 个类别中的每一个都有一些共同的变量和一些特定的变量。我所说的 Gen-Spec 是指泛化-专业化。
CREATE TABLE Person_Category(id TINYINT NOT NULL,category CHAR(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO Person_Category(id,category)
VALUES
(1,"Insider"),
(2,"Outsider");
内部人员进一步分为 3 个类别中的一个 - 队友、校友和顾问。这不是 Gen-Spec 模式,因为所有 3 个类别都具有相同的变量。
CREATE TABLE Insider_Person_Category (id TINYINT NOT NULL,
category CHAR(10) NOT NULL,
CONSTRAINT Insider_Person_Category_PK PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO Insider_Person_Category (id, category)
VALUES
(1, "Team-mate"),
(2, "Alumni"),
(3, "Advisor");
CREATE TABLE Person(id int NOT NULL AUTO_INCREMENT,
first_name CHAR(20),
last_name CHAR(20),
category_id TINYINT NOT NULL,
CONSTRAINT Person_PK PRIMARY KEY (id),
CONSTRAINT FOREIGN_KEY (category_id) REFERENCES Person_Category(id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE Insider_Person(id int NOT NULL,
insider_category TINYINT NOT NULL,
Person_Image MEDIUMBLOB,
email_address VARCHAR(100),
Designation VARCHAR(100),
Biography TEXT,
CONSTRAINT Insider_Person_FK1 FOREIGN_KEY (insider_category) REFERENCES Insider_Person_Category(id),
CONSTRAINT Insider_Person_PK PRIMARY KEY(id),
CONSTRAINT Insider_Person_FK2 FOREIGN_KEY (id) REFERENCES Person(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE Outsider_Person (id int NOT NULL,
Person_URL TEXT,
Person_Detailed_Designation TEXT,
CONSTRAINT Outsider_Person_PK PRIMARY KEY(id),
CONSTRAINT Outsider_Person_FK FOREIGN_KEY (id) REFERENCES Person(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE USER 'user1-read-only'@'localhost' IDENTIFIED BY 'password1';
CREATE USER 'user2-never-login-as-this-user'@'localhost' IDENTIFIED BY 'password2'
CREATE USER 'user3-read-n-use-procedure'@'localhost' IDENTIFIED BY 'password3';
- 'user1-read-only' is the usual read only user
- 'user2-never-login-as-this-user' is a user who has read and write permissions, but we will NEVER log in as this user,since he can directly WRITE to tables.
- 'user3-read-n-use-procedure' is a user who can read AND write to tables but ONLY via the procedure CALL. This will be the user who updates the database once a week.
GRANT INSERT,DELETE,SELECT ON TABLE *Person TO 'user2-never-login-as-this-user'@'localhost';
DELIMITER //
CREATE DEFINER = 'user2-never-login-as-this-user'@'localhost'
PROCEDURE Person_Database.Person_Add (_first_name CHAR(20),_last_name CHAR(20),_category_id TINYINT,
_insider_category TINYINT,
_Person_Image MEDIUMBLOB,
_email_address VARCHAR(100),_Designation VARCHAR(100),_Biography TEXT,
_Person_URL TEXT,_Person_Detailed_Designation TEXT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION;
BEGIN
ROLLBACK;
END;
START TRANSACTION;
--- Basic check before we start.
IF _category_id NOT in (1,2)
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Invalid Category';
ENDIF;
--- Insert into Person
INSERT INTO Person(first_name,last_name,category_id) VALUES (_first_name,_last_name,_category_id);
SET @id = LAST_INSERT_ID();
IF _category_id=1
--- Insert into Insider_Person
INSERT INTO Inside_Person(id,insider_category,Person_Image,email_address,Designation,Biography)
VALUES (@id,_insider_category,_Person_Image,_email_address,_Designation,_Biography);
ELSE
--- Insert INTO Outsider_Person
INSERT INTO Outside_Person(id,Person_URL,Person_Detailed_Designation)
VALUES
(@id,_Person_URL,_Person_Detailed_Designation);
END IF;
COMMIT;
END//
DELIMITER ;
GRANT EXECUTE ON PROCEDURE Person_Database.Person_Add TO 'user3-read-n-use-procedure'@'localhost';
请注意,如果我们仅通过上述用户定义的过程添加人员,则一个人员将始终具有一个类别(内部人员/外部人员)。如果事务在添加到 Person 后失败,它将被回滚,这样我们就没有既不是 Insider 也不是 Outsider 的 Person。还要注意这如何处理共享主键 - (在上面的示例中称为 id (在表 Person / Insider_Person / Outsider_Person 表中)。查询:如何确保仅通过上述功能添加人员,而不是通过任何其他方式添加?我每周只添加 10 条记录,其余时间将是只读数据库。我怎样才能利用它并优化它的行为?
此数据库的 SELECT 查询。
只有内部人员
策略:将 Insiders 加入 Insider 类别,然后添加来自 Person 的 Insiders 的属性
SELECT * FROM Insider_Person as IP LEFT JOIN Insider_Person_Category as IPC ON IP.insider_category = IPC.id
LEFT JOIN Person as P ON IP.id = P.id;
策略:只有一些人是局外人,所以我们应该做一个 LEFT JOIN
只有局外人
SELECT * FROM Outsider_Person as OP LEFT JOIN Person as P ON OP.id = P.id;
所有人
策略:我们需要使用 4 个 LEFT JOIN,因为 Person 有 ALL 人,只有一些人在 Insider_Person 中。同样,我们可能有内部人员类别,但可能没有相应的 Insider_Person 开始。此外,我们有 ALL People in Person,但只有一些人在 Outsider_Person。如果碰巧我们只有 INSIDERS 怎么办?然后我们应该只对 Person_Category 进行 LEFT JOIN。这就是为什么我们有以下内容:-
SELECT * FROM Person as P LEFT JOIN Insider_Person as IP ON P.id = IP.id
LEFT JOIN Insider_Person_Category as IPC ON IP.insider_category = IPC.id
LEFT JOIN Outsider_Person as OP ON P.id = OP.id
LEFT JOIN Person_Category as PC ON P.category_id=PC.id;
创新数据库。时期。句号。
甚至一整天每秒 10 条记录也不是问题。只需在需要时执行
UPDATEs
orINSERTs
即可。另一方面,如果您正在重建整个表,那么就这样说;还有其他技术。
您提到了子类型,但想要的是相关性吗?
请提供
SHOW CREATE TABLE
,即使仍在草图中。SELECTs
和 10 次每日更改同上。A
FOREIGN KEY
做了三件事,但都不是强制性的:INDEX
. 您可以在INDEX
没有 FK 的情况下提供。句法
BEGIN...END
在身体周围。DELIMITER
声明。ROLLBACK
,代码继续INSERT
;也许你想退出 proc?也许ELSE
......将是做到这一点的方式。INSERT
失败不会导致ROLLBACK
.为了防止在存储过程中偷偷摸摸,不要给任何人
INSERT
对表的特权。仅授予存储过程权限。并且不要将
root
(SUPER
priv) 用于管理任务以外的任何事情。