-- MySQL 5.7.21：只读诊断脚本，请先选中生产业务库。
-- 下面默认查询全部时间、全部单位，与列表初次打开一致。
-- 请先返回本文件的版本、行数估计、索引、8 份 EXPLAIN 结果；不要先执行建索引脚本。
-- EXPLAIN 不返回业务明细，不代表真实耗时；验证耗时需另行运行实际 SQL。
SELECT VERSION(), DATABASE();
SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN ('negative','negative_blame','sup_police','sup_depart','sup_depart_police_size');
SHOW INDEX FROM negative;
SHOW INDEX FROM negative_blame;
SHOW INDEX FROM sup_police;
SHOW INDEX FROM sup_depart;
SHOW INDEX FROM sup_depart_police_size;

SET @beginTime = '1949-01-01 00:00:00';
SET @endTime = NOW();

-- 单位问题库：优化前列表查询（第一页 10 条）
EXPLAIN
SELECT
            d.id depart_id,
            d1.short_name parent_depart_name,
            d.short_name depart_name,
            p.police_size,
            p.aux_size,
            count(DISTINCT nb.blameIdCode) verify_police_size,
            count(DISTINCT n.id) verify_size
        FROM sup_depart d
            LEFT JOIN sup_depart d1 ON d.pid = d1.id
            LEFT JOIN sup_depart_police_size p ON p.depart_id = d.id
            INNER JOIN negative n ON d.id = n.involveDepartId
            LEFT JOIN negative_blame nb ON n.id = nb.negativeId
        WHERE d.level = 3
            AND n.crtTime BETWEEN @beginTime AND @endTime



        GROUP BY d.id, d1.short_name, d.short_name, p.police_size, p.aux_size
        ORDER BY
             verify_size
             DESC , d.id ASC
LIMIT 0, 10;

-- 单位问题库：优化前分页总数
EXPLAIN
SELECT count(*) FROM (
SELECT
            d.id depart_id,
            d1.short_name parent_depart_name,
            d.short_name depart_name,
            p.police_size,
            p.aux_size,
            count(DISTINCT nb.blameIdCode) verify_police_size,
            count(DISTINCT n.id) verify_size
        FROM sup_depart d
            LEFT JOIN sup_depart d1 ON d.pid = d1.id
            LEFT JOIN sup_depart_police_size p ON p.depart_id = d.id
            INNER JOIN negative n ON d.id = n.involveDepartId
            LEFT JOIN negative_blame nb ON n.id = nb.negativeId
        WHERE d.level = 3
            AND n.crtTime BETWEEN @beginTime AND @endTime



        GROUP BY d.id, d1.short_name, d.short_name, p.police_size, p.aux_size
) counted;

-- 单位问题库：优化后列表查询（第一页 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;

-- 单位问题库：优化后分页总数
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;

-- 个人问题库：优化前列表查询（第一页 10 条）
EXPLAIN
SELECT
            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,
            count(DISTINCT n.id) verify_size
        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
                INNER JOIN negative_blame nb ON p.id_code = nb.blameIdCode AND nb.type = 'personal'
                INNER JOIN negative n ON nb.negativeId = n.id
        WHERE
            p.del = 0
            AND n.checkStatus IN ('1', '2')
            AND n.processing_status = 'completed'
            AND n.discoveryTime BETWEEN @beginTime AND @endTime





        GROUP BY
            p.id_code,
            p.NAME,
            p.emp_no,
            p.employment_date,
            p.position,
            p.person_type,
            d.short_name,
            d.id,
            d1.short_name
        ORDER BY
            verify_size
             DESC , p.id_code ASC
LIMIT 0, 10;

-- 个人问题库：优化前分页总数
EXPLAIN
SELECT count(*) FROM (
SELECT
            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,
            count(DISTINCT n.id) verify_size
        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
                INNER JOIN negative_blame nb ON p.id_code = nb.blameIdCode AND nb.type = 'personal'
                INNER JOIN negative n ON nb.negativeId = n.id
        WHERE
            p.del = 0
            AND n.checkStatus IN ('1', '2')
            AND n.processing_status = 'completed'
            AND n.discoveryTime BETWEEN @beginTime AND @endTime





        GROUP BY
            p.id_code,
            p.NAME,
            p.emp_no,
            p.employment_date,
            p.position,
            p.person_type,
            d.short_name,
            d.id,
            d1.short_name
) 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;

-- 个人问题库：优化后分页总数
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;