MySQL Online DDL(3) [Index Operations]

Index Operation 같은 경우 대부분 instant 알고리즘을 지원하지 않는다.

InPlace 알고리즘을 지원하기 때문에 Row Log Buffer만 충분하다면 metatable lock 수준으로

작업이 가능하다.

다음은 해당 내용의 요약표 이다.

 

INDEX 생성

Index 생성 Syntax와 Online DDL 시 생성 진행되는 쿼리의 Buffer를 조회해 보면 다음과 같다.

-- Session 1

CREATE INDEX ix_alter_test_tbl_b ON alter_test_tbl(b) ALGORITHM=inplace LOCK=none;

-- 또는

ALTER TABLE alter_test_tbl

ADD INDEX ix_alter_test_tbl_b (b), ALGORITHM=inplace, LOCK=none;

 

첫 번째 Session에서 Index를 생성한다.

이 작업과 동시에 DML 문을 실행해 본다.

-- Session 2

INSERT INTO test.alter_test_tbl (b, c) VALUES (null, 'test1');

SELECT * FROM test.alter_test_tbl ORDER BY a DESC LIMIT 1;

UPDATE test.alter_test_tbl SET b='test2' WHERE a=1;

SELECT * FROM test.alter_test_tbl WHERE a=1;

DELETE FROM test.alter_test_tbl WHERE a=2;

SELECT * FROM test.alter_test_tbl WHERE a=2;

 

Index 생성이 완료되지 않아도 해당 쿼리 들이 LOCK이 없이 진행이 되고 해당과 같이 쿼리 결과 들이 저장된다.

+----------+------+-------+

| a        | b    | c     |

+----------+------+-------+

| 10026940 | NULL | test1 |

+----------+------+-------+

 

+---+-------+--------------------------------------+

| a | b     | c                                    |

+---+-------+--------------------------------------+

| 1 | test2 | d876c45d-3241-11ec-83e7-000d3ad76afd |

+---+-------+--------------------------------------+

 

또한 생성 중 세 번째 세션에서 다음과 같은 쿼리를 진행해보면 기존에 존재하지 않던

row_log_buf 항목이 생성되는 것을 볼 수 있다.

SELECT event_name

 , current_count

 , sys.format_bytes(current_alloc) AS current_alloc

 , sys.format_bytes(current_avg_alloc) AS current_avg_alloc

 , high_count

 , sys.format_bytes(high_alloc) AS high_alloc

 , sys.format_bytes(high_avg_alloc) AS high_avg_alloc

FROM sys.x$memory_global_by_current_bytes 

WHERE event_name='memory/innodb/row_log_buf' \G;

 

*************************** 1. row ***************************

       event_name: memory/innodb/row_log_buf

    current_count: 1

    current_alloc: 1.00 MiB

current_avg_alloc: 1.00 MiB

       high_count: 1

       high_alloc: 1.00 MiB

   high_avg_alloc: 1.00 MiB

 

따라서 해당과 같이 Online DDL 시 진행된 DML은 Row Log Buffer에서 진행됨을 알 수 있고

최대 사이즈 변수의 값을 넘어 버리면 오류가 발생되어 DDL이 실패하게 된다.

show global variables like '%online%';

+----------------------------------+-----------+

| Variable_name                    | Value     |

+----------------------------------+-----------+

| innodb_online_alter_log_max_size | 134217728 |

+----------------------------------+-----------+

 

INDEX 삭제

index 삭제 syntax

DROP INDEX ix_alter_test_tbl_b ON alter_test_tbl ALGORITHM=inplace LOCK=none;

-- 또는

ALTER TABLE alter_test_tbl 

DROP INDEX ix_alter_test_tbl_b, ALGORITHM=inplace, LOCK=none;

 

INDEX 이름 변경

index rename syntax

ALTER TABLE alter_test_tbl 

RENAME INDEX ix_alter_test_tbl_b TO ix_alter_test_tbl_new, 

ALGORITHM=INPLACE, LOCK=NONE;

 

show indexes from alter_test_tbl;

+----------------+------------+-----------------------+

| Table          | Non_unique | Key_name              |

+----------------+------------+-----------------------+

| alter_test_tbl |          0 | PRIMARY               |

| alter_test_tbl |          1 | ix_alter_test_tbl_new |

+----------------+------------+-----------------------+

 

FULLTEXT INDEX 생성

FullText index 추가도 같은 방향으로 실행되나 FTS_DOC_ID 컬럼이 정의되어 있지 않다면

Table rebuild가 발생되는데 여기서 FTS_DOC_ID는 FullText Index가 단어를 맵핑하기 위해

사용하는 고유 문서 식별자 컬럼으로 FullText Index 생성 시 해당 컬럼이 없으면

자동으로 생성하기 위해 Online DDL이 아니더라도 Table Rebuild가 일어나기 떄문에

Online DDL에서도 같은 Warning이 발생된다.

CREATE TABLE opening_lines (

 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

 opening_line TEXT(500),

 author VARCHAR(200),

 title VARCHAR(200)

) ENGINE=InnoDB;

 

CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);

 

SHOW WARNINGS;

+---------+------+--------------------------------------------------+

| Level   | Code | Message                                          |

+---------+------+--------------------------------------------------+

| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |

+---------+------+--------------------------------------------------+

 

공간 INDEX 생성

공간 index의 경우 Lock=none을 지원하지 않는다.

shared lock 이상을 지원하는데 따라서 CUD Transaction 이 발생되었을 시 Lock 경합이 발생된다.

-- Create Table & Sample Data

CREATE TABLE geom (g GEOMETRY NOT NULL);

 

INSERT INTO geom VALUES(st_geomfromtext('POINT(127.32132 37.34567)'));

INSERT INTO geom VALUES(st_geomfromtext('POINT(127.32132 37.34567)'));

INSERT INTO geom VALUES(st_geomfromtext('POINT(127.32132 37.34567)'));

INSERT INTO geom VALUES(st_geomfromtext('POINT(127.32132 37.34567)'));

INSERT INTO geom VALUES(st_geomfromtext('POINT(127.32132 37.34567)'));

INSERT INTO geom VALUES(st_geomfromtext('POINT(127.32132 37.34567)'));

INSERT INTO geom VALUES(st_geomfromtext('POINT(127.32132 37.34567)'));

 

-- Session 1

START TRANSACTION;

INSERT INTO geom VALUES(st_geomfromtext('POINT(127.32132 37.34567)'));

 

-- Session 2

ALTER TABLE geom ADD SPATIAL INDEX six_geom_g(g), ALGORITHM=INPLACE, LOCK=SHARED;

-- 또는

CREATE SPATIAL INDEX six_geom_g ON geom(g) ALGORITHM=inplace LOCK=SHARED;

 

위와 같은 상황 시 Session 1 에 작업이 commit 되기 전까지 index 생성이 대기를 하게 된다.

-- Session 1

COMMIT;

 

다음과 같이 Commit을 진행하면 정상적으로 index 생성 실행

 

INDEX TYPE 변경

Type 변경 같은 경우 instant 알고리즘을 사용할 수 있는데 다음과 같이 사용이 된다.

먼저 기존에 생성된 Index를 조회해 보면

show indexes from alter_test_tbl;

+----------------+---------------------+-------------+------------+

| Table          | Key_name            | Column_name | Index_type |

+----------------+---------------------+-------------+------------+

| alter_test_tbl | ix_alter_test_tbl_b | b           | BTREE      |

+----------------+---------------------+-------------+------------+

 

이후 아래의 쿼리를 통해 Index의 Type을 변경하게 되는데 이때 관련 컬럼

또는 Index의 이름 등을 수정해서는 Instant 알고리즘은 사용할 수 없고

(변경 하게 된다면 Index 삭제 및 생성 이기 때문)

Type만 변경 시에 instant 알고리즘을 사용할 수 있다.

ALTER TABLE alter_test_tbl 

DROP INDEX ix_alter_test_tbl_b, 

ADD INDEX ix_alter_test_tbl_b(b) USING HASH, 

ALGORITHM=INSTANT;

 

위 쿼리를 진행하면 warning이 뜨게 되는데 innodb에선 hash index를 진행하지 않기 때문이다.

따라서 반영이 안되고 조회 시 B-tree index로 조회가 된다.

show warnings;

+-------+------+---------------------------------------------------------+

| Level | Code | Message                                                 |

+-------+------+---------------------------------------------------------+

| Note  | 3502 | This storage engine does not support the HASH index ... |

+-------+------+---------------------------------------------------------+

 

 

show indexes from alter_test_tbl;

+----------------+---------------------+-------------+------------+

| Table          | Key_name            | Column_name | Index_type |

+----------------+---------------------+-------------+------------+

| alter_test_tbl | ix_alter_test_tbl_b | b           | BTREE      |

+----------------+---------------------+-------------+------------+

 

Memory Engine과 NDB Cluster에선 Hash Index가 지원된다.

Memory Engine 경우 2가지 지원이 가능하지만 Engine 특성 상

Index 재 작성 시 Table Rebuild가 이루어져야 하기 때문에 Copy 알고리즘만 사용이 가능하다.

CREATE TABLE alter_index_test_tbl

(

    a int NOT NULL AUTO_INCREMENT PRIMARY KEY,

    b int,

    c int

)ENGINE=MEMORY;

 

INSERT INTO alter_index_test_tbl (b,c) VALUES (1,1);

INSERT INTO alter_index_test_tbl (b,c) VALUES (2,2);

INSERT INTO alter_index_test_tbl (b,c) VALUES (3,3);

INSERT INTO alter_index_test_tbl (b,c) VALUES (4,4);

INSERT INTO alter_index_test_tbl (b,c) VALUES (1,1);

 

CREATE INDEX ix_alter_index_test_tbl_b ON alter_index_test_tbl(b);

 

show indexes from alter_index_test_tbl;

+----------------------+---------------------------+-------------+-----------+

| Table                | Key_name                  | Column_name |Index_type |

+----------------------+---------------------------+-------------+-----------+

| alter_index_test_tbl | PRIMARY                   | a           |HASH       |

| alter_index_test_tbl | ix_alter_index_test_tbl_b | b           |HASH       |

+----------------------+---------------------------+-------------+-----------+

 

ALTER TABLE alter_index_test_tbl DROP INDEX ix_alter_index_test_tbl_b, 

ADD INDEX ix_alter_index_test_tbl_b (b) USING BTREE, ALGORITHM=COPY, LOCK=SHARED;

 

show indexes from alter_index_test_tbl;

+----------------------+---------------------------+-------------+------------+

| Table                | Key_name                  | Column_name | Index_type |

+----------------------+---------------------------+-------------+------------+

| alter_index_test_tbl | PRIMARY                   | a           | HASH       |

| alter_index_test_tbl | ix_alter_index_test_tbl_b | b           | BTREE      |

+----------------------+---------------------------+-------------+------------+

 

참조

https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html#innodb-fulltext-index-docid

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

https://dba.stackexchange.com/questions/2817/why-does-mysql-not-have-hash-indices-on-myisam-or-innodb