第七天sql优化篇

分手后的思念是犯贱 2024-03-24 16:58 30阅读 0赞

一、查询SQL尽量**不要使用select \***，而是select具体字段  
因为select \* 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询  
select stu.name from student stu;

二、如果知道查询结果只有一条或者只要最大/最小一条记录，**建议用limit 1**  
反例：select id，name from employee where name='jay'  
正例：select id，name from employee where name='jay' limit 1;  
理由：加上limit 1后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高。

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

例子：需要查询userid为1或者年龄为18岁的用户  
反例：select \* from user where userid=1 or age =18  
正例：select \* from user where userid=1 union select \* from user where age =18  
理由：如果user表中userid字段和age字段都有索引，那么使用OR操作符可能会导致索引失效，从而影响查询性能。  
为了避免索引失效，可以将OR操作符改为UNION操作符，将两个条件分别查询，然后将结果合并。

四、优化limit分页  
日常做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下。  
反例：查询员工表中从10000行开始，返回10条分页数据  
select id，name，age from employee limit 10000，10  
正例：select id，name from employee where id>10000 limit 10

五、优化你的like语句  
日常开发中，如果用到模糊关键字查询，很容易想到like，但是like很可能让你的索引失效。  
反例：select userId，name from user where userId like '%123';  
正例：select userId，name from user where userId like '123%';  
**理由：把%放前面，并不走索引，会导致索引失效**

六、使用where条件限定要查询的数据，避免返回多余的行  
假设业务场景是这样：查询某个用户是否是会员。  
反例：List<Long> userIds = sqlMap.queryList("select userId from user where isVip=1");  
boolean isVip = userIds.contains(userId);  
正例：Long userId = sqlMap.queryObject("select userId from user where userId='userId' and isVip='1' ")

boolean isVip = userId！=null;  
理由：

\- 需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销。

七、使用联合索引时，注意索引列的顺序，一般遵循**最左匹配原则。**  
反例：select \* from user where age = 10;  
正例：select \* from user where userid=10 and age =10；//符合最左匹配原则select \* from user where userid =10;  
理由：如果**不遵守最左匹配原则，会造成索引失效**，  
MySQL最左匹配原则是指在使用多列索引进行查询时，  
MySQL会优先使用最左边的列开始匹配，直到无法再匹配为止。  
这意味着在使用多列索引时，如果查询条件中没有使用索引中最左边的列，  
那么索引将不会被使用，而是会退化为**全表扫描**，导致查询性能下降。  
八、对查询进行优化，应考虑在where及order by涉及的列上建立索引，尽量避免全表扫描。  
反例：select \* from user where address ='深圳' order by age ;

正例：给address和age添加索引alter table user add index idx\_address\_age (address,age)

九、在适当的时候，添加覆盖索引  
覆盖索引能够使得你的SQL语句不需要回表，仅仅访问索引就能够得到所有需要的数据，大大提高了查询效率。  
反例：// like模糊查询，不走索引了select \* from user where userid like '%123%'

正例：//id为主键，那么为普通索引，即**覆盖索引**登场了。

select id,name from user where userid like '%123%';

十、不要有超过5个以上的表连接  
\- 连表越多，编译的时间和开销也就越大。

\- 把连接表拆开成较小的几个执行，可读性更高。

\- 如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。

十一、当在SQL语句中连接多个表时,请使用表的别名，并把别名前缀于每一列上，这样语义更加清晰。  
反例：select \* from A innerjoin B on A.deptId = B.deptId;

正例：select memeber.name,deptment.deptName from A member innerjoin B deptment on member.deptId = deptment.deptId;

十二、如果字段类型是字符串，where时一定用引号括起来，否则索引失效  
反例：select \* from user where userid =123;  
正例：select \* from user where userid ='123';  
理由：为什么第一条语句未加单引号就不走索引了呢？  
这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

十三、使用explain 分析你SQL的计划  
explain select \* from user where userid =10086 or age =18;  
如果字段type内容是ALL,代表没有走索引,如果字段type类型是ref，代表走了索引  
![1cb6cf01006944b7a7a86bdee1505c09.png][]

[1cb6cf01006944b7a7a86bdee1505c09.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/a5e1380cfd684605b683f161e80a100e.png