百万（258万）数据单表查询WHERE+ORDER BY慢查询优化实战-PHP后端- WEB程序开发笔记

百万（258万）数据单表查询WHERE+ORDER BY慢查询优化实战

原创
作者：程序员三丰
发布时间：2024-11-08 00:36
标签： Mysql Mysql5.7
浏览量：64

本文优化内容涉及复合索引创建，以及索引失效后如何使用强制索引。

慢查询SQL语句：
```sql
SELECT
	`id`,
	`order_number`,
	`status`,
  `mer_id`,
	`user_id`,
	`provider_id`,
	`pay_type`,
	`order_amount`,
	`pay_amount`,
	`out_order_number`,
	`trans_order_number`,
	`create_time`,
	`pay_time`,
	`order_type`,
	`refund_amount`,
	`order_title`,
	`request_ip`,
	`pay_url`,
	`remark` 
FROM
	`order`
WHERE
	`mer_id` = '675325ee-acda-48af-affe-5d76ebceb035' 
ORDER BY
	`create_time` DESC 
	LIMIT 0, 11
```

上面是一条简单的单表查询SQL，查询结构很简单，一个WHERE条件，一个ORDER BY排序，且查询字段 mer_id 和 排序字段 create_time 都分别创建了单字段的索引，而且还做了分页，这样看来不应该会出现慢查询。
结果这条SQL查询实际耗时：<strong style="color: #f00">80秒左右</strong>  🤯🤯🤯 这指定无法接受啊！！

直接上优化解决方案：

①首先查看表的总记录数

```sql
SELECT COUNT(*) FROM order
```
等了N秒查询结果出来了，我擦，2580178条，看着挺大毕竟百万级，理论上并不算大。继续~~

② 考虑索引，很多人觉得索引不合理或者没生效，改变索引策略，针对上面的SQL结构创建一个复合索引
```sql
CREATE INDEX idx_mer_id_create_time ON `order` (mer_id, create_time);
```
到这一步，正常的话应该就可以解决了本文的慢查询的的问题了。

但是，如果你发现还没效果，依然慢查询😓😓😓 此时头大了，如此简单的SQL语句，建了索引无效果，难不成这个数据量就要分区或分表了吗？？想想也不至于搞这么麻烦啊~~ 继续~

③ 此时我们需要借助执行计划查询工具 EXPLAIN，检查下我们创建的索引是否生效
```sql
EXPLAIN SELECT
	`id`,
	`order_number`,
	`status`,
  `mer_id`,
	`user_id`,
	`provider_id`,
	`pay_type`,
	`order_amount`,
	`pay_amount`,
	`out_order_number`,
	`trans_order_number`,
	`create_time`,
	`pay_time`,
	`order_type`,
	`refund_amount`,
	`order_title`,
	`request_ip`,
	`pay_url`,
	`remark` 
FROM
	`order`
WHERE
	`mer_id` = '675325ee-acda-48af-affe-5d76ebceb035' 
ORDER BY
	`create_time` DESC 
	LIMIT 0, 11
```
执行结果如下：
![](https://cnd.51blog.xyz/images/202411/f0674e3d7aab7db014391cd11e9c4ffe.png)
如果结果中 key 列值为 idx_mer_id_create_time （上文我们创建的索引），则表示索引生效，应该就解决了慢查询的问题；
否则，那就是上文我们创建的索引未生效，解决方案是在SQL中通过 FORCE INDEX 强制使用上文创建的索引，实际SQL如下：
```sql
EXPLAIN SELECT
	`id`,
	`order_number`,
	`status`,
  `mer_id`,
	`user_id`,
	`provider_id`,
	`pay_type`,
	`order_amount`,
	`pay_amount`,
	`out_order_number`,
	`trans_order_number`,
	`create_time`,
	`pay_time`,
	`order_type`,
	`refund_amount`,
	`order_title`,
	`request_ip`,
	`pay_url`,
	`remark` 
FROM
	`order` FORCE INDEX ( idx_mer_id_create_time )
WHERE
	`mer_id` = '675325ee-acda-48af-affe-5d76ebceb035' 
ORDER BY
	`create_time` DESC 
	LIMIT 0, 11
```
此时，再使用 EXPLAIN 检查，我们上文创建的索引就生效了，慢查询此时也解决了，此时查询耗时：<strong style="color: #0c0">0.036秒</strong> ，😎😎😎 这效果也太明显了

> 扩展：
ThinkPHP中可以使用force方法来强制使用指定索引。

WEB程序开发笔记

百万（258万）数据单表查询WHERE+ORDER BY慢查询优化实战

文章归档

推荐文章

热门标签

友情链接