상상플러스웅자의 상상플러스

1. 테이블 생성
CREATE TABLE `TranTest` (
`num` int(11) NOT NULL auto_increment,
`col01` varchar(32) default NULL,
PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `TranTest2` (
`num` int(11) NOT NULL auto_increment,
`col01` varchar(32) default NULL,
PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 입력 용 프로시저 생성
CREATE PROCEDURE `Prc_TranTest_Input`
(
in in_col01 varchar(32)
)
BEGIN
INSERT INTO TranTest SET col01 = in_col01;
END;

3. SELECT 용 프로시저 생성
CREATE PROCEDURE `Prc_TranTest_Select`
(
in in_col01 varchar(32)
)
BEGIN
SELECT num, col01 FROM TranTest WHERE col01 like concat(in_col01, ‘%’) ;
END;

4. Transaction 용 프로시저 생성
CREATE PROCEDURE `Prc_TranTest_InError`
(
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ”
BEGIN
DECLARE dbErr int default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET dbErr = -1;
START TRANSACTION;
INSERT INTO TranTest SET col01 = ‘1234′;
INSERT INTO TranTest2 SET col012 = ‘1234′; //일부러 에러값을 내기위해 칼럼명을 다르게 적습니다.
IF dbErr < 0 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END;

5. PHP 를 이용하여 SELECT 및 입력하기
$mysqli = new mysqli(”localhost”, “USERID”, “USERPW”, “USERDB”);
if (mysqli_connect_error()) {
printf(”Connect Failed : %s\n”, mysqli_connect_error());
exit;
}
$mysqli->query(”set names utf8″);

//SELECT
if($qry = $mysqli->query(”Call Prc_TranTest_Select(’123′)”)) {
$rs = $qry->fetch_object();
echo $rs->col01;
}

//INSERT TranTest;
$mysqli->query(”Call Prc_TranTest_InError()”);

EXPLAIN 을 사용함으로써 인덱스가 적절히 사용되고 있는지 검토할 수 있다. 인덱스가 잘못 사용되고 있다면 ANALYZE TABLE 을 사용하여 테이블을 점검하라.
이것은 테이블의 상태를 갱신하며 옵티마이저의 동작에 영향을 준다.

옵티마이저가 SELECT 에 기록된 순서대로 조인을 행하게 강제하려면 SELECT 대신에 SELECT STRAIGHT_JOIN 을 사용하라.

EXPLAIN 은 SELECT 문에 사용된 각 테이블당 하나의 행을 리턴한다. 나열된 순서는 MYSQL 이 쿼리처리에 사용하는 순서대로 출력된다.

MYSQL 은 모든 조인을 single-sweep multi-join 방식을 사용하여 해결한다. 이것은 MYSQL 이 첫번째 테이블에서 한행을 읽고, 두번째 테이블에서 매치되는 행을 찾고, 세번째 테이블에서 매치되는 행을 찾고.. 그러한 방식이다. 모든 테이블들이 처리된 후 추출된 컬럼을 출력하고 다시 처음 테이블로 돌아가서 조인을 계속한다. 이런식으로 첫번째 테이블에 더이상 남는행이 없을때까지 실행한다.

(어느것이 첫번째 테이블이 될지는 mysql 옵티마이저가 결정할 문제이다. STRAIGHT_JOIN 을 명시하지 않았다면 유저가 입력한 순서와는 관련이 없다.)

MYSQL 4.1 버전에서 EXPLAIN 의 출력포멧이 UNION 과 subquery, derived table 을 다루기에 더 효과적으로 변경되었다. 무엇보다 중요한 것은 id , select_type 의 두 컬럼이 추가된 것이다.

EXPLAIN 의 각 행은 하나의 테이블에 대한 정보를 보여주며 다음과 같은 컬럼들로 구성된다.

• id

SELECT 번호, 쿼리내의 SELECT 의 구분번호이다.

• select_type

SELECT 의 타입, 다음과 같다.

• SIMPLE

단순 SELECT (UNION 이나 서브쿼리를 사용하지 않음)

• PRIMARY

가장 외곽의 SELECT

• UNION

UNION 에서의 두번째 혹은 나중에 따라오는 SELECT

• DEPENDENT UNION

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

• UNION RESULT

UNION 의 결과물.

• SUBQUERY

서브쿼리의 첫번째 SELECT

• DEPENDENT SUBQUERY

서브쿼리의 첫번째 SELECT, 외곽쿼리에 의존적이다.

• DERIVED

SELECT 로 추출된 테이블 (FROM 절 내부의 서브쿼리)

• table

나타난 결과가 참조하는 테이블명.

• type

조인타입, 아래와 같다. 우수한 순서대로 뒤로갈수록 나쁜 조인형태이다.

• system

테이블에 단 하나의 행만 존재(시스템 테이블). const join 의 특수한 경우이다.

• const

많아야 하나의 매치되는 행만 존재하는 경우. 하나의 행이기 때문에 각 컬럼값은 나머지 연산에서 상수로 간주되며, 처음 한번만 읽어들이면 되기 때문에 무지 빠르다.
PRIMARY KEY 나 UNIQUE index 를 상수와 비교하는 경우.
아래의 경우에서 tbl_name 은 const table 로 조인된다.

SELECT * FROM tb