like 검색도 in 처럼 여러개 검색이 가능하다.

in 절 처럼 like 여러개 검색하고 싶은 경우

where 컬럼 REGEXP ('문자열1|문자열2|문자열3')

1. ORDER BY FIELD() 를 사용하여 '자전거'가 첫번째, '비행기'가 두번째, 그외 나머지는 오름차순으로 정렬하기

SELECT 
	product_name
FROM
    COIN
ORDER BY
    FIELD(product_name, '자전거', '비행기') DESC,
    product_name ASC

- FIELD 함수의 첫번째 파라미터에 정렬할 컬럼을 넣고, 그 이후 파라미터엔 우선적으로 정렬할 값들을 역순으로 넣어준다.

그리고 FIELD 함수를 DESC정렬 시켜주면 된다.

- 원리는 FIELD 함수를 사용하면 첫번째 파라미터의 값과 같은 값이 그 이후 파라미터의 몇번째에 있는지 index값을 반환한다.

즉 이후 파라미터의 첫번째에 있는 '자전거'는 1이 반환되고, 두번째에 있는 '비행기'는 2가 반환이 된다.

같은 값이 없는 나머지는 0이 반환된다.

이것을 DESC정렬하여 우선적으로 정렬되게 한다. 

2. order by 에 case 문을 사용

SELECT
	*
FROM 
	table A
ORDER BY 
	CASE WHEN A.product_name = '자전거' THEN 1 
	ELSE
		2
	END

똑같은 원리다. 상위에 정렬 시키고 싶은 내용들을 case 문의 상수값을 작은 숫자로 반환시켜주면 된다.

mysql 에는 rownum 이라는 키워드가 없다,

그래서 변수 선언을 통해서 임의로 rownum 을 표시할 수 있다.

rownum 생성 방법

SELECT
  @rownum:=@rownum+1 as no
  table.*
FROM
  table 
where 
  (@rownum:0)=0

where 절에서 변수를 초기화 하고 사용하는 방법이다.

select 절보다 where 절에서 먼저 실행되기 때문에 where 절에서 변수 선운 후 사용 가능하다.

또는 아래와 같은 set 키워드로 변수를 선언 가능하다.

SET @rownum:=0;
SELECT
  @rownum:=@rownum+1 as no
  table.*
FROM
 table;

mysql Table 정보검색 및 column정보 검색

1 테이블 정보검색.

select * 

from INFORMATION_SCHEMA.tables 

where table_schema='데이터베이스명';

=> show tables;

2. 컬럼명 검색.

select * 

 from INFORMATION_SCHEMA.columns 

where table_schema='데이터베이스명' 

    and table_name='테이블명' 

 order by ordinal_position;

=> show full columns from 테이블명 ;

table

어떤 테이블에 대한 접근을 표시하고 있는지는 table 필드에 표시되어있다.

id

id는 SELECT에 붙은 번호를 말한다. MySQL은 조인을 하나의 단위로 실행하기 때문에 id는 그 쿼리에 실행 단위를 식별하는 것이다. 따라서 조인만 수행하는 쿼리에서는 id는 항상 1이 된다.

select_type

select_type은 항상 SIMPLE 이된다. 복잡한 조인을 해도 SIMPLE이 된다. 서브쿼리나 UNION이 있으면 id와 select_type이 변한다.

SIMPLE: 단순 select ( union이나 서브쿼리를 사용하지 않음 )

PRIMARY: 가장 외곽에 있는 select문

UNION: union에서의 두번째 혹은 나중에 따라오는 select문

DEPENDENT UNION: union에서의 두번째 혹은 나중에 따라오는 select문, 외곽 쿼리에 의존적이다.

UNION RESULT: union의 결과물

SUBQUERY: 서브쿼리의 첫번째 select

DEPENDENT SUBQUERY: 서브쿼리의 첫번째 select, 바깥 쪽 쿼리에 의존적이다.

DERIVED: from절의 서브쿼리

partitions

partitions는 파티셔닝이 되어 있는 경우에 사용되는 필드이다. 이 쿼리에서 사용된 테이블을 모두 파티셔닝이 되어 있지 않기 때문에 이 필드가 모두 NULL로 출력되았다. 파티셔닝 되어 있는 경우는 반드시 이 필드를 확인하자

type

type은 접근 방식을 표시하는 필드다. 접근 방식은 테이블에서 어떻게 행데이터를 가져올것인가를 가리킨다.

ALL, eq_ref는 조인시 기본 키나 고유키를 사용하여 하나의 값으로 접근(최대 1행만을 정확하게 패치), ref는 여러 개의 행을 패치할 가능성이 있는 접근을 의미한다.접근 방식은 대상 테이블로의 접근이 효율적일지 여부를 판단하는 데 아주 중요한 항목이다.

이들 접근 방식 가운데도 주의가 필요한 것은 ALL, index, ref_or_null이다.

ALL, index 두 가지는 테이블 또는 특정 인덱스가 전체 행에 접근하기 때문에 테이블 크기가 크면 효율이 떨어진다. ref_or_null의 경우 NUL이 들어있는 행은 인덱스의 맨 앞에 모아서 저장하지만 그 건수가 많으면 MySQL 서버의 작업량이 방대해진다. 다시 말해서 ALL 이외의 접근 방식은 모두 인덱스를 사용한다.

접근 방식이 ALL 또는 index인 경우는 그 쿼리로 사용할 수 있는 적절한 인덱스가 없다는 의미일 수도 있다. 위 쿼리에서 Country 테이블에 대한 접근은 ALL이지만 이는 WHERE 구의 조건을 지정하지 않았기 때문이다. 그러한 쿼리에서 드라이빙 테이블에 접근한다면 전체 행을 스캔 할수 밖에 없다.

접근 방식설명

possible_keys

possible_keys 필드는 이용 가능성있는 인덱스의 목록이다. [테이블 검색에 사용할 수 있는 인덱스]

key

possible_keys 필드는 이용 가능성있는 인덱스의 목록 중에서 실제로 옵티마이저가 선택한 인덱스가 key가 된다. 위 EXPLAN 에서는 County 테이블(첫 번째 행)의Key는 NULL 인데 이는 행 데이터를 가져오기 위해 인덱스를 사용할 수 없다는 의미이다.

실제 사용한 key 임

key_len

key_len 필드는 선택된 인덱스의 길이를 의미한다.

중요한 필드는 아니지만 인덱스가 너무 긴 것도 비효율적이므로 기억해두자.

rows

rows는 이 접근 방식을 사용해 몇 행을 가져왔는가를 표시한다. 최초에 접근하는 테이블에 대해서 쿼리 전체에 의해 접근하는 행 수, 그 이후에 테이블에 대해서는 1행의 조인으로 평균 몇 행에 접근했는가를 표시한다. 단 어디까지나 통계 값으로 계산한 값이므로 실제 행 수와 반드시 일치하지 않는다.

filtered

filtered는 행 데이터를 가져와 WHERE 구의 검색 조건이 적용되면 몇행이 남는지를 표시한다. 이 값도 통계 값 바탕으로 계산한 값이므로 현실의 값과 반드시 일치하지 않는다.

extra

Extra 필드는 옵티마이저가 동작하는데 대해서 우리에게 알려주는 힌트다. 이 필드는 EXPLAN을 사용해 옵티마이저의 행동을 파악할때 아주 중요하다.

참고문헌

https://cheese10yun.github.io/mysql-explian/ [MySQL 실행계획]

쿼리 실행 절차

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

첫 번째 단계를 "SQL 파싱(Parsing)"이라고 하며, MySQL 서버의 "SQL 파서"라는 모듈로 처리합니다. 만약 SQL 문장이 문법적으로 잘못됐다면 이 단계에서 걸러집니다. 또한 이 단계에서 "SQL 파스 트리"가 만들어집니다. MySQL 서버는 SQL 문장 그 자체가 아니라 SQL 파스 트리를 이용해 쿼리를 실행합니다.

두 번째 단계는 첫 번째 단계에서 만들어진 SQL 파스 트리를 참조하면서, 다음과 같은 내용을 처리합니다.

불필요한 조건의 제거 및 복잡한 연산의 단순화  
여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정  
각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정  
가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정

물론 이 밖에도 수많은 처리를 하지만, 대표적으로 이런 작업을 들 수 있습니다. 두 번째 단계는 "최적화 및 실행 계획 수립" 단계이며, MySQL 서버의 "옵티마이저"에서 처리합니다. 또한 두 번째 단계가 오나료되면 쿼리의 "실행 계획"이 만들어집니다.

세 번째 단계는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행합니다.

옵티마이저의종류

1. 비용 기반 최적화(Cost-based optimizer, CBO)
2. 규칙 기반 최적화 방법(Rule-based optimizer, RBO)

비용 기반 최적화는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 각 실행 계획별 비용을 산출한다. 이렇게 산출된 각 실행 방법별로 최소 비용이 소요되는 처리 방식을 선택해 최종 쿼리를 실행하게 된다.

규칙 기반 최적화는 각 테이블이나 인덱스의 통계 정보가 거의 없고, 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스러웠기 때문에 사용되던 최적화 방법이다. 현재는 거의 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있으며, MySQL 역시 마찬가지이다.

통계정보

MySQL에서 관리되는 통계 정보는 대략의 레코드 건수와 인덱스의 유니크한 값의 개수 정도가 전부이다. 오라클과 같은 DBMS에서는 통계 정보가 상당히 정적이고 수집에 많은 시간이 소요되기 때문에 통계 정보만 따로 백업하기도 한다. 하지만 MySQL에서 통계 정보는 사용자가 알아채지 못하는 순간순간 자동으로 변경되기 때문에 상당히 동적인 편이다. 하지만 레코드 건수가 많지 않으면 통계 정보가 상당히 부정확한 경우가 많으므로 "ANALYZE" 명령을 이용해 강제적으로 통계 정보를 갱신해야 할 때도 있다. 특히 이런 현상은 레코드 건수가 얼마 되지 않는 개발용 MySQL 서버에서 자주 발생한다.

MEMORY 테이블은 별도로 통계 정보가 없으며, MyISAM과 InnoDB의 테이블과 인덱스 통계 정보는 다음과 같이 확인할 수 있다. ANALYZE 명령은 인덱스 키값의 분포도(선택도)만 업데이트하며, 전체 테이블의 건수는 테이블의 전체 페이지 수를 이용해 예측한다.

MyISAM 테이블의 ANALYZE는 정확한 키값 분포도를 위해 인덱스 전체를 스캔하므로 많은 시간이 소요된다. 이와는 달리 InnoDB 테이블은 인덱스 페이지 중에서 8개 정도만 랜덤하게 선택해서 분석하고 그 결과를 인덱스의 통계 정보로 갱신한다.

https://12bme.tistory.com/160 [mysql 옵티마지이져원리 부터 실행계획까지]