从慢查询到毫秒响应:资深DBA教你玩转MySQL索引优化

一、从一次血泪教训说起

去年双十一,我们的电商平台遭遇了惊心动魄的数据库雪崩。凌晨促销开始时,订单查询接口响应时间突然从200ms飙升到15秒,监控大屏一片飘红。紧急排查发现,新上线的优惠券功能导致user_coupons表的查询量暴增,而这张日均增百万记录的表竟然没有合适的索引!

二、索引的本质与B+树探秘

很多人把索引简单理解为书的目录,但MySQL的B+树索引远比这精妙。想象一下图书馆的智能书架:每个书架(非叶子节点)不仅存放书籍位置信息,还通过精心设计的平衡算法(B+树特性)保证无论藏书量多大,查找路径的深度始终保持稳定。这就是为什么十亿级数据查询仍能保持毫秒响应的奥秘。

三、索引设计的黄金法则

选择性原则:给性别字段加索引就像在沙滩上找珍珠——当字段distinct值少于全表30%时,优化器可能直接全表扫描
覆盖索引魔法:通过EXPLAIN查看Extra字段出现'Using index'时,说明查询所需字段都包含在索引中,无需回表
联合索引排列组合:把等值查询字段放前面,范围查询字段置后。遇到WHERE a=1 AND b>5 AND c=2时,(a,c,b)的索引组合效率最高

四、那些年我们踩过的索引坑

隐式类型转换:WHERE phone=13800138000(phone是varchar类型)会导致索引失效
函数操作陷阱:WHERE DATE(create_time)='2023-08-08'不如WHERE create_time BETWEEN '2023-08-08 00:00:00' AND '2023-08-08 23:59:59'
最左前缀失效:联合索引(a,b,c)下,查询条件只有b和c时就像没有地图的导航——完全迷失方向

五、高级调优实战

案例1:用户画像系统的标签查询优化

-- 原始慢查询
SELECT user_id FROM user_tags 
WHERE tag_id IN (101,205,307) 
GROUP BY user_id HAVING COUNT(DISTINCT tag_id)=3;

-- 优化方案:创建覆盖索引(tag_id, user_id)
-- 查询重写为:
SELECT user_id FROM (
  SELECT user_id, COUNT(DISTINCT tag_id) cnt 
  FROM user_tags 
  WHERE tag_id IN (101,205,307)
  GROUP BY user_id
) t WHERE cnt=3;

案例2:订单时间范围查询优化
通过引入虚拟列和函数索引处理CREATE_TIME的时分秒查询:

ALTER TABLE orders 
ADD COLUMN create_date DATE GENERATED ALWAYS AS (DATE(create_time)),
ADD INDEX idx_create_date_status(create_date, order_status);

六、索引监控与维护

定期运行以下SQL发现冗余索引:

SELECT * FROM sys.schema_redundant_indexes;

使用Percona Toolkit的pt-duplicate-key-checker识别重复索引。对于碎片化索引,采用在线重组:

ALTER TABLE tbl_name ALTER INDEX index_name INVISIBLE;
OPTIMIZE TABLE tbl_name;
ALTER TABLE tbl_name ALTER INDEX index_name VISIBLE;

七、新时代的索引选择

当遇到JSON字段查询时,别急着上FULLTEXT索引,MySQL 8.0的多值索引(Multi-Valued Indexes)可能更合适:

CREATE TABLE users (
  id INT PRIMARY KEY,
  hobbies JSON,
  INDEX idx_hobbies ((CAST(hobbies->'$[*]' AS CHAR(32) ARRAY)))
);

对于时序数据,考虑用R-TREE索引优化地理位置查询。而当QPS超过5万时,可能需要引入内存索引或搜索引擎方案。

结语:索引优化不是银弹,需要结合业务场景持续迭代。记住最好的索引,往往是能最大限度减少磁盘I/O的那个设计。