신문과 프로그래머.

외모가 출중하면 아르바이트를 할 수 있는 사이트가 나왔다.

역시 외모는 중요하구나..

내가 만든 웹페이지중에 엄청나게 속도가 느린 곳이 있었다.

분명 DB에서 SELECT하는데 문제가 있을 것이었다.

문제의 그 쿼리는 3개의 테이블을 JOIN해서 SELECT 하는데, 매우 느렸다. 

아래와 같은 방법을 써서 JOIN 하는 쿼리의 속도를 개선시켰다.

1. 쿼리의 성능을 측정(EXPLAIN)

그 쿼리가 어떻게 돌아가는지 보기 위해 EXPLAIN 을 써보았다.

EXPLAIN

SELECT * FROM 테이블명 WHERE 조건 

다음과 같은 쿼리를 실행하면, 

아래와 같은 항목이 나타난다. 

 id

select type 

table 

type 

possible_keys

key 

key_len 

ref 

extra 

각 항목에 대한 자세한 정보는

http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21444&cat1=7&cat2=217&cat3=227&lang=k 

이곳을 참조 하면 된다.

이중에 유심히 봐야할 정보는 type 이다.

내 경우에는 type이 all 로 나타나고 있었다. 풀스캔 하고있었단 소리..

또한, extra에 using temporary 라든가 여러가지가 나타나면 속도는 굉장히 느려진다.

2. JOIN 쿼리의 최적화 

아래 예제의 테이블을 조인한다고 가정해보자.

A테이블 (이름이 명시된 테이블) 

A_ID(PK) 

NAME 

 1 사자 

 2 고양이 

 3

 신매력 

B테이블 (스킬이 명시된 테이블)

B_ID(PK) 

SKILL 

 1

 얼음꽃 

 2 흐규흐규 

C테이블 (A와 B를 참조하는 관계테이블)

INDEX_01 : A_ID

INDEX_02 : B_ID

A_ID(FK) 

B_ID(FK) 

 1

1 

 1

2 

 2 2 

여기서, A B C 테이블을 조인해서 모든 컬럼을 가져와야한다고 하면 어떤 순서로 조인해야할까?

먼저 A테이블과 C를 조인한다고 하면..

SELECT * FROM A 

LEFT JOIN C

ON A.A_ID = C.A_ID   가 될텐데,

이렇게 하면 ALL 이 될 것이다.

왜냐>??

A테이블은 C에 없는 A_ID 행들을 쫙 가지고있다.

A기준으로 찾는다고 생각을 해보면, A테이블을 다 뒤져보게 되는 것이다.

그래서 반대로 조회를 해야한다.

SELECT * FROM C 

LEFT JOIN A

ON C.A_ID = A.A_ID

더구나 C 테이블의 C.A_ID는 인덱스까지 잡혀있다.

EXPLAIN으로 나온 type중에 index로 나와도 ALL 다음으로 최악이지만

ALL보다는 훨씬 빠르다.. (using temporary 등등 그런게 없어진다)

그래서 세개를 다 합친 쿼리는 대충 아래와 같이 된다.

SELECT * FROM C

LEFT JOIN A

ON C.A_ID = A.A_ID

LEFT JOIN B

ON C.B_ID = B.B_ID 

3. STRAIGHT_JOIN

EXPLAIN으로 봤더니 또 type이 ALL이었다...

그 이유는 MySql 에서 내부적으로 join 순서를 바꿔서 실행하기 때문이다.(mysql 속의 통계 디비를 참조하여 실행된다고 함)

그래서.. 저 명시된 순서로 조인을 하기 위해서는 STRAIGHT_JOIN을 사용해야한다.

SELECT STRAIGHT_JOIN * FROM C

LEFT JOIN A

ON C.A_ID = A.A_ID

LEFT JOIN B

ON C.B_ID = B.B_ID  

* 참고

나의 경우는 저렇게 하고도 또 ALL이 두번 나타났는데, 

그 이유는...

1. ORDER BY 없이 LIMIT 를 거는 행위를 해서 ㅠㅠ

(어느 컬럼 기준인지 모르므로 풀스캔을 하게 되었다)

2. ORDER BY 하는 컬럼의 기준 때문이었다. 

저 위에 예제 테이블에서 A 테이블이나 B테이블 기준으로 ORDER BY를 하면 

모든 곳이 탐색될 것이다..

$(document).ready(function() {

$(".telnumber").keyup(function(){

$(this).val($(this).val().replace(/[^0-9]/g,""));

});

$(".name").keyup(function(){

$(this).val($(this).val().replace(/[^\!-z]/g,""));

});

});

1. user를 등록하는데 모든권한을 N 로 해서 추가합니다.

mysql> select * from user;
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host          | User     | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

      |          |            |             |              |             0 |           0 |               0 |
| localhost     | freekang | password| N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                |          |            |             |              |             0 |           0 |               0 |
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

2. 해당 유저의 권한설정이 제대로 되어있는지 확인합니다.

[root@test229 root]# mysql -ufreekang -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.9-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 
mysql> 
mysql> show databases;
Empty set (0.00 sec)

mysql> quit
Bye

3. root로 로그인하여 해당 테이블에 대한 권한을 설정합니다.

[root@test229 root]# mysql -uroot -p    
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.1.9-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| MIDAS    |
| myphp    |
| mysql    |
+----------+
3 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into tables_priv values('%','mysql','freekang','user','','','Select','Select');

--> user table에 대해서 select권한만 주도록 설정했습니다.

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

4. 해당 유저로 로긴하여 권한부여가 제대로 됐는지 확인합니다.

[root@test229 root]# mysql -ufreekang -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.1.9-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql      |
+----------+

-> 2번에서 아무것도 보이지 않던것에 비하여 현재는 mysql DB가 보입니다.

1 row in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| user                |
+-----------------+
1 row in set (0.00 sec)

-> mysql의 테이블중 user테이블만 보이고 있습니다.

mysql> select * from user;
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host          | User     | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

| localhost     | freekang | *B648CC8E9ED597C6394246A09FCD6C8DCF9A2E34 | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                |          |            |             |              |             0 |           0 |               0 |
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

-> select query에 대해서 정상적으로 실행되고 있습니다.

mysql> INSERT INTO `user` VALUES ('localhost','seokjoo','ajf;dljsdsfl;jowiurel;asjdlfjlas;djfl;adsjf;lasdjfasl;fj

34','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0);
ERROR 1142 (42000): INSERT command denied to user 'freekang'@'localhost' for table 'user'
mysql> delete from user where User="kkang";                                                 
ERROR 1142 (42000): DELETE command denied to user 'freekang'@'localhost' for table 'user'
mysql>

-> 권한을 주지않은 insert와 delete에 대해서는 command denied 라는 말이 나오는걸 보니 적용된거 같네요.^^

MySQL flush privileges 명령어

INSERT나 UPDATE, DELETE문을 이용해서 MySQL의 사용자를 추가,삭제하거나, 사용자 권한 등을 변경하였을 때, MySQL에 변경사항을 적용하기 위해서 사용하는 명령어가 flush privileges 입니다.

이 flush privileges 는 정확히 말하면 grant 테이블을 reload 함으로 변경사항을 바로 적용해주는 명령어인데, INSERT, UPDATE와 같은 SQL문이 아닌 grant 명령어를 사용해서 사용자를 추가하거나 권한등을 변경하였다면 굳이 실행할 필요가 없습니다.

flush privileges 명령어처럼 grant 테이블을 reload 하는 명령어로 mysqladmin reload 나 

mysqlamdin flush-privileges 명령어가 있는데 차이라면 "mysqladmin 명령어"이므로 쉘 프롬프트에서 사용하여야 한다는 것과 비밀번호가 있을 경우 마지막 예와 같이 조금 번거로울 수 있다는 것입니다.

이제는 개인 능력이 상품화 될 수 있는 창구가 점점 늘어나는 듯 싶다.

http://memorystory.net/bbs/board.php?bo_table=M03S06&wr_id=387

-- 이벤트 목록 보기

SELECT * FROM information_schema.EVENTS

또는

SHOW EVENTS ;

-- 등록 되어 있는 이벤트 내용 보기 

SHOW CREATE EVENT `이벤트명` ;

-- 등록되어 있는 이벤트 수정 하기

ALTER EVENT `이벤트명`

ON SCHEDULE EVERY 1 MONTH STARTS '2014-05-27 01:00:00' ;

-- 등록되어 있는 이벤트 삭제 하기 

DROP event `이벤트명` ;

배열을 URL GET 변수로 만들어 주는 함수 - http_build_query   PHP  

이런 자잘한 함수를 알면 시간이 절약되고 코드가 깔끔해 진다. 특히 자잘한 함수들에 자잘하게 신경쓸 게 많은 경우 그렇다.

코드를 한 번 보자.

<a target="_blank" href="http://twitter.com/share?text=<?php echo urlencode('[맑시즘 2011]'.$page_title)?>&url=http://<?php echo $_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI']?>&via=marxismTwit" title="트위터로 퍼가요">

이걸 이렇게 만들 수 있다.

$twitter_query_array = array(

    'text'=>'[맑시즘 2011]'.$page_title,

    'url'=>'http://'.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'],

    'via'=>'marxismTwit'

);

$twitter_query = http_build_query($twitter_query_array);

<a target="_blank" href="http://twitter.com/share?<?php echo $twitter_query?>" title="트위터로 퍼가요">

GET 변수를 직접 쓰는 방식이 아니라 array로 만든 후 http_build_query 함수를 이용해 변환하는 방식을 사용하면 장점이 있다.

일단, 위에서 볼 수 있는대로 URL 길이가 줄어든다.

다음으로, 변수와 값의 쌍이 1줄에 1개씩 들어가는 것으로 관리되므로, SVN 같은 버전관리 시스템에서 관리하기 용이하다.

알아보기 쉬움은 물론이다.

마지막으로 한글이나 특수문자가 들어가는 경우 urlencode 함수를 사용해 줘야 하는데 알아서 변환해 준다.

accept="image/*" 옵션 사용시 갤러리등 이미지 선택 어플 동작.

capture="camera" 옵션 추가시 카메라 촬영 동작.