为了减少延迟和卡顿，我对 MySQL 查询做了这些优化处理....

桃扇骨 2024-03-16 16:02 2阅读 0赞

### 前言 ###

在程序上线运行一段时间后，一旦数据量上去了，或多或少会感觉到系统出现延迟、卡顿等现象，出现这种问题，就需要程序员或架构师进行系统调优工作了。

其中，大量的实践经验表明，调优的手段尽管有很多，但涉及到SQL调优的内容仍然是非常重要的一环，本文将结合实例，总结一些工作中可能涉及到的SQL优化策略；

### 查询优化 ###

可以说，对于大多数系统来说，读多写少一定是常态，这就表示涉及到查询的SQL是非常高频的操作；

> 前置准备，给一张测试表添加10万条数据

使用下面的存储过程给单表造一批数据，将表换成自己的就好了

create procedure addMyData()
     
     begin
     
      declare num int;
      set num =1;
      
      while num <= 100000 do
      
       insert into XXX_table values(
        replace(uuid(),'-',''),concat('测试',num),concat('cs',num),'123456'
       );
     
       set num =num +1;
      end while;
     
     end ;

然后调用该存储过程

call addMyData();

本篇准备了3张表，分别为学生（student）表，班级（class）表，账户(account)表，各自有50万，1万和10万条数据用于测试；

![4b5b0474662126a3d91c68210d9238f7.png][]

![652ded63919563b8b411e8ddb435dbc8.png][]

#### 1、分页查询优化 ####

分页查询是开发中经常会遇到的，有一种情况是，当分页的数量非常大的时候，查询的时候往往非常耗时，比如查询student表，使用下面的sql查询，耗时达到0.2秒；

![f6caf47864c9302aa065cbef8c709ddb.png][]

实践经验告诉我们，越往后，分页查询效率越低，这就是分页查询的问题所在， 因为，当在进行分页查询时，如果执行 `limit 400000,10` ，此时需要 MySQL 排序前4000 10 记 录，仅仅返回`400000 - 4 00010` 的记录，其他记录丢弃，查询排序的代价非常大

优化思路：

一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化；

**1） 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容**

SELECT * FROM student t1,(SELECT id FROM student ORDER BY id LIMIT 400000,10) t2 WHERE t1.id =t2.id;

执行上面的sql，可以看到响应时间有一定的提升；

![9b5729aa14d9c689a215059bfe95b4af.png][]

**2）对于主键自增的表，可以把Limit 查询转换成某个位置的查询**

select * from student where id > 400000 limit 10;

执行上面的sql，可以看到响应时间有一定的提升；

![87af971ea57c99a4e0017912aaa9a9c7.png][]

#### 2、关联查询优化 ####

在实际的业务开发过程中，关联查询可以说随处可见，关联查询的优化核心思路是，最好为关联查询的字段添加索引，这是关键，具体到不同的场景，还需要具体分析，这个跟mysql的引擎在执行优化策略的方案选择时有一定关系；

2.1 左连接或右连接

下面是一个使用left join 的查询，可以预想到这条sql查询的结果集非常大

select t.* from student t left join class cs on t.classId = cs.id;

为了检查下sql的执行效率，使用explain做一下分析，可以看到，第一张表即left join左边的表student走了全表扫描，而class表走了主键索引，尽管结果集较大，还是走了索引；

![10df8d38c331fd84612fbb1fc41cd21f.png][]

**针对这种场景的查询，思路如下：**

*  让查询的字段尽量包含在主键索引或者覆盖索引中；
 *  查询的时候尽量使用分页查询；

![71d4e0f4069e870207f551e675f49d46.png][]

**关于左连接（右连接）的explain结果补充说明**

*  左连接左边的表一般为驱动表，右边的表为被驱动表；
 *  尽可能让数据集小的表作为驱动表，减少mysql内部循环的次数；
 *  两表关联时，explain结果展示中，第一栏一般为驱动表；

2.2 关联查询关联的字段建立索引

看下面的这条sql，其关联字段非表的主键，而是普通的字段；

explain select u.* from tenant t left join `user` u on u.account = t.tenant_name where t.removed is null and u.removed is null;

![8d802c8a380570ace3e362ae241d7796.png][]

通过explain分析可以发现，左边的表走了全表扫描，可以考虑给左边的表的`tenant_name`和`user`表的\`\` 各自创建索引；

create index idx_name on tenant(tenant_name);
    
    create index idx_account on `user`(account);

再次使用explain分析结果如下

![47a2f386507e329309ae2f6993e7a612.png][]

可以看到第二行type变为ref，rows的数量优化比较明显。这是由左连接特性决定的，`LEFT JOIN`条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点,一定需要建立索引 。

2.3 内连接关联的字段建立索引

我们知道，左连接和右连接查询的数据分别是完全包含左表数据，完全包含右表数据，而内连接（`inner join` 或join） 则是取交集（共有的部分），在这种情况下，驱动表的选择是由mysql优化器自动选择的；

在上面的基础上，首先移除两张表的索引

ALTER TABLE `user` DROP INDEX idx_account;
    ALTER TABLE `tenant` DROP INDEX idx_name

使用explain语句进行分析

![7a8cdcda944887e728ba75fd6deb5bd6.png][]

然后给user表的account字段添加索引，再次执行explain我们发现，user表竟然被当作是被驱动表了；

![c2bbc840ca65944124fbe115d9d5ffe0.png][]

此时，如果我们给tenant表的`tenant_name`加索引，并移除user表的account索引，得出的结果竟然都没有走索引，再次说明，使用内连接的情况下，查询优化器将会根据自己的判断进行选择；

![e153ca6ca0ca60e7e342430c7aeb2e39.png][]

#### 3、子查询优化 ####

子查询在日常编写业务的SQL时也是使用非常频繁的做法，不是说子查询不能用，而是当数据量超出一定的范围之后，子查询的性能下降是很明显的，关于这一点，本人在日常工作中深有体会；

比如下面这条sql，由于student表数据量较大，执行起来耗时非常长，可以看到耗费了将近3秒；

select st.* from student st where st.classId in (
     
     select id from class where id > 100
     
    );

![0048da6f6b6e8ac43c1e18cfd8b9fe0d.png][]

通过执行explain进行分析得知，内层查询 id > 100的子查询尽管用上了主键索引，但是由于结果集太大，带入到外层查询，即作为in的条件时，查询优化器还是走了全表扫描；

![79f74b6ac13ecd1d4f014c6833ed5fe9.png][]

针对上面的情况，可以考虑下面的优化方式

select st.id from student st join class cl on st.classId = cl.id where cl.id > 100;

**子查询性能低效的原因**

*  子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录，查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询；
 *  子查询结果集存储的临时表，不论是内存临时表还是磁盘临时表都不能走索引 ，所以查询性能会受到一定的影响；
 *  对于返回结果集比较大的子查询，其对查询性能的影响也就越大；

> 使用mysql查询时，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表 ，其速度比子查询要快 ，如果查询中使用索引的话，性能就会更好，尽量不要使用`NOT IN` 或者 `NOT EXISTS`，用`LEFT JOIN xxx ON xx WHERE xx IS NULL`替代；

一个真实的案例

在下面的这段sql中，优化前使用的是子查询，在一次生产问题的性能分析中，发现某个`tenant_id`下的数据达到了35万多，这样直接导致某个列表页面的接口查询耗时达到了5秒左右；

![6fa95621864aacda03855aac56d59982.png][]

找到了问题的根源后，尝试使用上面的优化思路进行解决即可，优化后的sql大概如下，

![49ebcf8556f01569724f23ff44c59d60.png][]

#### 4、排序（order by）优化 ####

在mysql，排序主要有两种方式

*  `Using filesort` : 通过表索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort。buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序；
 *  `Using index`: 通过有序的索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高；

对于以上两种排序方式，`Using index`的性能高，而`Using filesort`的性能低，我们在优化排序操作时，尽量要优化为 `Using index`

4.1 使用age字段进行排序

由于age字段未加索引，查询结果按照age排序的时候发现使用了filesort，排序性能较低；

![bd0673309587990db54062a501e13cce.png][]

给age字段添加索引，再次使用order by时就走了索引；

![cd074b77914c54d371d3cd1ebcc1914d.png][]

4.2 使用多字段进行排序

通常在实际业务中，参与排序的字段往往不只一个，这时候，就可以对参与排序的多个字段创建联合索引；

如下根据stuno和age排序

![ba7fae526395a501985ea07b9ebbba6c.png][]

给stuno和age添加联合索引

create index idx_stuno_age on `student`(stuno,age);

再次分析时结果如下，此时排序走了索引

![a703f02cc7ee665dfb56c326ea3f39fb.png][]

关于多字段排序时的注意事项

**1）排序时，需要满足最左前缀法则,否则也会出现 filesort；**

在上面我们创建的联合索引顺序是stuno和age，即stuno在前面，而age在后，如果查询的时候调换排序顺序会怎样呢？通过分析结果发现，走了filesort；

![05e0790fa634c20f065687dc68fce3cd.png][]

**2）排序时，排序的类型保持一致**

在保持字段排序顺序不变时，默认情况下，如果都按照升序或者降序时，`order by可`以使用index，如果一个是升序，另一个是降序会如何呢？分析发现，这种情况下也会走filesort；

![e95ee859d01526090869e9516e8cf1e9.png][]

#### 5、分组（group by）优化 ####

`group by` 的优化策略和`order by` 的优化策略非常像，主要列举如下几个要点：

*  `group by` 即使没有过滤条件用到索引，也可以直接使用索引；
 *  `group by` 先排序再分组，遵照索引建的最佳左前缀法则；
 *  当无法使用索引列时，增大 `max_length_for_sort_data` 和 `sort_buffer_size` 参数的设置；
 *  where效率高于having，能写在where限定的条件就不要写在having中了；
 *  减少使用order by，能不排序就不排序，或将排序放到程序去做。`Order by`、`groupby`、`distinct`这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的；
 *  如果sql包含了`order by`、`group by`、`distinct`这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢；

5.1 给group by的字段添加索引

如果字段未加索引，分析结果如下，这种结果性能显然很低效

![1095e174e3ff7aec8b6ad93b06860b58.png][]

给stuno添加索引之后

![51de561ae2794cfcf456afe5b7732c8f.png][]

给stuno和age添加联合索引

![c6fc0a744113d3484ad1194b23608e8b.png][]

如果不遵循最佳左前缀，group by 性能将会比较低效

![9923b96a09f6ccc31a1f957e2b5976d7.png][]

遵循最佳左前缀的情况如下

![cffcff8a9ec9d27cd3b4aaee9ae8dcb2.png][]

#### 6、count 优化 ####

`count()` 是一个聚合函数，对于返回的结果集，一行行判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值；

> 用法：`count（*）`、`count（主键）`、`count（字段）`、`count（数字）`

如下列举了count的几种写法的详细说明

![af7ef9953b3eca3ff978d7bb02fcaadf.png][]

**经验值总结**

按照效率排序来看，`count(字段) < count(主键 id) < count(1) ≈ count(*)`，所以尽量使用 `count(*)`

[4b5b0474662126a3d91c68210d9238f7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/e232ef07a1384ac9aa70b6b458dd4fbf.png
[652ded63919563b8b411e8ddb435dbc8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/c1a35d6b8e7a4195b41354254eda9850.png
[f6caf47864c9302aa065cbef8c709ddb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/41265753675144afa333aaf025796663.png
[9b5729aa14d9c689a215059bfe95b4af.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/a5409c2a428a4033a4d175c8662be541.png
[87af971ea57c99a4e0017912aaa9a9c7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/9d2dd54122ed44a5b76122bcfc60cdb5.png
[10df8d38c331fd84612fbb1fc41cd21f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/2fff18c3c4c34b1e89163e3c88e95a49.png
[71d4e0f4069e870207f551e675f49d46.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/2086046f4afa43d6bb615f40b940ee82.png
[8d802c8a380570ace3e362ae241d7796.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/bb6cf57243464b539ac2e619397ddb16.png
[47a2f386507e329309ae2f6993e7a612.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/9604dba5c4124cb2b46f6731f602a3b9.png
[7a8cdcda944887e728ba75fd6deb5bd6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/347e46c734224ed691f4109a4af80aaa.png
[c2bbc840ca65944124fbe115d9d5ffe0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/6edada9bef39416c85d640699d34a564.png
[e153ca6ca0ca60e7e342430c7aeb2e39.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/0865227975c54e62a191923743f6f549.png
[0048da6f6b6e8ac43c1e18cfd8b9fe0d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/90c1f9dad0b842ca8b5a410943385289.png
[79f74b6ac13ecd1d4f014c6833ed5fe9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/d9e27fabd6d5486d86db3af7c7b6720e.png
[6fa95621864aacda03855aac56d59982.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/0d8022ab47694d9a8ceb285bb115e877.png
[49ebcf8556f01569724f23ff44c59d60.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/c7ed4e631ad34a5989c07bdc7ba9305c.png
[bd0673309587990db54062a501e13cce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/606c3749288a43e59d504667b432a07b.png
[cd074b77914c54d371d3cd1ebcc1914d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/f35a3839bdd34279acfecfea808bccfb.png
[ba7fae526395a501985ea07b9ebbba6c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/6a4b005c472841a7a0d943e00b610be4.png
[a703f02cc7ee665dfb56c326ea3f39fb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/a3ef128370484ff9bbb45164ce0b8d8d.png
[05e0790fa634c20f065687dc68fce3cd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/6c58ea4a31c648d18d38cb8199468185.png
[e95ee859d01526090869e9516e8cf1e9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/9272b0e925b64b3f900d9ca4453f918f.png
[1095e174e3ff7aec8b6ad93b06860b58.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/1da93f752c0e483f91039c6add275ecc.png
[51de561ae2794cfcf456afe5b7732c8f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/d5fc5a897595417e8e31a27fbba34cac.png
[c6fc0a744113d3484ad1194b23608e8b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/b13b7b561b15457392b8b43d08b165f5.png
[9923b96a09f6ccc31a1f957e2b5976d7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/b416357a2fe742f284111dec2b8e8f64.png
[cffcff8a9ec9d27cd3b4aaee9ae8dcb2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/c2eb765f9736487bacb276d62aac866e.png
[af7ef9953b3eca3ff978d7bb02fcaadf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/ba650c9e8ace4d83b07f4ef4abbbf117.png