Today I Learned

오늘은 SQL 5장 정렬, 인덱스, 레코드 제한에 대해 공부했다.
오늘도 깃허브 1일 1커밋을 하고있는데, 잔디가 왜 노랗자? 놀래서 검색해보니 할로윈이라서 그런거였다. 잔디가 잘 여물은 것 같아 보기 좋다.

---

ORDER BY(정렬)

가져온 레코드를 정렬해서 표시

SELECT 컬럼명
FROM 테이블명
ORDER BY 기준컬럼명 정렬방식(ASC or DESC);

• 기준이 되는 컬럼명을 정렬 키 or 소트 키(sort key)라고 한다.

• SELECT에 없는 컬럼도 정렬 키로 사용이 가능하다.

• ASC는 오름차순, DESC는 내림차순이다.

• 정렬방식을 생략하면 오름차순으로 정렬된다.

• ORDER BY 구는 가장 마지막에 작성한다.

• 실행 순서도 전체에서 가장 마지막에 실행되어 결과물을 정렬할 뿐이다.

• ORDER BY구는 데이터가 많아지면 사용 시 부하와 처리 시간이 상당부분 소요되므로 꼭 필요한 경우에만 사용하는 것이 좋다.

• 예시(오름차순)

SELECT *
FROM product
ORDER BY price ASC;

• 예시(내림차순)

SELECT *
FROM product
ORDER BY price DESC;

---

번호 사용

SELECT 구에 있는 컬럼 순서대로 번호를 부여해 그 번호를 정렬키로 적을 수 있다.
그러나 가독성이 명확하진 않기 때문에 추천하는 방식은 아니다.

ex)

SELECT product_name, stock
FROM product
ORDER BY 2; // 1은 product_name, 2는 stock 컬럼을 지칭한다.

---

연산자, 함수를 정렬 키로 활용

연산자나 함수도 정렬키로 활용이 가능하다.

ex)

SELECT product_name, stock * price
FROM product
ORDER BY stock * price;

---

복수의 정렬 기준

같은 순위의 레코드의 순서는 정해져있지 않다. 이를 정하고 싶으면 ORDER BY구에 세미콜론(,)으로 다음번 정렬 기준을 적으면 된다.

ORDER BY column1 DESC, column2 ASC, column3 DESC,...

• 먼저 column1을 기준으로 내림차순으로 정렬하고, 같은 순위만 column2를 기준으로 오름차순, 또 다시 동순위가 나오면 column3 내림차순으로 정렬,... 하는 방식이다.

• 예시

SELECT *
FROM product
ORDER BY price ASC, stock DESC;

---

다른 구와 사용

• WHERE구나 GROUP BY구와 당연히 같이 사용이 가능하다.

• 예시 : inquㅑry 테이블을 pref으로 그룹화하고 그룹의 레코드 수로 정렬해라.

SELECT pref, COUNT(*)
FROM inquiry
GROUP BY pref
ORDER BY count(*);

---

NULL 취급

• DBMS에 따라 다를 수 있지만 MySQL에서는 정렬 컬럼의 레코드에 NULL이 있으면 오름차순(ASC)의 경우 NULL 레코드가 가장 위로 올라온다.

SELECT * FROM product
ORDER BY price ASC;

• NULL 레코드를 가장 뒤로 밀고 싶으면, price IS NULL ASC를 ORDER BY구 가장 처음에 두면된다. NULL인값이 1, 아니면 0인데 오름차순이므로 NULL값들만 가장 뒤로 이동하게 된다.

SELECT * FROM product
ORDER BY price IS NULL ASC, price ASC;

특정 값 정렬

• 특정한 값들만 레코드의 가장 위나 아래에 오게 할 수 있다. 방법은 바로 위의 방법과 같다.

SELECT *
FROM product
ORDER BY price >= 140 DESC, price ASC;

• price >= 150인 레코드만 1이고 나머지는 0인데 내림차순이므로 해당 레코드가 1순위 정렬기준으로 가장 위에 온다. 그 후 price를 기준으로 오름차순 정렬을 한다.

SELECT구의 별명 사용

ORDER BY구는 SELECT구 다음에 실행되므로 SELECT구에서 설정한 별명을 문제없이 ORDER BY구에서 사용 가능하다.

SELECT product_name, price AS 가격
FROM product
ORDER BY 가격 ASC;

• 이 때, 별명은 '가격'이나 "가격"처럼 쿼테이션으로 감싸지 않고 바로(가격) 적는다.

---

대조 순서

문자열은 ASC로 정렬하면 기본적으로는 사전순이라고 표현을 한다. 하지만 엄밀히 따지면 대조 순서에 따라 정렬이 된다.

• 1,A,a,B,ab를 오름차순으로 정렬하려고 할 때,
  utf8_general_ci : 1,A,a,ab,B
  utf8_bin : 1,A,B,a,ab
  utf8_unicode_ci : 1,A,a,ab,B
  와 같이 대조순서에 따라 정렬되는 데이터의 순서가 달라진다.

• 따라서 여러 문자열이 섞여있는 데이터를 정렬할 땐 대조순서를 제대로 파악해야 한다.

---

인덱스

추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조

• 인덱스를 사용하면 ORDER BY를 사용할 때 더 빠른 정렬을 가능하게 해준다.

• DB의 인덱스는 각각의 컬럼에 대해서 만들 수 있다. 자주 검색하는 컬럼에 사용하는 것이 효율이 좋다.

• 테이블 조회 속도와 성능 향상이 가능하고, 전반적인 시스템 부하를 줄일 수 있다.

• DB의 약 10%의 추가적인 공간이 필요하다.

• 수정이 잦은 컬럼에 사용 시 오히려 성능이 저하될 수 있다.

• 보통은 기본 키(PK) 컬럼에 대해 인덱스가 자동으로 생성된다. 그래서 기본키 컬럼을 ORDER BY나 WHERE구로 사용 시 검색 및 정렬 속도가 빠르다.

---

LIMIT & OFFSET

LIMIT 과 OFFSET은 표준 SQL 문법이 아니라 MySQL과 PosetSQL에서만 사용이 가능하다. 다른 SQL DBMS에는 비슷한 기능의 다른 문법을 사용해야 한다.

• 예시 참고용 product 테이블(ORDER BY price ASC)
  

LIMIT

가져 온 레코드 중 첫 n행만 원할 경우 LIMIT을 사용하면 된다.

SELECT 컬럼명
FROM 테이블명
ORDER BY 정렬키
LIMIT 행수; // 지정된 행수만큼의 레코드만 가져온다.

• 예시
  product 테이블의 모든 컬럼을 price 기준 오름차순으로 정렬해 첫 3개의 레코드만 가져온다.

SELECT *
FROM product
ORDER BY price
LIMIT 3;

OFFSET

첫 n개가 아니라 k번째부터 n개를 가져와야할 경우 LIMIT과 OFFSET을 사용하면 된다.

• 가장 첫 레코드는 0에서부터 시작한다.
  따라서, OFFSET 시작하고싶은행수-1;로 써야한다.

SELECT 컬럼명
FROM 테이블명
ORDER BY 정렬키
LIMIT 행수
OFFSET 시작위치; // 시작위치부터 행수만큼의 레코드만 가져온다.

• 예시
  product 테이블의 전체 컬럼을 price 오름차순으로 정렬해서 시작위치 2부터 3개의 레코드를 가져온다.

SELECT *
FROM product
ORDER BY price
LIMIT 3
OFFSET 2;

OFFSET 생략

OFFSET을 LIMIT의 앞부분에 넣어 생략가능하다.

• LIMIT 행수 OFFSET 시작위치 를
  LIMIT 시작위치, 행수로 줄여서 표현이 가능하다.

LIMIT 3 OFFSET 2;
//위는 아래와 같다.
LIMIT 2,3;