[Java Interview] Please tell me the advantages and disadvantages of Mysql index?

The interview question shared today made a young man who has worked for 4 years go to Dianping and get an annual salary of 60W.

This interview question is: "Please tell me the advantages and disadvantages of Mysql index"

Regarding this question, see the answers of ordinary people and experts

Ordinary people:

Um. . . . . . . . .

Expert:

An index is a data structure that helps Mysql to efficiently retrieve data from disk.

In the InnoDB engine in Mysql, the B+ tree structure is used to store indexes and data

In my opinion, there are many advantages of indexes in Mysql

1. Storing data through the structure of B+ tree can greatly reduce the number of disk IO during data retrieval, thereby improving the performance of data query
2. When the B+ tree index performs range search, it only needs to find the starting node, and then read it down based on the linked list structure of the leaf node, and the query efficiency is high.
3. Through the unique index constraint, the uniqueness of each row of data in the data table can be guaranteed

Of course, the unreasonable use of indexes will also bring many disadvantages.

1. The addition, modification, and deletion of data need to involve the maintenance of the index. When the amount of data is large, the maintenance of the index will bring about a large performance overhead.
2. One clustered index and multiple non-clustered indexes are allowed in a table, but the number of indexes cannot be created too much, otherwise the cost of index maintenance will be too high.
3. When creating an index, you need to take into account the dispersion of index field values. If there is too much duplicate data in the field, creating an index will reduce performance.

In my opinion, any technical solution will have two sides. In most cases, the choice of technical solution is more based on its advantages and the matching degree of the current problem.

The above is my understanding of the problem.

Summarize

Intensified competition in the industry, coupled with the current bad environment, all first- and second-tier factories are laying off employees.

The problem is that it is more difficult to select talents and find jobs more and more difficult.

This question examines the job seeker's understanding of Mysql. It is not difficult, but many people can be stuck.

Friends who like it remember to like and favorite.

If you have any work and study questions, you can send me a private message at any time.

Copyright notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless otherwise stated. Please indicate the source for Mic带你学架构 !
If this article is helpful to you, please help to follow and like, your persistence is the driving force for my continuous creation. Welcome to follow the WeChat public account of the same name to get more technical dry goods!