1. 为什么你的SQL查询总是慢我见过太多开发者在数据库性能问题上栽跟头。最常见的场景是一个原本运行良好的系统随着数据量增长突然变得异常缓慢。上周就遇到一个案例某电商平台的商品搜索接口响应时间从200ms飙升到5秒直接导致用户流失率上升30%。问题的根源往往在于索引策略不当。数据库就像一本没有目录的百科全书当数据量小时从头翻到尾也能快速找到内容但当数据量达到百万级时这种全表扫描的方式就会成为性能杀手。关键事实根据MySQL官方基准测试在1000万行数据的表上合理使用索引可以将查询速度提升10-100倍不等。但错误的索引策略反而可能让性能下降50%。2. 索引优化的四大黄金法则2.1 法则一选择性原则索引的选择性是指索引列中不同值的数量与表中记录总数的比值。高选择性的列如用户ID、手机号是最佳索引候选而低选择性的列如性别、状态标志则不适合单独建索引。计算选择性的SQL示例SELECT COUNT(DISTINCT user_id)/COUNT(*) AS user_id_selectivity, COUNT(DISTINCT gender)/COUNT(*) AS gender_selectivity FROM users;在我的实践中通常建议选择性高于0.1的列才考虑单独建立索引。对于低选择性列可以采用复合索引策略。2.2 法则二最左前缀原则复合索引的查询必须从最左列开始不能跳过中间列。比如索引是(A,B,C)以下查询能利用索引WHERE A1 AND B2 AND C3WHERE A1 AND B2WHERE A1但以下查询无法充分利用索引WHERE B2跳过了AWHERE A1 AND C3跳过了B2.3 法则三覆盖索引原则当查询的所有列都包含在索引中时数据库可以直接从索引获取数据而无需回表这称为覆盖索引。例如-- 假设有索引 (user_id, name) SELECT user_id, name FROM users WHERE user_id 100;在我的一个优化案例中通过改造20%的查询为覆盖索引查询整体系统吞吐量提升了35%。2.4 法则四避免索引失效陷阱常见导致索引失效的操作包括在索引列上使用函数WHERE YEAR(create_time) 2023类型转换WHERE user_id 100user_id是整型使用!或NOT IN模糊查询以通配符开头WHERE name LIKE %张3. 实战电商系统索引优化全流程3.1 案例背景某电商平台的订单表有1500万行数据主要慢查询包括按用户ID查询历史订单按时间段订单状态筛选按商品ID订单状态统计销量原始表结构CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT, product_id BIGINT, status TINYINT COMMENT 0-待支付 1-已支付 2-已发货 3-已完成, amount DECIMAL(10,2), create_time DATETIME, update_time DATETIME );3.2 优化方案设计经过分析我们设计了以下索引策略基础索引ALTER TABLE orders ADD INDEX idx_user (user_id); ALTER TABLE orders ADD INDEX idx_product (product_id);复合索引ALTER TABLE orders ADD INDEX idx_status_time (status, create_time); ALTER TABLE orders ADD INDEX idx_product_status (product_id, status);覆盖索引优化ALTER TABLE orders ADD INDEX idx_user_cover (user_id, status, create_time);3.3 优化效果对比查询类型优化前耗时优化后耗时提升倍数用户订单查询1200ms85ms14x状态时间查询2500ms110ms22x商品销量统计1800ms65ms27x4. 高级优化技巧与避坑指南4.1 索引合并优化MySQL5.0支持Index Merge优化可以同时使用多个索引。例如SELECT * FROM orders WHERE user_id 100 OR product_id 200;但实践中发现这种查询往往性能不稳定。更好的做法是使用UNIONSELECT * FROM orders WHERE user_id 100 UNION SELECT * FROM orders WHERE product_id 200;4.2 前缀索引技巧对于长文本字段可以使用前缀索引节省空间ALTER TABLE products ADD INDEX idx_name (name(20));但要注意前缀长度的选择应保证足够的选择性。我通常的做法是SELECT COUNT(DISTINCT LEFT(name, 10))/COUNT(*) AS sel10, COUNT(DISTINCT LEFT(name, 20))/COUNT(*) AS sel20, COUNT(DISTINCT LEFT(name, 30))/COUNT(*) AS sel30 FROM products;选择选择性接近完整列值的最小长度。4.3 隐式排序陷阱当使用DESC排序时MySQL8.0支持降序索引ALTER TABLE orders ADD INDEX idx_time_desc (create_time DESC);但在早期版本中这种查询会导致filesortSELECT * FROM orders ORDER BY create_time DESC LIMIT 100;解决方案是使用延迟关联SELECT * FROM orders o JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 100) t ON o.id t.id;5. 监控与持续优化5.1 慢查询日志分析配置my.cnf开启慢查询日志slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1使用mysqldumpslow工具分析mysqldumpslow -s t /var/log/mysql/mysql-slow.log5.2 执行计划解读使用EXPLAIN分析查询EXPLAIN SELECT * FROM orders WHERE user_id 100;关键指标解读type最好达到ref或range避免ALL全表扫描key实际使用的索引rows预估扫描行数Extra注意Using filesort和Using temporary5.3 索引使用统计查询information_schema获取索引使用情况SELECT index_name, COUNT_READ, COUNT_FETCH FROM information_schema.INDEX_STATISTICS WHERE table_schema your_db;定期清理未使用的索引可以提升写入性能。在我的维护经验中大约15-20%的索引是几乎从不使用的。6. 不同数据库的索引特性6.1 MySQL的索引特性InnoDB聚簇索引主键索引包含完整数据二级索引需要回表自适应哈希索引自动为频繁访问的索引页建立哈希索引不可见索引MySQL8.0支持标记索引为不可见测试删除索引的影响6.2 SQL Server的索引优化包含列索引CREATE INDEX idx_orders ON orders(user_id) INCLUDE (status, create_time);筛选索引CREATE INDEX idx_active_users ON users(email) WHERE is_active 1;6.3 PostgreSQL的索引特色部分索引CREATE INDEX idx_orders_active ON orders(user_id) WHERE status 1;表达式索引CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM create_time));7. 真实案例从20秒到0.2秒的蜕变去年优化的一个物流系统中有个报表查询需要关联8张表原始执行时间超过20秒。通过以下步骤实现优化使用EXPLAIN ANALYZE定位瓶颈点为所有关联字段添加复合索引重写查询使用CTE替代子查询对统计查询使用物化视图调整join_buffer_size等参数最终将查询时间降至0.2秒。关键点在于发现了一个被忽略的跨表关联条件为其添加复合索引后性能立即提升8倍。