SQL optimization-Project combat

SQL optimization in a project is one of the effective ways to improve project execution efficiency. If you are a programmer, the use of good SQL not only makes the project execution efficiency improvement, but also is a personal technical ability embodiment; If you are a project manager, it is important to check whether the programmer's SQL is a good SQL, so how does SQL optimize it?

SQL Optimization scenarios:

1. Tips for using indexes in sql:

(1) For Multi-column indexes (composite indexes), the SQL query condition only uses the leftmost column for the index to take effect.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/87/62/wKiom1fejXDRyaRKAAHHIG1V4jk970.png "title=" 11111. PNG "alt=" Wkiom1fejxdryarkaahhig1v4jk970.png "/>

 

(2) for a like fuzzy query, the fuzzy matching string is the first to prohibit the use of the character of the fuzzy match, such as%, _, otherwise the index will be invalidated, if the first must use fuzzy matching, it is recommended to use full-text indexing technology (Sphinx (Coresseek)).

(3)the SQL condition hasorkeyword,orBoth sides of the condition must be indexed(If this is a composite index, it must be the first), otherwise the index is all invalid. The general situation, can avoid the keywordor, try to avoid using them.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/87/5F/wKioL1fejnjj5N4PAADW9BgObDQ225.png "title=" 222222.png "alt=" Wkiol1fejnjj5n4paadw9bgobdq225.png "/>

(4) If a field in the SQL condition is a string type, be sure to enclose it in single or double quotation marks, otherwise the index is invalid.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/87/62/wKiom1fejzCxJ5sqAAGpAbwbnc0871.png "title=" 6666666.png "alt=" Wkiom1fejzcxj5sqaagpabwbnc0871.png "/>

2. GROUP BY use optimization:

When a group by group query is used, the default grouping is also automatically sorted, which may reduce query efficiency.

Optimization: Add ORDER by null after group by.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/87/5F/wKioL1fekCSDrUNvAAE2cxQJra8800.png "title=" Qq3333.png "alt=" Wkiol1fekcsdrunvaae2cxqjra8800.png "/>

3. Connect instead of subqueries and associated queries:

In general, a join is used instead of a subquery and an associated query, because with join, MySQL does not need to create temporary tables in memory.

select * from Tb_user_goods g,tb_shop s WHERE g.fk_shop_id=s.id; (

inner JOIN   Tb_shop s on  g.fk_ Shop_id=s.id;  

This article from the "Yao Technology Blog" blog, reproduced please contact the author!

SQL optimization-Project combat