高性能mysql——sql查询性能优化

最新推荐文章于 2024-05-22 20:11:58 发布

For_zwb

最新推荐文章于 2024-05-22 20:11:58 发布

阅读量779

点赞数 2

分类专栏： MySQL 文章标签：性能优化 sql mysql

本文链接：https://blog.csdn.net/weixin_42250655/article/details/121294898

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

为什么要优化sql查询？

在现在的系统和业务中，经常要使用sql语句来查询数据库获取用户或者商户数据等，可能我们时常关注的是框架的性能优化而忽略了sql的查询性能。往往一个不起眼的sql语句就会造成性能上的差异。所以sql查询优化也是我们需要重点关注的。

一条查询语句的生命周期分为：客户端->服务端->服务端上解析、生成执行计划(存储引擎选择最优查询方案)、执行->将结果返回客户端。 其中执行阶段又是最重要的，包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序，分组等。所以我们一般优化这一阶段的sql语句。

三个维度判断性能

响应时间（服务时间+排队时间):所以在不同的压力下响应时间可能不同，没有一致的计算公式。
扫描的行数
返回的行数

可以用explain select xxx 看到详细的执行信息来判断。

1. 优化数据访问

查询语句最简单的方式可能就是 select* from table;
但是这种语句会将所有数据返回给我们的客户端，会造成网络带宽的消耗，并且会走全表扫描。
优化：只查询我们需要的数据，而不是用*查询所有数据，并要合理的建立表的索引，方便我们查询。
要合理的分析业务需要建立索引，而不是建立非常多不必要的索引，这样会造成资源的浪费。例如通过联合索引，覆盖索引等特性来选择最优解。

三个查询坑点
(1)不要对索引字段做函数操作，会破坏是索引值的有序性导致索引失效。
(2)隐式类型转换; 会导致索引字段转换为别的类型字段，会因为索引列字段不匹配导致索引失效。
(3)隐式字符编码转换; 索引字段的编码方式不同造成隐式转换导致索引失效。

2. 一个复杂查询还是多个简单查询？

首先要分析当前机器的网络带宽和mysql 的查询性能可以支持的最大查询数(可以不精确，但大致差不多)。
分析当前的查询语句会查找出多少行的记录，是否可以进行拆分，分为多次查询。
如果一个查询能够胜任但是被拆分为多个小事务往往是不明智的选择。
降低一条语句事务的时间和大小，上锁太多会严重影响并发。

3. mysql通信方式对查询的影响

要明确的是mysql客户端和服务端通信方式是半双工的，一端要等另一端完全发送成功消息之后才能做出响应。所以这也是为什么我们要合理的使用limit限制返回行数的原因。

4. 关联查询的优化

合理的选择驱动表来进行关联查询。尽量用小表驱动大表，并在大表中建立对应的索引来优化。

如果大表不能建立对应的索引应该如何优化？

可以在业务代码中进行优化，将select语句分为多个语句来执行，并在业务层建立对应的hash表来优化查询。
可以使用临时表来建立索引。(适用于临时表可以过滤大量数据的时候)
例如：
先创建一个临时表并建立需要关联条件的索引；
插入需要查找数据的范围；
进行关联。

create temporary table temp_t(id int primary key, a int, b int, index(b)) engine=innodb;
insert into temp_t select *from t2 where b>=1 and b <= 2000;
select* from t1 join temp_t on (t1.b=temp_t.b);

5. 排序优化

尽量避免对数据进行排序，如果使用，判读是否可以用索引来优化排序。
如果不能使用索引排序，则mysql需要自己进行排序，如果数据量小则在内存中进行，如果数据量大，则需要使用磁盘，不过mysql将这个过程统一为文件排序(filesort)。

6. 优化limit分页

当limit偏移量非常大的时候，例如limit 10000, 20。可能会导致查询10020条记录返回20条结果，前面的10000行都被抛弃掉，导致代价过高。
1.采用延迟关联
例如： select id, price from t limit 1000, 5 order by title desc; 当这个表非常大的时候，查询性能就会降低，那我们可以采用延迟关联的技术来优化。
select t.id, t.price from t inner join (select id from t order by title limit 1000, 5) as lim using(id);
这样子写，会延迟关联减少扫描的行数。
2. 采用 “书签” 的方式