精华内容
下载资源
问答
  • 数据库优化 - 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

    图片

     

     

    展开全文
  • sql优化的几种方式

    万次阅读 多人点赞 2018-11-05 10:20:46
    一、为什么要对SQL进行优化 我们开发项目上线初期,由于业务数据量相对较少,一些SQL的执行效率对程序运行效率的影响不太明显,而开发和运维人员也无法判断SQL对程序的运行效率有多大,故很少针对SQL进行专门的优化...

    一、为什么要对SQL进行优化

    我们开发项目上线初期,由于业务数据量相对较少,一些SQL的执行效率对程序运行效率的影响不太明显,而开发和运维人员也无法判断SQL对程序的运行效率有多大,故很少针对SQL进行专门的优化,而随着时间的积累,业务数据量的增多,SQL的执行效率对程序的运行效率的影响逐渐增大,此时对SQL的优化就很有必要。

    二、SQL优化的一些方法

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。    
        
    2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:    
    select id from t where num is null    
    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:    
    select id from t where num=0    
        
    3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。    
        
    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.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    
        
    6.下面的查询也将导致全表扫描:    
    select id from t where name like '%abc%'    
        
    7.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:    
    select id from t where num/2=100    
    应改为:    
    select id from t where num=100*2    
        
    8.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:    
    select id from t where substring(name,1,3)='abc'--name以abc开头的id    
    应改为:    
    select id from t where name like 'abc%'    
        
    9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。    
        
    10.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。    
        
    11.不要写一些没有意义的查询,如需要生成一个空表结构:    
    select col1,col2 into #t from t where 1=0    
    这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:    
    create table #t(...)    
        
    12.很多时候用 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)    
        
    13.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。    
        
    14.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,    
    因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。    
    一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。    
        
    15.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。    
    这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。    
        
    16.尽可能的使用 varchar 代替 char ,因为首先变长字段存储空间小,可以节省存储空间,    
    其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。    
        
    17.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。    
        
    18.避免频繁创建和删除临时表,以减少系统表资源的消耗。

    19.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。    
        
    20.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,    
    以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

    21.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。    
        
    22.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。    
        
    23.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

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

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

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

     

     

    展开全文
  • 数据库查询优化复盘-20条必备sql优化技巧

    万次阅读 多人点赞 2020-12-08 08:29:00
    长按识别下方二维码,即可"关注"公众号每天早晨,干货准时奉上!0、序言本文我们来谈谈项目中常用的 20 条 MySQL 优化方法,效率至少提高 3倍!具体如下:1、使⽤ ...

    长按识别下方二维码,即可"关注"公众号

    每天早晨,干货准时奉上!

    0、序言

    本文我们来谈谈项目中常用的 20 条 MySQL 优化方法,效率至少提高 3倍!

    具体如下:

    1、使⽤ EXPLAIN 分析 SQL 语句是否合理

    使⽤ EXPLAIN 判断 SQL 语句是否合理使用索引,尽量避免 extra 列出现:Using File Sort、Using Temporary 等。

    2、必须被索引

    重要SQL必须被索引:update、delete 的 where 条件列、order by、group by、distinct 字段、多表 join 字段。

    3、联合索引

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

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

    4、强制索引

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

    5、日期时间类型

    对于非标准的日期字段,例如字符串的日期字段,进行分区裁剪查询时会导致无法识辨,依旧走全表扫描。

    尽量使用 TIMESTAMEP 类型,因为其存储空间只需要 datetime 的一半。

    6、禁止使用 SELECT *

    SELECT 只获取必要的字段,禁止使用 SELECT *。这样能减少不必要的消耗(CPU、IO、内存、网络带宽),增加使用覆盖索引的可能性;当表结构发生改变时,表结构变更对前端程序基本无影响。

    7、避免出现某些字段

    SQL 中避免出现 now()rand()sysdate()current_user() 等不确定结果的函数。在语句级复制场景下,引起主从数据不一致;不确定值的函数,产生的 SQL 语句无法使用 QUERY CACHE。

    8、where 子句

    避免在 where 子句中对字段进行 null 值判断:对于 null 的判断会导致引擎放弃使用索引而进行全表扫描。

    避免在where子句中对字段进行表达式操作:因为对字段就行了算术运算,这会造成引擎放弃使用索引。

    9、like

    禁止使用 % 前导查询,例如:like “%abc”,⽆法利⽤到索引。

    在日常中你会发现全模糊匹配的查询,由于 MySQL 的索引是 B+ 树结构,所以当查询条件为全模糊时,例如 %AB%%AB,索引无法使用,这时需要通过添加其他选择度高的列或者条件作为一种补充,从而加快查询速度。仅AB%形式的可以避免通配符引起索引屏蔽。

    10、用 IN 代替 OR

    OR 两边的字段中,如果有一个不是索引字段,而其它条件也不是索引字段,会造成该查询不走索引的情况。很多时候都会使用 IN 进行替代,或者使用 union all 或者是 union(必要的时候)的方式来代替“or”也会得到更好的效果。但 SQL 语句中 IN 包含的值不宜过多,应少于 1000 个。过多会使随机 IO 增大,影响性能。

    使用 IN 是因为 MySQL 对其做了相应的优化,即将 IN 中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗比较大。

    再例如:

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

    对于连续的数值,能用 between 就不要用 in 了;再或者使用连接来替换。

    11、禁止使⽤负向查询

    禁止使⽤负向查询,例如:not in、!=、<>、not like。

    12、范围查询

    在对字符串类型的索引进行大于运算时,会导致全表扫描。所以应改为区间between区间范围运算。

    13、order by/group by

    另外 order by/group by 的 SQL 涉及排序,尽量在索引中包含排序字段,并让排序字段的排序顺序与索引列中的顺序相同,这样可以避免排序或减少排序次数。如果排序字段没有用到索引,就尽量少排序。

    14、禁止使用 order by rand()

    order by rand() 会为表增加几个伪列,然后用 rand() 函数为每一行数据计算 rand() 值,最后基于该行排序,这通常都会生成磁盘上的临时表,因此效率非常低。建议先使用 rand() 函数获得随机的主键值,然后通过主键获取数据。

    15、尽量用union all代替union

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

    16、减少与数据库交互

    尽量采用批量 SQL 语句,减少与数据库交互次数。

    获取⼤量数据时,建议分批次获取数据,每次获取数据少于 5000 条,结果集应⼩于 1M。

    17、复杂查询还是简单查询?

    不要用一个SQL解决所有事情,可以分步骤做,省时、易理解、优化。且 MySQL 也十分擅长处理短而简单的 SQL,总体耗时会更短,而且也不会产生臃肿的 SQL,让人难以理解和优化。

    拆分复杂 SQL 为多个 小SQL,避免⼤事务。简单的 SQL 容易使用到 MySQL 的 QUERY CACHE;减少锁表时间特别是 MyISAM;可以使用多核 CPU。

    18、删除全表数据

    delete from table_name;会产生大量 undo 和 redo 日志,执行时间很长,可采用 TRUNCATE TABLE tablename;

    19、字符集问题

    col_utf8mb4 = col_utf8 关联类型都是 varchar ,但字符集不同,无法使用索引。使用过程中要特别注意。

    20、count 优化

    这也是一个被面试中经常会问到的问题,对于下面的四条 SELECT 语句:

    select count(*) from table … ;
    
    select count(1) from table … ;
    
    select count(primary key) from table … ;
    
    select count(index key) from table …;
    

    哪一条的执行效率最高呢?这个问题需要具体问题具体分析,不能一概而论。这里举 SELECT count(1) 这条 SQL 为例。

    图片来源于:《拉勾教育专栏:高性能MySQL实战》

    优化前和优化后,执行效率相差2倍。就添加了一个索引。

    「优化思路」 : 是选择索引 key_len 最短的二级索引效率高,不要使用全表扫描(PK 聚族索引会全表扫描),因为索引 key_len 越短,读取页面越少,进而 IO_COST 越小。

    小结

    • 大量的更新/删除操作需要控制频度,例如:每秒操作2000行以下

    • 使用 prepared statement 和绑定变量,可以提升性能并避免 SQL 注入

    • 程序应有捕获 SQL 异常的处理机制,必要时通过 rollback 显示回滚

    • 尽量少使用 distinct、order by、group by、union 等 SQL,排序需求可以放到前端(分页的就不方便交给前端排序)。

    • 大事务或者长查询的需求根据业务特点拆分

    • 杜绝程序中在处理事务时夹杂 RPC,会造成资源长时间不释放。有很多锁超时、并发数上涨都是由于事务中有 RPC 造成的。

    • 关注软件本身的优化同时,也需要关注硬件的性能指标和优化,以及硬件的发展方向。MySQL 属于 IO 密集型的应用,对存储硬件的 IO 性能要求比较高,在高并发的场景中,建议使用 PCI-e。

    「重点总结一下」:SQL 的执行过程->查询优化器的工作原理->SQL 执行计划的解读->MySQL 慢查询日志和分析->SQL 常用的优化手段->SQL 编写规范->深入实际业务对数据库访问进行优化。

    参考:

    • 《数据库高效优化:架构、规范与SQL技巧》

    • 《拉勾教育专栏:高性能MySQL实战》

    -- END --

     | 更多精彩文章 -


    《Java学习-进阶手册》

    公众号后台回复「手册」获取资料

    展开全文
  • MYSQL优化大法1. SQL优化1.1 查询SQL执行频率1.2 定位低效率执行SQL1.3 explain分析执行计划1.3.1 explain之ID1.3.2 explain之select_type1.3.3 explain之select_table1.3.4 explain之type1.3.5 explain之key1.3.6 ...

    一位爱好技术的橘右京的哥哥橘左京

    1. SQL优化

    在项目的前期数据量一般较小,开发者更加注重功能实现,发生SQL性能问题可能性较小。随着时间的推移,数据量会越来越大,就可能就会出现SQL性能问题,所以必须要对有性能问题的SQL进行优化。

    1.1 查询SQL执行频率

    可通过命令查询数据库或存储引擎的执行频率,语句:show [ session / global ] status like ‘名称’ ;若不写查询级别默认为Session。
    释义:session(当前会话) global(全局,在这指的是数据库从启动到现在)

    1.查询当前数据库全局状态信息
    show global status like ‘Com_______’;
    在这里插入图片描述

    2.查询当前存储引擎状态信息
    show global status like ‘Innodb_rows_%’;
    在这里插入图片描述

    1.2 定位低效率执行SQL

    可以通过两种方式定位低效率SQL

    1) 慢查询日志:通过慢查询日志可以定位效率低的SQL语句;
    2) show processlist:上述慢查询日志需要在查询结束以后才记录,出现效率问题时并不能定位问题,可以用show processlist查询mysql当前的线程及线程状态,是否锁表等,可实时查看SQL执行情况,同时对一些锁表操作进行优化。
    在这里插入图片描述
    ID:用户登录Mysql时,系统分配"connection_id",可以使用函数connection_id()查看;
    User:显示当前用户,若不是"Root"用户,就只显示权限范围的SQL语句;
    Host:显示这个语句是从哪个主机哪个端口发送的;
    db:数据库名称;
    Command:显示当前连接的执行命令,一般为sleep(休眠)、query(查询)、connect(连接)等;
    Time:显示这个状态的持续时间
    State:显示当前连接SQL语句的某个状态,以查询为例,需要经过copying to tmp table、sorting result、sending data等状态才可以完成;
    Info:显示这条SQL语句是判断问题语句的重要依据;

    1.3 explain分析执行计划

    //通过以上方法定位到低效率SQL,可通过explain查询该Select语句执行信息,包含在SELECT语句执行过程中表如何连接和连接的顺序。
    explain select * from res_resource;
    

    在这里插入图片描述

    1.3.1 explain之ID

    ID字段是Select查询的序列号,有三种情况

    1. ID相同表示的是执行顺序从上到下;
     explain select * from sys_user su,sys_role sr,sys_user_role sur where su.user_id = sur.user_id and sr.role_id = sur.role_id;
    

    在这里插入图片描述

    1. ID值越大,加载的优先级越高;
    explain select * from sys_user su where su.user_id = (select sur.user_id from sys_user_role sur where sur.role_id = '1');
    

    在这里插入图片描述
    同时存在ID相同和不同,相同的可认为是一组,从上往下顺序执行。在所有组中ID值越大执行优先级越高。

    explain select * from sys_user su
     left join sys_user_role sur1 ON su.user_id = sur1.user_id
     where sur1.role_id = ( select role_id from sys_role sr where sr.role_id = '1' )
    

    在这里插入图片描述

    1.3.2 explain之select_type

    1)SIMPLE :执行最简单的MYSQL查询,查询中不包含子查询或UNION。
    在这里插入图片描述

    2)PRIMARY:查询中包含复杂的子查询,外层查询为该标记。
    在这里插入图片描述

    3)SUBQUERY:在SELECT 或WHERE 包含子查询
    在这里插入图片描述

    4)DERVIED:在From列表包含子查询,被标注Dervide(衍生)MYSQL会递归执行
    在这里插入图片描述

    5)UNION:若第二个SELECT语句出现在UNION之后,则标记为UNION;若UNION出现在子语句的子查询中,外层SELECT将标记为:Dervide
    在这里插入图片描述

    1.3.3 explain之select_table

    select_table这一列表示的是当前执行的SQL语句是关于哪个表

    1.3.4 explain之type

    type字段是访问类型

    1)NULL:MYSQL执行操作不访问任何一个表及索引。
    2)System:数据表中只有一条记录。
    3)const:根据主键或唯一索引字段查询出一条数据
    4)eq_ref:类似ref,使用唯一或主键联合查询得到一条结果
    5)ref:不使用唯一索引联查可能得到多条结果
    6)range:where 之后出现between,>、<、in等操作
    7)index:全表扫描得到的一个带有索引字段的结果
    8)ALL:全表扫描得到的结果

    1.3.5 explain之key

    1)possible_keys:可能有的索引
    2)key:实际应用的索引
    3)key_len:索引使用的最大字节数,非实际长度,不损失精确度的情况下越短越好

    1.3.6 explain之rows

    扫描行的数量

    1.3.7 explain之filterred

    查询到的行数占有总扫描行数的百分比

    1.3.8 explain之extra

    其他的额外的执行信息

    1)using filesort:表示MYSQL会使用一个外部的索引排序,而不会使用内部的,称为"文件排序";
    2)using temporay:使用临时表保存中间的结果,常见在order by 或 group by时;
    3)using index:表示相应的查询操作使用了覆盖索引,避免访问表的数据行内容,效率高。

    1.4 show profile分析SQL

    在MySql5.0.37版本及以上增加了对show profiles和show profile的支持,能够了解到时间耗费到哪去了。
    可通过select @@have_profiling;查询当前MYSQL是否支持profile;

    在这里插入图片描述

    YES表示当前环境支持profile;
    默认的profiling是关闭的,可以在会话窗口级别中将他开启, 先进行查询查看是否已开启

    在这里插入图片描述

    0表示未开启,1表示已开启,使用 set profiling=1; 进行开启
    在执行了多条SQL后,可以通过show profiles;查看SQL的执行时间及SQL信息;

    在这里插入图片描述

    而且可以对具体的SQL执行情况进行查看 show profile for query 2;

    在这里插入图片描述

    1.5 SQL语句的优化

    1.5.1 优化insert语句

    原:

    insert into sys_user values (1,‘小明’)
    insert into sys_user values (2,‘小刚’)

    优化后:

    insert into sys_user values (1,‘小明’), (2,‘小刚’)

    1.5.2 优化order by语句

    order by 有两个排序方式,分别为filesort和using index

    1)filesort:文件排序,通过不是对索引字段的返回字段进行排序,效率低;

    在这里插入图片描述

    2)using index:索引排序,通过有序索引扫描直接返回有序数据,不需要额外排序,效率较高;
    说明:想要避免产生filesort排序,①多字段排序要选择索引列 ②多字段排序不可一个正序一个倒序③多字段排序字段顺序要和索引顺序一致

    在这里插入图片描述

    总结:尽量减少额外操作,通过索引返回索引字段的有序数据,where条件和order by使用相同的索引,order by的索引字段顺序要和索引顺序相同,order by 多字段排序要同时使用正序或倒序,否则会造成额外操作。

    filesort的优化:

    在进行order by优化时能尽量避免filesort,但某些情况下,因为条件限制不能解决filesort,那么只能对filesort进行优化。MySql有两种排序方式:

    1)两次扫描法:在Mysql4.1之前都是使用该方式排序。首先根据条件取出排序字段和行指针信息,然后在排序区sort buffer排序,若排序区不够,则在临时表tempoary table中存储排序结果,完成排序后再根据指针回标读取数据,该操作会导致大量随机I/O操作;

    2)一次扫描法:一次性取出满足条件的所有字段,然后在排序区sort buffer排序后直接返回结果集,内存开销较大,但效率高。
    Mysql通过系统变量max_length_for_sort_data的大小和Query进行比较,来决定使用哪种扫描方式,若前者更大就选择一次扫描法,否则使用第一种。
    可以适当提高sort_buffer_size和max_length_for_sort_data系统变量,来增大排序区的大小,提高排序效率。

    1.5.3 优化group by 语句

    group by会进行先排序后分组的操作,若在SQL运用了聚合函数会先执行聚合函数,group by在实现中同样可运用索引来实现。

    在这里插入图片描述

    本条SQL使用了临时表和文件排序,两者都很慢,可以使用 order by null 取消group by时的排序。

    在这里插入图片描述

    这样就取消了文件排序,同样也可对排序字段建立索引增加查询速度。

    在这里插入图片描述

    1.5.4 优化嵌套子查询

    MySql在4.1之后支持子查询,使用SQL去等于另一个SQL的执行结果完成查询操作,可被更有效的(JOIN)代替。

    1.5.5 优化or语句

    对于or语句,若使用了索引,那么每个条件列都要使用索引,且不能使用复合索引,否则会失效。若无索引,则考虑增加索引。

    使用了主键索引进行筛选查询。

    在这里插入图片描述

    使用了主键ID与另一个普通字段进行or,结果索引失效。

    在这里插入图片描述

    使用union去代替or,这样避免了索引失效问题。

    在这里插入图片描述

    可以看到user表存在的索引。

    在这里插入图片描述

    判断条件都为联合索引,但是失效了。

    在这里插入图片描述

    使用联合索引的话查询字段要使用覆盖索引。

    在这里插入图片描述

    1.5.6 优化limit语句

    进行分页操作时,通过创建覆盖索引进行分页效率要高很多,若进行limit 7000000的操作会抛弃掉前面7000000条只执行后十条,效率很低。

    方法一:使用子查询关联排序再进行查询,效率提高很多。

    方法二:若主键是自增ID,使用某个位置的查询,但不可有断层。

    1.5.7 使用SQL提示

    SQL提示,是优化数据库的重要手段,可以在SQL语句加入人为的指令达到优化的目的。
    可以查看到共有两个索引,主键索引和联合索引。

    在这里插入图片描述

    若使用SQL提示,有可能会使用主键和联合索引,但只是用了主键索引。

    在这里插入图片描述

    1)use index
    使用在查询语句表名的后面,作用是建议Mysql使用人为推荐的索引。

    在这里插入图片描述

    2)Ignore index
    忽略掉某个索引,不让MYSQL使用。

    在这里插入图片描述

    3)force index
    强制使用特定的索引。

    在这里插入图片描述

    1.6 应用优化

    在生产环境中,由于数据库本身的性能局限,就必须要对前台的应用进行一些优化。

    1.6.1 使用连接池

    对于访问数据库来说,建立连接的代价是比较昂贵的。频繁的开关链接耗费大量资源,所以要建立数据库连接池,提高访问性能。

    1.6.2 减少对MySql的访问

    1)若一次能获取结果,不需要分多步。
    ① 需要获取姓名和年龄
    select name,age from sys_user;
    ② 之后的业务逻辑需要获取备注
    select remark from sys_user;
    ③ 完全可以合二为一
    select name,age,remark from sys_user;

    2)增加cache层
    缓存能提升MYSQL性能,可以使用Mybatis,Hibernate提供的一二级缓存,或者使用Redis。

    1.6.3 负载均衡

    可以通过负载均衡让负载量分摊到各个服务器上,降低单台MYSQL的压力,提升效率。

    1)利用MYSQL赋值分流查询
    通过主从复制来实现读写分离,使增删改走主节点,查询走子节点,从而降低服务器的压力。

    2)采用分布式数据库架构
    分布式数据架构是通过多台服务器分布数据,可以实现多台服务器的负载均衡,适合大数据量,有更好的拓展和可用性 。

    1.7 查询缓存优化 (MYSQL8已废弃)

    1.7.1 了解查询缓存

    概述:

    开启查询缓存后,当执行完全相同的SQL时,服务器直接从缓存读取结果,不会再进行查询,若数据被改变,则查询缓存失效,适用于增删改操作较少的表。
    在这里插入图片描述

    过程:

    1.客户端发送一条查询给服务器;
    2.服务器去查询缓存查看当前SQL是否命中缓存,若命中则返回,否则进入下一阶段;
    3.服务器对SQL进行解析、预处理,再由优化器生成执行计划;
    4.MYSQL根据优化器生成的执行计划,调用存储引擎的API来执行查询;
    5.将结果返回给客户端;

    1.7.2 查询缓存配置

    1)查看当前MYSQL是否支持查询缓存
    show variables like ‘hava_query_cache’;

    在这里插入图片描述

    2)查询当前MYSQL是否开启查询缓存
    show variables like ‘query_cache_type’;

    在这里插入图片描述

    3)查询缓存大小
    show variables like ‘query_cache_size’;

    在这里插入图片描述

    4)查询缓存状态
    show status like ‘Qcache%’;

    在这里插入图片描述

    Query_free_blocks:可用内存块数
    Query_free_memory:可用内存空间
    Query_hits:查询缓存的命中次数
    Query_inserts:添加到查询缓存的次数
    Query_lowmem_prunes:若内存空间不足,将数据移除的次数
    Query_not_cached:未走缓存查询的次数
    Query_queries_in_cache:查询缓存中注册的查询数
    Query_total_blocks:查询缓存中的总块数

    1.7.3 开启查询缓存

    查询缓存默认是关闭的,需要手动配置参数query_cache_type,来开启查询缓存,query_cache_type可取值有三个:

    1)OFF或0:查询缓存功能关闭
    2)OF或1:查询缓存功能开启,SELECT的结果符合缓存条件即会缓存,否则不予缓存,显示指定SQL_NO_CACHE,不与缓存。
    3)Demand 或2:查询缓存功能按需缓存,显示指定SQL_CACHE语句才会缓存,否则不予缓存。

    Liunx:my.conf 配置 query_cache_type=1
    Windows:my.ini配置query_cache_type=1

    1.7.4 查询缓存SELECT选项

    1)SQL_CACHE:若查询结果是可缓存的,并Query_cache_type系统变量值为ON或DEMAND,则缓存查询结果。
    select SQL_CACHE,id,name from sys_user;

    2)SQL_NO_CACHE:服务器不使用查询缓存,也不检查是否已缓存,也不检查查询结果。
    select SQL_NO_CACHE,id,name from sys_user;

    1.7.4 查询缓存失效的情况

    1)语句不一致;
    select * from sys_user;
    Select * from sys_user;

    2)语句有动态改变函数时,不缓存,如now(),curdate(),rand(),UUID();
    select * from sys_user where update_time > now();
    select user();
    select database();

    3)不使用任何表查询语句;
    select ‘A’;

    4)查询mysql,information_schema或performance_schema数据库表时,不会走缓存查询。
    select * from information_schema.engines;

    5)在存储函数,触发器或事件的主体执行的查询;

    6)更改表;

    1.8 MYSQL内存优化与管理

    1.8.1 内存优化原则

    1)尽量将更多的内存分配给MYSQL作为缓存,但要给操作系统和其他应用预留足够的内存空间。

    2)MyISam存储引擎读取数据文件依赖于操作系统和自身的IO,若有MyISAm表要预留足够的内存空间。

    3)排序区、连接区是分配给每个数据库会话专用的,默认值的大小要根据最大连接数合理分配,若太大就会浪费资源,而且并发连接较高会导致物理内存耗尽。

    1.8.2 MyISAm内存优化

    MyISAm存储引擎使用key_buffer缓存索引块来加快MyISam的读取速度,没有特别的缓存机制,完全依靠与操作系统的IO缓存。

    1)key_buffer_size:决定索引缓冲区的大小,直接影响存取速度,对于一般MyISAm数据库可将1/4分配给数据库,可在my.ini设置 key_buffer_size=512M

    2)read_buffer_size:若经常对表进行读取,可将其值增大,每个read_buffer_size是session级的,如果设置默认值太大,可能会造成内存浪费。

    3)read_rnd_buffer_size:对于查询MyISAm表需要做排序的,进行order by操作可以增大其值,来加快查询速度,每个read_rnd_buffer_size是session级的,如果设置默认值太大,可能会造成内存浪费。

    1.8.3 InnoDB内存优化

    innoDB使用一块内存区来做IO缓冲池,不仅缓存索引块,也缓存数据块。

    1)innodb_buffer_pool_size:其作用是调整最大缓冲区大小,在保证操作系统和其他软件正常运行后,设置的越大磁盘IO就越少,性能也就越高。
    innodb_buffer_pool_size=512M

    2)innodb_log_buffer_size: 决定了innodb重做日志缓存大小,对于可能产生日志记录的大事务,增加innodb_log_buffer_size,可以避免innodb在事务提交之前将不必要的日志写入磁盘操作。

    1.9 MYSQL并发参数调整

    MySql服务是多线程结构,包括后台线程和客户服务线程,多线程可以有效利用服务器资源,提升数据库的性能。在MySql中控制并发连接和线程的主要参数包括max_connetctions, back_log,table_open_cache, thread_cache_size ,innodb_lock_wait_timeout 。

    1) max_connetctions:MYSQL的最大连接数,默认值151,如果连接超过151,则会进入back_log进行等待前面的连接释放,一般的liunx能够支持500-1000问题不大。

    2)back_log:其参数控制MYSQL监听TCP端口时的积压请求栈大小,如果数量超过了max_connections的设置就会进入等待,若超过了back_log设置的数量大小,就会直接报错,5.6.6之前默认是20个,之后的版本是50+(max_connetcions / 5),最大不超过九百。若短时间内需要大量连接,可以考虑调整其大小。

    3)table_open_cache:该参数设置SQL语句执行可打开表缓存的数量,一条SQL至少操作一张表,最少使用一个表缓存,多个表需要多个。该参数的值由最大连接数和每个连接执行关联查询中表的最大个数来决定的。
    最大连接数 * N;

    4)thread_cache_size:为了加快连接数据库的速度,MYSQL会缓存一定数目的服务线程做备用,可通过其参数设置最大线程数大小,类似于JAVA的线程池。

    5)innodb_lock_wait_timeout:该参数用来设置Innodb等待行锁的时间,默认50S,若业务是需要快速反馈的,则需要将值调小,避免长时间等待;若业务是不需要快速反馈,则可以将值调大,以免发生回滚降低性能。

    展开全文
  • 多目标优化

    万次阅读 多人点赞 2018-09-14 11:40:57
    优化问题的分类 1)无约束和有约束条件; 2)确定性和随机性最优问题(变量是否确定); 3)线性优化与非线性优化(目标函数和约束条件是否线性); 4)静态规划和动态规划(解是否随时间变化)。 - 什么是...
  • 粒子群优化算法(PSO)

    万次阅读 多人点赞 2018-06-04 20:07:09
    粒子群优化算法(Partical Swarm Optimization PSO),粒子群中的每一个粒子都代表一个问题的可能解,通过粒子个体的简单行为,群体内的信息交互实现问题求解的智能性。由于PSO操作简单、收敛速度快,...
  • 优化算法之粒子群算法(PSO)

    万次阅读 多人点赞 2018-08-03 10:26:45
      粒子群优化算法(PSO:Particle swarm optimization) 是一种进化计算技术(evolutionary computation)。源于对鸟群捕食的行为研究。粒子群优化算法的基本思想:是通过群体中个体之间的协作和信息共享来寻找最优...
  • 优化算法】简述灰狼优化算法(GWO)原理

    万次阅读 多人点赞 2019-03-25 21:10:34
    系列优化算法简述: OP_1. 简述遗传算法(GA)原理 OP_2 简述灰狼优化算法(GWO)原理 前言: 灰狼优化算法(Grey Wolf Optimizer,GWO)由澳大利亚格里菲斯大学学者 Mirjalili 等人于2014年提出来的一种群智能...
  • 本文主要讲解性能优化中的内存优化,希望你们会喜欢 目录 1. 定义 优化处理 应用程序的内存使用、空间占用 2. 作用 避免因不正确使用内存 &amp; 缺乏管理,从而出现 内存泄露(ML)、内存...
  • 修复短信注册,换成阿里巴巴云短信 上传徽标和徽标链接的背景更改 后台登录密码不清楚 清除平台统计 删除不必要的功能,优化性能体验。在前台添加新的不需要验证的注册方法 消除不必要的功能,优化性能体验 修复...
  • 数据库优化——慢查询MySQL定位优化流程

    万次阅读 多人点赞 2021-05-19 11:07:25
    如何定位并优化慢查询SQL?如何使用慢查询日志?本文带来慢查询例子演示,新手都能看懂!那查询语句慢怎么办?explain带你分析sql执行计划!当主键索引、唯一索引、普通索引都存在,查询优化器如何选择?本文带你一...
  • 本文全面讲解性能优化中的所有知识,献上一份 Android性能优化的详细攻略, 含:优化方向、原因 &amp; 具体优化方案,希望你们会喜欢 文章较长,建议预留较长时间阅读 / 收藏 目录 1. 性能优化...
  • 优化学习 约束优化问题

    万次阅读 2021-06-02 00:01:47
    约束优化问题约束优化问题约束优化最优解的特征 约束优化问题 (P)min⁡f(x)(P) \min f(x)(P)minf(x)s.t. gi(x)⩽0,i=1,…ms.t. \text{ }g_{i}(x) \leqslant 0, i=1, \ldots \mathrm{m}s.t. gi​(x)⩽0,i=1...
  • 如何优化MySQL千万级大表,我写了6000字的解读

    万次阅读 多人点赞 2019-10-21 20:03:03
    千万级大表如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面的经验总结,也欢迎大家提出建议。 从一开始脑海里开始也是...
  • 4 Classification of optimization problem (IP: integer programming, MINLP: mixed integer non-linear programming, MILP: mixed integer linear programming, LP: linear ...其中,离散优化又称组合优化. me..
  • Android源码编译速度优化 -- ccache设置

    万次阅读 2021-02-04 16:32:23
    谷歌给出的指导方法(优化编译环境章节):点击进入 2. 方法 要想长久方便使用,需要将以下环境 变量写入/etc/profile或者~/.bashrc中 export USE_CCACHE=1 export CCACHE_DIR=/<path_of_your_choice>/.ccache ...
  • 优化学习 凸优化问题

    万次阅读 2021-05-29 00:35:07
    优化问题凸优化问题(convex problems)局部最优等同于全局最优(凸优化)x⋆∈Sx^{\star} \in Sx⋆∈S是最优解⇔∇f(x)T(x−x∗)⩾0,∀x∈S\Leftrightarrow \nabla f(x)^{T}\left(x-x^{*}\right) \geqslant 0 ,...
  • 此篇博客主要是参考《Android高级进阶》,关于Android开发过程性能优化中的电量优化,布局优化和网络优化做一个记录。
  • 数据库优化 - 实例优化

    千次阅读 2019-10-25 10:30:00
    从网上去搜数据库优化基本都是从SQL层次进行优化的,很少有提及到数据库本身的实例优化。就算有也都是基于某个特定数据库的实例优化,本文涵盖目前市面上所有主流数据库的实例优化(Oralce、MySQL、POSTGRES、达梦)...
  • 数据库自身优化 优化①:增加次数据文件,设置文件自动增长(粗略数据分区) 1.1:增加次数据文件  从SQL SERVER 2005开始,数据库不默认生成NDF数据文件,一般情况下有一个主数据文件(MDF)就够了,但是有些大型...
  • 单目标优化,多目标优化,数值优化,组合优化

    万次阅读 多人点赞 2018-06-04 17:37:11
    何为优化?措施: 对应方法变得更优: 对应的结果更加的好优化: 动词,一种行为方法-----------&gt;目的是获得更好的结果,总之有所改善优化问题的三要素:(1) 决策变量所变:可以改变的量,可以优化的量,...
  • Android内存优化之图片优化

    万次阅读 多人点赞 2019-04-08 11:52:23
    关于图片优化,大概如下 为什么要进行图片优化 相信大概刚开始学习Android的时候有过图片过大而直接报错的情况,下面简单介绍一下OOM问题,Android支持的图片格式及图片优化的几种方式 什么是OOM?:Android系统的...
  • Win10必做的性能优化

    万次阅读 多人点赞 2018-09-16 20:39:45
    Windows10系统目前慢慢成为主流的操作系统,win7目前已经不再提供技术支持,到2020年正式退役。...由于win10系统功能变多,系统也变得臃肿,下面就给大家讲讲Windows10必做的优化。 1.关闭家...
  • 智能优化算法:灰狼优化算法-附代码

    万次阅读 多人点赞 2020-07-31 16:31:41
    智能算法:灰狼优化算法-附代码 摘要:受 灰 狼 群 体 捕 食 行 为 的 启 发,Mirjalili等[1]于 2014年提出了一种新型群体智能优化算法:灰狼优化算法。GWO通过模拟灰狼群体捕食行为,基于狼群群体协作的机制来达到...
  • MySQL数据库:SQL优化与索引优化

    万次阅读 多人点赞 2018-11-25 02:36:55
    一、索引优化规则: 1、union、in、or 都能够命中索引,建议使用 in: (1)union:能够命中索引,并且MySQL耗费的CPU最少。 select * from doc where status=1 union all select * from doc where status=2; ...
  • sql优化的几种方法

    万次阅读 多人点赞 2017-08-17 20:59:55
    在sql查询中为了提高查询效率,我们常常会采取一些措施对查询语句进行sql优化,下面总结的一些方法,有需要的可以参考参考。 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上...
  • 优化算法——常见优化算法分类及总结

    万次阅读 多人点赞 2018-10-27 12:54:53
    之前做特征选择,实现过基于群智能算法进行最优化的搜索,看过一些群智能优化算法的论文,在此做一下总结。 最优化问题  在生活或者工作中存在各种各样的最优化问题,比如每个企业和个人都要考虑的一个问题“在...
  • MySQL优化系列17-MySQL SQL语句优化

    千次阅读 2021-07-21 14:05:49
    优化select语句1.1 where子句优化1.2 IS NULL 优化1.3 ORDER BY优化1.4 group by 优化1.4.1 宽松的索引扫描1.4.2 紧索引扫描1.5 distinct优化1.6 Limit优化1.7 避免全表扫描二.优化DML语句2.1 优化insert语句2.2 ...
  • 优化第四章凸优化问题 4.2凸优化

    千次阅读 2019-01-21 21:15:50
    4.2凸优化 标准形式的凸优化问题 局部最优解与全局最优解 可微函数的最优性准则 等价的凸问题 拟凸优化 标准形式的凸优化问题 是凸函数,等式约束是仿射函数。则此优化问题是凸优化问题。 也可以写成 ...
  • 文章目录1、低延时编码1.1 转码方面优化1.2 低延时软件编码器的优化1.3 针对RTC的Codec实现1.4 低延迟应用2、AV1技术生态2.1 AV1与云游戏 1、低延时编码 低延时:(1s~5s)游戏直播,秀场直播等 超低延时: <1s...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,208,316
精华内容 1,283,326
关键字:

优化