NestLu 2019-03-28 16:58 采纳率: 0%
浏览 1899

MYSQL查询用到的主键id索引问题

CREATE TABLE mytest (
id int(10) NOT NULL AUTO_INCREMENT,
a int(50) DEFAULT NULL,
b int(50) DEFAULT NULL,
c int(50) DEFAULT NULL,
index int(50) DEFAULT NULL,
remark1 varchar(255) DEFAULT NULL,
remark2 varchar(255) DEFAULT NULL,
remark3 varchar(255) DEFAULT NULL,
remark4 varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=10059857 DEFAULT CHARSET=utf8 COMMENT='测试1000万数据';

本人菜鸟,希望大佬们多多指教
这是本人测试建的表,插入了1000万数据,主要是为了测试一下mysql千万数据分页查询时的速度,查询8000000往后100条数据
建表因为主键id唯一自增,所以默认为主键索引

查看索引

SELECT * from mytest limit 8000000,100

OK
时间: 8.106s
此查未使用到索引,耗时比较久

SELECT * from mytest where id>(SELECT id from mytest LIMIT 8000000,1) limit 100

OK
时间: 5.87s
此处似乎用到了索引,速度有所提升?

这是explain结果
图片说明
此处是外面查询用到了索引,子查询未用到索引吗?试了多次发现都是差不多这个速度,感觉还是挺慢的

然后
按理说主键id已经是自增索引,查询速度应该很快才对吧,这时候我又对主键id这个字段单独创建了一个普通索引
CREATE index id_index on mytest(id)

OK
时间: 26.275s

查询索引

查看索引发现和主键索引应该没啥区别,可是,,,

发现查询居然变快了不少???
SELECT * from mytest limit 8000000,100

OK
时间: 9.242s
这里未用到索引

SELECT * from mytest where id>(SELECT id from mytest LIMIT 8000000,1) limit 100

OK
时间: 1.853s

这是explain结果
图片说明

这里应该是用到了索引,可是为什么会出现这种情况呢,主键id也是索引,查询就比较慢,单独对id创建了一个普通索引查询速度居然变快了许多,想了半天没明白为啥,求前辈们指教

此外查询时我也尝试连表查询
SELECT * from mytest a join (SELECT id from mytest limit 8000000,100) b on a.id=b.id;
发现和SELECT * from mytest where id>(SELECT id from mytest LIMIT 8000000,1) limit 100;这个查询耗时几乎一致

附一下表数据

图片说明

  • 写回答

1条回答 默认 最新

  • 憧憬blog 2023-03-15 08:56
    关注

    首先,使用LIMIT语句进行分页查询的时候,尽量使用主键做数据分页的标准。

    在你的测试中,使用主键做LIMIT查找时,MySQL进行了1000万数据的扫描,而单独使用了ID索引后,MySQL范围操作就被优化,查询速度变快了。

    至于你的问题中的子查询未使用索引,这是因为子查询中使用了LIMIT语句。在MySQL优化中,LIMIT语句会强制MySQL需要执行子查询才能获得正确的查询结果,因此对子查询使用索引不起作用。对于这种情况,可以使用JOIN语句代替子查询。

    另外,你使用了一个名为index的字段,建议修改为其他名称,否则可能会和MySQL内置的关键字冲突。

    评论

报告相同问题?

悬赏问题

  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)