MySQL Index

WHERE절에서 조건으로 쓰이는 컬럼을 인덱스로 만드는 것이 좋고, AND 연산으로 검색되는 컬럼들은 모두 하나의 인덱스로 만드는 것이 좋다.

• mysql - Difference between using REFERENCES with and without FOREIGN KEY? - Stack Overflow
• https://dev.mysql.com/doc/refman/5.7/en/create-table.html

> MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

CREATE TABLE tablename (id CHAR(13) NOT NULL,
passwd CHAR(8) NOT NULL,
name CHAR(8) NOT NULL,
email CHAR(64) NOT NULL,
INDEX (id)
);

id 컬럼에 대한 인덱스를 생성한다. 두 개 이상의 컬럼에 대해서도 인덱스를 생성할 수 있다.

INDEX(id, passwd)

id 컬럼과 passwd 컬럼을 AND 조건으로 검색할 때 인덱스가 작동한다.

ALTER TABLE tablename ADD INDEX index_name(col1, col2,...);

index_name으로 인덱스 추가.

• CREATE INDEX

CREATE INDEX IDX_PART_OF_NAME ON customer (first_name, last_name);

SHOW INDEX FROM tablename;

ALTER TABLE tablename DROP INDEX index_name;

MySql Index Hints

특정 인덱스를 타도록 가이드 한다.

SELECT id FROM DATA USE INDEX(TYPE)
WHERE TYPE=12345 AND level > 3
ORDER BY id

특정 인덱스는 무시하고 다른 인덱스들을 대상으로 최적화한다.

SELECT id FROM DATA IGNORE INDEX(PRIMARY)
WHERE TYPE=12345 AND level > 3
ORDER BY id

• MySQL에서 커버링 인덱스로 쿼리 성능을 높여보자!! | gywndi's database
• Index 에 있는 값만으로 where, order by, select 각 필드를 처리할 수 있는 상황.
• Query 실행계획에서 Extra: Using index가 나오는 상황.
• 실제 데이터를 찾아 읽는 것보다 훨씬 빠르다.
• 또한, 테이블 전체 컬럼이 select 대상이더라도 먼저 커버링 인덱스로 PK값만가져오고, 그 결과로 나머지 컬럼을 가져오는게 훨씬 빠른 경우도 많다.

SELECT a.*
FROM (
  -- 서브쿼리에서 커버링 인덱스로만 데이터 조건과 select column을 지정하고,
      SELECT userno
      FROM usertest
      WHERE chgdate LIKE '2012%'
      LIMIT 100000, 100
) b JOIN usertest a ON b.userno = a.userno
-- 조인을 통해 나머지 모든 컬럼(a.*) 조회

• 7 ways to convince MySQL to use the right index