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 replies 2022-05-10 09:02:19 +08:00

codefever

May 9, 2022

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

awanganddong

May 9, 2022

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

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

&nbs; 3

morty0

May 9, 2022

先把宽表拆窄

encro

May 9, 2022

explain 结果贴出来看看

encro

May 9, 2022

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

awanganddong

May 9, 2022

现在就是采用主从库，我好像有些明白了，首先 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

May 9, 2022

你看错执行结果了。

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

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

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

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

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

awanganddong

May 9, 2022

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

itechify

PRO

May 9, 2022 via Android

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

adoal

May 9, 2022 via iPhone

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

azusematsuri

May 10, 2022 via Android

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

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

awanganddong

May 10, 2022

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