Technical Interview Q&A: MySQL, Indexing, Design Patterns, Algorithms, Networking, and Concurrency

Interview notes begin with MySQL interview questions, including tips for writing efficient SELECT statements, avoiding functions on indexed columns, preventing implicit type conversion, minimizing NULL fields, and careful use of leading wildcards with LIKE.

Indexing techniques are discussed: create indexes on highly selective columns, consider index length for strings, follow the left‑most prefix rule for composite indexes, and understand that MySQL uses B+ trees for index storage.

Further questions explore why B+ trees are chosen over red‑black trees, the concept of index push‑down to reduce row lookups, and recovery strategies when accidental deletions occur, emphasizing the importance of enabling binlog with ROW or MIXED format.

Additional MySQL topics cover why statement‑based binlog is unsuitable for replication, the mechanics of master‑slave replication (IO thread and SQL thread), and common causes of replication lag such as hardware differences, heavy read load on slaves, and large transactions.

Design pattern discussion mentions common patterns used in Laravel (Facade, Composite, Decorator, Observer) and stresses applying patterns only when appropriate.

A coding problem asks for a function that returns the index of a target value in a sorted array or the insertion point if not found; the solution uses binary search with O(log n) time and O(1) space:

function searchInsert($nums, $target) {
    if (!count($nums)) return 0;
    $l = 0;
    $r = count($nums) - 1;
    while ($l <= $r) {
        $middle = $l + (($r - $l) >> 1);
        if ($nums[$middle] == $target) return $middle;
        if ($nums[$middle] < $target) {
            $l = $middle + 1;
        } else {
            $r = $middle - 1;
        }
    }
    return $l;
}

The answer notes that binary search achieves O(log n) time complexity.

Networking questions cover transport‑layer protocols (TCP and UDP) and explain the TCP three‑handshake process and its necessity for confirming bidirectional communication.

Concurrency discussion presents a scenario where multiple entry points share a limited‑balance account, suggesting solutions such as MySQL conditional updates, pessimistic locking (FOR UPDATE), Redis Lua scripts, file locks, or queue‑based single‑consumer designs.

System design advice for login functionality recommends extracting authentication into a centralized login service (single sign‑on) to be reused across projects.

Additional topics include a brief comparison of coroutine scheduling in Swoole (single‑threaded) versus Go (multi‑threaded) and an acknowledgement of limited knowledge about Go's GMP model.