并发存储过程执行期间 MySQL 性能下降

Question

机器规范:macOS Sierra，版本 10.12.5，内存 8 GB

MySQL 版本: MySQL Enterprise Server - Advanced Edition (Commercial)3innodb_version : 5.7.18

问题:当不同 session 同时调用同一个存储过程时，响应时间会延迟很多。

下面的 SQL 是每次执行需要 0.12 秒的 SQL。为了模拟该问题，我在 while 循环中运行以下 SQL，该循环迭代 30 次，因此每次执行存储过程平均需要 3 秒。

但是，当我从不同的终端/ session 同时运行相同的存储过程时，两个存储过程都需要将近 50 秒。

表是在 innodb 中创建的，缓冲区大小和读/写 io 设置为正常值。

查询

 SELECT MIN(BEGIN_date) , MAX(END_date) 
   FROM employee e, department d 
  WHERE e.employee_ID = d.employee_ID 
    AND d.Department_ID = 72641 ;

Extended Plan解释如下(索引完美使用)

----+-------------+-------+------------+------+------------------+----------    --------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys    | key               | key_len | ref                           | rows | filtered | Extra       |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------------------+------+----------+-------------+
|  1 | SIMPLE      | d     | NULL       | ref  | deptmnt_dept_idx | deptmnt_dept_idx | 4       | const                         |  808 |   100.00 | NULL        |
|  1 | SIMPLE      | e     | NULL       | ref  | Emp_Name_Dt _idx | Emp_Name_Dt _idx | 4       | radar_bridge_db.d.Employee_ID |  156 |   100.00 | Using index |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------------------+------+----------+-------------+

存储过程:

CREATE PROCEDURE `fetchEmployeeDetails`()
BEGIN

DECLARE l_StartDate DATE DEFAULT CURRENT_DATE()-INTERVAL 7 DAY;
DECLARE l_EndDate DATE DEFAULT CURRENT_DATE();

DECLARE I_Range INT DEFAULT 0;

WHILE I_Range < 30 DO   

    SELECT MIN(BEGIN_date) , MAX(END_date)  
      INTO l_StartDate,  l_EndDate  FROM employee e, department d 
     WHERE e.employee_ID = d.employee_ID AND d.Department_ID = 72641 ;

    SET I_Range = I_Range + 1;

 END WHILE;

END;

当从不同的终端/ session 同时调用同一个 SP 时，两个 SP 都需要将近 50 秒。

该示例在同一台 Mac 机器上同时在 MySQL 社区版和企业版(线程池开启)上运行，但问题仍然存在。如果您有任何方法可以解决此性能问题，请告诉我。

CREATE TABLE `employee` (
  `COMPANY_ID` int(11) NOT NULL,
  `COMPANY_NAME` varchar(255) CHARACTER SET utf8 NOT NULL,
  `EMPLOYEE_ID` int(11) NOT NULL,
  `BEGIN_DATE` timestamp NULL DEFAULT NULL,
  `END_DATE` timestamp NULL DEFAULT NULL,
  KEY `Emp_Name_Dt_idx` (`EMPLOYEE_ID`,`BEGIN_DATE`,`END_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `department` (
  `Employee_ID` int(11) NOT NULL,
  `Department_ID` int(11) NOT NULL,
  KEY `deptmnt_dept_idx` (`Department_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer 1

请重新考虑模式。

名为employee 的表应该谈论一个人。如果此人被多次雇用，您所拥有的似乎有多行。或许应该叫tenure，和employee分开。

department 应该是一个部门。您拥有的是员工和部门之间的多对多映射。但是，一个员工可以属于多个部门吗？如果不是，那么它是一个 1:many 映射，并且不需要该表。取而代之的是 department_id 在 employee 表中。

而且，每个表都需要一个PRIMARY KEY:

PRIMARY KEY(employee_id) -- on employee
PRIMARY KEY(employee_id, start_date, end_date) -- on tenure
PRIMARY KEY(department_id) -- on department
PRIMARY KEY(department_id, employee_id) -- on many:many mapping
INDEX(department_id) -- on employee, if 1:many
INDEX(employee_id, department_id) -- if many:many

More discussion许多:很多。

在所有这些之后，我们可以继续讨论您的时间异常，如果它仍然存在的话。