查询语句,昨天与大家分享了SQL优化中的索引优

作者: www.9159.com  发布:2019-11-20

项目背景

有三张百万级数据表

知识点表(ex_subject_point)9,316条数据

试题表(ex_question_junior)2,159,519条数据 有45个字段

知识点试题关系表(ex_question_r_knowledge)3,156,155条数据

测试数据库为:mysql (5.7)

 

昨天与大家分享了SQL优化中的索引优化,今天给大家聊一下,在开发过程中高质量的代码也是会带来优化的

1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

1、查询语句,尽量避免查询全部,避免写 select * from table,查哪个写哪个,提高效率;

1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

网上关于SQL优化的教程很多,但是比较杂乱。整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

更新语句,能update具体字段的,不要update所有字段,提升效率。

案例分析:

SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior 
WHERE ex_question_junior.GRADE_ID=15

执行时间:17.609s (多次执行,在17s左右徘徊)

优化后:给GRADE_ID字段添加索引后

执行时间为:11.377s(多次执行,在11s左右徘徊)

备注:我们一般在什么字段上建索引?

这是一个非常复杂的话题,需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引,其它需要建索引的字段应满足以下条件:

  a、字段出现在查询条件中,并且查询条件可以使用索引;

  b、语句执行频率高,一天会有几千次以上;

  c、通过字段条件可筛选的记录集很小,那数据筛选比例是多少才适合?

这个没有固定值,需要根据表数据量来评估,以下是经验公式,可用于快速评估:

小表(记录数小于10000行的表):筛选比例<10%;

大表:(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

单条记录长度≈字段平均内容长度之和+字段数*2

以下是一些字段是否需要建B-TREE索引的经验分类:

图片 1

SQL语句优化,简单的说就是对SQL语句进行高效率的代码编写,其原理其实与SQL索引优化一致:

select id from t where num is null

要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引

2、应尽量避免在 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

建立索引其实就是减少数据库在执行时所扫描的影响行数,尽可能的避免全局扫描

最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。

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

案例分析:

在mysql数据库中对字段进行null值判断,是不会放弃使用索引而进行全表扫描的。

图片 2

SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior 
WHERE IS_USE is NULL

执行时间是:11.729s

图片 3

SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior 
WHERE IS_USE =0

执行时间是12.253s

时间几乎一样。

优化注意:
对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

一般一张表中索引不要超过6个

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

应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0

3、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,

案例分析:

在mysql数据库中where 子句中使用 != 或 <> 操作符,引擎不会放弃使用索引。

EXPLAIN
SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior 
WHERE ex_question_junior.GRADE_ID !=15

图片 4

执行时间是:17.579s

图片 5

执行时间是:16.966s

优化方案:
这里给大家说一下什么SQL语句会执行全表扫描

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

如:select id from t where num is null

4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描

1. 查询条件中含有is null的select语句执行慢

4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:

最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库.

案例分析:

GRADE_ID字段有索引,QUESTION_TYPE没索引

图片 6

执行时间是:11.661s

优化方案:

通过union all 方式,把有索引字段和非索引字段分开。索引字段就有效果了

图片 7

执行时间是:11.811s

但是,非索引字段依然查询速度会很慢,所以查询条件,能加索引的尽量加索引

解决方法:SQL语法中使用NULL会有很多麻烦,最好索引列都是NOT NULL的,而且最好不要给数据库留NULL,尽可能的使用NOT NULL填充数据库.备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。;

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'

备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。

5.in 和 not in 也要慎用,否则会导致全表扫描

is null,可以建立索引,is null查询时可以启用索引查找,但是效率还不是值得肯定,建议不要使用。
is not null 时永远不会使用索引。一般数据量大的表不要用is null查询。

5.in 和 not in 也要慎用,否则会导致全表扫描,如:

不要以为 NULL 不需要空间,

案例分析

注:在mysql数据库中where 子句中对索引字段使用 in 和 not in操作符,引擎不会放弃使用索引。

图片 8

注:在mysql数据库中where 子句中对不是索引字段使用 in 和 not in操作符,会导致全表扫描。

图片 9

不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

select id from t where num in (1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3

如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

案例分析2:

用between和in的区别

SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior
WHERE ex_question_junior.QUESTION_TYPE  IN(1,2,3,4)

执行时间为1.082s

SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior
WHERE ex_question_junior.QUESTION_TYPE between 1 and 4

执行时间为0.924s时间上是相差不多的

可以在对字段上设置默认值0,确保表中字段列没有null值,然后这样可以匹配where 字段等于0的数据:

很多时候用 exists 代替 in 是一个好的选择:

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询select id from t where num = 0

案例分析3:

用exists 和 in区别:结论

1. in()适合B表比A表数据大的情况(A<B)

select * from A

where id in(select id from B)

2. exists()适合B表比A表数据小的情况(A>B)

  select * from A

  where exists(

  select 1 from B where B.id = A.id

  )

3、当A表数据与B表数据一样大时,

in与exists效率差不多,可任选一个使用.语法

****************************************************************************

ex_question_r_knowledge(A)表数据量大,ex_subject_point表数据量小(B)(A>B)

用exists适合

SELECT *
FROM ex_question_r_knowledge
WHERE ex_question_r_knowledge.SUBJECT_POINT_ID IN
(
    SELECT ex_subject_point.SUBJECT_POINT_ID
    FROM ex_subject_point
    WHERE ex_subject_point.SUBJECT_ID=7
)

执行时间是:38.404s

SELECT *
FROM ex_question_r_knowledge
WHERE  exists
(
    SELECT 1
    FROM ex_subject_point
    WHERE ex_subject_point.SUBJECT_ID=7
    AND ex_subject_point.SUBJECT_POINT_ID = ex_question_r_knowledge.SUBJECT_POINT_ID
)

执行时间是:13.537s

*************************************************************************

ex_subject_point表数据量小(A),ex_question_r_knowledge(B)表数据量大(A<B)

用in适合

SELECT * 
FROM ex_subject_point 
WHERE ex_subject_point.SUBJECT_POINT_ID IN(
   SELECT ex_question_r_knowledge.SUBJECT_POINT_ID 
  FROM ex_question_r_knowledge 
    WHERE ex_question_r_knowledge.GRADE_TYPE=2 )

执行时间是:1.554s

SELECT *
    FROM ex_subject_point
    WHERE  exists(
    SELECT ex_question_r_knowledge.SUBJECT_POINT_ID
    FROM ex_question_r_knowledge
    WHERE ex_question_r_knowledge.GRADE_TYPE=2
    AND ex_question_r_knowledge.SUBJECT_POINT_ID= ex_subject_point.SUBJECT_POINT_ID
)

执行时间是:11.978s

 

select num from a where num in (select num from b)

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

6、like模糊全匹配也将导致全表扫描

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

用下面的语句替换:

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

案例分析

EXPLAIN

SELECT *

FROM ex_subject_point

WHERE ex_subject_point.path like "%/11/%"

图片 10

若要提高效率,可以考虑全文检索。lucene了解一下。或者其他可以提供全文索引的nosql数据库,比如tt server或MongoDB

昨天晚上突发奇想,like 模糊全匹配,会导致全表扫描,那模糊后匹配和模糊前匹配也会是全表扫描吗?

今天开电脑,做了下测试。结果如下:

 

like模糊后匹配,不会导致全表扫描

图片 11

 

like模糊前匹配,会导致全表扫描

图片 12

MY SQL的原理就是这样的,LIKE模糊全匹配会导致索引失效,进行全表扫描;LIKE模糊前匹配也会导致索引失效,进行全表扫描;但是LIKE模糊后匹配,索引就会有效果。

 

还会陆续更新,还有几个小节。

参考:

***************************************************************************

作者:小虚竹
欢迎任何形式的转载,但请务必注明出处。
限于本人水平,如果文章和代码有表述不当之处,还请不吝赐教。

原因:SQL中,不等于操作符会限制索引,引起全表扫描,即使比较的字段上有索引
解决方法:通过把不等于操作符改成or,可以使用索引,避免全表扫描。例如,把column<>’aaa’,改成column<’aaa’ or column>’aaa’,就可以使用索引了。

select num from a where exists (select 1 from b where num=a.num)

可以这样查询:

3.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:

6.下面的查询也将导致全表扫描:

select id from t where num = 10

select id from t where num=10 or Name = 'admin'
可以这样查询:

select id from t where name like ‘%abc%’
若要提高效率,可以考虑全文检索。

union all

select id from t where num = 10
union all
select id from t where Name = 'admin'

7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

select id from t where Name = ‘admin’

union解释
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
使用or连接索引不生效
代码如下:
mysql> explain select id from t1 where name=1 or age=2G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: in_name
key: NULL
key_len: NULL
ref: NULL
rows: 10000
filtered: 55.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
如果使用UNION连接的话查询时索引生效。
代码如下:
mysql> desc select * from t1 where name=1 union all select *from t1 where age=2G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY 第一条语句索引生效
table: t1
partitions: NULL
type: ref
possible_keys: in_name
key: in_name
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 2
select_type: UNION 第二条语句未设定索引没有使用索引
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10000
filtered: 10.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)

select id from t where num=@num

6、避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描

4.in 和 not in 也要慎用,否则会导致全表扫描,如:

可以改为强制查询使用索引:

select id from t where num/2 = 100

select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:

select id from t with (index(索引名)) where num=@num

可以改为:

select id from t where num between 1 and 3

应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where num = 100*2

5.模糊查询下面的查询也将导致全表扫描:

select id from t where num/2=100
应改为:
select id from t where num=100*2

7、避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描

select id from t where name like ‘%abc%’
若要提高效率,可以考虑全文检索。

9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的id

5.in 和 not in 也要慎用,否则会导致全表扫描,如:

select id from t where substring(name,1,3)=’abc’ -–name以abc开头的id
select id from t where datediff(day,createdate,’2015-11-30′)=0 -–‘2015-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'

select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ –生成的id

 

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

可以改为:

很多时候用 exists 代替 in 是一个好的选择:

11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

select id from t where name like ‘abc%’

select num from a where num in(select num from b)
用下面的语句替换:

12.不要写一些没有意义的查询,如需要生成一个空表结构:

select id from t where createdate >= ‘2005-11-30’ and createdate < ‘2005-12-1’

select num from a where exists(select 1 from b where num=a.num)

select col1,col2 into #t from t where1=0
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

8、in 和 not in 也要慎用,否则会导致全表扫描,如:select id from t where num in(1,2,3)

6.如果在 where 子句中使用参数,也会导致全表扫描。

create table #t(…)

对于连续的数值,能用 between 就不要用 in 了:

因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

13.Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

select id from t where num between 1 and 3

select id from t where num = @num
可以改为强制查询使用索引:

14.对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差。

用 exists 代替 in 是一个好的选择:

select id from t with(index(索引名)) where num = @num
应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

15.select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。

select num from a where num in(select num from b)

select id from t where num/2 = 100
应改为:

16.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

可以改为:

select id from t where num = 100*2
7.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

17.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

select num from a where exists(select 1 from b where num=a.num)

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
应改为:

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

9、like模糊查询也将导致全表扫描:

select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'
8.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

19.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

select id from t where name like ‘%abc%’

9.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

20.任何地方都不要使用

可以在界面上使用下拉菜单,让用户选择的方式,或者ajax弹出匹配的关键字,然后再查询。

10.不要写一些没有意义的查询,如需要生成一个空表结构:

select * from t

若要提高效率,可以考虑全文检索(Lucene或者solr)。

select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

用具体的字段列表代替“*”,不要返回用不到的任何字段。

10、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,

create table #t(…)

21.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

10.Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

22.避免频繁创建和删除临时表,以减少系统表资源的消耗。临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件, 最好使用导出表。

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

11.对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差。

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

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

12.select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。

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

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

13.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

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

14.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

15、用>=替代> 往往会高效

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

27.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

高效:SELECT * FROM EMP WHERE DEPTNO >=4

16.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

低效: SELECT * FROM EMP WHERE DEPTNO >3

17.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

29.尽量避免大事务操作,提高系统并发能力。

两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

18.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

16、多数数据库都是从 左到右的顺序处理条件,把能过滤更多数据的条件放在前面,过滤少的条件放后面

19. 避免频繁创建和删除临时表,以减少系统表资源的消耗。临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件, 最好使用导出表。

实际案例分析:拆分大的 DELETE 或INSERT 语句,批量提交SQL语句

select * from employee

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

如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。

where salary >1000 –条件1,过滤的数据较少

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

Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。

and dept_id=’01’ –条件2,过滤的数据比条件1多

22.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你的WEB服务崩溃,还可能会让你的整台服务器马上挂了。

上面的SQL就不符合我们的原则了,应该把过滤数据更多的条件放在前面,因此改为下面这样更好

23.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

所以,如果你有一个大的处理,你一定把其拆分,使用 LIMIT oracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例:

select * from employee

24.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

while(1){//每次只做1000条
mysql_query(“delete from logs where log_date <= ’2015-11-01’ limit 1000”);
if(mysql_affected_rows() == 0){//删除完成,退出!break;
}//每次暂停一段时间,释放表让其他进程/线程访问。
usleep(50000)
}

where dept_id=’01’ –过滤更多数据的条件放在前面

25.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

and salary > 1000

26.尽量避免大事务操作,提高系统并发能力。

当然,Oracle数据库的where条件处理顺序是从右向左的。

27.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

17、实际案例分析:拆分大的 DELETE 或INSERT 语句,批量提交SQL语句

实际案例分析:拆分大的 DELETE 或INSERT 语句,批量提交SQL语句

如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。

如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。

如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你的WEB服务崩溃,还可能会让你的整台服务器马上挂掉。

Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。

所以,如果你有一个大的处理,你一定把其拆分,使用 LIMIT oracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例:

如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你的WEB服务崩溃,还可能会让你的整台服务器马上挂了。

图片 13

所以,如果你有一个大的处理,你一定把其拆分,使用 LIMIT oracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例:

这是一张图

 

18、总结不要在建立的索引的数据列上进行下列操作:

while(1){

避免对索引字段进行计算操作

//每次只做1000条

避免在索引字段上使用not,<>,!=

mysql_query(“delete from logs where log_date <= ’2012-11-01’ limit 1000”);

避免在索引列上使用IS NULL和IS NOT NULL

if(mysql_affected_rows() == 0){

避免在索引列上出现数据类型转换

//删除完成,退出!
break;
}

避免在索引字段上使用函数

//每次暂停一段时间,释放表让其他进程/线程访问。
usleep(50000)

避免建立索引的列中使用空值。

}

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

 

参考链接:

 

这篇文章Qi号也没有完全融汇贯通,希望可以对大家有所帮助。

晚安。

本文由9159.com发布于www.9159.com,转载请注明出处:查询语句,昨天与大家分享了SQL优化中的索引优

关键词: