質問編集履歴
2
表、クエリが見づらかったため修正
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -34,6 +34,8 @@
|
|
|
34
34
|
|
|
35
35
|
-- 対象のSQL
|
|
36
36
|
|
|
37
|
+
```mysql
|
|
38
|
+
|
|
37
39
|
SELECT msd.groups as p_name,CHAR_LENGTH(msd.groups)+(LENGTH(msd.groups)-CHAR_LENGTH(msd.groups))/2 as namelen, COUNT(*) as cnt,SUM(af.gross) as hassei,msd.disp
|
|
38
40
|
|
|
39
41
|
FROM `mail_settings_detail` msd , af_result af
|
|
@@ -52,6 +54,10 @@
|
|
|
52
54
|
|
|
53
55
|
GROUP BY msd.groups ORDER BY category , disp;
|
|
54
56
|
|
|
57
|
+
```
|
|
58
|
+
|
|
59
|
+
|
|
60
|
+
|
|
55
61
|
|
|
56
62
|
|
|
57
63
|
問題のインデックス：mail_settings_detailのstatus
|
|
@@ -60,47 +66,55 @@
|
|
|
60
66
|
|
|
61
67
|
statusがvisibleの場合の実行計画
|
|
62
68
|
|
|
63
|
-
id
|
|
69
|
+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
|
|
64
70
|
|
|
65
|
-
|
|
71
|
+
|:--|:--:|--:|
|
|
66
72
|
|
|
73
|
+
|1|SIMPLE|af||range|pgid,tag_time,status|tag_time|4||35196|60|Using index condition; Using where; Using temporary; Using filesort|
|
|
74
|
+
|
|
67
|
-
1
|
|
75
|
+
|1|SIMPLE|msd||ref|pgid,status|pgid|4|DB.af.pgid|1|40|Using where|
|
|
68
76
|
|
|
69
77
|
|
|
70
78
|
|
|
71
79
|
statusがinvisibleの場合の実行計画
|
|
72
80
|
|
|
73
|
-
id
|
|
81
|
+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
|
|
74
82
|
|
|
75
|
-
|
|
83
|
+
|:--|:--:|--:|
|
|
76
84
|
|
|
85
|
+
|1|SIMPLE|af||range|pgid,tag_time,status|tag_time|4||35196|60|Using index condition; Using where; Using temporary; Using filesort|
|
|
86
|
+
|
|
77
|
-
1
|
|
87
|
+
|1|SIMPLE|msd||ref|pgid|pgid|4|DB.af.pgid|1|13.33|Using where|
|
|
78
88
|
|
|
79
89
|
|
|
80
90
|
|
|
81
91
|
statusのインデックスを削除した場合の実行計画
|
|
82
92
|
|
|
83
|
-
id
|
|
93
|
+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
|
|
84
94
|
|
|
85
|
-
|
|
95
|
+
|:--|:--:|--:|
|
|
86
96
|
|
|
97
|
+
|1|SIMPLE|msd||ALL|pgid||||651|4|Using where; Using temporary; Using filesort|
|
|
98
|
+
|
|
87
|
-
1
|
|
99
|
+
|1|SIMPLE|af||ref|pgid,tag_time,status|pgid|4|DB.msd.pgid|1178|0.72|Using where|
|
|
88
100
|
|
|
89
101
|
|
|
90
102
|
|
|
91
103
|
対象テーブルのインデックス
|
|
92
104
|
|
|
93
|
-
|
|
105
|
+
```mysql
|
|
94
106
|
|
|
95
|
-
mail_settings_detail
|
|
107
|
+
CREATE INDEX pgid ON mail_settings_detail(pgid);
|
|
96
108
|
|
|
97
|
-
mail_settings_detail
|
|
109
|
+
CREATE INDEX userid ON mail_settings_detail(userid);
|
|
98
110
|
|
|
99
|
-
mail_settings_detail
|
|
111
|
+
CREATE INDEX mail_setid ON mail_settings_detail(mail_setid);
|
|
100
112
|
|
|
101
|
-
mail_settings_detail
|
|
113
|
+
CREATE INDEX status ON mail_settings_detail(status);
|
|
102
114
|
|
|
103
|
-
|
|
115
|
+
```
|
|
116
|
+
|
|
117
|
+
|
|
104
118
|
|
|
105
119
|
|
|
106
120
|
|
1
対象のSQL、実行計画、インデックス、テーブル構造を追記いたしました。
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -26,6 +26,140 @@
|
|
|
26
26
|
|
|
27
27
|
|
|
28
28
|
|
|
29
|
+
追記
|
|
30
|
+
|
|
31
|
+
対象のSQL、実行計画、インデックス、テーブル構造を追記いたしました。
|
|
32
|
+
|
|
33
|
+
|
|
34
|
+
|
|
35
|
+
-- 対象のSQL
|
|
36
|
+
|
|
37
|
+
SELECT msd.groups as p_name,CHAR_LENGTH(msd.groups)+(LENGTH(msd.groups)-CHAR_LENGTH(msd.groups))/2 as namelen, COUNT(*) as cnt,SUM(af.gross) as hassei,msd.disp
|
|
38
|
+
|
|
39
|
+
FROM `mail_settings_detail` msd , af_result af
|
|
40
|
+
|
|
41
|
+
WHERE
|
|
42
|
+
|
|
43
|
+
msd.category IN ('AAA','BBB','CCC','DDD')
|
|
44
|
+
|
|
45
|
+
AND af.tag_time BETWEEN '2020-05-01 00:00:00' AND '2020-05-15 23:59:59'
|
|
46
|
+
|
|
47
|
+
AND msd.pgid = af.pgid
|
|
48
|
+
|
|
49
|
+
AND msd.status = 1
|
|
50
|
+
|
|
51
|
+
AND af.status NOT IN ('XX','YY')
|
|
52
|
+
|
|
53
|
+
GROUP BY msd.groups ORDER BY category , disp;
|
|
54
|
+
|
|
55
|
+
|
|
56
|
+
|
|
57
|
+
問題のインデックス：mail_settings_detailのstatus
|
|
58
|
+
|
|
59
|
+
|
|
60
|
+
|
|
61
|
+
statusがvisibleの場合の実行計画
|
|
62
|
+
|
|
63
|
+
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
64
|
+
|
|
65
|
+
1 SIMPLE af range pgid,tag_time,status tag_time 4 35196 60 Using index condition; Using where; Using temporary; Using filesort
|
|
66
|
+
|
|
67
|
+
1 SIMPLE msd ref pgid,status pgid 4 DB.af.pgid 1 40 Using where
|
|
68
|
+
|
|
69
|
+
|
|
70
|
+
|
|
71
|
+
statusがinvisibleの場合の実行計画
|
|
72
|
+
|
|
73
|
+
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
74
|
+
|
|
75
|
+
1 SIMPLE af range pgid,tag_time,status tag_time 4 35196 60 Using index condition; Using where; Using temporary; Using filesort
|
|
76
|
+
|
|
77
|
+
1 SIMPLE msd ref pgid pgid 4 DB.af.pgid 1 13.33 Using where
|
|
78
|
+
|
|
79
|
+
|
|
80
|
+
|
|
81
|
+
statusのインデックスを削除した場合の実行計画
|
|
82
|
+
|
|
83
|
+
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
84
|
+
|
|
85
|
+
1 SIMPLE msd ALL pgid 651 4 Using where; Using temporary; Using filesort
|
|
86
|
+
|
|
87
|
+
1 SIMPLE af ref pgid,tag_time,status pgid 4 DB.msd.pgid 1178 0.72 Using where
|
|
88
|
+
|
|
89
|
+
|
|
90
|
+
|
|
91
|
+
対象テーブルのインデックス
|
|
92
|
+
|
|
93
|
+
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible
|
|
94
|
+
|
|
95
|
+
mail_settings_detail 0 PRIMARY 1 rowid A 651 BTREE YES
|
|
96
|
+
|
|
97
|
+
mail_settings_detail 1 pgid 1 pgid A 526 BTREE YES
|
|
98
|
+
|
|
99
|
+
mail_settings_detail 1 userid 1 userid A 515 BTREE YES
|
|
100
|
+
|
|
101
|
+
mail_settings_detail 1 mail_setid 1 mail_setid A 102 BTREE NO
|
|
102
|
+
|
|
103
|
+
mail_settings_detail 1 status 1 status A 3 BTREE YES
|
|
104
|
+
|
|
105
|
+
|
|
106
|
+
|
|
107
|
+
statusのインデックスがvisibleの場合の実行時間
|
|
108
|
+
|
|
109
|
+
0.302秒
|
|
110
|
+
|
|
111
|
+
|
|
112
|
+
|
|
113
|
+
statusのインデックスがinvisibleの場合の実行時間
|
|
114
|
+
|
|
115
|
+
0.215秒
|
|
116
|
+
|
|
117
|
+
|
|
118
|
+
|
|
119
|
+
statusのインデックス削除後の場合の実行時間
|
|
120
|
+
|
|
121
|
+
10.115秒
|
|
122
|
+
|
|
123
|
+
|
|
124
|
+
|
|
125
|
+
mail_settings_detailテーブル
|
|
126
|
+
|
|
127
|
+
レコード数：651
|
|
128
|
+
|
|
129
|
+
rowid : int11 primarykey
|
|
130
|
+
|
|
131
|
+
pgid : int11
|
|
132
|
+
|
|
133
|
+
category : varchar30
|
|
134
|
+
|
|
135
|
+
groups : varchar30
|
|
136
|
+
|
|
137
|
+
disp : int11
|
|
138
|
+
|
|
139
|
+
status : int11
|
|
140
|
+
|
|
141
|
+
他、本SQLに出てこないものが6カラム
|
|
142
|
+
|
|
143
|
+
|
|
144
|
+
|
|
145
|
+
af_resultテーブル
|
|
146
|
+
|
|
147
|
+
レコード数：290万
|
|
148
|
+
|
|
149
|
+
retstid : bigint20 primarykey
|
|
150
|
+
|
|
151
|
+
pgid : int11
|
|
152
|
+
|
|
153
|
+
gross : int11
|
|
154
|
+
|
|
155
|
+
tag_time : timestamp
|
|
156
|
+
|
|
157
|
+
status : enum
|
|
158
|
+
|
|
159
|
+
他、本SQLに出てこないものが27カラム
|
|
160
|
+
|
|
161
|
+
|
|
162
|
+
|
|
29
163
|
### 補足情報（FW/ツールのバージョンなど）
|
|
30
164
|
|
|
31
165
|
MySQL 8.0.17
|