精华内容
下载资源
问答
  • 对超长sql优化体会
    千次阅读
    2016-11-04 09:26:30

            可能对于大多数人对于超长的sql优化,可能看sql去理解逻辑都要花很长时间,尤其在帮别人优化的过程中,在不理解业务的情况下更是无从下手,其实对于超长sql的优化,其实并没有想象中的那么难,对于那些超长sql,我们只需要明白,其中有特殊的操作,比如在sql中有没有视图,有没有子查询,有没有标量查询,这些都是一些要考虑的问题,这只是观察,并不需要我们去理解真正sql想表达的含义。当我们看完sql以后,接下来是去分析执行计划,通过我们观察的sql我们,可以想象到在执行计划中,如果有视图在执行计划中会发生什么,如果有只查询在执行计划中回发生什么,这些都是要能够想象到的。然后再根据返回的行数来确认,嵌套循环是否能用,考虑到这这么多因素以后,我认为优化长sql就不会那么难了。

          今天跟大家分享一个超长sql,但是具体内容我本能贴出来,我只能贴部分执行计划。sql的大概是两个表做hash,即id=2 和id=10 做关联。id=10为一个视图



     

    从执行计划可以看出,所以的时间都是花在id=10的视图中,而且id=1的视图最终返回一行结果,这个明显的可以让他走嵌套循环嘛,其实可以走嵌套循环为什么oracle没有选择走呢,按常规来说oracle在于视图做关联的时候默认会把谓词推入的,但是在这里没有。这里可能有两个原因一个id=10视图的原因,一个是谓词推入的开关关掉了。在查看设置以后是开关关掉了,开启开关了20s就把这个sql给跑完了。所以sql也就有话了。

          

    更多相关内容
  • SQL优化 SQL优化软件 SQL优化工具 很好用的工具,可以分析优化TSQL语句,oracle数据库语句优化工具
  • 本文我们来谈谈项目中常用的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 举报,一经查实,本站将立刻删除。

    展开全文
  • SQL语句执行的过程、ORACLE优化器 ,表之间的关联,如何得到SQL执行计划,如何分析执 行计划等内容,从而由浅到深的方式了解SQL优化的过 程,使大家逐步掌握SQL优化
  • 本书是作者十年磨一剑的成果之一,深入分析与解剖oracle sql优化与调优技术,主要内容包括: 第一篇“执行计划”详细介绍各种执行计划的含义与操作,为后面的深入分析打下基础。重点讲解执行计划在sql语句执行的...
  • 数据库优化 - SQL优化

    万次阅读 多人点赞 2019-11-01 21:00:00
    以实际SQL入手,带你一步一步走上SQL优化之路!
    前面一篇文章从实例的角度进行数据库优化,通过配置一些参数让数据库性能达到最优。但是一些“不好”的SQL也会导致数据库查询变慢,影响业务流程。本文从SQL角度进行数据库优化,提升SQL运行效率。

    判断问题SQL

    判断SQL是否有问题时可以通过两个表象进行判断:

    • 系统级别表象
      • CPU消耗严重
      • IO等待严重
      • 页面响应时间过长
      • 应用的日志出现超时等错误

    可以使用sar命令,top命令查看当前系统状态。

    也可以通过Prometheus、Grafana等监控工具观察系统状态。(感兴趣的可以翻看我之前的文章)
    640?wx_fmt=png

    • SQL语句表象
      • 冗长
      • 执行时间过长
      • 从全表扫描获取数据
      • 执行计划中的rows、cost很大

    冗长的SQL都好理解,一段SQL太长阅读性肯定会差,而且出现问题的频率肯定会更高。更进一步判断SQL问题就得从执行计划入手,如下所示:640?wx_fmt=png

    执行计划告诉我们本次查询走了全表扫描Type=ALL,rows很大(9950400)基本可以判断这是一段"有味道"的SQL。

    获取问题SQL

    不同数据库有不同的获取方法,以下为目前主流数据库的慢查询SQL获取工具

    • MySQL

      • 慢查询日志
      • 测试工具loadrunner
      • Percona公司的ptquery等工具
    • Oracle

      • AWR报告
      • 测试工具loadrunner等
      • 相关内部视图如v$、$session_wait等
      • GRID CONTROL监控工具
    • 达梦数据库

      • AWR报告
      • 测试工具loadrunner等
      • 达梦性能监控工具(dem)
      • 相关内部视图如v$、$session_wait等

    SQL编写技巧

    SQL编写有以下几个通用的技巧:

    • 合理使用索引

    索引少了查询慢;索引多了占用空间大,执行增删改语句的时候需要动态维护索引,影响性能 选择率高(重复值少)且被where频繁引用需要建立B树索引;

    一般join列需要建立索引;复杂文档类型查询采用全文索引效率更好;索引的建立要在查询和DML性能之间取得平衡;复合索引创建时要注意基于非前导列查询的情况

    • 使用UNION ALL替代UNION

    UNION ALL的执行效率比UNION高,UNION执行时需要排重;UNION需要对数据进行排序

    • 避免select * 写法

    执行SQL时优化器需要将 * 转成具体的列;每次查询都要回表,不能走覆盖索引。

    • JOIN字段建议建立索引

    一般JOIN字段都提前加上索引

    • 避免复杂SQL语句

    提升可阅读性;避免慢查询的概率;可以转换成多个短查询,用业务端处理

    • 避免where 1=1写法

    • 避免order by rand()类似写法

    RAND()导致数据列被多次扫描

    SQL优化

    执行计划

    完成SQL优化一定要先读执行计划,执行计划会告诉你哪些地方效率低,哪里可以需要优化。我们以MYSQL为例,看看执行计划是什么。(每个数据库的执行计划都不一样,需要自行了解)explain sql640?wx_fmt=png

    字段解释
    id每个被独立执行的操作标识,标识对象被操作的顺序,id值越大,先被执行,如果相同,执行顺序从上到下
    select_type查询中每个select 字句的类型
    table被操作的对象名称,通常是表名,但有其他格式
    partitions匹配的分区信息(对于非分区表值为NULL)
    type连接操作的类型
    possible_keys可能用到的索引
    key优化器实际使用的索引(最重要的列) 从最好到最差的连接类型为consteq_regrefrangeindexALL。当出现ALL时表示当前SQL出现了“坏味道”
    key_len被优化器选定的索引键长度,单位是字节
    ref表示本行被操作对象的参照对象,无参照对象为NULL
    rows查询执行所扫描的元组个数(对于innodb,此值为估计值)
    filtered条件表上数据被过滤的元组个数百分比
    extra执行计划的重要补充信息,当此列出现Using filesort , Using temporary 字样时就要小心了,很可能SQL语句需要优化

    接下来我们用一段实际优化案例来说明SQL优化的过程及优化技巧。

    优化案例

    • 表结构

      CREATE TABLE `a`
      (
          `id`          int(11) NOT NULLAUTO_INCREMENT,
          `seller_id`   bigint(20)                                       DEFAULT NULL,
          `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
          `gmt_create`  varchar(30)                                      DEFAULT NULL,
          PRIMARY KEY (`id`)
      );
      CREATE TABLE `b`
      (
          `id`          int(11) NOT NULLAUTO_INCREMENT,
          `seller_name` varchar(100) DEFAULT NULL,
          `user_id`     varchar(50)  DEFAULT NULL,
          `user_name`   varchar(100) DEFAULT NULL,
          `sales`       bigint(20)   DEFAULT NULL,
          `gmt_create`  varchar(30)  DEFAULT NULL,
          PRIMARY KEY (`id`)
      );
      CREATE TABLE `c`
      (
          `id`         int(11) NOT NULLAUTO_INCREMENT,
          `user_id`    varchar(50)  DEFAULT NULL,
          `order_id`   varchar(100) DEFAULT NULL,
          `state`      bigint(20)   DEFAULT NULL,
          `gmt_create` varchar(30)  DEFAULT NULL,
          PRIMARY KEY (`id`)
      );
      
    • 三张表关联,查询当前用户在当前时间前后10个小时的订单情况,并根据订单创建时间升序排列,具体SQL如下

      select a.seller_id,
             a.seller_name,
             b.user_name,
             c.state
      from a,
           b,
           c
      where a.seller_name = b.seller_name
        and b.user_id = c.user_id
        and c.user_id = 17
        and a.gmt_create
          BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
          AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
      order by a.gmt_create;
      
    • 查看数据量  

      640?wx_fmt=png

    • 原执行时间
      640?wx_fmt=png

    • 原执行计划
      640?wx_fmt=png

    • 初步优化思路

    1. SQL中 where条件字段类型要跟表结构一致,表中 user_id 为varchar(50)类型,实际SQL用的int类型,存在隐式转换,也未添加索引。将b和c表 user_id 字段改成int类型。
    2. 因存在b表和c表关联,将b和c表 user_id创建索引
    3. 因存在a表和b表关联,将a和b表 seller_name字段创建索引
    4. 利用复合索引消除临时表和排序

    初步优化SQL

    alter table b modify `user_id` int(10) DEFAULT NULL;
    alter table c modify `user_id` int(10) DEFAULT NULL;
    alter table c add index `idx_user_id`(`user_id`);
    alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
    alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);
    

    查看优化后执行时间

    640?wx_fmt=png

    查看优化后执行计划
    640?wx_fmt=png

    查看warnings信息
    640?wx_fmt=png

    继续优化alter table a modify "gmt_create" datetime DEFAULT NULL;

    查看执行时间

    640?wx_fmt=png

    查看执行计划
    640?wx_fmt=png

    总结

    1. 查看执行计划 explain
    2. 如果有告警信息,查看告警信息 show warnings;
    3. 查看SQL涉及的表结构和索引信息
    4. 根据执行计划,思考可能的优化点
    5. 按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
    6. 查看优化后的执行时间和执行计划
    7. 如果优化效果不明显,重复第四步操作
     

    系列文章

     
     

    温馨提示

    如果你喜欢本文,请关注我的个人公众号!或者关注我的个人博客www.javadaily.cn

    图片

     

     

    展开全文
  • 深入揭示OracleSQL优化与调优的原理、核心技术与思想方法 盖国强鼎力推荐! Oracle数据库的性能优化直接关系到系统的运行效率,而影响数据库性能的一个重要因素就是SQL性能问题。本书是作者十年磨一剑的成果之一...
  • MySql基础知识总结(SQL优化篇)

    万次阅读 多人点赞 2021-07-10 12:26:26
    2、添加索引 3、更改索引顺序 4、去掉in 5、小结 四、双表sql优化 1、建表语句 2、左连接查询 3、小结 五、避免索引失效的一些原则 六、一些其他的优化方法 1、exist和in 2、order by 优化 七、sql顺序 -> 慢日志...

    🍅 作者简介:CSDN2021博客之星亚军🏆、新星计划导师✌、博客专家💪

    🍅 哪吒多年工作总结:Java学习路线总结,搬砖工逆袭Java架构师

    🍅 关注公众号【哪吒编程】,回复1024,获取Java学习路线思维导图、大厂面试真题、加入万粉计划交流群、一起学习进步

    目录

    一、explain返回列简介

    1、type常用关键字

    2、Extra常用关键字

    二、触发索引代码实例

    1、建表语句 + 联合索引

    2、使用主键查询

    3、使用联合索引查询

    4、联合索引,但与索引顺序不一致

    5、联合索引,但其中一个条件是 >

    6、联合索引,order by

    三、单表sql优化

    1、删除student表中的联合索引。

    2、添加索引

    3、更改索引顺序

    4、去掉in

    5、小结

    四、双表sql优化

    1、建表语句

    2、左连接查询 

    3、小结

    五、避免索引失效的一些原则

    六、一些其他的优化方法

    1、exist和in

    2、order by 优化

    七、sql顺序 -> 慢日志查询

    1、慢查询日志

    2、阈值

    八、慢查询日志 --> mysqldumpslow工具

    1、mysqldumpslow工具

    2、查询不同条件下的慢sql

    九、分析海量数据

    1、show profiles

    2、精确分析,sql诊断

    3、全局查询日志

    十、锁机制详解

    1、操作分类

    2、操作范围

    3、加读锁,代码实例

    4、加写锁

    5、MyISAM表级锁的锁模式

    6、MyISAM分析表锁定

    7、InnoDB分析表锁定

    8、加行锁代码实例

    9、行锁的注意事项


    本篇是MySQL知识体系总结系列的第二篇,该篇的主要内容是通过explain逐步分析sql,并通过修改sql语句与建立索引的方式对sql语句进行调优,也可以通过查看日志的方式,了解sql的执行情况,还介绍了MySQL数据库的行锁和表锁。

    一、explain返回列简介

    1、type常用关键字

    system > const > eq_ref > ref > range > index > all。

    1. system:表仅有一行,基本用不到;
    2. const:表最多一行数据配合,主键查询时触发较多;
    3. eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型;
    4. ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取;
    5. range:只检索给定范围的行,使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range;
    6. index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小;
    7. all:全表扫描;

    实际sql优化中,最后达到ref或range级别。

    2、Extra常用关键字

    Using index:只从索引树中获取信息,而不需要回表查询;

    Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。需要回表查询。

    Using temporary:mysql常建一个临时表来容纳结果,典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时;

    索引原理及explain用法请参照前一篇:MySQL索引原理,explain详解

    二、触发索引代码实例

    1、建表语句 + 联合索引

    CREATE TABLE `student` (
      `id` int(10) NOT NULL,
      `name` varchar(20) NOT NULL,
      `age` int(10) NOT NULL,
      `sex` int(11) DEFAULT NULL,
      `address` varchar(100) DEFAULT NULL,
      `phone` varchar(100) DEFAULT NULL,
      `create_time` timestamp NULL DEFAULT NULL,
      `update_time` timestamp NULL DEFAULT NULL,
      `deleted` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `student_union_index` (`name`,`age`,`sex`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    2、使用主键查询

    3、使用联合索引查询

    4、联合索引,但与索引顺序不一致

    备注:因为mysql优化器的缘故,与索引顺序不一致,也会触发索引,但实际项目中尽量顺序一致。

    5、联合索引,但其中一个条件是 >

    6、联合索引,order by

    where和order by一起使用时,不要跨索引列使用。

    三、单表sql优化

    1、删除student表中的联合索引。

    2、添加索引

    alter table student add index student_union_index(name,age,sex);

    优化一点,但效果不是很好,因为type是index类型,extra中依然存在using where。

    3、更改索引顺序

    因为sql的编写过程

    select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

    解析过程

    from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

     因此我怀疑是联合索引建的顺序问题,导致触发索引的效果不好。are you sure?试一下就知道了。

    alter table student add index student_union_index2(age,sex,name);

    删除旧的不用的索引:

    drop index student_union_index on student

    索引改名

    ALTER TABLE student RENAME INDEX student_union_index2 TO student_union_index

    更改索引顺序之后,发现type级别发生了变化,由index变为了range。

    range:只检索给定范围的行,使用一个索引来选择行。

    备注:in会导致索引失效,所以触发using where,进而导致回表查询。

    4、去掉in

    ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取;

    index 提升为ref了,优化到此结束。

    5、小结

    1. 保持索引的定义和使用顺序一致性;
    2. 索引需要逐步优化,不要总想着一口吃成胖子;
    3. 将含in的范围查询,放到where条件的最后,防止索引失效;

    四、双表sql优化

    1、建表语句

    CREATE TABLE `student` (
      `id` int(10) NOT NULL,
      `name` varchar(20) NOT NULL,
      `age` int(10) NOT NULL,
      `sex` int(11) DEFAULT NULL,
      `address` varchar(100) DEFAULT NULL,
      `phone` varchar(100) DEFAULT NULL,
      `create_time` timestamp NULL DEFAULT NULL,
      `update_time` timestamp NULL DEFAULT NULL,
      `deleted` int(11) DEFAULT NULL,
      `teacher_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    CREATE TABLE `teacher` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(100) DEFAULT NULL,
      `course` varchar(100) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    2、左连接查询 

    explain select s.name,t.name from student s left join teacher t on s.teacher_id = t.id where t.course = '数学'

    上一篇介绍过,联合查询时,小表驱动大表。小表也称为驱动表。其实就相当于双重for循环,小表就是外循环,第二张表(大表)就是内循环。

    虽然最终的循环结果都是一样的,都是循环一样的次数,但是对于双重循环来说,一般建议将数据量小的循环放外层,数据量大的放内层,这是编程语言的优化原则。

    再次代码测试:

    student数据:四条

    teacher数据:三条

    按照理论分析,teacher应该为驱动表。

    sql语句应该改为:

    explain select teacher.name,student.name from teacher left join student on teacher.id = student.id  where teacher.course = '数学'

    优化一般是需要索引的,那么此时,索引应该怎么加呢?往哪个表上加索引?

    索引的基本理念是:索引要建在经常使用的字段上。

    on teacher.id = student.id可知,teacher表的id字段使用较为频繁。

    left join on,一般给左表加索引;因为是驱动表嘛。

    alter table teacher add index teacher_index(id);
    alter table teacher add index teacher_course(course);

    备注:如果extra中出现using join buffer,表明mysql底层觉得sql写的太差了,mysql加了个缓存,进行优化了。

    3、小结

    1. 小表驱动大表
    2. 索引建立在经常查询的字段上
    3. sql优化,是一种概率层面的优化,是否实际使用了我们的优化,需要通过explain推测。

    五、避免索引失效的一些原则

    1、复合索引,不要跨列或无序使用(最佳左前缀);

    2、符合索引,尽量使用全索引匹配;

    3、不要在索引上进行任何操作,例如对索引进行(计算、函数、类型转换),索引失效;

    4、复合索引不能使用不等于(!=或<>)或 is null(is not null),否则索引失效;

    5、尽量使用覆盖索引(using index);

    6、like尽量以常量开头,不要以%开头,否则索引失效;如果必须使用%name%进行查询,可以使用覆盖索引挽救,不用回表查询时可以触发索引;

    7、尽量不要使用类型转换,否则索引失效;

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

    六、一些其他的优化方法

    1、exist和in

    select name,age from student exist/in (子查询);

    如果主查询的数据集大,则使用in;

    如果子查询的数据集大,则使用exist;

    2、order by 优化

    using filesort有两种算法:双路排序、双路排序(根据IO的次数)

    MySQL4.1之前,默认使用双路排序;双路:扫描两次磁盘(①从磁盘读取排序字段,对排序字段进行排序;②获取其它字段)。

    MySQL4.1之后,默认使用单路排序;单路:只读取一次(全部字段),在buffer中进行排序。但单路排序会有一定的隐患(不一定真的是只有一次IO,有可能多次IO)。

    注意:单路排序会比双路排序占用更多的buffer。

    单路排序时,如果数据量较大,可以调大buffer的容量大小。

    set max_length_for_sort_data = 1024;单位是字节byte。

    如果max_length_for_sort_data值太低,MySQL底层会自动将单路切换到双路。

    太低指的是列的总大小超过了max_length_for_sort_data定义的字节数。

    提高order by查询的策略:

    1. 选择使用单路或双路,调整buffer的容量大小;
    2. 避免select * from student;(① MySQL底层需要对*进行翻译,消耗性能;② *永远不会触发索引覆盖 using index);
    3. 符合索引不要跨列使用,避免using filesort;
    4. 保证全部的排序字段,排序的一致性(都是升序或降序);

    七、sql顺序 -> 慢日志查询

    慢查询日志就是MySQL提供的一种日志记录,用于记录MySQL响应时间超过阈值的SQL语句(long_query_time,默认10秒) ;

    慢日志默认是关闭的,开发调优时打开,最终部署时关闭。

    1、慢查询日志

    (1)检查是否开启了慢查询日志:

    show variables like '%slow_query_log%'

    (2)临时开启:

    set global slow_query_log = 1;

    (3)重启MySQL: 

    service mysql restart;

    (4)永久开启:

    /etc/my.cnf中追加配置:

    放到[mysqld]下:

    slow_query_log=1
    
    slow_query_log_file=/var/lib/mysql/localhost-slow.log

    2、阈值

    (1)查看默认阈值:

    show variables like '%long_query_time%'

    (2)临时修改默认阈值:

    set global long_query_time = 5;

    (3)永久修改默认阈值:

    /etc/my.cnf中追加配置:

    放到[mysqld]下:

    long_query_time = 5;

    (4)MySQL中的sleep:

    select sleep(5);

    (5)查看执行时间超过阈值的sql:

    show global status like '%slow_queries%';

    八、慢查询日志 --> mysqldumpslow工具

    1、mysqldumpslow工具

    慢查询的sql被记录在日志中,可以通过日志查看具体的慢sql。

    cat /var/lib/mysql/localhost-slow.log

    通过mysqldumpslow工具查看慢sql,可以通过一些过滤条件,快速查出需要定位的慢sql。

    mysqldumpslow --help

    参数简要介绍:

    s:排序方式

    r:逆序

    l:锁定时间

    g:正则匹配模式

    2、查询不同条件下的慢sql

    (1)返回记录最多的3个SQL

    mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

    (2)获取访问次数最多的3个SQL

    mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

    (3)按时间排序,前10条包含left join查询语句的SQL

    mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

    九、分析海量数据

    1、show profiles

    打开此功能:set profiling = on;

    show profiles会记录所有profileing打来之后,全部SQL查询语句所花费的时间。

    缺点是不够精确,确定不了是执行哪部分所消耗的时间,比如CPU、IO。

    2、精确分析,sql诊断

    show profile all for query  上一步查询到的query_id。

    3、全局查询日志

    show variables like '%general_log%'

    开启全局日志:

    set global general_log = 1;

    set global log_output = table;

    十、锁机制详解

    1、操作分类

    读写:对同一个数据,多个读操作可以同时进行,互不干扰。

    写锁:如果当前写操作没有完毕,则无法进行其它的读写操作。

    2、操作范围

    表锁:一次性对一张表整体加锁。

    如MyISAM存储引擎使用表锁,开销小、加锁快、无死锁;但锁的范围大,容易发生冲突、并发度低。

    行锁:一次性对一条数据加锁。

    如InnoDB存储引擎使用的就是行锁,开销大、加锁慢、容易出现死锁;锁的范围较小,不易发生锁冲突,并发度高(很小概率发生高并发问题:脏读、幻读、不可重复读)

    lock table 表1 read/write,表2 read/write,...

    查看加锁的表:

    show open tables;

    3、加读锁,代码实例

    会话0:
    lock table student read;
    select * from student; --查,可以
    delete from student where id = 1;--增删改,不可以
    
    select * from user; --查,不可以
    delete from user where id = 1;--增删改,不可以
    

    如果某一个会话对A表加了read锁,则该会话可以对A表进行读操作、不能进行写操作。即如果给A表加了读锁,则当前会话只能对A表进行读操作,其它表都不能操作

    会话1:
    select * from student; --查,可以
    delete from student where id = 1;--增删改,会“等待”会话0将锁释放
    
    会话1:
    select * from user; --查,可以
    delete from user where id = 1;--增删改,可以
    
    

    会话0给A表加了锁,其它会话的操作①可以对其它表进行读写操作②对A表:读可以,写需要等待释放锁。

    4、加写锁

    会话0:
    lock table student write;

    当前会话可以对加了写锁的表,可以进行任何增删改查操作;但是不能操作其它表;

    其它会话:

    对会话0中对加写锁的表,可以进行增删改查的前提是:等待会话0释放写锁。

    5、MyISAM表级锁的锁模式

    MyISAM在执行查询语句前,会自动给涉及的所有表加读锁,在执行增删改前,会自动给涉及的表加写锁。

    所以对MyISAM表进行操作,会有如下情况发生:

    (1)对MyISAM表的读操作(加读锁),不会阻塞其它会话(进程)对同一表的读请求。但会阻塞对同一表的写操作。只有当读锁释放后,才会执行其它进程的写操作。

    (2)对MyISAM表的写操作(加写锁),会阻塞其它会话(进程)对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。

    6、MyISAM分析表锁定

    查看哪些表加了锁:

    show open tables;1代表被加了锁

    分析表锁定的严重程度:

    show status like 'table%';

    Table_locks_immediate:可能获取到的锁数

    Table_locks_waited:需要等待的表锁数(该值越大,说明存在越大的锁竞争)

    一般建议:Table_locks_immediate/Table_locks_waited > 5000,建议采用InnoDB引擎,否则采用MyISAM引擎。

    7、InnoDB分析表锁定

    为了研究行锁,暂时将自动commit关闭,set autocommit = 0;

    show status like '%innodb_row_lock%';

    Innodb_row_lock_current_waits:当前正在等待锁的数量
    Innodb_row_lock_time:等待总时长。从系统启动到现在一共等待的时间
    Innodb_row_lock_time_avg:平均等待时长。从系统启动到现在一共等待的时间
    Innodb_row_lock_time_max:最大等待时长。从系统启动到现在一共等待的时间
    Innodb_row_lock_waits:等待次数。从系统启动到现在一共等待的时间

    8、加行锁代码实例

    (1)查询student

    select id,name,age from student

    (2)更新student 

    update student set age = 18 where id = 1

    (3)加行锁 

    通过select id,name,age from student for update;给查询加行锁。

    依旧修改成功,原因是MySQL默认是自动提交的,因此需要暂时将自动commit关闭

    set autocommit = 0;

    9、行锁的注意事项

    (1)如果没有索引,行锁自动转为表锁。

    (2)行锁只能通过事务解锁。

    (3)InnoDB默认采用行锁

    优点:并发能力强,性能高,效率高

    缺点:比表锁性能损耗大

    高并发用InnoDb,否则用MyISAM。

    🍅 作者简介:CSDN2021博客之星亚军🏆、新星计划导师✌、博客专家💪

    🍅 哪吒多年工作总结:Java学习路线总结,搬砖工逆袭Java架构师

    🍅 关注公众号【哪吒编程】,回复1024,获取Java学习路线思维导图、大厂面试真题、加入万粉计划交流群、一起学习进步

    关注公众号,回复1024,获取Java学习路线思维导图、加入万粉计划交流群

    展开全文
  • 大聪明教你学Java | 谈谈SQL优化

    万次阅读 2021-11-25 09:25:59
    SQL优化已经成为衡量程序猿优秀与否的硬性指标,甚至某些公司招聘时,在岗位职能上都有明码标注,这也就更证明了掌握SQL优化技能的重要性,借此机会就和大家分享一下我在优化SQL时的一些经验心得。 小弟献丑啦,嘿嘿...
  • sql优化的几种方式

    万次阅读 多人点赞 2018-11-05 10:20:46
    一、为什么要对SQL进行优化 我们开发项目上线初期,由于业务数据量相对较少,一些SQL的执行效率对程序运行效率的影响不太明显,而开发和运维人员也...二、SQL优化的一些方法 1.对查询进行优化,应尽量避免全表扫描...
  • SQL优化(一)、sql优化一般步骤

    万次阅读 多人点赞 2018-10-20 16:17:13
    sql优化一般步骤概要: 1 通过 show status 命令了解各种sql的执行频率 2 定位执行效率较低的sql语句 3 通过explain分析低效sql的执行计划 4 通过 show profile 分析sql 5 通过trace分析 优化器 如何选择执行...
  • SQL 优化推荐书单

    千次阅读 2018-05-26 15:11:39
    还有与之交互的软件,SQL 和 内嵌的语言 远古时期的数据库应用,只有少数科学家在上面跑批处理,瓶颈往往都是单个硬件组件,比如 CPU, 内存,IO. 大家都知道的是老式的硬盘,往往转速不够快,导致计算一直很缓慢,...
  • sql优化常用的几种方法

    千次阅读 2022-02-16 15:31:55
    做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。 下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据: type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 key列,使用...
  • SQL优化(五)索引长度

    千次阅读 2018-09-20 16:00:02
    SQL执行计划中,key_len 表示索引长度,经常用于判断复合索引是否被完全使用。先说结论: 在utf8编码方式下,一个字符占3个字节。 如果索引字段可以为null,MySQL会使用1个字节标识。 如果索引字段的类型长度可...
  • 史上最牛B的SQL优化培训文档

    热门讨论 2016-01-29 10:11:01
    史上最牛B的SQL优化文档,调优,优化SQL,只要2积分,大家快来下载吧。专家级的材料,实战级的材料
  • Oracle SQL性能优化 SQL优化

    万次阅读 2017-07-31 08:47:44
    (1) 选择最有效率的表名顺序(只在基于规则的优化器(Oracle有两种优化器:RBO基于规则的优化器和CBO基于成本的优化器)中有效): ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表...
  • SQL优化--SQL优化语句的一般步骤

    万次阅读 2018-07-25 15:55:59
    --SQL优化语句的一般步骤 --1.通过show status命令了解各种SQL的执行频率 --2.定位执行效率较低的SQL语句 --3.通过EXPLAIN分析较低SQL的执行计划 --4.通过show profile分析SQL --5.通过trace分析优化器如何选择...
  • oracle中慢sql优化思路

    千次阅读 2020-07-04 09:59:18
    参考资料:官方文档SQL Tuning Guide https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/sql-performance-fundamentals.html#GUID-DD9CAA74-3E0B-48C9-8770-AADB614BC992 Oracle Database 2...
  • sql优化

    千次阅读 2020-09-17 17:35:07
    具体优化方案 1.通过慢查询日志定位到具体那一条SQL 通过配置两个参数来输出慢查询sql: ①set global slow_query_log = on(开启慢查询日志) ②set global long_query_time = 1000(设置sql执行时间超过...
  • SQL优化(面试题)

    万次阅读 多人点赞 2019-06-13 22:21:12
    最近在面试的时候经常被问到SQL优化,总结如下: 1、在表中建立索引,优先考虑where、group by使用到的字段。 2、尽量避免使用select *,返回无用的字段会降低查询效率。如下: SELECT * FROM t 优化方式:...
  • 数据库sql优化

    万次阅读 多人点赞 2018-07-06 15:55:30
    网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。这篇文章我花费了大量的时间查找资料、修改、排版,希望大家阅读之后,感觉好的...
  • MySQL版SQL优化

    千人学习 2019-07-06 23:26:29
    本课程通过Centos版的MySQL讲解了SQL优化的一些常见手段。 其中讲解了MySQL的分层、存储引擎等底层逻辑,并讲解了常见的索引优化手段。在讲解时,先通过理论对先关的优化知识进行了铺垫,然后使用实际的案例详细的...
  • 常见的SQL优化

    万次阅读 多人点赞 2018-07-16 22:47:04
    SQL优化: 个人理解:主要是对查询的优化。对于sql的优化主要就是下面几个方面的优化, 1.避免全表扫描 2.避免索引失效 3.避免排序,不能避免,尽量选择索引排序 4.避免查询不必要的字段 5.避免临时表的创建,...
  • Sql优化总结!详细!(2021最新面试必问)

    万次阅读 多人点赞 2021-04-09 16:20:03
    Sql优化基础Sql优化查询SQL尽量不要使用select *,而是具体字段避免在where子句中使用or来连接条件使用varchar代替char尽量使用数值替代字符串类型查询尽量避免返回大量数据使用explain分析你SQL执行计划是否使用了...
  • 详解优化sql的过程(看完可以跟面试官正面对线)

    千次阅读 多人点赞 2021-10-19 10:37:12
    一般都逃不开数据库引擎的区别和如何优化sql的问题,关于数据库引擎的区别,我上一篇文章就很详细的写了有兴趣的可以去学习学习(Mysql引擎之间的区别),下面我们就一起来学习学习如何在面试中关于Sql优化问题与...
  • 记一次sql优化经历(优化in语句)

    万次阅读 2019-06-03 16:33:34
    业务背景: 根据客户群组查询标签,群组和标签的对应关系在tb_biz_type_tags中 根据标签查询客户的手机号和机构标识(tb_customer_tags) ...原始sql: select a.* from tb_account a where a.mobile in(...
  • SQL语句优化之like 的优化+性能优化

    千次阅读 2020-06-16 13:22:09
    开头和结尾都是%,对不起,很难优化 SQL> select * from test_like where object_name like '%%'; 以常量结束,直接写的时候是不能应用index的 SQL> select * from test_like where object_name like '.
  • 真·mysql中的SQL优化

    万次阅读 多人点赞 2018-08-01 18:15:42
    1. 优化SQL语句中的一般步骤 通过show status命令了解各种SQL的执行频率 定位执行效率较低的SQL语句 可以通过以下两种方式定位执行效率较低的SQL语句。 通过慢查询日志定位那些执行效率较低的SQL...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 589,668
精华内容 235,867
关键字:

长sql优化