1. 强制索引的实战抉择FORCE INDEX vs USE INDEX当MySQL优化器选择了一个不理想的执行计划时强制索引就像给数据库装上方向盘。但不同指令的操控力度有本质区别FORCE INDEX是绝对的命令式控制相当于对优化器说必须走这个索引哪怕全表扫描更快 我曾在商品库查询中强制使用时间索引结果查询时间从800ms飙升到12秒——这就是过度干预的代价。它的典型使用场景是优化器严重误判索引选择性统计信息过期导致代价估算错误特定业务场景下已知最优路径USE INDEX则是温和的建议像对优化器说优先考虑这些索引但如果你有更好方案也可以改。 在用户行为分析系统中我们用它提示优化器优先使用(user_id, event_time)组合索引同时保留使用其他索引的可能性。实际测试对比百万级数据表# 强制索引 SELECT * FROM order_history FORCE INDEX(idx_user) WHERE user_id100 AND create_time2023-01-01; # 建议索引 SELECT * FROM order_history USE INDEX(idx_user,idx_time) WHERE user_id100 AND create_time2023-01-01;执行计划显示FORCE INDEX使执行时间稳定在15-20ms但偶尔因数据分布变化导致性能骤降USE INDEX则在5-50ms间波动但不会出现极端情况。这印证了一个经验法则当优化器频繁犯错时用FORCE在不确定时用USE。2. 存储过程压测高效生成测试数据的艺术真实场景的压力测试需要符合业务特征的数据这个存储过程模板我用了5年DELIMITER // CREATE PROCEDURE generate_test_data(IN rows INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i rows DO INSERT INTO user_behavior VALUES( NULL, FLOOR(1 RAND() * 10000), -- 用户ID ELT(FLOOR(1 RAND() * 5), click, buy, search, login, view), -- 行为类型 ROUND(RAND() * 100, 2), -- 金额 DATE_ADD(2020-01-01, INTERVAL FLOOR(RAND() * 1460) DAY), -- 时间 CONCAT({ip:, FLOOR(RAND()*256), ., FLOOR(RAND()*256), ., FLOOR(RAND()*256), ., FLOOR(RAND()*256), }) -- JSON扩展字段 ); SET i i 1; -- 每1万条提交一次 IF i % 10000 0 THEN COMMIT; END IF; END WHILE; END // DELIMITER ;关键技巧使用ELT函数模拟枚举值分布通过DATE_ADD生成时间序列数据JSON字段保留扩展性分批提交避免大事务压测时发现一个坑RAND()函数会导致存储过程执行时间非线性增长。解决方案是在循环外先预生成随机数SET rnd RAND(); INSERT ... VALUES(rnd*100, rnd*50...);3. 慢SQL深度剖析从日志到执行计划开启慢查询日志的正确姿势SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 1秒阈值 SET GLOBAL log_queries_not_using_indexes ON; -- 捕获无索引查询分析日志时重点关注三类语句高频出现的相同模式比如分页查询LIMIT 10000,20执行时间波动大可能参数嗅探问题全表扫描操作特别是大表的ALL类型访问一个真实案例某电商平台的订单查询突然变慢EXPLAIN显示------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------- | 1 | SIMPLE | orders| ALL | idx_user | NULL| NULL | NULL| 120384 | Using where | -------------------------------------------------------------------------------------根本原因是发生了隐式类型转换-- user_id是varchar但传入数字 SELECT * FROM orders WHERE user_id 10086;优化方案不止一种-- 方案1强制类型一致 SELECT * FROM orders WHERE user_id 10086; -- 方案2函数索引(MySQL 8.0) CREATE INDEX idx_user_cast ON orders((CAST(user_id AS UNSIGNED)));4. 索引失效的七宗罪及救赎之道第一宗罪函数操作-- 错误索引失效 SELECT * FROM products WHERE DATE(create_time) 2023-01-01; -- 正确范围查询 SELECT * FROM products WHERE create_time BETWEEN 2023-01-01 00:00:00 AND 2023-01-01 23:59:59;第二宗罪隐式编码转换当联表查询的字符集不一致时-- utf8mb4与utf8表关联时 ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4;第三宗罪最左前缀缺失对于组合索引(a,b,c)WHERE b1 AND c2 -- 失效 WHERE a1 AND c2 -- 部分使用第四宗罪索引选择性差性别字段建索引不如用复合索引-- 低效 INDEX idx_gender (gender) -- 高效 INDEX idx_gender_age (gender, age)第五宗罪OR条件失控-- 危险写法 SELECT * FROM users WHERE age30 OR salary10000; -- 优化方案 SELECT * FROM users WHERE age30 UNION ALL SELECT * FROM users WHERE salary10000 AND age30;第六宗罪LIKE通配符滥用-- 无法使用索引 SELECT * FROM articles WHERE title LIKE %MySQL%; -- 全文索引方案 CREATE FULLTEXT INDEX idx_content ON articles(title); SELECT * FROM articles WHERE MATCH(title) AGAINST(MySQL);第七宗罪统计信息失真定期更新统计信息ANALYZE TABLE orders;在千万级用户系统中通过组合这些优化技巧我们把关键API的响应时间从2.3秒降到了67毫秒。记住索引不是越多越好就像在仓库里贴满指示牌反而会让工人困惑。一个好的经验是每张表的索引数不超过字段数的30%重点优化那些真正影响用户体验的热点查询。