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
anxn
V2EX  ›  MySQL

SELECT COUNT(*) 查询如何优化?

  •  
  •   anxn · 2022-05-09 22:30:27 +08:00 · 3755 次点击
    这是一个创建于 929 天前的主题,其中的信息可能已经有所发展或是发生改变。

    如题,业务上需要全文搜索,数据大约有两百万条,每次需要查询并统计条数,发现这样花的时间是两倍 ,第一条 SQL 花了两秒,COUNT 又花了两秒,这样效率太低了,请教如何优化?

    因为项目原因,不让用 Redis ,是不是最好的方案就是再弄一张表单独记录 COUNT ,然后定期更新数据?

    第一条 SQL

    SELECT
    	* 
    FROM
    	bookclist 
    WHERE
    	btitle LIKE '%社会主义%'
    

    第二条 SQL

    SELECT
    	COUNT(*) 
    FROM
    	bookclist 
    WHERE
    	btitle LIKE '%社会主义%'
    

    还有就是全文搜索准备用全文索引,然后用 MATCH AGAINST 查询,但是就是发现在查三个字的人名时速度还比不上 LIKE 查询,不知道各位 v 友知道怎么解决吗?

    26 条回复    2022-05-10 20:23:12 +08:00
    Jooooooooo
        1
    Jooooooooo  
       2022-05-09 22:35:13 +08:00
    不小的数据量, 搜索匹配, 不是 mysql 的强项, 搞个 es 吧.
    ration
        2
    ration  
       2022-05-09 22:50:10 +08:00 via Android
    第一条是需要分页吧。。只能全文索引了,单独记录 count 的话也没用,搜索条件是不确定的。不上 es 的话,试一下 mysql 的全文索引,缓存占用比较大才能发挥优势。
    akira
        3
    akira  
       2022-05-09 22:58:33 +08:00
    不是 COUNT(*) 的问题,是全文检索的问题
    lanlanye
        4
    lanlanye  
       2022-05-09 23:09:12 +08:00 via iPhone
    1. 分成两个接口,这样相同条件只需要 count 1 次

    2. 如果全文检索本身无法利用索引优化,可以用 OVER() 一次获取到结果和总数,少查一次
    xiaopanzi
        5
    xiaopanzi  
       2022-05-09 23:19:27 +08:00
    Never use COUNT(*) in production mode.
    anxn
        6
    anxn  
    OP
       2022-05-09 23:20:12 +08:00
    @ration 对 第一条就是有分页
    Juszoe
        7
    Juszoe  
       2022-05-09 23:22:01 +08:00   ❤️ 2
    如果你的业务和搜索引擎一样只需要近似值,如“约 100000 条结果”,可以用 explain 命令
    Euthpic
        8
    Euthpic  
       2022-05-09 23:34:07 +08:00
    全文搜索用 ES 是最好的,用 MySQL 的 ngram 也行
    anxn
        9
    anxn  
    OP
       2022-05-09 23:41:15 +08:00
    @lanlanye 感谢建议,不过第 2 条查了下是 MySQL 8 才支持 OVER()函数,公司项目还是 5.7 版的
    再研究研究怎么弄合适吧
    Sasasu
        10
    Sasasu  
       2022-05-09 23:52:37 +08:00   ❤️ 1
    过会你就来问深翻页怎么办了
    yesterdaysun
        11
    yesterdaysun  
       2022-05-10 00:57:37 +08:00
    如果总条数不是特别重要, 可以考虑只精确查询特定数量以下的条数, 比如 10000 条, 10000 条之上统一显示 10000 或者提示超过 10000, 之下才精确计算

    可以先用 select 1 from t where xxx limit 10000,1 确定是不是超过 1 万条, 超过则 total 直接写 10000
    如果没超过再 select count(1) form t where xxx

    1 万条可能大了点, 可以换成 1 千条之类的, 应该可以提高一点 count 的速度, 但是效果可能不会很好, 有条件还是上 ES 吧
    sutra
        12
    sutra  
       2022-05-10 01:38:42 +08:00   ❤️ 2
    sutra
        13
    sutra  
       2022-05-10 01:42:16 +08:00
    上面的 link 有点错。

    后面的 anchor 改成 #function_row-count


    SELECT
    SQL_CALC_FOUND_ROWS *
    FROM
    bookclist
    WHERE
    btitle LIKE '%社会主义%'

    select found_rows();
    sutra
        14
    sutra  
       2022-05-10 01:46:12 +08:00
    我怎么老是贴错,anchor 应该是 #function_found-rows
    akira
        15
    akira  
       2022-05-10 03:51:15 +08:00
    @sutra 看了下文档,他是建议还是继续用 select count(*) 作为第二个语句来获取总行数的呀
    anxn
        16
    anxn  
    OP
       2022-05-10 09:56:07 +08:00
    @sutra 这个方法测试可行,感谢!
    sutra
        17
    sutra  
       2022-05-10 11:32:37 +08:00
    @akira 我也没怎么用过这个,只是知道。
    encro
        18
    encro  
       2022-05-10 13:48:42 +08:00
    @sutra
    @anxn

    看文档意思是 found rows 已经被弃用(因为某些优化无效),建议用 count(*)会更好。

    Mysql 应该是无解的,简单来说用全文索引会好过 like 很多。特别对于 tilte 这种,走索引后几百万数据还是挺快的。


    Mysql 中文全文索引(含实例 5 分钟上手)

    https://c4ys.com/archives/2098
    anxn
        19
    anxn  
    OP
       2022-05-10 14:05:03 +08:00
    @encro 全文索引也研究了,就是发现查 3 个字的人名还是比较慢,比如包含这个人名的记录有 5k 多条(记录不超过 1k 条得话可以达到毫秒级),查询还是得花 3 秒多,不过 COUNT(*)就非常快了,可以达到毫秒级

    这是我的配置文件
    innodb_ft_min_token_size = 1
    innodb_ft_max_token_size = 84
    ngram_token_size = 2

    SQL 语句:

    SELECT
    *
    FROM
    bookclist
    WHERE
    MATCH ( btitle ) AGAINST ('邓小平' IN NATURAL LANGUAGE MODE );

    查"邓小平"或者"邓小"都是 3 秒多,研究了好久不知道咋优化了,不知道 MySQL 能不能对指定词库做索引,之前只查到了 stopword
    anxn
        20
    anxn  
    OP
       2022-05-10 14:09:36 +08:00
    @anxn 补充一下,全文搜索 LIMIT 1000;应该是个优化思路
    sutra
        21
    sutra  
       2022-05-10 14:10:43 +08:00
    @anxn 你是说 select count(*) from blockclist; (注意不带任何 where 语句)很快是吧?这个 mysql 优化过,相比 postgresql 。
    anxn
        22
    anxn  
    OP
       2022-05-10 14:15:34 +08:00
    @sutra 不是,是带上 WHERE MATCH ( btitle ) AGAINST 子句的,就是 MATCH ( btitle ) AGAINST 的记录数
    mingl0280
        23
    mingl0280  
       2022-05-10 16:30:52 +08:00
    第一个需求:不让你用 redis 你还不能自己写个缓存嘛……
    第二个需求没搞过,不知道。
    zmal
        24
    zmal  
       2022-05-10 16:54:32 +08:00
    SQL 层面没什么好优化的。
    1. 审视全文检索的需求到底是否必要,业务上是否可以规避,比如只允许查询预先缓存的 keyword 。
    2. 用 mysql 的全文索引肯定没问题啊,你说的某个人名 5000 多条慢 90%是 IO 原因。
    encro
        25
    encro  
       2022-05-10 20:20:34 +08:00
    没办法,记录集中确实慢。

    ngram_token_size=1 试一下,中文简单的话都分字性能好像还会高一点

    你 explain 的时候要看慢在哪里。
    encro
        26
    encro  
       2022-05-10 20:23:12 +08:00
    理解 explain 每一列的意思,看没一列的输出,然后就知道有没有优化空间了。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2081 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 36ms · UTC 01:00 · PVG 09:00 · LAX 17:00 · JFK 20:00
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.