1. 索引优化实战从原理到落地MySQL索引优化是数据库性能调优的核心战场。我处理过的90%慢查询案例最终都通过合理的索引设计得到解决。但很多开发者对索引的理解停留在加个索引就能快的层面这往往会导致更严重的性能问题。1.1 B树索引的底层运作机制理解索引优化必须从B树开始。与教科书上的抽象图示不同实际工作中的B树有这样几个关键特征非叶子节点只存储键值和指针不存储数据记录。这意味着一次磁盘I/O可以加载更多索引条目叶子节点通过双向链表连接这对范围查询至关重要。我曾通过EXPLAIN观察到当使用WHERE id BETWEEN 100 AND 200时MySQL只需定位到id100的叶子节点然后沿着链表扫描即可默认情况下InnoDB的索引键最大长度是767字节utf8mb4字符集下约191个字符。超出时需要使用前缀索引重要提示在utf8mb4字符集下VARCHAR(255)字段建索引会失败因为255*41020字节超过限制。这是新手常踩的坑。1.2 最左前缀原则的实战应用某电商平台商品表有联合索引(category_id, price, sales)。以下SQL能否命中索引SELECT * FROM products WHERE price 100 ORDER BY sales DESC;答案是否定的。这就像电话簿按姓氏-名字排序时无法快速查找所有叫Michael的人。必须使用索引的最左列-- 有效用法 SELECT * FROM products WHERE category_id5 AND price100 ORDER BY sales DESC; -- 另一种有效用法 SELECT * FROM products WHERE category_id5 ORDER BY price, sales; -- 排序字段符合索引顺序1.3 索引选择性量化你的优化决策索引选择性 不重复索引值数量 / 表记录总数。经验值高于0.2优秀候选0.1-0.2考虑使用低于0.1通常不值得计算示例SELECT COUNT(DISTINCT gender)/COUNT(*) AS gender_selectivity, COUNT(DISTINCT city)/COUNT(*) AS city_selectivity FROM users;对于性别这种低选择性字段加索引往往适得其反。我曾见过在gender字段建索引导致写入性能下降30%的案例。2. 排序分组深度调优超越ORDER BY当执行计划出现Using filesort时就意味着MySQL不得不在内存或磁盘上进行额外排序。以下是几个关键优化策略2.1 利用索引消除排序最理想的排序优化是不排序。对于这个查询SELECT * FROM orders WHERE user_id100 ORDER BY create_time DESC;创建索引(user_id, create_time)后数据已经按需排列EXPLAIN中的Using filesort会消失。2.2 排序缓冲区调优当无法避免filesort时sort_buffer_size就至关重要。通过监控可以确定是否需要调整SHOW STATUS LIKE Sort_merge_passes; -- 若值持续增长需增大sort_buffer_size配置建议默认值4MB通常太小建议设置为2-4MB乘以并发连接数但不要超过总内存的5%我在处理一个报表系统时将sort_buffer_size从4MB调整到16MB排序操作耗时从1.2秒降至0.3秒。2.3 分组操作的隐藏成本GROUP BY的常见性能陷阱SELECT category_id, COUNT(*) FROM products GROUP BY category_id;如果category_id没有索引MySQL会创建临时表。更糟的是SELECT category_id, COUNT(*) FROM products WHERE price100 GROUP BY category_id;即使category_id有索引WHERE条件可能迫使全表扫描。解决方案是创建联合索引(price, category_id)。3. 执行计划深度解析看懂EXPLAIN的每一个字段3.1 type字段的实战含义执行计划中的type列揭示了访问方式按性能从优到劣system系统表单行查询const主键或唯一索引等值查询eq_ref关联查询中被驱动表的主键匹配ref非唯一索引等值查询range索引范围扫描index全索引扫描ALL全表扫描我曾将type从ALL优化到range的案例查询时间从1200ms降到15ms。3.2 Extra字段的关键信息Using index覆盖索引无需回表Using filesort需要额外排序Using temporary使用临时表Using where存储引擎返回数据后服务器层再过滤特别注意Using index condition这是ICP优化(Index Condition Pushdown)MySQL5.6可以将WHERE条件下推到存储引擎层。4. 高级索引策略应对复杂场景4.1 索引合并的利与弊当WHERE中有多个条件时MySQL可能使用索引合并SELECT * FROM users WHERE mobile13800138000 OR emailtestexample.com;如果有mobile和email的单列索引执行计划会显示Using union。但要注意只适合高选择性字段比联合索引效率低优化器可能判断错误更好的方案是创建函数索引ALTER TABLE users ADD INDEX idx_contact (mobile, email);4.2 函数索引的妙用MySQL8.0支持函数索引-- 为JSON字段创建索引 ALTER TABLE products ADD INDEX idx_specs ((CAST(specs-$.weight AS DECIMAL(10,2)))); -- 为日期部分创建索引 ALTER TABLE orders ADD INDEX idx_order_date ((DATE(create_time)));我曾用这种方法优化了一个JSON字段查询性能提升40倍。5. 实战问题排查手册5.1 索引失效的六大场景隐式类型转换WHERE mobile13800138000mobile是varchar使用函数WHERE DATE(create_time)2023-01-01前导通配符WHERE name LIKE %张使用OR条件除非所有列都有索引不符合最左前缀索引列参与计算WHERE price101005.2 慢查询日志分析技巧配置my.cnfslow_query_log1 slow_query_log_file/var/log/mysql/mysql-slow.log long_query_time1 log_queries_not_using_indexes1使用mysqldumpslow工具分析mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log5.3 性能优化检查清单所有查询都使用EXPLAIN验证过吗是否避免了全表扫描排序操作是否利用了索引联合索引的列顺序是否合理索引选择性是否足够高是否定期分析表ANALYZE TABLE更新统计信息6. 参数调优关键配置项解析6.1 InnoDB缓冲池优化# 建议设置为可用内存的70-80% innodb_buffer_pool_size12G # 缓冲池实例数建议每GB配1个实例 innodb_buffer_pool_instances12监控命中率SELECT (1-(SELECT variable_value FROM performance_schema.global_status WHERE variable_nameInnodb_buffer_pool_reads)/ (SELECT variable_value FROM performance_schema.global_status WHERE variable_nameInnodb_buffer_pool_read_requests))*100 AS hit_ratio;6.2 连接相关参数# 最大连接数根据应用需求调整 max_connections200 # 连接超时秒 wait_timeout300 # 交互式连接超时 interactive_timeout60检查连接使用情况SHOW STATUS LIKE Threads_%;7. 真实案例电商系统优化实录某电商平台商品搜索接口响应慢平均800ms优化过程原SQLSELECT * FROM products WHERE category_id5 AND status1 ORDER BY sales DESC LIMIT 20;问题诊断虽然有(category_id,status)索引但排序字段不在索引中每次查询需要排序约10万条记录解决方案ALTER TABLE products ADD INDEX idx_cat_status_sales (category_id, status, sales);优化结果查询时间降至50msCPU使用率下降30%8. 未来优化方向MySQL8.0新特性降序索引CREATE INDEX idx_desc ON t1 (a DESC, b ASC)隐藏索引ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE函数索引如前文所述直方图统计优化器能获得更准确的数据分布信息-- 创建直方图 ANALYZE TABLE products UPDATE HISTOGRAM ON price WITH 100 BUCKETS;这些新特性在特定场景下能带来显著性能提升。比如降序索引可以使ORDER BY id DESC避免filesort操作。