In recent back-end interviews, MySQL indexing has become more frequent again.
I have read many candidates’ answers. The biggest problem is not memorizing too little, but memorizing it like a formula: functions will fail, scopes will fail later,like '%xx' Will be invalid, implicit conversion will be invalid. The interviewer continued to ask: "Does this SQL use indexes? Which paragraph is used? Why? Extra There are still Using index condition? "A lot of people just get stuck.
The term index failure is a bit crude.
A more accurate statement is: Can the optimizer still use the orderliness of the index for positioning? Can I also use columns in the index to filter in advance? Do I need to return the form in the end?
By separating these three questions, you will not be easily chased during the interview.
Let’s not talk about invalidation first. Let’s look at how the joint index is sorted.
Suppose there is a user table:
CREATE TABLE user_profile (
id BIGINT PRIMARY KEY,
age INT NOT NULL,
city VARCHAR(32) NOT NULL,
score INT NOT NULL,
name VARCHAR(64) NOT NULL,
INDEX idx_age_city_score (age, city, score)
);
idx_age_city_score Not three trees, nor three indexes put together.
The official MySQL manual describes multi-column indexes very straightforwardly: a multi-column index can be viewed as an array sorted by concatenating multiple column values. In other words, the order is roughly as follows: first press age Row,age Press again the same city Row,city Press again the same score Row.
This sentence is more important than the "leftmost prefix principle".
Because all subsequent judgments are asking the same thing: does your query condition use this ordered chain from left to right?
for example:
SELECT * FROM user_profile
WHERE age = 25 AND city = 'Hangzhou';
This one can be used age, city Two column positioning. Because I found it first age = 25 continuous interval, and then find in this interval city = Hangzhou, the sequence is not broken.
Look again:
SELECT * FROM user_profile
WHERE city = 'Hangzhou' AND score = 90;
This is not because it is not written in SQL age So "don't go to the index if you're in a bad mood", but because the entire index tree is first pressed age Row. You skipped the first column,city It is not continuously ordered globally, and the optimizer cannot directly use it for ordered positioning.
This is the essence of the leftmost prefix.
Why do range conditions cause subsequent columns to be "broken"?
Many stereotypes will write: The fields behind the range query are invalid.
This sentence is easily misleading. What is really broken is the ability to "continue to use orderliness for precise positioning", not that the subsequent columns are completely useless.
Look at this:
SELECT * FROM user_profile
WHERE age = 25 AND city > 'Hangzhou' AND score = 90;
The joint index is (age, city, score).
age = 25 Can be positioned to an interval.city > 'Hangzhou' It is also a range condition, and this interval can be further reduced.
but arrived score = 90, here comes the question: in city > Hangzhou In this range, the data is first pressed city Grouped and sorted. different city Each below has its own score order,score = 90 It is not a continuous interval in the entire remaining range.
so score Cannot continue to participate in the "positioning interval".
But it doesn't have to be completely useless.
If MySQL can see it directly in the index entry score, it may use Index Condition Pushdown, also known as ICP, to convert part of WHERE Pushed to the storage engine under certain conditions. The official manual also writes very clearly: The goal of ICP is to reduce the number of times that complete lines are read.EXPLAIN of Extra you will see Using index condition.
This is where many people get the answer wrong:
"Column following range invalid" is too bold.
A better answer is: it cannot continue to expand the ordered positioning prefix of the joint index, but it can be used as an index condition to push down early filtering and reduce table returns.
Functions, implicit conversions and prefix wildcards, the problem lies in "columns are hidden"
Let’s take a look at some common pitfalls.
SELECT * FROM user_profile
WHERE age + 1 = 26;
Many people will say that functions cause index failure. Strictly speaking, it is not the function itself that has magic, but what is stored in the index age an ordered value of , not age + 1 of ordered values.
If you wrap the column in an expression, the optimizer cannot directly locate it by the original ordered value in the index.
If changed to:
SELECT * FROM user_profile
WHERE age = 25;
The index is useful only if the columns are exposed.
Implicit conversion is a similar problem.
hypothesis city is a string type:
SELECT * FROM user_profile
WHERE city = 330100;
This type of writing may trigger type conversion. The actual conversion direction is related to the field type, character set, and optimizer rules. You cannot cover all situations in the interview. But the risk is clear: once the index column is converted into an expression to participate in the comparison, the original ordered search may be destroyed.
Therefore, the most stable way to write the project is to make the parameter type and field type consistent:
SELECT * FROM user_profile
WHERE city = '330100';
LIKE It's the same logic.
WHERE name LIKE 'open%'
The prefix is certain, and B+Tree can also find a range starting with "Zhang".
WHERE name LIKE '%open'
It is preceded by any character, and the beginning is uncertain. There is no way to start positioning from the ordered head of the index. no LIKE It is inherently impossible to index, because the prefix wildcard erases the starting point.
type = index Not necessarily good news
If the interviewer gives you a paragraph EXPLAIN, don’t just focus on “whether there is a key”.
I usually look at these fields:
key: Which index was actually selected by the optimizer. It doesn't matter which index you build, which one it will use.
type:Access method. The common order from best to worst can be roughly understood as const, eq_ref, ref, range, index, ALL. But don’t treat it as an absolute ranking, consider the number of rows and the scene.
rows: Estimate how many lines to scan. This value is very large, even if key There is something, or it may just be scanning a very wide index range.
Extra: See if there is any Using index, Using index condition, Using where, Using filesort.
Among them, the most easily misjudged is type = index.
It's not "it's good because it uses an index", it often means scanning the entire index. It may be a little lighter than scanning the entire table because the index page is smaller; but in essence it is still scanning, not precise positioning.
For example, if you only check the columns in the index:
SELECT age, city, score FROM user_profile
WHERE score = 90;
Because the query columns are all in idx_age_city_score Here, a covering index may occur, and there is no need to return the table. but score If it is not the leftmost column, the optimizer may still scan the index. At this time you see Using index, we cannot directly say that the performance is very good, we can only say that "the covering index avoids returning the table, but the positioning ability is not strong, it depends on the number of scanned rows."
This sentence is more reliable than memorizing "covering index is fast".
How do you answer this question in an interview as if you have actually done it?
Don't just report eight failure scenarios.
It can be said in this order:
First, look at the joint index order. Can it form a continuous positioning prefix starting from the leftmost column.
Second, look at the conditional form. Equality conditions usually preserve order, while range conditions prevent subsequent columns from being positioned in order. Functions, expressions, and implicit conversions may hide index columns.
Third, see if there is still in-index filtering capability. Subsequent columns cannot be positioned further, which does not mean they are completely useless. They may be filtered in advance at the storage engine layer through ICP.
Fourth, use EXPLAIN verify. Focus on key, type, rows, Extra, don’t just look at whether there is a hit index.
If the interviewer asks you to optimize a slow SQL on the spot, your answer can be as follows:
-- Original SQL
SELECT * FROM user_profile
WHERE city = 'Hangzhou' AND age = 25
ORDER BY score DESC;
If there is an existing index it is (age, city, score), this SQL even WHERE Written in order city, age, the optimizer can usually rearrange the equality conditions without worrying about the order of the SQL text.
What really matters is the business query pattern.
If most queries are by age, city Filter and press score Sorting, the index order is reasonable.
If the business often only presses city Check, that (age, city, score) It is not friendly to it and may require a separate city index, or replace it with city Union index at the beginning. But this is not "it's always good to build more indexes". Writing costs, disk space, and optimizer selection costs must be taken into account.
When I practice this type of question myself, I will throw several SQLs into Mianling's mock interview and have it continuously ask "Why can't this column continue to be positioned?"Using index condition and Using index "What's the difference?" This kind of questioning is more effective than running through a list, because you will soon find that you can't explain which step.
Finally, give a short answer template
If you are only given thirty seconds in an interview, you can answer this:
The MySQL joint index is not a combination of multiple single-column indexes, but a B+Tree sorted from left to right by the index column. To determine whether a SQL statement uses an index efficiently, you must first see whether it can form a continuous positioning prefix starting from the leftmost column. The equality condition can generally be used later. The columns following the range condition usually cannot continue to participate in ordered positioning, but may be filtered within the index through ICP. The essence of the problem with functions, expressions, implicit conversions, and prefix wildcards is that the optimizer cannot directly use the ordered values of the index column. Definitely use it in the end EXPLAIN verify, see key, type, rows and Extra, you can't just say "the index is gone" or "the index is invalid".
Being able to follow this passage is much easier than memorizing eight formulas to resist questioning.