mysql optimizer mrr_模拟优化参数optimizer_switch选项mrr（Multi_Range Read）对查询优化的影响...

最新推荐文章于 2023-06-27 19:02:02 发布
最新推荐文章于 2023-06-27 19:02:02 发布
阅读量157 收藏
点赞数
文章标签： mysql optimizer mrr
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_29001423/article/details/113258951
版权

11.1背景：模拟优化参数optimizer_switch选项mrr(Multi_Range Read)对查询优化的影响

11.2建表语句

mysql> show create table order_line\G;

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

Table: order_line

Create Table: CREATE TABLE `order_line` (

`ol_o_id` int(11) NOT NULL,

`ol_d_id` tinyint(4) NOT NULL,

`ol_w_id` smallint(6) NOT NULL,

`ol_number` tinyint(4) NOT NULL,

`ol_i_id` int(11) DEFAULT NULL,

`ol_supply_w_id` smallint(6) DEFAULT NULL,

`ol_delivery_d` datetime DEFAULT NULL,

`ol_quantity` tinyint(4) DEFAULT NULL,

`ol_amount` decimal(6,2) DEFAULT NULL,

`ol_dist_info` char(24) DEFAULT NULL,

PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`),

KEY `fkey_order_line_2` (`ol_supply_w_id`,`ol_i_id`),

CONSTRAINT `fkey_order_line_1` FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`) REFERENCES `orders` (`o_w_id`, `o_d_id`, `o_id`),

CONSTRAINT `fkey_order_line_2` FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`) REFERENCES `stock` (`s_w_id`, `s_i_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

11.3查询语句

select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);

11.4示例及分析：

11.4.1源语句执行计划分析

mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);

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

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

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

|  1 | SIMPLE      | order_line | ALL  | fkey_order_line_2 | NULL | NULL    | NULL | 1701558 |    50.00 | Using where |

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

1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G;

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

Level: Note

Code: 1003

Message: /* select#1 */ select `tpcc`.`order_line`.`ol_o_id` AS `ol_o_id`,`tpcc`.`order_line`.`ol_d_id` AS `ol_d_id`,`tpcc`.`order_line`.`ol_w_id` AS `ol_w_id`,`tpcc`.`order_line`.`ol_number` AS `ol_number`,`tpcc`.`order_line`.`ol_i_id` AS `ol_i_id`,`tpcc`.`order_line`.`ol_supply_w_id` AS `ol_supply_w_id`,`tpcc`.`order_line`.`ol_delivery_d` AS `ol_delivery_d`,`tpcc`.`order_line`.`ol_quantity` AS `ol_quantity`,`tpcc`.`order_line`.`ol_amount` AS `ol_amount`,`tpcc`.`order_line`.`ol_dist_info` AS `ol_dist_info` from `tpcc`.`order_line` where ((`tpcc`.`order_line`.`ol_i_id` = 90000) and (`tpcc`.`order_line`.`ol_supply_w_id` > 0) and (`tpcc`.`order_line`.`ol_supply_w_id` < 6))

1 row in set (0.00 sec)

该语句执行了全表扫描。

11.4.2关闭mrr对执行计划的影响

注释掉快速预热缓冲池功能

#innodb_buffer_pool_dump_at_shutdown=1

#innodb_buffer_pool_dump_now=1

#innodb_buffer_pool_load_at_start=1

#innodb_buffer_pool_load_now=1

重启数据库示例

关闭mrr对数据查询的影响

mysql> set @@optimizer_switch='mrr=off,mrr_cost_based=on';

Query OK, 0 rows affected (0.00 sec)

mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);

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

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

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

|  1 | SIMPLE      | order_line | ALL  | fkey_order_line_2 | NULL | NULL    | NULL | 1638777 |    50.00 | Using where |

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

1 row in set, 1 warning (0.00 sec)

执行全表扫描。

mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);

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

| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d       | ol_quantity | ol_amount | ol_dist_info             |

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

|    2124 |       7 |       2 |         5 |   90000 |              2 | 2014-07-24 16:12:33 |           5 |      0.00 | 3dvrSdvNlCswM5astj4EBmCd |

|    2833 |      10 |       2 |         4 |   90000 |              2 | NULL                |           5 |      0.00 | jdJuW0MobORbaxEKWW5HWqK9 |

|    3268 |       2 |       3 |        11 |   90000 |              3 | NULL                |           3 |    127.62 | HklNywWs02ab8XGFID2HpjK4 |

|    2838 |       3 |       3 |         5 |   90000 |              3 | NULL                |           5 |      0.00 | DtqfRbDNgmJLH7Vn0xvk6uTS |

|     653 |       5 |       3 |        12 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     80.73 | QIBcVanwiqRhxVJPckA7KEbv |

|     909 |       5 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     74.72 | zcl2wdh3RGEdI8EhgOHhR8wT |

|    1129 |       5 |       3 |         5 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     62.02 | JtP5oa0koNxc2BPRoppNt98J |

|    1740 |       6 |       3 |        11 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |      1.16 | 5rY0GnyMEPnQsitnTO5o5Y1I |

|    1306 |       9 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     64.57 | P8pWwOmvEZKNWCWEyuxPl4Z3 |

|    1203 |      10 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     33.59 | iCDsA2a62VtLQnUBvPGSTA5E |

|    3014 |       1 |       4 |         5 |   90000 |              4 | NULL                |           8 |    551.15 | hJjs6oxAamoY5ZrhW2oboiIY |

|     840 |       5 |       4 |         9 |   90000 |              4 | 2014-07-24 15:52:26 |           5 |     55.06 | D85SGasZQaeFzFnpFOmOdtAW |

|    2444 |       7 |       4 |         7 |   90000 |              4 | NULL                |           5 |      0.00 | 5RUMFQ9PDgfMyM1kPfnJayJu |

|    2577 |       4 |       5 |         3 |   90000 |              5 | NULL                |           5 |      0.00 | 1sqa7AzfsPurzqvMHnpnGuKz |

|     620 |       6 |       5 |         6 |   90000 |              5 | 2014-07-24 15:52:26 |           5 |     72.78 | vskOqsHuimM1gd58oPxKoGA0 |

|    1247 |       8 |       5 |         9 |   90000 |              5 | 2014-07-24 15:52:26 |           5 |     89.96 | v0c9QGlqZwD9Mg9GtswM3f6q |

|    2628 |       9 |       5 |         9 |   90000 |              5 | NULL                |           5 |      0.00 | LCLh9GhbMoJo9cXNk1Rhb9aX |

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

17 rows in set (6.63 sec)

所花费时间为6.63 sec。

增加限制条件，使其使用索引。

mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1701557;

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

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

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

|  1 | SIMPLE      | order_line | range | fkey_order_line_2 | fkey_order_line_2 | 3       | NULL | 850779 |   100.00 | Using index condition |

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

1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G;

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

Level: Note

Code: 1003

Message: /* select#1 */ select `tpcc`.`order_line`.`ol_o_id` AS `ol_o_id`,`tpcc`.`order_line`.`ol_d_id` AS `ol_d_id`,`tpcc`.`order_line`.`ol_w_id` AS `ol_w_id`,`tpcc`.`order_line`.`ol_number` AS `ol_number`,`tpcc`.`order_line`.`ol_i_id` AS `ol_i_id`,`tpcc`.`order_line`.`ol_supply_w_id` AS `ol_supply_w_id`,`tpcc`.`order_line`.`ol_delivery_d` AS `ol_delivery_d`,`tpcc`.`order_line`.`ol_quantity` AS `ol_quantity`,`tpcc`.`order_line`.`ol_amount` AS `ol_amount`,`tpcc`.`order_line`.`ol_dist_info` AS `ol_dist_info` from `tpcc`.`order_line` where ((`tpcc`.`order_line`.`ol_i_id` = 90000) and (`tpcc`.`order_line`.`ol_supply_w_id` > 0) and (`tpcc`.`order_line`.`ol_supply_w_id` < 6)) limit 1500000

1 row in set (0.00 sec)

mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1701557;

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

| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d       | ol_quantity | ol_amount | ol_dist_info             |

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

|    2124 |       7 |       2 |         5 |   90000 |              2 | 2014-07-24 16:12:33 |           5 |      0.00 | 3dvrSdvNlCswM5astj4EBmCd |

|    2833 |      10 |       2 |         4 |   90000 |              2 | NULL                |           5 |      0.00 | jdJuW0MobORbaxEKWW5HWqK9 |

|    3268 |       2 |       3 |        11 |   90000 |              3 | NULL                |           3 |    127.62 | HklNywWs02ab8XGFID2HpjK4 |

|    2838 |       3 |       3 |         5 |   90000 |              3 | NULL                |           5 |      0.00 | DtqfRbDNgmJLH7Vn0xvk6uTS |

|     653 |       5 |       3 |        12 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     80.73 | QIBcVanwiqRhxVJPckA7KEbv |

|     909 |       5 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     74.72 | zcl2wdh3RGEdI8EhgOHhR8wT |

|    1129 |       5 |       3 |         5 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     62.02 | JtP5oa0koNxc2BPRoppNt98J |

|    1740 |       6 |       3 |        11 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |      1.16 | 5rY0GnyMEPnQsitnTO5o5Y1I |

|    1306 |       9 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     64.57 | P8pWwOmvEZKNWCWEyuxPl4Z3 |

|    1203 |      10 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     33.59 | iCDsA2a62VtLQnUBvPGSTA5E |

|    3014 |       1 |       4 |         5 |   90000 |              4 | NULL                |           8 |    551.15 | hJjs6oxAamoY5ZrhW2oboiIY |

|     840 |       5 |       4 |         9 |   90000 |              4 | 2014-07-24 15:52:26 |           5 |     55.06 | D85SGasZQaeFzFnpFOmOdtAW |

|    2444 |       7 |       4 |         7 |   90000 |              4 | NULL                |           5 |      0.00 | 5RUMFQ9PDgfMyM1kPfnJayJu |

|    2577 |       4 |       5 |         3 |   90000 |              5 | NULL                |           5 |      0.00 | 1sqa7AzfsPurzqvMHnpnGuKz |

|     620 |       6 |       5 |         6 |   90000 |              5 | 2014-07-24 15:52:26 |           5 |     72.78 | vskOqsHuimM1gd58oPxKoGA0 |

|    1247 |       8 |       5 |         9 |   90000 |              5 | 2014-07-24 15:52:26 |           5 |     89.96 | v0c9QGlqZwD9Mg9GtswM3f6q |

|    2628 |       9 |       5 |         9 |   90000 |              5 | NULL                |           5 |      0.00 | LCLh9GhbMoJo9cXNk1Rhb9aX |

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

17 rows in set (2.38 sec)

所花费时间为2.38sec。

11.4.2限制条件“limit n”的选择：

(1)limit n限制条件结果

mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1;

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

| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d       | ol_quantity | ol_amount | ol_dist_info             |

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

|    2124 |       7 |       2 |         5 |   90000 |              2 | 2014-07-24 16:12:33 |           5 |      0.00 | 3dvrSdvNlCswM5astj4EBmCd |

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

1 row in set (0.18 sec)

limit是对查询结果后的第一条记录的限制。

mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 2;

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

| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d       | ol_quantity | ol_amount | ol_dist_info             |

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

|    2124 |       7 |       2 |         5 |   90000 |              2 | 2014-07-24 16:12:33 |           5 |      0.00 | 3dvrSdvNlCswM5astj4EBmCd |

|    2833 |      10 |       2 |         4 |   90000 |              2 | NULL                |           5 |      0.00 | jdJuW0MobORbaxEKWW5HWqK9 |

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

2 rows in set (0.19 sec)

limit是对查询结果后的前二条记录的限制。

mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 17;

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

| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d       | ol_quantity | ol_amount | ol_dist_info             |

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

|    2124 |       7 |       2 |         5 |   90000 |              2 | 2014-07-24 16:12:33 |           5 |      0.00 | 3dvrSdvNlCswM5astj4EBmCd |

|    2833 |      10 |       2 |         4 |   90000 |              2 | NULL                |           5 |      0.00 | jdJuW0MobORbaxEKWW5HWqK9 |

|    3268 |       2 |       3 |        11 |   90000 |              3 | NULL                |           3 |    127.62 | HklNywWs02ab8XGFID2HpjK4 |

|    2838 |       3 |       3 |         5 |   90000 |              3 | NULL                |           5 |      0.00 | DtqfRbDNgmJLH7Vn0xvk6uTS |

|     653 |       5 |       3 |        12 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     80.73 | QIBcVanwiqRhxVJPckA7KEbv |

|     909 |       5 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     74.72 | zcl2wdh3RGEdI8EhgOHhR8wT |

|    1129 |       5 |       3 |         5 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     62.02 | JtP5oa0koNxc2BPRoppNt98J |

|    1740 |       6 |       3 |        11 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |      1.16 | 5rY0GnyMEPnQsitnTO5o5Y1I |

|    1306 |       9 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     64.57 | P8pWwOmvEZKNWCWEyuxPl4Z3 |

|    1203 |      10 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     33.59 | iCDsA2a62VtLQnUBvPGSTA5E |

|    3014 |       1 |       4 |         5 |   90000 |              4 | NULL                |           8 |    551.15 | hJjs6oxAamoY5ZrhW2oboiIY |

|     840 |       5 |       4 |         9 |   90000 |              4 | 2014-07-24 15:52:26 |           5 |     55.06 | D85SGasZQaeFzFnpFOmOdtAW |

|    2444 |       7 |       4 |         7 |   90000 |              4 | NULL                |           5 |      0.00 | 5RUMFQ9PDgfMyM1kPfnJayJu |

|    2577 |       4 |       5 |         3 |   90000 |              5 | NULL                |           5 |      0.00 | 1sqa7AzfsPurzqvMHnpnGuKz |

|     620 |       6 |       5 |         6 |   90000 |              5 | 2014-07-24 15:52:26 |           5 |     72.78 | vskOqsHuimM1gd58oPxKoGA0 |

|    1247 |       8 |       5 |         9 |   90000 |              5 | 2014-07-24 15:52:26 |           5 |     89.96 | v0c9QGlqZwD9Mg9GtswM3f6q |

|    2628 |       9 |       5 |         9 |   90000 |              5 | NULL                |           5 |      0.00 | LCLh9GhbMoJo9cXNk1Rhb9aX |

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

17 rows in set (0.45 sec)

mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 18;

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

| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d       | ol_quantity | ol_amount | ol_dist_info             |

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

|    2124 |       7 |       2 |         5 |   90000 |              2 | 2014-07-24 16:12:33 |           5 |      0.00 | 3dvrSdvNlCswM5astj4EBmCd |

|    2833 |      10 |       2 |         4 |   90000 |              2 | NULL                |           5 |      0.00 | jdJuW0MobORbaxEKWW5HWqK9 |

|    3268 |       2 |       3 |        11 |   90000 |              3 | NULL                |           3 |    127.62 | HklNywWs02ab8XGFID2HpjK4 |

|    2838 |       3 |       3 |         5 |   90000 |              3 | NULL                |           5 |      0.00 | DtqfRbDNgmJLH7Vn0xvk6uTS |

|     653 |       5 |       3 |        12 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     80.73 | QIBcVanwiqRhxVJPckA7KEbv |

|     909 |       5 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     74.72 | zcl2wdh3RGEdI8EhgOHhR8wT |

|    1129 |       5 |       3 |         5 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     62.02 | JtP5oa0koNxc2BPRoppNt98J |

|    1740 |       6 |       3 |        11 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |      1.16 | 5rY0GnyMEPnQsitnTO5o5Y1I |

|    1306 |       9 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     64.57 | P8pWwOmvEZKNWCWEyuxPl4Z3 |

|    1203 |      10 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     33.59 | iCDsA2a62VtLQnUBvPGSTA5E |

|    3014 |       1 |       4 |         5 |   90000 |              4 | NULL                |           8 |    551.15 | hJjs6oxAamoY5ZrhW2oboiIY |

|     840 |       5 |       4 |         9 |   90000 |              4 | 2014-07-24 15:52:26 |           5 |     55.06 | D85SGasZQaeFzFnpFOmOdtAW |

|    2444 |       7 |       4 |         7 |   90000 |              4 | NULL                |           5 |      0.00 | 5RUMFQ9PDgfMyM1kPfnJayJu |

|    2577 |       4 |       5 |         3 |   90000 |              5 | NULL                |           5 |      0.00 | 1sqa7AzfsPurzqvMHnpnGuKz |

|     620 |       6 |       5 |         6 |   90000 |              5 | 2014-07-24 15:52:26 |           5 |     72.78 | vskOqsHuimM1gd58oPxKoGA0 |

|    1247 |       8 |       5 |         9 |   90000 |              5 | 2014-07-24 15:52:26 |           5 |     89.96 | v0c9QGlqZwD9Mg9GtswM3f6q |

|    2628 |       9 |       5 |         9 |   90000 |              5 | NULL                |           5 |      0.00 | LCLh9GhbMoJo9cXNk1Rhb9aX |

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

17 rows in set (0.44 sec)

limit是对查询结果后的所有记录的限制。

(2)limit n限制条件规律探索

由此，在不知道有多少查询记录的情况下，可以使用：

mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);

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

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

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

|  1 | SIMPLE      | order_line | ALL  | fkey_order_line_2 | NULL | NULL    | NULL | 1701558 |    50.00 | Using where |

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

1 row in set, 1 warning (0.00 sec)

可以看到影响的行数为1701558，那么使用1701558看执行计划的结果：

mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1701558;

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

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

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

|  1 | SIMPLE      | order_line | ALL  | fkey_order_line_2 | NULL | NULL    | NULL | 1701558 |    50.00 | Using where |

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

1 row in set, 1 warning (0.00 sec)

使用n-1看执行计划的结果：

mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1701557;

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

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

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

|  1 | SIMPLE      | order_line | range | fkey_order_line_2 | fkey_order_line_2 | 3       | NULL | 850779 |   100.00 | Using index condition |

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

1 row in set, 1 warning (0.00 sec)

11.4.3打开mrr对数据查询的影响

重启数据库

打开mrr功能

mysql> set @@optimizer_switch='mrr=on,mrr_cost_based=off';

Query OK, 0 rows affected (0.01 sec)

mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);

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

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

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

|  1 | SIMPLE      | order_line | ALL  | fkey_order_line_2 | NULL | NULL    | NULL | 1604617 |    50.00 | Using where |

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

1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G;

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

Level: Note

Code: 1003

Message: /* select#1 */ select `tpcc`.`order_line`.`ol_o_id` AS `ol_o_id`,`tpcc`.`order_line`.`ol_d_id` AS `ol_d_id`,`tpcc`.`order_line`.`ol_w_id` AS `ol_w_id`,`tpcc`.`order_line`.`ol_number` AS `ol_number`,`tpcc`.`order_line`.`ol_i_id` AS `ol_i_id`,`tpcc`.`order_line`.`ol_supply_w_id` AS `ol_supply_w_id`,`tpcc`.`order_line`.`ol_delivery_d` AS `ol_delivery_d`,`tpcc`.`order_line`.`ol_quantity` AS `ol_quantity`,`tpcc`.`order_line`.`ol_amount` AS `ol_amount`,`tpcc`.`order_line`.`ol_dist_info` AS `ol_dist_info` from `tpcc`.`order_line` where ((`tpcc`.`order_line`.`ol_i_id` = 90000) and (`tpcc`.`order_line`.`ol_supply_w_id` > 0) and (`tpcc`.`order_line`.`ol_supply_w_id` < 6))

1 row in set (0.01 sec)

使用的是全表扫描。

mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1604617;

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

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

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

|  1 | SIMPLE      | order_line | ALL  | fkey_order_line_2 | NULL | NULL    | NULL | 1604617 |    50.00 | Using where |

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

1 row in set, 1 warning (0.01 sec)

mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1604616;

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

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

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

|  1 | SIMPLE      | order_line | range | fkey_order_line_2 | fkey_order_line_2 | 3       | NULL | 802308 |   100.00 | Using index condition; Using MRR |

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

1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G;

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

Level: Note

Code: 1003

Message: /* select#1 */ select `tpcc`.`order_line`.`ol_o_id` AS `ol_o_id`,`tpcc`.`order_line`.`ol_d_id` AS `ol_d_id`,`tpcc`.`order_line`.`ol_w_id` AS `ol_w_id`,`tpcc`.`order_line`.`ol_number` AS `ol_number`,`tpcc`.`order_line`.`ol_i_id` AS `ol_i_id`,`tpcc`.`order_line`.`ol_supply_w_id` AS `ol_supply_w_id`,`tpcc`.`order_line`.`ol_delivery_d` AS `ol_delivery_d`,`tpcc`.`order_line`.`ol_quantity` AS `ol_quantity`,`tpcc`.`order_line`.`ol_amount` AS `ol_amount`,`tpcc`.`order_line`.`ol_dist_info` AS `ol_dist_info` from `tpcc`.`order_line` where ((`tpcc`.`order_line`.`ol_i_id` = 90000) and (`tpcc`.`order_line`.`ol_supply_w_id` > 0) and (`tpcc`.`order_line`.`ol_supply_w_id` < 6)) limit 1604616

1 row in set (0.00 sec)

可以看到使用了索引和mrr优化参数

mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1604616;

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

| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d       | ol_quantity | ol_amount | ol_dist_info             |

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

|    2124 |       7 |       2 |         5 |   90000 |              2 | 2014-07-24 16:12:33 |           5 |      0.00 | 3dvrSdvNlCswM5astj4EBmCd |

|    2833 |      10 |       2 |         4 |   90000 |              2 | NULL                |           5 |      0.00 | jdJuW0MobORbaxEKWW5HWqK9 |

|    3268 |       2 |       3 |        11 |   90000 |              3 | NULL                |           3 |    127.62 | HklNywWs02ab8XGFID2HpjK4 |

|    2838 |       3 |       3 |         5 |   90000 |              3 | NULL                |           5 |      0.00 | DtqfRbDNgmJLH7Vn0xvk6uTS |

|     653 |       5 |       3 |        12 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     80.73 | QIBcVanwiqRhxVJPckA7KEbv |

|     909 |       5 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     74.72 | zcl2wdh3RGEdI8EhgOHhR8wT |

|    1129 |       5 |       3 |         5 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     62.02 | JtP5oa0koNxc2BPRoppNt98J |

|    1740 |       6 |       3 |        11 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |      1.16 | 5rY0GnyMEPnQsitnTO5o5Y1I |

|    1306 |       9 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     64.57 | P8pWwOmvEZKNWCWEyuxPl4Z3 |

|    1203 |      10 |       3 |         1 |   90000 |              3 | 2014-07-24 15:52:26 |           5 |     33.59 | iCDsA2a62VtLQnUBvPGSTA5E |

|    3014 |       1 |       4 |         5 |   90000 |              4 | NULL                |           8 |    551.15 | hJjs6oxAamoY5ZrhW2oboiIY |

|     840 |       5 |       4 |         9 |   90000 |              4 | 2014-07-24 15:52:26 |           5 |     55.06 | D85SGasZQaeFzFnpFOmOdtAW |

|    2444 |       7 |       4 |         7 |   90000 |              4 | NULL                |           5 |      0.00 | 5RUMFQ9PDgfMyM1kPfnJayJu |

|    2577 |       4 |       5 |         3 |   90000 |              5 | NULL                |           5 |      0.00 | 1sqa7AzfsPurzqvMHnpnGuKz |

|     620 |       6 |       5 |         6 |   90000 |              5 | 2014-07-24 15:52:26 |           5 |     72.78 | vskOqsHuimM1gd58oPxKoGA0 |

|    1247 |       8 |       5 |         9 |   90000 |              5 | 2014-07-24 15:52:26 |           5 |     89.96 | v0c9QGlqZwD9Mg9GtswM3f6q |

|    2628 |       9 |       5 |         9 |   90000 |              5 | NULL                |           5 |      0.00 | LCLh9GhbMoJo9cXNk1Rhb9aX |

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

17 rows in set (0.44 sec)

所花费时间为0.94sec，比没有启用mrr缩短了近2sec，原先的是2.38sec。

11.5总结

(1)在索引的选择率影响索引的选择时，增加limit限制，使其使用索引，从而提高查询效率；

(2)limit n中的n的选择还有待进一步验证和探讨；

(3)MRR的使用的优化目的是减少磁盘的随机访问，其前提是使用索引，找到所需的行，并按照主键进行排序，并将随机访问转换为较为顺序的数据访问，其优化使用于range、ref和eq_ref类型的查询，也就是说非这些类型的查询，优化也就失去了作用：

mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);

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

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

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

|  1 | SIMPLE      | order_line | ALL  | fkey_order_line_2 | NULL | NULL    | NULL | 1604617 |    50.00 | Using where |

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

1 row in set, 1 warning (0.00 sec)

可以看到，仍然是全表扫描。

吕欲知
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql optimizer_mysql 参数optimizer_switch
weixin_39835178的博客
01-20 513
mysql 5.1中开始引入optimizer_switch, 控制mysql优化器行为。他有一些结果集，通过on和off控制开启和关闭优化器行为。使用有效期全局和会话两个级别，在5.5中optimizer_swtich 可取结果如下，不同mysql版本可取结果不同。5.1和5.6参考官方文档。mysql> select @@optimizer_switch;+---------------...
mysql 参数optimizer_switch
aeoluspu的专栏
06-18 6607
mysql 5.1中开始引入optimizer_switch, 控制mysql优化器行为。他有一些结果集，通过on和off控制开启和关闭优化器行为。使用有效期全局和会话两个级别，在5.5中optimizer_swtich 可取结果如下，不同mysql版本可取结果不同。5.1和5.6参考官方文档。 mysql> select @@optimizer_switch; +--------------
【MySQL】控制MySQL优化器行为方法之optimizer_switch系统变量
SQLplusDB的小院
06-24 1649
本文将介绍如何通过系统变量optimizer_switch控制优化器功能。
mysql optimizer组件_mysql optimizer_switch
weixin_33146437的博客
02-04 282
参考:https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html一 mysql 优化器常用选项mysql> SELECT @@optimizer_switch\G*************************** 1. row ***************************@@optimizer_sw...
MySQL 常用配置变量- optimizer_switch
java_w的专栏
12-20 2151
控制查询优化器优化行为的参数（>=5.6.9 版本） optimizer_switch 系统变量允许控制优化器行为。 此变量的值是一组标签，每个标签（子选项）具有 on 或 off 值，以指示相应的优化程序行为是启用还是禁用。 该参数的各个子选项之间没有顺序的限制。 该参数有众多子选项，全局，会话，动态变量，set 类型，全局默认值可以在服务器启动时设置，默认值为：index_...
PyTorch中model.zero_grad()和optimizer.zero_grad()用法
09-16
因此，无论你是使用 `model.zero_grad()` 还是 `optimizer.zero_grad()`，只要优化器已经包含了模型的所有参数，效果都是一样的。 3. **反向传播与梯度计算**： 在前向传播过程中，我们通过模型处理输入数据并计算...
glsl-optimizer-master.zip_glsl 算法_glsl 算法优化_glsl_optimizer js_g
09-23
GLSL-Optimizer是一个开源项目，用于对GLSL代码进行优化，提高其性能并减少潜在错误。 标题中提到的"glsl-optimizer-master.zip"是一个包含GLSL优化器源代码的压缩包，主要由C++语言编写。这个项目对于学习计算数学...
Grey Wolf Optimizer Toolbox.zip_SVM 寻优_Toolbox 优化_wolf_灰狼SVM_灰狼
07-14
代码可能包括预定义的GWO函数，用户可以通过调用这些函数，结合自己的SVM模型，实现对超参数的优化。此外，可能还包含了一些示例或者教程，帮助用户理解如何将工具箱与SVM结合，以及如何解读和分析优化结果。 在...
pso.zip_PSO_PSo- optimizer_in_swarm
09-24
Particle swarm optimizer, in matlab
New-folder.rar_Ant Lion_Ant lion optimizer_lion_lion algorithm
07-14
标题 "New-folder.rar_Ant Lion_Ant lion optimizer_lion_lion algorithm" 提及的核心概念是“蚁狮优化器”（Ant Lion Optimizer, ALO），这是一款基于生物行为的优化算法，灵感来源于蚁狮捕食的行为。蚁狮优化器在...
mysql optimizer mrr_mysql8 参考手册--optimizer_switch优化
weixin_28939623的博客
02-01 643
使用optimizer_switch系统变量可以控制优化程序的行为。它的值是一组标志，每个标志的值都为on 或off指示相应的优化器行为是启用还是禁用。此变量具有全局值和会话值，可以在运行时更改。可以在服务器启动时设置全局默认值。要查看当前的优化器标志集，请选择变量值：mysql> SELECT @@optimizer_switch\G*************************** ...
2023-06-27 mysql-optimizer_switch参数derived_merge-分析
最新发布
技术之路
06-27 449
在分析包含内连接的嵌套外连接的过程中, 发现optimizer_switch参数derived_merge控制多个表连接是否使用派生表, 本文对其做分析.
MySQL优化器功能开关optimizer_switch之功能概览
oraly的博客
05-02 5128
MySQL 8.0新增特性use_invisible_indexes 是否使用不可见索引，MySQL 8.0新增可以创建invisible索引，这一开关控制优化器是否使用invisible索引，on表示考虑使用。MySQL 5.7新增derived_merge 派生表合并，类似Oracle的视图合并，当派生SQL中存在以下操作是无法展开UNION 、GROUP 、DISTINCT、LIMIT及聚合...
mysql optimizer mrr_mysql 索引优化特性之MRR
weixin_30133813的博客
01-19 154
MRR针对于辅助索引上的范围查询进行优化,收集辅助索引对应主键rowid。进行排序后回表查询，随机IO转顺序IO当我们需要对大表(基于辅助索引)进行范围扫描时,会导致产生许多随机/O。而对于普通磁盘来说,随机的性能很差,会遇到瓶颈,在 MySQL 5.6/5.7和MariaDB5.3/5.5/10.0/10.1版本里对这种情况进行了优化,一个新的名词 Multi Range Read(MRR)出现...
MySQL8.0 优化器介绍（一）
GreatSQL2021的博客
03-29 204
线上，遇到一些sql性能问题，需要手术刀级别的调优。optimizer_trace是一个极好的工具，已经有很多资料介绍optimizer_trace怎么使用与阅读。有必要再介绍一下我们平时不太能注意到，但是又对sql性能起着绝对作用的优化器。优化器是啥？在sql整个生命周期里处于什么样的位置，起到什么样的作用，cmu15445 课程(https://15445.courses.cs.cmu.edu/fall2022/notes/14-optimization.pdf)中对此有一些直观的描述。
Mysql的新特性--MRR
stay hungry ! stay foolish!
05-31 812
MRR的全称是Multi-Range Read Optimization，是优化器将随机IO转化为顺序IO以降低查询过程中IO开销的一种手段。 MRR原理 执行一个范围查询： mysql > explain select * from stu where age between 10 and 20; +----+-------------+-------+-------+------+---------+------+------+-------------------...
MySQL(InnoDB剖析):27---B+树索引的使用（联合索引、覆盖索引、优化器不使用索引的情况、索引提示、MRR优化、ICP优化）
董哥的黑板报
02-10 659
前言 在了解了B+树索引的本质和实现后，下一个需要考虑的问题是怎样正确地使用B+树索引，这不是一个简单的问题。这里所总结的可能并不适用于所有的应用场合。我所能做的只是概括一个大概的方向。在实际的生产环境使用中，每个DBA和开发人员，还是需要根据自己的具体生产环境来使用索引，并观察索引使用的情况，判断是否需要添加索引。不要盲从任何人给你的经验意见 根据前面的介绍，用户已经知道数据库中存在两种类型......
由optimizer_switch所引起的诡异问题
Joy0921的博客
06-08 560
一、参数描述MySQL中不同的版本优化器会有很多新特性，比如MRR、BKA等，其中optimizer_switch这个参数就是控制查询优化器怎样使用这些特性。很多情况下我们会根据自身的需求去设置optimizer_switch满足我们的需求。前段时间客户的环境中遇到一个奇怪的问题，select count(*)显示返回是有数据，但select *返回是空结果集，最终的原因就是因为optimizer...
mysql优化器关闭_MySQL优化器功能开关optimizer_switch
06-07
MySQL的优化器功能开关optimizer_switch可以用来控制优化器的一些特性和行为。默认情况下，所有的优化器特性都是开启的，但是在某些情况下，关闭某些特性可以提高查询性能。 例如，如果你的查询中使用了大量的子...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值