V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
awanganddong
V2EX  ›  MySQL

mysql 表查询语句优化

  •  
  •   awanganddong · 2022-05-09 14:06:37 +08:00 · 1905 次点击
    这是一个创建于 689 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    12 条回复    2022-05-10 09:02:19 +08:00
    codefever
        1
    codefever  
       2022-05-09 14:11:55 +08:00   ❤️ 1
    根据查询条件,建立索引,优化索引、优化访问方式,限制结果集的数据量。注意填充因子要适当(最好是使用默认值 0 )。索引应该尽量小,使用字节数小的列建索引好(参照索引的创建),不要对有限的几个值的字段建单一索引如性别字段
    awanganddong
        2
    awanganddong  
    OP
       2022-05-09 15:59:09 +08:00
    https://tech.meituan.com/2014/06/30/mysql-index.html

    这是美团关于慢 sql 优化文章,我现在的索引,基本上属于普通索引,下一步就是进行联合索引的设计。
    morty0
        3
    morty0  
       2022-05-09 16:07:32 +08:00
    先把宽表拆窄
    encro
        4
    encro  
       2022-05-09 16:12:45 +08:00
    explain 结果贴出来看看
    encro
        5
    encro  
       2022-05-09 16:14:58 +08:00
    你这种属于非用户端非实时性需求,最简单办法可以考虑采用从库查询。
    awanganddong
        6
    awanganddong  
    OP
       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
        7
    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
        8
    awanganddong  
    OP
       2022-05-09 16:44:32 +08:00
    @encro 我理解理解,我感觉我又有点理解出错了。
    oneisall8955
        9
    oneisall8955  
       2022-05-09 17:25:56 +08:00 via Android
    理解 B+树就好理解了,有几个索引非常没作用还带来维护成本的,例如 gender ,各个 state 索引
    adoal
        10
    adoal  
       2022-05-09 18:16:27 +08:00 via iPhone
    把这些浮点和三角函数预先计算出来存成列试试?
    azusematsuri
        11
    azusematsuri  
       2022-05-10 05:39:10 +08:00 via Android
    先进行后面的限制,得到中间结果后,再进行 not in ?后面是有索引的,是不是比全表 not in 要快

    问题应该在实际执行的时候为什么没命中索引,看列表里 id 怎么没 primary key 。遇到过联合索引优先级的坑导致索引不生效,但是你这没有联合索引,不知道……
    awanganddong
        12
    awanganddong  
    OP
       2022-05-10 09:02:19 +08:00
    大概查看了下,确实是没有联合索引的问题,是这优化了下,将改接口优化到查询时间在 400ms 左右,然后发现其他接口查询效率降下来了。头大。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3188 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 14:24 · PVG 22:24 · LAX 07:24 · JFK 10:24
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.