MySQL 8.0查询优化器更智能

原创键盘丐 2022-06-02

2156

概述

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

实验环境

序号	操作系统	数据库版本	表数据行数	服务器内存
1	CentOS 7.6	MySQL 5.7.19	332237	4G
2	CentOS 7.6	MySQL 8.0.29	332237	4G

实验过程

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)

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

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     | <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执行计划：

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.造成如此大的差别，需要查看真正执行的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     | <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查询优化器改写后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查询优化器是在视图的结果上再过滤year、month条件，而8.0.29查询优化器直接把year、month条件放入视图内执行，所以使用到idx_budget_year_month 索引，看起来MySQL 8.0的查询优化器更加智能，赶紧升版本吧！

墨力计划 mysql

最后修改时间：2022-06-07 09:53:23

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

MySQL 8.0查询优化器更智能

评论