10 principles for Basic statement optimization

The optimization of database includes two aspects, on the one hand, the optimization of SQL program statement and the optimization of database server and configuration. Query statement optimization involves two main aspects: some universally adhered principles, and how to perform performance analysis on query statements.

 10 principles for basic statement Optimizationin the application of the database, the programmer has summed up a lot of experience through the continuous practice, these experiences are some universally applicable rules. Every programmer should understand and remember them and develop good habits when constructing SQL. The following is a list of 10 more important principles. principle 1: Try to avoid doing operations on columns, which can cause indexing to fail. For example, the original sentence is:

SELECT * FROM T WHERE year (d) >= 2011;

optimized to:

SELECT * from T WHERE d >= ' 2011-01-01 ';

Principle 2: When using join, a large result set should be driven with a small result set. Split the complex join query into multiple queries at the same time. Because joins multiple tables, it can result in more locks and blockages. For example:

SELECT * from a join B on a.id = B.idleft Join C on c.time = A.dateleft Join D on c.pid = B.aidleft Join E on e.cid = A.di D

principle 3: Pay attention to the use of like fuzzy query, avoid the percent. For example, the original sentence is:

SELECT * from T WHERE name like '%de% '

optimized to:

SELECT * from T WHERE name >= ' de ' and name <= ' DF '

principle 4: Lists only the fields that need to be queried, which does not have a noticeable effect on speed, mainly considering saving memory. For example, the original sentence is:

SELECT * from Member;

optimized to:

SELECT id,name,pwd from Member;

principle 5: Use BULK INSERT statements to save interactivity. For example, the original sentence is:

Insert into T (id,name) VALUES (1, ' a ') and insert into T (id,name) VALUES (2, ' B '); INSERT into T (id,name) VALUES (3, ' C ');

optimized to:

INSERT into T (id,name) VALUES (1, ' a '), (2, ' B '), (3, ' C ');

Principle 6: Use between when the base of limit is relatively large. For example, the original sentence is:

SELECT * from article as article RODER by ID LIMIT 1000000, 10;

optimized to:

SELECT * from article as article WHERE ID between 1000000 and 1000010 RODER by ID;

between limit is faster than limit, so a large amount of data access, it is recommended to between or where to replace the limit. But between also has a flaw, if there is a break in the middle of the ID or the middle part ID is not read, the total number of reads will be less than the estimated number! in the comparison of the data behind, through the Desc method to reverse the data lookup, to reduce the previous data scanning, so that the limit of the base of the smaller the better! Principle 7: Do not use the rand () function to get multiple random records. For example:

SELECT * FROM Table ORDER by rand () limit 20;

use the following statement instead:

SELECT * from ' table ' as T1 join (select rand () (RAND () * ((SELECT MAX (ID) from ' table ')-(select min (id) from ' table ') + (select Min (id) from ' table ')) as-id) as T2 where t1.id >= t2.id ORDER by t1.id limit 1;

this is to get a random record so that even if executed 20 times, it is more efficient than the original statement. or first use PHP to generate a random number, the string is passed to Mysql,mysql in query. principle 8: Avoid using NULL. Principle 9: Do not use COUNT (ID), but count (*). principle: Do not do unnecessary sorting operations, and as far as possible in the index to complete the sorting.

10 principles for Basic statement optimization