实用性极强的MySQL查询优化战术

在步地上线初始一段本领后，一朝数据量上去了，或多或少会嗅觉到系统出现延长、卡顿等兴奋，出现这种问题，就需要步地员或架构师进行系统调优责任了。

其中，普遍的现实告诫标明，调优的妙技尽管有好多，但触及到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万条数据用于测试。

二、分页查询优化

分页查询是开发中每每会际遇的，有一种情况是，当分页的数目特等大的时候，查询的时候每每特等耗时，比如查询student表，使用底下的sql查询，耗时达到0.2秒。

现实告诫告诉咱们，越往后，分页查询后果越低，这等于分页查询的问题地点， 因为，当在进行分页查询时，若是履行 limit 400000,10 ，原文阿谀:https://www.yyxinfushe.cn原文阿谀:https://www.jinglingcs.cn原文阿谀:https://www.xinfuse.com此时需要 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，不错看到反映本领有一定的擢升。

三、接续查询优化

在实质的业务开发历程中，接续查询不错说遍地可见，接续查询的优化中枢念念路是，最好为接续查询的字段添加索引，这是要津。具体到不同的场景，还需要具体分析，这个跟mysql的引擎在履行优化战术的决策选拔时有一定关联。

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.接续查询接续的字段开导索引

看底下的这条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条目用于笃定若何从右表搜索行，原文阿谀:https://lnxnw.cn原文阿谀:https://vzxhtr.cn原文阿谀:https://bhwa.cn原文阿谀:https://jzycym.cn原文阿谀:https://jufanghui.cn原文阿谀:https://tjgjgg.cn 左边一定齐有，是以右边是咱们的要津点，一定需要开导索引。

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索引，得出的收尾确切齐莫得走索引，再次讲明，使用内阿谀的情况下，查询优化器将会字据我方的判断进行选拔。

四、子查询优化

子查询在日常编写业务的SQL时亦然使用特等频繁的作念法，不是说子查询不成用，而是当数据量超出一定的限制之后，子查询的性能下落是很昭彰的，对于这少量，本东说念主在日常责任中深有体会。

比如底下这条sql，由于student表数据量较大，履行起来耗时特等长，不错看到骤然了快要3秒。

select st.* from student st where st.classId in (

select id from class where id > 100

);

通过履行explain进行分析得知，内层查询 id > 100的子查询尽管用上了主键索引，然则由于收尾集太大，带入到外层查询，即作为in的条目时，查询优化器也曾走了全表扫描。

针对上头的情况，不错考虑底下的优化样貌：

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

子查询性能低效的原因

子查询时，MySQL需要为内层查询语句的查询收尾开导一个临时表 ，然后外层查询语句从临时表中查询纪录，查询杀青后，再销毁这些临时表 。这么会消耗过多的CPU和IO资源，产生普遍的慢查询； 子查询收尾集存储的临时表，不管是内存临时表也曾磁盘临时表齐不成走索引，是以查询性能会受到一定的影响； 对于复返收尾集相比大的子查询，其对查询性能的影响也就越大。

使用mysql查询时，不错使用阿谀（JOIN）查询来替代子查询。阿谀查询不需要开导临时表 ，其速率比子查询要快 ，若是查询中使用索引的话，性能就会更好，原文阿谀:https://jmbpc.cn原文阿谀:https://syjsxz.cn原文阿谀:https://jhyunda.cn原文阿谀:https://nidiss.cn原文阿谀:https://qtmfscs.cn尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代。

一个着实的案例

鄙人面的这段sql中，优化前使用的是子查询，在一次坐褥问题的性能分析中，发现某个tenant_id下的数据达到了35万多，这么成功导致某个列表页面的接口查询耗时达到了5秒把握。

找到了问题的根源后，尝试使用上头的优化念念路进行处分即可，优化后的sql能够如下：

五、排序（order by）优化

在MySQL，排序主要有两种样貌：

Using filesort : 通过表索引或全表扫描，读取餍足条目的数据行，然后在排序缓冲区sort buffer中完成排序操作，系数不是通过索引成功复返排序收尾的排序齐叫 FileSort 排序； Using index : 通过有序的索引限定扫描成功复返有序数据，这种情况即为 using index，不需要很是排序，操作后果高。

对于以上两种排序样貌，Using index的性能高，而Using filesort的性能低，咱们在优化排序操作时，尽量要优化为 Using index。

1.使用age字段进行排序

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

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

2.使用多字段进行排序

庸碌在实质业务中，参与排序的字段每每不单一个，这时候，就不错对参与排序的多个字段创建聚拢索引。

如下字据stuno和age排序：

给stuno和age添加聚拢索引：

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

再次分析时收尾如下，此时排序走了索引：

1）对于多字段排序时的防御事项

排序时，需要餍足最左前缀法例，不然也会出现 filesort

在上头咱们创建的聚拢索引限定是stuno和age，即stuno在前边，而age在后，若是查询的时候疏通排序限定会若何呢？通过分析收尾发现，走了filesort。

排序时，排序的类型保合手一致

在保合手字段排序限定不变时，默许情况下，若是齐按照升序或者降序时，order by不错使用index，若是一个是升序，另一个是降序会若何呢？分析发现，这种情况下也会走filesort。

六、分组（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会很慢；

1.给group by的字段添加索引

若是字段未加索引，分析收尾如下，这种收尾性能昭彰很低效。

给stuno添加索引之后：

给stuno和age添加聚拢索引：

若是不盲从最好左前缀，group by 性能将会相比低效。

盲从最好左前缀的情况如下：

七、count 优化

count() 是一个团聚函数，对于复返的收尾集，一转行判断，若是 count 函数的参数不是NULL，累计值就加 1，不然不加，临了复返累计值。

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

如下列举了count的几种写法的详备讲明：

告诫值回首：

按照后果排序来看，count(字段) < count(主键 id) < count(1) ≈ count(*)，是以尽量使用 count(*)

分页字段counthttpssql发布于：河南省声明：该文不雅点仅代表作家本东说念主，搜狐号系信息发布平台，搜狐仅提供信息存储空间管事。