MySQL 5.0环境sf_collections大表自连接查询性能优化方案咨询

你的查询逻辑本身没有错，能覆盖两种交叉匹配的需求，但4秒的响应速度是由索引匹配效率低、冗余计算拉满两个问题导致的，完全没有跑满MySQL的性能：

• 现有索引对查询的覆盖度极差：单独建的memberid、cardid、state单列索引，在MySQL5.0的优化器能力下，根本没法同时支撑“按用户筛数据集、按卡ID做连接、按状态筛匹配”的全流程，再加上连接条件里的OR分支写法，优化器很难触发高效的索引合并，最后很可能退化成先把两个用户的所有收藏记录从几百万行表里捞出来，再在内存里做全量嵌套循环连接，IO和计算开销都很高。
• 冗余的去重逻辑拖慢速度：你用GROUP BY rac.memberid去重，本质上只要存在任意一条匹配记录就需要返回1，完全没必要拉取所有匹配行再做分组聚合；而且这个分组本身没有实际作用——WHERE条件已经固定了rac.memberid=1，分组后永远只有单条结果，纯粹是多做了一次无意义的聚合操作，MyISAM引擎下全量拉匹配行的磁盘IO是查询慢的主要占比。

1. 替换为全匹配覆盖的联合索引 #

这是提速最核心的一步，MySQL5.0对联合索引的最左前缀规则支持是稳定的，你可以删掉冗余的单列索引，建一个完全贴合查询路径的联合索引：

ALTER TABLE sf_collections 
DROP KEY memberid,
DROP KEY cardid,
DROP KEY state,
ADD INDEX idx_mem_state_card (memberid, state, cardid);

这个索引的匹配逻辑完全贴合查询的执行路径：

• 最左列memberid直接把全表几百万行的扫描范围，缩到单个用户的几千/几万条记录规模
• 第二列state直接在索引里筛出D/M状态的子集，不用回表
• 第三列cardid直接提供连接匹配需要的卡ID值，整个查询全程不需要回表查数据行，纯索引扫描就能完成

注意：原有的联合唯一键sf_collections_pkey (memberid,collectionid,cardid,state)因为最左前缀顺序和查询需求不匹配，第三列才是cardid，连接效率远低于上面新建的索引，而且collectionid字段和当前查询逻辑无关，留着只会增加索引的存储、写入开销。

2. 改写查询去掉冗余计算 #

把原来需要拉全量匹配行再分组的写法，改成半连接匹配，只要找到第一条符合要求的匹配记录就立刻终止扫描，比原写法快一个数量级：

SELECT EXISTS(
    SELECT 1
    FROM sf_collections rac
    INNER JOIN sf_collections myrac 
        ON rac.cardid = myrac.cardid
        AND rac.state <> myrac.state
    WHERE rac.memberid = 1 
      AND myrac.memberid = 2
    LIMIT 1
) AS cross_match_exists;

这里用rac.state <> myrac.state替代原来的OR分支判断，因为state的取值只有D、M两种，逻辑和原写法完全等价，同时去掉了OR的分支判断开销，让优化器更容易走索引常量匹配。

3. MyISAM引擎的配套优化 #

因为你用的是MyISAM存储引擎，索引是存在独立的索引文件里的，调大key buffer的内存占比，把上面新建的联合索引全部加载到内存中，就能完全避免查询时的磁盘随机读：

-- 建议将key_buffer_size设置为服务器总内存的30%~40%，足够放下全表的索引
SET GLOBAL key_buffer_size = 1073741824; -- 示例为1G大小

按照上面的方案调整完之后，原本4秒的查询响应时间会降到10ms以内——因为两个用户的收藏记录规模本身很小，走联合索引的匹配过程全程在内存中完成，根本不会触发大表扫描。

内容的提问来源于stack exchange，提问作者blies