Backend Anti‑Patterns and Optimization Strategies for MySQL, Redis, and Thread Pools

Introduction: The author describes recent project performance problems caused by poor code practices and aims to share anti‑patterns as teaching material to help developers recognize and avoid bad code in daily development.

Inefficient For‑Loop Queries in MySQL

The following code retrieves user information by first fetching role IDs for a user, then iterating over each role ID to query the corresponding menu permissions and merging them into a UserInfo object.

public UserInfo findUserInfo(SysUser sysUser) {
       UserInfo userInfo = new UserInfo();
       userInfo.setSysUser(sysUser);
       // Set role list (ID)
       List
roleIds = sysRoleService.findRolesByUserId(sysUser.getUserId()).stream().map(SysRole::getRoleId)
             .collect(Collectors.toList());
       userInfo.setRoles(ArrayUtil.toArray(roleIds, Long.class));

       // Set permission list (menu.permission)
       Set
permissions = new HashSet<>();
       roleIds.forEach(roleId -> {
          List
permissionList = sysMenuService.findMenuByRoleId(roleId).stream()
                .collect(Collectors.toList());
          permissions.addAll(permissionList);
       });
       userInfo.setPermissions(ArrayUtil.toArray(permissions, String.class));
       return userInfo;
    }

Instead of executing a separate query inside a for loop, a better approach is to use an IN clause to retrieve all permissions in a single MySQL query, and optionally cache the role‑permission mapping in Redis to avoid database hits altogether.

Heavy Interface Implementation

The next example shows a method that performs many MySQL queries, multiple Redis lookups, and remote service calls, resulting in a very costly interface when data volume grows.

public void complexQueryMethod(List
idList) {
        // 1. Execute one MySQL query
        ResultSet result1 = executeMySQLQuery("SELECT * FROM table1 WHERE condition1");
        // 2. Execute another MySQL query
        ResultSet result2 = executeMySQLQuery("SELECT * FROM table2 WHERE condition2");
        // 3. Execute 3‑4 join queries across multiple tables
        ResultSet result3 = executeMySQLQuery("SELECT * FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id");
        ResultSet result4 = executeMySQLQuery("SELECT * FROM table3 t3 JOIN table4 t4 ON t3.id = t4.id");
        // 4. Query MySQL once then loop over Redis
        ResultSet result5 = executeMySQLQuery("SELECT id FROM table5 WHERE condition3");
        List
redisKeys = new ArrayList<>();
        while (result5.next()) {
            String id = result5.getString("id");
            redisKeys.add(id);
        }
        for (String key : redisKeys) {
            String redisValue = queryRedis(key);
            // process Redis data
        }
        // 5. Execute a 5‑table join query
        ResultSet result6 = executeMySQLQuery("SELECT * FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id JOIN table3 t3 ON t2.id = t3.id JOIN table4 t4 ON t3.id = t4.id JOIN table5 t5 ON t4.id = t5.id");
        // 6. Remote micro‑service call (which may itself call another service)
        ResultSet result7 = this.remoteService.queryInfo(idList);
        // Process all results
        processResults(result1, result2, result3, result4, result5, result6, result7);
    }
    // Pseudo‑methods for demonstration
    private ResultSet executeMySQLQuery(String query) { return null; }
    private String queryRedis(String key) { return null; }
    private void processResults(ResultSet... results) { }

Optimization ideas include:

Divide the large method into smaller, focused methods (divide‑and‑conquer).

Reduce I/O by consolidating requests that can be satisfied with a single query.

Leverage caching (e.g., Redis + JVM) for read‑heavy scenarios.

Pre‑compute results when possible so that runtime calls only read prepared data.

Introduce specialized middleware such as Elasticsearch, Flink, or Spark to build an ETL pipeline.

MySQL Full‑Table Scan

Example of a query that triggers a full table scan because the WHERE columns lack indexes:

SELECT id, alert_message, alert_time
FROM alert_records
WHERE alert_time >= '2024-01-01' AND alert_status = 'ACTIVE';

Running EXPLAIN yields:

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

alert_records

ALL

NULL

NULL

NULL

NULL

19889000

Using where

The type: ALL indicates a full‑table scan. To optimize, create indexes on alert_time and alert_status , and understand MySQL concepts such as left‑most matching, covering indexes, composite indexes, clustered and non‑clustered indexes.

Incorrect Thread‑Pool Usage

Creating a thread pool inside a method causes a new pool on every call, leading to excessive thread creation and CPU spikes under high load.

public List
queryRanking(RankingReq req) {
    // Create a thread pool
    ExecutorService executorService = ThreadUtil.newExecutor(3);
    // Business logic
    ........
}

The fix is to declare the ExecutorService as a singleton or a static class member so that only one pool exists.

Long‑Running MySQL Transactions

A scheduled task annotated with @Transactional opens a transaction at the start, then performs many loops, MySQL queries, and HTTP calls before committing, resulting in a long‑lived transaction that blocks other updates and deletes.

In complex scheduled jobs, beginning a transaction early and holding it until the very end can cause lock wait timeouts and read‑write contention.

To inspect current transactions:

select * from information_schema.innodb_trx;

If a transaction's trx_started timestamp is far in the past, it likely indicates a long‑running transaction.

@Transactional
@XxlJob("RecordsJob.recordsJob")
public void complexProcessingMethod() {
    // Execute MySQL query
    ResultSet resultSet1 = executeMySQLQuery("SELECT id, name FROM table1 WHERE condition = " + i);
    for(int i = 0; i < resultSet1.size(); i++) {
        ResultSet resultSet2 = executeMySQLQuery("SELECT id, key FROM table0 WHERE condition = " + i);
        while (resultSet2.next()) {
            String id = resultSet2.getString("id");
            String key = resultSet2.getString("key");
            String httpResponse1 = performHttpRequest("https://api.example.com/resource?id=" + id + "&key=" + key );
            ResultSet resultSet3 = executeMySQLQuery("SELECT additional_data FROM table2 WHERE id = '" + id );
            while (resultSet3.next()) {
                String additionalData = resultSet3.getString("additional_data");
                if (StringUtils.isNotBlank(additionalData)) {
                    String httpResponse2 = performHttpRequest("https://api.example.com/another-resource?data=" + additionalData + "&extra=" + id);
                    processHttpResponse(httpResponse2);
                }
            }
        }
    }
    // Additional independent query
    ResultSet resultSet3 = executeMySQLQuery("SELECT * FROM table3 WHERE some_condition");
    while (resultSet3.next()) {
        String info = resultSet3.getString("info");
        String httpResponse3 = performHttpRequest("https://api.example.com/final-resource?info=" + info);
        processHttpResponse(httpResponse3);
    }
}
// Pseudo‑methods
private ResultSet executeMySQLQuery(String query) { return null; }
private String performHttpRequest(String url) { return null; }
private void processHttpResponse(String response) { }

Optimization: only start a transaction when an update is truly needed, keep its duration short, and commit as early as possible.