咨询：用自增ID替换姓名列优化时序位置表的测试方法

1. 创建people表并导入唯一姓名组合 #

首先创建存储唯一姓名组合的表，添加唯一约束避免重复，同时用自增ID作为主键：

CREATE TABLE people (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    UNIQUE KEY unique_full_name (first_name, last_name)
);

从原表中提取所有不重复的姓名组合插入到people表：

INSERT IGNORE INTO people (first_name, last_name)
SELECT DISTINCT first_name, last_name
FROM original_location_table;

INSERT IGNORE会自动跳过因唯一约束产生的重复数据，确保数据唯一性。

2. 创建新的时序位置表并导入数据 #

创建简化后的时序位置表，用person_id关联people表，同时设置新的主键（原主键的姓名部分替换为person_id）：

CREATE TABLE location_timeline (
    time DATETIME NOT NULL,
    person_id INT NOT NULL,
    loc_lat DECIMAL(10,8) NOT NULL,
    loc_long DECIMAL(11,8) NOT NULL,
    PRIMARY KEY (time, person_id),
    FOREIGN KEY (person_id) REFERENCES people(id)
);

通过关联people表，将原表数据映射到新表中：

INSERT INTO location_timeline (time, person_id, loc_lat, loc_long)
SELECT 
    ot.time,
    p.id AS person_id,
    ot.loc_lat,
    ot.loc_long
FROM original_location_table ot
INNER JOIN people p 
    ON ot.first_name = p.first_name 
    AND ot.last_name = p.last_name;

3. 验证磁盘空间节省效果 #

通过查询数据库元数据对比原表与新表（people + location_timeline）的总占用空间，以MySQL为例：

-- 查看原表总占用空间（单位：MB）
SELECT 
    table_name,
    ROUND(data_length/1024/1024, 2) AS data_size_mb,
    ROUND(index_length/1024/1024, 2) AS index_size_mb,
    ROUND((data_length + index_length)/1024/1024, 2) AS total_size_mb
FROM information_schema.tables
WHERE table_schema = '你的数据库名' 
  AND table_name = 'original_location_table';

-- 查看新表组合的总占用空间
SELECT 
    ROUND(SUM(data_length)/1024/1024, 2) AS total_data_mb,
    ROUND(SUM(index_length)/1024/1024, 2) AS total_index_mb,
    ROUND(SUM(data_length + index_length)/1024/1024, 2) AS total_size_mb
FROM information_schema.tables
WHERE table_schema = '你的数据库名' 
  AND table_name IN ('people', 'location_timeline');

注意事项 #

• 测试时请在测试数据库操作，避免影响生产数据
• 若原表数据量极大，可分批执行INSERT操作，避免长时间锁表
• 导入数据前可临时关闭外键约束（SET FOREIGN_KEY_CHECKS=0;），导入完成后再开启（SET FOREIGN_KEY_CHECKS=1;）提升导入效率
• 根据实际数据库类型（如PostgreSQL、SQL Server）调整SQL语法，比如PostgreSQL用SERIAL代替AUTO_INCREMENT，SQL Server用IDENTITY(1,1)

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