Backend Fundamentals Deep Dive: MySQL Internals, Redis, and the HTTP/HTTPS Request Lifecycle

This is a backend-fundamentals rapid-fire round covering MySQL internals, Redis internals, and the HTTP/HTTPS request lifecycle. Work through each part, explaining the *why* behind the mechanism, not just the name. ### Constraints & Assumptions - Assume MySQL with the **InnoDB** storage engine unless stated otherwise. - Assume a standard Redis single-instance deployment. - "Index" questions assume a B+Tree index (InnoDB's default), not hash or full-text indexes. - Answers should reflect default configurations (e.g. default isolation level, default `appendfsync` policy) and then note where the defaults can be changed. ### Clarifying Questions to Ask - Which storage engine should I assume for the MySQL questions — InnoDB, or do you want me to contrast with MyISAM? - For replication, are we talking about classic asynchronous replication, semi-synchronous, or group replication? - For the URL-resolution question, how deep do you want me to go — should I cover DNS, TCP, TLS handshake, and the HTTP exchange end to end, or focus on one layer? - For the index-hit part, is the composite index `(name, score, subject)` the only index on the table, and are these the exact column orders? ### Part 1 — MySQL logs What logs does MySQL (InnoDB) maintain, and what is each one's purpose? Be specific about the difference between the server-layer log and the engine-layer logs, and explain how they cooperate to guarantee durability and crash recovery. ```hint Group them by layer Some logs live in the MySQL **server layer** (above the storage engine) and some live **inside InnoDB**. Sorting them that way makes the list and their purposes fall out naturally. ``` ```hint Durability mechanism One specific pair of logs is the heart of crash-safe commits. Think about *write-ahead logging* and the protocol that keeps the server-layer log and the engine redo log consistent across a crash. ``` #### What This Part Should Cover ```premium-lock What This Part Should Cover ``` ### Part 2 — Binlog-based master/slave replication Explain how MySQL primary/replica (master/slave) replication works using the binlog. In particular: does the replica **pull** the binlog from the primary, or does the primary **push** it? Walk through the threads and files involved. ```hint Follow the binlog across the wire Trace the binlog event from where it's written on the primary, across the network, to where it's applied on the replica. There's an intermediate on-disk file on the replica side, and more than one thread is involved. ``` #### What This Part Should Cover ```premium-lock What This Part Should Cover ``` ### Part 3 — Index structure: clustered vs non-clustered What data structure backs MySQL/InnoDB indexes, and what is the difference between a **clustered** index and a **non-clustered (secondary)** index? Specifically, what does the leaf node of each store? ```hint Leaf-node payload is the key distinction Both are B+Trees. The real exam is: what sits in the **leaf nodes** of each kind of index, and what that implies about how many lookups a secondary-index query needs. ``` #### What This Part Should Cover ```premium-lock What This Part Should Cover ``` ### Part 4 — Transaction isolation levels and phantom reads List MySQL's transaction isolation levels, the read anomalies each one prevents, and explain specifically how InnoDB prevents **phantom reads**. ```hint Map anomalies to levels Build the standard 4-level × 3-anomaly matrix (dirty read, non-repeatable read, phantom read). Then for phantom reads, recall InnoDB's two distinct mechanisms — one for consistent snapshot reads and one for locking reads. ``` #### What This Part Should Cover ```premium-lock What This Part Should Cover ``` ### Part 5 — Redis persistence: RDB vs AOF What persistence options does Redis offer, and what is the difference between **RDB** and **AOF**? Discuss the trade-offs in durability, recovery speed, and file size. ```hint Snapshot vs log One mechanism periodically snapshots the whole dataset; the other appends every write command to a log. That framing drives every trade-off — durability window, file size, and restart speed. ``` #### What This Part Should Cover ```premium-lock What This Part Should Cover ``` ### Part 6 — Why Redis is fast, and I/O multiplexing Why is Redis so fast despite being (largely) single-threaded for command execution? You mentioned I/O multiplexing — what models of I/O multiplexing exist and how do they differ? ```hint Separate "single-threaded" from "I/O model" Two independent reasons make Redis fast: where the data lives + the simplicity of single-threaded command execution, and the **event loop** built on an I/O-multiplexing syscall. For the syscalls, contrast the three classic ones by how they detect ready file descriptors. ``` #### What This Part Should Cover ```premium-lock What This Part Should Cover ``` ### Part 7 — What happens when you type a URL (HTTP vs HTTPS) Walk through everything that happens from typing a URL in the browser and pressing Enter, until the page renders. Then explain what additionally happens when the URL is **HTTPS**. ```hint Go layer by layer Move down then back up the stack: URL parse → cache/DNS resolution → connection setup → request/response → render. The HTTPS delta is a single extra phase inserted at one specific point in that sequence. ``` #### What This Part Should Cover ```premium-lock What This Part Should Cover ``` ### Part 8 — Composite index hit analysis A table has the composite index `(name, score, subject)`. For each of the following queries, state whether it can use the index (and how much of it), and justify with the **leftmost-prefix** rule: 1. `WHERE name = 'xxx' AND score > 80` 2. `WHERE score > 80` 3. `WHERE subject = 'math'` ```hint One rule decides all three The leftmost-prefix rule governs every case: a composite index can be used only starting from its leftmost column, and a range condition stops further columns from being used for seeking. Apply it query by query. ``` #### What This Part Should Cover ```premium-lock What This Part Should Cover ``` ### What a Strong Answer Covers ```premium-lock What a Strong Answer Covers ``` ### Follow-up Questions - In Part 1, explain the **two-phase commit between redo log and binlog**: what exact failure scenarios would cause data inconsistency between the engine and replicas if this protocol did not exist? - In Part 3, given the secondary-index "back-to-table" cost, when would you deliberately add a **covering index**, and what is the downside of widening indexes? - In Part 4, give a concrete interleaving of two transactions that would exhibit a phantom read under Read Committed but not under Repeatable Read in InnoDB. - In Part 8, if you reordered the index to `(score, name, subject)`, which of the three queries' answers would change, and why?