1.EXPLAIN用于优化MySQL。我们应该善用EXPLAIN来查看SQL的执行计划。下面我们举一个简单的例子，将我们要关注的数据类型列和连接类型标注为（1,2,3,4,5）。一个好的SQL语句至少要达到范围级别。避免使用所有级别的键列和索引名称。如果未选择索引，则值为NULL。您可以使用强制索引方法key_len列，索引长度rows列和要扫描的行数。该值是一个估计的额外列，详细。注意常见的不友好值有：Usingfilesort，Usingtemporary2.SQL语句中IN包含的值不要太多。MySQL对IN进行相应的优化，即将IN中的所有常量存储在一个数组中，并对这个数组进行排序。但是，如果值很大，消耗会比较大。又如：selectidfromtable_namewherenumin(1,2,3)对于连续值，可以用between代替in；或使用连接替换。3、SELECT语句必须指定字段名SELECT*增加了很多不必要的消耗（cpu、io、内存、网络带宽）；增加使用覆盖索引的可能性；当表结构发生变化时，之前的break也需要更新。所以需要在select之后直接连接字段名。4.当只需要一条数据时，使用limit1，这是为了让EXPLAIN中的type列达到const类型。5.如果排序字段不使用索引，尽量少排序。如果oror两边有一个字段不是索引字段，其他条件也不是索引字段，则查询不会使用索引。很多情况下，用unionall或union（必要时）代替“或”会得到更好的结果七、尽量用unionall代替unionunion和unionall的区别主要是前者需要合并结果集独特的过滤操作，涉及到排序，增加大量CPU操作，增加资源消耗和延迟。当然，unionall的前提是两个结果集中没有重复的数据。8.不要使用ORDERBYRAND()selectidfrom`table_name`orderbyrand()limit1000;上面的sql语句可以优化为selectidfrom`table_name`t1join(selectrand()*(selectmax(id)from`table_name`)asnid)t2ont1.id>t2.nidlimit1000;9、区分in和exists，notin和notexistsselect*fromtableAwhereidin(selectidfromtableB)上面的sql语句等同于select*fromtableAwhereexists(select*fromtableBwheretableb.id=tableA.id)区分in和exists主要是因为驱动顺序的变化（这是性能变化的关键），如果是exists，那么outertable就是驱动表，也就是先访问，如果是IN，则先执行子查询。所以IN适用于外观大而内表小的情况；EXISTS适用于外观小，内表大的情况。关于notin和notexists，建议使用notexists，不仅仅是效率问题，对于notin可能会有逻辑问题。如何高效的写一个替换notexists的sql语句？原始SQL语句selectcolname...fromAtablewherea.idnotin(selectb.idfromBtable)高效的SQL语句selectcolname...fromAtableLeftjoinBtableonwherea.id=b。id在哪里b。id为null取出的结果集如下图所示。A表中的数据不在B表中。10.使用合理的分页方式提高分页效率selectid,namefromtable_namelimit866613,20有些人可能会发现随着表数据量的增加，直接使用查询limit分页会越来越慢。优化方法如下：可以取上一页最大行数的id，然后根据最大id限制下一页的起点。比如这一列，上一页最大的id是866612。SQL可以这样写：selectid,namefromtable_namewhereid>866612limit2011.分段查询在一些用户选择页面，可能有的用户选择的时间范围太大，导致查询慢。主要原因是扫描线太多。这时候可以使用程序进行分段查询，循环遍历，合并结果显示。在下图所示的sql语句中，当扫描行数在百万以上时，可以使用分段查询。12、避免在where子句中判断字段的空值。null的判断会导致引擎放弃使用索引，进行全表扫描。十三。不建议使用%前缀模糊查询，如LIKE"%name"或LIKE"%name%"。这种查询会导致索引失效，进行全表扫描。但是你可以使用LIKE"name%"。那么如何查询%name%呢？如下图所示，虽然在secret字段中添加了索引，但并没有在explain结果中使用。那么如何解决这个问题呢，答案：使用全文索引，selectid,fnum,fdstfromtable_namewhere在我们查询user_namelike'%zhangsan%'的时候经常用到；.对于这样的语句，普通的索引是无法满足查询要求的。幸运的是，在MySQL中，有全文索引可以帮助我们。创建全文索引的SQL语法为：ALTERTABLE`table_name`ADDFULLTEXTINDEX`idx_user_name`(`user_name`);使用全文索引的SQL语句为：selectid,fnum,fdstfromtable_namewherematch(user_name)against('zhangsan'inbooleanmode);注意：在需要创建全文索引之前，请联系DBA确定是否可以创建。同时需要注意的是，查询语句的写法与普通索引的写法不同。十四、避免对where子句中的字段进行表达式操作，如selectuser_id,user_projectfromtable_namewhereage*2=36;，这将导致引擎放弃使用索引。建议改为selectuser_id,user_projectfromtable_namewhereage=36/2；15.避免where子句中列字段类型与传入参数类型不一致的隐式类型转换发生类型转换时，建议先判断where中的参数类型。16.对于联合索引，必须遵循最左前缀规则。例如，索引包含字段id、name和school。可以直接使用id字段，也可以按照id、name、butname的顺序；学校不能使用这个索引。因此，在创建联合索引时，一定要注意索引字段的顺序。常用的查询字段放在顶部。十七、必要时可以使用forceindex强制查询使用某个索引。有时MySQL优化器会使用它认为合适的索引。去检索sql语句，但是它可能使用的索引不是我们想要的。这时候可以使用forceindex强制优化器使用我们做的索引。十八、注意范围查询语句对于联合索引，如果有范围查询，比如between，>，<等条件，会导致后面的索引字段失效。19.关于JOIN优化LEFTJOINA表为驱动表INNERJOINMySQL会自动找数据少的表作为驱动表RIGHTJOINB表为驱动表注意：MySQL中没有fulljoin，你可以使用以下方法解决select*fromAleftjoinBonB.name=A.namewhereB.nameisnullunionallselect*fromB;尽量使用innerjoin，避免leftjoin参与至少2张表的联合查询，一般会有大小差异点。如果连接方式是innerjoin，在没有其他过滤条件的情况下，MySQL会自动选择小表作为驱动表，而leftjoin在驱动表的选择上遵循从左向右驱动的原则，即即，驱动器表的leftjoin左侧的表名。合理使用索引驱动表的索引字段作为on的限制字段。用小表驱动大表从示意图中可以直观的看出，如果可以减少驱动表，就可以减少嵌套循环的循环次数，从而减少总的IO和CPU的运算次数。熟练使用STRAIGHT_JOINinnerjoin被mysql选为驱动表，但是在某些特殊情况下，需要选择另外一张表作为驱动表，比如有groupby，orderby等的时候。"Usingfilesort"and“使用临时”。STRAIGHT_JOIN强制连接顺序，STRAIGHT_JOIN左边的表名是驱动表，右边是从动表。使用STRAIGHT_JOIN的前提是查询是innerjoin，即内连接。其他链接不建议使用STRAIGHT_JOIN，否则可能导致查询结果不准确。这种方法有时可以减少3倍的时间。这里仅提供以上优化方案，当然还有其他的优化方式，大家可以探索尝试，感谢阅读。.更多学习内容，可访问【与各大厂商对比】优质PHP架构师教程目录。只要能读懂，就能保证你的薪水更上一层楼（持续更新中）。以上内容希望对大家有所帮助。很多PHPer总会遇到一些问题和瓶颈，业务代码写多了没有方向感，不知道从哪里开始改进，我整理了一些这方面的资料，包括但不限于：分布式架构，高可扩展性、高性能、高并发、服务器性能调优、TP6、laravel、YII2、Redis、Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等知识点免费分享给你如果你需要高级干货。有需要的可以点击链接领取高级PHP月薪30k>>>架构师成长之路【免费获取视频和面试资料】
