四种优化MySQL大厅查询的方法，你会选择哪一种？

准备工作

为了测试下面列出的一些优化，下面是对现有表格的解释。

• 表名：orders_history
• 描述：特定公司的订单历史表
• 主要字段：unsigned int id,tinyint(4) int type
• 字段状态：该表共有37个字段，大数据（例如文本）的最大大小为 varchar(500)，并且 id 字段是索引并且不断增加。
• 数据量：5709294
• MySQL版本：5.7.16

百万级测试表离线不容易找到。如果需要自己测试的话，可以写一个shell脚本什么的，输入数据进行测试。以下所有sql语句的执行环境均未改变。主要测试结果如下：

select count(*) from orders_history;

复制代码

返回结果：5709294 三个查询时间分别为： 8903 ms8323 ms8401 ms

通用搜索查询

通用搜索查询 使用简单搜索子句，通用查询可以应用约束。 。 limit 子句声明如下：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

复制代码

LIMIT 子句可用于指定 SELECT 语句返回的记录数。请注意以下几点：

• 第一个参数设置返回第一行记录的偏移量
• 第二个参数设置返回记录的最大行数
• 如果只给出一个参数：表示返回最大记录行数
• 第二个参数为-1，表示查找从某个偏移量到记录集末尾的所有记录行
• 原记录行的偏移量为0 （不是1）

下面是一个应用示例：

select * from orders_history where type=8 limit 1000,10;

复制代码

该语句请求orders_history表中第1000条数据之后的10条数据，即最多1001 10010条数据。默认情况下，数据表中的记录按主键（通常是 id）排序。以上结果是等价的：

select * from orders_history where type=8 order by id limit 10000,10;

复制代码

三个查询时间分别是：3040 ms3063 ms3018 ms对于这种查询方式，下面的测试查询记录了三个查询量对时间的影响：❙。 查询 1 条记录：3072 ms 3092 ms 3002 ms 查询 10 条记录：3081 ms 3077 ms 3032 ms 查询 100 条记录：3118 ms 3200 ms 查询 100 条记录：3128 ms 3394 ms 查询 10000 条记录：3749 ms 3802 ms 3696 ms 此外，我还提出了十多个请求。根据查询时间，基本可以确定，如果查询记录量小于100条，则查询时间本质上不存在差距。随着查询记录量的增大，所花费的时间也就越多。查询偏移测试：

select * from orders_history where type=8 limit 100,100;
select * from orders_history where type=8 limit 1000,100;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 100000,100;
select * from orders_history where type=8 limit 1000000,100;

复制代码

三个查询时间为： 查询 100 偏移：25ms 24ms 24ms 查询 1000 偏移：78ms 76ms 77ms 查询偏移 2ms：3318ms 100000 偏移：3878ms 3812ms 3798ms 查询 1 000000 偏移：14608ms 14062ms 14700ms 随着查询偏移量的增加，尤其是查询偏移量超过10万后，查询时间急剧增加。这种搜索查询方式是从数据库的第一条记录开始扫描，所以越晚查询，速度就越慢，而且查询的数据越多，整体查询速度就越慢。

使用子查询优化。如果ID增大，此方法适用。

select * from orders_history where type=8 limit 100000,1;
select id from orders_history where type=8 limit 100000,1;
select * from orders_history where type=8 and 
id>=(select id from orders_history where type=8 limit 100000,1) 
limit 100;
select * from orders_history where type=8 limit 100000,100;

复制代码

四个语句的请求时间如下：1。语句：3674 ms 第二条语句：1315 ms 第三条语句：1327 ms 第四条请求注释：3710 ms❙❙♾ 比较第一条语句和第二条语句：使用 * 代替 select id，速度提升 3 倍

比较第2句和第3句：速度相差几十毫秒

对比第3句和第4句：由于Select id速度的提升，第3句的查询速度提升了3倍

该方法比原来的通用查询方法快数倍。

使用ID约束优化

该方法假设数据表的id不断增加。然后我们可以根据查询的页数和查询次数来计算查询ID范围。可以在 和 之间使用 id 来查询：

select * from orders_history where type=2 
and id between 1000000 and 1000100 limit 100;

复制代码

查询时间：15ms 12ms 9ms 这种查询方式可以大大优化查询速度，基本可以在几十毫秒内完成。限制是只有在明确知道 ID 的情况下才能使用。不过在创建表的时候一般也会加上基本的id字段，这给浏览查询带来了很多方便。还有一种写法：

select * from orders_history where id >= 1000001 limit 100;

复制代码

当然也可以使用in方法进行查询。该方法常用于涉及多表的查询。使用其他表查询的ID集来查询：

select * from orders_history where id in
(select order_id from trade_2 where goods = 'pen')
limit 100;

复制代码

这种查询方法要小心：某些版本的mysql不支持在子句中使用约束。

使用临时表优化

这个方法不再是查询优化，我在这里提一下。如果用ID限制优化存在问题，就必须不断增加ID。但是，在某些场景下，例如使用历史表或丢失数据，您可能需要考虑使用临时存储表来存储页面ID。使用 ID 和页码来发出请求。这可以大大提高传统搜索查询的速度，尤其是当数据量达到千万级时。

从数据表id的描述来看

一般情况下，在数据库中创建表时，是强制给每个表添加一个id递增字段，以方便查询。当数据量很大时，比如订单数据库，通常会分为数据库和表。目前不建议使用数据库ID作为唯一标识符。相反，应该使用分布式高并发唯一ID生成器来生成它，并使用数据表中的另一个字段来存储唯一标识符。使用中间查询先查找ID（或索引），然后使用索引查找数据可以将查询速度提高很多倍。这意味着首先选择 id，然后选择 *；

作者：程序员追风
链接：https://juejin.im/post/5d905039e51d45782d053ca3
来源：掘金版权归作者所有。商业转载请联系作者获取授权。非商业转载请注明出处。