怎样优化数据库查询
宏观:
1.缓存。在持久层或持久层之上做缓存。 从数据库中查询出来的数据先放入缓存中,下次查询时,先訪问缓存。假设未命中则查询数据库。
2.表分区和拆分.不管是业务逻辑上的拆分还是无业务含义的分区。
3.提高磁盘速度.这包含RAID和其它磁盘文件分段的处理。基本的思想是提高磁盘的并发度(多个物理磁盘存放同一个文件)。
微观:
表设计方面:
1.字段冗余.降低跨库查询和大表连接操作。
2.数据库表的大字段剥离.保证单条记录的数据量非常小。
3.恰当地使用索引, 甚至是多级索引。
查询优化方面:
2.应尽量避免在 where 子句中对字段进行 null 值推断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
能够在num上设置默认值0,确保表中num列没有null值。然后这样查询:
select id from t where num=0
3.应尽量避免在 where 子句中使用!=或<>操作符。否则将引擎放弃使用索引而进行全表扫描。
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.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
6.以下的查询也将导致全表扫描:
select id from t where name like '%abc%' 若要提高效率,能够考虑全文检索。
select id from t where num is null
能够在num上设置默认值0,确保表中num列没有null值。然后这样查询:
select id from t where num=0
3.应尽量避免在 where 子句中使用!=或<>操作符。否则将引擎放弃使用索引而进行全表扫描。
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.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
6.以下的查询也将导致全表扫描:
select id from t where name like '%abc%' 若要提高效率,能够考虑全文检索。
7.索引并非越多越好,索引固然能够提高对应的 select 的效率,但同一时候也减少了 insert 及 update 的效率,由于 insert 或 update 时有可能会重建索引。所以如何建索引须要谨慎考虑,视详细情况而定。一个表的索引数最好不要超过6个。若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
8.尽量使用数字型字段,若仅仅含数值信息的字段尽量不要设计为字符型,这会减少查询和连接的性能,并会添加存储开销。这是由于引擎在处理查询和连接时会逐个比較字符串中每个字符,而对于数字型而言仅仅须要比較一次就够了。
9.不论什么地方都不要使用 select * from t ,用详细的字段列表取代“*”,不要返回用不到的不论什么字段。
10.避免频繁创建和删除暂时表,以降低系统表资源的消耗。