보통 랜덤으로 목록을 뽑을 때

select rnd_id, rnd_value from t_random_innodb order by rand() limit 10;

이런식으로 쿼리하시죠? 이게 간단해서 좋기는 한데 대상 컬럼수가 1만줄이 넘으면 느린걸 느낍니다. 10만 줄 쯤 되면 테이블 구조에 따라 1초 넘어가는 경우도 있고.. 해서 다른 방법을 찾아보았습니다.

1. 조건절에 random 확율연산만 넣는 방법

이 방법의 핵심은 아래쪽에 select 절에 있는 @lim := @lim -1 와 where 조건절에 있는 @cnt :=@cnt-1 입니다.

select 절에 있는 저 문장은 select 하는 결과레코드가 있을 때만 동작합니다. 즉 레코드가 하나 나타날때는 @lim 은 9로 줄고... 해서 10개의 레코드가 보이면 @lim은 0이 되죠.

아래 @lim/@cnt 의 random 확율에서 @lim 이 0이 되면 확률은 0이 되겠죠? 결과 레코드를 하나 뽑을 때마다 결과로 뽑힐 확률을 낮추는 효과가 있습니다.

where 조건절에 있는 @cnt :=@cnt-1  문장은 모든 대상열에 대해서 동작합니다. 즉 레코드 수가 1000개라면 처음에는 @cnt가 1000이 되고 마지막 레코드에서는 1이 됩니다. 커서가 뒤로 갈 수록 뽑힐 확률을 높여주는 역할을 합니다. 

이 방법의 장점은 order by rand() 보다 빠르다는 것입니다.  어떤 경우에든 목표로 하는 결과값이 다

나오며, 그 확률은 랜덤확률에 거의 근접합니다. (약간 차이가 있지만 무시할 수 있습니다)

500만건이 있는 테이블에서 10건을 뽑아내는데 order by rand()로 4.4초가 걸리는 쿼리가 이 방법을 쓰면 3.2초에 끝나게 됩니다.

단점도 있습니다. PK가 있는 테이블은 PK순서대로 결과값이 나옵니다. 만일 11개의 대상열이 있는데 10개를 뽑는다면? 거의 같은 목록이 똑같은 순서로 계속 나오게 됩니다. 단, 이건 * 로 뽑을때의 이야기이고, pk만 뽑아낼때는 랜덤하게 뽑아집니다.

또 CPU자원을 많이 사용합니다. 대상열이 100만줄 있으면 저 변수 변경, rand() 연산 및 비교연산을 100만번 반복해야 합니다...

상대적으로 innodb 보다는 myisam에서 빠른 쿼리입니다.

http://explainextended.com/2009/03/01/selecting-random-rows/ 요기서 본 내용입니다.

2. 임시테이블을 만들어 원하는 값을 insert 하는 방법

자동증가하는 PK가 있을 때 유용한 방법입니다.  프로시저로 보여드릴께요.

DELIMITER $$

CREATE PROCEDURE `pSelect_Random_Test`(
     In in_RecordCnt  int
)
BEGIN
    declare v_max_idx int default 0;
    declare v_idx int default 0;
    declare v_cnt int default 0;
    declare v_t_cnt int default 0;

-- 임시테이블을 만들고

    DROP TEMPORARY TABLE IF EXISTS tmp_random; 

    create temporary table tmp_random (rnd_id int not null, rnd_value int not null, primary key (rnd_id) ) engine=memory;

-- key의 max값을 뽑은 다음

    select max(rnd_id) from t_random_innodb into v_max_idx;

 loop_me : LOOP

    -- 임시테이블에 원하는 레코드가 들어갔거나 원하는 레코드에 대해 10배수의 insert 시도를 했을 경우 루프에서 벋어난다.
    select count(*) from tmp_random into v_cnt;

    if (v_t_cnt>=in_RecordCnt*10) or (v_cnt >= in_RecordCnt) then
       LEAVE loop_me;
    end if;

    -- 최대 key값에 random 값을 곱해서 최대키값보다 작은 어떤 임의의 키값을 하나 임시테이블에 입력한다.

    set v_idx = v_max_idx * rand() + 1;
    insert into tmp_random (rnd_id, rnd_value)
       select rnd_id, rnd_value from t_random_innodb where rnd_id <= v_idx order by id desc limit 1 on duplicate key update rnd_value=values(rnd_value);

  set v_t_cnt = v_t_cnt + 1;
 END LOOP loop_me;

 -- 임시테이블에 들어있는 값을 정렬 random 으로 쿼리한다.

 select * from tmp_random order by rand();
END $$
DELIMITER ; 

insert 건수가 많을 수록 2번이 1번보다 훨~씬 빨라진다.

500만건 기준으로 방법 1은 3.2초가 걸리는 쿼리가 방법 2로 하면 0.1초가 걸립니다^^

datetime 은 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 만 허용된다

그누보드 이전 mysql 5.x 버전에서

datetime NOT NULL default '0000-00-00 00:00:00'

허용되지 않는 0000-00-00 00:00:00 값이 들어가서 에러가 발생한다

이때는

SQL MODE 를 변경해주어야 한다

mysql> set sql_mode='ALLOW_INVALID_DATES';

또는

[my.cnf]

sql_mode=ALLOW_INVALID_DATES

데이터가 이미 있으면 따로 후속처리없이

그냥 아무행위도 안하고 나머지 데이터들만 INSERT를 진행하고자할 때

INSERT INTO 구문에 IGNORE을 추가해주면 된다.

해당 구문은 다음과 같다

기존 INSERT INTO 사이에 IGNORE을 추가해주기만 한거라

구문 자체는 매우 간단하다.

예)

가볍게 테이블 하나 있다 치고

위의 쿼리를 그대로 실행하면

이러한 결과가 나오며

해당 쿼리를 한번 더 실행하면

이미 PK값이 있기때문에 에러는 발생하지 않고 

IGNORE 뜻과 마찬가지로 데이터를 INSERT 하지 않는다.

그래서 결과는 그대로이다.

----------------

- DUPLICATE ON KEY UPDATE  - 

데이터가 있으면 UPDATE 없으면 INSERT 처리를

 제어할 수 있는 DUPLICATE ON KEY UPDATE 구문이

어찌 보면 오라클의 MERGE INTO 구문과 비슷하다고 느껴진다.

해당 구문에 대해 알아보자

기존 INSERT INTO 구문에 뒷부분에 추가해서 써주기만하면 된다.

성능면에서도 괜찮은 구문이라는 말이 많다.

예)

간단하게 테이블 하나를 만들어놓고 시작해보자

위의 쿼리를 그대로 실행하면

이러한 결과가 나오며

해당 쿼리를 다시한번 실행하게 되면

이런식으로 값이 변한것을 확인이 가능하다.

추가적으로 실험은 해보지 않았지만

key 필드 값 역시 뒤에 UPDATE 구문에 써줄수 있다.

개요

MySQL 이 8로 업그레이드되면서 패스워드 정책이 더 깐깐해졌습니다.

DBMS 이니 보안에 더 충실해진 면도 있고 어떻게 보면 예전 버전들이 너무 보안에 신경 안 쓴 부분도 꽤 있다고 생각합니다.

운영 시스템이라면 이런 철저한 암호 정책을 따라야 겠지만 로컬이나 개발 환경에서는 이런 정책을 다 따르기 어려운 부분이 있습니다.

변경된 암호 정책

5.7 이전까지 버전은 암호 정책을 낮추려면 다음 설정을 mysql 설정 파일에 추가하고 재구동하면 됐습니다.

validate_password_policy=LOW
default_password_lifetime=0

8 에서는 저 옵션이 안 먹어서 SHOW VARIABLES LIKE 'validate_password% 로 확인해 보니 아래와 같이 validate_password 뒤에 _ 문자가 . 으로 변경이 되었습니다. 즉 validate_password_policy 는 validate_password.policy 로 바뀌줘야 합니다.

아마 설정을 더 체계적으로 관리하기 위해 도입한 방법인 것 같은데 default_password_lifetime 는 그대로 입니다.

mysql> SHOW VARIABLES LIKE 'validate_password.%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+

8.0 에서 qwert123 같은 암호를 사용하려면 다음 내용을 mysql 설정 파일에 넣어주고 재구동하면 됩니다.

echo 'validate_password.policy=LOW' | sudo tee -a /etc/my.cnf
echo 'default_password_lifetime=0' | sudo tee -a /etc/my.cnf

echo 'validate_password.policy=LOW' | sudo tee -a /etc/mysql/mysql.conf.d/mysqld.cnf
echo 'default_password_lifetime=0' | sudo tee -a /etc/mysql/mysql.conf.d/mysqld.cnf

추가로 암호 자릿수 제한, 특수 문자 포함 여부, 대소문자 구분, 숫자 포함 여부를 모두 끄려면 아래 내용을 추가해 주고 재구동해주면 됩니다.

echo 'validate_password.length=6' | sudo tee -a /etc/my.cnf
echo 'validate_password.special_char_count=0' | sudo tee -a /etc/my.cnf
echo 'validate_password.mixed_case_count=0' | sudo tee -a /etc/my.cnf
echo 'validate_password.number_count=0' | sudo tee -a /etc/my.cnf

echo 'validate_password.length=6' | sudo tee -a /etc/mysql/mysql.conf.d/mysqld.cnf
echo 'validate_password.special_char_count=0' | sudo tee -a /etc/mysql/mysql.conf.d/mysqld.cnf
echo 'validate_password.mixed_case_count=0' | sudo tee -a /etc/mysql/mysql.conf.d/mysqld.cnf
echo 'validate_password.number_count=0' | sudo tee -a /etc/mysql/mysql.conf.d/mysqld.cnf

update tableName set

    field1 = concat(if(isnull(field1), '', concat(field1, '\n')), '추가되는 내용')

    where uid = 1

​

* ALL

 - full table scan

 - MySQL 이 행을 찾기 위해 테이블의 처음부터 끝까지 스캔해야 한다는 것을 의미한다.

​

* index

 - index 순서로 스캔하므로 정렬할 필요가 없다는 장점이 있다.

 - 가장 큰 단점은 전체 테이블을 index 순서로 읽어들이는 비용에 있고 이는 임의의 순서로 행에 접근하는 데에 큰 비용이 필요하다는 의미이다.

 - Extra 컬럼에 "Using index" 가 출력된다면 MySQL 이 커버링 인덱스를 사용하게 되고, index 순서로 각 행을 읽는 것이 아니라 index 에 있는 데이터만을 스캔한다는 것을 의미하며 테이블을 index 순서로 스캔하는 것에 비해 비용이 적에 든다.

​

* range

 - index 의 특정 부분에서 시작해서 특정 범위에 있는 값을 가지는 행을 반환한다.

 - 전체 index 스캔보다 더 나은 성능을 보인다.

​

* ref

 - 어떤 값 하나에 대해 매치되는 행들을 반환해 주는 index 접근 방식이다.

​

* eq_ref

 - MySQL 이 기껏해야 값 하나만을 반환해준다는 것을 알고 있을 때 이런 index 탐색법이 사용되며, MySQL 이 쿼리를 실행하기 위해 참조 값을 기본 키 혹은 unique index 에 비교할 때 이런 접근 방법을 사용하는 것을 확인할 수 있다.

​

* const, system

 - 쿼리의 일부를 상수로 대체해서, 쿼리를 최적화할 수 있을 경우 MySQL 에서는 이런 접근 방법을 사용하게 된다.

 - WHERE 절에 기본 키를 이용해 검색하는 조건을 사용하는 경우 MySQL 에서는 쿼리를 상수로 바꿔버린 뒤 조인에서 테이블을 사실상 제거해 버린다.

​

* NULL

 - MySQL 이 최적화 단계에서 쿼리를 처리할 수 있으므로 실행 단계에서 index 나 테이블에 접근조차하지 않는다는 의미이다.

 - index 가 되어 있는 컬럼에서 최소값을 선택하려는 경우라면 index 만 찾아보면 되므로 실행 단계에서 테이블에 접근할 필요가 없다.

출처 : https://blog.naver.com/websearch/221566114706

-w 옵션 사용 : 조건 입력

mysqldump -u [아이디] -p [DB명] [테이블명] -w "seq_id<1000000 order by seq_id" > backup_01.sql

mysqldump -u [아이디] -p [DB명] [테이블명] -w "seq_id>=1000000 order by seq_id" > backup_02.sql

복구시

첫번째 파일 제외하고 아래 관련 구문 삭제 후 저장

DROP TABLE IF EXISTS `[테이블]`;

CREATE TABLE `[테이블]`

.

.

.

[복구]

mysql -u [아이디] -p [DB명] < backup_01.sql

mysql -u [아이디] -p [DB명] < backup_02.sql

http://www.joshi.co.kr/index.php?mid=board_iuyq53&document_srl=305356 

​최근 IT 비용 절감으로 인하여, 오픈 소스 소프트웨어 적용 비율이 높아지고 있는데, DB 쪽도 예외가 아닙니다. 

비용이 그다지 문제가 되지 않는다면, Oracle Exadata를 구매해서 사용해보면 국내 웬만한 서비스들은 다 커버가능하지 않을까

하는 희망찬 꿈에 빠져보기도 하지만, 서비스 운영 시 비용은 반드시 고려해야할 난관입니다.
IT 비용을 절감하기 위해서, 물론 장비 도입 시기부터 결정하는 방법도 있겠지만,

기존 장비를 오픈소스 소프트웨어(Oracle to MySQL)로 데이터 이관해야하는 경우도 있습니다.

오늘 제가 말씀드리고 싶은 사항은 기존 Oracle로 구현되어 있던 솔루션을 MySQL로 포팅한 사례이며, MySQL로 서비스 후

나날이 성능 저하 현상이 발생한 원인과 개선 방안에 관한 내용입니다.

아무래도 솔루션인지라, 쉽게 Application을 변경하기 어려운 상황이고, 기존 구조를 거의 그대로 유지해야 하므로,

기존 SQL튜닝과는 다른 방향으로 문제를 해결하였습니다.

성능 저하 요소 분석

통계 분석 시간이 시간이 지날수록 크게 소요되는 현상과 데이터 저장을 위한 디스크 사용 효율이 시간이 지남에 따라

나날이 떨어지는 현상이 발생하였습니다.

디스크는 사용량은 300G를 데이터 영역으로 사용 중이었고, 일부 테이블에서는 인덱스 파일 사이즈가 데이터 파일 사이즈보다

큰 현상도 존재하였습니다.

다음은 개선 전 테이블이 사용하는 디스크 실제 크기입니다.

InnoDB특성을 고려하여 통계 DB의 성능 저하 원인을 다음과 같이 분석 하였습니다.

• InnoDB Cluster Index (Primary Key) 로 인한 디스크 I/O
• InnoDB Secondary Index 구조 특성에 따른 인덱스 사이즈 거대화
• 디스크 공간이 재사용되지 않아서 발생하는 저장 공간 낭비
• 250G 한 개 파일로 inndb file이 생성되어 있어서 관리가 어려움
• 이 외 Replication 관련 버그 Fix되기 버전 사용으로 인한 안정성 이슈

DB 성능 최적화 적용

DBMS 성능 최적화 활동InnoDB 특성을 고려하여 다음과 같이 최적화 활동을 진행하였습니다.

1. 통계 쿼리 실행 최적화를 위해 Monthly Partitioning을 Daily Partitioning으로 변경
   Primary Key 순으로 데이터가 순차 저장되지 않는 구조이기 때문에 DISK I/O가 크게 발생하였고, 기존 테이블 구조를 변경하기는 불가했습니다. Partitioning 관리를 월별에서 일별로 변경함으로써 기존 발생하던 DISK I/O이슈를 최소화하였습니다.자동 파티셔닝 관리를 위해 별도 Shell Script 작성
2. Primary Key 및 Secondary Key 재구성
   일별 Partitioning 적용된 로그성 테이블에는 Primary Key를 제거 및 날짜 관련 인덱스 제거하여, 디스크 공간 사용 최소화
   (통계 처리 시 1일 단위로 처리되므로, 인덱스가 굳이 불필요함)
3. 로그 성 테이블 외 대용량 테이블은 Hash Partitioning을 활용하여 물리적으로 분리
   로그 성 테이블을 제외한 테이블(일자 별 관리가 어려운 테이블)은 Primary Key 값을 기준으로 Hash Partitioning을 적용하여 물리적으로 분리하여 InnoDB Primary Key 유지를 위한 DISK I/O 발생 이슈를 최소화하였습니다.
4. 한 개 파일로 관리되고 있는 innodb file을 innodb_file_per_table 옵션을 적용
   autoextend 옵션으로 innodb file유지 시 저장 공간 반환이 되지 않기 때문에 재사용되지 않습니다. autoextend 옵션 없이 innodb file 유지 시 추후 innodb tablespace 공간 확장 시 DB를 shutdown후 작업을 해야하는 이슈가 발생하기 때문에 innodb_file_per_table 옵션을 활용하여 테이블 별로 저장 공간을 할당하였습니다.
5. Archive Storage 엔진 사용하여 과거 데이터 백업 관리
   45일 이전 데이터는 다른 Database에 Archive Storage Engine 테이블에 백업 후 해당 일자 파티션 삭제하여 디스크 비효율을 제거하였습니다. Archive Storage Engine을 사용하게 되면 InnoDB 대비 1/5 정도 용량을 차지고, 인덱스를 제거하여 백업 데이터 유지한 결과 기존 대비 1/10 정도 Disk 공간으로 과거 데이터를 보관할 수 있습니다.
6. MySQL 5.1.57 으로 버전 Upgrade하여 Replication Fail Error 방지

다음은 파티셔닝 관리를 위해 나름 작성한 shell 스크립트입니다. shell 스크립트에 아직 익숙하지가 않은지라, 마음에 확 와닿게 짜지는 못했네요..^^;;

DB성능 최적화 결과

결과에 관한 요약은 다음과 같습니다. 그래프는 MySQL Enterprise Monitoring 에서 추출하였고, 동일 날짜 통계 추출 데이터 실행

결과입니다.
개선 전/후 모두 6시간에 관한 모니터링 결과를 뽑아낸 것으로 가시적으로 실행시간이 단축된 것을 확인할 수 있습니다.
아직 SQL튜닝은 진행하지 않았기 때문에, 통계 TOP SQL 일부를 튜닝한다면 적어도 60분 이내로 가능하지 않을까 생각이 드네요.^^

테이블 사이즈 순 상위 15개 테이블 저장 공간 비교

MySQL 5 이상 (MariaDB) UTF-8, MyISAM 엔진

1900-01-01 ~ 2200-12-31 까지 데이터

https://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=download&wr_id=16893

상기 데이터 참고후 수정한 자료

-- num : 등록순번

-- lunar_date : 음력일자 (0000-00-00 형식)

-- solar_date : 양력일자 (0000-00-00 형식)

-- yun : 윤달여부 (0 : 평달, 1 :윤달)

-- ganji : 간지

-- memo : 공휴일 (연휴적용)

--

-- 공휴일 구분 (2013년 ~ 기준)

-- 신정

-- 설날

-- 3·1절

-- 어린이날

-- 석가탄신일

-- 제헌절 ? 국경일이지만 법정공휴일 제외

-- 광복절

-- 추석

-- 개천절

-- 한글날

-- 성탄절

*MySQL 쓰면서 하지 말아야 할 것 17가지*

권장사항이다. 이것을 이해하면 당신의 어플리케이션이 더 나은 성능을 발휘할 것이다.

다만 이것이 사람의 실력을 판단하는 척도로 사용되서는 안 될 것이다.

작게 생각하기

- 조만간 규모가 커질거라면 MySQL ecosystem을 봐야된다.

- 그리고 캐싱 빡시게 안 하는 메이저 웹사이트는 없다.

- develooper.com의 Hansen PT랑 Ilia 튜토리얼 볼 것

- 처음부터 확장 가능하게 아키텍처 잘 쪼개놔야된다.

- 복제랑 파티셔닝 어떻게 할지 미리 계획 세워놔라.

- 파일 기반 세션 좀 쓰지마 -_-

- 그렇다고 너무 쓸데없이 크게 생각하지도 말 것

- 특히 성능하고 확장성 구분 못 하면 난감함

EXPLAIN 안 써보기

- SELECT 앞에 EXPLAIN 이라고 붙이기만 하면 되는 것을 (..)

- 실행 계획 확인

- 타입 컬럼에 index 써있는거랑 Extra 컬럼에 index 써있는거랑 “매우 큰” 차이 있음

* 타입에 있으면 Full 인덱스 스캔 (안 좋다.)

* Extra 컬럼에 있으면 Covering 인덱스 찾았다는 의미임 (좋다!)

- 5.0 이후부터는 index_merge 최적화도 한다.

잘못된 데이터 타입 선택

- 한 메모리 블럭 단위에 인덱스 레코드가 많이 들어갈수록 쿼리가 빨리 실행될 것이다. (중요)

- 아.. 정규화 좀 해 -_-… (이거 정말 충격과 공포인 듯)

- 가장 작은 데이터 타입을 써.. (진짜 BIGINT가 필요하냐고..)

- 인덱스 걸리는 필드는 정말 최소한으로 데이터 크기를 써야된다고.

- IP는 INT UNSIGNED로 저장해!! (아주 공감)

* 이럴 때 쓰라고 INET_ATON 함수가 아예 내장되어 있음.

PHP에서 pconnect 쓰는 짓

- 아파치에서 좀비 프로세스라도 생기면 그 커넥션은 그냥 증발하는거야..

- 어차피 MySQL 접속 속도는 Oracle이나 PostgreSQL 보다 10~100배 빠르다고.

너무 과도한 DB 추상화 계층을 두는 것

- 어디 포팅 열심히 할 거 아니면 추상화 계층 쓰지마 (ADODB, MDB2, PearDB 등)

- scale out 가능한걸 쓰라고.

스토리지 엔진 이해 못 하는 것

- 단일 엔진만으로 전체 아키텍처를 결정했다면 대부분 최적이 아님

- 엔진 별 장단점을 공부할 것

- ARCHIVE : zlib으로 압축해주고 UPDATE 안 되고 로그 Bulk Insert에 유용함.

- MEMORY : 서버 재시작하면 증발. 인덱스가 HASH나 BTREE로 가능함. 임시, 요약 데이터에 사용.

* 주간 top X 테이블 같은 것.

* 하여튼 메모리에 박아넣고 싶은 데이터 있으면..

인덱스 레이아웃 이해 못 하는 것

- 제대로 인덱스랑 스토리지 엔진 선택하려면 공부 좀 해

- 엔진은 데이터와 인덱스 레코드를 메모리나 디스크에 레이아웃하는 걸 구현한 것

- clustered 구성은 데이터를 PK 순서에 따라 저장함.

- non-clustered 구성은 인덱스만 순서대로 저장하고 데이터는 순서 가정하지 않음.

- clustered에서는 인덱스만 타면 추가적인 조회 없이 바로 데이터 가져오는 것임.

- 그래서 clustered PK는 작은 놈으로 할 필요가 있다는거

* 다른 인덱스는 각 레코드마다 PK를 앞에 더 붙이게 되니까.

* PK 지정 안 하면 아무렇게나 해버림

쿼리 캐시 이해 못 하는 것

- 어플리케이션 read/write 비율은 알고 있어야지

- 쿼리 캐시 설계는 CPU 사용과 읽기 성능 간의 타협

- 쿼리 캐시 크기를 늘린다고 읽기 성능이 좋아지는게 아님. heavy read라도 마찬가지.

- 과도한 CPU 사용을 막기 위해 무효화 할 때는 캐시 항목들을 뭉텅이로 날려버림

- 한마디로 SELECT가 참조하는 테이블 데이터 하나라도 변경되면 그 테이블 캐시는 다 날라간다는 얘기임

- 수직 테이블 파티셔닝으로 처방

* Product와 ProductCount를 쪼갠다든지..

* 자주 변하는 것과 변하지 않는 것을 쪼개는게 중요하다 이 말임.

Stored Procedure를 쓰는 것

- 무조건 쓰면 안 된다는게 아니고..

- 컴파일 할 때 무슨 일이 일어나는지 이해 못 하고 쓰면 재앙이 된다 이 말.

- 다른 RDBMS랑 다르게 connection thread에서 실행 계획이 세워짐.

- 이게 뭔 얘기냐 하면 데이터 한 번 가져오고 연결 끊으면 그냥 CPU 낭비 (7~8% 정도)하는 꼴이라는 것.

- 웬만하면 Prepared 구문과 Dynamic SQL을 써라.. 아래 경우를 제외하고

* ETL 타입 프로시저

* 아주아주 복잡하지만 자주 실행되지는 않는 것

* 한 번 요청할 때마다 여러번 실행되는 간단한 것 (연결한 상태로 여러번 써야 된다니까)

인덱스 컬럼에 함수 쓰는 것

- 함수에 인덱스 컬럼 넣어 호출하면 당연히 인덱스 못 탄다

- 함수를 먼저 계산해서 상수로 만든 다음에 = 로 연결해야 인덱스 탈 수 있다.

* 여기 실행 계획 보면 LIKE도 range type 인덱스 타는 것 보임

인덱스 빼먹거나 쓸모없는 인덱스 만들어 놓는 것

- 인덱스 분포도(selectivity)가 허접하면 안 쓴다.

- S = d/n

* d = 서로 다른 값의 수 (# of distinct values)

* n = 테이블의 전체 레코드 수

- 쓸모없는 인덱스는 INSERT/UPDATE/DELETE를 느리게 할 뿐..

- FK는 무조건 인덱스 걸어라. (물론 FK 제약 걸면 인덱스 자동으로 생긴다.)

- WHERE나 GROUP BY 표현식에서 쓰이는 컬럼은 인덱스 추가를 고려할 것

- covering index 사용을 고려할 것

- 인덱스 컬럼 순서에 유의할 것!

join 안 쓰는 짓

- 서브쿼리는 join으로 재작성해라

- 커서 제거해라

- 좋은 Mysql 성능을 내려면 기본

- 집합 기반으로 생각해야지 루프 돌리는거 생각하면 안 된다.

Deep Scan 고려하지 않는 것

- 검색엔진 크러울러가 쓸고 지나갈 수 있다.

- 이 경우 계속해서 전체 집합을 정렬한 다음 LIMIT로 가져와야 하니 무진장 느려진다.

- 어떻게든 집합을 작게 줄인 다음 거기서 LIMIT 걸어 가져올 것

InnoDB 테이블에서 WHERE 조건절 없이 SELECT COUNT(*) 하는 짓

- InnoDB 테이블에서는 조건절 없이 COUNT(*) 하는게 느리다.

- 각 레코드의 transaction isolation을 유지하는 MVCC 구현이 복잡해서 그렇다는..

- 트리거 걸어서 메모리 스토리지 엔진 쓰는 테이블에 통계를 별도로 유지하면 된다.

프로파일링이나 벤치마킹 안 하는 것

- 프로파일링 : 병목 찾아내기

- 벤치마킹 : 시간에 따른 성능 변화 추이 평가, 부하 견딜 수 있는지 테스트

- 프로파일링 할 때는 실제 데이터를 옮겨와서 할 것

- 어디가 병목이냐~ Memory? Disk I/O? CPU? Network I/O? OS?

- 느린 쿼리 로그로 남기기

* log_slow_queries=/path/to/log

* log_queries_not_using_indexes

- 벤치마킹 시에는 다 고정시키고 변수 하나만 바꿔가면서 해야 함. (쿼리 캐시는 끌 것.)

- 도구를 써라~~

* EXPLAIN

* SHOW PROFILE

* MyTop/innotop

* mysqlslap

* MyBench

* ApacheBench (ab)

* super-smack

* SysBench

* JMeter/Ant

* Slow Query Log

AUTO_INCREMENT 안 쓰는 것

- PK를 AUTO_INCREMENT로 쓰는건 무진장 최적화 되어 있음

* 고속 병행 INSERT 가능

* 잠금 안 걸리고 읽으면서 계속 할 수 있다는!

- 새 레코드를 근처에 놓음으로써 디스크와 페이지 단편화를 줄임

- 메모리와 디스크에 핫 스팟을 생성하고 스와핑을 줄임

ON DUPLICATE KEY UPDATE를 안 쓰는 것

- 레코드가 있으면 업데이트하고 없으면 인서트하고 이런 코드 필요없다!! 다 날려버려라!!

- 서버에 불필요하게 왔다갔다 할 필요가 없어짐

- 5-6% 정도 빠름

- 데이터 입력이 많다면 더 커질 수 있음

하지 말아야 할 것 총정리

Thinking too small

Not using EXPLAIN

Choosing the wrong data types

Using persistent connections in PHP

Using a heavy DB abstraction layer

Not understanding storage engines

Not understanding index layouts

Not understanding how the query cache works

Using stored procedures improperly

Operating on an indexed column with a function

Having missing or useless indexes

Not being a join-fu master

Not accounting for deep scans

Doing SELECT COUNT(*) without WHERE on an InnoDB table

Not profiling or benchmarking

Not using AUTO_INCREMENT

Not using ON DUPLICATE KEY UPDATEK

mysql에서 order by desc를 사용하면 성능이 급격히 낮아지는 경우가 있는데,

이유는 index를 역순으로 저장하는 방식을 mysql은 지원하지 않기 때문이다.

그래서 (key1 desc, key2 asc)와 같은 결합인덱스를 생성할 때 문제가 발생할 수 있다.

문제 해결책 :

이런 문제를 해결하는 방법은 정렬 column 값에 -1을 곱해서 table에 넣어버려서 추후 order by asc로 해결하는 패턴을 많이 사용한다.

심화문제 :

하지만 

desc 정렬 column이 auto_increment로 생성되는 값이라면 조금 더 복잡해진다.

심화문제 해결책 1 :

매우 제한적인 상황에서 통할 수 있는 엽기적인 방법인데 session variable 중 auto_increment_increment의 값을 -1로 해버리면 해결되는 경우가 있다.

심화문제 해결책 2 :

last_insert_id()를 사용하는 방법으로 -1*(last_insert_id()+1) 으로 auto_decrement를 구현한다.

https://blog.naver.com/lipaz/100047320100

[출처] mysql index의 order by desc 문제|작성자 천재민

Ubuntu 16.04 

MariaDB 10.0.36

사용자권한, 방화벽 등등 이상이 없는데도 원격접속이 되지 않을때

Connect Error: Can't connect to MySQL server on 'domain.com' (111)

vi /etc/mysql/mariadb.conf.d/50-server.cnf 파일을 열어서

#bind-address = 127.0.0.1 를 주석처리 또는  

bind-address = 0.0.0.0 으로 설정하고 mysql 재시작

mysql 에서 위와 같은 에러발생시 해결 방법

unsigned 에러의 경우

unsigned 타입의 연산시 결과값이 unsigned 타입이 아닐 경우 발생한다

예른 들어 unsigned 타입의 A 필드 값이 100인 경우

select (A - 200) from table

위 쿼리는 마이너스값이 리턴되어 에러를 발생하게 된다

[해결책]

select (CONVERT(A, SIGNED) - 200) from table 

또는

select (CAST(A as SIGNED) - 200) from table

 트랜젝션 기능을 가진 테이블(InnoDB, BDB) 중의 하나임

 BEGIN/COMMIT/ROLLBACK

 InnoDB 형식의 테이블을 만들 때
    1) CREATE TABLE... TYPE=INNODB 문을 사용하여
    2) MyISAM 형식의 테이블을 InnoDB 형식의 테이블로 변경
       ALTER TABLE  ... TYPE=INNODB 문을 사용
       빈 InnoDB 테이블을 만들어 Insert into ... select * from ...문을 사용

--------------------------------------------------------------------------------

InnoDB에서 트랜젝션의 COMMIT, ROLLBACK을 사용하게 된다.
InnoDB는 row level로 lock할 수 있고, SELECT 문에서 읽을 수 있도록 non-lock할 수도 있다.
 이렇게 함으로써 다중사용자의 동시 사용과 퍼포먼스를 증가시키게 된다.
 이는 InnoDB의 단계적 lock이 요구되지 않기 때문에 InnoDB의 row level lock이 아주 적은 공간에 적합하다.
InnoDB는 FOREIGN KEY 제약을 지원한다.
InnoDB는 대용량의 데이터 처리에서 가장 좋은 퍼포먼스를 위하여 설계되었다.
InnoDB는 데이터와 인덱스를 캐시하기 위한 자체적인 buffer pool로 주 메모리를 사용한다.
InnoDB는 테이블과 인덱스를 여러 파일로 구성되기도 하는 tablespace에 저장한다.
  이는 MyISAM 테이블이 각각의 파일로 저장하는 것과 다르다.
InnoDB는 2GB의 파일크기까지 가능하지만 운영체제에 따라 다를 수 있다.
InnoDB에 관한 정보는 http://www.innodb.com에서 제공하므로 참고하면 좋다.
InnoDB는 MySQL 4.0부터 디폴트로 사용할 수 있도록 허용된다.
 그러므로  InnoDB를 허용하기 위해서 어떤 부분을 지정할 필요가 없다.
MySQL의 datadir에 'ibdata1'라는 10MB의 auto-extending 파일을 디폴트로 만들게 된다
InnoDB를 사용하지 않으려면 MySQL 옵션으로 skip-innodb를 부가하면 된다.
InnoDB가 시작되면 innodb_data_file_path 라인에 auto-extending을 추가되어 다음과 같은 형식이 된다.

   pathtodatafile:sizespecification;pathtodatafile:sizespecification;...
   ;pathtodatafile:sizespecification[:auto-extend[:max:sizespecification]]

이처럼 auto-extend 옵션이 데이터파일에 추가되면, InnoDB는 데이터파일을 확장하여
tablespace의 여유공간을 탕진하게 되는데 한번에 8MB씩 증가되어 다음의 예처럼 된다.

 innodb_data_home_dir=
 innodb_data_file_path=/idbata/ibdata1:100M:autoextend

위 예시는 InnoDB에게 하나의 데이터파일을 100MB크기로 만들며 공간이 소진될 때마다
8MB씩 확장하도록 한 예이다.
점점 확장되어 디스크가 모두 차면 다른 디스크를 데이터 파일로 추가해야 한다.
먼저 'ibdata1'의 크기를 보고서 그 크기가 1MB의 배수에 가장 근접하면
 innodb_data_file_path에 'ibdata1'의 값으로 그 크기를 지정해야 한다.
 그런  다음에 다른 데이터 파일을 다음과 같이 추가할 수 있다:

innodb_data_home_dir=
innodb_data_file_path=/idbata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

파일시스템의 최대 파일크기가 2GB인지 확인해 보라,
 InnoDB는 OS의 최대 파일크기를 인지하지 않는다.
 그런 시스템에서는 다음과 같이 데이터파일의 최대 크기를 지정할 수 있다.

innodb_data_home_dir=
innodb_data_file_path=/idbata/ibdata1:100M:autoextend:max:2000M
       
'my.cnf'의 예
128MB RAM과 하나의 하드디스크로 된 컴퓨터라고 가정한다.
 다음은 InnoDB를 위한 'my.cnf'의(윈도우 버전에서는 'my.ini') 구성 예이다.
 InnoDB와 여러 디스크에 대한 log 파일로서,
   데이터 파일 'ibdata1'과
   두 개의 InnoDB log 파일인 'ib_logfile0'과 'ib_logfile1'가 /mysql/data라는 datadir에 있는 경우이다.
 또한 datadir에 InnoDB log 파일인 ib_arch_log_0000000000가 존재한다.

    [mysqld]
    # Uncomment the following if you are using InnoDB tables
    innodb_data_file_path = ibdata1:10M:autoextend
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    set-variable = innodb_buffer_pool_size=70M
    set-variable = innodb_additional_mem_pool_size=10M
    # Set .._log_file_size to 25 % of buffer pool size
    set-variable = innodb_log_file_size=20M
    set-variable = innodb_log_buffer_size=8M
    innodb_flush_log_at_trx_commit=1

mysqld가 구동되면서 읽는 구성 파일은 다음과 같다.
• '/etc/my.cnf' : Global 옵션
• 'COMPILATION_DATADIR/my.cnf' : Server-specific 옵션
• 'defaults-extra-file' : --default-extra-file=...로 지정한 파일
• '~/.my.cnf' : User-specific 옵션

여기서 'COMPILATION_DATADIR' 이란 MySQL의 데이터 디렉토리로 mysqld가 컴파일 할 때
./configure의 옵션으로 지정한 곳으로 source로 인스톨하는 경우에는 '/usr/local/var'이
지만, 본서에서는 /export/home/mysql/var이다..
mysqld --default-file=your_path_to_my_cnf처럼 mysqld가 구동될 때 읽을 my.cnf를 지정
해도 된다.
InnoDB의 데이터 파일에 대한 경로와 디렉토리 지정은 innodb_data_home_dir으로 데이터
파일 디렉토리를 지정하고, innodb_data_file_path로 경로를 지정한다. 만약
innodb_data_home_dir을 지정하지 않을 경우의 디폴트는 MySQL의 datadir을 의미하는 ./ 이 된다.

다음 my.cnf의 예는 2GB RAM과 60GB 디스크의 디렉토리 경로는 '/',  '/dr2', '/dr3'인
경우의 예이다.

    [mysqld]
    # Uncomment the following if you are using InnoDB tables
    innodb_data_home_dir = /export/home/mysql/var/
    innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
    innodb_log_group_home_dir = /export/home/mysql/var/
    innodb_log_arch_dir = /export/home/mysql/var/
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    set-variable = innodb_buffer_pool_size=1G
    set-variable = innodb_additional_mem_pool_size=20M
    innodb_log_group_home_dir = /dr3/iblogs
    innodb_log_arch_dir = /dr3/iblogs
    set-variable = innodb_log_files_in_group=3
    # Set .._log_file_size to 25 % of buffer pool size
    set-variable = innodb_log_file_size=150M
    set-variable = innodb_log_buffer_size=8M
    innodb_flush_log_at_trx_commit=1
    set-variable = innodb_lock_wait_timeout=50
 
모든 데이터 파일을 동일 디스크에 놓지 않고 서로 다른 디스크에 각각 위치시키는 것 즉,
데이터가 있는 디스크와 다른 디스크에 log 파일을 위치시키는 것이 퍼포먼스상 유리하다.

mysqld의 다른 변수 튜닝
    skip-locking
    set-variable    = max_connections=200
    set-variable    = read_buffer_size=1M
    set-variable    = sort_buffer=1M
    set-variable    = key_buffer=16M
 
my.cnf의 numeric 변수 설정의 형식은 set-variable  = innodb....=123 형식이고, string과
boolean 변수 설정의 형식은 innodb_...=.... 형식이다.
다음은 변수의 의미이다.

innodb_data_home_dir
        InnoDB의 데이터파일의 디렉토리. 'my.cnf'에 지정하지  않으면 MySQL의 datadir
        가 디폴트임, empty string(빈 공간)인 경우에는 innodb_data_file_path의 파일경로
        를 사용

innodb_data_file_path
        각  데이터파일의  경로와   그 크기.   각  데이터파일에   대한  절대   경로로
        innodb_data_home_dir가 사용할 파일의  크기는 MB이며  'M'으로 표시한다.  또한
        'G'는 GB를 의미한다.

innodb_mirrored_log_groups
        복사될 log 그룹의 수로 디폴트는 1임

innodb_log_group_home_dir
        InnoDB log 파일의 디렉토리 경로

innodb_log_files_in_group
        log 그룹에 존재할 log 파일의 수로 3을 추천함

innodb_log_file_size
        log 그룹에 존재할 log 파일의 크기로 MB이다. 1M에서 buffer spool의 1/nth까지의
        범위이며 여기서 n은 그룹내 log 파일의 수이다.

innodb_log_buffer_size
        InnoDB가 log 파일에 기록할 log의 버퍼의 크기로 1M∼8M 범위이다.

innodb_flush_log_at_trx_commit
        디폴트로 1이며, transaction commit에서 log가 디스크에 flush됨
        0이면, log가 log 파일에 기록되며 1초마다 log 파일이 flush됨
        2이면, log가 commit 때마다 log 파일에 기록되며 1초마다 log 파일이 flush됨

innodb_log_arch_dir
        log 파일이 문서화(archive)될 디렉토리로 innodb_log_group_home_dir과 같다.

innodb_log_archive
        디폴트로 0이며,   백업으로부터 복구할  때  log  파일을 사용하므로   현재까지는
        InnoDB log 파일을 archive할 필요가 없다.

innodb_buffer_pool_size
        데이터와 인덱스를 캐시하기 위해 InnoDB가 쓸 메모리 버퍼의 크기로 실제 메모리
        의 80%까지 사용할 수 있다.

innodb_buffer_pool_awe_mem_mb
        buffer   pool의  MB   크기로  32비트   윈도우에서  AWE(address   windowing
        extensions)를 사용하 수 있게 하며 최대값은 64000이다.

innodb_additional_mem_pool_size
        InnoDB가 데이터 dictionary 정보나 다른 내부 데이터 구조를 저장하기  사용할 메
        모리 pool의 크기로 디폴트는 2M이다.

innodb_file_io_threads
        InnoDB에서 파일 I/O 스레드의 수로 보통 4이다.

innodb_lock_wait_timeout
        rollback전에 deadlock까지 기다리는 timeout 시간(초)

innodb_flush_method
        디폴트로 fdatasync이며, 다른 옵션은 0_DSYNC이다.

1) InnoDB tablespace 만들기
2) InnoDB table 만들기
3) InnoDB의 Foreign Key Constraints
4) InnoDB에 대한 Data 파일와 Log 파일의 추가와 제거
5) InnoDB database의 백업과 복구
6) InnoDB database를 다른 운영체제 기계로 이동하기
6) InnoDB 트랜젝션 모델
7) 다중-버전 기능
8) 테이블과 인덱스 구조
9) 파일 공간 관리와 디스크 I/O
10) 오류 처리
11) InnoDB 테이블의 제약조건

--------------------------------------------------------------------------------

【예제】 
mysql> CREATE TABLE customer (a INT, b CHAR(20), INDEX (a)
    -> ) TYPE=INNODB;
mysql> SHOW TABLE STATUS;
+-----------+--------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+------------------------+
| Name      | Type   | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment |  Create_time        | Update_time         |  Check_time         | Create_options |  Comment               |
+-----------+--------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+------------------------+
| customer  | InnoDB | Fixed      |     0 |              0 |       16384 | NULL            |        16384 |         0 |           NULL | NULL                | NULL                | NULL                |                | InnoDB free: 381952kB  |
+-----------+--------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+------------------------+
 
【예제】 
mysql> CREATE TABLE test3 ( a int(11) default NULL) TYPE=MyISAM;
mysql> SHOW TABLE STATUS;
mysql> ALTER TABLE test3 type=innodb;
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW TABLE STATUS;

【예제】
mysql> create table test2 ( a int)type=innodb;
mysql> set autocommit=0;
mysql> insert into test2 values(10);
mysql> select * from test2;
+------+
| a    |
+------+
|   10 |
+------+
mysql> commit;
mysql> update test2 set a=20;
mysql> select * from test2;
+------+
| a    |
+------+
|   20 |
+------+
mysql> rollback;
mysql> select * from test2;
+------+
| a    |
+------+
|   10 |
+------+
mysql>
 
 
현재의 autocommit 상태를 확인하는 방법은 다음 예제와 같다.
【예제】
mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.03 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.01 sec)

mysql>

mysql 대용량처리 (페이지가 없어서 구글에 저장된 페이지를 잽싸게 가져옴 ㅇㅇ )

개요
일반적으로 MYSQL에서 자료가 많아지면 LIMIT절을 이용하여 자료를 분할하여 가져오는 방식으로 쓰인다. 
여기서 발생하는 문제점으로, LIMIT는 첫번째 값만큼 자료를 '무시'하고 직접 가져오는게 아니라 실제로 모든 값을 읽되 가져올 필요가 없는 부분은 '스킵'해가는 방식이기 때문에 거대한 자료의 끝에 위치한 부분을 조회하려 하는 경우 심각한 부하가 일어날 수 있다.
ex)note_list라는 테이블에 로우가 1억개가 있고, 9999만9990의 자료부터 10개를 가져온다고 칠 경우 쿼리는 아래 형태가 된다.
SELECT * FROM note_list LIMIT 99999990, 10
보통은 저 쿼리에서 앞에 수만큼 건너뛰고 뒤에 수만큼 가져오는 것이라고 생각하기 쉽지만, 사실은 전부 읽되 앞에 수만큼을 스킵해버리고 뒤에 수만큼 가져오기 때문에 결과적으로는 1억개의 자료를 모두 읽는 것과 다를 바가 없게 된다.

해결책
해결책은 여러가지 방법이 존재한다.
아래는 그 방법 중 시도해본 세가지이다.
(1) 로우의 조회 범위를 구하여 해당 범위만큼 PK를 BETWEEN 연산으로 가져오기
인덱스를 타기 때문에 자료가 많아도 상당히 빠른 조회가 가능하다.
ex) SELECT * FROM note_list WHERE no BETWEEN 10001 AND 10010
여기서 no는 note_list의 PK자 Auto Increment 값이다.
단, PK가 무조건 순차적으로 저장이 되어있어야 가능하며 만약 중간에 비어버리는 값이 
생기는 경우 자료를 올바르게 가져올 것이란 보장을 할 수 없다.
ex) 위의 쿼리를 기반으로, 만약 no가 10001인 로우와 no가 10007인 로우의 자료가 없다면
의도된 쿼리대로라면 총 10개의 자료가 조회되어야 하지만 8개가 조회되어 버린다.
이 단점을 보완하려면 총 자료가 원하는 갯수가 될 때 까지 초기 BETWEEN 범위에서 계속 
늘려가면서 조회 쿼리를 반복해서 날리던지, 비어있는 값들을 구하여 BETWEEN의 범위를 
보정해야 한다.
(2) LIMIT를 사용하되, 조회 범위를 제한하여 가져오기
LIMIT를 사용하면 (1)에서처럼 중간에 비는 값이 있어도 갯수만큼 가져오기 때문에 (1)에서
의 문제는 해결할 수 있다.
다만 조회 범위를 제한하는 방법이 마땅치가 않다는 문제점이 있다.
(1)과 (2) 모두 로우의 순차정보를 담고 있는 컬럼이 존재한다면 무리없이 구현 가능하다.(ORACLE로 치면 ROWNUM과 비슷한 요소의 컬럼)
다만 특정 로우가 삭제될 경우, 해당 로우 위에 존재하는 모든 로우의 순차정보값을 갱신해야한다는 문제점이 존재한다.
ex) 로우가 100만개 들어있는 테이블에서 맨 처음 들어간 자료(순차정보가 1인 자료)를 삭제한다면, 남은 99만9999개의 순차정보를 모두 -1 시켜버려야하므로 과부하가 발생한다

(3) 로우를 특정 사이즈만큼 묶고, 묶은 값을 구분할 수 있는 컬럼과 테이블을 추가하여 관리
하는 방법
(2)의 방법에서 파생되었으며 실제로 조회 범위를 제한할 수 있는 방법으로, 기존에 있는 
테이블에 별개의 구분값 컬럼을 줘서 해당 구분값으로 조회 범위를 제한하는 방법이다.
이후의 설명은 (3)을 사용한다는 조건 하의 설명이다.


조회범위의 제한 
해당 컬럼의 이름은 segment 이고 해당 segment의 정보를 담은 note_list_게시판ID_segment 라는 테이블이 추가로 존재한다.

로우를 묶는다?
자료가 1억개 있다고 치자.
해당 1억개의 자료에서 LIMIT를 하는 것은 DB에 큰 부하를 준다고 위에서 설명했지만, 조회 범위가 정해진 상태에서 LIMIT를 하는 것은 그다지 부하를 주지 않는다.
즉, 1억개의 자료를 1만개씩 분할하고 해당 분할된 값을 구분할 수 있는 컬럼을 넣은 뒤 쿼리를 다음처럼 변경한다면 자료가 아무리 많아도 조회 범위가 정해져 있기 때문에 부하가 적다.
SELECT * FROM note_list WHERE segment = 7 LIMIT 9990, 10

세그먼트의 정보를 담는 테이블의 구조
CREATE TABLE `note_list_silver_segment` (
`no` int(11) NOT NULL AUTO_INCREMENT, -- 세그먼트 번호
`count` int(5) NOT NULL, --해당 세그먼트의 값을 갖는 게시물의 갯수
PRIMARY KEY (`no`)
) 

고려해야 할 점
(1) 세그먼트와 세그먼트끼리 걸치는 경우가 있기 때문에 해당 부분을 계산해 한다.
특정 테이블의 가장 최신 세그먼트의 값이 5이며 세그먼트 5의 게시물 갯수가 5개 있고, 
세그먼트 4의 게시물 갯수가 1만개 있을 경우 자료의 조회 범위는 세그먼트 5개의 게시글 
5개 + 세그먼트 4의 게시글 5개가 되어야 한다.
(2) 게시물 갯수의 변동이 있을 때마다 세그먼트 테이블의 count값도 같이 변경해줘야 함


구현 순서
(1) 현재 조회해야 하는 페이지를 기준으로 조회 범위를 구해야 한다.
조회 범위는 페이지-1한 값에 조회갯수를 곱한 값을 전체 게시물 갯수에서 뺀 값으로
구할 수 있다.
ex)전체 갯수가 34245개이고 조회하려는 페이지가 11페이지이고 조회갯수는 10일 경우, 페이지-1한 값은 10이며 여기에 10을 곱하면 100이 되고 이 값을 14245에서 빼면 실제 조회 범위는 34145가 된다.
(2) 조회 범위를 구했다면 해당 조회 범위가 어떤 세그먼트에 속하는지 구해야 한다.
세그먼트의 목록을 불러온 다음, 세그먼트의 count를 처음부터 더해가면서 만약 더해지는 
그 값이 조회 범위보다 커지거나 같아진다면 해당 세그먼트가 현재 페이지의 세그먼트이
다.
ex) 아래와 같이 자료가 존재할 경우
no count
------------------------------------
4 4148
3 9998
2 10000
1 9999
조회 페이지가 11이라면 조회 범위는 34145가 된다.
아래서부터 count를 더해간 뒤 조회 범위와 비교를 한다.
9999 >= 34145 -> FALSE
19999 >= 34145 -> FALSE
29997 >= 34145 -> FALSE
34245 >= 34145 -> TRUE
이로써 조회 범위 34145의 소속 세그먼트는 4라는 것을 알 수 있다.

(3) 세그먼트 내에서의 조회 범위를 구해야 함
현재 조회해야 하는 세그먼트 내에서 LIMIT 해야 하는 범위를 구해야한다.
공식은 다음과 같다.
현재 세그먼트의 게시물 합 - (현재 세그먼트 이전에 있는 세그먼트의 게시물 갯수 합 - (현재 조회 페이지 * 조회갯수)) - 조회갯수
ex)자료 형태가 위와 같다면
-> 4148 - (4148 - (11 * 10)) - 10
-> 4148 - (4148 - 110) - 10
-> 4148 - 4038 - 10 
-> 100
그러므로 쿼리는
SELECT * FROM note_list WHERE segment IN (4, 3) ORDER BY no DESC LIMIT 100, 10
이 된다.
※ 맨 처음 세그먼트 조회가 아닌 경우는 위에서 설명했던 값이 걸쳐있는 경우가 있기 때문에 현재 세그먼트와 이전 세그먼트 모두 조회대상에 포함시켜야 한다.(고려해야할 점 1 참고)

답글 문제
위에서의 모든 전제는 답글이라는 요소를 배제하고 작성되었다. 그래서 답글 처리를 생각해봐야한다.
답글 처리에는 크게 두가지 방식이 있을 수 있다.
(1) 실제 테이블에 답글 대상이 되는 게시물 밑에 끼워넣게 배치하기
이 경우 테이블 내의 로우 배치가 달라짐으로써 이런 저런 문제점을 야기할 수 있다.
(2) 일반 게시물처럼 쓰이되 답글과 답글이 아닌 글을 구분지을 수 있게 하기
별도의 컬럼이 필요하다.
여기서는 (2)의 경우로 구현을 한다고 치겠다.

답글의 구현
답글과 답글이 아닌 글의 구분을 위해 2개의 컬럼을 추가했다.
(1) parent_no : 답글의 대상이 되는 게시글의 고유번호를 담는다. 답글이 아닌 글의 경우는 이 
값이 0으로 들어간다.
(2) depth : 답글의 깊이를 나타낸다. 보통 답글은 1이 설정되며 답글의 답글은 2, 답글의 답글
의 답글은 3 이런 식으로 증가하게 된다.
답글은 세그먼트 정보 테이블에 count 정보에 포함시키지 않는데, 페이징을 용이하게 하기 위함이다.

답글을 게시글 조회 결과에 더하기
(1) 위에서 정의된대로 조회하려는 페이지의 게시글을 가져온다.
(2) 가져온 게시글 목록의 no를 parent_no로 갖는 글들을 가져온다.
(3) 루프를 돌며 답글을 게시글에 알맞게 끼워맞춰준다.

FULLTEXT 검색 방식

• 자연어 검색
  검색 문자열을 단어 단위로 분리한 후, 해당 단어 중 하나라도 포함되는 행을 찾는다.
• 불린 모드 검색
  검색 문자열을 단어 단위로 분리한 후, 해당 단어가 포함되는 행을 찾는 규칙을 추가적으로 사용할 수 있다.
• 쿼리 확장 검색
  2단계에 걸쳐서 검색을 수행한다. 첫 단계에서는 자연어 검색을 수행한 후, 첫 번쨰 검색의 결과에 매칭된 행을 기반으로 검색 문자열을 재구성하여 두 번째 검색을 수행한다. 이는 1단계 검색에서 사용한 단어와 연관성이 있는 단어가 1단계 검색에 매칭된 결과에 나타난다는 가정을 전제로 한다.

[참고자료]

• [MYSQL/MS-SQL] FULL TEXT SEARCH 간단히 정리
• DBMS] MySQL FULLTEXT을 이용한 형태소 분석없는 한글검색 방법
• FULLTEXT 인덱스와 서치

1. LENGTH() : 길이를 bytes로 나타낸다. 

SELECT LENGTH('한글') -> 6
SELECT LENGTH('abc') -> 3

2. CHAR_LENGTH() : 글자의 수를 나타낸다. 

SELECT CHAR_LENGTH('한글') -> 2
SELECT CHAR_LENGTH('abc') -> 3

3. BIT_LENGTH() : 길이를 bit로 나타낸다. 

SELECT BIT_LENGTH('한글a') -> 56
SELECT BIT_LENGTH('한글')   -> 48