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

Mysql 有索引的情况下依然几乎扫描全表

  •  
  •   ivito ·
    fireboyvt · 2016-04-06 11:38:22 +08:00 · 11611 次点击
    这是一个创建于 3138 天前的主题,其中的信息可能已经有所发展或是发生改变。
    某张表有一个主键 id ,一个 item_id 字段,我对 item_id 字段加了索引,但是当我使用 select * from table_name limit 10000,20 查询时,发现 Rows_examined 为 10 万,大约等于全表大小。这是什么原因呢?
    22 条回复    2016-04-07 11:16:09 +08:00
    wesley
        1
    wesley  
       2016-04-06 11:42:12 +08:00
    需要加上 order by item_id
    dongyado
        2
    dongyado  
       2016-04-06 11:43:52 +08:00
    你的查询语句都没用 id,item_id 肯定会全表扫描的啊。。。
    keakon
        3
    keakon  
       2016-04-06 14:21:36 +08:00
    没有 where 条件
    sghcel
        4
    sghcel  
       2016-04-06 14:27:35 +08:00   ❤️ 1
    select item_id from table_name limit 10000,20 这样就不会全表扫描

    具体原因看我的 blog: http://dorid.me/2016/02/03/mysql-limit-performance-optimize/
    vincenttone
        5
    vincenttone  
       2016-04-06 14:30:51 +08:00
    explain select * from table_name limit 10000,20;
    explain select * from table_name limit 10000,20 order by id desc;
    explain select * from table_name where item_id = 1 limit 10000,20;

    挨个执行看看,你还需要花 20 分钟研究一下索引到底是什么
    rqrq
        6
    rqrq  
       2016-04-06 14:48:28 +08:00
    先加上 order by id desc 试试,还是不行试试:
    select * from table_name where id < (select min(id) from (select id from table_name order by id desc limit 10000) as tmp) limit 20
    msg7086
        7
    msg7086  
       2016-04-06 14:50:42 +08:00   ❤️ 2
    MySQL 的分页是先读再丢,所以应该要读了扔了 10 万条记录以后再返回。
    我自己是先读 ID 然后再用 ID IN () 去查表返回的。
    davidzhang
        8
    davidzhang  
       2016-04-06 15:06:43 +08:00
    因为你没有用到索引啊
    treycheng
        10
    treycheng  
       2016-04-06 15:33:36 +08:00
    这个 limit 10000,20 做分页查询 首先会查处前面 10000 行的所有 select 的内容, 然后丢弃掉
    分页查询 一般先查询你目标内容的主键 再通过主键查询目标行
    sghcel
        11
    sghcel  
       2016-04-06 15:49:37 +08:00
    @treycheng 简而言之就是避免在丢弃的过程中回行
    ivito
        12
    ivito  
    OP
       2016-04-06 17:36:48 +08:00
    @dongyado 不会根据 Primary key 直接从从 B 数的左侧或右侧遍历吗,耗时不应该只是个扫描 Primary key 索引的耗时吗
    ivito
        13
    ivito  
    OP
       2016-04-06 17:46:00 +08:00
    @sghcel 结果展示的很有说服力,不过没有深入分析啊,还有时间数据 0.061 应该是 0.0061
    jiehuangwei
        14
    jiehuangwei  
       2016-04-06 17:47:30 +08:00
    看到这种SQL,就有想往死里打的冲动。。。。
    wikimore
        15
    wikimore  
       2016-04-06 17:50:37 +08:00
    你这个 SQL 就是不全表扫 也不会太快 LIMIT 10000,20
    wwek
        16
    wwek  
       2016-04-06 18:15:17 +08:00
    大翻页问题
    翻页从产品角度优化. 只给下一页 ,获取更多什么的
    ihuotui
        17
    ihuotui  
       2016-04-06 18:25:03 +08:00
    @msg7086 你说得太对了。 sql limit 执行过程。
    suikator
        18
    suikator  
       2016-04-06 19:12:10 +08:00 via Android
    @sghcel 文中一处好像有错,不是 0.0187 应该是 0.187 吧?
    ivito
        19
    ivito  
    OP
       2016-04-06 20:56:19 +08:00
    @rqrq 第一种无效,第二种方法快了一倍,但依然接近全表扫描,快出的部分应该是临时表只 select 了 id
    rqrq
        20
    rqrq  
       2016-04-06 23:01:14 +08:00
    @ivito 你的目的就是要达到索引扫描?
    说个题外话, 300ms 以内的响应速度我认为完全可以接受。
    sghcel
        21
    sghcel  
       2016-04-07 09:27:45 +08:00
    @ivito 深入分析就是丢弃的过程中产生的回行,这是根本原因
    elgoog1970
        22
    elgoog1970  
       2016-04-07 11:16:09 +08:00
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5740 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 06:22 · PVG 14:22 · LAX 22:22 · JFK 01:22
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.