SELECT * FROM users WHERE status = 1;

SELECT id, name, email, status FROM users WHERE status = 1;

-- 兩種方式性能相當(dāng)
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE vip_level > 3);

SELECT * FROM orders o 
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.vip_level > 3);

-- EXISTS通常更優(yōu)
SELECT * FROM large_table t1
WHERE EXISTS (SELECT 1 FROM large_table t2 WHERE t2.parent_id = t1.id);

-- 索引失效！
SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-01-01';
SELECT * FROM products WHERE LOWER(name) = 'iphone';

-- 使用范圍查詢
SELECT * FROM orders 
WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

-- 保持字段原樣查詢  
SELECT * FROM products WHERE name = 'iPhone';

-- 性能較差，但結(jié)果準(zhǔn)確
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- 性能更好
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

-- 高頻查詢1：按狀態(tài)查詢
SELECT * FROM orders WHERE status = 'pending';

-- 高頻查詢2：按用戶+時(shí)間查詢  
SELECT * FROM orders WHERE user_id = 123 AND create_time > '2024-01-01';

-- 索引方案
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_user_time ON orders(user_id, create_time);

WHERE status = 'pending'  -- 使用索引
WHERE status = 'pending' AND create_time > '2024-01-01'  -- 使用索引
WHERE status = 'pending' AND create_time > '2024-01-01' AND user_id = 123  -- 使用索引

WHERE create_time > '2024-01-01'  -- 索引失效！
WHERE user_id = 123  -- 索引失效！
WHERE status = 'pending' AND user_id = 123  -- 部分使用索引

-- 索引失效的寫(xiě)法
SELECT * FROM products WHERE price + 100 > 500;
SELECT * FROM users WHERE YEAR(create_time) = 2024;

-- 優(yōu)化后的寫(xiě)法
SELECT * FROM products WHERE price > 400;
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- 越往后越慢！
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 使用游標(biāo)分頁(yè)
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 或者記錄上次查詢的最大ID
SELECT * FROM orders WHERE id > last_max_id ORDER BY id LIMIT 20;

for (User user : userList) {
    String sql = "INSERT INTO users(name, age) VALUES(?, ?)";
    // 每次插入都產(chǎn)生網(wǎng)絡(luò)IO和事務(wù)開(kāi)銷
}

-- 一次批量插入
INSERT INTO users(name, age) 
VALUES('張三', 25), ('李四', 30), ('王五', 28);

SELECT * FROM products 
WHERE category_id IN (
    SELECT id FROM categories WHERE type = 'electronic'
);

SELECT p.* FROM products p 
INNER JOIN categories c ON p.category_id = c.id 
WHERE c.type = 'electronic';

SELECT p.* FROM products p 
STRAIGHT_JOIN categories c ON p.category_id = c.id 
WHERE c.type = 'electronic';

-- 假設(shè)在age字段有索引
SELECT name FROM users WHERE age > 18;

-- 建立復(fù)合索引
CREATE INDEX idx_users_age_name ON users(age, name);

-- 現(xiàn)在查詢直接在索引中完成
SELECT name FROM users WHERE age > 18;

-- 錯(cuò)誤選擇
CREATE TABLE users (
    id VARCHAR(50),  -- 應(yīng)該用INT/BIGINT
    age VARCHAR(10), -- 應(yīng)該用TINYINT
    create_time VARCHAR(20) -- 應(yīng)該用DATETIME
);

-- 正確選擇
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT,
    age TINYINT UNSIGNED,
    create_time DATETIME,
    PRIMARY KEY(id)
);

-- 查詢變得復(fù)雜
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- 聚合函數(shù)忽略NULL
SELECT AVG(age) FROM users; -- 忽略NULL值

-- 設(shè)置默認(rèn)值
CREATE TABLE users (
    phone VARCHAR(20) NOT NULL DEFAULT '',
    age INT NOT NULL DEFAULT 0
);

-- 多表關(guān)聯(lián)查詢
SELECT u.name, o.order_no, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.id = 123;

-- 單表查詢（在orders表中冗余用戶和商品信息）
SELECT order_no, user_name, product_name 
FROM orders 
WHERE user_id = 123;

SELECT * FROM orders 
WHERE user_id = 123 
AND status IN ('paid', 'shipped') 
AND create_time BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY create_time DESC;

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status IN ('paid', 'shipped');

CREATE INDEX idx_orders_user_status_time ON orders(user_id, status, create_time);

SELECT order_id, user_id, amount, status, create_time 
FROM orders 
WHERE user_id = 123 
AND status IN ('paid', 'shipped') 
AND create_time >= '2024-01-01' 
AND create_time < '2024-07-01'  -- 避免BETWEEN
ORDER BY create_time DESC;

-- 每天執(zhí)行一次，但需要30秒
SELECT COUNT(*) as total_orders,
       SUM(amount) as total_amount,
       AVG(amount) as avg_amount
FROM orders 
WHERE DATE(create_time) = CURDATE();

SELECT COUNT(*) as total_orders,
       SUM(amount) as total_amount, 
       AVG(amount) as avg_amount
FROM orders 
WHERE create_time >= DATE(CURDATE()) 
AND create_time < DATE(CURDATE()) + INTERVAL 1 DAY;

-- 每日預(yù)聚合
CREATE TABLE order_daily_stats (
    stat_date DATE,
    total_orders INT,
    total_amount DECIMAL(15,2),
    avg_amount DECIMAL(10,2),
    PRIMARY KEY(stat_date)
);

-- 查詢時(shí)直接查匯總表
SELECT * FROM order_daily_stats WHERE stat_date = CURDATE();

EXPLAIN SELECT * FROM users WHERE age > 18;