要理解MySQL如何執(zhí)行查詢，首先使用EXPLAIN工具分析查詢計(jì)劃。1. 優(yōu)先查看type列，其值反映表的訪問效率，如system/const最好，ALL最差，應(yīng)盡量避免；2. 關(guān)注Extra列中的提示，如Using filesort和Using temporary表示排序或臨時表問題，需優(yōu)化索引或查詢結(jié)構(gòu)；3. 結(jié)合rows和filtered列評估查詢效率，若rows大而filtered小，說明篩選效率低，需改進(jìn)索引或條件順序；4. 通過創(chuàng)建復(fù)合索引、拆分復(fù)雜查詢、提前使用更精確條件等方式優(yōu)化查詢性能。示例中通過為customer_id和order_date建立復(fù)合索引，消除了文件排序，顯著提升了性能。掌握這些核心要點(diǎn)后，EXPLAIN將成為優(yōu)化查詢的關(guān)鍵工具。

When you're trying to understand how MySQL executes a query, the EXPLAIN statement is your go-to tool. It gives insight into how tables are joined, which indexes are used (or not), and where performance bottlenecks might be hiding. But reading an EXPLAIN plan can feel overwhelming at first — especially if you're new to query optimization.

Let’s break down what matters most in the output so you can start making informed tuning decisions.

Understanding the type column: How tables are accessed

The type column tells you how MySQL finds rows in a table. This is one of the most important indicators of query efficiency.

Here’s a rough ranking from best to worst for common types:

• system / const: Super fast — typically when you’re querying by a primary key or unique index.
• eq_ref: Usually appears in joins using primary keys — also very efficient.
• ref: Used when non-unique indexes are matched exactly (like WHERE col = value).
• range: Uses an index to retrieve a range of values — still good but not as fast as exact matches.
• index: Full scan of the index — slower than range, but better than scanning all rows.
• ALL: Full table scan — this is what you want to avoid if possible.

If you see ALL, it usually means there's no suitable index in place. Consider adding or modifying indexes to improve that step.

Pay attention to Extra: Hidden clues about performance

The Extra column often contains critical details that aren’t obvious elsewhere. Here are some common messages and what they mean:

• Using filesort: MySQL needs to do an extra sort pass because the data isn't ordered as expected. Try adjusting the index to include the columns used in ORDER BY.
• Using temporary: A temporary table is being created, often due to complex grouping or ordering. Look at simplifying the query or restructuring indexes.
• Using where: Rows are filtered after being retrieved — not necessarily bad, but could indicate room for optimization.
• Using index: Great! The query is satisfied entirely from the index without hitting the table data.

If you see both "Using filesort" and "Using temporary", that’s a red flag. You’ll want to look closely at how the query is structured and whether the right composite index exists.

Check rows and filtered: Estimating workload

MySQL tries to estimate how many rows will be examined during each step. That’s what the rows column shows. While these numbers aren’t always 100% accurate, they give you a ballpark idea of how much work is involved.

The filtered column adds more nuance — it shows the percentage of rows filtered by the table condition. If rows is high and filtered is low, that suggests the query isn’t narrowing things down efficiently. Again, this points to indexing issues or opportunities to rewrite the query.

For example:

• A rows value of 100,000 with filtered at 2% means only about 2,000 rows actually match the criteria. That’s a lot of wasted effort.

You can reduce this by:

• Creating covering indexes
• Breaking up complex queries
• Using more selective conditions earlier in joins

Use real examples to guide your changes

Say you have a query like this:

SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date DESC LIMIT 10;

And the EXPLAIN shows:

• type: ref on customer_id
• Extra: Using filesort

That tells you MySQL found the matching customer rows quickly but had to sort them afterward. To fix this, try creating a composite index:

CREATE INDEX idx_customer_date ON orders(customer_id, order_date DESC);

Now the same query may show:

• type: ref on the new index
• Extra: Using index condition; Using where

No filesort anymore — and that makes a big difference in performance.

In practice, reading EXPLAIN plans becomes easier once you know what to focus on. Start with type, then dig into Extra, and use rows and filtered to guide your tuning efforts. It’s not magic — just pattern recognition and a bit of trial and error.

基本上就這些。

以上是分析mySQL解釋查詢調(diào)整的計(jì)劃輸出的詳細(xì)內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！