MySQL | 使用 limit 优化查询和防止SQL被优化

桃扇骨 2022-10-30 09:23 28阅读 0赞

--------------------

### 文章目录 ###

*  查询优化
 *   *  1.1 最大值和最小值的优化
     *  1.2 优化 limit 分页
     *   *  1.2.1 使用关联查询优化
         *  1.2.2 使用范围查询
         *  1.2.3 利用唯一自增序列进行查询
 *  防止被优化
 *  参考

--------------------

# 查询优化 #

## 1.1 最大值和最小值的优化 ##

对于 `MIN()` 和 `MAX()` 查询，`MySQL` 的优化做的并不是太好，例如

select MIN(id) FROM film where name = '西游记'；

假设表 `film` 数据如下：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>price</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>英雄本色</td> 
   <td>12</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>哪吒传奇</td> 
   <td>14</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>西游记</td> 
   <td>34</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>水浒传</td> 
   <td>23</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>红楼梦</td> 
   <td>34</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>红与黑</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>红与黑</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>美人鱼</td> 
   <td>23</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>爸爸归来</td> 
   <td>23</td> 
  </tr> 
  <tr> 
   <td>10</td> 
   <td>我是谁</td> 
   <td>12</td> 
  </tr> 
  <tr> 
   <td>11</td> 
   <td>喜羊羊</td> 
   <td>56</td> 
  </tr> 
  <tr> 
   <td>12</td> 
   <td>西游记</td> 
   <td>67</td> 
  </tr> 
 </tbody> 
</table>

> 其中 `id` 为主键并自增，`name` 为 `varchar` 且没有索引

因为 `name` 没有索引，因为 `MySQL` 将会进行一次全表扫描。因为 `id` 为自增，那么我们可以当作，第一次找到 `name='西游记'`时，`id` 就为我们想要的结果，此时我们可以改写 `SQL` 为：

select id FROM film where name = '西游记' limit 1；

此时当查到第一条记录时，就会停止继续查询，获得更高的性能。

## 1.2 优化 limit 分页 ##

在系统进行分页操作的时候，当偏移量大时，例如：`limit 10000,20` 时，`MySQL` 需要查询 `10020` 条记录然后只返回 `20` 记录，前面的记录全部被舍弃，这样的代价非常高

SELECT id, name, price FROM file LIMIT 10000 OFFSET 20

上面的 `SQL` 我想是分页常规的写法，写法没有什么错误，正如上面说到，浪费了大量的性能。

### 1.2.1 使用关联查询优化 ###

优化此类查询一个简单的方法就是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。对于偏移大的时候，这样做的效率提升非常大。

SELECT
        id, name, price
    FROM film
    INNER JOIN (
        SELECT id
        FROM film
        LIMIT 10000 OFFSET 20
        ) AS LIM USING(id)

### 1.2.2 使用范围查询 ###

有时候可以将 `LIMIT` 转化为已知位置的查询，让 `MySQL` 通过范围扫描获得到对应的结果。例如，如果在一个位置列上有索引，并且预先计算出了边界值，则改写查询为：

SELECT id, name, price
    FROM film
    WHERE position BETWEEN 10000 AND 10020
    ORDER BY position

### 1.2.3 利用唯一自增序列进行查询 ###

这里的唯一自增序列可以是自增 id 主键，也可以其他的具有唯一和升序的数字即可

在前面的思路中，我们考虑的都是传入页数和每页数量，在一些操作中可以改为传入上一次查询到的自增序列，然后往后查询对应的每页数量即可。

例如原来要求前端传入页数（pageIndex）和 每页数量（pageSize）, 此时的 SQL 为

select * from film 
    limit (pageIndex -1) * pageSize OFFSET pageIndex * pageSize

如果改为让前端传入最后一次查询到结果的 自增序列（sid） 和 每页数量（pageSize）

比如这时的自增序列（sid） 就是 `film` 的 id, 则 SQL 可以改写成

select * from film 
    where id > sid
    limit pageSize

当查第一页的时候，sid 传入 0 即可，查第二页的时候，传入获取第一页时最后得到 id 即可