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

一个 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

2016-07-31 14:28:31 +08:00

对 category,id 做多列索引？

Srar

2016-07-31 14:34:26 +08:00

可能是 LIMIT 导致的查下高性能分页吧

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

2016-07-31 14:38:29 +08:00

limit 的性能确实很差的，如果表变动不大的话可以再加一个 realid 索引字段用来排序，定时计划任务处理下，然后就可以 WHERE category='students' and realid>xxx and realid <xxx+10 时间应该可以控制在 10 毫秒级

rekulas

2016-07-31 14:39:40 +08:00

哦忽略了点三种类型的话得分开排序才行

shiny

2016-07-31 14:42:28 +08:00 via iPhone

很常见的 limit 过大导致的性能问题

rekulas

2016-07-31 14:43:42 +08:00

另外，这机器的配置估计也很低，几十万级就算 limit 也不至于这个级别(0.X 秒我觉得比较正常)，可能硬盘比较差

skydiver

2016-07-31 14:56:13 +08:00

一共就三种类型，加索引意义不大

dexterzzz

2016-07-31 14:56:44 +08:00

category 的索引去掉。

“永远不要索引性别列”，是由于这列只会存在男性和女性两个值。当遇到 WHERE Gender=的语句时使用表扫描要远远好于书签查找，查询优化器无法从这个索引中获益。”

kiwi95

2016-07-31 14:59:47 +08:00

limit 起始的位置导致性能下降，子查询可以找出起始的 id 再加 where id>start order by id limit 10

dexterzzz

2016-07-31 15:01:08 +08:00

可以去看这个 SQL 索引博客
http://www.cnblogs.com/CareySon/archive/2012/09/10/2678247.html

bugsnail

2016-07-31 15:05:26 +08:00

http://iexplore.cc/2016/offsetSlow/

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

2016-07-31 15:23:27 +08:00

@dexterzzz 谢谢，那请问像这种只存在三种情况的字段，应该怎样优化查询？硬盘确实很渣， HDD 的， CPU 还是 atom 的 cpu

oclock

2016-07-31 15:31:21 +08:00

category 的 cardinality 这么小，索引没什么效果，看一下 explain 在哪里花的时间最多

otakustay

2016-07-31 16:04:02 +08:00

这种问题不都应该先让楼主 explain 下把结果弄上来再说么

mathgl

2016-07-31 17:35:54 +08:00

46 万记录，如果没有 blob,text 。稍微大点内存都直接进 cache 了，就算全表扫描也不需要 5 秒。

phperstar

2016-07-31 20:33:23 +08:00

https://www.94cb.com/t/2276

AbrahamGreyson

2016-07-31 21:56:15 +08:00

id 建索引， category 移除， limit 改小，用 id 做细节限制。

zzcworld

2016-07-31 22:58:09 +08:00 via iPhone

不要用 LIMIT 300000,10 ，使用 WHERE id > xxx LIMIT 10

superalsrk

2016-07-31 23:51:56 +08:00

扫表的话。。 40 多万条也不应该这么慢啊。。可以看一下 profile 查看一下是哪个过程比较耗时: 参考 http://stackbox.cn/2016-07-some-performance-realated-tools/

Aluhao

2016-08-01 01:10:54 +08:00 via iPad

去掉这个会快很多 ORDER BY id DESC

501956430

2016-08-01 01:23:32 +08:00 via iPhone

主机性能不行，只能这样了，有个类似的表 50w 数据分页查询就几百毫秒以内

Symars

2016-08-01 07:53:33 +08:00 via iPhone

limit 问题先查根据 cate 查 id

Khlieb

2016-08-01 08:58:44 +08:00 via Android

MariaDB

winglight2016

2016-08-01 10:56:45 +08:00

@Khlieb MariaDB 据说和 MySQL 差距不是很明显啊？

yuxing1171

2016-08-01 11:11:15 +08:00

问题出在 LIMIT 300000 ，换种翻页方式吧。

firefox12

2016-08-01 11:12:12 +08:00

为什么 id 不加索引呢？

Navee

2016-08-01 12:39:11 +08:00

问题不在索引，在 limit
看看这个 http://www.fienda.com/archives/110
楼主还是多学习，多问问提，少下结论

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

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

2016-08-01 14:23:24 +08:00

顶楼上， 2 核 4G ， HDD ，青云提供的 mysql 服务，> 200 w 单个小表，平均 5 ms 内响应

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

2016-08-01 23:04:51 +08:00

这么点数据应该不会这么慢的， id 肯定是要加索引，然后就是上面有提到的那种子句查询，这是一种延迟关联，这种确实可以大大提升性能，然后就是 nosql ，这种方案可行，最后就是那种翻页的时候传递 id ，这种应该是效果最好的，综合效果（包括维护优化成本），不过好像我在开发中除了做 app 有这样写过， pc 应用好像都还没这么做过。

shaohuifan

2016-08-04 11:17:26 +08:00 via iPhone

mysql 千万级才会有性能问题，你的问题是 limit

Khlieb

2016-08-05 23:20:17 +08:00

@winglight2016 接口应该差不多，但性能差得明显。 @livid 有个帖子做过比较。