SQL性能优化的小技巧

- 日理万妓 2024-04-03 10:19 88阅读 0赞

一、查询SQL尽量不要使用select \*，而是具体字段  
1、反例

SELECT \* FROM user  
1  
2、正例

SELECT id,username,tel FROM user  
1  
3、理由

节省资源、减少网络开销。  
可能用到覆盖索引，减少回表，提高查询效率。  
注意：为节省时间，下面的样例字段都用\*代替了。

二、避免在where子句中使用 or 来连接条件  
1、反例

SELECT \* FROM user WHERE id=1 OR salary=5000  
1  
2、正例

（1）使用union all

SELECT \* FROM user WHERE id=1  
UNION ALL  
SELECT \* FROM user WHERE salary=5000  
1  
2  
3  
（2）分开两条sql写

SELECT \* FROM user WHERE id=1

SELECT \* FROM user WHERE salary=5000  
1  
2  
3  
3、理由

使用or可能会使索引失效，从而全表扫描；  
对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描；  
也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定；  
虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的；  
三、尽量使用数值替代字符串类型  
1、正例

主键（id）：primary key优先使用数值类型int，tinyint  
性别（sex）：0代表女，1代表男；数据库没有布尔类型，mysql推荐使用tinyint  
2、理由

因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；  
而对于数字型而言只需要比较一次就够了；  
字符会降低查询和连接的性能，并会增加存储开销；  
四、使用varchar代替char  
1、反例

\`address\` char(100) DEFAULT NULL COMMENT '地址'  
1  
2、正例

\`address\` varchar(100) DEFAULT NULL COMMENT '地址'  
1  
3、理由

varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；  
char按声明大小存储，不足补空格；  
其次对于查询来说，在一个相对较小的字段内搜索，效率更高；  
五、技术延伸，char与varchar2的区别？  
1、char的长度是固定的，而varchar2的长度是可以变化的。

比如，存储字符串“101”，对于char(10)，表示你存储的字符将占10个字节（包括7个空字符），在数据库中它是以空格占位的，而同样的varchar2(10)则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。

2、char的效率比varchar2的效率稍高。

3、何时用char，何时用varchar2?

char和varchar2是一对矛盾的统一体，两者是互补的关系，varchar2比char节省空间，在效率上比char会稍微差一点，既想获取效率，就必须牺牲一点空间，这就是我们在数据库设计上常说的“以空间换效率”。

varchar2虽然比char节省空间，但是假如一个varchar2列经常被修改，而且每次被修改的数据的长度不同，这会引起“行迁移”现象，而这造成多余的I/O，是数据库设计中要尽力避免的，这种情况下用char代替varchar2会更好一些。char中还会自动补齐空格，因为你insert到一个char字段自动补充了空格的,但是select后空格没有删除，因此char类型查询的时候一定要记得使用trim，这是写本文章的原因。

如果开发人员细化使用rpad()技巧将绑定变量转换为某种能与char字段相比较的类型（当然，与截断trim数据库列相比，填充绑定变量的做法更好一些，因为对列应用函数trim很容易导致无法使用该列上现有的索引），可能必须考虑到经过一段时间后列长度的变化。如果字段的大小有变化，应用就会受到影响，因为它必须修改字段宽度。

正是因为以上原因，定宽的存储空间可能导致表和相关索引比平常大出许多，还伴随着绑定变量问题，所以无论什么场合都要避免使用char类型。

六、where中使用默认值代替null  
1、反例

SELECT \* FROM user WHERE age IS NOT NULL  
1  
2、正例

SELECT \* FROM user WHERE age>0  
1  
3、理由

并不是说使用了is null或者 is not null就会不走索引了，这个跟mysql版本以及查询成本都有关；  
如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效；  
其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；  
如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点；  
七、避免在where子句中使用!=或<>操作符  
1、反例

SELECT \* FROM user WHERE salary!=5000

SELECT \* FROM user WHERE salary<>5000  
1  
2  
3  
2、理由

使用!=和<>很可能会让索引失效  
应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描  
实现业务优先，实在没办法，就只能使用，并不是不能使用  
八、inner join 、left join、right join，优先使用inner join  
三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小。

inner join 内连接，只保留两张表中完全匹配的结果集；  
left join会返回左表所有的行，即使在右表中没有匹配的记录；  
right join会返回右表所有的行，即使在左表中没有匹配的记录；  
为什么？

如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点；  
使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；  
这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；

[尚学堂给同学们带来全新的Java300集课程啦!java零基础小白自学Java必备优质教程\_手把手图解学习Java，让学习成为一种享受\_哔哩哔哩\_bilibili][Java300_java_Java_Java_bilibili]

[Java300_java_Java_Java_bilibili]: https://www.bilibili.com/video/BV1qL411u7eE/?spm_id_from=333.337.search-card.all.click