Mysql Database SQL optimization principles (Experience summary)

I. Prerequisites

The principle here is only for MySQL databases. Some of the other databases are similar, and some are still different. I have summarized the General Rules of MySQL and have to take special measures in some special cases. Make good habits when constructing SQL statements.

Ii. Summary of principles

Principle 1: only list the fields to be queried, which will not significantly affect the speed, mainly to save the memory of the application server.

Original statement: select * from admin

Optimized to: select admin_id, admin_name, admin_password from admin

Principle 2: Avoid Column Operations as much as possible, resulting in index failure.

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

Optimized to: select * from admin where admin_time> '2017-01-01 ′

Principle 3: When Using JOIN, a small result should be used to drive a large result (the results of the left table of left join should be as small as possible. If conditions are available, the left table should be processed first, and right join is the same as reverse JOIN ), colleagues try to split queries involving multi-table join into multiple queries (queries with multiple connected tables are inefficient, and it is easy to lock and block tables later ).

The original statement select * from admin left join log on admin. admin_id = log. admin_id where log. 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 queries. Avoid using %. You can use % with the backend. Double % does not go through the index.

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

Optimization: select * from admin where admin_name> = 'de' and admin_nam <'df '(note that it is not equivalent. Here we try to provide optimization ideas)

Principle 5: Use batch insert to save interaction (for example, it is better to use a stored procedure to process various logic of batch SQL ).

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', 'passs3 ′)

Optimized to: insert into admin (admin_name, admin_password) values ('test1', 'pass1'), ('test2', 'pass2'), ('test3 ′, 'passs3 ′)

Principle 6: use between when the limit base is large.

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 obtain multiple random records.

Original statement: select * from admin order by rand () limit 20

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

Principle 8. Avoid NULL.

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

Principle 10: do not perform unnecessary sorting operations, but use indexes to complete sorting.

Iii. Summary

Database optimization involves two aspects: SQL program optimization and database configuration optimization. In addition, you can use the following statement in MySQL to view and optimize the SQL statement:

Copy codeThe Code is as follows:
Set @ profiling = 1;
Select * from typecho_comments order mail limit 10, 30;
Show profiles;

Show profile for query