• SQL优化常用的几种方法


    为什么要对SQL优化:

    1、执行性能低
    2、等待时间过长
    3、SQL写的太差
    4、索引失效
    ·····等等
    

    SQL优化的一些方法:

    1、EXPLAIN

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

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

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

    key列,使用到的索引名。如果没有选择索引,值是NULL。

    key_len列,索引长度。不损失精确性的情况下,长度越短越好

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

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

    注:MySQL Explain详解可以参考 https://www.cnblogs.com/leeego-123/p/11846613.html

    2、区分in和exists

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

    用下面的语句替换:

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

    区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)。

    如果是exists,那么以外层表为驱动表,先被访问;

    如果是IN,那么先执行子查询。

    所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

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

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

    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

    select id from t where num=0

    4、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

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

    比如下面的SQL中对字段就行了算术运算,这会造成引擎放弃使用索引,

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

    建议改成:

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

    再比如

    select id from t where num/2=100

    应改为:

    select id from t where num=100*2

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

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

    select id from t where substring(name,1,3)='abc'; --name以abc开头的id

    应改为:

    select id from t where name like 'abc%'

    8、避免隐式类型转换

    隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

    由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.

    错误的例子:

    select * from test where tu_mdn=13333333333;

    正确的例子:

    select * from test where tu_mdn='13333333333';

    9、对于联合索引(组合索引)来说,要遵守最左前缀法则

    可以参考这两篇博客——

    https://w.cnblogs.com/rocker-pg/p/11635414.html

    https://www.cnblogs.com/hongmoshui/p/10429842.html

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

    SELECT*增加很多不必要的消耗(CPU、IO、内存、网络带宽);

    增加了使用覆盖索引的可能性;

    当表结构发生改变时,前断也需要更新。

    所以要求直接在select后面接上字段名。

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

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

    or两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。

    很多时候使用union all或者是union(必要的时候)的方式来代替“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

    13、尽量用union all代替union

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

    当然,union all的前提条件是两个结果集没有重复数据。

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

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

    假如有这样一列code的值为'AAA','AAB','BAA','BAB' ,如果where code like '%AB'条件

    由于前面是模糊的,所以不能利用索引的顺序,必须一个个去找,看是否满足条件。这样会导致全索引扫描或者全表扫描。

    如果是这样的条件where code like 'A % ',就可以查找CODE中A开头的CODE的位置,

    当碰到B开头的数据时,就可以停止查找了,因为后面的数据一定不满足要求。这样就可以利用索引了。

    15、索引数量不要过多

    索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,

    因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。

    一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

  • 相关阅读:
    分析facebook的AsyncDisplayKit框架,async-display使用async-transaction
    自制反汇编逆向分析工具 迭代第四版本
    自制反汇编逆向分析工具 迭代第三版本
    自制反汇编逆向分析工具 迭代第二版本
    分析facebook的AsyncDisplayKit框架中的Transaction的工作原理
    Oracle索引知识学习笔记
    PDF文件添加二维码水印教程
    图片添加二维码水印教程
    CAS单点登录之支持数据库认证
    CAS单点登录之服务端部署
  • 原文地址:https://www.cnblogs.com/qq1445496485/p/14385759.html
Copyright © 2020-2023  润新知