本文转载自微信公众号《Java极客技术》，作者鸭血范。转载本文请联系Java极客技术公众号。在开始之前，我们需要知道：如果我的SQL语句执行得足够快，是否还需要优化它？完全没有必要吧？所以我们一般说如果要优化SQL，一定是这条SQL语句执行的比较慢那么，为什么执行的慢呢？SQL语句执行慢的三种原因是没有建立索引，或者索引失效导致SQL语句执行慢。这应该更容易理解。如果数据比较多，在千万级别以上，那么就没有建立索引。在千万级别的数据中找到自己想要的内容，简直是肉搏（哎哟，厉害了，敢打无效索引的内容有很多话要说，比如，查询的时候，把like通配符放在前面，比如常说的“最左匹配原则”，比如我们在查询条件中使用or，条件前后有一列orwithoutIndexes等.会导致索引失效和锁等待，常用的存储引擎主要有InnoDB和MyISAM，前者支持行锁和表锁，而后者如果数据库操作是基于表锁的，则只支持表锁，也就是说如果现在有update操作，整个表都会被锁住，然后不允许进行查询操作，所以不要说提高系统的并发性能，聪明的你一定知道，因为MyISAM只有表锁是su据报道，那么使用InnoDB不是很好吗？你觉得InnoDB的行锁不会升级为表锁吗？太年轻，太单纯！如果你对一张表进行大量的更新操作，mysql认为你会使事务的执行效率降低，最终还是会导致性能下降。在这种情况下，最好将行锁升级为表锁。还有一点，行锁是建立在索引加锁的基础上的。在执行更新操作时，条件索引如果都无效，那么这个锁也会执行从行锁升级为表锁的不合适的SQL语句。这也是比较常见的。什么是不合适的SQL语句？，但是，为了省事，直接select*，或者orderby，后面的条件不是索引字段，这是不合适的SQL语句。优化SQL语句。知道SQL语句执行慢的原因后，接下来要做的最有效的办法就是对症下药。EXPLAIN语法是最有效的方法。你知道ShowProfile也可以用来做锁等待吗？在不合适的SQL语句部分，我们会介绍几种常用的SQL优化，比如如何优化分页查询，让查询速度更快。你不是说select*不是正确的打开方式吗？正确的选择方法是什么？？放心，下面阿凡要说的废话我就不说了。先从一张表开始吧，保证优化后的结果和我写的一样（至少90%是一致的，那我们就用同一个数据库好吗？？哎~先建个demo数据库吧，接下来我们建表我们建一个很简单的表好吗？)ENGINE=INNODB插入10万条数据;SETi=i+1;ENDWHILE;END;CALLdemo_insert();OK，准备工作做好了，接下来开始分析SQL是如何通过EXPLAIN执行的。只要提到SQL调优，就离不开EXPLAINEXPLAINSELECT*FROMtableWHEREid<100ORDERBYa;我们可以看到有几个参数：id：每个执行计划都会有一个id。如果是联合查询，这里会显示几个idselect_type：表示select查询类型，常见的有SIMPLE（普通查询，即没有联合查询/子查询）、PRIMARY（主查询）、UNION(subqueryinUNION),SUBQUERY（子查询）表：执行查询计划的表，这里我查表，所以显示的是表，那么如果我给表起个别名a，这里显示的是一个类型：执行查询的方式。这是我们分析SQL优化时一个非常重要的指标。这个值从好到坏依次为：system>const>;eq_ref>ref>range>index>ALLsystem/const：表示表中只有一行数据匹配。这时候查询一次索引就可以找到对应的数据。eq_ref:Useuniqueindexscanning,多表join时经常用到主键和唯一索引作为关联条件时，可以看到ref:non-uniqueindexscan,也可以看到range:索引范围在唯一索引的最左原则匹配扫描中扫描。比如查询条件使用<,>,between等条件index:索引全表扫描，此时会遍历整棵索引树ALL:表示全表扫描，即需要遍历整个表找到对应的rowpossible_keys：表示可能使用的索引key：实际索引key_len：使用的索引长度ref：关联id等信息rows：找到条件时扫描的行数，这里虽然有10万条数据，但是因为有索引，所以扫描了99行数据Extra：额外的信息，common有以下几种Usingwhere：不需要读取表中的所有信息，只需要通过指数。当表的所有请求列都在同一个索引部分中时，就会发生此过程。Usingtemporary：表示mysql需要使用一个临时表来存储结果集，常见于groupby/orderbyUsingfilesort：当查询语句中包含orderby操作时，orderby后面的内容不是索引，没有办法使用索引来完成排序。会用到“文件排序”，例子中，创建的索引是id，但是我的查询语句orderby后面是a，没办法使用索引Usingjoinbuffer:usingtheconnectioncacheUsingindex:using覆盖索引如果你对这些参数有很好的理解，那么EXPLAIN的内容对你来说就不难了ShowProfileAnalysisSQL执行Performance通过EXPLAIN分析执行计划，只能解释SQL的外部执行。如果想知道mysql具体是怎么查询的，需要通过ShowProfiles来分析。您可以使用SHOWPROFILES；statement查询最近发送到服务器的SQL语句。默认情况下，是记录最近执行过的15条记录，如下图：我想看具体的语句，你看到Query_ID了吗？然后运行??SHOWPROFILEFORQUERY82；这个命令没问题：Yes结果中可以看到，Sendingdata耗时最长。这是因为此时mysql线程开始读取数据，并将数据返回给客户端。在这个过程中，会有大量的磁盘I/O操作，通过这个SQL语句的分析，我们可以知道在查询过程中是磁盘I/O影响查询速度还是系统锁影响查询速度SQL语句的处理过程。知道了病因之后，对症下药就容易多了。怎么可能进行分页查询呢？它更快。使用分页查询的时候，会用到limit关键字，但是对于分页查询，其实可以优化一步。我这里给的数据库不是很好，因为太简单了，没有区别。我用当前项目中正在使用的表作为实验，可以看出区别（使用的SQL语句如下）：EXPLAINSELECT*FROM`te_paper_record`ORDERBYidLIMIT10000,20;EXPLAINSELECT*FROM`te_paper_record`WHEREid>=(SELECTidFROM`te_paper_record`ORDERBYidLIMIT10000,1)LIMIT20;上图中，我没有使用子查询，可以看到执行耗时0.033s。在下面的查询中，我使用了子查询进行优化，可以看到执行耗时0.007s，优化后的结果还是很不错的。很明显，为什么使用子查询时查询速度会提高呢？这是因为当我们不使用子查询的时候，查询到的10020行数据都返回了，那么这10020行数据是不是可以在进行过滤操作后直接返回需要的20行数据，这样你不需要再做过滤操作，直接返回即可？你也太聪明了吧？子查询就是这样做的，所以在查询时间上有很大的优化。正确的select开启方式有时候是为了在查询的时候省事，直接使用select*fromtablewhereid=1这样的SQL语句，但是这样的写法在某些环境下会有一定的性能损失，所以最好selectquery是查询需要的任何字段。一般在查询的时候，都会有条件。此时打开select的正确方式是什么？?如果可以使用主键索引，where后面的条件为什么要优先使用主键索引呢？这就需要MySQL的存储规则了。MySQL常用的存储引擎有MyISAM和InnoDB。InnoDB会创建一个主键索引，主键索引属于聚簇索引，即在存储数据时，索引是基于B+树的，具体的行数据存储在叶子节点中。即如果通过主键索引查询，则直接查找B+树来查询数据。如果不是通过主键索引进行查询，需要先查找索引树，在B+树上获取值，然后在B+树上查找符合条件的数据。这个过程就是“还表”。显然，返回表可以产生时间。这也是为什么建议，对于where后面的条件，优先考虑主键索引等优化。看完以上，你应该心里有数了。SQL调优主要是建立索引/防止锁等待/使用合适的SQL语句查询但是，如果你问你，除了索引，除了上述方法之外，还有其他调优方法吗？竟然还有？！是的，这个是需要跳出来的，不要局限于具体的SQL语句，需要在数据库设计之初就考虑好。比如我们常说要遵循三大范式，但是在某些业务场景下，如果数据库中的冗余字段比较多，性能可能会比严格遵循三大范式带来的性能要好很多。但这一点是对平时积累的考验。阿芬在这里提出这个观点后，希望各位读者看看自己项目中目前使用的数据库中是否有多余的字段。为什么要这样设计？这么多要观察，你的技术能力很难提升，就是这样~
