江苏龙网

  1. 首页 > 人文 > >

#架构师师长#来点干货！SQL优化和诊断，没内鬼

Explain诊断Explain各参数的含义如下：
#架构师师长#来点干货！SQL优化和诊断，没内鬼
文章图片
select_type常见类型及其含义SIMPLE：不包含子查询或者UNION操作的查询PRIMARY：查询中如果包含任何子查询 ， 那么最外层的查询则被标记为PRIMARYSUBQUERY：子查询中第一个SELECTDEPENDENTSUBQUERY：子查询中的第一个SELECT ， 取决于外部查询UNION：UNION操作的第二个或者之后的查询DEPENDENTUNION：UNION操作的第二个或者之后的查询,取决于外部查询UNIONRESULT：UNION产生的结果集DERIVED：出现在FROM字句中的子查询type常见类型及其含义system：这是const类型的一个特例 ， 只会出现在待查询的表只有一行数据的情况下consts：常出现在主键或唯一索引与常量值进行比较的场景下 ， 此时查询性能是最优的eq_ref：当连接使用的是完整的索引并且是PRIMARYKEY或UNIQUENOTNULLINDEX时使用它ref：当连接使用的是前缀索引或连接条件不是PRIMARYKEY或UNIQUEINDEX时则使用它ref_or_null：类似于ref类型的查询 ， 但是附加了对NULL值列的查询index_merge：该联接类型表示使用了索引进行合并优化range：使用索引进行范围扫描 ， 常见于between、>、<这样的查询条件index：索引连接类型与ALL相同 ， 只是扫描的是索引树 ， 通常出现在索引是该查询的覆盖索引的情况ALL：全表扫描 ， 效率最差的查找方式阿里编码规范要求：至少要达到range级别 ， 要求是ref级别 ， 如果可以是consts最好
key列实际在查询中是否使用到索引的标志字段
如何查看Mysql优化器优化之后的SQL#仅在服务器环境下或通过Navicat进入命令列界面explainextendedSELECT*FROM`student`where`name`=1and`age`=1;#再执行showwarnings;#结果如下：/*select#1*/select`mytest`.`student`.`age`AS`age`,`mytest`.`student`.`name`AS`name`,`mytest`.`student`.`year`AS`year`from`mytest`.`student`where((`mytest`.`student`.`age`=1)and(`mytest`.`student`.`name`=1))为什么要做这个事呢？我们知道Mysql有一个最左匹配原则 ， 那么如果我的索引建的是age ， name ， 那我以name ， age这样的顺序去查询能否使用到索引呢？实际上是可以的 ， 就是因为Mysql查询优化器可以帮助我们自动对SQL的执行顺序等进行优化 ， 以选取代价最低的方式进行查询（注意是代价最低 ， 不是时间最短）
SQL优化超大分页场景解决方案如表中数据需要进行深度分页 ， 如何提高效率？在阿里出品的Java编程规范中写道：
利用延迟关联或者子查询优化超多分页场景
说明：MySQL并不是跳过offset行 ， 而是取offset+N行 ， 然后返回放弃前offset行 ， 返回N行 ， 那当offset特别大的时候 ， 效率就非常的低下 ， 要么控制返回的总页数 ， 要么对超过特定阈值的页数进行SQL改写
#反例（耗时129.570s）select*fromtask_resultLIMIT20000000,10;#正例（耗时5.114s）SELECTa.*FROMtask_resulta,(selectidfromtask_resultLIMIT20000000,10)bwherea.id=b.id;#说明task_result表为生产环境的一个表 ， 总数据量为3400万 ， id为主键 ， 偏移量达到2000万获取一条数据时的Limit1如果数据表的情况已知 ， 某个业务需要获取符合某个Where条件下的一条数据 ， 注意使用Limit
说明：在很多情况下我们已知数据仅存在一条 ， 此时我们应该告知数据库只用查一条 ， 否则将会转化为全表扫描
#反例（耗时2424.612s）select*fromtask_resultwhereunique_key='ebbf420b65d95573db7669f21fa3be3e_861414030800727_48';#正例（耗时1.036s）select*fromtask_resultwhereunique_key='ebbf420b65d95573db7669f21fa3be3e_861414030800727_48'LIMIT1;#说明task_result表为生产环境的一个表 ， 总数据量为3400万 ， where条件非索引字段 ， 数据所在行为第19486条记录批量插入#反例INSERTintoperson(name,age)values('A',24)INSERTintoperson(name,age)values('B',24)INSERTintoperson(name,age)values('C',24)#正例INSERTintoperson(name,age)values('A',24),('B',24),('C',24);#说明比较常规 ， 就不多做说明了like语句的优化like语句一般业务要求都是'%关键字%'这种形式 ， 但是依然要思考能否考虑使用右模糊的方式去替代产品的要求 ， 其中阿里的编码规范提到：


推荐阅读


上一篇：#手机大魔王#Y50，骁龙665售价1698元，vivo

下一篇：HotDogNews@有一种稳赚的投资方式