SQL语句优化

最新推荐文章于 2024-09-25 14:27:06 发布

sunny潘先生

最新推荐文章于 2024-09-25 14:27:06 发布

阅读量120

点赞数

文章标签： sql

原文链接：https://www.jianshu.com/p/f0c0d73cf6e6?from=timeline&isappinstalled=0

版权

应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将导致引擎放弃使用索引而进行全表扫描。SQL中，不等于操作符会限制索引，引起全表扫描，即使比较的字段上有索引。MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些方式的LIKE（'a%'）【右模糊查询见第20条】。
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

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

#能用TINYINT就不用SMALLINT，能用SMALLINE就不用INT，磁盘和内存消耗越小越好, 
#where子句中出现column字段的类型和传入的参数类型不一致的时候会发生类型转换，建议先确定where中的参数类型

将大的delete，update或者insert查询变成多个小查询
能写一个几十行、几百行的SQL语句是不是显得很厉害？然而，为了更优的性能以及更好的数据控制，应将它们写成多个小查询。
结果集允许重复的话，尽量用union all代替union
union 和 union all 的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。而UNINON ALL不去重，效率高于UNION。当然，union all 的前提条件是两个结果集没有重复数据。或者如果结果集允许重复的话，尽量使用union all 代替 union 。
如果限制条件中其他字段没有索引，尽量少用 or。or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。很多时候使用 union all 或者是 union（必要的时候）的方式来代替“or” 会得到更好的效果。如：
为获得相同结果集的多次执行，请保持SQL语句前后一致。这样做的目的是为了充分利用查询缓冲。
比如根据地域和产品ID查询产品价格，第一次使用了：
select price from order where id='123' and region='BEIJING'
那么第二次同样的查询，请保持以上语句的一致性，比如不要将where语句里面的id和region位置调换顺序。
select语句务必指明字段名称，尽量避免使用"select * "。如果不查询表中的所有的列，尽量避免使用select *，因为它会进行全表扫描，不能有效利用索引，增加很多不必要的消耗（CPU、IO、内存、网络带宽），增大了数据库服务器的负担，以及它与应用程序客户端之间的网络IO开销。当表结构发生改变时，前端也需要更新。所以要求直接在select后面接上字段名。
join子句里面的列尽量被索引。同样只是”尽量“，并不是说所有的列。
left join a表为驱动表，inner join MySQL会自动找出那个数据少的表作为驱动表，right join b表为驱动表。

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。因地制宜，根据实际情况进行调整，因为有时索引太多也会降低性能。

#order by的列尽量被索引。order by的列如果被索引，性能也会更好
#不使用order by rand() limit ~MySQL的随机抽取实现方法

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;

注意范围查询语句
对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。
使用EXPLAIN关键字去查看执行计划。EXPLAIN可以检查索引使用情况以及扫描的行。
如果排序字段没有用到索引，就尽量少排序
SQL语句中 in 和 not in 也要慎用，否则会导致全表扫描
MySQL对于in做了相应的优化，即将in中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。对于连续的数值，能用between就不要用in了；再或者使用连接来替换。
区分 in 和 exists、not in 和not exists ( 区分 in 和 exists 主要是造成了驱动顺序的改变（这是性能变化的关键）。如果是exists，那么以外层表为驱动表，先被访问；如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；exists适合于外表小而内表大的情况。)
```
#关于 not in 和 not exists，推荐使用not exists，不仅仅是效率问题，not in 可能存在逻辑问题

原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
```
任何地方都不要使用 select * from t ，用具体的字段列表代替 *，不要返回用不到的任何字段。

limit 优化。使用合理的分页方式以提高分页的效率( 使用limit实现分页逻辑。不仅提高了性能，同时减少了不必要的数据库和应用间的网络传输, 当只需要一条数据的时候，使用limit 1，这是为了使EXPLAIN中type列达到const类型。 "limit 1"可以避免全表扫面，找到对应结果就不会再继续扫描了。 )

#使用下面SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

select id,name from product 
limit 89757, 20

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

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

对于复合索引来说，要遵守最左前缀法则。在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
举例来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建复合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。
必要时可以使用force index来强制查询走某个索引
有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用force index来强制优化器使用我们制定的索引。
模糊查询效率很低
原因：like本身效率就比较低，应该尽量避免查询条件使用like。对于like ‘%...%’（全模糊）这样的条件，是无法使用索引的，全表扫描自然效率很低；另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。
　　解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like ‘…%’，是会使用索引的；左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index 的形式，变化成 like ‘…%’。全模糊是无法优化的，一定要的话考虑用搜索引擎。出于降低数据库服务器负载的考虑，尽可能地减少数据库模糊查询。
如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
应尽量避免在 where 子句中对字段进行函数、算术运算或其他表达式运算，这将导致引擎放弃使用索引而进行全表扫描。如：
不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
避免频繁创建和删除临时表，以减少系统表资源的消耗。
临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
尽量避免大事务操作，提高系统并发能力。
如果排序字段没有用到索引，就尽量少排序。

注意：

MySQL中没有full join，可以用以下方式来解决：

select * from A 
left join B on B.name = A.name where B.name is null
union all 
select * from B;

尽量使用inner join，避免left join：
参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表；但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。
合理利用索引：
被驱动表的索引字段作为on的限制字段。
利用小表去驱动大表
从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。
巧用STRAIGHT_JOIN：
inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。
这个方式有时能减少3倍的时间。

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