今天汤姆哥带大家回顾一个高频面试考点。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.分页查询优化如果我们要开发一个列表显示页面，支持翻页，我们通常写SQLselect*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最小的索引树节点位置，读取接下来的20条数据通过偏移量。阿里的规范手册也有相应的描述：5.避免select*的负例：select*fromtablewherebuyer_id=#{buyer_id}我们知道，MySQL创建表后，具体行数据存放在的叶子节点主键索引（属于聚集索引）。二级索引是非聚集索引，其叶子节点存储主键值select*。查询过程：首先在buyer_id的二级索引B+树中找出对应的主键id列表，然后回表。在主键索引中，因此我们需要列出必要的查询字段，字段尽量在覆盖索引中，减少回表操作。6、SQL执行计划EXPLAIN分析授人以鱼不如授人以渔。除了知道常见的不规范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'auto-incrementprimarykey',`user_id`bigint(20)NOTNULLCOMMENT'userid',`money`bigint(20)NOTNULLCOMMENT'amount',PRIMARYKEY(`id`),KEY`idx_user_id`(`user_id`))ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8COMMENT='订单表';插入记录：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）；例如下面的语句，我们可以查看是否使用了索引，并通过explainexplainselect*fromuserwhereid<20;分析出对应的执行计划；接下来解释一下每个字段id的含义：每个select查询都会产生一个id，值越大优先级越高，优先执行select_type：查询类型，SIMPLE（普通查询，即没有jointquery,subquery),PRIMARY(主查询),UNION(UNION后的查询),SUBQUERY(子查询)等table:查询哪个表partitions:分区，如果对应的表有分区表，则具体分区这里会显示信息类型：执行方法，是SQL优化中非常重要的指标，结果值从好到坏依次为：system>const>eq_ref>ref>range>index>ALLsystem/const：表中只有一行数据匹配，此时可以根据索引查询一次找到对应的数据eq_ref：使用唯一索引扫描，常见于以主键和唯一索引为关联条件的多表连接ref:非唯一索引扫描，也见于uniqueiindex最左原则匹配scanrange：索引范围扫描，例如<、>、between等操作index：索引全表扫描，此时遍历整个索引树ALL：表示全表扫描，需要遍历整个表找到对应的行possible_keys：可能的索引keys：实际使用的索引key_len：索引长度ref：关联的id等信息行：查找记录扫描的行数，SQL优化的重要指标，扫描的行数越少，过滤的性能越高：所需记录与扫描记录总数的比率Extra：额外信息说明select*fromuseru,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语句的执行时间
