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

2016-07-31 14:07:18 +08:00
 kisshere

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

9369 次点击
所在节点    MySQL
36 条回复
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
很常见的 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
bugsnail
2016-07-31 15:05:26 +08:00
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
AbrahamGreyson
2016-07-31 21:56:15 +08:00
id 建索引, category 移除, limit 改小,用 id 做细节限制。
zzcworld
2016-07-31 22:58:09 +08:00
不要用 LIMIT 300000,10 ,使用 WHERE id > xxx LIMIT 10

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/296130

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX