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

mysql 中的最后一页达不到 limit 的要求,查询变得奇慢无比,求问为什么啊?

  •  
  •   Weixiao0725 · 2016-04-27 15:03:17 +08:00 · 6705 次点击
    这是一个创建于 3116 天前的主题,其中的信息可能已经有所发展或是发生改变。

    我的 sql 语句: select * from table_name force index(PRIMARY) where source_sys = 'gfm' and order_create_gmt >= '2016-03-08 00:00:00' and order_create_gmt<= '2016-03-08 23:59:59' and id >10152664498617 order by id asc limit 1000; 正常的走主键索引查询是很快的,但是最后一页不够 1000 的时候,查询变得很慢。

    另外大家对 mysql 执行引擎偶尔改变索引导致查询变慢都是怎么解决的? force index 吗?谢谢!

    18 条回复    2016-05-16 10:55:17 +08:00
    hinkal
        1
    hinkal  
       2016-04-27 15:06:58 +08:00 via Android
    Q
    sefemp
        2
    sefemp  
       2016-04-27 15:08:44 +08:00
    记录下最后第二页到哪里
    keakon
        3
    keakon  
       2016-04-27 15:24:23 +08:00
    你的索引可能不能用来优化这个查询,导致需要全表扫描。你 explain 一下就知道原因了。
    ksupertu
        4
    ksupertu  
       2016-04-27 16:24:16 +08:00 via Android
    是不是存了很多二进制内容…这个很容易导致全表扫描,新出的版本针对这个问题有一些优化
    Weixiao0725
        5
    Weixiao0725  
    OP
       2016-04-27 16:28:49 +08:00
    上 irc 的 mysql 频道问了下,应该是 id > 1372 之后有很多数据,需要全部扫描才知道后面有多少数据。
    CosWind
        6
    CosWind  
       2016-04-27 17:46:07 +08:00
    http://timyang.net/data/key-list-pagination/ 最好用> from_id 的方式分页
    ryd994
        7
    ryd994  
       2016-04-27 20:52:56 +08:00
    我想知道,为什么 force index(PRIMARY) ?
    Walleve0
        8
    Walleve0  
       2016-04-28 00:43:52 +08:00
    不明白你一页的意思
    建议用 explain 和 show profile 看下具体执行过程就知道了。
    Weixiao0725
        9
    Weixiao0725  
    OP
       2016-04-28 09:27:55 +08:00
    @ryd994 这就是我最后说的那个问题,有时候 MySql 自己会走错索引,导致查询变慢, 所以我才加的 force index
    Weixiao0725
        10
    Weixiao0725  
    OP
       2016-04-28 09:29:24 +08:00
    @CosWind id >10152664498617 ,我是这么做的啊。但是我看 timyang 说的这个超长列表问题,即数据的长尾问题,也没有好的解决方案。
    CosWind
        11
    CosWind  
       2016-04-28 09:37:02 +08:00
    @Weixiao0725 sorry ,没看清除,不过数据这么大,为什么不做下 sharding
    ihuotui
        12
    ihuotui  
       2016-04-28 11:00:21 +08:00
    Mysql 的分页查询语句的性能分析


      MySql 分页 sql 语句,如果和 MSSQL 的 TOP 语法相比,那么 MySQL 的 LIMIT 语法要显得优雅了许多。使用它来分页是再自然不过的事情了。

    2.1 最基本的分页方式:

    Sql 代码 收藏代码

    SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

    在中小数据量的情况下,这样的 SQL 足够用了,唯一需要注意的问题就是确保使用了索引:
    举例来说,如果实际 SQL 类似下面语句,那么在 category_id, id 两列上建立复合索引比较好:

    Sql 代码 收藏代码
    SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10


    2.2 子查询的分页方式:

    随着数据量的增加,页数会越来越多,查看后几页的 SQL 就可能类似:
    Sql 代码 收藏代码
    SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10

    一言以蔽之,就是越往后分页, LIMIT 语句的偏移量就会越大,速度也会明显变慢。
    此时,我们可以通过子查询的方式来提高分页效率,大致如下:
    Sql 代码 收藏代码
    SELECT * FROM articles WHERE id >=
    (SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10


    2.3JOIN 分页方式

    Sql 代码 收藏代码
    SELECT * FROM `content` AS t1
    JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2
    WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

    经过我的测试, join 分页和子查询分页的效率基本在一个等级上,消耗的时间也基本一致。
    explain SQL 语句:
    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY <derived2> system NULL NULL NULL NULL 1
    1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
    2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

    ----------------------------------------

    为什么会这样呢?因为子查询是在索引上完成的,而普通的查询时在数据文件上完成的,通常来说,索引文件要比数据文件小得多,所以操作起来也会更有效率。

    实际可以利用类似策略模式的方式去处理分页,比如判断如果是一百页以内,就使用最基本的分页方式,大于一百页,则使用子查询的分页方式。
    ihuotui
        13
    ihuotui  
       2016-04-28 11:02:16 +08:00
    看你数据量吧,建议看看我提供的资料
    Weixiao0725
        14
    Weixiao0725  
    OP
       2016-04-28 15:35:21 +08:00
    @ihuotui 好的,谢谢
    pine
        15
    pine  
       2016-05-13 16:40:58 +08:00
    和索引的顺序有关,没看你贴出索引顺序啊
    Weixiao0725
        16
    Weixiao0725  
    OP
       2016-05-13 17:26:48 +08:00
    @pine 应该不是索引顺序吧,就是同样的 sql 语句,以前他会走一个索引,现在他又走另一个索引。所以我只能 force index 解决。
    pine
        17
    pine  
       2016-05-16 09:57:04 +08:00
    source_sys order_create_gmt 这两个索引你怎么建的? force index(PRIMARY) 感觉怪怪的。。
    Weixiao0725
        18
    Weixiao0725  
    OP
       2016-05-16 10:55:17 +08:00
    @pine order_create_gmt 和 source_sys 有各自的索引。后来,我只能先根据查询条件先查出满足条件的一共多少条,然后最后一页的时候 limit 一个准确的数字,这样能快不少。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1019 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 20:38 · PVG 04:38 · LAX 12:38 · JFK 15:38
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.