SQL Optimization (RPM)

1. Negative condition query cannot use index

SELECT * from order where status!=0 and stauts!=1

Not in/not exists are not good habits

Can be optimized for in query:

SELECT * from order where status in (2,3)

2. A leading fuzzy query cannot use an index

SELECT * FROM order where desc like '%XX '

Instead of a leading fuzzy query, you can:

SELECT * FROM order where desc like ' XX% '

3. Indexes are not suitable for fields with small data sensitivity

SELECT * from user where sex=1

Reason: Gender only male, female, every time the data filtered out is very little, not to use the index.

In experience, indexes can be used when filtering 80% of data. For order status, if the status value is very small, the index should not be used, if the state value is many, can filter a large amount of data, you should establish an index.

4. Evaluation on attributes cannot hit index

SELECT * FROM order where year (date) < = ' 2017 '

Even if an index is established on date, it is fully scanned and can be optimized for value calculation:

SELECT * from order where date < = Curdate ()

Or:

SELECT * from order where date < = ' 2017-01-01 '

5. If the business is mostly a single query, using hash index performance is better, such as User Center

SELECT * from user where uid=?

SELECT * from user where login_name=?

Reason:

The time complexity of the B-tree index is O (log (n))

The time complexity of the hash index is O (1)

6. Allow null columns, query for potentially large pits

The column index does not have a null value, the composite index does not have all null values, and if the column is allowed to be null, you may get a result set that does not match expectations

SELECT * from user where name! = ' Shenjian '

If name is allowed to be null, the index does not store null values and these records are not included in the result set.

Therefore, use the NOT NULL constraint and the default value.

7. Compound index leftmost prefix, not value SQL statement where order is consistent with composite index

The User Center has built a composite index (login_name, passwd)

SELECT * from user where login_name=? and passwd=?

SELECT * from user where passwd=? and login_name=?

are able to hit the index

SELECT * from user where login_name=?

Can also hit the index to satisfy the leftmost prefix of the composite Index

SELECT * from user where passwd=?

Cannot hit index, does not satisfy the leftmost prefix of composite index

8. Limit 1 can improve efficiency if it is clear that only one result is returned

SELECT * from user where login_name=?

Can be optimized to:

SELECT * from user where login_name=? Limit 1

Reason:

You know there's only one result, but the database doesn't know, tell it explicitly, let it actively stop the cursor movement

9. Put the calculation into the business layer, not the database layer, in addition to the data-saving CPU, there are unexpected query cache optimization effect

SELECT * from order where date < = Curdate ()

This is not a good SQL practice and should be optimized for:

$curDate = Date (' y-m-d ');

$res = mysql_query (' SELECT * from order where date < = $curDate ');

Reason:

Freed the CPU of the database

Multiple invocations, the same SQL passed in, can take advantage of query caching

10. Forced type conversion full table scan

SELECT * from user where phone=13800001234

Optimization:

The Phone field is a string type, and the phone value is quoted to: ' 13800001234 '

Or/in/union and index optimization for MySQL

1). Union all must be able to hit the index

2). Simple in to hit index

3). For or, the new version of MySQL can hit the index

4). For! =, negative queries must not hit the index

12. Disable the use of select *, get only the necessary fields, need to display the Description column properties

Interpretation:

1). Read unwanted columns increases CPU, IO, net consumption

2). Cannot effectively use the overlay index

3). Use SELECT * to easily add or remove fields after a program bug occurs

13. Disable INSERT INTO t_xxx VALUES (XXX), you must display the specified column properties

READ: Easy to add or delete fields after the program bug

14. Disable the use of attribute implicit conversions

Interpretation: The SELECT uid from T_user WHERE phone=13812345678 causes a full table scan and cannot hit the phone index

15. Prohibit the use of functions or expressions on the properties of a Where condition

Interpretation: SELECT uid from T_user WHERE from_unixtime (day) >= ' 2017-02-15 ' will cause full table scan

The correct notation is: SELECT uid from T_user WHERE day>= unix_timestamp (' 2017-02-15 00:00:00 ')

16. Suppress negative queries, as well as the fuzzy query starting with%

Interpretation:

1). Negative query conditions: not,! =, <>,!<,!>, not in, no like, etc., causes full table scan

2).% start of fuzzy query, will cause full table scan

17. Prohibit large tables from using a join query, prevent large tables from using subqueries

Interpretation: can generate temporary tables, consume more memory and CPU, greatly affect database performance

18. Prohibit the use or condition, must change in query

Interpretation: The old version of MySQL or query is not hit index, even if you can hit the index, why should the database consume more CPU to help implement query optimization?

19. The application must capture the SQL exception and have the appropriate processing

The SQL statement is as simple as possible: A SQL can only operate on one CPU; a large statement splits the small statement, reducing the lock time; a big SQL can block the entire library.

21. Simple transaction: Transaction time is as short as possible

() or overwrite as in (); or overwrite to union (voiceover: The latest MySQL kernel has been optimized)

. Limit efficient paging: the higher the limit, the lower the efficiency of the Select ID from T limit 10000, 10; should be changed to = Select ID from t where ID > 10000 limit 10;

24. Use UNION ALL instead of union,union with a go-to-heavy overhead

25. Try not to join joins

26. Breaking Up Batch Updates

27. Using the New Energy analysis tool: explain;show slow log;

28. Use a fairly typed example in the Join table and index it: If your application has many join queries, you should confirm that the fields of join in two tables are indexed. In this way, MySQL internally initiates the mechanism for you to optimize the SQL statement for join.

29. Always set an ID for each table: we should set an ID for each table in the database as its primary key, and the best is an int type (recommended to use unsigned), and set the automatically added Auto_increment flag.

Content transferred from the public number: Architect's path

Go: Best 20+ for MySQL performance optimization experience: https://coolshell.cn/articles/1846.html

SQL Optimization (RPM)