Under what circumstances do I need to create a MySQL index? _ MySQL

This article mainly introduces how to create a MySQL index? This article also describes which situations are not suitable for creating MySQL indexes. if you need an index, you can refer to the index to improve data retrieval efficiency and reduce the I/O cost of the database, in addition, indexes can reduce database sorting costs. Sorting group operations mainly consume CPU resources and memory. Therefore, using indexes in sorting group operations can greatly reduce CPU resource consumption.

How can I determine whether to create an index?

1. fields frequently used as query conditions

We all know this. What is frequent teaching? Analyze all the SQL statements you run. It is best to list them one by one. After analysis, we find that some of the fields are used in most SQL statement queries, so we can create an index for them.

2. fields with poor uniqueness are not suitable for indexing.

What is a field with poor uniqueness. Such as status field and type field. Fields that only store fixed values, such as user logon status and message status. This involves the features of index scanning. For example, you can use an index to search for data with key values A and B, and use A to find the data that matches A. the data is displayed on page X and then continues scanning, the storage engine then discards the data on the X page and stores the data on the Y page, until all the data corresponding to A is searched, and then field B is searched, and the data corresponding to field B is found on page X. then, the system scans the page X again, the X page is scanned twice or more times. Similarly, the same data page may be read and discarded multiple times, and read. This will undoubtedly add a great IO burden to the storage engine.

3. fields updated too frequently are not suitable for index creation

When you create an index for this field, when you update the field data again, the database automatically updates its index, so when this field is updated too frequently, the index is constantly updated, and the impact of performance can be imagined. The fields updated after dozens of queries are compared to the index creation rules. If a field is updated multiple times in the same time period, you cannot create an index for it.

4. fields that do not appear in the where condition should not be indexed

I believe everyone knows this.