MySQL 优化JOIN语句详解

[TOC] # 目录 ## JOIN优化 ### 驱动表 vs 被驱动表 - 外层循环的表是驱动表，内层循环的表是被驱动表 Table | Join Type ---|--- t1 | range t2 | ref t3 | ALL > t1 是 t2 的驱动表，t2 是 t1 的被驱动表 t2 是 t3 的驱动表，t3 是 t2 的被驱动表 ### JOIN调优原则-1 - 用小表驱动大表 - 一般无需人工考虑，**关联查询优化器会自动选择最优的执行顺序** - 如果优化器抽风，可使用 **STRAIGHT_JOIN**, 强制MySQL先读取左边的表，在读取右边的表 - 如果有where条件，应当要能够使用索引，并尽可能地减少外层循环的数据量 #### 1. 示例表 ```sql -- 小表 mysql> SELECT COUNT(*) FROM employees; +----------+ | COUNT(*) | +----------+ | 300025 | +----------+ 1 row in set (0.02 sec) -- 大表 mysql> SELECT COUNT(*) FROM salaries; +----------+ | COUNT(*) | +----------+ | 2844047 | +----------+ 1 row in set (0.10 sec) ``` #### 2. employees是驱动表，salaries是被驱动表 ```sql -- 1. 当id相同的时候，那么就从上往下执行 -- 2. 当id不同的时候，那么就根据id大的先执行 -- 3. 下面列子就会先执行第一行 mysql> EXPLAIN -> SELECT * FROM employees e -> LEFT JOIN salaries s on e.emp_no = s.emp_no -> WHERE e.emp_no = 10001; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | e | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | s | NULL | ref | PRIMARY | PRIMARY | 4 | const | 17 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) ``` #### 3. employees是驱动表，dept_emp是被驱动表 dept_emp是驱动表，departments是被驱动 ```sql mysql> EXPLAIN -> SELECT * FROM employees e -> LEFT JOIN dept_emp de on e.emp_no = de.emp_no -> LEFT JOIN departments d on de.dept_no = d.dept_no -> WHERE e.emp_no = 10001; +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+ | 1 | SIMPLE | e | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | de | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 16 | employees.de.dept_no | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+ 3 rows in set, 1 warning (0.00 sec) ``` #### 4. 尽管查询语句是大表驱动小表，关联查询优化器会自动选择最优的执行顺序 ```sql mysql> EXPLAIN -> SELECT * FROM salaries s -> LEFT JOIN employees e on s.emp_no = e.emp_no -> WHERE e.emp_no = 10001; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | e | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | s | NULL | ref | PRIMARY | PRIMARY | 4 | const | 17 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) ``` #### 5. 使用 STRAIGHT_JOIN, 强制MySQL先读取左边的表，在读取右边的表 ```sql mysql> EXPLAIN -> SELECT * FROM salaries s -> STRAIGHT_JOIN employees e on s.emp_no = e.emp_no -> WHERE e.emp_no = 10001; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s | NULL | ref | PRIMARY | PRIMARY | 4 | const | 17 | 100.00 | NULL | | 1 | SIMPLE | e | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) ``` ### JOIN调优原则-2 - join的字段尽量创建索引 - join字段的类型要保持一致 - 尽量减少扫描的行数（explain-rows） - 尽量控制在百万以内（经验之谈，仅供参考） #### 示例创表语句 ```sql create table `test_user` ( `id` int auto_increment, `name` varchar(45) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ); INSERT INTO test_user (id, name) VALUES (1, 'Eddie'); CREATE TABLE `test_user_ext` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` varchar(45) NOT NULL DEFAULT '', `email` varchar(50) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `test_user_ext_user_id_index` (`user_id`) ); INSERT INTO test_user_ext (id, user_id, email) VALUES (1, '1', 'eddie@qq.com'); ``` #### 问题查询（隐式转换） ```sql mysql> EXPLAIN -> SELECT * FROM test_user u -> LEFT JOIN test_user_ext tue on u.id = tue.user_id -> WHERE u.id = 1; +----+-------------+-------+------------+-------+-----------------------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-----------------------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | u | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | tue | NULL | ALL | test_user_ext_user_id_index | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+-------+-----------------------------+---------+---------+-------+------+----------+-------------+ 2 rows in set, 3 warnings (0.01 sec) ``` > 会发现 test_user_ext 表出现 type=ALL 全表扫描的情况， 为什么出现这种情况呢? 因为join字段的类型不同时，索引无法使用： **u.id(int) = tue.user_id(varchar)** 两个关联字段类型不一样而导致 ##### 通过终端查看 show warnings 获得警告信息 ```sql mysql> use employees; mysql> EXPLAIN SELECT * FROM test_user u LEFT JOIN test_user_ext tue on u.id = tue.user_id WHERE u.id = 1; mysql> show warnings; +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1739 | Cannot use ref access on index 'test_user_ext_user_id_index' due to type or collation conversion on field 'user_id' | | Warning | 1739 | Cannot use range access on index 'test_user_ext_user_id_index' due to type or collation conversion on field 'user_id' | | Note | 1003 | /* select#1 */ select '1' AS `id`,'Eddie' AS `name`,`employees`.`tue`.`id` AS `id`,`employees`.`tue`.`user_id` AS `user_id`,`employees`.`tue`.`email` AS `email` from `employees`.`test_user` `u` left join `employees`.`test_user_ext` `tue` on(('1' = `employees`.`tue`.`user_id`)) where true | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) ``` ##### 解决办法 varchar类型修改为int类型即可 ```sql ALTER TABLE test_user_ext MODIFY user_id int DEFAULT 0 NOT NULL mysql> EXPLAIN SELECT * FROM test_user u LEFT JOIN test_user_ext tue on u.id = tue.user_id WHERE u.id = 1; +----+-------------+-------+------------+-------+-----------------------------+-----------------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-----------------------------+-----------------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | u | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | tue | NULL | ref | test_user_ext_user_id_index | test_user_ext_user_id_index | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+-----------------------------+-----------------------------+---------+-------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) ``` ### JOIN调优原则-3 - 参与join的表不要太多 - 阿里编程规约建议不超过3张 (比如业务就是很复杂，只能拆分语句，Java编写不同方法调用) - 如果被驱动表join字段用不了索引，且内存较为充足，可以考虑把参数：**join buffer** 设置得大一些 #### 复杂语句拆分示例 ```sql -- 拆分前 SELECT * FROM employees e LEFT JOIN dept_emp de on e.emp_no = de.emp_no LEFT JOIN departments d on de.dept_no = d.dept_no WHERE e.emp_no = 10001; -- 拆分后 SELECT * FROM employees WHERE emp_no = 10001; SELECT * FROM dept_emp WHERE emp_no = 10001; ### d005 SELECT * FROM departments WHERE dept_no = 'd005'; ``` > 比如使用Mybatis, 可以拆分三个不同的方法，得到数据，这里举例说明，一般三个Join是可以接受的 不要以为写复杂的语句就很牛，要么你是DBA，要么你就想将来接手项目的人崩溃。拆分代码方法性能绝对比你多张表join 数据库循环更有效率。 ### 吐槽在本人工作经历，曾经有一些自认很牛叉的人，关联十张或者更多join，然后一堆函数和字段返回再次查询，然后在 UNION ALL。无论怎么跟他们解释，都是感觉自己一条SQL写到尾。返回数据不是更快捷，可惜将来接手的人，看到Mybatis的XML文件，一个方法过百行，十几个join，就无语了。只能说一句，你们要么就是很懒的人不想写代码，要么就想把项目搞崩。