学习笔记一常用的sql优化技巧

说梦话能治吗

已于 2024-04-03 16:55:52 修改

阅读量463

点赞数

文章标签： sql 学习数据库

于 2022-08-28 18:36:44 首次发布

本文链接：https://blog.csdn.net/qq_44490656/article/details/126477124

版权

前言

本文章只做个人学习使用，分享给大家，有什么不对的地方，欢迎批评指正！

sql优化，是个老生常谈的话题，也是面试中高频话题，个人认为sql优化围绕的点就是避免全表扫描，使我们的sql语句拥有更快的执行速度。
下面是我从几个方面进行sql优化的整理

1.1 避免使用select *

在开发中，应该尽量做到用什么字段，取什么字段，也就是避免使用select * ,使用selecct * 会有一下几个问题：
1.增加了数据库开销，查出来的数据越多，数据传输用的时间也会相应增多
2.使用select * 不会走索引覆盖（什么是索引覆盖，后面会讲）会出现大量的回表操作

1.2用union all 代替union

说到这个就要说一说，这两个关键字有什么区别了，首先这两个都是用来联表的，这里的联表是指将多条sql语句的结果连接到一起，组成一个结果输出，不同之处在于union all 是将所有结果连接到一起，不管连接的结果里面有没有相同的，反之union 就会对结果进行去重，去重这个操作肯定就会消耗更多的时间

1.3 用小表驱动大表

这个意思就是说用数据多的表作为数据少的表的条件查询
这个一般有两个关键字，一个是in 还有一个是exists
那么这两个关键字有什么区别呢，主要区别为查询返回值
in 如果有子查询，则先查询in里面的，此时返回的是两个表的交集
exists 如果语句中有这个关键字同in相同，也是先执行exists中的查询不同的是exists中的结果是作为一个条件存在
例如：有一个用户表 user，100条数据一个订单表 order 10000条数据
如果想查询有效用户的订单则可以这样（包含in关键字，则先执行in里面的查询）
select * from order where user_id in (select user_id from user where user_statu ==1)
还可以这样写（包含exists关键字，先执行主查询，然后作为条件区匹配后面的查询）
select * from order where exists (select 1 from user where order.user_id = user.id and status=1)

1.4 批量插入

批量插入这种一般大致分为两种操作，
第一种：程序里面写循环，进行一条一条的插入（不推荐）
第二中：将需要插入的数据整体传入，在xml文件中进行循环（推荐）
因为在程序里面循环进行一条一条的插入会频繁的连接数据库，造成不必要的开销
当然第二种插入的话也要注意数据量，一般大于500就要分批处理了

1.5多用limit关键字

在很多时候，我们需要查询一类数据的第一条或者最后一条，这时候就可以对数据进行limit 1 只取第一条。前面说select *的时候说过，程序跟数据库交互，返回的数据结果过大，也会影响响应速度

1.6 in中的值太多

在开发中经常会遇到根据一些值，查询对应的数据，如果不对数据做一些数量限制，很可能会因为数据量过大造成超时等一些其他错误，这个跟前面的小表驱动大表有相似之处，如果in中的值太多，也会影响响应速度，要把握一个度，可以利用limit 关键字对数据做限制，也要在代码中做数据限制，判断数据量大小，对查询的数据量做限制，从而提升响应速度。

1.7 增量查询

有时候，我们需要通过远程接口查询数据，然后同步到另外一个数据库。
直接用 select* from tableName 虽然很方便，但是当数据量大以后，就会出现问题。
此时可以对数据进行排序，然后分批进行查询，记录每次查询的数据的某个排序的值，作为下次查询的条件。

1.8 高效分页

在平时开发中我们做分页大部分都是使用 limit 1,20 这种方式进行分页，但是当数据量特别大的时候，
比如 limit 1000000,20 这个查询效率就会非常的低。那么这种要怎么优化呢，
首先可以用上次查询的最后一条数据的id 或者其他有序的一个值作为查询条件，在进行limit 关键字查询
但是这个有个缺点就是作为条件的字段需要是有序的。
还有另外一个方式就是使用between and关键字进行分页例如limit 1000000,20 就可以写长between 1000000 and 1000020 这个也有个缺点，因为是范围查询，所以between的字段需要是唯一索引，不然会照成每页大小不一致的问题。

1.9 用连接查询代替子查询

在开发中通常需要查询多张表的数据，如果数据量不大的话，使用in 关键字，将一个表中的数据结果作为另一个表的条件，这种写法比较直观，易读。但是就跟前面提到的in关键字一样，如果in中的数据过多，就会出现问题，还有就是子查询会创建临时表，在使用完以后，再销毁这个表，这种额外的操作肯定是要消耗一些性能的。可以将子查询改为连接查询。

1.10 join的表不宜过多

这个是阿里巴巴开发手册规定的，主要原因是因为join的表过多。mysql选择索引的时候会变得复杂，很容易出错。当然如果能确定走正确的索引，这个也不算是个大问题。可以利用explan关键字查询执行细节，来保证索引的正确性。另外如果能预见这种问题，可以给表增加一定的冗余来减少join表数量

1.11 join时要注意

使用join 时，用的最多的是inner join 和left join
inner join 是取两个表的交集，这个mysql再执行的是会自动选择小表去驱动大表
left join 是取两个表的交集和左边表剩下的数据，这个mysql再执行的时候是以left join左边的表去驱动右边的表，如果左边的表数据过大，就会出现性能问题。所以开发中要尽量使用inner join 少用left join

1.12 控制索引的数量

索引是我们优化sql时候最常用的手段，但是索引也不是越多越好
因为索引也是需要维护的，就类似书本的目录，如果索引太多，目录也会很多，那么找目录的时间相应的也会增加。所以要控制索引数量。因为mysql是用B+树保存索引的，所以再新增和修改删除数据的时候也是需要更新索引的，这会增加sql时间。阿里巴巴开发手册规定，单个索引数量不能超过五个，如果系统并发量比较少，那么超过五个也没关系。但是话又说回来了，并发量少的话，也就不需要优化了
那么如果需要那么多索引来提高效率怎么办，可以添加联合索引，就是将多个字段添加为索引。
说到联合索引，就要说一下前面提到的索引覆盖，
索引覆盖听名字高大上，说白了就是从索引中获取我们需要的信息。就像我们查字典，肯定是先去找目录，目录中就有我们要找的那个字，那么就不用再翻到对应的页码去找了。对应数据库就是，直接返回了，不需要再做回表操作了。
在联合索引中，还有个最左匹配原则：
最左匹配原则就是指在联合索引中，如果你的 sql 语句中用到了联合索引中的最左边的索引，那么这条 sql 语句就可以利用这个联合索引去进行匹配
假如有三个索引 a，b，c 在查询的时候
使用 a=‘xx’ and b = ‘xx’ and c=‘xx’ 可以命中
使用 a=‘xx’ and b = ‘xx’ 可以命中
使用 a=‘xx’ and c = ‘xx’ 不可以命中
使用 b=‘xx’ and c = ‘xx’ 不可以命中

1.13 选择合理的字段类型

char表示固定字符串类型，该类型的字段存储空间的固定的，会浪费存储空间。
varchar表示变长字符串类型，该类型的字段存储空间会根据实际数据的长度调整，不会浪费存储空间。
如果是长度固定的字段，比如用户手机号，一般都是11位的，可以定义成char类型，长度是11字节。
但如果是企业名称字段，假如定义成char类型，就有问题了。
如果长度定义得太长，比如定义成了200字节，而实际企业长度只有50字节，则会浪费150字节的存储空间。
如果长度定义得太短，比如定义成了50字节，但实际企业名称有100字节，就会存储不下，而抛出异常。
所以建议将企业名称改成varchar类型，变长字段存储空间小，可以节省存储空间，而且对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
我们在选择字段类型时，应该遵循这样的原则：
能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
长度固定的字符串字段，用char类型。
长度可变的字符串字段，用varchar类型。
金额字段用decimal，避免精度丢失问题。
还有很多原则，这里就不一一列举了。