数据库sql优化计算之2-百万级数据库优化方案 案

2019-10-11 12:06 来源:未知

案例分析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

8、在 where 子句中对字段进行表达式操作,是不会导致全表扫描。不过查询速度会变慢,所以尽量避免使用。

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

案例分析:

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

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

彩民之家高手论坛 1

执行时间是:17.579s

彩民之家高手论坛 2

执行时间是:16.966s

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

例子请看第8点和第9点。

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

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

12.不要写一些没有意义的查询,如需要生成一个空表结构:(一般开发也不会这么无聊啦,在正式的项目上写这种玩意)

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

create table #t(…)

参考:

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

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

 

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

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

项目背景

有三张百万级数据表

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

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

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

测试数据库为:mysql (5.7)

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

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

案例分析

彩民之家高手论坛 3

执行时间是1.064s

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

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

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

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

案例分析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时间上是相差不多的

案例分析

彩民之家高手论坛 4

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

案例分析

EXPLAIN

SELECT *

FROM ex_subject_point

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

彩民之家高手论坛 5

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

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

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

 

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

彩民之家高手论坛 6

 

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

彩民之家高手论坛 7

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

 

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

参考:

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

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

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

参考链接:

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

优化方案

SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior 
WHERE ex_question_junior.QUESTION_CHANNEL_TYPE =4/2;

执行时间是0.012s

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

案例分析

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

彩民之家高手论坛 8

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

彩民之家高手论坛 9

优化方案

SELECT *
FROM ex_subject_point 
WHERE CREATE_DT >= "2018-05-31"
AND CREATE_DT < "2018-07-01"

 

但是EXPLAIN一下,发现这样还是全表扫描的

彩民之家高手论坛 10

难道是因为日期字段索引没有效果吗?还是因为用了>=和<运算符号?

来验证一下

缩小查询范围,发现索引是有效果的。所以不是日期字段的问题。

彩民之家高手论坛 11

换个字段查询,用>=和<运算符号,索引还是有效果的。但那是什么原因呢?

彩民之家高手论坛 12

后来去网上查找了资料,原因是查询数量是超过表的一部分,mysql30%,oracle 20%(这个数据可能不准确,不是官方说明,仅供参考),导致索引失效。

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

案例分析:

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

彩民之家高手论坛 13

SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior 
WHERE IS_USE is NULL

执行时间是:11.729s

彩民之家高手论坛 14

SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior 
WHERE IS_USE =0

执行时间是12.253s

时间几乎一样。

案例分析

复合索引字段:PATH,PARENT_POINT_ID

彩民之家高手论坛 15

调换WHERE子句中的条件顺序。发现还是可以使用索引的

彩民之家高手论坛 16

复合索引只查询第一个字段,是有效果的

彩民之家高手论坛 17

复合索引只查询第二个字段,发现索引没有效果了。

彩民之家高手论坛 18

select id from t where num between 1 and 3

案例分析:

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索引的经验分类:

彩民之家高手论坛 19

7、在 where 子句中使用参数,是不会导致全表扫描。

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

案例分析:

GRADE_ID字段有索引,QUESTION_TYPE没索引

彩民之家高手论坛 20

执行时间是:11.661s

优化方案:

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

彩民之家高手论坛 21

执行时间是:11.811s

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

案例分析

彩民之家高手论坛 22

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

项目背景

有三张百万级数据表

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

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

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

测试数据库为:mysql (5.7)

 

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

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

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

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

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

晚安。

 

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

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

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

select id from t where name like ‘

TAG标签: MySQL
版权声明:本文由彩民之家高手论坛发布于彩民之家高手论坛,转载请注明出处:数据库sql优化计算之2-百万级数据库优化方案 案