关于SQL优化13连问，赶紧进收藏吧。

ゝ一纸荒年。 2024-03-23 14:54 36阅读 0赞

### 前言 ###

大家好，我是田螺。

**金三银四**已经开始啦，准备了**SQL优化的13连问**，内容非常干！相信大家看完一定会有帮助的。

![format_png][]

### 1.日常工作中，你是怎么优化SQL的？ ###

大家可以从这几个维度回答这个问题：

*  分析慢查询日志
 *  使用explain查看执行计划
 *  索引优化
 *  深分页优化
 *  避免全表扫描
 *  避免返回不必要的数据（如`select`具体字段而不是`select*`）
 *  使用合适的数据类型（如可以使用`int`类型的话，就不要设计为`varchar`）
 *  优化sql结构（如`join`优化等等）
 *  适当分批量进行 (如批量更新、删除)
 *  定期清理无用的数据
 *  适当分库分表
 *  读写分离

### 2. 是否遇到过深分页问题，如何解决 ###

我们可以通过减少回表次数来优化。一般有**标签记录法**和**延迟关联法**。

**标签记录法**

> 就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。

假设上一次记录到100000，则SQL可以修改为：

select  id,name,balance FROM account where id > 100000 limit 10;
    复制代码

这样的话，后面无论翻多少页，性能都会不错的，因为命中了`id`索引。但是这种方式有局限性：需要一种类似连续自增的字段。

**延迟关联法**

延迟关联法，就是把条件转移到主键索引树，然后减少回表。 假设原生SQL是这样的的，其中`id`是主键，`create_time`是普通索引

select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;
    复制代码

使用延迟关联法优化，如下：

select  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN 
    (SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) 
    AS acct2 on acct1.id= acct2.id;
    复制代码

优化思路就是，先通过`idx_create_time`二级索引树查询到满足条件的`主键ID`，再与原表通过`主键ID`内连接，这样后面直接走了主键索引了，同时也减少了回表。

### 3. 聊聊explain执行计划 ###

当`explain`与`SQL`一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。即`MySQL`解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表等信息。

一条简单SQL，使用了`explain`的效果如下：

![format_png 1][]

一般来说，我们需要重点关注`type、rows、filtered、extra、key`。

#### 3.1 type ####

type表示**连接类型**，查看索引执行情况的一个重要指标。以下性能从好到坏依次：`system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL`

*  system：这种类型要求数据库表中只有一条数据，是`const`类型的一个特例，一般情况下是不会出现的。
 *  const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。
 *  eq\_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
 *  ref : 常用于非主键和唯一索引扫描。
 *  ref\_or\_null：这种连接类型类似于`ref`，区别在于`MySQL`会额外搜索包含`NULL`值的行
 *  index\_merge：使用了索引合并优化方法，查询使用了两个以上的索引。

[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/9e85a224d5964996a7ffa9bbe884a868.png
[format_png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/4fdb9b6de0154cccaf5ec53588054d1b.png