0. Long running Query
|
SELECT user, host, db, command, time, state, info FROM information_schema.processlist WHERE time > @@long_query_time AND command != 'Sleep' AND state NOT IN ('Waiting for master to send event', 'Has read all relay log; waiting for the slave I/O thread t', 'Waiting on empty queue', 'Master has sent all binlog to slave; waiting for binlog to be up'); |
0. 사용하는 테이블의 인덱스 정보 확인
|
MariaDB > show indexes from tbl_post ;
MariaDB > SELECT table_name , index_name , seq_in_index , column_name , index_type -> FROM information_schema.statistics -> WHERE table_name = 'tbl_post' ;
+------------+-----------------------------------+--------------+----------------+------------+ | table_name | index_name | seq_in_index | column_name | index_type | +------------+-----------------------------------+--------------+----------------+------------+ | tbl_post | PRIMARY | 1 | id | BTREE | | tbl_post | home_id_2 | 1 | home_id | BTREE | | tbl_post | home_id_2 | 2 | published_date | BTREE | | tbl_post | home_id_3 | 1 | home_id | BTREE | | tbl_post | home_id_3 | 2 | today | BTREE | | tbl_post | home_id_4 | 1 | home_id | BTREE | | tbl_post | home_id_4 | 2 | service_type | BTREE | | tbl_post | home_id_4 | 3 | item_seq | BTREE | | tbl_post | home_id_item_seq | 1 | home_id | BTREE | | tbl_post | home_id_item_seq | 2 | item_seq | BTREE | | tbl_post | home_id_published_date_auth_state | 1 | home_id | BTREE | | tbl_post | home_id_published_date_auth_state | 2 | auth | BTREE | | tbl_post | home_id_published_date_auth_state | 3 | state | BTREE | | tbl_post | home_id_published_date_auth_state | 4 | published_date | BTREE | | tbl_post | home_id_auth_state_folder_id | 1 | home_id | BTREE | | tbl_post | home_id_auth_state_folder_id | 2 | auth | BTREE | | tbl_post | home_id_auth_state_folder_id | 3 | state | BTREE | | tbl_post | home_id_auth_state_folder_id | 4 | folder_id | BTREE | +------------+-----------------------------------+--------------+----------------+------------+ |
1. 플랜 확인
|
MariaDB > EXPLAIN -> SELECT -> home_id, id, created_date, updated_date, published_date, service_type, item_seq, board_no, writer_id, -> writer_name, title, visit_count, reply_count AS comment_count, auth, state, has_object1, has_object2, -> has_object3, has_object4, has_object5, has_object6, has_object7, has_object8, has_object9, has_object10, -> summary -> FROM tbl_post -> WHERE home_id = 'xxxxxx' AND published_date <= NOW() -> AND published_date < '2006-02-06 01:30:00.173000' -> AND state = 0 AND auth >= 1 ORDER BY published_date DESC, id DESC LIMIT 30; +------+-------------+----------+-------+---------------------------------------------------------------------------------------------------------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+-------+---------------------------------------------------------------------------------------------------------------+-----------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_post | range | home_id_2,home_id_3,home_id_4,home_id_item_seq,home_id_published_date_auth_state,home_id_auth_state_folder_id | home_id_2 | 41 | NULL | 40 | Using where | +------+-------------+----------+-------+---------------------------------------------------------------------------------------------------------------+-----------+---------+------+------+-------------+ 1 row in set (0.06 sec)
MariaDB > EXPLAIN -> SELECT -> home_id, id, created_date, updated_date, published_date, service_type, item_seq, board_no, writer_id, -> writer_name, title, visit_count, reply_count AS comment_count, auth, state, has_object1, has_object2, -> has_object3, has_object4, has_object5, has_object6, has_object7, has_object8, has_object9, has_object10, -> summary -> FROM tbl_post /*! USE INDEX(home_id_published_date_auth_state) */ -> WHERE home_id = 'xxxxxx' AND published_date <= NOW() -> AND published_date < '2006-02-06 01:30:00.173000' -> AND state = 0 AND auth >= 1 ORDER BY published_date DESC, id DESC LIMIT 30; +------+-------------+----------+------+-----------------------------------+-----------------------------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+-----------------------------------+-----------------------------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | tbl_post | ref | home_id_published_date_auth_state | home_id_published_date_auth_state | 32 | const | 1202 | Using where; Using filesort | +------+-------------+----------+------+-----------------------------------+-----------------------------------+---------+-------+------+-----------------------------+ |
MariaDB 10.1.0 이상 부터는 EXPLAIN 대신 ANALYZE command를 이용할 수 있다.
Analyze SQL …; 을 수행하면 Optimizer를 통하여 쿼리를 수행 한 후에 쿼리 수행 결과 대신 EXPLAIN 결과를 리턴 해 준다.
rows : 옵티마이저가 테이블로 부터 읽을 것으로 예측한 row의 수
r_rows : 테이블로 부터 읽은 실제 row의 수
filtered : 옵티마이저가 판단한 Where절을 적용 한 후에 남은 row의 %
r_filtered : Where 절을 적용 후 남은 row의 %
Analyze format=json select … 하면 JSON 파일 형식으로 보여 줌.
|
analyze select * from orders, customer where customer.c_custkey=orders.o_custkey and customer.c_acctbal < 0 and orders.o_totalprice > 200*1000 ;
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ | 1 | SIMPLE | customer | ALL | PRIMARY,... | NULL | NULL | NULL | 149095 | 150000 | 18.08 | 9.13 | Using where | | 1 | SIMPLE | orders | ref | i_o_custkey | i_o_custkey | 5 | customer.c_custkey | 7 | 10 | 100.00 | 30.03 | Using where | +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
customer 테이블은 테이블로 부터 읽을 row수를 옵티마이저가 비슷하게 계산을 하였지만, orders 테이블에 대해서는 계산한 것보다 더 많은 row를 읽었다. 통계정보를 갱신할 필요가 있음을 알 수 있다. 오래된 통계정보를 사용하므로 filtered 와 r_filtered 값도 많이 차이가 남.
analyze select * from orders, customer where customer.c_custkey=orders.o_custkey and customer.c_acctbal < -0 and customer.c_comment like '%foo%' orders.o_totalprice > 200*1000 ;
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ | 1 | SIMPLE | customer | ALL | PRIMARY,... | NULL | NULL | NULL | 149095 | 150000 | 18.08 | 0.00 | Using where | | 1 | SIMPLE | orders | ref | i_o_custkey | i_o_custkey | 5 | customer.c_custkey | 7 | NULL | 100.00 | NULL | Using where | +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
orders 테이블 정보 중 r_rows 와 r_filtered 값에 NULL이 있음은, 해당 테이블은 한번도 테이블 엑세스가 발생하지 않았음을 의미한다. r_filtered값이 0 임은 customer의 where 조건에 의해서 추려진 데이터가 없음을 의미한다.
|
2. 실제 처리량 확인
|
Innodb_buffer_pool_read_requests are number of request to read a row from the buffer pool Innodb_buffer_pool_reads is the number of times Innodb has to perform read data from disk to fetch required data pages. innodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001
Full Table Access Handler_read_rnd_next, Handler_read_rnd
Index Access Handler_read_key, Handler_read_next |
|
MariaDB > show indexes from tbl_reply ; +-----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl_reply | 0 | PRIMARY | 1 | id | A | 1455760 | NULL | NULL | | BTREE | | | | tbl_reply | 0 | PRIMARY | 2 | post_id | A | 1455760 | NULL | NULL | | BTREE | | | | tbl_reply | 1 | post_id | 1 | post_id | A | 727880 | NULL | NULL | | BTREE | | | | tbl_reply | 1 | idx_postId_parentId_id | 1 | post_id | A | 727880 | NULL | NULL | | BTREE | | | | tbl_reply | 1 | idx_postId_parentId_id | 2 | parent_id | A | 1455760 | NULL | NULL | YES | BTREE | | | | tbl_reply | 1 | idx_postId_parentId_id | 3 | id | A | 1455760 | NULL | NULL | | BTREE | | | +-----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 6 rows in set (0.08 sec)
MariaDB > explain -> SELECT id, post_id, content FROM tbl_reply WHERE id in (xxxxxxxxx ,yyyyyyyyy,zzzzzzzzz,wwwwwwwww ) ; +------+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_reply | range | PRIMARY | PRIMARY | 4 | NULL | 4 | Using where | +------+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
MariaDB > show session status like 'handle%' ;
| Handler_read_key | 4 | <-- 인덱스 읽은 횟수 | Handler_read_next | 3 | <-- 인덱스로 읽은 행수(?) | Handler_read_rnd | 0 | <-- Table Full Scan으로 읽은 행수(?) | Handler_read_rnd_next | 247 |
MariaDB > SELECT id, post_id, content FROM tbl_reply WHERE id in (xxxxxxxxx ,yyyyyyyyy,zzzzzzzzz,wwwwwwwww ) ; +-----------+--------------------------+---------------------------------------------------------------------+ | id | post_id | content | +-----------+--------------------------+---------------------------------------------------------------------+ | xxxxxxxxx | 465AAC1CEB400341BE1E6401 | [{"type":"text","value":"xxxxxxxxxxxxx"}] | | yyyyyyyyy | 46C264383E000341BE1E6401 | [{"type":"text","value":"yyyyyyyyyyyyy"}] | | zzzzzzzzz | 47D0E61093800341BE1E6401 | [{"type":"text","value":"zzzzzzzzzzzzz"}] | +-----------+--------------------------+---------------------------------------------------------------------+ 3 rows in set (0.00 sec)
MariaDB > show session status like 'handle%' ;
| Handler_read_key | 8 | <-- 인덱스로 읽은 횟수 8-4 = 4 | Handler_read_next | 6 | <-- 인덱스로 읽은 행수 6-3 = 3 | Handler_read_rnd | 0 | <-- Table Full Scan으로 읽은 행수(?) | Handler_read_rnd_next | 247 |
MariaDB > explain -> SELECT id FROM tbl_reply WHERE id in (xxxxxxxxx ,yyyyyyyyy,zzzzzzzzz,wwwwwwwww ) ; +------+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | tbl_reply | range | PRIMARY | PRIMARY | 4 | NULL | 4 | Using where; Using index | +------+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
MariaDB > show session status like 'handle%' ; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +----------------------------+-------+ 24 rows in set (0.00 sec)
MariaDB > SELECT id FROM tbl_reply WHERE id in (xxxxxxxxx ,yyyyyyyyy,zzzzzzzzz,wwwwwwwww ) ; +-----------+ | id | +-----------+ | 409162371 | | 429296696 | | 462514195 | +-----------+ 3 rows in set (0.02 sec)
MariaDB > show session status like 'handle%' ; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_read_key | 4 | | Handler_read_next | 3 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +----------------------------+-------+ 24 rows in set (0.00 sec)
|