Mariadb 실행계획

실행된 프로세스 리스트 확인
show processlist;

특정 스레드에서 실행 중인 쿼리의 EXPLAIN 정보 확인

show explain for <thread_id>;

또는 explain 쿼리문

 

explain 스레드 번호 thread_id가 실행 중인 쿼리에 대한 출력을 생성, thread_id 는 show processlist 확인 가능

root@localhost:(none) 09:57:48>show processlist;
+--------+-----------------+---------------------+------+-------------+----------+---------------------------------------------------------------+----------------------------------------------+----------+
| Id     | User            | Host                | db   | Command     | Time     | State                                                         | Info           							    | Progress |
+--------+-----------------+---------------------+------+-------------+----------+---------------------------------------------------------------+----------------------------------------------+----------+
|      3 | event_scheduler | localhost           | NULL | Daemon      | 17855878 | Waiting on empty queue                                        | NULL                                         |    0.000 |
|      6 | repl_user       | 192.168.10.202:33720| NULL | Sleep       |        1 |                                                               | NULL                                         |    0.000 |
|     25 | repl_user       | 192.168.10.201:44238| NULL | Sleep       |        1 |                                                               | NULL                                         |    0.000 |
|     26 | repl_user       | 192.168.10.102:33036| NULL | Binlog Dump | 17855853 | Master has sent all binlog to slave; waiting for more updates | NULL                                         |    0.000 |
|     38 | root            | localhost           | imsi | Query       |   36326  | Sending data                                                  | select * from tbl where no between 10 and 30 |    0.000 |
+--------+-----------------+---------------------+------+-------------+----------+---------------------------------------------------------------+----------------------------------------------+----------+
5 rows in set (0.000 sec)
root@localhost:(none) 10:18:40>show explain for 38;
+------+-------------+-----------------+------+---------------+------+---------+------+-------+------------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows  | Extra      |
+------+-------------+-----------------+------+---------------+------+---------+------+-------+------------+
|   1  | SIMPLE      | tbl             | range| PRIMARY       |    4 | NULL    | NULL |  9552 | using where|
+------+-------------+-----------------+------+---------------+------+---------+------+-------+------------+
1 row in set, 1 warning (0.001 sec)

Note (Code 1003): select * from `tbl` where no between 10 and 30
root@localhost:(none) 10:18:50>

Explain 명령 : 단순히 옵티마이저가 쿼리의 구조와 통계정보를 이용해서 쿼리 실행 정보

shwo explain 명령 : 클라이언트 스레드가 수립하고 실행해 나가고 있는 실행 계획 정보

 

 

 

참조 : real mariadb 책