使用连接或顺序更新哪个更快? [英] Which update is faster using join or sequential?

查看：77
本文介绍了使用连接或顺序更新哪个更快?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

此问题按我的上一个问题 要求在删除一行后更新同一张表.

我可以使用存储过程而不是Trigger或nested-query编写两个解决方案.

两者都使用辅助函数 my_signal(msg).

用于从Employee表中删除员工的存储过程.

  • 第一手解决方案:使用表中的UPDATE行，不执行联接操作:

CREATE PROCEDURE delete_employee(IN dssn varchar(64))
BEGIN
    DECLARE empDesignation varchar(128);
    DECLARE empSsn         varchar(64);
    DECLARE empMssn        varchar(64);
     SELECT SSN, designation, MSSN  INTO empSsn, empDesignation, empMssn 
     FROM Employee 
     WHERE SSN = dssn;

   IF (empSsn IS NOT NULL) THEN
    CASE       
           WHEN empDesignation = 'OWNER' THEN 
               CALL my_signal('Error: OWNER can not deleted!');

           WHEN empDesignation = 'WORKER' THEN 
            DELETE FROM Employee WHERE SSN = empSsn;               

           WHEN empDesignation = 'BOSS' THEN 
               BEGIN 
                   UPDATE Employee
                   SET MSSN = empMssn
                   WHERE MSSN = empSsn;

                DELETE FROM Employee WHERE SSN = empSsn;                   

               END;
    END CASE;
   ELSE 
               CALL my_signal('Error: Not a valid row!');
   END IF;
END//

  • 第二个解决方案:，正如我在上一个问题中使用INNER JOIN
    • 所建议的那样 

CREATE PROCEDURE delete_employee(IN dssn varchar(64))
BEGIN
    DECLARE empDesignation varchar(128);
    DECLARE empSsn         varchar(64);
    DECLARE empMssn        varchar(64);
      SELECT SSN, designation, MSSN  INTO empSsn, empDesignation, empMssn 
      FROM Employee 
      WHERE SSN = dssn;

   IF (empSsn IS NOT NULL) THEN
       IF (empDesignation = 'OWNER') THEN 
        CALL my_signal('Error: OWNER can not deleted!');
       END IF;

       UPDATE `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
       SET B.MSSN = A.MSSN WHERE A.SSN = empSsn;

       DELETE FROM `Employee` WHERE SSN = empSsn;
   ELSE 
       CALL my_signal('Error: Not a valid row!');
   END IF;    
END//

我阅读了解决方案

经过一会儿的思考，我几乎可以肯定它没有什么作用，第一个解决方案可能会稍微慢一些，但是规模无法测量. /p>

第一个意图是，第一个解决方案会更快，因为您首先要通过id获取数据并仅在必要时进行更新.

但是，MySQL在UPDATE .. JOIN语句中内部没有执行任何其他操作，只是在内部执行，因此可能更有效.

您的第一个解决方案没有遇到默认情况-如果我没有得到WORKER或BOSS怎么办?

您的执行时间(0.09s)也非常长，到目前为止我对您的数据库所知还无法解释.

您设置了任何索引吗?

查看表结构后，您在此处发布了此表 我对结构本身有一些改进.

1..存储integer values时使用类型int.数据库可以更有效地处理整数方式

2..为什么要自己生成SSN?在PRIMARY KEY上使用auto_increment更容易处理，并且在添加新员工时可以节省很多工作

ALTER TABLE `Employee`
    CHANGE `SSN` `SSN` int(11) NOT NULL AUTO_INCREMENT ,
    CHANGE `MSSN` `MSSN` int(11) DEFAULT NULL,
    ADD KEY `KEY_Employee_MSSN` ( `MSSN` );

3..您是否使用名称进行查找?如果是这样，它也必须是唯一的

ALTER TABLE `Employee`
    ADD UNIQUE KEY `UNI_KEY_Employee` ( `name` );

4..您有固定的名称范围吗?枚举强制输入为已定义的值之一

ALTER TABLE `Employee`
    CHANGE `designation` `designation` ENUM( 'BOSS', 'WORKER' ) NOT NULL DEFAULT 'WORKER',
    ADD KEY `KEY_Employee_designation` ( `designation` );

最终结构 

mysql> EXPLAIN `Employee`;

+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| SSN         | int(11)               | NO   | PRI | NULL    | auto_increment |
| name        | varchar(64)           | YES  | UNI | NULL    |                |
| designation | enum('BOSS','WORKER') | NO   | MUL | WORKER  |                |
| MSSN        | int(11)               | YES  | MUL | NULL    |                |
+-------------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

This question is in sequence of my previous Question required update same table on deletion a row.

I could write two solutions using Stored Procedure instead of trigger or nested-query .

Both use a helper function my_signal(msg).

A Stored Procedure to delete employee from Employee Table.

  • Fist Solution: use UPDATE rows in table, without join operation: 

CREATE PROCEDURE delete_employee(IN dssn varchar(64))
BEGIN
    DECLARE empDesignation varchar(128);
    DECLARE empSsn         varchar(64);
    DECLARE empMssn        varchar(64);
     SELECT SSN, designation, MSSN  INTO empSsn, empDesignation, empMssn 
     FROM Employee 
     WHERE SSN = dssn;

   IF (empSsn IS NOT NULL) THEN
    CASE       
           WHEN empDesignation = 'OWNER' THEN 
               CALL my_signal('Error: OWNER can not deleted!');

           WHEN empDesignation = 'WORKER' THEN 
            DELETE FROM Employee WHERE SSN = empSsn;               

           WHEN empDesignation = 'BOSS' THEN 
               BEGIN 
                   UPDATE Employee
                   SET MSSN = empMssn
                   WHERE MSSN = empSsn;

                DELETE FROM Employee WHERE SSN = empSsn;                   

               END;
    END CASE;
   ELSE 
               CALL my_signal('Error: Not a valid row!');
   END IF;
END//

  • Second solution: as I was suggested in my previous question using INNER JOIN 

CREATE PROCEDURE delete_employee(IN dssn varchar(64))
BEGIN
    DECLARE empDesignation varchar(128);
    DECLARE empSsn         varchar(64);
    DECLARE empMssn        varchar(64);
      SELECT SSN, designation, MSSN  INTO empSsn, empDesignation, empMssn 
      FROM Employee 
      WHERE SSN = dssn;

   IF (empSsn IS NOT NULL) THEN
       IF (empDesignation = 'OWNER') THEN 
        CALL my_signal('Error: OWNER can not deleted!');
       END IF;

       UPDATE `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
       SET B.MSSN = A.MSSN WHERE A.SSN = empSsn;

       DELETE FROM `Employee` WHERE SSN = empSsn;
   ELSE 
       CALL my_signal('Error: Not a valid row!');
   END IF;    
END//

I read here that using join is efficient for Efficient SELECT. But my problem includes only one table and I feel my solution(first) is much efficient than second because join will consume memory comparatively.

Please suggest me which is better and efficient, if Employee table is sufficiently large. Which is better for me? Reason

EDIT: I checked for small table consist of 7 rows only, and both solution take same time. 

mysql> CALL delete_employee(4);
Query OK, 1 row affected (0.09 sec)

I know SQL function behaves non-deterministically because table heuristics. Which choice is better? Either if you have some idea How query can be further optimised.

解决方案

After a while of thinking I am almost sure it doesn't make a difference, first solution may be even slightly slower, but in a not measurable dimension.

First intention would be, that the first solution is faster because you first fetch data by id and update only if nessesary.

But MySQL internally does nothing else in the UPDATE .. JOIN statement, just internally and as a result of this probably more efficiently as well.

Your first solution doesn't catch a default case - what happens if I neither get WORKER or BOSS?

Also your execution time (0.09s) is extremely high, which can't be explained with what I know about your database so far.

Did you set any index?

EDIT:

After looking at the table structure you've posted here I have some improvement offers for the structure itself.

1. Use type int when you are storing integer values. The database can handle integer way more efficient

2. Why generate SSN by yourself? Using auto_increment on the PRIMARY KEY is much simpler to handle and saves you a lot of work when you add new employees

ALTER TABLE `Employee`
    CHANGE `SSN` `SSN` int(11) NOT NULL AUTO_INCREMENT ,
    CHANGE `MSSN` `MSSN` int(11) DEFAULT NULL,
    ADD KEY `KEY_Employee_MSSN` ( `MSSN` );

3. Do you use the name for lookups? If so, it needs to be unique as well

ALTER TABLE `Employee`
    ADD UNIQUE KEY `UNI_KEY_Employee` ( `name` );

4. Do you have a fixed range of designations? enum forces the input to be one of the defined values

ALTER TABLE `Employee`
    CHANGE `designation` `designation` ENUM( 'BOSS', 'WORKER' ) NOT NULL DEFAULT 'WORKER',
    ADD KEY `KEY_Employee_designation` ( `designation` );

Final structure 

mysql> EXPLAIN `Employee`;

+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| SSN         | int(11)               | NO   | PRI | NULL    | auto_increment |
| name        | varchar(64)           | YES  | UNI | NULL    |                |
| designation | enum('BOSS','WORKER') | NO   | MUL | WORKER  |                |
| MSSN        | int(11)               | YES  | MUL | NULL    |                |
+-------------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

这篇关于使用连接或顺序更新哪个更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆