1MK-QUERY-ADVISOR(1) User Contributed Perl Documentation MK-QUERY-ADVISOR(1)
2
6 mk-query-advisor - Analyze queries and advise on possible problems.
7
9 Usage: mk-query-advisor [OPTION...] [FILE]
10 mk-query-advisor analyzes queries and advises on possible problems.
12 Queries are given either by specifying slowlog files, --query, or
13 --review.
14 # Analyzer all queries in the given slowlog
16 mk-query-advisor /path/to/slow-query.log
17 # Get queries from tcpdump using mk-query-digest
19 mk-query-digest --type tcpdump.txt --print --no-report | mk-query-advisor
20 # Get queries from a general log
22 mk-query-advisor --type genlog mysql.log
23
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 mk-query-advisor simply reads queries and examines them, and is thus
31 very low risk.
32 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 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 See also "BUGS" for more information on filing bugs and getting help.
42
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
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 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 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 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 ALI.001
73 severity: note
74 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 ALI.002
80 severity: warn
81 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 ALI.003
88 severity: note
89 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 ARG.001
95 severity: warn
96 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 ARG.002
102 severity: note
103 LIKE without a wildcard. A LIKE pattern that does not include a
105 wildcard is potentially a bug in the SQL.
106 CLA.001
108 severity: warn
109 SELECT without WHERE. The SELECT statement has no WHERE clause.
111 CLA.002
113 severity: note
114 ORDER BY RAND(). ORDER BY RAND() is a very inefficient way to
116 retrieve a random row from the results.
117 CLA.003
119 severity: note
120 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 CLA.004
126 severity: note
127 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 CLA.005
133 severity: warn
134 ORDER BY constant column.
136 CLA.006
138 severity: warn
139 GROUP BY or ORDER BY different tables will force a temp table and
141 filesort.
142 CLA.007
144 severity: warn
145 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 COL.001
151 severity: note
152 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 COL.002
158 severity: note
159 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 LIT.001
166 severity: warn
167 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 LIT.002
174 severity: warn
175 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 KWR.001
180 severity: note
181 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 JOI.001
188 severity: crit
189 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 JOI.002
195 severity: crit
196 A table is joined twice. The same table appears at least twice in
198 the FROM clause.
199 JOI.003
201 severity: warn
202 Reference to outer table column in WHERE clause prevents OUTER
204 JOIN, implicitly converts to INNER JOIN.
205 JOI.004
207 severity: warn
208 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 RES.001
217 severity: warn
218 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 RES.002
224 severity: warn
225 LIMIT without ORDER BY. LIMIT without ORDER BY causes non-
227 deterministic results, depending on the query execution plan.
228 STA.001
230 severity: note
231 != is non-standard. Use the <> operator to test for inequality.
233 SUB.001
235 severity: crit
236 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
245 "--query" and "--review" are mutually exclusive.
246 This tool accepts additional command-line arguments. Refer to the
248 "SYNOPSIS" and usage information for details.
249 --ask-pass
251 Prompt for a password when connecting to MySQL.
252 --charset
254 short form: -A; type: string
255 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 --config
263 type: Array
264 Read this comma-separated list of config files; if specified, this
266 must be the first option on the command line.
267 --[no]continue-on-error
269 default: yes
270 Continue working even if there is an error.
272 --daemonize
274 Fork to the background and detach from the shell. POSIX operating
275 systems only.
276 --database
278 short form: -D; type: string
279 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 --defaults-file
285 short form: -F; type: string
286 Only read mysql options from the given file. You must give an
288 absolute pathname.
289 --group-by
291 type: string; default: rule_id
292 Group items in the report by this attribute. Possible attributes
294 are:
295 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 --help
303 Show help and exit.
304 --host
306 short form: -h; type: string
307 Connect to host.
309 --ignore-rules
311 type: hash
312 Ignore these rule IDs.
314 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 --password
320 short form: -p; type: string
321 Password to use when connecting.
323 --pid
325 type: string
326 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 --port
334 short form: -P; type: int
335 Port number to use for connection.
337 --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 --query
346 type: string
347 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 --report-format
353 type: string; default: compact
354 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 --review
362 type: DSN
363 Analyze queries from this mk-query-digest query review table.
365 --sample
367 type: int; default: 1
368 How many samples of the query to show.
370 --set-vars
372 type: string; default: wait_timeout=10000
373 Set these MySQL variables. Immediately after connecting to MySQL,
375 this string will be appended to SET and executed.
376 --[no]show-create-table
378 default: yes
379 Get "SHOW CREATE TABLE" for each query's table.
381 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 --socket
389 short form: -S; type: string
390 Socket file to use for connection.
392 --type
394 type: Array
395 The type of input to parse (default slowlog). The permitted types
397 are slowlog and genlog.
398 --user
400 short form: -u; type: string
401 User for login if not current user.
403 --verbose
405 short form: -v; cumulative: yes; default: 1
406 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 --version
413 Show version and exit.
414 --where
416 type: string
417 Apply this WHERE clause to the SELECT query on the "--review"
419 table.
420
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 • A
429 dsn: charset; copy: yes
431 Default character set.
433 • D
435 dsn: database; copy: yes
437 Database that contains the query review table.
439 • F
441 dsn: mysql_read_default_file; copy: yes
443 Only read default options from the given file
445 • h
447 dsn: host; copy: yes
449 Connect to host.
451 • p
453 dsn: password; copy: yes
455 Password to use when connecting.
457 • P
459 dsn: port; copy: yes
461 Port number to use for connection.
463 • S
465 dsn: mysql_socket; copy: yes
467 Socket file to use for connection.
469 • t
471 Table to use as the query review table.
473 • u
475 dsn: user; copy: yes
477 User for login if not current user.
479
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 wget http://www.maatkit.org/get/toolname
486 or
487 wget http://www.maatkit.org/trunk/toolname
488 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
495 The environment variable "MKDEBUG" enables verbose debugging output in
496 all of the Maatkit tools:
497 MKDEBUG=1 mk-....
499
501 You need the following Perl modules: DBI and DBD::mysql.
502
504 For a list of known bugs see
505 <http://www.maatkit.org/bugs/mk-query-advisor>.
506 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 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
518 This program is copyright 2009-2011 Percona Inc. Feedback and
519 improvements are welcome.
520 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 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 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
536 Baron Schwartz, Daniel Nichter
537
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
546 This manual page documents Ver 1.0.4 Distrib 7540 $Revision: 7531 $.
547perl v5.38.0 2023-07-20 MK-QUERY-ADVISOR(1)