RSS구독하기:SUBSCRIBE TO RSS FEED
즐겨찾기추가:ADD FAVORITE
글쓰기:POST
관리자:ADMINISTRATOR
출처 블로그 > Greate Teacher Onizuka
원본 http://blog.naver.com/semi7623/100005610936

쿼리 결과를 파일로 저장하기 위해서는 into outfile를 사용한다.
파일을 절대경로로 지정 가능하고 data폴더 아래 데이타베이스 폴더아래 생성된다.


mysql>select * into outfile 'filename' from table_name;

2006/09/08 11:03 2006/09/08 11:03
이 글에는 트랙백을 보낼 수 없습니다
출처 블로그 > loves0508님의 블로그
원본 http://blog.naver.com/loves0508/2941304
MySQL이 지원하는 컬럼타입
이 글은 MySQL사이트에서 제공하는 매뉴얼 중에서 컬럼관련 부분을 기초로 번역 및 정리한 내용입니다.
더욱 자세하고 내용을 원하시면 mysql의 document를 참고하십시요.

자 그럼 시작하겠습니다.



1. Column types
MySQL은 numeric types, date and time types, 그리고 string (character) types의 총 세가지 타입을 지원합니다.

아래에는 MySQL이 지원하는 컬럼 타입을 적었는데 바로 밑의 것은 컬럼타입을 설명하는데 사용한 문자에 대한 설명입니다.


M
최대 표시 크기를 나타냅니다. 최대크기는 255입니다.
D
부동소주점 타입에 적용되는 것으로 소수점아래의 자릿수를 나타냅니다.
ZEROFILL
입력한 값의 자릿수에 여유가 있으면 그 부분을 모두 0으로 채웁니다.
UNSIGNED
0과 양수만 사용합니다.
Square brackets (`[' 과 `]')은 옵션으로 특별한 사항을 나타내는데 사용합니다.

A. NUMERIC TYPES
주의! : 만일 컬럼에 ZEROFILL를 명기하면, MySQL은 자동적으로 UNSIGNED 특성을 컬럼에 적용합니다.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
매우 작은 정수. 사용범위 : -128 to 127. unsigned로 정의하면 0 to 255.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
매우 작은 정수. 사용범위 : -32768 to 32767. unsigned로 정의하면 0 to 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
중간 크기의 정수. 사용범위 : -8388608 to 8388607. unsigned로 정의하면 0 to 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
보통 크기의 정수. 사용범위 : -2147483648 to 2147483647. unsigned로 정의하면 0 to 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
INT와 같음.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
큰 정수. 사용범위 : -9223372036854775808 to 9223372036854775807. unsigned로 정의하면 0 to 18446744073709551615.
Note!
모든 계산은 부호가 있는 BIGINT 또는 DOUBLE 타입의 값으로 이루어집니다.
그러므로 bit 함수를 제외한 9223372036854775807보다 큰 정수는 사용하지 마십시오.

연산 대상 둘 다 INTEGER값이면, -,+와 *는 BIGINT 연산을 사용할 것입니다.
이 의미는 만일 두 개의 BIGINT를 당신이 곱할 경우 (또는 함수의 계산 결과가 정수를 반환할 경우),
그 결과가 9223372036854775807보다 클 경우에는 원하지 않는 결과를 얻게 된다는 것입니다.
FLOAT(precision) [ZEROFILL]
부동소수점 수(일반적으로 말하는 소수). unsigned는 사용할 수 없습니다.
precision(정밀도)은 4 또는 8만 사용가능합니다.
FLOAT(4)은 single-precision의 수를 나타내고,
FLOAT(8)은 double-precision의 수를 나타냅니다.
이런 타입은 타입과 바로 밑에서 기술하는 FLOAT와 DOUBLE 타입과 같습니다.
FLOAT(4)와 FLOAT(8) 은 FLOAT 과 DOUBLE 타입과 같습니다.
그라나 소수부분의 표시 크기나 수는 정의되지 않습니다.
MySQL 3.23에서, 이것은 true floating point value입니다.
이보다 이전 버전에서는, FLOAT(precision)은 항상 2 decimals이었습니다.
이 구문은 ODBC 호환성을 위해 미리 제공되는 것입니다.
FLOAT[(M,D)] [ZEROFILL]
매우 작은(single-precision) 부동소수점 수.
unsigned 값은 사용할 수 없습니다.
사용범위 : -3.402823466E+38부터 -1.175494351E-38까지와 1.175494351E-38부터 3.402823466E+38까지, 그리고 0.
DOUBLE[(M,D)] [ZEROFILL]
보통 크기의(double-precision) 부동소수점 수.
unsigned 사용할 수 없음.
사용 범위 : -1.7976931348623157E+308부터 -2.2250738585072014E-308까지와 2.2250738585072014E-308부터 1.7976931348623157E+308까지 그리고 0.
DOUBLE PRECISION[(M,D)] [ZEROFILL]
REAL[(M,D)] [ZEROFILL]
이것은 DOUBLE과 동일합니다.
DECIMAL(M,D) [ZEROFILL]
unpacked 소동소수점 수.
unsigned은 사용할 수 없음.
CHAR 컬럼 같이 행동합니다.
``unpacked'' 은 수를 문자처럼 저장한다는 의미입니다.
소수점, 음의 부호(-) 등을 포함하여 수 한자리를 저장하는데 각각 digit의 값을 사용하여 저장합니다.
만일 D가 0이면 소수점 또는 소수부분이 없는 값으로 지정하는 것입니다.
최대 사용 범위는 DOUBLE과 같습니다만, 실제적인 사용범위는 M 과 D의 선택 사항에 의해 결정됩니다.
MySQL 3.23에서 M은 부호나 소수점을 포함하지 않는다. (이것은 ANSI SQL에 따른 것이다.)
NUMERIC(M,D) [ZEROFILL]
DECIMAL과 같다.

B. DATE and TIME TYPES
DATE
날짜. 지원 범위는 '1000-01-01'부터 '9999-12-31'까지입니다.
MySQL 은 'YYYY-MM-DD' 형식으로 값을 표시하지만,
string 또는 number 모두를 사용하여 DATE 컬럼에 값을 할당할 수 있다.
이 내용은 date and time 타입에 거의 모두 적용되므로 앞으로는 기술하지 않습니다.
DATETIME
date와 time을 조합한 타입읻아.
이 타입은 '1000-01-01 00:00:00'부터 '9999-12-31 23:59:59'까지를 사용할 수 있다.
MySQL은 'YYYY-MM-DD HH:MM:SS' 형식으로 나타냅니다.
TIMESTAMP[(M)]
타임스탬프(편지.문서의 발송.접수. 날짜.시간을 기록).
이 범위는 '1970-01-01 00:00:00' 로부터 때때로 2037년까지 사용된다.
MySQL은 TIMESTAMP값으로 YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD 또는 YYMMDD 형식으로 나타낸다.
M이 값이 14, 12, 8 또는 6인지에 따라 달라진다.
이 컬럼타입도 마찬가지로 string과 number를 이용하여 값을 할당할 수 있다.
시간과 날짜를 insert, update하는데 유용하다.

TIME
시간.
지원 범위는 '-838:59:59' to '838:59:59'.
MySQL은 'HH:MM:SS'형식으로 time을 나타낸다.
YEAR
연도.
1901년부터 2155까지 그리고 0000년을 사용할 수 있다.
MySQL은 YEAR컬럼에 YYYY 형식으로 나타낸다.
YEAR 타입은 MySQL 3.22.에서 새로 지원하는 타입이다.


C. STRING TYPES
CHAR(M) [BINARY]
길이가 정해진 문자열.
저장할 때 길이보다 문자열의 길이가 작으면 그 길이 만큼 오른쪽에 공백문자(SPACE)를 더하여 저장한다.
값이 retrieve될 때는 덧붙혀진 공백은 제거된다.
CHAR값은 BINARY 키워드로 지정되지 않는다면 case-insensitive(대소문자 구분없이) 형태로 저장되고 비교된다.

VARCHAR(M) [BINARY]
가변길이의 문자열.
뒤에 덧붙어있는 공백문자는 저장될 때 제거된다. (이것은 ANSI SQL specification과 다르다).
M은 1부터 255문자까지 사용가능하다.
VARCHAR 값도 BINARY키워드로 설정되지 않으면 case-insensitive 형태로 저장되고 비교된다.

TINYBLOB
최대 길이가 255 (2^8 - 1)인 BLOB 컬럼.
BLOB는 그림이나 바이너리 파일 등을 저장하는데 사용된다.
TINYTEXT
최대 길이가 255 (2^8 - 1)인 TEXT 컬럼.
BLOB
최대 길이가 65535 (2^16 - 1)인 BLOB 컬럼.

TEXT
최대 길이가 65535 (2^16 - 1)인 TEXT 컬럼.

MEDIUMBLOB
최대 길이가 16777215 (2^24 - 1)인 BLOB 컬럼.

MEDIUMTEXT
최대 길이가 16777215 (2^24 - 1)인 TEXT 컬럼.
LONGBLOB
최대 길이가 4294967295 (2^32 - 1)인 BLOB 컬럼.
LONGTEXT
최대 길이가 4294967295 (2^32 - 1)인 TEXT 컬럼.
ENUM('value1','value2',...)
목록 컬럼.
목록에서 선택된 하나의 값 또는 NULL만을 가질 수 있는 문자열 객체이다.
ENUM은 구성요소의 총 문자열 길이가 최대 65535까지의 서로 다른 값만을 사용할 수 있다.
예를 들어 test란 테이블에 test란 컬럼명으로 ENUM("one", "two", "three")을 설정한 컬럼이 있다면 다음과 같이 각각에 인덱스가 붙는다. :

Value
Index

NULL
NULL

""
0

"one"
1

"two"
2

"three"
3


커리에서 where절을 사용하여 one을 선택한 경우를 select하려면 다음과 같이 한다.
mysql> select * from test where test=1;
SET('value1','value2',...)
A set.
zero 또는 리스트에서 선택한 각각의 값을 가질 수 있는 문자열 객체.
최대 64개의 member를 가질 수 있다.
예를 들어 SET("one", "two") NOT NULL 과 같이 정의된 컬럼이 있다면 다음과 같은 값을 목록에 가지고 있다.:
""
"one"
"two"
"one,two"

MySQL은 수의 형태로 값(bit값)을 저장한다.
만약 SET("a","b","c","d")으로 정의한 컬럼이 있다면 다음과 같은 bit값을 가진다. SET member
Decimal value
Binary value

a
1
0001

b
2
0010

c
4
0100

d
8
1000


만일 9 즉, 바이너리로 1001인 값을 저장했다면, "a" 와 "d"를 선택한 것으로 결과 값은 "a,d"이다.
이런 형식으로 저장하기에 "a,d"나 "d,a" 또는 "d,a,a,d,d" 등은 모두 "a,d"로 저장되고 보여진다.
NULL값은 non-NULL값으로 셋팅된 것보다 전에 sort된다.
보통, SELECT 또는 FIND_IN_SET() 함수를 set으로 정의한 컬럼에 like 연산자를 이용하여 사용한다.
다음과 같은 경우를 예로 들 수 있다.
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;


그러나 이런 경우는 다음과 같이 실행된다. :
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
mysql> SELECT * FROM tbl_name WHERE set_col & 1;

위 문장들 중 첫 번째는 확실히 일치하는 값을 찾는다.
그리고 두 번째는 첫 번째 멤버를 포함한 값을 찾는다.



2.Column type의 요구()사항
아래의 A,B,C는 각 category별로 컬럼 타입마다 저장에 필요한 공간(크기)을 적은 것입니다.

A. Numeric types
Column type
Storage required

TINYINT
1 byte

SMALLINT
2 bytes

MEDIUMINT
3 bytes

INT
4 bytes

INTEGER
4 bytes

BIGINT
8 bytes

FLOAT(4)
4 bytes

FLOAT(8)
8 bytes

FLOAT
4 bytes

DOUBLE
8 bytes

DOUBLE PRECISION
8 bytes

REAL
8 bytes

DECIMAL(M,D)
M bytes (D+2, if M < D)

NUMERIC(M,D)
M bytes (D+2, if M < D)




B. Date and time types
Column type
Storage required

DATETIME
8 bytes

DATE
3 bytes

TIMESTAMP
4 bytes

TIME
3 bytes

YEAR
1 byte




C. String types
Column type
Storage required

CHAR(M)
M bytes, 1 <= M <= 255

VARCHAR(M)
L+1 bytes, where L <= M and 1 <= M <= 255

TINYBLOB, TINYTEXT
L+1 bytes, where L < 2^8

BLOB, TEXT
L+2 bytes, where L < 2^16

MEDIUMBLOB, MEDIUMTEXT
L+3 bytes, where L < 2^24

LONGBLOB, LONGTEXT
L+4 bytes, where L < 2^32

ENUM('value1','value2',...)
1 or 2 bytes, 목록 값의 개수에 달려 있다.
(최대 길이 65535)

SET('value1','value2',...)
1, 2, 3, 4 or 8 bytes, 셋 멤버의 개수에 달려있다.
(최대 64개의 멤버)


VARCHAR과 BLOB and TEXT types은 가변길이를 가진다.
그러므로 저장공간은 저장하는 값의 실제 길이에 따라 정해진다.
BLOB and TEXT types은 컬럼에 값을 저장하기 위해 1, 2, 3 또는 4 바이트가 필요하다.



3. Column indexes
MySQL은 BLOB and TEXT type을 제외한 모든 column types을 색인할(indexed) 수 있습니다.
컬럼에 관계된 인덱스의 사용은 select 연산에서의 속도를 개선하는데 최선의 방법이고,
하나의 테이블은 16개의 인덱스를 사용할 수 있습니다.
인덱스의 최대길이는 256바이트이고, 이것은 MySQL을 컴파일할 때 변경할 수 있습니다.
그러나 인덱스에는 null을 사용할 수 없습니다. 그러므로 인덱스로 사용할 컬럼은 NOT NULL로 선언되어야 합니다.

char나 varchar컬럼을 인덱스할 때 전부가 아닌 일부분을 할 수도 있습니다.
이것은 하드의 공간이나 검색속도에서 좋습니다.
다음과 같이 키(인덱스키)를 정의하면 됩니다.

KEY index_name (col_name(length))
예로 name컬럼의 첫 10글자만 인덱스로 지정하여 테이블을 만드는 것을 보이면 다음과 같습니다. :

mysql> CREATE TABLE test (
name CHAR(200) NOT NULL,
KEY index_name (name(10)));
MySQL이 지원하는 컬럼타입과 인덱스에 대해 알아보았습니다.
2006/09/08 11:02 2006/09/08 11:02
이 글에는 트랙백을 보낼 수 없습니다
출처 블로그 > loves0508님의 블로그
원본 http://blog.naver.com/loves0508/2941553
mysql의 최대 성능 향상 방법

10.1 버퍼 크기 조정
mysqld 서버가 사용하는 기본 버퍼 크기는 다음의 명령으로 알 수 있다.

shell> mysqld --help

이 명령은 모든 mysqld 옵션의 목록과 설정 변수를 보여준다. 출력되는 내용은 기본값을
포함하고 있으며 다음과 비슷하다.

Possible variables for option --set-variable (-O) are:
back_log current value: 5
connect_timeout current value: 5
join_buffer current value: 131072
key_buffer current value: 1048540
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_connections current value: 90
max_connect_errors current value: 10
max_join_size current value: 4294967295
max_sort_length current value: 1024
net_buffer_length current value: 16384
record_buffer current value: 131072
sort_buffer current value: 2097116
table_cache current value: 64
tmp_table_size current value: 1048576
thread_stack current value: 131072
wait_timeout current value: 28800

mysqld 서버가 현재 가동중이면 다음의 명령을 통해 실제 변수값을 볼 수 있다.

shell> mysqladmin variables

각 옵션은 밑에서 설명한다. 버퍼 크기, 길이, 스택 크기는 바이트이다. 'K'(킬로바이트)
나 'M'(메가바이트)를 앞에 붙여 값을 지정할 수 있다. 예를 들면 16M는 16 메가바이트를
가리킨다. 대소문자는 구별하지 않는다. 16M 와 16m은 같다.

-back_log
mysql이 가질 수 있는 최대 연결 요청의 수. 이것은 main mysql 스레드가 매우 짧은 시간
동안 매우 많은 연결 요청을 받을 때 기능을 한다. 이때 메인 스레드가 연결을 체크하고 새
로운 스레드를 시작하는데는 약간의 시간이 걸린다.(그러나 아주 짧은 시간임) back_log 값
은 mysql이 순간적으로 새로운 요청에 답하는 것을 멈추기전에 이 짧은 시간동안 얼마나
많은 요청을 쌓아두고 있는지를 지정한다. 매우 짧은 시간동안 매우 많은 연결이 예상될때
만 이 값을 증가시켜야 한다.

다른 말로 이 값은 tcp/ip 연결을 받는 listen queue의 크기이다. 각 운영체제마다 이러한 큐
의 크기에 한계가 있다. Unix system call listen(2) 매뉴얼페이지에 자세한 정보가 있다. ba
ck_log값의 한계는 운영체제 문서를 확인해봐라. back_log를 최대값보다 더 높여도 효과가
없다.

-connect_timeout
Bad handshake에 반응하기 전에 연결 패킷을 mysql 서버에서 기다리는 시간.(초)

-join_buffer
(인덱스를 사용하지 않는 조인의) full-join에서 사용하는 버퍼의 크기. 버퍼는 두 테이블 사
이에서 각 full-join마다 한번 할당이 된다. 인덱싱을 추가하지 못할 때 조인 버퍼를 증가시
키면 full join의 속도를 향상시킬 수 있다. (일반적으로 빠르게 조인을 하는 가장 좋은 방법
은인덱스를 추가하는 것이다)

-key_buffer
인덱스 블락은 버퍼링되고 모든 스레드에서 공유한다. 키 버퍼는 인덱스 블락에서 사용하는
버퍼의 크기이다. 인덱스가 많은 테이블에서 delete나 insert 작업을 많이 하면 키 버퍼값을
증가시키는 것이 좋다. 더 빠른 속도를 내려면 LOCK TABLES를 사용하자. [Lock Tables]
참고.

-max_allowed_packet
한 패킷의 최대 크기. 메시지 버퍼는 net_buffer_length 바이트로 초기화되지만 필요하면 최
대 허용 패킷 바이트를 증가시킬 수 있다.기본값은 큰 패킷을 잡기에는 작다. 거대 BLOB
컬럼을 사용한다면 값을 증가시켜야 한다. 사용자가 원하는 최대 blob만큼 크게 해야 한다.

-max_connections
동시 클라이언트 숫자. mysqld가 필요로하는 파일 지시자(descriptor)의 숫자만큼 값을 늘려
야 한다. 밑에서 파일 디스크립터 제한에 대한 내용을 참고하자.

-max_connect_errors
호스트에서 최대 연결 에러이상의 interrupted 연결이 있으면 더 많은 연결을 위해 호스트는
block화된다. FLUSH HOSTS 명령으로 호스트의 block을 해제할 수 있다.

-max_join_size
최대 조인 크기이상으로 레크도를 읽는 조인을 하면 에러가 난다. 만약 사용자가 where 문
을 사용하지 않고 시간이 많이 걸리면서 몇백만개의 레코드를 읽는 조인을 수행하려 하면
이 값을 설정한다.

-max_sort_length
BLOB나 TEXT 값으로 정열할때 사용하는 바이트의 숫자. (각 값중 오직 첫번째 max_sort
_length 바이트만 사용된다. 나머지는 무시된다)

-net_buffer_length
질의에서 통신 버퍼가 초기화되는 크기. 일반적으로 바뀌지 않지만 매우 적은 메모리를 가
지고 있을 때 예상되는 질의에 맞게 세팅할 수 있다. (이것은 클라이언트에 가는 예상된 sql
문의 길이이다. 질의문이 이 크기를 넘으면 버퍼는 자동으로 max_allowed_packet 바이트까
지 증가한다)

-record_buffer
순차적인 검색을 하는 각 스레드에서 각 검색 테이블에 할당하는 버퍼 크기. 순차적인 검색
을 많이 하면 이 값을 증가시켜야 한다.

-sort_buffer
정렬이 필요한 각 스레드에서 할당하는 버퍼 크기. order by 나 group by 오퍼레이션을 빠
르게 하려면 이 값을 증가시킨다. 16.4 [임시 파일] 참고.

-table_cache
모든 스레드에서 열 수 있는 테이블의 숫자. mysqld가 필요로 하는 파일 디스크립터의 숫
자만큼 이 값을 증가시켜라. mysql은 각 유일한 오픈 테이블에서 두개의 파일 디스크립터가
필요하다. 파일 디스크립터 제한을 참고한다. 테이블 캐쉬가 어떻게 작동하는지는 10.6 [테
이블 캐쉬]를 참고한다.

-tmp_table_size
임시 테이블이 이 값을 넘으면 mysql은 "The Table tbl_name is full"이라는 에러 메시지를
낸다. 매우 많은 group by 질의를 사용하면 이 값을 증가시켜야 한다.

-thread_stack
각 스레드의 스택 사이즈. creash-me test(**역자주 : 데이터베이스의 벤치마킹을 하는 테스
트입니다. 말그대로 데이터베이스를 죽여주지요) 에서 잡히는 많은 제한은 이 값에 달려있
다. 기본값은 일반적으로 충분히 크다. 11장의 [벤치마크] 참조

-wait_timeout
연결을 끊기전에 연결 활동(activity)을 서버에서 기다리는 시간(초).

table_cache 와 max_connections는 서버가 열 수 있는 최대 파일 갯수에 영향을 미친다. 이
값을 증가시키면 운영시스템에서 오픈 파일 디스크립터의 per-process 숫자의 한계까지 올
릴 수 있다. (** ... imposed by your operating system on the per-process number of
open file descriptors. 번역이 이상하므로 영문 참고)
그러나 많은 시스템에서 이 한계를 증가시킬수 있다. 이렇게 하려면 각 시스템에서 이 한계
를 변화시키는 방법이 매우 다양하므로 운영체제 문서를 참고해야 한다.

table_cache 는 max_connections 와 관계가 있다. 예를 들면 200개의 연결이 있으면 최소 2
00 * n 의 테이블 캐쉬를 가져야 한다. 여기서 n은 조인에서 테이블의 최대 숫자이다.

mysql은 매우 유용한 알고리즘을 사용하기 때문에 일반적으로는 매우 적은 메모리로 사용
할 수 있으며 메모리가 많을 수록 성능이 더 많이 향상된다.

많은 메모리와 많은 테이블을 가졌고 중간정도 숫자의클라이언트에서 최대의 성능을 원한다
면 다음과 같이 사용한다.

shell> safe_mysqld -O key_buffer=16M -O table_cache=128
-O sort_buffer=4M -O record_buffer=1M &

메모리가 적고 연결이 많으면 다음과 같이 사용한다.

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k
-O record_buffer=100k &

또는:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k
-O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

매우 많은 연결이 있을 때 mysqld가 각 연결마다 최소한의 메모리를 사용하도록 설정하지
않았다면 "swapping problems" 문제가 생길 것이다.

mysqld에서 옵션을 바꾸었으면 그것은 서버의 해당하는 인스턴스에만 영향을 미친다는 것
을 기억하자.

옵션을 바꾸었을때의 효과를 보기 위해 다음과 같이 해보자.

shell> mysqld -O key_buffer=32m --help

마지막에 --help 옵션이 들어간 것을 기억하자. 그렇지 않으면 커맨드 라인에서 사용한 옵
션의 효력은 출력에는 반영되지 않을 것이다.


10.2 메모리 사용 방법 <메모리 최적화>

아래에서 설명하는 목록은 mysqld 서버가 메모리를 사용하는 방법에 대해서 나타내고 있
다. 메모리 사용과 관련된 서버의 변수 이름이 주어진다.


- 키 버퍼(변수 key_buffer)는 모든 스레드에서 공유한다. 서버에서 사용하는 다른 버퍼는
필요한대로 할당이 된다.

- 각 연결은 각 스레드마다의 특정한 공간을 사용한다. 스택(64k, 변수 thread_stack) , 연결
버퍼(변수 net_buffer_length), result 버퍼 (변수 net_buffer_length) 등. 연결 버퍼와 result
버퍼는 필요할때 max_allowed_packet 까지 동적으로 증가된다. 질의가 수행될 때 현재의
질의문의 복사문이 또한 할당이 된다.
(** When a query is running a copy of the current query string is also alloced.)

- 모든 스레드는 같은 기본 메모리를 공유한다.
- 메모리 맵은 아직 지원이 안된다. (압축 테이블을 제외하고. 그러나 이것은 다른 이야기이
다) 왜냐하면 4GB의 32비트 메모리 공간은 대부분의 대형 테이블에서 충분히 크기가 않기
때문이다. 우리가 64비트 주소 공간을 가진 시스템을 가지게 될 때 우리는 메모리 맵핑을
위한 일반적인 지원을 추가할 것이다.

- 테이블에서 순차적인 검색을 하는 각 요청은 read 버퍼에 할당이 된다. (변수 record_buff
er)

- 모든 조인은 한번에 수행이 되며 대부분의 조인은 임시 테이블을 생성하지 않고 수행이
된다. 대부분의 테이블은 메모리 기반(HEAP) 테이블이다. 거대 길이의 레코드를 가졌거나
BLOB 컬럼을 포함한 임시 테이블은 디스크에 저장이 된다. 현재의 문제는 메모리 기반 테
이블이 tmp_table_size를 초과했을때 "The table tbl_name is full"이라는 에러가 생기는 것
이다. 가까운 시일안에 필요할때 자동적으로 메모리 기반(HEAP) 테이블을 디스크 기반(NI
SAM) 테이블로 바꾸도록 고칠 것이다.
이 문제를 해결하기 위해서 mysqld의 tmp_table_size 옵션을 설정하여 임시 테이블 크기를
늘이거나 클라이언트 프로그램에서 SQL_BIG_TABLES라는 sql 옵션을 설정하여야 한다. 7.
24 SET OPTION 을 참고하자.
mysql 3.20에서 임시 테이블의 최대 크기는 record_buffer*16이다. 3.20 버전을 사용하고 있
다면 record_buffer의 값을 증가시켜야 한다. 또한 mysqld를 시작할 때 --big-tables 옵션을
사용하여 항상 임시 테이블을 디스크에 저장할 수 있지만 질의 속도에 영향을 미친다.

- 정열을 하는 대부분의 요청은 정렬 버퍼와 하나나 두개의 임시 파일을 할당한다. 16.4의
[임시 파일]을 참고한다.

- 대부분의 파징(parsing)과 계산은 지역 메모리에서 이루어진다. 작은 아이템에는 메모리 o
verhead가 필요없고 일반적인 느린 메모리 할당(slow memory allocation)과 freeing(메모리
해제)는 무시된다. 메모리는 오직 예상지 못한 거대 문자열에서 할당이 된다.( mallloc() 과
free() 사용)

- 각 인덱스 파일은 한번에 열리며 각 병행수행되는 스레드에서 데이터 파일은 한번에 열
린다. 각 병행수행 스레드마다 테이블 구조, 각 컬럼의 컬럼 구조, 3 * n 의 버퍼 크기가 할
당된다. ( n은 최대 레코드 길이이며 BLOB 컬럼은 해당하지 않는다) BLOB는 BLOB 데이
터의 길이에 5에서 8 바이트를 더한 값을 사용한다.

- BLOB 컬럼을 가진 각 테이블에서 버퍼는 거대 BLOB 값을 읽을 수 있도록 동적으로 커
진다. 테이블을 검색하면 버퍼는 최대 BLOB의 값만큼 버퍼가 할당이 된다.

- 모든 사용중인 테이블의 테이블 핸들러는 캐쉬에 저장되며 FIFO로 관리가 된다. 일반적
으로 캐쉬는 64 엔트리를 갖는다. 동시에 두개의 실행 스레드에서 테이블을 사용하면 캐쉬
는 테이블의 두 엔트리를 포함한다. 10.6 [테이블 캐쉬]를 참고한다.

- mysqladmin flush-tables 명령은 사용하지 않는 모든 테이블을 닫고 현재 실행되는 스레
드가 끝날 때 모든 사용중인 테이블을 닫는다고 표시한다. 이것은 효과적으로 사용중인 메
모리를 해제한다.


ps 와 다른 시스템 상황 프로그램은 mysqld가 많은 메모리를 사용하고 있다고 보고할 것이
다. 이것은 다른 메모리 주소의 스레드-스택때문에 생긴다. 예를 들면 솔라리스의 ps 는 스
택사이의 사용하지 않는 메모리를 사용하는 메모리로 간주한다. 이것은 swap -s를 이용 사
용가능한 스왑을 체크하여 확인할수 있다. 우리는 mysqld를 상용 메모리 유출 측정 프로그
램으로 테스팅해서 mysqld에는 메모리 유출이 없다.


10.3 속도 향상에 영향을 미치는 컴파일/링크 방법 <컴파일시 최적화하기>

다음 테스트의 대부분은 리눅스와 mysql 벤치마크를 가지고 수행되었지만 다른 운영 시스
템에도 암시해주는 것이 있다.

static으로 링크를 할때 가장 빠른 실행 속도를 얻을 수 있다. 데이터베이스에 연결하기 위
해 TCP/IP보다는 유닉스 소켓을 사용하면 더 좋은 성능을 낼 수 있다.

리눅스에서 pgcc와 -O6을 사용하면 가장 빠르다. 'sql_yacc.cc'를 이 옵션으로 컴파일하려면
gcc/pgcc는 모든 성능을 내기 위해 많은 메모리가 필요하기 때문에 180M의 메모리가 필요
하다. 또한 mysql을 설정할때 libstdc++ 라이브러리를 포함하지 않기 위해 CXX=gcc라고 설
정해야 한다.

- pgcc를 사용하고 모두다 -O6 옵션으로 컴파일하면 mysqld 서버는 gcc로 컴파일한 것보
다 11% 빨라진다.

- 동적으로 링크하면 (-static을 사용하지 않고) 13% 느려진다.
If you connect using TCP/IP rather than Unix sockets, the result is 7.5% slower.
- 유닉스 소켓을 사용하는 것보다 tcp/ip로 연결하는 것이 7.5% 느려진다.

- On a Sun sparcstation 10, gcc 2.7.3 is 13% faster than Sun Pro C++ 4.2.
- On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads.
(** 번역을 안한 이후. 리눅스랑 상관없으니깐... **)

TcX에서 제공한 mysql 리눅스 배포판은 pgcc로 컴파일되었고 정적으로 링크되었다.


10.4 How MySQL uses indexes

prefix- and end-space compressed. See section 7.26 CREATE INDEX syntax (Compatibil
ity function).

모든 인덱스(PRIMARY, UNIQUE and INDEX()) 는 B-trees 에 저장된다. 문자열은 자동적
으로 앞 뒤의 공간(?)이 압축된다. 7.26 [인덱스 생성] 참고.

인덱스의 사용 :
- WHERE 문에서 해당하는 레코드 빨리 찾기
- 조인을 수행할때 다른 테이블에서 레코드 가져오기
- 특정 키에서 MAX() 나 MIN() 값 찾기
- 소팅이나 그룹화할때 인덱스 키를 사용하면 테이블을 정열하거나 그룹화한다. 키에 DES
C가 붙으면 역순으로 인덱스를 읽는다.
- 어떤 경우에는 데이터 파일에 묻지 않고 값을 가져온다. 어떤 테이블에서 사용하는 모든
컬럼이 숫자이고 특정 키로 형성되어있으면 빠른 속도로 인덱스 트리에서 값을 가져올 수
있다.

다음 예제를 보자.

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;


다중 컬럼 인덱스가 col1 과 col2에 있으면 해당하는 레코드를 직접 가져올 수 있다. 분리
된 단일 컬럼 인덱스가 col1 과 col2 에 있으면 최적화기는 어떤 인덱스가 더 적은 레코드
를 가졌는지 확인하고 레코드를 가져오기 위해 그 인덱스를 사용하도록 결정한다.

테이블이 다중 컬럼 인덱스를 가졌다면 최적화기가 레코드를 찾는데 어떤 인덱스키를 사용
할 수 있다. 예를 들면 세가지 컬럼 인덱스(col1, col2, col3)를 가졌다면 (col1), (col1,col2)
(col1,col2,col3) 인덱스를 사용하여 검색을 할 수 있다.

MySQL can't use a partial index if the columns don't form a leftmost prefix of the inde
x.
Suppose you have the SELECT statements shown below:
(** 해석이 잘 안되는데 예제를 보시면 무슨 말인지 알 수 있을 것임**)

mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If an index exists on (col1,col2,col3), only the first query shown above uses the index.
The second and third queries do involve indexed columns, but (col2) and (col2,col3) are
not leftmost prefixes of (col1,col2,col3).

인덱스가 (col1,col2,col3)로 있다면 위의 질의중 오직 첫번째 질의만 인덱스를 사용한다. 두
번째 및 세번째 질의은 인덱스된 컬럼이 포함되어 있지만 (col2) 와 (col2,col3)는 (col1,col2,c
ol3) 인덱스에 해당하지 않는다.

MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant
string that doesn't start with a wildcard character. For example, the following SELECT
stat ements use indexes:

mysql은 또한 LIKE의 인수가 와일드카드 문자로 시작하지 않는 상수 문자열일이라면 LIK
E 비교문에서 인덱스를 사용한다. 예를 들어 다음의 SELECT 문은 인덱스를 사용한다.

mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

첫번째 문장에서는 "Patrick" <= key_col < "Patricl" 을 가진 레코드만 고려된다. 두번째 문
장에서는 "Pat" <= key_col < "Pau" 을 가진 레코드만 고려된다.


다음의 SELECT 문은 인덱스를 사용하지 않는다:

mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;

첫번째 문장에서 LIKE 값은 와일드카드 문자로 시작하고 있다. 두번째 문장에서는 LIKE
값이 상수가 아니다.



10.5 WHERE 문에서 최적화하기
(이번 절은 완전한 내용을 포함하고 있지는 않다. mysql은 많은 최적화방법이 있다.)

In general, when you want to make a slow SELECT ... WHERE faster, the first thing t
o check is whether or not you can add an index. All references between different tables
should usually be done with indexes. You can use the EXPLAIN command to determine
which indexes are used for a SELECT. See section 7.21 EXPLAIN syntax (Get informat
ion about a SELECT).
일반적으로 느린 SELECT ... WHERE 문을 빠르게 하려면 가장 먼저 확인해야 할 것이 인
덱스 추가 문제이다. 다른 테이블사이에서 모든 레퍼런스(references 참조)는 일반적으로 인
덱스에 의해 수행된다. SELECT 문에서 어떤 인덱스를 사용하는지 결정하기 위해 EXPLAI
N 명령을 사용할 수 있다. 7.21 [Explain]을 참고.

mysql에서 수행하는 최적화는 다음과 같다.


- 불필요한 삽입어 제거

((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b ANDc) OR (a AND b AND c AND d)

-상수 폴딩(folding)

(a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

- 상수 조건 제거(상수 폴딩때문에 필요)

(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

- 인덱스에서 사용되는 상수 표현은 한번에 계산된다.
(Constant expressions used by indexes are evaluated only once.)

- WHERE 절이 없는 단일 테이블의 COUNT(*)는 테이블 정보에서 직접 값을 가져온다.
단일 테이블에서 사용된 NOT NULL 표현도 이와 같이 수행된다.

- 유효하지 않은 상수 표현은 미리 제거된다. mysql은 불가능하고 해당하는 레코드가 없는
SELECT 문을 빠르게 감지한다.

- GROUP BY 나 그룹 펑션(COUNT(), MIN() ...)을 사용하지 않으면 HAVING은 WHERE
에 합쳐진다.
(** HAVING 절에서는 인덱스를 사용하지 못함. 그러므로 가능한 HAVING절을 사용하지
않는게 속도면에서 좋다 **)

- 각 서브 조인에서 빠르게 WHERE 문을 계산하고 가능한한 레코드를 제외하도록 간소하
게 WHERE 문이 만들어진다.

- mysql은 일반적으로 최소한의 레코드를 찾기 위해 인덱스를 사용한다. =, >, >=, <, <=,
BETWEEN 그리고 'something%' 처럼 앞이 와일드카드로 시작하지 않는 LIKE 문등을
사용하여 비교를 할 때 인덱스를 사용한다. (** 10.4 절에서 설명하였듯이 like 를 사용할때
와일드카드로 시작하는 like 문을 사용하면 인덱스를 사용하지 않는다. 일정한 단어로만 시
작하는 컬럼에서 자료를 찾을 때 유용할 것이다. **)

- Any index that doesn't span all AND levels in the WHERE clause is not used to opti
mize the query.

다음의 WHERE 문은 인덱스를 사용한다.:

... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
/* optimized like "index_part1='hello'" */

다음의 WHERE 문은 인덱스를 사용하지 않는다.:

... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */
... WHERE index=1 OR A=10 /* No index */
... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */

- 질의에서 다른 테이블보다 모든 상수 테이블을 먼저 읽는다. 상수 테이블은 다음과 같다.
ㅇ빈 테이블이나 1개의 레코드만 있는 테이블
ㅇWHERE 문에서 UNIQUE 인덱스나 PRIMARY KEY 를 사용하고 모든 인덱스
는 상수 표현으로된 테이블

다음의 테이블은 상수 테이블로 사용된다.

mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

- 모든 가능성을 시도하여 테이블을 조인하는데 가장 좋은 조인 조합을 찾는다. (ORDER B
Y나 GROUP BY의 모든 컬럼이 동일한 테이블에서 나오면 조인을 할때 이 테이블이 먼저
선택된다)

- ORDER BY 문과 다른 GROUP BY 문이 있을 때, 또는 ORDER BY 나 GROUP BY가
조인 큐의 첫번째 테이블이 아닌 다른 테이블의 컬럼을 포함하고 있으면 임사 테이블을 만
든다.

- 각 테이블 인덱스를 찾고 레코드의 30%미만을 사용하는 (best) 인덱스가 사용된다. 그런
인덱스가 없으면 빠른 테이블 검색이 사용된다.

- 어떤 경우에는 mysql은 데이터 파일을 조회하지 않고 인덱스에서 레코드를 읽을 수 있
다. 인덱스에서 사용한 모든 컬럼이 숫자라면 질의를 처리하는데 단지 인덱스 트리만을 사
용한다.

- 각 레코드가 출력되기 전에 HAVING 절에 맞지 않는 레코드는 건너뛴다.

다음은 매우 빠른 질의의 예이다:

mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

다음의 커리는 인덱스 트리만을 사용하여 값을 구한다.(인덱스 컬럼은 숫자라고 가정):

mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name
WHERE key_part1=val1 and key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

다음의 질의는 개별적인 정열을 하지 않고 정열된 순서대로 열을 가져오는 데 인덱스를 사
용한다:

mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...
mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...


10.6 테이블 열고 닫는 방법

open 테이블의 캐쉬는 table_cache의 최대값까지 커질 수 있다. (기본값 64 ; 이 값은 mysql
d에서 -0 table_cache=# 으로 바꿀 수 있다) 캐쉬가 꽉 찼을때, 그리고 다른 스레드가 테이
블을 열려고 할 때, 또는 mysqladmin refresh 나 mysqladmin flush-tables를 사용할때를 제
외하고는 테이블은 결코 닫히지 않는다.

테이블 캐쉬가 꽉 차면 서버는 캐쉬 엔트리를 사용하도록 조절하기 위해 다음의 절차를 사
용한다.

- 가장 먼저 사용했던 순서대로 현재 사용하지 않는 테이블을 닫는다.
- 캐쉬가 꽉 찼고 어떤 테이블도 닫히지 않지만 새로운 테이블을 열어야 한다면 캐쉬가 필
요한 만큼 임시적으로 확장된다.
- 캐쉬가 임시적으로 확장된 상태이고 테이블을 사용할 수 없는 상황으로 가면 테이블을
닫고 캐쉬를 해제한다.

테이블은 각 동시병행적인 접근때마다 열린다. 동일한 테이블에 접근하는 두개의 스레드가
있거나 같은 질의에서 테이블에 두번 접근하면(with AS) 테이블을 두번 열여야 한다는 의
미이다. 테이블의 첫번째 개방은 두개의 파일 디스크립터를 가진다. ; 추가적인 테이블의 개
방은 하나의 파일 디스크립터를 가질 뿐이다. 처음에 개방에 사용하는 추가적은 파일 디스
크립터는 인덱스 파일에 사용된다. ; 이 디스크립터는 모든 스레드에서 공유된다.


10.6.1 데이터베이스에서 많은 수의 테이블을 만들때의 단점

디렉토리에 많은 파일이 있다면 open, close 그리고 create 오퍼레이션은 느려질 것이다. 서
로 다른 많은 테이블에서 SELECT 문을 수행하면 테이블 캐쉬가 꽉 찰 때 약간의 overhea
d가 있을 것이다. 왜냐면 개방된 테이블이 있다면 다른 테이블은 닫혀야 하기 때문이다. 테
이블 캐쉬를 크게 해서 이러한 오우버헤드를 줄일 수 있다.


10.7 많은 테이블을 여는 이유

mysqladmin status 를 실행할 때 다음과 같이 나올 것이다:

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

단지 6테이블을 사용했는데 이러한 결과는 당황스러울 것이다.

mysql은 멀티스레드를 사용한다. 그래서 동시에 같은 테이블에서 많은 질의를 할 수 있다.
같은 파일에 대하여 다른 상황을 가지는 두개의 스레드에 대한 문제를 줄이기 위해 테이블
은 각 동시병행적인 스레드마다 독립적으로 개방된다. 이것은 테이타 파일에서 약간의 메모
리와 하나의 추가적인 파일 디스크립터를 사용한다. 모든 스레드에서 인덱스 파일은 공유된
다.


10.8 데이터베이스와 테이블에서 심볼릭 링크 사용

데이터베이스 디렉토리에서 테이블과 데이터베이스를 다른 위치로 옮기고 새로운 위치로 심
볼릭 링크를 사용할 수 있다. 이렇게 하는 것을 원할 경우가 있다. 예를 들면 데이터베이스
를 더 여유공간이 많은 파일시스템으로 옮기는 경우 등.

mysql에서 테이블이 심볼링 링크되었다는 것을 감지하면 심볼링 링크가 가리키는 테이블을
대신 사용할 수 있다. realpath() call 을 지원하는 모든 시스템에서 작동한다. (최소한 리눅
스와 솔라리스는 realpath()를 지원한다) realpath()를 지원하지 않는 시스템에서 동시에 실
제 경로와 심볼릭 링크된 경로에 접근하면 안된다. 이런 경우에는 업데이트 된후에 테이블
이 모순될 수 있다.

mysql은 기본값으로 데이터베이스 링크를 지원하지 않는다. 데이터베이스간에 심볼릭 링크
를 사용하지 않는 작동을 잘 할 것이다. mysql 데이터 디렉토리에 db1 데이터베이스가 있
고 db1을 가리키는 db2 심볼릭 링크를 만들었다고 해보자:

shell> cd /path/to/datadir
shell> ln -s db1 db2

이제 db1에 tbl_a라는 테이블이 있다면 db2에도 tbl_a가 나타날 것이다. 한 스레드가 db1.tbl
_a를 업데이트하고 다른 스레드가 db2.tbl_a를 업데이트하면 문제가 생길 것이다.

정말로 이 기능이 필요하면 , `mysys/mf_format.c'에서 다음의 코드를 수정해야 한다.:

if (!lstat(to,&stat_buff)) /* Check if it's a symbolic link */
if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))

위 코드를 다음과 같이 수정한다 :

if (realpath(to,buff))


10.9 테이블에 락 거는 방법

mysql의 모든 락은 deadlock-free 이다. 언제나 질의를 시작할때 한번에 모든 필요한 락을
요청하고 언제나 같은 순서대로 테이블에 락을 걸어 관리한다.

WRITE 락을 사용하는 방법은 다음과 같다:

- 테이블에 락이 없으면 그 테이블에 write 락을 건다.
- 이런 경우가 아니라면 write 락 큐에 락을 요청한다.

READ 락을 사용하는 방법은 다음과 같다:

- 테이블에 write 락이 없으면 그 테이블에 read 락을 건다.
- 이런 경우가 아니라면 read 락 큐에 락을 요청한다.

락이 해제되었을 때 락은 write 락 큐의 스레드에서 사용할 수 있으며 그러고 나서 read 락
큐의 스레드에서 사용한다.

테이블에서 업데이트를 많이 하면 SELECT 문은 더 이상 업데이트가 없을 때까지 기다린
다는 것을 의미한다.

이러한 문제를 해결하기 위해 테이블에서 INSERT 와 SELECT 오퍼레이션을 많이 사용하
는 경우에 다음과 같이 하면 된다. 임시 테이블에 레코드를 입력하고 한번에 임시 테이블에
서 실제 테이블로 레코드를 업데이트한다.

다음의 예를 보자:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> insert into real_table select * from insert_table;
mysql> delete from insert_table;
mysql> UNLOCK TABLES;

만약 어떤 경우에 SELECT문에 우선권을 주고 싶다면 INSERT 옵션에서 LOW_PRIORITY
or HIGH_PRIORITY 옵션을 사용할 수 있다. 7.13 [Insert] 참고. (** LOW_PRIORITY를 지
정하면 클라이언트에서 테이블을 읽지 않을 때까지 INSERT 문 수행이 미루어진다. **)

단일 큐를 사용하기 위해 `mysys/thr_lock.c' 의 락킹 코드를 바꿀 수 있다. 이런 경우 writ
e 락과 read 락은 같은 우선권을 가지며 어떤 애플리케이션에서는 유용할 수 있다.

10.10 테이블을 빠르고 작게 배열하는 방법 <** 테이블 최적화 **>

다음은 테이블에서 최대의성능을 내는 방법과 저장 공간을 절약할 수 있는 테크닉이다:

- 가능한한 NOT NULL로 컬럼을 선언한다. 속도가 빨라지며 각 컬럼마다 1 비트를 절약할
수 있다.
- default 값을 가질 때 유리하다. 입력되는 값이 기본값과 다를 때만 확실하게 값이 입력된
다. INSERT 문에서 첫번째 TIMESTAMP 컬럼이나 AUTO-INCREAMENT 컬럼의 값을
입력할 필요가 없다. 18.4.49 [mysql_insert_id()] 참고.
- 가능한한 테이블을 작게 만드려면 더 작은 integer 타입을 사용하자. 예를 들면 MEDIUM
INT 가 보통 INT 보다 좋다.
- 가변 길이 컬럼이 없다면(VARCHAR, TEXT or BLOB columns), 고정 길이 레코드 포
맷이 사용된다. 이 경우 속도는 더 빠르지만 불행히도(흑흑~) 낭비되는 공간이 더 많다. 10.1
4 [Row format] 참고.
- mysql이 질의를 효과적으로 최적화하기 위해 많은 양의 데이터를 입력한후 isamchk --a
nalyze를 실행하자. 이렇게 하면 동일한 값을 가진 줄의 평균 숫자를 가리키는 각 인덱스의
값을 업데이트한다. (물론 unique 인덱스에서는 항상 1이다)
- 인덱스와 인덱스에 따른 데이타를 정열하려면
isamchk --sort-index --sort-records=1 을 사용하자.(if you want to sort on index 1).
인덱스에 따라 정렬된 모든 레코드를 읽기 위해 unique 인덱스를 가졌다면 이렇게 하는 것
이 속도를 빠르게 하는 가장 좋은 방법이다.
- INSERT 문에서 가능한 다중 값 목록을 사용하자. 개별적인 SELECT 문보다 훨씬 빠르
다. 데이타를 테이블에 입력할 때 LOAD DATA INFILE을 사용하자. 많은 INSERT 문을
사용하는 것보다 보통 20배 빠르다. 7.15 [Load] 참고.

많은 인덱스를 가진 테이블에 데이타를 입력할때 다음의 과정을 사용하면 속도를 향상시킬
수 있다.
1. mysql이나 Perl 에서 CREATE TABLE로 테이블을 만든다.
2. mysqladmin flush-tables 실행. (** 열린 테이블을 모두 닫음 **)
3. isamchk --keys-used=0 /path/to/db/tbl_name 사용. 테이블에서 모든 인덱스 사용을 제
거한다.
4. LOAD DATA INFILE 를 이용 테이블에 데이타를 입력.
5. pack_isam을 가지고 있고 테이블을 압축하기 원하면 pack_isam을 실행.
6. isamchk -r -q /path/to/db/tbl_name 를 이용 인덱스를 다시 생성.
7. mysqladmin flush-tables 실행.

- LODA DATA INFILE 과 INSERT 문에서 더 빠른 속도를 내려면 키 버퍼를 증가시킨
다. mysqld나 safe_mysqld에서 -O key_buffer=# 옵션을 사용하면 된다. 예를 들어 16M는
풍부한 램을 가졌다면 훌륭한 값이다.
- 다른 프로그램을 사용하여 데이타를 텍스트 파일로 덤프할때 SELECT ... INTO OUTFIL
E 을 사용하자. 7.15 [LOAD DATA INFILE] 참고.
- 연속으로 다량의 insert와 update를 할 때 LOCK TABLE을 사용하여 테이블에 락을 걸
면 속도를 향상시킬 수 있다. LOAD DATA INFILE 그리고 SELECT ...INTO OUTFILE
는 원자적이기 때문에 LOCK TABLE을 사용하면 안된다. 7.23 [LOCK TABLES/UNLOCK
TABLES] 참고.

테이블이 얼마나 단편화되었는지 점검하려면 '.ISM' 파일에서 isamchk -evi 를 실행한다. 1
3장 [Maintenance] 참고.



10.11 INSERT 문에서 속도에 영향을 미치는 부분 <** insert 최적화 **>

insert 하는 시간은 다음와 같이 구성된다:

Connect: (3)
Sending query to server: (2)
Parsing query: (2)
Inserting record: (1 x size of record)
Inserting indexes: (1 x indexes)
Close: (1)

(숫자)는 비례적인 시간이다. 이것은 테이블을 개방할때 초기의 overhead를 고려하고 있지
는 않다. (매 동시병행적으로 수행되는 질의마다 발생)

The size of the table slows down the insertion of indexes by N log N (B-trees).


테이블의 크기는 N log N(B-trees)에 따라 인덱스의 입력이 느려진다. (**말이 좀 이상. 테
이블이 커짐에 따라 인덱스 생성도 느려진다는 뜻이겠죵 **)

테이블에 락을 걸거나 insert 문에서 다중 값 목록을 사용하여 입력 속도를 빠르게 할 수
있다. 다중 값 목록을 사용하면 단일 insert 보다 5배 정도 속도가 빨라진다.

mysql> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql> INSERT INTO a VALUES (8,26),(6,29);
mysql> UNLOCK TABLES;

주요한 속도 차이는 모든 INSERT 문이 완료되고 난 후에 한번에 인덱스 버퍼가 쓰여기지
때문에 생긴다. 보통 서로 다른 여러 INSERT 문이 있으면 많은 인덱스 버퍼 플러쉬가 있
을 것이다. 모든 줄을 단일 문으로 입력하면 락은 필요없다.

락킹은 또한 다중 연결 테스트의 총 시간을 줄일 수는 있다. 그러나 어떤 스레드에서는 총
대기시간은 증가할 수 있다.(왜냐면 락을 기다리기 때문이다)
예를 들어보자:

thread 1 does 1000 inserts
thread 2, 3, and 4 does 1 insert
thread 5 does 1000 inserts

락을 사용하지 않으면 2, ,3 4는 1과 5 전에 끝마칠 것이다. 락을 사용하면 2,3,4는 아마도 1
이나 5 전에 끝나지 않을 것이다. 그러나 총 시간은 40% 빨라진다.

INSERT, UPDATE, DELETE 오퍼레이션은 mysql에서 매우 빠르다. 그렇기 때문에 줄에서
5개 이상의 insert나 update를 할 때 락을 추가하면 더 좋은 성능을 얻을 수 있다. 줄에 매
우 많은 자료를 입력한다면 다른 스레드에서 테이블에 접근하도록 하기 위해 때때로(각 100
0줄마다) UNLOCK TABLES를 사용하는 LOCK TABLES 실행하면 된다. 이렇게 하면 좋
은 성능을 낼 수 있다. (** 열심히 입력을 하고 중간에 락을 풀었다가 다시 락을 거는 것
반복함**)

물론 LOAD DATA INFILE 이 더 빠르다.



10.12 DELETE 문에서 속도에 영향을 미치는 부분 <** DELETE 문 최적화 **>

레코드를 삭제하는 시간은 정확히 인덱스 숫자에 비례한다. 레코드를 빠르게 지우기 위해
인덱스 캐쉬의 크기를 증가시킬 수 있다. 기본 인덱스 캐쉬는 1M 이다; 빠르게 삭제하기 위
해 증가되어야 한다.(충분한 메모리를 가지고 있다면 16M로 하자)


10.13 mysql에서 최대 속도를 얻는 방법

벤치마킹을 시작하자! mysql 벤치마크 스위트에서 어떤 프로그램을 사용할 수 있다. (일반
적으로 'sql-bench' 디렉토리에 있음) 그리고 입맞에 맞게 수정하자. 이렇게 하면 당신의 문
제를 해결할 수 있는 다른 해결책을 찾을 수 있으며 당신에게 가장 빠른 해결책을 테스트할
수 있다.

- mysqld를 적절한 옵션으로 시작하자. 메모리가 많을수록 속도가 빠르다.
10.1 [MySQL parameters] 참고.
- SELECT 문의 속도를 빠르게 하기 위해 인덱스를 만들자.
10.4 [MySQL indexes] 참고.
- 가능한 효율적으로 컬럼 타입을 최적화하자. 예를 들면 가능한 NOT NULL로 컬럼을 정
의하자. 10.10 [Table efficiency] 참고.
- --skip-locking 옵션은SQL 요청에서 파일 락킹을 없앤다. 속도가 빨라지지만 다음의 과
정을 따라야 한다:
ㅇ isamchk로 테이블을 체크하거나 수리하기 전에 mysqladmin flush-tables 로 모
든 테이블을 플러시해야 한다. (isamchk -d tbl_name은 언제나 허용된다. 왜냐하면 이건 단
순히 테이블의 정보를 보여주기 때문이다)
ㅇ 동시에 뜬 두개의 mysql 서버가 동일한 테이블을 업데이트하려 한다면 동일한
데이터 파일에 두개의 mysql 서버를 띄우면 안된다.

--skip-locking 옵션은 MIT-pthreads로 컴파일할때 기본값이다. 왜냐면 모든 플랫
폼의 MIT-pthreads에서 flock()가 완전하게 지원이 되지 않기 때문이다.

- 업데이트에 문제가 있다면 업데이트를 미루고 나중에 하자. 많은 업데이트를 하는 것이
한번에 하나를 업데이트하는 것보다 더 빠르다.
- FreeBSD 시스템에서 MIT-pthreads에 문제가 있으면 FreeBSD 3.0 이후 버전으로 업데
이트 하는것이 좋다. 이렇게 하면 유닉스 소켓을 사용하는 것이 가능하며(FreBSD에서 유닉
스 소켓이 MIT-pthreads에서 TCP/IP 연결을 사용하는 것보다 빠르다) 그리고 스레드 패키
지가 조정(intergrated?)되어야 한다.
- 테이블이나 컬럼 단계를 체크하는 GRANT는 성능을 떨어뜨린다.


10.14 로우 포맷과 다른 점은 무엇인가? 언제 VARCHAR/CHAR을 사용해야 하는가?

mysql은 실제의 SQL VARCHAR 타입이 없다. 그대신 mysql은 레코드를 저장하고 이것을
VARCHAR로 에뮬레이트하는데 세가지 방법이 있다.

테이블에 VARCHAR, BLOB, TEXT 컬럼이 없으면 고정 row size를 사용한다. 그외에는
동적 row size를 사용한다. CHAR 과 VARCHAR 컬럼은 애플리케이션의 관점에서 동일하
게 취급된다; 둘다 trailing space는 컬럼을 가져올때 제거된다.

isamchk -d 를 이용 테이블에서 사용하는 포맷을 체크할 수 있다.
(-d 는 "테이블 묘사"를 의미)

mysql은 세가지 다른 테이블 포맷을 가지고 있다; 고정길이, 다이나믹, 압축.


고정 길이 테이블
- 기본 포맷. 테이블에 VARCHAR, BLOB, TEXT 컬럼이 없을 때 사용.
- 모든 CHAR, NUMERIC, DECIMAL 컬럼은 컬럼 길이에 space-padded 이다. (** space-
padded를 무엇이라고 번역해야 할지 애매모호해서 **)
- 매우 빠름
- 캐쉬하기 쉽다
- 손상 후 복구가 쉽다. 왜냐면 고정된 위이에 레코드가 위치하기 때문이다.
- 많은 양의 레코드가 지워졌거나 운영 시스템에서 자유 공간을 늘리길 원치 않는다면 (isa
mchk를 이용) 재조직화할 필요없다.
- 보통 다이나믹 테이블보다 많은 디스크 공간을 필요로 한다.


다이나믹 테이블
- 테이블이 VARCHAR, BLOB, TEXT 컬럼을 포함하고 있을 때 사용.
- 모든 문자열 컬럼은 다이나믹하다.(4보다 작은 길이를 가진 문자열 제외)
- 컬럼이 문자열 컬럼에서 비었거나 ('') 숫자형 컬럼에서 0(NULL 값을 가진 컬럼과 동일
한 것이 아니다) 을 나타내는 비트맵이 모든 레코드 앞에 선행된다. 문자열 컬럼에서 trailin
g space를 제거한 후 zero의 길이를 가지거나 숫자형 컬럼이 zero의 값을 가지면 비트 맵으
로 표시되고 디스크에 저장되지 않는다. 비지 않은 문자는 문자내용에 길이 바이트만큼 추
가되어 저장된다.
- 보통 고정 길이 테이블보다 디스크 공간 절약.
- 줄의 길이를 확장하는 정보를 가지고 줄을 업데이트하면 줄은 단편화될 것이다. 이런 경
우 더 좋은 성능을 위해 때때로 isamchk -r 을 실행해야 한다. 통계적으로(?) isamchk -ei
tbl_name을 사용하자.
- 손상후 복구가 어렵다. 왜냐면 레코드가 많은 조각드로 단편화되고 링크(단편)가 없어지
기 때문이다.
- 다이나믹 사이즈 테이블의 예상되는 열 길이 :
3
+ (number of columns + 7) / 8
+ (number of char columns)
+ packed size of numeric columns
+ length of strings
+ (number of NULL columns + 7) / 8

각 링크마다 6 바이트가 더 있다. 다이나믹 레코드는 업데이트로 레코드가 늘어날때마다 링
크된다. 각 새로운 링크는 최소 20바이트일 것이며, 그래서 다음의 확장은 아마도 동일한 링
크로 될 것이다. 그게 아니라면 다른 링크가 있을 것이다. isamchk -ed 로 얼마나 많은 링
크가 있는지 체크할 수 있다. 모든 링크는 isamchk -r 로 제거할 수 있다.(** ?? **)

There is a penalty of 6 bytes for each link. A dynamic record is linked whenever an up
date causes an enlargement of the record. Each new link will be at least 20 bytes, so th
e next enlargement will probably go in the same link. If not, there will be another link.
You may check how many links there are with isamchk -ed. All links may be removed
with isamchk -r.


압축 테이블

- 읽기 전용 테이블은 pack_isam 유틸리티로 만들 수 있다. 확장 mysql 이메일 지원을 구
입한 모든 고객은 내부적인 용도로 pack_isam을 사용할 권리가 주어진다.
- 압축해제 코드는 모든 mysql 배포판에 있으므로 pack_isam이 없는 고객도 pack_isam으
로 압축된 테이블을 읽을 수 있다. (테이블이 같은 플랫폼에서 압축되어 있는한)
- 매우 적은 디스크 용량을 사용.
- 각 레코드는 개별적으로 압축이 된다.( 매우 적은 액세스 overhead) 레코드의 헤더는 테
이블의 가장 큰 레코드에 따라 (1-3 바이트) 고정된다. 각 컬럼은 다르게 압축이 된다. 압축
타입은 다음과 같다:

ㅇ 일반적으로 각 컬럼마다 다른 Huffman 테이블이다.
ㅇ Suffic 공간 압축
ㅇ Prefix 공간 압축
ㅇ 0 값을 가진 숫자는 1비트로 저장.
ㅇ integer 컬럼의 값이 작은 범위를 가졌다면, 컬럼은 최대한 작은 타입으로 저장
된다. 예를 들면 BIGINT 컬럼은 모든 값이 0부터 255라면 TINIINT 컬럼(1바이트)로 저장
된다.
ㅇ 컬럼이 몇가지 가능한 값으로만 구성되어 있다면, 컬럼 타입은 ENUM으로 변환
된다.
ㅇ 컬럼은 위 압축 방법을 조합하여 사용한다.
- 고정 길이나 다이나믹 길이의 테이블을 다룰 수 있다. 그러나 BLOB나 TEXT 컬럼은 다
룰 수 없다.
- isamchk로 압축을 해재할 수 있다.

mysql은 다른 인덱스 타입을 지원한다. 그러나 일반적인 타입은 NISAM이다. 이것은 B-tre
e 인덱스이며 모든 키의 갑을 합하여 (키 길이+4)*0.67로 인덱스 파일의 크기를 대강 계산
할 수 있다. (이것은 모든 키가 정렬된 순서로 입력된 가장 나쁜 경우이다)


String indexes are space compressed. If the first index part is a string, it will also be p
refix compressed. Space compression makes the index file smaller if the string column h
as a lot of trailing space or is a VARCHAR column that is not always used to the full
length. Prefix compression helps if there are many strings with an identical prefix.

문자열 인덱스는 공간이 압축된다. 첫번째 인덱스 부분이 문자열이라면, prefix가 압축된다.
문자열 컬럼이 다량의 trailing space를 가졌거나 언제나 완전한 길이를 사용하지 않는 VA
RCHAR 컬럼일 때 space 압축은 인덱스 파일을 더 작게 만든다. prefix 압축은 많은 문자
열에 동일한 prefix가 있을 때 유용하다.
2006/09/08 11:01 2006/09/08 11:01
이 글에는 트랙백을 보낼 수 없습니다
출처 블로그 > loves0508님의 블로그
원본 http://blog.naver.com/loves0508/2942014

이번 기사에서는 Swynk.com의 필자인 Alexander Chigrik가 제안하는 대용량 SQL Server 관리자를 위한 다양한 팁 14가지에 대해 알아보자.

이번 내용에서는 잘 알려지지는 않았지만 SQL Server 환경에서 성능을 개선하기 위한 매우 유용한 방법들에 대해서 알아보자.

1. 가능한 테이블의 컬럼수는 작게 설계하는 것이 좋다.

테이블의 컬럼수가 적은 것은 당연히 I/O의 성능 향상에 도움이 된다. 당연히 쿼리 연결시 필요한 컬럼만을 가져와야 하며 작은 컬럼수의 설계는 백업이나 리스토어 등의 업무에 성능 개선을 준다.

2. 컬럼 타입을 text/ntext를 써야 하는 경우라면 varchar/nvarchar 컬럼 형태로 사용하는 것이 좋다.

왜냐하면 SQL Server는  text/ntext 컬럼의 경우 다른 데이터들과 분할에서 저장하게 되며 이 저장된 값이 필요할때는 다른 형식의 값들보다 오래걸리는 것은 당연한 이야기이다.

3. 만약 유니코드값이 입력되지 않는 컬럼 타입에 nchar/nvarchar를 써야 하는 경우라면 char/varchar를 고려하는 것이 좋다.

당연히 이러한 설계는 테이블 사이즈를 감소시킨다. 작은 테이블 사이즈는 I/O비용을 감소시키고 데이터베이스의 부하를 줄이면서 성능향상의 기본이 된다.

4. 만약 테이블 컬럼에 들어올 데이터의 크기가 4바이트 이하라면 varchar/nvarchar 컬럼 대신 char/nchar를  사용하는 것이 좋다.

고정된 값의 크기를 갖는 char 데이터 타입은 가변적인 값을 갖는 varchar 데이터 타입보다 작은 사이즈를 갖는다.

결국 char 데이터 타입을 사용하면 업데이트를 하거나 입력을 하는데 도움이 된다. 업데이트를 실행하게 되면 기존 값을 지우고 새로운 값을 입력하는 수정작업이 이뤄지게 되는데 이러한 물리적인 데이터의 수정작업은 사이즈가 작을 수록 성능 향상에 도움이 되는 것이다.

5. 데이터베이스의 테이블을 3정규화를 하는 것이 좋다.

만약 3정규화를 통한 테이블 설계가 이뤄지지 않고 종속관계를 포함하지 않거나 2정규화 등을 통해 테이블 설계가 이뤄진다면 많은 문제를 발생시킬 수 있다.

3정규화를 통해 여러 테이블의 조인비용등의 두려움으로 인해 반정규화를 한다는 것은 위에서 말한 심플한 컬럼 디자인과 쿼리시 필요한 컬럼만을 조인하는 방법등으로 얼마든지 성능개선을 할 수 있다.

6. 데이터베이스의 테이블이 4정규화와 5정규화를 요구한다면 반정규화를 고민해보는것도 좋다.

정규화에서 4정규화와 5정규화를 요구한다면 그 결과는 성능저하를 요인할 수 있다. 이런 경우 성능 개선을 위해 반정규화 설계를 고민해보는 것이 필요하다.

7. 만약 모든 테이블의 레코드를 지우는 삭제작업을 해야 한다면 DELETE 명령어를 통한 삭제보다는 TRUNCATE TABLE을 사용하는 것을 고려해 볼만 하다.

TRUNCATE TABLE를 사용하면 각각의 레코드를 지우며 로깅을 하는 DELETE 보다는 훨씬 빠른 방법이다.

8. 가능한 엔터프라이즈 메니저를 통해 원격 접속하여 관리하는 것은 서버에 많은 부담을 준다.

엔터프라이즈 매니저의 경우 많은 리소스를 요구하기 때문에 가능한 쿼리분석기등을 통한 제어가 부담을 줄일 수 있다.

9. PDC, BDC, 도메인 컨트롤러 또는 DNS서비스등이 설치된 곳에 설치하지 않는 것이 좋다.

도메인 컨틀롤러 등은 많은 오버헤드가 발생한다.  가능한 SQL Server 만을 독립적으로 설치해서 모든 자원이 서버만을 위해 사용되게 설계하는 것은 기본이다.

10. 성능 저하를 막기 위해 가능한 로컬 하드에 백업한 후 다른 백업장치로 옮기는게 좋다.

만약 백업을 하게 되면 SQL Server에서 몇가지 명령들을 사용할 수 없게 된다. 예를 들어 백업하는 동안 "ALTER DATABASE","ADD FILE","REMOVE FILE 옵션","shrink a database","CREATE INDEX","SELECT INTO" 등을 사용할 수 없다. 백업하는 시간이 오래걸리구 성능저하를 고려한다면 먼저 로컬 하드디스크에 백업한 후 백업 파일을 테이프 백업장치 등에 옮기는것이 좋다.

11. 가능한 로그를 남기지 않는 "nonlogged bulk copy"를 이용하는 것이 좋다.

"nonlogged bulk copy" 는 로그를 남기면서 대량 복사 작업을 하는 것보다 무척 빠르다. 하지만 이것을 이용하려면 다음을 충족시켜야 한다.

  • 데이터베이스 옵션에서 "select into/bulkcopy"가 체크되어야 한다.
  • 만들어질 테이블은 만들어져 있지 않아야 한다.
  • 만들어질 테이블은 인덱스가 없어야 실행할 수 있다.

12. 가능한 "bcp" 또는 "DTS"를 이용하는 것보다 "BULK INSERT" 명령어를 통해 데이터를 옮기는 것이 좋다.

"BULK INSERT" 명령어를 이용하면 텍스트 파일로 생성한후 "DTS"를 통해서 값을 가져오거나 서버간 연결을 통해 "DTS"로 가져오는 것보다 훨씬 빠르다. 자신의 데이터가 수억레코드가 된다면 고려해 볼만한 내용이다.

13. SQL Server 테이블에 값을 넣거나 다른 데이터로 변환하고자 할때 DTS를 이용하는 것보다 bcp 유틸리티를 사용하는 것이 좋다.

bcp 유틸리티를 사용하는 것이 DTS를 사용하는 것보다 매우 빠르다.

14. 가능한 트랜잭션을 짧게 구성하는 것이 좋다.

트랜잭션 처리 작업이 짧게 이뤄지도록 설계하는것은 데드락을 발생시키는 것을 줄이는 당연한 이야기이다.

2006/09/08 11:01 2006/09/08 11:01
이 글에는 트랙백을 보낼 수 없습니다
출처 블로그 > loves0508님의 블로그
원본 http://blog.naver.com/loves0508/2942328
mysql 이 웹에서 가장 많이 사용되는 RDBMS 라는 데는 의심의 여지가 없는 것 같다. 주로 Apache 와 PHP 와 연동해서 사용되어 지는데 (이 3가지 조합을 APM 이라고 한다), 간단한 카운터, 방명록에서 부터, 좀더 복잡한 쇼핑몰, 스케쥴관리, 게시판, 웹메일 등 거의 쓰이지 않는 곳이 없을 정도이다.
Mysql 이 PHP 와 함께 웹에서 사용하는게 가장 일반적인 용도이긴 하지만, 많은 경우 시스템레벨에서 직접 다루어야 하는경우도 생긴다. 이러한 경우를 위해서 mysql 은 Perl, Python, C, C++ 등 다양한 API를 제공하는데, 우선 C를 이용한 접근에 대해서 알아보도록 하겠다. (나중에 시간이 허락하면 C++ 을 이용한 mysql 접근에 대해서도 알아보도록 하겠습니다) 설명에 들어감에 앞서 이문는 여러분이 SQL과 RDBMS에 대한 개념과, mysql 의 설치 및 운영에 관련된 기본 사항은 알고 있다는 가정하에 쓰여졌으며, 설명을 위해 쓰인 코드들은 기능과 효율성에 염두를 둔 코드가 아닌 순수 스터디용 (돌아만가는)코드 임을 공지합니다. mysql과 SQL 에 대한 상세한 내용은 관련 서적이나 database.sarang.net을 참고하세요.

코드를 테스트 하기 전에 우선 여러분의 시스템에 mysql client 가 설치되어 있는지 확인을 해보바란다. 필자의 경우 mysql-3.23.46 이 설치되어 있으며, 인클루드 파일은 /usr/local/include/mysql 에 라이브러리 파일은 /usr/local/lib/mysql 설치되어 있다. 요즘 왠만한 Linux 배포판은 기본으로 mysql 이 설치되어 있으니, 위의 인클루드와, 라이브러리 경로가 어디에 있는지 확인만 하면 될것이다.
그럼 테스트를 위한 테이블을 만들고 테이블에 간단한 내용을 입력해보자, 테이블이 위치할 DB 는 test 이고 테이블의 이름은 address 이다.
CREATE TABLE address (  name varchar(25) default NULL,  address text,  tel varchar(25) default NULL);
이 테이블은 간단한 주소록인데, "이름", "주소", "전화번호" 를 저장하기 위해서 사용된다.
이제 테스트를 위해서 2개 정도의 data 를 입력하도록 하자.
INSERT INTO address VALUES ('홍길동','경기도 연천 연천아파트','02-500-5000');INSERT INTO address VALUES ('아무개','광주광역시 서구 현대 아파트','015-000-1111');
우리가 만들 프로그램을 테스트 하기 위한 환경이 갖추어 졌다면, 이제 본격적으로 코드를 작성해 보도록 하겠다.

예제 : mysql_test.c
#include <mysql.h>#include <string.h>#include <stdio.h>#define DB_HOST "127.0.0.1"#define DB_USER "root"#define DB_PASS "gkwlak"#define DB_NAME "test"#define CHOP(x) x[strlen(x) - 1] = ' '    int main(void){    MYSQL       *connection=NULL, conn;    MYSQL_RES   *sql_result;    MYSQL_ROW   sql_row;    int       query_stat;     char name[12];    char address[80];    char tel[12];    char query[255];        mysql_init(&conn);    connection = mysql_real_connect(&conn, DB_HOST,                                    DB_USER, DB_PASS,                                    DB_NAME, 3306,                                    (char *)NULL, 0);    if (connection == NULL)    {        fprintf(stderr, "Mysql connection error : %s", mysql_error(&conn));        return 1;    }    query_stat = mysql_query(connection, "select * from address");    if (query_stat != 0)    {        fprintf(stderr, "Mysql query error : %s", mysql_error(&conn));        return 1;    }        sql_result = mysql_store_result(connection);        printf("%+11s   %-30s   %-10s", "이름", "주소", "전화번호");    while ( (sql_row = mysql_fetch_row(sql_result)) != NULL )    {        printf("%+11s   %-30s   %-10s", sql_row[0], sql_row[1], sql_row[2]);    }    mysql_free_result(sql_result);    printf("이름 :");    fgets(name, 12, stdin);    CHOP(name);    printf("주소 :");    fgets(address, 80, stdin);    CHOP(address);    printf("전화 :");    fgets(tel, 12, stdin);    CHOP(tel);    sprintf(query, "insert into address values "                   "('%s', '%s', '%s')",                   name, address, tel);    query_stat = mysql_query(connection, query);    if (query_stat != 0)    {        fprintf(stderr, "Mysql query error : %s", mysql_error(&conn));        return 1;    }    mysql_close(connection);}
mysql 은 mysql 연결, query 결과 받아오기, 결과물의 Row 값을 저장등을 위한 몇개의 구조체가 존재한다.
MYSQL데이타 베이스에 연결했을때, 이 연결을 다루기 위해 사용되는 구조체 이다
MYSQL_RES(SELECT, SHOW, DESCRIBE, EXPLAIN)등의 쿼리를 내렸을때 그 결과를 다루기 위해 사용되는 구조체이다.
MYSQL_ROW이것은 데이타의 하나의 row 값을 가리킨다. 만약 row 값이 없다면 null 을 가르키게 된다.
MYSQL_FIELD이 구조체는 각 필드의 정보를 가지고 있다. 여기에는 필드의 이름, 타입, 크기 등의 정보를 가지게 된다. mysql 에서 DESC 쿼리를 내렸을때의 정보를 가지고 있다고 보면된다.
MYSQL_FIELD_OFFSETmysql 필드 리스트의 위치를 가진다.
이 프로그램이 하는 일은 최초 mysql DB에 연결을 한다음에 query 를 통하여 address 의 내용을 가져와서 화면에 출력시켜주고. 다음에 사용자 입력을 받아서 DB에 저장하는데까지다. 앞에서 말했듯이, 유저 인터페이스라든가 하는 기능적인 측면은 전혀 신경쓰지 않았다. 이러한 측면에 대해서는 나중에 curse 계열을 다루면서 연구하게 될것이다. 그럼 이제부터 소스를 분석해 보기로 하겠다.

mysql_init() 는 mysql DB에 연결하기 전에 가장 먼저 실행되며, mysql 연결 지시자를 초기화 하는 일을 한다. mysql_init()를 이용해서 mysql 연결을 초기화 하고 나서 mysql_real_connect 를 이용해서 mysql 서버에 실제로 접근하게 된다. 아규먼트로는 우리가 터미널에서 mysql 을이용해서 접근하는데 필요한, 호스트이름(DB_HOST), 유저계정(DB_USER), 계정에 대한 패스워드(DB_PASS), 접근 하고자 하는 DB이름(DB_NAME), 포트번호(DB_PORT) 등이 들어간다. 연결에 성공하면 connection 핸들 값을 넘겨주고 실패하게 되면 NULL 값을 넘겨 주게 된다. 어떤 이유로 실패했는지 자세한 내용을 알아보고 싶다면 mysql_error() 을 사용하라.
mysql 서버로의 연결까지 성공적으로 마쳤다면, 이제 query 를 이용해서 본격적인 작업에 돌입한다. mysql_query 를 통해서 필요한 query를 실행 시키면된다.
우리가 보통 사용하는 쿼리는 "SELECT, SHOW, DESC, EXPLAIN" 과 같이 쿼리에 그 결과 값(row)을 요청하는 것과, "INSERT, UPDATE, DELETE" 와 같이 그 결과 값이 필요 없는 명령어로 나누어 볼수 있다. row 값을 요청하지 않는 쿼리라면 필요가 없겠지만 row 값을 요청하는 쿼리라면 쿼리의 결과값을 저장해야 할것이다. 이럴때 mysql_store_result()를 이용해서 쿼리의 결과값을 되돌려 받을수 있다. mysql_store_result()를 통해서 쿼리의 결과값을 되돌려 받았다면, mysql_fetch_row()를 이용해서 row 단위로 결과 값을 가져올수 있다. 이 함수는 쿼리의 결과값에서 다음의 row 값을 가져오며, 더이상 가져올 row 값이 없다면 NULL 을 돌려준다.
가져온 row 에서의 각필드 값은 row[0] 에서 row[mysql_num_fields(result)-1] 에 저장되어 있으 므로, 해당 row 의 field 값을 쉽게 가져올수 있다.
mysql_store_result() 을 통해서 가져온 쿼리결과값을 더이상 사용할 필요가 없다면, 메모리를 되돌려줘야 하는데, mysql_free_result 를 통해서 되돌려주면 된다. mysql_store_result()를 사용했다면 반드시 mysql_free_result()를 사용해서 메모리를 해제 시켜줘야 한다. 그렇지 않으면 메모리 누수가 발생하게 된다.

mysql_free_result()까지 시켜주고 나서, fgets() 함수를 통하여서 "이름", "주소", "전화번호" 를 입력받아서, 입력받은 정보로 쿼리를 만들고 이를 mysql_query()를 이용해서 DB 에 적재하는 것을 마지막으로 예제의 설명이 끝났다.
누가 보더라도 이해하기 쉬운 코드일것이다. 이제 이 코드를 컴파일 해서 실행시켜 보는 일만 남았다.
gcc -o mysql_test mysql_test.c -I/usr/local/include/mysql                    -L/usr/local/lib/mysql -lmysqlclient
-I 과 -L 옵션을 이용해서 컴파일 시키는데 필요한 인클루드 파일과 라이브러리 파일이 있는 디렉토리를 명시해주면 된다. 위의 디렉토리는 필자가 가진 시스템의 경우이고, 위의 디렉토리는 시스템에 따라서 다를수 있으니 자신의 시스템 설정에 맞도록 옵션을 주고 컴파일 하기 바란다.

이상 간단하게 mysql API 를 살펴보았다. 위의 예제는 많은 mysql API 중의 극히 일부분만을 써서 꼭필요한 기능만 구현한것이다. 더 자세한 정보는 www.mysql.org 사이트를 참고하기 바란다
2006/09/08 11:00 2006/09/08 11:00
이 글에는 트랙백을 보낼 수 없습니다
출처 블로그 > 불행한 바보
원본 http://blog.naver.com/deepinheart/20006875024
byeok 2004-04-09 21:36:13.0
출처 :  http://www.okjsp.pe.kr/
ssweon@rtv.or.kr
mysql 4.1.1 alpha 에서 한글 제대로 설정하기

mysql이 4.1로 업그레이드 되면서 서브쿼리가 지원되게 되어

아마도 많은 사람들이 개발시점에서 한 번쯤은 mysql4.1을 사용할지

고민했으리라 생각합니다.

그러나 아직 제대로된 버전이 아닌관계로 버젓한 메뉴얼이 없어

변화된 localization셋팅때문에 많은 분들이 고생했을 것이라 예상합니다.


mysql4.1에서는 4.0에서처럼 default character set하나만 바꿔주면 끝나질

않습니다. 서버/데이터베이스/테이블/connection/ 심지어 필드

하나하나, 스트링 하나하나까지도 character set과 collation을 설정할 수

있게 되었습니다. 그래서 4.0에서 서비스하던 사이트를 4.1로

데이터를 옮기는 과정에서 한글이 깨지는 현상이 발생합니다.


쉽게 말씀드리면 4단계에 걸쳐서 각각 character set과 collation을

변경해주시면 됩니다. 우선 데이터베이스를 다음과 같이 새로 생성합니다.


>> create database DB_NAME character set euckr collation euckr_korean_ci


그리고 4.0의 데이터를 스크립트로 복사한 다음 각각의 테이블 끝에

붙어있는 character set을 euc-kr 이나 latin1에서 euckr로 변경해줍니다.

(4.1에서 한글의 character set이름이 euc-kr에서 euckr로 변경되었습니다.)


그런 다음 mysql 을 --default-character-set=euckr --default-collation=euckr_korean_ci 의 옵션을 주어 실행시키면서 자료를 덤프받습니다.


>> mysql -uroot -p --default-character-set=euckr --default-collation=euckr_korean_ci < DB_SCRIPT.SQL 

(DB_SCRIPT.SQL파일은 4.0에서 받은 데이터 스크립트파일이겠죠.)


그렇게 하면 일단 데이터베이스 어필리케이션(예를 들면 SQLyog같은 프로그램)에서는 정상적으로 데이터를 볼 수 있을 것입니다.

하지만 JDBC를 사용해서 접속하는 경우 connection characterset을  따로

설정해주지 않으면 한글이 깨질 것입니다.

저같은 경우 어플리케이션에서 connection을 얻어오는 즉시

해당 connection을 이용하여 "SET CHARACTER SET euckr"이란

SQL명령을 executeUpdate 시켰서 해결했습니다만 아마도 더 깔끔한

방법이 있을 줄 압니다.


그렇게 하면 mysql4.1.1알파에서도 한글을 문제없이 사용할 수 있습니다.

참고로 그냥 latin1으로 설정한 상태에서도 한글이 사용이 가능하긴 합니다만 order by나 일부 한글로의 검색시에 제대로 검색이 안되는 경우가 발생합니다. 그런 경우 해당 필드만 character set을 설정해서 사용할 수도 있습니다. 예를 들면 latin1으로 저장된 한글을 euckr의 코드셋 순서로 정렬하고 싶을 때 다음과 같이 하면 됩니다.


>> SELECT * FROM table_name ORDER BY k COLLATE euckr_korean_ci


그러면 한글 순서로 정렬이 됩니다.

이 말은 만약 이제까지 latin1으로 잘 써오신 분이라면 정렬을 위해서만 추가적으로 코드를 추가해줄 경우 문제없이 잘 정렬이 됨을 의미하는 것이니 데이터베이스를 통째로 수정할 필요가 없겠지요.


저도 초보라 고생끝에 알아낸 팁입니다. 유용하게 참고하셨으면 좋겠습니다.

2006/09/08 10:53 2006/09/08 10:53
이 글에는 트랙백을 보낼 수 없습니다
출처 블로그 > kukustyle
원본 http://blog.naver.com/inch772/100016274834

Mysql Replication을 구성하는 여러 구조의 예와 각각의 장단점을 정리한 문서

출처 : High Performance Mysql Ch7

(http://dev.mysql.com/books/hpmysql-excerpts/ch07.html)


Sample Configurations

Building on the four rules, let's begin by constructing simple replication configurations and discussing the types of problems they solve. We'll also look at the types of configurations that don't work because they violate the second rule. We'll use the simple configuration as a building block for arbitrarily complex architectures.

Each configuration is illustrated in a figure that includes the server ID of each server as well as its role: master, slave, or master/slave.

Master with slaves

The most basic replication model, a single master with one or more slaves, is illustrated in Figure 7-1. The master is given server ID 1 and each slave has a different ID.


Figure 7-1. Simple master/slave replication

This configuration is useful in situations in which you have few write queries and many reads. Using several slaves, you can effectively spread the workload among many servers. In fact, each of the slaves can be running other services, such as Apache. By following this model, you can scale horizontally with many servers. The only limit you are likely to hit is bandwidth from the master to the slaves. If you have 20 slaves, which each need to pull an average of 500 KB per second, that's a total of 10,000 KB/sec (or nearly 10 Mbits/sec) of bandwidth.

A 100-Mbit network should have little trouble with that volume, but if either the rate of updates to the master increases or you significantly increase the number of slaves, you run the risk of saturating even a 100-Mbit network. In this case, you need to consider gigabit network hardware or an alternative replication architecture, such as the pyramid described later.

Slave with two masters

It would be nice to use a single slave to handle two unrelated masters, as seen in Figure 7-2. That allows you to minimize hardware costs and still have a backup server for each master. However, it's a violation of the second rule: a slave can't have two masters.


Figure 7-2. A slave can't have two masters

To get around that limitation, you can run two copies of MySQL on the slave machine. Each MySQL instance is responsible for replicating a different master. In fact, there's no reason you couldn't do this for 5 or 10 distinct MySQL masters. As long as the slave has sufficient disk space, I/O, and CPU power to keep up with all the masters, you shouldn't have any problems.

Dual master

Another possibility is to have a pair of masters, as pictured in Figure 7-3. This is particularly useful when two geographically separate parts of an organization need write access to the same shared database. Using a dual-master design means that neither site has to endure the latency associated with a WAN connection.


Figure 7-3. Dual master replication

Furthermore, WAN connections are more likely to have brief interruptions or outages. When they occur, neither site will be without access to their data, and when the connection returns to normal, both masters will catch up from each other.

Of course, there are drawbacks to this setup. Section 7.7.3, later in this chapter, discusses some of the problems associated with a multi-master setup. However, if responsibility for your data is relatively well partitioned (site A writes only to customer records, and site B writes only to contract records) you may not have much to worry about.

A logical extension to the dual-master configuration is to add one or more slaves to each master, as pictured in Figure 7-4. This has the same benefits and drawbacks of a dual-master arrangement, but it also inherits the master/slave benefits at each site. With a slave available, there is no single point of failure. The slaves can be used to offload read-intensive queries that don't require the absolutely latest data.


Figure 7-4. Dual master replication with slaves

Replication ring (multi-master)

The dual-master configuration is really just a special case of the master ring configuration, shown in Figure 7-5. In a master ring, there are three or more masters that form a ring. Each server is a slave of one of its neighbors and a master to the other.


Figure 7-5. A replication ring or multi-master replication topology

The benefits of a replication ring are, like a dual-master setup, geographical. Each site has a master so it can update the database without incurring high network latencies. However, this convenience comes at a high price. Master rings are fragile; if a single master is unavailable for any reason, the ring is broken. Queries will flow around the ring only until they reach the break. Full service can't be restored until all nodes are online.

To mitigate the risk of a single node crashing and interrupting service to the ring, you can add one or more slaves at each site, as shown in Figure 7-6. But this does little to guard against a loss of connectivity.


Figure 7-6. A replication ring with slaves at each site

Pyramid

In large, geographically diverse organizations, there may be a single master that must be replicated to many smaller offices. Rather than configure each slave to contact the master directly, it may be more manageable to use a pyramid design as illustrated in Figure 7-7.


Figure 7-7. Using a pyramid of MySQL servers to distribute data

The main office in Chicago can host the master (1). A slave in London (2) might replicate from Chicago and also serve as a local master to slaves in Paris, France (4), and Frankfurt, Germany (5).


2006/09/08 10:53 2006/09/08 10:53
이 글에는 트랙백을 보낼 수 없습니다

예를 들면 아래와 같은 Table이 있다고 했을때 Korea를 America로, America를 Korea으로 바꾸려고 한다면.....

[Table Name] National
==========================
Field1      Field2
==========================
1           Korea
2           America
3           England
4           Japan
...         ...

예전에 내가 사용한 방법

update national set field2 = 'America' where field1 = 1;
update national set field2 = 'Korea' where field1 = 2;

Update 구문을 두번 사용했다. -,.-; 그나마 변경할 레코드가 2개일땐 그렇다 쳐도 변경할 레코드가 죠낸 많아진다면, update 구문을 죠낸 남발해야 하는 경우가 생기는 것이다.

게다가 위에 구문을 성공하고 난 후에, 아래의 쿼리가 실패하게 되면 죠낸 안습인 상황이 되게 되는 것이다. 레코드에 America만 두개가 되는 상황... -,.-;;;

이 방법을 어찌 해결할 방법이 없을까 "mysql.org"를 뒤지다가 알게된 "CASE"문.... 어둠속에서 한줄기 빛을 보게 된 느낌이다.

CASE문의 기본 문법은 다음과 같다.

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

자.. 그럼 저 CASE문을 활용해서 UPDATE 구문을 하나로 바꿔보자....

UPDATE national set field2 = CASE
   WHEN field1 = 1 THEN "America"
   WHEN field1 = 2 THEN "Korea"
ELSE
  field2
END;

2개의 update 구문이 하나로 줄어들었다. 게다가 값을 바꾸는 SWAP같은 경우에도 문제없이 처리할 수 있게 되었다. 단 여기서 한가지 주의할게 있는데, ELSE 다음에 field2는 반드시 필요한 구문이다. 그렇게 해주지 않으면, WHEN 조건절에 해당하지 않게되는 레코드의 field2의 값은 null값이 입력되게 될테니 말이다.

여기에 추가로 해주는 작업이 WHERE 구문.... WHERE 구문을 통해 범위를 좁혀주면 완벽한 DATA SWAP이 이뤄지게 된다.

자.. 최종적으로 사용된 형태를 살펴볼까나?

UPDATE national set field2 = CASE
   WHEN field1 = 1 THEN "America"
   WHEN field1 = 2 THEN "Korea"
ELSE
  field2
END
WHERE field1 in (1, 2);
2006/09/08 10:52 2006/09/08 10:52
이 글에는 트랙백을 보낼 수 없습니다
출처 블로그 > ~(^0^)~ 유유자적 BABU
원본 http://blog.naver.com/solokimm/20010328180

create database japan4;


use japan4


create table abc (
aaa varchar(10),
bbb int(4)
);


INSERT INTO abc VALUES ('aaa', 100);
INSERT INTO abc VALUES ('bbb', 200);


delimiter //


create procedure kimsh (IN num int)
BEGIN
IF num > 0 THEN
UPDATE abc SET bbb = bbb - 50;
ELSE
UPDATE abc SET bbb = bbb + 100;
END IF;
END
//


delimiter ;


call kimsh(1);


SELECT * FROM abc;


call kimsh(-1);


SELECT * FROM abc;


##################################


create table person (
name varchar(20),
age int(4)
);


delimiter //


create procedure kim (IN a varchar(20), IN b int, OUT c varchar(30))
BEGIN
INSERT INTO person values (a, b);
SELECT CONCAT('Hello', a);
END
//


delimiter ;


call kim ('kimsh', 20, @kk);

2006/09/08 10:52 2006/09/08 10:52
이 글에는 트랙백을 보낼 수 없습니다
출처 블로그 > s1726c9966m님의 블로그
원본 http://blog.naver.com/s1726c9966m/100016925896
------------------Mysqli 확장 사용하기 1장 : 개괄과 미리준비된 문장

번역자
김영진(cogolda@hanmail.net)

--------------------------알아 두기
이 문서는 http://www.zend.com/php5/articles/php5-mysqli.php 를 제가 번역 수정한 내용입니다. 혹시 질문이나 의견 있으신 분은 코멘트나 이메일을 이용해 주시면 감사하겠습니다.  

대상독자
소개
* 주요 목적
* 주요 특징
* 왜 바꾸는가?
* 경고와 놀라움
소스 코드 보기
* 기본 사용
* 객체지향 인터페이스 사용하기
미리준비된(prepared) 문장
* 바운드(bound) 파라미터
* 바운드 결과(results)
* 바운드 파라미터와 바운드 결과를 같이 사용하기
요약

-------------------------대상 독자

이 기사는 php와 mysql의 사용 경험이 있는 독자를 위해 작성하였다. 그것은 데이터베이스와 프로그래밍의 기본적인 개념을 알고있고, php 스크립트 사용법과, mysql 서버 쿼리문을 보내는 법을 알고 있다고 가정했다.

Php와 mysql 설치법은 이 기사의 범위를 넘어선다

Php 5 설치에 관한 정보는, http://www.php.net/installation
이용 가능한 mysqli 확장에 관한 정보는,  http://www.php.net/mysqli
MySQL 4.1.2 이나 그 이상 버전의 설치에 관한 정보는,  http://www.mysql.com/doc/en/Installing.html

------------------------용어 설명
(원문에는 용어 설명이 맨 마지막에 있는데, 제가 위로 옮겼습니다.)

* Mysql/확장(ext/mysql) :: php의 구형 MySQL 확장(extension). MySQL version 4.1의 모든 기능을 지원하지 않는다.
* ext/mysqli :: php5의 새로운 MySQL 확장. MySQL 3.22 에서 5.0까지 모든 기능을 지원한다.
* MySQL 클라이언트 라이브러리 :: 프로그램이 RDBMS로 통신하기위하게 만드는 MySQL RDBMS의 컴퍼넌트
* MySQL 서버 :: 데이터베이스 안에서 디스크 수준의 표현을 관리하고, 처리작업과 요구를 하는 MySQL RDBMS의 컴포넌트.
(쉽게 말하자면 이 문서는 php5의 새로운 확장인 mysqli에 관한 내용입니다.)

----------------------------소개

90년대 중반 이후, mysql확장은 php와 mysql 사이에 주요 연결로서 서비스되었다. 비록 수년에 걸쳐서, 증가하는 불편함과 약간의 고생이 있었지만, 일반적으로 mysql확장은 좋은 성능을 가지고 있었고, php와 mysql 둘다 버전업을 하면서 보조를 맞추었다.

그렇지만, php5와 mysql 4.1의 소개 이후, 이런 변화는 다소 문제가 시작되고 있다는 것 보여준다. (아마도 mysql의 라이센스 문제때문이라고 보입니다.)

mysql확장에 있는 고생스러움은, 대부분 뚜렷하게도 mysql_pconnet()과 자동적이고 디폴트 커넥션이다. 추가적으로, mysql확장이 지원하는 특징과 mysql 클라이언트 라이브러리가 지원하는 특징 사이의 비호환성은 존재했었다.

이런 문제를 해결하는 노력으로서, 조지 리처는 mysql 4.1과 그 이상에서 새로운 특징을 지원 할 php5를 위한 새로운 mysql 확장을 만들었다.

이 확장은 mysqli확장(ext/mysqli)라고 부른다. I의 의미는 향상(improved), 연결(interface), 독창적인(ingenious), 비호환적인(incompatible), 미완성(incomplete) 이다.
(미완성이란 의미는 지금 개발중이라는 뜻으로 겸손한 입장에서 쓴걸로 보입니다.)

------------------------------주요 목표

새로운 확장의 주요 디자인 목표이다.

* 더 쉬운 관리 == mysql확장 소스코드는 다소복잡하고 조잡한했다. 다양한 특징을 요구하는 mysql의 기능에서 주요 개선은 클라이언트 라이브러리의 버전에 기반하여 사용가능하거나 사용불가능하게 되는 것이다.
* 보다 높은 호환성 == mysql 클라이언트 라이브러리에 보다 가깝게 편하기위해 필요했다. 그래서 그 앞으로 개선될 라이브러리에 대해 php 보다 쉽게 지원될 수 있다.
* 이전 버전과의 호환성 == 비록 mysql확장과 mysqli 사이에 호환성은 완벽하지 않다. 노력은 mysql확장에서 mysqli확장으로 간단히 포팅하는 쪽으로 할 것이다.

추가적으로, 이 확장은 추가적인 추적(additional tracing)와 디버깅, 로드 발란싱(load balancing), 복제기능을 지원한다.

---------------------------왜 바꾸는가?

Mysql 4.1+의 새로운 접근을 넘어서, 모든 사람이 mysqli확장을 사용하기를 원하는 이유가 무엇인가?

위에 언급한 기능에 추가로, mysqli 확장의  다음과 같은 장점이 있다.

* 놀라운 속도 == Ext/mysql과 비교하여 특정 조건에서는 40 가량 빠르다.
* 안전한 보안 == mysql RDBMS의 이전 버전에서 네트워크로부터 약간 패스워드 해쉬를 뽑아내기 위한 공격의 가능성이 존재했고, 그 다음에 사용자 패스워드를 재생성했다. 새로운 인증 절차는 보다 강하고 SSH같은 안전한 인증 절차이다.

--------------------------경고와 의외성

mysqli의 어떤 관점은 이전 확장과 아주 다르다.

* 디폴트 테이터베이스 커넥션 == 만약 여러분이 명시된 케녁션을 서버에 하지 않으면, mysqli는 여러분을 위해 접속하지 않을 것이다.
* 디폴트 링크 == 여러분이 사용하기 원하는 데이터베이스 서버 커넥션은 여러분이 절차 문맥을 통해 mysqli 확장을 사용할 때 명시적으로 참조될 것이다.

이 기사는 ‘world’ 테이터베이스를 사용하며 www.mysql.com에서 다운받을 수 있다.

-------------------기본적인 사용

여기 mysql 서버에 접속하고, 연결된 접속을 사용하여 서버에 쿼리문을 보내고 쿼리 결과를 보여주고 커넥션을 닫는 간단한 예제가 있다.

<?php

/* MySQL server에 접속 */
$link = mysqli_connect(
           'localhost',  /* The host to connect to */
           'user',       /* The user to connect as */
           'password',   /* The password to use */
           'world');     /* The default table to query */

if (!$link) {
  printf("Can't connect to MySQL Server. Errorcode: %s\n", mysqli_connect_error());
  exit;
}

/* 쿼리문을 서버에 보낸다 */
if ($result = mysqli_query($link, 'SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5')) {

   print("매우 큰 도시:\n");

   /* 쿼리문을 불러들이기 */
   while( $row = mysqli_fetch_assoc($result) ){
       printf("%s (%s)\n", $row['Name'], $row['Population']);
   }

   /* 결과를 파괴하고 그것에 사용된 메모리를 풀어준다 */
   mysqli_free_result($result);
}

/* 접속 닫기 */
mysqli_close($link);
?>

위의 스크립트는 다음과 같이 보여준다.

Very large cities are:

Mumbai (Bombay) (10500000)
Seoul (9981619)
S&atilde;o Paulo (9968485)
Shanghai (9696300)
Jakarta (9604900)

코드가 보여주듯이, mysqli확장과 mysql확장은 아주 비슷하다. 다만 주요 차이점은 절차적인 방식을 사용할 때, 약간 장황하다.

에러 체크를 없이, 위의 스크립트는 모든 핵심을 실패할 수 있고 에러 메시지를 보여줄 수 있다.

-----------------------객체 지향 인터 페이스 사용하기

* 우리는 명령을 위해 명시적 접속이 필요 치 않다. 접속 정보는 우리의 $mysqli와 $result 객체제 저장되고, 메소드를 호출할 때, 필요로서 접근된다
* fetch_assoc()를 사용하여 결과로부터 쿼리 테이터의 열(row)을 불러(fetching)들일 때, 우리는 그것을 사용하기 위해 명시적인 결과 처리를 하지 않아도 된다. 접속 정보로서, 결과 핸들링은 $result 객체에 저장된다.

<?php

/* MySQL 서버에 접속 */
$mysqli = new mysqli('localhost', 'user', 'password', 'world');

if (mysqli_connect_errno()) {
  printf("MySQL 서버에 접속할 수 없습니다. Errorcode: %s\n", mysqli_connect_error());
  exit;
}

/* 쿼리문을 서버에 보내기 */
if ($result = $mysqli->query('SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5')) {

   print("매우 큰 도시:\n");

   /* Fetch the results of the query */
   while( $row = $result->fetch_assoc() ){
       printf("%s (%s)\n", $row['Name'], $row['Population']);
   }

   /* 결과를 파괴하고 사용된 메모리를 풀어준다 */
   $result->close();
}

/* 접속 닫기 */
$mysqli->close();
?>


-------------------미리준비된 문장(Prepared Statements)

이제 우리는 이 확장의 기본 사용법을 봤다. 새로운 특징을 시험해 보자.

미리준비된 문장은 개발자에게 보다 안전하게 쿼리문을 작성할 수 있고 보다 성능이 높이 작서아기 보다 쉽게 만들어 준다.

그들은 두 양념이 있다.
바운드 파라미터 미리준비된 문장과 바운드 결과 미리준비된 문장.
Bound parameter parepared statements, and bound result prepared statements
(이걸 어떻게 번역해야 할 지, 도저히 감이 않오네요. 이상해도 이해바랍니다.)

--------------------------바운드 파라미터

바운드 파라미터 미리준비된 문장은 쿼리 템플릿을 만들고나서 MySQL 서버에 저장하게 한다. 쿼리문을 만들어야 하고, 템플릿에서 채우기 위한 데이터에서 mysql 서버에 보낸다. 그리고 완전한 쿼리는 형성되고나서 실행된다.

바운드 파라미터 미리 준비된 문장을 사용하고 만드는 기본 과정은 간단하다.

쿼리 템플릿은 만들어지고 mysql 서버에 보내진다. Mysql 서버는 쿼리 템플릿을 받는다. 잘 정의되었는지 검사하고 의미있는지 구분분석하고 특정 버퍼에 저장한다. 그것은 미리준비된 문장을 참고하기 위해 사용될 수 있게 특정 핸들을 반환한다.

만들어야 하는 쿼리가 필요할 때, 템플릿에서 채우기 위한 데이터는 MySQL 서버에 보내지고 완전한 쿼리는 형성되고 나서 실행된다.

이런 과정은 그것을 감싸는 대단히 중요한 동작이다.

쿼리문의 몸체(body)는 단지 MySQL 서버에 보내진다. 쿼리의 실행하기 위한 요청이 있으면, 단지 템플릿에서 채우기위한 대이테가 MySQL 서버에 보내게 되는 것이 필요하다.

쿼리가 실행되는 각각의 시간 대신에, 그런 쿼리문을 검사하고 분석하기 위해 필요한 작업의 대부분은 한번에 되는 것이 필요하다.

추가적으로, 작은 데이터를 포함하는 쿼리를 위해, 쿼리 전송 오버해드는 대단히 줄어든다. 예를 들면, 다음과 같은 쿼리가 있다면..

INSERT INTO City (ID, Name) VALUES (NULL, 'Calgary');

여러분이 쿼리를 실행하고 나서, 여러분은 60이나 그 이상 바이트 대신에, 단지 쿼리 데이터 16 바이트정보 보내는 것이 필요할 것이다.

쿼리를 위한 데이터는 SQL 인젝션 공격(SQL injection attcacks) 존재하지 않기 위해 mysql_real_string()같은 함수를 통하여 넘기는 것이 필요치 않다.

!!!!!!!!!!!!!!용어 설명시작
SQL 인젝션 공격은 데이터가 악의적인 방법으로 기대되지 않은 and/or를 동작하기 위해 쿼리문을 입력할 때 존재한다. 예를 들면 “DELETE FROM grades WHERE class_name=’test_$class’” 처럼 php 스크립트에서 간단한 쿼리를 주면, $class의 변수에 대해 제어 할 수 있는 공격자가, “oops’ or class_name LIKE ‘%’” 처럼 어떤 것을 $class의 변수를 바꿈으로서 발생을 위해 강제로 지울 수 있다.
!!!!!!!!!!!!!!!용어 설명끝

대신에, MySQL 클라이언트와 서버는 미리준비된 문장으로 결속되었을 때, 보낸 데이터가 안전하게 처리될수 있도록 한다.

INSERT INTO City (ID, Name) VALUES (?, ?);

'?' 위치는 literal 데이터를 가진 대부분의 위치에서 사용될 수 있고, 예를 들면from에서 변형 될 수 있다

SELECT Name FROM City WHERE Name = 'Calgary';

위 문장이 아래로 바뀔 수 있다.

SELECT Name FROM City WHERE name = ?;

여기 전체 과정을 설명하기 위한 보다 복잡한 예제가 있다.

<?php
$mysqli = new mysqli('localhost', 'user', 'password', 'world');

/* 접속 체크 */
if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

/* 미리준비된 문장 실행하기 */
$stmt->execute();

printf("%d Row inserted.\n", $stmt->affected_rows);

/* 문장과 접속 닫기 */
$stmt->close();

/* CountryLanguage 테이블을 지운다 */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d Row deleted.\n", $mysqli->affected_rows);

/* 접속 닫기 */
$mysqli->close();
?>

bind_param() 이 처음 파라미터로서 짧은문장이 가지고 있는걸 주목하여라. 이것은 바운드 변수에서 데이터가 어떻게 처리되는지 알려주기 위해 사용되는 형식 문자열이다.

위의 스크립트 경우에서 ‘sssd’는 네번째 파라미터 $percent가 double 또는 float 변수를 포함하는 반면에, 처음 세개의 파라미터 $code, $language, $official가 문자열로서 보내질 것을 지적한다.

!!!!!!!!!용어 설명 시작
계가 bound parameter를 계속 바운드 파라미터라고 번역 했는데, 한글로 번역하자면, 결속 인자라고 할 수 있습니다. Bound는 bind의 과거분사로서 결합된, 결속된, 묶인 그런 의미가 있고, 우리가 포장할 때 상자를 끈으로 묶는(bound)는 의미가 있습니다.
!!!!!!!!!용어설명 끝

bind_param()에서, 각각의 바운드 변수는, 형식 문자열에서, 변수가 어떻게 핸들링되는 명세하는 다른 문자가 될 것이다.

예를 들면

$stmt->bind_param('s', $foo);
$stmt->bind_param('si', $foo, $bar);
$stmt->bind_param('sid', $foo, $bar, $baz);

바인트 타입은 mysqli 확장이 효육성을 위해 그것이 보내는 데이터를 어떻게 인코드(encode)하는지 알게 해준다.

결속(bind) 타입은 매우 간결하다. 데이터는 바운드 변수에서 정수(integer) 변수나 double 변수나 문자열(string)으로서 처리 될것이다.

MySQL 서버에 보내지는 long blobs 같은 특별한 타입도 있다
다음과 같은 데이블은 그 사용을 보여준다.

----------------------------
BIND    |  TYPE COLUMN TYPE
i        |  All INT types
d        |  DOUBLE and FLOAT
b        |  BLOBs
s        | All other types
--------------------------------

---------------------------바운드 결과

바운드 결과 미리준비된 문장은 php에서 가변 변수가 쿼리 결과들에서 데이터 필드 변수에 묶이게 되게 해준다.

이런 결속설정하는 과정이다.

* 쿼리 생성
* 쿼리를 미리준비하게 위해 MySQL 서버에 요청
* 미리준비된 쿼리에서 PHP 변수를 컬럼(columns)에 결속(bind)한다
* 새로운 데이터의 열이 바운드 변수로 적재되도록 요청한다.

여기  이 과정을 상세설명하는 짧은 코드가 있다.

<?php
$mysqli = new mysqli("localhost", "user", "password", "world");

if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

/* 미리 준비된 문장 */
if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country ORDER BY Name LIMIT 5")) {
   $stmt->execute();

   /* 변수를 미리준비된 문장에 결속한다 */
   $stmt->bind_result($col1, $col2);

   /* 값을 불러들인다 */
   while ($stmt->fetch()) {
       printf("%s %s\n", $col1, $col2);
   }

   /* 문장을 닫는다 */
   $stmt->close();
}
/* 접속을 닫는다 */
$mysqli->close();

?>


-----------------------바운드 파라미터와 바운드 결과를 같이 사용하기

이것은 바운드 파라미터와 바운드 결과를 함께 쓰는 법을 설명하는 보다 복잡한 예제이다.


<?php
$mysqli = new mysqli("localhost", "user", "password", "world");

if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

/* 미리 준비된 문장 */
if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country WHERE Code LIKE ? LIMIT 5")) {

   $stmt->bind_param("s", $code);
   $code = "C%";

   $stmt->execute();

   /* 변수를 미리 준비된 문장에 결합, bind variables to prepared statement */
   $stmt->bind_result($col1, $col2);

   /* fetch values */
   while ($stmt->fetch()) {
       printf("%s %s\n", $col1, $col2);
   }

   /* 문장 닫기 */
   $stmt->close();
}
/* 접속 닫기 */
$mysqli->close();

?>
/* 제가 보니 잘만 사용하면 대단히 효율적일 것 같다는 생각이 듭니다. 일종의 템플릿 같은 거라고 생각하시면 될 듯 싶습니다. */

---------------------------요약

이 기사는, 우리가 그 개발 과정의 빠른 요약과 함께 mysqli확장의 특징과 설계의 개요를 제공했다. 여러분은 MySQL의 미리준비된 문장과 그것의 장점을 이해했고 어떻게 사용하는지 알았고 Mysql 확장에 객체지향 인터페이스를 편하게 사용할 수 있을 것이다.

---------------------------번역자에 관하여

집에서 놀고 있는 백수


2006/09/08 10:51 2006/09/08 10:51
이 글에는 트랙백을 보낼 수 없습니다
웅쓰:웅자의 상상플러스
웅자의 상상플러스
전체 (379)
게임 (5)
영화 (2)
기타 (23)
맛집 (5)
영어 (2)
대수학 (3)
형태소 (5)
Hacking (9)
Linux (112)
HTML (48)
Application_developing (48)
Web_developing (102)
Window (11)
«   2024/12   »
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        
  1. 2016/01 (1)
  2. 2015/12 (3)
  3. 2015/10 (3)
  4. 2015/03 (2)
  5. 2015/01 (4)