MySQL 쿼리 튜닝 하다가 생각 한번 해봐야 하는 경우
--
--
--
SELECT DISTINCT
placement_id
, zone_id
FROM ox_placement_zone_assoc assoc
WHERE EXISTS(SELECT 1
FROM ox_campaigns m INNER JOIN ox_clients c
ON m.clientid = c.clientid AND c.block_status = 'pass'
WHERE
m.status = 0 AND m.block_status = 'pass' AND m.deliverable <= 1 AND assoc.placement_id = m.campaignid)
;
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
| 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 6728 | Using where; Using temporary |
| 1 | PRIMARY | m | ref | PRIMARY,ox_campaigns_clientid | ox_campaigns_clientid | 3 | OPENX_V1_ADMIN.c.clientid | 3 | Using where |
| 1 | PRIMARY | assoc | ref | ox_placement_zone_assoc_placement_id | ox_placement_zone_assoc_placement_id | 4 | OPENX_V1_ADMIN.m.campaignid | 78 | Using index |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
+------------------------------+------------+
| Status | Duration |
+------------------------------+------------+
| starting | 0.000070 |
| checking permissions | 0.000014 |
| checking permissions | 0.000012 |
| checking permissions | 0.000012 |
| Opening tables | 0.000034 |
| After opening tables | 0.000016 |
| System lock | 0.000014 |
| Table lock | 0.000017 |
| init | 0.000037 |
| optimizing | 0.000043 |
| statistics | 0.000040 |
| preparing | 0.000032 |
| executing | 0.000012 |
| Creating tmp table | 0.000037 |
| Copying to tmp table | 1.089634 |
| converting HEAP to Aria | 0.121867 |
| Creating index | 0.000091 |
| Repair by sorting | 0.912552 |
| Saving state | 0.000058 |
| Creating index | 0.000014 |
| converting HEAP to Aria | 0.006953 |
| Copying to tmp table on disk | 145.988661 | -- 메모리의 임시 테이블을 디스크로 내리면서 걸리는 시간 이 대부분
| Sending data | 1.860387 |
| end | 0.000047 |
| removing tmp table | 0.062840 |
| end | 0.000021 |
| query end | 0.000021 |
| closing tables | 0.000015 |
| Unlocking tables | 0.000031 |
| freeing items | 0.000028 |
| updating status | 0.000026 |
| cleaning up | 0.000041 |
+------------------------------+------------+
-- 현재 인스턴스의 메모리 사용 부분
# CACHES AND LIMITS #
tmp_table_size = 64M
max_heap_table_size = 64M
# 수정
tmp_table_size = 512M
max_heap_table_size = 512M
####################
개발
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
| 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 4668 | Using where; Using temporary |
| 1 | PRIMARY | m | ref | PRIMARY,ox_campaigns_clientid | ox_campaigns_clientid | 3 | OPENX_V1_ADMIN.c.clientid | 3 | Using where |
| 1 | PRIMARY | assoc | ref | ox_placement_zone_assoc_placement_id | ox_placement_zone_assoc_placement_id | 4 | OPENX_V1_ADMIN.m.campaignid | 76 | Using index |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
| 1 | PRIMARY | m | ref | PRIMARY,ox_campaigns_clientid,ox_campaigns_status | ox_campaigns_status | 6 | const,const | 2728 | Using index condition; Using where; Using temporary |
| 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 3 | OPENX_V1_ADMIN.m.clientid | 1 | Using where |
| 1 | PRIMARY | assoc | ref | ox_placement_zone_assoc_placement_id | ox_placement_zone_assoc_placement_id | 4 | OPENX_V1_ADMIN.m.campaignid | 76 | Using index |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
##################
delivery2@localhost:OPENX_V1_ADMIN 12:21:04>select status, block_status, count(*)
-> from ox_campaigns
-> group by 1, 2
->
-> ;
+--------+--------------+----------+
| status | block_status | count(*) |
+--------+--------------+----------+
| 0 | | 166 |
| 0 | pass | 5400 | -- 정의역
| 0 | block | 12060 |
| 2 | | 1 |
| 2 | pass | 36 |
| 2 | block | 20 |
| 3 | | 112 |
| 3 | pass | 20078 |
| 3 | block | 18450 |
| 4 | pass | 6 |
+--------+--------------+----------+
-- ox_campaigns index 구성
+--------------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ox_campaigns | 0 | PRIMARY | 1 | campaignid | A | 50058 | NULL | NULL | | BTREE | | |
| ox_campaigns | 1 | ox_campaigns_clientid | 1 | clientid | A | 16686 | NULL | NULL | | BTREE | | |
| ox_campaigns | 1 | ox_campaigns_campaign_group_id | 1 | campaign_group_id | A | 32 | NULL | NULL | YES | BTREE | | |
+--------------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
-- 인덱스 생성
-- 그러나 status 값이 변동이 빈번 할 수 있음
-- (2차)
alter table ox_campaigns
add key if not exists ox_campaigns_status (status, block_status) using BTREE ;
--
-- 개발 플랜
--
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
| 1 | PRIMARY | m | ref | PRIMARY,ox_campaigns_clientid,ox_campaigns_status | ox_campaigns_status | 6 | const,const | 2728 | Using index condition; Using where; Using temporary |
| 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 3 | OPENX_V1_ADMIN.m.clientid | 1 | Using where |
| 1 | PRIMARY | assoc | ref | ox_placement_zone_assoc_placement_id | ox_placement_zone_assoc_placement_id | 4 | OPENX_V1_ADMIN.m.campaignid | 76 | Using index |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
--
-- 최종안
--
set global tmp_table_size = 512 * 1024 * 1024;
set global max_heap_table_size = 512 * 1024 * 1024;
Copying to tmp table on disk 사라짐
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000079 |
| checking permissions | 0.000015 |
| checking permissions | 0.000014 |
| checking permissions | 0.000015 |
| Opening tables | 0.000036 |
| After opening tables | 0.000016 |
| System lock | 0.000016 |
| Table lock | 0.000018 |
| init | 0.000040 |
| optimizing | 0.000045 |
| statistics | 0.000043 |
| preparing | 0.000036 |
| executing | 0.000015 |
| Creating tmp table | 0.000038 |
| Copying to tmp table | 5.617840 |
| Sending data | 1.745527 |
| end | 0.000019 |
| removing tmp table | 0.024801 |
| end | 0.000015 |
| query end | 0.000015 |
| closing tables | 0.000013 |
| Unlocking tables | 0.000018 |
| freeing items | 0.000020 |
| updating status | 0.000020 |
| cleaning up | 0.000019 |
+----------------------+----------+
댓글 없음:
댓글 쓰기