0%

MySQL各种关键字

权限管理,一般指根据系统设置的安全规则或者安全策略,用户可以访问而且只能访问自己被授权的资源,不多不少。权限管理几乎出现在任何系统里面,只要有用户和密码的系统。很多人常将“用户身份认证”、“密码加密”、“系统管理”等概念与权限管理概念混淆。

一、常用

  1. grant

    • 权限分类
      • 全局权限
      • db权限
      • 表权限
  2. 使用

    • 新建一个sshuser用户,密码123456 create user 'sshuser'@'%' identified by '123456';

    • 授予权限

      • 全局权限(生产环境一般不推荐) grant all privileges on *.* to 'sshuser'@'%' with grant option;
      • db权限 grant all privileges on db1.* to 'sshuser'@'%' with grant option;
      • 表权限 grant all privileges on db1.table_name to 'sshuser'@'%' with grant option;GRANT SELECT, INSERT ON db1.table_name TO 'sshuser'@'%' with grant option;
      • 列权限 GRANT SELECT(id), INSERT (id,a) ON db1.table_name TO 'sshuser'@'%' with grant option;
    • 收回权限 revoke all privileges on *.* from 'sshuser'@'%';

  3. group

  4. in和exists

    • 定义表
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    CREATE TABLE ie_table_in (
    id int(11) unsigned NOT NULL AUTO_INCREMENT,
    name char(20) NOT NULL DEFAULT '',
    phone char(11) NOT NULL DEFAULT '',
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

    CREATE TABLE ie_table_exist (
    id int(11) unsigned NOT NULL AUTO_INCREMENT,
    name char(20) NOT NULL DEFAULT '',
    phone char(11) NOT NULL DEFAULT '',
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    • 定义存储过程
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    delimiter $$

    create procedure init_in_exist()

    begin
    declare i int;
    set i=0;
    while(i<10000)do
    insert into ie_table_in(name,phone) value(substring(md5(rand()),1,20),concat('1',ceiling(rand()*9000000000+1000000000)));
    insert into ie_table_exist(name,phone) value(substring(md5(rand()),1,20),concat('1',ceiling(rand()*9000000000+1000000000)));
    set i=i+1;
    end while;
    end$$

    delimiter ;
    • 调用存储过程call init_in_exist();

in

  • 执行查询

    1
    2
    3
    4
    5
    select * from ie_table_in t1
    where t1.id in (
    select id from ie_table_exist t2
    where t1.id=t2.id
    );
  • 查看执行计划explain select * from ie_table_in t1 where t1.id in (select id from ie_table_exist t2 where t1.id=t2.id)\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
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: t2
    partitions: NULL
    type: index
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 4
    ref: NULL
    rows: 9863
    filtered: 100.00
    Extra: Using index
    *************************** 2. row ***************************
    id: 1
    select_type: SIMPLE
    table: t1
    partitions: NULL
    type: eq_ref
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 4
    ref: exec.t2.id
    rows: 1
    filtered: 100.00
    Extra: NULL
    2 rows in set, 2 warnings (0.00 sec)
  • 同样可开启profiling和optimizer_trace

    • show profile all for query [ID];
    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
    274
    275
    276
    277
    278
    279
    280
    281
    282
    283
    284
    285
    286
    287
    288
    289
    290
    291
    292
    293
    294
    295
    296
    297
    298
    299
    300
    301
    302
    303
    304
    305
    306
    307
     *************************** 1. row ***************************
    Status: starting
    Duration: 0.000111
    CPU_user: 0.000101
    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.000011
    CPU_user: 0.000007
    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: check_access
    Source_file: sql_authorization.cc
    Source_line: 802
    *************************** 3. row ***************************
    Status: checking permissions
    Duration: 0.000011
    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: check_access
    Source_file: sql_authorization.cc
    Source_line: 802
    *************************** 4. row ***************************
    Status: checking permissions
    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: check_access
    Source_file: sql_authorization.cc
    Source_line: 802
    *************************** 5. row ***************************
    Status: checking permissions
    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: check_access
    Source_file: sql_authorization.cc
    Source_line: 802
    *************************** 6. row ***************************
    Status: Opening tables
    Duration: 0.000027
    CPU_user: 0.000025
    CPU_system: 0.000001
    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
    *************************** 7. row ***************************
    Status: init
    Duration: 0.000168
    CPU_user: 0.000113
    CPU_system: 0.000056
    Context_voluntary: 0
    Context_involuntary: 0
    Block_ops_in: 0
    Block_ops_out: 0
    Messages_sent: 0
    Messages_received: 0
    Page_faults_major: 1
    Page_faults_minor: 0
    Swaps: 0
    Source_function: handle_query
    Source_file: sql_select.cc
    Source_line: 121
    *************************** 8. row ***************************
    Status: System lock
    Duration: 0.000015
    CPU_user: 0.000012
    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: mysql_lock_tables
    Source_file: lock.cc
    Source_line: 323
    *************************** 9. row ***************************
    Status: optimizing
    Duration: 0.000036
    CPU_user: 0.000033
    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: 151
    *************************** 10. row ***************************
    Status: statistics
    Duration: 0.000113
    CPU_user: 0.000111
    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
    *************************** 11. row ***************************
    Status: preparing
    Duration: 0.000034
    CPU_user: 0.000031
    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: optimize
    Source_file: sql_optimizer.cc
    Source_line: 475
    *************************** 12. row ***************************
    Status: executing
    Duration: 0.000005
    CPU_user: 0.000003
    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: exec
    Source_file: sql_executor.cc
    Source_line: 119
    *************************** 13. row ***************************
    Status: Sending data
    Duration: 0.019846
    CPU_user: 0.019599
    CPU_system: 0.000246
    Context_voluntary: 0
    Context_involuntary: 0
    Block_ops_in: 0
    Block_ops_out: 0
    Messages_sent: 75
    Messages_received: 0
    Page_faults_major: 0
    Page_faults_minor: 0
    Swaps: 0
    Source_function: exec
    Source_file: sql_executor.cc
    Source_line: 195
    *************************** 14. row ***************************
    Status: end
    Duration: 0.000011
    CPU_user: 0.000004
    CPU_system: 0.000007
    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
    *************************** 15. row ***************************
    Status: query end
    Duration: 0.000009
    CPU_user: 0.000009
    CPU_system: 0.000001
    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
    *************************** 16. row ***************************
    Status: closing tables
    Duration: 0.000011
    CPU_user: 0.000010
    CPU_system: 0.000001
    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
    *************************** 17. row ***************************
    Status: freeing items
    Duration: 0.000017
    CPU_user: 0.000012
    CPU_system: 0.000004
    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
    *************************** 18. row ***************************
    Status: cleaning up
    Duration: 0.000016
    CPU_user: 0.000016
    CPU_system: 0.000000
    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: dispatch_command
    Source_file: sql_parse.cc
    Source_line: 1924
    18 rows in set, 1 warning (0.01 sec)
  • SELECT TRACE INTO outfile '/Users/liuyulong/mysql_trace/in.log' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

exists

  • 执行查询

    1
    2
    3
    4
    5
    select * from ie_table_in t1
    where exists(
    select id from ie_table_exist t2
    where t1.id=t2.id
    );
  • 查看执行计划explain select * from ie_table_in t1 where exists(select id from ie_table_exist t2 where t1.id=t2.id)\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
    *************************** 1. row ***************************
    id: 1
    select_type: PRIMARY
    table: t1
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 9890
    filtered: 100.00
    Extra: Using where
    *************************** 2. row ***************************
    id: 2
    select_type: DEPENDENT SUBQUERY
    table: t2
    partitions: NULL
    type: eq_ref
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 4
    ref: exec.t1.id
    rows: 1
    filtered: 100.00
    Extra: Using index
    2 rows in set, 2 warnings (0.00 sec)

对比

  • show profiles\G

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    *************************** 6. row ***************************
    Query_ID: 6
    Duration: 0.01895000
    Query: select * from ie_table_in t1
    where t1.id in (
    select id from ie_table_exist t2
    where t1.id=t2.id
    )
    *************************** 7. row ***************************
    Query_ID: 7
    Duration: 0.05620200
    Query: select * from ie_table_in t1
    where exists(
    select id from ie_table_exist t2
    where t1.id=t2.id
    )

在内外表数据量相同的情况下,发现in语句反而比exists快

in语句

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
mysql> show profile all for query 6\G
*************************** 1. row ***************************
Status: starting
Duration: 0.000118
CPU_user: 0.000108
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.000008
CPU_user: 0.000003
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: check_access
Source_file: sql_authorization.cc
Source_line: 802
*************************** 3. row ***************************
Status: checking permissions
Duration: 0.000006
CPU_user: 0.000005
CPU_system: 0.000001
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.000214
CPU_user: 0.000126
CPU_system: 0.000088
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: 4
Swaps: 0
Source_function: open_tables
Source_file: sql_base.cc
Source_line: 5714
*************************** 5. row ***************************
Status: init
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: handle_query
Source_file: sql_select.cc
Source_line: 121
*************************** 6. row ***************************
Status: System lock
Duration: 0.000013
CPU_user: 0.000011
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.000016
CPU_user: 0.000015
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: 151
*************************** 8. row ***************************
Status: statistics
Duration: 0.000038
CPU_user: 0.000036
CPU_system: 0.000001
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.000020
CPU_user: 0.000018
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: 475
*************************** 10. row ***************************
Status: executing
Duration: 0.000004
CPU_user: 0.000002
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.018410
CPU_user: 0.016988
CPU_system: 0.001308
Context_voluntary: 0
Context_involuntary: 26
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 75
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 327
Swaps: 0
Source_function: exec
Source_file: sql_executor.cc
Source_line: 195
*************************** 12. row ***************************
Status: end
Duration: 0.000010
CPU_user: 0.000004
CPU_system: 0.000006
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.000007
CPU_user: 0.000006
CPU_system: 0.000001
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.000007
CPU_user: 0.000006
CPU_system: 0.000001
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.000012
CPU_user: 0.000008
CPU_system: 0.000003
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.000009
CPU_user: 0.000009
CPU_system: 0.000001
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: dispatch_command
Source_file: sql_parse.cc
Source_line: 1924
  • exists语句

    1
    大量的executing和Sending data
  1. order

    全字段排序

    • 初始化表
    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE `t` (
    `id` int(11) NOT NULL,
    `city` varchar(16) NOT NULL,
    `name` varchar(16) NOT NULL,
    `age` int(11) NOT NULL,
    `addr` varchar(128) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `city` (`city`)
    ) ENGINE=InnoDB;
    • 查询 select city,name,age from t where city='杭州' order by name limit 1000;

    • 查看执行计划 explain select city,name,age from t where city='杭州' order by name limit 1000;

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
               id: 1
    select_type: SIMPLE
    table: table_order
    partitions: NULL
    type: ref
    possible_keys: city
    key: city
    key_len: 50
    ref: const
    rows: 1
    filtered: 100.00
    Extra: Using index condition; Using filesort
    • Extra这个字段中的“Using filesort”表示的就是需要排序,MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer。

    city字段索引图

    • 从图中可以看到,满足 city=’杭州’条件的行,是从 ID_X 到 ID_(X+N) 的这些记录。
    • 通常情况下,这个语句执行流程如下所示 :
      • 初始化 sort_buffer,确定放入 name、city、age 这三个字段;
      • 从索引 city 找到第一个满足 city=’杭州’条件的主键 id,也就是图中的 ID_X;
      • 到主键 id 索引取出整行,取 name、city、age 三个字段的值,存入 sort_buffer 中;
      • 从索引 city 取下一个记录的主键 id;
      • 重复步骤 3、4 直到 city 的值不满足查询条件为止,对应的主键 id 也就是图中的 ID_Y;
      • 对 sort_buffer 中的数据按照字段 name 做快速排序;
      • 按照排序结果取前 1000 行返回给客户端。

我们暂且把这个排序过程,称为全字段排序,执行流程的示意图如下所示,下一篇文章中我们还会用到这个排序。

全字段排序

图中“按 name 排序”这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数 sort_buffer_size。sort_buffer_size,就是 MySQL 为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size,排序就在内存中完成。但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序,也称外部排序。在使用外部排序时,MySQL会分成好几份单独的临时文件用来存放排序后的数据,然后在将这些文件合并成一个大文件

rowid排序

接下来修改一个参数,让 MySQL 采用另外一种算法 SET max_length_for_sort_data = 16;

  • max_length_for_sort_data是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是如果单行的长度超过这个值,MySQL 就认为单行太大,要换一个算法。

  • city、name、age这三个字段的定义总长度是36,把 max_length_for_sort_data 设置为16,我们再来看看计算过程有什么改变。

  • 新的算法放入sort_buffer的字段,只有要排序的列(即name字段)和主键id。

  • 但这时,排序的结果就因为少了 city 和 age 字段的值,不能直接返回了,整个执行流程就变成如下所示的样子:

    • 初始化 sort_buffer,确定放入两个字段,即 name 和 id;
    • 从索引 city 找到第一个满足 city=’杭州’条件的主键 id,也就是图中的 ID_X;
    • 到主键 id 索引取出整行,取 name、id 这两个字段,存入 sort_buffer 中;
    • 从索引 city 取下一个记录的主键 id;
    • 重复步骤 3、4 直到不满足 city=’杭州’条件为止,也就是图中的 ID_Y;
    • 对 sort_buffer 中的数据按照字段 name 进行排序;
    • 遍历排序结果,取前 1000 行,并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

rowid排序

优化

  • 创建联合索引减少排序
  • 使用覆盖索引减少排序
  1. optimize table

  2. analyze table

  3. alter table

  4. explain

    • 引入

      • EXPLAIN显示了MySQL如何使用索引来处理SELECT语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。
      • EXPLAIN命令用法十分简单, 在SQL语句前加上Explain就可以了,如:EXPLAIN SELECT * FROM 数据库.表;
    • 详解

    EXPLAIN SELECT * FROM users;\G结果如下:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
               id: 1
    select_type: SIMPLE
    table: users
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 2
    filtered: 100.00
    Extra: NULL
字段 说明
id 查询序列号(值相同时默认由上而下执行,不同时值越大代表优先级越高,则越先被执行)
select_type 查询类型,详情见下表
table 查询的表
partitions 匹配的分区
type 联接类型
possible_keys 可能选用的索引,如果该列是NULL,则没有相关的索引,这种情况下可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高查询性能
key 实际使用的索引
key_len MySQL决定使用的键长度,如果键是NULL,则长度为NULL
ref 哪个字段或常数与key一起被使用
rows 扫描了多少行
filtered 通过条件过滤出的行数的百分比估计值
Extra 额外的信息
  • id

    • 值为NULL时说明这一行数据是由另外两个SQL语句进行 UNION操作后产生的结果集
    • 值相同时说明SQL执行顺序是按照显示的从上至下执行的
    • 值不同时,值越大代表优先级越高,越先被执行
  • select_type

查询类型 说明
SIMPLE 简单SELECT(不使用UNION或子查询)
PRIMARY 最外面的SELECT,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
UNION UNION中的第二个或后面的SELECT语句
DEPENDENT UNION UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT UNION 的结果
SUBQUERY 子查询中的第一个SELECT
DEPENDENT SUBQUERY 子查询中的第一个SELECT,取决于外面的查询
DERIVED 派生表的SELECT, FROM子句的子查询
UNCACHEABLE SUBQUERY 一个子查询的结果不能被缓存,必须重新评估外链接的第一行
  • type:从上到下性能最佳到最坏
类型 说明
system 表中只有一条数据,这个类型是特殊的const类型
const 针对主键或唯一索引的等值查询扫描,最多只返回一行数据
eq_ref 通常出现在多表的join查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果,并且查询的比较操作通常是=,查询效率较高
ref 通常出现在多表的join查询,针对于非唯一或非主键索引,或者是使用了最左前缀规则索引的查询
ref_or_null 该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行
index_merge 该联接类型表示使用了索引合并优化方法
unique_subquery 该类型替换了下面形式的IN子查询的ref:value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高
index_subquery 该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range 表示使用索引范围查询,通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =,<>,>,>=,<,<=,IS NULL,<=>,BETWEEN,IN() 操作中。当type是range时,那么EXPLAIN输出的ref字段为NULL,并且key_len字段是此次查询使用到的索引的最长的那个
index 表示全索引扫描(full index scan),和ALL类型类似,只不过ALL类型是全表扫描,而index类型则仅仅扫描所有的索引而不扫描数据。index类型通常出现在覆盖索引的情况。
ALL 表示全表扫描,这个类型的查询是性能最差的查询之一
  • extra: 查询的额外信息
类型 说明
Distinct MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #) MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用
Using filesort MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
Using index 覆盖索引
Using where 服务层使用WHERE条件来过滤数据
Using index condition 5.6+引入的新特性索引条件下推(index condition pushdown)
Using temporary 为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using sort_union(…), Using union(…), Using intersect(…) 这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by 类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

using index condition会先过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用WHERE子句中的其他条件去过滤这些数据行

  • 用不到索引
    • 数据类型出现隐式转换
    • 复合索引的情况下,查询条件不满足索引最左的原则
    • Mysql估计使用索引比全表扫描慢
    • 用or分割开的条件,or前条件有索引,or后的列没有索引
    • 负向查询(not,not in,not like,<>,!=,!>,!<)不会使用索引
    • 独立的列索引不能是表达式的一部分
  1. join

    • 构造数据
      • 技术
      • MRR
      • BKA
      • BNL转BKA
    • 在业务上优化
  2. union

    • 建表
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    create table union_one(
    id int(11) unsigned not null primary key auto_increment,
    name varchar(100) not null default '',
    created datetime not null default current_timestamp
    );

    create table union_two(
    id int(11) unsigned not null primary key auto_increment,
    name varchar(100) not null default '',
    created datetime not null default current_timestamp
    );
    • 初始化数据
    1
    2
    3
    4
    5
    6
    7
    8
    insert into union_one(`name`) values ('tom');
    insert into union_one(`name`) values ('tim');
    insert into union_one(`name`) values ('jack');

    insert into union_two(`name`) values ('tom');
    insert into union_two(`name`) values ('tim');
    insert into union_two(`name`) values ('jack');
    insert into union_two(`name`) values ('john');
    • 查询
    • union
    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
    select * from union_one union select * from union_two;

    +----+------+---------------------+
    | id | name | created |
    +----+------+---------------------+
    | 1 | tom | 2019-11-05 17:40:15 |
    | 2 | tim | 2019-11-05 17:40:15 |
    | 3 | jack | 2019-11-05 17:40:15 |
    | 4 | john | 2019-11-05 17:40:15 |
    +----+------+---------------------+
    4 rows in set (0.00 sec)


    explain select * from union_one union select * from union_two\G

    *************************** 1. row ***************************
    id: 1
    select_type: PRIMARY
    table: union_one
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 2
    filtered: 100.00
    Extra: NULL
    *************************** 2. row ***************************
    id: 2
    select_type: UNION
    table: union_two
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 4
    filtered: 100.00
    Extra: NULL
    *************************** 3. row ***************************
    id: NULL
    select_type: UNION RESULT
    table: <union1,2>
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: NULL
    filtered: NULL
    Extra: Using temporary
    3 rows in set, 1 warning (0.00 sec)
    • union all
    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
    select * from union_one union all select * from union_two;
    +----+------+---------------------+
    | id | name | created |
    +----+------+---------------------+
    | 1 | tom | 2019-11-05 17:40:15 |
    | 2 | tim | 2019-11-05 17:40:15 |
    | 3 | jack | 2019-11-05 17:40:15 |
    | 1 | tom | 2019-11-05 17:40:15 |
    | 2 | tim | 2019-11-05 17:40:15 |
    | 3 | jack | 2019-11-05 17:40:15 |
    | 4 | john | 2019-11-05 17:40:15 |
    +----+------+---------------------+
    7 rows in set (0.00 sec)


    explain select * from union_one union all select * from union_two\G

    *************************** 1. row ***************************
    id: 1
    select_type: PRIMARY
    table: union_one
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 2
    filtered: 100.00
    Extra: NULL
    *************************** 2. row ***************************
    id: 2
    select_type: UNION
    table: union_two
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 4
    filtered: 100.00
    Extra: NULL
    2 rows in set, 1 warning (0.00 sec)
    • 对比
    • union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序,效率低
    • union all:对两个结果集进行并集操作,包括重复行,不进行排序,效率高
  3. group by与sql_mode

    • 建表并初始化记录
    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
    CREATE TABLE `student` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) NOT NULL DEFAULT '',
    `phone` char(11) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

    INSERT INTO `student` VALUES (1, '张三', '18812345678');
    INSERT INTO `student` VALUES (2, '李四', '18856781234');
    INSERT INTO `student` VALUES (3, '王五', '18811111111');
    INSERT INTO `student` VALUES (4, '马六', '18811112222');

    CREATE TABLE `score` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `sid` int(11) NOT NULL,
    `score` float(4,2) NOT NULL,
    `subject` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

    INSERT INTO `score` VALUES (1, 1, 80.00, '语文');
    INSERT INTO `score` VALUES (2, 1, 85.00, '数学');
    INSERT INTO `score` VALUES (3, 1, 89.00, '英语');
    INSERT INTO `score` VALUES (4, 2, 50.00, '语文');
    INSERT INTO `score` VALUES (5, 2, 30.00, '数学');
    INSERT INTO `score` VALUES (6, 2, 90.00, '英语');
    INSERT INTO `score` VALUES (7, 3, 60.00, '语文');
    INSERT INTO `score` VALUES (8, 3, 30.00, '数学');
    INSERT INTO `score` VALUES (9, 3, 79.00, '英语');
    INSERT INTO `score` VALUES (10, 4, 70.00, '语文');
    INSERT INTO `score` VALUES (11, 4, 35.00, '数学');
    INSERT INTO `score` VALUES (12, 4, 95.00, '英语');
    • 查询总分排名前三的记录
    1
    2
    3
    4
    5
    select a.id,a.name,sum(b.score) total_score from score b 
    join student a on a.id=b.sid
    group by b.sid
    order by total_score desc
    limit 0,3

  • 尝试以student表为主表关联查询

    1
    2
    3
    4
    5
    select a.id,a.name,sum(b.score) total_score from student a 
    left join score b on a.id=b.sid
    group by b.sid
    order by total_score desc
    limit 0,3

    报错:In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'xiaohe.a.id'; this is incompatible with sql_mode=only_full_group_by

    • 在MySQL5.7.5后,默认开启了ONLY_FULL_GROUP_BY,所以导致了之前的一些SQL无法正常执行。对于GROUP BY聚合操作,如果在SELECT中的列没有在GROUP BY中出现,那么这个SQL是不合法的。传送门

    • sql_mode介绍

      • ONLY_FULL_GROUP_BY:在该模式下,target list中的值要么是来自于聚集函数的结果,要么是来自于group by list中的表达式的值。
      • STRICT_TRANS_TABLES:在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制。
      • NO_ZERO_IN_DATE:在严格模式下,不允许日期和月份为零。
      • NO_ZERO_DATE:设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。
      • ERROR_FOR_DIVISION_BY_ZERO:在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时MySQL返回NULL。
      • NO_AUTO_CREATE_USER:禁止GRANT创建密码为空的用户。
      • NO_ENGINE_SUBSTITUTION:如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常。
      • PIPES_AS_CONCAT:将”||”视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似。
      • ANSI_QUOTES:启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符。
      • NO_AUTO_VALUE_ON_ZERO:该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。
  • 解决方案

    • 方案一、修改配置文件my.cnf,文件最后加入以下设置,重启mysql,永久有效
      sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

    • 方案二、修改当前会话配置,mysql重启后失效

      • 查看当前模式

        1
        2
        3
        show variables like '%sql_mode';
        show session variables like '%sql_mode%';
        show global variables like '%sql_mode%';
      • 修改当前模式

        1
        2
        set global sql_mode='';
        set session sql_mode='';

四、参考

  1. ICP