2018-9-21 데이터베이스 인덱스

DB Index

인덱스 : 테이블에 대한 동작의 속도를 높여주는 자료구조 무조건 빠르게 해주는것이 아니라 하나의 성능을 개선하면 다른한쪽이 저하가 된다(Trade Off). Insert, Update, Delete속도를 희생해서 Read속도를 매우 높인다.

기본세팅

  • load data를 통해 200만건의 레코드를 삽입한다.
SET foreign_key_checks=0;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` char(16) NOT NULL,
  `name` varchar(16) DEFAULT NULL,
  `last_visit` datetime DEFAULT NULL,
  `money` decimal(10,0) DEFAULT NULL,
  `choo` char(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `choo` (`choo`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`choo`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA LOCAL INFILE 'data.csv'
	INTO TABLE user
	FIELDS TERMINATED BY ','
	IGNORE 1 LINES;
  • set foreign_key_checks=0 데이터를 삽입할때 외래키를 고려하지 않는다.
  • KEY choo (choo), CONSTRAINT user_ibfk_1 FOREIGN KEY (choo) REFERENCES user (id)를 할때 외래키 검사를 안하게 된다.

mysql -u jimmy -p jimmydb –local-infile=1 < index_ex.sql 명령어를 통해 index_ex.sql의 명령어를 실행하고 data.csv를 집어넣는다.

mysql> select @@foreign_key_checks;
+----------------------+
| @@foreign_key_checks |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql> set @@foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@foreign_key_checks;
+----------------------+
| @@foreign_key_checks |
+----------------------+
|                    0 |
+----------------------+
  • foreign_key_checks를 1에서 0으로 바꾼다.

실습

mysql> select count(id) from user;
+-----------+
| count(id) |
+-----------+
|   2000000 |
+-----------+

mysql> select count(*) from user where id like 'm%';
+----------+
| count(*) |
+----------+
|    83262 |
+----------+
1 row in set (0.03 sec)

mysql> select count(*) from user where money=1200;
+----------+
| count(*) |
+----------+
|      189 |
+----------+
1 row in set (0.53 sec)
  • 0.03초만에 id가 m으로 시작되는것이 83262개이다. 굉장히 빨리 실행이됬다.
  • 밑에꺼랑 위에꺼랑 시간이 굉장히 차이가 많이 난다. 왜??

쿼리마다 성능이 다른이유?

  1. 특정 칼럼에 대한 인덱스가 자동으로 만들어진다.
  2. primary key 인덱스 자동 생성
  3. foreign key에도 인덱스가 자동 생성된다.
mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     1820892 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | choo     |            1 | choo        | A         |     1781468 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


// 예쁘게
mysql> show index from user\G;
*************************** 1. row ***************************
        Table: user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 1867779
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: user
   Non_unique: 1
     Key_name: choo
 Seq_in_index: 1
  Column_name: choo
    Collation: A
  Cardinality: 1977604
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)
2 rows in set (0.00 sec)
  • id, choo column에 BTREE타입의 인덱스가 존재를 한다.
  • index는 show index명령어로 확인이 가능하다.
mysql> select count(*) from user where id like 'home%';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from user where choo like 'home%';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)
  • id와 choo에는 index가 걸려있기때문에 검색하는데 굉장히 빨리걸린다.
mysql> select count(*) from user where money = 6600;
+----------+
| count(*) |
+----------+
|      195 |
+----------+
1 row in set (0.55 sec)

mysql> select count(*) from user where money = 1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.47 sec)
  • money에는 index가 없기때문에 인덱스가 있는 Column에 비해서 오래걸린다.

    어떻게 속도를 빨리할까?? 인덱스를 만든다.

mysql> create index idx_user1 on user(money);
  • idx_user1이라는 이름을 가진 인덱스를 user(money)에 만든다.
mysql> select count(*) from user where money = 1000;
+----------+
| count(*) |
+----------+
|      194 |
+----------+
1 row in set (0.00 sec)

mysql> select * from user where money=1000;
194 rows in set (0.01 sec)
  • index가 만들어지면 이전과 비해서 굉장히 검색이 빨라진다.

create index indexName on TableName(ColumnName);을 이용하면 인덱스를 만들수 있다.

  • 모든열에 인덱스를 만들면 되지 않은가?? 안된다 왜?? 인덱스를 만들때마다. insert update delete가 느려지고 용량이 굉장히 커진다.
Written on September 21, 2018