江苏龙网

  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
【没内鬼，来点干货！SQL优化和诊断】说明：在很多情况下我们已知数据仅存在一条 ， 此时我们应该告知数据库只用查一条 ， 否则将会转化为全表扫描
#反例（耗时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语句一般业务要求都是'%关键字%'这种形式 ， 但是依然要思考能否考虑使用右模糊的方式去替代产品的要求 ， 其中阿里的编码规范提到：
页面搜索严禁左模糊或者全模糊 ， 如果需要请走搜索引擎来解决
#反例（耗时78.843s）EXPLAINselect*fromtask_resultwheretaskidLIKE'%tt600e6b601677b5cbfe516a013b8e46%'LIMIT1;#正例（耗时0.986s）select*fromtask_resultwheretaskidLIKE'tt600e6b601677b5cbfe516a013b8e46%'LIMIT1###########################################################################对正例的Explain1SIMPLEtask_resultrangeadapt_idadapt_id9899100.00Usingindexcondition#对反例的Explain1SIMPLEtask_resultALL3362855411.11Usingwhere#说明task_result表为生产环境的一个表 ， 总数据量为3400万 ， taskid是一个普通索引列 ， 可见%%这种匹配方式完全无法使用索引 ， 从而进行全表扫描导致效率极低 ， 而正例通过索引查找数据只需要扫描99条数据即可复制代码避免SQL中对where字段进行函数转换或表达式计算#反例select*fromtask_resultwhereid+1=15551;#正例select*fromtask_resultwhereid=15550;###########################################################################对正例的Explain1SIMPLEtask_resultconstPRIMARYPRIMARY8const1100.00#对反例的Explain1SIMPLEtask_resultALL33631512100.00Usingwhere#说明其实在知道了有SQL优化器之后 ， 我个人感觉这种普通的表达式转换应该可以提前进行处理再进行查询 ， 这样一来就可以用到索引了 ， 但是问题又来了 ， 如果mysql优化器可以提前计算出结果 ， 那么写sql语句的人也一定可以提前计算出结果 ， 所以矛盾点在这个地方 ， 导致5.7版本以前的此种情况都无法使用索引吧 ， 未来可能会对其进行优化使用ISNULL()来判断是否为NULL值说明：NULL与任何值的直接比较都为NULL
#1）NULL<>NULL的返回结果是NULL ， 而不是false 。 #2）NULL=NULL的返回结果是NULL ， 而不是true 。 #3）NULL<>1的返回结果是NULL ， 而不是true 。 多表查询我所在的公司基本禁止了多表查询 ， 那如果必须使用到的话 ， 我们可以一起参考一下阿里的编码规范
Eg：超过三个表禁止join 。 需要join的字段 ， 数据类型必须绝对一致；多表关联查询时 ， 保证被关联的字段需要有索引
明明有索引为什么还走全表扫描之前回答一些面试问题的时候 ， 对某一个点的理解出现了偏差 ， 即我认为只要查询的列有索引则一定会使用索引去Push数据
然而实际上不仅仅是这样 ， 真正应该是：针对查询的数据行占总数据量过多时会转化成全表查询
那么这个过多指代的是多少呢？
我的测试结果是50% ， 但个人认为MySQL优化器不会完全纠结于行数区分是否全表 ， 而是有很多其他因素综合考虑发现全表扫描的效率更高等等 ， 所以充分认识到该问题即可
count(*)还是count(id)阿里的Java编码规范中有以下内容：
【强制】不要使用count(列名)或count(常量)来替代count(*)
count(*)是SQL92定义的标准统计行数的语法 ， 跟数据库无关 ， 跟NULL和非NULL无关 。
说明：count(*)会统计值为NULL的行 ， 而count(列名)不会统计此列为NULL值的行
字段类型不同导致索引失效阿里的Java编码规范中有以下内容：
【推荐】防止因字段类型不同造成的隐式转换 ， 导致索引失效
实际上数据库在查询的时候会作一层隐式的转换 ， 比如varchar类型字段通过数字去查询
#正例EXPLAINSELECT*FROM`user_coll`wherepid='1';type：refref：constrows:1Extra:Usingindexcondition#反例EXPLAINSELECT*FROM`user_coll`wherepid=1;type：indexref：NULLrows:3(总记录数)Extra:Usingwhere;Usingindex#说明pid字段有相应索引 ， 且格式为varcharTips自建数据表进行测试
CREATETABLE`student`(`id`bigint(20)NOTNULLAUTO_INCREMENTCOMMENT'主键',`name`varchar(255)NOTNULL,`class`varchar(255)DEFAULTNULL,`page`bigint(20)DEFAULTNULL,`status`tinyint(3)unsignedNOTNULLCOMMENT'状态：0正常 ， 1冻结 ， 2删除',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=0DEFAULTCHARSET=utf8mb4插入数据
DELIMITER;;CREATEPROCEDUREinsertData()BEGINdeclareiint;seti=1;WHILE(i<1000000)DOINSERTINTOstudent(`name`,class,`page`,`status`)VALUES(CONCAT('class_',i),CONCAT('class_',i),i,(SELECTFLOOR(RAND()*2)));seti=i+1;ENDWHILE;commit;END;;CALLinsertData();


    推荐阅读


    上一篇：“卖身”之后热度大减，坚果和美图，都找对“东家”了吗？

    下一篇：支撑反恐作战的高科技装备