概述
測試MySQL查詢重寫時發(fā)現MySQL 8.0的查詢優(yōu)化器更為智能，以下實驗過程比對同一個語句在MySQL 5.7和MySQL 8.0下查詢優(yōu)化器改寫SQL差異。
實驗環(huán)境
| 序號 | 操作系統 | 數據庫版本 | 表數據行數 | 服務器內存 |
| 1 | CentOS 7.6 | MySQL 5.7.19 | 332237 | 4G |
| 2 | CentOS 7.6 | MySQL 8.0.29 | 332237 | 4G |
實驗過程
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)化器更加智能，趕緊升版本吧！
最后修改時間：2022-06-07 09:53:23
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創(chuàng)內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發(fā)現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發(fā)送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
