如何优化SQL查询：将3列更新改为仅更新单列以提升性能

Got it, let's tackle this performance optimization by merging your percentile_pages and percentile_visits columns into a single composite column. This cuts down on write overhead (since we're updating one column instead of multiple), which is a huge win for performance—especially when dealing with large datasets.

Step 1: Pick a Composite Column Type #

Most modern databases support flexible composite storage; JSON is the most universal option (works in PostgreSQL, MySQL 5.7+, SQL Server 2016+, etc.). If you're using PostgreSQL, you can also use a custom composite type for stricter type checking.

Option 1: JSON (Universal Approach)

First, add the new composite column if it doesn't exist:

ALTER TABLE mytable ADD COLUMN percentiles JSON;

Then update this single column with both percentile calculations:

UPDATE mytable 
SET percentiles = JSON_OBJECT(
    'pages', CASE 
        WHEN pages >= 1 AND pages < 2 THEN 25 
        WHEN pages >= 2 AND pages < 3 THEN 35 
        WHEN pages >= 3 AND pages < 5 THEN 40 
        WHEN pages >= 5 THEN 45 
        ELSE percentile_pages  -- Keep existing value if no conditions match
    END,
    'visits', CASE 
        WHEN visits >= 2 AND visits < 3 THEN 25 
        WHEN visits >= 3 AND visits < 4 THEN 35 
        WHEN visits >= 4 AND visits < 6 THEN 40  -- Fill in your remaining threshold logic here
        WHEN visits >= 6 THEN 45 
        ELSE percentile_visits  -- Keep existing value if no conditions match
    END
);

Option 2: Custom Composite Type (PostgreSQL Example)

For stricter type safety, define a custom type first:

CREATE TYPE percentile_stats AS (pages INT, visits INT);

Add the new column:

ALTER TABLE mytable ADD COLUMN percentiles percentile_stats;

Update the column with your calculations:

UPDATE mytable 
SET percentiles = (
    CASE 
        WHEN pages >= 1 AND pages < 2 THEN 25 
        WHEN pages >= 2 AND pages < 3 THEN 35 
        WHEN pages >= 3 AND pages < 5 THEN 40 
        WHEN pages >= 5 THEN 45 
        ELSE percentile_pages 
    END,
    CASE 
        WHEN visits >= 2 AND visits < 3 THEN 25 
        WHEN visits >= 3 AND visits < 4 THEN 35 
        WHEN visits >= 4 AND visits < 6 THEN 40 
        WHEN visits >= 6 THEN 45 
        ELSE percentile_visits 
    END
)::percentile_stats;

Step 2: Clean Up (Optional) #

Once you've verified the new column has all correct data, you can drop the old columns to save space and simplify your table:

ALTER TABLE mytable DROP COLUMN percentile_pages, DROP COLUMN percentile_visits;

Why This Boosts Performance #

• Reduced Write IO: Updating one column means fewer disk blocks need modification. For large tables, this cuts down on disk contention and speeds up the entire UPDATE operation significantly.
• Simpler Maintenance: All percentile logic lives in one place, making it easier to adjust thresholds or add new metrics later.
• Scalability: If you need to add more percentile metrics (like percentile_time_on_site), you can extend the JSON object or composite type without adding new columns.

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