一个 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
jarlyyn 2016-07-31 14:28:31 +08:00
对 category,id 做多列索引?
|
2
Srar 2016-07-31 14:34:26 +08:00
可能是 LIMIT 导致的 查下高性能分页吧
|
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 几乎是扫表了。。 |
4
rekulas 2016-07-31 14:38:29 +08:00
limit 的性能确实很差的,如果表变动不大的话可以再加一个 realid 索引字段用来排序,定时计划任务处理下,然后就可以 WHERE category='students' and realid>xxx and realid <xxx+10 时间应该可以控制在 10 毫秒级
|
5
rekulas 2016-07-31 14:39:40 +08:00
哦 忽略了点 三种类型的话得分开排序才行
|
6
shiny 2016-07-31 14:42:28 +08:00 via iPhone
很常见的 limit 过大导致的性能问题
|
7
rekulas 2016-07-31 14:43:42 +08:00
另外,这机器的配置估计也很低,几十万级就算 limit 也不至于这个级别(0.X 秒我觉得比较正常),可能硬盘比较差
|
8
skydiver 2016-07-31 14:56:13 +08:00
一共就三种类型,加索引意义不大
|
9
dexterzzz 2016-07-31 14:56:44 +08:00
category 的索引去掉。
“永远不要索引性别列”,是由于这列只会存在男性和女性两个值。当遇到 WHERE Gender=的语句时使用表扫描要远远好于书签查找,查询优化器无法从这个索引中获益。” |
10
kiwi95 2016-07-31 14:59:47 +08:00
limit 起始的位置导致性能下降,子查询可以找出起始的 id 再加 where id>start order by id limit 10
|
11
dexterzzz 2016-07-31 15:01:08 +08:00 1
|
12
bugsnail 2016-07-31 15:05:26 +08:00
|
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; |
14
kisshere OP @dexterzzz 谢谢,那请问像这种只存在三种情况的字段,应该怎样优化查询?硬盘确实很渣, HDD 的, CPU 还是 atom 的 cpu
|
15
oclock 2016-07-31 15:31:21 +08:00
category 的 cardinality 这么小,索引没什么效果,看一下 explain 在哪里花的时间最多
|
16
otakustay 2016-07-31 16:04:02 +08:00
这种问题不都应该先让楼主 explain 下把结果弄上来再说么
|
17
mathgl 2016-07-31 17:35:54 +08:00
46 万记录,如果没有 blob,text 。稍微大点内存都直接进 cache 了,就算全表扫描也不需要 5 秒。
|
18
phperstar 2016-07-31 20:33:23 +08:00
|
19
AbrahamGreyson 2016-07-31 21:56:15 +08:00
id 建索引, category 移除, limit 改小,用 id 做细节限制。
|
20
zzcworld 2016-07-31 22:58:09 +08:00 via iPhone
不要用 LIMIT 300000,10 ,使用 WHERE id > xxx LIMIT 10
|
21
superalsrk 2016-07-31 23:51:56 +08:00
扫表的话。。 40 多万条也不应该这么慢啊。。可以看一下 profile 查看一下是哪个过程比较耗时: 参考 http://stackbox.cn/2016-07-some-performance-realated-tools/
|
22
Aluhao 2016-08-01 01:10:54 +08:00 via iPad
去掉这个会快很多 ORDER BY id DESC
|
23
501956430 2016-08-01 01:23:32 +08:00 via iPhone
主机性能不行,只能这样了,有个类似的表 50w 数据 分页查询就几百毫秒以内
|
24
Symars 2016-08-01 07:53:33 +08:00 via iPhone
limit 问题 先查根据 cate 查 id
|
25
Khlieb 2016-08-01 08:58:44 +08:00 via Android
MariaDB
|
26
winglight2016 2016-08-01 10:56:45 +08:00
@Khlieb MariaDB 据说和 MySQL 差距不是很明显啊?
|
27
yuxing1171 2016-08-01 11:11:15 +08:00
问题出在 LIMIT 300000 , 换种翻页方式吧。
|
28
firefox12 2016-08-01 11:12:12 +08:00
为什么 id 不加索引呢?
|
29
Navee 2016-08-01 12:39:11 +08:00
|
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 排序 |
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 。 大数据量下的翻页可以牺牲一些准确度换取性能。 |
32
hao123yinlong 2016-08-01 14:23:24 +08:00
顶楼上 , 2 核 4G , HDD ,青云提供的 mysql 服务 ,> 200 w 单个小表 ,平均 5 ms 内响应
|
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 的索引来查更好,均匀分布的话,就查询几十条。
|
34
nightspirit 2016-08-01 23:04:51 +08:00
这么点数据应该不会这么慢的, id 肯定是要加索引,然后就是上面有提到的那种子句查询,这是一种延迟关联,这种确实可以大大提升性能,然后就是 nosql ,这种方案可行,最后就是那种翻页的时候传递 id ,这种应该是效果最好的,综合效果(包括维护优化成本),不过好像我在开发中除了做 app 有这样写过, pc 应用好像都还没这么做过。
|
35
shaohuifan 2016-08-04 11:17:26 +08:00 via iPhone
mysql 千万级才会有性能问题,你的问题是 limit
|
36
Khlieb 2016-08-05 23:20:17 +08:00
@winglight2016 接口应该差不多,但性能差得明显。 @livid 有个帖子做过比较。
|