-
MySQL - How to use INDEXFull-Stack/Back-end 2008. 8. 3. 18:40
MySQL - How to use INDEX?
장정환
웹프로그래밍이 널리 퍼지면서 그 파트너로 Database(이하 DB)가 널리 사용되어지고 있다. ASP로 대표되는 Windows진영에서는 M$의 MSSQL이 주로 사용되며 상업적인 사이트에서는 Oracle을 많이 이용하고 있다. 무료라는 장점으로 인해 근래 널리 퍼지고 있는 Linux의 경우에는 ProgreSQL, mSQL, MySQL등이 많이 사용되고 있다. 특히 MySQL의 경우에는 작년부터 주목받기 시작한 PHP와 짝을 이루면서 현재에는 Linux 기반에서 가장 많이 쓰이고 있는 DB중의 하나가 되었다.
이번 글에서는 점점 널리 쓰여지고 있는 MySQL에서 성능을 높이기 위해 INDEX를 사용하는 법과 사용할 때의 주의점들에 대해서 알아보도록 하겠다.
INDEX는 하나의 컬럼에서 특정한 값을 찾을때(즉, select작업을 할때) 주로 사용된다. MySQL에서는 어떠한 type의 컬럼이라도 인덱스될 수 있다. 하나의 table은 16개까지의 index를 가질 수 있다. index의 최대 길이는 256byte지만 이 값은 MySQL을 컴파일 할 때 변경할 수 있다. CHAR type이나 VARCHAR type의 경우에는 컬럼의 앞쪽의 일부만 인덱스화 할 수 있는데 이는 컬럼의 모든 부분을 인덱스 하는 것 보다 더 낫다. 그리고 BLOB나 TEXT type의 컬럼의 경우에는 모든 컬럼이 인덱스화 될 수 없으며 앞의 일부분만을 인덱스화 하여야 한다.
INDEX를 사용하기 위한 형식과 실제 table을 생성시킬때의 예제는 다음과 같다.
KEY index_name (column_name(length)) 예제1) Create table tablename( id varchar(14) primary key, name varchar(10) not null, occupation varchar(10) not null, address varchar(100), key idx_name(name), key idx_occupation(occupation) )
MySQL에서는 multiple-column index도 가능하다. 하나의 index에는 최고 15개의 컬럼을 가질 수 있다.
예제2) Create table tablename( id varchar(14) primary key, name varchar(10) not null, occupation varchar(10) not null, address varchar(100), key idx_name(name, occupation) )
여기서 multiple-column index를 사용할 때 주의하여야 한다. 실제로 multiple-column index가 사용될 때, 어떠한 방식으로 사용되는지 살펴볼 필요가 있다. 다음 예제를 살펴보자.
mysql> SELECT * FROM tablename WHERE name='dinosfx' AND occupation='scan';
여기서 name 컬럼과 occupation이 위의 예제1과 같이 두개의 컬럼 각각에 index가 설정되어 있다고 하자. 이 경우 MySQL에서는 name 컬럼이나 occupation 컬럼을 살펴보고 index로 살펴보아야 할 행의 수가 적은쪽을 선택하여 검색을 한다. 만약 occupation에서 검색해야 할 행의 수가 적다면 MySQL은 occupation 컬럼을 먼저 검색한다는 이야기이다.
그리고 예제2에서처럼 두개의 컬럼이 multiple-column으로 index가 설정되어 있다고 가정하자. 이 경우 MySQL은 원하는 값을 바로 찾아내게 된다.
이렇게 multiple-column index를 사용하면 검색을 손쉽게 할 수 있다. 그러나 multiple-column index를 사용할 때에는 주의할 점이 있다. multiple-column index에서는 가장 왼쪽의 컬럼이 select의 where절에 사용되어야 한다는 것이다. 다음의 예제를 보자.
이 예제에서 index는 KEY index_name (column1, column2, column3) 과 같이 걸려 있다.1) mysql> SELECT * FROM tablename WHERE column1 = value1 2) mysql> SELECT * FROM tablename WHERE column2 = value2; 3) mysql> SELECT * FROM tablename WHERE column2 = value2 and column3 = value3;
1)의 경우 index에서 제일 왼쪽에 설정되어 있는 column이므로 index를 사용하여 검색할 수 있다. 그러나 2)와 3)의 경우에는 column1이 검색 조건에 포함되지 않았으므로 index를 사용하지 못하고 검색이 이루어진다. 따라서 1), 2)와 3)의 세가지 경우에 대해서 모두 index를 사용하고자 한다면
key idx1(column1) key idx2(column2, column3)
와 같이 index를 설정하여야 할 것이다.
MySQL에서는 다음과 같은 경우에 index를 사용하여 검색을 한다. (예제1 참조)
mysql> select * from tablename where name LIKE "dino%"; mysql> select * from tablename where name LIKE "di%sf%";
다음과 같은 경우에는 index가 설정이 되어 있더라도 index를 사용하지 못하고 검색을 하게 된다. (예제1 참조)
mysql> select * from tablename where name LIKE "%nos%"; mysql> select * from tablename where name LIKE id; // 다른 컬럼과의 비교
첫번째의 경우에는 wild card문자가 앞쪽에 있기 때문에 index를 사용하지 못한다. 그러므로 위의 예제에서처럼 검색어의 가운데나 끝에 wild card문자가 들어가야 한다. 두번째의 경우에는 like에서의 비교 대상이 변하지 않는 값이 아니므로(the LIKE value is not a constant.) index를 사용할 수 없다.
MySQL에서는 이 외에도 =, >, >=, <, <=, BETWEEN, LIKE 연산자를 이용하여 검색을 할 때 index를 사용한다. (물론 검색어의 첫머리에 wild card 문자가 포함되면 index를 사용하지 못한다.)
간단하게나마 MySQL에서 index를 사용하여 검색 속도를 향상하는 방법을 살펴보았다. 그러나 실제로 index를 사용할 때 주의해야 할 점이 하나 있다. Index는 검색(select 작업)을 할때 큰 역할을 한다. (MySQL reference를 보면 1000개의 행에서 검색을 할때 index를 설정한 경우가 그렇지 않은 경우보다 적어도 100배 빠르다고 한다.) 하지만 빈번하게 insert나 update가 이루어지는 경우 index를 갱신하는 작업이 필요하므로 select할 때 줄인 시간만큼 insert나 update에서 더 시간이 걸릴 수도 있다. 그러므로 index의 사용은 table과 자료의 목적에 따라 적절히 사용되어야 할 것이다.
[출처] MySQL - How to use INDEX?|작성자 제임스
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=10Sort 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=1Suppose 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.
댓글