关系型数据库视图产生派生表无法优化案例
环境:mysql 5.7/8.0
导入测试数据:
饭桶克隆https://github.com/datacharmer/test_db
激光唱片测试_db
mysql -u root -p employees.sql
员工: 300024条记录
工资:2844047条记录
1、执行一个两表关联统计SQL,执行速度非常快,整个过程扫描了122行。
关系型数据库选择变量_值进入@ a from performance _ schema。会话状态,其中VARIABLE _ name=' Innodb _ rows _ read ';
查询正常,1行受影响(0.00秒)
关系型数据库从员工e限制10中选择e.emp_no,(从薪资s中选择最高工资,其中s . EMP _ no=e . EMP _ no);
- -
| emp_no |(从薪资s中选择最大值(s。工资),其中s.emp_no=e.emp_no) |
- -
| 10001 | 88958 |
| 10002 | 72527 |
| 10003 | 43699 |
| 10004 | 74057 |
| 10005 | 94692 |
| 10006 | 60098 |
| 10007 | 88070 |
| 10008 | 52668 |
| 10009 | 94443 |
| 10010 | 80324 |
- -
10行一组(0.00秒)
关系型数据库从性能模式。会话状态中选择变量_值进入@b,其中VARIABLE _ name=' Innodb _ rows _ read ';
查询正常,1行受影响(0.00秒)
关系型数据库选择@ B- @ a;
-
| @b-@a |
-
| 122 |
-
一行一组(0.00秒)
2、将这个关联SQL,做成视图,再次查询会非常慢,实际扫描了314W行。
MySQL创建视图v _ test作为选择e . EMP _ no,(从薪资s中选择最高工资,其中s.emp_no=e.emp_no)来自员工e;
查询正常,0行受影响(0.01秒)
关系型数据库选择变量_值进入@ a from performance _ schema。会话状态,其中VARIABLE _ name=' Innodb _ rows _ read ';
查询正常,1 r
ow affected (0.00 sec)
mysql select * from v_test limit 10;
+--------+----------------------------------------------------------------+
| emp_no | (select max(s.salary) from salaries s where s.emp_no=e.emp_no) |
+--------+----------------------------------------------------------------+
| 10001 | 88958 |
| 10002 | 72527 |
| 10003 | 43699 |
| 10004 | 74057 |
| 10005 | 94692 |
| 10006 | 60098 |
| 10007 | 88070 |
| 10008 | 52668 |
| 10009 | 94443 |
| 10010 | 80324 |
+--------+----------------------------------------------------------------+
10 rows in set (1.34 sec)
mysql select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
Query OK, 1 row affected (0.00 sec)
mysql select @b-@a;
+---------+
| @b-@a |
+---------+
| 3144071 |
+---------+
1 row in set (0.00 sec)
3、分别查看执行计划
mysql explain select e.emp_no,(select max(s.salary) from salaries s where s.emp_no=e.emp_no) from employees e limit 10; +----+--------------------+-------+------------+-------+---------------+---------+---------+--------------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-------+---------------+---------+---------+--------------------+--------+----------+-------------+ | 1 | PRIMARY | e | NULL | index | NULL | PRIMARY | 4 | NULL | 299556 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | s | NULL | ref | PRIMARY | PRIMARY | 4 | employees.e.emp_no | 9 | 100.00 | NULL | +----+--------------------+-------+------------+-------+---------------+---------+---------+--------------------+--------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec) mysql explain select * from v_test limit 10; +----+--------------------+------------+------------+-------+---------------+---------+---------+--------------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+-------+---------------+---------+---------+--------------------+--------+----------+-------------+ | 1 | PRIMARY | derived2 | NULL | ALL | NULL | NULL | NULL | NULL | 299556 | 100.00 | NULL | | 2 | DERIVED | e | NULL | index | NULL | PRIMARY | 4 | NULL | 299556 | 100.00 | Using index | | 3 | DEPENDENT SUBQUERY | s | NULL | ref | PRIMARY | PRIMARY | 4 | employees.e.emp_no | 9 | 100.00 | NULL | +----+--------------------+------------+------------+-------+---------------+---------+---------+--------------------+--------+----------+-------------+ 3 rows in set, 2 warnings (0.00 sec)
4、分析执行计划:
两个执行计划中,唯一不同的是使用视图后,多了一个派生表。
关于派生表说明如下:
https://dev.mysql.com/doc/refman/5.7/en/derived-tables.html
关于派生表官方优化
https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html
由于加了limit 10;第一个 SQL 虽然显示 e 表扫描行数很多,但实际并没有进行全表扫描,只统计了前10条记录便停止了。
第二个 SQL 虽然也加了 limit 10,但因为优化器产生了派生表,也就是将统计SQL结果都写入到一个临时表中,再到这个临时表中去读10条记录。
官方虽然有派生表合并优化功能,但对于派生表中包含聚合函数,group by ,having , count ,limit 等,就无法进行优化。
目前解决这种问题,应该只有一个办法 ,就是别用视图。
内容来源网络,如有侵权,联系删除,本文地址:https://www.230890.com/zhan/67479.html