When is a MySQL index created?

Index can improve data retrieval efficiency and reduce the IO cost of the database, and the index can also reduce the sorting cost of the database. SortingGrouping operations mainly consume CPU resources and memory, so making good use of indexes in sorting and grouping operations will greatly reduce the consumption of CPU resources.

How to determine whether to create an index?
1. We all know the fields that are frequently used as query
conditions. What does it mean to teach frequently? Analyze all SQL statements you execute. It’s best to list them all one by one. Then after analysis, we found that some of the fields are used in most SQL statement queries, so we decisively created indexes for them.
2. Fields with poor uniqueness are not suitable for indexing
What are fields with poor uniqueness? Such as status field and type field. Those fields that only store a few fixed values, such as user login status, message status, etc. This involves the characteristics of index scanning. For example: Find some data with key values ​​A and B through the index, find a matching piece of data through A, and this data is on page X, and then continue scanning, and find that the data matching A appears on page Y. , then the storage engine will discard the data on the Page X will be scanned again, and page X will be scanned 2 times or even multiple times. By analogy, the same data page may be repeatedly read, discarded, and read again, which undoubtedly greatly increases the IO burden on the storage engine.
3, UpdateThe field that is too frequent is not suitable for creating an index
When you create an index for this field, when you update the field data again, the database will automatically Update its index, so when this field is updated too frequently, the index will be constantly updated. The impact on performance can be imagined. Only fields that have been retrieved dozens of times and updated once are more in line with the indexing specifications. And if a field is updated multiple times in the same time period, then it cannot be indexed.
4. Fields that do not appear in the where condition should not be indexed
I believe everyone knows this.

The above is the detailed content of When is a MySQL index created?. For more information, please follow other related articles on the PHP Chinese website!