V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
cleveryun
V2EX  ›  MySQL

mysql 这个查询速度正常吗,怎么优化?

  •  1
     
  •   cleveryun ·
    Yakima-Teng · 128 天前 · 5548 次点击
    这是一个创建于 128 天前的主题,其中的信息可能已经有所发展或是发生改变。

    买的数据库是阿里云的,配置信息:

    • 数据库类型:MySQL8.0
    • 规格族:通用型
    • CPU:2 核
    • 数据库内存:16384 M
    • 规格默认连接数:8000
    • 最大 IOPS:6800
    • 实例规格:mysql.n8m.medium.2c

    目前有 4 千万不到的数据,我拆成了 8 个表,每个表放 500 万行数据。单张表的表结构如下:

    create table `bio-hub`.`pubmed-article-0`
    (
        pm_id             int           not null
            primary key,
        title             varchar(2000) not null,
        author            text          not null,
        lang              varchar(255)  null,
        abstract          text          null,
        keywords          text          null,
        journal_title     varchar(255)  null,
        journal_pub_year  varchar(255)  null,
        journal_pub_month varchar(255)  null,
        journal_i_s_s_n   varchar(255)  null,
        mesh_ids          varchar(2000) null,
        mesh_cat          varchar(2000) null comment '医学主题词所属分类,如`A01`',
        created_at        datetime      not null,
        updated_at        datetime      not null
    );
    
    create index `pubmed-article-0_journal_pub_year`
        on `bio-hub`.`pubmed-article-0` (journal_pub_year);
    

    现状是我再 DataGrip 里光执行下面这样一句 count 都要三四十秒(首次,没缓存的情况下),是我哪里姿势不对吗,这也太慢了。带上关键词查询的 sql 不得更慢了。怎么破?

    更新:我人在上海,数据库节点也是上海的。

    SELECT COUNT(1) FROM `pubmed-article-1`;
    
    第 1 条附言  ·  119 天前

    参考大家的建议,这几天做了一些优化,量一上来还是很慢,感觉没辙了,下面是新的表结构。

    create table if not exists `bio-hub`.`pubmed-article`
    (
        pm_id             int           not null
            primary key,
        title             varchar(2000) not null,
        author            text          not null,
        lang              char(3)       not null,
        abstract          text          not null,
        keywords          text          not null,
        journal_title     char(239)     not null,
        journal_pub_year  smallint      not null,
        journal_pub_month tinyint       not null,
        journal_i_s_s_n   char(9)       not null,
        mesh_ids          varchar(543)  not null,
        mesh_cat          char(3)       not null,
        created_at        datetime      not null,
        updated_at        datetime      not null
    );
    
    create index `pubmed-article_journal_pub_year`
        on `bio-hub`.`pubmed-article` (journal_pub_year);
    
    create fulltext index title_index
        on `bio-hub`.`pubmed-article` (title);
    
    第 2 条附言  ·  119 天前
    然后执行这条语句:
    ```sql
    SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 0

    ```
    返回的 count 结果是 14375917 (我把最近 20 年的数据放到了 pubmed-article 表中)。
    用时:1 row retrieved starting from 1 in 3 s 194 ms (execution: 3 s 159 ms, fetching: 35 ms)


    ```sql
    SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 2022;
    ```
    返回 count 结果是 10907 。
    用时:1 row retrieved starting from 1 in 135 ms (execution: 116 ms, fetching: 19 ms)

    ```sql
    SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 2022 AND title like '%test%';
    ```
    返回 count 结果是 184 。
    用时:1 row retrieved starting from 1 in 110 ms (execution: 88 ms, fetching: 22 ms)。


    ```sql
    SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 2022 AND MATCH (title) AGAINST ('hello' IN NATURAL LANGUAGE MODE);
    ```
    返回 count 结果是 8621 。
    用时:1 row retrieved starting from 1 in 17 m 45 s 295 ms (execution: 17 m 45 s 257 ms, fetching: 38 ms)



    ```sql
    SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 2010
    ```
    返回 count 结果是 10135085 。
    用时:1 row retrieved starting from 1 in 2 s 178 ms (execution: 2 s 147 ms, fetching: 31 ms)


    ```sql
    SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 2010 AND title like '%test%';
    ```
    返回 count 结果是 197070 。
    用时:1 row retrieved starting from 1 in 1 m 39 s 260 ms (execution: 1 m 39 s 221 ms, fetching: 39 ms)



    ```sql
    explain SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 2010 AND title like '%test%';
    ```
    返回:
    ```json
    [
    {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "pubmed-article",
    "partitions": null,
    "type": "ALL",
    "possible_keys": "pubmed-article_journal_pub_year",
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": 14042175,
    "filtered": 5.55,
    "Extra": "Using where"
    }
    ]
    ```
    第 3 条附言  ·  118 天前
    数据量的话,我用 datagrip 的导出到文件(多行)试了下导出 750 万行大概 9.5GB 大小。估算 4000 万行数据大学对应 50GB 空间。
    第 4 条附言  ·  118 天前
    这是不是意味着我需要买台 64G 运行内存的数据库/服务器?
    64 条回复    2023-11-10 12:20:18 +08:00
    rimutuyuan
        1
    rimutuyuan  
       128 天前
    不正常,尝试用数据库同地域的服务器测试下
    winglight2016
        2
    winglight2016  
       128 天前
    配置低了,我们这里 4000 多万条数据,也是 mysql ,机器配置高一些,count 一下也需要 17 秒左右。

    count 是全表扫描,这个速度算是正常吧。

    如果是查询 limit 1000 以内,可以 1 秒左右返回。
    errZX
        3
    errZX  
       128 天前 via Android
    打个索引看看,不走索引的话估计比较难受
    xoxo419
        4
    xoxo419  
       128 天前 via iPhone
    count 走的全表扫描,where 后的字段加索引后应该是很快的,如果需要频繁统计总数再建立一张统计表 用来专门保存统计的数据
    ZZ74
        5
    ZZ74  
       128 天前
    count(journal_pub_year ) 试一试?
    ZZ74
        6
    ZZ74  
       128 天前
    或者 count(pm_id) 试一试?
    mayli
        7
    mayli  
       128 天前
    500 万行 = 5M 行, 你要是不需要 InnoDB 的特性,试试用 MyISAM 。
    一般这种静态表可以不用 InnoDB ,如果必须要 InnoDB 看看增大 innodb_buffer_pool_size 。
    mayli
        8
    mayli  
       128 天前   ❤️ 1
    我测试了一下,甚至 sqlite3 也没有这么慢

    $ sqlite3 test.db
    SQLite version 3.40.1 2022-12-28 14:03:47
    Enter ".help" for usage hints.
    sqlite> create table random_data as
    with recursive tmp(x) as (
    select random()
    union all
    select random() from tmp
    limit 5000000
    )
    sqlite> select count(1) from random_data;
    5000000

    $ time sqlite3 test.db 'select count(1) from random_data;'
    5000000

    real 0m0.175s
    user 0m0.124s
    sys 0m0.051s
    fredcc
        9
    fredcc  
       128 天前 via Android
    mysql.n8m.medium.2c 是通用型,与其他用户共享 CPU 磁盘资源,不保证最大 iops
    阿里云 RDS 自带基础硬件性能监控,查询性能监控和查询优化建议。
    owen800q
        10
    owen800q  
       128 天前
    差不多吧,建議換 mongo
    bthulu
        11
    bthulu  
       128 天前
    mysql 的全表 count 就是这么慢的, 你要么换 sql server 或者 oracle, 表 count 瞬间返回
    akira
        12
    akira  
       128 天前
    不正常, explain 看一眼呢
    me1onsoda
        13
    me1onsoda  
       128 天前
    说个题外话,这个配置合理吗?我 4 核 8g ,只能只吃 60%巍然不动,cpu 经常打满
    Bingchunmoli
        14
    Bingchunmoli  
       128 天前 via Android
    大概率是公网调用等延迟了
    wellsc
        15
    wellsc  
       128 天前
    别用 count ,找个外部存储存计数
    Flourite
        16
    Flourite  
       128 天前
    缺少数据库参数,innodb_buffer_pool_size 是多少
    Itesting
        17
    Itesting  
       128 天前
    你这全表扫描了,count 就这么慢,加配置 or 加 where 条件吧
    ahopunk
        18
    ahopunk  
       128 天前
    同阿里云 mysql 实例,这个速度是正常的。
    不过楼主 2 核 16G 内存的配置有点不理解,我 4 核 8G 的实例,跑起来内存和 cpu 占用比较和谐。
    fd9xr
        19
    fd9xr  
       128 天前 via iPhone
    无语…才四千万你优化它干毛
    Features
        20
    Features  
       128 天前
    啊?还有人的 slow_launch_time 值大于 1 吗?
    楼上说 count 就是这么慢认真的吗?
    huigeer
        21
    huigeer  
       128 天前 via iPhone
    这种场景需要用 mysql 嘛😄
    NickX
        22
    NickX  
       128 天前
    看看 cpu 占用情况,感觉是服务器的问题。
    cleveryun
        23
    cleveryun  
    OP
       128 天前
    @winglight2016 你们用的配置大概是如何,可以参考下吗,不知道该升级到什么配置。这个配置 limit 1000 的话用时在 2 秒左右。
    cleveryun
        24
    cleveryun  
    OP
       128 天前
    @errZX explain 一下看着是走了索引。

    ```sql
    EXPLAIN SELECT COUNT(1) FROM `pubmed-article-0`;
    ```

    结果:
    [
    {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "pubmed-article-0",
    "partitions": null,
    "type": "index",
    "possible_keys": null,
    "key": "pubmed-article-0_journal_pub_year",
    "key_len": "1023",
    "ref": null,
    "rows": 3722473,
    "filtered": 100,
    "Extra": "Using index"
    }
    ]
    dimingchan
        25
    dimingchan  
       128 天前
    肯定是用了默认的 InnoDB 存储引擎,InnoDB 的 count 是需要全表扫描的,如果不需要用到事务,建议换成 myisam 存储引擎,元数据直接记录表的记录数的;另外,不要 count(1),count(pm_id),count("主键")是最快的了。
    cleveryun
        26
    cleveryun  
    OP
       128 天前
    @mayli 谢谢提醒,我搜了下阿里云的 RDS MySQL 不支持 MyISAM 。InnoDB 的 innodb_buffer_pool_size 默认配置是{DBInstanceClassMemory*3/4},最大可以调整到{DBInstanceClassMemory*8/10},看了大家的评论,我去调整到 8/10 了(最大可调值)。

    *注:DBInstanceClassMemory:实例规格的内存大小减去实例的管控进程占用的内存大小,整数型。例如,实例规格的内存大小为 16 GB ,实例的管控进程占用的内存大小为 4 GB ,则 DBInstanceClassMemory 的值为 12 GB 。
    cleveryun
        27
    cleveryun  
    OP
       128 天前
    @huigeer 用什么比较合适呢
    mahone3297
        28
    mahone3297  
       128 天前
    @cleveryun 看你的 explain 结果,372w 行的数据,你的配置也不高,2c ,这个结果就这样,我认为合理吧

    你应该考虑的事,这些数据不从 mysql 查,从 redis 查。每次都基本上全表扫描,当然就是这结果了。
    ps:4kw 完全不需要分表吧。mysql 完全可以上亿。不过也要看单行数据量。
    ohxiaobai
        29
    ohxiaobai  
       128 天前
    这个 count 语句相等于扫描全表了,慢是正常的。
    1. MySQL 单表数量上限很高,这种数据量级不算大。
    2. 建议根据具体场景优化,比如针对这个 SQL ,如果不考虑删除,那么可以 count 1 次,然后用缓存计数,后面就不用 count 了;或者加一个自增 id 字段,设置从当前 count 之后开始自增,这样只需要记录新增后的自增 id 值就行了。
    happy32199
        30
    happy32199  
       127 天前 via iPhone
    换 32 核 64g 内存的 ecs ,自己装 mysql ,保证又快又省钱……而且 10 亿前不用分表
    lxy42
        31
    lxy42  
       127 天前
    从 explain 结果来看, 查询使用了 pubmed-article-0_journal_pub_year 索引, 正文说这个索引建立在 journal_pub_year 列, journal_pub_year 的长度是 255, 主键的长度是 4, 那么 explain 中的 key_len 不应该是 1023 啊.

    另外你说分了 8 张表, 每张表 5M 记录, 可是 explain 中的 rows 是 3722473. 看你的描述应该是手动分表, 也没有使用 MySQL 自带的分区表
    lxy42
        32
    lxy42  
       127 天前
    @lxy42 忘了一个 char 根据 charset 的不同可能对应多个字节, key_len 还是有可能是 1023 的
    Rehtt
        33
    Rehtt  
       127 天前
    @fd9xr 才四千万就这么慢不优化难道删库跑路吗
    chunworkhard
        34
    chunworkhard  
       127 天前
    count 不加条件 单表 500W 按理说正常也得 4-5s 吧
    ljsh093
        35
    ljsh093  
       127 天前
    @mayli #8 sqlite3 性能不弱的
    ZX16815
        36
    ZX16815  
       127 天前
    排查一下你的网络,如果没问题的话就提个工单问问吧。
    encro
        37
    encro  
       127 天前
    不要问了,count 就是慢。。。。。。
    encro
        38
    encro  
       127 天前
    你这 4000 万数据不用分表,直接查询,count 一定要代条件,建议为时间建立索引,只 count 最近几天的,就能快非常多。
    xlzyxxn
        39
    xlzyxxn  
       127 天前
    先说结论:1 、count(*)=count(1)>count(主键字段)>count(字段)
    2 、对大表使用 count 是不好的
    楼主这张表创建了二级索引,所以 count(*)会使用这个二级索引,从 explain 的结果可以看出来符合
    优化:1 、使用近似值,show table status 或者 explain 命令来表进行估算,explain 是很快的,rows 字段值就是估算出来的
    2 、如#15 楼所说,将具体计数保存在另外一张表中
    coderzhangsan
        40
    coderzhangsan  
       127 天前
    1.mysql count 查询默认会使用表中索引长度最短得二级索引,索引长度越长,扫描越慢,可以冗余个 tinyint 或 int 列做二级索引。
    2.如果不是精确的统计查询,可以使用 explain count ... 中得扫描行数或 show table status like '{table}' rows 来替代。
    https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_count
    opengps
        41
    opengps  
       127 天前
    mysql 的 count 好像本身就慢
    Morii
        42
    Morii  
       127 天前
    感觉楼上没说到点子上吧,你直接用阿里的 DMS 平台查下
    noparking188
        43
    noparking188  
       127 天前
    你 DataGrip 确定没问题嘛,你直接 mycli 命令行连上去 count 看看时间
    realNewBee
        44
    realNewBee  
       127 天前
    正常,这个数据量这个配置,count 就是慢。所以,如果非得查询全表的数据,不走条件的情况下,必须得根据实际需求来换种查法
    justfindu
        45
    justfindu  
       127 天前
    count(*) 试一试, 不要 1 .
    sleepybear1113
        46
    sleepybear1113  
       127 天前
    weibo> SELECT COUNT(*)
    FROM weibo.hot_search_realtime t

    [2023-10-30 11:52:23] completed in 5 m 59 s 185 ms

    总共行数:2892 7376

    基础型-1 核 1000MB 内存,100GB 存储空间,高性能云盘,IOPS:2300

    比你的稍微慢一点点。从上海访问成都。
    sleepybear1113
        47
    sleepybear1113  
       127 天前
    exam_data> SELECT COUNT(*)
    FROM exam_data.admission_data t
    [2023-10-30 11:57:56] completed in 256 ms

    行数:45 4828
    实例规格 1 核/1GB 最大 IOPS 8000 TDSQL-C MySQL 兼容数据库 MySQL5.7

    这个快不少
    123qwerty
        48
    123qwerty  
       127 天前
    MySQL 从 8.0.17 开始对于无条件的 count(*)会强制走主键,即使执行计划里面写的是走二级索引,这是因为因为在这个版本更新了并行扫描主键的功能。由于你的机器 CPU 配置不高,肯定跑不出好的效果。所以从结果上来说就比 5.7 的扫描二级索引要慢
    yh7gdiaYW
        49
    yh7gdiaYW  
       127 天前
    mysql 做这个量级的统计查询时就是很慢(当然你这个有点不正常),我们当初因为这个换了 MongoDB ,现在准备换到 StarRocks (这玩意儿速度太逆天了)
    iyaozhen
        50
    iyaozhen  
       127 天前
    count(*)=count(1)>count(主键字段)>count(字段) 这个不认同,可以让楼主试下,count 啥都是慢

    按之前经验来看,就是慢,不要不加 where 条件的 count 。用前面大家说的估算方法

    带上关键词查询的 sql 不得更慢了,NO 。你 where 命中索引是很快的,几百万一张表几乎不需要啥优化。
    MoYi123
        51
    MoYi123  
       127 天前
    mysql 的 count 就是这样的, 具体原因和事务隔离有关系;
    tangyiyong
        52
    tangyiyong  
       127 天前
    会不会是因为索引建在 varchar(255)上的问题?文本的字段一般不能做索引吧?
    tangyiyong
        53
    tangyiyong  
       127 天前
    journal_pub_year varchar(255) null,

    create index `pubmed-article-0_journal_pub_year`
    on `bio-hub`.`pubmed-article-0` (journal_pub_year);

    索引里允许 null ?
    ccagml
        54
    ccagml  
       127 天前 via Android
    这种是不是得阿里云监控看看有什么指标达到瓶颈了吗?感觉也太慢了
    sivacohan
        55
    sivacohan  
       126 天前
    fio --ioengine=libaio --bs=4k --direct=1 --thread --time_based --rw=randrw --filename=/root/io_test --runtime=300 --numjobs=1 --iodepth=1 --group_reporting --name=randread-dep1 --size=256M


    测一下磁盘 IO 性能看看
    cleveryun
        56
    cleveryun  
    OP
       118 天前 via Android
    大佬们求救,最新的情况我 Append 追加了。
    cleveryun
        57
    cleveryun  
    OP
       118 天前 via Android
    字段长度我都改成实际数据中各字段实际最长的长度值了
    cleveryun
        58
    cleveryun  
    OP
       118 天前 via Android
    @sivacohan 这是是要在服务器上跑吗,我买的是单独的数据库,只能跑 sql 语句。
    cleveryun
        59
    cleveryun  
    OP
       118 天前 via Android
    @tangyiyong 已修改,年月都改成数字重新建了索引
    cleveryun
        60
    cleveryun  
    OP
       118 天前 via Android
    @iyaozhen 我的场景要用到类似 like '%keyword%'的功能。用户会用关键词搜。
    cleveryun
        61
    cleveryun  
    OP
       118 天前 via Android
    @yh7gdiaYW 用 MongoDB 后速度怎么样了,这个量大概需要买多大的 MongoDB 数据库。
    sivacohan
        62
    sivacohan  
       117 天前
    @cleveryun
    噢,我看错了。我以为是自己部署的数据库。
    直接用 RDS 的话,就看看监控吧,看跑查询的时候什么指标是瓶颈。
    yh7gdiaYW
        63
    yh7gdiaYW  
       117 天前
    @cleveryun 比 MySQL 速度快很多,主要是查询耗时不会随数据规模非线性增长。但用于数据分析的话,MongoDB 和 starrocks 比有数量级上的差距,10 倍速度、1/10 空间占用,我调研完都惊了
    tangyiyong
        64
    tangyiyong  
       116 天前
    MATCH() AGAINST()会使用全文索引,journal_pub_year > 2022 的条件会尝试使用 B-tree 索引;通常不会在一个查询中组合使用全文索引和 B-tree 索引,可能是索引问题吧?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2630 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 15:36 · PVG 23:36 · LAX 07:36 · JFK 10:36
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.