1 set environment


DROP TABLE IF EXISTS a, b, c, d, e; 

CREATE TABLE a ( a_0 INT, b_0 INT, KEY (a_0), KEY (b_0) ); 
CREATE TABLE b ( b_0 INT, PRIMARY KEY (b_0) ); 
CREATE TABLE c ( c_0 INT, c_1 INT, c_2 INT, a_0 INT, KEY (c_0), KEY (c_1) ); 
CREATE TABLE d ( c_0 INT, e_0 INT, KEY (c_0) ); 
CREATE TABLE e ( e_0 INT, e_1 INT, PRIMARY KEY (e_0), KEY (e_1) ); 

EXPLAIN SELECT 1 FROM 
a 
LEFT JOIN c AS c0 ON c0.a_0 = a.a_0 
LEFT JOIN d ON d.c_0 = c0.c_0 
LEFT JOIN c AS c1 ON c1.a_0 = a.a_0 
LEFT JOIN c AS c2 ON c2.a_0 = a.a_0 
LEFT JOIN c AS c3 ON c3.a_0 = a.a_0 
LEFT JOIN c AS c4 ON c4.a_0 = a.a_0 
LEFT JOIN c AS c5 ON c5.a_0 = a.a_0 
LEFT JOIN c AS c6 ON c6.a_0 = a.a_0 
LEFT JOIN c AS c7 ON c7.a_0 = a.a_0 
LEFT JOIN c AS c8 ON c8.a_0 = a.a_0 
LEFT JOIN c AS c9 ON c9.a_0 = a.a_0 
JOIN b AS b1 ON b1.b_0 = a.b_0 
JOIN b AS b2 ON b2.b_0 = a.b_0 
JOIN b AS b3 ON b3.b_0 = a.b_0 
JOIN b AS b4 ON b4.b_0 = a.b_0 
JOIN b AS b5 ON b5.b_0 = a.b_0 
JOIN b AS b6 ON b6.b_0 = a.b_0 
JOIN b AS b7 ON b7.b_0 = a.b_0 
JOIN b AS b8 ON b8.b_0 = a.b_0 
JOIN b AS b9 ON b9.b_0 = a.b_0 
LEFT JOIN c as c10 ON c10.a_0 = a.a_0 
AND c10.c_1 IS NULL 
LEFT JOIN e AS e1 ON e1.e_0 = c10.c_2 
JOIN b AS b10 ON b10.b_0 = a.b_0 
LEFT JOIN e AS e2 ON e2.e_0 = d.e_0 
AND e2.e_1 IS NULL;


2 execute "EXPLAIN SELECT 1 FROM...", it run very slowly. Why?

mysql> select version();

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

| version() |

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

| 6.1.10 |

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

1 row in set (0.00 sec)



mysql> EXPLAIN SELECT 1 FROM

-> a

-> LEFT JOIN c AS c0 ON c0.a_0 = a.a_0

-> LEFT JOIN d ON d.c_0 = c0.c_0

-> LEFT JOIN c AS c1 ON c1.a_0 = a.a_0

-> LEFT JOIN c AS c2 ON c2.a_0 = a.a_0

-> LEFT JOIN c AS c3 ON c3.a_0 = a.a_0

-> LEFT JOIN c AS c4 ON c4.a_0 = a.a_0

-> LEFT JOIN c AS c5 ON c5.a_0 = a.a_0

-> LEFT JOIN c AS c6 ON c6.a_0 = a.a_0

-> LEFT JOIN c AS c7 ON c7.a_0 = a.a_0

-> LEFT JOIN c AS c8 ON c8.a_0 = a.a_0

-> LEFT JOIN c AS c9 ON c9.a_0 = a.a_0

-> JOIN b AS b1 ON b1.b_0 = a.b_0

-> JOIN b AS b2 ON b2.b_0 = a.b_0

-> JOIN b AS b3 ON b3.b_0 = a.b_0

-> JOIN b AS b4 ON b4.b_0 = a.b_0

-> JOIN b AS b5 ON b5.b_0 = a.b_0

-> JOIN b AS b6 ON b6.b_0 = a.b_0

-> JOIN b AS b7 ON b7.b_0 = a.b_0

-> JOIN b AS b8 ON b8.b_0 = a.b_0

-> JOIN b AS b9 ON b9.b_0 = a.b_0

-> LEFT JOIN c as c10 ON c10.a_0 = a.a_0

-> AND c10.c_1 IS NULL

-> LEFT JOIN e AS e1 ON e1.e_0 = c10.c_2

-> JOIN b AS b10 ON b10.b_0 = a.b_0

-> LEFT JOIN e AS e2 ON e2.e_0 = d.e_0

-> AND e2.e_1 IS NULL;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | a | ALL | b_0 | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c10 | ref | c_1 | c_1 | 5 | const | 0 | |

| 1 | SIMPLE | b1 | eq_ref | PRIMARY | PRIMARY | 4 | test.a.b_0 | 1 | Using index |

| 1 | SIMPLE | b2 | eq_ref | PRIMARY | PRIMARY | 4 | test.a.b_0 | 1 | Using index |

| 1 | SIMPLE | b3 | eq_ref | PRIMARY | PRIMARY | 4 | test.a.b_0 | 1 | Using index |

| 1 | SIMPLE | b4 | eq_ref | PRIMARY | PRIMARY | 4 | test.b3.b_0 | 1 | Using where; Using index |

| 1 | SIMPLE | b5 | eq_ref | PRIMARY | PRIMARY | 4 | test.a.b_0 | 1 | Using index |

| 1 | SIMPLE | b6 | eq_ref | PRIMARY | PRIMARY | 4 | test.b1.b_0 | 1 | Using where; Using index |

| 1 | SIMPLE | b7 | eq_ref | PRIMARY | PRIMARY | 4 | test.a.b_0 | 1 | Using index |

| 1 | SIMPLE | b8 | eq_ref | PRIMARY | PRIMARY | 4 | test.b7.b_0 | 1 | Using where; Using index |

| 1 | SIMPLE | b9 | eq_ref | PRIMARY | PRIMARY | 4 | test.b8.b_0 | 1 | Using where; Using index |

| 1 | SIMPLE | c0 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | d | ref | c_0 | c_0 | 5 | test.c0.c_0 | 1 | |

| 1 | SIMPLE | e2 | ref | PRIMARY,e_1 | e_1 | 5 | const | 0 | Using index |

| 1 | SIMPLE | e1 | eq_ref | PRIMARY | PRIMARY | 4 | test.c10.c_2 | 1 | Using index |

| 1 | SIMPLE | b10 | eq_ref | PRIMARY | PRIMARY | 4 | test.b9.b_0 | 1 | Using where; Using index |

| 1 | SIMPLE | c1 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c2 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c3 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c4 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c5 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c6 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c7 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c8 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c9 | ALL | NULL | NULL | NULL | NULL | 1 | |

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

25 rows in set (52.83 sec)


3 analyse and solve it

mysql> show variables like 'optimizer_search_depth';

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

| Variable_name | Value |

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

| optimizer_search_depth | 62 |

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

1 row in set (0.00 sec)


If we set optimizer_search_depth, sql run quickly

mysql> set optimizer_search_depth = 7;

Query OK, 0 rows affected (0.00 sec)


mysql> EXPLAIN SELECT 2 FROM

-> a

-> LEFT JOIN c AS c0 ON c0.a_0 = a.a_0

-> LEFT JOIN d ON d.c_0 = c0.c_0

-> LEFT JOIN c AS c1 ON c1.a_0 = a.a_0

-> LEFT JOIN c AS c2 ON c2.a_0 = a.a_0

-> LEFT JOIN c AS c3 ON c3.a_0 = a.a_0

-> LEFT JOIN c AS c4 ON c4.a_0 = a.a_0

-> LEFT JOIN c AS c5 ON c5.a_0 = a.a_0

-> LEFT JOIN c AS c6 ON c6.a_0 = a.a_0

-> LEFT JOIN c AS c7 ON c7.a_0 = a.a_0

-> LEFT JOIN c AS c8 ON c8.a_0 = a.a_0

-> LEFT JOIN c AS c9 ON c9.a_0 = a.a_0

-> JOIN b AS b1 ON b1.b_0 = a.b_0

-> JOIN b AS b2 ON b2.b_0 = a.b_0

-> JOIN b AS b3 ON b3.b_0 = a.b_0

-> JOIN b AS b4 ON b4.b_0 = a.b_0

-> JOIN b AS b5 ON b5.b_0 = a.b_0

-> JOIN b AS b6 ON b6.b_0 = a.b_0

-> JOIN b AS b7 ON b7.b_0 = a.b_0

-> JOIN b AS b8 ON b8.b_0 = a.b_0

-> JOIN b AS b9 ON b9.b_0 = a.b_0

-> LEFT JOIN c as c10 ON c10.a_0 = a.a_0

-> AND c10.c_1 IS NULL

-> LEFT JOIN e AS e1 ON e1.e_0 = c10.c_2

-> JOIN b AS b10 ON b10.b_0 = a.b_0

-> LEFT JOIN e AS e2 ON e2.e_0 = d.e_0

-> AND e2.e_1 IS NULL;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | a | ALL | b_0 | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c10 | ref | c_1 | c_1 | 5 | const | 0 | |

| 1 | SIMPLE | c0 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | d | ref | c_0 | c_0 | 5 | test.c0.c_0 | 1 | |

| 1 | SIMPLE | e2 | ref | PRIMARY,e_1 | e_1 | 5 | const | 0 | Using index |

| 1 | SIMPLE | c1 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c2 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c3 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | e1 | eq_ref | PRIMARY | PRIMARY | 4 | test.c10.c_2 | 1 | Using index |

| 1 | SIMPLE | b1 | eq_ref | PRIMARY | PRIMARY | 4 | test.a.b_0 | 1 | Using index |

| 1 | SIMPLE | b2 | eq_ref | PRIMARY | PRIMARY | 4 | test.a.b_0 | 1 | Using index |

| 1 | SIMPLE | b3 | eq_ref | PRIMARY | PRIMARY | 4 | test.a.b_0 | 1 | Using index |

| 1 | SIMPLE | b4 | eq_ref | PRIMARY | PRIMARY | 4 | test.b3.b_0 | 1 | Using where; Using index |

| 1 | SIMPLE | b5 | eq_ref | PRIMARY | PRIMARY | 4 | test.a.b_0 | 1 | Using index |

| 1 | SIMPLE | b6 | eq_ref | PRIMARY | PRIMARY | 4 | test.b1.b_0 | 1 | Using where; Using index |

| 1 | SIMPLE | b7 | eq_ref | PRIMARY | PRIMARY | 4 | test.a.b_0 | 1 | Using index |

| 1 | SIMPLE | b8 | eq_ref | PRIMARY | PRIMARY | 4 | test.b7.b_0 | 1 | Using where; Using index |

| 1 | SIMPLE | b9 | eq_ref | PRIMARY | PRIMARY | 4 | test.b8.b_0 | 1 | Using where; Using index |

| 1 | SIMPLE | b10 | eq_ref | PRIMARY | PRIMARY | 4 | test.b9.b_0 | 1 | Using where; Using index |

| 1 | SIMPLE | c4 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c5 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c6 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c7 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c8 | ALL | NULL | NULL | NULL | NULL | 1 | |

| 1 | SIMPLE | c9 | ALL | NULL | NULL | NULL | NULL | 1 | |

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

25 rows in set (0.01 sec)


结论:

MySQL 对于多表连接算法,通常采取的是贪婪算法(greedy_search函数),这个函数对于多表连接的空间进行限制,如果表很多,则连接的顺序的各种可能解则会很多,如果搜索空间大,则搜索耗费的时间就会多(因为每种检查都会被确认一遍).所以查询执行计划生成就会很慢.系统参数'optimizer_search_depth'就是限制搜索空间大小的.所以设置为一个较小的值,有利于很快得到查询执行计划, 但得到的很可能不是最优的.


启示:

如果在MySQL V5.5.X上运行多个表的连接(外连接/内连接等都包括),如果查询执行计划获得速度较慢（如超过一秒），则可以查看其'optimizer_search_depth'参数的设置，并修改它，使得生成查询执行计划的速度变快。


另外,对于参数'optimizer_search_depth',MySQL的默认值给的是62,这个是不合适的,请在多表连接时稍加注意,避免因这个参数的值影响到查询执行计划的生成速度.