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

请教一个 MySQL 的问题

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

    这有两条 SQL 与各自稳定的执行时间,表记录是 1000w 条,平均单条记录大小 700b

    SELECT * FROM `user_operation_log` 	  LIMIT 1000000, 1; 0.3s
    SELECT user_id FROM `user_operation_log` LIMIT 1000000, 1; 0.5s
    

    为什么 SELECT user_id 比 select * 快这么多?

    两者执行计划的 type 都是 all 。

    期待大佬答复。

    第 1 条附言  ·  168 天前
    不好意思,时间标错了
    ```
    SELECT * FROM `user_operation_log` LIMIT 1000000, 1; 0.5s
    SELECT user_id FROM `user_operation_log` LIMIT 1000000, 1; 0.3s
    ```
    第 2 条附言  ·  168 天前
    user_id 不是索引
    26 条回复
    dumbbell5kg
        1
    dumbbell5kg  
    OP
       169 天前
    我认为两者的时间是花在了扫描前 100w 条记录上,扫描过程中把完整的数据页读到内存的,这个读数据页的过程对于两 SQL 来说是一样的,不明白为什么差了这么多。
    iminto
        2
    iminto  
       169 天前 via Android


    对于 SQL 这种相对黑盒,你这差距不到 50%,没必要浪费时间纠结。

    当有几倍或者几十,几百倍差距的时候,才是值得你去思考的时候。
    dumbbell5kg
        3
    dumbbell5kg  
    OP
       169 天前
    @iminto 面向面试纠结....
    Sawyerhou
        4
    Sawyerhou  
       169 天前
    没明白哪个快,时间标的是 * 快,问题描述说 user_id 快,
    mysql 底层对 * 有优化吧,一般情况下没加 index 时 * 应该快一点?
    dumbbell5kg
        5
    dumbbell5kg  
    OP
       168 天前
    @Sawyerhou 是 user_id 快,不好意思
    sagaxu
        6
    sagaxu  
       168 天前
    EXPLAIN ANALYZE SELECT * FROM `user_operation_log` LIMIT 1000000, 1;
    EXPLAIN ANALYZE SELECT user_id FROM `user_operation_log` LIMIT 1000000, 1;

    看看输出有无区别
    lesismal
        7
    lesismal  
       168 天前   ❤️ 1
    先搞清楚要对比的是什么, 如果是 count(*) 对比 count(user_id) 是有可比性的, 但 select * 对比 select user_id, 每条 700B vs 每条数据最多 8B, 单说拷贝 100w 条数据的量就性能差别巨大了所以二者根本没有可比性
    dumbbell5kg
        8
    dumbbell5kg  
    OP
       168 天前
    @lesismal "每条 700B vs 每条数据最多 8B, 单说拷贝 100w 条数据的量就性能差别巨大了"
    你是说读数据页到内存的时候,能只读这个数据页上多条记录的某个字段,比如 userId ?
    dumbbell5kg
        9
    dumbbell5kg  
    OP
       168 天前
    @sagaxu 我是 MySQL5.7 ,没有 ANALYZE 这个用法,不过两者的 EXPLAIN 结果是一样的
    dumbbell5kg
        10
    dumbbell5kg  
    OP
       168 天前
    @lesismal LIMIT 1000000, 1 = LIMIT 1 offset 1000000
    如果你指的是说传输到客户端的话,要传输的只有一条
    weijancc
        11
    weijancc  
       168 天前
    我之前测试过, 因为 select *返回的数据更多, 这也影响了速度, 如果你是本地 MySQL, 那就区别不大
    0xD800
        12
    0xD800  
       168 天前
    加个 Order By 更加面向面试学习
    oneisall8955
        13
    oneisall8955  
       168 天前   ❤️ 1
    user_id 有索引的话,select * 要回表
    yuLiong
        14
    yuLiong  
       168 天前
    一个是找到全班第十个小朋友的名字就好,一个是还得去班上看看今天小朋友穿什么衣服。
    dumbbell5kg
        15
    dumbbell5kg  
    OP
       168 天前
    @oneisall8955 没有索引
    dumbbell5kg
        16
    dumbbell5kg  
    OP
       168 天前
    @weijancc 我本地虚拟机里的 MySQL ,"因为 select *返回的数据更多"是指返回给客户端更多? 一条 700b 数据的传输用不到 0.2s
    dumbbell5kg
        17
    dumbbell5kg  
    OP
       168 天前
    @oneisall8955 回表是因为走二级索引拿不全数据,这里没走索引,没有回表的因素呀
    RangerWolf
        18
    RangerWolf  
       168 天前
    user_id 是索引嘛? 如果是索引列,那么扫描的东西可能就完全不一样了。
    dumbbell5kg
        19
    dumbbell5kg  
    OP
       168 天前
    @RangerWolf 不是索引。。怎么感觉大家对 type=all 不太熟呀,这个不是全表扫描不走索引的意思吗,还是我理解错 type=all 了
    tinyzilan123
        20
    tinyzilan123  
       168 天前
    可以做个测试,select * 使用不同数目的 column 量,看对比,可以判断是不是数据量拷贝大造成的时间差异。
    例如 select user_id, select 10 个 column, select 50 个 column 等等
    dumbbell5kg
        21
    dumbbell5kg  
    OP
       168 天前
    @tinyzilan123 测了,时间随着 column 数量的增加而增加,你说的“数据量拷贝”是指哪个过程的拷贝呀,
    数据到客户端,还是数据页从磁盘到内存?
    q727729853
        22
    q727729853  
       168 天前   ❤️ 1
    mysql 可以分为两层:sever 层和存储引擎层。
    流程:先到存储引擎层查数据,然后返回给 sever 层进行过滤。limit 是在返回数据给客户端的时候才执行的。
    因此,本质上是 sever 层向存储引擎层获取数据 1000020 次,时间花费在了 io 上
    tinyzilan123
        23
    tinyzilan123  
       168 天前   ❤️ 1
    @dumbbell5kg #21 是从 mysql 存储层到 mysql 服务器层,这里的 limit 100000,1 实际上包含了 order by id asc 的操作,这样 mysql 才能正确的从排序后的数据获得前 1000000 条数据,再返回结果。
    而排序这个操作是需要在服务器层处理的,因此需要将数据拷贝到内存再进行排序后才能获得结果。
    darklost
        24
    darklost  
       168 天前
    ... 你猜列出文章标题和列出整篇文章那个快
    IamUNICODE
        25
    IamUNICODE  
       168 天前
    user_id 是主键吗,还有*的内容有多少,是不是数据吞吐上就有不同?
    dumbbell5kg
        26
    dumbbell5kg  
    OP
       168 天前
    @tinyzilan123
    @q727729853
    终于明白了,感谢两位,根据两位的提示,我也在这里
    https://developer.aliyun.com/article/763836
    找到了更详细的答案
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3240 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 22ms · UTC 13:10 · PVG 21:10 · LAX 05:10 · JFK 08:10
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.