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

Indexes can improve the retrieval efficiency of the data, reduce the IO cost of the database, and the index can reduce the sorting cost of the database. Sorting grouping operations mainly consumes CPU resources and memory, so the ability to make good use of indexes in sorted grouping operations can greatly reduce CPU resource consumption.

How do I determine if I need to create an index?

1, more frequently as a query criteria for the field

This all knows. What is teaching frequently? Analyze all the SQL statements you execute. It's best to list them all. Then analyze and find that some of these fields are used in most SQL query queries, and then decisively index them.

2, the uniqueness of the field is too bad for indexing

What is the uniqueness of a field that is too bad. such as the Status field, the Type field. Those fields that store only a few values, such as user login status, message status, and so on. This involves the characteristics of the index scan. For example, to find some data with a key value of a and B through an index, by a find a match of data, this data on X page, and then continue to scan, and found that conforms to A's data appears on the Y page, then the storage engine will discard the X page data, and then store the data on the Y page, Until you find all the corresponding A's data, and then find the B field, find the X page with the corresponding B field data, then he will scan the X page again, equal to X page will be scanned 2 or more times. And so on, so that the same data page can be repeatedly read, discarded, in the read, which undoubtedly to the storage engine greatly increased the burden of IO.

3, update too frequently fields are not appropriate to create an index

When you create an index for this field, when you update the field data again, the database will automatically update his index, so when the field is updated too frequently, then it is constantly updating the index, the impact of performance can be imagined. The fields that were probably retrieved dozens of times before are updated to match the indexing specification. And if a field is updated multiple times within the same time period, then decisiveness cannot be indexed for him.

4. Fields that do not appear in the Where condition should not be indexed

I believe we all know this.