浅析join表的explain

今天被join表的执行计划给搞的很惨,折腾了半天,无果;经zuoxingyu大哥的指点,有点眉目,做下笔记.

   
   

    
    表:
   
   
   
   

    
    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;
   
   
   
   

    
    索引:
   
   
   
   

    
    create index idx_or_id on ord1(or_id)
   
   

   
   

    
    分析的sql:
   
   
   
   

    
    select * from ord1 ,ord2 where ord1.or_id = ord2.id
   
   
   
   

    
    数据:
   
   
   
   

    
    向表ord1 插入5条数据,ord2插入3条数据
   
   
   
   

    
    mysql> select * from ord1;
+----+-------+------+
| id | or_id | name |
+----+-------+------+
|  1 |     1 | a    |
|  2 |     2 | b    |
|  3 |     3 | c    |
|  4 |     4 | d    |
|  5 |     5 | e    |
+----+-------+------+
5 rows in set (0.00 sec)
   
   
   
   

    
    ======
   
   
   
   

    
    mysql> select * from ord2;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)
   
   
   
   

    
    查看执行计划
   
   
   
   

    
    mysql> explain select * from ord1 ,ord2 where ord1.or_id = ord2.id/G;
   
   
   
   

    
    
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ord2
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: ord1
         type: ALL
possible_keys: idx_or_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where; Using join buffer
2 rows in set (0.00 sec)
   
   
   
   

    
    ================================
   
   
   
   

    
    1:分析:2个表都是全表扫描,没有利用任何索引.(数据量少,mysql优化器认为全表扫描更快,)
   
   
   
   

    
    执行计划先扫描ord2,再扫描ord1
   
   
   
   

    
    ================================
   
   
   
   

    
    向数据表ord2 加到10条数据,
   
   
   
   

    
    再运行执行计划:
   
   
   
   

    
    mysql> explain select * from ord1 ,ord2 where 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: 5
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: ord2
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: x.ord1.or_id
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)
   
   
   
   

    
    ================================
   
   
   
   

    
    2:分析:ord1全表扫描,ord2索引扫描.
   
   
   
   

    
    执行计划先扫描ord1,再扫描ord2
   
   
   
   

    
    ================================
   
   
   
   

    
    1,2对比说明:数据量小的表的执行计划里面总是最先开始被执行,(1里面ord2只有3条数据比ord1少(3<5)先被扫描,
   
   
   
   

    
    而2里面ord1
    
    的数据少,5<10;ord1先被扫描.)验证了:小表驱动大表的原则.
   
   
   
   

    
    =========================================
   
   
   
   

    
    确认上面的结论,再向ord1里面插入数据,到15条;
   
   
   
   

    
    运行执行计划:
   
   
   
   

    
    mysql> explain select * from ord1 ,ord2 where ord1.or_id = ord2.id/G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ord2
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: ord1
         type: ref
possible_keys: idx_or_id
          key: idx_or_id
      key_len: 5
          ref: x.ord2.id
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)
   
   
   
   

    
    ================================================
   
   
   
   

    
    3:
    
    分析:ord2是全表扫描,而ord1是索引扫描,
   
   
   
   

    
    执行计划先扫描ord1,再扫描ord2.
   
   
   
   

    
    ================================================
   
   
   
   

    
    

   
   
   
   

    
    总结:
    
    在MYSQL的JOIN里，使用的是嵌套循环的方法去实现的,以小表驱动大表的原则进行.
   
   
   
   

    
             在JOIN查询中，只需要在被驱动表（JOIN 后的表）<一般都是大表>添加索引就可以了。
   
   
   
   

    
    就如上面例子中的:(ord1的数据里小于ord2)也就是说先同ORD1中取出1条，然后到ORD2中去循环，去找条件符合的记录。
   
   
   
   

    
    然后ORD1移动到下一条，继续道ORD2中查找，直到ORD1全部取完。得到一个结论：ORD1是全表扫描的，
   
   
   
   

    
    不管是否有索引。ORD2是索引扫描的，如果有索引的话.
   
   
   
   

    
    所以上面的例子中第一个小表都是全表扫描.
   
   
   
   

   
   

   
   

    
    zuoxingyu说明:http://blog.chinaunix.net/u3/116107/showart.php?id=2406310
   
   
   
   

   
   

   
   

    
    学习:------------------摘自<MySQL性能调优与架构设计>
   
   
   
   

    
    Join 的实现原理:
   
   
   
   

    
    Nested Loop Join 实际上就是通过驱动表
的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数
据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join 结果集作为循环基础数据，
再一次通过循环查询条件到第三个表中查询数据，如此往复。
   
   
   
   

    
    Join 语句的优化:
   
   
   
   

    
    1:永远用小结果集驱动大的结果集
   
   
   
   

    
    2:保证Join 语句中被驱动表上Join 条件字段已经被索引；
   
   
   
   

    
    3:当无法保证被驱动表的Join 条件字段被索引且内存资源充足的前提下，不要太吝惜Join
Buffer 的设置(我们的Join 必须是All，Index，range 或者是index_merge 类型的
时候，Join Buffer 就会派上用场了)
   
   
   
   

   
   

   
   

    
    未完.....