MySQL) create table & mul index

3.6.9 Using AUTO_INCREMENT

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. For example:

 

https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

MySQL :: MySQL 8.0 Reference Manual :: 3.6.9 Using AUTO_INCREMENT

인덱스 만들기

 

(예제)

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLE `books` (        `id` varchar(5) NOT NULL,        `name` varchar(20) NOT NULL,        `writer` varchar(20) NOT NULL,        `price` decimal(10,2) DEFAULT '0.00',        `genre` varchar(20) NOT NULL,        `publisher` varchar(20) NOT NULL,        `discount_yn` char(1) NOT NULL,        `discount_rate` decimal(10,0) DEFAULT '0',        `cnt` decimal(10,0) DEFAULT '0',        `sale_date` datetime DEFAULT NULL,        PRIMARY KEY (`id`),        INDEX `idx_test` (`writer`)      );

 

  

 

1. 추가하여 만들기

    CREATE INDEX <인덱스명> ON <테이블명> ( 칼럼명1, 칼럼명2, ... );

 

2. 테이블 생성시 만들기

    끝에....

    INDEX <인덱스명> ( 칼럼명1, 칼럼명2 )

    UNIQUE INDEX <인덱스명> ( 칼럼명 )  --> 항상 유일해야 함.

 

3. 이렇게도 생성한다

    ALTER TABLE <테이블명> ADD INDEX <인덱스명> ( 칼럼명1, 칼럼명2, ... );

 

4. 인덱스 보기

    SHOW INDEX FROM <테이블명>;

 

5. 인덱스 삭제

    ALTER TABLE <테이블명> DROP INDEX <인덱스명>;





=======================================================================================================





인덱스는 테이블 상에서 MUL 로 표시된다. 

 

Mysql 서버에서 인덱스를 생성하는 방법은 3가지가 있다. 

 

처음으로 테이블을 만들며서 생성하는 방법이 두 가지이고, 테이블을 만든 후에 사용하다가 인덱스

를 추가 할 수 있는 방법이 있다. 인덱스 파일은 "테이블명.MYI"파일로 데이터베이스 디렉토리 아래 저장된다. 

 

1.CREATE TABLE 문에서 생성

 

CREATE TABLE 문에서 인덱스를 생성하는 방법으로 가장 많이 사용된다. 다음과 같이 사용 가능하고 , 인덱스명은 생략 가능하며, 임의로 idx1으로 준것이다. aaaidx로 주어도 아무 상관이 없다. 

 

---------------------------------------------------------------------------------------

INDEX 인덱스명 (칼럼명) OR

INDEX (칼럼명) OR

KEY 인덱스명 (칼럼명)

---------------------------------------------------------------------------------------

인덱스를 만들고, desc 명령을 사용했을 때, 인덱스를 준 칼럼에 MUL 이라고 보이면 인덱스가 생성된 것이다. 임의로 만든 index1 테이블은 "주키-학번-이름"으로 구성된 테이블 이다. 

BLOB과 TEXT 데이터형은 인덱스를 줄 수 없으며, 인덱스를 사용하려면 Full-text Search 를 사용해야 한다. 

뒤에 자세한 설명을 참고하기 바란다. 

----------------------------------------------------------------------------------------

mysql> create table index1( 
    -> code int not null auto_increment primary key,
    -> hakbun int not null,
    -> name char(30) not null,
    -> index idx1(hakbun)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> desc index1;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| code   | int(11)  |      | PRI | NULL    | auto_increment |
| hakbun | int(11)  |      | MUL | 0       |                |
| name   | char(30) |      |     |         |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
----------------------------------------------------------------------------------------

 

생성한 테이블에 인덱스의 목록을 보고 싶으면 "SHOW KEYS FROM 테이블 명"명령을 사용하면 볼 수 있다. Key_name에 PRIMARY 와 idx1 으로 적힌 것을 볼 수 있을 것이다. 주키(PRIMARY)는 인덱스가 기본으로 추가된다. 

 

mysql> show keys from index1;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| index1 |          0 | PRIMARY  |            1 | code        | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| index1 |          1 | idx1     |            1 | hakbun      | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

 

2. CREATE INDEX 문에서 생성

 

테이블을 만든 후에 바로 인덱스를 추가 할 수 있는데, 이경우 CREATE INDEX 문을 사용한다. 

 

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON tbl_name(col_name[(length)],...)

 

처음에 CREATE TABLE 문에서 인덱스를 주지 않고 index2 테이블을 생성한 후에, CREATE INDEX 문을 사용해서 인덱스를 추가하는 것이다. 

 

mysql> create table index2(
    -> code int not null auto_increment primary key,
    -> hakbun int not null,
    -> name char(30) not null
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc index2 
    -> ;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| code   | int(11)  |      | PRI | NULL    | auto_increment |
| hakbun | int(11)  |      |     | 0       |                |
| name   | char(30) |      |     |         |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> CREATE INDEX idx1 ON index2(hakbun);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc index2;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| code   | int(11)  |      | PRI | NULL    | auto_increment |
| hakbun | int(11)  |      | MUL | 0       |                |
| name   | char(30) |      |     |         |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


3. 인덱스 제거

인덱스는 한 개의 테이블에서 2개 칼럼에 주는것이 적당하다. 인덱스는 데이터베이스 용량의 3분의 1 정도를 차지하기 때문에 너무 많은 인덱스를 생성하면 용량이 커져서 퍼포먼스가 떨어질 수도 있게 된다. 인덱스를 제거하려면 DROP INDEX 문이나 ALTER TABLE 문을 사용하면 된다. 

 

https://www.albumbang.com/board/board_view.jsp?board_name=free&orderby=hit%20desc&no=141 

[MySQL] 인덱스 생성, 조회 | 고객게시판