大家好，我是Tom，MySQL。相信大家对MySQL都不陌生，但是熟悉了，却未必会用得上！知道怎么用不一定能用好！今天Tom就带大家回顾一下一个高频面试考点，SQL优化有哪些技巧？当然这个还是很实用的，工作中一定要用到。如果应用得当，升职加薪指日可待。1、创建索引，一定要记住创建索引，创建索引，创建索引。创建索引重要的事情说三遍！执行没有索引的SQL语句，必须扫描全表，肯定是慢的。这个查询无疑是一个慢SQL查询。那么问题来了，是否需要收集所有where查询条件，然后为所有组合建立索引呢？答案肯定是否定的。MySQL为了提高数据查询率，采用了B+树结构，采用了以空间换时间的设计思想。另外，每次更新表数据，都要调整相应的索引树，执行效率肯定会受到影响。符合第二十八条原则，互联网要求多读少写，所以一定要找到一个平衡点。阿里巴巴开发者手册建议单表索引数量控制在5个以内，组合索引字段数量不允许超过5个。其他建议：禁止为表中的每一列创建单独的索引。每个Innodb表都必须有一个主键。注意复合索引中字段的顺序。优先考虑覆盖索引并避免使用外键约束。2.避免索引失败。不要以为你有索引，一切都会好起来的。众所周知，索引失效也是造成查询慢的主要原因之一。索引失败的常见场景有哪些？LIKE以%开头的查询。创建了复合索引，但查询条件不满足“最左匹配原则”。如：创建索引。idx_type_status_uid(type,status,uid)，但是使用status和uid作为查询条件。如果在查询条件中使用了or，并且or的前后条件中没有某列的索引，则不会使用涉及的索引。对索引列的操作，函数upper()等，或，!=(<>)，notin等。3.锁粒度MySQL存储引擎分为两类：MyISAM和InnoDB。MyISAM支持表锁；InnoDB支持行锁和表锁。在进行更新操作时，为了保证表数据的准确性，通常会加锁。为了提高系统的高并发能力，我们通常推荐使用行锁来减少锁冲突和锁等待时间。所以存储引擎通常选择InnoDB。行锁可以升级为表锁。有哪些场景？如果批量更新一个表，使用大量的行锁，可能会导致其他事务长时间等待，严重影响事务的执行效率。此时，MySQL会将行锁升级为表锁。行锁是添加到索引的锁。如果条件索引失败，行锁也会升级为表锁。注意：行锁降低了锁的粒度，从而提高了系统的并发性。但也有一个缺点，可能会造成死锁，需要特别注意。4.分页查询优化如果我们要开发一个列表展示页面，支持翻页，我们通常会这样写SQL。select*fromtablelimit#{start},#{pageSize};随着翻页深度的增加，起始值越来越大，例如：limit10000,10。好像只返回10条记录，但是数据库引擎需要查询10010条记录，然后丢弃前10000条记录，最后只返回最后10条记录。性能可想而知。解决这个问题，我们通常有另一种方案：先定位到上次分页的最大id，然后对该id进行条件索引查询。由于数据库的索引采用了B+树结构，所以一步到位。select*fromtablewhereid>#{id}limit#{pageSize};任何事物都有优点和缺点。这种翻页方式只支持上一页和下一页，不支持跨越式翻页。上图是淘宝的商品搜索列表。为了提升用户体验，它还采用了竖梯式的翻页方式。为了避免翻页过多影响性能，在产品交互上做了一些取舍，对总页数进行了限制，最大支持100页。方案二：使用子查询select*fromtablewhereid>(selectidfromtableorderbyidlimit100001)limit20;将原来的单条SQL查询拆分为两步：首先查询一页数据中最小的id。然后通过B+树，准确定位到id最小的索引树节点位置，通过offset读取接下来的20条数据。阿里的规范手册也有相应的描述：5.避免select*Negativecase:select*fromtablewherebuyer_id=#{buyer_id}我们知道MySQL建表后，具体的行数据存放在主键索引中（属于到簇索引）叶节点。二级索引是非聚集索引，其叶节点存储主键值。select*的查询过程：首先在buyer_id的二级索引B+树中找出对应的主键id列表，然后回表查询主键索引中id对应的行数据。因此，我们需要清楚地列出必要的查询字段。并且字段尽量在覆盖索引中，减少回表操作。6.EXPLAIN分析SQL执行计划授人以鱼不如授人以渔。除了知道常见的非标准SQL写法，避免在开发过程中踩坑。我们还应该知道如何对慢速SQL进行故障排除和优化。安排了实验。CREATETABLE`user`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'自增主键',`income`bigint(20)NOTNULLCOMMENT'income',`expend`bigint(20)NOTNULLCOMMENT'支出',PRIMARYKEY(`id`),KEY`idx_income`(`income`))ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8COMMENT='usertable';CREATETABLE`biz_order`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'自增主键',`user_id`bigint(20)NOTNULLCOMMENT'userid',`money`bigint(20)NOTNULLCOMMENT'amount',PRIMARYKEY(`id`),KEY`idx_user_id`(`user_id`))ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8COMMENT='Ordertable';插入记录：insertintouservalues(10,100,100);insertintouservalues(20,200,200);insertintouservalues(30,300,300);insertintouservalues(40,400,400);insertintobiz_ordervalues(1,10,30);insertintobiz_order值（2,10,40）；插入biz_order值（3,10,50）；插入biz_order值（4,20,10）；比如下面的语句，我们可以查看是否使用了索引，通过explain分析出对应的执行计划。解释select*fromuserwhereid<20;接下来我们一一解释一下各个字段id的含义：每次select查询都会产生一个id，值越大优先级越高，先执行。select_type：查询类型，SIMPLE（普通查询，即无联合查询，子查询），PRIMARY（主查询），UNION（UNION中的子查询），SUBQUERY（子查询）等table：查询哪个表。partitions：分区，如果对应的表有分区表，那么这里会显示具体的分区信息。type：执行模式，在SQL优化中是一个很重要的指标。结果值从好到坏依次为：system>const>eq_ref>ref>range>index>ALL。system/const：表中只有一行数据匹配。这时候查询一次索引就可以找到对应的数据。eq_ref：使用唯一索引扫描，常见于多表连接。使用主键和唯一索引作为关联条件ref:Non-uniqueindexscan，也可以看唯一索引匹配扫描范围的最左原则：索引范围扫描，比如<,>,between等操作索引：索引全表扫描，此时遍历整个索引树ALL：表示全表扫描，需要遍历整个表，找到对应的行possible_keys：可能的索引key：实际索引key_len：索引长度ref：关联的idandotherinformationrows：找到记录的扫描行数，SQL优化的重要指标，扫描的行数越少，性能越高filtered：需要的记录数占扫描记录总数的比例Extra：附加信息说明select*从useru,biz_orderbwhereu.id=b.user_idandu.id<20;7,ShowProfile分析SQL执行性能。ShowProfile和EXPLAIN的区别在于前者主要是在外围进行分析；后者深入MySQL内核，从执行线程的状态和时间来分析。MySQL在5.0.37版本之后才支持ShowProfile，选择@@have_profiling返回YES表示开启该功能。mysql>显示配置文件；Emptyset,1warning(0.00sec)显示为空，表示profiles功能关闭。使用以下命令启用：mysql>setprofiling=1;QueryOK,0rowsaffected,1warning(0.00sec)获取Query_ID后，使用showprofileforqueryID查看线程各状态消耗情况SQL语句的执行时间。
