1MK-QUERY-ADVISOR(1)   User Contributed Perl Documentation  MK-QUERY-ADVISOR(1)
2
3
4

NAME

6       mk-query-advisor - Analyze queries and advise on possible problems.
7

SYNOPSIS

9       Usage: mk-query-advisor [OPTION...] [FILE]
10
11       mk-query-advisor analyzes queries and advises on possible problems.
12       Queries are given either by specifying slowlog files, --query, or
13       --review.
14
15          # Analyzer all queries in the given slowlog
16          mk-query-advisor /path/to/slow-query.log
17
18          # Get queries from tcpdump using mk-query-digest
19          mk-query-digest --type tcpdump.txt --print --no-report | mk-query-advisor
20
21          # Get queries from a general log
22          mk-query-advisor --type genlog mysql.log
23

RISKS

25       The following section is included to inform users about the potential
26       risks, whether known or unknown, of using this tool.  The two main
27       categories of risks are those created by the nature of the tool (e.g.
28       read-only tools vs. read-write tools) and those created by bugs.
29
30       mk-query-advisor simply reads queries and examines them, and is thus
31       very low risk.
32
33       At the time of this release there is a bug that may cause an infinite
34       (or very long) loop when parsing very large queries.
35
36       The authoritative source for updated information is always the online
37       issue tracking system.  Issues that affect this tool will be marked as
38       such.  You can see a list of such issues at the following URL:
39       <http://www.maatkit.org/bugs/mk-query-advisor>.
40
41       See also "BUGS" for more information on filing bugs and getting help.
42

DESCRIPTION

44       mk-query-advisor examines queries and applies rules to them, trying to
45       find queries that look bad according to the rules.  It reports on
46       queries that match the rules, so you can find bad practices or hidden
47       problems in your SQL.  By default, it accepts a MySQL slow query log as
48       input.
49

RULES

51       These are the rules that mk-query-advisor will apply to the queries it
52       examines.  Each rule has three bits of information: an ID, a severity
53       and a description.
54
55       The rule's ID is its identifier.  We use a seven-character ID, and the
56       naming convention is three characters, a period, and a three-digit
57       number.  The first three characters are sort of an abbreviation of the
58       general class of the rule.  For example, ALI.001 is some rule related
59       to how the query uses aliases.
60
61       The rule's severity is an indication of how important it is that this
62       rule matched a query.  We use NOTE, WARN, and CRIT to denote these
63       levels.
64
65       The rule's description is a textual, human-readable explanation of what
66       it means when a query matches this rule.  Depending on the verbosity of
67       the report you generate, you will see more of the text in the
68       description.  By default, you'll see only the first sentence, which is
69       sort of a terse synopsis of the rule's meaning.  At a higher verbosity,
70       you'll see subsequent sentences.
71
72       ALI.001
73           severity: note
74
75           Aliasing without the AS keyword.  Explicitly using the AS keyword
76           in column or table aliases, such as "tbl AS alias," is more
77           readable than implicit aliases such as "tbl alias".
78
79       ALI.002
80           severity: warn
81
82           Aliasing the '*' wildcard.  Aliasing a column wildcard, such as
83           "SELECT tbl.* col1, col2" probably indicates a bug in your SQL.
84           You probably meant for the query to retrieve col1, but instead it
85           renames the last column in the *-wildcarded list.
86
87       ALI.003
88           severity: note
89
90           Aliasing without renaming.  The table or column's alias is the same
91           as its real name, and the alias just makes the query harder to
92           read.
93
94       ARG.001
95           severity: warn
96
97           Argument with leading wildcard.  An argument has a leading wildcard
98           character, such as "%foo".  The predicate with this argument is not
99           sargable and cannot use an index if one exists.
100
101       ARG.002
102           severity: note
103
104           LIKE without a wildcard.  A LIKE pattern that does not include a
105           wildcard is potentially a bug in the SQL.
106
107       CLA.001
108           severity: warn
109
110           SELECT without WHERE.  The SELECT statement has no WHERE clause.
111
112       CLA.002
113           severity: note
114
115           ORDER BY RAND().  ORDER BY RAND() is a very inefficient way to
116           retrieve a random row from the results.
117
118       CLA.003
119           severity: note
120
121           LIMIT with OFFSET.  Paginating a result set with LIMIT and OFFSET
122           is O(n^2) complexity, and will cause performance problems as the
123           data grows larger.
124
125       CLA.004
126           severity: note
127
128           Ordinal in the GROUP BY clause.  Using a number in the GROUP BY
129           clause, instead of an expression or column name, can cause problems
130           if the query is changed.
131
132       CLA.005
133           severity: warn
134
135           ORDER BY constant column.
136
137       CLA.006
138           severity: warn
139
140           GROUP BY or ORDER BY different tables will force a temp table and
141           filesort.
142
143       CLA.007
144           severity: warn
145
146           ORDER BY different directions prevents index from being used. All
147           tables in the ORDER BY clause must be either ASC or DESC, else
148           MySQL cannot use an index.
149
150       COL.001
151           severity: note
152
153           SELECT *.  Selecting all columns with the * wildcard will cause the
154           query's meaning and behavior to change if the table's schema
155           changes, and might cause the query to retrieve too much data.
156
157       COL.002
158           severity: note
159
160           Blind INSERT.  The INSERT or REPLACE query doesn't specify the
161           columns explicitly, so the query's behavior will change if the
162           table's schema changes; use "INSERT INTO tbl(col1, col2) VALUES..."
163           instead.
164
165       LIT.001
166           severity: warn
167
168           Storing an IP address as characters.  The string literal looks like
169           an IP address, but is not an argument to INET_ATON(), indicating
170           that the data is stored as characters instead of as integers.  It
171           is more efficient to store IP addresses as integers.
172
173       LIT.002
174           severity: warn
175
176           Unquoted date/time literal.  A query such as "WHERE col<2010-02-12"
177           is valid SQL but is probably a bug; the literal should be quoted.
178
179       KWR.001
180           severity: note
181
182           SQL_CALC_FOUND_ROWS is inefficient.  SQL_CALC_FOUND_ROWS can cause
183           performance problems because it does not scale well; use
184           alternative strategies to build functionality such as paginated
185           result screens.
186
187       JOI.001
188           severity: crit
189
190           Mixing comma and ANSI joins.  Mixing comma joins and ANSI joins is
191           confusing to humans, and the behavior differs between some MySQL
192           versions.
193
194       JOI.002
195           severity: crit
196
197           A table is joined twice.  The same table appears at least twice in
198           the FROM clause.
199
200       JOI.003
201           severity: warn
202
203           Reference to outer table column in WHERE clause prevents OUTER
204           JOIN, implicitly converts to INNER JOIN.
205
206       JOI.004
207           severity: warn
208
209           Exclusion join uses wrong column in WHERE.  The exclusion join
210           (LEFT OUTER JOIN with a WHERE clause that is satisfied only if
211           there is no row in the right-hand table) seems to use the wrong
212           column in the WHERE clause.  A query such as "... FROM l LEFT OUTER
213           JOIN r ON l.l=r.r WHERE r.z IS NULL" probably ought to list r.r in
214           the WHERE IS NULL clause.
215
216       RES.001
217           severity: warn
218
219           Non-deterministic GROUP BY.  The SQL retrieves columns that are
220           neither in an aggregate function nor the GROUP BY expression, so
221           these values will be non-deterministic in the result.
222
223       RES.002
224           severity: warn
225
226           LIMIT without ORDER BY.  LIMIT without ORDER BY causes non-
227           deterministic results, depending on the query execution plan.
228
229       STA.001
230           severity: note
231
232           != is non-standard.  Use the <> operator to test for inequality.
233
234       SUB.001
235           severity: crit
236
237           IN() and NOT IN() subqueries are poorly optimized.  MySQL executes
238           the subquery as a dependent subquery for each row in the outer
239           query.  This is a frequent cause of serious performance problems.
240           This might change version 6.0 of MySQL, but for versions 5.1 and
241           older, the query should be rewritten as a JOIN or a LEFT OUTER
242           JOIN, respectively.
243

OPTIONS

245       "--query" and "--review" are mutually exclusive.
246
247       This tool accepts additional command-line arguments.  Refer to the
248       "SYNOPSIS" and usage information for details.
249
250       --ask-pass
251           Prompt for a password when connecting to MySQL.
252
253       --charset
254           short form: -A; type: string
255
256           Default character set.  If the value is utf8, sets Perl's binmode
257           on STDOUT to utf8, passes the mysql_enable_utf8 option to
258           DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL.  Any
259           other value sets binmode on STDOUT without the utf8 layer, and runs
260           SET NAMES after connecting to MySQL.
261
262       --config
263           type: Array
264
265           Read this comma-separated list of config files; if specified, this
266           must be the first option on the command line.
267
268       --[no]continue-on-error
269           default: yes
270
271           Continue working even if there is an error.
272
273       --daemonize
274           Fork to the background and detach from the shell.  POSIX operating
275           systems only.
276
277       --database
278           short form: -D; type: string
279
280           Connect to this database.  This is also used as the default
281           database for "--[no]show-create-table" if a query does not use
282           database-qualified tables.
283
284       --defaults-file
285           short form: -F; type: string
286
287           Only read mysql options from the given file.  You must give an
288           absolute pathname.
289
290       --group-by
291           type: string; default: rule_id
292
293           Group items in the report by this attribute.  Possible attributes
294           are:
295
296              ATTRIBUTE GROUPS
297              ========= ==========================================================
298              rule_id   Items matching the same rule ID
299              query_id  Queries with the same ID (the same fingerprint)
300              none      No grouping, report each query and its advice individually
301
302       --help
303           Show help and exit.
304
305       --host
306           short form: -h; type: string
307
308           Connect to host.
309
310       --ignore-rules
311           type: hash
312
313           Ignore these rule IDs.
314
315           Specify a comma-separated list of rule IDs (e.g.
316           LIT.001,RES.002,etc.)  to ignore. Currently, the rule IDs are case-
317           sensitive and must be uppercase.
318
319       --password
320           short form: -p; type: string
321
322           Password to use when connecting.
323
324       --pid
325           type: string
326
327           Create the given PID file when daemonized.  The file contains the
328           process ID of the daemonized instance.  The PID file is removed
329           when the daemonized instance exits.  The program checks for the
330           existence of the PID file when starting; if it exists and the
331           process with the matching PID exists, the program exits.
332
333       --port
334           short form: -P; type: int
335
336           Port number to use for connection.
337
338       --print-all
339           Print all queries, even those that do not match any rules.  With
340           "--group-by" "none", non-matching queries are printed in the main
341           report and profile.  For other "--group-by" values, non-matching
342           queries are only printed in the profile.  Non-matching queries have
343           zeros for "NOTE", "WARN" and "CRIT" in the profile.
344
345       --query
346           type: string
347
348           Analyze this single query and ignore files and STDIN.  This option
349           allows you to supply a single query on the command line.  Any files
350           also specified on the command line are ignored.
351
352       --report-format
353           type: string; default: compact
354
355           Type of report format: full or compact.  In full mode, every
356           query's report contains the description of the rules it matched,
357           even if this information was previously displayed.  In compact
358           mode, the repeated information is suppressed, and only the rule ID
359           is displayed.
360
361       --review
362           type: DSN
363
364           Analyze queries from this mk-query-digest query review table.
365
366       --sample
367           type: int; default: 1
368
369           How many samples of the query to show.
370
371       --set-vars
372           type: string; default: wait_timeout=10000
373
374           Set these MySQL variables.  Immediately after connecting to MySQL,
375           this string will be appended to SET and executed.
376
377       --[no]show-create-table
378           default: yes
379
380           Get "SHOW CREATE TABLE" for each query's table.
381
382           If host connection options are given (like "--host", "--port",
383           etc.)  then the tool will also get "SHOW CREATE TABLE" for each
384           query.  This information is needed for some rules like JOI.004.  If
385           this option is disabled by specifying "--no-show-create-table" then
386           some rules may not be checked.
387
388       --socket
389           short form: -S; type: string
390
391           Socket file to use for connection.
392
393       --type
394           type: Array
395
396           The type of input to parse (default slowlog).  The permitted types
397           are slowlog and genlog.
398
399       --user
400           short form: -u; type: string
401
402           User for login if not current user.
403
404       --verbose
405           short form: -v; cumulative: yes; default: 1
406
407           Increase verbosity of output.  At the default level of verbosity,
408           the program prints only the first sentence of each rule's
409           description.  At higher levels, the program prints more of the
410           description.  See also "--report-format".
411
412       --version
413           Show version and exit.
414
415       --where
416           type: string
417
418           Apply this WHERE clause to the SELECT query on the "--review"
419           table.
420

DSN OPTIONS

422       These DSN options are used to create a DSN.  Each option is given like
423       "option=value".  The options are case-sensitive, so P and p are not the
424       same option.  There cannot be whitespace before or after the "=" and if
425       the value contains whitespace it must be quoted.  DSN options are
426       comma-separated.  See the maatkit manpage for full details.
427
428       •   A
429
430           dsn: charset; copy: yes
431
432           Default character set.
433
434       •   D
435
436           dsn: database; copy: yes
437
438           Database that contains the query review table.
439
440       •   F
441
442           dsn: mysql_read_default_file; copy: yes
443
444           Only read default options from the given file
445
446       •   h
447
448           dsn: host; copy: yes
449
450           Connect to host.
451
452       •   p
453
454           dsn: password; copy: yes
455
456           Password to use when connecting.
457
458       •   P
459
460           dsn: port; copy: yes
461
462           Port number to use for connection.
463
464       •   S
465
466           dsn: mysql_socket; copy: yes
467
468           Socket file to use for connection.
469
470       •   t
471
472           Table to use as the query review table.
473
474       •   u
475
476           dsn: user; copy: yes
477
478           User for login if not current user.
479

DOWNLOADING

481       You can download Maatkit from Google Code at
482       <http://code.google.com/p/maatkit/>, or you can get any of the tools
483       easily with a command like the following:
484
485          wget http://www.maatkit.org/get/toolname
486          or
487          wget http://www.maatkit.org/trunk/toolname
488
489       Where "toolname" can be replaced with the name (or fragment of a name)
490       of any of the Maatkit tools.  Once downloaded, they're ready to run; no
491       installation is needed.  The first URL gets the latest released version
492       of the tool, and the second gets the latest trunk code from Subversion.
493

ENVIRONMENT

495       The environment variable "MKDEBUG" enables verbose debugging output in
496       all of the Maatkit tools:
497
498          MKDEBUG=1 mk-....
499

SYSTEM REQUIREMENTS

501       You need the following Perl modules: DBI and DBD::mysql.
502

BUGS

504       For a list of known bugs see
505       <http://www.maatkit.org/bugs/mk-query-advisor>.
506
507       Please use Google Code Issues and Groups to report bugs or request
508       support: <http://code.google.com/p/maatkit/>.  You can also join
509       #maatkit on Freenode to discuss Maatkit.
510
511       Please include the complete command-line used to reproduce the problem
512       you are seeing, the version of all MySQL servers involved, the complete
513       output of the tool when run with "--version", and if possible,
514       debugging output produced by running with the "MKDEBUG=1" environment
515       variable.
516

COPYRIGHT, LICENSE AND WARRANTY

518       This program is copyright 2009-2011 Percona Inc.  Feedback and
519       improvements are welcome.
520
521       THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
522       WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
523       MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
524
525       This program is free software; you can redistribute it and/or modify it
526       under the terms of the GNU General Public License as published by the
527       Free Software Foundation, version 2; OR the Perl Artistic License.  On
528       UNIX and similar systems, you can issue `man perlgpl' or `man
529       perlartistic' to read these licenses.
530
531       You should have received a copy of the GNU General Public License along
532       with this program; if not, write to the Free Software Foundation, Inc.,
533       59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.
534

AUTHOR

536       Baron Schwartz, Daniel Nichter
537

ABOUT MAATKIT

539       This tool is part of Maatkit, a toolkit for power users of MySQL.
540       Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
541       primary code contributors.  Both are employed by Percona.  Financial
542       support for Maatkit development is primarily provided by Percona and
543       its clients.
544

VERSION

546       This manual page documents Ver 1.0.4 Distrib 7540 $Revision: 7531 $.
547
548
549
550perl v5.34.0                      2021-07-22               MK-QUERY-ADVISOR(1)
Impressum