greatsql> CREATE TABLE t1 (c1 int primary key, c2 varchar(32),date1 datetime);greatsql> INSERT INTO t1 VALUES (1,'aaa','2021-03-25 16:44:00.123456'),(2,'bbb','2022-03-25 16:44:00.123456'),(3,'ccc','2023-03-25 16:44:00.123456');
# 这里看到以下的type类型是const，说明在优化器看来这是一张const tablegreatsql> EXPLAIN SELECT * FROM t1 WHERE c1=1 AND c1<10;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (3.60 sec)
# 这句sql语句最后被处理成以下的语句了，可以看到条件语句被提到前面当做列，最后的条件变为where true了。greatsql> SHOW WARNINGS;+-------+------+-------------------------------------------------------------------------------------------------------------+| Level | Code | Message                                                                                                     |+-------+------+-------------------------------------------------------------------------------------------------------------+| Note  | 1003 | /* select#1 */ select '1' AS `c1`,'aaa' AS `c2`,'2021-03-25 16:44:00' AS `date1` from `db1`.`t1` where true |+-------+------+-------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

bool JOIN::make_join_plan() {  if (!(query_block->active_options() & OPTION_NO_CONST_TABLES)) {    // Detect tables that are const (0 or 1 row) and read their contents.    //根据表的统计信息table->file->stats.records直接寻找该值为0或者1的表，并且表的cached_table_flags必须为HA_STATS_RECORDS_IS_EXACT，只有引擎是MyISAM和memory才有这个标志    if (extract_const_tables()) return true;
    // Detect tables that are functionally dependent on const values.    // 循环所有剩下的没有被标识的表，找出有索引或者有表依赖的表来标识    if (extract_func_dependent_tables()) return true;  }}
bool JOIN::extract_func_dependent_tables() {do {  for (JOIN_TAB **pos = best_ref + const_tables; *pos; pos++) {    一、遍历每张出现的表，寻找表里已经创建的索引。    二、判断表有join条件的情况，本次不涉及    三、判断表有表依赖的情况，本次不涉及    四、检查表是否可以通过key读取或表仅使用 const ref。    这里需要排除以下表：    1. 全文搜索，或    2. 嵌套外连接的一部分，或    3. 半连接的一部分，或    4. 具有大开销的外连接条件。    5. 被 const 表优化的处理程序阻止。    6. 将不会被使用，通常是因为它们是流式传输的而不是物化的（请参阅 Query_expression::can_materialize_directly_into_result()）。    7. 位于完整连接的两侧    // 如果找到表有索引，这里用到的keyuse在之前JOIN::update_ref_and_keys()已经获取到。    if (eq_part.is_prefix(table->key_info[key].user_defined_key_parts)) {      // 该索引是唯一索引      if (table->key_info[key].flags & HA_NOSAME) {        if (const_ref == eq_part) {          // 标识该表是const table          mark_const_table(tab, start_keyuse);          // 改变table的状态为const          join_read_const_table(tab, positions + const_tables - 1);        }      }    }  }} while ((const_table_map & found_ref) && ref_changed);

-- 下面的select 1表类型就是system而不是const。但是select 1这张表的数量也会计入const_tablesgreatsql> EXPLAIN SELECT * FROM (SELECT 1);+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           ||  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+2 rows in set, 1 warning (12.33 sec)

greatsql> CREATE TABLE t2 (c1 int, c3 varchar(32));greatsql> CREATE index idx1 ON t2(c1);greatsql> INSERT INTO t2 VALUES (1,'aaa'),(2,'bbb');
-- 非唯一索引不是const tablegreatsql> EXPLAIN SELECT * FROM t2 WHERE c1=1 AND c1<10;+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+|  1 | SIMPLE      | t2    | NULL       | ref  | idx1          | idx1 | 4       | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+1 row in set, 1 warning (3.84 sec)
-- 改变c1为primary key，看一下结果。-- between条件也是唯一条件，因此是const table-- 这里有个约束条件：BETWEEN的上下界条件值必须一样，才会被判定为const，否则是rangegreatsql> EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 1 AND 1 AND c1<10;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)
-- value in后面的值即使最小值和最大值相同也走的范围扫描，不被判定为const tablegreatsql> EXPLAIN SELECT * FROM t1 WHERE c1 IN (1,1) AND c1<10;+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)
-- 下面的表只有一行，但是由于引擎不是MyISAM或者memory而不被判定为const tablegreatsql> CREATE TABLE t3 AS SELECT 1 AS c1;greatsql> EXPLAIN SELECT * FROM t3 WHERE c1=1;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (1.91 sec)
-- 下面的表只有一行，由于引擎是MEMORY而被判定为const tablegreatsql> CREATE TABLE t5 engine=memory AS SELECT 1 AS c1;greatsql> EXPLAIN SELECT * FROM t5 WHERE c1=1;+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+|  1 | SIMPLE      | t5    | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (2.70 sec)
-- 两张表进行join的时候，即使这里t1的顺序在后面，因为在优化器里面被判定为const table，所以顺序被提前到第一位。greatsql> EXPLAIN SELECT t1.c1,t1.c2 FROM t2,t1 WHERE t1.c1=1 AND t1.c1<10;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        ||  1 | SIMPLE      | t2    | NULL       | index | NULL          | idx1    | 4       | NULL  |    1 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+2 rows in set, 1 warning (2.70 sec)
greatsql> EXPLAIN FORMAT=TREE SELECT t1.c1,t1.c2 FROM t2,t1 WHERE t1.c1=1 AND t1.c1<10;+-----------------------------------------------------+| EXPLAIN                                             |+-----------------------------------------------------+| -> Index scan on t2 using idx1  (cost=0.35 rows=1)  |+-----------------------------------------------------+1 row in set (40.27 sec)
-- 通过打印堆栈查看当前最佳的排列顺序，可以看到表t1确实被提到第一个了。Thread 56 "connection" hit Breakpoint 16, JOIN::get_best_combination (this=0x7fff14c6d0f0)    at /sql/sql_optimizer.cc:36633663          for (Table_ref *sj_nest : query_block->sj_nests) {(gdb) p best_ref[0]->table_ref->table_name$99 = 0x7fff14c5d660 "t1"(gdb) p best_ref[1]->table_ref->table_name$100 = 0x7fff14c5d538 "t2"