Какое обновление выполняется быстрее с помощью объединения или последовательного?

Этот вопрос находится в последовательности моего предыдущего Вопроса, требуемого обновления той же таблицы при удалении строки.

Я могу написать два решения, используя Сохраненная процедура вместо триггера или вложенного запроса.

Оба используют вспомогательную функцию my_signal (msg).

Сохраненная процедура удаления сотрудника из таблицы Employee.

  • Решение Fist: используйте 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//

Я прочитал здесь, что использование эффективного соединения эффективно для эффективного SELECT. Но моя проблема включает только одну таблицу, и я чувствую, что мое решение (первое) намного эффективнее второго, потому что соединение будет потреблять память сравнительно.

Пожалуйста, предложите мне, который лучше и эффективнее, если Employee table достаточно большой. Что лучше для меня? Причина

EDIT: Я проверил небольшую таблицу, состоящую только из 7 строк, и оба решения принимают одинаковое время.

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

Я знаю, что функция SQL ведет себя недетерминированно, потому что эвристика таблицы. Какой выбор лучше? Либо если у вас есть идея, как можно оптимизировать запрос.

Ответы

Ответ 1

Через некоторое время я почти уверен, что это не имеет никакого значения, первое решение может быть даже немного медленнее, но в не измеримом измерении.

Первое намерение состояло бы в том, что первое решение быстрее, потому что вы сначала извлекаете данные по id и обновляете, только если это необходимо.

Но MySQL внутренне ничего не делает в инструкции UPDATE .. JOIN, только внутренне и в результате этого, вероятно, более эффективно.

Ваше первое решение не поймает случай по умолчанию - что произойдет, если я не получу WORKER или BOSS?

Также ваше время исполнения (0.09s) чрезвычайно велико, что пока не объясняется тем, что я знаю о вашей базе данных.

Вы установили какой-либо индекс?

EDIT:

После просмотра структуры которую вы разместили здесь У меня есть предложения по улучшению самой структуры.

1. Используйте тип int, когда вы храните integer values. База данных может обрабатывать целостный путь более эффективно

2. Зачем генерировать SSN самостоятельно? Использование auto_increment на PRIMARY KEY намного проще в обработке и сэкономит вам много работы при добавлении новых сотрудников

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. Есть ли у вас фиксированный диапазон обозначений? enum заставляет вход быть одним из определенных значений

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)