SQL优化的方法_sql优化常用的几种方法

（1）建立物化视图或尽可能减少多表查询。

（2）以不相干子查询替代相干子查询。

（3）只检索需要的列。

（4）用带in的条件子句等价替换or子句。

（5）经常提交commit，以尽早释放锁。

（6）避免嵌套的游标（Cursor）和多重循环等。

（7）在经常查询的列上创建索引，提高查询效率。

（8）避免使用模糊查询进行匹配，如果一定要使用，建议使用最左模糊匹配原则。

（9）慢的查询的sql，根据性能和存储容量大小进行评估，适当的可以考虑水平分表和垂直分表，以提高sql的查询性能。

（10）查询数据是否存在，适当的可以使用exists替代in。

建表

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

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

临时表

1. 避免频繁创建和删除临时表，以减少系统表资源的消耗
2. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
3. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
4. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log，以提高速度；
5. 如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
6. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table，然后 drop table，这样可以避免系统表的较长时间锁定。

游标的问题

1. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。
2. 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
3. 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

事务

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

数据量问题

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

具体SQL优化 

1.避免使用select *

select *不走覆盖索引，会有大量的回表操作，从而导致查询SQL的性能很低。应该使用具体的字段代替*，只返回使用到的字段。     

2.用union all代替union

union可以获取排除重复后的数据，union all可以获取所有数据，包含重复的数据，排除重复的过程需要遍历，排序和比较，他更耗时，更消耗CPU资源，所以如果能用union all，尽量不用union，除非是业务场景中不允许产生重复数据

3.小表驱动大表

in 和 not in 也要慎用，否则会导致全表扫描。对于连续的数值，能用 between 就不要用 in，对于子查询，可以用exists代替。

用小表的数据集驱动大表的数据集

in关键字，他会优先执行in里面的子查询语句，然后在执行in外面的语句，in里面的数据量很少，作为条件查询速度更快

exists关键字，他会优先执行exists左边的语句（即主查询语句），然后把它作为条件，去跟右边的语句匹配，如果匹配上，则可以查出数据，如果匹配不上，数据就被过滤掉了

in适用于左边大表，右边小表

exists适用于左边小表，右边大表

4.批量操作

每次远程请求数据库，是会消耗一定性能的

提供一个批量插入的方法，这样只需要远程请求一次数据库，SQL性能会提升，数据量越大，提升越多，但是不建议一次批量操作太多数据，如果数据太多，数据库响应也会很慢，批量操作需要把握一个度，建议每批数据尽量控制在500以内，多批如果数据多于500，则分多批处理。

5.多用limit

6.in中值太多

如果in数据太多，不做任何限制，可能会导致接口超时

可以在SQL语句中对数据用limit做限制，不过更多的是在业务代码中加限制

如果超出500，可以分批用多线程去查询数据，每批只查500条记录，最后把查询到的数据汇总到一起返回

7.增量查询

有时候，我们需要通过远程接口查询数据，然后同步到另一个数据库，如果直接获取所有的数据，然后同步过去，这样如果数据很多，查询性能会非常差，可以按时间和id升序，每次只同步一批数据，这一批数据只有100条记录，每次同步完后，保存这100条数据中最大的id和时间，给同步下一批数据的时候用，通过这种增量查询的方式，能够提升单次查询的效率

select * from user where id > #{lastId} and create_time >= #{lastCreateTime} limit 100;

8.高效的分页

列表页在查询数据的时候，为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理，在数据库中分页一般用的limit关键字如图

select id,name,age from user limit 10,20;

如果表中的数据量较少，用limit关键字做分页，没什么问题，但如果表中数据量很多，用他就会出现性能问题，

select id,name,age from user limit 1000000,20;

优化

select id,name,age from user where id >1000000 limit 20;

利用id上的索引查询，要求id是连续的，并且是有序的，还可以使用between优化分页

select id,name,age from user where id between 1000000 and  1000020;

between要在唯一索引上分页，不然会出现每页大小不一致的问题

9.用连接查询代替子查询

数据库中如果需要从两张以上的表中查询出数据的话，一般有两种方式，子查询和连接查询

子查询，可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中，程序先运行嵌套在最内层的语句，在运行外层的语句，子查询的优点是简单，结构化，如果涉及的表数据不多的话，但缺点是数据库执行子查询时，需要创建临时表，查询完毕后，会删除这些临时表，有一些额外的性能消耗

select * from order where user_id in (select id from user where status=1);

连接查询，性能会更高

select o.* from order o
inner join user u on o.user_id=u.id
where u.status=1;

10.join的表不宜过多

join表的数据不应超过3个，如果join太多，数据库在选择索引的时候会非常复杂，很容易选错索引，并且如果每天命中，nested loop join就是分别从两个表读一行数据进行两两对比

11.join时要注意

我们在使用多张表联合查询的时候，一般会使用join关键字，join使用最多的是是left join和inner join

left join求两个表的交集外加左表剩下的数据

inner join求两个表交集的数据

12.索引

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

        索引问题 法则：不要在建立的索引的数据列上进行下列操作:避免对索引字段进行计算操作。避免在索引字段上使用not，<>，!=。避免在索引列上使用IS NULL和IS NOT NULL。避免在索引列上出现数据类型转换。避免在索引字段上使用函数。避免建立索引的列中使用空值。

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

        在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

控制索引的数量：索引可以显著提升 查询的性能，但索引数量并非越多越好，因为表中新增数据时，需要同时为他创建索引，而索引时需要额外的存储空间的，而且还会有一定的性能消耗，单表中的索引数量应该尽量控制在5个以内，并且单个索引中的字段不超过5个

13.合理的数据类型

14.提升group by的效率

select user_id,user_name from order group by user_id having user_id <=200;

优化

select user_id,user_name from order where user_id <=200 group by user_id;

15，索引优化

检查SQL语句有没有走索引-explain查看数据库的执行计划

 

16.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

where 表之间的连接必须写在其他 Where 条件之前， 那些可以过滤掉最大数量记录的条件必须写在 Where 子句的末尾，HAVING 最后。

不要在where条件中使用左右两边都是%的like模糊查询，这样会导致数据库引擎放弃索引进行全表扫描。优化：尽量在字段后面使用模糊查询

应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，优化：可以用in代替or。

尽量不要在 where 子句中对字段进行表达式操作，这样也会造成全表扫描。

where条件里尽量不要进行null值的判断，null的判断也会造成全表扫描。给字段添加默认值，对默认值进行判断。

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

应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。将表达式.函数操作移动到等号右侧。

不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

尽量不要使用where 1=1的条件,有时候，在开发过程中，为了方便拼装查询条件，我们会加上该条件，这样，会造成进行全表扫描。优化：如果用代码拼装sql，则由代码进行判断，没where加where，有where加and如果用mybatis，请用mybatis的where语法。

其他的优化

20.Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

21.尽量使用Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大。

22.对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会
很高，性能很差。

23.前提也是在sql基础优化完成后，有多表联合查询导致查询数据很慢，可以在代码上进行分割，如一条语句查多个表，可以拆分成两条sql语句或者多条sql语句，然后再代码上进行数据拼装。

24.业务层面优化是指在sql基础优化上没有问题之后，然后一次性查询的数据量很大，达到上亿的数据量，即使是分页也会很慢，所以要在业务层面进行优化，固定条件，缓存count值，避免每次查询全表扫描计算count值，每次更新都要对count值进行同步修改

Count优化

count(column) ：是表示结果集中有多少个column字段不为空的记录。

count(*) ：是表示整个结果集有多少条记录

count(1)：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。