前言SQL优化是大家比较关注的热门话题。不管是面试还是工作，都有可能遇到。如果有一天你负责的一个在线接口出现了性能问题，它就需要优化。那么你第一个想到的很可能就是优化sql语句，因为它的修改成本比代码小很多。那么，如何优化sql语句呢？本文从15个方面分享一些sql优化的技巧，希望对你有所帮助。1、避免使用select*很多时候，我们在写SQL语句的时候，为了方便，喜欢直接使用select*，一次性找出表中所有列的数据。反例：select*fromuserwhereid=1;在实际的业务场景中，我们可能只需要用到一两个列。查了很多资料，但是没有用，浪费了数据库资源，比如内存或者cpu。另外，检测到的数据越多，也会增加网络IO传输过程中的数据传输时间。还有一个最重要的问题是：select*不会使用覆盖索引，会有大量的回表操作，导致查询SQL性能低下。那么，如何优化呢？正例：selectname,agefromuserwhereid=1;查询SQL语句时，只检查需要用到的列，多余的列根本不需要检查。2.使用unionall而不是union。我们都知道在sql语句中使用union关键字后，可以得到去重后的数据。而如果使用unionall关键字，则可以获得所有数据，包括重复数据。反例：(select*fromuserwhereid=1)union(select*fromuserwhereid=2);去重的过程需要遍历、排序、比较，比较耗时，比较耗cpu资源。所以能用unionall的尽量不要用union。正例：(select*fromuserwhereid=1)unionall(select*fromuserwhereid=2);除非有一些特殊的场景，比如unionall后，结果集中出现重复数据，业务场景是不允许出现重复数据的。这时候就可以使用union了。3、小表带动大表小表带动大表，也就是说用小表的数据集来带动大表的数据集。假设有两张表，order和user，其中order表有10000条数据，user表有100条数据。这时候如果要查看所有有效用户的下单列表。可以使用in关键字实现：select*fromorderwhereuser_idin(selectidfromuserwherestatus=1)也可以使用exists关键字实现：select*fromorderwhereexists(select1fromuserwhereorder.user_id=user.idandstatus=1)对于上面提到的业务场景，使用in关键字更适合实现业务需求。为什么？因为sql语句如果包含in关键字，会先执行in内的子查询语句，再执行in外的语句，如果in内的数据量小，作为条件查询会更快。而如果sql语句中包含exists关键字，则会先执行exists左边的语句（即主查询语句）。然后以此为条件匹配右边的语句。如果匹配，则可以查询数据。如果没有匹配项，则过滤掉数据。在这个需求中，order表有10000条数据，而user表有100条数据。订单表是大表，用户表是小表。如果订单表在左边，使用in关键字的性能会更好。总结一下：in适用于左边的大表和右边的小表。存在适用于左侧的小表和右侧的大表。无论使用in还是exists关键字，其核心思想都是以小表带动大表。4.批量操作业务处理后有一批数据需要插入怎么办？反例：for(Orderorder:list){orderMapper.insert(order):}在循环中逐条插入数据。insertintoorder(id,code,user_id)values(123,'001',100);该操作需要多次请求数据库才能完成这批数据的插入。但是众所周知，在我们的代码中，每次远程请求数据库，都会消耗一定的性能。而如果我们的代码需要多次请求数据库来完成这个业务功能，势必会消耗更多的性能。那么如何优化呢？正例：orderMapper.insertBatch(list)：提供批量插入数据的方法。insertintoorder(id,code,user_id)values(123,'001',100),(124,'002',100),(125,'003',101);这样只需要对数据库进行一次远程请求，sql性能就会得到提升，数据越多，提升越大。但是需要注意的是，不建议在一个batch中操作过多的数据。如果数据太多，数据库响应会很慢。批量操作需要一定的掌握。建议每批数据尽量控制在500条以内。如果数据超过500条，会分多批处理。5.多次使用限制有时候，我们需要查询一些数据中的第一项，比如：查询一个用户下的第一笔订单，想看他第一次下单的时间。反例：selectid,create_datefromorderwhereuser_id=123orderbycreate_dateasc;根据用户id查询订单，按订单时间排序，先找出该用户的所有订单数据，得到一个订单集合。然后在代码中，获取第一个元素的数据，也就是第一个订单的数据，就可以获取到第一个订单的时间。列表list=orderMapper.getOrderList();Orderorder=list.get(0);这种方法虽然在功能上没有问题，但是效率很低。需要先查询所有的数据，有点浪费资源。那么，如何优化呢？正例：selectid,create_datefromorderwhereuser_id=123orderbycreate_dateasclimit1；使用limit1，只返回用户下单时间最小的那条数据。另外，在删除或修改数据时，也可以在SQL语句的末尾加上limit，以防止误操作导致删除或修改不相关的数据。例如：updateordersetstatus=0,edit_time=now(3)whereid>=100andid<200limit100;这样即使你操作错了，比如把id弄错了，也不会影响太多的数据。6、in中的值过多对于批量查询接口，我们通常使用in关键字来过滤数据。比如：我想通过一些指定的ids来批量查询用户信息。sql语句如下：selectid,namefromcategorywhereidin(1,2,3...100000000);如果我们不做任何限制的话，这个查询语句可能会一次查询很多数据，很容易导致接口超时。这个时候怎么办？selectid,namefromcategorywhereidin(1,2,3...100)limit500;可以使用limit来限制sql中的数据。但是，我们需要在业务代码中添加限制。伪代码如下：publicListgetCategory(Listids){if(CollectionUtils.isEmpty(ids)){returnnull;}if(ids.size()>500){thrownewBusinessException("Amaximumof一次允许查询500条记录")}returnmapper.getCategoryList(ids);}另一种解决方法是：如果ids超过500条记录，可以使用多线程批量查询数据。每批只检查500条记录，最后将查询到的数据聚合返回。不过这只是临时解决方案，不适合id过多的场景。因为id太多，即使能很快找到数据，如果返回的数据量太大，网络传输也会消耗很大的性能，接口性能也好不到哪里去。7、增量查询有时候，我们需要通过远程接口查询数据，然后同步到另一个数据库。反例：select*fromuser;如果直接获取所有数据，那就同步过去。这样虽然很方便，但是带来了一个非常大的问题，就是如果数据量很大，查询性能会很差。这个时候怎么办？例如：select*fromuserwhereid>#{lastId}andcreate_time>=#{lastCreateTime}limit100;按照id和时间从小到大的顺序，每次只同步一批数据，这批数据只有100条记录。每次同步完成后，保存这100条数据中最大的id和时间，在同步下一批数据时使用。通过这种增量查询的方式，可以提高单次查询的效率。8、高效的分页有时候，列表页在查询数据的时候，为了避免一次返回过多的数据，影响界面的性能，我们一般会对查询界面进行分页处理。mysql中一般用于分页的limit关键字：selectid,name,agefromuserlimit10,20；如果表的数据量不大，使用limit关键字进行分页是没有问题的。但是如果表中的数据很多，使用的时候就会出现性能问题。比如分页参数现在变成：selectid,name,agefromuserlimit1000000,20；mysql会找到1000020条数据，然后丢弃前1000000条数据，只检查后面的20条数据，这是一种资源浪费。那么，这些海量数据应该如何分页呢？优化sql：selectid,name,agefromuserwhereid>1000000limit20;先找到上次分页最大的id，然后对id使用索引查询。但是，此方案要求id是连续且有序的。您还可以使用between来优化分页。selectid,name,agefromuserwhereidbetween1000000and1000020;需要注意的是，between要在唯一索引上分页，否则会出现每页大小不一致的问题。9、使用连接查询代替子查询如果需要在mysql中查询两个以上表的数据，一般有两种实现方式：子查询和连接查询。子查询的一个例子如下：select*fromorderwhereuser_idin(selectidfromuserwherestatus=1)子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中。程序先运行嵌套在最内层的语句，再运行外层的语句。子查询语句的优点是简单，结构化，如果涉及的表数不大。但是缺点是mysql在执行子查询的时候，需要创建临时表。查询完成后，需要删除这些临时表，有一定的额外性能消耗。这时可以改为连接查询。具体示例如下：selecto.*fromorderoinnerjoinuseruono.user_id=u.idwhereu.status=110。连接表不应该太多。根据阿里巴巴开发者手册的规定，join表的个数不能超过3个。反例：selecta.name,b.name.c.name,d.namefrominnerjoinbona.id=b.a_idinnerjoinconc.b_id=b.idinnerjoindond.c_id=c.idinnerjoineone.d_id=d.idinnerjoinfonf.e_id=e.idinnerjoinong.f_id=如果f.id的join太多，mysql在选择索引的时候会很复杂，很容易选错索引。而如果没有命中，嵌套循环join就是从两个表中读取一行数据进行两两比较，复杂度为n^2。所以我们应该尽量控制连接表的数量。正例：selecta.name,b.name.c.name,a.d_namefrominnerjoinbona.id=b.a_idinnerjoinconc.b_id=b.id如果在业务场景的实现中需要查询其他几个表的数据，你可以查询a,b中的数据，c表中的冗余特殊字段，例如：a表中冗余的d_name字段，用来保存需要查询的数据。但是，我以前见过一些ERP系统。并发量不大，但是业务比较复杂。它需要连接十几个表来查询数据。因此，连接表的数量要根据系统的实际情况来定，不能一概而论。桌子越少越好。11、加入时要注意。涉及到多表联合查询时，我们一般会使用join关键字。最常用的连接是左连接和内连接。leftjoin：求两张表的交集加上左表剩余的数据。innerjoin：查找两个表的交集数据。使用innerjoin的例子如下：selecto.id,o.code,u.namefromorderoinnerjoinuseruono.user_id=u.idwhereu.status=1;如果两张表使用innerjoin关联，mysql会自动选择两张表中的小表来驱动大表，所以性能上不会有太大问题。使用leftjoin的例子如下：selecto.id,o.code,u.namefromorderoftjoinuseruono.user_id=u.idwhereu.status=1;如果两张表用leftjoin关联，mysql会默认leftjoin关键字左边的表，去驱动它右边的表。如果左边的表中数据很多，就会出现性能问题。需要特别注意的是，在使用leftjoin关联查询时，左边要用小表，右边可以用大表。能用innerjoin的就尽量少用leftjoin。12、控制索引的数量众所周知，索引可以显着提高查询SQL的性能，但索引越多越好。因为当向表中添加新数据时，需要同时为其创建索引，而索引需要额外的存储空间，会有一定的性能消耗。阿里巴巴的开发者手册规定，单表的索引个数尽量控制在5个以内，单个索引的字段数不要超过5个。mysql使用的B+树结构来保存索引，B+树索引在插入、更新、删除操作时需要更新。如果索引太多，会消耗很多额外的性能。那么，问题来了，表中的索引太多了，超过5个怎么办？这个问题要辩证地看待。一个就好了，只要不超过太多就行了。但是对于一些高并发的系统，请务必遵守单表索引数量不能超过5个的限制。那么，高并发系统中如何优化索引数量呢？如果能建联合索引，就不要建单一索引，可以删除没用的单一索引。将部分查询功能迁移到其他类型的数据库，如ElasticSeach、HBase等，只需要在业务表中建立少量关键索引即可。13、选择一个合理的字段类型char来表示一个固定的字符串类型。这类字段的存储空间是固定的，会浪费存储空间。altertableorderaddcolumncodechar(20)NOTNULL;varchar表示变长字符串类型，该类型的字段存储空间会根据实际数据的长度进行调整，不会浪费存储空间。altertableorderaddcolumncodevarchar(20)NOTNULL；如果是定长字段，比如用户的手机号码，一般为11位，可以定义为char类型，长度为11个字节。但是如果是公司名称字段，如果定义成char类型，就会出现问题。如果长度定义得太长，比如200字节，而实际企业长度只有50字节，就会浪费150字节的存储空间。如果长度定义的太短，比如定义为50字节，但实际的公司名称是100字节，将无法存储，并抛出异常。因此，建议将企业名称改为varchar类型。变长字段的存储空间小，可以节省存储空间，对于查询来说，在相对较小的字段中搜索效率明显更高。我们在选择字段类型的时候要遵循这样一个原则：如果可以使用数值类型，就不需要字符串，因为字符的处理往往比数字慢。尽可能使用小的类型，例如：使用bit存储布尔值，使用tinyint存储枚举值等。固定长度的String字段，使用char类型。变长字符串字段，使用varchar类型。金额字段使用小数以避免精度损失。还有很多其他的原则，这里就不一一列举了。14.提高groupby的效率我们有很多业务场景需要用到groupby关键字。它的主要功能是去重和分组。通常与having一起使用，表示分组后按一定条件过滤数据。反例：selectuser_id,user_namefromordergroupbyuser_idhavinguser_id<=200;这种写法性能很差。它首先根据userid对所有订单进行分组，然后过滤userid大于等于200的用户。分组是一个比较耗时的操作。为什么不先缩小数据范围再分组呢？正例：selectuser_id,user_namefromorderwhereuser_id<=200groupbyuser_id在分组前使用where条件过滤掉冗余数据。效率会更高。其实这是一种思路，不仅仅局限于groupby的优化。在我们的sql语句做一些耗时的操作之前，数据范围要尽量缩小，这样可以提高sql的整体性能。15.索引优化SQL优化中，有一个很重要的内容就是：索引优化。很多时候，带索引和不带索引的SQL语句在执行效率上有很大的差别。因此，索引优化被视为SQL优化的首选。索引优化的第一步是检查SQL语句是否使用了索引。那么，如何检查SQL是否离开了索引呢？可以使用explain命令查看mysql的执行计划。例如：explainselect*from`order`wherecode='002';结果：可以通过这些列来判断索引的使用情况，执行计划中包含的列的含义如下图所示：想了解explain的详细用法可以看看我的其他文章《explain | 索引优化的这把绝世好剑，你真的会用吗?》说句实话，SQL语句并没有用到索引，除了没有建索引，最大的可能就是索引失效了。下面说说索引失败的常见原因：如果不是上述原因，则需要进一步排查其他原因。另外，大家有没有遇到过这样的情况：明明是同一个sql，只是入参不同而已。有时使用索引a，有时使用索引b？是的，有时候mysql会选择错误的索引。如果需要，可以使用forceindex强制查询sql到某个索引。至于mysql为什么选错索引，后面会有专门的文章介绍，这里先留个悬念吧。
