MySQL关联查询优化：避免笛卡尔积，提升查询效率
前言多表关联查询是性能重灾区新手写关联查询最容易出现两个问题产生笛卡尔积、索引失效导致全表扫描小数据量无感知生产大数据量直接接口超时、数据库卡死。本篇讲解关联查询核心优化方案从原理到实战彻底解决关联查询慢、数据冗余问题。一、什么是笛卡尔积最大坑点产生原因多表关联时遗漏关联条件 ON、关联条件错误、多表无有效关联关系。危害A表1000条、B表1000条笛卡尔积结果为 100万条数据数据量呈指数级暴涨直接拖垮数据库。错误案例笛卡尔积-- 无ON关联条件产生笛卡尔积 SELECT * FROM user u,order o;二、杜绝笛卡尔积的核心规范1、所有 JOIN 关联查询必须写完整 ON 关联条件禁止省略2、多表关联必须保证「主外键对应」user.id order.user_id关联字段精准匹配3、禁止三张及以上无关表强行关联业务无关表拆分查询。三、关联查询核心优化方案1、关联字段必须建索引多表关联的 ON 字段外键字段必须建立索引如 order.user_id避免关联时全表扫描。2、小表驱动大表核心优化原则MySQL 关联查询底层是嵌套循环用数据量小的表驱动数据量大的表循环次数更少性能更高。LEFT JOIN 左表放小表右表放大表INNER JOIN 自动优化无需手动调整。3、禁止 SELECT *只查需要字段关联查询本身数据量大SELECT * 会查询所有冗余字段增加IO开销、内存开销只查询业务所需字段。4、筛选条件前置减少关联数据量先通过 WHERE 筛选过滤数据再进行关联避免大量无效数据参与关联计算。LEFT JOIN 右表筛选条件放在 ON 后避免破坏左表数据完整性。四、优化前后对比案例未优化低效、危险SELECT * FROM user u LEFT JOIN order o ON u.id o.user_id WHERE o.order_name LIKE %张三%;优化后高效、规范SELECT u.name, o.order_name FROM user u LEFT JOIN order o ON u.id o.user_id AND o.order_name LIKE 张三%;五、总结关联查询优化口诀必写ON防笛卡尔、关联字段建索引、小表驱动大表、字段按需查询、筛选条件前置
