Mysql Slow Query Diagnose

-- Option A: MySQL 5.7+ performance_schema (recommended)
SELECT
  DIGEST_TEXT AS sql_template,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_time_sec,
  ROUND(AVG_TIMER_WAIT / 1000000000000, 3) AS avg_time_sec,
  ROUND(MAX_TIMER_WAIT / 1000000000000, 3) AS max_time_sec,
  ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS avg_rows_examined,
  ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS avg_rows_sent,
  SUM_NO_INDEX_USED AS no_index_count
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
  AND SUM_TIMER_WAIT > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- Option B: slow_log table (if enabled)
SELECT
  start_time,
  user_host,
  query_time,
  lock_time,
  rows_sent,
  rows_examined,
  LEFT(sql_text, 300) AS sql_fragment
FROM mysql.slow_log
WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY query_time DESC
LIMIT 20;

SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Slow_launch_threads';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log';

-- Get the EXPLAIN plan
EXPLAIN FORMAT=JSON {the_query};

-- Check current table indexes
SHOW INDEX FROM {table_name};

-- Check table size and row count
SELECT
  TABLE_NAME,
  TABLE_ROWS,
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '{db}' AND TABLE_NAME = '{table}';

IF index_hit_rate < 1% AND no_index_used:
  → Type: MISSING_INDEX (Critical)
  → Action: Add index
ELSE IF scan_efficiency > 50%:
  → Type: FULL_SCAN (High)
  → Action: Optimize query or add covering index
ELSE IF lock_ratio > 30%:
  → Type: LOCK_CONTENTION (Medium)
  → Action: Check concurrent transactions
ELSE IF avg_time > threshold:
  → Type: DATA_VOLUME (Medium)
  → Action: Archive or partition

-- Example: query is SELECT * FROM orders WHERE status = ? AND create_time > ?
-- Current: full table scan, 12M rows examined, 380 rows returned
-- Index hit rate: 380/12800000 = 0.003% → CRITICAL

-- Recommended index:
ALTER TABLE orders ADD INDEX idx_status_createtime (status, create_time);

-- Expected improvement:
-- rows_examined: 12,000,000 → ~40,000 (300x reduction)
-- query_time: 23.7s → <0.1s

🔍 MySQL Slow Query Diagnostic Report
══════════════════════════════════════
📋 Instance: {host}:{port}
   Analysis Period: Last {hours} hours
   Slow Query Threshold: {threshold}s
   Total Slow Queries: {count}

🔴 TOP {N} Slow Queries (by total impact)
─────────────────────────────────────────

  TOP 1 ⚡ Total Impact: {total_time}s across {count} executions
  ┌─────────────────────────────────────────────┐
  │ SQL: {sql_template}                         │
  │ Avg Time: {avg}s  |  Max: {max}s            │
  │ Rows Examined: {examined}  |  Returned: {sent}│
  │ Index Hit Rate: {rate}% 🔴 MISSING INDEX     │
  │                                             │
  │ Root Cause: Full table scan on {table}       │
  │   → No index on ({columns})                 │
  │                                             │
  │ ✅ Recommended:                              │
  │   ALTER TABLE {table} ADD INDEX              │
  │     idx_{name} ({columns});                  │
  │   Expected: {current}s → <0.1s              │
  └─────────────────────────────────────────────┘

  TOP 2 ⚡ ...
  TOP 3 ⚡ ...

📊 Summary
  | Rank | SQL Pattern | Executions | Avg Time | Hit Rate | Fix |
  |------|------------|------------|----------|----------|-----|
  | 1    | {pattern}  | {count}    | {time}s  | {rate}%  | Add index |
  | 2    | ...        | ...        | ...      | ...      | ... |

⚡ Priority Actions
  1. [Critical] Add index on {table}({cols}) — fixes TOP 1, est. 300x speedup
  2. [High] Add index on {table}({cols}) — fixes TOP 2
  3. [Medium] Schedule batch query for off-peak — TOP 3 is legitimate report

✅ Index Verification
══════════════════
  Query: {sql_template}
  Before: avg {old}s, examined {old_rows} rows
  After:  avg {new}s, examined {new_rows} rows
  Improvement: {factor}x faster 🎉

I'll analyze slow queries from the last hour.

🔍 MySQL Slow Query Diagnostic Report
══════════════════════════════════════
📋 Instance: db-prod-01:3306
   Analysis Period: Last 1 hour
   Total Slow Queries: 62

🔴 TOP 3 Slow Queries

  TOP 1 ⚡ 23.7s avg × 47 executions = 1,114s total impact
  ┌────────────────────────────────────────────────┐
  │ SELECT * FROM order_detail                     │
  │   WHERE create_time > ? AND status = ?         │
  │                                                │
  │ Avg Time: 23.7s | Max: 45.2s                  │
  │ Rows Examined: 12,800,000 | Returned: 380      │
  │ Index Hit Rate: 0.003% 🔴                       │
  │                                                │
  │ Root Cause: Full table scan — no index on      │
  │   (status, create_time)                        │
  │                                                │
  │ ✅ Fix: ALTER TABLE order_detail ADD INDEX      │
  │      idx_status_createtime (status, create_time)│
  │   Expected: 23.7s → <0.1s (237x faster)        │
  └────────────────────────────────────────────────┘

  TOP 2 ⚡ 8.4s avg × 12 executions = 100.8s total impact
  │ Full scan on user_order (5.6M rows)
  │ Fix: ADD INDEX (merchant_id)

  TOP 3 ⚡ 4.2s avg × 3 executions = 12.6s total impact
  │ Index hit, batch report — schedule off-peak

⚡ Priority Actions:
  1. [Critical] Add (status, create_time) on order_detail
  2. [High] Add (merchant_id) on user_order
  3. [Info] Schedule TOP 3 report query to 02:00-05:00