Hey there! Let's tackle this problem of sorting columns A, B, C per row in your massive InnoDB table—efficiency is non-negotiable here, so we’ll avoid naive approaches that lock your table for hours or waste network bandwidth.

1. Core SQL Logic (The Fastest Way) #

Handling sorting directly in MySQL is way better than pulling all rows into Python first—network overhead and memory usage would cripple performance for large tables. Here’s how to get sorted values for each row:

Ascending Order (A ≤ B ≤ C) #

We’ll unpivot the three columns into rows, sort them per row ID, then map them back to sorted A/B/C:

WITH sorted_vals AS (
  SELECT
    id,
    val,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY val) AS rn
  FROM (
    SELECT id, A AS val FROM your_table
    UNION ALL
    SELECT id, B AS val FROM your_table
    UNION ALL
    SELECT id, C AS val FROM your_table
  ) AS unpivoted
)
SELECT
  id,
  MAX(CASE WHEN rn = 1 THEN val END) AS sorted_A,
  MAX(CASE WHEN rn = 2 THEN val END) AS sorted_B,
  MAX(CASE WHEN rn = 3 THEN val END) AS sorted_C
FROM sorted_vals
GROUP BY id;

Descending Order (A ≥ B ≥ C) #

Just flip the sort direction in the window function:

WITH sorted_vals AS (
  SELECT
    id,
    val,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY val DESC) AS rn
  FROM (
    SELECT id, A AS val FROM your_table
    UNION ALL
    SELECT id, B AS val FROM your_table
    UNION ALL
    SELECT id, C AS val FROM your_table
  ) AS unpivoted
)
SELECT
  id,
  MAX(CASE WHEN rn = 1 THEN val END) AS sorted_A,
  MAX(CASE WHEN rn = 2 THEN val END) AS sorted_B,
  MAX(CASE WHEN rn = 3 THEN val END) AS sorted_C
FROM sorted_vals
GROUP BY id;

2. Batch Updates for Large Tables #

A single UPDATE on a huge table will lock everything and grind your database to a halt. Instead, we’ll split the work into small batches using your table’s primary key (assuming you have an indexed, unique PK like id).

Step 1: Python Setup #

Use pymysql (a lightweight, reliable connector) — install it if you haven’t:

pip install pymysql

Step 2: Batch Update Script #

Adjust BATCH_SIZE based on your server’s memory (1000-5000 is a safe starting point):

import pymysql

# Configure your database connection
DB_CONFIG = {
    'host': 'your_host',
    'user': 'your_user',
    'password': 'your_password',
    'database': 'your_db',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}
TABLE_NAME = 'your_table'
BATCH_SIZE = 1000  # Tune this based on your server's capacity
SORT_ORDER = 'ASC'  # Change to 'DESC' for descending sort

def get_id_range():
    """Grab the min/max primary key to split batches"""
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            cursor.execute(f"SELECT MIN(id) AS min_id, MAX(id) AS max_id FROM {TABLE_NAME}")
            return cursor.fetchone()

def update_batch(start_id, end_id):
    """Update a single batch of rows with sorted values"""
    sort_dir = 'ASC' if SORT_ORDER == 'ASC' else 'DESC'
    update_query = f"""
    UPDATE {TABLE_NAME} t
    JOIN (
        WITH sorted_vals AS (
            SELECT
                id,
                val,
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY val {sort_dir}) AS rn
            FROM (
                SELECT id, A AS val FROM {TABLE_NAME} WHERE id BETWEEN %s AND %s
                UNION ALL
                SELECT id, B AS val FROM {TABLE_NAME} WHERE id BETWEEN %s AND %s
                UNION ALL
                SELECT id, C AS val FROM {TABLE_NAME} WHERE id BETWEEN %s AND %s
            ) AS unpivoted
        )
        SELECT
            id,
            MAX(CASE WHEN rn = 1 THEN val END) AS sorted_A,
            MAX(CASE WHEN rn = 2 THEN val END) AS sorted_B,
            MAX(CASE WHEN rn = 3 THEN val END) AS sorted_C
        FROM sorted_vals
        GROUP BY id
    ) AS sorted_data ON t.id = sorted_data.id
    SET t.A = sorted_data.sorted_A, t.B = sorted_data.sorted_B, t.C = sorted_data.sorted_C;
    """
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            cursor.execute(update_query, (start_id, end_id, start_id, end_id, start_id, end_id))
        conn.commit()
        print(f"Updated rows with ID range: {start_id} → {end_id}")

def main():
    id_range = get_id_range()
    current_start = id_range['min_id']
    max_id = id_range['max_id']
    
    while current_start <= max_id:
        current_end = min(current_start + BATCH_SIZE - 1, max_id)
        update_batch(current_start, current_end)
        current_start = current_end + 1

if __name__ == "__main__":
    main()

3. Critical Optimizations for InnoDB #

• Batch by Primary Key: The indexed PK lets MySQL quickly locate each batch without full table scans.
• Per-Batch Transactions: Each batch runs in its own transaction, reducing lock hold time and avoiding long-running transactions that block other operations.
• Row-Level Locking: InnoDB only locks the rows in the current batch, not the entire table.
• Explicit Commits: Disabling autocommit (we use commit() per batch) cuts down on unnecessary disk I/O.

4. Alternative: Python-Side Sorting (Not Recommended for Large Tables) #

If you need custom sorting logic that SQL can’t handle, you can pull batches into Python, sort, then bulk update. But this is slower due to network overhead:

def batch_python_sort(start_id, end_id):
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            # Fetch batch of rows
            cursor.execute(f"SELECT id, A, B, C FROM {TABLE_NAME} WHERE id BETWEEN %s AND %s", (start_id, end_id))
            rows = cursor.fetchall()
            
            # Prepare sorted values
            update_rows = []
            for row in rows:
                sorted_vals = sorted([row['A'], row['B'], row['C']], reverse=(SORT_ORDER == 'DESC'))
                update_rows.append((sorted_vals[0], sorted_vals[1], sorted_vals[2], row['id']))
            
            # Bulk update
            cursor.executemany(f"UPDATE {TABLE_NAME} SET A=%s, B=%s, C=%s WHERE id=%s", update_rows)
        conn.commit()

内容的提问来源于stack exchange，提问作者user982835