Redshift与MySQL中SELECT *和指定列查询的性能对比及原因咨询

Awesome question—this is a super common pitfall that trips up folks moving between row-store and column-store databases. Let’s break down exactly how these two query styles impact I/O, memory, and speed in each system, then compare the core differences.

MySQL (Row-Store Database) #

MySQL uses a row-based storage engine (InnoDB by default), where entire rows are stored together on disk pages. The performance gap between SELECT * and targeted columns depends heavily on whether you can leverage a covering index:

• When a covering index is available (all columns you need are contained in an index, no need to "go back" to the main table):
  • SELECT col1, col2 will pull data directly from the smaller index file, avoiding expensive random I/O to fetch full rows from the main table. This cuts down I/O drastically—sometimes by 50% or more on large tables.
  • SELECT * forces the database to first use the index to find row locations, then go back to the main table to fetch every column. This adds a ton of extra I/O, making the query noticeably slower.
• When no covering index is available (e.g., full table scan or primary key lookup):
  • Disk I/O differences are minimal, since InnoDB reads entire disk pages into memory regardless of how many columns you need.
  • But memory and CPU still take a hit: Targeted columns only load the data you need into memory buffers, reducing memory bloat and speeding up data processing/transfer. SELECT * loads every column—including large TEXT/BLOB fields—wasting memory bandwidth and forcing the CPU to process unnecessary data. This can add 10-30% latency, especially on tables with many columns.

Redshift (Column-Store Data Warehouse) #

Redshift is built for OLAP workloads with column-based storage—each column is stored separately on disk, with its own compression. Here, SELECT * is way more costly than targeted columns:

• I/O is the biggest offender: SELECT col1, col2 only reads the storage blocks for those two columns. SELECT * has to read blocks for every single column in the table. If your table has 10+ columns, this can multiply I/O by 5x or more—column storage is designed to avoid exactly this kind of waste.
• Memory overhead is massive: Loading all columns into memory uses far more buffer space, which can lead to disk swapping (when memory runs out) and slower query execution. Targeted columns keep memory usage lean, letting Redshift process data faster.
• Compression amplifies the gap: Redshift compresses each column with algorithms optimized for its data type. Reading fewer columns means less data to decompress, cutting down CPU usage significantly.

Core Comparison #

Final Takeaway #

Both databases suffer performance hits from SELECT *, but Redshift’s penalty is far more extreme because it directly contradicts column storage’s core design. In MySQL, the gap depends on indexing, but targeted columns are always a safer practice—they also avoid unexpected issues if the table schema changes (e.g., new columns adding unwanted data to your results).

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