MYSQL SQL statement optimization personal summary

1. Avoid SELECT *

For every field less extracted in SELECT, the speed of data extraction will increase accordingly. The speed of improvement depends on the size of the discarded fields.Copy the code

2. Create an index

Index as appropriate, single or combined, according to your own needs. There is a left-most rule for mysql to create federated indexes.Copy the code

3. Avoid operations on columns, which may cause index invalidation

	SELECT usercode,username,age FROM t WHERE score/10 = 9;
Copy the code

Optimization for

	SELECT usercode,username,age FROM t WHERE score = 9*10;Copy the code

4. When using JOIN

You should use small result sets to drive large result sets and split complex JOIN queries into multiple Queries, because joining multiple tables can lead to more locking and blocking. You can replace complex subqueries with Joins.Copy the code

5. When using LIKE

Avoid using full obfuscation ('%%') queries, which will perform a full table scan without moving the index.Copy the code
	SELECT usercode,username,age,score FROM t WHERE username LIKE '% % king';Copy the code

Optimization for

	SELECT usercode,username,age,score FROM t WHERE username LIKE 'the king %';Copy the code

6. Avoid null values

Null indicates that the index is not movedCopy the code
SELECT usercode,username,age FROM t WHERE score IS NULL;
Copy the code

Optimization method: You can add the default value 0 to the field to determine the value 0. As follows:

SELECT usercode,username,age FROM t WHERE score = 0;
Copy the code

7. Avoid using or

Or performs a full table scanCopy the code
SELECT id,username,age,score FROM t WHERE id = 1 OR usercode = '001';
Copy the code

Optimization method: Union can be used instead of OR. As follows:

SELECT id,username,age,score FROM t WHERE id = 1
UNION
SELECT id,username,age,score FROM t WHERE usercode = '001';
Copy the code

8. Avoid in and not in

SELECT usercode,username,age,score FROM t WHERE id IN (2.3);
SELECT usercode,username,age,score FROM t WHERE username IN (SELECT username FROM t1);
Copy the code

Optimization method: If the value is continuous, use between instead. As follows:

SELECT usercode,username,age,score FROM t WHERE id BETWEEN 2 AND 3;Copy the code

If it is a subquery, use exists instead. As follows:

SELECT usercode,username,age,score FROM t WHERE EXISTS (SELECT * FROM t1 WHERE t.username = t1.username);
Copy the code

9.LIMIT

If the cardinality of limit is large, use between, which is faster than limit. However, between also has some defects. If there is a line break in the middle of the ID or the middle part of the ID is not read, the data will be lessCopy the code
select usercode,username,age,score from t where score=100 limit 100000.10;
Copy the code

Optimization for

select usercode,username,age,score from t where score=100 between 100000 and 100010;
Copy the code

Slow SQL tuning

Find out what causes slow SQL and what causes it to be slow. There may be no index for the query condition, or the amount of data queried is too large. 1. There is no index in the query condition. Select the best index field based on the query condition to create an index. That is, the values of this field are scattered and not concentrated, and this field cannot have null values, because there are fewer enumerations in the field value set. The database engine may not move the index. If the index field is null, the table will not be scanned. 2. The amount of data is too large If the amount of queried data is too large. The value of a certain condition results in a large amount of data, which accounts for a large part of the whole table data. You need to analyze the data to see if you can index a large portion of the data by the mandatory criteria of the query, or if you can index a large portion of the data by certain criteria and then create a joint index for those criteria.Copy the code