查詢與檢索架構
另有 19 種語言版本
English · Español · 日本語 · Português (Brasil) · Français · Deutsch · Italiano · 한국어 · 简体中文 · Português (Portugal) · Bahasa Indonesia · Türkçe · Polski · Tiếng Việt · ไทย · Українська · Español (México) · Français (Canada) · Deutsch (Schweiz)
Data Architect 課程第 8 章的學習指南。請先閱讀五個模組；這一頁幫你檢查自己記住了什麼。
**關於本學習指南。**它只依據本課程自己的內容，以及 Salesforce 公開發布的考試指南中的目標清單編寫。它從不基於真實的考試題目，也不聲稱知道考試會考什麼。如果你參加過考試，請不要把考題發給我：認證協議禁止分享考題，而且死記答案本來就不是成為架構師的正確方式。
This chapter is the query and search half of the exam’s Large Data Volume Considerations section (20% of the exam). The three checks and the thresholds are where I’d spend the most time: they’re the ideas people find hardest, and the thresholds are worth being able to work out on paper. Platform numbers change, so check the current ones before you rely on them. ⟲
邊學邊打勾。每一條都連結到講授它的地方。
Inside the engine (module 1) ⟲
- I know Salesforce is multitenant and metadata-driven: many orgs share one platform, kept apart by the org ID, and adding a field adds metadata, not a database column. (One Building, Many Tenants, No New Tables, Just Metadata) ⟲
- I know standard and custom fields are stored separately, so a query that uses both does a join, and that skinny tables avoid it. (The Join You Never Wrote) ⟲
- I know custom fields can’t use a native database index, so Salesforce copies indexed values into index tables. The internal table names aren’t exam material. (Why a Normal Index Doesn’t Fit) ⟲
Indexes (module 2) ⟲
- I can explain a table scan and an index (the library and the card catalog), and that an index is a copy: it makes finding faster and every save slower. (The Advanced Technology: Card Catalogs, Every Copy Has to Be Kept Up to Date) ⟲
- I know SOQL asks by structure and SOSL asks by words. (Two Ways to Ask) ⟲
- I know the standard indexed fields: RecordTypeId, Division, CreatedDate, Systemmodstamp (LastModifiedDate), Name, Email on contacts and leads, lookup and master-detail fields, and the record ID. (Standard Indexes) ⟲
- I know a custom index comes from Salesforce Customer Support or the Metadata API, and that marking a custom field as an External ID (Auto Number, Email, Number or Text) or Unique indexes it. (Custom Indexes) ⟲
- I know which fields can’t have a custom index: long and rich text areas, multi-select picklists, encrypted text, currency in a multi-currency org, and non-deterministic formulas. A formula can be indexed only if it’s deterministic: no TODAY() or NOW(), and no values from another object. (Which Fields Can Be Indexed) ⟲
When Salesforce uses an index (module 3) ⟲
- I know the three checks, the nitro: the field is indexed; I ask in a selective way; and few enough records match. (The Nitro Boost, All Three: the Boost) ⟲
- I know what asking in a selective way means. Selective: equals, starts with (
LIKE 'abc%'), a shortINlist, a range on a date or number. Not selective: contains and ends with (a leading%), negative operators (!=,NOT LIKE,NOT IN) and comparisons with an empty value. (Asking the Index the Right Way) ⟲ - I can work out the thresholds: standard index, 30% of the first million records plus 15% beyond, capped at 1 million; custom index, 10% plus 5%, capped at 333,333. A standard index on 2 million records allows 450,000; a custom index on 1 million allows 100,000. (The Thresholds) ⟲
- I know a filter can stop being selective without the query changing: cases close, history piles up. (The Closed-Cases Trap) ⟲
- I plan with 30% growth a year, and I don’t confuse it with Well-Architected’s 30% reserve under governor limits. (Add 30% Growth) ⟲
How Salesforce executes a query (module 4) ⟲
- I know the four words: query (which records), fetch (how much you carry back), table scan and join. (Four Words to Know) ⟲
- I know the query optimizer runs pre-queries against its statistics, judges each filter with the three checks, drives from the best index or from the table, and orders the joins; and that a user who sees all records can get a different plan from one who sees only their own. (The Librarian Is the Query Optimizer) ⟲
- I know the optimizer runs for SOQL, reports and list views: a contains or not-equal filter slows a list view as much as code. (When Is the Librarian Working? All the Time) ⟲
- I measure the real distribution, then read the Query Plan: the lowest cost wins, and a cost above 1 means the filter isn’t selective. (Measure the Distribution, Then Ask the Optimizer) ⟲
- I know with AND the most selective indexed filter can drive; with OR, each filter must meet the threshold on its own and every field must be indexed. (More Than One Index: Which One Drives?) ⟲
- I know a two-column index: the first column selects, the second sorts or narrows; the order matters; nulls are allowed in the second column. (Two-Column Indexes) ⟲
- I know a skinny table is not an index: Customer Support creates it; up to 200 columns from one object, no fields from other objects; it leaves out soft-deleted records, avoids the join, and is used automatically when a query, report or list view uses only its fields; custom objects plus Account, Contact, Opportunity, Lead and Case; copied to Full sandboxes only. (Skinny Tables) ⟲
- I know divisions partition a large org’s data to reduce the records queries and reports return: more than one million records in one object, more than 35 licenses, enabled by Customer Support. (Divisions: Splitting a Huge Object) ⟲
- I know Salesforce keeps database statistics, gathered nightly, and that after large loads, updates or deletes through the API, queries may not run efficiently until they’re gathered. (Database Statistics: The Librarian’s Notebook) ⟲
- I can choose between index, rewrite, redesign and archive, and I test at realistic volume and distribution. (When It’s Slow: Index, Rewrite, Redesign or Archive, Correct Isn’t the Same as Scalable) ⟲
Search (module 5) ⟲
- 我知道什麼時候該用 SOSL，而不是一堆 SOQL 的 OR。（查詢從結構開始；搜尋從詞語開始）
- I know search finds tokens in its own index first, then fetches the records, and that the index is asynchronous: about 15 minutes or more for new text, and hours after a bulk load. (How Search Works: Tokens First, Then the Fetch, Freshness Is Part of the Design) ⟲
- I know SOSL’s limits: at most 2,000 records searched, then permissions applied; 250 for one object unless you add WHERE or ORDER BY; up to 2,000 divided by the number of objects (and no more than 250 each) when you search several; 20 SOSL queries per Apex transaction. (Asking Search Well) ⟲
- I can explain search crowding: the cut at 2,000 comes before sharing, so records a user can’t see take up places, and a record they can see can be left out. (Search Crowding, Step by Step) ⟲
- I know the levers: search groups (NAME, EMAIL, PHONE), RETURNING, specific terms over wildcards, and wildcards only in the middle or at the end. (Asking Search Well) ⟲
- I know how to reduce crowding: decide which objects each profile searches, take noisy fields out of the index, archive history, and check as the user. (Mitigating Search Crowding: A Playbook) ⟲
考試是底線，不是天花板。下面這些是本章中架構師需要的習慣，不管有沒有考題問到它們。
- Design against the behavior Salesforce documents, not a table layout. Salesforce rebuilt the engine underneath once already. ⟲
- An index is a copy. Every one makes finding faster and saving slower. ⟲
- “Don’t worry, that field is indexed” is never the end of the conversation. Ask the three questions, then do the math. ⟲
- A query can go bad without changing. ⟲
- Measure before you optimize, and test at the zeros you’ll actually have. ⟲
- 當索引是乾淨、通用的解決方案時才建索引；對一次性的需求，寧可重新設計也不要建索引。
- Metadata should tell the truth. Don’t mark a field as an External ID just for the index. ⟲
- 有時最好的查詢最佳化，是一個更小的活動資料集。
- **用搜索來發現；用查詢來運營。**搜尋不是資料模型。
- Data can deserve to exist without deserving to appear in search. ⟲
- Search runs as someone. Check as the user, not as an admin. ⟲
| 術語 | 一句話解釋 |
|---|---|
| Multitenant | Many companies’ orgs share one platform, kept apart by the org ID |
| Metadata-driven | Objects and fields are described as data, not built as database tables |
| Table scan | Touching every record to find the ones you want |
| Index | A copy of one field’s values, with each record’s location, for finding them fast |
| Index table | Where Salesforce copies indexed custom-field values |
| Query optimizer | The part of the platform that chooses the cheapest path for every query, report and list view |
| Selective | Asked in a way an index can use, and matching few enough records |
| Selectivity threshold | The most records an indexed filter can match and still be used |
| Query | Finding which records match |
| Fetch | Carrying back the fields of the records found |
| Join | Crossing from one table or object to another |
| Query Plan | A tool showing the optimizer’s possible paths and their relative cost |
| Custom index | An index on a custom field, from Customer Support, the Metadata API or an External ID |
| 雙列索引 | 覆蓋兩個經常一起使用的欄位的一個索引 |
| Skinny table | A copy of selected fields from one object, kept by Salesforce, for fast reads without the join |
| Division | A partition of a large org’s data that reduces the records queries return |
| Database statistics | The counts the optimizer uses to choose a plan, gathered nightly |
| SOQL | Salesforce 用於結構化問題的查詢語言 |
| SOSL | Salesforce’s search language for finding words across objects |
| Search crowding | Matches the user doesn’t want, or can’t see, filling the 2,000 places before the record they need |
按課程風格編寫的簡短情景題。開啟答案之前，先想好你的答案。
1. 一個物件有 1,000 萬條記錄。一個建了自訂索引的欄位上的篩選條件返回 400,000 條。最佳化器會用這個索引嗎？
Probably not. The custom-index threshold is 10% of the first million plus 5% beyond, capped at 333,333; at 10 million the cap applies, and 400,000 is over it. Check with the Query Plan. Reread The Thresholds.
2. An object has 2 million records. A filter on Name, a standard index, returns 500,000. Selective?
⟲No. A standard index allows 30% of the first million (300,000) plus 15% of the next million (150,000): 450,000. At 500,000 the filter is over the threshold, and the optimizer will look elsewhere or scan the table. Reread As the Object Grows.
3. A list view on 8 million cases filters on Status not equal to Closed. Status has a custom index. Why is it slow?
⟲A negative operator isn’t asked in a selective way, so the index can’t help, and the optimizer runs for list views just as it does for code. Ask positively instead, for the open statuses, and check that the open cases stay under the threshold. Reread Asking the Index the Right Way and When Is the Librarian Working? All the Time.
4. A query on IsClosed = true was fast at launch and is slow three years later. Nothing in the code changed. Why?
⟲資料老化了：案例會關閉，所以一個曾經只比對一小部分的篩選條件，現在幾乎比對一切。選擇性取決於分佈，而分佈會隨時間變化。重讀已關閉案例的陷阱。
5. Someone asks for an index for a report run once a year. What do you ask?
⟲Why it’s slow, and whether a permanent index is worth its cost for a one-off. Measure the distribution and read the Query Plan first; consider rewriting the query, redesigning, or archiving history so the active population is smaller. Reread Before You Ask for an Index.
6. An important query needs recent line items for one Retail Partner, but the partner is on the parent work order, and there are 14.5 million line items. Options?
⟲Redesign: copy the partner onto the line item when it’s created (and index it if the filter needs it), so the query filters the child directly. Weigh the benefit against the duplicate data and its upkeep; here the partner never changes, which helps. Reread When It’s Slow: Index, Rewrite, Redesign or Archive.
7. "Let's ask Support for a skinny table index on Account." What's wrong with that sentence?
⟲A skinny table isn’t an index. It’s a copy of selected fields from one object, which avoids the join between standard and custom fields and makes the fetch lighter. It’s used automatically only when a query, report or list view asks for its fields alone. Reread Skinny Tables.
8. Last night the team loaded 2 million records through the Bulk API. This morning some queries are slow and new records can't be found in search. Broken?
⟲Probably not. The optimizer’s statistics are gathered nightly, so they may not reflect a very large load yet; and the search index is asynchronous, so a bulk load can take hours to become searchable. Plan loads with both in mind. Reread Database Statistics: The Librarian’s Notebook and Freshness Is Part of the Design.
9. A user types any text and expects to find the customer by name, email, phone or notes. The developer builds a SOQL query with ten LIKE clauses joined by OR. Better?
⟲It’s a search problem: use SOSL, which is built for words across fields and objects. A long OR chain also needs every filter to be selective and indexed on its own, and contains-style LIKE never is. Reread Query Starts With Structure; Search Starts With a Term.
10. A care agent searches for a customer's last name and gets 1,900 results, but not the case she needs, which she can open from a list view. Why?
⟲Search crowding. The name matched more than 2,000 records, and search keeps the first 2,000 before it checks what she can see: 100 of those she can’t see, so 1,900 come back, and her case may be among the matches left out. Use a more specific term, search one object, and reduce the noise at the source; then check as her, not as an admin. Reread Search Crowding, Step by Step and Mitigating Search Crowding: A Playbook.
11. Automation must find a delivery exception the moment it's created. Use search?
⟲不用。搜尋索引是非同步更新的，可能滯後 15 分鐘或更久。流程知道結構，所以結構化查詢是確定性的選擇。重讀新鮮度是設計的一部分。
- Salesforce Certified Platform Data Architect Exam Guide （在新標籤頁中開啟）, Salesforce Help, checked 2026-11-01: the official list of objectives and their weights. Always check the current version before you book the exam. ⟲
- 每個模組自己的「延伸探索」部分列出了其事實所依據的 Salesforce 文件，並註明了查閱日期。