开发转兼职DBA（二）：执行计划教我做事

开发转兼职DBA二执行计划教我做事查询慢了不知道为什么加了索引还是慢复合索引怎么建执行计划怎么看——这些不是DBA的专利是每个写SQL的开发者迟早要面对的事。文章目录开发转兼职DBA二执行计划教我做事起点查询慢了怎么办执行计划是什么Oracle怎么看执行计划已经在跑的SQL怎么看我遇到的第一个问题全表扫描解决加索引 改查询索引的本质第二个问题加了索引还是慢第三个问题索引不是越多越好常见的执行计划操作类型统计信息执行计划的基石我学到的几条规矩从SQL消费者到SQL思考者起点查询慢了怎么办上一篇说到社保系统查一个人要30秒。我当时的排查思路是重启数据库 → 没用重启应用服务器 → 没用清一下缓存 → 没用问同事 → 他也不知道百度Oracle查询慢怎么办 → 有人说看执行计划就这样我第一次打开了执行计划。执行计划是什么执行计划就是数据库的施工图。你写一条SQL数据库不会直接执行。它会先想一下这条SQL有几种执行方式每种方式大概花多少成本选成本最低的那种。这个想的过程叫查询优化Query Optimization想出来的方案就是执行计划Execution Plan。Oracle怎么看执行计划EXPLAINPLANFORSELECT*FROMkc22WHEREsfzh110101199001011234;SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);输出大概这样Plan hash value: 1234567890 | Id | Operation | Name | Rows | Bytes | Cost | |----|-----------------------------|------------|------|-------|------| | 0 | SELECT STATEMENT | | 1 | 100 | 2 | | 1 | TABLE ACCESS BY INDEX ROWID| KC22 | 1 | 100 | 2 | | 2 | INDEX UNIQUE SCAN | PK_KC22_SFZH | 1 | | 1 |关键看几个东西Operation数据库在干什么。TABLE ACCESS FULL是全表扫描INDEX RANGE SCAN是走索引INDEX UNIQUE SCAN是走唯一索引Cost数据库估算的成本。越低越好Rows数据库估算会返回多少行。和实际差太远说明统计信息不准已经在跑的SQL怎么看生产环境的SQL已经在执行了怎么回头看它的执行计划SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_idabc123,formatALL));或者查正在跑的SQLSELECTsql_id,sql_textFROMv$sqlWHEREsql_textLIKE%kc22%;拿到sql_id再去查执行计划。我遇到的第一个问题全表扫描SELECT*FROMkc22WHERExmLIKE%张%;执行计划| Id | Operation | Name | Rows | Cost | |----|-------------------|------|-------|------| | 0 | SELECT STATEMENT | | 30000 | 200 | | 1 | TABLE ACCESS FULL| KC22 | 30000 | 200 |TABLE ACCESS FULL——30万行全扫一遍。原因有两个LIKE %张%通配符在前面索引用不上xm字段上根本没有索引解决加索引 改查询CREATEINDEXidx_kc22_xmONkc22(xm);然后改查询通配符放后面SELECT*FROMkc22WHERExmLIKE张%;执行计划变了| Id | Operation | Name | Rows | Cost | |----|-----------------------------|------------|------|------| | 0 | SELECT STATEMENT | | 500 | 5 | | 1 | TABLE ACCESS BY INDEX ROWID| KC22 | 500 | 5 | | 2 | INDEX RANGE SCAN | IDX_KC22_XM| 500 | 2 |Cost从200降到5。查询从30秒降到毫秒级。这一刻我理解了一件事索引不是加不加的问题是加在哪、怎么查的问题。索引的本质索引就是书的目录。没有目录找张三要从第一页翻到最后一页。有了目录先查目录知道张三在第37页直接翻过去。数据库的索引通常是B树。B树的特点叶子节点有序——支持范围查询BETWEEN、LIKE 张%叶子节点有指针——范围扫描时不需要回溯父节点非叶子节点只存键值——树的高度很低通常3~4层就能索引几千万行查找过程根节点 → 比较键值 → 走对应的子节点 → ... → 叶子节点 → 找到行号 → 回表取数据回表是关键概念——索引里存的是键值行号ROWID拿到行号后还要回原表取其他字段。如果SELECT只要索引里的字段就不需要回表这叫索引覆盖。第二个问题加了索引还是慢社保系统有个查询按身份证号和姓名同时查。SELECT*FROMkc22WHEREsfzh110101199001011234ANDxm张三;我加了两个索引CREATEINDEXidx_kc22_sfzhONkc22(sfzh);CREATEINDEXidx_kc22_xmONkc22(xm);以为够了吧执行计划一看| Id | Operation | Name | Rows | Cost | |----|-----------------------------|---------------|------|------| | 0 | SELECT STATEMENT | | 1 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID| KC22 | 1 | 4 | | 2 | INDEX UNIQUE SCAN | PK_KC22_SFZH | 1 | 2 |只走了sfzh上的索引。xm上的索引根本没用。为什么Oracle的优化器判断身份证号已经能唯一定位一条记录了再查姓名是多余的。所以只用了sfzh的索引。那这个查询其实没问题——身份证号能唯一定位效率已经很高了。但换一个场景按姓名和性别查。SELECT*FROMkc22WHERExm张三ANDxb1;这时候只有一个索引能用。如果张三有500个数据库要先从索引里找到500个行号再回表500次去检查性别。更好的做法复合索引。CREATEINDEXidx_kc22_xm_xbONkc22(xm,xb);复合索引的排列顺序很重要。原则是选择性高的放前面姓名选择性高张三在30万人里可能有几百个放前面性别选择性低只有男女两个值放后面为什么因为复合索引是按定义顺序组织的。先按姓名排序相同姓名再按性别排序。查询时先用姓名缩小范围再用性别进一步过滤。如果性别放前面索引的第一层只有两个分支几乎没有过滤效果。第三个问题索引不是越多越好有段时间我给每个查询字段都加了索引。结果INSERT变慢了——每插一条数据要同时更新所有索引UPDATE变慢了——改了一个索引列的值索引要重新组织存储空间涨了——索引也要占磁盘一个表5个索引意味着每次INSERT要写6个地方1个表5个索引。经验值一个表的索引不要超过5~6个高频查询的字段才加。可以通过监控V$SQL找出真正慢的查询有针对性地加。常见的执行计划操作类型操作含义什么时候出现TABLE ACCESS FULL全表扫描没有索引或索引不适用INDEX UNIQUE SCAN唯一索引扫描等值查询唯一索引列INDEX RANGE SCAN索引范围扫描范围查询、前缀LIKETABLE ACCESS BY INDEX ROWID通过索引回表索引里没有所需的所有列SORT ORDER BY排序ORDER BY且没有索引支持HASH JOIN哈希连接大表关联等值连接NESTED LOOPS嵌套循环小表驱动大表MERGE JOIN合并连接两个表都按连接列排序看到TABLE ACCESS FULL不要慌——小表全表扫描比走索引还快。大表超过几万行全表扫描才是问题。统计信息执行计划的基石有一次我明明加了索引执行计划还是走全表扫描。折腾半天才发现Oracle的统计信息过期了。执行计划是优化器根据统计信息做决策的。统计信息包括表有多少行、每个列有多少不同的值、数据分布情况等。如果统计信息不准优化器就会做出错误判断。-- 手动收集统计信息BEGINDBMS_STATS.GATHER_TABLE_STATS(ownnameSCOTT,tabnameKC22,cascadeTRUE);END;/收集完执行计划立刻变了。走了索引。生产环境要定期收集统计信息。Oracle有自动任务但有时候不够——大批量数据导入后、大量删除后统计信息可能严重失真。我学到的几条规矩写SQL之前先想执行计划——这条SQL会扫多少行能走索引吗索引加在查询条件上——SELECT的列不影响索引选择WHERE的列才影响复合索引注意顺序——选择性高的放前面不要盲目加索引——索引有代价写入变慢慢查询先看执行计划——别猜让数据库告诉你它在干什么定期收集统计信息——执行计划的准确性依赖统计信息从SQL消费者到SQL思考者这个阶段最大的变化以前写SQL只考虑对不对现在开始考虑快不快。执行计划就是那个转折点。它让数据库告诉你它在干什么而不是让你猜。下一篇要进入更深的水域——当查询优化解决不了问题数据库本身挂了的时候。标签#DBA #Oracle #执行计划 #索引 #全表扫描 #复合索引 #统计信息 #SQL优化