吕尚《酒店慢查询分析》

•基本方法•索引与性能•死锁问题•字段类型与性能•NULL陷阱•insertinto...select...问题•基本方法–SQL调整--索引调整、语句调整–硬件调整--添加内存、ssd–设计调整--结构修改、应用修改•基本方法•SQL调整–慢查询缺少索引•添加索引idx_substatus_depcity_arrcity•执行效率提升不明显•执行计划–添加索引前–添加索引后•设计调整–进一步修改逻辑•fsubscribe_mobile、fsubscribe_info两表合并•去掉条件fi.endDate='2013-12-17'•分页量1000改为3000，减少查表次数•修改后的语句：•调优结果–cacti•添加索引后出现第一次下降•全部优化完成后，大幅下降•基本方法•索引与性能•死锁问题•字段类型与性能•NULL陷阱•insertinto...select...问题•索引与性能–高性能索引•从一个大范围的数据中找出极小的一部分–在选择性高的字段上添加索引–通过覆盖索引避免回表•使用联合索引代替左前缀单字段索引–idx_t1(a,b,c)–idx_t2(a,b)–idx_t3(a)•不使用函数索引–UNIX时间戳转换为日期FROM_UNIXTIME()–日期转换为UNIX时间戳用函数UNIX_TIMESTAMP()•冗余索引–idx_t1(a,b)oridx_t2(a)–思考：以下语句排序能否被优化？•select*fromTwherea=1012orderbyid;–idx_t1(a,b,c)–思考：以下语句排序能否被优化？•select*fromTwherea=1021andbin('Bob','Niky')orderbycdesc;•函数索引–修改举例•基本方法•索引与性能•死锁问题•字段类型与性能•NULL陷阱•insertinto...select...问题•死锁问题–死锁概念•两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象•innodb的行级锁粒度特性•系统检测到死锁后，会自动回滚其中事务较小的一个•死锁问题–锁的兼容性共享锁S排他锁X共享锁S兼容冲突排他锁X冲突冲突•死锁问题–innodb的记录锁类型：•recordlock--索引记录上的锁•gaplock--索引记录之间存在的锁(或者在最开始的索引之前/最后的索引之后)•next-keylock--以上两种锁的混合–日常操作的锁类型•deletefrom...where...：exclusivenext-keylock•update...where...：exclusivenext-keylock•insert...：anexclusivelockontheinsertedro•死锁举例–报错信息•死锁举例–报错信息•过程分析获得image_info表的240872记录的Xnext-keylock无影响无影响获得room_info表的240872记录的Snext-keylock申请room_info表的Xrecordlock240872，等待！申请image_info表的Xrecordlock240873，等待！•过程分析–room_info表产生锁等待没有任何疑问–image_info表为什么会锁等待？•240872next-keylock范围：[240871,240873)•但是！大家思考一下，240872记录的前后一定是这两个记录值吗？答案是否定的！–情况1：不存在240873记录，此时的锁定范围：[240871,240872)[240872,240874),此时insert240873必然会锁等待–情况2：不存在240872之后的记录，此时的锁定范围：[240871,240872)[240872,之后的所有值)，此时的insert240873也必然产生锁等待–这次的死锁例子就是这两种情况其中之一（已无法具体辨明）•总结一下–结果•两个事务陷入互相等待的状态，死锁产生•MySQL检测到死锁后，回滚了事务较小的Transaction1，将导致死锁的两个insert语句写入日志–建议•线上不要使用这种子查询语句•基本方法•索引与性能•死锁问题•字段类型与性能•NULL陷阱•insertinto...select...问题•字段类型与性能–选择合适的字段类型–join连接字段保持一致•字段类型•字符集（包括校验规则）–utf8_general_ci–utf8_bin•字段类型与性能•字段类型与性能–`depTime`varchar(20)DEFAULTNULLCOMMENT'出发时间(包括日期)',•执行计划–修改depTime字段类型•将depTime改为datetime类型•最终修改后的执行计划(不改变字段类型，只在where条件中带入字符串类型定值，虽然用到索引，但性能不及datetime)•基本方法•索引与性能•死锁问题•字段类型与性能•NULL陷阱•insertinto...select...问题•NULL陷阱–概述•NULL陷阱–判断表达式•wherecolumnisnull•wherecolumnisnotnull•NULL陷阱–null与notin•selectdistincta.clientidfromV_DATA.V_CLIENT_SHARDawherea.clientidin(selectdomainfromV_DATA.V_WRAPPERS)anda.clientid='xzc.trade.qunar.com';•Emptyset(0.01sec)•selectdistincta.clientidfromV_DATA.V_CLIENT_SHARDawherea.clientidnotin(selectdomainfromV_DATA.V_WRAPPERS)anda.clientid='xzc.trade.qunar.com';•Emptyset(0.04sec)•NULL陷阱–null与notin•V_CLIENT_SHARD表中是否存在记录xzc.trade.qunar.com？•null陷阱–null与notin•null陷阱–null与notin•基本方法•索引与性能•死锁问题•字段类型与性能•NULL陷阱•insertinto...select...问题•insert...select问题–语句格式：•insertintotarget_tbselect*fromsource_tb(where...);–影响：•执行过程中对source_tb的影响巨大！！！•insert...select问题•insert...select问题–日常操作的锁类型(补充)•insertintoTselect...fromSwhere...–insert：exclusivelock(record)–select：shardnext-keylocks(rows)•insert...select问题–修改参数后再次测试•insert...select问题•insert...select问题–看似是满足逻辑要求的，但是我们思考一下，binlog中会如何记录？•若Update操作先进行提交，那么在binlog中记录会先于insert…select，按照这种顺序复制到从库执行，target_tb的最终数据很显然与主库不符•insert...select问题–接下来的问题•insert...select问题–查看状态视图•session2的insert被阻塞•锁类型为：auto_inclock•insert...select问题–auto-inclock•自增长列插入操作时添加的锁–inserttypes:•simpleinsert--insert、replace...•bulkinsert--insert...select、Loaddata...–innodb_autoinc_lock_mode•0：一切insert加表级锁•1：simpleinsert通过缓存中的计数器计算自增累加bulkinsert加表级锁•2：一切insert都通过缓存计数器计算自增值•insert...select问题–总结：•insert...select语句会同时影响source_tb和target_tb，阻塞并发操作•应尽量避免insert...select这类无法确定行数的操作•若确实需要，则应尽量减小每个语句执行的时间，分段执行缩短锁定时间和范围