join表的索引

2010-11-23 09:19:01 356 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL JOIN性能优化实战：驱动表选择、索引策略与执行计划深度解读 JOIN是关系型数据库最基础也最易误用的操作，其本质是多表间基于关联条件的数据匹配过程。理解INNER JOIN、LEFT JOIN等类型的执行原理，关键在于把握优化器如何根据数据分布选择驱动表、何时下推WHERE条件、以及索引如何影响嵌套循环或哈希连接的效率。技术价值体现在显著提升QPS、避免全表扫描与笛卡尔积爆炸；典型应用场景包括电商订单关联用户信息、金融对账补全维度、SaaS多租户数据隔离等。本文聚焦MySQL执行计划中的key_len、rows与Extra字段诊断，结合ON/WHERE语义差异、覆盖阅读详情

table:

CREATE TABLE `ord1` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `or_id` int(11) DEFAULT NULL,

  `name` varchar(10) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB

;

CREATE TABLE `ord2` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(10) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB

;

sql:

select ord1.id,ord1.name,ord2.id,ord2.name from ord1 inner join ord2 on ord1.or_id =ord2.id

如何为该表加索引.

我加了

create index idx_or_id on ord1(or_id)

但是explain的结果为:

mysql> explain select ord1.id,ord1.name,ord2.id,ord2.name from ord1 inner join o

rd2 on ord1.or_id =ord2.id\G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: ord1

         type: ALL

possible_keys: idx_or_id

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 3

        Extra:

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: ord2

         type: ALL

possible_keys: PRIMARY

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 5

        Extra: Using where; Using join buffer

2 rows in set (0.00 sec)



ERROR:

No query specified

求指教..

...全文

left join 被驱动表关联字段加索引了还是很慢问题分析早上跑线上sql的时候，发现跑得很慢，于是就去定位问题场景表 score_detail_temp（驱动表，5k的数据量）表 order_offline_info（被驱动表，1kw的数据量，被关联字段order_id已经加了索引）分析使用explain来解析sql EXPLAIN select * from crm.score_detail_temp as sd left join `order`.order_offline_info as oi on sd.order_no = 阅读详情

小小小小周

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

zuoxingyu 2010-11-24

打赏
举报

[Quote=引用 10 楼 mr_mablevi 的回复:]
还一点就是:
数据量少,mysql优化器认为全表扫描更快,但是为什么这条就走索引了,里面也就是3条记录
mysql> explain select * from ord1 where or_id =2\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ord1
type: ref
possible_keys: idx_or_id
key: idx_or_id key_len: 4
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)[/Quote]

你后面的这个例子，3条记录的，显示都根据索引来查找数据的，实际操作可能不是这样的。
要验证的话，可以先把自动提交关闭，然后select * from ord1 where or_id =2 FOR UPDATE。

如果是走索引的话，就只会锁定ID=2的这一条记录，那么你再去做UPDATE ORD1 SET NAME='22' WHERE OR_ID=1;这条命令应该能够执行成功，不需要等待FOR UPDATE语句的COMMIT。

如果不走索引，那么做UPDATE ORD1 SET NAME='22' WHERE OR_ID=1;就执行不成功，因为全表扫描的时候，锁定了全表记录。

zuoxingyu 2010-11-24

打赏
举报

在JOIN查询中，只需要在被驱动表（JOIN 后的表）添加索引就可以了。

所以也就有了“一般来说，用小结果集驱动大结果集”这样的说法了。

zuoxingyu 2010-11-24

打赏
举报

在MYSQL的JOIN里，使用的是嵌套循环的方法去实现的。基本的语法类似这样的

for i =1 to table1.numbers
for j=1 to table2.numbers
if table1(i).id=table2(i).id then
在这里组合结果集
end if
next
next

从你的查询语句看出，explain select ord1.id,ord1.name,ord2.id,ord2.name from ord1 inner join ord2 on ord1.or_id =ord2.id\G;套用上面的循环语句TABLE1=ORD1,TABLE2=ORD2。

也就是说先同ORD1中取出1条，然后到ORD2中去循环，去找条件符合的记录。然后ORD1移动到下一条，继续道ORD2中查找，直到ORD1全部取完。

这样可以得到一个结论：
ORD1是全表扫描的，不管是否有索引。ORD2是索引扫描的，如果有索引的话。

所以你得到EXPLAIN结果是正确的。

ACMAIN_CHM 2010-11-23

打赏
举报

估计需要两三千字能把这个问题讲清楚。

建议楼主还是自己先看一下书中的SQL语句优化章节吧。

《数据库系统概论（第四版）》王珊萨师煊高等教育出版社 (掌握基础知识和概念)

然后你可以再看MYSQL手册中的优化部分。讲得都很清楚了。

小小小小周 2010-11-23

打赏
举报

[Quote=引用 9 楼 acmain_chm 的回复:]
引用显示出来都是全部扫描(type为ALL),为什么不使用我加索引?

1. 如果一个班级中只有三个学生，让你找出其中的男同学。你是直接到教室中去一个一个看出来，还是先到花名册中找到男的学生的位置，再到座位上去把你喊起来？！

2. 一个高中班级中50个学生，让你把所有身高>100cm 的人找出来排队。你是先到事先按身高排序好的花名册上把身高.100cm 的一个一个在名单上找到位置，然后……
[/Quote]
这个我清楚了,
那么要是数据量多的话select ord1.id,ord1.name,ord2.id,ord2.name from ord1 inner join ord2 on ord1.or_id =ord2.id
这个索引怎么加,
在ord1.or_id上加索引就可以吗?
还一点就是:
数据量少,mysql优化器认为全表扫描更快,但是为什么这条就走索引了,里面也就是3条记录
mysql> explain select * from ord1 where or_id =2\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ord1
type: ref
possible_keys: idx_or_id
key: idx_or_id key_len: 4
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)

ACMAIN_CHM 2010-11-23

打赏
举报

[Quote]显示出来都是全部扫描(type为ALL),为什么不使用我加索引?[/Quote]

1. 如果一个班级中只有三个学生，让你找出其中的男同学。你是直接到教室中去一个一个看出来，还是先到花名册中找到男的学生的位置，再到座位上去把你喊起来？！

2. 一个高中班级中50个学生，让你把所有身高>100cm 的人找出来排队。你是先到事先按身高排序好的花名册上把身高.100cm 的一个一个在名单上找到位置，然后喊出去，还是怎么做？

小小小小周 2010-11-23

打赏
举报

数据量少,mysql优化器认为全表扫描更快,但是为什么这条就走索引了,里面也就是3条记录
mysql> explain select * from ord1 where or_id =2\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ord1
type: ref
possible_keys: idx_or_id
key: idx_or_id key_len: 4
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)

小小小小周 2010-11-23

打赏
举报

我明白狼头大哥你的意思,那比如数据量多的话,该如何加索引,就像我加的这样:

create index idx_or_id on ord1(or_id)

小小小小周 2010-11-23

打赏
举报

 explain select ord1.id,ord1.name,ord2.id,ord2.name from ord1 inner join o

rd2 on ord1.or_id =ord2.id

显示出来都是全部扫描(type为ALL),为什么不使用我加索引?
要是我加错了,该如何加索引来避免全表扫描?

ACMAIN_CHM 2010-11-23

打赏
举报

你表中总共才三条记录，你说MYSQL吃饱了撑的需要去走索引吗？三条记录直接去记录中查了。

小小小小周 2010-11-23

打赏
举报

ord1:

mysql> show index from ord1 \G;

*************************** 1. row ***************************

       Table: ord1

  Non_unique: 0

    Key_name: PRIMARY

Seq_in_index: 1

 Column_name: id

   Collation: A

 Cardinality: 3

    Sub_part: NULL

      Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

*************************** 2. row ***************************

       Table: ord1

  Non_unique: 1

    Key_name: idx_or_id

Seq_in_index: 1

 Column_name: or_id

   Collation: A

 Cardinality: 3

    Sub_part: NULL

      Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

2 rows in set (0.00 sec)

ord2:



mysql> show index from ord2 \G;

*************************** 1. row ***************************

       Table: ord2

  Non_unique: 0

    Key_name: PRIMARY

Seq_in_index: 1

 Column_name: id

   Collation: A

 Cardinality: 2

    Sub_part: NULL

      Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

1 row in set (0.00 sec)

ACMAIN_CHM 2010-11-23

打赏
举报

[Quote]贴出你的
show index from tablename[/Quote]

小小小小周 2010-11-23

打赏
举报

感谢狼头大哥这么快回复.
索引目前都没有,2个表ID为主键,就在ord1的or_id字段上加了
create index idx_or_id on ord1(or_id)
这个索引.
--
或则认为,就当我给出2个表的结构,和sql,如何加索引呢?

ACMAIN_CHM 2010-11-23

打赏
举报

贴出你的
show index from tablename

join表的索引

相关推荐