MySQL为何使用物化视图省去JOIN的查询反而比带JOIN的原查询更慢

1. 联合索引顺序不符合最左匹配原则
   即使你已经创建了三个字段的联合索引，但如果索引字段没有将等值查询条件放在最前，索引就无法被完全利用。当前查询的WHERE条件里，studyId和attrId都是固定值的等值查询，sampleId是IN范围查询，正确的联合索引顺序应该是(studyId, attrId, sampleId)。如果你的联合索引把sampleId放在了前面，那么等值条件无法触发最左匹配，索引只能用于过滤sampleId，剩下的studyId、attrId条件需要逐行判断，自然会扫描大量无关行。当IN子句的ID数量很大时，需要过滤的行数会进一步上升，性能问题会更明显。
2. 多表JOIN的层层过滤效率更高
   原始带JOIN的查询执行时，数据库会先从最小的过滤结果开始关联：首先从cancer_study表过滤出唯一匹配studyId='xxxxx'的行，再关联patient表筛选出该研究下的所有患者，再关联sample表筛选出IN列表里的样本，最后关联clinical_sample表过滤attrId，全程每一步都在缩小数据范围，最终需要扫描的行数自然远少于未正确利用索引的单表查询。
3. 优化器选错索引或统计信息过时
   即使你建了正确顺序的联合索引，也可能因为test表的统计信息过时，导致优化器错误判断单列索引（比如sampleId的单列索引）的过滤效率更高，最终选择走单列索引，后续还要逐行校验另外两个条件，也会导致扫描行数暴增。

• 重建联合索引，调整字段顺序为(studyId, attrId, sampleId)，如果要进一步优化可以把查询需要返回的所有字段都加入索引做成覆盖索引：(studyId, attrId, sampleId, internalId, patientId, attrValue)，这样查询全程走索引不需要回表。
• 用EXPLAIN命令分析第二条查询的执行计划，确认是否命中了正确的联合索引，同时检查字段类型是否匹配，比如sampleId字段在test表中的类型是否和IN子句里的字符串类型一致，避免隐式转换导致索引失效。
• 更新test表的统计信息，让优化器可以正确判断索引效率，以MySQL为例执行ANALYZE TABLE test;即可。

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