SQL查询的优化方案，教你学会真正的书写SQL!

-- 假设有一个大的用户表`users`，经常根据用户名进行搜索CREATE INDEX idx_users_username ON users(username);

-- 不好的写法，可能导致全表扫描SELECT * FROM products WHERE price > 100 AND category = 'Electronics';
-- 更好的写法，如果category上有索引，会先通过索引过滤SELECT * FROM products WHERE category = 'Electronics' AND price > 10

-- 不推荐，尤其是当表有很多列时SELECT * FROM orders;
-- 推荐，只选择必要的字段SELECT order_id, customer_id, order_date FROM orders;

-- 假设有订单表orders和客户表customers，通过外键关联SELECT o.order_id, c.customer_name FROM orders AS oINNER JOIN customers AS c ON o.customer_id = c.customer_idWHERE c.country = 'USA';
-- 确保customer_id在两个表上都有索引

-- 使用子查询SELECT product_id FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'Germany');
-- 改进为JOINSELECT o.product_id FROM orders AS oINNER JOIN customers AS c ON o.customer_id = c.customer_idWHERE c.country = 'Germany';

-- 不理想的分页方法，随着offset增大，性能下降SELECT * FROM products ORDER BY product_id LIMIT 10 OFFSET 100;
-- 更好的分页方法，利用ID直接定位（假设product_id是连续的）SELECT * FROM products WHERE product_id > (SELECT product_id FROM products ORDER BY product_id LIMIT 10, 1)ORDER BY product_id LIMIT 10;

EXPLAIN SELECT product_id FROM orders WHERE customer_id = 123;
-- 根据输出调整查询或索引，比如如果看到Seq Scan，可能需要为customer_id添

-- 分析表收集统计信息，帮助优化器做出更好的决策ANALYZE products;
-- 重新构建破碎的索引REINDEX INDEX idx_products_customer_id;

-- 只获取前100条记录SELECT * FROM logs ORDER BY timestamp DESC LIMIT 100;

-- 假设使用Redis作为缓存IF NOT EXISTS redis.GET('recent_orders') THEN    SELECT * FROM orders WHERE order_date > NOW() - INTERVAL '1 DAY' INTO redis.SET('recent_orders', EXPIRE 60);END IF;

-- 假设有一个日志表logs，按日期进行分区CREATE TABLE logs (    log_id SERIAL PRIMARY KEY,    log_content TEXT,    log_time TIMESTAMP NOT NULL) PARTITION BY RANGE (log_time);
-- 创建具体分区CREATE TABLE logs_2023 PARTITION OF logsFOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
-- 查询时，数据库自动定位到相关分区SELECT * FROM logs WHERE log_time BETWEEN '2023-04-01' AND '2023-04-30';

-- 使用参数化查询（以PostgreSQL为例）PREPARE get_user_by_id (int) ASSELECT * FROM users WHERE user_id = $1;
EXECUTE get_user_by_id(123);
-- 或在应用程序中使用参数化查询，如在Python的psycopg2库cursor.execute("SELECT * FROM users WHERE user_id = %s", (user_id,))