"MySQL" 10 SQL optimization statements, let your MySQL database run faster!

1. Slow SQL consumes 70%~90% database CPU resources;

2. SQL statement is independent of the program design logic, compared to the program source code optimization, the optimization of SQL statements in the cost of time and risk is very low;

3. SQL statements can be written in different ways;

1 Do not use subqueries

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 Avoiding function indexing

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 cannot 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 prohibit sorting

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 fetching

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 prohibition of 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 Insert

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 ');

"MySQL" 10 SQL optimization statements, let your MySQL database run faster!