MySQL索引实战：一文看懂单字段索引与组合索引如何选择？

-- 用户登录校验SELECT * FROM users WHERE username = '张三';CREATE INDEX idx_username ON users(username);

-- 获取销售额最高的城市TOP10SELECT city, SUM(amount) FROM orders GROUP BY city ORDER BY SUM(amount) DESC;CREATE INDEX idx_city ON orders(city);

-- 订单与用户表关联查询SELECT * FROM orders o JOIN users u ON o.user_id = u.id;CREATE INDEX idx_user_id ON orders(user_id);

-- 查询某用户的年度订单SELECT * FROM orders WHERE user_id = 1001   AND order_date BETWEEN '2025-01-01' AND '2025-12-31';-- 推荐方式（遵循最左前缀规则）CREATE INDEX idx_user_date ON orders(user_id, order_date);

-- 统计未支付订单总数SELECT COUNT(*) FROM orders WHERE status = 'unpaid';-- 构建覆盖索引CREATE INDEX idx_status ON orders(status);

-- 查看用户最近10条消息SELECT * FROM messages WHERE user_id = 123 ORDER BY create_time DESC LIMIT 10;CREATE INDEX idx_user_time ON messages(user_id, create_time);

CREATE TABLE orders (    order_id INT PRIMARY KEY,    user_id INT,    status ENUM('已支付','未支付'),    create_time DATETIME);

-- 无索引支持下，执行耗时约2秒SELECT * FROM orders WHERE user_id = 1001   AND status = '已支付';

-- 创建高效组合索引CREATE INDEX idx_user_status ON orders(user_id, status);

-- 分别创建两个独立索引CREATE INDEX idx_tracking ON logistics(tracking_number);CREATE INDEX idx_update_time ON logistics(update_time);

-- 建立组合索引以提升效率CREATE INDEX idx_tracking_time ON logistics(tracking_number, update_time);

SELECT status FROM logistics WHERE tracking_number = 'TN20250730' ORDER BY update_time DESC LIMIT 1;

-- 错误示例（无法有效匹配查询）CREATE INDEX idx_city_name ON users(city, username);-- 正确顺序（符合最左前缀原则）CREATE INDEX idx_name_city ON users(username, city);

-- 这些单独索引作用有限CREATE INDEX idx_name ON users(name);CREATE INDEX idx_age ON users(age);CREATE INDEX idx_gender ON users(gender);

EXPLAIN SELECT * FROM orders WHERE user_id = 1001;

-- 使用SQL_NO_CACHE防止缓存干扰，真实反映性能变化SELECT SQL_NO_CACHE * FROM table WHERE ...;