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

工作中同事问的一个问题,查了很久没有明白,望大神指点

  •  
  •   zivyou · 2020-06-02 18:14:44 +08:00 · 4157 次点击
    这是一个创建于 1395 天前的主题,其中的信息可能已经有所发展或是发生改变。
    有一个表 TableA,数据量很大,约 4000w 条。
    使用 where 条件,where TableA.columnA='value'后,可以查出约 70w 条数据。
    现在有个业务逻辑: 想在 TableA 中使用 where 条件 where TableA.columnA='value'查询数据,并且将数据按照时间戳排序,所以最后的 sql 是:
    select * from TableA where TableA.columnA='value' order by timestamp;
    通过 explain 发现,此次执行是一个 simple 查询,扫描了全表 4000w 数据(columnA 上没有建索引,所以是正常的),耗时 13s 。
    但是奇怪的是,如果不用 order by,直接 select * from TableA where TableA.columnA='value'; 花了 1s 不到的时间,因此,可以认为时间都花在了 order by 排序上。
    于是他用 select * from TableA order by timestamp; 发现也需要 17s 左右的时间,验证了时间都花在了 order by 上。

    现在我的同事灵机一动,想到可以把数据用子查询先查出,再做排序,按道理因为子查询查出的数据量只有 70w,已经降了两个量级了,这个时候再做排序应该要轻松一些。于是: select * from (select * from TableA where Table.A.columnA='value') as B order by B.timestamp; 但是用 explain 一看:

    '1','SIMPLE','ulog_data_attitude',NULL,'ALL',NULL,NULL,NULL,NULL, 'xxx','xxx','Using where; Using filesort'

    发现用没用子查询执行计划都一样。

    现在的问题是:
    1. 为什么「先用子查询查出数据,再用 order by 排序子查询的数据」的方法行不通?
    2. 这个查询除了对 columnA 建索引外,有没有其他更好的优化方法?

    望大神们指点~
    14 条回复    2020-06-03 10:38:20 +08:00
    vindac
        1
    vindac  
       2020-06-02 18:38:06 +08:00 via Android
    先查 id,再查全部?
    pushback
        2
    pushback  
       2020-06-02 18:43:12 +08:00
    order by 不是也吃索引吗
    776491381
        3
    776491381  
       2020-06-02 18:43:33 +08:00 via iPhone
    索引不只是要对 columnA 建立,如果要建,要把 timestamp 也建进去,否则优化的只是 1s
    rogwan
        4
    rogwan  
       2020-06-02 18:44:51 +08:00 via iPhone
    sql 执行不是想当然的先后顺序,order by 需要建个索引是常规操作。
    allen9527
        5
    allen9527  
       2020-06-02 18:44:57 +08:00
    为什么不想加索引?看执行计划,基本在扫描然后又内存排序。 子查询应该是一样的啊。。。
    emm 。。。。 要不改改 sort_buffer_size 之类的参数大小
    yjxjn
        6
    yjxjn  
       2020-06-02 18:45:42 +08:00
    慎用子查询,join 不行吗?再就是 order by 不建立索引肯定速度慢呀。。
    eke
        7
    eke  
       2020-06-02 18:47:12 +08:00
    1. db 已经帮你做了 query optimization 吧?
    nnd
        8
    nnd  
       2020-06-02 19:02:14 +08:00   ❤️ 2
    1. 为什么「先用子查询查出数据,再用 order by 排序子查询的数据」的方法行不通?
    2. 这个查询除了对 columnA 建索引外,有没有其他更好的优化方法?

    答:
    1. 数据库做了逻辑优化,所以查询路径是一致的,当然你可以修改数据库统计信息,来欺骗数据库。如果数据库统计信息没有错误的话,数据库给你的执行计划就是相对最佳的执行计划。

    2.
    A. 如果建索引要建的是 columnA 和 timestamp 的联合索引,而不是单列索引;
    B.更好的优化方法:
    1) 改业务,不需要排序;
    2 )换存储( RAID 、SSD 、傲腾等),增大内存;
    3 )架构层面增加缓存系统,REDIS 、memcache 等;
    4 )数据库缓存结果,或者使用物化视图;
    5 )优化 SQL,增加联合索引;

    供参考😀
    zivyou
        9
    zivyou  
    OP
       2020-06-02 19:18:22 +08:00
    @nnd 感谢详细的解答

    多谢各位老哥~
    snoy
        10
    snoy  
       2020-06-02 20:37:30 +08:00
    用 select * from TableA force index(columnA) where TableA.columnA='value' order by timestamp;试试?
    jay0726
        11
    jay0726  
       2020-06-02 23:14:05 +08:00
    搜索关键字全字段排序和 rowid 排序了解一下就知道了,可以建立 columnA 和 timestamp 的联合索引,进一步优化还能建立覆盖索引减少回表
    xyjincan
        12
    xyjincan  
       2020-06-02 23:15:34 +08:00
    查询取出 70w 数据好多啊,你看看分页,取前面的,跟取最后面的速度也是不一样的。 把结果查询保存到内存的临时表
    egfegdfr
        13
    egfegdfr  
       2020-06-03 09:53:14 +08:00
    1. 为什么「先用子查询查出数据,再用 order by 排序子查询的数据」的方法行不通?
    2. 这个查询除了对 columnA 建索引外,有没有其他更好的优化方法?

    1. ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。
    排序的对象是 where 后的数据,所以你用不用子查询,需要排序的数据都是 70w 。 所以在这里,这个方法是行不通的。

    2. 不知道数据插入的时候有 主键是不是按时间戳顺序生成的。如果是那就好办了,直接 order by 主键就行。如果不是,好像除了改业务,或者是加硬件 也就是建立 timestamp 的索引这个方法比较实际了。
    ljzxloaf
        14
    ljzxloaf  
       2020-06-03 10:38:20 +08:00
    1. mysql 本来就是这么干的
    2. 适当调大 sort-buffer,使每次排序的数据量大一些,减少 merge 排序的次数,从而减少 io ; 不过还是建议加个联合索引( columnA,timestamp ),这样就不用每次排序了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1006 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 19:49 · PVG 03:49 · LAX 12:49 · JFK 15:49
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.