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
ukipoi
V2EX  ›  MySQL

请问这个 MYSQL 的语句,我应该如何优化或者建索引

  •  
  •   ukipoi · 76 天前 · 1880 次点击
    这是一个创建于 76 天前的主题,其中的信息可能已经有所发展或是发生改变。

    语句如下

    SELECT
    	pni.parent_name,
    	ni.name,
    	dt.data_time,
    	dt.value_a,
    	dt.value_b
    FROM
    	data_table dt
    LEFT JOIN rel_table rt ON
    	dt.data_id = rt.rel_id
    LEFT JOIN name_info ni ON
    	rt.rel_b_id = ni.rel_b_id
    LEFT JOIN parent_name_info pni ON
    	ni.parent_id = pni.parent_id
    WHERE
    	dt.data_time > ${dayTime}
    	AND dt.data_time <= date_add(${dayTime},INTERVAL 1 DAY)
    	AND pni.parent_id IN (
    	SELECT
    		parent_id
    	FROM
    		parent_list)
    

    mysql 的版本是 5.7 ,现在的情况是这个语句查询不出来。 数据表日期范围内的数据大概是不到 1W 条,rel 表中的数据不到 200 条。 ni 和 pni 这两个表中的数据可能有几十万条。

    19 条回复    2024-04-10 23:44:37 +08:00
    encro
        1
    encro  
       76 天前
    explain 结果出来看下再说
    encro
        2
    encro  
       76 天前
    dt 的 parnet_id 加 data_time 建立组合索引。
    Richared
        3
    Richared  
       76 天前
    左关联会关联所有数据,形成大表再筛选,先筛选再关联减少连接数。你这数据量还没到扒着看的程度
    me1onsoda
        4
    me1onsoda  
       76 天前
    以我浅薄的知识,恐怕没有什么好办法。我可能会把 pni.parent_id IN 这个 clause 放到表连接中,提高过滤率
    kwater
        5
    kwater  
       76 天前
    IN (
    SELECT
    parent_id
    FROM
    parent_list)

    这个必须改 join ,否则你可能在某些人的偏执下会失去工作 😈
    leon1900
        6
    leon1900  
       76 天前
    parent_list 多少条数据
    ukipoi
        7
    ukipoi  
    OP
       76 天前
    @encro
    结果是这个。

    id select_type `table` partitions `type` possible_keys `key` key_len `ref` `rows` filtered Extra
    1 SIMPLE rt ALL rel_id,data_id 187 100 Using where
    1 SIMPLE ni ref rel_id rel_id 111 test.rt.rel_id 1 100
    1 SIMPLE dt ref data_id,data_time data_id 110 test.rt.data_id 232 3.73 Using where
    1 SIMPLE pni ALL 96014 10 Using where; Using join buffer (Block Nested Loop)
    1 SIMPLE <subquery2> eq_ref <auto_key> <auto_key> 110 test.pni.parent_id 1 100
    2 MATERIALIZED parent_list index parent_id parent_id 110 185 100 Using index
    ukipoi
        8
    ukipoi  
    OP
       76 天前
    @leon1900
    200 条不到
    qiqw
        9
    qiqw  
       76 天前
    关注
    ukipoi
        10
    ukipoi  
    OP
       76 天前
    @Richared 我试着把条件都写在 join 的表里了,最终结果大概是
    A ( 9000 ) B ( 200 ) C ( 200 ) D(50) 这 4 个表关联,还是出不来结果
    我看这 4 个表做全关联要 180W 数据了,mysql 会这样么。
    主要是之前用的都是 oracle ,自己数据库也不太行,oracle 关联好像可以直接出数据。
    Felix0504
        11
    Felix0504  
       76 天前   ❤️ 1
    @ukipoi #7 你的 rt 和 pni 都是全表扫描,rt 数据量小也就算了,pni 数据量大,全表扫描肯定耗时,parent_id 加索引了吗
    elkfnmoyu
        12
    elkfnmoyu  
       76 天前
    3 楼说的对,子查询里 data_time 先筛选 data_table 数据,然后再用子查询关联其他表
    leon1900
        13
    leon1900  
       76 天前   ❤️ 1
    pni.parent_id 没索引吗
    ukipoi
        14
    ukipoi  
    OP
       76 天前
    @Felix0504 没有单独的 parent_id 的索引,我加了结果就能出来了,谢谢!
    Richared
        15
    Richared  
       76 天前
    @ukipoi 加个分页吧。
    Richared
        16
    Richared  
       76 天前
    @ukipoi 这样的问题,首先考虑减小关联数,然后是否是全量返回,数据量大小多大,最后看你的条件走没走索引,尤其大表。你把条件写里边就是一个一个的简单查询,这个判断走不走索引,该怎么建立索引就简单多了,照着操作就行了。基本都能解决。
    fxjson
        17
    fxjson  
       76 天前   ❤️ 1
    如果是我的话,首先 data_time 添加索引,
    其次 date_add(${dayTime},INTERVAL 1 DAY)去掉使用 date_add 函数,
    其次,子查询在应用中单独 sql 查询出结果然后用 in(xxx,xxx),
    最后看看 on 字段是否建立了对应的索引

    最后根据 explain 看结果进行分析是否有改进,如果上面几步都改了,sql 大概率不会有其他问题了
    encro
        18
    encro  
       76 天前
    @ukipoi
    #7

    前有人提到过,就是 pni.parent_id 的问题,rows 96014 就它最大,且没有利用索引。
    给 pni.parent_id 建立索引就解决了。
    基本所有关联外键都需要建立索引的。
    aliveyang
        19
    aliveyang  
       76 天前
    pni.parent_id IN ()既然有这个条件了, LEFT JOIN 是不是没必要了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1976 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 16:19 · PVG 00:19 · LAX 09:19 · JFK 12:19
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.