mysql Database sql Optimization Principles of Experience Summary

1. The premise

The principles here apply only to THE MySQL database, some of the other databases lead to the same path, and some of them differ. I've also summarized the general rules of MySQL, which apply to special cases. Get in the habit of constructing SQL statements.

2. Summary of principles

Principle 1, only list the fields that need to be queried. This does not have a noticeable impact on speed, but the main concern is to save memory on the application server.

select * from admin

Optimization: select admin_id,admin_name,admin_password from admin

Rule 2. Try to avoid doing operations on columns that would cause indexes to fail.

select * from admin where year(admin_time) > 2014

Optimized as select * from admin where admin_time > '2014-01-01'

Principle 3. When using JOIN, small results should be used to drive large results (left join left table results should be as small as possible, if conditions are possible, it should be placed on the left and processed first, right join is the same as reverse). Colleagues should try to split multiple query queries involving multi-table federation (multiple linked table queries are inefficient, easy to lock tables and block after they arrive).

select * from admin join on admin.admin_id = log. admin_id where.admin_id > 10

Optimized to: select * from (select * from admin where admin_id > 10) T1 lef join log on T1.admin_id = log.admin_id

Principle 4, pay attention to the use of LIKE fuzzy query, avoid using %%, can be followed by %, double % is not out of the index.

select * from where admin_name '%de%'

Optimization: select * from admin where admin_name > ='de' and admin_nam < 'df' (note not equivalent here try to provide optimization ideas)

Principle 5. Use bulk inserts to save interaction (when, for example, using stored procedures to handle bulk sql various logic is a better choice).

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 as: insert into admin(admin_name,admin_password) values(' test1 ','pass1 '),(' test2 ','pass2 '),(' test3 ','pass3 ')

between is used when the cardinality of limit is relatively large.

select * from admin by admin_id limit 1000,10

Optimization: select * from admin where admin_id between 100,000 admin 100010 order by admin_id

Principle 7. Do not use the rand function to obtain multiple random records.

select * from admin order by rand() limit 20

Optimization for: select * from admin t1 Join(select round()*(select max(admin_id) from admin -(select min(id) from admin) +(select min(id) as t2 where 1 > =t2.id order by t1.id limit

Principle 8. Avoid NULL.

Principle 9. Do not use count(id) Use count(*).

Rule 10. Don't do unnecessary sorting; use the index instead.

3. Summary

Database optimization includes two aspects, one is SQL program optimization, the other is the optimization of database configuration. In addition, MySQL can see the following statement to help SQL optimize:

 
set @@profiling = 1; 
select * from typecho_comments order mail limit 10,30; 
show profiles; 

show profile for query