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 我理解理解，我感觉我又有点理解出错了。