SQL优化PPT课件.pptx

及TMS中的优化实践 SQL优化 梁泽宇 liangzeyu 2018 TMS是什么 为什么要优化 1 教师 学生 课程 报名2 财务3 资源4 目标5 数据统计 2020 3 19 2 发现问题 工欲善其事 必先利其器1 APM工具 OneApm Pinpoint2 MySQL慢sql日志3 开发环境看日志 蠢了点 实用 2020 3 19 3 解决问题 1 代码优化原则 空间换时间 减少重复用功 思想 缓存 缓存计算结果 缓存获取的数据等 这里的缓存不单指redis memcached 任何的计算中间值都可以 避免重复运算 2 IO优化 包括SQL优化3 业务逻辑优化1 牺牲非必要功能获取巨大性能提升2 牺牲实时性获取性能提升 2020 3 19 4 SQL优化思路 1 Explainmysql explainselectsql no cacherental datefromt1whereinventory id 80000 G 1 row id 1select type SIMPLEtable t1type rangepossible keys inventory idkey inventory idkey len 3ref NULLrows 153734Extra Usingindexcondition1rowinset 0 00sec 2020 3 19 5 1 Usingwhere这意味着mysql服务器将在存储引擎检索行后再进行过滤 许多where条件里涉及索引中的列 当它如果并且读取索引时 就能背存储引擎检验 因此不是所有带有where子句的查询都会显示usingwhere 2 Usingindex这个值表示mysql将使用覆盖索引 以避免访问表 不要把覆盖索引和type index访问类型混淆了 3 UsingindexconditionIndexConditionPushdown ICP 是MySQL5 6版本中的新特性 是一种在存储引擎层使用索引过滤数据的一种优化方式4 Usingfilesort这意味着mysql会对结果使用一个外部索引排序 而不是按照索引次序从表里读取行 mysql有两种文件排序算法 两种方式都可以在内存或磁盘文件上完成 EXPLAIN不会告诉你mysql将使用哪一种文件排序 也不会告诉你排序会在内存里还是磁盘上完成 5 Usingtemporary这意味着mysql对查询结果排序时会使用一个临时表 当多表连接排序时出现 2020 3 19 6 为什么要用索引 1 减少IO开销 提升查找速度索引可以大大减少服务器需要扫描的数据量索引可以帮助服务器避免排序和临时表 2 减少锁定行带来的额外的开销 减少死锁几率 提高并发性能 2020 3 19 7 索引B 树 特点 针对磁盘设计基于数据页每次io开销9ms控制IO次数非叶子节点只存指针和索引值真实的数据存在于叶子节点h m 1 Nm 磁盘块的大小 数据项的大小数据有序复合索引从左到右 2020 3 19 8 聚集索引和辅助索引 聚集索引 主键索引 聚集索引就是按照每张表的主键构造一颗B 树 同时叶子节点中存放的即为整张表的记录数据辅助索引 二级索引 非主键索引 叶子节点 键值 主键索引值 2020 3 19 9 索引设计原则 1 最适合索引的列是出现在where子句中的列 或者连接子句中指定的列 而不是出现select关键字后的选择列表中的列 2 GroupBy SortBy使用与where同样的索引 并按索引定义排列3 设置复合索引时 充分利用最左优先原则 避免重复索引定义 使用频率 选择性 同时最左前缀中 遇到第一个范围查询 between in不受影响 则后面的列索引就不能使用了 所以一个重要的原则是将范围查询较多的列放在后面 如createTime这些 4 对于InnoDB因为二级索引包换了主键 所以列 A 上的索引就相当与 A ID 所以类似whereA 5orderbyid这样的查询 这个索引会很有用 但是扩展为索引 A B 之后 则实际变成了 A B ID 则orderby就无法用到索引排序了 5 不要盲目的在很多个单列上创建单列的索引 MySQL5 0以后的版本 引入了 索引合并 OR条件的联合 union AND条件的相交 intersection 组合前两种情况的联合及相交 6 使用短索引 如果是字符串索引 如果有可能应指定前缀 因为like前导模糊不支持索引 前缀索引可以更小 更快 但是也有缺点 无法使用前缀索引做orderby和groupby 也无法使用前缀做覆盖扫描 注意 可以把字符串反转后存储 建立前缀索引 其实就相当于建立后缀索引了 例如找到某个域名的所有电子邮件地址 2020 3 19 10 其他SQL优化项 1 on where having 尽量把限制条件放到先执行的地方2 避免在索引列上使用计算 WHERE子句中 如果索引列是函数的一部分 优化器将不使用索引而使用全表扫描 可以把计算放到运算符的右侧 3 避免类型转换4 使用 临时表 暂存中间结果简化SQL语句的重要方法就是采用临时表暂存中间结果 但是 临时表的好处远远不止这些 将临时结果暂存在临时表 后面的查询就在tempdb中了 这可以避免程序中多次扫描主表 也大大减少了程序执行中 共享锁 阻塞 更新锁 减少了阻塞 提高了并发性能 5 避免在索引列上使用ISNULL和ISNOTNULL 将使索引失效 设置表时 除了varchar和text类型之外的都要设置默认值 6 巧用2进制原理记录组合数据 注意日期格式 Date datetime timestamp 数字 2020 3 19 11 覆盖索引 覆盖索引 coveringindex 指一个查询语句的执行只需要从辅助索引中就可以得到查询记录 而不需要查询聚集索引中的记录 也可以称之为实现了索引覆盖 2020 3 19 12 无where条件的查询优化 explainselectcount staff id fromt1执行计划中 type为ALL 代表进行了全表扫描 扫描的行数也与表的行数一致 执行时间0 85saltertablet1addkey staff id 执行计划中 Usingindex表示使用到了索引 执行时间0 7s从索引中获取数据 减少了读取的数据块的数量 通过索引来实现索引覆盖查询 但前提条件是 查询返回的字段数足够少 更不用说select 之类的了 毕竟 建立keylength过长的索引 始终不是一件好事情 2020 3 19 13 二次检索优化 selectsql no cacherental datefromt1whereinventory id 80000 Usingindexcondition 返回8w条记录 0 2saltertablet1addkey inventory id rental date selectsql no cacherental datefromt1whereinventory id 80000 Usingindex 0 1s没有回表查询 节省了8w条记录在主表中的查询时间 2020 3 19 14 分页查询优化 ADDUNIQUEINDEX stuNumber stuNumber 查询一 SELECTstuId stuMemberIdFROMtb res studentORDERBYstuNumberLIMIT300000 100Explain 全表扫描 rows 432017 耗时1 2s查询二 SELECTstuId stuMemberIdFROMtb res studentFORCEINDEX stuNumber ORDERBYstuNumberLIMIT300000 100Explain type index rows 300100 耗时0 9sADDUNIQUEINDEX stuNumber stuNumber stuMemberId 执行查询一0 123s 2020 3 19 15 查询4 SELECTSQL NO CACHE FROMtb res studentt1INNERJOIN SELECTstuIdFROMtb res studentORDERBYstuNumberLIMIT300000 100 t2ONt1 stuId t2 stuId0 145s通过使用覆盖索引查询返回需要的主键 再根据这写主键关联原表获得需要的行 这可以减少mysql扫描那些需要丢弃的行子查询不是魔鬼 当子查询中能命中索引或覆盖索引 且子查询结果集较小时 有奇效覆盖索引在某些场景下 具有意想不到的优化效果 延时关联 2020 3 19 16 GroupBy优化 充分利用最左前缀原理 使groupby orderby用上索引indexidx c1 c2 c3 ontablet1 c1 c2 c3 c4 SELECTc1 c2FROMt1GROUPBYc1 c2 SELECTDISTINCTc1 c2FROMt1 SELECTc1 c2FROMt1WHEREc1constGROUPBYc1 c2 SELECTc1 MIN c2 FROMt1GROUPBYc1 2020 3 19 17 2 考虑是否必须用groupby场景 已有报名表 查找每个已报名学生的收费最多的报名SELECT FROM SELECT FROMtb registrORDERBYr regPayPrice desc ttGROUPBY regStudentId 0 25sSELECT FROMtb registrWHERENOTEXISTS SELECTregIdFROMtb registeWHEREe regStudentId r regStudentIdANDe regCreateDate r regCreateDate 0 108sec 2020 3 19 18 善用子查询 场景 1 select中使用 通过join无法直接获取数据保证子查询能命中索引rows不能太多返回多列技巧 concat取出 SUBSTRING INDEX分隔 或者代码中二次处理2 join子查询的表索引会丢失 即使数据库重建索引也有消耗选择结果集小的作为子查询表考虑用临时表3 where中使用子查询当并不需要使用表中的值而只是过滤用 不用join 尽量使用in或者exist Join会对后续的排序或group产生额外消耗 2020 3 19 19 切分查询 删除数据就是一个很好的例子 清除大量的数据 可能需要锁住大量的数据 占满整个事务 耗尽资源 阻塞很多小的查询 切分是一个很好的办法 比方说 把如下的句子deletefrommessagewherecreate time0一次删除10000行 影响就会很小 压力就会分担开来了 实用性很强 在大量更新操作时分段执行 甚至sleep一下 有效减少服务器压力和避免主从延时问题 2020 3 19 20 分解关联查询LazyLoadvseagerLoad select fromtagjointag postontag post tag id tag idjoinpostontag post post id post idwheretag tag hreo 分解为 select fromtagwheretag hreo select fromtag postwheretag id 1234 select frompostwhereidin 123 546 432 a 让缓存的效率更高 如果第一个查询的结果已经缓存了 那么就可以跳过第一个查询 另外对一MySQL的查询缓存QueryCache来说 如果关联的表发生了修改 就无法使用缓存了 拆分后 那么一个表的改变不会影响其他表的缓存 b 单个查询可以减少锁的竞争 c 在应用层做关联 有更好的扩展性 d 可以减少冗余记录的查询 因为数据库关联查询时 可能需要重复的访问一部分数据 e 这个效果 在负载均衡 或者数据分布在不同的数据库是更明显 缺点 1 需要应用层做关联 依赖框架2 当where中包括多个表的条件 反而增加了消耗 2020 3 19 21 真实场景优化注意项 必须搞懂业务 在功能与性能间取平衡空间换时间 缓存 中间数据牺牲实时性 功能拆分 异步分库分表 垂直 水平 2020 3 19 22 排课优化 场景 为班级匹配满足条件的老师 1 满足教师的排课意愿2 老师排课表和班级的课表不冲突优化 功能拆分 筛选老师仅判断条件1 确定老师时验证条件2把sql的计算逻