MySql常用查询优化策略

发布时间：2023-07-01 11:20:36 所属栏目：MySql教程来源：

导读：本篇内容介绍了“MySql常用查询优化策略有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

本篇内容介绍了“MySql常用查询优化策略有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

查询优化
可以说，对于大多数系统来说，读多写少一定是常态，这就表示涉及到查询的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万条数据用于测试；

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

实践经验告诉我们，越往后，分页查询效率越低，这就是分页查询的问题所在，因为，当在进行分页查询时，如果执行 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，可以看到响应时间有一定的提升；

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

select * from student where id > 400000 limit 10;

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

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表走了主键索引，尽管结果集较大，还是走了索引；

针对这种场景的查询，思路如下：
让查询的字段尽量包含在主键索引或者覆盖索引中；
查询的时候尽量使用分页查询；
关于左连接（右连接）的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;
登录后复制

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

create index idx_name on tenant(tenant_name);

create index idx_account on `user`(account);

再次使用explain分析结果如下

可以看到第二行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语句进行分析

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

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

“MySql常用查询优化策略有哪些”的内容就介绍到这里了，感谢大家的阅读。

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!