平平淡淡从从容容

0%

MySQL各种关键字

发表于 分类于 6.5k 6 分钟

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

一、常用

  1. grant

    • 权限分类

      • 全局权限
      • db权限
      • 表权限

    • 使用

      • 新建一个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'@'%';

  2. group

  3. 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