专栏首页爱可生开源社区故障分析 | MySQL 优化案例 - select count(*)

故障分析 | MySQL 优化案例 - select count(*)

作者:xuty

本文来源:原创投稿 *爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


本文关键字:count、SQL、二级索引

相关文章推荐:

故障分析 | MySQL 优化案例 - 字符集转换

技术分享 | MySQL 监控利器之 Pt-Stalk

一、故事背景

项目组联系我说是有一张 500w 左右的表做 select count(*) 速度特别慢。

二、原 SQL 分析

Server version: 5.7.24-log MySQL Community Server (GPL)

SQL 如下,仅仅就是统计 api_runtime_log 这张表的行数,一条简单的不能再简单的 SQL:

select count(*) from api_runtime_log;

我们先去运行一下这条 SQL,可以看到确实运行很慢,要 40 多秒左右,确实很不正常~

mysql> select count(*) from api_runtime_log;
+----------+
| count(*) |
+----------+
|  5718952 |
+----------+
1 row in set (42.95 sec)

我们再去看下表结构,看上去貌似也挺正常的~存在主键,表引擎也是 InnoDB,字符集也没问题。

CREATE TABLE `api_runtime_log` (
  `BelongXiaQuCode` varchar(50) DEFAULT NULL,
  `OperateUserName` varchar(50) DEFAULT NULL,
  `OperateDate` datetime DEFAULT NULL,
  `Row_ID` int(11) DEFAULT NULL,
  `YearFlag` varchar(4) DEFAULT NULL,
  `RowGuid` varchar(50) NOT NULL,
   ......
  `apiid` varchar(50) DEFAULT NULL,
  `apiname` varchar(50) DEFAULT NULL,
  `apiguid` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`RowGuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

三、执行计划

通过执行计划,我们看下是否可以找到什么问题点。

mysql> explain select count(*) from api_runtime_log \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: api_runtime_log
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 152
          ref: NULL
         rows: 5718952
     filtered: 100.00
      Extra: Using index

可以看到,查询走的是 PRIMARY,也就是主键索引。貌似也没有什么问题,走索引了呀!那么是不是真的就没问题呢?

四、原理

为了找到答案,通过 Google 查找 MySQL 下 select count(*) 的原理,找到了答案。这边省略过程,直接上结果。

简单介绍下原理:

  • 聚簇索引:每一个 InnoDB 存储引擎下的表都有一个特殊的索引用来保存每一行的数据,称为聚簇索引(通常都为主键),聚簇索引实际保存了 B-Tree 索引和行数据,所以大小实际上约等于为表数据量
  • 二级索引:除了聚集索引,表上其他的索引都是二级索引,索引中仅仅存储了对应索引列及主键列

InnoDB 存储引擎中,count(*) 函数是先从内存中读取数据到内存缓冲区,然后进行扫描获得行记录数。这里 InnoDB 会优先走二级索引;如果同时存在多个二级索引,会选择key_len 最小的二级索引;如果不存在二级索引,那么会走主键索引;如果连主键都不存在,那么就走全表扫描

这里我们由于走的是主键索引,所以 MySQL 需要先把整个主键索引读取到内存缓冲区,这是个从磁盘读写到内存的过程,而且主键索引基本等于整个表数据量(10GB+),所以非常耗时!

那么如何解决呢?

答案就是:建二级索引。

因为二级索引只包含对应的索引列及主键列,所以体积非常小。在 select count(*) 的查询过程中,只需要将二级索引读取到内存缓冲区,只有几十 MB 的数据量,所以速度会非常快。

举个形象的比喻,我们想知道一本书的页数:

  • 走聚集索引:从第一页翻到最后一页,知道总页数;
  • 走二级索引:通过目录直接知道总页数。

五、验证

创建二级索引后,再次执行 SQL 及查看执行计划。

mysql> create index idx_rowguid on api_runtime_log(rowguid);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from api_runtime_log;
+----------+
| count(*) |
+----------+
|  5718952 |
+----------+
1 row in set (0.89 sec)

mysql> explain select count(*) from api_runtime_log \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: api_runtime_log
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_rowguid
      key_len: 152
          ref: NULL
         rows: 5718952
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

可以看到添加二级索引后,确实速度明显变快,而且执行计划也变成了走二级索引。至此这个问题其实已经解决了,就是由于表上缺少二级索引导致

六、深入测试

为了进一步验证上述的推论,所以就做了如下的测试。

测试过程如下:

1. 通过 sysbench 创建了一张 500W 的测试表 sbtest1,表上仅仅包含一个主键索引,表大小为 1125MB;

2. 调整部分 MySQL 参数,重启 MySQL,保证目前 innodb buffer pool (内存缓冲区) 中为空,不缓存任何数据;

3. 执行 select count(*),理论上走主键索引,查看当前内存缓冲区中缓存的数据量(理论上会缓存整个聚簇索引);

4. 在测试表 sbtest1添加二级索引,索引大小为 55MB;

5. 再次重启 MySQL,保证内存缓冲区为空;

6. 再次执行 select count(*),理论上走二级索引

7. 再次查看内存缓冲区中缓存的数据量(理论上只会缓存二级索引)。

测试结果如下:

1. 聚簇索引

查询当前内存缓冲区状态,结果为空证明不缓存测试表数据。

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test';
Empty set (1.92 sec)

mysql>  select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
|  5188434 |
+----------+
1 row in set (5.52 sec)

再次查看内存缓冲区,发现缓存了 sbtest1 表上 1G 多的数据,基本等于整个表数据量。

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test' \G;
*************************** 1. row ***************************
object_schema: test
  object_name: sbtest1
    allocated: 1.08 GiB
         data: 1.01 GiB
        pages: 71081
 pages_hashed: 0
    pages_old: 28119
  rows_cached: 5189798

最后我们再来看下执行计划,确实走的是主键索引,放在最后执行是为了避免影响缓冲区。

mysql> explain  select count(*) from test.sbtest1 \G;                                          
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 5117616
     filtered: 100.00
        Extra: Using index

2. 二级索引

创建二级索引 idx_id,查看 sbtest1 表上主键索引与二级索引的数据量。

mysql> create index idx_id on sbtest1(id);
Query OK, 0 rows affected (12.97 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT sum(stat_value) pages ,index_name ,
(round((sum(stat_value) * @@innodb_page_size)/1024/1024)) as MB 
  FROM mysql.innodb_index_stats 
  WHERE table_name = 'sbtest1' 
  AND database_name = 'test' 
  AND stat_description = 'Number of pages in the index' 
  GROUP BY index_name;
+-------+------------+------+
| pages | index_name | MB   |
+-------+------------+------+
| 72000 | PRIMARY    | 1125 |
|  3492 | idx_id     |   55 |
+-------+------------+------+

重启 MySQL,再次查看缓冲区同样为空,证明没有缓存测试表上的数据。

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test';
Empty set (1.49 sec)

mysql> select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
|  5188434 |
+----------+
1 row in set (2.92 sec)

再次查看内存缓冲区,发现仅仅缓存了 sbtest1 表上的 50M 数据,约等于二级索引的数据量。

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test' \G;
*************************** 1. row ***************************
object_schema: test
  object_name: sbtest1
    allocated: 49.48 MiB
         data: 46.41 MiB
        pages: 3167
 pages_hashed: 0
    pages_old: 1575
rows_cached: 2599872

最后确认下执行计划,确实走的是二级索引。

mysql> explain select count(*) from test.sbtest1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_id
      key_len: 4
          ref: NULL
         rows: 5117616
     filtered: 100.00
        Extra: Using index

七、案例总结

从上述这个测试结果可以看出,和之前的推论基本吻合。

如果 select count(*) 走的是主键索引,那么会缓存整个表数据,大量查询时间会花费在读取表数据到缓冲区。

如果存在二级索引,那么只需要读取索引页到缓冲区即可,速度自然快。

另:项目上由于磁盘性能层次不齐,所以当遇上这种情况时,性能较差的磁盘更会放大这个问题;一张超级大表,统计行数时如果走了主键索引,后果可想而知~

八、优化建议

此次测试过程中我们仅仅模拟是百万数据量,此时我们通过二级索引统计表行数,只需要读取几十 M 的数据量,就可以得到结果。

那么当我们的表数据量是上千万,甚至上亿时呢。此时即便是最小的二级索引也是 几百 M、过 G 的数据量,如果继续通过二级索引来统计行数,那么速度就不会如此迅速了。

这个时候可以通过避免直接 select count(*) from table 来解决,方法较多,例如:

1. 使用 MySQL 触发器 + 统计表实时计算表数据量;

2. 使用 MyISAM 替换 InnoDB,因为 MyISAM 自带计数器,坏处就不多说了;

3. 通过 ETL 导入表数据到其他更高效的异构环境中进行计算;

4. 升级到 MySQL 8 中,使用并行查询,加快检索速度。

当然,什么时候 InnoDB 存储引擎可以直接实现计数器的功能就好了!

本文分享自微信公众号 - 爱可生开源社区(ActiontechOSS),作者:xuty

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-07-07

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 技术分享 | 常见索引问题处理

    数据库技术爱好者,爱可生 DBA 团队成员,负责 MySQL 日常问题处理以及数据库运维平台的问题排查,擅长 MySQL 主从复制及优化,喜欢钻研技术问题,还有...

    爱可生开源社区
  • 技术分享 | 连接数据库这个操作做了什么?

    MySQL DBA,擅长 python 和 SQL,目前维护着 github 的两个开源项目:mysqltools 、dbmc 以及独立博客:https://w...

    爱可生开源社区
  • MySQL 8.0.20 正式发行(GA)

    MySQL 的最新版本 8.0.20 正式发行。与之前 8.0 的系列版本一样,这次的发行版,除了包含缺陷修复,也同样包括新功能。让我们快速浏览一下。

    爱可生开源社区
  • MySQL专题- 数据库索引使用场景&注意事项

    用户1081422
  • 【MySQL性能调优】-关于索引的那些事儿(一)

    数据库和索引的关系就像新华字典和目录的关系一样,索引存在的目的就是为了提高数据查询效率。索引其实就是一种数据结构,存储引擎能通过索引能快速找到你想要的数据。尤...

    MySQL数据库技术栈
  • MySQL索引优化实战

    众所周知,索引类似于字典的目录,可以提高查询的效率。 索引从物理上可以分为:聚集索引,非聚集索引 从逻辑上可以分为:普通索引,唯一索引,主键索引,联合索引,全文...

    Java识堂
  • MySQL——索引优化实战

    上篇文章中介绍了索引的基本内容,这篇文章我们继续介绍索引优化实战。在介绍索引优化实战之前,首先要介绍两个与索引相关的重要概念,这两个概念对于索引优化至关重要。

    撸码那些事
  • 这句简单的sql,如何加索引?颠覆了我多年的认知

    不啰嗦,直接入正题。问题是这样的。请问下面的sql语句,要想加快查询速度,该怎么创建索引?以下,以mysql数据库为准。

    xjjdog
  • 一线互联网公司必问的MySql优化神器

    使用EXPLAIN关键字可以模拟优化器执行SQL语句,分析查询语句或是结构的性能瓶颈。在select语句之前增加explaion关键字,MySQL会在查询上设置...

    程序员小强
  • Android关于微信小程序的唤起和分享

    这个错误可能是由于创建 IWXAPI 实例的 APP_KEY 没有和小程序进行绑定, WXAPIFactory.createWXAPI(mContext, i...

    静默加载

扫码关注云+社区

领取腾讯云代金券