join表的索引

[小小小小周]

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

求指教..

[ACMAIN_CHM]

贴出你的
show index from tablename

[小小小小周]

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

[ACMAIN_CHM]

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

[小小小小周]

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]

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

[小小小小周]

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

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

[小小小小周]

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

create index idx_or_id on 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]

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

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

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

[小小小小周]

[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]

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

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

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

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

[zuoxingyu]

在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结果是正确的。

[zuoxingyu]

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

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

[zuoxingyu]

[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;就执行不成功，因为全表扫描的时候，锁定了全表记录。