处理百万级以上的数据提高查询速度的方法:
一.避免全表扫描
1.应尽量避免在where子句中使用!=或<>操作符。
2.对查询进行优化,应尽量避免全表扫描,首先应考虑在where及order by涉及的列上建立索引。
3.应尽量避免在where子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表所描,如:
select id from t where num is null
可以在num上设置默认值0,确认表中num列没有null值,然后这样查询:
select id from t where num = 0
4.应尽量避免在where子句中使用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
5.下面的查询也将导致全表扫描
select id from t where name like '%abc%'
6.in和not in也要慎用,否则会导致全表扫描,如:
select id from t where num in (1,2,3)
对于连续的数值,能用between就不要用in了:
select id from t where num between 1 and 3
7.如果在where子句中使用参数,也会导致全表扫描。
select id from t where num=@num 可以改为强制使用索引:
select id from t with(index(索引名)) where num=@num
8.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2 = 100
应改为:
select id from t where num = 200
9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3) = 'abc'
select id from t where datediff(day,createdate,'2015-11-30') = 0
应改为:
select id from t where name like '%abc%'
select id from t where createdate >= '2005-11-30' and createdate <= '2005-12-01'
二.检查索引
在sql语句的where和join部分用到的所有字段上,都应该加上索引。进行这3分钟的sql性能测试。不管你的
如何,一定要阅读那些带有信息的结果。
三.限制工作数据集的大小
检查那些select语句中用到的表,看看你是否可以应用where子句进行过滤。一个典型的例子是,当表中只有
几千行的记录时,一个查询能够很好的执行。但随着应用程序的成长,查询慢了下来,解决方案或许非常简单,
限制查询来查看当前月的数据即可。
当你的查询语句带有子查询时,注意子查询的内部语句上使用过滤,而不是在外部语句上。
四.只选择你需要的字段
额外的字段通常会增加返回数据的处理,从而导致更多的数据返回到sql客户端。另外:
1.使用带有报告和分析功能的应用程序时,有时报告性能低是因为报告工具必须对收到的、带有详细形式的数据做
聚合操作。
2.偶尔查询也可能运行的足够快,但你的问题可能是一个网络相关的问题,因为大量的详细数据通过网络发送到报告
服务器。
3.当使用一个面向列的DBMS时,只有你选择的列会从磁盘中读取。在你的查询中包含的列越少,IO开销就越小。
五.移除不必要的表
移除不必要的表的原因,和移除查询语句中不需要的字段的原因一致。
编写sql语句是一个过程,通常需要大量编写和测试sql语句的迭代过程。在开发过程中,你可能将表添加到查询中,
而这对于sql代码返回的数据可能不会有任何影响。一旦sql运行正确,我发现许多人不会回顾他们的脚本,不会删除
那些对最终数据没有任何影响和作用的表。通过移除与那些不必要表的joins操作,你减少了大量数据库必须执行的流 程。有时,就像移除列一样,你会发现你减少的数据又通过数据库返回来了。
六.移除外部连接查询
这说起来容易做起来难,它取决于改变表的内容有多大的影响。一个解决办法是通过两个表的行中放置占位符来删除
outer joins操作。假如你有以下的表,它们通过定义outer joins来确保返回所有的数据:
解决办法是在customer表的行中增加一个占位符,并更新sales表中的所有的null值到占位符。
你不止是删除了对outer join操作的依赖,同时标准化了没有客户的销售人员如何表示,其他
开发人员不必编写额外语句,例如isNull(customer_id,"No customer yet")
七.删除join和where子句中的计算字段
这是另外一个有时可能说起来容易做起来难的技巧,它取决于你更改表模式的权限大小。可以将连接
语句中用到的计算字段作为一个新字段在表中创建。给出一下sql语句:
在sales表中利用年和月增加一列,可以提高性能。更新后的sql语句将如下:
八.总结
上边的建议可以归结为一下几点:
1.检查索引。
2.在所需要的最小数据集上操作。
3.移除不必要的字段和表。
4.移除你join和where子句中的计算操作。
如果所有的这些建议都没能提高你的sql查询性能,最后一个建议是搬去金星把。你需要的就是一天能调优你的sql语句。