是否有任何 MySQL 基准测试工具？[关闭]

Question

bassxzero

Asked: 2017-09-06 06:12:43 +0800 CST2017-09-06 06:12:43 +0800 CST 2017-09-06 06:12:43 +0800 CST

优化工作流引擎数据库的设计

772

我需要帮助优化此设计，因为我认为它不会很好地扩展。

背景资料

该数据库用于工作流引擎应用程序。最终用户创建具有不同属性的事务，并且事务根据其属性的子集执行一系列步骤（目前子集为 3，但将来可能会更改）。

事务经过的步骤的数量和顺序来自我定义的路由。这些路线几乎从不改变。

这是用于查找路由的表的结构。

这就是Transaction桌子的样子。

CREATE TABLE `Transaction` (
 `TID` int(11) NOT NULL AUTO_INCREMENT,
 `Type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
 `WavPost` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
 `EmployeeClass` char(1) COLLATE utf8mb4_unicode_ci NOT NULL,
 PRIMARY KEY (`TID`)
)

这是我创建的用于生成所有可能路线的视图( route_vw)

  select 
    `d`.`facstaff` AS `facstaff`, 
    `f`.`waveposted` AS `waveposted`, 
    `h`.`tempreg` AS `tempreg`, 
    `j`.`step_name` AS `step_name`, 
    `i`.`route_step_id` AS `route_step_id`, 
    `i`.`route_id` AS `route_id`, 
    `i`.`step_id` AS `step_id`, 
    `i`.`ndx` AS `ndx`, 
    `i`.`on_time` AS `on_time`, 
    `i`.`behind` AS `behind`, 
    `i`.`late` AS `late` 
from 
    (
        (
            (
                (
                    (
                        (
                            (
                                (
                                    `route_defn` `a` 
                                    join `fac_staff_list` `c` on(
                                        (
                                            `a`.`fac_staff_list_id` = `c`.`fac_staff_list_id`
                                        )
                                    )
                                ) 
                                join `fac_staff` `d` on(
                                    (
                                        `c`.`fac_staff_list_id` = `d`.`fac_staff_list_id`
                                    )
                                )
                            ) 
                            join `wave_post_list` `e` on(
                                (
                                    `a`.`wave_post_list_id` = `e`.`wave_post_list_id`
                                )
                            )
                        ) 
                        join `wave_post` `f` on(
                            (
                                `e`.`wave_post_list_id` = `f`.`wave_post_list_id`
                            )
                        )
                    ) 
                    join `temp_reg_list` `g` on(
                        (
                            `a`.`temp_reg_list_id` = `g`.`temp_reg_list_id`
                        )
                    )
                ) 
                join `temp_reg` `h` on(
                    (
                        `g`.`temp_reg_list_id` = `h`.`temp_reg_list_id`
                    )
                )
            ) 
            join `route_step` `i` on(
                (
                    `a`.`route_defn_id` = `i`.`route_id`
                )
            )
        ) 
        join `step_defn` `j` on(
            (
                `i`.`step_id` = `j`.`step_defn_id`
            )
        )
    )

这就是我找到 (the) Transaction(s)路线的方式。

select *
from `Transaction` `A`
join `route_vw` `B`
on (
    (`B`.`facstaff` = `A`.`EmployeeClass`) 
    and (`B`.`waveposted` = `A`.`WavPost`) 
    and (`B`.`tempreg` = `A`.`Type`)
)

问题

Array
(
    [EVENT_ID] => 4
    [Duration] => 0.003877
    [SQL_TEXT] => SELECT *
FROM Transaction A
INNER JOIN route_vw D on (
A.EmployeeClass = D.facstaff AND
A.WavPost = D.waveposted AND
A.Type = D.tempreg 
)
    [DIGEST_TEXT] => SELECT `d` . `facstaff` AS `facstaff` , `f` . `waveposted` AS `waveposted` , `h` . `tempreg` AS `tempreg` , `j` . `step_name` AS `step_name` , `i` . `route_step_id` AS `route_step_id` , `i` . `route_id` AS `route_id` , `i` . `step_id` AS `step_id` , `i` . `ndx` AS `ndx` , `i` . `on_time` AS `on_time` , `i` . `behind` AS `behind` , `i` . `late` AS `late` FROM ( ( ( ( ( ( ( ( `UchihaTigerTalent` . `route_defn` `a` JOIN `UchihaTigerTalent` . `fac_staff_list` `c` ON ( ( `a` . `fac_staff_list_id` = `c` . `fac_staff_list_id` ) ) ) JOIN `UchihaTigerTalent` . `fac_staff` `d` ON ( ( `c` . `fac_staff_list_id` = `d` . `fac_staff_list_id` ) ) ) JOIN `UchihaTigerTalent` . `wave_post_list` `e` ON ( ( `a` . `wave_post_list_id` = `e` . `wave_post_list_id` ) ) ) JOIN `UchihaTigerTalent` . `wave_post` `f` ON ( ( `e` . `wave_post_list_id` = `f` . `wave_post_list_id` ) ) ) JOIN `UchihaTigerTalent` . `temp_reg_list` `g` ON ( ( `a` . `temp_reg_list_id` = `g` . `temp_reg_list_id` ) ) ) JOIN 
    [NO_INDEX_USED] => 0
    [NO_GOOD_INDEX_USED] => 0
    [ROWS_AFFECTED] => 0
    [ROWS_SENT] => 214
    [ROWS_EXAMINED] => 820
)

这些统计数据是在Transaction有 14 行时获取的。基于ROWS_EXAMINED我认为这种方法不会很好地扩展。有没有更好的方法来写这个，这样我就不必检查那么多行了？

注意：我上传了一个db-fiddle.com演示。

更新 1

问题 - “我不明白你的 1 列“*_list”表。”

答案 - *_lists 表允许我指定和重用路由应该用于的一组值/列表。

例如

路线 #4 ( route_defn.route_defn_id = 4) 适用于员工交易。

路线 #5 适用于教职工交易。

fac_staff_list_id = 3 的路线适用于教职工交易。* 请注意，没有 fac_staff_list_id = 3 的路线。

此逻辑和结构也适用于 temp_reg_list 和 wave_post_list。

更新 2

“在 1:many 映射中不需要中间表 (*_list)。fac_staff 表只需要一个列 route_defn_id。摆脱中间的 fac_staff_list。（其他 1:many 关系也是如此。）” - @rick-james

“请提供 EXPLAIN SELECT ... 和 SHOW CREATE TABLE” - @rick-james

下面更新的架构基于@rick-james 的建议。

显示创建视图 route_vw

CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `route_vw` AS select `fac_staff`.`facstaff` AS `facstaff`,`wave_post`.`waveposted` AS `waveposted`,`temp_reg`.`tempreg` AS `tempreg`,`route_step`.`route_step_id` AS `route_step_id`,`route_step`.`route_id` AS `route_id`,`route_step`.`step_id` AS `step_id`,`route_step`.`ndx` AS `ndx`,`route_step`.`on_time` AS `on_time`,`route_step`.`behind` AS `behind`,`route_step`.`late` AS `late` from ((((`route_defn` join `fac_staff` on((`route_defn`.`route_defn_id` = `fac_staff`.`route_defn_id`))) join `wave_post` on((`route_defn`.`route_defn_id` = `wave_post`.`route_defn_id`))) join `temp_reg` on((`route_defn`.`route_defn_id` = `temp_reg`.`route_defn_id`))) join `route_step` on((`route_defn`.`route_defn_id` = `route_step`.`route_id`)))

显示创建表 route_defn

CREATE TABLE `route_defn` (
 `route_defn_id` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`route_defn_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

显示创建表 fac_staff

CREATE TABLE `fac_staff` (
  `fs_id` int(11) NOT NULL AUTO_INCREMENT,
  `route_defn_id` int(11) NOT NULL,
  `facstaff` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`fs_id`),
  UNIQUE KEY `route_defn_id` (`route_defn_id`,`facstaff`),
  KEY `facstaff` (`facstaff`),
  CONSTRAINT `fac_staff_ibfk_1` FOREIGN KEY (`route_defn_id`) REFERENCES `route_defn` (`route_defn_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

显示创建表 wave_post

CREATE TABLE `wave_post` (
  `wp_id` int(11) NOT NULL AUTO_INCREMENT,
  `route_defn_id` int(11) NOT NULL,
  `waveposted` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`wp_id`),
  UNIQUE KEY `route_defn_id` (`route_defn_id`,`waveposted`),
  KEY `waveposted` (`waveposted`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

显示创建表事务

CREATE TABLE `Transaction` (
  `TID` int(11) NOT NULL AUTO_INCREMENT,
  `Type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `RequestType` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `WavPost` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `IncSupPosition` int(8) unsigned zerofill NOT NULL,
  `IncSupEmplid` int(6) unsigned zerofill NOT NULL,
  `Supervisor` varchar(55) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Phone` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Location` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `HireMgrName` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `HireMgrEmail` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Comment` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `SalaryStart` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `PositionJustification` varchar(1500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `InternalComparison` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `AdditionalDuties` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ReducedTask` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PositionNumber` int(8) unsigned zerofill DEFAULT NULL,
  `StatePosition` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `JobReqID` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `JobCode` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `DepartmentID` int(4) unsigned zerofill NOT NULL,
  `DepartmentName` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `BudgetCenter` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `FullPart` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  `StandardHours` float NOT NULL,
  `IncumbentEmplid` int(6) unsigned zerofill DEFAULT NULL,
  `IncumbentName` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PreHireEscalate` int(11) NOT NULL DEFAULT ''1'',
  `CountyCode` int(2) unsigned zerofill NOT NULL,
  `StateCode` varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `EmployeeClass` char(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  `SubmittedBy` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL,
  `TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`TID`),
  UNIQUE KEY `TID` (`TID`,`Type`,`WavPost`,`EmployeeClass`),
  KEY `BudgetCenter` (`BudgetCenter`),
  KEY `HireMgrEmail` (`HireMgrEmail`),
  KEY `SubmittedBy` (`SubmittedBy`),
  KEY `Type` (`Type`),
  KEY `EmployeeClass` (`EmployeeClass`),
  KEY `WavPost` (`WavPost`),
  KEY `Type_2` (`Type`,`WavPost`,`EmployeeClass`)
) ENGINE=InnoDB AUTO_INCREMENT=6380 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

显示创建表 temp_reg

CREATE TABLE `temp_reg` (
  `tr_id` int(11) NOT NULL AUTO_INCREMENT,
  `route_defn_id` int(11) NOT NULL,
  `tempreg` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`tr_id`),
  UNIQUE KEY `route_defn_id` (`route_defn_id`,`tempreg`),
  KEY `tempreg` (`tempreg`),
  KEY `rotue_defn_id` (`route_defn_id`),
  CONSTRAINT `temp_reg_ibfk_1` FOREIGN KEY (`route_defn_id`) REFERENCES `route_defn` (`route_defn_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

显示创建表 route_step

CREATE TABLE `route_step` (
  `route_step_id` int(11) NOT NULL AUTO_INCREMENT,
  `route_id` int(11) NOT NULL,
  `step_id` int(11) NOT NULL,
  `ndx` int(11) NOT NULL,
  `on_time` tinyint(1) DEFAULT NULL,
  `behind` tinyint(1) DEFAULT NULL,
  `late` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`route_step_id`),
  UNIQUE KEY `route_id_2` (`route_id`,`step_id`,`ndx`),
  KEY `step_id` (`step_id`),
  KEY `ndx` (`ndx`),
  KEY `route_id` (`route_id`),
  CONSTRAINT `route_step_ibfk_1` FOREIGN KEY (`step_id`) REFERENCES `step_defn` (`step_defn_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `route_step_ibfk_2` FOREIGN KEY (`route_id`) REFERENCES `route_defn` (`route_defn_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

1 个回答

Voted

Rick James · Answer 1 · 2017-09-07T06:21:34+08:00

Best Answer

Rick James

2017-09-07T06:21:34+08:002017-09-07T06:21:34+08:00

1 条路线适用于多种 fac_staff 类型。

1:many 映射中不需要中间表 (*_list)。该fac_staff表只需要一列route_defn_id。摆脱干预fac_staff_list。（同上其他 1：许多关系。）

1

优化工作流引擎数据库的设计

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

优化工作流引擎数据库的设计

1 个回答

相关问题