首页 > 代码库 > optimizer_search_depth探讨

optimizer_search_depth探讨

10.1背景:模拟通过多表查询以分类为分组查询分类中的节目数

10.2建表语句(由于实际应用的表,删除了部分字段)

mysql> show create table movies\G;

*************************** 1. row ***************************

       Table: movies

Create Table: CREATE TABLE `movies` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `title` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT ‘‘,

  `mpeg` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT ‘‘,

  `pre_mpeg` varchar(255) CHARACTER SET latin1 DEFAULT ‘‘,

  `genre` int(11) DEFAULT ‘1‘,

  `icp` int(11) DEFAULT ‘1‘,

  .....

  `is_section` int(11) DEFAULT ‘0‘,

  `keyword` varchar(255) CHARACTER SET latin1 DEFAULT NULL,

  `movie_type` varchar(255) CHARACTER SET latin1 DEFAULT NULL,

  `sourcetype` int(11) DEFAULT ‘0‘,

  `pre_mpeg_type` int(11) DEFAULT ‘0‘,

  `fenbianlv` varchar(255) CHARACTER SET latin1 DEFAULT NULL,

  `look_focus` varchar(255) CHARACTER SET latin1 DEFAULT NULL,

  `play_time` varchar(20) CHARACTER SET latin1 DEFAULT NULL,

  `downline_time` varchar(20) CHARACTER SET latin1 DEFAULT NULL,

  `channelcode` varchar(255) CHARACTER SET latin1 DEFAULT NULL,

  `uniquecode` varchar(64) CHARACTER SET latin1 NOT NULL DEFAULT ‘‘,

  PRIMARY KEY (`id`),

  UNIQUE KEY `mpeg_index` (`mpeg`),

  KEY `title_index` (`title`),

  KEY `genre_index` (`genre`),

  KEY `tele_index` (`teleplaycode`,`movie_order`),

  KEY `count_index` (`isdel`,`auditing`),

  KEY `madein_index` (`madein`),

  KEY `keyword_index` (`keyword`),

  KEY `movie_type_index` (`movie_type`),

  KEY `index_imgname` (`image_name`),

  KEY `uniquecode_index` (`uniquecode`),

  KEY `actor` (`actor`),

  KEY `actor1` (`actor1`),

  KEY `director` (`director`)

) ENGINE=MyISAM AUTO_INCREMENT=160715 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

id为主键,genre列有索引

mysql> show create table standard_menu\G;

*************************** 1. row ***************************

       Table: standard_menu

Create Table: CREATE TABLE `standard_menu` (

  `id` varchar(64) NOT NULL DEFAULT ‘‘,

  `parent_id` varchar(64) NOT NULL DEFAULT ‘0‘,

  `name` varchar(128) NOT NULL DEFAULT ‘‘,

  `service_code` varchar(32) DEFAULT NULL,

  `keyword` varchar(255) DEFAULT NULL,

  `language` int(11) DEFAULT ‘0‘,

  `menu_kind` int(11) DEFAULT ‘0‘,

  `position` int(11) DEFAULT ‘99‘,

  `genre_id` int(11) DEFAULT NULL,

  ......

  `lck_mark` char(1) DEFAULT ‘0‘,

  `coll_state` char(1) DEFAULT ‘0‘,

  `search_range` varchar(10) DEFAULT NULL,

  `search_value` varchar(255) DEFAULT NULL,

  `sel_info` varchar(255) DEFAULT NULL,

  `menu_style` int(11) DEFAULT ‘0‘,

  `cp_id` int(11) DEFAULT ‘0‘,

  `reserve_info` text,

  `menu_remark` text,

  PRIMARY KEY (`id`),

  KEY `Index_sel` (`parent_id`,`position`),

  KEY `index_lock` (`lck_mark`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

id为主键,genre列无索引

mysql> show create table genre\G;

*************************** 1. row ***************************

       Table: genre

Create Table: CREATE TABLE `genre` (

  `gen_id` double DEFAULT NULL,

  `gen_name` varchar(765) DEFAULT NULL,

  `gen_seq` tinyint(4) DEFAULT NULL,

  `gen_level` varchar(765) DEFAULT NULL,

  `display_order` varchar(120) DEFAULT NULL,

  KEY `genre_gen_id_idx` (`gen_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql> show create table menu_movie\G;

*************************** 1. row ***************************

       Table: menu_movie

Create Table: CREATE TABLE `menu_movie` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `menu_code` varchar(64) NOT NULL DEFAULT ‘0‘,

  `movie_id` int(11) DEFAULT NULL,

  `movie_mpeg` varchar(255) NOT NULL DEFAULT ‘0‘,

  `movie_order` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `menu_movie_menu_code_idx` (`menu_code`)

) ENGINE=MyISAM AUTO_INCREMENT=12374 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

10.3查询语句

mysql> explain extended select a.genre,count(*) from movies a

    -> join genre b on b.gen_id=a.genre

    -> join standard_menu c on b.gen_id=c.genre_id

    -> join menu_movie d

    -> where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code

    -> group by a.genre having count(*)>10

    -> order by a.id desc;

10.4打开与关闭bka算法的示例:

10.4.1关闭

默认是关闭的。也可以手工关闭:

mysql> SET optimizer_switch=‘mrr=off,mrr_cost_based=on,batched_key_access=off‘;

Query OK, 0 rows affected (0.00 sec)

mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where                                  |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                     |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where                                  |

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

4 rows in set, 1 warning (0.00 sec)

10.4.1打开

mysql> SET optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on‘;

Query OK, 0 rows affected (0.00 sec)

10.5探讨bka参数设置

MySQL里限制一个查询的join表数目上限为61,对于一个有61个表参与的join操作,理论上需要61!(阶乘)次的评估。在多表join的场景下,为了避免优化器占用太多时间,MySQL提供了一个参数 optimizer_search_depth 来控制递归深度。这个参数对算法的控制可以简单描述为:对于所有的排列,只取前当前join顺序的前optimizer_search_depth个表估算代价。举例来说,20张表的,假设optimizer_search_depth为4,那么评估次数为20*19*18*17。

怎么选择参数optimizer_search_depth的值?MySQL中optimizer_search_depth默认值为62.也就是说默认为全排列计算。

曾试图找到什么规律,暂时也没找到。

寻找思路如下:

(1)optimizer_prune_level=0情况下,查询执行计划情况;

(2)optimizer_prune_level=1情况下,查询执行计划情况。

10.5.1optimizer_prune_level=0情况

mysql> set optimizer_prune_level=0;

Query OK, 0 rows affected (0.01 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.00 sec)


mysql> set profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> set optimizer_search_depth=0;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.00 sec)

optimizer_search_depth=0与默认情况下的执行计划基本相同。


mysql> show profile for query 237;

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

| Status               | Duration |

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

| starting             | 0.000172 |

| checking permissions | 0.000010 |

| checking permissions | 0.000008 |

| checking permissions | 0.000006 |

| checking permissions | 0.000010 |

| Opening tables       | 0.000111 |

| init                 | 0.000052 |

| System lock          | 0.000073 |

| optimizing           | 0.000030 |

| statistics           | 0.000268 |

| preparing            | 0.000038 |

| Creating tmp table   | 0.000032 |

| Sorting result       | 0.000009 |

| explaining           | 0.000071 |

| query end            | 0.000013 |

| removing tmp table   | 0.000007 |

| query end            | 0.000007 |

| closing tables       | 0.000014 |

| freeing items        | 0.000142 |

| cleaning up          | 0.000403 |

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

20 rows in set, 1 warning (0.00 sec)

查询持续时间为0.000268s

mysql> set optimizer_search_depth=1;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | b     | index  | genre_gen_id_idx         | genre_gen_id_idx         | 9       | NULL                 |  376 |    99.47 | Using where; Using index; Using temporary; Using filesort     |

|  1 | SIMPLE      | c     | ref    | PRIMARY,ind_genre_id     | ind_genre_id             | 5       | Portal_19.b.gen_id   |    4 |   100.00 | Using index condition; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | d     | ref    | menu_movie_menu_code_idx | menu_movie_menu_code_idx | 66      | Portal_19.c.id       |    3 |   100.00 | Using where; Using join buffer (Batched Key Access)           |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY                  | 4       | Portal_19.d.movie_id |    1 |   100.00 | Using where; Using join buffer (Batched Key Access)           |

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

4 rows in set, 1 warning (0.00 sec)


mysql> show profile for query 239;

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

| Status               | Duration |

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

| starting             | 0.000225 |

| checking permissions | 0.000013 |

| checking permissions | 0.000031 |

| checking permissions | 0.000006 |

| checking permissions | 0.000011 |

| Opening tables       | 0.000125 |

| init                 | 0.000058 |

| System lock          | 0.000035 |

| optimizing           | 0.000027 |

| statistics           | 0.000241 |

| preparing            | 0.000069 |

| Creating tmp table   | 0.000037 |

| Sorting result       | 0.000009 |

| explaining           | 0.000087 |

| query end            | 0.000017 |

| removing tmp table   | 0.000008 |

| query end            | 0.000006 |

| closing tables       | 0.000015 |

| freeing items        | 0.000968 |

| cleaning up          | 0.000138 |

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

20 rows in set, 1 warning (0.00 sec)

在optimizer_search_depth=1时,即评估次数为4,表的扫描顺序为bcda。似乎这种情况是最优的。但所有的扫描持续时间有都比其他情况下时间长?

下面的optimizer_search_depth=2-6,表的扫描顺序为dcba,所有的扫描持续时间基本相同。

mysql> set optimizer_search_depth=2;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 241;

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

| Status               | Duration |

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

| starting             | 0.000313 |

| checking permissions | 0.000017 |

| checking permissions | 0.000006 |

| checking permissions | 0.000005 |

| checking permissions | 0.000013 |

| Opening tables       | 0.000146 |

| init                 | 0.000065 |

| System lock          | 0.000062 |

| optimizing           | 0.000033 |

| statistics           | 0.000143 |

| preparing            | 0.000066 |

| Creating tmp table   | 0.000037 |

| Sorting result       | 0.000009 |

| explaining           | 0.000079 |

| query end            | 0.000018 |

| removing tmp table   | 0.000007 |

| query end            | 0.000007 |

| closing tables       | 0.000016 |

| freeing items        | 0.000848 |

| cleaning up          | 0.000091 |

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

20 rows in set, 1 warning (0.00 sec)


mysql> 

mysql> set optimizer_search_depth=3;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 243;

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

| Status               | Duration |

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

| starting             | 0.000194 |

| checking permissions | 0.000013 |

| checking permissions | 0.000007 |

| checking permissions | 0.000007 |

| checking permissions | 0.000012 |

| Opening tables       | 0.000123 |

| init                 | 0.000101 |

| System lock          | 0.000044 |

| optimizing           | 0.000035 |

| statistics           | 0.000162 |

| preparing            | 0.000036 |

| Creating tmp table   | 0.000073 |

| Sorting result       | 0.000012 |

| explaining           | 0.000118 |

| query end            | 0.000018 |

| removing tmp table   | 0.000009 |

| query end            | 0.000008 |

| closing tables       | 0.000027 |

| freeing items        | 0.001002 |

| cleaning up          | 0.000075 |

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

20 rows in set, 1 warning (0.00 sec)

mysql> set optimizer_search_depth=4;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.00 sec)


mysql> show profile for query 245;

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

| Status               | Duration |

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

| starting             | 0.000247 |

| checking permissions | 0.000016 |

| checking permissions | 0.000007 |

| checking permissions | 0.000007 |

| checking permissions | 0.000011 |

| Opening tables       | 0.000117 |

| init                 | 0.000088 |

| System lock          | 0.000039 |

| optimizing           | 0.000030 |

| statistics           | 0.000430 |

| preparing            | 0.000065 |

| Creating tmp table   | 0.000035 |

| Sorting result       | 0.000010 |

| explaining           | 0.000082 |

| query end            | 0.000019 |

| removing tmp table   | 0.000009 |

| query end            | 0.000007 |

| closing tables       | 0.000017 |

| freeing items        | 0.000042 |

| cleaning up          | 0.000023 |

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

20 rows in set, 1 warning (0.00 sec)


mysql> 

mysql> set optimizer_search_depth=5;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.00 sec)


mysql> show profile for query 247;

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

| Status               | Duration |

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

| starting             | 0.000170 |

| checking permissions | 0.000034 |

| checking permissions | 0.000006 |

| checking permissions | 0.000006 |

| checking permissions | 0.000008 |

| Opening tables       | 0.000104 |

| init                 | 0.000079 |

| System lock          | 0.000036 |

| optimizing           | 0.000029 |

| statistics           | 0.000151 |

| preparing            | 0.000037 |

| Creating tmp table   | 0.000033 |

| Sorting result       | 0.000009 |

| explaining           | 0.000062 |

| query end            | 0.000015 |

| removing tmp table   | 0.000007 |

| query end            | 0.000006 |

| closing tables       | 0.000032 |

| freeing items        | 0.000035 |

| cleaning up          | 0.000020 |

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

20 rows in set, 1 warning (0.00 sec)


mysql> 

mysql> set optimizer_search_depth=6;

Query OK, 0 rows affected (0.01 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.01 sec)


mysql> show profile for query 249;

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

| Status               | Duration |

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

| starting             | 0.000095 |

| checking permissions | 0.000015 |

| checking permissions | 0.000008 |

| checking permissions | 0.000006 |

| checking permissions | 0.000126 |

| Opening tables       | 0.000124 |

| init                 | 0.000061 |

| System lock          | 0.000063 |

| optimizing           | 0.000033 |

| statistics           | 0.000162 |

| preparing            | 0.000071 |

| Creating tmp table   | 0.000036 |

| Sorting result       | 0.000010 |

| explaining           | 0.000081 |

| query end            | 0.000016 |

| removing tmp table   | 0.000008 |

| query end            | 0.000008 |

| closing tables       | 0.000017 |

| freeing items        | 0.000761 |

| cleaning up          | 0.000062 |

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

20 rows in set, 1 warning (0.00 sec)




10.5.2optimizer_prune_level=1情况


mysql> select @@optimizer_prune_level;

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

| @@optimizer_prune_level |

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

|                       0 |

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

1 row in set (0.01 sec)


mysql> set optimizer_prune_level=1;

Query OK, 0 rows affected (0.00 sec)


mysql> set optimizer_search_depth=0;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.00 sec)



mysql> show profile for query 253;

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

| Status               | Duration |

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

| starting             | 0.000311 |

| checking permissions | 0.000016 |

| checking permissions | 0.000006 |

| checking permissions | 0.000006 |

| checking permissions | 0.000011 |

| Opening tables       | 0.000165 |

| init                 | 0.000068 |

| System lock          | 0.000042 |

| optimizing           | 0.000030 |

| statistics           | 0.000149 |

| preparing            | 0.000044 |

| Creating tmp table   | 0.000039 |

| Sorting result       | 0.000009 |

| explaining           | 0.000116 |

| query end            | 0.000018 |

| removing tmp table   | 0.000008 |

| query end            | 0.000007 |

| closing tables       | 0.000016 |

| freeing items        | 0.000800 |

| cleaning up          | 0.000095 |

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

20 rows in set, 1 warning (0.00 sec)


mysql> 

mysql> set optimizer_search_depth=1;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | b     | index  | genre_gen_id_idx         | genre_gen_id_idx         | 9       | NULL                 |  376 |    99.47 | Using where; Using index; Using temporary; Using filesort     |

|  1 | SIMPLE      | c     | ref    | PRIMARY,ind_genre_id     | ind_genre_id             | 5       | Portal_19.b.gen_id   |    4 |   100.00 | Using index condition; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | d     | ref    | menu_movie_menu_code_idx | menu_movie_menu_code_idx | 66      | Portal_19.c.id       |    3 |   100.00 | Using where; Using join buffer (Batched Key Access)           |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY                  | 4       | Portal_19.d.movie_id |    1 |   100.00 | Using where; Using join buffer (Batched Key Access)           |

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

4 rows in set, 1 warning (0.00 sec)


mysql> show profile for query 255;

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

| Status               | Duration |

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

| starting             | 0.000139 |

| checking permissions | 0.000011 |

| checking permissions | 0.000006 |

| checking permissions | 0.000006 |

| checking permissions | 0.000008 |

| Opening tables       | 0.000106 |

| init                 | 0.000051 |

| System lock          | 0.000036 |

| optimizing           | 0.000030 |

| statistics           | 0.000174 |

| preparing            | 0.000041 |

| Creating tmp table   | 0.000034 |

| Sorting result       | 0.000009 |

| explaining           | 0.000085 |

| query end            | 0.000014 |

| removing tmp table   | 0.000008 |

| query end            | 0.000007 |

| closing tables       | 0.000016 |

| freeing items        | 0.000819 |

| cleaning up          | 0.000059 |

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

20 rows in set, 1 warning (0.01 sec)

在optimizer_search_depth=1时,即评估次数为4,表的扫描顺序为bcda。似乎这种情况是最优的。但所有的扫描持续时间与其他情况下基本相同,与在optimizer_prune_level=0情况下基本相同。

下面的optimizer_search_depth=2-6,表的扫描顺序为dcba,所有的扫描持续时间基本相同。或许是数据量小的缘故,没测试出什么来。

mysql> 

mysql> set optimizer_search_depth=2;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.01 sec)


mysql> show profile for query 257;

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

| Status               | Duration |

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

| starting             | 0.000167 |

| checking permissions | 0.000014 |

| checking permissions | 0.000005 |

| checking permissions | 0.000006 |

| checking permissions | 0.000008 |

| Opening tables       | 0.000113 |

| init                 | 0.000086 |

| System lock          | 0.000037 |

| optimizing           | 0.000027 |

| statistics           | 0.000137 |

| preparing            | 0.000041 |

| Creating tmp table   | 0.000035 |

| Sorting result       | 0.000009 |

| explaining           | 0.000075 |

| query end            | 0.000015 |

| removing tmp table   | 0.000007 |

| query end            | 0.000007 |

| closing tables       | 0.000015 |

| freeing items        | 0.000032 |

| cleaning up          | 0.000043 |

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

20 rows in set, 1 warning (0.00 sec)


mysql>

mysql> set optimizer_search_depth=3;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.00 sec)


mysql> show profile for query 259;

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

| Status               | Duration |

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

| starting             | 0.000186 |

| checking permissions | 0.000014 |

| checking permissions | 0.000006 |

| checking permissions | 0.000006 |

| checking permissions | 0.000008 |

| Opening tables       | 0.000117 |

| init                 | 0.000058 |

| System lock          | 0.000034 |

| optimizing           | 0.000061 |

| statistics           | 0.000167 |

| preparing            | 0.000042 |

| Creating tmp table   | 0.000040 |

| Sorting result       | 0.000009 |

| explaining           | 0.000078 |

| query end            | 0.000014 |

| removing tmp table   | 0.000008 |

| query end            | 0.000004 |

| closing tables       | 0.000251 |

| freeing items        | 0.000050 |

| cleaning up          | 0.000024 |

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

20 rows in set, 1 warning (0.01 sec)


mysql> 

mysql> set optimizer_search_depth=4;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.00 sec)


mysql> show profile for query 261;

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

| Status               | Duration |

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

| starting             | 0.000217 |

| checking permissions | 0.000013 |

| checking permissions | 0.000006 |

| checking permissions | 0.000005 |

| checking permissions | 0.000008 |

| Opening tables       | 0.000100 |

| init                 | 0.000054 |

| System lock          | 0.000060 |

| optimizing           | 0.000030 |

| statistics           | 0.000149 |

| preparing            | 0.000042 |

| Creating tmp table   | 0.000037 |

| Sorting result       | 0.000009 |

| explaining           | 0.000076 |

| query end            | 0.000014 |

| removing tmp table   | 0.000008 |

| query end            | 0.000007 |

| closing tables       | 0.000014 |

| freeing items        | 0.000048 |

| cleaning up          | 0.000041 |

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

20 rows in set, 1 warning (0.00 sec)


mysql> set optimizer_search_depth=5;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.00 sec)


mysql> show profile for query 263;

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

| Status               | Duration |

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

| starting             | 0.000175 |

| checking permissions | 0.000035 |

| checking permissions | 0.000010 |

| checking permissions | 0.000006 |

| checking permissions | 0.000022 |

| Opening tables       | 0.000118 |

| init                 | 0.000084 |

| System lock          | 0.000056 |

| optimizing           | 0.000033 |

| statistics           | 0.000130 |

| preparing            | 0.000038 |

| Creating tmp table   | 0.000060 |

| Sorting result       | 0.000010 |

| explaining           | 0.000078 |

| query end            | 0.000014 |

| removing tmp table   | 0.000007 |

| query end            | 0.000006 |

| closing tables       | 0.000014 |

| freeing items        | 0.000746 |

| cleaning up          | 0.000059 |

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

20 rows in set, 1 warning (0.01 sec)


mysql> 

mysql> set optimizer_search_depth=6;

Query OK, 0 rows affected (0.00 sec)


mysql> explain extended select a.genre,count(*) from movies a join genre b on b.gen_id=a.genre join standard_menu c on b.gen_id=c.genre_id join menu_movie d where b.gen_id <1000000 and a.id=d.movie_id  and c.id=d.menu_code group by a.genre having count(*)>10 order by a.id desc;

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

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

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

|  1 | SIMPLE      | d     | ALL    | menu_movie_menu_code_idx | NULL             | NULL    | NULL                  |  998 |   100.00 | Using where; Using temporary; Using filesort        |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY,ind_genre_id     | PRIMARY          | 66      | Portal_19.d.menu_code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | b     | ref    | genre_gen_id_idx         | genre_gen_id_idx | 9       | Portal_19.c.genre_id  |    1 |   100.00 | Using where; Using index                            |

|  1 | SIMPLE      | a     | eq_ref | PRIMARY,genre_index      | PRIMARY          | 4       | Portal_19.d.movie_id  |    1 |   100.00 | Using where; Using join buffer (Batched Key Access) |

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

4 rows in set, 1 warning (0.00 sec)


mysql> show profile for query 265;

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

| Status               | Duration |

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

| starting             | 0.000181 |

| checking permissions | 0.000013 |

| checking permissions | 0.000009 |

| checking permissions | 0.000006 |

| checking permissions | 0.000008 |

| Opening tables       | 0.000126 |

| init                 | 0.000054 |

| System lock          | 0.000033 |

| optimizing           | 0.000028 |

| statistics           | 0.000200 |

| preparing            | 0.000040 |

| Creating tmp table   | 0.000034 |

| Sorting result       | 0.000008 |

| explaining           | 0.000072 |

| query end            | 0.000013 |

| removing tmp table   | 0.000007 |

| query end            | 0.000006 |

| closing tables       | 0.000014 |

| freeing items        | 0.000996 |

| cleaning up          | 0.000078 |

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

20 rows in set, 1 warning (0.00 sec)


10.6总结及疑问

(1)官方文档说,如果optimizer_search_depth=0(If set to 0, the system automatically picks a reasonable value.),系统自动选择合理值,这个值怎么能查看到?

(2)可配置的参数optimizer_search_depth应该设置为多少才合适?不应少于join连接的表的个数?上述的测试在该值大于1时,似乎是最优的。

(3)思考:在执行一个多表join的时候,是否可以先定义一个预期,比如优化器决策join顺序的时间不能超过200ms。根据show profile for query n的值查看各执行环节的消耗时间从而做出判断?

(4)网上有人这样说:MySQL文档说明设置为0则表示能够自动选择optimizer_search_depth的合理值,实际上代码策略就是,如果join表数N<=7,则optimizer_search_depth=N+1,否则选N。没读过代码,真的是这样的吗?没测试出来,希望以后能够验证真假。

optimizer_search_depth探讨