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

求助:sql 优化。

  •  
  •   ffw5b7 · 2021-05-31 22:47:57 +08:00 via Android · 1951 次点击
    这是一个创建于 1275 天前的主题,其中的信息可能已经有所发展或是发生改变。
    之前发错节点了。

    select
    r.uuid as recruitUuid,
    r.project_name as projectName,
    r.work_type as workType,
    r.user_name as name,
    r.certification_status as certificationStatus,
    r.team_status as teamStatus,
    r.user_phone as userPhone,
    r.recruit_num as invite,
    r.price as price,
    r.description as description,
    r.detail_site as projectAddress,
    r.create_time as createTime,
    r.top_flag as topFlag,
    r.release_type as releaseType,
    r.bind_phone as bindPhone,
    r.is_help_wanted as isHelpWanted,
    r.modify_time as modifyTime
    from
    recruit r
    where
    r.del_flag = 0
    order by
    r.top_flag desc ,
    (
    case
    r.is_help_wanted when '1' then 0
    else 1
    end ) asc,
    LOCATE('深圳市', detail_site) desc,
    LOCATE('广东',detail_site) desc,
    r.create_time desc
    limit 100


    is_help_wanted 和 detail_site 怎么建立索引。或者怎么优化关于这二个字段的排序?

    CREATE INDEX index_order_by ON recruit (
    top_flag DESC,
    is_help_wanted,
    detail_site,
    create_time DESC
    );

    is_help_wanted,detail_site 导致失效, 谷歌搜了下 LOCATE 没有关于 LOCATE 索引方面的,
    14 条回复    2021-06-11 16:27:27 +08:00
    RRRoger
        1
    RRRoger  
       2021-06-01 09:24:16 +08:00
    帮楼下大佬格式化一下~~

    ```sql
    SELECT r.uuid AS recruituuid,
    r.project_name AS projectname,
    r.work_type AS worktype,
    r.user_name AS name,
    r.certification_status AS certificationstatus,
    r.team_status AS teamstatus,
    r.user_phone AS userphone,
    r.recruit_num AS invite,
    r.price AS price,
    r.description AS description,
    r.detail_site AS projectaddress,
    r.create_time AS createtime,
    r.top_flag AS topflag,
    r.release_type AS releasetype,
    r.bind_phone AS bindphone,
    r.is_help_wanted AS ishelpwanted,
    r.modify_time AS modifytime
    FROM recruit r
    WHERE r.del_flag = 0
    ORDER BY r.top_flag DESC ,
    (CASE r.is_help_wanted
    WHEN '1' THEN 0
    ELSE 1
    END) ASC,
    locate('深圳市', detail_site) DESC,
    locate('广东', detail_site) DESC,
    r.create_time DESC LIMIT 100
    ```
    ffw5b7
        2
    ffw5b7  
    OP
       2021-06-01 09:41:15 +08:00 via Android
    这种是不是只能 where 后加条件过滤一些了?感觉怎么都是走 Using filesort,
    zhaofq
        3
    zhaofq  
       2021-06-01 10:28:02 +08:00
    LOCATE 不会走索引
    ffw5b7
        4
    ffw5b7  
    OP
       2021-06-01 10:48:57 +08:00
    @zhaofq 这种 sql 怎么提升速度,数据才几十万,耗时 0.8s ,整个接口耗时超过 1s,要求优化到毫秒级别。
    lesismal
        5
    lesismal  
       2021-06-01 12:23:45 +08:00
    一,插入数据前对 detail_site 进行标准化,比如广东深圳、广东省深圳市、中国广东省深圳市之类的,省市格式统一标准化成中国-省-市,然后直接 detail_site order by,不需要函数,需要修复旧数据
    二,需求降级,问问产品,能不能不 order by 这么细,就按 detail_site 字符串排序,如果可以,就不需要用函数了
    三,新增整形字段作为省、市排序值,更新当前所有记录的该字段值,后续插入时带上该字段值,查询用这两个值 order by

    ps:单一手段不可行的情况下,就要从不同的层次、角度考虑,穷则思变,不要局限在 sql 本身上
    ffw5b7
        6
    ffw5b7  
    OP
       2021-06-01 12:30:57 +08:00 via Android
    @lesismal 其实这是个搜索接口,地理位置推荐。接手老项目,改造 es 不给时间。
    ffw5b7
        7
    ffw5b7  
    OP
       2021-06-01 12:32:58 +08:00 via Android
    @lesismal 想问问大佬们有什么奇巧淫技。
    lesismal
        8
    lesismal  
       2021-06-01 13:59:02 +08:00
    1. #4 中描述只有几十万数据,是固定的地址数据吧?这里没有给出详细信息
    2. 问的问题是 sql,#6 中说改造 es 是指什么?到底用的 sql 还是 es,我的回答里好像没说需要改造成 es 。另外,但就这个查询来讲,sql 和 es 都适用
    3. 这个接口功能是地理位置推荐

    综合下:数据数量几十万、地理位置推荐,那我排除是按收件人地址类的搜索,假定你的数据量数量固定为几十万。

    如果是按照位置范围内远近进行推荐,mongodb 有地理位置存储和查询的支持,可以考虑数据导入到 mongo,导入时把地址的经纬度信息带上,然后再做,就简单了,而且比你用字符串可能更准确
    如果不需要按位置远近这种,只需要简单优化 sql 性能,#5 几种姿势说的很明白了,并且你才几十万数据,这么点数据量改造成本太低了。我怀疑楼主要么没看我的回答要么就是懒,然后还继续问什么奇技淫巧,那老夫我现在有点生气,不想再回答了 :joy:
    ffw5b7
        9
    ffw5b7  
    OP
       2021-06-01 14:38:54 +08:00
    需求是搜索兼任地理位置推荐:需要分词再 like 排序,
    像深圳钢筋,深圳,深刚,钢筋,其中一段规则是:相同市&工种>相同省&工种>相同市>相同省。
    本质是分词,多字段 like,根本 like 的字段优先排序,

    detail_site 插入是没有限制格式,深圳市,深圳,广东深圳,广东深圳市, 来源很多导入,爬虫,填写....

    至于说改造数据 我没回复,我可能没有把需求说清楚,感觉 不适合模糊。我的锅。
    接手老项目,改造 es 不给时间,说明下自身条件情况。过滤调改造 es 的回答。

    感谢你的回复,摸摸头别生气了。。。。。。
    chengquan17
        10
    chengquan17  
       2021-06-01 14:50:00 +08:00
    is_help_wanted 和 detail_site 要建索引干啥,又不是谓词条件,唯一的谓词是 r.del_flag = 0,需要看下你这个字段的数据分布情况再考虑是全表扫描还是走 del_flag 的索引
    lesismal
        11
    lesismal  
       2021-06-01 15:38:31 +08:00
    生气那是开玩笑呢,v 站 emoji 不显示 :joy: 不知道怎么破,好像是可以输入表情来着,忘记怎么搞了

    再补充点吧:如果你的数据是只有几十万甚至几百万这种量级,直接内存做也可以,这点内存不值钱。比如启动时初始化,分批查出每条数据按照你需要的关键字生成一条 string,存到 trie tree 里,查询时按关键字最多到最少循环查询,大于等于 limit 数量时结束循环。如果数据需要动态更新,更新 tree 成本也很低。
    甚至,就内存里按关键字弄几个排好序的,二分查找就是了,除了 go 这种,其他很多语言本身就自带这些有序的 container,没啥成本

    如果数据量是持续增长的,目前的数据量,改造成本也不大,早治疗早解决。
    ming159
        12
    ming159  
       2021-06-02 11:23:16 +08:00
    提供两个改造建议:
    1. where 后增加条件过滤,且增加的过滤字段,要建立索引,同时避免 "索引失效"的问题
    2. 简单的将 detail_site 建立全文索引(数据库支持的前提下)
    但是无论如何 del_flag 这个字段是要建立索引的,这是你当前 sql 中唯一的过滤条件.
    ffw5b7
        13
    ffw5b7  
    OP
       2021-06-02 13:51:45 +08:00
    del_flag 值只有 0 和 1, 区分度太低。加了效果不大
    QiangZai
        14
    QiangZai  
       2021-06-11 16:27:27 +08:00
    小白冒昧的想问,说那么多为什么不查出来交给前端排序
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3117 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 14:22 · PVG 22:22 · LAX 06:22 · JFK 09:22
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.