id BIGINT PRIMARY KEY AUTO_INCREMENT,
    invoicing_group_no VARCHAR(50) NOT NULL COMMENT '開票組編號',
    group_name VARCHAR(100) COMMENT '組名稱',
    status TINYINT DEFAULT 1 COMMENT '狀態(tài) 1-啟用 0-禁用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_status (status)
) COMMENT='開票組配置表，約1200行數(shù)據(jù)';

    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    claim_approval_no VARCHAR(50) NOT NULL COMMENT '審批單號',
    approval_status VARCHAR(20) COMMENT '審批狀態(tài)',
    amount DECIMAL(12,2) COMMENT '審批金額',
    applicant_id BIGINT COMMENT '申請人ID',
    apply_date DATE COMMENT '申請日期',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_approval_no (claim_approval_no),
    KEY idx_apply_date (apply_date),
    KEY idx_applicant (applicant_id)
) COMMENT='審批主表，約12萬行數(shù)據(jù)';

    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    business_no VARCHAR(50) NOT NULL COMMENT '業(yè)務(wù)單號',
    invoicing_group_no VARCHAR(50) NOT NULL COMMENT '關(guān)聯(lián)開票組',
    claim_approval_no VARCHAR(50) COMMENT '關(guān)聯(lián)審批單號',
    amount DECIMAL(10,2) COMMENT '金額',
    status TINYINT DEFAULT 0 COMMENT '狀態(tài) 0-待處理 1-已處理 2-已取消',
    create_user_id BIGINT COMMENT '創(chuàng)建人',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_business_no (business_no),
    KEY idx_status (status),
    KEY idx_create_user (create_user_id),
    KEY idx_created_at (created_at)
) COMMENT='業(yè)務(wù)主表，約100萬行數(shù)據(jù)，需關(guān)聯(lián)小表和大表';

SELECT m.*, g.group_name, a.approval_status, a.amount as approval_amount
FROM main_table m
JOIN group_table g ON m.invoicing_group_no = g.invoicing_group_no
JOIN approval_table a ON m.claim_approval_no = a.claim_approval_no
WHERE m.invoicing_group_no = 'GROUP001'
  AND m.status = 1
  AND g.status = 1
ORDER BY m.created_at DESC
LIMIT 100;

INDEX(status, created_at)  -- 適合 WHERE status=1 AND created_at > '2024-01-01'

-- 差索引：范圍在前，等值在后  
INDEX(created_at, status)  -- 范圍查詢會中斷索引使用

ALTER TABLE main_table ADD INDEX idx_group_approval (invoicing_group_no, claim_approval_no, status, created_at);

-- 小表：關(guān)聯(lián)字段索引
ALTER TABLE group_table ADD INDEX idx_invoicing_group (invoicing_group_no, status);

-- 大表：關(guān)聯(lián)字段索引
ALTER TABLE approval_table ADD INDEX idx_claim_approval (claim_approval_no);