数据库MySQL学习MYSQL

SQL语句优化

2019-04-08  本文已影响210人  Djbfifjd

MySQL和Oracle

阿里开发强制要求的11条SQL编写规范

MySQL大表优化

  1. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将导致引擎放弃使用索引而进行全表扫描。
    SQL中，不等于操作符会限制索引，引起全表扫描，即使比较的字段上有索引。

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

null
  1. 为列选择合适的数据类型，而且要避免隐式类型转换

  1. 将大的delete，update或者insert查询变成多个小查询
    能写一个几十行、几百行的SQL语句是不是显得很厉害？然而，为了更优的性能以及更好的数据控制，应将它们写成多个小查询。

  2. 结果集允许重复的话，尽量用union all代替union
    union 和 union all 的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。而UNINON ALL不去重，效率高于UNION。当然，union all 的前提条件是两个结果集没有重复数据。或者如果结果集允许重复的话，尽量使用union all 代替 union 。

  3. 如果限制条件中其他字段没有索引，尽量少用 or。or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。很多时候使用 union all 或者是 union（必要的时候）的方式来代替“or” 会得到更好的效果。如：

or

  1. 为获得相同结果集的多次执行，请保持SQL语句前后一致。这样做的目的是为了充分利用查询缓冲。
    比如根据地域和产品ID查询产品价格，第一次使用了：
    select price from order where id='123' and region='BEIJING'
    那么第二次同样的查询，请保持以上语句的一致性，比如不要将where语句里面的id和region位置调换顺序。

  2. select语句务必指明字段名称，尽量避免使用"select * "
    如果不查询表中的所有的列，尽量避免使用select *，因为它会进行全表扫描，不能有效利用索引，增加很多不必要的消耗（CPU、IO、内存、网络带宽），增大了数据库服务器的负担，以及它与应用程序客户端之间的网络IO开销。当表结构发生改变时，前端也需要更新。所以要求直接在select后面接上字段名。

  3. join子句里面的列尽量被索引。同样只是”尽量“，并不是说所有的列。
    left join a表为驱动表，inner join MySQL会自动找出那个数据少的表作为驱动表，right join b表为驱动表。

注意：

select * from A 
left join B on B.name = A.name where B.name is null
union all 
select * from B;
  1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。因地制宜，根据实际情况进行调整，因为有时索引太多也会降低性能。
select id from table order by rand() limit 1000;

上面的SQL语句，可优化为：

select id from table  t1 
join  (select rand() * (select max(id) from table ) as nid) t2 on t1.id > t2.nid
limit 1000;

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

  2. 使用EXPLAIN关键字去查看执行计划。EXPLAIN可以检查索引使用情况以及扫描的行。

  3. 如果排序字段没有用到索引，就尽量少排序

  4. SQL语句中 in 和 not in 也要慎用，否则会导致全表扫描
    MySQL对于in做了相应的优化，即将in中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。对于连续的数值，能用between就不要用in了；再或者使用连接来替换。

between
  1. 区分 in 和 exists、not in 和not exists
inANDexists

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

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

原SQL语句：

select colname … from tableA 
where a.id not in (select b.id from tableB)

高效的SQL语句：

select colname … from tableA 
left join tableB on a.id = b.id 
where b.id is null

取出的结果集为A表不在B表中的数据。

  1. 任何地方都不要使用 select * from t ，用具体的字段列表代替 *，不要返回用不到的任何字段。

  2. limit 优化。使用合理的分页方式以提高分页的效率

select id,name from product 
limit 89757, 20

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

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

  1. 对于复合索引来说，要遵守最左前缀法则。在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
    举例来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建复合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

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

  3. 模糊查询效率很低
    原因：like本身效率就比较低，应该尽量避免查询条件使用like。对于like ‘%...%’（全模糊）这样的条件，是无法使用索引的，全表扫描自然效率很低；另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。
    　　解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like ‘…%’，是会使用索引的；左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index 的形式，变化成 like ‘…%’。全模糊是无法优化的，一定要的话考虑用搜索引擎。出于降低数据库服务器的负载考虑，尽可能地减少数据库模糊查询。

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

参数
  1. 应尽量避免在 where 子句中对字段进行函数、算术运算或其他表达式运算，这将导致引擎放弃使用索引而进行全表扫描。如：
算术运算 函数操作

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

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

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

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

  5. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

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

  7. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

  8. 避免频繁创建和删除临时表，以减少系统表资源的消耗。

31.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

32.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

33.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

34.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

35.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

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

37.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

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

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

Oracle中的in参数的个数限制：

Oracle中in后括号中的参数个数有限制，Oracle 9i 中个数不能超过256，Oracle 10g个数不能超过1000。
当in的个数大于1000时，有以下两个解决办法：

  1. 对参数进行处理，分成多个in，其中每个in列表中参数都小于1000。如 params in(1,2,3.........1000) or params in(1001,1002...2000)。
    不过这种方法性能和维护性方面不好
  2. 将in后面的字符串改成子查询，将in里面的数据保存到临时表中，params in（select ....from dual)

SQL语句中过滤条件where和having的区别：

  1. where是一个约束声明，使用where约束来自数据库的数据，where是在结果返回之前起作用，where中不能使用聚合函数。
  2. Having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。
  3. 在查询过程中，where子句执行优先级高于聚合语句。聚合语句（sum，min，max，avg，count）优先级高于having子句。

一个表建有多个索引，Oracle如何选择

一个表最多可有255 个字段和32个索引。

  1. 表的主键、外键必须有索引；
  2. 数据量超过300的表应该有索引；
  3. 经常与其他表进行连接的表，在连接字段上应该建立索引；
  4. 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
  5. 索引应该建在选择性高的字段上；
  6. 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
  7. 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
    A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
    B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
    C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
    D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
    E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
  8. 频繁进行数据操作的表，不要建立太多的索引；
  9. 删除无用的索引，避免对执行计划造成负面影响；
    以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

总结：SQL调优方法很多，同样的查询结果可以有很多种不同的查询方式。其实最好的方法就是在开发环境中用最贴近真实的数据集和硬件环境进行测试，然后再发布到生产环境中。

上一篇下一篇

猜你喜欢

热点阅读