MySQL性能优化（2020）

拼搏现实的明天。 2022-11-21 04:13 218阅读 0赞

### 为什么需要进行MySQL的性能优化 ###

在我们的开发项目上线初期，业务量小，SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，所以很少针对SQL进行专门的优化，而随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。

### 通过什么方式判断语句是否需要优化 ###

1、慢查询记录  
2、explain执行计划  
3、相关的日记文件

### MySQL查询语句中七个查询命令特征 ###

[https://hxwwhl.blog.csdn.net/article/details/118712094][https_hxwwhl.blog.csdn.net_article_details_118712094]

如何测试已优化的SQL语句是否有效果：  
[SQL语句分析测试][SQL]

#### 1、exists、in的合理利用 ####

MySQL优化原则是小表驱动大表，小的数据集驱动大的数据集  
假设B的数据量小于A，则适合使用in

select num from A where num in(select num from B)

假设B的数据量大于A，则适合使用exists

select num from A where exists(select 1 from B where A.num=B.num)

#### 2、尽量使用union all代替union ####

如：

select * from student where id=1
    union
    select * from student where age=2

改成：

select * from student where id=1
    union all
    select * from student where age=2

解析：如果使用union，不管检索结果有没有，都会尝试进行合并，然后再输出最终结果进行排序，如果已知检索结果没有重复记录，使用union all 代替union，这样会提高效率。

#### 3、索引不宜太多，一般5个以内 ####

索引并不是越多越好，索引虽然提高了select查询的效率，但是也降低了insert插入和update更新的效率  
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。  
若太多则应考虑一些不常使用到的列上建的索引是否有必要。

#### 4、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型。 ####

解析：相对于数据型字段，字符型会降低查询和连接的性能，会增加存储开销。

#### 5、索引不适合建立在有大量重复数据的字段上，如性别类型的数据库字段 ####

因为SQL优化器是根据表中数据量进行查询优化的，如果索引列有大量重复数据，MySQL查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

#### 6、当在SQL语句中连接多个表时，请使用表的别名，并把别名前缀写清楚，这样语义更加清晰。 ####

#### 7、尽可能使用varchar和nvarchar 代替 char/nchar ####

解析：变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索，效率更高。

#### 8、优化group by语句的效率，可以在执行到该语句前，把不需要的记录过滤 ####

解析：原理就是过滤了之后再分组，可以提高效率  
如：

select job，avg（salary） from employee  group by job having job ='president' or job = 'managent';

改成：

select job，avg（salary） from employee where job ='president' or job = 'managent' group by job;

#### 9、字符串不加单引号会导致索引失效 ####

如果我们查询时使用了100而不是使用‘100’，MySQL会自动帮我们进行类型转换，使得索引失效，变为全表扫描，降低了性能。

#### 10、避免where语句进行表达式操作这会导致引擎失效 ####

如：

select * from b where num/2=100;

改为：

select * from b where num=100*2;

#### 11、避免大事物的操作，提高系统并发能力 ####

#### 12、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理 ####

解析：可以考虑分页进行返回数据  
如：

select * from LivingInfo where  watchTime >= Date_sub(now(),Interval 1 Y);

改为：  
//分页查询

select * from LivingInfo where watchTime>= Date_sub(now(),Interval 1 Y) limit offset,pageSize;

//如果是前端分页，可以先查询前两百条记录，因为一般用户应该也不会往下翻太多页，

select * from LivingInfo where watchTime>= Date_sub(now(),Interval 1 Y) limit 200;

#### 13、如果需要删除临时表的时候，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。 ####

#### 14、避免频繁创建和删除临时表，以减少系统表资源的消耗。 ####

#### 15、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 ####

#### 16、where子句中考虑使用默认值0代替null ####

如：

select * from student where age is not null;

改为：

select * from student where age > 0;

#### 17、慎用distinct关键字 ####

解析：distinct关键字一般用来过滤重复记录，已返回不重复的记录，在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果，但是在字段很多的时候使用，却会大大降低查询效率。  
如：

select distinct * from student;

改为：

select distinct name from student;

#### 18、对查询进行优化，应考虑在where及order by涉及的列上建立索引，尽量避免全表扫描。 ####

如：

explain SELECT * FROM student1 WHERE address='地址不详' ORDER BY borndate;

改为：  
//添加索引

alter table student1 add index idx_address_age (address,borndate);

//再执行

explain SELECT * FROM student1 WHERE address='地址不详' ORDER BY borndate;

#### 19、避免where子句中使用！=或<>操作符，否则引擎将放弃使用索引而进行全表扫描 ####

如：

select * from student where age <> 18;

改为：  
//可以考虑分开两条SQL写

select * from student where age <18;
    select * from student where age >18;

#### 20、inner join、left join、right join，优先使用inner join，如果是left join，左边表结果尽量小。 ####

inner join内连接，在两张表进行连接查询时，只保留两种表中完成全匹配的结果集  
left join 在两张表进行连接查询时，会返回左边表所有的行，即使在右边表中没有匹配的记录  
right join和left join相反  
都满足SQL需求的前提下，推荐优先使用inner join（内连接），如果要使用left join，左边表数据结果集尽量小，如果有条件的尽量放到左边处理。  
如：

select * from tab1 t1 left join tab2 t2 on t1.size = t2.size where t1.id>2;

改为：

select * from (select * from tab1 where id >2) t1 left join tab2 t2 on t1.size = t2.size;

解析：如果inner join是等值连接，或许返回的行数比较少，所以性能相对会好一点。  
同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少

#### 21、优化你的like语句，‘%’写在后面 ####

如：

select name,age  from student where id like '%123';

改为：

select name,age from student where id like '123%';

解析：  
解析：日常开发中，如果用到模糊关键字查询，很容易想到like，但是like很可能让你的索引失效。把%放在前面，并不走索引，把%放在后面，会走索引

#### 22、优化limit分页 ####

解析：实现分页功能的时候，我们一般会使用limit实现，但是当偏移量的别大的时候，

查询的效率就变得低下。

当偏移量最大的时候，查询效率就会越低，因为MySQL并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后在把前面偏移量这一段的数据抛弃掉再返回的。

方案一：返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。

方案二：使用order + by +索引，也是可以提高查询效率的。  
如：

select name,age from student limit 10000,10；

改为：  
//方案一：返回上次查询 的最大记录（偏移量）

select name,age from student where id>10000 limit 10;

//方案二：order by + 索引

select name,age from student order by id limit 10000,10;

#### 23、尽量避免在where子句中使用or来连接条件 ####

如：

select * from user where id =1 or age =18

改为：

select * from user where id = 1
    union all
    select * from user where age =18

解析：使用or可能会使索引失效，从而进行全表扫描，可以使用union all来解决。

#### 24、如果知道查询返回的结果只有一条或者只要最大/最小记录，建议使用limit 1. ####

如：

select name,age from student where name ='小明';

改为：

select name,age from student where name ='小明' limit 1;

#### 25、查询SQL尽量不要使用select \*，而是select具体字段,不需要返回你用不到的任何字段 ####

解析：只拿你需要的字段，节省资源、减少网络开销，select \* 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询  
如：

select * from student;

改为：

select name,age from student;

[https_hxwwhl.blog.csdn.net_article_details_118712094]: https://hxwwhl.blog.csdn.net/article/details/118712094
[SQL]: https://blog.csdn.net/qq_44739706/article/details/109379206