mysql 8.0查询优化器更智能 -m6米乐安卓版下载

概述

    测试mysql查询重写时发现mysql 8.0的查询优化器更为智能，以下实验过程比对同一个语句在mysql 5.7和mysql 8.0下查询优化器改写sql差异。

实验环境

实验过程

    1.在两个数据库上根据表zh_budget创建带分组查询的视图

        mysql 5.7.19创建过程

[root@node6 ~]# mysql -u root -p
enter password: 
welcome to the mysql monitor.  commands end with ; or \g.
your mysql connection id is 61
server version: 5.7.19 mysql community server (gpl)
米乐app官网下载 copyright (c) 2000, 2017, oracle and/or its affiliates. all rights reserved.
oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.
type 'help;' or '\h' for help. type '\c' to clear the current input statement.
mysql> use platform
reading table information for completion of table and column names
you can turn off this feature to get a quicker startup with -a
database changed
mysql> select count(*) from zh_budget;
 ---------- 
| count(*) |
 ---------- 
|   332237 |
 ---------- 
1 row in set (0.05 sec)

mysql> select version();
 ----------- 
| version() |
 ----------- 
| 5.7.19    |
 ----------- 
1 row in set (0.00 sec)

mysql> create view `v_test5.7` as select cu_id,
    ->    name,
    ->    year,
    ->    month,
    ->    sum( if ( ( type = 1 ), base, 0 ) ) as yl_base,
    ->    sum( if ( ( type = 2 ), base, 0 ) ) as yiliao_base,
    ->    sum( if ( ( type = 3 ), base, 0 ) ) as shy_base,
    ->    sum( if ( ( type = 4 ), base, 0 ) ) as gs_base,
    ->    sum( if ( ( type = 5 ), base, 0 ) ) as sy_base,
    ->    sum( if ( ( type = 6 ), base, 0 ) ) as gjj_base,
    ->    sum( if ( ( type = 1 ), dw_amount, 0 ) ) as yl_dw,
    ->    sum( if ( ( type = 1 ), gr_amount, 0 ) ) as yl_gr,
    ->    sum( if ( ( type = 2 ), dw_amount, 0 ) ) as yiliao_dw,
    ->    sum( if ( ( type = 2 ), gr_amount, 0 ) ) as yiliao_gr,
    ->    sum( if ( ( type = 3 ), dw_amount, 0 ) ) as shy_dw,
    ->    sum( if ( ( type = 3 ), gr_amount, 0 ) ) as shy_gr,
    ->    sum( if ( ( type = 4 ), dw_amount, 0 ) ) as gs_dw,
    ->    sum( if ( ( type = 4 ), gr_amount, 0 ) ) as gs_gr,
    ->    sum( if ( ( type = 5 ), dw_amount, 0 ) ) as sy_dw,
    ->    sum( if ( ( type = 5 ), gr_amount, 0 ) ) as sy_gr,
    ->    sum( if ( ( type = 6 ), dw_amount, 0 ) ) as gjj_dw,
    ->    sum( if ( ( type = 6 ), gr_amount, 0 ) ) as gjj_gr,
    ->    sum( dw_db ) as dw_db,
    ->    sum( gr_db ) as gr_db,
    ->    sum( dw_amount ) as dw_amount,
    ->    sum( gr_amount ) as gr_amount,
    ->    sum((( ( dw_amount  gr_amount )  ifnull( bj_amount, 0 ) )  ifnull( lx_amount, 0 ) )) as total_amount
    ->  from zh_budget 
    -> group by  cu_id,name,year,month;
query ok, 0 rows affected (0.00 sec)
mysql> show tables like 'v_test%';
 ------------------------------ 
| tables_in_platform (v_test%) |
 ------------------------------ 
| v_test5.7                    |
 ------------------------------ 
1 row in set (0.00 sec)

        mysql 8.0.29创建过程，创建视图sql语句相同，名称用于区分版本

[root@node5 ~]# mysql -u root -p
enter password: 
welcome to the mysql monitor.  commands end with ; or \g.
your mysql connection id is 11166
server version: 8.0.29 mysql community server - gpl
米乐app官网下载 copyright (c) 2000, 2022, oracle and/or its affiliates.
oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.
type 'help;' or '\h' for help. type '\c' to clear the current input statement.
mysql> use platform
reading table information for completion of table and column names
you can turn off this feature to get a quicker startup with -a
database changed
mysql> select version();
 ----------- 
| version() |
 ----------- 
| 8.0.29    |
 ----------- 
1 row in set (0.00 sec)

mysql> select count(*) from zh_budget;
 ---------- 
| count(*) |
 ---------- 
|   332237 |
 ---------- 
1 row in set (0.11 sec)

mysql> create view `v_test8.0` as select cu_id,
    ->    name,
    ->    year,
    ->    month,
    ->    sum( if ( ( type = 1 ), base, 0 ) ) as yl_base,
    ->    sum( if ( ( type = 2 ), base, 0 ) ) as yiliao_base,
    ->    sum( if ( ( type = 3 ), base, 0 ) ) as shy_base,
    ->    sum( if ( ( type = 4 ), base, 0 ) ) as gs_base,
    ->    sum( if ( ( type = 5 ), base, 0 ) ) as sy_base,
    ->    sum( if ( ( type = 6 ), base, 0 ) ) as gjj_base,
    ->    sum( if ( ( type = 1 ), dw_amount, 0 ) ) as yl_dw,
    ->    sum( if ( ( type = 1 ), gr_amount, 0 ) ) as yl_gr,
    ->    sum( if ( ( type = 2 ), dw_amount, 0 ) ) as yiliao_dw,
    ->    sum( if ( ( type = 2 ), gr_amount, 0 ) ) as yiliao_gr,
    ->    sum( if ( ( type = 3 ), dw_amount, 0 ) ) as shy_dw,
    ->    sum( if ( ( type = 3 ), gr_amount, 0 ) ) as shy_gr,
    ->    sum( if ( ( type = 4 ), dw_amount, 0 ) ) as gs_dw,
    ->    sum( if ( ( type = 4 ), gr_amount, 0 ) ) as gs_gr,
    ->    sum( if ( ( type = 5 ), dw_amount, 0 ) ) as sy_dw,
    ->    sum( if ( ( type = 5 ), gr_amount, 0 ) ) as sy_gr,
    ->    sum( if ( ( type = 6 ), dw_amount, 0 ) ) as gjj_dw,
    ->    sum( if ( ( type = 6 ), gr_amount, 0 ) ) as gjj_gr,
    ->    sum( dw_db ) as dw_db,
    ->    sum( gr_db ) as gr_db,
    ->    sum( dw_amount ) as dw_amount,
    ->    sum( gr_amount ) as gr_amount,
    ->    sum((( ( dw_amount  gr_amount )  ifnull( bj_amount, 0 ) )  ifnull( lx_amount, 0 ) )) as total_amount
    ->  from zh_budget 
    -> group by  cu_id,name,year,month;
query ok, 0 rows affected (0.01 sec)
mysql> show tables like 'v_test%';
 ------------------------------ 
| tables_in_platform (v_test%) |
 ------------------------------ 
| v_test8.0                    |
 ------------------------------ 
1 row in set (0.00 sec)

    2.查看两个表上索引情况，索引完全相同

        mysql 5.7.19如下：

mysql> select version();
 ----------- 
| version() |
 ----------- 
| 5.7.19    |
 ----------- 
1 row in set (0.00 sec)
mysql> show index from zh_budget;
 ----------- ------------ ----------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| table     | non_unique | key_name              | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment |
 ----------- ------------ ----------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| zh_budget |          0 | primary               |            1 | id          | a         |      330152 |     null | null   |      | btree      |         |               |
| zh_budget |          1 | idx_budget_year_month |            1 | year        | a         |           1 |     null | null   | yes  | btree      |         |               |
| zh_budget |          1 | idx_budget_year_month |            2 | month       | a         |          18 |     null | null   | yes  | btree      |         |               |
| zh_budget |          1 | idx_budget_emid       |            1 | em_id       | a         |        6304 |     null | null   | yes  | btree      |         |               |
| zh_budget |          1 | idx_budget_cuid       |            1 | cu_id       | a         |         353 |     null | null   | yes  | btree      |         |               |
 ----------- ------------ ----------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
5 rows in set (0.00 sec)

        mysql 8.0.29如下：

mysql> select version();
 ----------- 
| version() |
 ----------- 
| 8.0.29    |
 ----------- 
1 row in set (0.00 sec)
mysql> show index from zh_budget;
 ----------- ------------ ----------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------ 
| table     | non_unique | key_name              | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment | visible | expression |
 ----------- ------------ ----------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------ 
| zh_budget |          0 | primary               |            1 | id          | a         |      313637 |     null |   null |      | btree      |         |               | yes     | null       |
| zh_budget |          1 | idx_budget_year_month |            1 | year        | a         |           1 |     null |   null | yes  | btree      |         |               | yes     | null       |
| zh_budget |          1 | idx_budget_year_month |            2 | month       | a         |          18 |     null |   null | yes  | btree      |         |               | yes     | null       |
| zh_budget |          1 | idx_budget_emid       |            1 | em_id       | a         |        6566 |     null |   null | yes  | btree      |         |               | yes     | null       |
| zh_budget |          1 | idx_budget_cuid       |            1 | cu_id       | a         |         332 |     null |   null | yes  | btree      |         |               | yes     | null       |
 ----------- ------------ ----------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------ 
5 rows in set (0.01 sec)

3.根据以上索引，我们以年和月为条件查询视图，希望真实表查询走idx_budget_year_month索引

        mysql 5.7.19查询耗时7.17秒，结果如下：

mysql>  select count(*) from `v_test5.7` where year='2022' and month='05';
 ---------- 
| count(*) |
 ---------- 
|     3815 |
 ---------- 
1 row in set (7.17 sec)

        mysql 8.0.29查询0.17秒，结果如下：

mysql> select count(*) from `v_test8.0` where year='2022' and month='05';
 ---------- 
| count(*) |
 ---------- 
|     3815 |
 ---------- 
1 row in set (0.17 sec)

    4.相同环境，相同真实表，相同表索引，相同建视图sql语句，查询效率相差40多倍，分别查看执行计划如下，可以看出5.7.19版本执行计划全表扫描，计算所有行数据，而8.0.29版本执行计划走idx_budget_year_month索引，计算数据行数为全量的10%数据。

        mysql 5.7.19执行计划：

mysql> explain select count(*) from `v_test5.7` where year='2022' and month='05';
 ---- ------------- ------------ ------------ ------ --------------- ------------- --------- ------------- -------- ---------- --------------------------------- 
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref         | rows   | filtered | extra                           |
 ---- ------------- ------------ ------------ ------ --------------- ------------- --------- ------------- -------- ---------- --------------------------------- 
|1   | primary     |  | null       | ref  |    | 0> | 30      | const,const |     10 |   100.00 | null                            |
|  2 | derived     | zh_budget  | null       | all  | null          | null        | null    | null        | 330152 |   100.00 | using temporary; using filesort |
 ---- ------------- ------------ ------------ ------ --------------- ------------- --------- ------------- -------- ---------- --------------------------------- 
2 rows in set, 1 warning (0.00 sec)

        mysql 8.0.29执行计划：

mysql> explain select count(*) from `v_test8.0` where year='2022' and month='05';
 ---- ------------- ------------ ------------ ------ ----------------------- ----------------------- --------- ------------- ------- ---------- ----------------- 
| id | select_type | table      | partitions | type | possible_keys         | key                   | key_len | ref         | rows  | filtered | extra           |
 ---- ------------- ------------ ------------ ------ ----------------------- ----------------------- --------- ------------- ------- ---------- ----------------- 
|1   | primary     |  | null       | all  | null                  | null                  | null    | null        | 32934 |   100.00 | null            |
|  2 | derived     | zh_budget  | null       | ref  | idx_budget_year_month | idx_budget_year_month | 30      | const,const | 32934 |   100.00 | using temporary |
 ---- ------------- ------------ ------------ ------ ----------------------- ----------------------- --------- ------------- ------- ---------- ----------------- 
2 rows in set, 1 warning (0.00 sec)

    5.造成如此大的差别，需要查看真正执行的sql，使用explain sql语句 show warnings，查看查询优化器改写的sql，如下：

        mysql 5.7.19查询优化器改写后sql语句：

mysql> explain select count(*) from `v_test5.7` where year='2022' and month='05';
 ---- ------------- ------------ ------------ ------ --------------- ------------- --------- ------------- -------- ---------- --------------------------------- 
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref         | rows   | filtered | extra                           |
 ---- ------------- ------------ ------------ ------ --------------- ------------- --------- ------------- -------- ---------- --------------------------------- 
|  1 | primary     |  | null       | ref  |    | 0> | 30      | const,const |     10 |   100.00 | null                            |
|  2 | derived     | zh_budget  | null       | all  | null          | null        | null    | null        | 330152 |   100.00 | using temporary; using filesort |
 ---- ------------- ------------ ------------ ------ --------------- ------------- --------- ------------- -------- ---------- --------------------------------- 
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings\g
*************************** 1. row ***************************
  level: note
   code: 1003
message: /* select#1 */ select count(0) as `count(*)` from `platform`.`v_test5.7` where ((`v_test5.7`.`year` = '2022') and (`v_test5.7`.`month` = '05'))
1 row in set (0.00 sec)

        mysql 8.0.29查询优化器改写后sql语句：

mysql> explain select count(*) from `v_test8.0` where year='2022' and month='05';
 ---- ------------- ------------ ------------ ------ ----------------------- ----------------------- --------- ------------- ------- ---------- ----------------- 
| id | select_type | table      | partitions | type | possible_keys         | key                   | key_len | ref         | rows  | filtered | extra           |
 ---- ------------- ------------ ------------ ------ ----------------------- ----------------------- --------- ------------- ------- ---------- ----------------- 
|  1 | primary     |  | null       | all  | null                  | null                  | null    | null        | 32934 |   100.00 | null            |
|  2 | derived     | zh_budget  | null       | ref  | idx_budget_year_month | idx_budget_year_month | 30      | const,const | 32934 |   100.00 | using temporary |
 ---- ------------- ------------ ------------ ------ ----------------------- ----------------------- --------- ------------- ------- ---------- ----------------- 
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings\g
*************************** 1. row ***************************
  level: note
   code: 1003
message: /* select#1 */ select count(0) as `count(*)` from `platform`.`v_test8.0`
1 row in set (0.00 sec)

总结    

    以上实验可以看出5.7.19查询优化器是在视图的结果上再过滤year、month条件，而8.0.29查询优化器直接把year、month条件放入视图内执行，所以使用到idx_budget_year_month 索引，看起来mysql 8.0的查询优化器更加智能，赶紧升版本吧！