mysql 表查询语句优化

用户表现在有 200 万数据，字段有 137 个，表大小在 2G 左右。

现在复杂查询的情况下，有大量慢 sql 。如果不依托 es,如何优化。

其中包含 not in ,多类型字段检索。（类似于性别这种）

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)类型

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 是不能名字索引的。

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