Core point of view: Whether the index is fast or not does not depend on whether the index is indexed, but on how many rows are scanned, how many times the table is returned, and how the data is distributed.

1. Origin: the leftmost prefix principle

It all starts with a seemingly simple question:

Does the leftmost prefix principle of index only apply to joint indexes?

The answer is yes.

The leftmost prefix is Composite Index exclusive concept of , and in SQL WHEREThe order in which the conditions are written does not matter, only theField order when index is definedrelated.

CREATE INDEX idx_a_b_c ON t(a, b, c);

Query that can be indexed:

WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

Query that cannot be indexed:

WHERE b = 2
WHERE b = 2 AND c = 3

More importantly, I corrected a misunderstanding in myself:

The left and right order of the conditions in SQL is not important, but the left and right order in the index definition is important.

The MySQL optimizer will automatically rearrange conditions,WHERE b=1 AND a=1and WHERE a=1 AND b=1It makes no difference to the optimizer.

2. EXPLAIN type: the first indicator to judge the quality of the index

After understanding the leftmost prefix, another question naturally arises:

How do I know if the index is being used well?

The answer is to look EXPLAINof typefield.

Performance from best to worst:

const > eq_ref > ref > range > index > ALL
  • const/ eq_ref: Primary key/unique index, ultimate performance
  • ref: Ordinary index equivalent query, ideal state
  • range: Index range scan, acceptable
  • index:Full index scan, be vigilant
  • ALL: Full table scan, must be optimized

The bottom line for slow query optimization: at least achieve range。

3. Index experiment under tens of millions of data

In order to verify these theories, I imagined a scenario of a tens of millions order list and deduced it step by step.

1️⃣ Joint index + range query

Assume there are 10 million orders and 1,000 salesmen. The index is as follows:

CREATE INDEX idx_name_price ON orders(name, order_price);

Query:

SELECT * FROM orders
WHERE name IN ('a', 'b')
AND order_price > 100;

in conclusion:

  • ✅ Can walk idx_name_price
  • ✅ type = range
  • ❌ But SELECT *will result in a large number ofreturn table

2️⃣ Table return: the invisible killer of slow queries

Reply refers to:

Get the primary key ID through the secondary index, and then go back to the clustered index to get the complete data.

Replying to the table itself is not slow.What is slow is "a large number of random table returns" 。

So I realized a classic optimization method:Delayed return to table

-- 第一步：只拿 ID
SELECT id FROM orders
WHERE name IN ('a','b') AND order_price > 100;

-- 第二步：用 ID 查详情
SELECT * FROM orders WHERE id IN (...);

✅ First query: pure index scan, no table return

✅ Second query: precise positioning of primary key, sequential IO

The slow return is not because of "return", but because of "random return".

3️⃣ Range query vs Equivalence query

Continuing with the deduction, if the conditions are changed to:

SELECT id, price FROM orders WHERE price > 100;

even though priceThere is an index on it, and it is a covering index (Using index), may not be fast.

The reason is:

  • price > 100Likely to hit 30%~50% of the data
  • Too many scan lines
  • The optimizer may even abandon the index and choose a full table scan

And if you change to equivalent query:

SELECT id, price FROM orders WHERE price = 100;

The situation is completely different:

  • type = ref
  • Very few scan lines
  • Even if the data is completely out of order, B+Tree can locate it in milliseconds

Equivalence query is the "king of indexes", while range query is what you need to be wary of.

4. String index: Realistic torture of address query

Finally, I pushed the problem to a more complex real-world scenario:

If we build an index for a long text like "address", can the equivalent query return instantly with tens of millions of data?

The answer is:Yes, but there is a price.

✅Why is it so fast?

  • B+Tree does not care about data type
  • String equivalent query is also O(log N)
  • Lookup complexity is consistent with numeric indexing

❌ Why is this rarely done in engineering?

  1. Index size is large: Strings are 5 to 10 times larger than numbers, and the Buffer Pool is under great pressure.
  2. High write cost: The page split probability is high, and the performance in multiple write scenarios is degraded.
  3. Data is unstable: The address input is not standardized and the hit rate of equivalent query is low.
  4. Query requirements do not match: Real scenes are mostly fuzzy queries (LIKE '%福田%'）

✅ A more reasonable approach

  • Split fields:province, city, district
  • Address normalization + hash index
  • Handle complex searches to Elasticsearch

Not building an address index is not because it is slow, but because it is "not worth it".

5. Summary: Four levels of my understanding of indexes

Through this review, my understanding of indexes has evolved from "being able to use it" to "understanding trade-offs":

  1. grammatical layer:Know CREATE INDEX, knowing the leftmost prefix
  2. diagnostic layer: Will read EXPLAIN,understand type/ rows/ Extra
  3. performance layer: Understand the differences between table returns, covering indexes, and equality/range queries
  4. Engineering layer: Understand the cost of indexing, the impact of data distribution, and matching business scenarios

In short:

**The index solves "filtering", not "scanning";

Whether it is fast or not depends not on whether the index has been followed, but on how many rows have been scanned. **

Attached: Interview shorthand tips

  • The leftmost prefix depends on the definition, not the SQL order.
  • type at least to range, avoid ALL and index
  • Equivalence query is king, range query looks at proportion
  • Don’t be afraid of too many table returns. The primary key is the safest way to return tables.
  • Strings are faster but not worth it. Address splitting is more reasonable.

📌 This article is already a complete technical review, if you post it on your blog, it is a very solid MySQL index study notes + interview materials。

If you are willing, I can help you with the next steps:

  • ✅Give it 3 to 5 piecesHot headlines(Suitable for Nuggets / Zhihu)
  • ✅ Abbreviated into one "Interview 3-minute self-introduction version"
  • ✅Another extra story: "The interviewer asked me: Is it better to build more indexes?" 》

Just tell me what you want to do next 👍