-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 临时启用（重启后失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 记录未使用索引的查询

-- 永久启用（修改my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1  -- 记录管理语句

EXPLAIN SELECT * FROM orders 
WHERE status = 'completed' 
  AND created_at > '2024-01-01'
ORDER BY total_amount DESC;

EXPLAIN ANALYZE SELECT * FROM orders 
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 100;

-- ❌ 索引失效场景
-- 1. 索引列上使用函数
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- 2. 索引列上进行运算
SELECT * FROM products WHERE price * 1.1 > 100;

-- 3. 使用OR连接非索引字段
SELECT * FROM users WHERE id = 1 OR name = 'test';

-- 4. LIKE以%开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- 5. 数据类型不匹配
SELECT * FROM orders WHERE user_id = '123';  -- user_id是INT

-- ✅ 正确写法
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
SELECT * FROM products WHERE price > 100 / 1.1;
SELECT * FROM users WHERE id = 1 UNION SELECT * FROM users WHERE name = 'test';
SELECT * FROM users WHERE email LIKE 'test%@gmail.com';
SELECT * FROM orders WHERE user_id = 123;

-- 创建复合索引（遵循最左前缀原则）
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- 有效查询（使用索引）
SELECT * FROM orders WHERE status = 'completed';
SELECT * FROM orders WHERE status = 'completed' AND created_at > '2024-01-01';
SELECT * FROM orders WHERE status = 'completed' ORDER BY created_at;

-- 无效查询（不使用索引）
SELECT * FROM orders WHERE created_at > '2024-01-01';

-- ❌ 错误：查询所有列
SELECT * FROM orders WHERE status = 'completed';

-- ✅ 正确：只查询需要的列
SELECT id, order_no, total_amount, created_at 
FROM orders WHERE status = 'completed';

-- ❌ 低效分页（OFFSET过大）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- ✅ 高效分页（基于范围查询）
SELECT * FROM orders 
WHERE id > 1000000 
ORDER BY id 
LIMIT 10;

-- ✅ 使用游标分页
SELECT * FROM orders 
WHERE created_at > '2024-01-15 10:00:00' 
ORDER BY created_at 
LIMIT 100;

-- ❌ 低效：小表驱动大表
SELECT * FROM small_table s
JOIN large_table l ON s.id = l.small_id;

-- ✅ 高效：确保小表在前
SELECT * FROM large_table l
JOIN small_table s ON l.small_id = s.id;

-- ✅ 使用STRAIGHT_JOIN强制顺序
SELECT STRAIGHT_JOIN * 
FROM large_table l
JOIN small_table s ON l.small_id = s.id;

-- ❌ 低效子查询
SELECT * FROM orders 
WHERE user_id IN (
    SELECT id FROM users WHERE country = 'China'
);

-- ✅ 高效JOIN
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'China';

-- ✅ 使用EXISTS
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM users u 
    WHERE u.id = o.user_id AND u.country = 'China'
);

-- 启用Performance Schema
SET GLOBAL performance_schema = ON;

-- 查询慢查询统计
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%orders%'
ORDER BY SUM_TIMER_WAIT DESC;

-- 查询索引使用情况
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
ORDER BY COUNT_STAR DESC;

-- 优化前
EXPLAIN SELECT * FROM orders 
WHERE status = 'pending';
-- type: ALL, rows: 1000000

-- 创建索引
CREATE INDEX idx_orders_status ON orders(status);

-- 优化后
EXPLAIN SELECT * FROM orders 
WHERE status = 'pending';
-- type: ref, rows: 1000

-- 慢查询
SELECT * FROM orders 
WHERE user_id = 12345 
ORDER BY created_at DESC;

-- EXPLAIN分析
-- type: ALL, rows: 500000

-- 优化：创建复合索引
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- 优化后
-- type: ref, rows: 50

-- 慢查询
SELECT id, name, price 
FROM products 
WHERE category_id = 1 
ORDER BY price DESC;

-- EXPLAIN分析
-- Extra: Using where; Using filesort

-- 优化：创建覆盖索引
CREATE INDEX idx_products_category_price ON products(category_id, price DESC);

-- 优化后
-- Extra: Using index

-- 慢查询
SELECT o.id, o.order_no, u.username, p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed';

-- 优化：确保JOIN字段有索引
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);