MySQL的JOIN（四）：JOIN优化实践之快速匹配

优化原则：小表驱动大表，被驱动表建立索引有效，驱动表建立索引基本无效果。A left join B ：A是驱动表，B是被驱动表；A right join B，B是驱动表,A是被驱动表，A jion B,sql优化器会自动优化，实现小表驱动大表。

这篇博文讲述如何优化扫描速度。我们通过MySQL的JOIN（二）：JOIN原理得知了两张表的JOIN操作就是不断从驱动表中取出记录，然后查找出被驱动表中与之匹配的记录并连接。这个过程的实质就是查询操作，想要优化查询操作，建索引是最常用的方式。那索引怎么建呢？我们来讨论下，首先插入测试数据。

    CREATE TABLE t1 (

        id INT PRIMARY KEY AUTO_INCREMENT,

        type INT

    );

    SELECT COUNT(*) FROM t1;

    +----------+

    | COUNT(*) |

    +----------+

    |   110000 |

    +----------+

    CREATE TABLE t2 (

        id INT PRIMARY KEY AUTO_INCREMENT,

        type INT

    );

    SELECT COUNT(*) FROM t2;

    +----------+

    | COUNT(*) |

    +----------+

    |      100 |

    +----------+

左连接

左连接中，左表是驱动表，右表是被驱动表。想要快速查找被驱动表中匹配的记录，所以我们可以在右表建索引，从而提高连接性能。

    -- 首先两个表都没建索引

    EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.type=t2.type;

    +----+-------+------+------+--------+----------------------------------------------------+

    | id | table | type | key  | rows   | Extra                                              |

    +----+-------+------+------+--------+----------------------------------------------------+

    |  1 | t1    | ALL  | NULL | 110428 | NULL                                               |

    |  1 | t2    | ALL  | NULL |    100 | Using where; Using join buffer (Block Nested Loop) |

    +----+-------+------+------+--------+----------------------------------------------------+

    -- 尝试在左表建立索引，改进不大

    CREATE INDEX idx_type ON t1(type);

    EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.type=t2.type;

    +----+-------+-------+----------+--------+----------------------------------------------------+

    | id | table | type  | key      | rows   | Extra                                              |

    +----+-------+-------+----------+--------+----------------------------------------------------+

    |  1 | t1    | index | idx_type | 110428 | Using index                                        |

    |  1 | t2    | ALL   | NULL     |    100 | Using where; Using join buffer (Block Nested Loop) |

    +----+-------+-------+----------+--------+----------------------------------------------------+

    -- 尝试在右表建立索引，效果拔群，Using index!!!

    DROP INDEX idx_type ON t1;

    CREATE INDEX idx_type ON t2(type);

    EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.type=t2.type;

    +----+-------+------+---------------+----------+--------+-------------+

    | id | table | type | possible_keys | key      | rows   | Extra       |

    +----+-------+------+---------------+----------+--------+-------------+

    |  1 | t1    | ALL  | NULL          | NULL     | 110428 | NULL        |

    |  1 | t2    | ref  | idx_type      | idx_type |      1 | Using index |

    +----+-------+------+---------------+----------+--------+-------------+

右连接

右连接中，右表是驱动表，左表是被驱动表，想要快速查找被驱动表中匹配的记录，所以我们可以在左表建索引，从而提高连接性能。

    DROP INDEX idx_type ON t2;

    -- 两个表都没有索引

    EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.type=t2.type;

    +----+-------+------+------+--------+----------------------------------------------------+

    | id | table | type | key  | rows   | Extra                                              |

    +----+-------+------+------+--------+----------------------------------------------------+

    |  1 | t2    | ALL  | NULL |    100 | NULL                                               |

    |  1 | t1    | ALL  | NULL | 110428 | Using where; Using join buffer (Block Nested Loop) |

    +----+-------+------+------+--------+----------------------------------------------------+

    -- 在右边建立索引，改进不大

    CREATE INDEX idx_type ON t2(type);

    EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.type=t2.type;

    +----+-------+-------+---------------+----------+--------+----------------------------------------------------+

    | id | table | type  | possible_keys | key      | rows   | Extra                                              |

    +----+-------+-------+---------------+----------+--------+----------------------------------------------------+

    |  1 | t2    | index | NULL          | idx_type |    100 | Using index                                        |

    |  1 | t1    | ALL   | NULL          | NULL     | 110428 | Using where; Using join buffer (Block Nested Loop) |

    +----+-------+-------+---------------+----------+--------+----------------------------------------------------+

    -- 尝试在左边建立索引，效果拔群！

    DROP INDEX idx_type ON t2;

    CREATE INDEX idx_type ON t1(type);

    EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.type=t2.type;

    +----+-------+------+---------------+--------------+------+-------------+

    | id | table | type | possible_keys | ref          | rows | Extra       |

    +----+-------+------+---------------+--------------+------+-------------+

    |  1 | t2    | ALL  | NULL          | NULL         |  100 | NULL        |

    |  1 | t1    | ref  | idx_type      | test.t2.type |    5 | Using index |

    +----+-------+------+---------------+--------------+------+-------------+

内连接

我们知道，MySQL Optimizer会对内连接做优化，不管谁内连接谁，都是用小表驱动大表，所以如果要优化内连接，可以在大表上建立索引，以提高连接性能。

另外注意一点，在小表上建立索引时，MySQL Optimizer会认为用大表驱动小表效率更快，转而用大表驱动小表。

对内连接小表驱动大表的优化策略不清楚的话，可以看MySQL的JOIN（三）：JOIN优化实践之内循环的次数

    DROP INDEX idx_type ON t1;

    -- 两个表都没有索引，t2驱动t1

    EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.type=t2.type;

    +----+-------+------+------+--------+----------------------------------------------------+

    | id | table | type | key  | rows   | Extra                                              |

    +----+-------+------+------+--------+----------------------------------------------------+

    |  1 | t2    | ALL  | NULL |    100 | NULL                                               |

    |  1 | t1    | ALL  | NULL | 110428 | Using where; Using join buffer (Block Nested Loop) |

    +----+-------+------+------+--------+----------------------------------------------------+

    -- 在t2表上建立索引，MySQL的Optimizer发现后，用大表驱动了小表

    CREATE INDEX idx_type ON t2(type);

    EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.type=t2.type;

    +----+-------+------+----------+--------+-------------+

    | id | table | type | key      | rows   | Extra       |

    +----+-------+------+----------+--------+-------------+

    |  1 | t1    | ALL  | NULL     | 110428 | Using where |

    |  1 | t2    | ref  | idx_type |      1 | Using index |

    +----+-------+------+----------+--------+-------------+

    -- 在t1表上建立索引，再加上t1是大表，符合“小表驱动大表”的原则，性能比上面的语句要好

    DROP INDEX idx_type ON t2;

    CREATE INDEX idx_type ON t1(type);

    EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.type=t2.type;

    +----+-------+------+---------------+----------+------+-------------+

    | id | table | type | possible_keys | key      | rows | Extra       |

    +----+-------+------+---------------+----------+------+-------------+

    |  1 | t2    | ALL  | NULL          | NULL     |  100 | Using where |

    |  1 | t1    | ref  | idx_type      | idx_type |    5 | Using index |

    +----+-------+------+---------------+----------+------+-------------+

三表连接

上面都是两表连接，三表连接也是一样的，找出驱动表和被驱动表，在被驱动表上建立索引，即可提高连接性能。

总结

想要从快速匹配的角度优化JOIN，首先就是找出谁是驱动表，谁是被驱动表，然后在被驱动表上建立索引即可。

最新文章

  1. HTML自定义对象与属性（谷歌，火狐，IE9浏览器没问题）
  2. brew
  3. centos7引导项修复
  4. git 合并分支
  5. ember.js:使用笔记5 使用view
  6. Linux环境下配置eclipse，以及创建maven工程
  7. 删除github账号的方法
  8. Redefining already defined constructor
  9. CodeForces 705A Hulk (水题)
  10. Java json工具类，jackson工具类，ObjectMapper工具类
  11. AndroidStudio工程文件导入Jar包和So第三方库
  12. .net简单页面后台绑定下拉框，按钮，分页 前台aspx页面
  13. Improving the AbiWord's Piece Table
  14. ThinkPHP 常用配置项列表
  15. [ Swift框架 ] # SwiftyJSON
  16. OPENCV之GFTT特征点检测
  17. [SDOI 2008]Cave 洞穴勘测
  18. python +ps 三方面库整理
  19. MongoDB 数据库创建删除、表创建删除、数据增删改查
  20. spring boot与spring mvc的区别是什么？

热门文章

  1. 交换机配置—— 结合以太通道的VLAN配置
  2. TensorFlow使用记录 (七）： BN 层及 Dropout 层的使用
  3. 【知识库】-数据库_MySQL之高级数据查询:去重复、组合查询、连接查询、虚拟表
  4. Yum：[Errno 5] [Errno 2] No such file or directory
  5. LeetCode 77. 组合（Combinations）
  6. 朴素贝叶斯文本分类-在《红楼梦》作者鉴别的应用上（python实现）
  7. Java程序如何限速(控制下载和上传速度)
  8. iOS 修改打包后的.ipa应用名字
  9. WebView的用法
  10. pandas之时间序列(data_range)、重采样(resample)、重组时间序列(PeriodIndex)