MySQL8.0GroupbyPerformance

作者：过期物品请勿购_613 | 来源：互联网 | 2022-12-16 12:01

如何解决《MySQL8.0GroupbyPerformance》经验，为你挑选了1个好方法。

为了比较MySQL 5.7和8.0,我使用sysbench创建了一个表.我试过了这个测试.服务器的性能完全相同因此,oltp_point_select显示出几乎相似的性能.

但是,当通过下面的测试进行组时,MySQL 8.0的性能提高了10倍.但我不知道为什么它很快.

我不知道是否可以找到MySQL 8.0发行说明.在8.0,谁会告诉我为什么分组更快？

0.表架构

CREATE TABLE `sbtest2` (

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_2` (`k`),
  KEY `idx1` (`k`,`pad`)
) ENGINE=InnoDB AUTO_INCREMENT=1099289 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

1. MySQL 5.7 服务器版本:5.7.17-log源代码分发

5.7结果

    testdb 17:24:38 > select count(0),pad from sbtest2 where k between 400000 and 600000 group by pad limit 1;
        +----------+-------------------------------------------------------------+
        | count(0) | pad                                                         |
        +----------+-------------------------------------------------------------+
        |        1 | 00000012813-73478546096-60491222567-55128816668-26346684571 |
        +----------+-------------------------------------------------------------+
        **1 row in set (21.88 sec)**

testdb 17:21:30 > explain select count(0),pad from sbtest2 where k between 400000 and 600000 group by pad limit 10\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: sbtest2
       partitions: NULL
             type: range
    possible_keys: k_2,idx1
              key: idx1
          key_len: 4
              ref: NULL
             rows: 493200
         filtered: 100.00
            Extra: Using where; Using index; Using temporary; Using filesort
    1 row in set, 1 warning (0.00 sec)


    17:55:17 > show profile;
        +---------------------------+-----------+
        | Status                    | Duration  |
        +---------------------------+-----------+
        | starting                  |  0.000083 |
        | checking permissions      |  0.000008 |
        | Opening tables            |  0.000017 |
        | init                      |  0.000032 |
        | System lock               |  0.000013 |
        | optimizing                |  0.000010 |
        | statistics                |  0.076138 |
        | preparing                 |  0.000018 |
        | Creating tmp table        |  0.000033 |
        | Sorting result            |  0.000005 |
        | executing                 |  0.000003 |
        | Sending data              |  2.790353 |
        | converting HEAP to ondisk |  1.709809 |
        | Sending data              | 14.403732 |
        | Creating sort index       |  2.236432 |
        | end                       |  0.000014 |
        | query end                 |  0.000012 |
        | removing tmp table        |  0.017293 |
        | query end                 |  0.000007 |
        | closing tables            |  0.000011 |
        | freeing items             |  0.000252 |
        | logging slow query        |  0.000055 |
        | cleaning up               |  0.000014 |
        +---------------------------+-----------+

2. MySQL 8.0 Server版本:8.0.11 MySQL社区服务器 - GPL

结果

 testdb 17:24:24 > select count(0),pad from sbtest2 where k between 400000 and 600000 group by pad limit 1;
    +----------+-------------------------------------------------------------+
    | count(0) | pad                                                         |
    +----------+-------------------------------------------------------------+
    |        1 | 30506873183-39276415649-14634764539-17515472140-54546407224 |
    +----------+-------------------------------------------------------------+
    **1 row in set (2.32 sec)**

 testdb 17:23:29 > explain select count(0),pad from sbtest2 where k between 400000 and 600000 group by pad limit 10\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: sbtest2
       partitions: NULL
             type: range
    possible_keys: k_2,idx1
              key: idx1
          key_len: 4
              ref: NULL
             rows: 493200
         filtered: 100.00
            Extra: Using where; Using index; Using temporary
    1 row in set, 1 warning (0.04 sec)


    testdb 17:55:00 > show profile;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000103 |
    | checking permissions | 0.000018 |
    | Opening tables       | 0.000051 |
    | init                 | 0.000008 |
    | System lock          | 0.000013 |
    | optimizing           | 0.000010 |
    | statistics           | 0.000144 |
    | preparing            | 0.000024 |
    | Creating tmp table   | 0.000042 |
    | executing            | 0.000004 |
    | Sending data         | 2.407031 |
    | end                  | 0.000018 |
    | query end            | 0.000019 |
    | removing tmp table   | 0.087750 |
    | query end            | 0.000020 |
    | closing tables       | 0.000018 |
    | freeing items        | 0.000193 |
    | logging slow query   | 0.000061 |
    | cleaning up          | 0.000015 |
    +----------------------+----------+

@Rick James 5.7的sbtest2表被转储并应用于8.0.现在两个表格完全相同.

5_7.testdb 18:25:13 > select @@version;
+------------+
| @@version  |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.00 sec)

5_7.testdb 18:25:16 > select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
|  1099288 |
+----------+
1 row in set (0.17 sec)

5_7.testdb 18:25:22 > select count(0),pad from sbtest2 where k between 400000 and 500000 group by pad order by pad limit 1;
+----------+-------------------------------------------------------------+
| count(0) | pad                                                         |
+----------+-------------------------------------------------------------+
|        1 | 00000012813-73478546096-60491222567-55128816668-26346684571 |
+----------+-------------------------------------------------------------+
1 row in set (7.34 sec)

5_7.testdb 18:26:15 > explain select count(0),pad from sbtest2 where k between 400000 and 500000 group by pad order by pad limit 1;        
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                                     |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | sbtest2 | NULL       | range | k_2,idx1      | idx1 | 4       | NULL | 542160 |   100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select count(0) AS `count(0)`,`testdb`.`sbtest2`.`pad` AS `pad` from `testdb`.`sbtest2` where (`testdb`.`sbtest2`.`k` between 400000 and 500000) group by `testdb`.`sbtest2`.`pad` order by `testdb`.`sbtest2`.`pad` limit 1
5_7.testdb 18:26:27 > explain format=json select count(0),pad from sbtest2 where k between 400000 and 500000 group by pad order by pad limit 1;        
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "232810.86"
    },
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "table": {
          "table_name": "sbtest2",
          "access_type": "range",
          "possible_keys": [
            "k_2",
            "idx1"
          ],
          "key": "idx1",
          "used_key_parts": [
            "k"
          ],
          "key_length": "4",
          "rows_examined_per_scan": 542160,
          "rows_produced_per_join": 542160,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "124378.86",
            "eval_cost": "108432.00",
            "prefix_cost": "232810.86",
            "data_read_per_join": "380M"
          },
          "used_columns": [
            "id",
            "k",
            "pad"
          ],
          "attached_condition": "(`testdb`.`sbtest2`.`k` between 400000 and 500000)"
        }
      }
    }
  }
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

8_0.testdb 18:25:00 >  select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

8_0.testdb 18:25:25 > select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
|  1099288 |
+----------+
1 row in set (0.16 sec)

8_0.testdb 18:25:29 > select count(0),pad from sbtest2 where k between 400000 and 500000 group by pad order by pad limit 1;
+----------+-------------------------------------------------------------+
| count(0) | pad                                                         |
+----------+-------------------------------------------------------------+
|        1 | 00000012813-73478546096-60491222567-55128816668-26346684571 |
+----------+-------------------------------------------------------------+
1 row in set (1.27 sec)

8_0.testdb 18:26:04 > explain select count(0),pad from sbtest2 where k between 400000 and 500000 group by pad order by pad limit 1;        
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                                     |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | sbtest2 | NULL       | range | k_2,idx1      | idx1 | 4       | NULL | 503160 |   100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select count(0) AS `count(0)`,`testdb`.`sbtest2`.`pad` AS `pad` from `testdb`.`sbtest2` where (`testdb`.`sbtest2`.`k` between 400000 and 500000) group by `testdb`.`sbtest2`.`pad` order by `testdb`.`sbtest2`.`pad` limit 1
8_0.testdb 18:26:23 > explain format=json select count(0),pad from sbtest2 where k between 400000 and 500000 group by pad order by pad limit 1;        
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "104331.96"
    },
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "table": {
          "table_name": "sbtest2",
          "access_type": "range",
          "possible_keys": [
            "k_2",
            "idx1"
          ],
          "key": "idx1",
          "used_key_parts": [
            "k"
          ],
          "key_length": "4",
          "rows_examined_per_scan": 503160,
          "rows_produced_per_join": 503160,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "54015.96",
            "eval_cost": "50316.00",
            "prefix_cost": "104331.96",
            "data_read_per_join": "353M"
          },
          "used_columns": [
            "id",
            "k",
            "pad"
          ],
          "attached_condition": "(`testdb`.`sbtest2`.`k` between 400000 and 500000)"
        }
      }
    }
  }
} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

oysteing.. 6

MySQL 8.0使用新的存储引擎TempTable作为内部临时表.(有关详细信息,请参阅MySQL手册.)此引擎没有每个表的最大内存限制,但是所有内部表都有一个公共内存池.它也有自己的磁盘溢出机制,并且不像早期版本那样溢出到InnoDB或MyISAM.

5.7的配置文件包含"将HEAP转换为ondisk".这意味着表达到了MEMORY引擎的最大表大小(默认为16 MB),数据将传输到InnoDB.之后的大部分时间都花在访问InnoDB中的临时表上.在MySQL 8.0中,临时表的内存池的默认大小为1 GB,因此在这种情况下可能不会有任何磁盘溢出.

1> oysteing..：

推荐阅读

hook
VScode格式化文档换行或不换行的设置方法

本文介绍了在VScode中设置格式化文档换行或不换行的方法，包括使用插件和修改settings.json文件的内容。详细步骤为：找到settings.json文件，将其中的代码替换为指定的代码。 ... [详细]

蜡笔小新 2023-12-14 17:15:38
main
阿里云物联网 .NET Core 客户端 | CZGL.AliIoTClient：4. 设备上报属性

阿,里,云,物,联网,net,core,客户端,czgl,aliiotclient, ... [详细]

蜡笔小新 2023-12-14 12:40:20
list
eclipse学习（第三章：ssh中的Hibernate）——11.Hibernate的缓存（2级缓存，get和load）

本文介绍了eclipse学习中的第三章内容，主要讲解了ssh中的Hibernate的缓存，包括2级缓存和get方法、load方法的区别。文章还涉及了项目实践和相关知识点的讲解。 ... [详细]

蜡笔小新 2023-12-14 00:31:35
list
Win7如何显示桌面及快捷方式

本文介绍了三种方法来实现在Win7系统中显示桌面的快捷方式，包括使用任务栏快速启动栏、运行命令和自己创建快捷方式的方法。具体操作步骤详细说明，并提供了保存图标的路径，方便以后使用。 ... [详细]

蜡笔小新 2023-12-13 13:15:57
list
Spring源码解密之默认标签的解析方式分析

本文分析了Spring源码解密中默认标签的解析方式。通过对命名空间的判断，区分默认命名空间和自定义命名空间，并采用不同的解析方式。其中，bean标签的解析最为复杂和重要。 ... [详细]

蜡笔小新 2023-12-14 17:24:50
list
如何去除Win7快捷方式的箭头

本文介绍了如何去除Win7快捷方式的箭头的方法，通过生成一个透明的ico图标并将其命名为Empty.ico，将图标复制到windows目录下，并导入注册表，即可去除箭头。这样做可以改善默认快捷方式的外观，提升桌面整洁度。 ... [详细]

蜡笔小新 2023-12-14 16:17:05
go
Android Studio Bumblebee | 2021.1.1（大黄蜂版本使用介绍）

本文介绍了Android Studio Bumblebee | 2021.1.1（大黄蜂版本）的使用方法和相关知识，包括Gradle的介绍、设备管理器的配置、无线调试、新版本问题等内容。同时还提供了更新版本的下载地址和启动页面截图。 ... [详细]

蜡笔小新 2023-12-14 10:34:15
go
sklearn数据集库中的常用数据集类型介绍

本文介绍了sklearn数据集库中常用的数据集类型，包括玩具数据集和样本生成器。其中详细介绍了波士顿房价数据集，包含了波士顿506处房屋的13种不同特征以及房屋价格，适用于回归任务。 ... [详细]

蜡笔小新 2023-12-13 17:45:15
express
用SQL语句怎么把已存在的列加上IDENTITY(1,1)属性

ALTERTABLE通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。语法ALTERTABLEtable{[ALTERCOLUMNcolu ... [详细]

蜡笔小新 2023-12-13 09:49:28
main
Java学习笔记之面向对象编程（OOP）

本文介绍了Java学习笔记中的面向对象编程（OOP）内容，包括OOP的三大特性（封装、继承、多态）和五大原则（单一职责原则、开放封闭原则、里式替换原则、依赖倒置原则）。通过学习OOP，可以提高代码复用性、拓展性和安全性。 ... [详细]

蜡笔小新 2023-12-13 08:44:30
window
C++字符字符串处理及字符集编码方案

本文介绍了C++中字符字符串处理的问题，并详细解释了字符集编码方案，包括UNICODE、Windows apps采用的UTF-16编码、ASCII、SBCS和DBCS编码方案。同时说明了ANSI C标准和Windows中的字符/字符串数据类型实现。文章还提到了在编译时需要定义UNICODE宏以支持unicode编码，否则将使用windows code page编译。最后，给出了相关的头文件和数据类型定义。 ... [详细]

蜡笔小新 2023-12-13 04:59:58
go
Go Cobra命令行工具入门教程

本文介绍了Go语言实现的命令行工具Cobra的基本概念、安装方法和入门实践。Cobra被广泛应用于各种项目中，如Kubernetes、Hugo和Github CLI等。通过使用Cobra，我们可以快速创建命令行工具，适用于写测试脚本和各种服务的Admin CLI。文章还通过一个简单的demo演示了Cobra的使用方法。 ... [详细]

蜡笔小新 2023-12-12 20:02:41
text
iOS数据库Sqlite的SQL语句分类和常见约束关键字

本文介绍了iOS数据库Sqlite的SQL语句分类和常见约束关键字。SQL语句分为DDL、DML和DQL三种类型，其中DDL语句用于定义、删除和修改数据表，关键字包括create、drop和alter。常见约束关键字包括if not exists、if exists、primary key、autoincrement、not null和default。此外，还介绍了常见的数据库数据类型，包括integer、text和real。 ... [详细]

蜡笔小新 2023-12-12 18:42:03
php
CentOS 6.5安装VMware Tools及共享文件夹显示问题解决方法

本文介绍了在CentOS 6.5上安装VMware Tools及解决共享文件夹显示问题的方法。包括清空CD/DVD使用的ISO镜像文件、创建挂载目录、改变光驱设备的读写权限等步骤。最后给出了拷贝解压VMware Tools的操作。 ... [详细]

蜡笔小新 2023-12-12 18:18:49
text
在Xamarin XAML语言中如何在页面级别构建ControlTemplate控件模板

本文介绍了在Xamarin XAML语言中如何在页面级别构建ControlTemplate控件模板的方法和步骤，包括将ResourceDictionary添加到页面中以及在ResourceDictionary中实现模板的构建。通过本文的阅读，读者可以了解到在Xamarin XAML语言中构建控件模板的具体操作步骤和语法形式。 ... [详细]

蜡笔小新 2023-12-12 17:52:50

过期物品请勿购_613

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章