一、MySQL 多表查询详解数据库遵循三大范式会把数据拆分到多张表如用户表、订单表、商品表单张表无法满足复杂查询因此需要多表查询。多表查询主要分为三大类JOIN 连接查询、子查询、UNION 联合查询。先从基础的笛卡尔积讲起所有联表的底层。1. 前置知识笛卡尔积定义多张表不加筛选条件直接关联会得到两表行数乘积的结果集。例user表 100 行orders表 200 行笛卡尔积结果 100×20020000 行。产生方式sql-- 隐式联表无 where 条件 → 笛卡尔积 SELECT * FROM user, orders;危害数据量爆炸、查询效率极低、结果无业务意义。所有多表联表必须添加关联条件避免笛卡尔积。2. JOIN 连接查询最主流左右拼接列JOIN 是将多张表按关联字段横向拼接列合并分为交叉连接、内连接、外连接。2.1 交叉连接CROSS JOIN就是纯笛卡尔积几乎不会在业务中使用。sql-- 写法1 SELECT * FROM user CROSS JOIN orders; -- 写法2等价 SELECT * FROM user, orders;优点语法简单仅用于测试 / 特殊批量生成数据。缺点结果集爆炸性能极差生产环境禁止使用。2.2 内连接INNER JOIN日常最常用核心逻辑取两张表的交集只返回关联字段匹配成功的数据匹配失败的数据直接过滤。语法sql-- 显式内连接推荐可读性高 SELECT u.*, o.* FROM user u INNER JOIN orders o ON u.uid o.uid; -- 关联条件 -- 隐式内连接老代码常用逗号分隔表 where 做关联 SELECT u.*, o.* FROM user u, orders o WHERE u.uid o.uid;业务示例查询有下单记录的用户 对应订单没有订单的用户、没有对应用户的脏订单都不会出现在结果中。优缺点✅ 优点结果精准符合 “匹配关系” 的业务场景执行效率高MySQL 优化器对内连接优化最好支持等值、非等值、多条件关联。❌ 缺点会丢失不匹配的数据无法统计 “无关联数据”。2.3 外连接OUTER JOIN以某一张表为基准保留基准表所有数据另一张表匹配不上的字段填充NULL。 MySQL 只支持左外连接、右外连接不支持全外连接全连接需手动模拟。1左外连接LEFT JOIN使用频率最高基准表左侧表左表所有行全部保留右表匹配不到数据时右表字段补NULL。sql-- 查询 所有用户包括没下单的用户 对应的订单 SELECT u.username, o.oid, o.money FROM user u LEFT JOIN orders o ON u.uid o.uid;2右外连接RIGHT JOIN基准表右侧表右表所有行全部保留左表匹配不到数据时左表字段补NULL。sql-- 查询 所有订单包括无对应用户的脏订单 对应用户 SELECT u.username, o.oid, o.money FROM user u RIGHT JOIN orders o ON u.uid o.uid;实战技巧右连接完全可以改写为左连接开发中统一用LEFT JOIN即可减少理解成本。3全外连接MySQL 模拟方案需求保留左右两张表所有数据并集。 MySQL 无原生FULL JOIN用LEFT JOIN UNION RIGHT JOIN模拟sql-- 左连接结果 右连接中排除已匹配的数据去重 (SELECT * FROM user u LEFT JOIN orders o ON u.uido.uid) UNION (SELECT * FROM user u RIGHT JOIN orders o ON u.uido.uid WHERE u.uid IS NULL);外连接优缺点✅ 优点保留基准表全量数据适合统计、报表、全量遍历场景❌ 缺点执行效率略低于内连接结果存在大量NULL业务层需要判空处理。各类 JOIN 场景总结表格连接类型结果范围适用场景内连接 INNER JOIN两表交集查询存在关联关系的数据用户 有效订单左连接 LEFT JOIN左表全量 右表匹配数据统计全量主数据所有用户含无订单用户右连接 RIGHT JOIN右表全量 左表匹配数据极少使用可改写为左连接交叉连接 CROSS JOIN笛卡尔积仅测试生产禁用3. 子查询嵌套查询将一条SELECT查询结果作为另一条 SQL 的条件 / 表分为非相关子查询、相关子查询。3.1 分类 示例1非相关子查询子查询独立执行和外层无关子查询先执行一次结果对外层提供数据sql-- 查询 下单用户的信息子查询查所有下单用户ID SELECT * FROM user WHERE uid IN (SELECT DISTINCT uid FROM orders);2相关子查询子查询依赖外层表逐行执行子查询会跟着外层循环外层每一行子查询执行一次sql-- 相关子查询查询有订单的用户 SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.uid u.uid);优缺点✅ 优点逻辑分层清晰代码可读性强适合复杂分步查询简化多表关联逻辑。❌ 缺点相关子查询效率极低逐行循环执行大数据量下严重拖慢性能MySQL 对子查询的优化能力较弱复杂子查询易产生临时表、文件排序。实战建议能用 JOIN 就不用相关子查询。4. 联合查询UNION / UNION ALL上下拼接行作用把多条查询的结果集纵向合并行合并要求多条查询的字段数量、字段类型一致。4.1 核心区别UNION合并后自动去重 排序UNION ALL直接拼接不去重、不排序性能远高于UNION。示例sql-- 合并两个查询结果不去重推荐 SELECT uid, username FROM user WHERE age 20 UNION ALL SELECT uid, username FROM user WHERE age 18; -- UNION 会自动去重速度慢 SELECT uid, username FROM user WHERE age 20 UNION SELECT uid, username FROM user WHERE age 18;优缺点 场景✅ 优点适合合并结构相同的多张表 / 分区数据如分表、历史表 实时表❌ 缺点UNION去重排序开销大尽量用UNION ALL结果集过大时内存消耗高。5. 多表查询整体方案对比表格查询方式拼接形式核心特点性能适用场景INNER JOIN横向列拼接取交集丢失不匹配数据最高常规关联查询、精准匹配LEFT JOIN横向列拼接保留左表全量数据较高报表、全量统计、主表遍历子查询嵌套分层逻辑清晰相关子查询循环执行低简单分步查询小数据量UNION ALL纵向行拼接直接合并不去重较高多表合并、分区数据查询UNION纵向行拼接合并 去重排序最低需要自动去重的场景二、MySQL 查询调优全套方案调优核心思路先定位慢 SQL → 分析执行计划 → 从索引、SQL 写法、表结构、架构四层优化。前置流程开启慢查询日志捕获执行超时的 SQL使用EXPLAIN分析 SQL 执行计划定位瓶颈优先优化索引90% 慢查询都是索引问题再优化 SQL、表结构。1. 索引优化调优核心重中之重索引相当于 “书籍目录”避免全表扫描是提升查询速度最有效的手段。1.1 索引分类 使用场景主键索引PRIMARY KEY唯一、非空查询速度最快每张表必须有唯一索引UNIQUE字段值唯一用于手机号、身份证等普通索引INDEX常规查询字段最常用联合索引复合索引多个字段组合索引优化多条件查询覆盖索引索引中包含查询所需全部字段无需回表查询数据行性能天花板。1.2 联合索引最左匹配原则联合索引(a, b, c)查询条件必须从最左侧字段开始匹配索引才会生效生效where a?、where a? and b?、where a? and b? and c?失效where b?、where c?、where b? and c?1.3 索引失效十大高频场景避坑出现以下情况索引会失效触发全表扫描LIKE %关键词左模糊 / 全模糊查询LIKE 关键词%索引有效使用or连接无索引字段字段使用函数 / 运算WHERE DATE(create_time) 2025-01-01隐式类型转换索引字段是varchar查询值传数字WHERE phone1380000使用!、、NOT INORDER BY字段无索引产生文件排序GROUP BY字段无索引产生临时表IS NULL / IS NOT NULL索引对空值支持差联合索引违反最左匹配数据量差异过大如字段 90% 数据都是同一个值索引失效。1.4 索引创建原则优先为WHERE、JOIN、ORDER BY、GROUP BY字段建索引低基数字段如性别、状态不要建索引索引无意义频繁增删改的字段少建索引索引会拖慢写入速度单表索引数量不宜过多建议≤5 个大文本TEXT/BLOB禁止建普通索引可用前缀索引。1.5 覆盖索引优化查询字段全部包含在索引中避免 “回表”sql-- 给 (uid, username) 建联合索引这条 SQL 就是覆盖索引 SELECT uid, username FROM user WHERE uid 1001;2. SQL 语句写法优化低成本、见效快2.1 禁止SELECT *只查询业务需要的字段减少网络 IO、磁盘 IO更容易触发覆盖索引。2.2 分页优化Limit 大偏移量LIMIT 100000, 10会先扫描 10 万行再丢弃效率极低。优化方案主键偏移利用主键索引快速定位sql-- 原SQL慢 SELECT * FROM user LIMIT 100000, 10; -- 优化后快 SELECT * FROM user WHERE id 100000 LIMIT 10;2.3 联表查询优化小表驱动大表MySQL 循环机制用数据量小的表作为驱动表联表字段必须建立索引减少联表数量禁止三张以上表无意义联表。2.4 子查询优化非相关子查询可保留相关子查询一律改写为 JOIN彻底解决循环执行问题。2.5 WHERE 条件优化过滤力度最强的条件放前面提前缩小结果集禁止在索引列上做函数、计算慎用or多条件or可拆分为多条UNION ALL。2.6 DISTINCT / GROUP BY 优化DISTINCT有去重开销业务允许则去掉GROUP BY字段建立索引避免Using temporary临时表。3. 表结构设计优化事前优化从根源解决3.1 数据类型选型越小越好能用INT不用BIGINT能用TINYINT不用INT固定长度字符串用CHAR变长用VARCHAR日期优先用DATE/DATETIME不要用字符串存时间大字段TEXT/BLOB单独拆分到副表避免查询加载大字段。3.2 范式与反范式三范式减少数据冗余但联表变多高并发场景适当反范式增加冗余字段减少 JOIN 查询空间换时间。3.3 主键设计推荐自增主键 / 雪花 ID有序索引碎片少禁止UUID作为主键无序插入造成索引页分裂性能暴跌。3.4 大表拆分单表数据量超过1000 万建议分表水平分表按时间、ID、地区拆分如按月分订单表垂直分表将大字段、低频字段拆分到独立表。4. EXPLAIN 执行计划调优必备工具在 SQL 前加EXPLAIN查看 MySQL 执行逻辑重点关注以下字段sqlEXPLAIN SELECT * FROM user LEFT JOIN orders ON user.uidorders.uid;核心字段解读type优先级最高访问类型从优到劣const eq_ref ref range index ALLALL全表扫描必须优化range范围查询in、between、、正常const常量匹配最优。key实际使用的索引NULL表示未使用索引。rows预估扫描行数数值越小越好。Extra额外信息告警信号Using filesort文件排序ORDER BY 无索引需优化Using temporary使用临时表GROUP BY/DISTINCT 无索引需优化Using index覆盖索引最优状态。5. 架构 服务端优化高并发场景5.1 数据库配置优化my.cnf/my.ini针对 InnoDB 引擎MySQL 默认innodb_buffer_pool_size缓冲池物理内存的 50%~70%缓存数据和索引核心参数join_buffer_size联表查询缓冲区sort_buffer_size排序缓冲区tmp_table_size临时表大小避免磁盘临时表。5.2 读写分离主库负责增、删、改写操作从库负责查询读操作 分担单库压力适合读多写少业务。5.3 缓存优化应用层缓存Redis热点数据首页、配置、字典存入 Redis避免频繁查库MySQL 查询缓存MySQL 8.0 已彻底移除不再使用。5.4 其他细节避免大事务大事务锁表时间长、产生大量回滚日志尽量拆分定时整理索引碎片OPTIMIZE TABLE 表名禁止在数据库中做复杂计算、逻辑判断交给应用层处理。三、调优总结面试 / 实战速记优先优化索引解决 90% 慢查询牢记索引失效场景、最左匹配、覆盖索引规范 SQL 写法拒绝SELECT *、大偏移分页、滥用子查询合理设计表结构小数据类型、主键有序、大表拆分用 EXPLAIN 分析执行计划重点盯type、Extra字段高并发场景读写分离 Redis 缓存 分表分库架构层面兜底。