Mastering Backend Essentials: Redis Types, MySQL Indexes, Thread Safety, and SQL Injection Defense

Redis Data Types and Internal Implementations

Redis provides five basic data types—String, List, Set, Hash, and Zset—and three special types—HyperLogLog, Bitmap, and Geospatial. Their underlying structures include Simple Dynamic String (SDS), LinkedList, ZipList, QuickList, Dict (hash table), SkipList, Intset, and ListPack (which replaced ZipList in Redis 7.0). A mapping table shows which structures back each type. Additional structures such as Bloom filter and Bitfield are also available.

Handling Massive Key Expiration in Redis

Problem: Simultaneous expiration of many keys can increase request latency and memory usage because Redis must delete expired keys synchronously.

Solutions:

Randomize TTL values when setting keys to avoid clustering expirations.

Enable lazy free for expiration by setting lazyfree-lazy-expire yes in redis.conf, which makes deletion asynchronous.

Thread Safety in Java

Thread safety ensures data correctness when accessed concurrently. Three main strategies are:

Prefer non‑shared data (local variables or ThreadLocal).

Share immutable data (e.g., String, final fields).

If mutable sharing is required, use concurrent collections ( ConcurrentHashMap, CopyOnWriteArrayList), atomic classes ( AtomicInteger, AtomicLong), or explicit locks ( synchronized, ReentrantLock) together with coordination utilities ( CountDownLatch, Semaphore, CyclicBarrier).

ThreadLocal Mechanics

Each thread holds a ThreadLocalMap. The ThreadLocal instance is the key and the stored value is the map value. Multiple ThreadLocal variables in the same thread share the same map. Simplified constructor snippet:

ThreadLocalMap(ThreadLocal<?> firstKey, Object firstValue) { /* ... */ }

Covering Indexes in MySQL

A covering index contains all columns required by a query, eliminating the need for a back‑table lookup. In InnoDB, non‑primary indexes store the primary key as a pointer; if the query can be satisfied solely from the index, the row lookup is avoided.

When selected columns match the index columns, the index alone returns the result set.

Composite (Joint) Indexes and the Left‑most Prefix Rule

Composite indexes are defined on multiple columns, e.g., (score, name). MySQL can use the index for queries that match the leftmost prefix of the indexed columns. The rule stops when a range condition is encountered. Example:

CREATE TABLE student (id INT NOT NULL, name VARCHAR(100), class VARCHAR(100), PRIMARY KEY(id), KEY name_class_idx(name, class)) ENGINE=InnoDB;

Queries using only name or name + class can use the index; a query using only class cannot.

Index Skip Scan (ISS) in MySQL 8.0+

MySQL 8.0.13 introduced Index Skip Scan, allowing certain queries that violate the leftmost prefix rule to still use the index. A known bug (#109145) was reported and later fixed.

Bug reference: https://bugs.mysql.com/bug.php?id=109145

Analyzing Slow SQL Queries

Use EXPLAIN to inspect execution plans. Key fields: select_type: query type (SIMPLE, PRIMARY, UNION, SUBQUERY, etc.). type: access method, ordered from worst to best (ALL, index, range, ref, eq_ref, const, system). rows: estimated rows examined; lower is better.

SQL Injection Overview and Prevention

SQL injection occurs when user input is concatenated directly into SQL statements, allowing attackers to alter query logic. Classic bypass example uses the comment syntax -- to ignore the password clause.

Effective mitigation:

Use prepared statements (e.g., PreparedStatement in Java or #{} placeholders in MyBatis) to separate code from data.

Validate input on both client and server sides.

Example of vulnerable string concatenation:

String sql = "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'";

Secure version using a prepared statement:

PreparedStatement ps = connection.prepareStatement("SELECT * FROM users WHERE username=? AND password=?");
ps.setString(1, username);
ps.setString(2, password);
ResultSet rs = ps.executeQuery();

Additional input validation guidelines can be found at: https://javaguide.cn/system-design/security/data-validation.html