SELECT o.*, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id  -- user_id或u.id缺少索引
WHERE o.create_time > '2023-01-01'

-- 單列索引示例
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 復(fù)合索引示例（多列連接條件）
CREATE INDEX idx_order_composite ON orders(user_id, product_id);

-- orders.user_id是VARCHAR，而users.id是INT
SELECT * FROM orders o JOIN users u ON o.user_id = u.id

-- 修改表結(jié)構(gòu)統(tǒng)一類型
ALTER TABLE orders MODIFY user_id INT;

-- 或者使用顯式轉(zhuǎn)換（不推薦，影響性能）
SELECT * FROM orders o JOIN users u ON CAST(o.user_id AS SIGNED) = u.id

-- 存在索引idx_status_create_time(status, create_time)
SELECT * FROM orders WHERE create_time > '2023-01-01'  -- 無法使用索引

-- 調(diào)整查詢順序
SELECT * FROM orders WHERE status = 1 AND create_time > '2023-01-01'

-- 或創(chuàng)建新的復(fù)合索引
CREATE INDEX idx_create_time_status ON orders(create_time, status);

-- 原始查詢
SELECT o.id, o.order_no, u.name, p.product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 1
ORDER BY o.create_time DESC;

-- 創(chuàng)建覆蓋索引
CREATE INDEX idx_order_covering ON orders(
    status, 
    create_time DESC, 
    user_id, 
    product_id
) INCLUDE (id, order_no);

SELECT o.*, u.name, p.product_name
FROM (
    SELECT * FROM orders 
    WHERE status = 1
    ORDER BY create_time DESC
    LIMIT 1000
) o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id;

-- 不推薦
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE vip = 1);

-- 推薦
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id AND u.vip = 1;

# MySQL配置示例
join_buffer_size = 8M  # 增大連接緩沖區(qū)
sort_buffer_size = 4M  # 排序緩沖區(qū)
read_rnd_buffer_size = 4M  # 隨機(jī)讀緩沖區(qū)
optimizer_switch = 'index_merge=on'  # 啟用索引合并優(yōu)化

SELECT o.*, u.*, p.*
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.status IN (2,3,5)
AND u.vip_level > 3
AND p.category_id = 10
ORDER BY o.create_time DESC
LIMIT 50;

SELECT o.*, u.*, p.*
FROM (
    SELECT * FROM orders 
    WHERE status IN (2,3,5)
    ORDER BY create_time DESC
    LIMIT 50
) o
JOIN users u ON o.user_id = u.id AND u.vip_level > 3
JOIN products p ON o.product_id = p.id AND p.category_id = 10;

CREATE INDEX idx_orders_status_time ON orders(status, create_time DESC);
CREATE INDEX idx_users_vip ON users(vip_level, id);
CREATE INDEX idx_products_category ON products(category_id, id);

ANALYZE TABLE orders;
ANALYZE TABLE users;
ANALYZE TABLE products;

ALTER TABLE orders ENGINE=InnoDB;  -- 重建表整理碎片

-- 啟用慢查詢?nèi)罩?
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;