概念描述

MySQL有一些表级别的Hint，利用这些Hint配合索引、数据倾斜等特点，有时可以大幅度的提高SQL性能，以达到优化目的。

表级优化提示(Table-Level Optimizer Hints)，常用的Hint有4种：

• BKA, NO_BKA：启用或禁用BKA算法对表进行JOIN。
• BNL, NO_BNL：启用或禁用BNL算法对表进行JOIN。
• DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN：启用或禁用派生表条件下推(从MySQL 8.0.22版本开始)
• MERGE, NO_MERGE：启用或禁用视图合并(如视图、子查询、CTE等)。

这里还要说明以下3点：

• 建议读者先了解下NLJ、BNL、BKA、HASH算法。
• 派生表条件下推，在其它关系型数据库中早已存在，而MySQl到8.0.22版本才引入，这个优化是常用的技巧之一。
• 视图合并，MySQL对于复杂些的子查询优化的不是很好，但此方法也是常用的技巧之一，类似于把SQL中的子查询展开成基表访问。

测试验证

创建测试数据如下：

drop table if exists t1;
CREATE TABLE t1 (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  age int DEFAULT NULL,
  PRIMARY KEY (id),
  KEY ix_age (age)
) ENGINE=InnoDB;
insert into t1(id, name, age) values(1, 'name1', 10);
insert into t1(id, name, age) values(2, 'name2', 10);
insert into t1(id, name, age) values(3, 'name3', 20);
insert into t1(id, name, age) values(4, 'name4', 20);
insert into t1(id, name, age) values(5, 'name5', 30);
insert into t1(id, name, age) values(6, 'name6', 30);
insert into t1(id, name, age) values(7, 'name3', 40);
insert into t1(id, name, age) values(8, 'name4', 40);
insert into t1(id, name, age) values(9, 'name5', 50);
insert into t1(id, name, age) values(10, 'name6', 50);

drop table if exists t2;
create table t2 like t1;
insert into t2 select * from t1;

mysql> select * from t1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | name1 |   10 |
|  2 | name2 |   10 |
|  3 | name3 |   20 |
|  4 | name4 |   20 |
|  5 | name5 |   30 |
|  6 | name6 |   30 |
|  7 | name3 |   40 |
|  8 | name4 |   40 |
|  9 | name5 |   50 |
| 10 | name6 |   50 |
+----+-------+------+
10 rows in set (0.01 sec)

-- 查看optimizer_switch变量
mysql> show variables like 'optimizer_switch';
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-- 

1. BKA, NO_BKA：启用或禁用BKA算法对表进行JOIN.

-- 启用BKA，从Extra列可看出被驱动表走了BKA算法
mysql> explain
    -> select /*+ BKA(a, b) */ a.*
    -> from t1 a
    -> inner join t2 b
    -> on a.age=cast(b.age as char)
    -> ;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                                               |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | b     | NULL       | index | NULL          | ix_age | 5       | NULL |   10 |   100.00 | Using index                                         |
|  1 | SIMPLE      | a     | NULL       | ref   | ix_age        | ix_age | 5       | func |    2 |   100.00 | Using where; Using join buffer (Batched Key Access) |