Mi_Q Kingdom

Mysql 5.7

mysql>  alter table index_test add index idx_1 (b asc,c desc);
Query OK, 0 rows affected (3.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table index_test;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_test | CREATE TABLE `index_test` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `ix_c` (`c`),
  KEY `idx_1` (`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=1048561 DEFAULT CHARSET=utf8 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

=> (b asc, c desc) desc index를 생성하나 실제로는 (b asc, c asc) 인덱스가 생성됨

mysql> explain select c,b from index_test order by b asc, c desc limit 10;
+----+-------------+------------+------------+-------+---------------+-------+---------+------+--------+----------+-----------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key   | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | index_test | NULL       | index | NULL          | idx_1 | 308     | NULL | 995930 |   100.00 | Using index; Using filesort |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

=> 실제로 Descending index가 지원되는 것이 아니기 때문에 order by b asc, c desc 했을 때 Using filesort 가 발생함

Mysql 8.0

mysql> show create table index_test;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                            |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_test | CREATE TABLE `index_test` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `ix_c` (`c`) /*!80000 INVISIBLE */,
  KEY `idx_1` (`b`,`c` DESC)
) ENGINE=InnoDB AUTO_INCREMENT=1000014 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

=> KEY 'idx_1' ('b','c' DESC) Descending index 생성됨

mysql> explain select c,b from index_test order by b asc, c desc limit 10;
+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | index | NULL          | idx_1 | 308     | NULL |   10 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

=> 5.7 과는 다르게 Descending index가 동작하여 Using filesort 가 발생하지 않음

참고 : Mysql 8.0 Descending Index (sarc.io)

INSERT INTO [table]([column1], [column2], [column3]) VALUE([value1], [value2], NOW()) 
ON DUPLICATE KEY 
UPDATE [column1] = [value1], [column2] = [value2], [column3] = NOW();

# insert 를 시도 하고, 만약 행이 있다면 해당 행에 update를 한다.

DECLARE EXIT HANDLER FOR SQLEXCEPTION : 예외 처리.
DECLARE EXIT HANDLER FOR SQLWARNING : 경고 처리.




DELIMITER $$
CREATE PROCEDURE sp_procedure_ex( IN vDoc VARCHAR(13), IN vSequenceID VARCHAR(256) )
BEGIN
     DECLARE iResult INT DEFAULT(0);
     DECLARE iSQLDoc VARCHAR(13);

     -- 쿼리 예외처리 오류 시 해당 처리
     DECLARE EXIT HANDLER FOR SQLEXCEPTION
     BEGIN
          GET DIAGNOSTICS CONDITION 1
          @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
          -- 오류 상태, 오류 메시지.
          SELECT @p1 AS RETURNED_SQLSTATE, @p2 as MESSAGE_TEXT;

          ROLLBACK;     -- TRANSACTION 종료 되지 않는 부분 종료 처리 (Unlock)
     END; 

     -- 쿼리 경고 생기면 해당 처리
     DECLARE EXIT HANDLER FOR SQLWARNING 
     BEGIN
          GET DIAGNOSTICS CONDITION 1
          @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
          -- 경고 상태, 경고 메시지.
          SELECT @p1 AS RETURNED_SQLSTATE, @p2 as MESSAGE_TEXT;

          ROLLBACK;      -- TRANSACTION 종료 되지 않는 부분 종료 처리 (Unlock)
     END;

PROC_RETURN : BEGIN      -- BLOCK NAME 설정 (RETURN  유사 기능 처리. LEAVE  -> BREAK  비슷)
     IF NOT CHAR_LENGTH(vDoc) = 13 THEN     -- iDoc의 길이가 13 이지 않으면 반환
          SET iResult = 99999;
          LEAVE PROC_RETURN;
     END IF;
    
     SELECT doc INTO iSQLDoc FROM docTBL WHERE doc = vDoc; 
     SET @SQL_Query = CONCAT('INSERT INTO sequenceTBL VALUES(1, "', iSQLDoc, '", "', vDoc, '") ');
     PREPARE curQuery FROM @SQL_Query;
     EXECUTE curQuery;
     DEALLOCATE PREPARE curQuery;

-- TRANSACTION = lock (작업단위 락) / COMMIT or ROLLBACK = Unlock (작업단위 락 해제)
START TRANSACTION;
     INSERT INTO sequenceTBL VALUES(2, "', iSQLDoc, '", "', vDoc, '") ');
     INSERT INTO sequenceTBL VALUES(2, "', iSQLDoc, '", "', vDoc, '") ');     -- 중복 오류(예외처리) 발생 시.
     -- 예외 처리 발생 되면 DECLARE EXIT HANDLER FOR SQLEXCEPTION 실행.
     -- 예외 처리 발생 되면 DECLARE EXIT HANDLER FOR SQLWARNING 실행.
     -- 현재 부분 오류 시 COMMIT 나 ROLLBACK 종료 되지 않아 TRANSACTION 진행 상태로 다른 쿼리 대기 발생.
COMMIT;

END PROC_RETURN;    -- BLOCK NAME 설정 해제. LEAVE PROC_BODY; 이면 여기 이후로 실행.
     IF 0 <> iResult THEN
          SELECT iResult;
     ELSE
          SELECT 0;
     END IF;
 END$$