SQL optimization optimization SELECT statement

WHERE clause optimization

This section discusses the optimizations that can be made to handle the WHERE clause. These examples use SELECT statements, but the same optimization applies to clauses in the WHERE to DELETE and UPDATE statements.

Pay attention to

Because work on the MySQL optimizer is ongoing, not all optimizations performed by MySQL are recorded here.

You might want to rewrite the query to make the arithmetic faster at the expense of readability. Because MySQL does similar optimizations automatically, you can usually avoid this work and leave queries in a form that is easier to understand and maintain. Some optimizations performed by MySQL are as follows:

  • Remove unnecessary parentheses:

       ((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
Copy the code

  • Constant folding:

       (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5
Copy the code

  • Constant condition elimination:

       (b> =5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
-> b=5 OR b=6
Copy the code

  • The constant expression used by the index is evaluated only once.

  • There is no single table on COUNT(*) WHERE retrieves MyISAM and MEMORY tables directly from the table information. NOT NULL This operation is also performed for any expression when used with only one table.

  • Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are not possible and does not return any rows.

  • HAVING ‘WHERE’ merges if you do not use GROUP BY or aggregate functions (COUNT(), MIN(), etc.).

  • For each table in the join, WHERE constructs a simpler WHERE table to quickly evaluate the table and skip rows as quickly as possible.

  • All constant tables are read first before any other tables in the query. The constant scale can be any of the following:

    • An empty table or a table with a row.
    • With a or indexWHEREIs used with the clause on, where all index parts are compared with constant expressions and defined as.PRIMARY KEY``UNIQUE``NOT NULL

    All the following tables are used as regular scales:

    SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
Copy the code

  • Try all possible methods to find the best join combination for joining tables. If all columns in the ORDER BYandGROUP BY clause come from the same table, then that table is used in preference when joining.

  • A temporary table is created if there is an ORDER BY clause and another GROUP BY clause, or if either ORDER BY or GROUP BY contains columns in a table other than the first table in the join queue.

  • If the SQL_SMALL_RESULT modifier is used, MySQL uses temporary tables in memory.

  • Query each table index and use the best index unless the optimizer decides that a table scan is more efficient. A single use scan is based on whether the best index spans more than 30% of the table, but a fixed percentage no longer determines whether to use an index or scan. Now, the optimizer is more complex, with estimates based on other factors such as table size, number of rows, and I/O block size.

  • In some cases, MySQL can even read rows from the index without querying the data file. If all columns used in the index are numeric, only the index tree is used to parse the query.

  • HAVING skips lines that do not match the clause before printing each line.

Some examples of quick queries:

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;

SELECT.FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT.FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC. LIMIT10;
Copy the code

Given that index columns are numbers, MySQL only uses index trees to parse the following queries:

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
  WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;
Copy the code

The following queries use indexes to retrieve rows in sort order without a separate sort traversal:

SELECT.FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT.FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC. ;Copy the code

More content welcome to pay attention to my personal public number “Han Elder brother has words”, 100G artificial intelligence learning materials, a large number of back-end learning materials waiting for you to take.