MyISAM 과 InnoDB 엔진 비교, 그리고 잡다한 테스트..

/*
MyISAM :
1. 테이블 단위 lock
2. SELECT 속도가 빠름
3. 트랜젝션 지원 안함
4. 왜래키 지원 안함
5. Full-Index 지원함

InnoDB:
1. 행 단위 lock
2. INSERT, UPDATE, DELETE 속도가 빠름
3. 트랜젝션 지원함
4. 왜래키 지원함
5. Full-Index 지원 안함


테이블 마다 각각 다르게 사용 할 수는 있지만,
lock, 트랜젝션 등이 달라 사용이 어렵고,
join시 둘중 하나가 빠르더라도 늦은 엔진 기준으로 조회가 되니 효용이 떨어짐.
Backup 또한 달라 효과적이진 않음. (InnoDB는 DB백업을 Dump로만 가능)
그러나 독립적으로 사용할 테이블이라면 실제 속도에 따라 융통성있게 사용하면 될듯


Mysql 5.6 기준
아래처럼 테스트를 해보니 건당 10만개씩 데이터를 넣을때 MyISAM이 비교도 안되게 빠름
아 도데체 먼지..
*/

USE TEST;

# DB에 속해있는 테이블 조회
show tables from TEST;

# 테이블 생성
CREATE TABLE TEST_BIG
(
  idx int auto_increment primary key
  ,v1 varchar(200)
  ,v2 varchar(200)
  ,v3 varchar(200)
  ,v4 varchar(200)
)
ENGINE=InnoDB;


# 테이블 엔진 타입 조회
select table_schema, table_name, engine from information_schema.tables where table_schema='TEST'; -- 해당 DB에 속한 모든 테이블 조회 
select table_schema, table_name, engine from information_schema.tables where table_name='TEST_BIG'; -- 해당 테이블만 조회


# 테이블 엔진 변경
alter table TEST_BIG engine=InnoDB;
alter table TEST_BIG engine=MyISAM;

# 가장 좋은 방법은 dump 후  engine=MyISAM 을 Engine=InnoDB 로 바꿔서(혹은 반대로) 복구하는 게 좋다는데..
# 참조 : https://sarc.io/index.php/mariadb/1126-myisam-innodb




# 테스트 데이터 입력에 사용할 프로시저 생성
DELIMITER $$ /* 구분자 정의. ;으로 인한 종결 방지 목적*/
DROP PROCEDURE IF EXISTS TEST.PROC_Data_Insert;
CREATE PROCEDURE PROC_Data_Insert(IN tbname varchar(50), IN cnt int)
  COMMENT '데이터  생성 프로시저'
BEGIN

  DECLARE i INT;
  DECLARE tbcnt INT;
  DECLARE inflag bool DEFAULT false ;
  DECLARE str varchar(50);

  SET i = 1;
  SET str = '';

  SET tbname = IFNULL(tbname, ''); #입력된 파라미터 NULL 처리
  SET cnt = IFNULL(cnt, 0); #입력된 파라미터 NULL 처리


#   변수에 값 할당 하기 (선언해 주지 않아도 됨)
#   SELECT 컬럼1, 컬럼2 INTO @변수1, @변수2 FROM 테이블명 WHERE 조건;
#   SELECT @변수1:=컬럼1, @변수2:=컬럼2 FROM 테이블명 WHERE 조건;
#   select @변수1, @변수2;

  if tbname <> '' then

    SET @sql = 'SELECT COUNT(*) INTO @tbcnt FROM Information_schema.tables WHERE table_schema = ''TEST'' AND table_name = ?';
    PREPARE stmt FROM @sql;
    SET @c1 = tbname;
    EXECUTE stmt USING @c1; -- ? 에 변수 할당
    DEALLOCATE PREPARE stmt;
    -- SET tbval = @tbcnt;
    -- select @tbcnt; -- 변수 값 리턴


    IF (@tbcnt = 0) then
      set inflag = false;
      -- select '없음(테이블 생성코드)';
    else
      set inflag = true;
      -- select '있음';
    end if;


    if (inflag) then
      /* 데이터 저장 반복문 */
      label1: WHILE i <= cnt DO

        /* CONCAT은 쉼표(,)를 기준으로 문자열을 합친다 */
        SET @sql2 = CONCAT("INSERT INTO ", tbname, " (v1, v2, v3, v4) values(?, ?, ?, ?); ");
        PREPARE stmt FROM @sql2;
        SET @v1 = CONCAT("내용입니다v1_", i);
        SET @v2 = CONCAT("내용입니다v2_", i);
        SET @v3 = CONCAT("내용입니다v3_", i);
        SET @v4 = CONCAT("내용입니다v4_", i);
        EXECUTE stmt USING @v1, @v2, @v3, @v4; -- ? 에 변수 할당
        DEALLOCATE PREPARE stmt;

        -- select i;

        SET i = i + 1;
      END WHILE label1;

    end if;

  end if;

END $$
DELIMITER ;


# 프로시저 호출
call PROC_Data_Insert ('TEST_BIG', 1000000);




# 조회시 lock 방지를 위해 아래와 같은 형식을 사용해 보았으나 그냥 쿼리도 lock 안 걸리고 조회가 잘됨..
# SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
# select * from TEST_BIG order by idx desc LOCK IN SHARE MODE;
# SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
#
#
# SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
# select * from TEST_BIG order by idx desc;
# SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;



# 실행계획
EXPLAIN select * from TEST_BIG order by idx desc LOCK IN SHARE MODE;


# 현재 실행 프로세스(쿼리) 조회
SHOW PROCESSLIST;
/*
- Id : 쿼리 아이디. PID
- User : 쿼리 실행 user
- Host : 연결 host ip
- db : database명
- Command
  (1) Query : 실행중인 쿼리
  (2) Sleep : 쿼리의 실행이 완료되고 연결만 유지되고 있는 경우
- Time : 쿼리 실행 시간 (초)
- State : 현재 쿼리가 뭘 하고있는지 나타냄
- Info : 쿼리문
*/


# 프로세스 종료
KILL 1; -- KILL [ID 번호]

 

index 생성 (조회가 빈번한 컬럼엔 index를 생성 해줘야함, 성별처럼 데이터 분별력이 없는 컬럼은 제외)

CREATE INDEX [index명] ON [테이블명] ([컬럼명]);

예) CREATE INDEX v2_index ON TEST_BIG (v2);

 

index 조회

SHOW INDEX FROM [테이블명];