1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 、join、 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
推荐方案:用其它相同功能的操作运算代替。如:a<>0改为 a>0 and a<0。 a<>' ' 改为a>' '
3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
如: a is not null 改为 a>0 或 a>' '
在建表时不可能为空的字段一定要添加非空约束,来避免字段进行 null 值判断(在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用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.like 的使用 下面的查询也将导致全表扫描:
select id from t where name like '%abc%'
常量开头的like , 会利用index
select * from t where name like 'AS%'
以常量结束的,加个reverse 函数,又可以用上index了'(需要反向索引的支持)
select * from t where reverse(name) like reverse('%AS')
若要提高效率,可以考虑全文检索。
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
推荐用EXISTS、NOT EXISTS 或外连接(join on)方案代替
7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:
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=100*2
9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3)='abc'--name以abc开头的id
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id
应改为:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
11.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。
12.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
13.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。
14.mysql limit 的优化
当一个数据库表过于庞大,LIMIT offset, length中的offset值过大,则SQL查询语句会非常缓慢
如:select * from t limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢。
* limit offset 如果数据在 10000 以下基本上影响不大, 速度也挺快
* limit offset 如果数据 > 10000 and < 100000 之间,可通过索引列或索引表
如原始:select * from t limit 50000,10 查询时间:0.087 平均
索引列:select * from t where id>=(select id from t ORDER BY id LIMIT 50000,1) limit 10 查询时间:0.020 平均
索引表:select b.* from (
select id from t order by id LIMIT 50000,10) a LEFT JOIN t b on a.id=b.id 查询时间:0.020 平均
* limit offset 如果 > 100000 以上方法经过本人测试都很卡,只有将 limit 改成 between and 可将速度提升至 0.001 平均。
如:select * from t where id BETWEEN 100000 and 100010 查询时间:0.001 平均
注:可以新增自动增长列或表来实现 between and 的使用
15.索引
*在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列:在WHERE子句中出现的列,在join子句中出现的列。
*在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引。
*在频繁进行排序或分组(即进行group?by或order?by操作)的列上建立索引。
*在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。
*索引列不能参与计算,保持列“干净”
如:from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引。 所以语句应该写成 create_time = unix_timestamp(’2014-05-29’);
*如果待排序的列有多个,可以在这些列上建立复合索引(compound?index)。
*使用系统工具。如Informix数据库有一个tbcheck工具,可以在可疑的索引上进行检查。在一些数据库服务器上,索引可能失效或者因为频繁操作而使得读取效率降低,如果一个使用索引的查询不明不白地慢下来,
可以试着用tbcheck工具检查索引的完整性,必要时进行修复。另外,当数据库表更新大量数据后,删除并重建索引可以提高查询速度。