[DB] mysql index 정리

참고페이지 : http://www.mysqlkorea.co.kr/sub.html?mcode=develop&scode=01&m_no=21712&cat1=7&cat2=219&cat3=251&lang=k

 

INDEX의 의미?

   

RDBMS에서 검색속도를 높이기 사용하는 하나의 기술이입니다.

INDEX는 색인입니다. 해당 TABLE의 컬럼을 색인화(따로 파일로 저장)하여 검색시 해당 TABLE의 레코드를 full scan 하는게 아니라 색인화 되어있는 INDEX 파일을 검색하여 검색속도를 빠르게 합니다.

이런 INDEX는 TREE구조로 색인화합니다. RDBMS 에서 사용하는 INDEX는 Balance Search Tree 를 사용합니다.

실제로는 RDBMS 에서 사용되는 B-Tree 는 B-Tree 에서 파생된 B+ Tree 를 사용한다고 합니다.

   

참고로 ORACLE이나 MSSQL에서는 여러종류의 TREE를 선택하여 사용가능하다.

   

INDEX의 원리?

   

INDEX를 해당 컬럼에 주게 되면 초기 TABLE생성시 만들어진 MYD,MYI,FRM 3개의 파일중에서

MYI에 해당 컬럼을 색인화 하여 저장합니다. 물론 INDEX를 사용안할시에는 MYI파일은 비어 있습니다. 그래서 INDEX를 해당컬럼에 만들게 되면 해당컬럼을 따로 인덱싱하여 MYI 파일에 입력합니다. 그래서 사용자가 SELECT쿼리로 INDEX가 사용하는 쿼리를 사용시 해당 TABLE을 검색하는것이 아니라 빠른 TREE로 정리해둔 MYI파일의 내용을 검색합니다.

만약 INDEX를 사용하지 않은 SEELCT쿼리라면 해당 TABLE full scan하여 모두 검색합니다.

이는 책의 뒷부분에 찾아보기와 같은 의미로 정리해둔 단어중에서 원하는 단어를 찾아서 페이지수를 보고 쉽게 찾을수 있는 개념과 같습니다. 만약 이 찾아보기 없다면 처음부터 끝까지 모든 페이지를 보고 찾아야 할것입니다.

   

INDEX의 장점?

   

보통 INDEX를 사용하지 않은 select쿼리와 INDEX를 사용한 쿼리의 검색속도는 6.5배가 차이납니다.이는 데이타양이 많아질수록 더욱더 차이납니다.

   

INDEX의 단점?

   

1.디스크용량 감소

인덱스를 사용하명 MYI파일에  추가 입력되기 때문에 디스크용량이 늘어납니다.

모든컬럼을 인덱스하면 데이타파일보다 인덱스파일이더 커질수도 있습니다.

적당하게 사용해야 합니다.

인덱스를 사용해도 디스크 용량은 염려할 정도로 많이 안먹으며 그에 비해 대부분의 경우는 퍼포먼스의 향상을 가져오게 되므로 좋은점이 더 많습니다.

   

2.INSERT,UPDATE속도 저하

해당 TABLE에 INDEX을 주게되면 INSERT,UPDATE가 조금 느려집니다.

왜냐하면 매번 해당 table과 table의 index를 검사해야 하기때문에 해당 table만 검사했을때보다

느리다.

   

인덱스 삭제

   

drop index 컬럼명 on 테이블명;

   

INDEX의 목적?

   

RDBMS에는 INDEX가 있습니다. 인덱스의 목적은 해당 RDBMS의 검색 속도를 높이는데 있습니다.

SELECT 쿼리의 WHERE절이나 JOIN 예약어를 사용했을때만 인덱스를 사용되며 SELECT 쿼리의 검색 속도를 빠르게 하는데 목적을 두고 있습니다.

※ DELETE,INSERT,UPDATE쿼리에는 해당 사항없으며 INDEX사용시 좀 느려집니다.

   

INDEX 를 사용해야 하는 경우

   

데이터 양이 많고 검색이 변경보다 빈번한 경우
인덱스를 걸고자 하는 필드의 값이 다양할 값을 가질 경우 
(성별과 같이 데이터의 값의 종류가 일정한 경우 인덱스 효과 없음)

   

INDEX 사용시 조심할점

   

Index가 가해지는 필드는 가능한 Null값이 없어야 한다.

   

한 테이블에 5개 이상의 인덱스 적용은 권장하지 않는다.

(이화식 씨의 대용량 데이터베이스 설계 중에서)

   

인덱스를 사용한 필드를 조건에서 연산,가공하여 사용하면 인덱스효과는 없다. 
ex : Select * from 테이블 where 인덱스필드 * 10 > 100 
--> Select * from 테이블 where 인덱스필드  > 100 / 10 으로 사용

   

INDEX가 동작하지 않는 경우

   

다음 연산자는 인덱스를 타지 않는다.

not,<> 는 인덱스 사용못함(= >= <= 는 사용가능)

like '%value' 와 like '%value%'는 인덱스 사용못함(like 'like%'는 사용가능)

조건 컬럼을 가공하거나 연산을 하면 인덱스를 사용 못합니다.

문자열 타입에 인덱스를 걸경우 150 바이트 이하까지만 인덱스가 적용됩니다.

 

 

Index 관련 SQL statement

alter table test drop index num; create index num_idx on tablename(field(length)); alter table t add index t_idx(id) alter table table_name add index(col_name,...) ; - create table문 create table test( test1 int not null auto_increment primary key(test1), /* 프라이머리키 설정 */ test2 char(1), test3 char(2), index idx_test2(test2), /* test2필드를 idx_test2라는키이름으로 인덱스설정 */ index idx_test23(test2,test3) /*test2,test3필드를 idx_test23이라는 키이름으로 복합인덱스설정 */ );

   

show index 명령시 ( show index from table_name )

컬럼이름 : 설명 Table : 테이블이름. Non_unique : unique 여부. (0이면 unique 한값이구, 1이면 아님다.) Key_Name : 인덱스키 이름. Seq_In_Index : 인덱스안의 칼럼순서 (흠. 맞는건지..여튼 인덱스안의 컬럼순서번호 라는군요. 첨시작할시는 1) Column_Name : 컬럼이름. Collation : 인덱스가 소트가 되어있는가인데...A는 순차적이고 NULL은 안소트라는군요.. Cardinality : 인덱스의 유니크한값이라고 되어있네요!! 업데이트는 isamchk -a 일때라고 하는데...흠 (-,.-)a Sub_part : 인덱스된 문자의 번호...인덱스중에서 부분문자열로 인덱스한거 그거말하나바여 ^^;; Packed : 팩되었나? Comment : 헉! 코멘트까졍?

   

Full-text search is performed with the MATCH function.

mysql> CREATE TABLE articles ( -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -> title VARCHAR(200), -> body TEXT, -> FULLTEXT (title,body) -> ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO articles VALUES -> (0,'MySQL Tutorial', 'DBMS stands for DataBase Management ...'), -> (0,'How To Use MySQL Efficiently', 'After you went through a ...'), -> (0,'Optimising MySQL','In this tutorial we will show how to ...'), -> (0,'1001 MySQL Trick','1. Never run mysqld as root. 2. Normalise ...'), -> (0,'MySQL vs. YourSQL', 'In the following database comparison we ...'), -> (0,'MySQL Security', 'When configured properly, MySQL could be ...'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database'); +----+-------------------+---------------------------------------------+ | id | title | body | +----+-------------------+---------------------------------------------+ | 5 | MySQL vs. YourSQL | In the following database comparison we ... | | 1 | MySQL Tutorial | DBMS stands for DataBase Management ... | +----+-------------------+---------------------------------------------+ 2 rows in set (0.00 sec) Full-text index는 against로 확인할 수 있다.

   

12.4 How MySQL uses indexes - from MySQL reference

Indexes are used to find rows with a specific value of one column fast. Without an index MySQL has to start with the first record and then read through the whole table until it finds the relevent rows. The bigger the table, the more this costs. If the table has an index for the colums in question, MySQL can get fast a position to seek to in the middle of the data file without having to look at all the data. If a table has 1000 rows this is at least 100 times faster than reading sequentially. Note that if you need to access almost all 1000 rows it is faster to read sequentially because we then avoid disk seeks. All MySQL indexes (PRIMARY, UNIQUE and INDEX) are stored in B-trees. Strings are automatically prefix- and end-space compressed. See section 7.35 CREATE INDEX syntax. Indexes are used to: Quickly find the rows that match a WHERE clause. Retrieve rows from other tables when performing joins. Find the MAX() or MIN() value for a specific indexed column. This is optimized by a pre-processor that checks if you are using WHERE key_part_# = constant on all key parts < N. In this case MySQL will do a single key lookup and replace the MIN() expression with a constant. If all expressions are replaced with constants, the query will return at once. SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10 Sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part_1,key_part_2 ). The key is read in reverse order if all key parts are followed by DESC. The index can also be used even if the ORDER BY doesn't match gthe index exactly, as long as all the not used index parts and all the extra are ORDER BY columns are constants in the WHERE clause. The following queries will use the index to resolve the ORDER BY part. SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3; SELECT * FROM foo WHERE column=constant ORDER BY column, key_part1; SELECT * FROM foo WHERE key_part1=const GROUP BY key_part2; In some cases a query can be optimized to retrieve values without consulting the data file. If all used columns for some table are numeric and form a leftmost prefix for some key, the values may be retrieved from the index tree for greater speed. SELECT key_part3 FROM table_name WHERE key_part1=1 Suppose you issue the following SELECT statement: mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index will find fewer rows and using that index to fetch the rows. If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1,col2,col3), you have indexed search capabilities on (col1), (col1,col2) and (col1,col2,col3). MySQL can't use a partial index if the columns don't form a leftmost prefix of the index. Suppose you have the SELECT statements shown below: mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3; If an index exists on (col1,col2,col3), only the first query shown above uses the index. The second and third queries do involve indexed columns, but (col2) and (col2,col3) are not leftmost prefixes of (col1,col2,col3). MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant string that doesn't start with a wild-card character. For example, the following SELECT statements use indexes: mysql> select * from tbl_name where key_col LIKE "Patrick%"; mysql> select * from tbl_name where key_col LIKE "Pat%_ck%"; In the first statement, only rows with "Patrick" <= key_col < "Patricl" are considered. In the second statement, only rows with "Pat" <= key_col < "Pau" are considered. The following SELECT statements will not use indexes: mysql> select * from tbl_name where key_col LIKE "%Patrick%"; mysql> select * from tbl_name where key_col LIKE other_col; In the first statement, the LIKE value begins with a wild-card character. In the second statement, the LIKE value is not a constant. Searching using column_name IS NULL will use indexes if column_name is an index. MySQL normally uses the index that finds least number of rows. An index is used for columns that you compare with the following operators: =, >, >=, <, <=, BETWEEN and a LIKE with a non-wild-card prefix like 'something%'. Any index that doesn't span all AND levels in the WHERE clause is not used to optimize the query. In other words: To be able to use an index, a prefix of the index must be used in every AND group. The following WHERE clauses use indexes: ... WHERE index_part1=1 AND index_part2=2 AND other_column=3 ... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */ ... WHERE index_part1='hello' AND index_part_3=5 /* optimized like "index_part1='hello'" */ ... WHERE index1=1 and index2=2 or index1=3 and index3=3; /* Can use index on index1 but not on index2 or index 3 */ These WHERE clauses do NOT use indexes: ... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */ ... WHERE index=1 OR A=10 /* Index is not used in both AND parts */ ... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */ Note that in some cases MySQL will not use an index, even if one would be available. Some of the cases where this happens are: If the use of the index, would require MySQL to access more than 30 % of the rows in the table. (In this case a table scan is probably much faster as this will require us to do much fewer seeks). Note that if you with such a query use LIMIT to only retrieve part of the rows, MySQL will use an index anyway as it can this way much more quickly find the few rows to return in the result.

   

기타

primary key 로 설정하면 자동으로 인덱스가 걸립니다. .myi는 인덱스정보를 담고 있는 파일입니다.