MySQL Query Optimization-WHERE statement optimization, mysql-where

MySQL Query Optimization-WHERE statement optimization, mysql-where
MySQL Query Optimization-WHERE statement Optimization

If you need to reprint please indicate the source: http://blog.csdn.net/itas109
QQ technology exchange group: 12951803

Environment:
MySQL version: 5.5.15
Operating System: windows

This article discusses the optimization of WHERE statements. These examples use the SELECT statement, but the same optimization applies to the WHERE statement in the DELETE and UPDATE statements.

Examples of faster query speed:

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, ... LIMIT 10;
If the index column is numeric, MySQL uses only secondary indexes to solve 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;
The following query uses index data to retrieve rows in sorted order without requiring a separate sort pass:

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

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ...;
You may try to rewrite your query to speed up arithmetic operations while sacrificing readability. Because MySQL automatically performs similar optimizations, it is usually possible to avoid this kind of work and leave the query in a form that is easier to understand and maintain. Some optimizations performed by MySQL are as follows:

1. Remove unnecessary brackets
Removal of unnecessary parentheses

Reduce the or and and tree layers of grammar and reduce CPU consumption

   ((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)
2. Constant transfer
Constant folding

Try not to use variables

   (a <b AND b = c) AND a = 5
-> b> 5 AND b = c AND a = 5
3. Eliminate useless SQL conditions
Constant condition removal (needed because of constant folding)

   (B> = 5 AND B = 5) OR (B = 6 AND 5 = 5) OR (B = 7 AND 5 = 6)
-> B = 5 OR B = 6
4. The constant expression used by the index is only evaluated once
Constant expressions used by indexes are evaluated only once

5.COUNT (*) optimization
COUNT (*) is retrieved directly from the table information of MyISAM and MEMORY tables on a single table without WHERE. When used with only one table, this also applies to any NOT NULL expression.
For transactional storage engines such as InnoDB, storing the exact number of rows is problematic because multiple transactions may be occurring, and each transaction may affect the count.

6. Detect invalid constant expressions as soon as possible.
MySQL quickly detects that some SELECT statements are impossible and does not return any rows.

Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows.

7. Try to merge WHERE and HAVING
If you do not use GROUP BY or aggregate functions (COUNT (), MIN (), etc.), try to merge HAVING and WHERE.

HAVING is merged with WHERE if you do not use GROUP BY or aggregate functions (COUNT (), MIN (), and so on).

select * from t1 (select * from tab where id> 10) as t2 where t1.age> 10 and t2.age <25;
-> select * from t1, tab as t2 where t1.age> 10 and t2.age <25 and t2.id> 10;
Specific steps:

1) From and from merge, modify the corresponding parameters
2) Where and where merged, connected with and
3) Modify the corresponding predicate (in change =)

8. For each table in the join, construct a simpler WHERE to get a quick WHERE evaluation of the table and skip the rows as soon as possible.
For each table in a join, a simpler WHERE is constructed to get a fast WHERE evaluation for the table and also to skip rows as soon as possible.

9. Read all constant tables first before any other tables in the query.
The constant table is any of the following:
1) An empty table or a table with only one row of data
2) A table used with the WHERE clause in the primary key or unique index, where all index parts are compared with constant expressions and defined as NOT NULL.
A table that is used with a WHERE clause on a PRIMARY KEY or a UNIQUE index, where all index parts are compared to constant expressions and are defined as NOT NULL.

All the following tables are used as constant tables:

SELECT * FROM t WHERE primary_key = 1;
SELECT * FROM t1, t2
  WHERE t1.primary_key = 1 AND t2.primary_key = t1.id;
10. Optimize connection combination
Find the best connection combination of the join table as much as possible. If all the columns in the ORDER BY and GROUP BY clauses are from the same table, the table is selected first when joining.

11. ORDER BY, GROUP BY and temporary table
If there is an ORDER BY clause and a different GROUP BY clause, or ORDER BY or GROUP BY contains columns from a table other than the first table in the join queue, a temporary table is created.

12.SQL_SMALL_RESULT modifier
If you use the SQL_SMALL_RESULT modifier, MySQL uses a temporary table in memory

13. Use the best index
Query the index of each table and use the best index unless the optimizer thinks it is more efficient to use table scans. At one time, scanning was used based on whether the best index exceeded 30% of the table, but a fixed percentage no longer determined the choice between using index or scanning. The optimizer is now more complex and is estimated based on additional factors such as table size, number of rows, and I / O block size.

Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I / O block size.

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

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

15. Skip the lines that do not match the HAVING clause before outputting each line 16. Optimize the order of selection criteria
Put conditions that can filter more data in the front, and conditions that filter less in the back

MySQL processes conditions in order from left to right, putting conditions that filter more data in front, and conditions that filter less in the back

 select * from employee
where salary> 1000 --condition 1, less filtered data
and dept_id = '01 '-Condition 2, the filtered data is more than Condition 1
The above SQL does not conform to our principles, and more conditions for filtering data should be placed in front, so it is better to change to the following

select * from employee
where dept_id = '01 '-filter more data in front
and salary> 1000
Reference:
https://dev.mysql.com/doc/refman/5.5/en/where-optimization.html

I think the article is helpful to you, you can donate it to the blogger by scanning the QR code, thank you!

If you need to reprint, please indicate the source: http://blog.csdn.net/itas109
QQ technical exchange group: 12951803

View comments