mysql 如何优化limit_mysql优化之limit优化

今天来探讨的问题是mysql的limit优化问题。

准备环境：

1.centos7一台

2.mysql数据库一个

3.actor表一个

我们首先来看一下表结构：

下面我们来看一下表的记录条数：

从图中可以看出来，表有四个字段，其中actor_id是主键,并且表中记录是200条。

那么我们来测试第一条sql。

explain select * from actor limit 0,1 \G;

从图中我们可以看出当我们查询这一条数据的时候数据库扫描的行数为200行。

那么我们来测试第二条sql

explain select * from actor limit 1,1 \G;

从图中我们可以看出当我们执行这条sql的时候数据库扫描的行数为200行。

从上面的情况下似乎我们这一个简单的查询语句需要表扫描，为了证明这一点我们向表中插入一条数据看一下。

好的：我们插入一条数据成功。这个时候我们再来执行一下刚开始的sql吧。

我们从图中发现事实就如我们猜想的那样，尽管我们只查询了一条数据，但是表扫描行数确实整张表，如果这张表的行数是几千万，那岂不是要扫描几千万行才返回给我们一条数据吗？遇到这种问题，我们该怎么优化呢？

我们用主键或者索引的时候可以减少查询的行数，那么让我们来改一下我们的sql。

explain select * from actor order by actor_id limit 0,1 \G;

从图中我们惊喜的发现它这次只查询了一条就直接返回了我们的数据，那到底是不是这样呢？

带着疑问我们再把sql改一下：

explain select * from actor order by actor_id limit 10,1 \G;

从图中我们看到我们同样是查询了一条数据，但是数据库查询的行数是11行，这个既让我们惊喜的同时也让我们觉得有些伤心。

惊喜的是我们查询一条数据不在是扫描整张表了，伤心的是我们需要扫描从查询的起始开始的之前数据都要被扫描。

我们再测试一条sql

explain select * from actor order by actor_id limit 200,1 \G;

从图中我们看出扫描了201行，那么我们来假设这种情况，如果我们做分页的时候，行数达到了1千万行，那么我分页查询倒数第一条数据的时候，数据库就要扫描1千万行，我们只需要查询一条啊，加入这个数再大了呢？这是我们觉得不允许的，那么我们该如何来优化呢？

我们来改一下之前sql

expalin select * from actor where actor_id > 0 and actor_id < 2 \G;

从图中我们发现执行这条语句的时候只查询了1条。那么我们把where的值改大一下看一下吧。

explain select * from actor where actor_id > 199 and actor_id < 201 \G;

从图中我们发现我们达到了要求，当我们只想要一条数据时候，我们只希望表扫描一行的要求。

那么到现在我们做的这些有什么意义呢？

比如我们是否这样可以解读？只需要一条数据，完全可以用主键代替查询，比如像下面这样

explain select * from actor where actor_id = 200 \G;

很显然，上面是查询了一条数据就返回了结果。但是对于数据库分页来说真实来说每次返回的数据显然不是一条而是多条。

但是我们又从上面看出来用limit呢数据量小的时候还好，即使没有按照主键排序来说数据库的I/o操作都是很小的，可以忽略不计的，但是我们的sql优化针对的不是简单的200行数据，而是针对的几百万行，上千万行，这个时候对于limit的使用来说我们查询几条数据带来的数据库I/O操作开销是巨大的。

那么我们就可以用上面的测试结果来进行分页的优化，我们可以这样想每次查询完之前就得到上次查询的最后的ID，然后再放入where中，当然了前提是where中的查询条件是主键。

但是在上面的操作是有一个缺点的，比如我们的数据库主键如果中间断掉了一部分怎么办，我们就无法保证每页都存在数据了。

比如我们分页的时候每次要求查询三条数据，这个时候数据库从101到104这些数据都被删除了，那么当用户查询这一页的时候就得不到数据，数据库返回为空。

所以为了解决上面的问题我们想着如果有这么一行数据和主键相似，但是删除之后就可以自己从新开始排序多好啊，这样我们赋予这一类数据为索引，当使用上面的where的时候岂不是完美吗？

但是真有这样的数据类型吗？