本文将和大家聊一聊项目中常用的MySQL优化方法。下面举个简单的例子，标记（1,2,3,4,5）我们要关注的数据：类型列，连接类型。一个好的SQL语句至少要达到范围级别。避免所有级别。键列，使用的索引名称。如果未选择索引，则值为NULL。可以采用强制索引。key_len列，索引长度。rows列，要扫描的行数。该值是一个估计值。额外专栏，详细说明。注意常见的不友好值如下：Usingfilesort，Usingtemporary。2、SQL语句中IN包含的值不宜过多。MySQL对IN进行了相应的优化，即将IN中的所有常量存储在一个数组中，并对数组进行排序。但是，如果值很大，消耗会比较大。又如：selectidfromtwherenumin(1,2,3)对于连续值，能用between就不要用in；或使用连接替换。3.SELECT语句必须指定字段名SELECT*增加了很多不必要的消耗（CPU、IO、内存、网络带宽）；增加使用覆盖索引的可能性；当表结构发生变化时，之前的break也需要更新。所以需要在select之后直接连接字段名。4.当只需要一条数据时，使用limit1使EXPLAIN中的type列达到const类型。5.如果排序字段不使用索引，尽量少排序。如果oror两边有一个字段不是索引字段，其他条件也不是索引字段，则查询不会使用索引。在很多情况下，使用unionall或union（必要时）代替“或”会得到更好的结果。7、尽量用unionall代替unionunion和unionall。union和unionall的主要区别是前者需要合并结果集，然后进行唯一的过滤操作，会涉及到排序，增加大量的CPU运算，增加资源消耗和延迟。当然，unionall的前提是两个结果集中没有重复的数据。8.不要使用ORDERBYRAND()selectidfrom`dynamic`orderbyrand()limit1000;上面的SQL语句可以优化为：selectidfrom`dynamic`t1join(selectrand()*(selectmax(id)from`dynamic`)asnid)t2ont1.id>t2.nidlimit1000;9、区分in和exists，notin和notexistsselect*fromtableAwhereidin(selectidfromtableB)上面的SQL语句等同于select*fromtableAwhereexists(select*fromtableB.id=tableA.id)in和exists的区别主要是导致驱动顺序的变化（这是性能变化的关键）。如果存在，那么外表就是驱动表，首先被访问。如果是IN，那么先执行子查询。.因此，IN适用于外观大而内表面小的场合；EXISTS适用于外观小而内表面大的情况。关于notin和notexists，建议使用notexists，不仅仅是效率问题，对于notin可能会有逻辑问题。如何高效的编写一条替换notexists的SQL语句？原始SQL语句：selectcolname...fromAtablewherea.idnotin(selectb.idfromBtable)高效SQL语句：selectcolname...fromAtableLeftJoinBtableonwherea.id=b.idwhereb.idisnull取出的结果集如下图所示，A表中的数据不在B表中：10.使用合理的分页方式提高分页效率selectid,namefromproductlimit866613,20使用时上面的SQL语句进行分页，有些人可能会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。优化方法如下：可以取上一页最大行数的id，然后根据最大id限制下一页的起点。比如这一栏，上一页***的id是866612。SQL可以这样写：selectid,namefromproductwhereid>866612limit2011，分段查询在一些用户选择页面中，可能部分用户选择的时间范围过大，导致查询缓慢。主要原因是扫描线太多。这时候可以使用程序进行分段查询，循环遍历，合并结果显示。在如下图的SQL语句中，当扫描的行数超过***以上时，可以使用分段查询：12、避免在where子句中判断字段的空值。null的判断会导致引擎放弃使用索引，进行全表扫描。13、不推荐使用%前缀模糊查询，如LIKE"%name"或LIKE"%name%"。这种查询会导致索引失效，进行全表扫描。但是你可以使用LIKE"name%"。那么如何查询%name%呢？如下图，虽然在secret字段上加了索引，但是explain结果中并没有用到：那么如何解决这个问题呢，答案：使用全文索引。selectid,fnum,fdstfromdynamic_201606whereuser_namelike'%zhangsan%';在我们的查询中经常使用。对于这样的语句，普通的索引是无法满足查询要求的。幸运的是，在MySQL中，有全文索引可以帮助我们。创建全文索引的SQL语法为：ALTERTABLE`dynamic_201606`ADDFULLTEXTINDEX`idx_user_name`(`user_name`);使用全文索引的SQL语句为：selectid,fnum,fdstfromdynamic_201606wherematch(user_name)against('zhangsan'inbooleanmode);创建全文索引前，请联系DBA确定是否可以创建。同时需要注意的是，查询语句的写法与普通索引的写法不同。14、避免在where子句中对字段进行表达式操作。例如：selectuser_id,user_projectfromuser_basewhereage*2=36；将对字段执行算术运算，这将导致引擎放弃使用索引。建议改成：selectuser_id,user_projectfromuser_basewhereage=36/2;15。为避免隐式类型转换的where子句中列字段的类型与传入的参数类型不一致时发生类型转换，建议先判断where中的参数类型。16.对于联合索引，要遵守最左前缀规则。例如，索引包含字段id、name和school。可以直接使用id字段，也可以按照id、name、butname的顺序；学校不能使用这个索引。因此，在创建联合索引时，一定要注意索引字段的顺序，常用的查询字段放在最前面。17、如果需要，可以使用forceindex强制查询到某个索引。有时MySQL优化器使用它认为合适的索引来检索SQL语句，但它使用的索引可能不是我们想要的。这时候可以使用forceindex强制优化器使用我们做的索引。18.注意范围查询语句对于联合索引，如果有范围查询，比如between,>,<等条件，会导致后面的索引字段失效。19、关于JOIN优化，LEFTJOINA表为驱动表，INNERJOINMySQL会自动找数据少的表作为驱动表，RIGHTJOINB表为驱动表。注意：1）MySQL中没有fulljoin，可以通过以下方式解决：selectuser_id,user_projectfromuser_basewhereage=36/2；2）尽量使用innerjoin，避免leftjoin：参与联合查询的表至少2个表，一般存在size。如果连接方式是innerjoin，在没有其他过滤条件的情况下，MySQL会自动选择小表作为驱动表，而leftjoin在驱动表的选择上遵循从左向右驱动的原则，即即，驱动器表的leftjoin左侧的表名。3）合理使用索引：将被驱动表的索引字段作为on的限制字段。4）用小表驱动大表：从示意图中可以直观看出，如果可以减少驱动表，则可以减少嵌套循环的循环次数，从而减少IO总量和CPU操作数。5）巧妙使用STRAIGHT_JOIN：MySQL选择驱动表进行innerjoin，但在某些特殊情况下，需要选择另外一张表作为驱动表，如groupby，orderby等。《Usingfilesort》和《Usingtemporary》”。STRAIGHT_JOIN强制连接顺序，STRAIGHT_JOIN左边的表名是驱动表，右边是从动表。使用STRAIGHT_JOIN的前提是查询是innerjoin，即内连接。其他链接不建议使用STRAIGHT_JOIN，否则可能导致查询结果不准确。这种方法有时可以减少3倍的时间。以上19种MySQL优化方法希望对大家有所帮助！
