mysql表关联中的索引使用情况

本文探讨了MySQL在不同连接类型下的自动优化行为,特别是在inner join和left/right outer join场景中的表现。通过对具体案例的分析,揭示了MySQL如何智能选择最优查询路径。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

874人阅读 评论(0) 收藏 举报
本文章已收录于:
分类:

MySQL在某些情况下可以自动优化表关联的关联条件,如:通过分析条件中的字段是否有索引,关联表中的数据总数之类,通过这些条件mysql便可以实现一些自动优化sql语句的功能。

以下几种情况基本是靠一些简单实验进行验证,没有得到确切的官方文档证实

一、inner join情况下mysql自动优化

表结构细节不做介绍,简单介绍下

bus_creative表中的owner字段和sys_user表中的id字段在业务上是外键关联,n-->1,但是owner字段没有建立索引,业务上一般bus_creative数据量大于sys_user,这下就好玩了,先看下通过EXPLAIN执行后的结果

EXPLAIN select b.id from dshp.bus_creative a inner join dshp.sys_user b on a.`owner`=b.id;


下面是交换完关联条件位置后的结果

EXPLAIN select b.id from dshp.sys_user b join dshp.bus_creative a on b.id=a.`owner`;


这里多说一下EXPLAIN的id选项

id 列
建表:
create table a(a_id int);
create table b(b_id int);
create table c(c_id int);
mysql> explain select * from a join b on a_id=b_id where b_id in (select c_id from c);
+----+--------------------+-------+...
| id | select_type        | table |...
+----+--------------------+-------+...
|  1 | PRIMARY            | a     |...
|  1 | PRIMARY            | b     |...
|  2 | DEPENDENT SUBQUERY | c     |...
+----+--------------------+-------+...

从 3 个表中查询,对应输出 3 行,每行对应一个表, id 列表示执行顺序,id 越大,越先执行,id 相同,由上至下执行。此处的执行顺序为(以 table 列表示):c -> a -> b

从上面两个图中可以看到都是先执行的a表,再执行b表,这是为什么呢?啪啪的你知道吗?

来让哥给你解释下吧,此时mysql会先分析关联条件中的两个字段owner和id,mysql掐指一算id是主键,owner是普通字段没有索引,这就好办了,肯定是以有主键或者索引的表数据为查询基准数据,然后遍历没有索引字段的表数据,这样的结果就是先遍历a表,然后以a表的数据在b表中进行查询(因为b中的id是主键索引,查的快啊,如果顺序反过来你想想会是什么结果,肯定用不到索引,这样查询速度肯定慢!),而且图2证明交换关联条件的位置结果也是一样的,更加印证了上面的逻辑。

乖乖了,原来mysql这么智能啊!

废个话,要不你个屌丝都能搞个数据库系统了,还要人家mysql作甚。敲打。。。这只是毛毛雨啦

接下来看个更屌的

哥哥假想如果把关联条件中owner也做成索引,此时mysql他老人家该怎么玩呢,

走起来,哥先建个索引,索引名称是index_owner,这时我们不妨大胆猜测一下结果,

两个关键点,

1.a表中数据量10万,b表中数据量2千吧

2.a中owner是索引,b中id是主键

此时用你那大腿猜猜都该猜到,肯定是先执行数据量少而且关联字段中对方表字段又是索引的那个啊委屈


然后再看看实验结果吧,上图

EXPLAIN select b.id from dshp.bus_creative a join dshp.sys_user b on a.`owner`=b.id;



看看吧,mysql他老人家也是这么想的生气,而且该用到的索引也都用到了



二、left/right outer join情况下指定顺序执行

基于上面的结果,如果指定了left/right ouert的话,不用想,此时肯定得以left/right指定的那个表为主表先查询,什么?要问我为什么,呵呵,天机不可泄露!我去,你丫滚犊子吧。。。。

因为如果指定了outer后不管怎么优化指定的表数据肯定是全部显示,所以肯定先执行了。

有图有真相就在这里晒:

EXPLAIN select b.id from dshp.bus_creative a left join dshp.sys_user b on a.`owner`=b.id;


换个位置再来一次偷笑

EXPLAIN select b.id from dshp.bus_creative a right join dshp.sys_user b on a.`owner`=b.id;




0
0
 
 
我的同类文章
### MySQL连接查询优化与索引使用 在处理涉及多个的复杂查询时,MySQL 的查询优化器会尝试找到最优执行计划来提高效率。当涉及到三个或更多格之间的连接操作时,合理利用索引可以显著提升查询性能。 #### 辅助索引的选择依据 对于 `COUNT(id)` 这样的聚合函数,在某些情况下 MySQL 可能会选择辅助索引来代替主键聚簇索引进行计算。这是因为二级索引通常只包含指向实际记录位置的信息以及被索引列的数据,因此其大小往往小于完整的行数据集,从而使得扫描所需时间减少[^1]。 #### Hash Join的应用场景 自 MySQL 8.0 版本起引入了哈希连接(Hash Join),它可以在特定条件下替代传统的嵌套循环连接(Nested Loop Join)。特别是当两个之间存在大量重复值或者其中一个非常小时,采用哈希连接能够带来更好的性能现[^2]。 #### 多JOIN的最佳实践建议 为了使 MySQL 更有效地完成多 JOIN 操作: - **关联字段加索引**:确保参与 join 条件中的每一侧都已创建适当类型的索引; - **选择合适的驱动**:尽可能让较小的一方作为外层循环的基础,即所谓的“小驱动大”。这可以通过 STRAIGHT_JOIN 提示强制指定顺序,避免优化器错误估计而选择了次优方案[^3]; - **考虑覆盖索引**:如果可能的话,构建复合索引以满足整个 SELECT 列的需求,这样即使不访问原始也能获取到全部必要信息; 下面是一个简单的例子展示如何通过添加索引来改善三联接查询的速度: 假设有一个订单系统包含客户 (`customers`)、产品(`products`) 和订单详情(`order_items`) 三个关系型数据库。要找出某个时间段内购买某类商品最多的前五位顾客名单及其消费金额总和。 ```sql SELECT c.customer_name, SUM(oi.quantity * oi.unit_price) AS total_spent FROM customers c INNER JOIN orders o ON c.id = o.customer_id -- 客户ID上的索引加速查找 INNER JOIN order_items oi ON o.order_id = oi.order_id -- 订单ID上的索引同样重要 WHERE DATE(o.purchase_date) BETWEEN 'start-date' AND 'end-date' AND p.product_category IN ('category-name') GROUP BY c.customer_name ORDER BY total_spent DESC LIMIT 5; ``` 在这个 SQL 中,`customer_id`, `order_id` 都应当建立相应的单列索引,而对于过滤条件中使用的日期范围和类别名称,则可以根据实际情况决定是否需要额外增加组合索引或是全文搜索引擎的支持。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值