求助:sql 优化。

[ffw5b7]

之前发错节点了。

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 索引方面的，

[RRRoger]

帮楼下大佬格式化一下~~

```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]

这种是不是只能 where 后加条件过滤一些了？感觉怎么都是走 Using filesort，

[zhaofq]

LOCATE 不会走索引

[ffw5b7]

@zhaofq 这种 sql 怎么提升速度，数据才几十万，耗时 0.8s ，整个接口耗时超过 1s，要求优化到毫秒级别。

[lesismal]

一，插入数据前对 detail_site 进行标准化，比如广东深圳、广东省深圳市、中国广东省深圳市之类的，省市格式统一标准化成中国-省-市，然后直接 detail_site order by，不需要函数，需要修复旧数据
二，需求降级，问问产品，能不能不 order by 这么细，就按 detail_site 字符串排序，如果可以，就不需要用函数了
三，新增整形字段作为省、市排序值，更新当前所有记录的该字段值，后续插入时带上该字段值，查询用这两个值 order by

ps：单一手段不可行的情况下，就要从不同的层次、角度考虑，穷则思变，不要局限在 sql 本身上

[ffw5b7]

@lesismal 其实这是个搜索接口，地理位置推荐。接手老项目，改造 es 不给时间。

[ffw5b7]

@lesismal 想问问大佬们有什么奇巧淫技。

[lesismal]

1. #4 中描述只有几十万数据，是固定的地址数据吧？这里没有给出详细信息
2. 问的问题是 sql，#6 中说改造 es 是指什么？到底用的 sql 还是 es，我的回答里好像没说需要改造成 es 。另外，但就这个查询来讲，sql 和 es 都适用
3. 这个接口功能是地理位置推荐

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

如果是按照位置范围内远近进行推荐，mongodb 有地理位置存储和查询的支持，可以考虑数据导入到 mongo，导入时把地址的经纬度信息带上，然后再做，就简单了，而且比你用字符串可能更准确
如果不需要按位置远近这种，只需要简单优化 sql 性能，#5 几种姿势说的很明白了，并且你才几十万数据，这么点数据量改造成本太低了。我怀疑楼主要么没看我的回答要么就是懒，然后还继续问什么奇技淫巧，那老夫我现在有点生气，不想再回答了 :joy:

[ffw5b7]

需求是搜索兼任地理位置推荐：需要分词再 like 排序，
像深圳钢筋，深圳，深刚，钢筋，其中一段规则是：相同市&工种＞相同省&工种＞相同市＞相同省。
本质是分词，多字段 like，根本 like 的字段优先排序，

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

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

感谢你的回复，摸摸头别生气了。。。。。。

[chengquan17]

is_help_wanted 和 detail_site 要建索引干啥，又不是谓词条件，唯一的谓词是 r.del_flag = 0，需要看下你这个字段的数据分布情况再考虑是全表扫描还是走 del_flag 的索引

[lesismal]

生气那是开玩笑呢，v 站 emoji 不显示 :joy: 不知道怎么破，好像是可以输入表情来着，忘记怎么搞了

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

如果数据量是持续增长的，目前的数据量，改造成本也不大，早治疗早解决。

[ming159]

提供两个改造建议:
1. where 后增加条件过滤,且增加的过滤字段,要建立索引,同时避免 "索引失效"的问题
2. 简单的将 detail_site 建立全文索引(数据库支持的前提下)
但是无论如何 del_flag 这个字段是要建立索引的,这是你当前 sql 中唯一的过滤条件.

[ffw5b7]

del_flag 值只有 0 和 1， 区分度太低。加了效果不大

[QiangZai]

小白冒昧的想问，说那么多为什么不查出来交给前端排序