Mysql Database SQL Optimization Principles (experience Summary) _mysql

First, the premise

The principle here is only for MySQL database, other database some is the same, some still exist differences. My summary is also the general rule of MySQL, special treatment for some special cases. Develop good habits when constructing SQL statements.

Ii. Summary of Principles

Principle 1, which lists only the fields that need to be queried, does not have a noticeable effect on speed, primarily by considering saving the memory of the application server.

Original statement: SELECT * from admin

Optimized for: Select Admin_id,admin_name,admin_password from admin

Principle 2, try to avoid doing operations on columns, which results in index invalidation.

Original statement: SELECT * from admin where year (admin_time) >2014

Optimized for: SELECT * from admin where admin_time> ' 2014-01-01′

Principle 3, when you use join, you should drive large results with small results (the left table is as small as possible if conditions should be placed on the left side first, right join in reverse), colleagues try to split multiple query queries involving multiple tables (multiple queries are inefficient, Easy to lock table and block later.

Original Statement SELECT * FROM admin left join log on admin.admin_id = log.admin_id where log.admin_id>10

Optimized for: SELECT * FROM (SELECT * from admin where admin_id>10) T1 lef join log on t1.admin_id = log.admin_id

Principle 4, note the use of like fuzzy query, to avoid the use of%, you can use the following%, double% is not to go index.

Original statement: SELECT * from admin where admin_name like '%de% '

Optimized for: SELECT * from admin where admin_name >= ' de ' and Admin_nam < ' DF ' (attention is not equivalent here try to provide optimized ideas)

Principle 5, use bulk inserts to save the interaction (preferably if you use stored procedures to handle the bulk of SQL logic).

Original statement: INSERT INTO admin (admin_name,admin_password) VALUES (' test1′, ' pass1′);

Insert into admin (admin_name,admin_password) VALUES (' test2′, ' pass2′);

Insert into admin (admin_name,admin_password) VALUES (' test3′, ' pass3′)

Optimized for: INSERT INTO admin (admin_name,admin_password) VALUES (' test1′, ' pass1′ '), (' test2′, ' pass2′), (' test3′, ' pass3′)

Principle 6, limit base is relatively large when using between.

Original statement: SELECT * from admin order by admin_id limit 100000,10

Optimized to: SELECT * from admin where admin_id between 100000 admin 100010 order by admin_id

Principle 7, do not use the RAND function to get more than one random record.

Original statement: SELECT * FROM Admin order by rand () limit 20

Optimized for: SELECT * from admin as T1 Join (select round (admin_id) to (select Max () from admin) + (select min (id) from admin) + (s Elect min (ID) from admin) as ID) as T2 where t1.id>=t2.id order by t1.id limit

Principle 8, avoid using NULL.

Principle 9. Do not use COUNT (*) with count (ID).

Principle 10, do not do unnecessary sorting operations, but should use the index to complete the sort.

three, summary

Database optimization includes two aspects, one is SQL program optimization, the other is Database configuration optimization. Also in MySQL you can view the help SQL for optimization by using the following statements:

Copy code code as follows:

set @ @profiling = 1;
SELECT * from typecho_comments order mail limit 10, 30;
Show Profiles;

Show profiles for query