mysql 表查询语句优化

EXPLAIN SELECT ma.gender, ma.face_audit_state, ma.nickname, ma.id, ma.birth, ma.vd_address, ma.sign, ma.accid, ma.home_town_title, ma.create_time, ma.address_distance, round(6371 * 2 * asin(sqrt(pow(sin((32.076434 * pi() / 180 - ma.hb_lat * pi() / 180) / 2), 2) + cos(32.076434 * pi() / 180) * cos(ma.hb_lat * pi() / 180) * pow(sin((118.608198 * pi() / 180 - ma.hb_lng * pi() / 180) / 2), 2))) * 1000) AS distance FROM `pyjy_member_account` `ma` WHERE `ma`.`id` NOT IN('2201041', '567573', '602180', '654435', '901333', '1074617', '1703630', '1983745', '24936', '83914') AND `ma`.`puppet` = '1' AND `ma`.`has_im` = '1' AND `ma`.`birth` >= '63043200' AND `ma`.`birth` <= '1104422400' AND `ma`.`personal_want` = '6' AND `ma`.`face_audit_state` = '3' AND `ma`.`gender` = '2' AND(round(6371 * 2 * asin(sqrt(pow(sin((32.076434 * pi() / 180 - ma.hb_lat * pi() / 180) / 2), 2) + cos(32.076434 * pi() / 180) * cos(ma.hb_lat * pi() / 180) * pow(sin((118.608198 * pi() / 180 - ma.hb_lng * pi() / 180) / 2), 2))) * 1000) <= 46000) AND `ma`.`block_state` = '1' ORDER BY `ma`.`hb_time` DESC LIMIT 20, 20

 UNIQUE KEY `qm_member_account_accid_uindex` (`accid`), UNIQUE KEY `qm_member_account_account_uindex` (`account`,`app_name`), KEY `pyjy_member_account_hb_time_idx` (`hb_time`), KEY `invitation_code` (`invitation_code`), KEY `member_account_phone_idx` (`phone`), KEY `sign_auditing_idx` (`sign_auditing`) USING BTREE, KEY `nickname_auditing_idx` (`nickname_auditing`), KEY `account_puppet_idx` (`puppet`) USING BTREE, KEY `account_online_mode_idx` (`online_mode`) USING BTREE, KEY `account_block_state_idx` (`block_state`) USING BTREE, KEY `account_face_audit_state_idx` (`face_audit_state`) USING BTREE, KEY `account_gender_idx` (`gender`) USING BTREE, KEY `account_prepare_state_idx` (`prepare_state`) USING BTREE

以下是表索引。通过 explain 发现 PRIMARY,account_puppet_idx,account_block_state_idx,account_face_audit_state_idx,account_gender_idx 这些索引可以被设计，但是在实际执行过程中，仅仅命中此 pyjy_member_account_hb_time_idx 索引。

所以想问下，类似于这种改如何优化性能。这些查询条件绝大多说都是 tinyint(1)类型

12 条回复 2022-05-10 09:02:19 +08:00

codefever

2022-05-09 14:11:55 +08:00

根据查询条件，建立索引，优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值 0 ）。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建），不要对有限的几个值的字段建单一索引如性别字段

awanganddong

2022-05-09 15:59:09 +08:00

https://tech.meituan.com/2014/06/30/mysql-index.html

这是美团关于慢 sql 优化文章，我现在的索引，基本上属于普通索引，下一步就是进行联合索引的设计。

morty0

2022-05-09 16:07:32 +08:00

先把宽表拆窄

encro

2022-05-09 16:12:45 +08:00

explain 结果贴出来看看

encro

2022-05-09 16:14:58 +08:00

你这种属于非用户端非实时性需求，最简单办法可以考虑采用从库查询。

awanganddong

2022-05-09 16:21:53 +08:00

现在就是采用主从库，我好像有些明白了，首先 mysql 在执行过程中，仅仅会从众多索引中选择最合适的一条索引进行匹配。
那么我们准备这种情况就必须联合索引。联合索引的话，就必须按照区分度大小选择合适的，像我这种语句必须按照区分度比较大的，且是基础查询组合联合索引。

联合索引中字段如果是=这种等值查询的。
比如联合索引（ a,b,c ）
那么 select * from test where b=1 and a=1 and c=1,在这种情况下，a,b,c 都是可以命中索引的。
如果这种 select * from test where b=1 and b>1 and c=1 ,在这种情况，c 是不能名字索引的。

这个概念我理解的有偏差，所以我才把应该用联合索引的地方，全部走普通索引。

encro

2022-05-09 16:28:08 +08:00

你看错执行结果了。

hb_time 是用于排序，等于完全没有用上索引。

索引的原理主要有几条，记住基本就不会错了：

1 ，让索引尽量分散；（这样查找就能更快）；
2 ，尽量减少磁盘搜索；(where 条件没有用索引，比如你这个)
3 ，尽量减少文件排序；(order by 没有走索引)

你这里最分散的应该是 birth,(hb_lat,hb_lng)，personal_want ，face_audit_state 。

最好的办法应该是对 hb_lat,hb_lng 建立地理位置索引。

awanganddong

2022-05-09 16:44:32 +08:00

@encro 我理解理解，我感觉我又有点理解出错了。

itechify

PRO

2022-05-09 17:25:56 +08:00 via Android

理解 B+树就好理解了，有几个索引非常没作用还带来维护成本的，例如 gender ，各个 state 索引

adoal

2022-05-09 18:16:27 +08:00 via iPhone

把这些浮点和三角函数预先计算出来存成列试试？

azusematsuri

2022-05-10 05:39:10 +08:00 via Android

先进行后面的限制，得到中间结果后，再进行 not in ？后面是有索引的，是不是比全表 not in 要快

问题应该在实际执行的时候为什么没命中索引，看列表里 id 怎么没 primary key 。遇到过联合索引优先级的坑导致索引不生效，但是你这没有联合索引，不知道……

awanganddong

2022-05-10 09:02:19 +08:00

大概查看了下，确实是没有联合索引的问题，是这优化了下，将改接口优化到查询时间在 400ms 左右，然后发现其他接口查询效率降下来了。头大。