Mysql分页查询问题

来源：9-3 索引加的不好，效果可能适得其反（上）

SuccessorSocialism

2020-09-27

一哥您专栏里面的第4大章里的4.4<关于 SQL 查询语句，有什么好的建议吗？>里面有讲如何处分页

SELECT * FROM worker, (SELECT id FROM worker ORDER BY id DESC LIMIT 10000, 10) worker_ WHERE worker.id = worker_.id;

先去查询分页中需要数据的主键 id，之后再根据主键 id 去查询你所需要的数据信息，这个查询数据的过程是依赖主键索引完成的

然后我这边先模拟上边的例子实现了

SELECT spu.id,name,cover_image FROM spu, (SELECT id FROM spu ORDER BY id DESC LIMIT 0, 20) spu_ WHERE spu.id = spu_.id;

这里是查询结果分析

这里可以看到走了index 即主键索引
然后我根据这个拓展写出了如下的sql语句

 SELECT spu.id spu_id,cover_image image,name,category_id,is_sale,k.price,spu.sale,k.stock,spu.unit,spu.commission_rule,spu.share_rule,spu.ctime,spu.up_time,spu.sort  FROM spu ,(SELECT id FROM spu  ORDER BY sort DESC limit 0,20 )spu_  LEFT JOIN sku k ON spu_.id=k.spu_id WHERE  spu.id=spu_.id AND spu.ent_id=131419 GROUP BY spu.id ORDER BY spu.sort DESC,spu.ctime DESC

这是join语句的分析结果

然后这里看到走的全表这个是因为我的group by或者join产生的?还是我需要从新建一个联合索引?

写回答

2回答

张勤一

2020-09-27

已采纳

琨陈你好：

其实你这里的问题就是说：明明（其实是鲲鲲）是优化了 SQL 语句，但是，为什么 MySQL 还是会全表扫描？那么，问题来了，全表扫描就一定会很慢，性能很差吗？

不，O No，你可能忽略了一个很重要的概念：【回表】（表就是表哥那个表）。实际上这是因为当使用索引的时候，除非使用了 covering index（覆盖索引），否则一旦索引定位到数据地址后，这里会有一个「回表」的操作，形象一点来说，就是返回原始表中对应行的数据，以便引擎进行再次过滤，一旦回表操作过于频繁，那么性能无疑将急剧下降，全表扫描没有这个问题，因为它就没用索引，所以不存在所谓「回表」操作。

那么，当你的数据量很小的时候，MySQL 不需要优化器去优化你的 SQL 语句，不需要回表，当然是最快的方式了。所以，当你的数据只有1万条左右的时候（且数据结构不是很复杂），你想怎么查就可以怎么查，MySQL 的性能比你想象中的要强悍很多（但是，应该没有宇文志成和表哥强悍）。

但是，我终究还是想要（不管你想不想要，我是很想要）强调一点，全表扫描在大多数情况下都是应该避免的，特别是数据量很大的情况下。所以，我讲解几个会造成全表扫描的点：

1. 模糊查询 like '%...%'

2. 查询条件中含有 is null 的 select 语句

3. 查询条件中使用了不等于操作符（<>、!=）的 select 语句

4. 使用组合索引，如果查询条件中没有 prefix 列，那么索引不起作用，会引起全表扫描

5. or 语句使用不当会引起全表扫描：where 子句中比较的两个条件，一个有索引，一个没索引，使用 o r则会引起全表扫描

我是勤一，致力于将这门课程的问答区打造为 Java 知识体系知识库，Java 知识体系 BBS！共同建造、维护这门课程，我需要每一个你！

SuccessorSocialism

非常感谢！

2020-10-19

共1条回复