概述

    測試MySQL查詢重寫時發(fā)現MySQL 8.0的查詢優(yōu)化器更為智能，以下實驗過程比對同一個語句在MySQL 5.7和MySQL 8.0下查詢優(yōu)化器改寫SQL差異。

實驗環(huán)境

實驗過程

    1.在兩個數據庫上根據表zh_budget創(chuàng)建帶分組查詢的視圖

        MySQL 5.7.19創(chuàng)建過程

[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)

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創(chuàng)建過程，創(chuàng)建視圖SQL語句相同，名稱用于區(qū)分版本

[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

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.相同環(huán)境，相同真實表，相同表索引，相同建視圖SQL語句，查詢效率相差40多倍，分別查看執(zhí)行計劃如下，可以看出5.7.19版本執(zhí)行計劃全表掃描，計算所有行數據，而8.0.29版本執(zhí)行計劃走idx_budget_year_month索引，計算數據行數為全量的10%數據。

        MySQL 5.7.19執(zhí)行計劃：

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     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 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執(zhí)行計劃：

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     | <derived2> | 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.造成如此大的差別，需要查看真正執(zhí)行的SQL，使用explain SQL語句 show warnings，查看查詢優(yōu)化器改寫的SQL，如下：

        MySQL 5.7.19查詢優(yōu)化器改寫后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     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 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查詢優(yōu)化器改寫后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     | <derived2> | 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查詢優(yōu)化器是在視圖的結果上再過濾year、month條件，而8.0.29查詢優(yōu)化器直接把year、month條件放入視圖內執(zhí)行，所以使用到idx_budget_year_month 索引，看起來MySQL 8.0的查詢優(yōu)化器更加智能，趕緊升版本吧！