欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

sql 优化技能(mysql)

更新时间: 2014-01-05 02:09:30 责任编辑: Author_N1

 

sql 优化技巧(mysql)
1,
使用  count(*) 而不是count(1) 和 count(primary_key) ,应为数据库对 count(*) 计数操作做了一些特别的优化。

2,count(column) 和count(*) 查询结果不一致

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

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

3,
1)查询结果顺序如果与查询条件中使用的字段上索引顺序一致,就不需要进行排序动作了,通常认为在索引中排序了,其实是因为索引顺序与 排序一致,少了排序一步。

索引数据实际上是有序的,如果我们的需要的数据和某个索引的顺序一致,而且我们的查询又通过这个索引来执行,那么数据库一般会省略排序操作,而直接将数据返回,因为数据库知道数据已经满足我们的排序需求了

2)如果有group by ,肯定会经过排序一个环节,如果结果不需要排序,可以order by null,就可以让数据库忽略 group by 过程中的排序环节。

总结排序:

对于MySQL来说,减少排序有多种办法,比如:

  通过利用索引来排序的方式进行优化

  减少参与排序的记录条数

  非必要不对数据进行排序

4,
select 子句中的字段多少会在很大程度上影响到我们的排序效率,所以子句中尽量少用 select *
而非子句上的select a,b 与select a,b,c执行速度是一样的


大多数关系型数据库都是按照行(row)的方式存储,而数据存取操作都是以一个固定大小的IO单元(被称作 block 或者 page)为单位,一般为4KB,8KB… 大多数时候,每个IO单元中存储了多行,每行都是存储了该行的所有字段(lob等特殊类型字段除外)。

  所以,我们是取一个字段还是多个字段,实际上数据库在表中需要访问的数据量其实是一样的。

也有例外情况,那就是我们的这个查询在索引中就可以完成,也就是说当只取 a,b两个字段的时候,不需要回表,而c这个字段不在使用的索引中,需要回表取得其数据。在这样的情况下,二者的IO量会有较大差异。

5,尽量用 join 代替子查询

  虽然 Join 性能并不佳,但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题,虽然这个问题已经存在多年,但是到目前已经发布的所有稳定版本中都普遍存在,一直没有太大改善。虽然官方也在很早就承认这一问题,并且承诺尽快解决,但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。

6,尽量少 or

  当 where 子句中存在多个条件以“或”并存的时候,MySQL 的优化器并没有很好的解决其执行计划优化问题,再加上 MySQL 特有的 SQL 与 Storage 分层架构方式,造成了其性能比较低下,很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

如:
select id from t where num=10 or num=20
可以这样查询:
select id from t where num=10
union all
select id from t where num=20

但是非要用or又要用上索引,那么就需要把所有or的字段都加上索引


7,尽量用 union all 代替 union

  union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的 CPU 运算,加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候,尽量使用 union all 而不是 union。
8,尽量早过滤

  这一优化策略其实最常见于索引的优化设计中(将过滤性更好的字段放得更靠前)。

  在 SQL 编写中同样可以使用这一原则来优化一些 Join 的 SQL。比如我们在多个表进行分页数据查询的时候,我们最好是能够在一个表上先过滤好数据分好页,然后再用分好页的结果集与另外的表 Join,这样可以尽可能多的减少不必要的 IO 操作,大大节省 IO 操作所消耗的时间。
9,避免类型转换
这里所说的“类型转换”是指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换,这样直接导致 MySQL(实际上其他数据库也会有同样的问题)无法使用索引。

10,优先优化高并发的 SQL,而不是执行频率低某些“大”SQL

  对于破坏性来说,高并发的 SQL 总是会比低频率的来得大,因为高并发的 SQL 一旦出现问题,甚至不会给我们任何喘息的机会就会将系统压跨。而对于一些虽然需要消耗大量 IO 而且响应很慢的 SQL,由于频率低,即使遇到,最多就是让整个系统响应慢一点,但至少可能撑一会儿,让我们有缓冲的机会


二,其他不能使用索引以及优化建议

1,
where 子句中对字段进行 null 值判断
如:select id from t where num is null
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0

2,where 子句中使用!=或<>操作符。
3,where 子句中使用 or 来连接条件
4,in 和 not in ,如果顺序的可以使用 between and 代替
5,like  以 _ 或者 %开头 如 like '%模糊'/like ‘_模糊’,可以使用模糊查询  match 方式针对英文,中文可以使用sphinx
6,
如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:
6,
在 where 子句中对字段进行表达式操作

select id from t where num/2=100
应改为:
select id from t where num=100*2

7,
where子句中对字段进行函数操作

select id from t where substring(name,1,3)='abc'--name以abc开头的id
应改为:
select id from t where name like 'abc%'

8, where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

9,
很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)

10,

并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

11.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。


12.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

13.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

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


15,
避免频繁创建和删除临时表,以减少系统表资源的消耗。

16.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

17.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

18.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

19.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

20.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

21.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

22.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

23.尽量避免大事务操作,提高系统并发能力。

24.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理



上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-01-05 02:09:30
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/6343/
本文WWW.DOC100.NET DOC100.NET版权所有。