记录一次Mysql慢查询优化

2024/5/17 mysql

1. Limit offset越大越慢的原因

目前我们产品的日活200-300w，如果增加到2个亿那么接口很难扛得住，因为一直以来业务开发都没有特别考虑到性能问题。这次遇到的问题是表单有9万多页，点击后面的页码会超时～这是个经典的mysql慢查询优化问题。
原语句为：

select id,product_id,username, data 
    from table_name 
    where product_id = 2 
    order by id desc limit 90000,10;

执行时间为6.65秒。
这是由于它的执行过程是这样的：

通过二级索引查到主键值（找出所有product_id=2的id）
再根据查到的主键值索引找到相应的数据块(根据id找出对应的内容)
根据offset值为90000，查询900010次主键索引的数据，最后将之前的900000条都丢弃掉，取后面10条
也就是说它并不会从900000条开始查询，而是此前数据全部查询出来，因此偏移量很大的时候，查询时间也就越多。

2. 解决方法

🐱添加索引

可以添加什么索引呢？

联合索引
创建联合索引
```
  alter table table_name 
  add 
  index `idx_productid_username` (`product_id`,`username`)
```
- 多列经常要一起查询，创建联合索引
- 多列经常被用于排序，例如一个学生表里经常要按照年级和成绩排序。
- 多列经常被用于连接操作
  联合索引需要注意查询时候的列顺序，满足最左匹配。
全文索引
我的表中，data字段是text 类型，查询的时候我需要在大量数据里用like匹配一些数据，在百万数据行里耗时就直接起飞。
创建全文索引：
```
 alter table table_name
 add
 fulltext index `ft_idx_data`(`data`)
```
那么查询的时候可以不用like，可以使用 MATCH AGAINST代替～
```
    #原语句
    select * from table_name where data like '%cId:500%';
    #MATCH AGAINST
    SELECT * FROM table_name WHERE MATCH(data) AGAINST('cId:500' IN BOOLEAN MODE);
```

🐱使用子查询

使用子查询可以将查询结果限制到一个较小的范围内，从而减小了查询的数据量，先查询出偏移后的主键，再根据主键索引查询数据块的所有内容。

    select a.id, product_id,username, data 
    from table_name a
    join ( select id from table_name where product_id = 2 and (其他查询条件) order by id desc limit 90000, 10) b
    on a.id=b.id;

执行时间为4.26秒

🐱主键直接定位

前提条件：主键需要有序

    select id,product_id,username, data 
    from table_name 
    where id <= (select id from table_name where product_id = 2 order by id desc limit 90000, 1)
    and product_id = 2 
    order by id desc limit 10;