====== MySQL Index ======

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

===== REFERENCES 구문 사용하지 말 것. =====
  * [[https://stackoverflow.com/questions/14230892/difference-between-using-references-with-and-without-foreign-key|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.

===== Index 생성 =====

==== Table 생성시 ====
<code sql>
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)
);
</code>

id 컬럼에 대한 인덱스를 생성한다. 두 개 이상의 컬럼에 대해서도 인덱스를 생성할 수 있다.
<code sql>
INDEX(id, passwd)
</code>
id 컬럼과 passwd 컬럼을 AND 조건으로 검색할 때 인덱스가 작동한다.

==== Table 생성 후 추가 ====
<code sql>
ALTER TABLE tablename ADD INDEX index_name(col1, col2,...);
</code>
index_name으로 인덱스 추가.

==== CREATE INDEX ====
  * [[https://dev.mysql.com/doc/refman/5.7/en/create-index.html|CREATE INDEX]]

<code sql>
CREATE INDEX IDX_PART_OF_NAME ON customer (first_name, last_name);
</code>

===== Index 정보 보기 =====
<code sql>
SHOW INDEX FROM tablename;
</code>

===== Index 삭제 =====
<code sql>
ALTER TABLE tablename DROP INDEX index_name;
</code>

===== Index Hint =====
[[http://dev.mysql.com/doc/refman/5.6/en/index-hints.html|MySql Index Hints]]

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

<code sql>
SELECT id FROM data USE INDEX(type)
WHERE type=12345 AND level > 3
ORDER BY id
</code>

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

<code sql>
SELECT id FROM data IGNORE INDEX(PRIMARY)
WHERE type=12345 AND level > 3
ORDER BY id
</code>


===== Covering Index =====
  * [[https://gywn.net/2012/04/mysql-covering-index/|MySQL에서 커버링 인덱스로 쿼리 성능을 높여보자!! | gywndi's database]]
  * Index 에 있는 값만으로 ''where'', ''order by'', ''select 각 필드''를 처리할 수 있는 상황.
  * Query 실행계획에서 **Extra:  Using index**가 나오는 상황.
  * 실제 데이터를 찾아 읽는 것보다 훨씬 빠르다.
  * 또한, 테이블 전체 컬럼이 select 대상이더라도 먼저 커버링 인덱스로 PK값만가져오고, 그 결과로 나머지 컬럼을 가져오는게 훨씬 빠른 경우도 많다.
<code sql>
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.*) 조회
</code>


===== 참조 =====
  * [[http://code.openark.org/blog/mysql/7-ways-to-convince-mysql-to-use-the-right-index|7 ways to convince MySQL to use the right index]]