首页 > 数据库 >【GreatSQL 优化器 - 01】const_table

【GreatSQL 优化器 - 01】const_table

时间：2024-11-13 15:43:18浏览次数：1  
标签：01 const GreatSQL t1 key table c1 NULL

【GreatSQL 优化器 - 01】const_table

一、const_table 介绍
GreatSQL 的优化器主要用 JOIN 类来进行处理 SQL 语句的，JOIN 类有以下四个 table 数量相关的成员变量。其中 const_tables 是 optimize 最开始就检查并且标识的，因为这样可以把记录最少的表放在执行计划的第一步，在后面的执行计划里面这些 const tables 是不参与循环遍历和计算的，因此可以减少很多开销。

计数名称 说明 哪个函数进行累加
tables 该查询语句的所有表的数量，包含物化表和临时表 JOIN::get_best_combination()
primary_tables 该查询语句的主要表的数量，不包含物化表 JOIN::get_best_combination()
const_tables 该查询语句中只有 0 行或者 1 行的表数量 JOIN::extract_const_tables 和 JOIN::extract_func_dependent_tables
tmp_tables 该查询语句中临时表数量 JOIN::make_tmp_tables_info()
下面用一个简单的例子来说明 const_table 是什么。

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 table

greatsql> 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)
二、const_table 标识
const_table 的寻找和标识在函数 JOIN::extract_const_tables 和 JOIN::extract_func_dependent_tables 进行处理，这两个函数的功能各有不同。用上面的例子来说明。

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);
以下是实际使用的时候表扫描方式类型汇总，其中 JT_CONST 和 JT_SYSTEM 就是我们本章要说明的。JT_SYSTEM 类型，这个需要跟 JT_CONST 区分开，JT_SYSTEM 主要是扩展表而不是物理表，JT_SYSTEM 表没有索引。JT_SYSTEM 类型也会被计入 const_tables，详情见下面。

join_type 访问方法的类型 说明
JT_UNKNOWN 无效
JT_SYSTEM 表只有一行，比如 select * from (select 1)
JT_CONST 表最多只有一行满足，比如 WHERE table.pk = 3
JT_EQ_REF '= 符号用在唯一索引
JT_REF '= 符号用在非唯一索引
JT_ALL 全表扫描
JT_RANGE 范围扫描
JT_INDEX_SCAN 索引扫描
JT_FT Fulltext 索引扫描
JT_REF_OR_NULL 包含 null 值，比如 "WHERE col = ... OR col IS NULL
JT_INDEX_MERGE 一张表执行多次范围扫描最后合并结果
-- 下面的select 1表类型就是system而不是const。但是select 1这张表的数量也会计入const_tables
greatsql> EXPLAIN SELECT * FROM (SELECT 1);
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | | 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 table
greatsql> 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，否则是range
greatsql> 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 table
greatsql> 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 table
greatsql> 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 table
greatsql> 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:3663
3663 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"
cqgn.iyitiyu.com,cqgn.tcq0.com,cqgn.xwtcfs.com
cqgn.hgyb0831.com,cqgn.bochendoor.com
四、总结
从上面优化器最早的步骤我们认识了 const table 的定义和判定方法，可以发现实际运用中 const table 是可以提高查询速度的。这里面通过唯一索引来定位查找数据是最快的就是因为被判定为 const table，在后面的实践里面发现 =（等值查询）、特殊情况下的 BETWEEN 的时候都可以被认为是 const table，但是 IN 却不行，因此实际运用的时候尽量不要用 IN 条件，而多用等值条件 和 特殊条件下的BETWEEN 来查询，最好是只用等值条件。

标签：01,const,GreatSQL,t1,key,table,c1,NULL
From： https://www.cnblogs.com/bkbk1234/p/18544078

相关文章

  • [Ynoi2018] 未来日记
    [Ynoi2018]未来日记老早之前就想写了，人生中第一道大分块，调了一上午+下午一个小时，对拍了不知道多少万组，终于过了。\[\Huge{本题不卡常本题不卡常本题不卡常本题不卡常本题不卡常本题不卡常本题不卡常}\]\[\Huge{快来写快来写快来写快来写快来写快来写快来写快来写快来写快来写......
  • AT_agc011_d [AGC011D] Half Reflector 题解
    用\(1\)表示A，\(0\)表示B，观察进行一次操作后字符串会发生什么变化。首先当第一个数为\(1\)时，只会将第一个数变为\(0\)。对于剩下的情况，手玩一下可以发现会将第一个数移到末尾，然后将所有数异或\(1\)。先考虑暴力怎么做，可以记一个指针\(i\)和当前应该给全体数异或的值\(......
  • [题解]P3225 [HNOI2012] 矿场搭建
    P3225[HNOI2012]矿场搭建挖煤点坍塌相当于把该点和与其相连的边在图上删掉。借用wjyyy的题解，我们定义“叶子连通块”为“只包含\(1\)个割点的点双连通分量”，“非叶子连通块”为“包含\(\ge2\)个割点的点双连通分量”。如下图，橙色点是割点，红色框圈出的是点双，加粗的是叶子连通......
  • 2012年美国数学奥林匹克P6：Chebyshev不等式证明方法的应用
    题目已知整数$n\geq2$,实数$x_1,x_2,\cdots,x_n$满足$x_1+x_2+\cdots+x_n=0,$且$x_1^2+x_2^2+\cdots+x_n^2=1.$对每个集合$A\subseteq\{1,2,\cdots,n\}$,定义$\displaystyle{S_A=\sum_{i\inA}x_i,}$其中若$A$为空集,则记$S_A=0.$求证：对任意正实数$\lambda$,满足......
  • MBR20100DC-ASEMI光伏专用二极管MBR20100DC
    编辑：llMBR20100DC-ASEMI光伏专用二极管MBR20100DC型号：MBR20100DC品牌：ASEMI封装：TO-263特性：贴片二极管正向电流：20A反向耐压：100V恢复时间：35ns引脚数量：3芯片个数：2芯片尺寸：MIL浪涌电流：200A漏电流：10ua工作温度：-50℃~150℃包装方式：500/盘；5000/箱备受欢迎的MBR20100DC-ASE......
  • P2612 [ZJOI2012] 波浪 题解
    前置知识：连续段dp题目链接：P2612[ZJOI2012]波浪随机一个\(1\)到\(n\)的排列\(P_{1...n}\)，问以下式子的值\(\lem\)的概率是多少？\[|P_1-P_2|+|P_2-P_3|+|P_3-P_4|+...+|P_{n-1}+P_n|\]输出一个答案表示概率。保留\(k\)位小数。对于\(40%\)......
  • 国标GB28181-2016平台LiteGBS国标GB28181软件打造跨区域监控与数据共享新生态
    近年来，随着网络视频监控技术的快速发展，政府部门和跨区域行业单位对视频监控的需求不再局限于本地联网监控系统。越来越多的机构开始探索在现有本地联网基础上，建立覆盖省级乃至全国范围的跨区域监控网络，目的是为实现更广泛的数据共享与协同管理。LiteGBS视频融合云平台应运而生，它......
  • [GXYCTF2019]BabySQli 1
    [GXYCTF2019]BabySQli1打开实例发现是个登录页，查看源代码未发现有效信息，admin登录,显示密码错误，发现参数name和pw查看源代码发现base编码解密发现是base32+base64混合编码，并发现解密后的SQL语句，判断注入点为usernameselect*fromuserwhereusername='$name'尝试万......
  • 微信小程序 - 解决报错{“errno“：600001,“errMsg“：“request：fail errcode：-202cronet_
    前言关于此问题网上的教程都无法解决，如果您的报错信息与我相似，即可解决。在微信小程序开发中，详细解决小程序请求接口报错：{“errno”：600001,“errMsg”：“request：failerrcode：-202cronet_error_code：-202error_msg：net：：ERR_CERT_AUTHORITY_INVALID”}，微信小程序发起网络请求......
  • LCR 012. 寻找数组的中心下标(简单)(主站724)
    https://leetcode.cn/problems/tvdfij/https://leetcode.cn/problems/find-pivot-index/难度：☆☆☆题目：给你一个整数数组nums，请计算数组的中心下标。数组中心下标是数组的一个下标，其左侧所有元素相加的和等于右侧所有元素相加的和。如果中心下标位于数组最左端，那么左......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99