join表的索引

Question

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求指教..

Answer 1

贴出你的
show index from tablename

Answer 2

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

Answer 3

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)

Answer 4

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

Answer 5

 explain select ord1.id,ord1.name,ord2.id,ord2.name from ord1 inner join o
rd2 on ord1.or_id =ord2.id
显示出来都是全部扫描(type为ALL),为什么不使用我加索引?
要是我加错了,该如何加索引来避免全表扫描?

Answer 6

我明白狼头大哥你的意思,那比如数据量多的话,该如何加索引,就像我加的这样:
create index idx_or_id on ord1(or_id)

Answer 7

数据量少,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)

Answer 8

1. 如果一个班级中只有三个学生，让你找出其中的男同学。 你是直接到教室中去一个一个看出来，还是先到花名册中找到男的学生的位置，再到座位上去把你喊起来？！2. 一个高中班级中50个学生，让你把所有身高>100cm 的人找出来排队。你是先到事先按身高排序好的花名册上把身高.100cm 的一个一个在名单上找到位置，然后喊出去，还是怎么做？

Answer 9

这个我清楚了,
那么要是数据量多的话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)

Answer 10

估计需要两三千字能把这个问题讲清楚。建议楼主还是自己先看一下书中的SQL语句优化章节吧。《数据库系统概论（第四版）》 王珊 萨师煊   高等教育出版社 (掌握基础知识和概念) 然后你可以再看MYSQL手册中的优化部分。讲得都很清楚了。

Answer 11

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

Answer 12

在JOIN查询中，只需要在被驱动表（JOIN 后的表）添加索引就可以了。所以也就有了“一般来说，用小结果集驱动大结果集”这样的说法了。

Answer 13

你后面的这个例子，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;就执行不成功，因为全表扫描的时候，锁定了全表记录。