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

46 万行的 MySQL 数据表,一个 where col='xxx'查询都要 5 秒左右,正常吗?

  •  
  •   kisshere · 2016-07-31 14:07:18 +08:00 · 9363 次点击
    这是一个创建于 3035 天前的主题,其中的信息可能已经有所发展或是发生改变。

    一个 46 万行的 mysql 表,其中的 category 字段做了普通索引的: ALTER TABLE users ADD INDEX(category), category 就三种类型:'students','teachers','workers',现在一个 SELECT id FROM users WHERE category='students' ORDER BY id DESC LIMIT 300000,10 ,这个 query 试了好多次平均耗时都在 5 秒左右,请问有没有什么优化的办法?

    第 1 条附言  ·  2016-07-31 15:32:36 +08:00
    谢谢大家反馈,目前这个硬件设备不是一般的渣, HDD 二手硬盘, cpu 是 Atom N2800 的 cpu , 4G 内存,但是这个数据表是每天才增删改一次,我不想采用 where id>xxx 这种游标分页的方式,而是采用 /page-1 /page-2 这种 url 方式分页,我目前想的方法是用 nosql 缓存每个 page_id 对应的 id ,/page-2345 查询时,查询 page_id=2345 在 nosql 中对应的 MySQL 主键 id ,再在 MySQL 中 query id>xxx 游标分页的方式,请问这种方式如何,还有更好的方法吗?
    第 2 条附言  ·  2016-08-01 10:48:22 +08:00
    刚刚把数据表迁移到手上一台 Ramnode 1GB RAM 40GB SSD 的 vps 上,执行 http://www.v2ex.com/t/296130 这里面的查询,还是要 1.8s ,我现在得出结论了, MySQL 就不适合十万级以上的数据!哪怕是 SELECT id FROM users WHERE category='students' ORDER BY id DESC LIMIT 400000,1 这个语句都在 1.6s 以上。。。
    36 条回复    2016-08-05 23:20:17 +08:00
    jarlyyn
        1
    jarlyyn  
       2016-07-31 14:28:31 +08:00
    对 category,id 做多列索引?
    Srar
        2
    Srar  
       2016-07-31 14:34:26 +08:00
    可能是 LIMIT 导致的 查下高性能分页吧
    clarkchen
        3
    clarkchen  
       2016-07-31 14:35:59 +08:00
    SELECT id FROM users WHERE category='students' ORDER BY id DESC LIMIT 10 这个查询十行
    SELECT id FROM users WHERE category='students' ORDER BY id DESC LIMIT 1000, 10 这个查询千行的量
    SELECT id FROM users WHERE category='students' ORDER BY id DESC LIMIT 10000, 10 这个查询万行的量

    感觉你这个 sql 几乎是扫表了。。
    rekulas
        4
    rekulas  
       2016-07-31 14:38:29 +08:00
    limit 的性能确实很差的,如果表变动不大的话可以再加一个 realid 索引字段用来排序,定时计划任务处理下,然后就可以 WHERE category='students' and realid>xxx and realid <xxx+10 时间应该可以控制在 10 毫秒级
    rekulas
        5
    rekulas  
       2016-07-31 14:39:40 +08:00
    哦 忽略了点 三种类型的话得分开排序才行
    shiny
        6
    shiny  
       2016-07-31 14:42:28 +08:00 via iPhone
    很常见的 limit 过大导致的性能问题
    rekulas
        7
    rekulas  
       2016-07-31 14:43:42 +08:00
    另外,这机器的配置估计也很低,几十万级就算 limit 也不至于这个级别(0.X 秒我觉得比较正常),可能硬盘比较差
    skydiver
        8
    skydiver  
       2016-07-31 14:56:13 +08:00
    一共就三种类型,加索引意义不大
    dexterzzz
        9
    dexterzzz  
       2016-07-31 14:56:44 +08:00
    category 的索引去掉。

    “永远不要索引性别列”,是由于这列只会存在男性和女性两个值。当遇到 WHERE Gender=的语句时使用表扫描要远远好于书签查找,查询优化器无法从这个索引中获益。”
    kiwi95
        10
    kiwi95  
       2016-07-31 14:59:47 +08:00
    limit 起始的位置导致性能下降,子查询可以找出起始的 id 再加 where id>start order by id limit 10
    dexterzzz
        11
    dexterzzz  
       2016-07-31 15:01:08 +08:00   ❤️ 1
    bugsnail
        12
    bugsnail  
       2016-07-31 15:05:26 +08:00
    shot
        13
    shot  
       2016-07-31 15:07:07 +08:00
    可能原因:
    1. id 不是主键或者没加索引;
    2. 机器性能非常非常非常差。

    在 rmbp 2015 上测试,十万量级耗时 0.1 秒,百万量级 1.46 秒。

    ----------------
    delimiter $$

    create procedure init_data()
    begin
    declare i int default 1;
    declare s varchar(15);
    declare r double;

    drop table if exists users;

    create table users (
    id int(11) primary key auto_increment,
    category varchar(15) not null
    );
    alter table users add index(category);


    while (i <= 4600000) do
    set r = rand();
    if r < 0.8 then set s = 'students';
    elseif r < 0.9 then set s = 'teachers';
    else set s = 'workers';
    end if;

    insert into users values(i, s);
    set i = i + 1;
    end while;
    end$$

    delimiter ;

    call init_data();

    drop procedure if exists init_data;


    select count(1) from users;
    select count(1) from users where category = 'students';

    select id from users
    where category = 'students'
    order by id desc
    limit 3000000, 10;
    kisshere
        14
    kisshere  
    OP
       2016-07-31 15:23:27 +08:00
    @dexterzzz 谢谢,那请问像这种只存在三种情况的字段,应该怎样优化查询?硬盘确实很渣, HDD 的, CPU 还是 atom 的 cpu
    oclock
        15
    oclock  
       2016-07-31 15:31:21 +08:00
    category 的 cardinality 这么小,索引没什么效果,看一下 explain 在哪里花的时间最多
    otakustay
        16
    otakustay  
       2016-07-31 16:04:02 +08:00
    这种问题不都应该先让楼主 explain 下把结果弄上来再说么
    mathgl
        17
    mathgl  
       2016-07-31 17:35:54 +08:00
    46 万记录,如果没有 blob,text 。稍微大点内存都直接进 cache 了,就算全表扫描也不需要 5 秒。
    phperstar
        18
    phperstar  
       2016-07-31 20:33:23 +08:00
    AbrahamGreyson
        19
    AbrahamGreyson  
       2016-07-31 21:56:15 +08:00
    id 建索引, category 移除, limit 改小,用 id 做细节限制。
    zzcworld
        20
    zzcworld  
       2016-07-31 22:58:09 +08:00 via iPhone
    不要用 LIMIT 300000,10 ,使用 WHERE id > xxx LIMIT 10
    superalsrk
        21
    superalsrk  
       2016-07-31 23:51:56 +08:00
    扫表的话。。 40 多万条也不应该这么慢啊。。可以看一下 profile 查看一下是哪个过程比较耗时: 参考 http://stackbox.cn/2016-07-some-performance-realated-tools/
    Aluhao
        22
    Aluhao  
       2016-08-01 01:10:54 +08:00 via iPad
    去掉这个会快很多 ORDER BY id DESC
    501956430
        23
    501956430  
       2016-08-01 01:23:32 +08:00 via iPhone
    主机性能不行,只能这样了,有个类似的表 50w 数据 分页查询就几百毫秒以内
    Symars
        24
    Symars  
       2016-08-01 07:53:33 +08:00 via iPhone
    limit 问题 先查根据 cate 查 id
    Khlieb
        25
    Khlieb  
       2016-08-01 08:58:44 +08:00 via Android
    MariaDB
    winglight2016
        26
    winglight2016  
       2016-08-01 10:56:45 +08:00
    @Khlieb MariaDB 据说和 MySQL 差距不是很明显啊?
    yuxing1171
        27
    yuxing1171  
       2016-08-01 11:11:15 +08:00
    问题出在 LIMIT 300000 , 换种翻页方式吧。
    firefox12
        28
    firefox12  
       2016-08-01 11:12:12 +08:00
    为什么 id 不加索引呢?
    Navee
        29
    Navee  
       2016-08-01 12:39:11 +08:00
    问题不在索引,在 limit
    看看这个 http://www.fienda.com/archives/110
    楼主还是多学习,多问问提,少下结论
    cloudzhou
        30
    cloudzhou  
       2016-08-01 14:01:12 +08:00
    1 试试使用 smallint 来表示 category ,使用枚举,不要用字符
    2 在 1 的基础上, create index users_category_id_idx on users(category, id desc);

    然后使用同样的 sql 语句,看看这时候速度是多少呢?
    如果还是有问题,使用 redist 的 sortedset 来存储每个 category 的 id ,以 desc 排序
    iyaozhen
        31
    iyaozhen  
       2016-08-01 14:06:01 +08:00
    MySQL 就不适合十万级以上的数据!
    并不赞同。我刚跑了一下 1 亿条的表,差不多的 SQL 用时 2.34s 。机器上硬盘是 HDD ,不过 cpu 、内存比较大。

    楼上也说了,问题不在硬盘或者索引,在 limit 上,之前就有人说过这类的优化方案,你的 SQL 应该这样写: SELECT id FROM users WHERE category='students' and id > 400000 LIMIT 1 。

    大数据量下的翻页可以牺牲一些准确度换取性能。
    hao123yinlong
        32
    hao123yinlong  
       2016-08-01 14:23:24 +08:00
    顶楼上 , 2 核 4G , HDD ,青云提供的 mysql 服务 ,> 200 w 单个小表 ,平均 5 ms 内响应
    palfortime
        33
    palfortime  
       2016-08-01 20:17:04 +08:00
    只有三类值的 column ,加索引和不加基本没有什么区别。假如三个值均匀分布, SELECT id FROM users WHERE category='students' ORDER BY id DESC LIMIT 300000,10 这条语句也是要查找 15 万条,和 46 万也是在一个级别。按楼上说的,翻页时记着上一次最后一个 id ,用 id 的索引来查更好,均匀分布的话,就查询几十条。
    nightspirit
        34
    nightspirit  
       2016-08-01 23:04:51 +08:00
    这么点数据应该不会这么慢的, id 肯定是要加索引,然后就是上面有提到的那种子句查询,这是一种延迟关联,这种确实可以大大提升性能,然后就是 nosql ,这种方案可行,最后就是那种翻页的时候传递 id ,这种应该是效果最好的,综合效果(包括维护优化成本),不过好像我在开发中除了做 app 有这样写过, pc 应用好像都还没这么做过。
    shaohuifan
        35
    shaohuifan  
       2016-08-04 11:17:26 +08:00 via iPhone
    mysql 千万级才会有性能问题,你的问题是 limit
    Khlieb
        36
    Khlieb  
       2016-08-05 23:20:17 +08:00
    @winglight2016 接口应该差不多,但性能差得明显。 @livid 有个帖子做过比较。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3535 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 04:26 · PVG 12:26 · LAX 20:26 · JFK 23:26
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.