-- MySQL 5.7.21：result1.txt 确认类型/字符集不一致后的复测，无 DDL、无业务数据修改。
-- 步骤一：先单独执行以下检查。affected_rows 必须全部为 0，才部署新版单位查询。
-- 非零时请返回计数，先处理编号语义差异，不要直接部署。
SELECT 'negative.involveDepartId 非标准编号' AS check_item, COUNT(*) AS affected_rows
FROM negative n JOIN sup_depart d ON n.involveDepartId = d.id
WHERE d.level = 3
  AND NOT (n.involveDepartId = CAST(d.id AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci)
UNION ALL
SELECT 'sup_depart_police_size.depart_id 非标准编号', COUNT(*)
FROM sup_depart_police_size p JOIN sup_depart d ON p.depart_id = d.id
WHERE d.level = 3
  AND NOT (p.depart_id = CAST(d.id AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci)
UNION ALL
SELECT 'negative.id 无法无损转成 utf8', COUNT(*)
FROM negative
WHERE BINARY id <> BINARY CONVERT(CONVERT(id USING utf8) USING utf8mb4);

-- 步骤二：以上计数全为 0 后，执行下列 EXPLAIN 和实际查询。
-- 预期：nb 不再全索引扫描+Block Nested Loop，p 使用主键，单位总数中的 n 使用单位索引。
-- 查询时间仍需实测；请返回四份 EXPLAIN 和 SHOW PROFILES 的耗时，无需返回人员明细。
SET @beginTime = '1949-01-01 00:00:00';
SET @endTime = NOW();
SET profiling = 1;

-- 单位问题库：优化后列表查询（第一页 10 条）
EXPLAIN
SELECT DISTINCT
            d.id depart_id,
            d1.short_name parent_depart_name,
            d.short_name depart_name,
            p.police_size,
            p.aux_size,
            stats.verify_police_size,
            stats.verify_size
        FROM (
            SELECT d.id depart_id,
                count(DISTINCT nb.blameIdCode) verify_police_size,
                count(DISTINCT n.id) verify_size
            FROM sup_depart d
                INNER JOIN negative n ON n.involveDepartId = CAST(d.id AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci
                LEFT JOIN negative_blame nb ON nb.negativeId = CONVERT(n.id USING utf8) COLLATE utf8_general_ci
            WHERE
        d.level = 3




                AND n.crtTime BETWEEN @beginTime AND @endTime
            GROUP BY d.id
        ) stats
            INNER JOIN sup_depart d ON d.id = stats.depart_id
            LEFT JOIN sup_depart d1 ON d.pid = d1.id
            LEFT JOIN sup_depart_police_size p ON p.depart_id = CAST(d.id AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci
        ORDER BY
             verify_size
             DESC , d.id ASC
LIMIT 0, 10;
SHOW WARNINGS;

SELECT SQL_NO_CACHE DISTINCT
            d.id depart_id,
            d1.short_name parent_depart_name,
            d.short_name depart_name,
            p.police_size,
            p.aux_size,
            stats.verify_police_size,
            stats.verify_size
        FROM (
            SELECT d.id depart_id,
                count(DISTINCT nb.blameIdCode) verify_police_size,
                count(DISTINCT n.id) verify_size
            FROM sup_depart d
                INNER JOIN negative n ON n.involveDepartId = CAST(d.id AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci
                LEFT JOIN negative_blame nb ON nb.negativeId = CONVERT(n.id USING utf8) COLLATE utf8_general_ci
            WHERE
        d.level = 3




                AND n.crtTime BETWEEN @beginTime AND @endTime
            GROUP BY d.id
        ) stats
            INNER JOIN sup_depart d ON d.id = stats.depart_id
            LEFT JOIN sup_depart d1 ON d.pid = d1.id
            LEFT JOIN sup_depart_police_size p ON p.depart_id = CAST(d.id AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci
        ORDER BY
             verify_size
             DESC , d.id ASC
LIMIT 0, 10;

-- 单位问题库：优化后分页总数
EXPLAIN
SELECT count(*) FROM (
            SELECT DISTINCT d.id, p.police_size, p.aux_size
            FROM sup_depart d
                LEFT JOIN sup_depart_police_size p ON p.depart_id = CAST(d.id AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci
            WHERE
        d.level = 3




                AND EXISTS (
                    SELECT 1 FROM negative n
                    WHERE n.involveDepartId = CAST(d.id AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci
                        AND n.crtTime BETWEEN @beginTime AND @endTime
                )
        ) counted;
SHOW WARNINGS;

SELECT SQL_NO_CACHE count(*) FROM (
            SELECT DISTINCT d.id, p.police_size, p.aux_size
            FROM sup_depart d
                LEFT JOIN sup_depart_police_size p ON p.depart_id = CAST(d.id AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci
            WHERE
        d.level = 3




                AND EXISTS (
                    SELECT 1 FROM negative n
                    WHERE n.involveDepartId = CAST(d.id AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci
                        AND n.crtTime BETWEEN @beginTime AND @endTime
                )
        ) counted;

-- 个人问题库：优化后列表查询（第一页 10 条）
EXPLAIN
SELECT DISTINCT
        p.id_code, p.name, p.emp_no, p.position, p.employment_date, p.person_type,
        d.short_name depart_name, d.id depart_id, d1.short_name parent_depart_name
     , stats.verify_size
        FROM (
            SELECT nb.blameIdCode, count(DISTINCT n.id) verify_size
            FROM negative n
                INNER JOIN negative_blame nb ON nb.negativeId = n.id
            WHERE
        nb.type = 'personal'
        AND n.checkStatus IN ('1', '2')
        AND n.processing_status = 'completed'
        AND n.discoveryTime BETWEEN @beginTime AND @endTime

            GROUP BY nb.blameIdCode
        ) stats
            INNER JOIN sup_police p ON p.id_code = stats.blameIdCode
            LEFT JOIN sup_depart d ON p.org_id = d.id
            LEFT JOIN sup_depart d1 ON d.pid = d1.id AND d1.level >= 2
        WHERE
        p.del = 0





        ORDER BY verify_size
             DESC , p.id_code ASC
LIMIT 0, 10;
SHOW WARNINGS;

SELECT SQL_NO_CACHE DISTINCT
        p.id_code, p.name, p.emp_no, p.position, p.employment_date, p.person_type,
        d.short_name depart_name, d.id depart_id, d1.short_name parent_depart_name
     , stats.verify_size
        FROM (
            SELECT nb.blameIdCode, count(DISTINCT n.id) verify_size
            FROM negative n
                INNER JOIN negative_blame nb ON nb.negativeId = n.id
            WHERE
        nb.type = 'personal'
        AND n.checkStatus IN ('1', '2')
        AND n.processing_status = 'completed'
        AND n.discoveryTime BETWEEN @beginTime AND @endTime

            GROUP BY nb.blameIdCode
        ) stats
            INNER JOIN sup_police p ON p.id_code = stats.blameIdCode
            LEFT JOIN sup_depart d ON p.org_id = d.id
            LEFT JOIN sup_depart d1 ON d.pid = d1.id AND d1.level >= 2
        WHERE
        p.del = 0





        ORDER BY verify_size
             DESC , p.id_code ASC
LIMIT 0, 10;

-- 个人问题库：优化后分页总数
EXPLAIN
SELECT count(*) FROM (
            SELECT DISTINCT
        p.id_code, p.name, p.emp_no, p.position, p.employment_date, p.person_type,
        d.short_name depart_name, d.id depart_id, d1.short_name parent_depart_name

            FROM sup_police p
                LEFT JOIN sup_depart d ON p.org_id = d.id
                LEFT JOIN sup_depart d1 ON d.pid = d1.id AND d1.level >= 2
            WHERE
        p.del = 0





                AND EXISTS (
                    SELECT 1 FROM negative_blame nb
                    WHERE nb.blameIdCode = p.id_code AND nb.type = 'personal'
                        AND EXISTS (
                            SELECT 1 FROM negative n
                            WHERE n.id = nb.negativeId
                                AND
        n.checkStatus IN ('1', '2')
        AND n.processing_status = 'completed'
        AND n.discoveryTime BETWEEN @beginTime AND @endTime

                        )
                )
        ) counted;
SHOW WARNINGS;

SELECT SQL_NO_CACHE count(*) FROM (
            SELECT DISTINCT
        p.id_code, p.name, p.emp_no, p.position, p.employment_date, p.person_type,
        d.short_name depart_name, d.id depart_id, d1.short_name parent_depart_name

            FROM sup_police p
                LEFT JOIN sup_depart d ON p.org_id = d.id
                LEFT JOIN sup_depart d1 ON d.pid = d1.id AND d1.level >= 2
            WHERE
        p.del = 0





                AND EXISTS (
                    SELECT 1 FROM negative_blame nb
                    WHERE nb.blameIdCode = p.id_code AND nb.type = 'personal'
                        AND EXISTS (
                            SELECT 1 FROM negative n
                            WHERE n.id = nb.negativeId
                                AND
        n.checkStatus IN ('1', '2')
        AND n.processing_status = 'completed'
        AND n.discoveryTime BETWEEN @beginTime AND @endTime

                        )
                )
        ) counted;

SHOW PROFILES;
SET profiling = 0;