SQL优化的⼏种⽅法及总结
优化⼤纲：
通过explain 语句帮助选择更好的索引和写出更优化的查询语句。
SQL语句中的IN包含的值不应该过多。
当只需要⼀条数据的时候，使⽤limit 1。
如果限制条件中其他字段没有索引，尽量少⽤or。
尽量⽤union all代替union。
不使⽤ORDER BY RAND()。
区分in和exists、not in和not exists。
使⽤合理的分页⽅式以提⾼分页的效率。
查询的数据过⼤，可以考虑使⽤分段来进⾏查询。
避免在where⼦句中对字段进⾏null值判断。
避免在where⼦句中对字段进⾏表达式操作。
必要时可以使⽤force index来强制查询⾛某个索引。
注意查询范围，between、>、<；等条件会造成后⾯的索引字段失效。
关于JOIN优化。
优化使⽤
1、mysql explane ⽤法
explane显⽰了mysql如何使⽤索引来处理select语句以及连接表。可以帮助更好的索引和写出更优化的查询语句。
EXPLAIN SELECT*FROM l_line WHERE `status` =1and create_at >'2019-04-11';
explain字段列说明
table：显⽰这⼀⾏的数据是关于哪张表的
type：这是重要的列，显⽰连接使⽤了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和all
possible_keys：显⽰可能应⽤在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择⼀个合适的语句key：实际使⽤的索引。如果为null，则没有使⽤索引。很少的情况下，mysql会选择优化不⾜的索引。这种情况下，可以在select语句中使⽤use index（indexname）来强制使⽤⼀个索引或者⽤ignore index（indexname）来强制mysql忽略索引
key_len：使⽤的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显⽰索引的哪⼀列被使⽤了，如果可能的话，是⼀个常数
rows：mysql认为必须检查的⽤来返回请求数据的⾏数
extra：关于mysql如何解析查询的额外信息。但这⾥可以看到的坏的例⼦是using temporary和using filesort，意思mysql根本不能使⽤索引，结果是检索会很慢
extra列返回的说明
distinct: ⼀旦mysql到了与⾏相联合匹配的⾏，就不在搜索了。
巴啦啦小魔仙孙侨潞猝死not exists: mysql优化了left join,⼀旦到了匹配left join标准的⾏，就不在搜索了。
range checked for each record(index map:#): 没有到理想的索引，因此对于从前⾯表中来的每⼀个⾏组合，mysql检查使⽤哪个索引，并⽤它来从表中返回⾏。这是使⽤索引的最慢的连接之⼀。
using filesort: 看到这个的时候，查询就需要优化了。mysql需要⾏额外的步骤来发现如何对返回的⾏排序。它根据连接类型以及存储排序键值和匹配条件的全部⾏的⾏指针来排序全部⾏。
using index: 列数据是从仅仅使⽤了索引中的信息⽽没有读取实际的⾏动的表返回的，这发⽣在对表的全部的请求列都是同⼀个索引的部分的时候。
using temporary : 看到这个的时候，查询需要优化了。这⾥，mysql需要创建⼀个临时表来存储结果，这通常发⽣在对不同的列集进⾏order by上，⽽不是group by上。
where used: 使⽤了where从句来限制哪些⾏将与下⼀张表匹配或者是返回给⽤户。如果不想返回表中的全部⾏，并且连接类型all或index，这就会发⽣，或者是查询有问题不同连接类型的解释（按照效率⾼低的顺序排序）。
system 表只有⼀⾏：system表。这是const连接类型的特殊情况。
const：表中的⼀个记录的最⼤值能够匹配这个查询（索引可以是主键或惟⼀索引）。因为只有⼀⾏，这个值实际就是常数，因为mysql先读
这个值然后把它当做常数来对待。
eq_ref：在连接中，mysql在查询时，从前⾯的表中，对每⼀个记录的联合都从表中读取⼀个记录，它在查询使⽤了索引为主键或惟⼀键的全部时使⽤。
ref：这个连接类型只有在查询使⽤了不是惟⼀或主键的键或者是这些类型的部分（⽐如，利⽤最左边前缀）时发⽣。对于之前的表的每⼀个⾏联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。
range：这个连接类型使⽤索引返回⼀个范围中的⾏，⽐如使⽤>或<；查东西时发⽣的情况。
index：这个连接类型对前⾯的表中的每⼀个记录联合进⾏完全扫描（⽐all更好，因为索引⼀般⼩于表数据）。
all: 这个连接类型对于前⾯的每⼀个记录联合进⾏完全扫描，这⼀般⽐较糟糕，应该尽量避免。
2、SQL语句中IN包含的值不应过多
MySQL对于IN做了相应的优化，即将IN中的常量全部存储在⼀个数组⾥⾯，⽽且这个数组是排好序的。但是如果数值较多，产⽣的消耗也是⽐较⼤的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能⽤between就不要⽤in了；再或者使⽤连接来替换。
3、当只需要⼀条数据的时候，使⽤limit 1
这是为了使EXPLAIN中type列达到const类型。
4、如果限制条件中其他字段没有索引，尽量少⽤or
or两边的字段中，如果有⼀个不是索引字段，⽽其他条件也不是索引字段，会造成该查询不⾛索引的情况。很多时候使⽤union all或者是union（必要的时候）的⽅式来代替“or”会得到更好的效果。
5、尽量⽤union all代替union
union和union all的差异主要是前者需要将结果集合并后再进⾏唯⼀性过滤操作，这就会涉及到排序，增加⼤量的CPU运算，加⼤资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。
6、不适⽤ORDER BY RAND()
select id from `dynamic` order by rand() limit 1000;
上⾯的SQL语句，可优化为：
select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nidlimit 1000;
7、区分in和exists、not in 和not exists
总结： exists适⽤于外表⼩⽽内表⼤的情况。
select*from表A where id in (select id from表B);
missmatch什么意思上⾯SQL语句相当于:
select*from表A where exists(select*from表B where表B.id=表A.id);
区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执⾏⼦查询。所以IN适合于外表⼤⽽内表⼩的情况；EXISTS适合于外表⼩⽽内表⼤的情况。
关于not in和not exists，推荐使⽤not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何⾼效的写出⼀个替代not exists的SQL语句？
原SQL语句：
select colname … from A表where a.id not in (select b.id from B表);
⾼效的SQL语句：
select colname … from A表Left join B表on where a.id = b.id and b.id is null
8、使⽤合理的分页⽅式以提⾼分页的效率
select id,name from product limit 866613, 20;
使⽤上述SQL语句做分页的时候，可能有⼈会发现，随着表数据量的增加，直接使⽤limit分页查询会越来越慢。
kube优化的⽅法如下：可以取前⼀页的最⼤⾏数的id，然后根据这个最⼤的id来限制下⼀页的起点。⽐如此列中，上⼀页最⼤的id是866612。SQL可以采⽤如下的写法：
select id,name from product where id>866612 limit 20;
9、查询的数据过⼤，可以考虑使⽤分段来进⾏查询
在⼀些⽤户选择页⾯中，可能⼀些⽤户选择的时间范围过⼤，造成查询缓慢。主要的原因是扫描⾏数过多。这个时候可以通过程序，分段进⾏查询，循环遍历，将结果合并处理进⾏展⽰。
10、避免在where⼦句中对字段进⾏null值判断
对于null的判断会导致引擎放弃使⽤索引⽽进⾏全表扫描。
11、避免在where⼦句中对字段进⾏表达式操作
⽐如：
select user_id,user_project from user_base where age*2=36；
中对字段就⾏了算术运算，这会造成引擎放弃使⽤索引，建议改成：
select user_id,user_project from user_base where age=36/2;
12、必要时可以使⽤force index来强制查询⾛某个索引
有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采⽤的索引并不是我们想要的。这时就可以采⽤forceindex来强制优化器使⽤我们制定的索引。
13、注意查询范围，between、>、<；等条件会造成后⾯的索引字段失效。
对于联合索引来说，如果存在范围查询，⽐如between、>、<；等条件时，会造成后⾯的索引字段失效。
14、关于JOIN优化
LEFT JOIN A表为驱动表，INNER JOIN MySQL会⾃动出那个数据少的表作⽤驱动表，RIGHT JOIN B表为驱动表。
注意：
1）MySQL中没有full join，可以⽤以下⽅式来解决：
select*from A left join B on B.name = A.namewhere B.name is null union all select*from B;
2）尽量使⽤inner join，避免left join：
参与联合查询的表⾄少为2张表，⼀般都存在⼤⼩之分。如果连接⽅式是inner join，在没有其他过滤条件的情况下MySQL会⾃动选择⼩表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。
3）合理利⽤索引：
被驱动表的索引字段作为on的限制字段。
4）利⽤⼩表去驱动⼤表：
从⼩表去驱动⼤表，可以有效减少嵌套循环中的循环次数，已减少I/O总量以及CPU运算的次数。
5）巧⽤STRAIGHT_JOIN：
inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，⽐如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使⽤STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使⽤STRAIGHT_JOIN，否则可能造成查询结果不准确。
是有些特殊情况需要选择另个表作为驱动表，⽐如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN 来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使⽤STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使⽤STRAIGHT_JOIN，否则可能造成查询结果不准确。
其它的优化总结
1.对查询进⾏优化，应尽量避免全表扫描，⾸先应考虑在 where 及 order by 涉及的列上建⽴索引。
2.应尽量避免在 where ⼦句中对字段进⾏ null 值判断，否则将导致引擎放弃使⽤索引⽽进⾏全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3.应尽量避免在 where ⼦句中使⽤!=或<>操作符，否则将引擎放弃使⽤索引⽽进⾏全表扫描。
4.应尽量避免在 where ⼦句中使⽤ or 来连接条件，否则将导致引擎放弃使⽤索引⽽进⾏全表扫描，如：
select id from t where num=10or num=20
可以这样查询：
select id from t where num=10
union all自学编程能干嘛
select id from t where num=20
5.in 和 not in 也要慎⽤，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能⽤ between 就不要⽤ in 了：
select id from t where num between1and3
6.下⾯的查询也将导致全表扫描：
select id from t where name like'%abc%'
7.应尽量避免在 where ⼦句中对字段进⾏表达式操作，这将导致引擎放弃使⽤索引⽽进⾏全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
8.应尽量避免在where⼦句中对字段进⾏函数操作，这将导致引擎放弃使⽤索引⽽进⾏全表扫描。如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id
应改为:
select id from t where name like'abc%'
9.不要在 where ⼦句中的“=”左边进⾏函数、算术运算或其他表达式运算，否则系统将可能⽆法正确使⽤索引。
10.在使⽤索引字段作为条件时，如果该索引是复合索引，那么必须使⽤到该索引中的第⼀个字段作为条件时才能保证系统使⽤该索引，
否则该索引将不会被使⽤，并且应尽可能的让字段顺序与索引顺序相⼀致。
11.不要写⼀些没有意义的查询，如需要⽣成⼀个空表结构：
select col1,col2 into #t from t where1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
12.很多时候⽤ exists 代替 in 是⼀个好的选择：
select num from a where num in(select num from b)
⽤下⾯的语句替换：
select num from a where exists(select1from b where num=a.num)
注意：IN适合于外表⼤⽽内表⼩的情况；EXISTS适合于外表⼩⽽内表⼤的情况
13.并不是所有索引对查询都有效，SQL是根据表中数据来进⾏查询优化的，当索引列有⼤量数据重复时，SQL查询可能不会去利⽤索
引，如⼀表中有字段sex，male、female⼏乎各⼀半，那么即使在sex上建了索引也对查询效率起不了作⽤。
14.索引并不是越多越好，索引固然可以提⾼相应的 select 的效率，但同时也降低了 insert 及 update 的效率，js入门电子书
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况⽽定。
⼀个表的索引数最好不要超过6个，若太多则应考虑⼀些不常使⽤到的列上建的索引是否有必要。
15.尽量使⽤数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个⽐较字符串中每⼀个字符，⽽对于数字型⽽⾔只需要⽐较⼀次就够了。
16.尽可能的使⽤ varchar 代替 char ，因为⾸先变长字段存储空间⼩，可以节省存储空间，
其次对于查询来说，在⼀个相对较⼩的字段内搜索效率显然要⾼些。
17.任何地⽅都不要使⽤ select * from t ，⽤具体的字段列表代替“*”，不要返回⽤不到的任何字段。
18.避免频繁创建和删除临时表，以减少系统表资源的消耗。
19.临时表并不是不可使⽤，适当地使⽤它们可以使某些例程更有效，例如，当需要重复引⽤⼤型表或常⽤表中的某个数据集时。但是，对于⼀次性事件，最好使⽤导出表。
20.在新建临时表时，如果⼀次性插⼊数据量很⼤，那么可以使⽤ select into 代替 create table，避免造成⼤量 log ，以提⾼速度；如果数据量不⼤，为了缓和系统表的资源，应先create table，然后insert。
21.如果使⽤到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
22.尽量避免使⽤游标，因为游标的效率较差，如果游标操作的数据超过1万⾏，那么就应该考虑改写。
23.使⽤基于游标的⽅法或临时表⽅法之前，应先寻基于集的解决⽅案来解决问题，基于集的⽅法通常更有效。
24.与临时表⼀样，游标并不是不可使⽤。对⼩型数据集使⽤ FAST_FORWARD 游标通常要优于其他逐⾏处理⽅法，尤其是在必须引⽤⼏个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要⽐使⽤游标执⾏的速度快。如果开发时间允许，基于游标的⽅法和基于集的⽅法都可以尝试⼀下，看哪⼀种⽅法的效果更好。
25.尽量避免⼤事务操作，提⾼系统并发能⼒。
mysql面试题sql优化26.尽量避免向客户端返回⼤数据量，若数据量过⼤，应该考虑相应需求是否合理。
以上⽆法解决可以考虑：分库分表和读写分离
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论