sql 优化建议

Wesley13
• 阅读 400

1、查询语句,尽量避免查询全部,避免写 select * from table,查哪个写哪个,提高效率; 
更新语句,能update具体字段的,不要update所有字段,提升效率。 
要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引 

2、一张表中添加索引太少不行,索引的添加可以让查询速度变快,但是索引太多,新增操作会变慢, 
一般一张表中索引不要超过6个 

3、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描, 
如:select id from t where num is null 
最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库. 
备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。 
不要以为 NULL 不需要空间, 
如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。 
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询select id from t where num = 0 

4、应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。 

5、应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num=10 or Name = ‘admin’ 
可以这样查询: 
select id from t where num = 10 
union all 
select id from t where Name = ‘admin’ 

6、避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描 
select id from t where num/2 = 100 
可以改为: 
select id from t where num = 100*2 

7、避免在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’ 

8、 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 代替 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) 

9、 like模糊查询也将导致全表扫描: 
select id from t where name like ‘%abc%’ 
可以在界面上使用下拉菜单,让用户选择的方式,或者ajax弹出匹配的关键字,然后再查询。 
若要提高效率,可以考虑全文检索(Lucene或者solr)。 

10、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能, 
并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。 

11、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。 

12、如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。 

13、尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。(至于游标呢,可以把游标当作一个指针,它可以指定结果中的任何位置,然后允许用户对指定位置的数据进行处理) 

14、在可以使用UNION ALL的语句里,尽量使用,最好不要使用了UNION。 

15、用>=替代> 往往会高效 
高效:SELECT * FROM EMP WHERE DEPTNO >=4 
低效: SELECT * FROM EMP WHERE DEPTNO >3 
两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。 

16、多数数据库都是从 左到右的顺序处理条件,把能过滤更多数据的条件放在前面,过滤少的条件放后面 
select * from employee 
where salary >1000 –条件1,过滤的数据较少 
and dept_id=’01’ –条件2,过滤的数据比条件1多 
上面的SQL就不符合我们的原则了,应该把过滤数据更多的条件放在前面,因此改为下面这样更好 
select * from employee 
where dept_id=’01’ –过滤更多数据的条件放在前面 
and salary > 1000 
当然,Oracle数据库的where条件处理顺序是从右向左的。 

17、实际案例分析:拆分大的 DELETE 或INSERT 语句,批量提交SQL语句 
  如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。 
  如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你的WEB服务崩溃,还可能会让你的整台服务器马上挂掉。 
  所以,如果你有一个大的处理,你一定把其拆分,使用 LIMIT oracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例:

sql 优化建议  

18、总结 不要在建立的索引的数据列上进行下列操作: 
避免对索引字段进行计算操作 
避免在索引字段上使用not,<>,!= 
避免在索引列上使用IS NULL和IS NOT NULL 
避免在索引列上出现数据类型转换 
避免在索引字段上使用函数 
避免建立索引的列中使用空值。

结束!! 希望给各位猿友带来帮助吧!!! 偷偷告诉你们!!!这是我刚写的日记!!

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Kevin501 Kevin501
3年前
常见SQL编写和优化
常见的SQL优化方式1.对查询进行优化,应尽量避免全表扫描,首先应考虑在where及orderby涉及的列上建立索引。2.应尽量避免在where子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:selectidfromtwherenumisnul
Wesley13 Wesley13
2年前
MySQL基础学习笔记——数据库优化(2):SQL查询优化
数据库优化SQL查询优化1.避免全表扫描,应该考虑在where及orderby涉及的列上建立索引;2.查询时使用select明确指明所要查询的字段,避免使用select(keys,flushdb等)的操作;3.SQL语句尽量大写,
Wesley13 Wesley13
2年前
mysql千万级大数据SQL查询优化
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在where及orderby涉及的列上建立索引。2.应尽量避免在where子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:selectidfromtwherenumisnull可以在num上设置默认值0,确保表中num列没有
Stella981 Stella981
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
2年前
MySQL 常用30种SQL查询语句优化方法
1、应尽量避免在where子句中使用!或<操作符,否则将引擎放弃使用索引而进行全表扫描。2、对查询进行优化,应尽量避免全表扫描,首先应考虑在where及orderby涉及的列上建立索引。3、应尽量避免在where子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描。如:selectidfromt
Wesley13 Wesley13
2年前
Sql优化技巧
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在where及orderby涉及的列上建立索引。2.应尽量避免在where子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:selectidfromtwherenumisnull可以在num上设
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
2个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这