Tag: MySQL
All the articles with the tag "MySQL".
-
MySQL Credit Deduction — 4 Locks Compared, Pessimistic at 180ms / 100% accurate, plus the self-invocation trap I hit during measurement
An ordinary scenario — 100 workers concurrently subtracting 1 from an account with balance 100. Four lock strategies (optimistic / pessimistic / MySQL GET_LOCK / Redisson) all produce different results — pessimistic 180ms / 100% / balance 0, optimistic 549ms (retry storm under contention), GET_LOCK 5015ms (advisory lock cost), Redisson 53/100 (single-instance limitation). And during measurement I hit the self-invocation trap — successes=100 but the balance never moved. The real Spring/JPA pitfall is not logic, it is AOP proxy bypass. A walkthrough including direct demos of the connection-bound GET_LOCK traps in 4 scenarios.
-
RDB Mastery #3 — Mastering EXPLAIN ANALYZE: Push Down Traps and the Real Mechanics of Index Selection
Once you can read a single line of an EXPLAIN ANALYZE operator tree, you can directly verify the optimizer's decisions. Filter vs Index Range Scan over — a one-word difference that splits push down success from failure. The ANSI SQL standard row constructor (a,b)<(?,?) doesn't match the MySQL optimizer's whitelist patterns and fails to push down — Bug #16247, filed in 2006, is a long-standing known limitation (currently marked duplicate in the tracker). Index Selection is also a cost-based judgment by the optimizer — the Q2 paradox (with a small LIMIT 5, the optimizer picks the wrong index and adding an index makes it slower). The optimizer is not right 100 percent of the time. We unpack the push-down mechanism and the internals of cost-based index selection by reading 5 EXPLAIN ANALYZE outputs line by line, measured on 10 million rows.
-
RDB Mastery #2 — MySQL Index Types: B-tree / Hash / Covering / Composite / Multi-valued / Functional, and When to Pick What
Not every index in InnoDB is a B-tree. Hash (Memory engine), Spatial (R-tree), Full-text (inverted index), Multi-valued (8.0+, JSON arrays), Functional (8.0.13+, expressions). And even within the B-tree family, clustered vs secondary, covering or not, the leftmost-prefix rule for composites, and cardinality / selectivity become the decision axes. Built five real indexes on a 10M-row table and decided when to pick what by measuring cardinality + Q1~Q5 latency. Q3 covering 2,476x / Q5 composite 577x / Q2 paradox where adding an index made it slower (0.66ms → 13.5ms). Indexes are not free — write cost 5~6x + storage 1.3GB. Unwound to the end with 9 diagrams.
-
RDB Mastery #1 — InnoDB Index Internals: From No-Index to Multi-Index, the Real Picture B-trees Draw
Even when you don't define an index, InnoDB already stores rows inside a B-tree. PK = clustered index = the table itself. Secondary index = a separate B-tree that points to PK. Covering index = an index where the answer lives in the leaf, no PK lookup needed. Reverse scan = walking the leaf doubly-linked list backward. OFFSET cannot skip because B-trees do not maintain row counters. Cursor is fast because WHERE triggers the binary-search primitive of the B-tree. Multi-index means N B-trees on the same table. With a 10M-row environment, [measured] Q3 covering 2,476x / Q5 composite 577x / OFFSET 1M = 171ms / cursor = 0.30ms — unwound to the end with 10 diagrams.