mysql 表查询语句优化

[awanganddong]

用户表现在有 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)类型

[codefever]

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

[awanganddong]

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

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

[morty0]

先把宽表拆窄

[encro]

explain 结果贴出来看看

[encro]

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

[awanganddong]

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

你看错执行结果了。

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

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

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

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

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

[awanganddong]

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

[oneisall8955]

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

[adoal]

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

[azusematsuri]

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

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

[awanganddong]

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