MySQL learn notes

工作中的一些记录，自用

安装

• MySQL gcc13 编译成功
• MySQL 当前最高只支持 gcc11
  • 后续使用高版本gcc编译，测试性能，当前已经是gcc15
  • 需要解决一些编码错误
  • 在编译 release 的时候，需要把 CPPFLAGS 去掉

mysql配置文件

有些配置只能在初始化的时候配置，一旦配置完成之后，就不能再修改了，所以需要预先确定自己的参数配置

名字为 my.cnf ，可以存在在多个位置，按照优先级读取，按照sever和client进行分组设置，分组的名字是启动的程序名字，例如 mysqld, mysqladmin, mysqldump 等。 此外还有两个独立的分组 server 和 clint 单独表述服务端和客户端

• server 的启动文件是 mysqld，所有直接或间接调用 mysqld 的程序都会调用其配置文件

• client 的启动文件是 mysql，mysqladmin, mysqldump 等，这些程序会读取 client 配置文件

• 具体的程序和实现有关，有些程序是脚本，而有的是单独的程序

• 相同的配置项以出现在最后的选项为主

# /etc/my.cnf
[mysqld]
lower_case_table_names=1
#performance_schema=off
log-error=/tmp/logerror.log
general_log=on
general_log_file=/tmp/mysqllog.log
slow_query_log = on
log-queries-not-using-indexes=on
slow-query-log-file=/tmp/mysqlslow.log
long_query_time=20

编译安装

docker 安装

docker run  -itd  --name mdev --hostname vscode --cap-add=SYS_PTRACE --privileged=true \
--mount type=bind,source=/home/asky/.mdep/boost_1_77_0,target=/home/vscode/.mdep/boost_1_77_0 \
 --shm-size 32G dev  bash


docker exec -it -u vscode mdev bash

docker cp /home/asky/percona_8032  mdev:/home/vscode
sudo chown -R vscode:vscode percona_8032


alias dev_msql='docker exec -it -u vscode mdev zsh'
alias stop_msql='docker stop mdev'
alias start_msql='docker start mdev' 


sudo apt install g++-11 gcc-11 gdb cmake git vim clangd
sudo apt-get install -y ninja-build libnuma-dev libcurl4-openssl-dev
sudo apt-get install -y libncurses5-dev pkg-config libreadline-dev
sudo apt install libtirpc-dev libpam-dev bison flex zsh

sudo apt update
sudo apt-get -y install git vim gcc-15 g++-15 
sudo apt-get -y install ninja-build
sudo apt-get install libedit-dev
sudo apt-get install liblz4-dev
sudo apt-get install libicu-dev
sudo apt-get install libprotobuf-dev
sudo apt-get install protobuf-compiler
sudo apt-get install libprotobuf-dev
sudo apt-get install protobuf-compiler
sudo apt-get install protobuf-c-compiler
sudo apt-get install libprotobuf-c-dev
sudo apt-get install libprotoc-dev
sudo apt-get install libtirpc-dev
sudo apt-get install zsh
sudo apt-get install curl flex bison g++ gcc gdb make libzstd-dev libxml2-dev libcurl4-openssl-dev vim libbz2-dev \
libperl-dev software-properties-common      pkg-config libreadline-dev python3-dev libldap-dev zlib1g-dev \
openssh-server iputils-ping libxslt-dev libpam-dev libssl-dev libreadline6-dev libssh2-1-dev cmake ninja-build sudo locales

# sudo update-alternatives --install /usr/bin/gcc gcc /usr/bin/gcc-11 110

# sudo update-alternatives --install /usr/bin/g++ g++ /usr/bin/g++-11 110


cmake -DCMAKE_BUILD_TYPE:STRING=Debug \
  -DCMAKE_EXPORT_COMPILE_COMMANDS:BOOL=TRUE \
  -DWITH_TURBO_MTR_TEST=OFF \
  -DWITH_TURBO=ON \
  -DWITH_QUERY_PLAN_PLUGIN=ON \
  -DFEATURE_SET=community \
  -DWITH_PAM=ON \
  -DWITH_ROCKSDB=OFF \
  -DWITH_TOKUDB=OFF \
  -DGROUP_REPLICATION_WITH_ROCKSDB=OFF \
  -DWITH_KEYRING_VAULT=OFF \
  -DWITH_INNODB_MEMCACHED=ON \
  -DWITH_ZLIB=bundled \
  -DWITH_NUMA=ON \
  -DWITH_LIBEVENT=bundled \
  -DWITH_SSL=system \
  -DWITH_AUTHENTICATION_LDAP=OFF \
  -DDOWNLOAD_BOOST=0 \
  -DWITH_BOOST=/home/vscode/.mdep/boost_1_77_0 \
  -DWITH_LICENSE=ON \
  -DWITH_DBLINK=0 \
  -DCMAKE_C_COMPILER=/usr/bin/gcc-11 \
  -DCMAKE_CXX_COMPILER=/usr/bin/g++-11 \
  -DCMAKE_POLICY_VERSION_MINIMUM=3.5 \
  -DINSTALL_MYSQLTESTDIR=0 \
  -DCMAKE_INSTALL_PREFIX=/home/vscode/.grdb \
  --no-warn-unused-cli \
  -S/home/vscode/percona \
  -B/home/vscode/percona/build/debug \
  -G Ninja


export PATH=/home/vscode/.percona/bin:$PATH
export LD_LIBRARY_PATH=/home/vscode/.percona/lib:$LD_LIBRARY_PATH
alias imysql='mysql -u root'
alias mstart='/home/vscode/.percona/support-files/mysql.server start --skip-grant-tables'
alias mstop='/home/vscode/.percona/support-files/mysql.server stop'

• MySQL 使用 cmake 管理项目
• MySQL 源码中 test 占据非常大的一部分，在build和安装中，会有多分拷贝
  • INSTALL_MYSQLTESTDIR 不安转 test。开发中，test 可以直接在 build 目录下测试

1. 下载boost依赖

git clone http://gitlab.greatopensource.com/deps/boost_1_77_0.git

1. 拉取依赖

   git submodule
   git submodule sync --recursive
   git submodule update --init --recursive

3. 编译安装及环境配置 这里使用的是 vscode 开发，所以一些vscode配置如下，包括安装位置，clangd配置，项目文件夹可见性配置

{
  "clangd.arguments": [
    "--compile-commands-dir=build/debug",
    "--all-scopes-completion",
    "--completion-style=bundled",
    "--cross-file-rename",
    "--header-insertion=iwyu",
    "--header-insertion-decorators",
    //"--background-index",
    "--clang-tidy",
    "--clang-tidy-checks=cppcoreguidelines-*,performance-*,bugprone-*,portability-*,modernize-*,google-*",
    "-j=6",
    "--pch-storage=disk",
    "--function-arg-placeholders=false",
  ],
  //  cmake -DCMAKE_BUILD_TYPE:STRING=Debug -DCMAKE_EXPORT_COMPILE_COMMANDS:BOOL=TRUE -DWITH_TURBO_MTR_TEST=OFF -DWITH_TURBO=ON -DWITH_QUERY_PLAN_PLUGIN=ON -DCMAKE_INSTALL_PREFIX=/home/asky/.grdb -DFEATURE_SET=community -DWITH_PAM=ON -DWITH_ROCKSDB=OFF -DWITH_TOKUDB=OFF -DGROUP_REPLICATION_WITH_ROCKSDB=OFF -DWITH_KEYRING_VAULT=OFF -DWITH_INNODB_MEMCACHED=ON -DWITH_ZLIB=bundled -DWITH_NUMA=ON -DWITH_LIBEVENT=bundled -DWITH_SSL=system -DWITH_AUTHENTICATION_LDAP=OFF -DDOWNLOAD_BOOST=0 -DWITH_BOOST=/home/asky/.mdep/boost_1_77_0 -DWITH_LICENSE=ON -DWITH_DBLINK=0 -DCMAKE_EXPORT_COMPILE_COMMANDS=True -DCMAKE_C_COMPILER=/usr/bin/gcc-11 -DCMAKE_CXX_COMPILER=/usr/bin/g++-11 -DCMAKE_POLICY_VERSION_MINIMUM=3.5 --no-warn-unused-cli -S/home/asky/percona-server -B/home/asky/percona-server/build/debug -G Ninja
  "cmake.configureArgs": [
    // "-DCMAKE_BUILD_TYPE=RelWithDebInfo",
    // "-DBUILD_CONFIG=mysql_release",
    "-DWITH_TURBO_MTR_TEST=OFF",
    "-DWITH_TURBO=ON",
    "-DWITH_QUERY_PLAN_PLUGIN=ON",
    "-DCMAKE_INSTALL_PREFIX=/home/asky/.grdb",
    "-DFEATURE_SET=community",
    "-DWITH_PAM=ON",
    "-DWITH_ROCKSDB=OFF",
    "-DWITH_TOKUDB=OFF",
    "-DGROUP_REPLICATION_WITH_ROCKSDB=OFF",
    "-DWITH_KEYRING_VAULT=OFF",
    "-DWITH_INNODB_MEMCACHED=ON",
    "-DWITH_ZLIB=bundled",
    "-DWITH_NUMA=ON",
    "-DWITH_LIBEVENT=bundled",
    "-DWITH_SSL=system",
    "-DWITH_AUTHENTICATION_LDAP=OFF",
    "-DDOWNLOAD_BOOST=0",
    "-DWITH_BOOST=/home/asky/.mdep/boost_1_77_0",
    "-DWITH_LICENSE=ON",
    "-DWITH_DBLINK=0",
    "-DCMAKE_EXPORT_COMPILE_COMMANDS=True",
    "-DCMAKE_C_COMPILER=/usr/bin/gcc-11",
    "-DCMAKE_CXX_COMPILER=/usr/bin/g++-11",
    "-DCMAKE_POLICY_VERSION_MINIMUM=3.5"
  ],
  "cmake.buildDirectory": "${workspaceFolder}/build/debug",
  "files.exclude": {
    "**/extra": true,
    "boost_1_77_0": true
  },
  "files.watcherExclude": {
    "**/extra": true,
    "boost_1_77_0": true
  },
  "explorer.autoRevealExclude": {
    "**/extra": true,
    "boost_1_77_0": true
  },
  "search.exclude": {
    "**/extra": true,
    "boost_1_77_0": true
  },
  "cmake.automaticReconfigure": false,
  "cmake.autoSelectActiveFolder": false,
  "cmake.configureOnEdit": false,
  "cmake.configureOnOpen": false,
}

4. 配置环境变量

export PATH=/home/asky/.percona/bin:$PATH
export LD_LIBRARY_PATH=/home/asky/.percona/lib:$LD_LIBRARY_PATH
alias imysql='mysql -u root'
alias mstart='/home/asky/.percona/support-files/mysql.server start --skip-grant-tables'
alias mstop='/home/asky/.percona/support-files/mysql.server stop'

5. 测试

 // test
 cd mysql-test
 ./mtr --force \
        --max-save-core=0 \
        --max-save-datadir=0 \
        --max-test-fail=0 \
        --verbose-restart \
        --retry=0 \
        --big-test \
        --testcase-timeout=120 \
        --suite-timeout=6000 \
        --mysqld=--synonym-translation-enabled=on \
        --debug-server \
        --parallel=50 \
        --suite=main,sys_vars,ora_compat,awr,innodb,mac,system_role \
        --skip-test-list=skip_err_test_list_for_mtrdebug
        

6. 启动mysql

mysqld --initialize
mstart
imysql
mstop


// 如果需要修改密码，
    alter user 'root'@'localhost' identified by  '123456';
mysqld &
mysql -u root -p

目录

❯ tree -L 1 -d
.
├── Docs  # 文档            
├── build
├── build-gs
├── build-ps
├── client
├── cmake
├── components
├── doc
├── doxygen_resources
├── extra                     # 第三方库，主要目的是为了多平台统一
├── greatdb-docs
├── greatdb-tools
├── include
├── internal
├── libbinlogevents
├── libbinlogstandalone
├── libchangestreams
├── libmysql
├── libservices
├── man
├── mysql-test
├── mysys
├── packaging
├── plugin
├── router
├── scripts
├── share
├── sql                        # 核心目录，优化器，执行器等
├── sql-common
├── storage                    # 存储引擎，插件实现
├── strings
├── support-files
├── testclients
├── unittest
├── utilities
└── vio

基础命令

这里是简单整理的一些基础命令的说明，但是没那么多精力一个一个的看过去

一些命令只是为了简化操作，不值得编写一个程序，不知道 MySQL 的研发怎么想的

-rw-r--r-- 1 asky asky  512 Apr 22 19:05 license.key
-rwxr-xr-x 1 asky asky 1.6K Apr 23 15:13 ps_mysqld_helper                              # 不知道，没见有人用
-rwxr-xr-x 1 asky asky  16K Apr 23 15:13 ps-admin                                      # 管理插件的，方便安装和卸载某几插件，无用
-rwxr-xr-x 1 asky asky 517K Apr 23 15:13 mysqldumpslow                                 # 分析慢查询日志的工具
-rwxr-xr-x 1 asky asky 1.9K Apr 23 15:13 mysqldecompress                               # 压缩工具
-rwxr-xr-x 1 asky asky  34K Apr 23 15:13 mysqld_safe                                   # 启动脚本，监控 mysqld 进程，异常退出时可以自动重启，无法直接使用 ^C 退出，需要使用 mysqladmin 命令
-rwxr-xr-x 1 asky asky  28K Apr 23 15:13 mysqld_multi                                  # 多实例启动脚本
-rwxr-xr-x 1 asky asky 4.9K Apr 23 15:13 mysql_config                                  # 输出当前 MySQL 的基础信息  
-rwxr-xr-x 1 asky asky  32K Apr 23 15:13 greatdbd_multi                                # 多实例启动脚本for greatdb
-r--r--r-x 1 asky asky 3.7K Apr 23 15:16 report_awr
-rwxr-xr-x 1 asky asky 3.2M Apr 23 15:16 license_client
-rwxr-xr-x 1 asky asky  11M Apr 23 15:16 mysql_ssl_rsa_setup
-rwxr-xr-x 1 asky asky 9.8M Apr 23 15:16 lz4_decompress
-rwxr-xr-x 1 asky asky 9.4M Apr 23 15:16 zstd_decompress
-rwxr-xr-x 1 asky asky 9.1M Apr 23 15:16 zlib_decompress
-rwxr-xr-x 1 asky asky  75K Apr 23 15:16 mysqltest_safe_process
-rwxr-xr-x 1 asky asky 9.9M Apr 23 15:16 my_print_defaults                             # 输出当前 MySQL 的 my.cnf 配置信息
-rwxr-xr-x 1 asky asky  11M Apr 23 15:16 comp_err
-rwxr-xr-x 1 asky asky  13M Apr 23 15:26 myisamlog
-rwxr-xr-x 1 asky asky  14M Apr 23 15:26 myisamchk
-rwxr-xr-x 1 asky asky  13M Apr 23 15:26 myisam_ftdump
-rwxr-xr-x 1 asky asky  13M Apr 23 15:26 myisampack
-rwxr-xr-x 1 asky asky  35M Apr 23 15:29 mysqlxtest
-rwxr-xr-x 1 asky asky  13M Apr 23 15:29 mysql_keyring_encryption_test
-rwxr-xr-x 1 asky asky  15M Apr 23 15:31 mysqlcheck
-rwxr-xr-x 1 asky asky  15M Apr 23 15:32 mysqlimport
-rwxr-xr-x 1 asky asky  15M Apr 23 15:32 mysqldump
-rwxr-xr-x 1 asky asky  11M Apr 23 15:32 mysqldecrypt
-rwxr-xr-x 1 asky asky  20M Apr 23 15:32 mysqltest                                     # 测试工具，可以
-rwxr-xr-x 1 asky asky  15M Apr 23 15:32 mysqlshow                                     # 
-rwxr-xr-x 1 asky asky  15M Apr 23 15:32 mysqladmin                                    # 管理工具，重点交互工具
-rwxr-xr-x 1 asky asky  15M Apr 23 15:32 mysqlslap
-rwxr-xr-x 1 asky asky  15M Apr 23 15:32 mysql_secure_installation
-rwxr-xr-x 1 asky asky  11M Apr 23 15:32 mysql_config_editor
-rwxr-xr-x 1 asky asky  17M Apr 23 15:32 mysql_upgrade
-rwxr-xr-x 1 asky asky  16M Apr 23 15:32 mysql
-rwxr-xr-x 1 asky asky  21M Apr 23 15:32 mysqlbinlog
-rwxr-xr-x 1 asky asky  12M Apr 23 15:32 perror
-rwxr-xr-x 1 asky asky  23M Apr 23 15:32 mysqlpump                                     # 备份工具
-rwxr-xr-x 1 asky asky  17M Apr 23 15:32 mysql_migrate_keyring
-rwxr-xr-x 1 asky asky  15M Apr 23 15:32 mysql_client_load_balance_test
-rwxr-xr-x 1 asky asky 9.1M Apr 23 15:32 mysql_tzinfo_to_sql
-rwxr-xr-x 1 asky asky  16M Apr 23 15:32 mysql_client_test
-rwxr-xr-x 1 asky asky  13M Apr 23 15:32 innochecksum
-rwxr-xr-x 1 asky asky  13M Apr 23 15:32 ibd2sdi
-rwxr-xr-x 1 asky asky 796K Apr 23 15:43 mysqlrouter_plugin_info
-rwxr-xr-x 1 asky asky 1.2M Apr 23 15:43 mysqlrouter_keyring
-rwxr-xr-x 1 asky asky 1.1M Apr 23 15:43 mysqlrouter_passwd
-rwxr-xr-x 1 asky asky 7.8M Apr 23 15:43 mysqlrouter
-rwxr-xr-x 1 asky asky 836M Apr 23 15:50 mysqld
-rw-r--r-- 1 asky asky   31 Apr 23 18:10 mysqld.my

在bin的同级目录support-files中还存在如下文件

-rwxr-xr-x 1 asky asky 1.1K Apr 22 19:05 mysqld_multi.server
-rwxr-xr-x 1 asky asky  11K Apr 23 15:13 mysql.server               # 数据库的启停使用这个
-rw-r--r-- 1 asky asky 2.0K Apr 23 15:13 mysql-log-rotate

调试技巧

MySQL 中有一些特殊的结构体，和 stl 的container类似，作为基础构建，我们可以无理由的相信他一定不会出问题， 我们只需要关注其内容即可，所以针对这部分实现了类似 std 容器的 pretty printer，MySQL pretty printer，可以方便的打印出 MySQL 内部的一些数据结构。例如

• list
• vecotors
• lock ...

其次对于一些大型结构，为了方便快速了解其大致结果，实现了一些特殊的 command，例如

• query_block tree，为了快速的了解其结构，除了使用 session debug = ast 之外，还可以用 'my qtree xx' ...

trace

MySQL trace 使用及实现调研

使用方法及作用

• 主要目的是输出优化过程中的各种细节，无论debug还是release，都可以使用，且只限定在 opt 阶段，和mysql debug有却别

SET optimizer_trace="enabled=on";

2. exec sql;

-- optimizer_switch='hypergraph_optimizer=off'

select trace from `information_schema`.`optimizer_trace`\G

trace: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `article_rank`.`aid` AS `aid`,sum(`article_rank`.`pv`) AS `num` from `article_rank` FORCE INDEX (`idx_day_aid_pv`) where (`article_rank`.`day` > 20190115) group by `article_rank`.`aid` order by `num` desc limit 10"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`article_rank`.`day` > 20190115)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`article_rank`.`day` > 20190115)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`article_rank`.`day` > 20190115)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`article_rank`.`day` > 20190115)"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`article_rank` FORCE INDEX (`idx_day_aid_pv`)",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`article_rank` FORCE INDEX (`idx_day_aid_pv`)",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1,
                    "cost": 1.79769e+308
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_day",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_day_aid_pv",
                      "usable": true,
                      "key_parts": [
                        "day",
                        "aid",
                        "pv",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_aid_day_pv",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "idx_day_aid_pv",
                    "cost": 0.35,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_skip_scan": {
                    "chosen": false,
                    "cause": "not_applicable_aggregate_function"
                  },
                  "skip_scan_range": {
                    "chosen": false,
                    "cause": "has_group_by"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_day_aid_pv",
                        "ranges": [
                          "20190115 < day"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": true,
                        "in_memory": 1,
                        "rows": 1,
                        "cost": 0.36,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`article_rank` FORCE INDEX (`idx_day_aid_pv`)",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1,
                      "filtering_effect": [
                      ],
                      "final_filtering_effect": 1,
                      "access_type": "scan",
                      "resulting_rows": 1,
                      "cost": 0.6,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 0.6,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`article_rank`.`day` > 20190115)",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`article_rank` FORCE INDEX (`idx_day_aid_pv`)",
                  "attached": "(`article_rank`.`day` > 20190115)"
                }
              ]
            }
          },
          {
            "optimizing_distinct_group_by_order_by": {
              "simplifying_order_by": {
                "original_clause": "`num` desc",
                "items": [
                  {
                    "item": "sum(`article_rank`.`pv`)"
                  }
                ],
                "resulting_clause_is_simple": false,
                "resulting_clause": "`num` desc"
              },
              "simplifying_group_by": {
                "original_clause": "`article_rank`.`aid`",
                "items": [
                  {
                    "item": "`article_rank`.`aid`"
                  }
                ],
                "resulting_clause_is_simple": true,
                "resulting_clause": "`article_rank`.`aid`"
              }
            }
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "GROUP BY",
              "steps": [
              ],
              "index_order_summary": {
                "table": "`article_rank` FORCE INDEX (`idx_day_aid_pv`)",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "idx_day_aid_pv",
                "plan_changed": false
              }
            }
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`article_rank` FORCE INDEX (`idx_day_aid_pv`)",
                "original_table_condition": "(`article_rank`.`day` > 20190115)",
                "final_table_condition   ": "(`article_rank`.`day` > 20190115)"
              }
            ]
          },
          {
            "refine_plan": [
              {
                "table": "`article_rank` FORCE INDEX (`idx_day_aid_pv`)"
              }
            ]
          },
          {
            "considering_tmp_tables": [
              {
                "adding_tmp_table_in_plan_at_position": 1,
                "write_method": "continuously_update_group_row"
              },
              {
                "adding_sort_to_table": ""
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "temp_table_aggregate": {
              "select#": 1,
              "steps": [
                {
                  "creating_tmp_table": {
                    "tmp_table_info": {
                      "table": "<temporary>",
                      "in_plan_at_position": 1,
                      "columns": 2,
                      "row_length": 20,
                      "key_length": 4,
                      "unique_constraint": false,
                      "makes_grouped_rows": true,
                      "cannot_insert_duplicates": false,
                      "location": "TempTable"
                    }
                  }
                }
              ]
            }
          },
          {
            "sorting_table": "<temporary>",
            "filesort_information": [
              {
                "direction": "desc",
                "expression": "`num`"
              }
            ],
            "filesort_priority_queue_optimization": {
              "limit": 10,
              "chosen": true
            },
            "filesort_execution": [
            ],
            "filesort_summary": {
              "memory_available": 262144,
              "key_size": 17,
              "row_size": 37,
              "max_rows_per_buffer": 11,
              "num_rows_estimate": 18446744073709551615,
              "num_rows_found": 0,
              "num_initial_chunks_spilled_to_disk": 0,
              "peak_memory_used": 495,
              "sort_algorithm": "none",
              "unpacked_addon_fields": "using_priority_queue",
              "sort_mode": "<fixed_sort_key, additional_fields>"
            }
          }
        ]
      }
    }
  ]
}

--
hypergraph_optimizer
          {
            "join_optimizer": [
              "Join list after simplification:",
              "* article_rank true  join_type=left",
              "* m1  join_type=inner",
              "",
              "Made this relational tree; WHERE condition is (none):",
              "* Left join [companion set 0x7730d8b4f4c8] (no join conditions)",
              "  * m1 [companion set 0x7730d8b4f4c8]",
              "  * article_rank [companion set 0x7730d8b4f4e8]",
              "",
              "Pushing conditions down.",
              "",
              "After pushdown; remaining WHERE conditions are (none), table filters are (none):",
              "* Left join [companion set 0x7730d8b4f4c8] (no join conditions)",
              "  * m1 [companion set 0x7730d8b4f4c8]",
              "  * article_rank [companion set 0x7730d8b4f4e8]",
              "",
              "Companion set: 0x7730d8b4f4c8:{}",
              "Companion set: 0x7730d8b4f4e8:{}",
              "Selectivity of join [left] (none):",
              "",
              "Constructed hypergraph:",
              "digraph G {  # 1 edges",
              "  m1 -> article_rank [label=\"[left] (none) (1)\"]",
              "}",
              "",
              "",
              "",
              "No interesting orders found. Not collecting functional dependencies.",
              "",
              "",
              "Enumerating subplans:",
              "",
              "Found node article_rank [rows=1]",
              " - {INDEX_SCAN, cost=2.18, init_cost=0, rows=1} [idx_day_aid_pv] is first alternative, keeping",
              " - {INDEX_SCAN, cost=2.18, init_cost=0, rows=1} [idx_aid_day_pv] is not better than existing path {INDEX_SCAN, cost=2.18, init_cost=0, rows=1}, discarding",
              " - {TABLE_SCAN, cost=0.47, init_cost=0, rows=1} is better than previous {INDEX_SCAN, cost=2.18, init_cost=0, rows=1}, replacing",
              " - current access paths for {article_rank}: {TABLE_SCAN, cost=0.47, init_cost=0, rows=1})",
              "",
              "Found node m1 [rows=1]",
              " - {TABLE_SCAN, cost=0.295, init_cost=0, rows=1} is first alternative, keeping",
              " - current access paths for {m1}: {TABLE_SCAN, cost=0.295, init_cost=0, rows=1})",
              "",
              "Found sets {m1} and {article_rank}, connected by condition [left] (none)",
              " - {HASH_JOIN, cost=2.61, init_cost=1.97, rescan_cost=0.641, rows=1, join_order=(m1,article_rank)} is first alternative, keeping",
              " - current access paths for {m1,article_rank}: {HASH_JOIN, cost=2.61, init_cost=1.97, rescan_cost=0.641, rows=1, join_order=(m1,article_rank)})",
              "",
              "Enumerated 3 subplans keeping a total of 3 access paths, got 1 candidate(s) to finalize:",
              "Adding final predicates",
              " - {HASH_JOIN, cost=2.61, init_cost=1.97, rescan_cost=0.641, rows=1, join_order=(m1,article_rank)} is first alternative, keeping",
              "Final cost is 2.6."
            ]
          }

详细实现

核心原理为

• 实现一个结构体，trace 当前语句，因为会 trace sp 的情况，所以一条语句可能有多个 stmt

• 实现格式的控制流程，区分上下级关系，区分 array，block 等，然后往当前 stmt 中添加适当的内容

• 此时这些信息都保存在 thd 的 trace_ctx 中，需要查询的时候

  • 查询 information_schema.optimizer_trace
  • 表是 临时表，会从 使用函数 fill_optimizer_trace_info 获取 trace_ctx 的信息，填充到 files 中

• hypergraph_optimizer 单独使用另一套采集方案，可以查看 TraceStarted ，但是底层实际是同一种东西，只是在切换到 hypergraph_optimizer 之前和之后，特殊处理一下

• optimizer_trace 是 information_schema 中的表，实际上不存在，实际作用只是为了实现 trace_info 的输出，详细查看st_schema_table

相关模块及数据结构

• trace_xxx trace 的信息采集模块，采集 opt 中的详细信息
• optimizer_trace 输出模块，information_schema 的一张零食表
• explain format=json 需要支持 json 格式的输出，由于 Opt_trace_context 可以遍历 explain tree 并且输出 json 信息，所以刚好比较合适处理这个信息， 实际上这里只需要输出 json 格式的 str 即可，explain 需要重构

优化方向

• 当前是以表的方式去使用，就代表优化中，会按照表的方式去处理，但是实际上更应该类似 function
• 建议实现类似 pg 的 funcscan 或者 duckdb 中的函数处理，轻量化处理

对比其他

pg

pg 有类似的功能，但是没有这么详细，而且也只是针对于每个阶段进行汇总，不是针对于每一步，另外 pg 的这个功能是为了 debug 使用的，功能比较简单，必须得设置宏才能使用，也没有控制的开关，只是简单的 print

就类似于 pg 社区反对使用 hint 的理由一样，他们相信大多是时候优化器可以根据你给定的参数和配置选择当前较好的执行计划，而无需手工介入，所以他们没有提供类似的 trace 根据， dba 无需知道内部的优化细节。

duckdb

完全没有类似的功能，最总只有一个 profing ，类似 pg 的analyze

总结

相比起来，MySQL 可能由于优化器不能提供较好的执行计划，所以需要给 dba 一个解释。。

debug

MySQL debug

-- d	开启DBUG_XXX
-- f	只跟踪指定的函数
-- F	跟踪指定的源码文件
-- i	跟踪指定的线程
-- L	跟踪指定的源码行数
-- n	打印函数调用层次序号
-- N	输出日志从0开始打印行号
-- o	指定输出到某个文件
-- O	类似o,每次写文件都会flush，reopen
-- P	匹配DBUG_PROCESS
-- p	打印process name
-- t	打印函数调用和退出

set session debug='d:t:o,/tmp/mysqld.trace';

set session debug='d:t:L:F:o,/tmp/mysqld.trace';

set session debug='d:t:L:F:n:O,/tmp/mysqld.trace';

1. 在 debug 的时候，可以详细的显示函数的调用路径
2. 或者提供一个额外的参数，供外部调用，以模拟或者实现某些特殊的功能，例如 skip_dd_table_access_check
   • 主要由 DBUG_EVALUATE_IF 实现的

• 最早代码可以追溯到 87 年

<do_command
>do_command
| >THD::clear_error
| <THD::clear_error
| >Diagnostics_area::reset_diagnostics_area
| <Diagnostics_area::reset_diagnostics_area
| >my_net_set_read_timeout
| | enter: timeout: 28800
| | >vio_socket_timeout
| | <vio_socket_timeout
| <my_net_set_read_timeout
| >vio_is_blocking
| <vio_is_blocking
| >net_read_raw_loop
| | >vio_read
| | | >vio_is_blocking
| | | <vio_is_blocking
| | | >vio_io_wait
| | | <vio_io_wait
| | <vio_read
| <net_read_raw_loop
| THD::enter_stage: 'starting' /home/asky/imysql/sql/conn_handler/init_net_server_extension.cc:104
| >PROFILING::status_change
| <PROFILING::status_change
| packet_header: Memory: 0x7f5af8007660  Bytes: (4)
38 00 00 00
| >net_read_raw_loop
| | >vio_read
| | <vio_read
| <net_read_raw_loop

实现原理

使用宏以及 c++ 对象自动析构的特点，实现函数调用栈的跟踪。并且加上输出的控制机制，可以指定输出位置，输出格式等

详细格式如下

  op_list_to_buf('d', cs->stack->keywords, DEBUGGING);
  op_int_to_buf('D', cs->stack->delay, 0);
  op_list_to_buf('f', cs->stack->functions, cs->stack->functions);
  op_bool_to_buf('F', cs->stack->flags & FILE_ON);
  op_bool_to_buf('i', cs->stack->flags & PID_ON);
  op_list_to_buf('g', cs->stack->p_functions, PROFILING);
  op_bool_to_buf('L', cs->stack->flags & LINE_ON);
  op_bool_to_buf('n', cs->stack->flags & DEPTH_ON);
  op_bool_to_buf('N', cs->stack->flags & NUMBER_ON);
  op_str_to_buf(((cs->stack->flags & FLUSH_ON_WRITE ? 0 : 32) |
                 (cs->stack->flags & OPEN_APPEND ? 'A' : 'O')),
                cs->stack->name, cs->stack->out_file != stderr);
  op_list_to_buf('p', cs->stack->processes, cs->stack->processes);
  op_bool_to_buf('P', cs->stack->flags & PROCESS_ON);
  op_bool_to_buf('r', cs->stack->sub_level != 0);
  op_intf_to_buf('t', cs->stack->maxdepth, MAXDEPTH, TRACING);
  op_bool_to_buf('T', cs->stack->flags & TIMESTAMP_ON);

主要宏操作

• DBUG_EVALUATE_IF(flag, a, b)

  • 满足 d 设置的标记的时候， 结果是 a ，否则是 b，类似运行时 ? :

• DBUG_EXECUTE(flag, expr)

  • 满足条件的时候， 执行 expr ，expr 是表达式，可以是函数调用，也可以是语句块

• DBUG_PRINT(flag, (format, args...))

  • 满足条件的时候，输出指定格式，这里是直接把参数传递 func，宏里面类似 db_doprnt(format, args...)

• DBUG_EXECUTE_IF(flag, expr)

  • 同上，但是只在 flag 条件下执行，和 DBUG_EXECUTE 类似，但是区别是 DBUG_EXECUTE_IF 只有设置对应得 falg 才会触发，而 DBUG_EXECUTE 则是列表为null或者设置flag得时候触发

对比其他

实现起来不难，但是没有见过其他数据库有类似的实现

• 首先这是 debug 期间使用的，对于非研发来讲，没有太大的作用
• 其次，如果是结构合理，模块清晰，调用堆栈其实没有太大的作用
  • MySQL 代码结构不合理， 所以这个还是有点用。。
  • 但是太多的 debug 宏的使用，会导致代码可读性和结构混乱，不利于维护和扩展，分不清哪些是逻辑代码，那些是debug代码
• 个人觉得这个只能适合那些
  1. 不是内核研发，但是只是想看看mysql的运行原理
  2. 或者是刚接触MySQL内核的研发人员，毕竟MySQL内核结构不是那么清晰，

readbook

• MySQL 是怎样运行的：从根儿上理解 MySQL

思考

• 对于 information_schema.optimizer_trace 这种从内存中获取信息的表，为什么不使用函数，类似 pg 的实现

  • MySQL 有函数可以返回一个自定义类型吗，或者 MySQL 支持定义类型吗
  • pg 是因为支持自定义类型，再或者其函数定义比较简单，函数可以非常方便的关联到具体的内部的某个 function 上，而 MySQL 有类似的行为吗，使用 udf 吗
  • duckdb 也有类似的支持，但是他主要是嵌入式，所以直接编写对应的函数，实现对应的接口，直接 register 就可以了，也是非常轻量级

• MySQL debug 和非 debug 的在某些实现上有非常明显的区别，MySQL 看起来非常注重内核的可观测性，但是有部分实现为了实现这种可观测性，进行了一些额外的修改

  • my_mutex_t 为了方便 debug 实现了 m_native 和 m_safe_ptr，如何保证他们之间一定没有任何逻辑上的差异

• limit 只能限定常量

• 优化之后的执行计划是什么，对比 pg，优化之后的树形结构是 Path tree，之后再 createplan 得到 Plan。MySQL 对应的是什么

  • pg 处理之后是 Path tree，之后再 createplan 得到 Plan
  • MySQL 在 create_access_paths 之后得到 AccessPath tree，之后由 CreateIteratorFromAccessPath 得到 RowIterator

• MySQL 和 pg 的 join order 的差异

  • MySQL make_join_pan Optimize_table_order --> choose_table_order 使用贪婪算法选择 join order
    • 但是这里计算的是 JOIN_TAB array 的顺序，此时每个 table 的具体的 accesspath 和join的具体算法还没有确定，怎么保证当前的顺序在后续生成 path 的过程中，不会导致执行计划劣化，当前步骤中，cost 计算的时候有考虑这个问题吗
      • 有考虑表的内在属性，例如 sort，关联条件的选择率等
      • MySQL 没有 merge join(几十年了，这种基础操作都还没有)
  • pg 是自底向上的 dp，每次选择join的时候，同时会选择具体的 join 算法，然后决定具体的join顺序

• setop 会调用 create_tmp_table，这个操作真的是建立一张临时表吗，这个操作没有使用 cost 判断吗，而且 MySQL 中的block op没有 bufer 管理，只能用临时表吗

• 抽象还不如不抽，抽的难以理解

MySQL基础

mysql系统表

默认存在下面几个database

+--------------------+
| Database           |
+--------------------+
| information_schema |  元数据管理
| mysql              |  杂项
| performance_schema |  性能监控，监控各项运行时指标
| sys                |  各种系统视图
| sys_mac            |
+--------------------+

• 区分没有那么明确，设计不是那么清晰易懂，不类似 pg 见文知意

mysqltpch测试

使用官方套件，或者使用duckdb输出数据之后导入

// 生产测试数据， s是数据集大小
./dbgen -vf -s 1
// 生成更新数据
./dbgen -v -U 1 -s 1

// 输出SQL
./qgen -d 1 > 1.sql

测试指标

• 压力测试

  • 需要测试22条查询语句和更新语句

• 吞吐量

  • 并发进行压力测试

• 上面两个是不同的指标， 也有专门的测试工具，但实际绝大多数测试报告只是导入数据之后，只测试22条查询语句，性能指标是总的时间，所以随大流即可

• 表结构可以按自己需求添加index

建表及数据加载

create database tpch;
use tpch;
source /home/asky/TPC-H-V3.0.1/dbgen/dss.ddl;
-- index
source /home/asky/TPC-H-V3.0.1/dbgen/dss.ri

set GLOBAL innodb_flush_log_at_trx_commit = 0;
set GLOBAL sync_binlog = 0;
set sql_log_bin = 0;

LOAD DATA  INFILE '/home/asky/TPC-H-V3.0.1/dbgen/customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINATED BY '|';
LOAD DATA  INFILE '/home/asky/TPC-H-V3.0.1/dbgen/lineitem.tbl' INTO TABLE LINEITEM FIELDS TERMINATED BY '|';
LOAD DATA  INFILE '/home/asky/TPC-H-V3.0.1/dbgen/nation.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|';
LOAD DATA  INFILE '/home/asky/TPC-H-V3.0.1/dbgen/orders.tbl' INTO TABLE ORDERS FIELDS TERMINATED BY '|';
LOAD DATA  INFILE '/home/asky/TPC-H-V3.0.1/dbgen/partsupp.tbl' INTO TABLE PARTSUPP FIELDS TERMINATED BY '|';
LOAD DATA  INFILE '/home/asky/TPC-H-V3.0.1/dbgen/part.tbl' INTO TABLE PART FIELDS TERMINATED BY '|';
LOAD DATA  INFILE '/home/asky/TPC-H-V3.0.1/dbgen/region.tbl' INTO TABLE REGION FIELDS TERMINATED BY '|';
LOAD DATA  INFILE '/home/asky/TPC-H-V3.0.1/dbgen/supplier.tbl' INTO TABLE SUPPLIER FIELDS TERMINATED BY '|';

analyze table CUSTOMER  ;
analyze table LINEITEM  ;
analyze table NATION    ;
analyze table ORDERS    ;
analyze table PART      ;
analyze table PARTSUPP  ;
analyze table REGION    ;
analyze table SUPPLIER  ;

• 导入速度慢的令人发指，需要调查具体慢的问题，pg 在开箱即用没有调优的情况下，1 g 的数据导入最多不过1分钟
  • MySQL有一些额外的安全机制
  • MySQL debug 有许多debug相关的宏，此时宏内部的操作 debug 下会执行 从文件导入，首要目的就是速度，安全的话完全可以使用insert，失败了的大不了重来就行，MySQL设计理念偏保守，个人赞同pg的处理方式

需要进行一轮完整的性能测试，使用release版本，并且尝试使用高版本gcc编译

MySQL参数

• ref https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html

• 变量

  • 分为不同作用域
    • 编译时设置
    • 初始化时设置
    • 需要重启
    • 需要
  • 分为 global和local
    • 当前 session 的值是创建链接时从全局变量设置的，运行时使用的session变量
    • session 变量

• 部分参数定义类似 pg，这部分 MySQL 称之为 VARIABLES，可以使用 'select @@[global|session].variable_name' 查询参数

• MySQL还存在一些反应系统状态的参数，MySQL 称之为 STATUS

  • 都可以使用语句 show [global|local] [variables|status] [like 'pattern'] 来查询

• 参数存在全局和local的区别，local参数只对当前会话有效，全局参数对所有会话有效

show variables like '%optimizer_switch%'; show global variables like '%optimizer_switch%';

• 实际是一条select 语句，读取的 pfs 中

QS:

• 变量的内部管理机制
  • 每一个参数都是静态变量，使用下面两个 hash 表管理
    • dynamic_system_variable_hash
      • 动态参数，插件内部定义的值，使用这个 map 管理，对外不区分使用方法
    • static_system_variable_hash
      • MySQL 内部参数，例如优化器参数
  • intern_find_sys_var 的使用，使用 name 去找具体的参数
  • 没有直接使用这两个hash表保存具体的参数，而是使用 System_variables 保存具体的参数
    • global_system_variables 是全局变量，保存的是全局参数
    • thd->variables 是 session 变量，保存的是当前会话参数
• 新链接的时候，session 变量的初始化过程
  • 在 THD 初始化的时候，在函数 plugin_thdvar_init 中，把 global_system_variables 复制给 thd->variables，然后再微调部分参数
• 设置值的时候，具体的操作步骤
  • 使用 find_static_system_variable 和 find_static_system_variable 按名字查找 var
  • 每一个参数是 继承自 sys_var 的子类
    • 修改 global 的时候，调用的是 global_update， 修改的是 global_system_variables
    • 修改 session 的时候，调用的是 session_update， 修改的是 thd->variables
• 使用的时候，使用的值
  • 无论什么时候，都是使用 thd->variables 的值，所以在修改参数的时候，如果修改的是 global 的值，已经启动的会话是不受影响的，除非重启会话

set global optimizer_switch='index_merge=off';

optimizer相关参数

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=on,
skip_scan=on,
hash_join=on,
subquery_to_derived=off,
prefer_ordering_index=on,
hypergraph_optimizer=off,
derived_condition_pushdown=on,
favor_range_scan=off,
remove_useless_outerjoin=off

#define PLUGIN_VAR_UNSIGNED 0x0080  /**< The variable is unsigned */
#define PLUGIN_VAR_THDLOCAL 0x0100  /**< Variable is per-connection */
#define PLUGIN_VAR_READONLY 0x0200  /**< Server variable is read only */
#define PLUGIN_VAR_NOSYSVAR 0x0400  /**< Not a server variable */
#define PLUGIN_VAR_NOCMDOPT 0x0800  /**< Not a command line option */
#define PLUGIN_VAR_NOCMDARG 0x1000  /**< No argument for cmd line */
#define PLUGIN_VAR_RQCMDARG 0x0000  /**< Argument required for cmd line */
#define PLUGIN_VAR_OPCMDARG 0x2000  /**< Argument optional for cmd line */
#define PLUGIN_VAR_NODEFAULT 0x4000 /**< SET DEFAULT is prohibited */
#define PLUGIN_VAR_MEMALLOC 0x8000  /**< String needs memory allocated */
#define PLUGIN_VAR_NOPERSIST \
  0x10000 /**< SET PERSIST_ONLY is prohibited for read only variables */
#define PLUGIN_VAR_PERSIST_AS_READ_ONLY 0x20000
#define PLUGIN_VAR_INVISIBLE 0x40000 /**< Variable should not be shown */
#define PLUGIN_VAR_SENSITIVE 0x80000 /**< Sensitive variable */
/**
  This flag enables variables to be recognized by SET_VAR() HINT. Should
  be used only THDVAR() variables, ie variables which have session scope.
 */
#define PLUGIN_VAR_HINTUPDATEABLE 0x100000

19 个结果 - 2 文件

sql/sql_plugin_var.h:
  211: class sys_var_pluginvar : public sys_var 

sql/sys_vars.h:
   198: class Sys_var_integer : public sys_var 
   358: class Sys_var_alias : public sys_var 
   577: class Sys_var_typelib : public sys_var 
   772: class Sys_var_multi_enum : public sys_var 
  1049: class Sys_var_charptr : public sys_var 
  1192: class Sys_var_proxy_user : public sys_var 
  1331: class Sys_var_dbug : public sys_var 
  1515: class Sys_var_double : public sys_var 
  1864: class Sys_var_plugin : public sys_var 
  1979: class Sys_var_debug_sync : public sys_var 
  2250: class Sys_var_have : public sys_var 
  2350: class Sys_var_struct : public sys_var 
  2431: class Sys_var_tz : public sys_var 
  2567: class Sys_var_gtid_next : public sys_var 
  2632: class Sys_var_gtid_set : public sys_var 
  2713: class Sys_var_charptr_func : public sys_var 
  2814: class Sys_var_gtid_purged : public sys_var 
  3004: class Sys_var_errors_set : public sys_var 

MySQL优化器汇总

qs:

• mysql 的某些操作会内部进行一些特殊的处理，不知道是基于什么原因实现的，但是不是太符合规范， 例如
  • 表达式运算中，会隐式转换，且转换之后不报错，转换规则也不是很符合常理，例如字符转数值，字符串转日期等，不清楚规则的情况下，隐式转换会导致出现预期之外的结果，或者index失效等
  • 一些函数行为不符合预期，例如 || 和 concat，round 函数等
• MySQL有没有某个函数每次输出不一样，且每次调用之后会导致某个状态发生改变
  • 使用函数或者sp，实现一个类似的函数
  • DETERMINISTIC 属性
• MySQL 相同的函数名，接受不同的参数类型，执行不同的逻辑
  • abs, greatest, least 等函数支持不同参数或者不同类型，需要继承 Item_func_numhybrid

MySQL 的表示使用继承实现，但是在 SQL 中，表达式的运算是很频繁的，使用继承实现会不会导致虚指针而导致性能问题 pg 为了加速表达式运算，已经重构过一次表达式，MySQL 相比于 pg，TPCH 的 q1 在所有的测试中，是性能比较弱的

• 减少函数调用，使用 goto 替换 switch,减少分支预测的开销，减少运行时的操作，更多的操作在 init 的时候实现 TODO: perf 采集下 tpch q1 MySQL 和 pg 的差距

• 表达式的运算逻辑，以 tpch q1 为例

tpch q20

dispatch_command dispatch_sql_command reset_for_next_command lex_start parse_sql { // 解析SQL语句，生成 PT_xx AST 树，之后生成 Sql_cmd add_table_to_list add_joined_table } mysql_rewrite_query // 有配置重写就尝试重写 mysql_execute_command { // 主要的执行逻辑，包括 opt 和 execute Sql_cmd_dml::execute { Sql_cmd_dml::prepare { check_table_access // 检查表权限
open_tables_for_query // 打开表 Query_block::prepare { // opt 前的准备工作，主要类似 pg 的analyze，解析类型，解析表字段，除此之外还有部分优化操作 setup_tables setup_fields setup_conds setup_order setup_connect_by resolve_limits resolve_subquery } } lock_tables { mysql_lock_tables decide_logging_format } Query_expression::optimize { Query_block::optimize { JOIN::optimize{ .. preprocess optimize_cond // 表达式优化， eg. 常量折叠，条件合并，等值传递，死条件删除等 JOIN::make_join_plan { init_planner_arrays update_ref_and_keys pull_out_semijoin_tables estimate_rowcount // 计算依据是什么，会根据条件计算吗 } } } } } }

Here is an overview of the logic of this function:

- Initialize JOIN data structures and setup basic dependencies between tables.

- Update dependencies based on join information. 对于存在outer join或recursive的tables进行关系传递propagate_dependencies()(用传递闭包算法)，构建出完整的依赖关系。(recursive这里具体指代未确定，nested？WITH RECURSIVE语法？)

- Make key descriptions (update_ref_and_keys()). 这一步骤较为烦杂，本意是想从conditions中找出join连接的condition，并识别出join condition相关的key(key指的就是索引)，为后续决定join_type到底是ref/ref_or_null/index等做好准备。但MySQL在这一步又加了不少特殊判断，比如对key is null的特殊处理等。

- Pull out semi-join tables based on table dependencies.

- Extract tables with zero or one row as const tables. 从这步开始的四个步骤都是const table优化，核心就是先把const table算出来，将变量替换成常量。这里是依靠获取采样判断const table。

- Read contents of const tables, substitute columns from these tables with
  actual data. Also keep track of empty tables vs. one-row tables.

- After const table extraction based on row count, more tables may
  have become functionally dependent. Extract these as const tables.

- Add new sargable predicates based on retrieved const values.

- Calculate number of rows to be retrieved from each table. 获取采样结果的步骤。

- Calculate cost of potential semi-join materializations.

- Calculate best possible join order based on available statistics. 即下文的Optimize_table_order::choose_table_order

- Fill in remaining information for the generated join order.

qs

• 表达式

  • 类型怎么绑定的
  • 结构怎么组织的
  • 常用的优化规则有那些，什么阶段生效
  • 表达式和子查询的联系及处理方法
  • 在执行阶段怎么evaluate的
  • 对比其他数据库的实现，当前实现有什么问题，怎么优化 ** 实现一个简单表达式

• 子查询

  • yacc怎么生成子查询
  • 子查询类型
  • 子查询怎么消除，具体的消除规则
  • 无法消除的子查询怎么处理，在优化阶段以及运行阶段
  • 子查询的代价计算 ** 对比其他数据库，存在什么问题，怎么优化

• join

  • join order 怎么确定的
  • join type 有哪些，怎么确定的
  • 怎么从下层 path 构造 join
  • cost 怎么影响 join 的选择
  • nest loop join 的优化
  • 运行时怎么执行 join ** 实现merge join有什么难点

• scan

  • 有哪些类型，包括 index 和 其他scan
  • 索引的选择和使用
  • 索引的维护和更新
  • 条件怎么处理的
  • 代价怎么计算的

• 聚合操作是怎么优化的

• sort 是怎么处理的

• 基于规则的优化和基于代价的优化，分别做了什么

• 参数化路径，join 中 a=b 这种，估算左右path的时候，怎么计算

• 对于一个组件，需要评估其使用情况，是否有必要使用，或者需要达到什么效果

  • 例如对于 explain，table 格式还需要存在吗

• Query_block::prepare 应该只关注于语言分析，不应该参杂太多优化工作

• parse:

  • 相比于 pg，差的不是一点半点，pg 的 parse 低耦合，已经可以作为一个单独的模块，被广泛使用，大多数新兴的基于使用c或者c++的数据库，不想写 parse 的，大多数可以直接使用 pgparse
    • lex 词不达意，一般是指词法分析，这里更类似parse ctx
  • 使用 show PARSE_TREE 显示语法树

1. 通过 contextualize，遍历 tree，构造以 query_block 为单位的语法树，同时在遍历过程中，检测某些关键信息，设置一些 flag

• 这里比其他数据库多了一层 contextualize，contextualize 还只是简单的遍历然后进行一个转换的过程，还没有使用具体的元数据 ? 为什么要 contextualize？ parse 里面不好构建 query_expression 和 query_block 树，所以需要 contextualize 层来补全语法树，然后再进行优化器的处理。

2. resolve：设置字段类型，检测表达式以及语句的合法性

• 类似 pg 的analyze，这里依赖与前面处理之后的 query_block 语法树，进行类型检查，表达式检查等

• 这里还包含语句的改写功能，例如子查询消除，outer join 转换等 ? 为什么不把 contextualize 和 resolve 合在一起处理，而且为什么把一些优化操作放在这里，统一由 optimizer 管理不行吗

• optimizer 处理的不是一个树形结构，而是 Query_expression 和 Query_block 组合的逻辑树

  • 怎么理解 Query_expression 和 Query_block 组合的逻辑树？
    • lex->unit 指向的是当前整个完整的 SQL 语句的 Query_expression
    • Query_block
      • master 指向的是包含当前的 Query_block 的 Query_expression
      • slave 指向的是当前 Query_block 的子查询的 Query_expression，可能是 union，也可能是子查询
      • next 指向的是同属于同一个 Query_expression 的 block 链表
      • link_next，link_prev 是维护所有当前语句中的 Query_block 的双向链表
    • Query_expression
      • m_query_term 当前 expression 的主要内容，可能是一个 union，或者就是一个 Query_block
      • 这个结构体只是为了维护同属于一个查询的 Query_block 的关系，暂时不了解其他作用，完全可以由 block 自己维护
      • slave 指向当前所属的第一个 Query_block
      • master 指向包含当前 Query_expression 的 Query_block

parser

1. 解析SQL语句，生成 PT_xx AST 树
2. 紧接着使用 do_contextualize 转换为 Query_block tree
3. 之后生成 Sql_cmd，这是最终运行时的一个包装类，主要包含一个 Query_result

• 怎么理解，和 pg 的portal有啥联系

怎么理解 Query_expression 和 Query_block 组合的逻辑树？

以下面的 SQL 为例

drop table if exists table1, table2, table3, table1_1_1, table1_1_2, table2_1_1_1_1, table2_1_1;

create table table1(id int, field int);
create table table2(f2 int, field int);
create table table3(id int, field int);
create table table1_1_1(id int);
create table table1_1_2(id int);
create table table2_1_1_1_1(id int, f1 int, f2 int);
create table table2_1_1(id int, f1 int, f2 int, f3 int);


   select *
     from table1
     where table1.field IN (select * from table1_1_1 union
                            select * from table1_1_2)
     union
   select *
     from table2
     where table2.field=(select (select f1 from table2_1_1_1_1
                                   where table2_1_1_1_1.f2=table2_1_1.f3)
                           from table2_1_1
                           where table2_1_1.f1=table2.f2)
     union
   select * from table3;

parse_sql: ast:

Query_term_union: 0x712e58b76b30 parent: (nil)
qb: 0x712e58b76fc0 join: (nil)
first distinct index: 1  last distinct index: 2

  Query_block: 0x712e58a771c8 parent: 0x712e58b76b30  with subqueries  qb: 0x712e58a771c8 join: (nil)

    Query_term_union: 0x712e58b9ab90 parent: (nil)
    qb: 0x712e58b9b020 join: (nil)
    first distinct index: 1  last distinct index: 1

      Query_block: 0x712e58b67850 parent: 0x712e58b9ab90       qb: 0x712e58b67850 join: (nil)

      Query_block: 0x712e58b693a0 parent: 0x712e58b9ab90       qb: 0x712e58b693a0 join: (nil)

  Query_block: 0x712e58b9c930 parent: 0x712e58b76b30  with subqueries  qb: 0x712e58b9c930 join: (nil)

    Query_block: 0x712e58b9dee8 parent: (nil)  with subqueries    qb: 0x712e58b9dee8 join: (nil)

      Query_block: 0x712e58b9e870 parent: (nil)       qb: 0x712e58b9e870 join: (nil)

  Query_block: 0x712e58b753f0 parent: 0x712e58b76b30   qb: 0x712e58b753f0 join: (nil)

Query_term_union: 0x742dd804a400 parent: 0x0 qb: 0x742dd804a890
  Query_block: 0x742dd808ade8 parent: 0x742dd804a400 tables: 'table1'
    Query_term_union: 0x742dd8075240 parent: 0x0 qb: 0x742dd80756d0
      Query_block: 0x742dd8063650 parent: 0x742dd8075240 tables: 'table1_1_1'
      Query_block: 0x742dd80651a0 parent: 0x742dd8075240 tables: 'table1_1_2'
  Query_block: 0x742dd8076fe0 parent: 0x742dd804a400 tables: 'table2'
    Query_block: 0x742dd8078598 parent: 0x0 tables: 'table2_1_1'
      Query_block: 0x742dd8078f20 parent: 0x0 tables: 'table2_1_1_1_1'
  Query_block: 0x742dd8048cc0 parent: 0x742dd804a400 tables: 'table3'





(gdb) my qtree pc.select->master->m_query_term
Query_term_union: 0x742dd804a400 parent: 0x0 qb: 0x742dd804a890
  Query_block: 0x742dd808ade8 parent: 0x742dd804a400 tables: 'table1'
    Query_term_union: 0x742dd8075240 parent: 0x0 qb: 0x742dd80756d0
      Query_block: 0x742dd8063650 parent: 0x742dd8075240 tables: 'table1_1_1'
      Query_block: 0x742dd80651a0 parent: 0x742dd8075240 tables: 'table1_1_2'
  Query_block: 0x742dd8076fe0 parent: 0x742dd804a400 tables: 'table2'
    Query_block: 0x742dd8078598 parent: 0x0 tables: 'table2_1_1'
      Query_block: 0x742dd8078f20 parent: 0x0 tables: 'table2_1_1_1_1'
  Query_block: 0x742dd8048cc0 parent: 0x742dd804a400 tables: 'table3'

(gdb) my qtree pc.select
Query_block: 0x742dd8048cc0 parent: 0x742dd804a400 tables: 'table3'
(gdb) my qtree  pc.select->link_next
Query_block: 0x742dd8078f20 parent: 0x0 tables: 'table2_1_1_1_1'
(gdb) my qtree  pc.select->link_next->link_next
Query_block: 0x742dd8078598 parent: 0x0 tables: 'table2_1_1'
  Query_block: 0x742dd8078f20 parent: 0x0 tables: 'table2_1_1_1_1'
(gdb) my qtree  pc.select->link_next->link_next->link_next
Query_block: 0x742dd8076fe0 parent: 0x742dd804a400 tables: 'table2'
  Query_block: 0x742dd8078598 parent: 0x0 tables: 'table2_1_1'
    Query_block: 0x742dd8078f20 parent: 0x0 tables: 'table2_1_1_1_1'
(gdb) my qtree  pc.select->link_next->link_next->link_next->link_next
Query_block: 0x742dd80651a0 parent: 0x742dd8075240 tables: 'table1_1_2'
(gdb) my qtree  pc.select->link_next->link_next->link_next->link_next->link_next
Query_block: 0x742dd8063650 parent: 0x742dd8075240 tables: 'table1_1_1'
(gdb) my qtree  pc.select->link_next->link_next->link_next->link_next->link_next->link_next
Query_block: 0x742dd808ade8 parent: 0x742dd804a400 tables: 'table1'
  Query_term_union: 0x742dd8075240 parent: 0x0 qb: 0x742dd80756d0
    Query_block: 0x742dd8063650 parent: 0x742dd8075240 tables: 'table1_1_1'
    Query_block: 0x742dd80651a0 parent: 0x742dd8075240 tables: 'table1_1_2'
(gdb) my qtree  *pc.select->link_next->link_next->link_next->link_next->link_next->link_next->link_prev
Query_block: 0x742dd808ade8 parent: 0x742dd804a400 tables: 'table1'
  Query_term_union: 0x742dd8075240 parent: 0x0 qb: 0x742dd80756d0
    Query_block: 0x742dd8063650 parent: 0x742dd8075240 tables: 'table1_1_1'
    Query_block: 0x742dd80651a0 parent: 0x742dd8075240 tables: 'table1_1_2'
(gdb) my qtree  *pc.select->link_next->link_next->link_next->link_next->link_next->link_prev
Query_block: 0x742dd8063650 parent: 0x742dd8075240 tables: 'table1_1_1'
(gdb) my qtree  *pc.select->link_next->link_next->link_next->link_next->link_prev
Query_block: 0x742dd80651a0 parent: 0x742dd8075240 tables: 'table1_1_2'
(gdb) my qtree  *pc.select->link_next->link_next->link_next->link_prev
Query_block: 0x742dd8076fe0 parent: 0x742dd804a400 tables: 'table2'
  Query_block: 0x742dd8078598 parent: 0x0 tables: 'table2_1_1'
    Query_block: 0x742dd8078f20 parent: 0x0 tables: 'table2_1_1_1_1'
(gdb) my qtree  *pc.select->link_next->link_next->link_prev
Query_block: 0x742dd8078598 parent: 0x0 tables: 'table2_1_1'
  Query_block: 0x742dd8078f20 parent: 0x0 tables: 'table2_1_1_1_1'
(gdb) my qtree  *pc.select->link_next->link_prev
Query_block: 0x742dd8078f20 parent: 0x0 tables: 'table2_1_1_1_1'
(gdb) my qtree  *pc.select->link_prev
Query_block: 0x742dd8048cc0 parent: 0x742dd804a400 tables: 'table3'

Query_block
  * master 指向的是包含当前的 Query_block 的 Query_expression
  * slave 指向的是当前 Query_block 的子查询的 Query_expression，可能是 union，也可能是子查询
  * next 指向的是同属于同一个 Query_block 的链表
  * link_next，link_prev 是维护所有当前语句中的 Query_block 的双向链表

Query_expression
  * m_query_term 当前 expression 的主要内容，可能是一个 union，或者就是一个 Query_block
  * 这个结构体只是为了维护同属于一个查询的 Query_block 的关系，暂时不了解其他作用，完全可以由 block 自己维护

总结

• lex->unit 指向的是当前整个完整的 SQL 语句的 Query_expression
• Query_block
  • master 指向的是包含当前的 Query_block 的 Query_expression
  • slave 指向的是当前 Query_block 的子查询的 Query_expression，可能是 union，也可能是子查询
  • next 指向的是同属于同一个 Query_expression 的 block 链表
  • link_next，link_prev 是维护所有当前语句中的 Query_block 的双向链表
• Query_expression
  • m_query_term 当前 expression 的主要内容，可能是一个 union，或者就是一个 Query_block
  • 这个结构体只是为了维护同属于一个查询的 Query_block 的关系，暂时不了解其他作用，完全可以由 block 自己维护
  • slave 指向当前所属的第一个 Query_block
  • master 指向包含当前 Query_expression 的 Query_block

binder

Q1 Q2

• 由 root block 开始，遍历 block，以 block 为单位，解析 block 中的关键信息
  • 对于 union 等 setop，直接遍历 children
  • 对于子查询，或者试图等 driverd table，需要按照其出现的位置进行解析
    • 如果是 from 中，使用 resolve_derived 进行解析

optimizer

JOIN::optimize()
    -# Logical transformations:
      - Outer to inner joins transformation.
      - Equality/constant propagation.
      - Partition pruning.
      - COUNT(*), MIN(), MAX() constant substitution in case of implicit grouping.
      - ORDER BY optimization.
    -# Perform cost-based optimization of table order and access path
       selection. See JOIN::make_join_plan()
    -# Post-join order optimization:
       - Create optimal table conditions from the where clause and the join conditions.
       - Inject outer-join guarding conditions.
       - Adjust data access methods after determining table condition (several times.)
       - Optimize ORDER BY/DISTINCT.
    -# Code generation
       - Set data access functions.
       - Try to optimize away sorting/distinct.
       - Setup temporary table usage for grouping and/or sorting.

JOIN::make_join_plan()
  - Initialize JOIN data structures and setup basic dependencies between tables.
  - Update dependencies based on join information.
  - Make key descriptions (update_ref_and_keys()).
  - Pull out semi-join tables based on table dependencies.
  - Extract tables with zero or one rows as const tables.
  - Read contents of const tables, substitute columns from these tables with actual data. Also keep track of empty tables vs. one-row tables.
  - After const table extraction based on row count, more tables may have become functionally dependent. Extract these as const tables.
  - Add new sargable predicates based on retrieved const values.
  - Calculate number of rows to be retrieved from each table.
  - Calculate cost of potential semi-join materializations.
  - Calculate best possible join order based on available statistics.
  - Fill in remaining information for the generated join order.

• CreateIteratorFromAccessPath

  • 先是优化为 QEP，此时已经可以作为完整的执行计划了，上古时期的 MySQL 直接使用这个结构体执行
  • 然后由 access_path 接口创建 AccessPath tree
  • 之后一对一的使用 CreateIteratorFromAccessPath 得到 RowIterator
    • RowIterator 是一个抽象类，对于不同的 node，实现了不同的 RowIterator，类似 pg 中的 Nodexxx

• AccessPath ? 怎么和代价进行关联的，怎么由代价选择具体的 path ? path 可以外部控制吗，可以控制代价吗

  • hint
  • optimizer_switch

SELECT                                                 
    c_custkey,                                         
    c_name,                                            
    sum(l_extendedprice * (1 - l_discount)) AS revenue,
    c_acctbal,                                         
    n_name,                                            
    c_address,                                         
    c_phone,                                           
    c_comment                                          
FROM                                                   
    customer,                                          
    orders,                                            
    lineitem,                                          
    nation                                             
WHERE                                                  
    c_custkey = o_custkey                              
    AND l_orderkey = o_orderkey                        
    AND o_orderdate >= CAST('1993-10-01' AS date)      
    AND o_orderdate < CAST('1994-01-01' AS date)       
    AND l_returnflag = 'R'                             
    AND c_nationkey = n_nationkey                      
GROUP BY                                               
    c_custkey,                                         
    c_name,                                            
    c_acctbal,                                         
    c_phone,                                           
    n_name,                                            
    c_address,                                         
    c_comment                                          
ORDER BY                                               
    revenue DESC                                       
LIMIT 20;                                              


| -> Limit: 20 row(s)  (actual time=40847.467..40847.477 rows=20 loops=1)
    -> Sort: revenue DESC, limit input to 20 row(s) per chunk  (actual time=40847.466..40847.474 rows=20 loops=1)
        -> Table scan on <temporary>  (actual time=40546.895..40794.485 rows=37967 loops=1)
            -> Aggregate using temporary table  (actual time=40546.838..40546.838 rows=37966 loops=1)
                -> Nested loop inner join  (cost=272639.29 rows=44407) (actual time=597.806..23338.038 rows=114705 loops=1)
                    -> Nested loop inner join  (cost=257096.69 rows=44407) (actual time=597.784..21907.947 rows=114705 loops=1)
                        -> Nested loop inner join  (cost=241460.31 rows=44407) (actual time=597.752..19513.204 rows=114705 loops=1)
                            -> Index range scan on orders using idx_orders_orderdate over ('1993-10-01' <= O_ORDERDATE < '1994-01-01'), with index condition: ((orders.O_ORDERDATE >= <cache>(cast('1993-10-01' as date))) and (orders.O_ORDERDATE < <cache>(cast('1994-01-01' as date))))  (cost=86921.70 rows=110656) (actual time=597.438..6692.232 rows=57069 loops=1)
                            -> Filter: (lineitem.L_RETURNFLAG = 'R')  (cost=1.00 rows=0.4) (actual time=0.187..0.223 rows=2 loops=57069)
                                -> Index lookup on lineitem using PRIMARY (L_ORDERKEY=orders.O_ORDERKEY)  (cost=1.00 rows=4) (actual time=0.179..0.218 rows=4 loops=57069)
                        -> Single-row index lookup on customer using PRIMARY (C_CUSTKEY=orders.O_CUSTKEY)  (cost=0.25 rows=1) (actual time=0.020..0.020 rows=1 loops=114705)
                    -> Single-row index lookup on nation using PRIMARY (N_NATIONKEY=customer.C_NATIONKEY)  (cost=0.25 rows=1) (actual time=0.012..0.012 rows=1 loops=114705)
 |
1 row in set (40.86 sec)


 Limit  (cost=10000614760.13..10000614760.18 rows=20 width=202) (actual time=802.229..802.235 rows=20 loops=1)
   ->  Sort  (cost=10000614760.13..10000614899.95 rows=55930 width=202) (actual time=802.228..802.232 rows=20 loops=1)
         Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
         Sort Method: top-N heapsort  Memory: 33kB
         ->  HashAggregate  (cost=609732.53..613271.85 rows=55930 width=202) (actual time=742.837..794.145 rows=37967 loops=1)
               Group Key: customer.c_custkey, nation.n_name
               Planned Partitions: 4  Batches: 5  Memory Usage: 8369kB  Disk Usage: 15544kB
               ->  Nested Loop  (cost=785.95..596554.02 rows=55930 width=182) (actual time=6.142..654.102 rows=114705 loops=1)
                     ->  Nested Loop  (cost=785.81..587914.98 rows=55930 width=160) (actual time=6.136..558.353 rows=114705 loops=1)
                           ->  Nested Loop  (cost=785.39..547601.88 rows=55930 width=16) (actual time=6.127..332.094 rows=114705 loops=1)
                                 ->  Bitmap Heap Scan on orders  (cost=784.96..27767.51 rows=56637 width=8) (actual time=6.100..77.419 rows=57069 loops=1)
                                       Recheck Cond: ((o_orderdate >= '1993-10-01'::date) AND (o_orderdate < '1994-01-01'::date))
                                       Heap Blocks: exact=23309
                                       ->  Bitmap Index Scan on idx_orders_orderdate  (cost=0.00..770.80 rows=56637 width=0) (actual time=2.888..2.889 rows=57069 loops=1)
                                             Index Cond: ((o_orderdate >= '1993-10-01'::date) AND (o_orderdate < '1994-01-01'::date))
                                 ->  Index Scan using idx_lineitem_orderkey on lineitem  (cost=0.43..9.14 rows=4 width=16) (actual time=0.003..0.004 rows=2 loops=57069)
                                       Index Cond: (l_orderkey = orders.o_orderkey)
                                       Filter: (l_returnflag = 'R'::bpchar)
                                       Rows Removed by Filter: 2
                           ->  Index Scan using customer_pkey on customer  (cost=0.42..0.72 rows=1 width=148) (actual time=0.002..0.002 rows=1 loops=114705)
                                 Index Cond: (c_custkey = orders.o_custkey)
                     ->  Index Scan using nation_pkey on nation  (cost=0.14..0.16 rows=1 width=30) (actual time=0.001..0.001 rows=1 loops=114705)
                           Index Cond: (n_nationkey = customer.c_nationkey)
 Planning Time: 0.670 ms
 Execution Time: 825.239 ms
(25 rows)

• tpch q10 为例，调整两边执行计划类似，其中MySQL为默认执行计划，没有调整任何参数，pg 关闭某些参数
• pg 执行时间不足 1s，MySQL 40s
• 对于 order 表

explain analyze select * from orders where o_orderdate >= CAST('1993-10-01' AS date) AND o_orderdate < CAST('1994-01-01' AS date)  ;
-> Index range scan on orders using idx_orders_orderdate over ('1993-10-01' <= O_ORDERDATE < '1994-01-01'), with index condition: ((orders.O_ORDERDATE >= <cache>(cast('1993-10-01' as date))) and (orders.O_ORDERDATE < <cache>(cast('1994-01-01' as date))))  (cost=86053.16 rows=110656) (actual time=556.981..6773.528 rows=57069 loops=1)
1 row in set (6.99 sec)

 Bitmap Heap Scan on orders  (cost=784.96..27767.51 rows=56637 width=107) (actual time=6.582..76.055 rows=57069 loops=1)
   Recheck Cond: ((o_orderdate >= '1993-10-01'::date) AND (o_orderdate < '1994-01-01'::date))
   Heap Blocks: exact=23309
   ->  Bitmap Index Scan on idx_orders_orderdate  (cost=0.00..770.80 rows=56637 width=0) (actual time=3.200..3.200 rows=57069 loops=1)
         Index Cond: ((o_orderdate >= '1993-10-01'::date) AND (o_orderdate < '1994-01-01'::date))
 Planning Time: 0.066 ms
 Execution Time: 77.943 ms

 Index Scan using idx_orders_orderdate on orders  (cost=0.43..105869.17 rows=56637 width=107) (actual time=0.057..77.665 rows=57069 loops=1)
   Index Cond: ((o_orderdate >= '1993-10-01'::date) AND (o_orderdate < '1994-01-01'::date))
 Planning Time: 0.059 ms
 Execution Time: 80.035 ms

 Seq Scan on orders  (cost=0.00..48633.00 rows=56637 width=107) (actual time=0.055..390.637 rows=57069 loops=1)
   Filter: ((o_orderdate >= '1993-10-01'::date) AND (o_orderdate < '1994-01-01'::date))
   Rows Removed by Filter: 1442931
 Planning Time: 0.080 ms
 Execution Time: 392.246 ms

• pg 无论是bitmap还是seq scan还是 index scan，时间不到1s，MySQL 耗时 7s

cost

executor

相关文档

TODO

1. 统一案例，保证每个迭代器都有一个可执行可调式的案例
2. 调查分析迭代器的优化方向，当前迭代器实现复杂度过高
3. 补充一些迭代器的实现细节

概述

MySQL执行器的架构体现了现代数据库系统的设计理念，通过迭代器模式¹实现了执行操作的标准化和模块化。这种设计不仅提高了代码的可维护性， 还为性能优化和功能扩展提供了良好的基础。整个执行器与查询优化器、存储引擎紧密配合，形成了MySQL查询处理的完整流水线。

迭代模型/火山模型（Iterator Model）作为业界常见的执行模型，其核心思想是将每一种操作抽象为一个 Operator， 整个 SQL 查询被构建成一个 Operator 树。查询执行时，树自顶向下调用 next() 接口，数据则自底向上被拉取处理， 因此这种处理方式也被称为拉取执行模型（Pull Based）。火山模型因其具有很高灵活性高、可扩展性好、易于实现和优化等特性，被广泛应用于数据库查询优化和执行中。

while (true) {
    slot = root->next();
    if (slot == NULL) {
        break;
    }
    // addtional processing
    if (filter(slot))
        yield slot;
}

一般实现显式指定算子的返回数据，但是 MySQL 隐藏这个过程，执行器中的数据流动实际是依赖于表以及field或者有算子自行维护，更类似于保存在执行中的上下文

handler

存储引擎层采用可动态加载的插件形式，为方便支持多种存储引擎，将存储引擎接口进行抽象化，server 层通过统一的接口访问存储引擎， 该抽象类即为 handler。各个存储引擎只需要实现 handler 相应的接口，server 层即可访问该存储引擎。

具体来说， handlerton 是MySQL中存储引擎与服务器层之间的核心接口结构体，它作为单例模式存在，代表一个特定的存储引擎。他定义了和具体得表无关的 存储引擎相关的操作接口，例如创建 handle，表空间管理等。

static handlerton *installed_htons[128];

enum legacy_db_type {
  DB_TYPE_UNKNOWN = 0,
  DB_TYPE_DIAB_ISAM = 1,
  DB_TYPE_HASH,
  DB_TYPE_MISAM,
  DB_TYPE_PISAM,
  DB_TYPE_RMS_ISAM,
  DB_TYPE_HEAP,
  DB_TYPE_ISAM,
  DB_TYPE_MRG_ISAM,
  DB_TYPE_MYISAM,
  DB_TYPE_MRG_MYISAM,
  DB_TYPE_BERKELEY_DB,
  DB_TYPE_INNODB,
  DB_TYPE_GEMINI,
  DB_TYPE_NDBCLUSTER,
  DB_TYPE_EXAMPLE_DB,
  DB_TYPE_ARCHIVE_DB,
  DB_TYPE_CSV_DB,
  DB_TYPE_FEDERATED_DB,
  DB_TYPE_BLACKHOLE_DB,
  DB_TYPE_PARTITION_DB,  // No longer used.
  DB_TYPE_BINLOG,
  DB_TYPE_SOLID,
  DB_TYPE_PBXT,
  DB_TYPE_TABLE_FUNCTION,
  DB_TYPE_MEMCACHE [[deprecated]],
  DB_TYPE_FALCON,
  DB_TYPE_MARIA,
  /** Performance schema engine. */
  DB_TYPE_PERFORMANCE_SCHEMA,
  DB_TYPE_TEMPTABLE,
  DB_TYPE_FIRST_DYNAMIC = 42,
  DB_TYPE_DEFAULT = 127  // Must be last
};

handler 接口定义了存储引擎的基本操作，包括表的创建、删除、打开、关闭、插入、删除、更新、查询等。handler 接口的具体实现由各个存储引擎自行实现， 没有要求存储引擎必须实现全部的接口，除了少部分核心操作定义为纯虚函数之外，其他大部分接口显然是可选的；存储引擎可以按照自己的特点按需实现， 例如 InnoDB 存储引擎实现了事务相关的接口，而 MyISAM 则没有实现这些接口，blackhole没有使用更新删除等接口。

一些重点接口如下:

表达式

SQL 语句中的表达式运算使用的是 Item 的 val_xx 接口，在执行中直接调用对应的接口获取值，例如 filter 预期得到的结果是 bool，直接调用 m_condition->val_int() 获得条件的值。

1. 普通表达式

   • 如果是常量，直接 Item 本身就保存有具体的值，直接调用 val_xx 接口获取值
   • 如果是 Field，需要确保 Field 的数据已经被填充，即关联的表已经读取对应的值

2. 聚合表达式

   • 聚合表达式只有在聚合迭代器运算完成之后，才能进行输出，在运行过程中，都是在进行一种累计操作
   • 最终输出也是调用的 val_xx 接口获取值

3. 子查询

   • 子查询需要区分相关子查询和非相关子查询，

     • 对于非相关子查询，MySQL 允许非相关子查询在优化阶段直接物化，此时执行计划显示 Rows fetched before execution，它在优化阶段就已经填充临时表

     如果 MySQL 需要支持执行计划缓存，则这是一个阻碍

     • 对于相关子查询，在表达式中使用 Item_subselect 表示，表达式计算的时候最终调用 Item_subselect::exec 获取子查询的值

     // 非相关子查询使用 optimize_derived 直接物化子查询
     #0  TableScanIterator::Read (this=0x718bc4a89920) at sql/iterators/basic_row_iterators.cc:286
     #1  0x000063674e8ae103 in FilterIterator::Read (this=0x718bc4a89970) at sql/iterators/composite_iterators.cc:93
     #2  0x000063674e8aeb93 in AggregateIterator::Read (this=0x718bc4a89998) at sql/iterators/composite_iterators.cc:333
     #3  0x000063674e4c95fe in Query_expression::ExecuteIteratorQuery (this=0x718bc4e1fc58, thd=0x718bc4d871a0) at sql/sql_union.cc:1832
     #4  0x000063674e4c9a05 in Query_expression::execute (this=0x718bc4e1fc58, thd=0x718bc4d871a0) at sql/sql_union.cc:1888
     #5  0x000063674e27bc29 in Table_ref::materialize_derived (this=0x718bc4e27cd0, thd=0x718bc4d871a0) at sql/sql_derived.cc:1843
     #6  0x000063674e27b63c in Table_ref::optimize_derived (this=0x718bc4e27cd0, thd=0x718bc4d871a0) at sql/sql_derived.cc:1728
     #7  0x000063674e306f54 in JOIN::optimize (this=0x718bc4a875d8, finalize_access_paths=true) at sql/sql_optimizer.cc:441
     #8  0x000063674e3eef91 in Query_block::optimize (this=0x718bc4da0698, thd=0x718bc4d871a0, finalize_access_paths=true) at sql/sql_select.cc:2147
     #9  0x000063674e4c60e9 in Query_expression::optimize (this=0x718bc4da05a0, thd=0x718bc4d871a0, materialize_destination=0x0, create_iterators=true, finalize_access_paths=true)
         at sql/sql_union.cc:1030
     #10 0x000063674e3ec6be in Sql_cmd_dml::execute_inner (this=0x718bc4e29b70, thd=0x718bc4d871a0) at sql/sql_select.cc:1125
     
     
     // 相关子查询在表达式运算的时候，调用子查询获得结果
     #0  RefIterator<false>::Read (this=0x718bc4a895b8) at sql/iterators/ref_row_iterators.cc:359
     #1  0x000063674e8aeb93 in AggregateIterator::Read (this=0x718bc4a89600) at sql/iterators/composite_iterators.cc:333
     #2  0x000063674e8ae103 in FilterIterator::Read (this=0x718bc4a89920) at sql/iterators/composite_iterators.cc:93
     #3  0x000063674e8ae72e in LimitOffsetIterator::Read (this=0x718bc4a89948) at sql/iterators/composite_iterators.cc:246
     #4  0x000063674e4c95fe in Query_expression::ExecuteIteratorQuery (this=0x718bc4e20878, thd=0x718bc4d871a0) at sql/sql_union.cc:1832
     #5  0x000063674e4c9a05 in Query_expression::execute (this=0x718bc4e20878, thd=0x718bc4d871a0) at sql/sql_union.cc:1888
     #6  0x000063674de5e6cc in Item_subselect::exec (this=0x718bc4e1f000, thd=0x718bc4d871a0) at sql/item_subselect.cc:786
     #7  0x000063674de5ed1f in Item_in_subselect::exec (this=0x718bc4e1f000, thd=0x718bc4d871a0) at sql/item_subselect.cc:892
     #8  0x000063674de62b22 in Item_in_subselect::val_bool_naked (this=0x718bc4e1f000) at sql/item_subselect.cc:1863
     #9  0x000063674dceef68 in Item_in_optimizer::val_int (this=0x718bc4e2a128) at sql/item_cmpfunc.cc:2634
     #10 0x000063674e8ae135 in FilterIterator::Read (this=0x718bc4a891b0) at sql/iterators/composite_iterators.cc:96
     #11 0x000063674e4c95fe in Query_expression::ExecuteIteratorQuery (this=0x718bc4da05a0, thd=0x718bc4d871a0) at sql/sql_union.cc:1832
     #12 0x000063674e4c9a05 in Query_expression::execute (this=0x718bc4da05a0, thd=0x718bc4d871a0) at sql/sql_union.cc:1888
     #13 0x000063674e3ec800 in Sql_cmd_dml::execute_inner (this=0x718bc4e29a28, thd=0x718bc4d871a0) at sql/sql_select.cc:1152
     #14 0x000063674e3eb831 in Sql_cmd_dml::execute (this=0x718bc4e29a28, thd=0x718bc4d871a0) at sql/sql_select.cc:823
     #15 0x000063674e34d5bb in mysql_execute_command (thd=0x718bc4d871a0, first_level=true) at sql/sql_parse.cc:5271
     

临时数据

在运行过程中，有的迭代器不是立刻就能输出数据，需要对数据进行一定处理之后，才能进行输出，此时可能有一些临时数据在，需要进行保存， 当前MySQL 中，主要有两种方式，一是使用临时表，二是自己使用 buffer 进行管理。

1. 使用临时表保存数据
   使用临时表的迭代器有个特点是在初始化的时候直接读取下层迭代器，填充临时表，而在 read 的时候直接从临时表读取数据

   • MaterializeIterator
   • StreamingIterator
   • MaterializedTableFunctionIterator
   • WeedoutIterator
   • TemptableAggregateIterator
   • MaterializeInformationSchemaTableIterator
   • WindowIterator

2. 使用 buffer 自己使用 buffer 管理，当前有下面几个迭代器有类似的机制

   • SortingIterator
   • HashIterator
   • BKAIterator

使用临时表保存临时数据的优点是实现简单，但是缺点是运行中还需要进行表的管理，耦合度太高

数据流动问题

重新认识 TABLE

struct TABLE 是 MySQL 中最核心的数据结构之一，它表示在查询执行期间内存中的一个表实例。该结构体定义在 sql/table.h 文件中，

TABLE 会保存 Field 字段，对应表的列

• Field 按照具体的类型对应不同的子类实现，除了保存正常的类型信息之外，还保存一个对应值的指针，指针指向的是 TABLE::record 字段
• TABLE::record 是 char* 类型，表示的是表在某一刻的具体的持有的值，具体参考 open_table_from_share
• TABLE 不仅仅只是逻辑上的表，还是具体的内存中的实体表，持有数据那种，在运行阶段，可以从 table 中获取数据

Field

Field 和 Item_field 存在一定的差异，他们的关系是

• Field 是 TABLE 结构体中用于管理列以及列数据，包含具体数据和数据类型
• 而 Item_field 用于管理 Field，用于参与表达式运算

Query_result

处理迭代器的输出的数据，有不同的子类实现，用于处理迭代器的输出，例如输出数据到客户端，或者进行数据的转发，如 select into，一般传递数据到客户端使用的是 Query_result_send

当前的继承关系

Query_result_send                               // 发送数据到客户端
    Query_result_explain                        // 具体的Query_result的包装类，用于实现explain
        Query_result_explain_into_var           // 用于把explain的结果输出到指定变量
    Query_fetch_protocol_binary                 // 使用二进制协议发送游标行的结果
Query_result_do                                 // DO stmt 使用
Query_result_interceptor                        // 运行中数据转发，由于不需要发送到客户端，所以不需要设置元数据信息
    Query_result_null                           // 不输出数据，用于explain analyze
    Query_result_to_file                        // 输出结果到文件
        Query_result_export                     // 输出结果到文件，只输出数据
        Query_result_dump                       // 输出结果到文件，以语句的形式
    Query_dumpvar                               // 导出变量
    Query_result_subquery                       // 处理子查询的基类
        Query_result_scalar_subquery            // 标量和行子查询的查询结果
        Query_result_max_min_subquery           // 用于独立的 ALL/ANY 查询
        Query_result_exists_subquery            // 处理 exists 语句，只判断是否有结果
    Query_fetch_into_spvars                     // 用于处理sp中的游标的值
    Query_result_delete                         // 用于处理delete语句，是一个空操作
    Query_result_insert                         // 用于处理 insert into select 语句中， 查询语句的结果，转发到目标表中
        Query_result_create                     // 类似 Query_result_insert，但是是create as select 语句
    Query_result_union                          // 用于 union 语句
        Query_result_materialize                // 用于处理游标，保存游标的数据
        Query_result_union_direct               // 已废弃
    Query_result_update                         // 用于更新语句

执行过程中，数据从存储引擎传递到表的 record 中，此时由于 Field 也指向 record，所以 Field 也相当于有数据，此时后续对 Field 的操作就可以直接操作数据。

当执行 select * from t1 的时候，调用堆栈如下

#0  Protocol_text::store_long (this=0x705581119a30, from=5, zerofill=0) at sql/protocol_classic.cc:3402
#1  0x00005c1a439ecdc2 in Field_long::send_to_protocol (this=0x7055817fc8f0, protocol=0x705581119a30) at sql/field.cc:4427
#2  0x00005c1a43b6113c in Protocol_classic::store_field (this=0x705581119a30, field=0x7055817fc8f0) at sql/protocol_classic.cc:1283
#3  0x00005c1a42e82acb in Item_field::send (this=0x705581826648, protocol=0x705581119a30) at sql/item.cc:9823
#4  0x00005c1a433dfec0 in THD::send_result_set_row (this=0x7055800084f0, row_items=mem_root_deque<Item *> with 2 = {...}) at sql/sql_class.cc:3364
#5  0x00005c1a432b5dc1 in Query_result_send::send_data (this=0x705581826620, thd=0x7055800084f0, items=mem_root_deque<Item *> with 2 = {...})
    at sql/query_result.cc:109
#6  0x00005c1a4368a70d in Query_expression::ExecuteIteratorQuery (this=0x7055818234a0, thd=0x7055800084f0) at sql/sql_union.cc:1847
#7  0x00005c1a4368aa05 in Query_expression::execute (this=0x7055818234a0, thd=0x7055800084f0) at sql/sql_union.cc:1888

其中有几个关键点：

1. Item 有定义 send 函数，用于将数据发送到协议层
2. Field 有定义 send_to_protocol 函数，用于将数据发送到协议层
3. Field 中有指向 TABLE::record 的指针，用于保存具体的数据，所以最终的数据是来自 TABLE

其中存在几个潜藏的问题：

1. 数据和表相关联，

Iteator

当前(MySQL8.4.4) 中共有 55 种迭代器

• RowIterator 定义迭代器的接口
• TableRowIterator 持有一个表，迭代器用于访问表中的数据，另一层意思是当前迭代器和上一层迭代器的数据不是用一个表，如果需要传递数据到下层，需要进行拷贝，例如 StreamingIterator

TableScanIterator

• TableScanIterator 是最基础的表访问方法，通过 rnd_init、ha_rnd_next 和 rnd_end 存储引擎接口进行操作，不使用任何索引。 除了正常访问基表之外，他还会用于访问执行中临时表的数据。例如聚合操作，set 操作等。

• 对于 except 和 insersect 的 distinct 有特殊操作，使用一个 counter 处理重复行

• 由 AccessPath::TABLE_SCAN 创建，执行计划显式的是 Table scan on xx

    while ((tmp = table()->file->ha_rnd_next(m_record))) {
      /*
       ha_rnd_next can return RECORD_DELETED for MyISAM when one thread is
       reading and another deleting without locks.
       */
      if (tmp == HA_ERR_RECORD_DELETED && !thd()->killed) continue;
      return HandleError(tmp);
    }
    if (m_examined_rows != nullptr) {
      ++*m_examined_rows;
    }

具体的例子

explain format=tree select * from t1;
| -> Table scan on t1  (cost=16.2 rows=160)

explain format=tree  select * from t3 intersect select * from t2;
| -> Table scan on <intersect temporary>  (cost=2.36..3.62 rows=2)
    -> Intersect materialize with deduplication  (cost=1.1..1.1 rows=2)
        -> Table scan on t3  (cost=0.45 rows=2)
        -> Table scan on t2  (cost=0.45 rows=2)

explain format = tree select avg(a) from t2 group by b;
| -> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Table scan on t2  (cost=0.45 rows=2)

IndexScanIterator

• IndexScanIterator 是 MySQL 查询执行引擎中的一个核心组件，用于沿着索引执行完整的索引扫描操作。使用一个 bool 模板控制 scan 的顺序

• 从 AccessPath::INDEX_SCAN 创建，执行计划显式的是 [Covering] Index scan using xx on xx

• 他的核心目的是为了替换 full table scan，例如 index only scan，因为在 MySQL 中，

表和index的存储都是使用的 btree, 表的scan有随机 IO 和 顺序 IO 的区别吗，表的数据是连续分布的吗，

explain format=tree SELECT  1 FROM t1 ;
| -> Covering index scan on t1 using i1_t1  (cost=16.2 rows=160)


explain format=tree SELECT  c3,c1 FROM t1 ;
| -> Covering index scan on t1 using i1_t1  (cost=16.2 rows=160)

explain format=tree SELECT c1, MIN(c4) FROM t1 GROUP BY c1;
| -> Group aggregate: min(t1.c4)  (cost=32.2 rows=2)
    -> Index scan on t1 using i1_t1  (cost=16.2 rows=160)


explain format=tree SELECT c1, MIN(c4) FROM t1 where c1 < 10 group by c1;
| -> Group aggregate: min(t1.c4)  (cost=32.2 rows=2)
    -> Filter: (t1.c1 < 10)  (cost=16.2 rows=160)
        -> Index scan on t1 using i1_t1  (cost=16.2 rows=160)

GroupIndexSkipScanIterator

【GreatSQL优化器-18】GROUP_INDEX_SKIP_SCAN

• 针对于min/max 和 distinct 的特殊优化，利用的是 index 自身自带 sorted group 的特性，直接定位到具体的min max 值，无需进行额外得聚合操作

• 从 AccessPath::GROUP_INDEX_SKIP_SCAN 构建而来

  • 对于 mix/max 的优化，执行计划显式的是 skip scan for grouping
  • 对于 distinct 的优化，执行计划显式的是 skip scan for deduplication

explain format=tree SELECT c1, Max(c2) FROM t1 GROUP BY c1;
| -> Covering index skip scan for grouping on t1 using i1_t1  (cost=1 rows=3)

explain format=tree SELECT distinct c1,c2 from t1;
| -> Covering index skip scan for deduplication on t1 using i1_t1  (cost=2.75 rows=10)

IndexSkipScanIterator

【GreatSQL优化器-16】INDEX_SKIP_SCAN

• 针对查询条件不满足 index 的前导列，但是满足部分后续列的语句，虽然 index 前导列不满足条件，但是 index 中其他列在索引中是有顺序的，可以通过跳跃式扫描来减少扫描的无效行数。

• 不是任何场景都适用，需要前导列低 NDV，否则可能实际情况不是太好

• 从 AccessPath::INDEX_SKIP_SCAN 构建而来， 执行计划显式的是 index skip scan

EXPLAIN format=tree SELECT c1, c2 FROM t1 WHERE c2  < 1;
| -> Filter: (t1.c2 < 1)  (cost=0.301..15.9 rows=53)
    -> Covering index skip scan on t1 using i1_t1 over NULL < c2 < 1  (cost=0.301..15.9 rows=53)

IndexRangeScanIterator

• 限定单位索引的范围查询，单个键进行范围索引扫描。

• 从 AccessPath::INDEX_RANGE_SCAN 构建而来，执行计划显式的是 index range scan

EXPLAIN format=tree SELECT c1, c2 FROM t1 WHERE c1 > 1 and c1 < 10 order by c1 asc;
| -> Filter: ((t1.c1 > 1) and (t1.c1 < 10))  (cost=16.3 rows=80)
    -> Covering index range scan on t1 using i1_t1 over (1 < c1 < 10)  (cost=16.3 rows=80)

ReverseIndexRangeScanIterator

• 和 IndexRangeScanIterator 类似，但是是反向扫描，虽然功能类似，但是没有使用同一个类实现

EXPLAIN format=tree SELECT c1, c2 FROM t1 WHERE c1 > 1 and c1 < 10 order by c1 desc;
| -> Filter: ((t1.c1 > 1) and (t1.c1 < 10))  (cost=16.3 rows=80)
    -> Covering index range scan on t1 using i1_t1 over (1 < c1 < 10) (reverse)  (cost=16.3 rows=80)

DynamicRangeIterator

【GreatSQL优化器-17】DYNAMIC RANGE

• 表上存在多个可用index，但是无法确定具体选择那个的时候，把选择延迟到具体执行的时候
• 执行的时候，在 Init 中，每次根据具体的值判断选择那个 index，或者执行 full scan，换言之，他实际执行的scan方式是动态选择的
• 每次 init ，表示这个迭代器只能出现在限定的地方，例如 nestloop join 的 outer，或者 subquery

create table t5(a int, b int, c int, d int);
create index idx_t5_1 on t5(a);
create index idx_t5_2 on t5(b);
create index idx_t5_3 on t5(c);
create table t6(a int, b int, c int, d int);
insert into t5 values(1,2,3,4);

explain format=tree  select * from t6 join t5 on true where t5.a = t6.a or t5.b = t6.b or t5.c = t6.c;
| -> Nested loop inner join  (cost=1.3 rows=2.59)
    -> Table scan on t6  (cost=0.35 rows=1)
    -> Filter: ((t5.a = t6.a) or (t5.b = t6.b) or (t5.c = t6.c))  (cost=0.509 rows=2.59)
        -> Index range scan on t5 (re-planned for each iteration)  (cost=0.509 rows=7)

TODO: 1. 优化器什么时候生成这个 Path
TODO: 2. 动态选择的具体过程

sort

Sort 在 SortingIterator::Init 的时候会读取下层迭代器，然后进行排序

• SortBufferIterator

  • 外部不可见的迭代器，内部使用，主要是使用 迭代器的方式读取一个已排序的 Filesort_info 对象，排序数据保存在内存中
  • 由 SortingIterator 构建，使用一个特化模板字段表示是否对数据进行压缩

• SortBufferIndirectIterator

  • 和 SortBufferIterator 类似，但是使用的是 rowid 进行排序，之后再使用 id 回表获得具体数据

• SortFileIterator

  • 磁盘排序，使用 merge sort 算法，将数据写入磁盘，然后再从磁盘读取数据进行排序

• SortFileIndirectIterator

  • 和 SortFileIterator 类似，但是使用的是 rowid 排序，之后再使用 id 回表获得具体数据

TODO: 调查 sort 的具体实现，以及排序方式的具体选择方法

UnqualifiedCountIterator

MySQL的COUNT查询并行优化策略

• innodb 使用并行实现一个 count 操作，但是由于具体实现实在 innodb 内部，所以针对这种场景进行特殊优化

• 所以使用场景非常苛刻，几乎只支持简单 count(*) 语句，并且只能用于 innodb 表

• 这是MySQL并行查询的起点(也是终点)

explain format=tree select count(*) from t1;
| -> Count rows in t1

ZeroRowsIterator

• 优化阶段可以确定算子没有输出，则使用这个算子，表示迭代器无输出，无输入

• 如果是聚合操作， 则需要输出null或者0，使用 ZeroRowsAggregatedIterator 处理

explain format=tree select count(*) from t1 where 1+1 = 3;
| -> Zero input rows (Impossible WHERE), aggregated into one output row  (cost=0..0 rows=1)

NestedLoopIterator

• 基础的 nestloop 实现

TODO: 调查数据是怎么流转的

explain format=tree select  * from t1 join t2 on t1.c1 = t2.a;
| -> Nested loop inner join  (cost=17.9 rows=160)
    -> Filter: (t2.a is not null)  (cost=0.45 rows=2)
        -> Table scan on t2  (cost=0.45 rows=2)
    -> Index lookup on t1 using i1_t1 (c1=t2.a)  (cost=4.75 rows=80)

BKAIterator

• 使用 BKA 加速 nestloop join，大致原理是 join 的驱动表读取一批数据之后，被驱动表使用 mrr 功能从index读取满足条件的数据，之后获得表的rowid，排序 rowid 之后把 随机 io 变成顺序 io

• 逻辑和 hash join 类似，只是 bak 和主要提升点是把回表的随机 io 转换为顺序 io， 减少了随机 io 的开销

  • 前提条件就是存在 IO 操作，如果小表或者表部分 page 有缓存，则没有 IO 操作

• 上位替代是 hash join

• 从 AccessPath::BKA_JOIN 构建而来， 执行计划显式 Batched key access 。当前它的inner侧限定为 mrr，这个迭代器无法独立存在

explain format=tree select /*+ bka(t1)*/ * from t1 join t2 on t1.c1 = t2.a;
| -> Batched key access inner join
    -> Batch input rows
        -> Filter: (t2.a is not null)  (cost=0.45 rows=2)
            -> Table scan on t2  (cost=0.45 rows=2)
    -> Multi-range index lookup on t1 using i1_t1 (c1=t2.a)  (cost=4.75 rows=80)

类似的另一种执行方式为 BNL，BNL 和 BKA 的区别是 BNL 是在 inner 没有index的时候使用的，批量化的访问inner,以减少交互次数，之前是因为 MySQL 没有实现 hashjoin，所以 BNL 还有点作用，但是有了 hashjoin 之后，BNL 就变得鸡肋了，所以现在删除了 BNL，使用 hashjoin替代，具体可以看 UseHashJoin

MultiRangeRowIterator

• 和 BKAIterator 绑定的迭代器，接受一个 outer 传递的 buffer，然后从 index 读取数据，获得对应的 rowid 之后，按照 pk 排序，把回表的随机 IO 转换为 顺序 IO

RemoveDuplicatesIterator

• RemoveDuplicatesIterator 通过维护缓存的分组字段值来检测重复行。当读取新行时，它会将当前行的分组字段值与之前缓存的值进行比较。如果值相同，则认为是重复行并跳过；如果不同，则更新缓存并返回该行。

• 要求输入是已排序或者group的，所以 child 只能是 index 或者 groupagg

• 由 AccessPath::REMOVE_DUPLICATES 构建而来，执行计划显式 Remove duplicates from input grouped on

EXPLAIN FORMAT=TREE SELECT DISTINCT a,a FROM t1 ORDER BY a;
EXPLAIN
-> Remove duplicates from input grouped on t1.a  (rows=11)
    -> Covering index scan on t1 using PRIMARY  (rows=11)

RemoveDuplicatesOnIndexIterator

• 类似 RemoveDuplicatesIterator ，但是限定子节点是 index，实现上原理类似，但是输入不同，这里要求的是index 的key，每次读取的时候通过和上次的值进行比较来判断是否重复

• 由 AccessPath::REMOVE_DUPLICATES_ON_INDEX 构建而来，执行计划显式 Remove duplicates from input on sorted

DROP TABLE t1;
DROP TABLE t2;
set optimizer_switch='firstmatch=off,materialization=off,duplicateweedout=off,loosescan=on';
CREATE TABLE t1 ( i INTEGER, PRIMARY KEY (i) );
CREATE TABLE t2 ( i INTEGER, INDEX i1 (i) );
INSERT INTO t1 VALUES (2), (3), (4), (5);
INSERT INTO t2 VALUES (1), (2), (3), (4);
ANALYZE TABLE t1, t2;

EXPLAIN format=tree SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i FROM t2);
| -> Nested loop inner join  (cost=2.05 rows=4)
    -> Remove duplicates from input sorted on i1  (cost=0.651 rows=4)
        -> Filter: (t2.i is not null)  (cost=0.651 rows=4)
            -> Covering index scan on t2 using i1  (cost=0.651 rows=4)
    -> Single-row covering index lookup on t1 using PRIMARY (i=t2.i)  (cost=1.1 rows=1)

NestedLoopSemiJoinWithDuplicateRemovalIterator

• JOIN_SEMI 的语义是返回满足条件的 outer 侧数据，即使 inner 有多条数据满足条件，所以在执行中，在匹配一行之后，可以立刻终止 inner 侧的扫描，继续匹配下一个 outer

• 如果能确定 inner 是unique输出的，则可以把 semi join 优化为 inner join

• 主要解决了一个性能优化问题：在传统的查询执行树中，去重操作需要放在半连接之上，因为第一个外表行不一定匹配内表的任何行。然而，这种方式是低效的，因为一旦找到匹配的外表/内表行对，应该停止扫描内表，直到有新的外表行。

  -> Nested loop join
     -> Table scan on t1
     -> Remove duplicates on t2_idx
        -> Nested loop semijoin
           -> Index scan on t2 using t2_idx
           -> Filter (e.g. t3.a = t2.a)
              -> Table scan on t3
  

• 使用这个迭代器，可以消除 semi join，转换为 inner join，例如下面例子中，t1 join (t3, t4) 原本逻辑上应该是 semi join，但是内部使用 NestedLoopSemiJoinWithDuplicateRemovalIterator 之后，没有重复行，所以无需使用 semi join

DROP TABLE t1, t2, t3,t4;

CREATE TABLE t1 (a INTEGER NOT NULL);
INSERT INTO t1 VALUES (2),(2);
CREATE TABLE t2 (b INTEGER);
INSERT INTO t2 VALUES (2),(11),(11);
CREATE TABLE t3 (b INTEGER,pk INTEGER);
create index idx_t3_k on t3(b);
INSERT INTO t3 VALUES (2,5);
CREATE TABLE t4 (pk INTEGER NOT NULL);
INSERT INTO t4 VALUES (5),(7);
ANALYZE TABLE t1,t2,t3,t4;

EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.a = t2.b WHERE t2.b IN ( SELECT t3.b FROM t3 JOIN t4 ON t3.pk = t4.pk );
| -> Inner hash join (t2.b = t1.a)  (cost=2 rows=2)
    -> Table scan on t2  (cost=0.175 rows=3)
    -> Hash
        -> Nested loop inner join  (cost=1.15 rows=2)
            -> Table scan on t1  (cost=0.45 rows=2)
            -> Nested loop semijoin with duplicate removal on b_key  (cost=1 rows=1)
                -> Index lookup on t3 using b_key (b=t1.a)  (cost=0.3 rows=1)
                -> Filter: (t4.pk = t3.pk)  (cost=0.3 rows=1)
                    -> Table scan on t4  (cost=0.3 rows=2)

WeedoutIterator

• 主要作用是避免 semijoin 使用物化操作，这里也需要物化，但是只是为了消除重复行，所以只需要记录 rowid 即可，无需保存完整数据，使用流式操作的方式去重

• NestedLoopSemiJoinWithDuplicateRemovalIterator 无法使用得情况下，才使用这个操作

• 可以把 semi join 转换为 inner join，从而允许更多的 join 组合方式，

explain format=tree SELECT 1 FROM t1 WHERE d IN (SELECT a FROM t1);
| -> Remove duplicate t1 rows using temporary table (weedout)  (cost=1.1 rows=2)
    -> Inner hash join (cast(t1.d as double) = cast(t1.a as double))  (cost=1.1 rows=2)
        -> Covering index scan on t1 using PRIMARY  (cost=0.35 rows=2)
        -> Hash
            -> Table scan on t1  (cost=0.45 rows=2)

HashJoinIterator

• HashJoinIterator 用于实现哈希连接算法。 该类继承自 RowIterator，是 MySQL 查询执行引擎中用于连接两个输入数据源的迭代器。

• HashJoinIterator 使用的是 hybrid hash join

  • 当hash表可以放在内存中时，所有操作都在内存中执行，inner 为小表，用于构建 hashtable，构建完成之后，每次读取一行inner用于匹配 outer
  • 当前内存不够时，则构建hashtable时使用二次分区操作，内存中保存一个分区，其他的分区保存在磁盘上，当构建完成之后，按照正常的hashjoin执行，内存中的分区消耗完成之后，从磁盘加载新的分区

• HashJoinIterator::Read 是一个状态机，执行的是join的操作，hashtable 的 build 操作在 HashJoinIterator::Init 时候已经处理完成

  • build 之前会先读一下outer child，确保outer有数据，而不是 build 完之后发现outer是空表

在 init 阶段就构建 hashtable，如果 inner 测有外层的依赖呢，例如 nestloop join 或者子查询，还是说执行计划限定不会有这种执行计划
build之前需要读一下outer本质上是对统计信息的不信任，无法在优化阶段确认表是否为空，MySQL的统计信息应用情况是什么样的，是怎么采样的，使用有没有其他手段确认表的状态
MySQL没有执行计划缓存，所以语句的执行计划所用的信息是当前实时的，没有过期的说法，所有优化阶段应该可以直接确定的

explain format=tree SELECT 1 FROM t1 WHERE d IN (SELECT a FROM t1);
| -> Remove duplicate t1 rows using temporary table (weedout)  (cost=1.1 rows=2)
    -> Inner hash join (cast(t1.d as double) = cast(t1.a as double))  (cost=1.1 rows=2)
        -> Covering index scan on t1 using PRIMARY  (cost=0.35 rows=2)
        -> Hash
            -> Table scan on t1  (cost=0.45 rows=2)

AggregateIterator

• 分组聚合，要求输入必须有序，一般是 index，否则需要使用 sort 进行排序，然后再聚合

• AggregateIterator 是流式算子，不会存储数据之后再进行聚合，需要使用临时表是 TEMPTABLE_AGGREGATE，不是这个迭代器

• 按照实际的功能，执行计划显式的时候，一般有下面集中情况

  1. 普通集合操作，没有 group by 的情况，直接显式 Aggregate:
  2. 有聚合操作，但是没有聚集函数，显示 Group (no aggregates)
  3. group with rollup|cube ，显示 Group aggregate with [rollup|cube]:
  4. 其他情况显示 Group aggregate

EXPLAIN FORMAT=TREE SELECT a, sum(b) FROM t1  GROUP BY a;
| -> Group aggregate: sum(t1.b)  (cost=1604 rows=8000)
    -> Index scan on t1 using PRIMARY  (cost=804 rows=8000)

TemptableAggregateIterator

• 使用临时表进行聚合操作，允许未排序的输入

EXPLAIN FORMAT=TREE SELECT d,a,c,sum(e) FROM t1 GROUP BY d,a,c;
| -> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Table scan on t1  (cost=0.75 rows=5)

MaterializeIterator

• 物化操作，使用临时表保存数据，一般用于一些需要重复使用数据或者临时保存数据的场景，例如子查询，聚合操作等

• 此外，和不同的算子配合使用的时候，具体细节也是不一样的，详情可以参考 ExplainMaterializeAccessPath

• 物化操作在 MaterializeIterator::Init 的时候已经开始了，而不是在具体的执行的时候，执行的时候已经开始从临时表中读数据了

• 有的语句可能会存在部分 tree 整体重新执行的情况，例如子查询需要根据外部提供得参数重新执行子查询，nestloop 需要使用 outer 得数据重新执行inner child，此时如果存在物化操作，则需要重新填充数据

explain format=tree select * from t1 union select * from t2;
| -> Table scan on <union temporary>  (cost=2.59..4.91 rows=9)
    -> Union materialize with deduplication  (cost=2.3..2.3 rows=9)
        -> Table scan on t1  (cost=0.85 rows=6)
        -> Table scan on t2  (cost=0.55 rows=3)

explain format=tree select * from t1, lateral (select * from (select * from (select t1.a from t2) as dt limit 1) dt2) dt3;
-> Nested loop inner join  (cost=8.62 rows=2)
    -> Invalidate materialized tables (row from t1)  (cost=0.45 rows=2)
        -> Table scan on t1  (cost=0.45 rows=2)
    -> Table scan on dt3  (cost=7.14..7.14 rows=1)
        -> Materialize (invalidate on row from t1)  (cost=4.62..4.62 rows=1)
            -> Table scan on dt2  (cost=4.53..4.53 rows=1)
                -> Materialize  (cost=2.01..2.01 rows=1)
                    -> Limit: 1 row(s)  (cost=1.91..1.91 rows=1)
                        -> Table scan on dt  (cost=1.91..3.18 rows=2)
                            -> Materialize  (cost=0.65..0.65 rows=2)
                                -> Table scan on t2  (cost=0.45 rows=2)

CacheInvalidatorIterator

• CacheInvalidatorIterator 的工作原理基于生成计数器机制。每当迭代器执行任何可能影响缓存有效性的操作时（如初始化、读取行、设置空行标志），都会递增内部的生成计数器。这种设计使得依赖该迭代器的上层组件（特别是MaterializeIterator）能够通过检查生成计数器的变化来确定是否需要重新物化数据。这种机制通常用于LATERAL表的场景，即连接一个依赖于连接中较早内容的派生表。

explain format=tree select t1.*, dt.c from t t1, lateral (select count(*) as c from t t2 left join t t3 on t3.a>t2.a-t1.a) as dt;
| -> Nested loop inner join  (cost=7 rows=2)
    -> Invalidate materialized tables (row from t1)  (cost=0.45 rows=2)
        -> Table scan on t1  (cost=0.45 rows=2)
    -> Table scan on dt  (cost=3.89..3.89 rows=1)
        -> Materialize (invalidate on row from t1)  (cost=1.38..1.38 rows=1)
            -> Aggregate: count(0)  (cost=1.28 rows=1)
                -> Left hash join (no condition), extra conditions: (t3.a > (t2.a - t1.a))  (cost=0.875 rows=4)
                    -> Table scan on t2  (cost=0.45 rows=2)
                    -> Hash
                        -> Table scan on t3  (cost=0.225 rows=2)

AppendIterator

• union all 专用，依次执行child ，然后输出

• union 使用的是 MaterializeIterator，物化之后去重

explain format=tree select * from t2 union all select * from t3 union all select * from t4;
| -> Append  (cost=1.45 rows=7)
    -> Stream results  (cost=0.55 rows=3)
        -> Table scan on t2  (cost=0.55 rows=3)
    -> Stream results  (cost=0.45 rows=2)
        -> Table scan on t3  (cost=0.45 rows=2)
    -> Stream results  (cost=0.45 rows=2)
        -> Table scan on t4  (cost=0.45 rows=2)

StreamingIterator

• StreamingIterator 主要用于优化器通常会设置物化操作但实际不需要的场景，特别是当你不需要多次读取写入的行，也不需要通过索引访问（只需要单次表扫描）的情况。它还负责在临时表上设置NULL行标志

• 他只是起到一个数据中转的作用，因为最终输出的时候，Query_result 处理的 field 只能是来自于同一张表的，详情参考迭代器中的数据流动

DeleteRowsIterator

• 不确定有何区别

TODO: 调查具体细节

EXPLAIN FORMAT=tree delete t1.* from t1;
| -> Delete from t1 (immediate)  (cost=0.65 rows=4)
    -> Table scan on t1  (cost=0.65 rows=4)

EXPLAIN FORMAT=tree delete  from t1;
| -> <not executable by iterator executor>

The end

对于SQL而言，逻辑算子是确定的，一条语句基本可以唯一对应棵逻辑树；但是具体到执行阶段，一个逻辑算子可能对应多种物理实现， 例如对于 table 来说，除了最基础的 seqscan 之外，我们还可以使用 index 加速查询。对于 join 来说，主要的物理算子有 nestloop join， merge join，hash join 等

MySQL 当前共有 55 中迭代器，但是部分迭代器是相互绑定的，无法独立使用，例如 CacheInvalidatorIterator 必须依赖于 MaterializeIterator，MultiRangeRowIterator 无法独立于BKAIterator而存在；还有的是为了 实现某些特定的优化，例如 Count(*)，有专用的 UnqualifiedCountIterator；再有就是有的迭代器只是为了限定某种SQL pattern，例如 NestedLoopSemiJoinWithDuplicateRemovalIterator；

总结下来，还是存在一些问题：

1. 功能设计过于分散，导致理解过于困难，例如没有显式指定每个迭代器的返回的数据，而是依赖于当前迭代器所处的 TABLE 的上下文中。
2. 迭代器之间的依赖关系复杂，有的迭代器需要依赖其他迭代器，或者迭代器执行的不是explain显式的，或者功能相近的使用不同的迭代器实现，某个特殊优化，使用单独的迭代器实现。
3. 表达式运算架构复杂，且运算中无关操作太多，导致表达式运算有较大的性能问题，无法用于 AP 场景
   • MySQL 的表达式实现被公开批斗，TPCH Q1 是典型的AP场景，表达式的实际计算在Q1中占比不足10%
   • MySQL TPCH Q1 在总的TPCH语句中占比为 13.77%，而 pg 的占比只有 2.16%，这说明 MySQL 的表达式实现还有优化空间

https://github.com/digoal/blog/blob/307dbe4a3fb2a9f800a09a5402f9782bff1172f1/202405/20240525_01.md

https://bbkv6krkep.feishu.cn/wiki/ClpKwq4nMiqU91kfWUbccvCXn9b

    MySQL 8032 sf=100                          pg 14.2 sf = 20
Q1  1082.89    |    20  |    162.633            Time: 60317.546 ms (01:00.318)  
Q2  161.951    |    13  |    894.933            Time: 88929.610 ms (01:28.930)  
Q3  334.043    |    17  |    43.552             Time: 32596.940 ms (00:32.597)  
Q4  101.223    |    16  |    14.084             Time: 48620.736 ms (00:48.621)  
Q5  198.459    |    10  |    46.459             Time: 55727.432 ms (00:55.727)  
Q6  200.663    |    2   |    28.383             Time: 207391.359 ms (03:27.391)  
Q7  169.708    |    21  |    86.762             Time: 27132.845 ms (00:27.133)  
Q8  460.922    |    11  |    68.929             Time: 117553.008 ms (01:57.553)  
Q9  1044.799   |    37  |    72.851             Time: 829795.748 ms (13:49.796)  
Q10 708.707    |    15  |    158.35             Time: 45392.179 ms (00:45.392)  
Q11 41.389     |    12  |    21.465             Time: 32471.915 ms (00:32.472)  
Q12 276.111    |    20  |    42.711             Time: 25439.356 ms (00:25.439)  
Q13 507.158    |    17  |    56.275             Time: 22745.637 ms (00:22.746)  
Q14 240.353    |    5   |    115.228            Time: 153935.888 ms (02:33.936)  
Q15 702.478    |    4   |    45.295             Time: 124949.464 ms (02:04.949)  
Q16 41.016     |    21  |    42.947             Time: 12197.386 ms (00:12.197)  
Q17 31.761     |    15  |    10.935             Time: 14841.749 ms (00:14.842)  
Q18 271.464    |    19  |    375.221            Time: 42511.465 ms (00:42.511)  
Q19 24.347     |    11  |    10.685             Time: 38056.589 ms (00:38.057)  
Q20 147.665    |    17  |    11.367             Time: 505946.346 ms (08:25.946)  
Q21 1093.338   |    35  |    44.189             Time: 302506.388 ms (05:02.506)  
Q22 19.613     |    11  |    15.214             Time: 1880.400 ms (00:01.880)  
    7860.058                                    2790939.986

1. Volcano-An Extensible and Parallel Query Evaluation System ↩

subquery

ref

• https://developer.aliyun.com/article/802290#slide-1
• https://www.alibabacloud.com/help/zh/polardb/polardb-for-mysql/user-guide/subquery-decorrelation
• https://segmentfault.com/a/1190000040833030
• https://zhuanlan.zhihu.com/p/60380557
• https://zhuanlan.zhihu.com/p/21177328157

子查询优化理论

• 子查询分为关联和非关联
  • 对于非关联，子查询只需执行一次，即使不消除子查询，一般也没有性能问题
  • 对于关联子查询，执行方式类似 nestloop，大多数场景下有性能问题，是首先需要关注的
• 子查询允许出现的位置
  • 子查询可以以表达式的方式存在，所以语句中所有的表达式可以出现的地方，子查询都允许，例如 select * from customer limit (select max(i_item_sk) from item) offset (select 1);
    • 以表达式的方式出现的子查询，成为 scalar 子查询， 限定只能输出一行，多行则报错
    • 标量在没有结果的时候，输出的是null，而不是空值，因为 scalar 必须有输出
  • 子查询可以以表的方式出现，所以语句中，所有表允许出现的地方，子查询亦可以，此时不限制输出行数
    • 以表的形式存在的时候，优化中可以看作一个表，参与join的正常的优化，但是也是需要判断是否存在关联字段
• 通用处理方案， 当前通用方案是基于 HyPer 和 sqlserver 的论文，引入一个新的表达式，对表达式应用规则， 然后进行子查询消除
  • https://www.cse.iitb.ac.in/infolab/Data/Courses/CS631/2005/Project/ref/orthOptOfSubQueriesp571galindolegaria.pdf
  • https://btw-2015.informatik.uni-hamburg.de/res/proceedings/Hauptband/Wiss/Neumann-Unnesting_Arbitrary_Querie.pdf
    • 关联条件上推，以达到子查询中没有关联字段， 从而消除子查询

MySQL 子查询

drop table if exists students;
drop table if exists exams;


CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    major VARCHAR(255),  
    year INT            
);

CREATE TABLE exams (
    sid INT,
    course VARCHAR(255),     
    curriculum VARCHAR(255), 
    date DATE,
    grade INT,
    FOREIGN KEY (sid) REFERENCES students(id)
);

1. 关联子查询

SELECT
    s.name,
    e.course
FROM
    students s,
    exams e
WHERE
    s.id = e.sid
    AND e.grade = (
        SELECT
            MIN(e2.grade)
        FROM
            exams e2
        WHERE
            s.id = e2.sid
    );


 -> Nested loop inner join  (cost=0.70 rows=1)
    -> Table scan on s  (cost=0.35 rows=1)
    -> Filter: (e.grade = (select #2))  (cost=0.35 rows=1)
        -> Index lookup on e using sid (sid=s.id)  (cost=0.35 rows=1)
        -> Select #2 (subquery in condition; dependent)
            -> Aggregate: min(e2.grade)  (cost=0.45 rows=1)
                -> Index lookup on e2 using sid (sid=s.id)  (cost=0.35 rows=1)

子查询

• resolve_subquery
  • 转换为 semi join
    • 十几条规则，简单来说， 子查询需要时简单子查询，不含聚合，setop，
  • 标识子查询需要物化
  • in 转 exists
  • all/any 转 min/max

select * from nation where n_regionkey in (select r_regionkey from region);

实现 generate_series 函数

• 按照参数输出序列

  • null in null out，有任何参数为null，则输出null

  • 2 个参数，则从 start 到 end 输出，数值类型步长为1，时间类型不接受两个参数，必须指定步长，那个参数在前面，就使用那个参数的类型

  • 3 个参数，则从 start 到 end 输出，步长为 step，时间类型步长为 step，输出类型为所有参数的公共类型

  • step 可以指定为负，此时输出序列为倒序

  • test 使用 duckdb 和 pg

  • 多次调用时候，函数状态

    • MySQL 有那些函数是有状态的函数

  • 参数为表达式

    • 嵌套复杂表达式
    • 子查询

  • 返回的是 record ，类似表的形式

    • 函数一般返回一行，或者一个值
      • pg 使用的是 Function Scan 调用，
    • MySQL 对应的是什么
      • MySQL 怎么把函数作为一个表

  • MySQL 不允许 select * from fun(), 只能 select func，func 不能出现在表的位置

    • 能出现在表的位置的

  • 社区可能不是不想做， 而是前置要求多

# non-persistent series generator
{ oid => '1066',  proname => 'generate_series', prorows => '1000', prosupport => 'generate_series_int4_support', proretset => 't', prorettype => 'int4', proargtypes => 'int4 int4 int4', prosrc => 'generate_series_step_int4' },
{ oid => '1067',  proname => 'generate_series', prorows => '1000', prosupport => 'generate_series_int4_support', proretset => 't', prorettype => 'int4', proargtypes => 'int4 int4', prosrc => 'generate_series_int4' },
{ oid => '3994',  proname => 'generate_series_int4_support', prorettype => 'internal', proargtypes => 'internal', prosrc => 'generate_series_int4_support' },
{ oid => '1068',  proname => 'generate_series', prorows => '1000', prosupport => 'generate_series_int8_support', proretset => 't', prorettype => 'int8', proargtypes => 'int8 int8 int8', prosrc => 'generate_series_step_int8' },
{ oid => '1069',  proname => 'generate_series', prorows => '1000', prosupport => 'generate_series_int8_support', proretset => 't', prorettype => 'int8', proargtypes => 'int8 int8', prosrc => 'generate_series_int8' },
{ oid => '3995',  proname => 'generate_series_int8_support', prorettype => 'internal', proargtypes => 'internal', prosrc => 'generate_series_int8_support' },
{ oid => '3259',  proname => 'generate_series', prorows => '1000', proretset => 't', prorettype => 'numeric', proargtypes => 'numeric numeric numeric', prosrc => 'generate_series_step_numeric' },
{ oid => '3260',  proname => 'generate_series', prorows => '1000', proretset => 't', prorettype => 'numeric', proargtypes => 'numeric numeric', prosrc => 'generate_series_numeric' },
{ oid => '938',  proname => 'generate_series', prorows => '1000', proretset => 't', prorettype => 'timestamp', proargtypes => 'timestamp timestamp interval', prosrc => 'generate_series_timestamp' },
{ oid => '939',  proname => 'generate_series', prorows => '1000', proretset => 't', provolatile => 's', prorettype => 'timestamptz', proargtypes => 'timestamptz timestamptz interval', prosrc => 'generate_series_timestamptz' },
{ oid => '6274',  proname => 'generate_series', prorows => '1000', proretset => 't', prorettype => 'timestamptz', proargtypes => 'timestamptz timestamptz interval text', prosrc => 'generate_series_timestamptz_at_zone' },

storage

performance_schema

简介

pfs， MySQL 自带的内核检测工具，用于监控 MySQL 服务器的运行状态，包括 CPU、内存、磁盘、网络、线程等资源的使用情况，以及 MySQL 服务器的内部运行状态，包括连接、查询、锁、事务等。

研究各项指标的检测实现，也不失为一种看源码的好方法

检测维度

1. 代码高度耦合，即使使用宏对核心代码进行包装，但是还有有部分对外的代码嵌在内核的各个角落，不知都对性能有没有影响
2. 需要对一个系统接口深度定制，例如 thread，mutex 等，如果使用 c++ 重写估计会简介很多
3. 资源消耗严重，状态信息都是保存在内存中，但是理论上这些信息可以在获取是再汇总，或者简单再处理下即可
4. pfs 已经默认开启许久，许多工具已经有一定的依赖性，对内，某些功能依赖这个组件，例如显示 thread 的名字，pfs不开，名字统一为 mysqld，对外，可能有些工具依赖
5. 此外不确定关闭宏之后，代码是否还正常
6. 为什么不直接使用时获取资源，而是需要保存再获取呢
   • 多线程，有什么不知道的难点吗

• PSI_THREAD "thread instrumentation"

  • 所有指标的核心，只有打开这个指标，其他的才可以使用，使用 HAVE_PSI_THREAD_INTERFACE 宏进行判断。
  • 通过 包装 thread 相关的系统调用，获取线程的创建、销毁、切换等信息，包括线程的状态、资源消耗、等待时间等。
    • 内部包装 inline_mysql_thread_xxx 系列函数，大部分在功能没有打开的时候，几乎不起作用
  • 当前线程的所用信息保存在 THR_PFS 中，理论上可以从这个对象找到所有当前线程你想要的信息
  • 由 全局对象 global_thread_container 保存所有 THR_PFS 对象，查询 threads 表的时候，从这里获取信息

• PSI_MUTEX "mutex instrumentation"

  • 使用 HAVE_PSI_MUTEX_INTERFACE 控制相关功能
  • innodb 和外部 SQL 引擎部分不是同一种 mutex，实现机制不一样

• PSI_RWLOCK "rwlock instrumentation"

• PSI_COND "condition instrumentation"

• PSI_FILE "file instrumentation"

• PSI_TABLE "table instrumentation"

• PSI_SOCKET "socket instrumentation"

• PSI_STAGE "stage instrumentation"

• PSI_STATEMENT "statement instrumentation"

• PSI_SP "stored procedure instrumentation"

• PSI_PS "prepared statements instances instrumentation"

• PSI_IDLE "idle instrumentation"

• PSI_STATEMENT_DIGEST "statement digest instrumentation"

• PSI_METADATA "metadata instrumentation"

• PSI_MEMORY "memory instrumentation"

• PSI_TRANSACTION "transaction instrumentation"

• PSI_ERROR "server error instrumentation"

• PSI_DATA_LOCK "data lock instrumentation"

  • 相比 pg 的 pg_lock，MySQL 可以更直观的看出谁在等谁，但是 pg 实现的更轻量级

• PSI_TLS_CHANNEL "tls channel instrumentation"

  • 记录 tsl 状态

• PSI_SERVER_TELEMETRY_TRACES "server telemetry traces instrumentation"

  • 实现 telemetry 功能，但是是侵入式实现，且没有见过的东西，可以直接删掉

• PSI_SERVER_TELEMETRY_LOGS "server telemetry logs instrumentation"

• PSI_METRICS "server telemetry metrics instrumentation"

使用案例

架构

作为 MySQL 的一个存储引擎，内部存储各项系统指标，

http://mysql.taobao.org/monthly/2021/09/03/

必要性？

• 内部一些瞬时指标，是否可以直接获取，而不需要存储
• 那些指标是需要体现变化趋势的，pfs 有么

开启之后的内存大小

关闭之后的内存大小

少了大概 300 M

ST_SCHEMA_TABLE

information_schema 中的临时表，用于实现一些实时的内存信息的查询，当前有下面

ST_SCHEMA_TABLE schema_tables[] = {
    {"COLUMN_PRIVILEGES",   column_privileges_fields_info,      fill_schema_column_privileges,  nullptr, nullptr, false},
    {"ENGINES",             engines_fields_info,                fill_schema_engines,            make_old_format,     nullptr, false},
    {"OPEN_TABLES",         open_tables_fields_info,            fill_open_tables,               make_old_format,     nullptr, true},
    {"OPTIMIZER_TRACE",     optimizer_trace_info,               fill_optimizer_trace_info,      nullptr, nullptr, false},
    {"PLUGINS",             plugin_fields_info, fill_plugins,   make_old_format,                nullptr,     false},
    {"PROCESSLIST",         processlist_fields_info,            fill_schema_processlist,        make_old_format, nullptr, false},
    {"PROFILING",           query_profile_statistics_info,      fill_query_profile_statistics_info, make_profile_table_for_show, nullptr,     false},
    {"SCHEMA_PRIVILEGES",   schema_privileges_fields_info,      fill_schema_schema_privileges,  nullptr, nullptr, false},
    {"TABLE_PRIVILEGES",    table_privileges_fields_info,       fill_schema_table_privileges,   nullptr, nullptr, false},
    {"USER_PRIVILEGES",     user_privileges_fields_info,        fill_schema_user_privileges,    nullptr, nullptr, false},
    {"TMP_TABLE_COLUMNS",   tmp_table_columns_fields_info,      show_temporary_tables,      make_tmp_table_columns_format, get_schema_tmp_table_columns_record, true},
    {"TMP_TABLE_KEYS",      tmp_table_keys_fields_info,         show_temporary_tables,     make_old_format, get_schema_tmp_table_keys_record, true},
    {nullptr, nullptr, nullptr, nullptr, nullptr, false}};

在查询的时候，在 add_table_to_list 中会构建相关的 Table_ref 后续在查询中，会使用对应的 fill_xx 填充 table 中 files 的数据

innodb

MySQL 核心存储引擎，核心结构为 BTREE

❯ tree -d
.
|-- api         InnoDB Native API   
|-- arch        Common interface for redo log and dirty page archiver system
|-- btr         btree
|-- buf         The database buffer buf_pool
|-- clone       Innodb Clone Interface
|-- data        SQL data field and tuple
|-- ddl
|-- dict         Data dictionary system
|-- eval         SQL evaluator: evaluates simple data structures, like expressions, in a query graph
|-- fil          The low-level file system
|-- fsp          File space management
|-- fts          Full text search
|-- fut          File-based list utilities
|-- gis          InnoDB R-tree search interfaces
|-- ha           Hash storage.
|-- handler
|-- ibuf         Insert buffer
|-- include
|   `-- detail
|       |-- fil
|       |-- fts
|       `-- ut
|-- lob          Large object (LOB) storage
|-- lock
|-- log          redo log / undo log / ddl log
|-- mach         Utilities for converting data from the database file to the machine format.
|-- mem          The memory management
|-- mtr          Mini-transaction
|-- os
|-- page
|-- pars
|-- que          Query graph
|-- read         Cursor read
|-- rem          Record manager
|-- row
|-- srv
|-- sync
|-- trx
|-- usr
`-- ut

多写几个单词会死...

• 核心是一个 btree，加上并发控制，事务，日志等机制

innodb相关文档

QS:

• 怎么查询表中得隐藏列
• 基于 redo undo log 怎么恢复数据
• 代码结构杂乱无章，怎么拆分模块
• 怎么获得具体的性能指标

btree

http://mysql.taobao.org/monthly/2025/03/03/

• 表也是 btree，称为聚簇索引

  • 有主键得情况下，key是主键
  • 没有主键得情况下，key 是什么？

• page 大小默认 16k，无法配置，压缩之后最小为 1024 字节

• index 指向表的 value 是什么，类似 pg 的ctid 还是具体的值还是其他

并发控制

• 基于 undo 的 lock + mvcc

  • 读写不阻塞
  • 写写的时候使用 lock 保证事务
    • 悲观锁

• lock

• mvcc

  • 快照

• mtr

log

https://www.cnblogs.com/mengxinJ/p/14211427.html

• redo log

  • 记录数据页面的变动，故障之后可以重放
  • pg 记录的是事务的数据的变动，故障恢复之后重放，和 redo 的区别在哪里

• undo log

  • 为支持 mvcc

• binlog

  • 记录所有语句的变动，用于数据同步，故障恢复等

故障恢复

初始化

• innodb_init 填充 handlerton 结构体，handlerton 是存储引擎的核心接口，定义了存储引擎的基本操作，比如初始化、创建表、插入数据等。 当前 innodb 几乎都实现了 handlerton 的所有接口
  • 可能有的接口还是由于 innodb 而添加的，许多接口是 innodb 专用的
  • handlerton 是存储引擎的全局操作 ，和具体的表无关
  • 与表相关的操作是通过继承 handler 实现的，常用的操作例如读index，读表等

相关接口

并行支持

官方实际只支持 count，并没有其余的外部使用案例，我们当前还在 rapid load 的时候使用 具可以参考 row_mysql_parallel_select_count_star 和 LoadTableFromPrimaryTable

并行是 innodb 内部并行，通过注册函数可以把一部分工作下推到 innodb 中

当前提供主要为下面三个接口，

• parallel_scan_init

  • 创建一个 scan ctx，所有线程公用，保存一些公共状态或者数据

• parallel_scan

  • 实际执行，这里会接受几个回调函数，用于实际的执行需求

• parallel_scan_end

  • 最后的清理工作

实际主要操作的是 Parallel_reader，可以参考 row_mysql_parallel_select_count_star ，这里是一个比较完整的可用的是例子

• add_scan

  1. 注册 index，index 就是 btree，可以是实际的表，也可以是 索引
  2. 切分 index，划分reader的任务，每个reader负责一个子树
  3. 注册执行函数，也就是并行是需要具体执行的操作

• run

  1. 启动线程，执行任务
  2. 底层会调用 traverse_recs 执行注册的函数，这里可以获得实际需要处理的数据 m_rec

上面的 innodb 提供的接口只是一个适配器，从 18年就添加的，但是到现在都没有实际的使用例子

sysbench

• sysbench 测试

100W. 

sysbench --threads=16 --time=60 --report-interval=1 /usr/share/sysbench/oltp_common.lua --mysql-host=localhost \
--mysql-port=3306 --mysql-user=root  --mysql-socket=/tmp/mysql.sock --mysql-password=123456 \
--mysql-db=tpch --tables=4 --table-size=1000000 prepare


alter user 'root'@'localhost' identified by  '123456';

create database tpch;

3. 预埋数据（测试机执行） 200W
sysbench --db-driver=mysql --mysql-host=localhost --mysql-socket=/tmp/mysql.sock --mysql-port=3306 --mysql-db=tpch \
--mysql-user=root --mysql-password=123456 --table_size=1000000 --tables=4 --threads=32 \
--events=0 --report-interval=1 --time=60 --percentile=95  --mysql-ignore-errors=all oltp_read_only prepare

4. 跑性能（测试机执行）：
sysbench --db-driver=mysql --mysql-host=localhost --mysql-socket=/tmp/mysql.sock --mysql-port=3306 --mysql-db=tpch \
--mysql-user=root --mysql-password=123456 --table_size=1000000 --tables=4 --threads=32 \
--events=0 --report-interval=1 --time=60 --percentile=95  --mysql-ignore-errors=all --range_selects=0 --skip-trx=1  oltp_read_write run


- oltp_read_only
SQL statistics:
    queries performed:
        read:                            5004920
        write:                           0
        other:                           0
        total:                           5004920
    transactions:                        500492 (8340.12 per sec.)
    queries:                             5004920 (83401.20 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0087s
    total number of events:              500492

Latency (ms):
         min:                                    0.42
         avg:                                    3.83
         max:                                   53.58
         95th percentile:                        7.84
         sum:                              1919257.98

Threads fairness:
    events (avg/stddev):           15640.3750/199.46
    execution time (avg/stddev):   59.9768/0.00

- oltp_read_write
SQL statistics:
    queries performed:
        read:                            239600
        write:                           95823
        other:                           13
        total:                           335436
    transactions:                        23956  (398.81 per sec.)
    queries:                             335436 (5584.18 per sec.)
    ignored errors:                      4      (0.07 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0680s
    total number of events:              23956

Latency (ms):
         min:                                   16.01
         avg:                                   80.18
         max:                                  558.66
         95th percentile:                      179.94
         sum:                              1920901.02

Threads fairness:
    events (avg/stddev):           748.6250/4.61
    execution time (avg/stddev):   60.0282/0.02

binlog

extension

ref

MySQL 插件详解 数据库插件概述 for postgres

ref

MySQL 插件详解 数据库插件概述 for postgres

MySQL 扩展

QS: * 就当前来说，添加新插件的时候需要使用 MYSQL_ADD_PLUGIN 来编写 cmake 文件，这个 func 简单看下来不能独立使用 * 其次是插件的回归是和 MySQL 的回归存放在一起的，不能独立存在 * 再之后是 api 问题，MySQL 的插件的符号直接就是 c++ 的符号，但是总所周知的问题，c++ 的 abi 存在严重的兼容性问题，所以几乎无法提供稳定的独立插件 * 上面两点几乎就限定 MySQL 的插件必须(有独立存在的MySQL插件吗)和源码一起发布 * 再然后是种类限制，当前支持种类有限，具体可以参考 pg 的调查文档

以前可扩展插件的核心操作就是 动态库的加载与卸载，再辅以一定的管理功能，实现功能的扩展

使用方法

• 具体可以参考回归中得某些插件的测试用例

1. 手动加载 INSTALL PLUGIN xx SONAME 'xx';，需要确保 so 文件已经存放在 MySQL 的插件目录下
2. 配置预加载 plugin_load="XXX"

MySQL插件定义

当前 MySQL 插件主要在 plugin 和 storage 目录下面 当前插件种类定义如下

#define MYSQL_UDF_PLUGIN 0                /* User-defined function        */
#define MYSQL_STORAGE_ENGINE_PLUGIN 1     /* Storage Engine               */
#define MYSQL_FTPARSER_PLUGIN 2           /* Full-text parser plugin      */
#define MYSQL_DAEMON_PLUGIN 3             /* The daemon/raw plugin type */
#define MYSQL_INFORMATION_SCHEMA_PLUGIN 4 /* The I_S plugin type */
#define MYSQL_AUDIT_PLUGIN 5              /* The Audit plugin type        */
#define MYSQL_REPLICATION_PLUGIN 6        /* The replication plugin type */
#define MYSQL_AUTHENTICATION_PLUGIN 7     /* The authentication plugin type */
#define MYSQL_VALIDATE_PASSWORD_PLUGIN 8  /* validate password plugin type */
#define MYSQL_GROUP_REPLICATION_PLUGIN 9  /* The Group Replication plugin */
#define MYSQL_KEYRING_PLUGIN 10           /* The Keyring plugin type   */
#define MYSQL_CLONE_PLUGIN 11             /* The Clone plugin type   */
#define MYSQL_QUERY_PLAN_PLUGIN 12 /* The Query Plan plugin type */
#define MYSQL_MAX_PLUGIN_TYPE_NUM 13 /* The number of plugin types */

此外还包含一些宏，用于简化插件的定义操作

mysql_declare_plugin   // 定义 st_mysql_plugin 结构体

MySQL 的插件重点是 storage 插件，最先有 mysiam ，之后实现 innodb，期间不知道 storage 插件和 innodb 谁先谁后，谁为谁服务的，但是可以明确的是当前 MySQL 的插件中，storage 占据了很重的分量

这由于 innodb 才完整的支持事务，所以当前 MySQL 默认使用 innodb，但是又由于历史原因，无法直接摒弃 mysiam，所以现在 MySQL storage 和 上层比较割裂，事务操作都是在下层做的

插件的加载与卸载

mysqld_main()
 |-init_server_components()
   |-plugin_register_dynamic_and_init_all()
     |-plugin_initialize()

• 插件在编译的时候，有的插件是必须使用的，所以是内置的插件，直接编译到 MySQL 的二进制文件中，有的的动态库，需要动态加载，但是无论是静态还是动态，都是从这里 init 的

• 详细的加载过程在 plugin_dl_add 总，使用 dlopen 打开 so 文件，之后检查版本，提取 st_mysql_plugin 的信息

• 之后存放在 plugin_dl_array 中

pfs

可以使用插件的方式实现 pfs，具体可以参考 innodb

• 应该可以使用函数的方式实现，但是 MySQL 不支持 func 作为表，func 无法返回表的结果

workNote

FAQ

Changelog

The end