Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you tips on mysql optimization. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

SQL performance Tip

Specific query performance tips (for tips on indexes, see database design tips):

1. Analyze the query execution plan using EXPLAIN

Use interpretation to view the plan for query execution

two。 Use slow query logs (always open! )

Use slow query logs (slow query logs)

3. Do not use DISTINCT when you have used or may use grouping

Do not use different when you can or are using grouping

4. Insertion performance

Insertion performance

1. Bulk insert and replace

Use bulk insert

two。 Use to load data instead of inserting

Load data instead of insert

5. Limit mmathn may not be as fast as it sounds

The limit mdirection n may not be as fast as it sounds.

6. If you have > ~ 2K records, do not use ORDER BY RAND ()

If your record is greater than 2000, please do not use Rand order ()

7. Use the SQL\ u NO\ u cache when selecting data or large datasets that are updated frequently

Use SQL\ u no\ u cache when querying data with frequently updated and large datasets

8. Avoid using wildcards at the beginning of LIKE queries

Avoid using wildcards in the header characters of variables that you like to query

9. Avoid related subqueries and in select and where clauses (avoid in as much as possible)

Avoid related subqueries..

10. No calculation comparison-isolated index column

Do not use calculated comparisons-index columns are still valid

11. Work best with equality and overlay indexes in order and restrictions

Subscribers and limits are more efficient when indexed and equal to conditions

twelve。 Separate the text / blob from the metadata and do not put the text / blob in the result if you don't need it

Separate the text / speckle data and do not include them in the result if you don't need it.

13. Derived tables (subqueries in the FROM clause) can be used to retrieve BLOB without sorting it. (if the first part finds ID and uses then to get the rest of the ID, self-join can speed up the query.)

14.ALTER TABLE... ORDER BY can sort the data in chronological order and reorder it by different fields-which makes the queries on that field run faster (maybe in the index? )

15. Know when to split complex queries and join smaller queries

Know when to split complex queries and merge simple queries

16. If you can, delete a small amount at a time

17. Keep similar queries consistent so that caching can be used

Change similar queries to identical queries, so that hidden objects can be used.

18. Have good SQL query standard

19. Do not use features that are not recommended

Do not use features that are not recognized

20. Set multiple index fields (

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.