自连接查询添加索引后变慢问题求助（附执行计划）

我为学习索引工作原理编写了一条自连接查询语句，针对包含24k条记录的personal_details表，无索引时查询耗时约76秒，但创建多个单字段索引及复合索引后，查询耗时反而增至约80秒。现寻求问题原因及优化建议。

表结构DDL #

CREATE TABLE personal_details (
    pno VARCHAR(20) PRIMARY KEY,   -- Personal number or unique ID
    first_name VARCHAR(20),        -- First name
    middle_name VARCHAR(20),       -- Middle name (nullable if not always used)
    last_name VARCHAR(20),         -- Last name
    dob DATE,                      -- Date of birth (YYYY-MM-DD format)
    gender CHAR(1),                -- Gender (M/F/O)
    email VARCHAR(50),             -- Email address
    phone_number VARCHAR(20),      -- Phone number (variable format, max 20 chars)
    address VARCHAR(255),          -- Address (full address string)
    city VARCHAR(50),              -- City
    state VARCHAR(50),             -- State
    zip_code VARCHAR(10),          -- Postal or zip code
    country VARCHAR(50),           -- Country
    marital_status VARCHAR(20),    -- Marital status (Single/Married/etc.)
    nationality VARCHAR(50),       -- Nationality
    occupation VARCHAR(50),        -- Job title or occupation
    salary DECIMAL(10, 2),         -- Salary (up to 10 digits, 2 decimal places)
    hire_date DATE,                -- Hire date (YYYY-MM-DD format)
    department VARCHAR(50),        -- Department name
    is_active BOOLEAN              -- Status flag for active/inactive (True/False)
);

慢查询语句 #

SELECT
    pd1.pno AS pno1,
    pd1.first_name AS first_name1,
    pd1.last_name AS last_name1,
    pd2.pno AS pno2,
    pd2.first_name AS first_name2,
    pd2.last_name AS last_name2,
    pd3.pno AS pno3,
    pd3.first_name AS first_name3,
    pd3.last_name AS last_name3
FROM
    personal_details pd1
JOIN
    personal_details pd2 ON pd1.city = pd2.city
JOIN
    personal_details pd3 ON pd2.state = pd3.state
WHERE
    pd1.dob BETWEEN '1980-01-01' AND '1990-12-31'
    AND pd2.gender = 'M'
    AND pd3.salary > 50000
ORDER BY
    pd1.pno, pd2.pno, pd3.pno;

已创建的索引 #

• CREATE INDEX idx_pno ON personal_details (pno);
• CREATE INDEX idx_composite_query ON personal_details (city, state, dob, gender, salary);
• CREATE INDEX idx_city ON personal_details (city);
• CREATE INDEX idx_state ON personal_details (state);
• CREATE INDEX idx_dob ON personal_details (dob);
• CREATE INDEX idx_gender ON personal_details (gender);
• CREATE INDEX idx_salary ON personal_details (salary);

EXPLAIN ANALYZE分析结果 #

-> Sort: pd1.pno, pd2.pno, pd3.pno  (actual time=19130..19461 rows=2.25e+6 loops=1)
    -> Stream results  (cost=346436 rows=105833) (actual time=129..1905 rows=2.25e+6 loops=1)
        -> Inner hash join (pd3.state = pd2.state)  (cost=346436 rows=105833) (actual time=129..634 rows=2.25e+6 loops=1)
            -> Filter: (pd3.salary > 50000)  (cost=50.4 rows=813) (actual time=0.0498..23.2 rows=22872 loops=1)
                -> Table scan on pd3  (cost=50.4 rows=24394) (actual time=0.0433..19.1 rows=24000 loops=1)
            -> Hash
                -> Nested loop inner join  (cost=7124 rows=3905) (actual time=0.105..62.5 rows=4916 loops=1)
                    -> Filter: ((pd2.gender = 'M') and (pd2.city is not null))  (cost=2633 rows=2439) (actual time=0.058..20.3 rows=7968 loops=1)
                        -> Table scan on pd2  (cost=2633 rows=24394) (actual time=0.0541..17.6 rows=24000 loops=1)
                    -> Index lookup on pd1 using idx_composite_query (city=pd2.city), with index condition: (pd1.dob between '1980-01-01' and '1990-12-31')  (cost=0.4 rows=1.6) (actual time=0.00432..0.00515 rows=0.617 loops=7968)

问题原因分析 #

1. 索引未被有效利用：从执行计划看，pd2、pd3均采用全表扫描。这是因为pd2.gender='M'过滤后返回7968行（约全表1/3），pd3.salary>50000返回22872行（几乎全表），此时索引扫描的IO成本反而高于全表扫描，优化器选择了更高效的全表扫描路径。
2. 复合索引设计不合理：idx_composite_query的字段顺序为(city, state, dob, gender, salary)，对于pd1的查询仅能用到city前缀，后续字段对当前查询无意义，且索引宽度过大，导致索引扫描的内存和IO开销增加。
3. 多索引冲突：过多的单字段索引和复合索引会让优化器在选择最优执行计划时增加判断成本，甚至可能误选低效索引；同时，索引维护本身也会在查询阶段带来额外开销。
4. 排序开销占比极高：执行计划中Sort阶段耗时约19秒，占总查询时间的大部分。返回的225万行数据需要在内存或磁盘中排序，现有索引未覆盖排序字段，无法通过索引消除排序操作。

优化建议 #

1. 索引精简与针对性设计 #

• 删除无用索引：删掉idx_pno（主键本身已是唯一索引）、idx_composite_query（设计冗余）以及所有单字段索引（idx_city、idx_state等），避免优化器决策混乱，减少索引维护成本。
• pd1专用覆盖索引：创建包含查询所需字段的索引，避免回表：

  CREATE INDEX idx_pd1_city_dob ON personal_details (city, dob) INCLUDE (pno, first_name, last_name);
  

• pd2专用覆盖索引：针对pd2的过滤和连接需求，创建覆盖索引：

  CREATE INDEX idx_pd2_gender_city_state ON personal_details (gender, city, state) INCLUDE (pno, first_name, last_name);
  

• pd3专用覆盖索引：针对pd3的连接和过滤需求，创建覆盖索引：

  CREATE INDEX idx_pd3_state_salary ON personal_details (state) INCLUDE (pno, first_name, last_name, salary);
  

2. 查询逻辑优化 #

• 减少排序开销：如果业务允许，可移除ORDER BY子句；若必须排序，可尝试调整连接顺序，先过滤出更小的结果集再排序。
• 缩小结果集：增加额外过滤条件（如限制特定城市、州），减少连接和排序的数据量，从根源降低查询负载。

3. 数据库配置优化 #

• 调整排序缓冲区：增大sort_buffer_size参数，让排序操作尽可能在内存中完成，避免磁盘临时表带来的IO开销。
• 更新统计信息：执行ANALYZE TABLE personal_details;，确保优化器能基于最新的表统计信息选择最优执行计划。

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