温馨提示:本文共2515个字,读完预计7分钟。
1.查询SQL尽量不要使用select ,而是select具体字段。
2. where子句比较符号左侧避免函数
尽量避免在where条件子句中,比较符号的左侧出现表达式、函数等操作。因为这会导致数据库引擎进行全表扫描,从而增加运行时间。
举个例子,下图是10名学生的成绩表,老师突然发现因为参考答案出错,给所有人都少加了5分,现在需要查询:给每人加5分后,成绩依然在90分以上的同学的学号。
按照题目的思路直接书写,“给每人加5分后,成绩90分以上”的条件很多人会这样写:
where 成绩 + 5 > 90 (表达式在比较符号的左侧)
优化方法:
where 成绩 > 90 – 5(表达式在比较符号的右侧)
所以,为了提高效率,where子句中遇到函数或加减乘除的运算,应当将其移到比较符号的右侧。
3.尽量避免使用or
or同样会导致数据库进项全表搜索。在工作中,如果你只想用or从几十万语句中取几条出来,是非常划不来的,怎么办呢?下面的方法可替代or。
从成绩表中选出成绩是是88分或89分学生的学号:
select 学号
from 成绩表
where 成绩 = 88 or 成绩 = 89
优化后:
select 学号 from 成绩表 where 成绩 = 88
union
select 学号 from 成绩表 where 成绩 = 89
语句虽然变长了一点,但处理大量数据时,可以省下很多时间,是非常值得的。
4.优化limit分页
//方案一 :返回上次查询的最大记录(偏移量)
select id,name from employee where id>10000 limit 10.
//方案二:orderby + 索引
select id,name from employee order by id limit 10000,10
5、优化你的like语句
select userId,name from user where userId like'123%';
6、使用where条件限定要查询的数据,避免返回多余的行
Long userId = sqlMap.queryObject("select userId from user where userId='userId' and isVip='1'")
boolean isVip = userId!=null;
7.尽量避免在索引列上使用mysql的内置函数
explain select userId,loginTime from loginuser where loginTime >= Date_ADD(NOW(),INTERVAL - 7DAY);
8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致系统放弃使用索引而进行全表扫
select * from user where age =11;
9.Inner join 、left join、right join,优先使用Inner join,如果是left join,左边表结果尽量小
- Inner join 内连接,在两张表进行连接查询时,只保留两张表中完全匹配的结果集
- left join 在两张表进行连接查询时,会返回左表所有的行,即使在右表中没有匹配的记录。
- right join 在两张表进行连接查询时,会返回右表所有的行,即使在左表中没有匹配的记录。
select * from (select * from tab1 where id >2) t1 left join tab2 t2 on t1.size = t2.size;
10、如果插入数据过多,考虑批量插入。
//一次500批量插入,分批进行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.name},#{item.age})
</foreach>
11、不要有超过5个以上的表连接
- 连表越多,编译的时间和开销也就越大。
- 把连接表拆开成较小的几个执行,可读性更高。
- 如果一定需要连接很多表才能得到数据,那么意味着糟糕的设计了。
12、尽可能使用varchar/nvarchar 代替 char/nchar。
`deptName` varchar(100) DEFAULT NULL COMMENT '部门名称'
13、如何字段类型是字符串,where时一定用引号括起来,否则索引失效
select * from user where userid ='123';
14.使用explain 分析你SQL的计划
explain select * from user where userid =10086 or age =18;
15、使用联合索引时,注意索引列的顺序,一般遵循最左匹配原则。
16、避免频繁创建和删除临时表,以减少系统表资源的消耗。
17、临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。
18、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先 create table,然后 insert。
19、如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。
20、尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过 1 万行,那么就应该考虑改写。
21、使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。
22、与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。
23、尽量避免大事务操作,提高系统并发能力。
24、尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。