先找瓶颈，再动手改，别凭感觉加索引

-- 1. 按总耗时排序的 Top SQL（需开启 performance_schema）
SELECT
    DIGEST_TEXT                                   AS sql_pattern,
    COUNT_STAR                                    AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1e12, 2)               AS total_sec,
    ROUND(AVG_TIMER_WAIT / 1e9, 2)                AS avg_ms,
    SUM_ROWS_EXAMINED                             AS rows_examined,
    SUM_ROWS_SENT                                 AS rows_sent,
    ROUND(SUM_ROWS_EXAMINED / GREATEST(SUM_ROWS_SENT, 1), 1) AS scan_ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- 2. 全表扫描严重的语句（scan_ratio 越高越可疑）
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_NO_INDEX_USED                             AS no_index_count,
    SUM_NO_GOOD_INDEX_USED                        AS bad_index_count
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
ORDER BY SUM_NO_INDEX_USED DESC
LIMIT 20;

-- 3. 从未被使用的索引（可考虑删除，减少写放大）
SELECT
    object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND index_name <> 'PRIMARY'
  AND count_star = 0
  AND object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY object_schema, object_name;

-- 4. 表级 IO 热点
SELECT
    object_schema, object_name,
    count_read, count_write,
    ROUND(sum_timer_read / 1e12, 2)  AS read_sec,
    ROUND(sum_timer_write / 1e12, 2) AS write_sec
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY (sum_timer_read + sum_timer_write) DESC
LIMIT 15;