› 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 · Jul 31, 2016 · 10778 views
    This topic created in 3762 days ago, the information mentioned may be changed or developed.

    一个 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 秒左右,请问有没有什么优化的办法?

    Supplement 1  ·  Jul 31, 2016
    谢谢大家反馈,目前这个硬件设备不是一般的渣, 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 游标分页的方式,请问这种方式如何,还有更好的方法吗?
    Supplement 2  ·  Aug 1, 2016
    刚刚把数据表迁移到手上一台 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 replies  •  2016-08-05 23:20:17 +08:00
    jarlyyn
    jarlyyn
    Jul 31, 2016
    1
    对 category,id 做多列索引?
    Srar
    Srar
    Jul 31, 2016
    2
    可能是 LIMIT 导致的 查下高性能分页吧
    clarkchen
    clarkchen
    Jul 31, 2016
    3
    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
    rekulas
    Jul 31, 2016
    4
    limit 的性能确实很差的,如果表变动不大的话可以再加一个 realid 索引字段用来排序,定时计划任务处理下,然后就可以 WHERE category='students' and realid>xxx and realid <xxx+10 时间应该可以控制在 10 毫秒级
    rekulas
    rekulas
    Jul 31, 2016
    5
    哦 忽略了点 三种类型的话得分开排序才行
    shiny
    shiny
    Jul 31, 2016 via iPhone
    6
    很常见的 limit 过大导致的性能问题
    rekulas
    rekulas
    Jul 31, 2016
    7
    另外,这机器的配置估计也很低,几十万级就算 limit 也不至于这个级别(0.X 秒我觉得比较正常),可能硬盘比较差
    skydiver
    skydiver
    Jul 31, 2016
    8
    一共就三种类型,加索引意义不大
    dexterzzz
    dexterzzz
    Jul 31, 2016
    9
    category 的索引去掉。

    “永远不要索引性别列”,是由于这列只会存在男性和女性两个值。当遇到 WHERE Gender=的语句时使用表扫描要远远好于书签查找,查询优化器无法从这个索引中获益。”
    kiwi95
    kiwi95
    Jul 31, 2016
    10
    limit 起始的位置导致性能下降,子查询可以找出起始的 id 再加 where id>start order by id limit 10
    dexterzzz
    dexterzzz
    Jul 31, 2016❤️1
    11
    shot
    shot
    Jul 31, 2016
    13
    可能原因:
    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
    kisshere
    OP
    Jul 31, 2016
    14
    @dexterzzz 谢谢,那请问像这种只存在三种情况的字段,应该怎样优化查询?硬盘确实很渣, HDD 的, CPU 还是 atom 的 cpu
    oclock
    oclock
    Jul 31, 2016
    15
    category 的 cardinality 这么小,索引没什么效果,看一下 explain 在哪里花的时间最多
    otakustay
    otakustay
    Jul 31, 2016
    16
    这种问题不都应该先让楼主 explain 下把结果弄上来再说么
    mathgl
    mathgl
    Jul 31, 2016
    17
    46 万记录,如果没有 blob,text 。稍微大点内存都直接进 cache 了,就算全表扫描也不需要 5 秒。
    AbrahamGreyson
    AbrahamGreyson
    Jul 31, 2016
    19
    id 建索引, category 移除, limit 改小,用 id 做细节限制。
    zzcworld
    zzcworld
    Jul 31, 2016 via iPhone
    20
    不要用 LIMIT 300000,10 ,使用 WHERE id > xxx LIMIT 10
    superalsrk
    superalsrk
    Jul 31, 2016
    21
    扫表的话。。 40 多万条也不应该这么慢啊。。可以看一下 profile 查看一下是哪个过程比较耗时: 参考 http://stackbox.cn/2016-07-some-performance-realated-tools/
    Alucns
    Alucns
    Aug 1, 2016 via iPad
    22
    去掉这个会快很多 ORDER BY id DESC
    501956430
    501956430
    Aug 1, 2016 via iPhone
    23
    主机性能不行,只能这样了,有个类似的表 50w 数据 分页查询就几百毫秒以内
    Symars
    Symars
    Aug 1, 2016 via iPhone
    24
    limit 问题 先查根据 cate 查 id
    Khlieb
    Khlieb
    Aug 1, 2016 via Android
    25
    MariaDB
    winglight2016
    winglight2016
    Aug 1, 2016
    26
    @Khlieb MariaDB 据说和 MySQL 差距不是很明显啊?
    yuxing1171
    yuxing1171
    Aug 1, 2016
    27
    问题出在 LIMIT 300000 , 换种翻页方式吧。
    firefox12
    firefox12
    Aug 1, 2016
    28
    为什么 id 不加索引呢?
    Navee
    Navee
    Aug 1, 2016
    29
    问题不在索引,在 limit
    看看这个 http://www.fienda.com/archives/110
    楼主还是多学习,多问问提,少下结论
    cloudzhou
    cloudzhou
    Aug 1, 2016
    30
    1 试试使用 smallint 来表示 category ,使用枚举,不要用字符
    2 在 1 的基础上, create index users_category_id_idx on users(category, id desc);

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

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

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