一 简介:此文章为经历过的sql案例集合和相关思路
二 案例1:
现象: 测试环境出现select语句,join2张表多次join,explain结果如下 出现 using where,using join buffer （Block Nested Loop)
思路分析:BNL 5.6优化,首先就看连接字段是否有索引(也有可能有索引没加上),发现没有索引
解决办法: 连接字段加上索引,问题解决(是不是很简单)
案例2
现象:线上出现select并发语句,语句比较复杂,就不贴出来了
思路分析:explain下发现多个join后面的连接字段没有索引
解决办法:连接字段加上索引,问题解决
案例3
select * from a where ctime <= DATE_FORMAT(DATE_ADD(SYSDATE(), INTERVAL - 30 DAY), '%Y-%m-%d')
现象: 此语句全表扫描
思路分析: 很奇怪的语句,ctime有索引,而且函数并没有作用于索引本身,为什么不走索引呢,尝试用字符串日期代替函数本身,explain下是走索引的,问题出现在函数这块
解决办法: 将sysdate去掉,用now取代,explain下发现,已经走索引
补充
sysdate和now的区别
1 select sysdate(),sleep(2),sysdate() select now(),sleep(2),now()
SYSDATE获取当时实时的时间,在语句执行中是不断变化
NOW()取的是语句开始执行的时间,在整个语句执行过程中都不会变化。
2 sysdate在函数计算中不走索引,now可以走索引,前提这个时间字段本身是索引项
3 选择sysdate可能导致主从同步问题
建议 在mysql语句中建议采用now函数
案例 4
select * from table where id in ( id1 ,id 2,。。。）
现象 查询很慢,id存在索引,但是并没有走
思路分析:由于此表数据量大,可能存在因为数据量导致的索引失效问题,所以采用 force index进行尝试
解决办法: 采用force index方式进行查询
补充: explain下的force index出现了失真,扫描行数居然是表数据量的几倍,暂时没找到原因,所以,不要过分依赖explain的结果,自己去查询试试
案例5
select * from table where a='' order by b
现象 已经建立了 a和b的联合索引,依然出现了file sort文件排序
思路分析: 调出表结构发现 a varchar(200) b varchar(300)，远远超过了5.6规定的767字节,那为什么还能建立成功呢,原来5.7已经放开了限制,已经调大到3000+.但是依然只能用到前缀索引
解决办法: 缩短字段长度,尽量索引长度不要超过767字符
补充: 5.7虽然放开了索引长度的限制,但是毫无意义,也请大家尽量规范好字符串的长度,防止发生上述问题
案例6
select * from table where a= b= order by c
现象 已经建立了 a c 联合索引 ,但是效率很低
思路分析: 首先通过explain发现，扫描行数并不多 也就3W多行,但是令人奇怪的是,建立了a c联合的索引,却只用到了a,而且extra中并没有出现文件排序
解决方法: 建立 a b c三者的联合索引。
总结:不要单单看扫描行数少就认为不要优化,让sql走正确的索引,去掉不正确的,这也是一个非常重要的原则
案例7
select * from table where a in () and b > ' ' order by b desc
索引情况 (a b) (b a) 联合索引
explain情况 走了(b a)的索引 但是只是走了部分没有走完全,而且extra没有文件排序
分析 大概可知执行顺序 是先进行了索引排序 范围取值 然后导致联合索引中断,去掉order by发现就走了(a,b)联合索引,这是典型的由于排序导致的索引的错误选择
解决方法 改写sql select * from (select * from table where a in () and b >'') t order by t,b desc 将排序放在外循环即可
案例8
select injer join injer join injer join group by order by limit 10 ,20
分析 根据expain分析 扫描行数只有2千行,但是执行用了2min,既然不能依赖explain 就定位哪里出了问题.
1 先去掉 group by order by 执行 结果速度一样,证明不是分组排序导致
2 先执行第一个injer join 记录时常
3 再 加入一个injer join 再记录时常
最后发现 每加一个injer join表 执行时间就耗时增加一倍,这样问题就好定位了
解决方法 将第一个injer join改成 in () 速度比之前快了很多,
总结 1 减少join的次数,用in代替 2 当explain无法分析出原因的时候 要手动进行排查定位