• [MySQL] 常用SQL的优化--18.4


      这里介绍下,Insert、Group By等SQL语句的优化方法:

    1、大批量数据插入

    当load命令导入数据的时候,可以进行适当的设置提高导入速度。

    1.1 对于MyISAM表,可以先禁用非唯一索引更新,再导入数据来快速导入大量的数据。

      alter table table_name disable keys;

      load date infile 'xxx.txt' into table_name;

      alter table table_name enable keys;

    1.2 对于InnoDB表:

    1)因为InnoDB表必须有主键,表是按照主键的顺序保存的,所以将要导入的数据按照主键排序,

    可以有效地提高导入数据的效率。

    2)如果表中存在唯一性约束,则在导入数据前执行关闭唯一性校验,可以提高导入效率。

      set unique_checks=0;

      loading the data;

      set unique_checks=1;

    3) 如果应用使用自动提交的方式,建议在导入前执行set autocommit=0,关闭自动提交。

    这样不用每次insert都去磁盘写日志,节省I/O。

      set auto_commit=0;

      loading the data;

      set auto_commit=1;

    2、优化Insert语句

    1)如果是同一客户插入很多行,应尽量使用多个值表的Insert语句。(节省连接,关闭等消耗)

      insert into test values(1,2),(1,3),(1,4);

    2)如果从不通客户端出入多行,可以使用Insert Delayed语句得到更高的速度。Delayed含义是让

    Insert 语句马上执行,不用等到其他用户对表的读写完成后才进行插入。LOW_PRIORITY刚好相反。

    3)将数据文件和索引文件分别存在不同的磁盘上,加快访问速度(建表时可以指定,或者my.cnf里)。

    4)如果是对MyISAM表进行批量插入,可以通过增加bulk_insert_buffer_size变量值的方法来提速。

    5)当从一个文本文件装载一格表时,使用load data infile,比大量insert语句快20倍。

    3、优化ORDER BY语句

    3.1 MySQL的两种排序

      第一种是有序索引通过顺序扫描直接返回有序数据,这种方式在使用explain分析查询的时候显示

    为Using Index,不需要额外的排序,操作效率高,例如:

      第二种是通过对返回数据进行排序,也就是通常说的Filesort排序,所有不是通过索引直接返回排序结果的排序

    都叫做Filesort排序。Filesort并不代表通过磁盘文件进行排序,而只是说明进行了一个排序操作,至于排序操作是

    使用了磁盘文件或者临时表等,则取决于MySQL服务器对排序参数的设置和需要排序的数据的大小。例如:

      又如,对组合索引进行访问,并在索引idx_storeid_email上发生了一次操作排序,所以执行计划中仍然有Using Filesort。

      

      Filesort是通过相应的排序算法,将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序,如果内存

    装载不下,他就会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个快合并成有序的结果集。sort_buffer_size

    设置的排序区是每个线程独占的,所以可以有多个sort buffer存在。

      MySQL优化目标:尽量减少额外的排序,通过索引直接返回有序数据。

      Where条件和order by使用相同的索引,并且order by的顺序和索引的顺序相同,并且order by的字段都是升序或者

    都是降序。如果不满足,则肯定需要额外的排序操作,这样就会出现Filesort。

      总结,下列SQL可以使用索引(符合组合索引的最左原则):

      但是在以下几种情况下则不能使用索引:

    3.2 Filesort的优化

      通过创建合适的索引就能减少Filesort的出现,但是某些情况下,不能避免,就要加快Filesort的操作。

    在MySQL中,Filesort有两种排序算法:

    • 两次扫描算法:先根据条件取出排序字段和行指针信息,之后在排序区中排序。再之后,通过排序后的
      行指针信息回表读取数据。
    • 一次扫描算法:一次性取出满足条件的行的所有字段,然后在排序区sort buffer中排序后直接输出结果集。
      排序时内存开销比较大,但是排序效率比两次扫描算法高。

    4. 优化group by 语句

      默认情况下,MySQL对所有group by col1,col2,...的字段进行排序。与order by col1,col2...类似,如果

    group by后显示的包括一个包含相同列的order by 子句,则对实际执行性能没什么影响。

      如果查询包含group by但用户想要避免排序结果的消耗,则可以指定order by null禁止排序,如下面例子:

    5.优化嵌套查询

      使用子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作,写起来容易。但是,有些情况下需要

    用更有效率的连接(join)来替代。

      使用连接(join)来替代子查询(in)

    6. MySQL优化or条件

      对于含有OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须用到索引;

    如果没有索引,要考虑增加索引。

      例如:有三个索引,id,year,(company_id,moneys);show index from table_name;查看索引

      由上看出,正确的使用了索引,并从执行计划中获得,MySQL处理含有OR字句的查询时,实际上是对OR的各个字段

    分别查询后的结果进行了UNION操作。

      但是当在组合索引的列company_id和moneys上面做OR操作时,却不能用到索引。

    7.优化分页查询

      一般分页查询时,通过创建覆盖索引能比较好的提高性能。一个常见又非常头疼的分页场景是“limit 1000,20”,

    此时MySQL排序出前1020条记录后仅需要返回第1001到1020条记录,前1000条记录都被抛弃,查询和排序的代价非常高。

    1)第一种优化思路:(使用子查询查出索引id limit m,n后联表查询)

      在索引上完成排序分页的操作,最后根据主键关联取回原表查询所需要的其他列内容。

    例如:处理效率不高的语句:

      按照索引分页后回表方式改写SQL后:

    2)第二种优化思路(将limit m,n 转变成limit n,过滤前m行后开始查询)

    explain select film_id ,description from film where film_id >50 order by film_id asc limit 5G

       注意,这样把limit m,n转换成 limit n的查询,只适合在排序字段不会出现重复值的特定环境(唯一键或者

    主键),能够减轻分页翻页的压力。如果排序字段出现大量重复值,则不适合这样优化方式。

    8.使用SQL提示(SQL HINT,手动干预执行计划)

      SQL提示(SQL HINT)是优化数据库的一个重要手段,简单来说就是在SQL语句中加入一些人为提示来达到优化操作

    的目的。比如:

      select sql_buffer_results * from ...  

      这个语句将强制MySQL生成一个临时结果集。生成临时结果集后,所有表上的锁定均被释放。这能在遇到表锁定问题时

    或要花很长时间将结果传给客户端时很有帮助,因为可以尽快释放锁资源。

      常见的SQL HINT:

      1)USE INDEX(提供希望MySQL去参考的索引列表,但MySQL可能不会采纳)

      explain select count(*) from rental use index (idx_rental_date)G

       2)IGNORE INDEX(让MySQL忽略一个或多个索引)

      explain select count(*) from rental ignore index (idx_rental_date)G

      3)FORCE INDEX(强制MySQL使用一个特定的索引)

      explain select* from rental force index (idx_fk_inventory_id) where inventory_id > 1G

  • 相关阅读:
    Chrome截屏-截取当前页
    SecureCRT 工具分享
    mongodb在shutdown时报错:shutdown must run from localhost when running db without auth
    gdb如何实现info vtbl命令
    aspose.word 替换图片
    字节跳动校招+社招
    Flink日志输出配置
    Kafka高可用及高性能原因
    基于SAAS模式的客服云平台落地实践
    代码Recode
  • 原文地址:https://www.cnblogs.com/langdashu/p/5909713.html
Copyright © 2020-2023  润新知