数据库sql常见优化方法

2022-10-22 15:19:16

以前刚开始做项目的时候，开发经验尚浅，每次遇到查询比较慢时，项目经理就会问：是不是又用select * 了？查询条件有没有加索引？一语惊醒梦中人，赶紧检查..果然如此！

有时我们写sql语句时，没有考虑它的性能或者完全没有优化意识，虽然能暂时满足基本功能，等到数据量大时，后果可想而知。

下面我们就聊一聊sql优化的一些常见方法：

1. 尽量不要用select * from table，除非需要返回数据库表的全部字段，否则不要返回用不到的任何字段。因为select * 会导致全表扫描，效率比较低。

2. where子句及order by涉及的列尽量建索引，不一定要全部建索引，依业务情形而定。对于多条where子句都用到的列，建议建索引。索引并不是越多越好，索引固然可以提高相应的select的效率，但同时也降低了insert及update 的效率。

3. 尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将会放弃使用索引而进行全表扫描。对于不等于这种情况，可考虑改为范围查询解决。

4. 尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，引擎将放弃使用索引而进行全表扫描，如：select id from person_info where age=10 or name= '张三'

可以这样查询：

select id from person_info where age = 10

union all

select id from person_info where name= '张三'

5. 尽量避免在 where 子句中对字段进行 null 值判断，因为空判断将导致全表扫描，而不是索引扫描。对于空判断这种情况，可以考虑对这个列创建数据库默认值。

    //null判断将会导致全表扫描

    select * from person_info where name is null;

    //可以考虑在经常需要null值判断的列，设为默认值，例如空字符串

    select * from person_info where name = '';

6. in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from person_info where age in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from person_info where age between 1 and 3

很多时候用 exists 代替 in 是一个好的选择：

select age from a where age in(select age from b)

用下面的语句替换：select age from a where exists(select age from b where b.age=a.age)

7. 尽量避免左右模糊查询，这样会导致索引失效，进而全表查询，如：select id from person_info where name like ‘%abc%’，可以使用右侧模糊查询，这样是可以索引查找的，如：select id from person_info where name like ‘abc%’

8. 如果在 where 子句中使用参数或对字段进行表达式操作，也会导致全表扫描，如：

select id from person_info where age/2 = 10

应改为: select id from person_info where age= 10*2

9. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。函数、算术运算或其他表达式运算通常将导致全表扫描，对于这种情况，可以考虑冗余部分数据到表中。

10. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

11. update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

12. 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。
13. select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。可以改为select count(id) from table。

14. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

15. 尽可能的使用varchar代替char，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

欢迎关注微信公众号【Java典籍】，收看更多Java技术干货！

　 ▼微信扫一扫下图↓↓↓二维码关注

码农公寓

相关文章