0%

联合索引的几种情况

天道酬勤,地道酬善,人道酬诚,道酬信,业道酬精。

一、前期准备

  1. 建库 create database if not exists multi_index default charset utf8 collate utf8_general_ci;
  2. 建表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table multi_index_abc(
id int(11) unsigned not null auto_increment,
a char(20) not null default '',
b char(20) not null default '',
c char(20) not null default '',
primary key (id),
key union_index(a,b,c),
created timestamp default current_timestamp
);

create table multi_index_ab(
id int(11) unsigned not null auto_increment,
a char(20) not null default '',
b char(20) not null default '',
c char(20) not null default '',
primary key (id),
key union_index(a,b),
created timestamp default current_timestamp
);
  1. 定义存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
delimiter $$

## 初始化init_multi_index_abc表数据
create procedure init_multi_index_abc()
begin
declare i int;
set i = 1;
while(i<10000)do
insert into multi_index_abc(a,b,c) value(substring(md5(rand()),1,20),substring(md5(rand()),1,20),substring(md5(rand()),1,20));
set i= i+1;
end while;
end$$

## 初始化init_multi_index_ab表数据
create procedure init_multi_index_ab()
begin
declare i int;
set i = 1;
while(i<10000)do
insert into multi_index_ab(a,b,c) value(substring(md5(rand()),1,20),substring(md5(rand()),1,20),substring(md5(rand()),1,20));
set i= i+1;
end while;
end$$
  • 存储过程名后需要加(),即相当于一个自定义函数
  • 定义存储过程前修改下结束符以便执行
  • 声明变量时需要指定变量类型
  1. 执行存储过程
1
2
3
delimiter ;
call init_multi_index_abc();
call init_multi_index_ab();

二、联合索引abc

  1. 通过联合索引abc查询,查询顺序abc

    • 开启资源消耗情况set profiling = on;

    • 查看此选项设置show variables like '%prof%';

    • 开启优化器跟踪set optimizer_trace="enabled=on";

    • 查看此选项设置show variables like 'optimizer_trace';

    • 执行查询语句:select * from multi_index_abc where a='0716e89340d1d57f91b9' and b='1b330994f598890c1ca5' and c='8a4ef874d7dc0a447c49'\G

1
2
3
4
5
6
7
*************************** 1. row ***************************
id: 1
a: 0716e89340d1d57f91b9
b: 1b330994f598890c1ca5
c: 8a4ef874d7dc0a447c49
created: 2019-11-10 00:01:51
1 row in set (0.01 sec)
  • 查看索引情况explain select * from multi_index_abc where a='0716e89340d1d57f91b9' and b='1b330994f598890c1ca5' and c='8a4ef874d7dc0a447c49'\G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: multi_index_abc
partitions: NULL
type: ref
possible_keys: union_index
key: union_index
key_len: 180
ref: const,const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
  • 查看所有执行语句信息show profiles\G
    • 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00202500
Query: show variables like 'optimizer_trace'
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.00012100
Query: set optimizer_trace="enabled=on"
*************************** 3. row ***************************
Query_ID: 3
Duration: 0.00062900
Query: select * from multi_index_abc where a='0716e89340d1d57f91b9' and b='1b330994f598890c1ca5' and c='8a4ef874d7dc0a447c49'
*************************** 4. row ***************************
Query_ID: 4
Duration: 0.00052800
Query: explain select * from multi_index_abc where a='0716e89340d1d57f91b9' and b='1b330994f598890c1ca5' and c='8a4ef874d7dc0a447c49'
4 rows in set, 1 warning (0.00 sec)
  • 查看某个具体query id资源使用情况show profile all for query 3 \G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
*************************** 1. row ***************************
Status: starting
Duration: 0.000103
CPU_user: 0.000094
CPU_system: 0.000010
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: NULL
Source_file: NULL
Source_line: NULL
*************************** 2. row ***************************
Status: checking permissions
Duration: 0.000018
CPU_user: 0.000014
CPU_system: 0.000003
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: check_access
Source_file: sql_authorization.cc
Source_line: 802
*************************** 3. row ***************************
Status: checking permissions
Duration: 0.000007
CPU_user: 0.000004
CPU_system: 0.000003
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: check_access
Source_file: sql_authorization.cc
Source_line: 802
*************************** 4. row ***************************
Status: Opening tables
Duration: 0.000033
CPU_user: 0.000024
CPU_system: 0.000008
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: open_tables
Source_file: sql_base.cc
Source_line: 5714
*************************** 5. row ***************************
Status: init
Duration: 0.000070
CPU_user: 0.000069
CPU_system: 0.000003
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: handle_query
Source_file: sql_select.cc
Source_line: 121
*************************** 6. row ***************************
Status: System lock
Duration: 0.000015
CPU_user: 0.000012
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_lock_tables
Source_file: lock.cc
Source_line: 323
*************************** 7. row ***************************
Status: optimizing
Duration: 0.000058
CPU_user: 0.000054
CPU_system: 0.000004
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 151
*************************** 8. row ***************************
Status: statistics
Duration: 0.000184
CPU_user: 0.000182
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 367
*************************** 9. row ***************************
Status: preparing
Duration: 0.000041
CPU_user: 0.000037
CPU_system: 0.000004
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 475
*************************** 10. row ***************************
Status: executing
Duration: 0.000005
CPU_user: 0.000003
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: exec
Source_file: sql_executor.cc
Source_line: 119
*************************** 11. row ***************************
Status: Sending data
Duration: 0.000077
CPU_user: 0.000076
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: exec
Source_file: sql_executor.cc
Source_line: 195
*************************** 12. row ***************************
Status: end
Duration: 0.000008
CPU_user: 0.000004
CPU_system: 0.000003
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: handle_query
Source_file: sql_select.cc
Source_line: 199
*************************** 13. row ***************************
Status: query end
Duration: 0.000010
CPU_user: 0.000009
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_execute_command
Source_file: sql_parse.cc
Source_line: 4946
*************************** 14. row ***************************
Status: closing tables
Duration: 0.000012
CPU_user: 0.000009
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_execute_command
Source_file: sql_parse.cc
Source_line: 4998
*************************** 15. row ***************************
Status: freeing items
Duration: 0.000027
CPU_user: 0.000013
CPU_system: 0.000014
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 1
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_parse
Source_file: sql_parse.cc
Source_line: 5610
*************************** 16. row ***************************
Status: cleaning up
Duration: 0.000047
CPU_user: 0.000019
CPU_system: 0.000028
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 2
Swaps: 0
Source_function: dispatch_command
Source_file: sql_parse.cc
Source_line: 1924
16 rows in set, 1 warning (0.00 sec)
  • 查看optimizer_traceSELECT trace FROM information_schema.OPTIMIZER_TRACE \GSELECT TRACE INTO outfile '/path/yourfile' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;,后者报错:ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
    • 查看选项配置show variables like 'secure_file_priv';
      • secure_file_prive=null –– 限制mysqld 不允许导入导出
      • secure_file_priv=/path/ —— 限制mysqld的导入导出只能发生在默认的/path/目录下
      • secure_file_priv=’’ —— 不对mysqld 的导入 导出做限制
    • 尝试修改此选项set global secure_file_priv='';,报错ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable,则去修改MySQL配置文件(MySQL5.7在mac下没有此文件,解决方法见下篇【mac下无mysql配置文件my.cnf】)
  • 假设你已经更改secure_file_priv,重新执行SELECT TRACE INTO outfile '/path/yourfile' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;,内容省略

  1. 通过联合索引abc查询,查询顺序cba(acb/bca/bac/cab)

    • 执行查询语句:select * from multi_index_abc where c='8a4ef874d7dc0a447c49' and b='1b330994f598890c1ca5' and a='0716e89340d1d57f91b9'\G
1
2
3
4
5
6
7
*************************** 1. row ***************************
id: 1
a: 0716e89340d1d57f91b9
b: 1b330994f598890c1ca5
c: 8a4ef874d7dc0a447c49
created: 2019-11-10 00:01:51
1 row in set (0.00 sec)
  • 查看索引情况explain select * from multi_index_abc where c='8a4ef874d7dc0a447c49' and b='1b330994f598890c1ca5' and a='0716e89340d1d57f91b9'\G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: multi_index_abc
partitions: NULL
type: ref
possible_keys: union_index
key: union_index
key_len: 180
ref: const,const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
  • 查看所有执行语句信息show profiles\G,同上可拿到query ID

    • 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead
  • 查看某个具体query id资源使用情况show profile all for query 14 \G,内容省略

  • 查看optimizer_traceSELECT trace FROM information_schema.OPTIMIZER_TRACE \GSELECT TRACE INTO outfile '/path/yourfile' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;,内容省略


  1. 通过联合索引abc查询,查询顺序ab,order by c
  • 执行查询语句:select * from multi_index_abc where a='0716e89340d1d57f91b9' and b='1b330994f598890c1ca5' order by c \G
1
2
3
4
5
6
7
*************************** 1. row ***************************
id: 1
a: 0716e89340d1d57f91b9
b: 1b330994f598890c1ca5
c: 8a4ef874d7dc0a447c49
created: 2019-11-10 00:01:51
1 row in set (0.01 sec)
  • 查看索引情况explain select * from multi_index_abc where a='0716e89340d1d57f91b9' and b='1b330994f598890c1ca5' order by c \G
1
2
3
4
5
6
7
8
9
10
11
12
13
           id: 1
select_type: SIMPLE
table: multi_index_abc
partitions: NULL
type: ref
possible_keys: union_index
key: union_index
key_len: 120
ref: const,const
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

Extra: Using index condition,即用到了索引条件下推ICP(Index Condition Pushdown)

  • 查看所有执行语句信息show profiles\G,同上可拿到query ID

    • 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead
  • 查看某个具体query id资源使用情况show profile all for query 22 \G,内容省略

  • 查看optimizer_traceSELECT trace FROM information_schema.OPTIMIZER_TRACE \GSELECT TRACE INTO outfile '/path/yourfile' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;,内容省略

三、联合索引ab

  1. 通过联合索引abc查询,查询顺序ab,order by c
  • 执行查询语句:select * from multi_index_ab where a='5f315b047b87d57bae65' and b='09aaf7fc89949acf3bc1' order by c \G
1
2
3
4
5
6
7
*************************** 1. row ***************************
id: 1
a: 5f315b047b87d57bae65
b: 09aaf7fc89949acf3bc1
c: 86e0687d8a95aa1abdff
created: 2019-11-10 00:05:43
1 row in set (0.00 sec)
  • 查看索引情况explain select * from multi_index_ab where a='5f315b047b87d57bae65' and b='09aaf7fc89949acf3bc1' order by c \G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: multi_index_ab
partitions: NULL
type: ref
possible_keys: union_index
key: union_index
key_len: 120
ref: const,const
rows: 1
filtered: 100.00
Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

Extra: Using index condition;; Using filesort即用到了索引条件下推ICP(Index Condition Pushdown)和排序

  • 查看所有执行语句信息show profiles\G,同上可拿到query ID

    • 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead
  • 查看某个具体query id资源使用情况show profile all for query [ID] \G

  • 查看optimizer_traceSELECT trace FROM information_schema.OPTIMIZER_TRACE \GSELECT TRACE INTO outfile '/path/yourfile' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;,内容省略

四、参考

  1. ICP