优化SQL查询

优化SQl查询

 

目标

 

1. 减少IO次数

数据库操作中90%的数据消耗都在IO，所以减少IO次数是最先考虑的。

 

2. 降低CPU计算

order by，group by，distinct，对CPU消耗都比较大

 

 

误区

 

1. count(1), count(primary_key)比count(*)好

对于有些场景，这样做可能性能会更差，应为数据库对 count(*) 计数操作做了一些特别的优化。？？？

一般我还是会用count(1)或count(primary_key)

 

 

2. count(column)和countd(*)是一样的

count(column) 是表示结果集中有多少个column字段不为空的记录

count(*) 是表示整个结果集有多少条记录

 

 

3. select a,b from … 比 select a,b,c from … 可以让数据库访问更少的数据量

其实数据量是一样的。数据库的存储原理

有索引的话就不一样，怎么不一样？？

 

 

4. order by 一定需要排序操作

索引本来就是排序的，加索引后，一般会省略排序操作

延伸阅读：MySQL ORDER BY 的实现分析，MySQL 中 GROUP BY 基本实现原理以及 MySQL DISTINCT 的基本实现原理这3篇文章中有更为深入的分析，尤其是第一篇

 

 

基本原则

 

1. 少用join

MySQL优化器效率高，多表join，一方面优化器受限，另一方面MySQL的join没Oracle好

 

 

2. 尽量减少排序

因为排序要占很多CPU资源，减少排序的方法

1. 用索引代替排序

2. 减少参与排序的数据条数

3.可以不排序，就不要对数据做排序

 

 

3. 尽量少使用 select *

一方面，如果用到order by，更少的数据会提升排序效果；另一方面，如果查询的字段刚好是索引的字段，会大大提升速度

 

 

 4. 尽量用 join 代替子查询

尽join性能上不太好，还是要比子查询要好

 

 

5. 尽量少用 or 

MySQL在where 后用or，性能还不如 union all、union，所以建议在必要的时候用 union all 或 union

 

 

6. 用 union all 代替 union

因为 union 合并后，会进行唯一性过滤，这涉及到排序，所以会大大占用CPU运算，如果确定数据不会重复或不在乎数据重复的情况下，尽量使用 union all

 

 

7. 尽早过滤

在用 join 的时候，可以在 join 子句中做一些简单的过滤，而不用在最后where的时候再以前过滤。

 

 

8. 首先优化高并发的SQL，而不是使用频率小的

因为高并发更容易出问题，速度慢一点点都可能压垮系统，而使用频率小的，即使慢一点，系统还是能顶住

 

 

9. 统一SQL语句写法

    select * from user

    SELECT * FROM user

对于我们来说，只是大小写的问题，但对于数据库解析器是要解析两次的，如果统一的话效率会更高。

 

 

10. 不要用太复杂的SQL

一般SQL语句不要超过三层嵌套，太多就看不懂，而且数据库解析会更慢，这样维护成本和解析成本都很高。

 

 

11. 少用数据库函数

数据库函数有事会根据不同数据库而不同，建议程序做好过使用数据库自带的函数，一是好维护，二是数据库迁移更容易。

 

 

 12. 可能会导致索引无效的情况，对索引字段进行如下操作

       1) like ‘%AA%’

       2) 索引字段进行计算操作

       3) not，<>，!=

       4) is null，is not null

       5) 类型转换

       6) 索引字段进行函数

 

 

13. 从全局出发优化，而不是片面调整

　　SQL 优化不能是单独针对某一个进行，而应充分考虑系统中所有的 SQL，尤其是在通过调整索引优化 SQL 的执行计划的时候，千万不能顾此失彼，因小失大。

 

 

14. 尽可能对每一条运行在数据库中的SQL进行 explain

　　优化 SQL，需要做到心中有数，知道 SQL 的执行计划才能判断是否有优化余地，才能判断是否存在执行计划问题。在对数据库中运行的 SQL 进行了一段时间的优化之后，很明显的问题 SQL 可能已经很少了，大多都需要去发掘，这时候就需要进行大量的 explain 操作收集执行计划，并判断是否需要进行优化。

 

 

15. 临时表

 

 

参考：http://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765465.html