当前位置：首页 > 数据库 > 正文内容

【GreatSQL优化器-03】查询开支预算

邻居的猫1个月前 (12-09)数据库802

【GreatSQL优化器-03】查询开支预算

一、cost和read_time介绍

GreatSQL的优化器在创立履行计划的时分是依据每张表的行数和数据散布以及读数据硬盘耗费等信息来判别先查询哪张表后查询哪张表，要不要运用索引，这些表资源信息就被称为cost，俗称为"开支"。在这之前现已履行了update_ref_and_keys（参阅【GreatSQL优化器-02】）和extract_const_tables（参阅【GreatSQL优化器-01】），拿到了const tables信息和表的keyuse_array索引信息，这儿就开端核算单张表扫描的开支，做一个开端的估量，用来给后边的choose_table_order()查找最佳表次序供给原始数据信息。

优化器经过estimate_rowcount函数开端核算单表开支，这个函数最终会核算出3个重要的数据。

称号 阐明 核算公式
found_records 表的总行数 tab->table()->file->stats.records
read_time 读取一切数据需求的开支 io_cost + cpu_cost + import_cost
worst_seeks 扫描全表需求的最差开支 find_worst_seeks(tab->table(), tab->found_records, tab->read_time)依据上面2项核算得出

下面用一个简略的比如来阐明这三个数字怎样检查：

greatsql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT,date1 DATETIME);
greatsql> INSERT INTO t1 VALUES (1,10,'2021-03-25 16:44:00.123456'),(2,1,'2022-03-26 16:44:00.123456'),(3,4,'2023-03-27 16:44:00.123456'),(5,5,'2024-03-25 16:44:00.123456');
greatsql> CREATE TABLE t2 (cc1 INT PRIMARY KEY, cc2 INT);
greatsql> INSERT INTO t2 VALUES (1,3),(2,1),(3,2),(4,3),(5,15);
greatsql> CREATE INDEX idx1 ON t1(c2);
greatsql> CREATE INDEX idx2 ON t1(c2,date1);
greatsql> CREATE INDEX idx2_1 ON t2(cc2);
greatsql> SET optimizer_trace = 'enabled=ON' ;

greatsql> SELECT * FROM t2,t1,(select 10) t3 where t1.c1=t2.cc2;
+-----+------+----+------+---------------------+----+
| cc1 | cc2  | c1 | c2   | date1               | 10 |
+-----+------+----+------+---------------------+----+
|   3 |    2 |  2 |    1 | 2022-03-26 16:44:00 | 10 |
|   1 |    3 |  3 |    4 | 2023-03-27 16:44:00 | 10 |
|   4 |    3 |  3 |    4 | 2023-03-27 16:44:00 | 10 |
|   2 |    1 |  1 |   10 | 2021-03-25 16:44:00 | 10 |
+-----+------+----+------+---------------------+----+
> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
          {
            "rows_estimation": [
              {
                "table": "`t2`",  -- 依照上面查询次序t2放第一个
                "table_scan": {
                  "rows": 5, 由于t2非const表，因而这儿显现的是一切行
                  "cost": 0.25, 这个是t2查询5条的开支，
                  "worst_seeks": 2 这儿是别的加上去的，为了检查便利
                }
              },
              {
                "table": "`t1`", 依照上面查询次序t1放第二个
                "table_scan": {
                  "rows": 4, 由于t1非const表，因而这儿显现的是一切行
                  "cost": 0.25,
                  "worst_seeks": 2 这儿是别的加上去的，为了检查便利
                }
              },
              {
                "table": " `t3`", 依照上面查询次序t3放第三个
                "rows": 1,
                "cost": 1, ※这儿有疑问，实践的read_time=worst_seeks=0.25，可是代码用了固定值1
                "worst_seeks": 0.25 这儿是别的加上去的，为了检查便利
                "table_type": "system", 由于t3是const表，因而这儿显现的system
                "empty": false
              }
            ]
          },

附表：价值系数

价值系数 值 阐明
ROW_EVALUATE_COST 0.1 扫描一行需求的开支
KEY_COMPARE_COST 0.05 比较row id需求的开支
MEMORY_TEMPTABLE_CREATE_COST 1.0 创立暂时表的开支，等于读10行
MEMORY_TEMPTABLE_ROW_COST 0.1 读或写一行到暂时表
DISK_TEMPTABLE_CREATE_COST 20.0 创立MyISAM表的开支
DISK_TEMPTABLE_ROW_COST 0.5 按次序生成 MyISAM 行的开支
MEMORY_BLOCK_READ_COST 0.25 读一个block从一个memory buffer pool
IO_BLOCK_READ_COST 1.0 从磁盘读取block

二、estimate_rowcount代码履行进程

实践代码履行进程如下，其间test_quick_select()函数在下面第三节介绍：

bool JOIN::make_join_plan() {
  if (estimate_rowcount()) return true;
}

bool JOIN::estimate_rowcount() {
        // 遍历每张表，核算每张表的上面3个值
        for (JOIN_TAB *tab = join_tab; tab < tab_end; tab++) {
            // 核算下面几个值
        tab->set_records(tab->found_records = tab->table()->file->stats.records);
        const Cost_estimate table_scan_time = tab->table()->file->table_scan_cost();
        tab->read_time = table_scan_time.total_cost();
        tab->worst_seeks =
            find_worst_seeks(tab->table(), tab->found_records, tab->read_time);
                // 这个函数是副功用，用于发现或许用于 GROUP BY 或 DISTINCT 查询的索引或或许用于 SKIP SCAN 的索引。
                // 主要给skip_scan_keys和const_keys增加能够用的索引
                add_loose_index_scan_and_skip_scan_keys(this, tab);
                // 假如上面核算得到的read_time<= 2.0那就不做快速查询test_quick_select()直接返回了，可是假如大于的话就要找是否有索引证快速查询来预算开支了。
                get_quick_record_count();
        }
}

上面触及的参数值见下表。

细项 阐明 核算公式
table_scan_cost() 引擎层读表的开支 ((stats.data_file_length) / IO_SIZE + 2) * table->cost_model()->page_read_cost(1.0)
find_worst_seeks() 全表扫描所需的最多开支 worst_seeks = min(table->file->worst_seek_times(tab->found_records / 10), tab->read_time * 3); min_worst_seek = table->file->worst_seek_times(2.0); 成果为：std::max(worst_seeks, min_worst_seek);

三、核算单表cost举例阐明

比如1：SELECT * FROM t1 join t2 ON t1.c1=t2.cc1 and t2.cc1=t1.c1 or t1.c1=5;
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 4,
                    "cost": 3.5 这儿算出来的开支大于2了，因而要走test_quick_select()预算
                  },
                  "potential_range_indexes": [ 这儿开端循环t1的一切索引，找出条件触及的索引
                    {
                      "index": "PRIMARY", 条件触及到了t1.c1，因而这儿PRIMARY索引被运用
                      "usable": true,
                      "key_parts": [
                        "c1"
                      ]
                    },
                    {
                      "index": "idx1", 条件不触及idx1，因而这儿idx1索引不被运用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx2", 条件不触及idx2，因而这儿idx2索引不被运用
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "best_covering_index_scan": { 经过find_shortest_key()找到的最短的索引，指掩盖一切需求的数据的联合索引table->covering_keys
                    "index": "idx2", 这个值为table->covering_keys，联合索引包含了主键信息
                    "cost": 1.40548, 开支小于上面的最早算出来的3.5，因而被挑选。这儿io_cost=1.00548，cpu_cost=4行*0.1（读每行开支）
                    "chosen": true
                  },
                  "setup_range_conditions": [ 没有找到mm tree
                  ],
                  "group_index_range": { 不是GROUP句子
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "skip_scan_range": { 没有装备skip_scan，不需求skip_scan
                    "chosen": false,
                    "cause": "not_single_table"
                  }
                }
              },
              {
                "table": "`t2`",
                "table_scan": {
                  "rows": 5,
                  "cost": 1 表t2算出来的开支小于2，因而不持续用快速查询核算了。
                }
              }
            ]
          },
-- t1挑选索引 idx2，由于cost更小
-- t2的挑选成果需求结合后边的choose_table_order()看，下一期再讲
greatsql> EXPLAIN SELECT * FROM t1 join t2 ON t1.c1=t2.cc1 and t2.cc1=t1.c1 or t1.c1=5;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+---------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                                                   |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+---------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | PRIMARY       | idx2   | 11      | NULL |    4 |   100.00 | Using index                                             |                               这儿挑选了idx2索引扫描，跟上面算出来的定论共同
|  1 | SIMPLE      | t2    | NULL       | index | PRIMARY       | idx2_1 | 5       | NULL |    5 |   100.00 | Using where; Using index; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+---------------------------------------------------------+

看另一个比如：

比如2：SELECT * FROM t1 join t2 ON t1.c1=t2.cc1 and t1.c1<5;
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 4,
                    "cost": 3.5 这儿算出来的开支大于2了，因而要走test_quick_select()预算
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY", 条件触及到了t1.c1，因而这儿PRIMARY索引被运用
                      "usable": true,
                      "key_parts": [
                        "c1"
                      ]
                    },
                    {
                      "index": "idx1", 条件不触及idx1，因而这儿idx1索引不被运用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx2", 条件不触及idx2，因而这儿idx2索引不被运用
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "best_covering_index_scan": { 找到联合索引，包含了主键信息
                    "index": "idx2",
                    "cost": 1.40548,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": { 不是GROUP句子
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "skip_scan_range": { 没有装备skip_scan，不需求skip_scan
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY", 依据get_best_group_min_max算出来的mm tree找到的最佳索引
                        "ranges": [
                          "c1 < 5"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "in_memory": 0,
                        "rows": 3, 规模扫描c1 < 5找到的契合的条数3条
                        "cost": 1.31293, 比上面算出来的cost低，因而被挑选
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans" 由于tree->n_ror_scans < 2，所以没有被挑选
                    }
                  },
                  "chosen_range_access_summary": { 总结上面一切核算的成果得出定论
                    "range_access_plan": {
                      "type": "range_scan", 定论是走索引规模扫描
                      "index": "PRIMARY",
                      "rows": 3,
                      "ranges": [
                        "c1 < 5"
                      ]
                    },
                    "rows_for_plan": 3, 找到3条数据
                    "cost_for_plan": 1.31293,
                    "chosen": true
                  }
                }
              },
              {
                "table": "`t2`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 5,
                    "cost": 3.6 这儿算出来的开支大于2了，因而要走test_quick_select()预算
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY", 触及到主键列，因而用到了
                      "usable": true,
                      "key_parts": [
                        "cc1"
                      ]
                    },
                    {
                      "index": "idx2_1", 没有触及cc2，因而没有用到
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "best_covering_index_scan": { 找到的非仅有索引，开支比上面的小，被挑选
                    "index": "idx2_1",
                    "cost": 1.50439,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "skip_scan_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "cc1 < 5"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "in_memory": 0,
                        "rows": 4, 依据cc1 < 5条件找到4条数据记载
                        "cost": 1.4133, 开支更小，被挑选
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": { 总结上面一切核算的成果得出定论
                    "range_access_plan": {
                      "type": "range_scan",  定论是走索引规模扫描
                      "index": "PRIMARY",
                      "rows": 4,
                      "ranges": [
                        "cc1 < 5"
                      ]
                    },
                    "rows_for_plan": 4, 找到4条记载
                    "cost_for_plan": 1.4133,
                    "chosen": true
                  }
                }
              }
            ]
          },

-- 定论：t1表用了规模扫描，跟上面定论共同
-- t2的挑选成果需求结合后边的best_access_path()看，下一期再讲
greatsql> EXPLAIN SELECT * FROM t1 join t2 ON t1.c1=t2.cc1 and t1.c1<5;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL      |    3 |   100.00 | Using where |                                    
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db1.t1.c1 |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+

四、总结

从上面优化器最早的过程咱们认识了优化器的cost和核算方法，知道了怎么开端预算单表的扫描cost而且依照最小cost挑选最佳索引，这些单表预算出来的cost会在后边greedy_search（贪婪查找）的时分用来做为核算依据，然后依照每张表的扫描开支对表进行排序，算出哪张表先扫描哪张表后扫描，最终得出最佳履行计划。

需求留意的是，上面的开端预算cost<=2的时分是不会进行后续快速扫描核算的，因而假如实践运用中想检查表的正确cost的话，需求依据其时表的实践数据量来做履行计划核算，而不是在空表或许数据量很小时分先做一次履行计划，然后用这个成果得出定论。

Enjoy GreatSQL 😃

关于 GreatSQL

GreatSQL是适用于金融级使用的国内自主开源数据库，具有高性能、高牢靠、高易用性、高安全等多个中心特性，能够作为MySQL或Percona Server的可选替换，用于线上出产环境，且完全免费并兼容MySQL或Percona Server。

相关链接： GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区：

社区博客有奖征稿概况：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技术交流群：

微信：扫码增加GreatSQL社区帮手微信老友，发送验证信息加群。

image-20221030163217640

扫描二维码推送至手机访问。

版权声明：本文由51Blog发布，如需转载请注明出处。

本文链接：https://www.51blog.vip/?id=545

标签: GreatSQL
分享给朋友：
返回列表

上一篇：第三十七讲：都说InnoDB好，那还要不要运用Memory引擎？

下一篇：Docker 中 PostgreSql 主从热备，主从切换计划

“【GreatSQL优化器-03】查询开支预算” 的相关文章

Redis探秘Sentinel（岗兵形式）

Redis探秘Sentinel（岗兵形式）

概述 Redis的高可用机制有耐久化、仿制、岗兵和集群。其首要的效果和处理的问题分别是： 耐久化：耐久化是最简略的高可用办法(有时乃至不被归为高可用的手法)，首要效果是数据备份，行将数据存储在硬盘，确保数据不会因进程退出而丢掉。 仿制：仿制是高可用Redis的根底，岗兵和集群都是在仿制根底上完结高可...

浅析REGEXP_SUBSTR,PRIOR,CONNECT BY

浅析REGEXP_SUBSTR,PRIOR,CONNECT BY

事务场景 teacher表中的tech_class字段存储的是每个教师所教授的课程，课程之间以英文逗号分隔。现在要用句子计算每个课程对应的教师数量。句子及作用如下： 句子其实很简略，各种博客或许gpt都有不错且可行的解决方案，咱们主要来理解下这段句子的履行原理，更好的学习。 part1 REGE...

decipher数据库,疾病相关基因组结构变异的宝库

decipher数据库,疾病相关基因组结构变异的宝库

DECIPHER是一个交互式的网络数据库，专门用于存储和共享人类基因组变异和表型数据。以下是DECIPHER数据库的一些关键用途和特点： 用途1. 数据共享和比较：DECIPHER被临床社区广泛用于共享和比较表型及基因型数据。该数据库包含来自49,745名患者的数据，这些患者同意广泛的数据共享。2....

大数据日志分析,二、大数据日志分析概述

大数据日志分析,二、大数据日志分析概述

1. 数据收集： 确定需要分析的日志数据源，例如服务器日志、应用程序日志、网络日志等。 使用日志收集工具（如Fluentd、Logstash等）从各个数据源收集日志数据。2. 数据存储： 将收集到的日志数据存储在适合大数据分析的存储系统中，如Hadoop HDFS、Amazon S...

手机数据库软件,高效数据管理的移动解决方案

手机数据库软件,高效数据管理的移动解决方案

1. MySQL Viewer 平台：安卓 特点：这款软件是一个优秀的安卓Mysql客户端，支持Mysql和MariaDB。它实用、美观，功能齐全，操作简单，适合进行远程管理。2. DBeaver 平台：安卓 特点：DBeaver社区版支持多种数据库平台，包括SQLite、P...

oracle去重查询,Oracle数据库去重查询详解

oracle去重查询,Oracle数据库去重查询详解

在Oracle数据库中，如果您想要去除查询结果中的重复记录，可以使用`DISTINCT`关键字。`DISTINCT`关键字可以确保查询结果中的每一行都是唯一的。下面是一个简单的例子，展示了如何使用`DISTINCT`关键字去除重复记录：```sqlSELECT DISTINCT column1, c...

Copyright © 2024-2028 51blog All Rights Reserved

XML地图