20251217-检验单索引优化

约 537 字大约 2 分钟

2025-12-17

检验单索引优化

冗余索引优化

索引首字段清单

SELECT
    TABLE_NAME,
    INDEX_NAME,
    INDEX_TYPE,
    -- 首字段（第一个字段）
    MAX(CASE WHEN SEQ_IN_INDEX = 1 THEN COLUMN_NAME END) AS FIRST_COLUMN,
    -- 所有字段（按顺序拼接）
    GROUP_CONCAT(
            CONCAT(
                    COLUMN_NAME,
                    CASE
                        WHEN SUB_PART IS NOT NULL THEN CONCAT('(', SUB_PART, ')')
                        ELSE ''
                        END
            )
            ORDER BY SEQ_IN_INDEX
            SEPARATOR ', '
    ) AS ALL_COLUMNS,
    -- 字段数量
    COUNT(*) AS COLUMN_COUNT,
    -- 索引属性
    IF(NON_UNIQUE = 0, 'UNIQUE', 'NON-UNIQUE') AS UNIQUENESS,
    IF(INDEX_NAME = 'PRIMARY', 'YES', 'NO') AS IS_PRIMARY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'qms_qit_incominspectorder_h'
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE
ORDER BY
    TABLE_NAME,
    MAX(CASE WHEN SEQ_IN_INDEX = 1 THEN COLUMN_NAME END);

TABLE_NAME	INDEX_NAME	INDEX_TYPE	FIRST_COLUMN	ALL_COLUMNS	COLUMN_COUNT	UNIQUENESS	IS_PRIMARY
qms_qit_incominspectorder_h	i_activity	BTREE	activity	activity	1	NON-UNIQUE	NO
qms_qit_incominspectorder_h	idx_checktype_dr	BTREE	check_type	check_type, status, recheck	3	NON-UNIQUE	NO
qms_qit_incominspectorder_h	idx_incominspectorder_h	BTREE	check_type	check_type, status, recheck, ytenant_id, pk_org, inspectDate	6	NON-UNIQUE	NO
qms_qit_incominspectorder_h	y_idx_code_tenantid_dr	BTREE	code	code, ytenant_id, dr	3	NON-UNIQUE	NO
qms_qit_incominspectorder_h	i_customer	BTREE	custom_id	custom_id	1	NON-UNIQUE	NO
qms_qit_incominspectorder_h	PRIMARY	BTREE	id	id	1	UNIQUE	YES
qms_qit_incominspectorder_h	idx_inspectdate_org_tenant	BTREE	inspectDate	inspectDate, pk_org, tenant_id	3	NON-UNIQUE	NO
qms_qit_incominspectorder_h	y_idx_inspectdate_org_tenant	BTREE	inspectDate	inspectDate, pk_org, ytenant_id	3	NON-UNIQUE	NO
qms_qit_incominspectorder_h	idx_ytenant_id_inspectDate	BTREE	inspectDate	inspectDate, ytenant_id	2	NON-UNIQUE	NO
qms_qit_incominspectorder_h	i_pk_applycheck_ytenant_id	BTREE	pk_applycheck	pk_applycheck, ytenant_id	2	NON-UNIQUE	NO
qms_qit_incominspectorder_h	idx_stockcheck	BTREE	pk_applycheckorg	pk_applycheckorg, pk_material, check_type, stockCheckType, dr, ytenant_id	6	NON-UNIQUE	NO
qms_qit_incominspectorder_h	idx_inspectdept_org_tenant	BTREE	pk_inspectdept	pk_inspectdept, pk_org, tenant_id	3	NON-UNIQUE	NO
qms_qit_incominspectorder_h	y_idx_inspectdept_org_tenant	BTREE	pk_inspectdept	pk_inspectdept, pk_org, ytenant_id	3	NON-UNIQUE	NO
qms_qit_incominspectorder_h	y_idx_material_org_tenant	BTREE	pk_material	pk_material, pk_org, ytenant_id	3	NON-UNIQUE	NO
qms_qit_incominspectorder_h	i_pk_org	BTREE	pk_org	pk_org	1	NON-UNIQUE	NO
qms_qit_incominspectorder_h	idx_incominspectorder_h_supplier_material	BTREE	pk_outsupplier	pk_outsupplier, pk_material, pk_org	3	NON-UNIQUE	NO
qms_qit_incominspectorder_h	idx_supplier_dr	BTREE	pk_outsupplier	pk_outsupplier, inspectDate, pk_material	3	NON-UNIQUE	NO
qms_qit_incominspectorder_h	i_prod_dept	BTREE	pk_prod_dept	pk_prod_dept, ytenant_id	2	NON-UNIQUE	NO
qms_qit_incominspectorder_h	y_idx_pudept_org_tenant	BTREE	pk_purchase_dept	pk_purchase_dept, pk_org, ytenant_id	3	NON-UNIQUE	NO
qms_qit_incominspectorder_h	i_project_id	BTREE	project_id	project_id	1	NON-UNIQUE	NO
qms_qit_incominspectorder_h	i_pubts_ytenant_id	BTREE	pubts	pubts, ytenant_id	2	NON-UNIQUE	NO
qms_qit_incominspectorder_h	i_org	BTREE	sales_org	sales_org	1	NON-UNIQUE	NO
qms_qit_incominspectorder_h	y_idx_code_tenant	BTREE	vbillcode	vbillcode, ytenant_id	2	NON-UNIQUE	NO
qms_qit_incominspectorder_h	i_wbs	BTREE	wbs	wbs	1	NON-UNIQUE	NO
qms_qit_incominspectorder_h	i_irs_yten_dr	BTREE	ytenant_id	ytenant_id, inspect_result_source, dr	3	NON-UNIQUE	NO
qms_qit_incominspectorder_h	idx_ytenant_id_pk_org	BTREE	ytenant_id	ytenant_id, pk_org	2	NON-UNIQUE	NO

冗余索引优化

-- 以`check_type`开始
alter table qms_qit_incominspectorder_h drop index idx_checktype_dr;

-- 以`inspectDate`开始
alter add index i_org_ytenant_date(pk_org, ytenant_id, inspectDate);
alter table qms_qit_incominspectorder_h drop index idx_ytenant_id_inspectDate;
alter table qms_qit_incominspectorder_h drop index idx_inspectdate_org_tenant;
alter table qms_qit_incominspectorder_h drop index y_idx_inspectdate_org_tenant;
alter table qms_qit_incominspectorder_h drop index i_pk_org;

-- 以`pk_inspectdept`开始
alter table qms_qit_incominspectorder_h drop index idx_inspectdept_org_tenant;

-- 以`idx_supplier_dr`开始
alter add index i_outsupp_material_org_date(pk_outsupplier, pk_material, pk_org, inspectDate);
alter table qms_qit_incominspectorder_h drop index idx_incominspectorder_h_supplier_material;
alter table qms_qit_incominspectorder_h drop index idx_supplier_dr;

-- 以`ytenant_id`开始
alter add index i_yten_irs_org(ytenant_id, inspect_result_source, pk_org);
alter table qms_qit_incominspectorder_h drop index i_irs_yten_dr;
alter table qms_qit_incominspectorder_h drop index idx_ytenant_id_pk_org;