红阳建设集团网站,肥城市区seo关键词排名,广州网站建设建航科技,中国建设监理协会官方网站Optimizer_switch变量是支持对优化器行为的控制。是一组值标志#xff0c;每个标志都有一个on或off的值#xff0c;以指示是否启用或禁用相应的行为。
MySQL8.0里除了熟悉的hash join重大变化之外#xff0c;其他方面也有优化。
mysql SHOW VARIABLES LIKE OPTIMIZER_…Optimizer_switch变量是支持对优化器行为的控制。是一组值标志每个标志都有一个on或off的值以指示是否启用或禁用相应的行为。
MySQL8.0里除了熟悉的hash join重大变化之外其他方面也有优化。
mysql SHOW VARIABLES LIKE OPTIMIZER_SWITCH\G;
*************************** 1. row ***************************
Variable_name: optimizer_switchValue: index_mergeon,index_merge_unionon,index_merge_sort_unionon,
index_merge_intersectionon,engine_condition_pushdownon,index_condition_pushdownon,
mrron,mrr_cost_basedon,block_nested_loopon,batched_key_accessoff,
materializationon,semijoinon,loosescanon,firstmatchon,
duplicateweedouton,subquery_materialization_cost_basedon,
use_index_extensionson,condition_fanout_filteron,derived_mergeon,##下面是目前5.7 Vs 8.0差异内容##
use_invisible_indexesoff,skip_scanon,hash_joinon,
subquery_to_derivedoff,prefer_ordering_indexon,
hypergraph_optimizeroff,derived_condition_pushdownon
use_invisible_indexes
MySQL8.0 开始支持隐藏索引有时往往因为索引不合理执行计划太差需要大动干戈现在不需要进行破坏性的更改优化器方面配合是否使用不可见索引来构建查询执行计划.默认是关闭。当然这里主键以外。
#索引age隐藏
mysql ALTER TABLE members ALTER INDEX idx_age INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0#执行计划全表扫描
mysql EXPLAIN select *from members where age 10;
-------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | members | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
-------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)#优化器可用隐藏索引打开。
mysql SET SESSION optimizer_switchuse_invisible_indexeson;
Query OK, 0 rows affected (0.00 sec)#执行计划使用age 隐藏索引
mysql EXPLAIN select *from members where age 10;
---------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | members | NULL | range | idx_age | idx_age | 5 | NULL | 3 | 100.00 | Using index condition |
---------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)
备注 在一些业务无法及时改变SQL语句的时可以通过更改索引隐藏属性有效控制手段。 特别是生产不确定索引性能影响的时候可以在线操作算是应急对策。
prefer_ordering_index
MySQL里对任何有LIMIT子句的ORDER BY或GROUP BY查询使用有序索引覆盖优化器所做的任何其他选择只要它确定这会导致更快的执行。因此做出这种判断的算法对数据分布和其他条件做出了某些假设所以它可能并不总是完全正确而且在某些情况下此类查询选择不同的优化可能会提供更好的性能。 8.0.21版本中optimizer_switch变量的prefer_ordering_index设置来禁用这种优化算法。
optimizer_switch参数中的prefer_ordering_index进行控制对于order by limit的优化。prefer_ordering_index默认打开表示MySQL会优先考虑通过order by列索引进行排序优化。
目前验证发现只在范围查询中有算法干扰。所以使用范围查询可要小心了。
#模拟表结构和数据
mysqlCREATE table members
(
id int unsigned NOT NULL AUTO_INCREMENT ,
first_name varchar(100) DEFAULT NULL ,
last_name varchar(100) DEFAULT NULL ,
age INT DEFAULT 0 ,
create_time timestamp DEFAULT CURRENT_TIMESTAMP,
update_time timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp(),
primary KEY (id) ,
KEY idx_first_last(first_name(3),last_name(3)),
KEY idx_age(age)
);
mysqlINSERT INTO members(id,first_name,last_name,age) VALUES(1,AAAAA,BBBBB,12),(2,AAAAA,CCCCC,20),(3,DDDDD,EEEEE,30);#排序优先开启
mysqlSET optimizer_switch prefer_ordering_indexon;#优先ORDER BY 和 LIMIT 选择主键
mysql EXPLAIN SELECT * FROM members WHERE age 15 order by id DESC LIMIT 1;
--------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
--------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | members | NULL | index | idx_age | PRIMARY | 4 | NULL | 1 | 66.67 | Using where; Backward index scan |
--------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)#排序优先关闭
mysql SET optimizer_switch prefer_ordering_indexoff;
Query OK, 0 rows affected (0.00 sec)#选择合理执行计划
mysql EXPLAIN SELECT * FROM members WHERE age 15 order by id DESC LIMIT 1;
-------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | members | NULL | range | idx_age | idx_age | 5 | NULL | 2 | 100.00 | Using index condition; Using filesort |
-------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)subquery_to_derived
MySQL 8.0.21开始优化器增加了subquery_to_derived默认关闭选项。优化器在许多情况下能够将SELECT、WHERE、JOIN或HAVING子句中的标量子查询转换为派生表上的左外部连接在某些情况下是内连接。 所为的
subquery: 包含在 SELECT 中的子查询(不在 FROM子句中)。derived : 包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中 也称为派生表(derived).
在大多数情况下启用此优化不会产生任何明显的性能改进(在许多情况下甚至会使查询运行得更慢)视情况而定所以选择默认值即可。
SHOW WARNINGS信息下可以看到语句改写情况
mysql SET SESSION optimizer_switchsubquery_to_derivedOFF;
Query OK, 0 rows affected (0.00 sec)mysql EXPLAIN SELECT * FROM t1 WHERE t1.a (SELECT COUNT(a) FROM t2);
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 33.33 | Using where |
| 2 | SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.00 sec)mysql SHOW WARNINGS;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Note | 1003 | /* select#1 */ select test.t1.a AS a from test.t1
where (test.t1.a (/* select#2 */ select count(test.t2.a) from test.t2)) |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql SET SESSION optimizer_switchsubquery_to_derivedON;
Query OK, 0 rows affected (0.00 sec)mysql EXPLAIN SELECT * FROM t1 WHERE t1.a (SELECT COUNT(a) FROM t2);
-----------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | derived2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 33.33 | Using where; Using join buffer (hash join) |
| 2 | DERIVED | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set, 1 warning (0.00 sec)mysql SHOW WARNINGS;;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Note | 1003 | /* select#1 */ select test.t1.a AS a
from test.t1 join (
/* select#2 */ select count(test.t2.a) AS COUNT(a)
from test.t2) derived_1_2
where (test.t1.a derived_1_2.COUNT(a)) |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
derived_condition_pushdown
Mysql 8.0.22之后的版本支持派生条件回移(Derived Condition Pushdown)优化。该优化可以减少derived派生表处理的行数从而提高查询执行的效率。
比方如下查询
SELECT * FROM
(SELECT i, j FROM t1) AS dt
WHERE i constant# ↓ ↓ ↓ 通过派生条件回移优化后类似如下形式WHERE条件拿到派生表的里面SELECT * FROM (SELECT i, j FROM t1 WHERE i constant)
就是说 将外部WHERE条件下推到派生表中应该会减少需要处理的行数从而加快查询的执行速度。
MySQL 8.0.29及以后版本中派生表条件下推优化可以用于UNION查询。 不能下推情况内部表包含子查询的条件公共表表达式LIMIT子句变量的赋值 等场景。
skip_scan
MySQL从8.0.13版本开始支持一种新的range scan方式。就是说 组合索引(f1,f2)的时候查询条件里 只有f2的时候 也可以实现索引扫描的功能.范围扫描比全索引扫描更有效优化器可以执行多个范围扫描每个值对应一个f1使用一种称为Skip Scan的方法类似于松散索引扫描。
从上述描述可以看到使用skip-scan的方式避免了全索引扫描从而提升了性能尤其是在索引前缀列区分度比较低的时候。可以说 避开了索引前缀原则。
mysql ALTER TABLE t1 ADD PRIMARY KEY(f1, f2);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 40;;
------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 53 | 100.00
| Using where; Using index for skip scan |
------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)
hypergraph_optimizer
MySQL 8.0.23提供了优化器支持hypergraph超图的模型。目前实际还不支持需要在debug下进行验证测试是不成熟的优化方案。
hypergraph是数学中用的概念。在数据算法中描述对象之间建立联系而用的。个人理解就是通过边界算法把对应的数据关联点范围等圈出来。
由此多表关联下从5.7的nest loop 进化到8.0的hash join在hash join进一步提升效率的的算法。这个算法非常切合BTree配合使用。 mysql SET SESSION optimizer_switchhypergraph_optimizerON;
ERROR 3999 (42000): The hypergraph optimizer does not yet support use in non-debug builds
总结
优化器行为变更可能会导致不同版本SQL语句性能体现不一样。当然变化是往好的方面延伸的但对于复杂的情况可能也会出现退化效果。所以版本升级的时候需要特别关注下。
在这里特别感兴趣hypergraph超图优化方案。因为MySQL对于多表关联是一个致命的问题关联表数量越多性能越差。对于hypergraph的算法本质上的理解可以说结合BTree性能提升空间很大。
