SQL statement Common Optimization Ten cases

1, slow SQL consumes the 70%~90% database CPU resources;

2, the SQL statement is independent of the program design logic, relative to the program source code optimization, the optimization of SQL statements in the cost of time and risk is very low;
3, the SQL statement can have different wording;

Here is a summary of some of the SQL common optimization methods, each case is easy to understand, in the development process can be used as a reference:

1, do not use sub-query
Example: SELECT * from t1 where ID (select ID from T2 where name= ' Hechunyang ');
Subquery in the MySQL5.5 version, the internal execution planner is doing this: first check the appearance and then match the inner table, rather than check the inner table t2, when the appearance of the data is very large, the query speed will be very slow.
In the mariadb10/mysql5.6 version, it is optimized with the join association, which is automatically converted to
SELECT t1.* from T1 JOIN t2 on t1.id = t2.id;
Note, however, that the optimization is only valid for select and is not valid for Update/delete subqueries, and the solid production environment should avoid using subqueries

2. Avoid function index
Example: SELECT * from T WHERE year (d) >= 2016;
Because MySQL does not support function indexing as Oracle does, even if the D field is indexed, it is scanned directly across the table.
Should be changed to----->
SELECT * from T WHERE d >= ' 2016-01-01 ';

3. Replace or with in
Low-efficiency queries
SELECT * from t WHERE loc_id = ten or loc_id = or loc_id = 30;
----->
Efficient query
SELECT * from T WHERE loc_in in (10,20,30);

4, like double percent semicolon can not be used to index
SELECT * from T WHERE name like '%de% ';
----->
SELECT * from T WHERE name like ' de% ';
Currently only MySQL5.7 supports full-text indexing (Chinese supported)

5. Read the appropriate record limit m,n
SELECT * from T WHERE 1;
----->
SELECT * from T WHERE 1 LIMIT 10;

6. Avoid inconsistent data types
SELECT * FROM t WHERE id = ' 19 ';
----->
SELECT * FROM t WHERE id = 19;

7, grouping statistics can be forbidden to sort
SELECT Goods_id,count (*) from the T GROUP by goods_id;
By default, MySQL is for all GROUP by col1,col2 ... To sort the fields. If the query includes group by and you want to avoid the consumption of sort results, you can specify order by NULL to prohibit sorting.
----->
SELECT Goods_id,count (*) from the T GROUP by goods_id ORDER by NULL;

8. Avoid random record taking
SELECT * from T1 WHERE 1=1 ORDER by RAND () LIMIT 4;
MySQL does not support function indexing, which causes full table scan
----->
SELECT * from t1 WHERE ID >= ceil (RAND () *1000) LIMIT 4;

9. Prohibit unnecessary order by ordering
SELECT count (1) from the user u left JOIN user_info i to U.id = i.user_id WHERE 1 = 1 ORDER by U.create_time DESC;
----->
SELECT count (1) from the user u left JOIN user_info i on u.id = i.user_id;

10. Bulk Insert Insertion
INSERT into t (ID, name) VALUES (1, ' Bea ');
INSERT into t (ID, name) VALUES (2, ' Belle ');
INSERT into t (ID, name) VALUES (3, ' Bernice ');
----->
INSERT into t (ID, name) VALUES (1, ' Bea '), (2, ' Belle '), (3, ' Bernice ');

This article is from the "Spring Yang Technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1747114

SQL statement Common Optimization Ten cases