首页 > 代码库 > 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探讨