[MySQL]3. 정렬과 연산

9. 정렬 - ORDER BY

ORDER BY 구를 사용하여 검색 결과의 행 순서를 바꿀 수 있다. 지정하지 않을 경우 데이터베이스 내부에 저장된 순서로 반환된다.

ORDER BY는 검색된 레코드를 어떤 순서로 정렬할지 결정한다. 만약 ORDER BY 절이 사용되지 않으면 SELECT 쿼리의 결과는 어떤 순서로 정렬될까?

인덱스를 사용한 SELECT의 경우에는 인덱스의 정렬된 순서대로 레코드를 가져온다.
인덱스를 사용하지 못하고 MyISAM 테이블은 테이블에 저장된 순서대로 가져오는데 이 순서가 INSERT 된 순서를 의미하는 것은 아니다. 일반적으로 테이블의 레코듸가 삭제 되면서 빈 공간이 생기고, ISNERT 되는 레코드는 항상 테이블의 마지막이 아니라 빈 공간이 있으면 그 빈 공간에 저장되기 때문이다. InnoDB의 경우는 항상 프라이머리 키로 클러스터링돼 있기 때문에 풀 테이블 스캔의 경우에는 기본적으로 프라이머리 키 순서대로 레코드를 가져온다.
SELECT 쿼리가 임시 테이블을 거쳐서 처리되면 조회되는 레코드의 순서를 예측하기는 어렵다.

ORDER BY에서 인덱스를 사용하지 못할 때는 추가적인 정렬 작업을 수행하고, 쿼리 실행 계획에 있는 Extra 칼럼에 Using filesort라는 코멘트가 표시된다.

WHERE -> SELECT -> ORDER BY 순서로 처리된다.

1) ORDER BY로 검색 결과 정렬하기

SELECT 열명
FROM 테이블명
WHERE 조건식
ORDER BY [DESC/ASC] 열명;

2) 대소 관계

수치형 : 숫자의 크기
날짜 : 숫자의 크기
문자열 : 숫자 < 대문자 < 소문자 < 한글 (UTF-8)
NULL : ASC 제일 먼저, DESC에는 제일 나중에

또한 같은 숫자라도 수치형과 문자형 데이터의 대소 관계가 다를 수 있으니 주의해야한다.

숫자 : 1 < 2 < 10 < 11
문자 : 1 < 10 < 11 < 2 : 문자형은 첫 글자를 기준으로 정렬한다.

10. 복수의 열을 지정해 정렬하기

SELECT 열명
FROM 테이블명
WHERE 조건식
ORDER BY 열1 [ASC|DESC]
    , 열2[ASC|DESC]
    , ...

여러 방향으로 동시정렬 하는 경우

CREATE TABLE tb_m(
    region VARCHAR(20) CHARACTER SET 'utf8' ,
    age INT,
    INDEX ix_age_region (age, region)
);

INSERT INTO tb_m VALUES('경기', 20);
INSERT INTO tb_m VALUES('경기', 25);
INSERT INTO tb_m VALUES('서울', 25);

해당 테이블에서 나이는 내림차순으로, 지역은 오름차순으로 정렬하고자 한다. 두 열에 모두 인덱스가 사용되고 있다.

EXPLAIN
SELECT age, region
FROM tb_m
ORDER BY age DESC, region ASC;

desc_explain

Extra에 Using filesort를 확인할 수 있다. ORDER BY에서 인덱스를 사용하지 못할 떄는 추가적인 정렬 작업을 수행하고, 쿼리 실행 계획에 Using filesort를 표시한다. Using filesort는 쿼리를 수행하는 도중에 MySQL 서버가 퀵 소트 정렬 알고리즘을 수행한다는 의미?이다.
filesort 시 정렬 대상이 많은 경우 여러 부분으로 나눠 처리하는데, 결과를 임시로 디스크나 메모리에 저장할 경우 인덱스를 사용할 수 없게된다.

이처럼 여러 개의 칼럼을 조함해서 정렬할 떄 각 칼럼의 정렬 순서가 오름차순과 내림차순이 혼용되면 인덱스를 이용할 수 없다. 이를 해결하기위해 칼럼의 값 자체를 변형시켜 테이블에 저장하는 방법이 있다. 문자엹 타입은 별다른 방법이 없지만 숫자나 날짜 타입은 다음과 같이 변경해서 사용할 수 있다.

숫자 타입의 값은 반대 부호로 변환해서 칼럼에 저장한다.
날짜 타입의 값은 TIMESTAMP 타입 값으로 변환하면 정수 타입으로 변환할 수 있다. 이 값의 부호를 음수로 만들어 저장한다. SELECT CAST(NOW() AS SIGNED);

INSERT INTO tb_m VALUES('경기', 20);
INSERT INTO tb_m VALUES('경기', 25);
INSERT INTO tb_m VALUES('서울', 25);


EXPLAIN
SELECT age * -1 AS _age, region
FROM tb_m
ORDER BY age ASC, region ASC;

explain

Extra 필드를 확인하면 인덱스 사용한다는 것을 알 수 이

//나이 내림차, 지역 오름차
+------+--------+
| _age | region |
+------+--------+
|   25 | 경기   |
|   25 | 서울   |
|   20 | 경기   |
+------+--------+

INDEX DESC 설정 : MySQL에선 8.0 에서 지원할 것이다 참고

11. 결과 행 제한하기 - LIMIT

SERECT 열명
FROM 테이블명
LIMIT 행수 [OFFSET 시작행]

EXPLAIN
SELECT *
FROM health_data
LIMIT 100000 OFFSET 500000;
;

INDEX를 사용하지 못하고 있다.

이렇게 INDEX 사용을 위해 WHERE절을 사용하긴 했지만 쿼리 진행에는 차이가 있다. LIMIT는 WHERE, ORDER BY를 거쳐 가장 마지막에 처리된다.

실행순서

WHERE 적용 및 조인 실행

드라이빙 테이블

드리븐 테이블 1

드리븐 테이블 2

GROUP BY 적용 (레코드 그룹핑)

DISTICNT 적용

HAVING 조건 필터링

ORDER BY 적용

LIMIT 적용

EXPLAIN
SELECT *
FROM health_data
WHERE id > 500000 ###########
LIMIT 100000;
;

INDEX를 사용한다.

12. 수치 연산

+
-
/
*
MOD(N, M);
N MOD M;
N % m;

시험 테이블

CREATE TABLE sample34(
    id INT PRIMARY KEY NOT NULL,
    price INT NOT NULL,
    quantity INT NOT NULL,
    INDEX idx_price_quantity (price, quantity)
);
+----+-------+----------+
| id | price | quantity |
+----+-------+----------+
|  1 |   100 |       10 |
|  2 |   230 |       24 |
|  3 |  1980 |        1 |
+----+-------+----------+

INSERT INTO sample34
VALUES(1 , 100, 10),
(2, 230, 24)
, (3, 1980, 1);

EXPLAIN
SELECT *
FROM sample34
WHERE price * quantity > 0

EXPLAIN
SELECT *, price * quantity AS amount # alias 지정
FROM sample34;

인덱스가 지정된 칼럼을 SELECT나 WHERE 구에서 연산을 해도 인덱스를 사용한다.

EXPLAIN
SELECT *, price * quantity AS amount
FROM sample34
ORDER BY amount DESC;

ORDER BY 절에 사용될 경우 using filesort;가 보인다.

NULL + 1
1 / NULL
...
모두 NULL


mysql> SELECT FALSE = NULL;
+--------------+
| FALSE = NULL |
+--------------+
|         NULL |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT FALSE != NULL;
+---------------+
| FALSE != NULL |
+---------------+
|          NULL |
+---------------+

13. 문자열 연산


# 문자열 결합

- 문자열은 UTF-8 사용이 보편적

SELECT CONCAT('ABC','1234');
+----------------------+
| CONCAT('ABC','1234') |
+----------------------+
| ABC1234              |
+----------------------+


# 문자열 자르기

#SUBSTRING(string, start, length)
SELECT SUBSTRING('20190402', 1, 4);#연도 자르기
+-----------------------------+
| SUBSTRING('20190402', 1, 4) |
+-----------------------------+
| 2019                        |
+-----------------------------+


# 공백제거

SELECT TRIM('  TEST   ');
+-------------------+
| TRIM('  TEST   ') |
+-------------------+
| TEST              |
+-------------------+

# CHARACTER_LENGTH

/*
CHARACTER_LENGTH은 문자열의 길이를 반환한다.

*/

SELECT CHARACTER_LENGTH("SQL Tutorial") AS LengthOfString;
+----------------+
| LengthOfString |
+----------------+
|             12 |
+----------------+


/*

    문자 인코딩 방식에 따라 저장되는 데이터 크기가 다르다
    - utf-8 : 한글을 3bytes / euckr : 한글을 2bytes

*/

CREATE TABLE sample_utf8
(
    text VARCHAR(32) CHARACTER SET 'utf8' 
);
INSERT INTO sample_utf8
VALUES ("헬로월드");
SELECT CHARACTER_LENGTH(text)
FROM sample_utf8;

SELECT CHARACTER_LENGTH(text)
FROM sample_utf8;
+------------------------+
| CHARACTER_LENGTH(text) |
+------------------------+
|                      4 |
+------------------------+

SELECT OCTET_LENGTH(text)
FROM sample_utf8;
+--------------------+
| OCTET_LENGTH(text) |
+--------------------+
|                 12 |
+--------------------+


CREATE TABLE sample_euckr
(
    text VARCHAR(32) CHARACTER SET 'euckr'
);

INSERT INTO sample_euckr
VALUES ("헬로월드");

SELECT CHARACTER_LENGTH(text)
FROM sample_euckr;
+------------------------+
| CHARACTER_LENGTH(text) |
+------------------------+
|                      4 |
+------------------------+

SELECT OCTET_LENGTH(text)
FROM sample_euckr;
+--------------------+
| OCTET_LENGTH(text) |
+--------------------+
|                  8 |
+--------------------+

14 날짜 연산

여기에서 기타 날짜/시간 관련 함수를 확인할 수 있다.


SELECT CURRENT_TIMESTAMP, NOW();

/*
*    NOW()        : 구문이 실행하기 시작하는 시간의 상수를 출력
*    SYSDATE()    : 이 함수가 실행 될 떄의 시간을 출력
*/

SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+





/*
*    # DATEDIFF(expr1,expr2)
*    - expr1 - expr2한 일자를 반환 모두 date나 date time 표현이여야 한다.
*/

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
        -> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
        -> -31

15. CASE 문으로 데이터 변환하기

CASE WHEN 조건식1 THEN 식1
    [ WHEN 조건식1 THEN 식2    ... ]
    [ ELSE 식3]
END

기존의 연산자나 함수만으로 처리할 수 없는 계산을 정의할 수 있따.
WHEN 절에는 참과 거짓을 반환하는 조건식 기술하며 해당 조건을 만족하여 참이되는 경우 THEN 절에 기술한 식이 처리된다. 어떤 조건식도 만족하지 못하면 ELSE 절에 기술한 식이 채택된다. ELSE는 생략 가능하며 생략할 경우 ELSE NULL로 간주된다.

+------+
| a    |
+------+
|    1 |
|    2 |
| NULL |
+------+

# NULL을 0으로 변환하는 CASE문
SELECT a,
    CASE
        WHEN a IS NULL THEN 0
        ELSE a
    END AS 'a(null=0)'
FROM sample73;
+------+-----------+
| a    | a(null=0) |
+------+-----------+
|    1 |         1 |
|    2 |         2 |
| NULL |         0 |
+------+-----------+


# NULL을 변환하는 경우 COALESCE 함수를 사용할 수 있다.
SELECT a,
    COALESCE(a, 0)
FROM sample37;
+------+----------------+
| a    | COALESCE(a, 0) |
+------+----------------+
|    1 |              1 |
|    2 |              2 |
| NULL |              0 |
+------+----------------+

# IFNULL()도 사용할 수 있다.
SELECT
    a,
    IFNULL(a, 0)
FROM sample37;
+------+--------------+
| a    | IFNULL(a, 0) |
+------+--------------+
|    1 |            1 |
|    2 |            2 |
| NULL |            0 |
+------+--------------+

CASE문

CASE 식1
    WHEN 식2 THEN 식3
    [ WHEN 식4  THEN 식5, ... ]
    [ ELSE 식6 ]
END

식 1의 값이 WHEN의 식2과 동일한지 비교하고 같다면 식3의 값이 CASE문 전체의 결과값이 된다.

SELECT
    CASE a
        WHEN 1 THEN '남자'
        WHEN 2 THEN '여자'
        ELSE '미지정'
    END AS 'gender'
FROM sample37;
+-----------+
| gender    |
+-----------+
| 남자      |
| 여자      |
| 미지정    |
+-----------+

ELSE를 생략했을 때 대응하는 WHEN이 하나도 없으면 기본으로 NULL을 반환하게 된다. ELSE를 생략하면 상정한 것 이외의 데이터가 왔을 때 NULL이 반환되므로 ELSE를 생략하지 않고 지정하는 것이 좋다.

CASE a
    WHEN 1 THEN '남자'
    WHEN 2 THEN '여자'
    WHEN NULL THEN '데이터없음'        #a=NULL로는 NULL 판별을 할 수 없다.
    ELSE '미지정'
END

CASE
    WHEN a=1 THEN '남자'
    WHEN a=2 THEN '여자'
    WHEN a IS NULL THEN '데이터없음'        #IS NULL을 사용
    ELSE '미지정'
END

CASE 문을 사용할 때도 인덱스가 적용된다.

SELECT에는 *를 사용하지 않도록 권장 : 필요한 열만 가져와 데이터크기가 커지지 않게 한다.

저작자표시 비영리 변경금지

'DB > MySQL' 카테고리의 다른 글

[MySQL] 5. 집계와 서브쿼리 (0)	2019.05.04
[MySQL] 4. 데이터의 추가, 삭제, 갱신 (0)	2019.05.03
[MySQL] 2. 테이블에서 데이터 검색 (0)	2019.04.16
[MySQL] 1. 데이터베이스와 SQL (0)	2019.04.09
[MySQL] worckbench로 다이어그램 그리기 (0)	2019.04.01

이것저것끄적

[MySQL]3. 정렬과 연산

9. 정렬 - ORDER BY

1) ORDER BY로 검색 결과 정렬하기

2) 대소 관계

10. 복수의 열을 지정해 정렬하기

여러 방향으로 동시정렬 하는 경우

11. 결과 행 제한하기 - LIMIT

12. 수치 연산

13. 문자열 연산

14 날짜 연산

15. CASE 문으로 데이터 변환하기

'DB > MySQL' 카테고리의 다른 글

티스토리툴바

[MySQL]3. 정렬과 연산

9. 정렬 - ORDER BY

1) ORDER BY로 검색 결과 정렬하기

2) 대소 관계

10. 복수의 열을 지정해 정렬하기

여러 방향으로 동시정렬 하는 경우

11. 결과 행 제한하기 - LIMIT

12. 수치 연산

13. 문자열 연산

14 날짜 연산

15. CASE 문으로 데이터 변환하기

'DB > MySQL' 카테고리의 다른 글

'DB/MySQL' Related Articles

티스토리툴바