GROUP BY 查询为何是该索引唯一提供的时间改进方式？

在IT行业这个发展更新速度很快的行业，只有不停止的学习，才不会被行业所淘汰。如果你是Golang学习者，那么本文《GROUP BY 查询为何是该索引唯一提供的时间改进方式？》就很适合你！本篇内容主要包括##content_title##，希望对大家的知识积累有所帮助，助力实战开发！

我正在运行 mysql 5.6 并使用 go 构建应用程序。我有一个无法优化的顽固查询，因此我试图将其分解为最简单的组件。根本问题是group by 列上的索引在运行时提供持续改进，而不是我期望的对数性能。

这是一个带有基准的示例。鉴于此 data.go：

package data

import (
    "database/sql"
    "log"

    _ "github.com/go-sql-driver/mysql"
)

type groupcount struct {
    cohort string
    cnt    uint
}

func groupbyquery(db *sql.db) ([]groupcount, error) {

    var counts []groupcount

    res, err := db.query(`
        select cohort, count(cohort) as cnt
        from test_table
        group by cohort
    `)
    defer res.close()

    if err != nil {
        log.println(err)
        return []groupcount{}, err
    }

    for res.next() {
        var gc groupcount
        err := res.scan(&gc.cohort, &gc.cnt)
        if err != nil {
            return []groupcount{}, err
        }

        counts = append(counts, gc)
    }

    return counts, nil

}

和data_test.go：

package data

import (
    "database/sql"
    "fmt"
    "math/rand"
    "testing"
    "time"
)

func benchmarkgroupbyquery(b *testing.b) {

    // local db connection
    db, err := sql.open("mysql", "dbuser:dbpass@tcp(localhost:3306)/testdb")
    defer db.close()

    // declare db table
    db.exec("drop table test_table")

    _, err = db.exec("create table test_table (id int, cohort varchar(255))")
    if err != nil {
        b.fatal(err)
    }
    // comment in or out to test index
    // _, err = db.exec("create index idx_cohort on test_table (cohort)")
    // if err != nil {
    //  b.fatal(err)
    // }

    // insert some data into the table
    n := 100000
    stmt := "insert into test_table values (%d, %s)"

    rand.seed(time.now().unixnano())

    for i := 0; i < n; i++ {
        j := rand.intn(5)
        insertstmt := fmt.sprintf(stmt, i, fmt.sprintf("\"group%d\"", j))

        _, err := db.exec(insertstmt)
        if err != nil {
            b.error(err)
        }
    }

    b.resettimer()

    // access and print results
    for i := 0; i < b.n; i++ {
        groupbyquery(db)
    }
}

要进行复制，您需要在 mysql 中设置具有用户访问权限的适当数据库。使用变量 n 运行给定的基准测试（ go test . --bench benchmarkgroupbyquery ），我得到以下结果：

unindexed
i = 100    :   0.5 ms/op
i = 1000   :   2.3 ms/op
i = 10000  :  20.1 ms/op
i = 100000 : 215.6 ms/op
indexed
i = 100    :  0.2 ms/op
i = 1000   :  0.7 ms/op
i = 10000  :  6.0 ms/op
i = 100000 : 59.6 ms/op

在给定分为 5 组的大型数据集的情况下，我可以验证带索引和不带索引的查询是否会给出不同的执行计划，指示使用或不使用索引。

没有索引

mysql> explain select cohort, count(cohort) as cnt from test_table group by cohort;
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | extra                           |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | simple      | test_table | all  | null          | null | null    | null | 100256 | using temporary; using filesort |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.00 sec)

带索引

mysql> explain select cohort, count(cohort) as cnt from test_table group by cohort;
+----+-------------+------------+-------+---------------+------------+---------+------+--------+-------------+
| id | select_type | table      | type  | possible_keys | key        | key_len | ref  | rows   | extra       |
+----+-------------+------------+-------+---------------+------------+---------+------+--------+-------------+
|  1 | simple      | test_table | index | idx_cohort    | idx_cohort | 768     | null | 100256 | using index |
+----+-------------+------------+-------+---------------+------------+---------+------+--------+-------------+
1 row in set (0.00 sec)

最后，这是查询本身的结果（考虑到基准测试中的设置，这有点随机）

mysql> SELECT cohort, COUNT(cohort) AS cnt FROM test_table GROUP BY cohort;
+--------+-------+
| cohort | cnt   |
+--------+-------+
| group0 | 19928 |
| group1 | 19791 |
| group2 | 19916 |
| group3 | 20282 |
| group4 | 20083 |
+--------+-------+
5 rows in set (0.07 sec)

这些结果让我非常惊讶。本质上，在我们分组的列上添加索引可以提供约 0.3 的大致恒定的运行时间改进系数。我不明白为什么它不能提供对数的运行时改进，或者根本没有改进。

正确答案

SELECT cohort, COUNT(*) AS cnt
    FROM test_table
    GROUP BY cohort

以“线性”时间运行。即 o(n)。您的计时显示（小表除外）排序 10 倍的行需要 10 倍的时间。

由于解析查询、打开表和其他“开销”的开销，100 行测试和 1000 行测试并没有相差整整 10 倍。对于较大的表，该开销会被摊销。

不带索引和带索引的区别很简单。该查询将通过以下两种方式之一执行：

• 没有有用的索引：表扫描（cf all）。将读取整个表格。
• index(cohort)：索引扫描（参见“使用索引”）。将读取整个索引。

表和索引分别存储在各自的 btree 中。

• 完整表的 btree 包含所有列。
• 索引的 btree 仅包含 cohort 和 primary key 的列。

索引的btree较小，因此完整扫描所需的时间较少。

优化器可以通过多种方式执行此类计数。

a 计划：（非索引）在 ram 中构建一个哈希表并在遇到队列时对其进行计数。 计划 b：（有索引）遍历索引，一次计算一个值的出现次数。 计划 c：（非索引，但决定反对计划 a）将所有值收集到临时表中，对该表进行排序，然后执行计划 b。

我不知道有什么方法可以强制使用 c 计划。由于排序是 o(n*logn)，这就是您获得“对数”的地方吗？

终于介绍完啦！小伙伴们，这篇关于《GROUP BY 查询为何是该索引唯一提供的时间改进方式？》的介绍应该让你收获多多了吧！欢迎大家收藏或分享给更多需要学习的朋友吧~golang学习网公众号也会发布Golang相关知识，快来关注吧！