Shopee Backend 2023 Salary Offers and In-Depth Interview Guide

Network Layer Models

OSI Seven‑Layer Model defines the functions of Physical, Data Link, Network, Transport, Session, Presentation, and Application layers.

TCP/IP Four‑Layer Model simplifies the OSI model into Application, Transport, Network, and Network Interface layers.

Application Layer

Transport Layer

Network Layer

Network Interface Layer

TCP Three‑Way Handshake

Establishing a TCP connection requires three steps:

SYN : Client sends a SYN with an initial sequence number (ISN) and enters SYN_SEND state.

SYN+ACK : Server replies with its own ISN (SYN) and acknowledges the client’s ISN (ACK), then enters SYN_RECV state.

ACK : Client acknowledges the server’s ISN; both sides move to ESTABLISHED state.

HTTP vs HTTPS

Both operate at the Application layer. HTTP uses port 80 and transmits plain text. HTTPS runs over TLS/SSL on port 443, providing confidentiality, integrity, and authentication. Search engines favor HTTPS for SEO.

Browser URL Access Process

User enters a URL in the browser.

Browser resolves the domain via DNS.

Browser initiates a TCP connection to the resolved IP and port.

Browser sends an HTTP request.

Server processes the request and returns an HTTP response.

Browser renders HTML, CSS, JS and fetches additional resources.

When communication ends, the TCP connection is closed.

MySQL Transaction Isolation Levels

The SQL standard defines four isolation levels, each balancing consistency and concurrency:

READ‑UNCOMMITTED : Allows dirty reads, non‑repeatable reads, and phantom reads.

READ‑COMMITTED : Prevents dirty reads; non‑repeatable reads and phantoms may still occur. Default for Oracle and SQL Server.

REPEATABLE‑READ : Prevents dirty and non‑repeatable reads; phantoms are possible in the standard. MySQL InnoDB’s default; mitigated by MVCC (snapshot reads) and Next‑Key Locks, which block inserts into the scanned range.

SERIALIZABLE : Full ACID isolation; transactions execute sequentially, preventing all three anomalies.

InnoDB implements REPEATABLE‑READ using Snapshot Read (MVCC) and Next‑Key Lock to avoid phantom rows in most workloads.

Primary Key vs Foreign Key

Primary Key : Uniquely identifies each row; cannot be NULL; one per table.

Foreign Key : References a primary key in another table; can be NULL or duplicate; enforces referential integrity.

Example with users (user_id as primary key) and orders (order_id primary key, user_id as foreign key referencing users.user_id) guarantees that orders belong to existing users and prevents orphan deletions.

Why Some Teams Avoid Foreign Keys

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。 Explanation: In high‑concurrency, distributed systems, foreign‑key constraints cause blocking and performance issues; they also hinder sharding.

Additional concerns from the Alibaba development manual include:

Increased complexity for INSERT/UPDATE/DELETE operations.

Extra database work to maintain referential checks, consuming resources.

Incompatibility with sharding, as foreign keys cannot span shards.

When the system is not heavily sharded and concurrency is moderate, foreign keys still provide data integrity and convenient cascade operations.

Slow Query Optimization in MySQL

Enable the slow‑query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/lib/mysql/ranking-list-slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET SESSION long_query_time = 1;
SET SESSION min_examined_row_limit = 100;

Example log entry:

# Time: 2022-10-09T08:55:37.486797Z
# User@Host: root[root] @ [172.17.0.1] Id: 14
# Query_time: 0.978054  Lock_time: 0.000164 Rows_sent: 999999  Rows_examined: 1999998
SET timestamp=1665305736;
SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;

Analyze with EXPLAIN to identify full table scans, missing indexes, and high rows estimates. Tools such as mysqldumpslow aggregate similar queries for further analysis.

JWT‑Based Authentication

Typical flow:

User submits username, password, and captcha.

Server validates credentials and returns a signed JWT.

Client stores the token (recommended in localStorage).

Subsequent requests include the token in the Authorization: Bearer <token> header.

Server verifies the token and extracts user information.

Advantages: stateless, reduces server load, mitigates CSRF when stored in localStorage, suitable for mobile and SSO. Risks include XSS exposure and difficulty revoking tokens.

Role‑Based Access Control (RBAC)

RBAC links users → roles → permissions, simplifying permission management. Typical schema includes tables for users, roles, permissions, and mapping tables ( user_role, role_permission).

Redis Sorted Set (ZSET) for Leaderboards

Sorted Set stores members with a score and maintains them in order. It supports O(log N) insertion, range queries ( ZRANGE, ZREVRANGE), rank lookup ( ZREVRANK), and score updates ( ZINCRBY).

Common use cases: game leaderboards, hot‑topic rankings, priority queues. Sorted Set enables fast ranking ( ZREVRANGE for top N), quick rank lookup ( ZREVRANK), and atomic score updates ( ZINCRBY), making it ideal for real‑time leaderboards.