SQL查询优化与索引设计咨询：残疾数据统计场景

需统计2018年1月每周罹患以下残疾类型的女性人数：精神障碍（Mental disorders）、认知障碍（Cognitive disorders）、社交障碍（Social disorders）；女性判定规则为出生编号（birth_number）第3-4位数字≥51。

现有SQL查询如下：

SELECT 
    disability_name,
    SUM(CASE WHEN TO_CHAR(start_date, 'IW') = '01' THEN 1 ELSE 0 END) AS first_week,
    SUM(CASE WHEN TO_CHAR(start_date, 'IW') = '02' THEN 1 ELSE 0 END) AS second_week,
    SUM(CASE WHEN TO_CHAR(start_date, 'IW') = '03' THEN 1 ELSE 0 END) AS third_week,
    SUM(CASE WHEN TO_CHAR(start_date, 'IW') = '04' THEN 1 ELSE 0 END) AS fourth_week
FROM p_disabilities
JOIN p_disability_type
    ON p_disabilities.id_disability = p_disability_type.disability_id
WHERE TO_CHAR(start_date, 'YYYY') = '2018'
  AND TO_CHAR(start_date, 'MM') = '01'
  AND TO_NUMBER(SUBSTR(birth_number, 3, 2)) >= 51
GROUP BY disability_name;

表结构脚本：

CREATE TABLE p_disabilities ( 
id_disability CHAR(6) NOT NULL PRIMARY KEY, 
birth_number CHAR(11) NOT NULL, 
start_date DATE NOT NULL, 
end_date DATE NOT NULL, 
disability_id NUMBER NOT NULL ); 

CREATE TABLE p_disability_type ( 
disability_id NUMBER NOT NULL, 
disability_name VARCHAR2(50) );

问题优化需求 #

因数据量较大，需对上述SQL查询进行简化与性能优化，同时咨询针对start_date、birth_number、disability_id字段的最优索引设计方案。

SQL查询优化方案 #

1. 修正JOIN关联错误 #

原SQL中关联条件p_disabilities.id_disability = p_disability_type.disability_id存在类型不匹配问题：id_disability是CHAR(6)类型，disability_id是NUMBER类型，会触发隐式类型转换，严重影响性能且可能导致结果错误。正确关联条件应为p_disabilities.disability_id = p_disability_type.disability_id。

2. 优化WHERE条件，避免索引失效 #

• 将日期过滤从TO_CHAR(start_date, 'YYYY') = '2018' AND TO_CHAR(start_date, 'MM') = '01'改为直接使用日期范围start_date BETWEEN DATE '2018-01-01' AND DATE '2018-01-31'，直接利用start_date字段的索引。
• 将出生编号的数字转换判断TO_NUMBER(SUBSTR(birth_number, 3, 2)) >= 51改为字符比较SUBSTR(birth_number, 3, 2) >= '51'，00-99的字符排序逻辑与数字排序一致，避免类型转换开销。
• 新增指定残疾类型的过滤条件，减少不必要的数据扫描：disability_name IN ('Mental disorders', 'Cognitive disorders', 'Social disorders')。

3. 优化后的SQL #

SELECT 
    dt.disability_name,
    SUM(CASE WHEN TO_CHAR(d.start_date, 'IW') = '01' THEN 1 ELSE 0 END) AS first_week,
    SUM(CASE WHEN TO_CHAR(d.start_date, 'IW') = '02' THEN 1 ELSE 0 END) AS second_week,
    SUM(CASE WHEN TO_CHAR(d.start_date, 'IW') = '03' THEN 1 ELSE 0 END) AS third_week,
    SUM(CASE WHEN TO_CHAR(d.start_date, 'IW') = '04' THEN 1 ELSE 0 END) AS fourth_week
FROM p_disabilities d
JOIN p_disability_type dt
    ON d.disability_id = dt.disability_id
WHERE d.start_date BETWEEN DATE '2018-01-01' AND DATE '2018-01-31'
  AND SUBSTR(d.birth_number, 3, 2) >= '51'
  AND dt.disability_name IN ('Mental disorders', 'Cognitive disorders', 'Social disorders')
GROUP BY dt.disability_name;

最优索引设计方案 #

1. p_disabilities表索引 #

创建复合覆盖索引：

CREATE INDEX idx_disabilities_start_birth_type ON p_disabilities(start_date, birth_number, disability_id);

• 优先按start_date排序，快速定位2018年1月的数据集。
• 包含birth_number，可直接在索引中完成女性判定过滤，无需回表。
• 包含disability_id，关联p_disability_type时直接获取关联字段，避免回表。

若特定残疾类型数据占比极低，可考虑调整顺序为：

CREATE INDEX idx_disabilities_type_start_birth ON p_disabilities(disability_id, start_date, birth_number);

具体选择需结合实际数据分布，通过执行计划验证。

2. p_disability_type表索引 #

首先添加主键约束（或唯一索引），确保disability_id唯一性并加速关联：

ALTER TABLE p_disability_type ADD CONSTRAINT pk_disability_type PRIMARY KEY (disability_id);

同时创建名称索引，快速过滤指定残疾类型：

CREATE INDEX idx_disability_type_name ON p_disability_type(disability_name);

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