10 principles for basic statement optimization: Principle 1: Avoid Column Operations as much as possible, which will cause index failure. For example: select * fromtwhereyear (d) 2011; optimized to: select * fromtwhered2011-01-01; principle 2: When Using JOIN, a small result set should be used to drive a large result set. Meanwhile, the complex JOIN query is split into multiple
10 principles for basic statement optimization: Principle 1: Avoid Column Operations as much as possible, which will cause index failure. Example: select * from t where year (d) = 2011; optimized to: select * from t where d =; principle 2: When Using JOIN, A small result set should be used to drive a large result set. Meanwhile, the complex JOIN query is split into multiple
10 basic statement optimization principles:
Principle 1: Avoid Column Operations as much as possible, which will cause index failure.
For example:
Select * from t where year (d) >=2011;
Optimized:
Select * from t where d> = '2017-01-01 ';
Principle 2: When Using JOIN, a small result set should be used to drive a large result set. Meanwhile, the complex JOIN Query is split into multiple queries. Because multiple JION tables may cause more locking and blocking.
Principle 3: Pay attention to the use of like fuzzy queries to avoid %.
For example:
Select * from t where name like '% de % ';
Optimized:
Select * from t where name> = 'de' and name <'df ';
Principle 4: only list the fields to be queried. This will not significantly affect the speed, and the main consideration is to save memory.
For example:
Select * from t;
Optimized:
Select id, name from t;
Principle 5: Use batch insert statements to save interactions.
After optimization: insert into t (id, name) values (1, 'A'), (2, 'B'), (3, 'C ');
Principle 6: use between when the LIMIT technology is relatively large.
For example:
Select * from t as t order by id limit 0, 10;
After optimization:
Select * from t as t where id between 1000000 and 1000010 order by id;
Principle 7: Do not use the rand function to obtain multiple random records.
For example:
Select * from t order by rand () limit 20;
Principle 8: Avoid NULL.
Principle 9: Do not use count (id), but count (*);
Principle 10: do not perform unnecessary sorting operations, but probably sort the indexes.