USE-THE-INDEX-LUKE 中文翻译

原文 https://use-the-index-luke.com

文章目录

(创建对应 md 文件，并修改如下 title 为中文)

1. 前言 — 为什么索引是一项开发任务？
2. 解剖索引 — 一条索引长什么样？
   1. 叶子节点 -- 双向链表
   2. B-Tree -- 平衡树
   3. 慢索引，第一部分 -- 两个因素使索引变得缓慢
3. The Where Clause — Indexing to improve search performance
   1. The Equals Operator — Exact key lookup
      1. Primary Keys — Verifying index usage
      2. Concatenated Keys — Multi-column indexes
      3. Slow Indexes, Part II — The first ingredient, revisited
   2. Functions — Using functions in the where clause
      1. Case-Insensitive Search — UPPER and LOWER
      2. User-Defined Functions — Limitations of function-based indexes
      3. Over-Indexing — Avoid redundancy
   3. Bind Variables — For security and performance
   4. Searching for Ranges — Beyond equality
      1. Greater, Less and BETWEEN — The column order revisited
      2. Indexing SQL LIKE Filters — LIKE is not for full-text search
      3. Index Combine — Why not using one index for every column?
   5. Partial Indexes — Indexing selected rows
   6. NULL in the Oracle Database — An important curiosity
      1. NULL in Indexes — Every index is a partial index
      2. NOT NULL Constraints — affect index usage
      3. Emulating Partial Indexes — using function-based indexing
   7. Obfuscated Conditions — Common anti-patterns
      1. Dates — Pay special attention to DATE types
      2. Numeric Strings — Don’t mix types
      3. Combining Columns — use redundant where clauses
      4. Smart Logic — The smartest way to make SQL slow
      5. Math — Databases don’t solve equations
4. Testing and Scalability — About hardware
   1. Data Volume — Sloppy indexing bites back
   2. System Load — Production load affects response time
   3. Response Time and Throughput — Horizontal scalability
5. The Join Operation — Not slow, if done right
   1. Nested Loops — About the N+1 selects problem in ORM
   2. Hash Join — Requires an entirely different indexing approach
   3. Sort-Merge Join ‌— Like a zipper on two sorted sets
6. Clustering Data — To reduce IO
   1. Index Filter Predicates Intentionally Used — to tune LIKE
   2. Index-Only Scan — Avoiding table access
   3. Index-Organized Table — Clustered indexes without tables
7. Sorting and Grouping — Pipelined order by: the third power
   1. Indexed Order By — where clause interactions
   2. ASC/DESC and NULL FIRST/LAST — changing index order
   3. Indexed Group By — Pipelining group by
8. Partial Results — Paging efficiently
   1. Selecting Top-N Rows — if you need the first few rows only
   2. Fetching The Next Page — The offset and seek methods compared
   3. Window-Functions — Pagination using analytic queries
9. Insert、Delete与Update — 索引对DML语句的影响
   1. Insert — 无法直接从索引中获益
   2. Delete — 为where子句使用索引
   3. Update — 不会影响到表的所有索引

原文目录链接

Table of Contents

1. Preface — Why is indexing a development task?
2. Anatomy of an Index — What does an index look like?
   1. The Leaf Nodes — A doubly linked list
   2. The B-Tree — It’s a balanced tree
   3. Slow Indexes, Part I — Two ingredients make the index slow
3. The Where Clause — Indexing to improve search performance
   1. The Equals Operator — Exact key lookup
      1. Primary Keys — Verifying index usage
      2. Concatenated Keys — Multi-column indexes
      3. Slow Indexes, Part II — The first ingredient, revisited
   2. Functions — Using functions in the where clause
      1. Case-Insensitive Search — UPPER and LOWER
      2. User-Defined Functions — Limitations of function-based indexes
      3. Over-Indexing — Avoid redundancy
   3. Bind Variables — For security and performance
   4. Searching for Ranges — Beyond equality
      1. Greater, Less and BETWEEN — The column order revisited
      2. Indexing SQL LIKE Filters — LIKE is not for full-text search
      3. Index Combine — Why not using one index for every column?
   5. Partial Indexes — Indexing selected rows
   6. NULL in the Oracle Database — An important curiosity
      1. NULL in Indexes — Every index is a partial index
      2. NOT NULL Constraints — affect index usage
      3. Emulating Partial Indexes — using function-based indexing
   7. Obfuscated Conditions — Common anti-patterns
      1. Dates — Pay special attention to DATE types
      2. Numeric Strings — Don’t mix types
      3. Combining Columns — use redundant where clauses
      4. Smart Logic — The smartest way to make SQL slow
      5. Math — Databases don’t solve equations
4. Testing and Scalability — About hardware
   1. Data Volume — Sloppy indexing bites back
   2. System Load — Production load affects response time
   3. Response Time and Throughput — Horizontal scalability
5. The Join Operation — Not slow, if done right
   1. Nested Loops — About the N+1 selects problem in ORM
   2. Hash Join — Requires an entirely different indexing approach
   3. Sort-Merge Join ‌— Like a zipper on two sorted sets
6. Clustering Data — To reduce IO
   1. Index Filter Predicates Intentionally Used — to tune LIKE
   2. Index-Only Scan — Avoiding table access
   3. Index-Organized Table — Clustered indexes without tables
7. Sorting and Grouping — Pipelined order by: the third power
   1. Indexed Order By — where clause interactions
   2. ASC/DESC and NULL FIRST/LAST — changing index order
   3. Indexed Group By — Pipelining group by
8. Partial Results — Paging efficiently
   1. Selecting Top-N Rows — if you need the first few rows only
   2. Fetching The Next Page — The offset and seek methods compared
   3. Window-Functions — Pagination using analytic queries
9. Insert, Delete and Update — Indexing impacts on DML statements
   1. Insert — cannot take direct benefit from indexes
   2. Delete — uses indexes for the where clause
   3. Update — does not affect all indexes of the table