Mysql篇——SQL优化

本篇将带领各位了解一些常见的sql优化方法，学到就是赚到，一起跟着练习吧~

SQL优化

准备工作

准备的话我们肯定是需要一张表的，什么表都可以，这里先给出我的表结构（表名：userinfo）

通过sql查看我们的表里面总共有2百万条数据，当然我这儿的数据量不是很多，但是练习用也够了~

这些准备好之后我们就可以正式进行练习阶段了~

练习

场景1：查出数据库里面所有姓王的且性别是女的数据，sql语句这么写：

select * from userinfo where username like "王%" and gender="女";

查询结果耗时2s多，这还是200w条数据的情况下，显然这个速率是不行的，所以先加个索引试试：

create index idx_name_gender on userinfo(username,gender)；

再查询发现整个运行时间直接降低到了0.6s左右 ，后续查询直接稳定到0.3s左右，这相对第一次查询已经有了很大的提升

从执行计划中我们也可以看到走了索引而且使用索引下推加快查询：

当然，你也可以通过开启缓存，下次这条sql语句就会直接从缓存里面读取数据，速度应该会更快一些，当然我这儿没有做实验，感兴趣的可以去试试~

场景2：分页查询，每页20条数据，查询第10000页的数据，并按照id进行升序排列

sql语句可以这么写：

select * from userinfo order by id ASC LIMIT 199980,20;

查询耗时稳定都按差不多在0.4s左右，这里就不截图了，你可以自己试一下，当然我这是在数据量不多的情况下的查询结果，如果数据量过大的话那么查询分页耗时一定会比这个大的多。

这里拓展一点知识：

mysql查询这条sql语句的时候，不止会查询当前页码的数据，它是从头开始查，比如你的limit是这样的-》limit n,m  那么mysq就会查询n+m条数据，然后舍弃掉前n条数据，这显然查询前n条数据是没有必要且浪费时间的，也会为什么查询速度慢的原因，读到这儿你应该有点儿印象，没错这就是mysql里面的深分页问题。

解决办法可以通过子查询或者寻找那一页的最小的id通过大于判断查询，比如说：

子查询：

select * from userinfo where id in (select id from userinfo LIMIT 199980,20) order by id ASC;

这条sql语句我这个版本的运行不了，你可以先试试~（手动狗头）

我们都知道，sql慢了考虑加索引，但是如果sql设计不当也会导致索引失效的情况，这里给你列出一些常见的索引失效的情况，借鉴借鉴~

索引失效

•  索引字段使用函数
• 索引字段使用or连接且or的后面添加了>或者<
• 索引使用了!=（这个不是绝对的，还得看执行计划）
• 未遵循最左匹配原则
• 使用了IS NULL或者IS NOT NULL
• 隐式字段转换（例外：如果字段num类型为int，查询num='1'不会导致索引失效，因为mysql会将参数转化为int类型）
• 使用了order by（可能走索引，可能使用filesort，数据量小的时候直接走内存查数据了）
• 使用了in（可能走索引，数据量小的话走索引优化）

.........

最后，既然是优化那肯定是出现了慢sql，那慢sql出现的可能原因我们得提前知道然后尽量杜绝：

慢sql

• 表设计不合理
• 表数据量过大
• 索引设计区分度不高
• 索引失效
• 多表join
• 回表次数太多

.........

当然，还有其他情况这里没有列出来的，各位可以在评论区进行补充，我们共同学习~