[Java Interview] This Internet high-frequency interview question stumped 80% of programmers? When does the index expire?

"When does the index expire?"

During the interview process, a question that came up suddenly, was it a bit confusing?

It doesn't matter, follow me, don't get lost in the interview.

I'm Mic, a Java programmer who has been working for 14 years.

There are a lot of knowledge points involved in index failure, so I have organized the answer to this question into a 20W interview document, which you can get.

Check out the experts' answers below

Expert:

There are two types of indexes in the InnoDB engine, one is the primary key index and the other is the ordinary index.

InnoDB uses a B+ tree structure to store index data.

When using the index column for data query, the corresponding data row will eventually be queried in the primary key index tree for return.

In theory, querying using indexed columns can greatly improve query efficiency, but non-standard use will cause the index to fail, thus failing to give full play to the value of the index itself.

There are many situations that can cause an index to fail:

1. Perform operations on indexed columns, such as using functions. When Mysql generates an execution plan, it determines whether to use an index based on statistical information.

   Adding a function operation to the index column causes Mysql to not recognize the index column, so it will no longer go through the index.

   However, starting from Mysql8, adding functional indexes can solve this problem.

2. In a composite index composed of multiple columns, it is necessary to follow the leftmost matching rule, that is, to search sequentially from the leftmost column of the index, otherwise the index will not be taken.

   In a composite index, the storage structure of the index is stored in the order of the index columns, so it is also necessary to match them one by one in SQL.

   Otherwise, InnoDB cannot identify the index and the index will be invalid.

3. When there is an implicit conversion of the index column, such as the index column is a string type, but quotes are not used in the SQL query.

   Then Mysql will automatically perform type conversion, which will cause the index to fail.

4. When the index column uses the unequal sign, not to query, because the retrieval efficiency of the index data is very low, the Mysql engine will judge not to use the index.

5. When using the like wildcard to match the suffix %xxx , because this method does not conform to the leftmost matching principle of the index, it will not follow the index.

   But conversely, if the wildcard matches the prefix xxx% , which matches the leftmost match, the index will also be taken.

6. When using or to connect the query, or does not use the index before and after the statement, then the index will be invalid. It will take effect only when or the left and right query fields are all index columns.

In addition to these scenarios, for multi-table join query scenarios, the join order also affects the use of indexes.

However, whether to use the index in the end, we can use the explain command to view the execution plan of sql, and then tune it accordingly.

Summarize

Many problems in Mysql can be investigated. After all, it is also a very frequently used component in work. Logically speaking, it is necessary for us to deeply study the underlying principles of Mysql.

After all, data security and data IO performance will affect the overall throughput of the system.

Remember to like, subscribe and follow.

Copyright notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless otherwise stated. Please indicate the source of 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!