mysql优化sql语句查询的方法
比如员工表的索引如下图:
1,对where和order by子句经常会使用的列建立索引,提高查询效率。
2,应尽量避免在where子句中使用!=或<>操作符,否则将导致引擎放弃使用索引而进行全表扫描。
3,应尽量避免在where子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描。尽量不使用 is null和is not null语句。
4,应尽量避免在where子句中使用or来连接条件,否则将导致引擎放弃使用索引而进行全表扫描。
但测试这种情况时,发现有如下两个案例,是有使用到索引的,or为什么没有全表扫描,还有还和mysql优化器有关,待验证。
EXPLAIN
SELECT * FROM t_employee t1
where U_ID = 123 OR u_id = 234
EXPLAIN
SELECT * FROM t_employee t1
where U_ID = 123
UNION
SELECT * FROM t_employee t1
where u_id = 234
5,模糊查询导致全表扫描。
例如:select * from t_user where user_code like '%tom%',会全表扫描
如果user_code 是有索引,语句这样写是可以使用到索引的,select * from t_user where user_code like 'tom%'。
6,in和not in也要慎用,否则会导致全表扫描。
7,不要在where子句中的“=”[左边]进行函数、算术运算或者其他表达式运算,否则系统将可能无法正确使用索引。
8,如果使用复合索引,那么必须使用到该索引的第一个字段时,才能保证使用到索引,否则不会被使用。
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查询可能不会去利用索引。
11,索引并不是越多越好,索引可以提高select效率,但同时也降低了insert及update的效率。因此建议索引的数量不能过多。
12,尽量避免大事务操作,提高系统并发能力。
13,能用union all就不要用union
union all不执行select distinct函数,这样就会减少很多不必要的资源,在跨多个不同的数据库时使用union是一个有趣的优化方法,union从两个互不关联的表中返回数据,
这就意味着不会出现重复的行,同时也必须要对数据进行排序,我们知道排序是非常耗时的,特别是对大表的排序。union all可以大大加快速度,如果你已经知道你的数据
不会返回重复行,或者不在乎出现重复行,以及union all不会进行排序。若不需要排序或者用程序逻辑来避免出现重复数据,则使用union all更合适。