SQL Indexing and Tuning e-Book
source link: https://use-the-index-luke.com/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
SQL Indexing and Tuning e-Book
A site explaining SQL indexing to developers—no crap about administration.
SQL indexing is the most effective tuning method—yet it is often neglected during development. Use The Index, Luke explains SQL indexing from grounds up and doesn’t stop at ORM tools like Hibernate.
Use The Index, Luke is the free web-edition of SQL Performance Explained. If you like this site, consider getting the book. Also have a look at the shop for other cool stuff that supports this site.
SQL Indexing in MySQL, Oracle, SQL Server, etc.
Use The Index, Luke presents indexing in a vendor agnostic fashion. Product specific notes are provided like here:
Use The Index, Luke covers SQL indexing for the Oracle database. Tests were conducted with Oracle 11g through 19c.
PostgreSQLSQL ServerHave more questions about SQL indexing or tuning? No problem—have a look at my training and tuning services at winand.at.
Table of Contents
Preface — Why is indexing a development task?
Anatomy of an Index — What does an index look like?
The Leaf Nodes — A doubly linked list
The B-Tree — It’s a balanced tree
Slow Indexes, Part I — Two ingredients make the index slow
The Where Clause — Indexing to improve search performance
The Equals Operator — Exact key lookup
Primary Keys — Verifying index usage
Concatenated Keys — Multi-column indexes
Slow Indexes, Part II — The first ingredient, revisited
Functions — Using functions in the
where
clauseCase-Insensitive Search —
UPPER
andLOWER
User-Defined Functions — Limitations of function-based indexes
Over-Indexing — Avoid redundancy
Bind Variables — For security and performance
Searching for Ranges — Beyond equality
Greater, Less and
BETWEEN
— The column order revisitedIndexing SQL
LIKE
Filters —LIKE
is not for full-text searchIndex Combine — Why not using one index for every column?
Partial Indexes — Indexing selected rows
NULL
in the Oracle Database — An important curiosityNULL
in Indexes — Every index is a partial indexNOT NULL
Constraints — affect index usageEmulating Partial Indexes — using function-based indexing
Obfuscated Conditions — Common anti-patterns
Dates — Pay special attention to
DATE
typesNumeric Strings — Don’t mix types
Combining Columns — use redundant
where
clausesSmart Logic — The smartest way to make SQL slow
Math — Databases don’t solve equations
Testing and Scalability — About hardware
Data Volume — Sloppy indexing bites back
System Load — Production load affects response time
Response Time and Throughput — Horizontal scalability
The Join Operation — Not slow, if done right
Nested Loops — About the N+1 selects problem in ORM
Hash Join — Requires an entirely different indexing approach
Sort-Merge Join — Like a zipper on two sorted sets
Clustering Data — To reduce IO
Sorting and Grouping — Pipelined
order by
: the third powerIndexed Order By —
where
clause interactionsASC
/DESC
andNULL FIRST
/LAST
— changing index orderIndexed Group By — Pipelining
group by
Partial Results — Paging efficiently
Selecting Top-N Rows — if you need the first few rows only
Fetching The Next Page — The offset and seek methods compared
Window-Functions — Pagination using analytic queries
Insert, Delete and Update — Indexing impacts on DML statements
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK