精华内容
下载资源
问答
  • SQL优化常用几种方法
    万次阅读 多人点赞
    2020-06-29 14:42:42

    一、背景

          在使用JPA时常常出现业务复杂不方便使用名称解析的情况,这时可采用原生SQL来实现,SQL在请求并发数量较多时效率会影响系统的整体效率,在此记录一下sql优化的常用几种方法。

    二、优化方法

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

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

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

                select id from t where num is null

                可以在num列设置默认值0,然后通过=等号查询:

                select id from t where num=0

         4、应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

                select id from t where num=10 or num=20

                此查询可优化为:select id from t where num=10
                                             union all
                                             select id from t where num=20

         5、下面的查询也将导致全表扫描:
                select id from t where name like '%abc%'

                若要提高效率,可以考虑全文检索。

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

               select id from t where num in(1,2,3)

               若查询的数值为连续值,则可以优化为between来查询:

               select id from t where num between 1 and 3

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

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

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

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

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

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

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

        12、不要写一些没有意义的查询,如需要生成一个空表结构:
                select col1,col2 into #t from t where 1=0
                这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
                create table #t(...)

       13、很多时候用 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)

       14、并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会            去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

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

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

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

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

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

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

       21、避免频繁创建和删除临时表,以减少系统表资源的消耗。

    更多相关内容
  • sql优化几种方法

    2018-05-18 23:12:34
    sql优化几种方法sql优化几种方法sql优化几种方法sql优化几种方法sql优化几种方法
  • sql优化常用几种方法

    千次阅读 2022-02-16 15:31:55
    本文我们来谈谈项目中常用的MySQL优化方法,共19条,具体如下: 1、EXPLAIN 做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。 下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据: type列,...

    本文我们来谈谈项目中常用的MySQL优化方法,共19条,具体如下:

    1、EXPLAIN

    做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。

    下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据:

    • type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
    • key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式。
    • key_len列,索引长度。
    • rows列,扫描行数。该值是个预估值。
    • extra列,详细说明。注意,常见的不太友好的值,如下:Using filesort,Using temporary。

    2、SQL语句中IN包含的值不应过多

    MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select id from t where num in(1,2,3) 对于连续的数值,能用between就不要用in了;再或者使用连接来替换。

    3、SELECT语句务必指明字段名称

    SELECT*增加很多不必要的消耗(CPU、IO、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构发生改变时,前断也需要更新。所以要求直接在select后面接上字段名。

    4、当只需要一条数据的时候,使用limit 1

    这是为了使EXPLAIN中type列达到const类型

    5、如果排序字段没有用到索引,就尽量少排序

    6、如果限制条件中其他字段没有索引,尽量少用or

    or两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。很多时候使用union all或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

    7、尽量用union all代替union

    union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all的前提条件是两个结果集没有重复数据。

    8、不使用ORDER BY RAND()

    select id from `dynamic` order by rand() limit 1000;

    上面的SQL语句,可优化为:

    select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nidlimit 1000;

    9、区分in和exists、not in和not exists

    select * from 表A where id in (select id from 表B)

    上面SQL语句相当于

    select * from 表A where exists(select * from 表B where 表B.id=表A.id)

    区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

    关于not in和not exists,推荐使用not exists,不仅仅是效率问题,not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句?

    原SQL语句:

    select colname … from A表 where a.id not in (select b.id from B表)

    高效的SQL语句:

    select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

    取出的结果集如下图表示,A表不在B表中的数据:

    10、使用合理的分页方式以提高分页的效率

    select id,name from product limit 866613, 20

    使用上述SQL语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。

    优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。SQL可以采用如下的写法:

    select id,name from product where id> 866612 limit 20

    11、分段查询

    在一些用户选择页面中,可能一些用户选择的时间范围过大,造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序,分段进行查询,循环遍历,将结果合并处理进行展示。

    如下图这个SQL语句,扫描的行数成百万级以上的时候就可以使用分段查询:

    12、避免在where子句中对字段进行null值判断

    对于null的判断会导致引擎放弃使用索引而进行全表扫描。

    13、不建议使用%前缀模糊查询

    例如LIKE“%name”或者LIKE“%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

    那如何查询%name%?

    如下图所示,虽然给secret字段添加了索引,但在explain结果并没有使用:

    那么如何解决这个问题呢,答案:使用全文索引。

    在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like ‘%zhangsan%’; 。这样的语句,普通索引是无法满足查询需求的。庆幸的是在MySQL中,有全文索引来帮助我们。

    创建全文索引的SQL语法是:

    ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

    使用全文索引的SQL语句是:

    select id,fnum,fdst from dynamic_201606 where match(user_name) against(‘zhangsan’ in boolean mode);

    注意:在需要创建全文索引之前,请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。

    14、避免在where子句中对字段进行表达式操作

    比如:

    select user_id,user_project from user_base where age*2=36;

    中对字段就行了算术运算,这会造成引擎放弃使用索引,建议改成:

    select user_id,user_project from user_base where age=36/2;

    15、避免隐式类型转换

    where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换,建议先确定where中的参数类型。

    16、对于联合索引来说,要遵守最左前缀法则

    举列来说索引含有字段id、name、school,可以直接用id字段,也可以id、name这样的顺序,但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面。

    17、必要时可以使用force index来强制查询走某个索引

    有的时候MySQL优化器采取它认为合适的索引来检索SQL语句,但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。

    18、注意范围查询语句

    对于联合索引来说,如果存在范围查询,比如between、>、<等条件时,会造成后面的索引字段失效。

    19、关于JOIN优化

    LEFT JOIN A表为驱动表,INNER JOIN MySQL会自动找出那个数据少的表作用驱动表,RIGHT JOIN B表为驱动表。

    注意:

    1)MySQL中没有full join,可以用以下方式来解决:

    select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;

    2)尽量使用inner join,避免left join:

    参与联合查询的表至少为2张表,一般都存在大小之分。如果连接方式是inner join,在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表,但是left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。

    3)合理利用索引:

    被驱动表的索引字段作为on的限制字段。

    4)利用小表去驱动大表:

    从原理图能够直观的看出如果能够减少驱动表的话,减少嵌套循环中的循环次数,以减少 IO总量及CPU运算的次数。

    5)巧用STRAIGHT_JOIN:

    inner join是由MySQL选择驱动表,但是有些特殊情况需要选择另个表作为驱动表,比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序,在STRAIGHT_JOIN左边的表名就是驱动表,右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接,也就是inner join。其他链接不推荐使用STRAIGHT_JOIN,否则可能造成查询结果不准确。

    这个方式有时能减少3倍的时间

    展开全文
  • 本文我们来谈谈项目中常用的MySQL优化方法,共19条,具体如下:1、EXPLAIN做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据:type列,连接类型。一个...

    本文我们来谈谈项目中常用的MySQL优化方法,共19条,具体如下:

    1、EXPLAIN

    做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。

    下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。

    key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式。

    key_len列,索引长度。

    rows列,扫描行数。该值是个预估值。

    extra列,详细说明。注意,常见的不太友好的值,如下:Using filesort,Using temporary。

    2、SQL语句中IN包含的值不应过多

    MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select id from t where num in(1,2,3) 对于连续的数值,能用between就不要用in了;再或者使用连接来替换。

    3、SELECT语句务必指明字段名称

    SELECT*增加很多不必要的消耗(CPU、IO、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构发生改变时,前断也需要更新。所以要求直接在select后面接上字段名。

    4、当只需要一条数据的时候,使用limit 1

    这是为了使EXPLAIN中type列达到const类型

    5、如果排序字段没有用到索引,就尽量少排序

    6、如果限制条件中其他字段没有索引,尽量少用or

    or两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。很多时候使用union all或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

    7、尽量用union all代替union

    union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all的前提条件是两个结果集没有重复数据。

    8、不使用ORDER BY RAND()

    select id from `dynamic` order by rand() limit 1000;

    上面的SQL语句,可优化为:

    select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nidlimit 1000;

    9、区分in和exists、not in和not exists

    select * from 表A where id in (select id from 表B)

    上面SQL语句相当于

    select * from 表A where exists(select * from 表B where 表B.id=表A.id)

    区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

    关于not in和not exists,推荐使用not exists,不仅仅是效率问题,not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句?

    原SQL语句:

    select colname … from A表 where a.id not in (select b.id from B表)

    高效的SQL语句:

    select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

    取出的结果集如下图表示,A表不在B表中的数据:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    10、使用合理的分页方式以提高分页的效率

    select id,name from product limit 866613, 20

    使用上述SQL语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。

    优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。SQL可以采用如下的写法:

    select id,name from product where id> 866612 limit 20

    11、分段查询

    在一些用户选择页面中,可能一些用户选择的时间范围过大,造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序,分段进行查询,循环遍历,将结果合并处理进行展示。

    如下图这个SQL语句,扫描的行数成百万级以上的时候就可以使用分段查询:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    12、避免在where子句中对字段进行null值判断

    对于null的判断会导致引擎放弃使用索引而进行全表扫描。

    13、不建议使用%前缀模糊查询

    例如LIKE“%name”或者LIKE“%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

    那如何查询%name%?

    如下图所示,虽然给secret字段添加了索引,但在explain结果并没有使用:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    那么如何解决这个问题呢,答案:使用全文索引。

    在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like ‘%zhangsan%’; 。这样的语句,普通索引是无法满足查询需求的。庆幸的是在MySQL中,有全文索引来帮助我们。

    创建全文索引的SQL语法是:

    ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

    使用全文索引的SQL语句是:

    select id,fnum,fdst from dynamic_201606 where match(user_name) against(‘zhangsan’ in boolean mode);

    注意:在需要创建全文索引之前,请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。

    14、避免在where子句中对字段进行表达式操作

    比如:

    select user_id,user_project from user_base where age*2=36;

    中对字段就行了算术运算,这会造成引擎放弃使用索引,建议改成:

    select user_id,user_project from user_base where age=36/2;

    15、避免隐式类型转换

    where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换,建议先确定where中的参数类型。

    16、对于联合索引来说,要遵守最左前缀法则

    举列来说索引含有字段id、name、school,可以直接用id字段,也可以id、name这样的顺序,但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面。

    17、必要时可以使用force index来强制查询走某个索引

    有的时候MySQL优化器采取它认为合适的索引来检索SQL语句,但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。

    18、注意范围查询语句

    对于联合索引来说,如果存在范围查询,比如between、>、

    19、关于JOIN优化

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    LEFT JOIN A表为驱动表,INNER JOIN MySQL会自动找出那个数据少的表作用驱动表,RIGHT JOIN B表为驱动表。

    注意:

    1)MySQL中没有full join,可以用以下方式来解决:

    select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;

    2)尽量使用inner join,避免left join:

    参与联合查询的表至少为2张表,一般都存在大小之分。如果连接方式是inner join,在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表,但是left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。

    3)合理利用索引:

    被驱动表的索引字段作为on的限制字段。

    4)利用小表去驱动大表:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    从原理图能够直观的看出如果能够减少驱动表的话,减少嵌套循环中的循环次数,以减少 IO总量及CPU运算的次数。

    5)巧用STRAIGHT_JOIN:

    inner join是由MySQL选择驱动表,但是有些特殊情况需要选择另个表作为驱动表,比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序,在STRAIGHT_JOIN左边的表名就是驱动表,右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接,也就是inner join。其他链接不推荐使用STRAIGHT_JOIN,否则可能造成查询结果不准确。

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    这个方式有时能减少3倍的时间。

    客服微信:(id1234562011)本文链接:https://www.changchenghao.cn/n/174426.html

    版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 394062665@qq.com 举报,一经查实,本站将立刻删除。

    展开全文
  • mysql 客户端/服务端通信阶段 -》查询缓存阶段-》查询优化处理阶段-》查询执行引擎阶段-》返回客户端阶段。 定位慢SQL: 业务驱动:根据业务反馈来确定哪些sql可能出现问题。 测试驱动:通过测试确定哪些sql出现...

    MySQL运行机理

    在这里插入图片描述
    mysql 客户端/服务端通信阶段 -》查询缓存阶段-》查询优化处理阶段-》查询执行引擎阶段-》返回客户端阶段。

    定位慢SQL:

    • 业务驱动:根据业务反馈来确定哪些sql可能出现问题。

    • 测试驱动:通过测试确定哪些sql出现问题。

    • 慢查询日志:通过日志记录的方式查找执行效率慢的sql。

    慢日志查询配置:

    • 查看是否开启慢日志保存
      show variables like ‘slow_query_log’
    • 打开慢日志
      set global slow_query_log = on
    • 慢日志保存位置
      set global slow_query_log_file = ‘/var/lib/mysql/gupaoedu-slow.log’
    • 没有命中索引的是否要记录慢日志
      set global log_queries_not_using_indexes = on
    • 设置执行时间超过多少为慢日志
      set global long_query_time = 0.1 (秒)

    SQL优化

    最重要的就是优化SQL索引。

    SQL索引的好处
    • 提高查询效率

    • 降低CPU使用率。

    SQL索引的弊端
    • 当数据量很大的时候,索引也会很大(当然相比于源表来说,还是相当小的)
    • 索引并不适用于所有情况
      1、少量数据
      2、频繁进行改动的字段,不适合做索引
      3、很少使用的字段,不需要加索引
    • 索引会提高数据查询效率,但是会降低“增、删、改”的效率。

    索引相当于字典的目录
    利用字典目录查找汉字的过程,就相当于利用SQL索引查找某条记录的过程。

    索引是帮助MySQL高效获取数据的一种【数据结构】。MySQL中一般用的是【B+树】。

    B+树图示说明
    在这里插入图片描述
    最下面的第3层,属于叶子节点,真实数据部份都是存放在叶子节点当中的。第1、2层中的数据用于分割指针块儿的,比如说小于26的找P1,介于26-30之间的找P2,大于30的找P3。

    增加“节点数”可以使这三层【B+树】存放上百万条数据。

    【B+树】中查询任意数据的次数,都是n次,n表示的是【B+树】的高度。

    索引的增删改查

    索引分普通索引、唯一索引、复合索引,主键索引不能有null值,唯一索引可以有null值。

    • 创建普通索引(create、alter两种方式,下同)
      create index 索引名称 on 表名(字段名);alter table 表名 add index 索引名称(字段名);
    • 创建唯一索引
      create unique index 索引名称 on 表名(字段名);alter table 表名 add unique index 索引名称(字段名);
    • 创建复合索引
      create index 索引名称 on 表名(dept,name);alter table 表名 add index 索引名称(字段名,字段名2);
    • 索引删除和索引查询
      drop index 索引名称 on 表名;show index from 表名;

    执行计划explain

    explain+ SQL语句

    执行计划中的几个重要“关键字”

    id :编号
    select_type :查询类型
    table :表
    type :类型
    possible_keys :预测用到的索引
    key :实际使用的索引
    key_len :实际使用索引的长度
    ref :表之间的引用
    rows :通过索引查询到的数据量
    Extra :额外的信息

    关键字详解

    id关键字,id值越大越优先;id值相同,从上往下顺序执行。

    select_type:查询类型

    • simple:简单查询,不包含子查询,不包含union查询。
    • primary:包含子查询的主查询(最外层)
    • subquery:包含子查询的主查询(非最外层)
    • derived:衍生查询(用到了临时表)
    • union:union之后的表称之为union表,如上例 union result:告诉我们,哪些表之间使用了union查询

    type:访问类型,sql查询优化中一个很重要的指标,结果值从好到坏依次是:system > const > eq_ref > ref > range > index > ALL

    • system:表只有一行记录(等于系统表)
    • const类型的特例,基本不会出现,可以忽略不计const:表示通过索引一次就找到了,const用于比较primary key 或者 unique索引
    • eq_ref:唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描
    • ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质是也是一种索引访问
    • range:只检索给定范围的行,使用一个索引来选择行(至少要这个级别)
    • index:Full Index Scan,索引全表扫描,把索引从头到尾扫一遍
    • ALL:Full Table Scan,遍历全表以找到匹配的行

    possible_keys:查询过程中有可能用到的索引

    key:实际使用的索引,如果为NULL,则没有使用索引

    rows:根据表统计信息或者索引选用情况,大致估算出找到所需的记录所需要读取的行数。

    filtered:它指返回结果的行占需要读到的行(rows列的值)的百分比。表示返回结果的行数占需读取行数的百分比,filtered的值越大越好。

    Extra :额外信息

    • Using filesort :mysql对数据使用一个外部的文件内容进行了排序,而不是按照表内的索引进行排序读取 order by xxx desc这样子的,如果是索引字段的排序则不是这样的,就不需要使用外部文件了
    • Using temporary:使用临时表保存中间结果,也就是说mysql在对查询结果排序时使用了临时表,常见于order by 或 group by
    • Using index:表示相应的select操作中使用了覆盖索引(Covering Index),避免了访问表的数据行,效率高
    • Using where :使用了where过滤条件
    • select tables optimized away:基于索引优化MIN/MAX操作或者MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段在进行计算,查询执行。计划生成的阶段即可完成优化

    几个优化小技巧

    最佳字段做前缀,复合索引顺序和使用顺序一致,不要跨字段使用

    添加索引的时候,要根据MySQL解析顺序添加索引

    编写过程
    select dinstinct …from …join …on …where …group by …having …order by …limit …

    解析过程
    from … on… join …where …group by …having …select dinstinct …order by …limit …

    使用了in有时候会导致索引失效,尽量将in字段索引放在最后面。

    不要在索引上进行任何操作(计算、函数、类型转换),否则索引失效

    索引不能使用不等于(!= <>)或is null (is not null),否则自身以及右侧所有全部失效(针对大多数情况)。复合索引中如果有>,则自身和右侧索引全部失效。

    like尽量以“常量”开头,不要以’%'开头,否则索引失效

    select * from … where name like ‘x%’;

    尽量不要使用类型转换(显示、隐式),否则索引失效

    索引对列类型为varchar,但给定的值为数值,涉及隐式类型转换

    尽量不要使用or,否则索引失效

    exist in如果主查询的数据集大,则使用in关键字,效率高。如果子查询的数据集大,则使用exist关键字,效率高。

    select …from … where exist (子查询)
    select …from … where 字段 in (子查询)

    避免使用select * …(select后面写所有字段,也比写*效率高)

    使用select * 取出全部列,会让优化器无法完成索引覆盖扫描这类优化,会影响优化器对执行计划的选择,也会增加网络带宽消耗,更会带来额外的I/O,内存和CPU消耗。

    用where字句替换HAVING字句

    避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。

    展开全文
  • 查询速度慢的原因很多,常见如下几种 1、没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 2、I/O吞吐量小,形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度...
  • sql优化几种方法(面试必背)

    千次阅读 2019-12-16 15:09:14
    在sql查询中为了提高查询效率,我们常常会采取一些措施对查询语句进行sql优化,下面总结的一些方法,有需要的可以参考参考。 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上...
  • 做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。 下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据: 写SQL记住这19个优化原则,效率至少提高3倍 type列,连接类型。一个好的SQL语句至少要达到...
  • sql优化几种方式

    万次阅读 多人点赞 2018-11-05 10:20:46
    一、为什么要对SQL进行优化 我们开发项目上线初期,由于业务数据量相对较少,一些SQL的执行效率对程序运行效率的影响不太明显,而开发和运维人员也...二、SQL优化的一些方法 1.对查询进行优化,应尽量避免全表扫描...
  • 做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。 下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据: type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 key列,使用到...
  • oracle sql优化几种方法

    千次阅读 2018-07-19 14:41:00
    oracle在内部执行了许多工作,比如解析SQL语句, 估算索引的利用率, 读数据块等等,都将大量耗费oracle数据库的运行 2.选择最有效率的表名顺序,也将明显提升oracle的性能。oracle解析器是按照从右到左的顺序处理...
  • hivesql几种优化方法

    千次阅读 2022-04-05 11:01:52
    目前项目中由于数据量巨大,导致一个sql要跑一个多小时,所以找了些可以优化的点,记录下来,以后方便使用。 1.map的优化,job在map task的过程时间较长 set mapreduce.map.memory.mb=8240; set mapreduce.reduce....
  • sql优化的N种方法_持续更新

    千次阅读 2020-09-25 16:34:17
    sql优化的方式 1: explain关键字 :使用explain关键字来查看当前sql语句的执行情况,来对症下药.因为内容较多,放在最后进行讲解 2: 正确的建立索引:索引的建立对sql的影响是非常大的,如果对索引不太理解的可以看我...
  • 数据库和sql优化几种方式

    千次阅读 2019-04-16 14:15:47
    数据库和sql优化几种方式 什么是索引? 把数据根据一些算法算出一个值,把数据放入到一个区域内,这个值对应那个区域,来查询数据的时候,可以快速的根据数据值定位到某个区域,快速找到某个值。 比如字典,...
  • sql语句优化几种方法

    千次阅读 2018-10-27 16:41:44
    在sql查询中为了提高查询效率,我们常常会采取一些措施对查询语句进行sql优化,下面总结的一些方法,有需要的可以参考参考。 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上...
  • SQL优化方法有很多种,针对平时的情况总结一下几种: 以下用到的表和数据,可以通过这篇文章中的语句添加: MySQL执行计划explain的详解_蓝星部队的博客-CSDN博客一、如何查看SQL执行计划:在MySQL中生成执行...
  • 常用SQL优化方法

    万次阅读 多人点赞 2018-04-10 07:55:09
    2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 3、应尽量避免在 where 子句中对字段进行 null值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id...
  • 高手常用的15 SQL 优化

    千次阅读 2021-11-22 11:28:19
    很多时候,我们写SQL语句时,为了方便,喜欢直接使用select *,一次性查出表中所有列的数据。 反例: select * from user where id=1; 在实际业务场景中,可能我们真正需要使用的只有其中一两列。查了很多数据,...
  • 基于Oracle数据库的几种常见SQL优化策略.pdf
  • 基于Oracle数据库的几种常见SQL优化策略研究.pdf
  • sql查询优化7种方法

    千次阅读 2020-11-11 16:26:06
    1.优化的方向 1.如何尽可能利用上索引 2.先where以后再关联(减少关联的数据量) 3.子查询尽量不要放在被驱动表,有可能使用不到索引; left join时,尽量让实体表作为被驱动表。 能够直接多表关联的尽量直接关联,...
  • Oracle SQL语句之常见优化方法总结

    万次阅读 多人点赞 2017-07-09 16:17:39
    下面列举一些工作中常常会碰到的Oracle的SQL语句优化方法: 1、SQL语句尽量用大写的;  因为oracle总是先解析SQL语句,把小写的字母转换成大写的再执行。 2、使用表的别名:   当在SQL语句中连接多个表时, ...
  • SQL Server数据库优化几种方法.

    千次阅读 2014-06-26 09:49:59
    巧妙优化SQL Server数据库的几种方法,在实际操作中导致查询速度慢的原因有很多,其中最为常见有以下的几种:没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷)。  I/O吞吐量小,形成了瓶颈...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 154,512
精华内容 61,804
关键字:

sql优化常用的几种方法