如何在MySQL中高效存储大量小型数据条目？——全年城市日均温场景

Hey there! Let's break down some straightforward, time-saving ways to store your daily average temperature data for multiple cities in MySQL—no advanced expertise needed, I promise.

If you're currently inserting one row at a time (like running INSERT INTO temps (...) VALUES (...); hundreds/thousands of times), that's probably why it's slow. Bulk inserts cut down on round-trips between your data source and MySQL, making the process way faster.

Option A: Multi-value INSERT #

Pack multiple rows into a single INSERT statement—super easy to implement:

INSERT INTO city_temperatures (city_name, record_date, avg_temp)
VALUES
('Beijing', '2024-01-01', -2.5),
('Beijing', '2024-01-02', -1.8),
('Shanghai', '2024-01-01', 5.2),
('Shanghai', '2024-01-02', 6.1);

If you hit errors, check MySQL's max_allowed_packet limit with SHOW VARIABLES LIKE 'max_allowed_packet';—you can increase it temporarily if needed.

Option B: LOAD DATA INFILE (fastest for large datasets) #

If your data is in a CSV/TSV file, use MySQL's built-in bulk import tool—it's optimized for speed. For example, if your CSV looks like:

Beijing,2024-01-01,-2.5
Beijing,2024-01-02,-1.8
Shanghai,2024-01-01,5.2

Run this SQL:

LOAD DATA INFILE '/path/to/your/temps.csv'
INTO TABLE city_temperatures
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(city_name, record_date, avg_temp);

Note: You might need to adjust file permissions or MySQL's secure_file_priv setting to let it access the file—this is a quick fix if you run into issues.

A lean, well-designed table will speed up inserts and save space:

• Use compact data types:
  • For dates: Stick with DATE (3 bytes) instead of DATETIME (8 bytes)—you only need the day, not the time.
  • For temperatures: DECIMAL(5,2) is perfect (covers -999.99 to 999.99, way more than real-world temps) and takes less space than FLOAT/DOUBLE.
  • Avoid repeating city names: Create a separate cities table with an INT primary key, then store the city ID in your temperature table. This cuts down on duplicate data and speeds up inserts. Example:

    CREATE TABLE cities (
        city_id INT AUTO_INCREMENT PRIMARY KEY,
        city_name VARCHAR(100) UNIQUE NOT NULL
    );
    
    CREATE TABLE city_temperatures (
        city_id INT,
        record_date DATE,
        avg_temp DECIMAL(5,2),
        PRIMARY KEY (city_id, record_date) -- Prevents duplicate entries for same city+date
    );
    

• Add indexes AFTER inserting data: Indexes slow down inserts because MySQL has to update them for every row. Wait until all your data is loaded before adding any non-primary-key indexes.

If you want to avoid index overhead during inserts or keep your main table clean, use a temporary table first:

-- Create a temp table without indexes (super fast to insert into)
CREATE TEMPORARY TABLE temp_temps (
    city_name VARCHAR(100),
    record_date DATE,
    avg_temp DECIMAL(5,2)
);

-- Bulk insert all your data into the temp table
LOAD DATA INFILE '/path/to/your/temps.csv'
INTO TABLE temp_temps
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

-- Copy data to your main table (faster than inserting directly)
INSERT INTO city_temperatures (city_name, record_date, avg_temp)
SELECT city_name, record_date, avg_temp FROM temp_temps;

-- Temp tables auto-delete when your MySQL session ends, no cleanup needed

These are temporary changes you can make during import, then revert afterward:

• Set innodb_flush_log_at_trx_commit = 2: This makes InnoDB write logs less frequently, speeding up inserts. Run SET GLOBAL innodb_flush_log_at_trx_commit = 2; before importing, then set it back to 1 (default) if you need full transaction safety.
• Increase max_allowed_packet for large multi-value inserts: SET GLOBAL max_allowed_packet = 64M; (adjust the size as needed).

Start with bulk inserts first—it's the easiest win. If you have a huge dataset, LOAD DATA INFILE combined with a temp table will be your best bet. And don't forget to keep your table structure lean!

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