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

mysql 有索引还是很慢怎么办?

  •  
  •   xjdata · 2016-10-26 16:53:15 +08:00 · 8587 次点击
    这是一个创建于 2932 天前的主题,其中的信息可能已经有所发展或是发生改变。

    [25260 ms]*/ SELECT * FROM `user` ORDER BY `id` DESC LIMIT 12 OFFSET 251676;

    前几页数据还好。速度可以接受,基本上都是秒内完成,但是越往后越慢。请问怎么办?

    谢谢。

    第 1 条附言  ·  2016-10-27 06:50:56 +08:00
    首先,谢谢大家的帮助。

    搜索了下,有很多解决方式, 当然没有通用的方法,还是得按实际情况来决定到底用那种。

    问题有了新的进展, 我这里的 id 是主键,自增。 索引类型默认就是 PRIMARY

    ```[25260 ms]*/ SELECT * FROM `user` ORDER BY `id` DESC LIMIT 12 OFFSET 251676;```



    ```
    EXPLAIN SELECT * FROM `user` WHERE `create_at` BETWEEN 1420065013 AND 1451687419 ORDER BY `id` ASC LIMIT 0,12

    id: 1
    select_type: SIMPLE
    table: user
    type: index
    possible_keys: NULL
    key: PRIMARY
    key_len: 4
    ref: NULL
    rows: 12
    Extra: Using where

    ```

    现在就算没有 OFFSET 查询速度依然很慢, 这个大概需要 17 秒左右。

    当我使用 create_at 作为 ORDER BY 的条件的时候 速度很快了, 2 秒。

    为什么呢? id 是主键,自增。 索引类型默认就是 PRIMARY

    为什么会这样呢? 当然这些问题 手册里应该有说。 偷个懒 还请有经验的朋友直接点醒下。 谢谢。
    第 2 条附言  ·  2016-10-27 06:58:49 +08:00
    为什么这个就很快呢? 基本上就 0.2 秒左右
    ```
    EXPLAIN SELECT * FROM `user` WHERE `create_at` BETWEEN 1443653577 AND 1451515975 ORDER BY `create_at` DESC LIMIT 12 OFFSET 12 \G;

    id: 1
    select_type: SIMPLE
    table: user
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 251719
    Extra: Using where; Using filesort
    ```
    17 条回复    2016-10-27 14:06:03 +08:00
    mahone3297
        1
    mahone3297  
       2016-10-26 17:10:48 +08:00
    这个问题,往上一大堆一大堆的,专门讲这个问题。
    搜 mysql 分页
    iyaozhen
        2
    iyaozhen  
       2016-10-26 17:14:03 +08:00 via Android
    MySQL 分页是有这个问题。貌似有个解决方案是 id between (xxx, yyy)
    luckyduck
        3
    luckyduck  
       2016-10-26 17:17:26 +08:00
    大数据量的分页就不能用 OFFSET 了,如果你只是上一页,下一页这种简单分页方式的话。
    可以第一页用
    SELECT * FROM `user` ORDER BY `id` DESC LIMIT 12 ;
    然后下一页用
    SELECT * FROM `user` WHERE `id` > 上一页最后一条记录的 ID ORDER BY `id` DESC LIMIT 12 ;
    kn007
        4
    kn007  
       2016-10-26 17:34:16 +08:00
    目前用着 3 楼的方法, 2 楼的记得之前也有用,但是数据多,还是会有慢查询
    shimanooo
        5
    shimanooo  
       2016-10-26 17:39:22 +08:00
    3 楼+1

    另外加了什么索引, explain 看看?
    chaegumi
        6
    chaegumi  
       2016-10-26 18:31:48 +08:00
    mysql limit 分页很大时的解决方案

    ```sql

    select AA.*, S1.site_name from analytics AA inner join(
    select A.id from analytics A left join sites S on A.site_id=S.id order by A.id desc limit 363460,10
    ) as my_results USING(id) left join sites S1 on AA.site_id=S1.id

    ```
    yidinghe
        7
    yidinghe  
       2016-10-26 18:36:15 +08:00 via Android
    好大的 offset ,这意味着即使有索引也必须按着索引扫过去。索引是用来直接定位记录的,扫索引效率照样低。
    cjyang1128
        8
    cjyang1128  
       2016-10-26 19:38:14 +08:00
    直接搜索 mysql 分页查询优化吧
    chenset
        9
    chenset  
       2016-10-26 20:44:12 +08:00
    我来粗略解析下楼主 SQL 耗时的原因.

    1. SELECT * FROM `user` ORDER BY `id` DESC
    MySQL 先全表扫描, ID 索引排序, 但并未返回给客户端.

    2. LIMIT 12 OFFSET 251676;
    丢弃掉前面的 251676 条, 返回后面的 12 条.


    就是因为引擎宏观上分两次执行了 SQL, 第 1 次时扫描了大量行而耗时, 客户端却只接收到 12 行数据.

    自己个人理解求大神教做人.
    enenaaa
        10
    enenaaa  
       2016-10-27 00:27:06 +08:00
    @chenset id 是索引时应该不会进入全表扫描。 我猜测偏移数较大时慢的原因是排序计算造成的。
    取前 10 名只是多项式复杂度。
    到 251676 可能已经接近于全排。
    enenaaa
        11
    enenaaa  
       2016-10-27 00:33:59 +08:00
    这也解释了为什么 3 楼的做法会有效
    pubby
        12
    pubby  
       2016-10-27 01:15:36 +08:00 via Android
    只给 100 页,其它用搜索解决。
    wangdu2012
        13
    wangdu2012  
       2016-10-27 08:40:08 +08:00 via iPhone
    offset 这么大。全表了吧。
    chenset
        14
    chenset  
       2016-10-27 08:41:07 +08:00
    @enenaaa 哦, 不是全表扫, 而是扫了 251676 + 12 行.
    dangyuluo
        15
    dangyuluo  
       2016-10-27 10:10:32 +08:00
    我现在是用的 Elasticsearch 解决。专业的事情就要交给专业的工具去做。
    fuxkcsdn
        16
    fuxkcsdn  
       2016-10-27 13:43:25 +08:00
    3 楼的方法+1

    但感觉再慢也不应该 LZ 说的那么慢,应该是表的字段太多,或者有很多 text 字段吧??
    我测试 2,297,101 条记录, 12 个字段
    int, int, tinyint,varchar(32),char(32),varchar(512),bigint,bigint,tinyint,varchar(256),varchar(512),tinyint
    即使用 LZ 的 SQL 查询也才 0.34 sec , 3 楼的方法 0.10 sec

    同服务器上,另一张表( 363,175 条记录), 24 个字段
    mediumint,char(32),char(2),char(2),char(5),mediumint,tinyint,timestamp,timestamp,tinyint,mediumint,bigint,bigint,tinyint,tinyint,tinyint,smallint,mediumint,varchar(512),varchar(512),varchar(64),varchar(256),varchar(32),varchar(32)
    同样用 LZ 的 SQL 查询得 2.51 sec , 3 楼的方法 0.09 sec

    同服务器上,领一张表( 363,175 条记录),是上一张表的附属表, 17 个字段
    mediumint,varchar(8),varchar(32),varchar(32),varchar(256),varchar(256),varchar(256),varchar(256),text,text,text,text,text,text,text,text,blob
    同样用 LZ 的 SQL 查询花了 1 min 21.13 sec , 3 楼的方法 0.21 sec
    Magic347
        17
    Magic347  
       2016-10-27 14:06:03 +08:00
    3 楼的解法只能从一定程度上缓解题主的痛点,但不是这一类问题的根本解决方案。
    这类问题其实是可以归结为同一类问题的,那就是如何高效的从海量关系型数据库中实时查询得到按需排序分页后的数据。以下的个人见解不提供完整的解决方案,仅供抛砖引玉,楼主可以之后慢慢探索优化方案。

    众所周知,类似 MySQL 这一类数据库引擎,在面对海量数据(量级千万以上)执行过滤排序时,效率是很难令人满意的。一般的做法往往是,预先将候选数据集筛选出来加载至内存,然后再在内存中使用一定的技巧进行排序和分页,最终输出所需展示的分页数据。

    那么,这里就涉及到至少 2 个问题:
    1. 如何高效的从数据库引擎中筛选候选数据集。
    对于单表的情况,执行简单查询即可。但是对于分表数据,则需要考虑跨表查询问题,这里就涉及到目标数据表的定位问题。
    2. 如何在内存中对候选数据集执行高效的内排序和分页操作。
    这里也自然不会简单就把候选数据做一个内存排序,可以借鉴桶排的思想,按照排序字段进行区间划分,然后定位到所需页面数据所位于的 k 个桶,最后仅需排序这 k 个桶中的数据即可。这里涉及到的一些计算和排序的细节往往都有很大的优化空间。

    以上。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3217 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 00:04 · PVG 08:04 · LAX 16:04 · JFK 19:04
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.